-- ============================================================
-- 11_create_game_scores.sql
-- Tabel game: XP, sesi, leaderboard, quiz questions
-- ============================================================

-- ── USER XP & LEVEL ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS user_xp (
    id              SERIAL PRIMARY KEY,
    user_id         UUID UNIQUE NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    total_xp        INTEGER DEFAULT 0,           -- Total XP
    level           INTEGER DEFAULT 1,           -- Level saat ini
    level_name      TEXT DEFAULT 'Pemula',       -- Pemula → Santri → Hafiz → Ulama
    total_correct   INTEGER DEFAULT 0,           -- Total jawaban benar
    total_played    INTEGER DEFAULT 0,           -- Total soal dimainkan
    game_streak     INTEGER DEFAULT 0,           -- Streak game harian
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TRIGGER user_xp_updated_at
    BEFORE UPDATE ON user_xp
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- ── GAME SESSIONS ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS game_sessions (
    id              SERIAL PRIMARY KEY,
    user_id         UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    mode            TEXT NOT NULL,                -- tebak-ayat, tebak-surah, urut-ayat, dll
    category        TEXT,                         -- Kategori soal (juz, surah)
    juz             INTEGER,                     -- Juz yang dipilih (jika ada)
    score           INTEGER DEFAULT 0,           -- Skor sesi ini
    correct         INTEGER DEFAULT 0,           -- Jawaban benar
    wrong           INTEGER DEFAULT 0,           -- Jawaban salah
    xp_earned       INTEGER DEFAULT 0,           -- XP yang didapat
    played_at       TIMESTAMPTZ DEFAULT NOW()
);

-- ── QUIZ QUESTIONS (CMS) ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS quiz_questions (
    id              SERIAL PRIMARY KEY,
    category        TEXT NOT NULL,                -- quran, islam, fiqh, dll
    question        TEXT NOT NULL,                -- Pertanyaan
    options         JSONB NOT NULL,               -- ["Opsi A","Opsi B","Opsi C","Opsi D"]
    answer          INTEGER NOT NULL,             -- Index jawaban benar (0-3)
    difficulty      TEXT DEFAULT 'medium',        -- easy, medium, hard
    explanation     TEXT,                         -- Penjelasan jawaban
    active          BOOLEAN DEFAULT TRUE,         -- Aktif / nonaktif
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

-- ── INDEXES ───────────────────────────────────────────────────
CREATE INDEX IF NOT EXISTS idx_user_xp_total ON user_xp(total_xp DESC);
CREATE INDEX IF NOT EXISTS idx_user_xp_user ON user_xp(user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_user ON game_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_mode ON game_sessions(mode);
CREATE INDEX IF NOT EXISTS idx_sessions_played ON game_sessions(played_at DESC);
CREATE INDEX IF NOT EXISTS idx_quiz_category ON quiz_questions(category);
CREATE INDEX IF NOT EXISTS idx_quiz_active ON quiz_questions(active);

-- ── RLS ───────────────────────────────────────────────────────
ALTER TABLE user_xp ENABLE ROW LEVEL SECURITY;
ALTER TABLE game_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE quiz_questions ENABLE ROW LEVEL SECURITY;

-- User XP: baca sendiri + baca semua (untuk leaderboard)
CREATE POLICY "xp_read_all" ON user_xp
    FOR SELECT USING (true);
CREATE POLICY "xp_upsert_own" ON user_xp
    FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "xp_update_own" ON user_xp
    FOR UPDATE USING (auth.uid() = user_id);

-- Game Sessions: hanya pemilik
CREATE POLICY "sessions_read_own" ON game_sessions
    FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "sessions_insert_own" ON game_sessions
    FOR INSERT WITH CHECK (auth.uid() = user_id);

-- Quiz Questions: semua boleh baca
CREATE POLICY "quiz_read_all" ON quiz_questions
    FOR SELECT USING (active = true);

-- ── LEADERBOARD VIEW ──────────────────────────────────────────
CREATE OR REPLACE VIEW leaderboard AS
SELECT 
    ux.user_id,
    p.name,
    p.avatar_initial,
    ux.total_xp,
    ux.level,
    ux.level_name,
    ux.total_correct,
    ux.total_played,
    CASE WHEN ux.total_played > 0 
        THEN ROUND((ux.total_correct::NUMERIC / ux.total_played) * 100, 1)
        ELSE 0 
    END AS accuracy,
    ROW_NUMBER() OVER (ORDER BY ux.total_xp DESC) AS rank
FROM user_xp ux
JOIN profiles p ON p.id = ux.user_id
ORDER BY ux.total_xp DESC
LIMIT 100;