-- =====================================================================
-- مخطط قاعدة بيانات منصة الأرشفة الرقمية - مجلة نور الإسلام
-- مصمم لدعم أرشيف متعدد المجلدات (Multi-Volume Archive)
-- متوافق مع مبادئ Dublin Core / METS للبيانات الوصفية
-- =====================================================================

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;

-- ---------------------------------------------------------------------
-- 1) المجلدات (Volumes) — كل مجلد = ملف PDF أصلي واحد (عدد/سنة)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS volumes (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    volume_number   TEXT NOT NULL,              -- "المجلد الأول"
    hijri_year      TEXT,                        -- "١٣٤٩"
    gregorian_year  TEXT,                        -- "١٩٣١"
    title           TEXT NOT NULL DEFAULT 'نور الإسلام',
    publisher       TEXT DEFAULT 'مشيخة الأزهر الشريف',
    page_count      INTEGER NOT NULL DEFAULT 0,
    source_filename TEXT,                        -- اسم ملف PDF الأصلي
    source_pdf_path TEXT,                         -- مسار الملف على الخادم
    cover_image_path TEXT,
    notes           TEXT,
    created_at      TEXT DEFAULT (datetime('now')),
    updated_at      TEXT DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_volumes_year ON volumes(hijri_year, gregorian_year);

-- ---------------------------------------------------------------------
-- 1ب) الأعداد الشهرية (Issues) — كل مجلد يحتوي عادة على 10-12 عددًا شهريًا
--     مكتشفة تجريبيًا من الفواصل البيضاء بين الأعداد (انظر سجل المعالجة)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS issues (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    volume_id       INTEGER NOT NULL REFERENCES volumes(id) ON DELETE CASCADE,
    issue_number    INTEGER NOT NULL,            -- العدد رقم 1، 2، 3... ضمن المجلد
    hijri_month     TEXT,                         -- المحرم/صفر/...
    pdf_start_page  INTEGER NOT NULL,
    pdf_end_page    INTEGER,
    printed_page_offset INTEGER,                  -- الإزاحة المقدّرة بين رقم PDF والرقم المطبوع لهذا العدد
    verified        INTEGER DEFAULT 0,
    UNIQUE(volume_id, issue_number)
);

CREATE INDEX IF NOT EXISTS idx_issues_volume ON issues(volume_id);

-- ---------------------------------------------------------------------
-- 2) المؤلفون (Authors) — جدول مستقل لدعم البحث بالمؤلف ومنع التكرار
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS authors (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    display_name    TEXT NOT NULL UNIQUE,        -- "فضيلة الأستاذ الشيخ ابراهيم الجبالي"
    normalized_name TEXT NOT NULL,               -- نسخة منقّاة للبحث والمطابقة
    role_title      TEXT,                        -- "رئيس التحرير" / "قلم الترجمة" ...
    notes           TEXT,
    created_at      TEXT DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_authors_normalized ON authors(normalized_name);

-- ---------------------------------------------------------------------
-- 3) الموضوعات / التصنيفات (Subjects) — تصنيف هرمي اختياري
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS subjects (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    name            TEXT NOT NULL UNIQUE,        -- "فقه" / "تاريخ" / "تربية" ...
    parent_id       INTEGER REFERENCES subjects(id)
);

