CREATE TABLE IF NOT EXISTS sessions (
    id                 TEXT PRIMARY KEY,
    player_id          TEXT NOT NULL REFERENCES players(id),
    status             TEXT NOT NULL DEFAULT 'active',
    score              INTEGER NOT NULL DEFAULT 0,
    questions_answered INTEGER NOT NULL DEFAULT 0,
    correct_answers    INTEGER NOT NULL DEFAULT 0,
    started_at         TEXT NOT NULL DEFAULT (datetime('now')),
    ended_at           TEXT
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_sessions_active_player
    ON sessions(player_id) WHERE status = 'active';