schema.sql

-- Prode schema (Cloudflare D1 / SQLite)

CREATE TABLE IF NOT EXISTS players (
  id             INTEGER PRIMARY KEY AUTOINCREMENT,
  name           TEXT    NOT NULL,          -- display name (free text, may repeat)
  username       TEXT    NOT NULL,          -- login handle
  username_lower TEXT    NOT NULL UNIQUE,   -- case-insensitive uniqueness
  email          TEXT,                      -- optional
  phone          TEXT,                      -- optional
  pass_hash      TEXT    NOT NULL,          -- PBKDF2-SHA256, hex
  salt           TEXT    NOT NULL,          -- hex
  status         TEXT    NOT NULL DEFAULT 'pending',  -- pending | approved | rejected
  is_admin       INTEGER NOT NULL DEFAULT 0,
  created_at     TEXT    NOT NULL,                    -- when access was requested
  approved_at    TEXT                                 -- when it was approved (null while pending)
);

CREATE TABLE IF NOT EXISTS picks (
  player_id  INTEGER NOT NULL,
  matchday   TEXT    NOT NULL,            -- 'f1', 'f2', ...
  match_n    INTEGER NOT NULL,
  home       INTEGER NOT NULL,
  away       INTEGER NOT NULL,
  updated_at TEXT    NOT NULL,
  PRIMARY KEY (player_id, matchday, match_n),
  FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE
);

-- Tournament-long prediction (final four, champion and individual awards). Entered once.
-- slot: 'champ' | 'runnerup' | 'third' | 'fourth' | 'scorer' | 'gloves' | 'ballon'
CREATE TABLE IF NOT EXISTS outrights (
  player_id  INTEGER NOT NULL,
  slot       TEXT    NOT NULL,
  value      TEXT    NOT NULL,            -- team or player name
  updated_at TEXT    NOT NULL,
  PRIMARY KEY (player_id, slot),
  FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE
);
-- Official tournament results (entered by the admin at the end, to score the outrights).
CREATE TABLE IF NOT EXISTS outright_results (
  slot  TEXT PRIMARY KEY,
  value TEXT NOT NULL
);

-- Reminders already sent (one email per player per matchday, about 24 h before the first match).
-- Prevents resends: a row is written only when the email went out successfully.
CREATE TABLE IF NOT EXISTS reminders (
  player_id INTEGER NOT NULL,
  matchday  TEXT    NOT NULL,
  sent_at   TEXT    NOT NULL,
  PRIMARY KEY (player_id, matchday),
  FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_picks_matchday ON picks (matchday);
CREATE INDEX IF NOT EXISTS idx_players_status ON players (status);
-- email and phone are unique when present (both work as login identifiers)
CREATE UNIQUE INDEX IF NOT EXISTS idx_players_email ON players (email) WHERE email IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_players_phone ON players (phone) WHERE phone IS NOT NULL;