-- ---------------------------------------------------------------------
-- 4) المقالات (Articles) — الوحدة الأساسية القابلة للاستدعاء
--    (مستخرجة من فهرس الموضوعات + معالجة يدوية/شبه آلية)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS articles (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    volume_id       INTEGER NOT NULL REFERENCES volumes(id) ON DELETE CASCADE,
    title           TEXT NOT NULL,
    author_id       INTEGER REFERENCES authors(id),
    start_page      INTEGER NOT NULL,            -- رقم الصفحة داخل المجلة (مطبوع)
    end_page        INTEGER,
    pdf_start_page  INTEGER,                     -- رقم الصفحة الفعلي داخل ملف PDF
    pdf_end_page    INTEGER,
    toc_order       INTEGER,                     -- ترتيب الظهور فى الفهرس الأبجدي
    toc_letter      TEXT,                        -- الحرف الهجائي فى الفهرس (أ/ب/ت...)
    summary         TEXT,
    created_at      TEXT DEFAULT (datetime('now')),
    updated_at      TEXT DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_articles_volume ON articles(volume_id);
CREATE INDEX IF NOT EXISTS idx_articles_author ON articles(author_id);
CREATE INDEX IF NOT EXISTS idx_articles_pages ON articles(volume_id, start_page);

-- حلقات المقال (Article Installments) — لدعم المقالات المنشورة على حلقات متعددة
-- (شائع جدًا فى الدوريات التاريخية: نفس المقال يُكمَل عبر أعداد متتالية)
CREATE TABLE IF NOT EXISTS article_installments (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    article_id      INTEGER NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    printed_start_page INTEGER NOT NULL,         -- رقم الصفحة المطبوع كما ورد فى الفهرس
    printed_end_page   INTEGER,
    pdf_start_page  INTEGER,                      -- بعد المطابقة الفعلية مع PDF
    pdf_end_page    INTEGER,
    installment_order INTEGER DEFAULT 1,          -- ترتيب الحلقة (1، 2، 3...)
    verified        INTEGER DEFAULT 0             -- تم تأكيد الربط بصريًا
);

CREATE INDEX IF NOT EXISTS idx_installments_article ON article_installments(article_id);

-- ---------------------------------------------------------------------
-- 4ب) الكلمات المفتاحية (Keywords) — وسوم حرة لكل مقال
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS keywords (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    term            TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS article_keywords (
    article_id      INTEGER NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    keyword_id      INTEGER NOT NULL REFERENCES keywords(id) ON DELETE CASCADE,
    PRIMARY KEY (article_id, keyword_id)
);

-- ---------------------------------------------------------------------
-- 4ج) الأعلام المذكورون (Mentioned Figures) — خريطة معرفة: مقال ← يذكر ← عَلَم
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS figures (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    name            TEXT NOT NULL UNIQUE,
    normalized_name TEXT NOT NULL,
    era             TEXT,                          -- "صحابي" / "تابعي" / "معاصر" ...
    notes           TEXT
);

CREATE TABLE IF NOT EXISTS article_figures (
    article_id      INTEGER NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    figure_id       INTEGER NOT NULL REFERENCES figures(id) ON DELETE CASCADE,
    PRIMARY KEY (article_id, figure_id)
);

-- ---------------------------------------------------------------------
-- 4د) الآيات القرآنية المستشهد بها (مقال ← يستشهد بـ ← آية)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS quran_citations (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    article_id      INTEGER NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    page_id         INTEGER REFERENCES pages(id),
    surah_name      TEXT,                          -- اسم السورة (إن عُرف)
    ayah_text       TEXT NOT NULL,                  -- نص الآية كما ورد فى المقال
    notes           TEXT
);
CREATE INDEX IF NOT EXISTS idx_quran_article ON quran_citations(article_id);

-- ---------------------------------------------------------------------
-- 4هـ) الأحاديث المستشهد بها (مقال ← يستشهد بـ ← حديث)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS hadith_citations (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    article_id      INTEGER NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    page_id         INTEGER REFERENCES pages(id),
    hadith_text     TEXT NOT NULL,
    narrator        TEXT,                          -- الراوي (إن عُرف)
    source_book     TEXT,                           -- المصدر (البخاري/مسلم...) إن ذُكر فى المقال نفسه
    notes           TEXT
);
CREATE INDEX IF NOT EXISTS idx_hadith_article ON hadith_citations(article_id);

