-- ============================================================
-- 02_create_ayahs.sql
-- Tabel 6.236 ayat Al-Quran
-- Sumber: Quran API ID (teks Arab, Latin, terjemahan, audio)
-- ============================================================

CREATE TABLE IF NOT EXISTS ayahs (
    id              SERIAL PRIMARY KEY,
    surah_number    INTEGER NOT NULL REFERENCES surahs(number),
    ayah_number     INTEGER NOT NULL,            -- Nomor ayat dalam surah
    ayah_key        TEXT UNIQUE NOT NULL,         -- "1:1", "2:255", dst
    arabic          TEXT NOT NULL,                -- Teks Arab
    latin           TEXT,                         -- Transliterasi Latin
    translation     TEXT NOT NULL,                -- Terjemahan Indonesia
    juz             INTEGER,                     -- Juz (1-30)
    page            INTEGER,                     -- Halaman mushaf
    manzil          INTEGER,                     -- Manzil
    ruku            INTEGER,                     -- Ruku
    audio_alafasy   TEXT,                        -- Audio: Mishari Alafasy
    audio_ajamy     TEXT,                        -- Audio: Ahmed al-Ajamy
    audio_husary    TEXT,                        -- Audio: Husary Mujawwad
    audio_minshawi  TEXT,                        -- Audio: Minshawi
    audio_ayyoub    TEXT,                        -- Audio: Muhammad Ayyoub
    audio_jibreel   TEXT,                        -- Audio: Muhammad Jibreel
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    
    UNIQUE(surah_number, ayah_number)
);

-- Index untuk query yang sering dipakai
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);

-- Full-text search index untuk pencarian terjemahan
CREATE INDEX IF NOT EXISTS idx_ayahs_translation_search 
    ON ayahs USING GIN (to_tsvector('indonesian', translation));

-- RLS: semua boleh baca
ALTER TABLE ayahs ENABLE ROW LEVEL SECURITY;

CREATE POLICY "ayahs_read_all" ON ayahs
    FOR SELECT USING (true);