init.sql

CREATE EXTENSION IF NOT EXISTS ai CASCADE;

-- 1. Authors
CREATE TABLE IF NOT EXISTS authors (
    author_id INT PRIMARY KEY,
    name_ar TEXT,
    death_hijri INT,
    death_hijri_text TEXT,
    alpha_sort TEXT,
    biography TEXT
);

-- 2. Categories
CREATE TABLE IF NOT EXISTS categories (
    category_id INT PRIMARY KEY,
    category_name_ar TEXT,
    name_en TEXT,
    sort_order INT
);

-- 3. Books
CREATE TABLE IF NOT EXISTS books (
    book_id INT PRIMARY KEY,
    shamela_id INT,
    title_ar TEXT,
    book_type INT,
    book_type_label TEXT,
    category_id INT REFERENCES categories(category_id),
    main_author_id INT REFERENCES authors(author_id),
    main_author_name_ar TEXT,
    main_author_death_hijri INT,
    main_author_death_hijri_text TEXT,
    authors_text TEXT,
    hijri_era TEXT,
    printed BOOLEAN,
    is_hidden BOOLEAN,
    parent_id INT,
    group_id INT,
    version_major INT,
    version_minor INT,
    betaka_text TEXT,
    meta JSONB,
    volume_count_observed INT,
    has_multi_part BOOLEAN,
    authors_json JSONB
);

-- Book Authors (Many-to-Many relation between books and authors)
CREATE TABLE IF NOT EXISTS book_authors (
    book_id INT REFERENCES books(book_id),
    author_id INT REFERENCES authors(author_id),
    role TEXT,
    name_ar TEXT,
    death_hijri INT,
    PRIMARY KEY (book_id, author_id)
);

-- 4. Pages
CREATE TABLE IF NOT EXISTS pages (
    page_id BIGINT PRIMARY KEY,
    book_id INT REFERENCES books(book_id),
    shamela_page_id INT,
    part TEXT,
    page_num INT,
    sequence_num INT,
    body TEXT,
    footnotes TEXT,
    hints TEXT,
    services_raw JSONB
);

-- Index for Full-Text Search on Arabic text
CREATE INDEX IF NOT EXISTS pages_body_idx ON pages USING GIN (to_tsvector('arabic', body));

-- 5. Table of Contents (TOC)
CREATE TABLE IF NOT EXISTS toc (
    title_id BIGINT PRIMARY KEY,
    book_id INT REFERENCES books(book_id),
    page_id BIGINT REFERENCES pages(page_id),
    parent_id BIGINT REFERENCES toc(title_id),
    shamela_title_id INT,
    title_text TEXT
);

-- 6. Other Metadata Tables (Narrators, Quran, Roots, Xrefs)
-- We use JSONB for flexible schema mapping until exact column definitions are parsed

CREATE TABLE IF NOT EXISTS quran_verses (
    verse_id INT PRIMARY KEY,
    surah_id INT,
    ayah_id INT,
    text_ar TEXT,
    data JSONB
);

CREATE TABLE IF NOT EXISTS narrators (
    narrator_id INT PRIMARY KEY,
    name_ar TEXT,
    data JSONB
);

CREATE TABLE IF NOT EXISTS root_dictionary (
    root_id INT PRIMARY KEY,
    token TEXT,
    data JSONB
);

CREATE TABLE IF NOT EXISTS hadith_xrefs (
    id SERIAL PRIMARY KEY,
    book_id INT REFERENCES books(book_id),
    page_id BIGINT REFERENCES pages(page_id),
    data JSONB
);

CREATE TABLE IF NOT EXISTS tafsir_xrefs (
    id SERIAL PRIMARY KEY,
    book_id INT REFERENCES books(book_id),
    page_id BIGINT REFERENCES pages(page_id),
    data JSONB
);

CREATE TABLE IF NOT EXISTS page_isnads (
    id SERIAL PRIMARY KEY,
    book_id INT REFERENCES books(book_id),
    page_id BIGINT REFERENCES pages(page_id),
    narrator_id INT REFERENCES narrators(narrator_id),
    data JSONB
);