ALTER TABLE blobs
  ADD COLUMN digest_sha2_256 BYTEA NULL,
  ADD COLUMN digest_sha3_256 BYTEA NULL,
  ADD CONSTRAINT check_digest_sha2_256 CHECK (digest_sha2_256 IS NULL OR LENGTH(digest_sha2_256) = 32),
  ADD CONSTRAINT check_digest_sha3_256 CHECK (digest_sha3_256 IS NULL OR LENGTH(digest_sha3_256) = 32);

CREATE TYPE PERIOD AS RANGE (
  subtype = INSTANT
);
CREATE DOMAIN PERIOD_LOWER AS PERIOD CHECK (NOT lower_inf(VALUE) AND lower_inc(VALUE) AND NOT upper_inc(VALUE));

CREATE DOMAIN game_option_display_name AS VARCHAR(100);
CREATE TYPE game_option_display_name_i18n_item AS(locale LOCALE_ID, value GAME_OPTION_DISPLAY_NAME);

CREATE DOMAIN game_option_display_name_i18n_id AS UUID;

CREATE DOMAIN GIT_COMMIT_REF AS BYTEA CHECK (LENGTH(VALUE) = 20);

CREATE TYPE GAME_CHANNEL_PERMISSION AS ENUM ('None', 'View', 'Play', 'Debug', 'Manage');
CREATE TYPE GAME_ENGINE_TYPE AS ENUM ('V96', 'Custom');
CREATE TYPE GAME_CATEGORY2 AS ENUM ('Big', 'Small', 'Puzzle', 'Challenge', 'Fun', 'Lab', 'Other');

CREATE DOMAIN PATCHER_FRAMEWORK_NAME AS VARCHAR(32) CHECK(value ~ '^[a-z][a-z0-9]*$');

CREATE DOMAIN FAMILIES_STRING AS TEXT CHECK(value ~ '^(?:(?:0|[1-9]\d*)(?:,(?:0|[1-9]\d*))*)?$');

-- Display name for a blob attached to a game (file name in the legacy API)
CREATE DOMAIN GAME_RESOURCE_DISPLAY_NAME AS VARCHAR(100);

--- Checks that arr is an array of unique non-null values sorted in ascending order
CREATE OR REPLACE FUNCTION test_game_option_display_name_i18n_items(
  IN main_locale LOCALE_ID,
  IN items game_option_display_name_i18n_item[]
) RETURNS BOOLEAN
  LANGUAGE sql
  IMMUTABLE STRICT PARALLEL SAFE AS
$$
SELECT
  -- The main locale is present
  (main_locale IN (
  SELECT (item).locale
  FROM (
    SELECT DISTINCT UNNEST(items) AS item
  ) AS items
))
  AND
  -- Entries are sorted by locale (without duplicates)
  (
    (
      SELECT ARRAY_AGG(locale)
      FROM (
        SELECT (item).locale::TEXT AS locale
        FROM (SELECT UNNEST(items) AS item) AS flat
      ) AS locales
    ) = (
      SELECT ARRAY_AGG(locale)
      FROM (
        SELECT DISTINCT (item).locale::TEXT AS locale
        FROM (SELECT UNNEST(items) AS item) AS flat
        ORDER BY locale ASC
      ) AS locales
    )
  );
$$;

CREATE TABLE game_option_display_name_i18n (
  game_option_display_name_id GAME_OPTION_DISPLAY_NAME_I18N_ID NOT NULL,
  main_locale LOCALE_ID NOT NULL,
  items game_option_display_name_i18n_item[] NOT NULL,
  CHECK (test_game_option_display_name_i18n_items(main_locale, items)),
  UNIQUE (game_option_display_name_id, main_locale),
  UNIQUE (main_locale, items)
);

CREATE DOMAIN game_option_id AS UUID;
CREATE TABLE game_option (
  game_option_id GAME_OPTION_ID NOT NULL,
  main_locale LOCALE_ID NOT NULL,
  display_name game_option_display_name_i18n_id NOT NULL,
  is_visible BOOLEAN NOT NULL,
  is_enabled BOOLEAN NOT NULL,
  default_value BOOLEAN NOT NULL,
  PRIMARY KEY (game_option_id),
  CONSTRAINT game_option__display_name__fk FOREIGN KEY (display_name, main_locale) REFERENCES game_option_display_name_i18n(game_option_display_name_id, main_locale)  ON DELETE RESTRICT ON UPDATE RESTRICT,
  UNIQUE (main_locale, display_name, is_visible, is_enabled, default_value),
  UNIQUE (game_option_id, main_locale)
);

