-- Interview Arena schema — public bot capability testing platform
-- 5 tables: exams, sessions, leaderboard, feedback, comments

-- ============================================
-- arena_exams — one row per exam (12 test sessions)
-- ============================================
CREATE TABLE IF NOT EXISTS arena_exams (
    id VARCHAR(48) PRIMARY KEY DEFAULT ('exam_' || encode(gen_random_bytes(12), 'hex')),
    exam_token VARCHAR(32) NOT NULL UNIQUE,
    interview_id UUID,
    listing_id VARCHAR(48),
    model VARCHAR(64),
    status VARCHAR(16) NOT NULL DEFAULT 'waiting',
    total_score INTEGER DEFAULT NULL,
    max_score INTEGER DEFAULT 147,
    report JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_arena_exam_token ON arena_exams(exam_token);
CREATE INDEX IF NOT EXISTS idx_arena_exam_status ON arena_exams(status);

-- ============================================
-- arena_sessions — one row per test within an exam
-- ============================================
CREATE TABLE IF NOT EXISTS arena_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    exam_id VARCHAR(48) NOT NULL REFERENCES arena_exams(id) ON DELETE CASCADE,
    session_token VARCHAR(32) NOT NULL UNIQUE,
    test_type VARCHAR(32) NOT NULL,
    test_index INTEGER NOT NULL,
    challenge_config JSONB NOT NULL,
    status VARCHAR(16) NOT NULL DEFAULT 'pending',
    score INTEGER DEFAULT NULL,
    max_score INTEGER NOT NULL DEFAULT 0,
    raw_result JSONB DEFAULT '{}'::jsonb,
    actions_log JSONB DEFAULT '[]'::jsonb,
    started_at TIMESTAMP WITH TIME ZONE,
    completed_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_arena_session_token ON arena_sessions(session_token);
CREATE INDEX IF NOT EXISTS idx_arena_session_exam ON arena_sessions(exam_id);

-- ============================================
-- arena_leaderboard — public ranking
-- ============================================
CREATE TABLE IF NOT EXISTS arena_leaderboard (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    exam_id VARCHAR(48) NOT NULL REFERENCES arena_exams(id) ON DELETE CASCADE,
    name VARCHAR(64) NOT NULL,
    model VARCHAR(64),
    score INTEGER NOT NULL,
    max_score INTEGER NOT NULL DEFAULT 147,
    detail JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_arena_lb_score ON arena_leaderboard(score DESC);

-- ============================================
-- arena_feedback — user feedback after exam
-- ============================================
CREATE TABLE IF NOT EXISTS arena_feedback (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    exam_id VARCHAR(48) REFERENCES arena_exams(id) ON DELETE SET NULL,
    desired_capabilities JSONB DEFAULT '[]'::jsonb,
    credibility_score INTEGER CHECK (credibility_score BETWEEN 0 AND 10),
    comment TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_arena_feedback_created ON arena_feedback(created_at DESC);

-- ============================================
-- arena_comments — public message board
-- ============================================
CREATE TABLE IF NOT EXISTS arena_comments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    nickname VARCHAR(64) NOT NULL,
    text TEXT NOT NULL,
    exam_id VARCHAR(48) REFERENCES arena_exams(id) ON DELETE SET NULL,
    score INTEGER,
    model VARCHAR(64),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_arena_comments_created ON arena_comments(created_at DESC);

-- ============================================
-- ID prefix migration (idempotent; no-ops after first run)
-- Widen arena_exams.id UUID → VARCHAR(48) so Stripe-style prefixed IDs
-- (exam_<24hex>) can coexist with legacy UUIDs.
-- FK constraint names are auto-generated by PostgreSQL: <table>_<column>_fkey.
-- arena_feedback and arena_comments use ON DELETE SET NULL; others CASCADE.
-- ============================================
ALTER TABLE arena_sessions DROP CONSTRAINT IF EXISTS arena_sessions_exam_id_fkey;
ALTER TABLE arena_leaderboard DROP CONSTRAINT IF EXISTS arena_leaderboard_exam_id_fkey;
ALTER TABLE arena_feedback DROP CONSTRAINT IF EXISTS arena_feedback_exam_id_fkey;
ALTER TABLE arena_comments DROP CONSTRAINT IF EXISTS arena_comments_exam_id_fkey;
ALTER TABLE arena_exams ALTER COLUMN id DROP DEFAULT;
ALTER TABLE arena_exams ALTER COLUMN id TYPE VARCHAR(48) USING id::text;
ALTER TABLE arena_exams ALTER COLUMN id SET DEFAULT ('exam_' || encode(gen_random_bytes(12), 'hex'));
ALTER TABLE arena_exams ALTER COLUMN listing_id TYPE VARCHAR(48) USING listing_id::text;
ALTER TABLE arena_sessions ALTER COLUMN exam_id TYPE VARCHAR(48) USING exam_id::text;
ALTER TABLE arena_leaderboard ALTER COLUMN exam_id TYPE VARCHAR(48) USING exam_id::text;
ALTER TABLE arena_feedback ALTER COLUMN exam_id TYPE VARCHAR(48) USING exam_id::text;
ALTER TABLE arena_comments ALTER COLUMN exam_id TYPE VARCHAR(48) USING exam_id::text;
ALTER TABLE arena_sessions ADD CONSTRAINT arena_sessions_exam_id_fkey FOREIGN KEY (exam_id) REFERENCES arena_exams(id) ON DELETE CASCADE;
ALTER TABLE arena_leaderboard ADD CONSTRAINT arena_leaderboard_exam_id_fkey FOREIGN KEY (exam_id) REFERENCES arena_exams(id) ON DELETE CASCADE;
ALTER TABLE arena_feedback ADD CONSTRAINT arena_feedback_exam_id_fkey FOREIGN KEY (exam_id) REFERENCES arena_exams(id) ON DELETE SET NULL;
ALTER TABLE arena_comments ADD CONSTRAINT arena_comments_exam_id_fkey FOREIGN KEY (exam_id) REFERENCES arena_exams(id) ON DELETE SET NULL;

-- Track when the bot first fetches the exam (GET /arena/test). The 3-min
-- TTL should measure from the bot's first fetch, not exam creation — the
-- user may delay copy-pasting the URL and the bot itself may have pickup
-- latency. Populated idempotently on first GET.
ALTER TABLE arena_exams ADD COLUMN IF NOT EXISTS first_fetched_at TIMESTAMP WITH TIME ZONE;

-- Exam-level completion timestamp. Needed to compute an accurate total
-- elapsed time for the leaderboard that isn't polluted by the user
-- typing their display name after finalize.
ALTER TABLE arena_exams ADD COLUMN IF NOT EXISTS completed_at TIMESTAMP WITH TIME ZONE;

-- Allow expires_at to be NULL at exam creation. The 3-min TTL is armed
-- atomically by GET /arena/test/:id (see backend/index.js), so the
-- countdown measures from the bot's first fetch rather than from the
-- channel-delivery-delayed INSERT. NULL = "clock not yet armed".
ALTER TABLE arena_exams ALTER COLUMN expires_at DROP NOT NULL;
