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

-- 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');
CREATE TYPE LOCALE_ID AS ENUM ('fr-FR', 'en-US', 'es-SP');

-- 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 RESTRICT 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,
  key                VARCHAR(32)              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 NULL,
  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,
  main_locale        LOCALE_ID                NOT NULL,
  CONSTRAINT game_author__fk FOREIGN KEY (author_id) REFERENCES users (user_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT game_locale__uniq UNIQUE (game_id, main_locale),
  CONSTRAINT game_key__uniq UNIQUE(key),
  CHECK (updated_at >= created_at)
);

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

CREATE TABLE public.game_options
(
  game_id      UUID         NOT NULL,
  mode_key     VARCHAR(100) NOT NULL,
  option_key   VARCHAR(100) NOT NULL,
  is_enabled   BOOLEAN      NOT NULL,
  _rank        INT          NOT NULL,
  main_locale  LOCALE_ID    NOT NULL,
  display_name VARCHAR(100) NOT NULL,
  PRIMARY KEY (game_id, mode_key, option_key),
  CONSTRAINT game_option_game_mode__fk FOREIGN KEY (game_id, mode_key, main_locale) REFERENCES game_modes (game_id, mode_key, main_locale) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT game_option_locale__uniq UNIQUE (game_id, mode_key, option_key, main_locale),
  UNIQUE (game_id, mode_key, option_key, _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 RESTRICT 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)
);

--- Checks that the `run_result.score` field is an array of 1 or 2 Sint32.
CREATE OR REPLACE FUNCTION test_run_result_scores(
  IN scores JSON
)
  RETURNS BOOLEAN
  LANGUAGE SQL
  IMMUTABLE
AS
$$
SELECT
    json_typeof($1) = 'array'
    AND 1 <= json_array_length($1)
    AND json_array_length($1) <= 2
    AND false NOT IN (
    SELECT
      (
          json_typeof(score.value) = 'number'
          AND -2147483648 <= score.value::TEXT::INT
          AND score.value::TEXT::INT < 2147483648
        ) AS is_ok
    FROM json_array_elements($1) AS score
  );
$$;

CREATE FUNCTION test_run_result_items(
  IN items JSON
)
  RETURNS BOOLEAN
  LANGUAGE SQL
  IMMUTABLE
AS
$$
SELECT
    json_typeof($1) = 'object'
    AND false NOT IN (
    SELECT
      (
          json_typeof(item_count.value) = 'number'
          AND 0 <= item_count.value::TEXT::INT
        ) AS is_ok
    FROM json_each($1) AS item_count
  );
$$;

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,
  max_level  INT                      NULL,
  scores     JSON                     NULL,
  items      JSON                     NULL,
  stats      JSON                     NULL,
  CONSTRAINT run_run_result___fk FOREIGN KEY (run_id) REFERENCES runs (run_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CHECK (max_level >= 0),
  CHECK (test_run_result_scores(scores)),
  CHECK (test_run_result_items(items))
);

CREATE TABLE public.blob_deletion_queue
(
  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,
  deleted_at TIMESTAMP WITH TIME ZONE NOT NULL,
  CHECK (updated_at >= created_at),
  CHECK (deleted_at >= updated_at)
);

CREATE TABLE public.game_locales
(
  game_id      UUID         NOT NULL,
  locale       LOCALE_ID    NOT NULL,
  display_name VARCHAR(100) NULL,
  description  TEXT         NULL,
  CONSTRAINT game_locale_game__fk FOREIGN KEY (game_id) REFERENCES games (game_id) ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (game_id, locale)
);

CREATE TABLE public.game_mode_locales
(
  game_id      UUID         NOT NULL,
  mode_key     VARCHAR(100) NOT NULL,
  locale       LOCALE_ID    NOT NULL,
  display_name VARCHAR(100) NULL,
  CONSTRAINT game_mode_locale_game_mode__fk FOREIGN KEY (game_id, mode_key) REFERENCES game_modes (game_id, mode_key) ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (game_id, mode_key, locale)
);

CREATE TABLE public.game_option_locales
(
  game_id      UUID         NOT NULL,
  mode_key     VARCHAR(100) NOT NULL,
  option_key   VARCHAR(100) NOT NULL,
  locale       LOCALE_ID    NOT NULL,
  display_name VARCHAR(100) NULL,
  CONSTRAINT game_option_locale_game_option__fk FOREIGN KEY (game_id, mode_key, option_key) REFERENCES game_options (game_id, mode_key, option_key) ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (game_id, mode_key, option_key, locale)
);

-- Cast sum from `BIGINT` to `INT` so it is loaded as a `number` by Node (instead of a string).
CREATE VIEW public.user_items
AS
(
WITH run_infos AS (
  SELECT
    user_id,
    game_id,
    run_id,
    items
  FROM run_results
         INNER JOIN runs USING (run_id)
),
     run_items AS (
       SELECT
         user_id,
         game_id,
         run_id,
         item.key::TEXT        AS item_id,
         item.value::TEXT::INT AS item_count
       FROM run_infos,
            LATERAL json_each(run_infos.items) AS item
     )
SELECT
  user_id,
  game_id,
  item_id,
  SUM(run_items.item_count)::INT AS item_count
FROM run_items
GROUP BY (user_id, game_id, item_id));