-- ============================================================
-- HAYFALA QURAN — FULL DATABASE MIGRATION
-- ============================================================
-- Salin SELURUH isi file ini ke Supabase SQL Editor
-- lalu klik "Run" untuk membuat semua tabel sekaligus.
--
-- Total: 20+ tabel, 5 views, 3 triggers, RLS semua aktif
-- ============================================================


-- ════════════════════════════════════════════════════════════
-- UTILITY FUNCTIONS
-- ════════════════════════════════════════════════════════════

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


-- ════════════════════════════════════════════════════════════
-- 01. SURAHS — 114 surah Al-Quran
-- ════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS surahs (
    id              SERIAL PRIMARY KEY,
    number          INTEGER UNIQUE NOT NULL,
    name_arabic     TEXT NOT NULL,
    name_latin      TEXT NOT NULL,
    translation     TEXT NOT NULL,
    revelation      TEXT NOT NULL,
    total_verses    INTEGER NOT NULL,
    description     TEXT,
    audio_url       TEXT,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_surahs_number ON surahs(number);
CREATE INDEX IF NOT EXISTS idx_surahs_name_latin ON surahs(name_latin);
CREATE INDEX IF NOT EXISTS idx_surahs_revelation ON surahs(revelation);

ALTER TABLE surahs ENABLE ROW LEVEL SECURITY;
CREATE POLICY "surahs_read_all" ON surahs FOR SELECT USING (true);


-- ════════════════════════════════════════════════════════════
-- 02. AYAHS — 6.236 ayat
-- ════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS ayahs (
    id              SERIAL PRIMARY KEY,
    surah_number    INTEGER NOT NULL REFERENCES surahs(number),
    ayah_number     INTEGER NOT NULL,
    ayah_key        TEXT UNIQUE NOT NULL,
    arabic          TEXT NOT NULL,
    latin           TEXT,
    translation     TEXT NOT NULL,
    juz             INTEGER,
    page            INTEGER,
    manzil          INTEGER,
    ruku            INTEGER,
    audio_alafasy   TEXT,
    audio_ajamy     TEXT,
    audio_husary    TEXT,
    audio_minshawi  TEXT,
    audio_ayyoub    TEXT,
    audio_jibreel   TEXT,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(surah_number, ayah_number)
);

CREATE INDEX IF NOT EXISTS idx_ayahs_surah ON ayahs(surah_number);
CREATE INDEX IF NOT EXISTS idx_ayahs_juz ON ayahs(juz);
CREATE INDEX IF NOT EXISTS idx_ayahs_page ON ayahs(page);
CREATE INDEX IF NOT EXISTS idx_ayahs_key ON ayahs(ayah_key);
CREATE INDEX IF NOT EXISTS idx_ayahs_translation_search 
    ON ayahs USING GIN (to_tsvector('indonesian', translation));

ALTER TABLE ayahs ENABLE ROW LEVEL SECURITY;
CREATE POLICY "ayahs_read_all" ON ayahs FOR SELECT USING (true);


-- ════════════════════════════════════════════════════════════
-- 03. TAFSIRS — Tafsir per ayat (3 versi)
-- ════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS tafsirs (
    id              SERIAL PRIMARY KEY,
    ayah_key        TEXT UNIQUE NOT NULL REFERENCES ayahs(ayah_key),
    kemenag_short   TEXT,
    kemenag_long    TEXT,
    quraish         TEXT,
    jalalayn        TEXT,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_tafsirs_ayah ON tafsirs(ayah_key);

ALTER TABLE tafsirs ENABLE ROW LEVEL SECURITY;
CREATE POLICY "tafsirs_read_all" ON tafsirs FOR SELECT USING (true);


-- ════════════════════════════════════════════════════════════
-- 04. HADITHS — 9 kitab hadits
-- ════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS hadiths (
    id              SERIAL PRIMARY KEY,
    book            TEXT NOT NULL,
    hadith_number   INTEGER NOT NULL,
    arabic          TEXT,
    translation     TEXT NOT NULL,
    narrator        TEXT,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(book, hadith_number)
);

CREATE INDEX IF NOT EXISTS idx_hadiths_book ON hadiths(book);
CREATE INDEX IF NOT EXISTS idx_hadiths_book_number ON hadiths(book, hadith_number);
CREATE INDEX IF NOT EXISTS idx_hadiths_translation_search 
    ON hadiths USING GIN (to_tsvector('indonesian', translation));

ALTER TABLE hadiths ENABLE ROW LEVEL SECURITY;
CREATE POLICY "hadiths_read_all" ON hadiths FOR SELECT USING (true);


-- ════════════════════════════════════════════════════════════
-- 05. DUAS — Doa harian
-- ════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS duas (
    id              SERIAL PRIMARY KEY,
    source_id       TEXT,
    title           TEXT NOT NULL,
    arabic          TEXT NOT NULL,
    translation     TEXT NOT NULL,
    source          TEXT,
    notes           TEXT,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_duas_source ON duas(source);
CREATE INDEX IF NOT EXISTS idx_duas_title_search 
    ON duas USING GIN (to_tsvector('indonesian', title));

ALTER TABLE duas ENABLE ROW LEVEL SECURITY;
CREATE POLICY "duas_read_all" ON duas FOR SELECT USING (true);


-- ════════════════════════════════════════════════════════════
-- 06. ADHKAR — Dzikir (pagi/sore/solat)
-- ════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS adhkar (
    id              SERIAL PRIMARY KEY,
    source_id       TEXT,
    arabic          TEXT NOT NULL,
    translation     TEXT NOT NULL,
    type            TEXT NOT NULL,
    repeat_count    TEXT,
    notes           TEXT,
    sort_order      INTEGER DEFAULT 0,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_adhkar_type ON adhkar(type);

ALTER TABLE adhkar ENABLE ROW LEVEL SECURITY;
CREATE POLICY "adhkar_read_all" ON adhkar FOR SELECT USING (true);


-- ════════════════════════════════════════════════════════════
-- 07. ASMAUL HUSNA — 99 nama Allah
-- ════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS asmaul_husna (
    id              SERIAL PRIMARY KEY,
    number          INTEGER UNIQUE NOT NULL,
    arabic          TEXT NOT NULL,
    latin           TEXT NOT NULL,
    translation     TEXT NOT NULL,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE asmaul_husna ENABLE ROW LEVEL SECURITY;
CREATE POLICY "asmaul_husna_read_all" ON asmaul_husna FOR SELECT USING (true);


-- ════════════════════════════════════════════════════════════
-- 08. PROFILES — Profil user (extend auth.users)
-- ════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS profiles (
    id              UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    name            TEXT,
    avatar_initial  TEXT DEFAULT 'H',
    city            TEXT,
    prayer_method   TEXT DEFAULT 'kemenag',
    theme           TEXT DEFAULT 'light',
    font_size       TEXT DEFAULT 'normal',
    qari_preference TEXT DEFAULT 'alafasy',
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_profiles_name ON profiles(name);

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
CREATE POLICY "profiles_read_own" ON profiles FOR SELECT USING (auth.uid() = id);
CREATE POLICY "profiles_update_own" ON profiles FOR UPDATE USING (auth.uid() = id);
CREATE POLICY "profiles_insert_own" ON profiles FOR INSERT WITH CHECK (auth.uid() = id);

CREATE TRIGGER profiles_updated_at
    BEFORE UPDATE ON profiles
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Auto-create profile on signup
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO profiles (id, name, avatar_initial)
    VALUES (
        NEW.id,
        COALESCE(NEW.raw_user_meta_data->>'name', 'User'),
        UPPER(LEFT(COALESCE(NEW.raw_user_meta_data->>'name', 'H'), 1))
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
    AFTER INSERT ON auth.users
    FOR EACH ROW EXECUTE FUNCTION handle_new_user();


-- ════════════════════════════════════════════════════════════
-- 09. BOOKMARKS
-- ════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS bookmarks (
    id              SERIAL PRIMARY KEY,
    user_id         UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    content_type    TEXT NOT NULL,
    content_key     TEXT NOT NULL,
    note            TEXT,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(user_id, content_type, content_key)
);

CREATE INDEX IF NOT EXISTS idx_bookmarks_user ON bookmarks(user_id);
CREATE INDEX IF NOT EXISTS idx_bookmarks_type ON bookmarks(user_id, content_type);

ALTER TABLE bookmarks ENABLE ROW LEVEL SECURITY;
CREATE POLICY "bookmarks_read_own" ON bookmarks FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "bookmarks_insert_own" ON bookmarks FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "bookmarks_delete_own" ON bookmarks FOR DELETE USING (auth.uid() = user_id);


-- ════════════════════════════════════════════════════════════
-- 10. PROGRESS — Streak, Checklist, Bacaan, Hafalan, Dzikir
-- ════════════════════════════════════════════════════════════

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,
    longest_streak  INTEGER DEFAULT 0,
    last_active     DATE,
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

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

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,
    doa             BOOLEAN DEFAULT FALSE,
    dzikir          BOOLEAN DEFAULT FALSE,
    hadits          BOOLEAN DEFAULT FALSE,
    sholat          BOOLEAN DEFAULT FALSE,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(user_id, date)
);

CREATE TABLE IF NOT EXISTS reading_progress (
    id              SERIAL PRIMARY KEY,
    user_id         UUID UNIQUE NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    surah_number    INTEGER NOT NULL,
    ayah_number     INTEGER NOT NULL DEFAULT 1,
    last_read_at    TIMESTAMPTZ DEFAULT NOW()
);

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',
    updated_at      TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(user_id, surah_number)
);

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,
    count           INTEGER DEFAULT 0,
    completed       BOOLEAN DEFAULT FALSE,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(user_id, date, type)
);

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);

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;

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);

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);

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);

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);

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);


