-- ============================================================
-- 13_create_products.sql
-- Tabel produk toko + CMS content (artikel, pengumuman)
-- ============================================================

-- ── PRODUCTS (TOKO) ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS products (
    id              SERIAL PRIMARY KEY,
    name            TEXT NOT NULL,                -- Nama produk
    description     TEXT,                        -- Deskripsi
    price           INTEGER NOT NULL,            -- Harga (Rp)
    discount_price  INTEGER,                     -- Harga diskon (null = tidak ada diskon)
    category        TEXT NOT NULL,                -- quran, sajadah, buku, aksesoris, bundling
    image_url       TEXT,                        -- URL gambar produk
    stock           INTEGER DEFAULT 0,           -- Stok tersedia
    rating          NUMERIC(2,1) DEFAULT 0,      -- Rating rata-rata (0.0 - 5.0)
    total_reviews   INTEGER DEFAULT 0,           -- Jumlah review
    total_sold      INTEGER DEFAULT 0,           -- Total terjual
    is_wakaf_option BOOLEAN DEFAULT FALSE,       -- Bisa "Beli untuk Diwakafkan"
    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();

-- ── ANNOUNCEMENTS (RUNNING TEXT) ──────────────────────────────
CREATE TABLE IF NOT EXISTS announcements (
    id              SERIAL PRIMARY KEY,
    text            TEXT NOT NULL,                -- Teks pengumuman
    link            TEXT,                         -- URL tujuan (opsional)
    active          BOOLEAN DEFAULT TRUE,
    priority        INTEGER DEFAULT 0,           -- Urutan tampalan
    start_date      DATE,                        -- Mulai tampil
    end_date        DATE,                        -- Berhenti tampil
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

-- ── ARTICLES ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS articles (
    id              SERIAL PRIMARY KEY,
    title           TEXT NOT NULL,                -- Judul artikel
    slug            TEXT UNIQUE NOT NULL,         -- URL slug
    body            TEXT NOT NULL,                -- Isi artikel (Markdown/HTML)
    excerpt         TEXT,                         -- Ringkasan singkat
    cover_image     TEXT,                        -- URL gambar cover
    tags            TEXT[],                       -- Tags: ['adab', 'doa', 'quran']
    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();

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

-- ── RLS ───────────────────────────────────────────────────────
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE announcements ENABLE ROW LEVEL SECURITY;
ALTER TABLE articles ENABLE ROW LEVEL SECURITY;

-- Semua boleh baca produk aktif
CREATE POLICY "products_read_active" ON products
    FOR SELECT USING (active = true);

-- Semua boleh baca pengumuman aktif
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)
    );

-- Semua boleh baca artikel yang sudah dipublish
CREATE POLICY "articles_read_published" ON articles
    FOR SELECT USING (published = true);