-- ---------------------------------------------------------------------
-- 4و) الأماكن المذكورة (مقال ← يذكر ← مكان)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS places (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    name            TEXT NOT NULL UNIQUE,
    normalized_name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS article_places (
    article_id      INTEGER NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    place_id        INTEGER NOT NULL REFERENCES places(id) ON DELETE CASCADE,
    PRIMARY KEY (article_id, place_id)
);

-- إضافة حقول الميتاداتا الموسّعة لجدول articles (مستخلص + نوع المادة + لغة)
-- (نستخدم ALTER TABLE داخل كتلة تتجاهل الخطأ لو كان الحقل موجودًا أصلًا)

-- ربط المقالات بالموضوعات (علاقة متعدد لمتعدد)
CREATE TABLE IF NOT EXISTS article_subjects (
    article_id      INTEGER NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    subject_id      INTEGER NOT NULL REFERENCES subjects(id) ON DELETE CASCADE,
    PRIMARY KEY (article_id, subject_id)
);

-- ---------------------------------------------------------------------
-- 5) الصفحات (Pages) — كل صفحة PDF كوحدة أرشفة مستقلة
--    تحمل: مسار صورة الصفحة + نص OCR + حالة المعالجة
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS pages (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    volume_id       INTEGER NOT NULL REFERENCES volumes(id) ON DELETE CASCADE,
    pdf_page_number INTEGER NOT NULL,            -- رقم الصفحة فى ملف PDF (1-820)
    printed_page_number TEXT,                     -- رقم الصفحة المطبوع على الصفحة نفسها (قد يختلف/يكون بالعربية)
    article_id      INTEGER REFERENCES articles(id),
    image_path      TEXT NOT NULL,                -- مسار صورة الصفحة (JPEG)
    thumb_path      TEXT,                          -- مسار صورة مصغّرة
    ocr_text        TEXT,                          -- النص المستخرج بالـ OCR
    ocr_confidence  REAL,                          -- مستوى ثقة OCR (تقديري)
    ocr_engine      TEXT DEFAULT 'tesseract-ara',
    ocr_status      TEXT DEFAULT 'pending',        -- pending | done | failed | reviewed
    reviewed_by_human INTEGER DEFAULT 0,
    created_at      TEXT DEFAULT (datetime('now')),
    UNIQUE(volume_id, pdf_page_number)
);

CREATE INDEX IF NOT EXISTS idx_pages_volume ON pages(volume_id, pdf_page_number);
CREATE INDEX IF NOT EXISTS idx_pages_article ON pages(article_id);
CREATE INDEX IF NOT EXISTS idx_pages_status ON pages(ocr_status);

-- ---------------------------------------------------------------------
-- 6) فهرس البحث النصي الكامل (FTS5) — على مستوى الصفحة وعلى مستوى المقال
--    استخدام unicode61 + remove_diacritics لتطبيع الهمزات/التشكيل العربي
-- ---------------------------------------------------------------------
CREATE VIRTUAL TABLE IF NOT EXISTS pages_fts USING fts5(
    ocr_text,
    content='pages',
    content_rowid='id',
    tokenize="unicode61 remove_diacritics 2"
);

CREATE VIRTUAL TABLE IF NOT EXISTS articles_fts USING fts5(
    title,
    author_name,
    summary,
    content='',
    tokenize="unicode61 remove_diacritics 2"
);

-- مُحرّكات (Triggers) لمزامنة pages_fts مع جدول pages تلقائيًا
CREATE TRIGGER IF NOT EXISTS pages_ai AFTER INSERT ON pages BEGIN
    INSERT INTO pages_fts(rowid, ocr_text) VALUES (new.id, new.ocr_text);
END;

CREATE TRIGGER IF NOT EXISTS pages_ad AFTER DELETE ON pages BEGIN
    INSERT INTO pages_fts(pages_fts, rowid, ocr_text) VALUES('delete', old.id, old.ocr_text);
END;

CREATE TRIGGER IF NOT EXISTS pages_au AFTER UPDATE ON pages BEGIN
    INSERT INTO pages_fts(pages_fts, rowid, ocr_text) VALUES('delete', old.id, old.ocr_text);
    INSERT INTO pages_fts(rowid, ocr_text) VALUES (new.id, new.ocr_text);
END;

-- ---------------------------------------------------------------------
-- 7) سجل عمليات المعالجة (Processing Log) — تتبّع خط أنابيب الأرشفة
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS processing_log (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    volume_id       INTEGER REFERENCES volumes(id),
    operation       TEXT NOT NULL,                -- "ocr" | "import" | "index" ...
    status          TEXT NOT NULL,                -- "started" | "completed" | "failed"
    details         TEXT,
    created_at      TEXT DEFAULT (datetime('now'))
);
