import gradio as gr
import json
import re
import sqlite3
import logging
from util import process_json_files
from gematria import calculate_gematria

logging.basicConfig(level=logging.INFO, format='%(message)s')

def flatten_text(text):
    """Hilfsfunktion, um verschachtelte Listen zu flachen Listen zu machen."""
    if isinstance(text, list):
        return " ".join(flatten_text(item) if isinstance(item, list) else item for item in text)
    return text

def initialize_database():
    conn = sqlite3.connect('gematria.db')
    c = conn.cursor()
    c.execute('''
        CREATE TABLE IF NOT EXISTS results (
            gematria_sum INTEGER,
            words TEXT,
            book INTEGER,
            title TEXT,
            chapter INTEGER,
            verse INTEGER
        )
    ''')
    conn.commit()
    conn.close()

def insert_phrase_to_db(c, gematria_sum, phrase_candidate, book_id, title, chapter_id, verse_id):
    logging.info(f"Inserting: {gematria_sum}, {phrase_candidate}, {book_id}, {title}, {chapter_id + 1}, {verse_id + 1}")
    c.execute('''
        INSERT INTO results (gematria_sum, words, book, title, chapter, verse)
        VALUES (?, ?, ?, ?, ?, ?)
    ''', (gematria_sum, phrase_candidate, book_id, title, chapter_id + 1, verse_id + 1))

def populate_database(tanach_texts, max_phrase_length=1):
    conn = sqlite3.connect('gematria.db')
    c = conn.cursor()
    for book_id, text in enumerate(tanach_texts):
        if not isinstance(text.get('text'), list):
            continue
        title = text.get('title', 'Unknown')
        chapters = text['text']
        for chapter_id, chapter in enumerate(chapters):
            if not isinstance(chapter, list):
                continue
            chapter_text = ' '.join([flatten_text(verse) for verse in chapter])
            chapter_text = re.sub(r"[^\u05D0-\u05EA ]+", "", chapter_text)
            chapter_text = re.sub(r" +", " ", chapter_text)
            words = chapter_text.split()
            max_length = min(max_phrase_length, len(words))
            for length in range(1, max_length + 1):
                for start in range(len(words) - length + 1):
                    phrase_candidate = " ".join(words[start:start + length])
                    gematria_sum = calculate_gematria(phrase_candidate.replace(" ", ""))
                    insert_phrase_to_db(c, gematria_sum, phrase_candidate, book_id, title, chapter_id, start + 1)
    conn.commit()
    conn.close()

def search_gematria_in_db(gematria_sum):
    conn = sqlite3.connect('gematria.db')
    c = conn.cursor()
    c.execute('''
        SELECT words, book, title, chapter, verse FROM results WHERE gematria_sum = ?
    ''', (gematria_sum,))
    results = c.fetchall()
    conn.close()
    return results

def db(tanach_texts, max_phrase_length=1):
    initialize_database()
    populate_database(tanach_texts, max_phrase_length)
    logging.info("Datenbank erfolgreich erstellt und gefüllt.")

def gematria_search_interface(phrase):
    debug_output = []

    def debug_callback(message):
        debug_output.append(message)
        logging.info(message)

    if not phrase.strip():
        return "Bitte geben Sie eine Phrase ein.", "\n".join(debug_output)

    phrase_gematria = calculate_gematria(phrase.replace(" ", ""))
    debug_callback(f"Debug: Gematria der Suchphrase '{phrase}' ist {phrase_gematria}")

    matching_phrases = search_gematria_in_db(phrase_gematria)

    if not matching_phrases:
        return "Keine passenden Phrasen gefunden.", "\n".join(debug_output)

    result = "Passende Phrasen:\n"
    for match in matching_phrases:
        result += f"Buch: {match[1]} ({match[2]})\nKapitel: {match[3]}, Vers: {match[4]}\nPhrase: {match[0]}\n\n"
    
    return result, "\n".join(debug_output)

def run_test():
    debug_output = []
    test_phrase = "אבגדהוזחטיכלמנסעפצקרשת"
    expected_gematria = 1495

    def debug_callback(message):
        debug_output.append(message)
        logging.info(message)

    # Lade die Test-JSON-Inhalte für 00.json
    test_texts_00 = process_json_files(0, 0)
    db(test_texts_00, max_phrase_length=22)  # Populate the database with 1-word phrases
    matching_phrases_00 = search_gematria_in_db(expected_gematria)
    assert matching_phrases_00, "Keine passenden Phrasen gefunden in 00.json."
    assert matching_phrases_00[0][0].replace(" ", "") == test_phrase, f"Gefundene Phrase stimmt nicht überein: {matching_phrases_00[0][0]}"
    print("Test erfolgreich: Die Phrase wurde korrekt gefunden und die Gematria stimmt überein in 00.json.")

    # Lade die Test-JSON-Inhalte für 01.json
    test_texts_01 = process_json_files(1, 1)
    db(test_texts_01, max_phrase_length=2)  # Populate the database with 1-word phrases
    search_phrase_01 = "אתקלך שמעתי"
    expected_gematria_01 = calculate_gematria(search_phrase_01.replace(" ", ""))

    matching_phrases_01 = search_gematria_in_db(expected_gematria_01)
    assert matching_phrases_01, "Keine passenden Phrasen gefunden in 01.json."
    assert matching_phrases_01[0][0].replace(" ", "") == search_phrase_01.replace(" ", ""), f"Gefundene Phrase stimmt nicht überein: {matching_phrases_01[0][0]}"
    print("Test erfolgreich: Die Phrase wurde korrekt gefunden und die Gematria stimmt überein in 01.json.")
    print("\n".join(debug_output))

iface = gr.Interface(
    fn=gematria_search_interface,
    inputs="text",
    outputs=["text", "text"],
    title="Gematria-Suche im Tanach",
    description="Suche nach Phrasen im Tanach, die denselben Gematria-Wert haben wie die eingegebene Phrase.",
    live=True  # Aktiviert das Live-Update
)

if __name__ == "__main__":
    run_test()  # Test ausführen
    iface.launch()