-- ════════════════════════════════════════════════════════════
-- 11. GAME — XP, Sessions, Quiz, Leaderboard
-- ════════════════════════════════════════════════════════════

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,
    level           INTEGER DEFAULT 1,
    level_name      TEXT DEFAULT 'Pemula',
    total_correct   INTEGER DEFAULT 0,
    total_played    INTEGER DEFAULT 0,
    game_streak     INTEGER DEFAULT 0,
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

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

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,
    category        TEXT,
    juz             INTEGER,
    score           INTEGER DEFAULT 0,
    correct         INTEGER DEFAULT 0,
    wrong           INTEGER DEFAULT 0,
    xp_earned       INTEGER DEFAULT 0,
    played_at       TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS quiz_questions (
    id              SERIAL PRIMARY KEY,
    category        TEXT NOT NULL,
    question        TEXT NOT NULL,
    options         JSONB NOT NULL,
    answer          INTEGER NOT NULL,
    difficulty      TEXT DEFAULT 'medium',
    explanation     TEXT,
    active          BOOLEAN DEFAULT TRUE,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

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_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);

ALTER TABLE user_xp ENABLE ROW LEVEL SECURITY;
ALTER TABLE game_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE quiz_questions ENABLE ROW LEVEL SECURITY;

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);

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);

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;


