-- Booru bans
CREATE TABLE dnp(
  "id" varchar(255) NOT NULL,
  "service" varchar(20) NOT NULL,
  "import" boolean NOT NULL DEFAULT TRUE
);

-- Flags
CREATE TABLE booru_flags(
  "id" varchar(255) NOT NULL,
  "user" varchar(255) NOT NULL,
  "service" varchar(20) NOT NULL,
  PRIMARY KEY (id, "user", service)
);

-- Board
CREATE TABLE board_replies(
  "reply" integer NOT NULL,
  "in" integer NOT NULL
);

-- Requests
CREATE TYPE request_status AS ENUM(
  'open',
  'fulfilled',
  'closed'
);

CREATE TABLE requests(
  "id" serial PRIMARY KEY,
  "service" varchar(20) NOT NULL,
  "user" varchar(255) NOT NULL,
  "post_id" varchar(255),
  "title" text NOT NULL,
  "description" text NOT NULL DEFAULT '',
  "created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "image" text,
  "price" numeric NOT NULL,
  "votes" integer NOT NULL DEFAULT 1,
  "ips" text[] NOT NULL,
  "status" request_status NOT NULL DEFAULT 'open'
);

CREATE request_title_idx ON requests
USING btree(
  "title"
);

CREATE request_service_idx ON requests
USING btree(
  "service"
);

CREATE request_votes_idx ON requests
USING btree(
  "votes"
);

CREATE request_created_idx ON requests
USING btree(
  "created"
);

CREATE request_price_idx ON requests
USING btree(
  "price"
);

CREATE request_status_idx ON requests
USING btree(
  "status"
);

-- Request Subscriptions
CREATE TABLE request_subscriptions(
  "request_id" numeric NOT NULL,
  "endpoint" text NOT NULL,
  "expirationTime" numeric,
  "keys" jsonb NOT NULL
);

CREATE INDEX request_id_idx ON request_subscriptions USING btree("request_id");

CREATE TABLE saved_session_keys(
  id serial PRIMARY KEY,
  service varchar NOT NULL,
  discord_channel_ids varchar,
  encrypted_key varchar NOT NULL,
  added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  dead boolean NOT NULL DEFAULT FALSE,
  contributor_id int REFERENCES account(id),
  UNIQUE (service, encrypted_key)
);

CREATE TABLE saved_session_keys_with_hashes(
  id serial PRIMARY KEY,
  service varchar NOT NULL,
  discord_channel_ids varchar,
  encrypted_key varchar NOT NULL,
  hash varchar NOT NULL,
  added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  dead boolean NOT NULL DEFAULT FALSE,
  dead_at timestamp,
  contributor_id int REFERENCES account(id),
  remote_user_id_hash varchar,
  UNIQUE (service, hash)
);

CREATE TABLE saved_session_key_import_ids(
  key_id int NOT NULL,
  import_id varchar NOT NULL,
  UNIQUE (key_id, import_id)
);

CREATE TABLE complete_imports(
  user_id varchar(255) NOT NULL,
  service varchar(20) NOT NULL,
  subscription varchar(255) NOT NULL,
  last_successful_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  info jsonb NOT NULL DEFAULT '{}' ::jsonb,
  CONSTRAINT complete_imports_pkey PRIMARY KEY (user_id, service, subscription)
);

CREATE INDEX saved_session_keys_contributor_idx ON saved_session_keys USING btree("contributor_id");

CREATE INDEX saved_session_keys_with_hashes_contributor_idx ON saved_session_keys_with_hashes USING btree("contributor_id");

CREATE INDEX saved_session_keys_with_hashes_dead_idx ON saved_session_keys_with_hashes USING btree("dead");

CREATE INDEX saved_session_keys_dead_idx ON saved_session_keys USING btree("dead");

CREATE TABLE jobs(
  job_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at timestamp DEFAULT CURRENT_TIMESTAMP,
  queue_name text NOT NULL,
  priority integer NOT NULL,
  consumer_id text,
  pids integer[],
  started_at timestamp,
  last_heartbeat_at timestamp,
  job_input jsonb NOT NULL,
  job_status jsonb DEFAULT '{}' ::jsonb,
  finished_at timestamp,
  resuming_at timestamp,
  error text
);

CREATE INDEX jobs_finished_at_queue_name_job_input_key_idx ON jobs(finished_at, queue_name,(job_input ->> 'key'));

CREATE TABLE posts_forced_reimports(
  creator_id text NOT NULL,
  service text NOT NULL,
  post_id text NOT NULL,
  reason text NULL,
  created_at timestamp DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT posts_forced_reimports_pkey PRIMARY KEY (creator_id, service, post_id)
);