-- =============================================================================
--  bhojendra.com  ·  डाटाबेस स्किमा (SQLite / SQL मानक)
--  UTF-8 encoding — नेपाली युनिकोड पूर्ण रूपमा समर्थित
-- =============================================================================
--  सम्बन्ध (Relationships) — संक्षिप्त नक्सा:
--    categories 1───∞ posts            (हरेक लेख एक श्रेणीमा)
--    posts      1───1 slider_banners   (स्लाइडरमा देखाइने लेखहरू)
--    posts      1───∞ post_media       (लेखभित्रका थप तस्बिर)
--    galleries  1───∞ media            (फोटो/भिडियो ग्यालरी आइटम)
--    documents        (स्वतन्त्र — PDF प्रतिवेदनहरू)
--    inquiries        (सम्पर्क फारमबाट आउने सन्देश)
--    admin_users      (प्रशासनिक लगइन)
-- =============================================================================

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;

-- ─────────────────────────────────────────────────────────────────────────────
-- १. प्रशासक प्रयोगकर्ता
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS admin_users (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    username      TEXT    NOT NULL UNIQUE,
    password_hash TEXT    NOT NULL,            -- bcrypt हास (कहिल्यै सादा पासवर्ड नराख्ने)
    full_name     TEXT,
    role          TEXT    NOT NULL DEFAULT 'admin',  -- admin | editor
    created_at    TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- ─────────────────────────────────────────────────────────────────────────────
-- २. श्रेणी (मेनु र फिल्टरिङका लागि)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS categories (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    name_np     TEXT    NOT NULL,              -- देखाइने नाम: "साहित्य"
    slug        TEXT    NOT NULL UNIQUE,        -- URL: "sahitya"
    description TEXT,
    sort_order  INTEGER NOT NULL DEFAULT 0,
    is_menu     INTEGER NOT NULL DEFAULT 1      -- मुख्य मेनुमा देखाउने कि नदेखाउने
);

-- ─────────────────────────────────────────────────────────────────────────────
-- ३. लेख / पोस्ट (ब्लग, लेख/रचना, साहित्य, अर्थतन्त्र, प्रविधि, स्वास्थ्य सबै यहीँ)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS posts (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    title_np        TEXT    NOT NULL,
    slug            TEXT    NOT NULL UNIQUE,
    excerpt_np      TEXT,                       -- संक्षिप्त सार (कार्डमा देखाइने)
    body_np         TEXT    NOT NULL,           -- रिच-टेक्स्ट HTML (नेपाली युनिकोड)
    category_id     INTEGER NOT NULL,
    feature_image   TEXT,                       -- /uploads/... मार्ग
    author          TEXT    NOT NULL DEFAULT 'भोजेन्द्र',
    status          TEXT    NOT NULL DEFAULT 'draft',   -- draft | published
    in_slider       INTEGER NOT NULL DEFAULT 0,         -- गृहपृष्ठ स्लाइडरमा देखाउने टगल
    view_count      INTEGER NOT NULL DEFAULT 0,
    published_at    TEXT,                       -- प्रकाशित मिति
    created_at      TEXT    NOT NULL DEFAULT (datetime('now')),
    updated_at      TEXT    NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
);
CREATE INDEX IF NOT EXISTS idx_posts_category  ON posts(category_id);
CREATE INDEX IF NOT EXISTS idx_posts_status    ON posts(status, published_at);
CREATE INDEX IF NOT EXISTS idx_posts_in_slider ON posts(in_slider);

-- ─────────────────────────────────────────────────────────────────────────────
-- ४. स्लाइडर ब्यानर (गृहपृष्ठ हिरो क्यारोसेल — क्रम र क्याप्शन नियन्त्रण)
--    posts सँग 1:1 — एउटै लेखलाई दोहोरो स्लाइड हुनबाट UNIQUE ले रोक्छ
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS slider_banners (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    post_id      INTEGER NOT NULL UNIQUE,
    caption_np   TEXT,                          -- ओभरले शीर्षक ओभरराइड (वैकल्पिक)
    sort_order   INTEGER NOT NULL DEFAULT 0,
    is_active    INTEGER NOT NULL DEFAULT 1,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
);

-- ─────────────────────────────────────────────────────────────────────────────
-- ५. ग्यालरी (फोटो वा भिडियो सङ्ग्रह)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS galleries (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    title_np    TEXT    NOT NULL,
    type        TEXT    NOT NULL DEFAULT 'photo',  -- photo | video
    created_at  TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- ─────────────────────────────────────────────────────────────────────────────
-- ६. मिडिया आइटम (ग्यालरीभित्रका तस्बिर वा भिडियो; र लेखका थप तस्बिर)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS media (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    gallery_id  INTEGER,                         -- कुन ग्यालरीको (NULL भए स्वतन्त्र)
    type        TEXT    NOT NULL DEFAULT 'image', -- image | video
    file_path   TEXT,                            -- अपलोड गरिएको तस्बिरको मार्ग
    embed_url   TEXT,                            -- YouTube/Vimeo embed (भिडियोका लागि)
    caption_np  TEXT,
    sort_order  INTEGER NOT NULL DEFAULT 0,
    created_at  TEXT    NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (gallery_id) REFERENCES galleries(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_media_gallery ON media(gallery_id);

-- लेख ↔ मिडिया जोड्ने (लेखभित्रका इनलाइन तस्बिर)
CREATE TABLE IF NOT EXISTS post_media (
    post_id   INTEGER NOT NULL,
    media_id  INTEGER NOT NULL,
    PRIMARY KEY (post_id, media_id),
    FOREIGN KEY (post_id)  REFERENCES posts(id)  ON DELETE CASCADE,
    FOREIGN KEY (media_id) REFERENCES media(id)  ON DELETE CASCADE
);

-- ─────────────────────────────────────────────────────────────────────────────
-- ७. कागजात (PDF प्रतिवेदन — आर्थिक सर्वेक्षण, बजेट आदि)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS documents (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    title_np      TEXT    NOT NULL,             -- "आर्थिक सर्वेक्षण २०८१/८२"
    file_path     TEXT    NOT NULL,             -- /uploads/docs/...
    file_size_kb  INTEGER,
    download_count INTEGER NOT NULL DEFAULT 0,
    created_at    TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- ─────────────────────────────────────────────────────────────────────────────
-- ८. सम्पर्क सोधपुछ (फ्रन्टएन्ड सम्पर्क फारम → ड्यासबोर्डमा देखिने)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS inquiries (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    name_np     TEXT    NOT NULL,
    email       TEXT    NOT NULL,
    subject_np  TEXT,
    message_np  TEXT    NOT NULL,
    is_read     INTEGER NOT NULL DEFAULT 0,
    created_at  TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- ─────────────────────────────────────────────────────────────────────────────
-- ९. साइट कन्फिगरेसन (की–भ्यालु: इमेल, सामाजिक लिंक, लेखक बायो आदि)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS settings (
    key         TEXT PRIMARY KEY,
    value_np    TEXT
);