import sqlite3
import pandas as pd
import json
import numpy as np

def generate_sqlite_dump():
    # Connect to in-memory SQLite database
    conn = sqlite3.connect(':memory:')
    cursor = conn.cursor()

    # 1. Create SQLite Schema for metadata
    schema = """
    CREATE TABLE IF NOT EXISTS authors (
        author_id INTEGER PRIMARY KEY,
        name_ar TEXT,
        death_hijri INTEGER,
        death_hijri_text TEXT,
        alpha_sort TEXT,
        biography TEXT
    );

    CREATE TABLE IF NOT EXISTS categories (
        category_id INTEGER PRIMARY KEY,
        category_name_ar TEXT,
        name_en TEXT,
        sort_order INTEGER
    );

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

    CREATE TABLE IF NOT EXISTS book_authors (
        book_id INTEGER REFERENCES books(book_id),
        author_id INTEGER REFERENCES authors(author_id),
        role TEXT,
        name_ar TEXT,
        death_hijri INTEGER,
        PRIMARY KEY (book_id, author_id)
    );

    CREATE TABLE IF NOT EXISTS pages (
        page_id INTEGER PRIMARY KEY,
        book_id INTEGER REFERENCES books(book_id),
        shamela_page_id INTEGER,
        part TEXT,
        page_num INTEGER,
        sequence_num INTEGER,
        body TEXT,
        footnotes TEXT,
        hints TEXT,
        services_raw TEXT
    );

    CREATE INDEX IF NOT EXISTS pages_body_idx ON pages (body);

    CREATE TABLE IF NOT EXISTS toc (
        title_id INTEGER PRIMARY KEY,
        book_id INTEGER REFERENCES books(book_id),
        page_id INTEGER REFERENCES pages(page_id),
        parent_id INTEGER REFERENCES toc(title_id),
        shamela_title_id INTEGER,
        title_text TEXT
    );

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

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

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

    CREATE TABLE IF NOT EXISTS hadith_xrefs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        book_id INTEGER REFERENCES books(book_id),
        page_id INTEGER REFERENCES pages(page_id),
        data TEXT
    );

    CREATE TABLE IF NOT EXISTS tafsir_xrefs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        book_id INTEGER REFERENCES books(book_id),
        page_id INTEGER REFERENCES pages(page_id),
        data TEXT
    );

    CREATE TABLE IF NOT EXISTS page_isnads (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        book_id INTEGER REFERENCES books(book_id),
        page_id INTEGER REFERENCES pages(page_id),
        narrator_id INTEGER REFERENCES narrators(narrator_id),
        data TEXT
    );
    """
    cursor.executescript(schema)
    
    # 2. Download and insert metadata
    print("Downloading Categories...")
    df_cat = pd.read_parquet("https://huggingface.co/datasets/AuthenticIlm/Shamela4_Full_DB/resolve/main/_meta/categories.parquet")
    if 'id' in df_cat.columns: df_cat.rename(columns={'id': 'category_id'}, inplace=True)
    if 'name_ar' in df_cat.columns and 'category_name_ar' not in df_cat.columns: df_cat.rename(columns={'name_ar': 'category_name_ar'}, inplace=True)
    if 'category_name' in df_cat.columns and 'category_name_ar' not in df_cat.columns: df_cat.rename(columns={'category_name': 'category_name_ar'}, inplace=True)
    cols_cat = ['category_id', 'category_name_ar', 'name_en', 'sort_order']
    df_cat = df_cat[[c for c in cols_cat if c in df_cat.columns]]
    df_cat.to_sql("categories", conn, if_exists="append", index=False)

    print("Downloading Authors...")
    df_auth = pd.read_parquet("https://huggingface.co/datasets/AuthenticIlm/Shamela4_Full_DB/resolve/main/_meta/authors.parquet")
    if 'id' in df_auth.columns: df_auth.rename(columns={'id': 'author_id'}, inplace=True)
    cols_auth = ['author_id', 'name_ar', 'death_hijri', 'death_hijri_text', 'alpha_sort', 'biography']
    df_auth = df_auth[[c for c in cols_auth if c in df_auth.columns]]
    df_auth.to_sql("authors", conn, if_exists="append", index=False)

    print("Downloading Books Metadata...")
    df_books = pd.read_parquet("https://huggingface.co/datasets/AuthenticIlm/Shamela4_Full_DB/resolve/main/_meta/book_metadata.parquet")
    cols_books = ['book_id', 'shamela_id', 'title_ar', 'book_type', 'book_type_label', 'category_id', 'main_author_id', 'main_author_name_ar', 'main_author_death_hijri', 'main_author_death_hijri_text', 'authors_text', 'hijri_era', 'printed', 'is_hidden', 'parent_id', 'group_id', 'version_major', 'version_minor', 'betaka_text', 'meta', 'volume_count_observed', 'has_multi_part', 'authors_json']
    df_books = df_books[[c for c in cols_books if c in df_books.columns]]
    if 'meta' in df_books.columns:
        df_books['meta'] = df_books['meta'].apply(lambda x: json.dumps(x.tolist()) if isinstance(x, np.ndarray) else (json.dumps(x) if isinstance(x, dict) else (x if pd.isna(x) else json.dumps(x))))
    if 'authors_json' in df_books.columns:
        df_books['authors_json'] = df_books['authors_json'].apply(lambda x: json.dumps(x.tolist()) if isinstance(x, np.ndarray) else (json.dumps(x) if isinstance(x, dict) else (x if pd.isna(x) else json.dumps(x))))
    df_books.to_sql("books", conn, if_exists="append", index=False)

    # 3. Dump to .sql file
    output_filename = 'metadata_dump.sql'
    print(f"Dumping to {output_filename}...")
    with open(output_filename, 'w', encoding='utf-8') as f:
        for line in conn.iterdump():
            # Include only schemas and INSERTs for metadata tables.
            # conn.iterdump() does a full backup of the schema and inserts for tables in memory.
            f.write('%s\n' % line)
    
    print(f"Done! The SQL dump is ready at: {output_filename}")
    conn.close()

if __name__ == "__main__":
    generate_sqlite_dump()