-- ============================================================
-- 10_create_progress.sql
-- Tabel progress ibadah: streak, checklist, bacaan, hafalan, dzikir
-- Menggantikan localStorage keys:
--   streak, checklist_*, lastRead, gameXP, gameLevel
-- ============================================================

-- ── USER STREAKS ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS user_streaks (
    id              SERIAL PRIMARY KEY,
    user_id         UUID UNIQUE NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    current_streak  INTEGER DEFAULT 0,           -- Streak hari berturut-turut
    longest_streak  INTEGER DEFAULT 0,           -- Streak terpanjang sepanjang masa
    last_active     DATE,                        -- Tanggal terakhir aktif
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TRIGGER user_streaks_updated_at
    BEFORE UPDATE ON user_streaks
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- ── DAILY CHECKLIST ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS daily_checklist (
    id              SERIAL PRIMARY KEY,
    user_id         UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    date            DATE NOT NULL DEFAULT CURRENT_DATE,
    quran           BOOLEAN DEFAULT FALSE,       -- Baca Quran ✅
    doa             BOOLEAN DEFAULT FALSE,       -- Baca Doa ✅
    dzikir          BOOLEAN DEFAULT FALSE,       -- Dzikir ✅
    hadits          BOOLEAN DEFAULT FALSE,       -- Baca Hadits ✅
    sholat          BOOLEAN DEFAULT FALSE,       -- Sholat 5 waktu ✅
    created_at      TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(user_id, date)
);

-- ── READING PROGRESS ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS reading_progress (
    id              SERIAL PRIMARY KEY,
    user_id         UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    surah_number    INTEGER NOT NULL,            -- Surah terakhir dibaca
    ayah_number     INTEGER NOT NULL DEFAULT 1,  -- Ayat terakhir dibaca
    last_read_at    TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(user_id)                              -- Hanya 1 record per user (last position)
);

-- ── HAFALAN PROGRESS ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS hafalan_progress (
    id              SERIAL PRIMARY KEY,
    user_id         UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    surah_number    INTEGER NOT NULL,
    status          TEXT DEFAULT 'not_started',   -- not_started, in_progress, memorized
    updated_at      TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(user_id, surah_number)
);

-- ── DZIKIR PROGRESS ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS dzikir_progress (
    id              SERIAL PRIMARY KEY,
    user_id         UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    date            DATE NOT NULL DEFAULT CURRENT_DATE,
    type            TEXT NOT NULL,                -- pagi, sore, solat
    count           INTEGER DEFAULT 0,           -- Jumlah dzikir selesai
    completed       BOOLEAN DEFAULT FALSE,       -- Semua dzikir selesai
    created_at      TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(user_id, date, type)
);

-- ── INDEXES ───────────────────────────────────────────────────
CREATE INDEX IF NOT EXISTS idx_streaks_user ON user_streaks(user_id);
CREATE INDEX IF NOT EXISTS idx_checklist_user_date ON daily_checklist(user_id, date);
CREATE INDEX IF NOT EXISTS idx_reading_user ON reading_progress(user_id);
CREATE INDEX IF NOT EXISTS idx_hafalan_user ON hafalan_progress(user_id);
CREATE INDEX IF NOT EXISTS idx_dzikir_user_date ON dzikir_progress(user_id, date);

-- ── RLS POLICIES ──────────────────────────────────────────────
ALTER TABLE user_streaks ENABLE ROW LEVEL SECURITY;
ALTER TABLE daily_checklist ENABLE ROW LEVEL SECURITY;
ALTER TABLE reading_progress ENABLE ROW LEVEL SECURITY;
ALTER TABLE hafalan_progress ENABLE ROW LEVEL SECURITY;
ALTER TABLE dzikir_progress ENABLE ROW LEVEL SECURITY;

-- Streaks
CREATE POLICY "streaks_read_own" ON user_streaks
    FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "streaks_upsert_own" ON user_streaks
    FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "streaks_update_own" ON user_streaks
    FOR UPDATE USING (auth.uid() = user_id);

-- Daily Checklist
CREATE POLICY "checklist_read_own" ON daily_checklist
    FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "checklist_upsert_own" ON daily_checklist
    FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "checklist_update_own" ON daily_checklist
    FOR UPDATE USING (auth.uid() = user_id);

-- Reading Progress
CREATE POLICY "reading_read_own" ON reading_progress
    FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "reading_upsert_own" ON reading_progress
    FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "reading_update_own" ON reading_progress
    FOR UPDATE USING (auth.uid() = user_id);

-- Hafalan
CREATE POLICY "hafalan_read_own" ON hafalan_progress
    FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "hafalan_upsert_own" ON hafalan_progress
    FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "hafalan_update_own" ON hafalan_progress
    FOR UPDATE USING (auth.uid() = user_id);

-- Dzikir
CREATE POLICY "dzikir_read_own" ON dzikir_progress
    FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "dzikir_upsert_own" ON dzikir_progress
    FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "dzikir_update_own" ON dzikir_progress
    FOR UPDATE USING (auth.uid() = user_id);