--  files
CREATE TABLE files(
  id serial PRIMARY KEY,
  hash varchar NOT NULL,
  mtime timestamp NOT NULL,
  ctime timestamp NOT NULL,
  mime varchar,
  ext varchar,
  added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (hash)
);

CREATE TABLE file_post_relationships(
  file_id int NOT NULL REFERENCES files(id),
  filename varchar NOT NULL,
  service varchar NOT NULL,
  "user" varchar NOT NULL,
  post varchar NOT NULL,
  contributor_id int REFERENCES account(id),
  inline boolean NOT NULL DEFAULT FALSE,
  PRIMARY KEY (file_id, service, "user", post)
);

CREATE TABLE file_discord_message_relationships(
  file_id int NOT NULL REFERENCES files(id),
  filename varchar NOT NULL,
  server varchar NOT NULL,
  channel varchar NOT NULL,
  id varchar NOT NULL,
  contributor_id int REFERENCES account(id),
  PRIMARY KEY (file_id, SERVER, channel, id)
);

CREATE TABLE file_server_relationships(
  file_id int NOT NULL REFERENCES files(id),
  remote_path varchar NOT NULL
);

CREATE TABLE archive_files(
  file_id int NOT NULL REFERENCES files(id),
  -- this for sure won't have problems down the line
  files text[] NOT NULL,
  password TEXT,
  CONSTRAINT archive_files_pk PRIMARY KEY (file_id)
);

CREATE INDEX file_id_idx ON file_post_relationships USING btree("file_id");

CREATE INDEX file_post_service_idx ON file_post_relationships USING btree("service");

CREATE INDEX file_post_user_idx ON file_post_relationships USING btree("user");

CREATE INDEX file_post_id_idx ON file_post_relationships USING btree("post");

CREATE INDEX file_post_contributor_id_idx ON file_post_relationships USING btree("contributor_id");

CREATE INDEX file_discord_id_idx ON file_discord_message_relationships USING btree("file_id");

CREATE INDEX file_discord_message_server_idx ON file_discord_message_relationships USING btree("server");

CREATE INDEX file_discord_message_channel_idx ON file_discord_message_relationships USING btree("channel");

CREATE INDEX file_discord_message_id_idx ON file_discord_message_relationships USING btree("id");

CREATE INDEX file_discord_message_contributor_id_idx ON file_discord_message_relationships USING btree("contributor_id");

CREATE INDEX file_server_relationships_remote_path_idx ON file_server_relationships USING btree(remote_path);