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

-- The list of official servers
CREATE TYPE HFEST_SERVER AS ENUM ('fr', 'en', 'es');
CREATE TYPE IDENTITY_TYPE AS ENUM ('hfest', 'ef');
CREATE TYPE DRIVE_ITEM_TYPE AS ENUM ('directory', 'file');
CREATE TYPE GAME_CATEGORY AS ENUM ('big', 'small', 'puzzle', 'challenge', 'fun', 'lab', 'other');

-- The list of itens in Hammerfest (official game)
CREATE TABLE public.hfest_items
(
  hfest_item_id VARCHAR(4) PRIMARY KEY NOT NULL,
  is_hidden     BOOLEAN                NOT NULL
);

-- A user
CREATE TABLE public.users
(
  user_id          UUID PRIMARY KEY         NOT NULL,
  created_at       TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at       TIMESTAMP WITH TIME ZONE NOT NULL,
  display_name     VARCHAR(20)              NOT NULL,
  is_administrator BOOLEAN                  NOT NULL,
  -- Has access to advanced Eternalfest features (closed beta)
  is_tester        BOOLEAN                  NOT NULL,
  email            VARCHAR(200),
  -- Used for authentication and customized URL
  username         VARCHAR(20),
  -- Hashed (scrypt) password (96 bytes)
  password_hash    BYTEA,
  CHECK (updated_at >= created_at),
  -- (is_administrator -> is_tester) = (!is_administrator || is_tester)
  CHECK (NOT is_administrator OR is_tester)
);

-- An Hammerfest account (hfest identity)
CREATE TABLE public.hfest_identities
(
  -- The parent identity
  identity_id    UUID PRIMARY KEY NOT NULL,
  -- Hammerfest server (fr, en, es)
  server         HFEST_SERVER     NOT NULL,
  -- Hammerfest id
  hfest_id       INT              NOT NULL,
  username       VARCHAR(20)      NOT NULL,
  email          VARCHAR(120),
  best_score     INT              NOT NULL,
  best_level     INT              NOT NULL,
  game_completed BOOLEAN          NOT NULL,
  UNIQUE (server, hfest_id),
  UNIQUE (server, username)
);

-- An Hammerfest or Eternalfest account (= save file in a desktop game)
CREATE TABLE public.identities
(
  identity_id UUID PRIMARY KEY         NOT NULL,
  type        IDENTITY_TYPE            NOT NULL,
  created_at  TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at  TIMESTAMP WITH TIME ZONE NOT NULL,
  -- If `user_id` is `null`, the identity is not yet attached to a user (can be created by unlinking for example).
  user_id     UUID,
  CONSTRAINT identity_user___fk FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT identitity_hfest_identity___fk FOREIGN KEY (identity_id) REFERENCES hfest_identities (identity_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CHECK (updated_at >= created_at)
);

-- The relation between the hfest_items and identities
CREATE TABLE public.inventories
(
  identity_id   UUID       NOT NULL,
  hfest_item_id VARCHAR(4) NOT NULL,
  count         INT        NOT NULL,
  max_count     INT        NOT NULL,
  CONSTRAINT inventory_hfest_item___fk FOREIGN KEY (hfest_item_id) REFERENCES hfest_items (hfest_item_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT inventory_identity___fk FOREIGN KEY (identity_id) REFERENCES identities (identity_id) ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (hfest_item_id, identity_id)
);

-- A session
CREATE TABLE public.sessions
(
  session_id UUID PRIMARY KEY            NOT NULL,
  user_id    UUID,
  -- 0: second precision
  created_at TIMESTAMP(0) WITH TIME ZONE NOT NULL,
  -- 0: second precision
  updated_at TIMESTAMP(0) WITH TIME ZONE NOT NULL,
  data       JSON                        NOT NULL,
  CHECK (updated_at >= created_at),
  CONSTRAINT session_user___fk FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE
);

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,
  detail       BOOLEAN                  NOT NULL,
  shake        BOOLEAN                  NOT NULL,
  sound        BOOLEAN                  NOT NULL,
  music        BOOLEAN                  NOT NULL,
  volume       INT                      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,
  CHECK (0 <= volume AND volume <= 100)
);

CREATE TABLE public.run_results
(
  run_id     UUID PRIMARY KEY         NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL,
  is_victory BOOLEAN                  NOT NULL,
  raw        JSON                     NOT NULL,
  CONSTRAINT run_run_result___fk FOREIGN KEY (run_id) REFERENCES runs (run_id) ON DELETE CASCADE ON UPDATE CASCADE
);