-- ════════════════════════════════════════════════════════════
-- 12. WAKAF — Programs, Donations, Payment logs
-- ════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS wakaf_programs (
    id              SERIAL PRIMARY KEY,
    title           TEXT NOT NULL,
    description     TEXT,
    target_amount   INTEGER NOT NULL,
    target_quran    INTEGER,
    collected       INTEGER DEFAULT 0,
    quran_collected INTEGER DEFAULT 0,
    total_donors    INTEGER DEFAULT 0,
    image_url       TEXT,
    category        TEXT DEFAULT 'quran',
    status          TEXT DEFAULT 'active',
    priority        BOOLEAN DEFAULT FALSE,
    province        TEXT,
    city            TEXT,
    institution     TEXT,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TRIGGER wakaf_programs_updated_at
    BEFORE UPDATE ON wakaf_programs
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TABLE IF NOT EXISTS wakaf_donations (
    id              SERIAL PRIMARY KEY,
    user_id         UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    program_id      INTEGER NOT NULL REFERENCES wakaf_programs(id),
    donor_name      TEXT NOT NULL,
    donor_message   TEXT,
    amount          INTEGER NOT NULL,
    quran_count     INTEGER DEFAULT 1,
    payment_status  TEXT DEFAULT 'pending',
    payment_method  TEXT,
    payment_ref     TEXT,
    is_anonymous    BOOLEAN DEFAULT FALSE,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    paid_at         TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS payment_logs (
    id              SERIAL PRIMARY KEY,
    donation_id     INTEGER NOT NULL REFERENCES wakaf_donations(id),
    gateway         TEXT NOT NULL,
    event_type      TEXT NOT NULL,
    payload         JSONB,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_programs_status ON wakaf_programs(status);
CREATE INDEX IF NOT EXISTS idx_donations_user ON wakaf_donations(user_id);
CREATE INDEX IF NOT EXISTS idx_donations_program ON wakaf_donations(program_id);
CREATE INDEX IF NOT EXISTS idx_donations_status ON wakaf_donations(payment_status);
CREATE INDEX IF NOT EXISTS idx_payment_donation ON payment_logs(donation_id);

ALTER TABLE wakaf_programs ENABLE ROW LEVEL SECURITY;
ALTER TABLE wakaf_donations ENABLE ROW LEVEL SECURITY;
ALTER TABLE payment_logs ENABLE ROW LEVEL SECURITY;

CREATE POLICY "programs_read_all" ON wakaf_programs FOR SELECT USING (true);
CREATE POLICY "donations_read_public" ON wakaf_donations 
    FOR SELECT USING (is_anonymous = false OR auth.uid() = user_id);
CREATE POLICY "donations_insert_own" ON wakaf_donations 
    FOR INSERT WITH CHECK (auth.uid() = user_id OR user_id IS NULL);


-- ════════════════════════════════════════════════════════════
-- 13. CMS — Products, Announcements, Articles
-- ════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS products (
    id              SERIAL PRIMARY KEY,
    name            TEXT NOT NULL,
    description     TEXT,
    price           INTEGER NOT NULL,
    discount_price  INTEGER,
    category        TEXT NOT NULL,
    image_url       TEXT,
    stock           INTEGER DEFAULT 0,
    rating          NUMERIC(2,1) DEFAULT 0,
    total_reviews   INTEGER DEFAULT 0,
    total_sold      INTEGER DEFAULT 0,
    is_wakaf_option BOOLEAN DEFAULT FALSE,
    active          BOOLEAN DEFAULT TRUE,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TRIGGER products_updated_at
    BEFORE UPDATE ON products
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TABLE IF NOT EXISTS announcements (
    id              SERIAL PRIMARY KEY,
    text            TEXT NOT NULL,
    link            TEXT,
    active          BOOLEAN DEFAULT TRUE,
    priority        INTEGER DEFAULT 0,
    start_date      DATE,
    end_date        DATE,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS articles (
    id              SERIAL PRIMARY KEY,
    title           TEXT NOT NULL,
    slug            TEXT UNIQUE NOT NULL,
    body            TEXT NOT NULL,
    excerpt         TEXT,
    cover_image     TEXT,
    tags            TEXT[],
    author          TEXT DEFAULT 'Hayfala',
    published       BOOLEAN DEFAULT FALSE,
    published_at    TIMESTAMPTZ,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TRIGGER articles_updated_at
    BEFORE UPDATE ON articles
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE INDEX IF NOT EXISTS idx_products_category ON products(category);
CREATE INDEX IF NOT EXISTS idx_products_active ON products(active);
CREATE INDEX IF NOT EXISTS idx_announcements_active ON announcements(active);
CREATE INDEX IF NOT EXISTS idx_articles_slug ON articles(slug);
CREATE INDEX IF NOT EXISTS idx_articles_published ON articles(published);
CREATE INDEX IF NOT EXISTS idx_articles_tags ON articles USING GIN(tags);

ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE announcements ENABLE ROW LEVEL SECURITY;
ALTER TABLE articles ENABLE ROW LEVEL SECURITY;

CREATE POLICY "products_read_active" ON products FOR SELECT USING (active = true);
CREATE POLICY "announcements_read_active" ON announcements 
    FOR SELECT USING (active = true AND (start_date IS NULL OR start_date <= CURRENT_DATE) AND (end_date IS NULL OR end_date >= CURRENT_DATE));
CREATE POLICY "articles_read_published" ON articles FOR SELECT USING (published = true);


-- ════════════════════════════════════════════════════════════
-- ✅ DONE — Semua tabel berhasil dibuat!
-- ════════════════════════════════════════════════════════════