import os
import sys
import json
import sqlite3
import math
import logging
from datetime import datetime

logger = logging.getLogger("app.oracle")

# Paths
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
PSALM_DIR = os.path.join(BASE_DIR, "daily_psalms_api")

def get_oracle_data(name: str = "Seeker", topic: str = "humanity", date_str: str = ""):
    """
    Consolidated Oracle Tool.
    date_str: YYYY-MM-DD
    """
    # 1. Prepare Environment
    original_cwd = os.getcwd()
    if PSALM_DIR not in sys.path: sys.path.append(PSALM_DIR)
    os.chdir(PSALM_DIR)
    
    try:
        from gematria import calculate_gematria
        from utils import date_to_words as ps_date_to_words
        import torah
        
        # 2. Date Handling
        if not date_str or date_str.lower() == "today":
            date_str = datetime.now().strftime("%Y-%m-%d")
        
        clean_date = date_str.replace("-00-00", "").replace("-00", "")
        date_words = ps_date_to_words(clean_date)
        
        # 3. Gematria Key Calculation
        search_input = f"{name} {topic}".strip()
        combined = f"{search_input} {date_words}".strip()
        gematria_sum = calculate_gematria(combined)
        
        # ELS Constant Adjustment
        ADJUSTMENT_CONSTANT = 137.035999177
        adjusted_sum = math.ceil(gematria_sum + (gematria_sum / ADJUSTMENT_CONSTANT))
        
        # 4. ELS Revelation Search
        els_results = torah.process_json_files(1, 1, adjusted_sum, "1,-1", 0, "en", True, True, True, True)
        if not els_results:
            return {"error": "No prophetic signal found in the depths of time."}
            
        els_text_heb = els_results[0].get("result_text", "")
        els_text_eng = els_results[0].get("translated_text", "")
        
        # Fallback Translation
        if not els_text_eng or els_text_eng == els_text_heb:
            try:
                from deep_translator import GoogleTranslator
                els_text_eng = GoogleTranslator(source='auto', target='en').translate(els_text_heb)
            except: pass

        els_gematria = calculate_gematria(els_text_heb)
        
        # 5. Wisdom Node Matching
        results_payload = {
            "query_context": {"name": name, "topic": topic, "date": date_str, "signal": els_gematria},
            "els_revelation": {"original": els_text_heb, "english": els_text_eng},
            "wisdom_nodes": []
        }

        def find_match(db_name, query, book_filter=None):
            db_path = os.path.join(PSALM_DIR, db_name)
            if not os.path.exists(db_path): return None
            try:
                with sqlite3.connect(db_path) as conn:
                    cursor = conn.cursor()
                    sql = "SELECT words, translation, book, chapter, verse FROM results WHERE gematria_sum = ?"
                    if book_filter: sql += f" AND book = '{book_filter}'"
                    sql += " ORDER BY LENGTH(words) ASC LIMIT 1"
                    cursor.execute(sql, (query,))
                    return cursor.fetchone()
            except: return None

        # [A] Psalm
        ps_match = find_match("gematria.db", els_gematria, "Psalms")
        if ps_match: results_payload["wisdom_nodes"].append(format_node(ps_match, "Biblical Psalm"))

        # [B] Sura
        sura_match = find_match("abjad.db", els_gematria)
        if sura_match: results_payload["wisdom_nodes"].append(format_node(sura_match, "Quranic Sura"))

        # [C] NT Prophecy
        bible_match = find_match("bible.db", els_gematria, "Revelation")
        if bible_match: results_payload["wisdom_nodes"].append(format_node(bible_match, "NT Prophecy"))

        return results_payload

    except Exception as e:
        logger.error(f"Oracle Error: {e}")
        return {"error": str(e)}
    finally:
        os.chdir(original_cwd)

def format_node(match, category):
    words, trans, book, ch, vs = match
    return {
        "category": category,
        "reference": f"{book} {ch}:{vs}",
        "original": words,
        "english": trans if trans and trans != words else "..."
    }