CREATE DOMAIN game_mode_display_name AS VARCHAR(100);
CREATE TYPE game_mode_display_name_i18n_item AS(locale LOCALE_ID, value GAME_MODE_DISPLAY_NAME);

CREATE DOMAIN game_mode_display_name_i18n_id AS UUID;

--- Checks that arr is an array of unique non-null values sorted in ascending order
CREATE OR REPLACE FUNCTION test_game_mode_display_name_i18n_items(
  IN main_locale LOCALE_ID,
  IN items game_mode_display_name_i18n_item[]
) RETURNS BOOLEAN
  LANGUAGE sql
  IMMUTABLE STRICT PARALLEL SAFE AS
$$
SELECT
  -- The main locale is present
  (main_locale IN (
  SELECT (item).locale
  FROM (
    SELECT DISTINCT UNNEST(items) AS item
  ) AS items
))
  AND
  -- Entries are sorted by locale (without duplicates)
  (
    (
      SELECT ARRAY_AGG(locale)
      FROM (
        SELECT (item).locale::TEXT AS locale
        FROM (SELECT UNNEST(items) AS item) AS flat
      ) AS locales
    ) = (
      SELECT ARRAY_AGG(locale)
      FROM (
        SELECT DISTINCT (item).locale::TEXT AS locale
        FROM (SELECT UNNEST(items) AS item) AS flat
        ORDER BY locale ASC
      ) AS locales
    )
  );
$$;

CREATE TABLE game_mode_display_name_i18n (
  game_mode_display_name_id GAME_MODE_DISPLAY_NAME_I18N_ID NOT NULL,
  main_locale LOCALE_ID NOT NULL,
  items game_mode_display_name_i18n_item[] NOT NULL,
  CHECK (test_game_mode_display_name_i18n_items(main_locale, items)),
  UNIQUE (game_mode_display_name_id, main_locale),
  UNIQUE (main_locale, items)
);

CREATE DOMAIN game_mode_id AS UUID;
CREATE TABLE game_mode (
  game_mode_id GAME_MODE_ID NOT NULL,
  main_locale LOCALE_ID NOT NULL,
  display_name game_mode_display_name_i18n_id NOT NULL,
  is_visible BOOLEAN NOT NULL,
  -- `sha3(json([["option_key1","option_id1"],["option_key2","option_id2"]])`
  -- TODO: Consider adding the rank of the latest option? (reverse foreign-key)
  _option_list_sha3_256 BYTEA NOT NULL,
  PRIMARY KEY (game_mode_id),
  CONSTRAINT game_mode__display_name__fk FOREIGN KEY (display_name, main_locale) REFERENCES game_mode_display_name_i18n(game_mode_display_name_id, main_locale)  ON DELETE RESTRICT ON UPDATE RESTRICT,
  UNIQUE (main_locale, display_name, is_visible, _option_list_sha3_256),
  UNIQUE (game_mode_id, main_locale)
);

CREATE DOMAIN game_option_key AS VARCHAR(100);

CREATE TABLE game_mode_option (
  game_mode_id GAME_MODE_ID NOT NULL,
  -- TODO: Consider adding the max rank? (for the reverse foreign-key with the game mode)
  main_locale LOCALE_ID NOT NULL,
  rank U32 NOT NULL,
  game_option_key GAME_OPTION_KEY NOT NULL,
  game_option_id GAME_OPTION_ID NOT NULL,
  _prev U32 NULL,
  PRIMARY KEY (game_mode_id, game_option_id),
  UNIQUE (game_mode_id, rank),
  UNIQUE (game_mode_id, game_option_key),
  -- Rank is zero, or prev is one less and points to a predecessor
  CHECK ((_prev IS NULL AND rank = 0) OR (_prev IS NOT NULL AND rank = _prev + 1)),
  CONSTRAINT game_mode_option__mode__fk FOREIGN KEY (game_mode_id, main_locale) REFERENCES game_mode(game_mode_id, main_locale)  ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT game_mode_option__option__fk FOREIGN KEY (game_option_id, main_locale) REFERENCES game_option(game_option_id, main_locale)  ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT game_mode_option__prev__fk FOREIGN KEY (game_mode_id, _prev) REFERENCES game_mode_option(game_mode_id, rank)  ON DELETE RESTRICT ON UPDATE RESTRICT
);

