-- ============================================================
-- 12_create_donations.sql
-- Tabel wakaf: program, donasi, payment logs
-- ============================================================

-- ── WAKAF PROGRAMS ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS wakaf_programs (
    id              SERIAL PRIMARY KEY,
    title           TEXT NOT NULL,                -- "Wakaf Quran Pedalaman"
    description     TEXT,                        -- Deskripsi program
    target_amount   INTEGER NOT NULL,            -- Target dana (Rp)
    target_quran    INTEGER,                     -- Target jumlah Quran
    collected       INTEGER DEFAULT 0,           -- Dana terkumpul
    quran_collected INTEGER DEFAULT 0,           -- Quran terkumpul
    total_donors    INTEGER DEFAULT 0,           -- Jumlah donatur
    image_url       TEXT,                        -- URL gambar program
    category        TEXT DEFAULT 'quran',        -- quran, mushaf-anak, dll
    status          TEXT DEFAULT 'active',       -- active, completed, inactive
    priority        BOOLEAN DEFAULT FALSE,       -- Prioritas (badge "Daerah Prioritas")
    province        TEXT,                        -- Provinsi tujuan
    city            TEXT,                        -- Kota tujuan
    institution     TEXT,                        -- Pesantren/Masjid/Sekolah tujuan
    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();

-- ── WAKAF DONATIONS ───────────────────────────────────────────
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,                -- Nama donatur
    donor_message   TEXT,                         -- Doa wakaf (tercetak di Quran)
    amount          INTEGER NOT NULL,             -- Jumlah donasi (Rp)
    quran_count     INTEGER DEFAULT 1,           -- Jumlah Quran dari donasi ini
    payment_status  TEXT DEFAULT 'pending',       -- pending, paid, failed, refunded
    payment_method  TEXT,                         -- bank_transfer, ewallet, dll
    payment_ref     TEXT,                         -- Reference dari payment gateway
    is_anonymous    BOOLEAN DEFAULT FALSE,        -- Anonim atau tidak
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    paid_at         TIMESTAMPTZ
);

-- ── PAYMENT LOGS ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS payment_logs (
    id              SERIAL PRIMARY KEY,
    donation_id     INTEGER NOT NULL REFERENCES wakaf_donations(id),
    gateway         TEXT NOT NULL,                -- midtrans, xendit
    event_type      TEXT NOT NULL,                -- created, callback, settled, expired
    payload         JSONB,                        -- Raw payload dari gateway
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

-- ── INDEXES ───────────────────────────────────────────────────
CREATE INDEX IF NOT EXISTS idx_programs_status ON wakaf_programs(status);
CREATE INDEX IF NOT EXISTS idx_programs_category ON wakaf_programs(category);
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_donations_created ON wakaf_donations(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_payment_donation ON payment_logs(donation_id);

-- ── RLS ───────────────────────────────────────────────────────
ALTER TABLE wakaf_programs ENABLE ROW LEVEL SECURITY;
ALTER TABLE wakaf_donations ENABLE ROW LEVEL SECURITY;
ALTER TABLE payment_logs ENABLE ROW LEVEL SECURITY;

-- Wakaf Programs: semua boleh baca
CREATE POLICY "programs_read_all" ON wakaf_programs
    FOR SELECT USING (true);

-- Wakaf Donations: baca sendiri + donasi publik (non-anonim)
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);

-- Payment Logs: hanya diperlukan oleh webhook (service_role)
-- Tidak ada policy SELECT untuk client → default deny