-- ============================================================
--  HEAD OR TAIL — Central (Contabo) deployment schema
--  PostgreSQL. Stage 1 foundation.
--
--  Money chain:  admin -> agent -> cashier -> player_session
--  Every money move is mirrored in the ledger (dual-entry).
-- ============================================================

-- ---------- ADMINS (1-2 co-equal super admins) ----------
CREATE TABLE IF NOT EXISTS admins (
    id            TEXT PRIMARY KEY,
    username      TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    balance       NUMERIC(16,2) NOT NULL DEFAULT 0,
    phone         TEXT DEFAULT '',
    active        BOOLEAN NOT NULL DEFAULT TRUE,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------- AGENTS (created & funded by an admin) ----------
CREATE TABLE IF NOT EXISTS agents (
    id            TEXT PRIMARY KEY,
    username      TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    balance       NUMERIC(16,2) NOT NULL DEFAULT 0,
    phone         TEXT DEFAULT '',
    admin_id      TEXT NOT NULL REFERENCES admins(id),
    active        BOOLEAN NOT NULL DEFAULT TRUE,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------- CASHIERS (always belong to exactly one agent) ----------
--  real_balance  = cash float (drawer), cashable
--  agent_id      = current owner (NOT NULL -> never unassigned)
CREATE TABLE IF NOT EXISTS cashiers (
    id            TEXT PRIMARY KEY,
    username      TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    real_balance  NUMERIC(16,2) NOT NULL DEFAULT 0,
    agent_id      TEXT NOT NULL REFERENCES agents(id),
    shop_name     TEXT DEFAULT '',
    phone         TEXT DEFAULT '',
    active        BOOLEAN NOT NULL DEFAULT TRUE,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------- TERMINALS (long-lived token tying a device to a cashier) ----------
CREATE TABLE IF NOT EXISTS terminals (
    token       TEXT PRIMARY KEY,
    cashier_id  TEXT NOT NULL REFERENCES cashiers(id),
    active      BOOLEAN NOT NULL DEFAULT TRUE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------- ACTIVATION CODES (agent issues, cashier activates a terminal) ----------
CREATE TABLE IF NOT EXISTS activation_codes (
    id          BIGSERIAL PRIMARY KEY,
    code_hash   TEXT NOT NULL,
    cashier_id  TEXT NOT NULL REFERENCES cashiers(id),
    agent_id    TEXT NOT NULL REFERENCES agents(id),
    used        BOOLEAN NOT NULL DEFAULT FALSE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------- PLAYER SESSIONS (a customer's running balance at a counter) ----------
--  real_balance      = cash funded by cashier, cashable any time
--  fund_balance      = bonus/cashback ("Fund Bet"), wager-locked
--  fund_rollover_rem = remaining 1x wagering required before fund-derived value clears
CREATE TABLE IF NOT EXISTS player_sessions (
    id                TEXT PRIMARY KEY,           -- player/slip code, also used in barcodes
    cashier_id        TEXT NOT NULL REFERENCES cashiers(id),
    real_balance      NUMERIC(16,2) NOT NULL DEFAULT 0,
    fund_balance      NUMERIC(16,2) NOT NULL DEFAULT 0,
    fund_rollover_rem NUMERIC(16,2) NOT NULL DEFAULT 0,
    active            BOOLEAN NOT NULL DEFAULT TRUE,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------- ROUNDS (one shared round across all shops) ----------
CREATE TABLE IF NOT EXISTS rounds (
    round_no        BIGINT PRIMARY KEY,
    status          TEXT NOT NULL DEFAULT 'betting',  -- betting|locked|revealing|settled
    result_sequence TEXT DEFAULT '',                  -- e.g. "HEADS TAILS HEADS ..."
    total_pool      NUMERIC(16,2) NOT NULL DEFAULT 0,
    total_payout    NUMERIC(16,2) NOT NULL DEFAULT 0, -- wins + near-miss + cashback (after scale)
    house_cut       NUMERIC(16,2) NOT NULL DEFAULT 0, -- 10%
    jackpot_cut     NUMERIC(16,2) NOT NULL DEFAULT 0, -- 5%
    scale_applied   NUMERIC(8,5)  NOT NULL DEFAULT 1, -- <1 only in oversubscribed rounds
    ticket_count    INTEGER NOT NULL DEFAULT 0,
    winner_count    INTEGER NOT NULL DEFAULT 0,
    started_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    settled_at      TIMESTAMPTZ
);

-- ---------- BETS / TICKETS ----------
--  picks      = JSON array [{"coin":1,"pick":"HEADS"}, ...]
--  group_key  = session_id || ':' || round_no  (used for >=5 clean-sweep cashback)
--  status     = pending|win|near_miss|loss|cancelled
CREATE TABLE IF NOT EXISTS bets (
    ticket_id    TEXT PRIMARY KEY,
    round_no     BIGINT NOT NULL REFERENCES rounds(round_no),
    session_id   TEXT REFERENCES player_sessions(id),  -- NULL for single-step cash bets
    cashier_id   TEXT NOT NULL REFERENCES cashiers(id),
    agent_id     TEXT NOT NULL,            -- owning agent AT TIME OF BET (history pins here)
    picks        JSONB NOT NULL,
    size         INTEGER NOT NULL,         -- number of coins selected (1..6)
    stake        NUMERIC(16,2) NOT NULL,
    fund_used    NUMERIC(16,2) NOT NULL DEFAULT 0,
    real_used    NUMERIC(16,2) NOT NULL DEFAULT 0,
    multiplier   NUMERIC(10,2) NOT NULL,   -- live multiplier snapshot at bet time
    group_key    TEXT NOT NULL,
    status       TEXT NOT NULL DEFAULT 'pending',
    win_amount   NUMERIC(16,2) NOT NULL DEFAULT 0,
    is_winner    BOOLEAN NOT NULL DEFAULT FALSE,
    near_miss    BOOLEAN NOT NULL DEFAULT FALSE,
    redeemed     BOOLEAN NOT NULL DEFAULT FALSE,
    redeemed_at  TIMESTAMPTZ,
    redeemed_by  TEXT,                      -- cashier who paid the ticket out
    cancelled    BOOLEAN NOT NULL DEFAULT FALSE,
    cancelled_at TIMESTAMPTZ,
    barcode      TEXT NOT NULL,
    hash         TEXT NOT NULL,            -- SHA-256 tamper hash
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_bets_round   ON bets(round_no);
CREATE INDEX IF NOT EXISTS idx_bets_cashier ON bets(cashier_id);
CREATE INDEX IF NOT EXISTS idx_bets_group   ON bets(group_key);
CREATE INDEX IF NOT EXISTS idx_bets_barcode ON bets(barcode);

-- ---------- JACKPOT (single accumulating pot) ----------
CREATE TABLE IF NOT EXISTS jackpot (
    id      INTEGER PRIMARY KEY DEFAULT 1,
    amount  NUMERIC(16,2) NOT NULL DEFAULT 0,
    CHECK (id = 1)
);
INSERT INTO jackpot (id, amount) VALUES (1, 0) ON CONFLICT (id) DO NOTHING;

-- ---------- HOUSE (single accumulating profit account) ----------
CREATE TABLE IF NOT EXISTS house (
    id      INTEGER PRIMARY KEY DEFAULT 1,
    profit  NUMERIC(16,2) NOT NULL DEFAULT 0,
    CHECK (id = 1)
);
INSERT INTO house (id, profit) VALUES (1, 0) ON CONFLICT (id) DO NOTHING;

-- ---------- PROMO POOL (funds cashback reimbursement; fed from jackpot/house config) ----------
CREATE TABLE IF NOT EXISTS promo_pool (
    id      INTEGER PRIMARY KEY DEFAULT 1,
    balance NUMERIC(16,2) NOT NULL DEFAULT 0,
    CHECK (id = 1)
);
INSERT INTO promo_pool (id, balance) VALUES (1, 0) ON CONFLICT (id) DO NOTHING;

-- ---------- LEDGER (dual-entry; every money move recorded) ----------
--  Each row = one transfer: amount moves FROM (debit) TO (credit).
--  account_type: admin|agent|cashier|player|pool_round|pool_jackpot|pool_house|pool_promo
CREATE TABLE IF NOT EXISTS ledger (
    id            BIGSERIAL PRIMARY KEY,
    txn_type      TEXT NOT NULL,   -- topup_agent|topup_cashier|fund_player|bet|win|near_miss|cashback|reimburse|jackpot_award|house_take|jackpot_take|reassign_transfer
    debit_type    TEXT NOT NULL,
    debit_id      TEXT,
    credit_type   TEXT NOT NULL,
    credit_id     TEXT,
    amount        NUMERIC(16,2) NOT NULL,
    round_no      BIGINT,
    ref           TEXT DEFAULT '',
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_ledger_round ON ledger(round_no);
CREATE INDEX IF NOT EXISTS idx_ledger_type  ON ledger(txn_type);

-- ---------- SHIFTS ----------
CREATE TABLE IF NOT EXISTS shifts (
    id              BIGSERIAL PRIMARY KEY,
    cashier_id      TEXT NOT NULL REFERENCES cashiers(id),
    agent_id        TEXT NOT NULL,         -- owner at shift open
    start_balance   NUMERIC(16,2) NOT NULL,
    start_time      TIMESTAMPTZ NOT NULL DEFAULT now(),
    end_balance     NUMERIC(16,2),
    end_time        TIMESTAMPTZ,
    status          TEXT NOT NULL DEFAULT 'open'  -- open|closed
);
CREATE INDEX IF NOT EXISTS idx_shifts_cashier ON shifts(cashier_id);

-- ---------- CASHIER REASSIGNMENTS (admin moves cashier between agents) ----------
CREATE TABLE IF NOT EXISTS cashier_reassignments (
    id              BIGSERIAL PRIMARY KEY,
    cashier_id      TEXT NOT NULL REFERENCES cashiers(id),
    from_agent_id   TEXT NOT NULL,
    to_agent_id     TEXT NOT NULL,
    balance_at_move NUMERIC(16,2) NOT NULL,  -- float that travelled with the cashier
    admin_id        TEXT NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------- GAME STATE PERSIST (resume round number across restarts) ----------
CREATE TABLE IF NOT EXISTS game_state (
    id          INTEGER PRIMARY KEY DEFAULT 1,
    last_round  BIGINT NOT NULL DEFAULT 1000,
    CHECK (id = 1)
);
INSERT INTO game_state (id, last_round) VALUES (1, 1000) ON CONFLICT (id) DO NOTHING;
