import sqlite3
import urllib.request
import urllib.error
import json
import time
import os

# 5 Books of Torah
TORAH_BOOKS = ["Genesis", "Exodus", "Leviticus", "Numbers", "Deuteronomy"]

with open('final_mishnah.json', 'r', encoding='utf-8') as f:
    MISHNAH_BOOKS = json.load(f)

with open('final_talmud.json', 'r', encoding='utf-8') as f:
    TALMUD_BOOKS = json.load(f)

def create_db(db_name):
    """Creates a SQLite database with a verses table"""
    conn = sqlite3.connect(db_name, isolation_level=None)
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS verses (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            book TEXT,
            chapter INTEGER,
            verse INTEGER,
            text_en TEXT,
            text_he TEXT
        )
    ''')
    cursor.execute('PRAGMA journal_mode=WAL;')
    return conn

def fetch_book_data(book_name, conn):
    """Fetches book data from Sefaria API and stores it in the database"""
    url_book_name = book_name.replace(' ', '_')
    url = f"https://www.sefaria.org/api/texts/{url_book_name}?context=0&pad=0"
    
    print(f"Fetching {book_name} from Sefaria...")
    try:
        req = urllib.request.Request(url, headers={'User-Agent': 'Mozilla/5.0'})
        with urllib.request.urlopen(req, timeout=15) as response:
            data = json.loads(response.read().decode('utf-8'))
            
            if 'error' in data:
                print(f"   [-] Book '{book_name}' returned error: {data['error'][:50]}")
                return False
                
            cursor = conn.cursor()
            
            english_text = data.get('text', [])
            hebrew_text = data.get('he', [])
            
            if not english_text and not hebrew_text:
                print(f"   [-] Book '{book_name}' has no EN/HE text.")
                return False
                
            count = 0
            
            def insert_recursive(en_node, he_node, path_indices):
                nonlocal count
                if isinstance(en_node, str) or isinstance(he_node, str):
                    chap_num = path_indices[0] if len(path_indices) > 0 else 1
                    verse_num = path_indices[-1] if len(path_indices) > 0 else 1
                    
                    en_str = en_node if isinstance(en_node, str) else ""
                    he_str = he_node if isinstance(he_node, str) else ""
                    
                    cursor.execute('''
                        INSERT INTO verses (book, chapter, verse, text_en, text_he)
                        VALUES (?, ?, ?, ?, ?)
                    ''', (book_name, chap_num, verse_num, en_str, he_str))
                    count += 1
                elif isinstance(en_node, list) or isinstance(he_node, list):
                    en_list = en_node if isinstance(en_node, list) else []
                    he_list = he_node if isinstance(he_node, list) else []
                    max_len = max(len(en_list), len(he_list))
                    
                    for i in range(max_len):
                        curr_en = en_list[i] if i < len(en_list) else ""
                        curr_he = he_list[i] if i < len(he_list) else ""
                        insert_recursive(curr_en, curr_he, path_indices + [i + 1])
            
            cursor.execute("BEGIN TRANSACTION")
            insert_recursive(english_text, hebrew_text, [])
            cursor.execute("COMMIT")
            
            if count > 0:
                print(f"   [+] Successfully saved {book_name} ({count} verses)")
            else:
                print(f"   [!] Book '{book_name}' fetched but no verses inserted.")
            return count > 0
            
    except Exception as e:
        print(f"   [X] Error fetching {book_name}: {e}")
        return False
    
def main():
    print("--- Starting Sefaria Data Extraction (FIXED NAMES VERSION) ---\n")
    
    DELAY = 1.0
    
    databases = [
        ("Torah", "torah.db", TORAH_BOOKS),
        ("Mishnah", "mishnah.db", MISHNAH_BOOKS),
        ("Talmud", "talmud.db", TALMUD_BOOKS)
    ]
    
    for db_label, db_file, book_list in databases:
        print(f"\n📚 Verifying/Creating {db_label} Database ({db_file})...")
        conn = create_db(db_file)
        
        for book in book_list:
            cursor = conn.cursor()
            cursor.execute("SELECT COUNT(*) FROM verses WHERE book=?", (book,))
            already_done = cursor.fetchone()[0] > 0
            
            # Additional check: If it was partially saved and aborted, we might have weird state, 
            # but assume if > 0 verses, it's fully downloaded to save rate limits.
            if not already_done:
                if fetch_book_data(book, conn):
                    time.sleep(DELAY) 
        
        # Verify Total Count dynamically at the end of each DB
        cursor.execute("SELECT COUNT(DISTINCT book) FROM verses")
        total_books = cursor.fetchone()[0]
        print(f"✔️ {db_label} Database Verification Complete! (Downloaded: {total_books}/{len(book_list)})")
        conn.close()
    
    print("\n--- Process Finished! All possible books downloaded successfully. ---")

if __name__ == "__main__":
    main()