-- Requires pgcrypto for gen_random_uuid(); UUIDs are v7 when the app generates
-- them (see internal/model), but the DB default falls back to v4 for rows
-- inserted outside the app (e.g. manual SQL).
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS citext;

-- change_seq is a single global sequence shared by platform and game so a
-- client can ask "what changed since N?" across both tables in one pass.
CREATE SEQUENCE change_seq_seq;

CREATE TABLE users (
  id            uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
  email         citext      NOT NULL UNIQUE,
  password_hash text        NOT NULL,
  name          text        NOT NULL,
  bio           text,
  -- Only admins may add/rename/delete from the shared platform list (see
  -- the platform table below); everyone can read it. The first account
  -- ever registered becomes an admin automatically (see CreateUser in
  -- db/query.sql) so there's always at least one without manual setup;
  -- promoting anyone beyond that is a manual
  -- `UPDATE users SET is_admin = true WHERE email = ...` for now.
  is_admin      boolean     NOT NULL DEFAULT false,
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE sessions (
  token_hash   bytea       PRIMARY KEY,
  user_id      uuid        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at   timestamptz NOT NULL DEFAULT now(),
  expires_at   timestamptz NOT NULL,
  last_seen_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_sessions_user_id ON sessions(user_id);

-- Platforms are shared: every user picks from and adds to the same list,
-- unlike games, which are private per user_id.
CREATE TABLE platform (
  id         uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
  name       text        NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz,
  change_seq bigint      NOT NULL DEFAULT nextval('change_seq_seq'),
  UNIQUE (name)
);

CREATE INDEX idx_platform_change_seq ON platform(change_seq);

CREATE TABLE game (
  id           uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id      uuid        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  platform_id  uuid        NOT NULL REFERENCES platform(id),
  name         text        NOT NULL,
  score        int         NOT NULL CHECK (score BETWEEN 0 AND 100),
  release_date date        NOT NULL,
  finished     date,
  updated_at   timestamptz NOT NULL DEFAULT now(),
  deleted_at   timestamptz,
  change_seq   bigint      NOT NULL DEFAULT nextval('change_seq_seq')
);

CREATE INDEX idx_game_user_change_seq ON game(user_id, change_seq);

-- Keep updated_at/change_seq current on every UPDATE so sync can detect
-- changes without clients having to set them manually.
CREATE OR REPLACE FUNCTION touch_change_seq() RETURNS trigger AS $$
BEGIN
  NEW.updated_at := now();
  NEW.change_seq := nextval('change_seq_seq');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER platform_touch_change_seq
  BEFORE UPDATE ON platform
  FOR EACH ROW EXECUTE FUNCTION touch_change_seq();

CREATE TRIGGER game_touch_change_seq
  BEFORE UPDATE ON game
  FOR EACH ROW EXECUTE FUNCTION touch_change_seq();