-- Tournaments: scheduled events with an entry fee, a prize pool, and a
-- leaderboard.
--
-- The prize pool is a real ledger account, not a number in a row. Entry fees
-- move into it and prizes move out of it, so a tournament's money is subject to
-- the same double-entry invariants as everything else and cannot be
-- accidentally created or lost.

CREATE TYPE tournament_status AS ENUM
    ('scheduled', 'registering', 'running', 'settled', 'cancelled');

CREATE TABLE tournaments (
    id             BIGSERIAL PRIMARY KEY,
    name           TEXT NOT NULL,
    game           TEXT NOT NULL,
    status         tournament_status NOT NULL DEFAULT 'scheduled',
    entry_fee_msat BIGINT NOT NULL CHECK (entry_fee_msat >= 0),
    -- The ledger account holding this tournament's pool.
    pool_account_id BIGINT NOT NULL REFERENCES accounts(id),
    -- Prize split as basis points per finishing position, highest first.
    -- e.g. {5000,3000,2000} pays 50/30/20 to the top three.
    payout_bp      INTEGER[] NOT NULL,
    max_entrants   INTEGER,
    registers_at   TIMESTAMPTZ NOT NULL,
    starts_at      TIMESTAMPTZ NOT NULL,
    ends_at        TIMESTAMPTZ NOT NULL,
    settled_at     TIMESTAMPTZ,
    created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT tournament_window CHECK (registers_at <= starts_at AND starts_at < ends_at)
);

CREATE INDEX tournaments_status_idx ON tournaments (status, starts_at);

CREATE TABLE tournament_entries (
    id            BIGSERIAL PRIMARY KEY,
    tournament_id BIGINT NOT NULL REFERENCES tournaments(id),
    account_id    BIGINT NOT NULL REFERENCES accounts(id),
    -- Score is net profit in millisatoshis across the tournament window.
    -- It may be negative; a losing player still has a standing.
    score_msat    BIGINT NOT NULL DEFAULT 0,
    rounds_played INTEGER NOT NULL DEFAULT 0,
    prize_msat    BIGINT NOT NULL DEFAULT 0 CHECK (prize_msat >= 0),
    entered_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (tournament_id, account_id)
);

CREATE INDEX tournament_entries_board_idx
    ON tournament_entries (tournament_id, score_msat DESC);

-- Deliberately NOT append-only. An entry row is a seat reservation; a seat
-- claimed but not paid for must be releasable so the player can retry once
-- funded. The money side is a ledger posting and remains immutable.