CREATE DOMAIN game_id AS UUID;
CREATE DOMAIN game_build_id AS UUID;

CREATE DOMAIN game_display_name AS VARCHAR(100);
CREATE TYPE game_display_name_i18n_item AS(locale LOCALE_ID, value GAME_DISPLAY_NAME);

CREATE DOMAIN game_display_name_i18n_id AS UUID;

--- Checks that arr is an array of unique non-null values sorted in ascending order
CREATE OR REPLACE FUNCTION test_game_display_name_i18n_items(
  IN main_locale LOCALE_ID,
  IN items game_display_name_i18n_item[]
) RETURNS BOOLEAN
  LANGUAGE sql
  IMMUTABLE STRICT PARALLEL SAFE AS
$$
SELECT
  -- The main locale is present
  (main_locale IN (
    SELECT (item).locale
    FROM (
      SELECT DISTINCT UNNEST(items) AS item
    ) AS items
  ))
    AND
    -- Entries are sorted by locale (without duplicates)
    (
      (
        SELECT ARRAY_AGG(locale)
        FROM (
          SELECT (item).locale::TEXT AS locale
          FROM (SELECT UNNEST(items) AS item) AS flat
        ) AS locales
      ) = (
        SELECT ARRAY_AGG(locale)
        FROM (
          SELECT DISTINCT (item).locale::TEXT AS locale
          FROM (SELECT UNNEST(items) AS item) AS flat
          ORDER BY locale ASC
        ) AS locales
      )
      );
$$;

CREATE TABLE game_display_name_i18n (
  game_display_name_id GAME_DISPLAY_NAME_I18N_ID NOT NULL,
  main_locale LOCALE_ID NOT NULL,
  items game_display_name_i18n_item[] NOT NULL,
  CHECK (test_game_display_name_i18n_items(main_locale, items)),
  UNIQUE (game_display_name_id, main_locale),
  UNIQUE (main_locale, items)
);

CREATE DOMAIN game_description AS VARCHAR(1000);
CREATE TYPE game_description_i18n_item AS(locale LOCALE_ID, value GAME_DESCRIPTION);

CREATE DOMAIN game_description_i18n_id AS UUID;

--- Checks that arr is an array of unique non-null values sorted in ascending order
CREATE OR REPLACE FUNCTION test_game_description_i18n_items(
  IN main_locale LOCALE_ID,
  IN items game_description_i18n_item[]
) RETURNS BOOLEAN
  LANGUAGE sql
  IMMUTABLE STRICT PARALLEL SAFE AS
$$
SELECT
  -- The main locale is present
  (main_locale IN (
    SELECT (item).locale
    FROM (
      SELECT DISTINCT UNNEST(items) AS item
    ) AS items
  ))
    AND
    -- Entries are sorted by locale (without duplicates)
    (
      (
        SELECT ARRAY_AGG(locale)
        FROM (
          SELECT (item).locale::TEXT AS locale
          FROM (SELECT UNNEST(items) AS item) AS flat
        ) AS locales
      ) = (
        SELECT ARRAY_AGG(locale)
        FROM (
          SELECT DISTINCT (item).locale::TEXT AS locale
          FROM (SELECT UNNEST(items) AS item) AS flat
          ORDER BY locale ASC
        ) AS locales
      )
      );
$$;

CREATE TABLE game_description_i18n (
  game_description_id GAME_DESCRIPTION_I18N_ID NOT NULL,
  main_locale LOCALE_ID NOT NULL,
  items game_description_i18n_item[] NOT NULL,
  CHECK (test_game_description_i18n_items(main_locale, items)),
  UNIQUE (game_description_id, main_locale),
  UNIQUE (main_locale, items)
);

