COMMENT ON SCHEMA public IS '{"version": "V002"}';

CREATE TYPE DRIVE_ITEM_TYPE AS ENUM ('directory', 'file');
CREATE TYPE GAME_CATEGORY AS ENUM ('big', 'small', 'puzzle', 'challenge', 'fun', 'lab', 'other');

ALTER TABLE inventories
  DROP CONSTRAINT inventory_hfest_item___fk;
ALTER TABLE hfest_items
  ALTER COLUMN hfest_item_id TYPE VARCHAR(4);
ALTER TABLE inventories
  ALTER COLUMN hfest_item_id TYPE VARCHAR(4);
ALTER TABLE inventories
  ADD CONSTRAINT inventory_hfest_item___fk FOREIGN KEY (hfest_item_id) REFERENCES hfest_items (hfest_item_id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE users
  ADD COLUMN is_tester BOOLEAN,
  ADD COLUMN email VARCHAR(200),
  ADD COLUMN username VARCHAR(20),
  ADD COLUMN password_hash BYTEA;
-- noinspection SqlWithoutWhere
UPDATE users
SET is_tester = users.is_administrator;
ALTER TABLE users
  ALTER COLUMN is_tester SET NOT NULL,
  ADD CHECK (NOT is_administrator OR is_tester);

ALTER TABLE hfest_identities
  DROP CONSTRAINT hfest_identity_identity___fk;
ALTER TABLE identities
  ADD CONSTRAINT identitity_hfest_identity___fk FOREIGN KEY (identity_id) REFERENCES hfest_identities (identity_id) ON DELETE CASCADE ON UPDATE CASCADE,
  ALTER COLUMN user_id DROP NOT NULL;

ALTER TABLE sessions
  ADD COLUMN created_at TIMESTAMP(0) WITH TIME ZONE,
  ADD COLUMN updated_at TIMESTAMP(0) WITH TIME ZONE;
-- noinspection SqlWithoutWhere
UPDATE sessions
SET created_at = sessions.expiration_date,
    updated_at = sessions.expiration_date;
ALTER TABLE sessions
  ALTER COLUMN created_at SET NOT NULL,
  ALTER COLUMN updated_at SET NOT NULL,
  DROP COLUMN expiration_date,
  ADD CHECK (updated_at >= created_at);

DROP TABLE hfest_progressions;


CREATE TABLE public.blobs
(
  blob_id    UUID PRIMARY KEY         NOT NULL,
  media_type VARCHAR(100)             NOT NULL,
  byte_size  INT4                     NOT NULL,
  buffer_id  VARCHAR(64)              NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL,
  CHECK (updated_at >= created_at)
);

-- A table for the data common to both directories and files
CREATE TABLE public.drive_items
(
  drive_item_id UUID PRIMARY KEY         NOT NULL,
  type          DRIVE_ITEM_TYPE          NOT NULL,
  created_at    TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at    TIMESTAMP WITH TIME ZONE NOT NULL,
  display_name  VARCHAR(100)             NOT NULL,
  CHECK (updated_at >= created_at),
  UNIQUE (drive_item_id, type)
);

-- The closure table containing all the ancestor/descendant links for the directories and files
CREATE TABLE public.drive_item_closure
(
  ancestor_id   UUID NOT NULL,
  descendant_id UUID NOT NULL,
  -- Distance between the ancestor and descendant
  distance      INT4 NOT NULL,
  CONSTRAINT drive_item_closure_ancestor___fk FOREIGN KEY (ancestor_id) REFERENCES drive_items (drive_item_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT drive_item_closure_descendant___fk FOREIGN KEY (descendant_id) REFERENCES drive_items (drive_item_id) ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (ancestor_id, descendant_id)
);

CREATE TABLE public.directories
(
  drive_item_id UUID PRIMARY KEY NOT NULL,
  type          DRIVE_ITEM_TYPE  NOT NULL,
  CONSTRAINT directory_drive_item___fk FOREIGN KEY (drive_item_id, type) REFERENCES drive_items (drive_item_id, type) ON DELETE CASCADE ON UPDATE CASCADE,
  CHECK (type = 'directory')
);

CREATE TABLE public.files
(
  drive_item_id UUID PRIMARY KEY NOT NULL,
  type          DRIVE_ITEM_TYPE  NOT NULL,
  blob_id       UUID             NOT NULL,
  CONSTRAINT file_drive_item___fk FOREIGN KEY (drive_item_id, type) REFERENCES drive_items (drive_item_id, type) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT file_blob___fk FOREIGN KEY (blob_id) REFERENCES blobs (blob_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CHECK (type = 'file')
);

CREATE TABLE public.drives
(
  drive_id   UUID PRIMARY KEY         NOT NULL,
  owner_id   UUID                     NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL,
  root_id    UUID                     NOT NULL,
  CONSTRAINT drive_user___fk FOREIGN KEY (owner_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT drive_directory__fk FOREIGN KEY (root_id) REFERENCES directories (drive_item_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CHECK (updated_at >= created_at)
);

CREATE TABLE public.upload_sessions
(
  upload_session_id UUID PRIMARY KEY         NOT NULL,
  media_type        VARCHAR(100)             NOT NULL,
  byte_size         INT4                     NOT NULL,
  written_bytes     INT4                     NOT NULL,
  created_at        TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at        TIMESTAMP WITH TIME ZONE NOT NULL,
  buffer_id         VARCHAR(64)              NOT NULL,
  blob_id           UUID,
  CONSTRAINT upload_session_blob__fk FOREIGN KEY (blob_id) REFERENCES blobs (blob_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CHECK (written_bytes <= byte_size),
  CHECK (updated_at >= created_at)
);

CREATE TABLE public.games
(
  game_id            UUID PRIMARY KEY         NOT NULL,
  display_name       VARCHAR(100)             NOT NULL,
  created_at         TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at         TIMESTAMP WITH TIME ZONE NOT NULL,
  public_access_from TIMESTAMP WITH TIME ZONE,
  author_id          UUID                     NOT NULL,
  icon_file_id       UUID,
  category           GAME_CATEGORY            NOT NULL,
  description        TEXT                     NOT NULL,
  families           TEXT                     NOT NULL,
  loader_version     VARCHAR(20)              NOT NULL,
  is_private         BOOLEAN                  NOT NULL,
  CONSTRAINT game_author__fk FOREIGN KEY (author_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CHECK (updated_at >= created_at)
);

CREATE TABLE public.game_modes
(
  game_id    UUID         NOT NULL,
  mode_tag   VARCHAR(100) NOT NULL,
  is_enabled BOOLEAN      NOT NULL,
  _rank      INT          NOT NULL,
  PRIMARY KEY (game_id, mode_tag),
  CONSTRAINT game_mode_game__fk FOREIGN KEY (game_id) REFERENCES games (game_id) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE (game_id, mode_tag, _rank)
);

CREATE TABLE public.game_options
(
  game_id    UUID         NOT NULL,
  mode_tag   VARCHAR(100) NOT NULL,
  option_tag VARCHAR(100) NOT NULL,
  is_enabled BOOLEAN      NOT NULL,
  _rank      INT          NOT NULL,
  PRIMARY KEY (game_id, mode_tag, option_tag),
  CONSTRAINT game_option_game_mode__fk FOREIGN KEY (game_id, mode_tag) REFERENCES game_modes (game_id, mode_tag) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE (game_id, mode_tag, option_tag, _rank)
);

CREATE TABLE public.game_resources
(
  game_id      UUID NOT NULL,
  file_id      UUID NOT NULL,
  resource_tag VARCHAR(100),
  _rank        INT  NOT NULL,
  PRIMARY KEY (game_id, file_id, _rank),
  CONSTRAINT game_resource_game__fk FOREIGN KEY (game_id) REFERENCES games (game_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT game_resource_file__fk FOREIGN KEY (file_id) REFERENCES files (drive_item_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE public.runs
(
  run_id       UUID                     NOT NULL,
  game_id      UUID                     NOT NULL,
  user_id      UUID                     NOT NULL,
  created_at   TIMESTAMP WITH TIME ZONE NOT NULL,
  started_at   TIMESTAMP WITH TIME ZONE,
  game_mode    VARCHAR(100)             NOT NULL,
  game_options JSON                     NOT NULL,
  details      BOOLEAN                  NOT NULL,
  shake        BOOLEAN                  NOT NULL,
  sound        BOOLEAN                  NOT NULL,
  music        BOOLEAN                  NOT NULL,
  PRIMARY KEY (run_id),
  CONSTRAINT run_game__fk FOREIGN KEY (game_id) REFERENCES games (game_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT run_file__fk FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE
);