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

CREATE TYPE LOCALE_ID AS ENUM ('fr-FR', 'en-US', 'es-SP');

ALTER TABLE games
  ADD COLUMN main_locale LOCALE_ID NULL;
ALTER TABLE game_modes
  ADD COLUMN main_locale  LOCALE_ID    NULL,
  ADD COLUMN display_name VARCHAR(100) NULL;
ALTER TABLE game_options
  ADD COLUMN main_locale  LOCALE_ID    NULL,
  ADD COLUMN display_name VARCHAR(100) NULL;

-- noinspection SqlWithoutWhere
UPDATE games SET main_locale = 'fr-FR';
-- noinspection SqlWithoutWhere
UPDATE game_modes SET main_locale  = 'fr-FR', display_name = mode_key;
UPDATE game_modes SET display_name = 'Boss Rush' WHERE mode_key = 'bossrush';
UPDATE game_modes SET display_name = 'Multi Coopératif' WHERE mode_key = 'multicoop';
UPDATE game_modes SET display_name = 'TA Multi' WHERE mode_key = 'multitime';
UPDATE game_modes SET display_name = 'Soccerfest' WHERE mode_key = 'soccer';
UPDATE game_modes SET display_name = 'Gazon maudit' WHERE mode_key = 'soccer_0';
UPDATE game_modes SET display_name = 'Temple du Ballon' WHERE mode_key = 'soccer_1';
UPDATE game_modes SET display_name = 'VolleyFest' WHERE mode_key = 'soccer_2';
UPDATE game_modes SET display_name = 'Maitrise aérienne' WHERE mode_key = 'soccer_3';
UPDATE game_modes SET display_name = 'Piste temporelle' WHERE mode_key = 'soccer_4';
UPDATE game_modes SET display_name = 'Stade de Basalte' WHERE mode_key = 'soccer_5';
UPDATE game_modes SET display_name = 'Coupe de glace' WHERE mode_key = 'soccer_6';
UPDATE game_modes SET display_name = 'Carton noir' WHERE mode_key = 'soccer_7';
UPDATE game_modes SET display_name = 'Terrain oublié' WHERE mode_key = 'soccer_8';
UPDATE game_modes SET display_name = 'Orange gardien' WHERE mode_key = 'soccer_9';
UPDATE game_modes SET display_name = 'Chaudron cracheur' WHERE mode_key = 'soccer_10';
UPDATE game_modes SET display_name = 'Salle à pics' WHERE mode_key = 'soccer_11';
UPDATE game_modes SET display_name = 'Kiwi Arena' WHERE mode_key = 'soccer_12';
UPDATE game_modes SET display_name = 'Cages inviolées' WHERE mode_key = 'soccer_13';
UPDATE game_modes SET display_name = 'Dojo Fulguro' WHERE mode_key = 'soccer_14';
UPDATE game_modes SET display_name = 'Terrain aléatoire' WHERE mode_key = 'soccer_alea';
UPDATE game_modes SET display_name = 'Aventure' WHERE mode_key = 'solo';
UPDATE game_modes SET display_name = 'Time Attack' WHERE mode_key = 'timeattack';
UPDATE game_modes SET display_name = 'Apprentissage' WHERE mode_key = 'tutorial';
-- noinspection SqlWithoutWhere
UPDATE game_options SET main_locale  = 'fr-FR', display_name = option_key;
UPDATE game_options SET display_name = 'Bombotopia' WHERE option_key = 'bombcontrol';
UPDATE game_options SET display_name = 'Explosifs Instables' WHERE option_key = 'bombexpert';
UPDATE game_options SET display_name = 'Tornade' WHERE option_key = 'boost';
UPDATE game_options SET display_name = 'Âge de Glace' WHERE option_key = 'ice';
UPDATE game_options SET display_name = 'Intuition' WHERE option_key = 'insight';
UPDATE game_options SET display_name = 'Contrôle avancé du ballon' WHERE option_key = 'kickcontrol';
UPDATE game_options SET display_name = 'Lave' WHERE option_key = 'lava';
UPDATE game_options SET display_name = 'Partage de vies' WHERE option_key = 'lifesharing';
UPDATE game_options SET display_name = 'Miroir' WHERE option_key = 'mirror';
UPDATE game_options SET display_name = 'Miroir' WHERE option_key = 'mirrormulti';
UPDATE game_options SET display_name = 'Cauchemar' WHERE option_key = 'nightmare';
UPDATE game_options SET display_name = 'Cauchemar' WHERE option_key = 'nightmaremulti';
UPDATE game_options SET display_name = 'Ninjutsu' WHERE option_key = 'ninja';
UPDATE game_options SET display_name = 'Nojutsu' WHERE option_key = 'nojutsu';
UPDATE game_options SET display_name = 'Randomfest' WHERE option_key = 'randomfest';
UPDATE game_options SET display_name = 'Bombes' WHERE option_key = 'soccerbomb';
UPDATE game_options SET display_name = 'Durée de match courte' WHERE option_key = 'soccer_short';
UPDATE game_options SET display_name = 'Durée de match normale' WHERE option_key = 'soccer_normal';
UPDATE game_options SET display_name = 'Durée de match longue' WHERE option_key = 'soccer_long';

ALTER TABLE games
  ALTER COLUMN main_locale SET NOT NULL,
  ADD CONSTRAINT game_locale__uniq UNIQUE(game_id, main_locale);
ALTER TABLE game_modes
  ALTER COLUMN main_locale SET NOT NULL,
  ALTER COLUMN display_name SET NOT NULL,
  ADD CONSTRAINT game_mode_locale__uniq UNIQUE(game_id, mode_key, main_locale);
ALTER TABLE game_options
  ALTER COLUMN main_locale SET NOT NULL,
  ALTER COLUMN display_name SET NOT NULL,
  ADD CONSTRAINT game_option_locale__uniq UNIQUE(game_id, mode_key, option_key, main_locale);

ALTER TABLE game_modes
  DROP CONSTRAINT game_mode_game__fk;
ALTER TABLE game_modes
  ADD CONSTRAINT game_mode_game__fk FOREIGN KEY (game_id, main_locale) REFERENCES games (game_id, main_locale) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE game_options
  DROP CONSTRAINT game_option_game_mode__fk;
ALTER TABLE game_options
  ADD 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;

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));