CREATE DOMAIN blob_i18n_id AS UUID;

CREATE TABLE blob_i18n (
  blob_i18n_id BLOB_I18N_ID NOT NULL,
  main_locale LOCALE_ID NOT NULL,
  _sha3_256 BYTEA NOT NULL,
  PRIMARY KEY (blob_i18n_id),
  UNIQUE (main_locale, _sha3_256),
  UNIQUE (blob_i18n_id, main_locale)
);

-- An entry in the `blob_i18n` sorted map, with key `locale` and value `blob_id`
CREATE TABLE blob_i18n_item (
  blob_i18n_id BLOB_I18N_ID NOT NULL,
  locale LOCALE_ID NOT NULL,
  blob_id BLOB_ID NOT NULL,
  PRIMARY KEY (blob_i18n_id, blob_id),
  UNIQUE (blob_i18n_id, locale),
  CONSTRAINT blob_i18n_item__blob_i18n__fk FOREIGN KEY (blob_i18n_id) REFERENCES blob_i18n(blob_i18n_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT blob_i18n_item__blob__fk FOREIGN KEY (blob_id) REFERENCES blobs(blob_id) ON DELETE RESTRICT ON UPDATE RESTRICT
);

ALTER TABLE blob_i18n
  ADD CONSTRAINT blob_i18n_main__blob_i18n_item__fk FOREIGN KEY (blob_i18n_id, main_locale) REFERENCES blob_i18n_item(blob_i18n_id, locale) ON DELETE RESTRICT ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED;

CREATE DOMAIN GAME_RESOURCE_ID AS UUID;
CREATE DOMAIN GAME_RESOURCE_LIST_ID AS UUID;
CREATE DOMAIN game_resource_display_name_i18n_id AS UUID;
CREATE TYPE game_resource_display_name_i18n_item AS(locale LOCALE_ID, value GAME_RESOURCE_DISPLAY_NAME);

--- Checks that arr is an array of unique non-null values sorted in ascending order
CREATE OR REPLACE FUNCTION test_game_resource_display_name_i18n_items(
  IN main_locale LOCALE_ID,
  IN items game_resource_display_name_i18n_item[]
) RETURNS BOOLEAN
  LANGUAGE sql
  IMMUTABLE STRICT PARALLEL SAFE AS
$$
SELECT
  -- The main locale is present
  (main_locale IN (
    SELECT (item).locale
    FROM (
           SELECT DISTINCT UNNEST(items) AS item
         ) AS items
  ))
    AND
    -- Entries are sorted by locale (without duplicates)
  (
      (
        SELECT ARRAY_AGG(locale)
        FROM (
               SELECT (item).locale::TEXT AS locale
               FROM (SELECT UNNEST(items) AS item) AS flat
             ) AS locales
      ) = (
        SELECT ARRAY_AGG(locale)
        FROM (
               SELECT DISTINCT (item).locale::TEXT AS locale
               FROM (SELECT UNNEST(items) AS item) AS flat
               ORDER BY locale ASC
             ) AS locales
      )
    );
$$;

CREATE TABLE game_resource_display_name_i18n (
  game_resource_display_name_id GAME_RESOURCE_DISPLAY_NAME_I18N_ID NOT NULL,
  main_locale LOCALE_ID NOT NULL,
  items game_resource_display_name_i18n_item[] NOT NULL,
  CHECK (test_game_resource_display_name_i18n_items(main_locale, items)),
  UNIQUE (game_resource_display_name_id, main_locale),
  UNIQUE (main_locale, items)
);

CREATE TABLE game_resource (
  game_resource_id GAME_RESOURCE_ID NOT NULL,
  main_locale LOCALE_ID NOT NULL,
  blob_i18n_id blob_i18n_id NOT NULL,
  display_name game_resource_display_name_i18n_id NULL,
  PRIMARY KEY (game_resource_id),
  UNIQUE(game_resource_id, main_locale),
  UNIQUE NULLS NOT DISTINCT (main_locale, blob_i18n_id, display_name),
  CONSTRAINT game_resource__blob__fk FOREIGN KEY (blob_i18n_id, main_locale) REFERENCES blob_i18n(blob_i18n_id, main_locale) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT game_resource__display_name__fk FOREIGN KEY (display_name, main_locale) REFERENCES game_resource_display_name_i18n(game_resource_display_name_id, main_locale) ON DELETE RESTRICT ON UPDATE RESTRICT
);

CREATE TABLE game_resource_list (
  game_resource_list_id GAME_RESOURCE_LIST_ID NOT NULL,
  main_locale LOCALE_ID NOT NULL,
  size U32 NOT NULL,
  _sha3_256 BYTEA NOT NULL,
  PRIMARY KEY (game_resource_list_id),
  UNIQUE(game_resource_list_id, main_locale),
  UNIQUE NULLS NOT DISTINCT (main_locale, size, _sha3_256)
);

CREATE TABLE game_resource_list_item (
  game_resource_list_id GAME_RESOURCE_LIST_ID NOT NULL,
  -- TODO: Consider adding the max rank? (for the reverse foreign-key with the game mode)
  main_locale LOCALE_ID NOT NULL,
  rank U32 NOT NULL,
  game_resource_id GAME_RESOURCE_ID NOT NULL,
  _prev U32 NULL,
  PRIMARY KEY (game_resource_list_id, rank),
  -- Rank is zero, or prev is one less and points to a predecessor
  CHECK ((_prev IS NULL AND rank = 0) OR (_prev IS NOT NULL AND rank = _prev + 1)),
  CONSTRAINT game_resource_list_item__list__fk FOREIGN KEY (game_resource_list_id, main_locale) REFERENCES game_resource_list(game_resource_list_id, main_locale) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT game_resource_list_item__resource__fk FOREIGN KEY (game_resource_id, main_locale) REFERENCES game_resource(game_resource_id, main_locale) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT game_resource_list_item__prev__fk FOREIGN KEY (game_resource_list_id, _prev) REFERENCES game_resource_list_item(game_resource_list_id, rank) ON DELETE RESTRICT ON UPDATE RESTRICT
);

CREATE DOMAIN GAME_PATCHER_ID AS UUID;

CREATE TABLE game_patcher (
  game_patcher_id GAME_PATCHER_ID NOT NULL,
  blob_id BLOB_ID NOT NULL,
  patcher_framework_name PATCHER_FRAMEWORK_NAME NOT NULL,
  patcher_framework_version_major U32 NOT NULL,
  patcher_framework_version_minor U32 NOT NULL,
  patcher_framework_version_patch U32 NOT NULL,
  meta JSONB NULL,
  PRIMARY KEY (game_patcher_id),
  CHECK (pg_column_size(meta) < 1024 * 1024),
  UNIQUE NULLS NOT DISTINCT (blob_id, patcher_framework_name, patcher_framework_version_major, patcher_framework_version_minor, patcher_framework_version_patch, meta)
);

CREATE DOMAIN game_channel_id AS UUID;
CREATE DOMAIN game_channel_key AS VARCHAR(100);
CREATE DOMAIN game_key AS VARCHAR(100);

CREATE TABLE game (
  game_id GAME_ID NOT NULL,
  created_at INSTANT NOT NULL,
  owner USER_ID NOT NULL,
  main_channel_key GAME_CHANNEL_KEY NOT NULL,
  PRIMARY KEY (game_id),
  CONSTRAINT game__owner__fk FOREIGN KEY (owner) REFERENCES users(user_id) ON DELETE RESTRICT ON UPDATE CASCADE
  -- Deferred foreign key (game_id, main_channel_key) -> game_channel(game_id, game_channel_key) defined further.
);

CREATE TABLE game_key_history (
  period PERIOD_LOWER NOT NULL,
  game_key GAME_KEY NOT NULL,
  game_id GAME_ID NOT NULL,
  PRIMARY KEY (period, game_key),
  CONSTRAINT game_key__game__fk FOREIGN KEY (game_id) REFERENCES game(game_id) ON DELETE CASCADE ON UPDATE CASCADE,
  EXCLUDE USING gist (game_key WITH =, period WITH &&)
);

CREATE TABLE game_build (
  game_build_id GAME_BUILD_ID NOT NULL,
  main_locale LOCALE_ID NOT NULL,
  game_display_name_id game_display_name_i18n_id NOT NULL,
  game_description_id game_description_i18n_id NULL,
  icon blob_i18n_id NULL,
  loader_version_major U32 NOT NULL,
  loader_version_minor U32 NOT NULL,
  loader_version_patch U32 NOT NULL,
  engine_type GAME_ENGINE_TYPE NOT NULL,
  custom_engine BLOB_ID NULL,
  patcher GAME_PATCHER_ID NULL,
  debug BLOB_ID NULL,
  content BLOB_ID NULL,
  content_i18n blob_i18n_id NULL,
  music_list GAME_RESOURCE_LIST_ID NOT NULL,
  families FAMILIES_STRING NOT NULL,
  category GAME_CATEGORY2 NOT NULL,
  -- locale_pack GAME_ASSET_ID NULL,
  -- `sha3(json([["mode_key1","mode_id1"],["mode_key2","mode_id2"]])`
  -- TODO: Consider adding the rank of the latest mode? (reverse foreign-key)
  _mode_list_sha3_256 BYTEA NOT NULL,
  PRIMARY KEY (game_build_id),
  CHECK ((engine_type = 'V96' AND custom_engine IS NULL) OR (engine_type = 'Custom' AND custom_engine IS NOT NULL)),
  CONSTRAINT game_build__display_name__fk FOREIGN KEY (game_display_name_id, main_locale) REFERENCES game_display_name_i18n(game_display_name_id, main_locale) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT game_build__description__fk FOREIGN KEY (game_description_id, main_locale) REFERENCES game_description_i18n(game_description_id, main_locale) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT game_build__icon__fk FOREIGN KEY (icon, main_locale) REFERENCES blob_i18n(blob_i18n_id, main_locale) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT game_build__custom_engine__fk FOREIGN KEY (custom_engine) REFERENCES blobs(blob_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT game_build__patcher__fk FOREIGN KEY (patcher) REFERENCES game_patcher(game_patcher_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT game_build__debug__fk FOREIGN KEY (debug) REFERENCES blobs(blob_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT game_build__content__fk FOREIGN KEY (content) REFERENCES blobs(blob_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT game_build__content_i18n__fk FOREIGN KEY (content_i18n, main_locale) REFERENCES blob_i18n(blob_i18n_id, main_locale) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT game_build__music_list__fk FOREIGN KEY (music_list, main_locale) REFERENCES game_resource_list(game_resource_list_id, main_locale) ON DELETE RESTRICT ON UPDATE RESTRICT,
  -- Used for reverse FKs (e.g. in `game_build_mode`)
  UNIQUE (game_build_id, main_locale),

  UNIQUE NULLS NOT DISTINCT (main_locale, game_display_name_id, game_description_id, icon, loader_version_major, loader_version_minor, loader_version_patch, engine_type, custom_engine, patcher, debug, content, content_i18n, music_list, families, category, _mode_list_sha3_256)
);

CREATE DOMAIN game_mode_key AS VARCHAR(100);

CREATE TABLE game_build_mode (
  game_build_id GAME_MODE_ID NOT NULL,
  -- TODO: Consider adding the max rank? (for the reverse foreign-key with the game build)
  main_locale LOCALE_ID NOT NULL,
  rank U32 NOT NULL,
  game_mode_key GAME_MODE_KEY NOT NULL,
  game_mode_id GAME_MODE_ID NOT NULL,
  _prev U32 NULL,
  PRIMARY KEY (game_build_id, game_mode_id),
  UNIQUE (game_build_id, rank),
  UNIQUE (game_build_id, game_mode_key),
  -- Rank is zero, or prev is one less and points to a predecessor
  CHECK ((_prev IS NULL AND rank = 0) OR (_prev IS NOT NULL AND rank = _prev + 1)),
  CONSTRAINT game_build_mode__build__fk FOREIGN KEY (game_build_id, main_locale) REFERENCES game_build(game_build_id, main_locale)  ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT game_build_mode__mode__fk FOREIGN KEY (game_mode_id, main_locale) REFERENCES game_mode(game_mode_id, main_locale)  ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT game_build_mode__prev__fk FOREIGN KEY (game_build_id, _prev) REFERENCES game_build_mode(game_build_id, rank)  ON DELETE RESTRICT ON UPDATE RESTRICT
);

--
-- CREATE DOMAIN game_id AS UUID;
-- CREATE DOMAIN game_revision_id AS UUID;
-- CREATE DOMAIN game_version_id AS UUID;
-- CREATE DOMAIN game_release_id AS UUID;
-- CREATE DOMAIN game_option_list_id AS UUID;
--
-- CREATE DOMAIN game_option_display_name_i18n_id AS UUID;
-- CREATE DOMAIN game_display_name AS VARCHAR(100);
-- CREATE DOMAIN game_channel_display_name AS VARCHAR(100);
-- CREATE TYPE game_channel_permission AS ENUM ('Manage', 'Debug', 'Play', 'View');
-- CREATE TYPE role_type AS ENUM ('Group', 'User');
--
-- CREATE TABLE game (
--   game_id GAME_ID PRIMARY KEY NOT NULL,
--   created_at INSTANT NOT NULL
-- --   fallback_display_name game_display_name NOT NULL,
-- --   owner USER_ID NOT NULL
-- );

CREATE TABLE game_version (
  game_id GAME_ID NOT NULL,
  version_major U32 NOT NULL,
  version_minor U32 NOT NULL,
  version_patch U32 NOT NULL,
  git_commit_ref GIT_COMMIT_REF NULL,
  created_at INSTANT NOT NULL,
  game_build_id GAME_BUILD_ID NOT NULL,
  PRIMARY KEY (game_id, version_major, version_minor, version_patch),
  CONSTRAINT game_version__game__fk FOREIGN KEY (game_id) REFERENCES game(game_id)  ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT game_version__game_build__fk FOREIGN KEY (game_build_id) REFERENCES game_build(game_build_id)  ON DELETE CASCADE ON UPDATE CASCADE
);

-- Namespace inside a game
CREATE TABLE game_channel (
--   game_channel_id GAME_CHANNEL_ID PRIMARY KEY NOT NULL,
  game_id GAME_ID NOT NULL,
  game_channel_key GAME_CHANNEL_KEY NOT NULL,
  created_at INSTANT NOT NULL,
  first_history_tt_period PERIOD_LOWER NOT NULL,
  first_history_vt_period PERIOD_LOWER NOT NULL,
  PRIMARY KEY (game_id, game_channel_key),
  UNIQUE (game_id, game_channel_key, created_at),
  CONSTRAINT game_channel__game__fk FOREIGN KEY (game_id) REFERENCES game(game_id)  ON DELETE CASCADE ON UPDATE CASCADE
  -- Deferred foreign key (game_id, game_channel_key, first_history_tt_period, first_history_vt_period) -> game_channel_history(game_id, game_channel_key, tt_period, vt_period) defined further.
);

ALTER TABLE game
  ADD CONSTRAINT game__game_channel__fk FOREIGN KEY (game_id, main_channel_key) REFERENCES game_channel(game_id, game_channel_key) ON DELETE RESTRICT ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED;

-- Semi-infinite bitemporal map with checked absence of gaps
CREATE TABLE game_channel_history (
  game_id GAME_ID NOT NULL,
  game_channel_key GAME_CHANNEL_KEY NOT NULL,
  -- Transaction time period
  tt_period PERIOD_LOWER NOT NULL,
  -- Valid time period
  vt_period PERIOD_LOWER NOT NULL,
  -- Denormalized game creation date; to ensure continuity along both time axis
  _game_channel_created_at INSTANT NOT NULL,
  -- `(tt_next_{vt,tt}_period)` is a pointer to the next tt entry, null if infinite
  _tt_next_tt_period PERIOD_LOWER NULL,
  _tt_next_vt_period PERIOD_LOWER NULL,
  -- `(vt_next_{vt,tt}_period)` is a pointer to the next vt entry, null if infinite
  _vt_next_tt_period PERIOD_LOWER NULL,
  _vt_next_vt_period PERIOD_LOWER NULL,
  -- User who created this history entry
  created_by USER_ID NOT NULL,
  -- Game revision to use at this time
  version_major U32 NOT NULL,
  version_minor U32 NOT NULL,
  version_patch U32 NOT NULL,
  -- Boolean indicating that the channel is enabled, only admins and owners can see disabled channels
  is_enabled BOOLEAN NOT NULL,
  -- Rank of this channel among the other channels. Channels are sorted by (lowest rank first, lowest creation time first).
  rank I32 NOT NULL,
  -- Default permission for authenticated users
  default_permission GAME_CHANNEL_PERMISSION NOT NULL CHECK (default_permission IN ('None', 'View', 'Play')),
  -- Give priority when listing games
  is_pinned BOOLEAN NOT NULL,
  -- Displayed date of publication
  publication_date INSTANT NULL,
  -- Date used when sorting by latest meaningful update
  sort_update_date INSTANT NOT NULL,
  CHECK ((upper(tt_period) IS NULL AND _tt_next_tt_period IS NULL AND _tt_next_vt_period IS NULL) OR (upper(tt_period) IS NOT NULL AND lower(_tt_next_tt_period) = upper(tt_period) AND _tt_next_vt_period @> lower(vt_period))),
  CHECK ((upper(vt_period) IS NULL AND _vt_next_tt_period IS NULL AND _vt_next_vt_period IS NULL) OR (upper(vt_period) IS NOT NULL AND lower(_vt_next_vt_period) = upper(vt_period) AND _vt_next_tt_period @> lower(tt_period))),
  CHECK (publication_date IS NULL OR (publication_date IS NOT NULL AND sort_update_date >= publication_date)),
  PRIMARY KEY (game_id, game_channel_key, tt_period, vt_period),
  EXCLUDE USING gist (game_id WITH =, game_channel_key WITH =, tt_period WITH &&, vt_period WITH &&),
  CONSTRAINT game_channel__fk FOREIGN KEY (game_id, game_channel_key, _game_channel_created_at) REFERENCES game_channel(game_id, game_channel_key, created_at) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT user__fk FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT game_version__fk FOREIGN KEY (game_id, version_major, version_minor, version_patch) REFERENCES game_version(game_id, version_major, version_minor, version_patch) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT tt_next__fk FOREIGN KEY (game_id, game_channel_key, _tt_next_tt_period, _tt_next_vt_period) REFERENCES game_channel_history(game_id, game_channel_key, tt_period, vt_period) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT vt_next__fk FOREIGN KEY (game_id, game_channel_key, _tt_next_tt_period, _tt_next_vt_period) REFERENCES game_channel_history(game_id, game_channel_key, tt_period, vt_period) ON DELETE CASCADE ON UPDATE CASCADE
);

ALTER TABLE game_channel
  ADD CONSTRAINT game_channel__first_history__fk FOREIGN KEY (game_id, game_channel_key, first_history_tt_period, first_history_vt_period) REFERENCES game_channel_history(game_id, game_channel_key, tt_period, vt_period) ON DELETE RESTRICT ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE runs
  ADD COLUMN locale LOCALE_ID NOT NULL DEFAULT 'fr-FR',
  ADD COLUMN game_channel_key GAME_CHANNEL_KEY NULL,
  ADD COLUMN version_major U32 NULL,
  ADD COLUMN version_minor U32 NULL,
  ADD COLUMN version_patch U32 NULL,
  ADD CONSTRAINT run_game_channel__fk FOREIGN KEY (game_id, game_channel_key) REFERENCES game_channel(game_id, game_channel_key) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT run_game_build__fk FOREIGN KEY (game_id, version_major, version_minor, version_patch) REFERENCES game_version(game_id, version_major, version_minor, version_patch) ON DELETE RESTRICT ON UPDATE CASCADE;

CREATE TABLE user_favorite_game (
  period PERIOD_LOWER NOT NULL,
  user_id USER_ID NOT NULL,
  game_id GAME_ID NOT NULL,
  PRIMARY KEY (period, user_id, game_id),
  CONSTRAINT favorite__user__fk FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT favorite__game__fk FOREIGN KEY (game_id) REFERENCES game(game_id) ON DELETE CASCADE ON UPDATE CASCADE,
  EXCLUDE USING gist (user_id WITH =, game_id WITH =, period WITH &&)
);