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

-- The list of official servers
CREATE TYPE HFEST_SERVER AS ENUM ('fr', 'en', 'es');
CREATE TYPE IDENTITY_TYPE AS ENUM ('hfest', 'ef');

-- The list of itens in Hammerfest (official game)
CREATE TABLE public.hfest_items (
  hfest_item_id INT PRIMARY KEY 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,
  CHECK (updated_at >= created_at)
);

-- 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,
  user_id     UUID                     NOT NULL,
  CONSTRAINT identity_user___fk FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CHECK (updated_at >= created_at)
);

-- 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,
  CONSTRAINT hfest_identity_identity___fk FOREIGN KEY (identity_id) REFERENCES identities (identity_id) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE (server, hfest_id),
  UNIQUE (server, username)
);

-- The relation between the hfest_items and identities
CREATE TABLE public.inventories (
  identity_id          UUID NOT NULL,
  hfest_item_id        INT  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,
  -- 0: second precision
  expiration_date   TIMESTAMP(0) WITH TIME ZONE NOT NULL,
  user_id           UUID,
  data              JSON                        NOT NULL,
  CONSTRAINT session_user___fk FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE public.hfest_progressions (
  hfest_progression_id        UUID    PRIMARY KEY         NOT NULL,
  created_at                  TIMESTAMP(0) WITH TIME ZONE NOT NULL
);