#!/usr/bin/env python3
"""
Load Complete Multi-Translation Bible Data into Oracle Database

Loads all 50+ Bible translations from:
/home/galileetees/ai-bible-app.pikzulstudios/bible-api/data/

Maps book names to standardized IDs and loads all verses.
"""

import sqlite3
import json
from pathlib import Path
from typing import Dict, Tuple

# Map book names (from JSON files) to standardized IDs
BOOK_NAME_TO_ID = {
    "Genesis": "GEN",
    "Exodus": "EXO",
    "Leviticus": "LEV",
    "Numbers": "NUM",
    "Deuteronomy": "DEU",
    "Joshua": "JOS",
    "Judges": "JDG",
    "Ruth": "RUT",
    "1 Samuel": "1SA",
    "2 Samuel": "2SA",
    "1 Kings": "1KI",
    "2 Kings": "2KI",
    "1 Chronicles": "1CH",
    "2 Chronicles": "2CH",
    "Ezra": "EZR",
    "Nehemiah": "NEH",
    "Esther": "EST",
    "Job": "JOB",
    "Psalms": "PSA",
    "Proverbs": "PRO",
    "Ecclesiastes": "ECC",
    "Song of Songs": "SNG",
    "Isaiah": "ISA",
    "Jeremiah": "JER",
    "Lamentations": "LAM",
    "Ezekiel": "EZK",
    "Daniel": "DAN",
    "Hosea": "HOS",
    "Joel": "JOL",
    "Amos": "AMO",
    "Obadiah": "OBA",
    "Jonah": "JON",
    "Micah": "MIC",
    "Nahum": "NAH",
    "Habakkuk": "HAB",
    "Zephaniah": "ZEP",
    "Haggai": "HAG",
    "Zechariah": "ZEC",
    "Malachi": "MAL",
    "Matthew": "MAT",
    "Mark": "MRK",
    "Luke": "LUK",
    "John": "JHN",
    "Acts": "ACT",
    "Romans": "ROM",
    "1 Corinthians": "1CO",
    "2 Corinthians": "2CO",
    "Galatians": "GAL",
    "Ephesians": "EPH",
    "Philippians": "PHP",
    "Colossians": "COL",
    "1 Thessalonians": "1TH",
    "2 Thessalonians": "2TH",
    "1 Timothy": "1TI",
    "2 Timothy": "2TI",
    "Titus": "TIT",
    "Philemon": "PHM",
    "Hebrews": "HEB",
    "James": "JAS",
    "1 Peter": "1PE",
    "2 Peter": "2PE",
    "1 John": "1JN",
    "2 John": "2JN",
    "3 John": "3JN",
    "Jude": "JUD",
    "Revelation": "REV"
}

def get_translation_abbreviation(filename: str) -> str:
    """Extract translation abbreviation from filename"""
    name = Path(filename).stem
    return name.upper()

def load_bible_translations(db_path: str, data_dir: str):
    """Load all Bible translations from JSON files into SQLite database"""

    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    # First, clear sample data
    print("Clearing existing sample data...")
    cursor.execute("DELETE FROM verses")
    conn.commit()

    data_path = Path(data_dir)
    json_files = list(data_path.glob("*.json"))

    print(f"Found {len(json_files)} Bible translation files")

    total_verses = 0
    loaded_translations = set()

    for json_file in sorted(json_files):
        translation = get_translation_abbreviation(json_file.name)

        if translation in loaded_translations:
            print(f"⚠️  Skipping duplicate: {json_file.name} (already loaded as {translation})")
            continue

        try:
            with open(json_file, 'r', encoding='utf-8') as f:
                data = json.load(f)

            if "books" not in data:
                print(f"⚠️  No 'books' key in {json_file.name}, skipping")
                continue

            books = data["books"]
            file_verse_count = 0

            for book_name, chapters in books.items():
                # Map book name to ID
                book_id = BOOK_NAME_TO_ID.get(book_name)
                if not book_id:
                    print(f"⚠️  Unknown book: {book_name} in {json_file.name}")
                    continue

                # chapters is dict: {"1": {"1": "text", "2": "text", ...}, "2": {...}}
                for chapter_num_str, verses in chapters.items():
                    try:
                        chapter_num = int(chapter_num_str)
                    except ValueError:
                        continue

                    for verse_num_str, verse_text in verses.items():
                        try:
                            verse_num = int(verse_num_str)
                        except ValueError:
                            continue

                        reference = f"{book_name} {chapter_num}:{verse_num}"

                        try:
                            cursor.execute('''
                                INSERT INTO verses
                                (reference, chapter, verse_start, verse_end, translation, text)
                                VALUES (?, ?, ?, ?, ?, ?)
                            ''', (reference, chapter_num, verse_num, None, translation, verse_text))
                            file_verse_count += 1
                            total_verses += 1
                        except sqlite3.IntegrityError:
                            # Duplicate reference for this translation, skip
                            pass

            loaded_translations.add(translation)
            print(f"✅ Loaded {file_verse_count:>6} verses from {translation:>8} ({json_file.name})")

        except Exception as e:
            print(f"❌ Error loading {json_file.name}: {e}")

    conn.commit()
    conn.close()

    print(f"\n{'='*70}")
    print(f"✅ TOTAL LOADED: {total_verses:,} verses across {len(loaded_translations)} translations")
    print(f"{'='*70}")

    return total_verses, len(loaded_translations)

if __name__ == "__main__":
    db_path = "/home/galileetees/apis.pikzulstudios/bible-companion/data/oracle.db"
    data_dir = "/home/galileetees/ai-bible-app.pikzulstudios/bible-api/data"

    if not Path(db_path).exists():
        print(f"❌ Database not found at {db_path}")
        print(f"   Run init_oracle.py first to create the database")
        exit(1)

    if not Path(data_dir).exists():
        print(f"❌ Bible data directory not found at {data_dir}")
        exit(1)

    print(f"Loading Bible translations from: {data_dir}")
    print(f"Database: {db_path}\n")

    verse_count, translation_count = load_bible_translations(db_path, data_dir)

    if verse_count > 0:
        print(f"\n✅ Success! {verse_count:,} verses loaded from {translation_count} translations")
        print(f"\nYou can now test the Bible API endpoints:")
        print(f"   curl http://127.0.0.1:8002/api/bible/books")
        print(f"   curl http://127.0.0.1:8002/api/bible/books/JHN/chapters")
        print(f"   curl http://127.0.0.1:8002/api/bible/chapters/JHN.3")
    else:
        print(f"❌ No verses loaded. Check the data directory.")