import os
import sys
import json
import sqlite3
from datetime import datetime
from logger import get_sage_logger

# Setup absolute paths
# Changed to use the local copy inside the repo
PSALM_DIR = os.path.abspath(os.path.join(os.path.dirname(__file__), "daily_psalms_api"))

# Configure logging
logger = get_sage_logger("spiritual_bridge")

def get_oracle_data(name: str = "", topic: str = "", date_str: str = ""):
    """
    Unified Oracle Tool backend. Queries Sura, Psalm, and Bible results.
    date_str format: YYYY-MM-DD. 
    Supports partials: YYYY-00-00 (whole year), YYYY-MM-00 (whole month).
    """
    sys.path.append(PSALM_DIR)
    original_cwd = os.getcwd()
    os.chdir(PSALM_DIR)
    try:
        from gematria import calculate_gematria
        from utils import date_to_words as ps_date_to_words
        import torah
        import math

        # 1. Date Handling (Partial Awareness)
        clean_date = date_str.replace("-00-00", "").replace("-00", "")
        if not clean_date:
            clean_date = datetime.now().strftime("%Y-%m-%d")
        
        # API's utils.py handles YYYY-MM-DD, YYYY-MM, and YYYY
        date_words = ps_date_to_words(clean_date)
        logger.info(f"DEBUG: Date '{clean_date}' converted to words: '{date_words}'")
        
        search_input = f"{name} {topic}".strip()
        combined = f"{search_input} {date_words}".strip()
        gematria_sum = calculate_gematria(combined)
        logger.info(f"DEBUG: Search combined: '{combined}' | Gematria: {gematria_sum}")
        
        # Constant for Genesis ELS search
        ADJUSTMENT_CONSTANT = 137.035999177
        adjusted_sum = math.ceil(gematria_sum + (gematria_sum / ADJUSTMENT_CONSTANT))
        logger.info(f"DEBUG: Adjusted Gematria for ELS: {adjusted_sum}")
        
        # 2. Get ELS Result from Genesis
        logger.info(f"DEBUG: Calling torah.process_json_files for Genesis ELS...")
        els_results = torah.process_json_files(1, 1, adjusted_sum, "1,-1", 0, "en", True, True, True, True)
        if not els_results:
            logger.warning(f"DEBUG: No ELS result for Adjusted Sum {adjusted_sum}")
            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", "")
        logger.info(f"DEBUG: ELS Raw (Heb): {els_text_heb} | ELS Trans (Eng): {els_text_eng}")

        if not els_text_eng or els_text_eng == els_text_heb:
            from deep_translator import GoogleTranslator
            try: 
                els_text_eng = GoogleTranslator(source='auto', target='en').translate(els_text_heb)
                logger.info(f"DEBUG: Fallback translation successful: {els_text_eng}")
            except Exception as te: 
                els_text_eng = els_text_heb
                logger.error(f"DEBUG: Fallback translation failed: {te}")

        els_gematria = calculate_gematria(els_text_heb)
        logger.info(f"DEBUG: Revelation Gematria Signal: {els_gematria}")
        
        # 3. Fetch Matches (Psalm, Sura, Bible)
        results_payload = {
            "query_context": {
                "name": name,
                "topic": topic,
                "date": date_str,
                "signal_strength": els_gematria
            },
            "els_revelation": {
                "original": els_text_heb,
                "english": els_text_eng
            },
            "wisdom_nodes": []
        }

        # Helper to find matches in multiple DBs
        def find_match(db_path, query, book_filter=None):
            logger.info(f"DEBUG: Matching Gematria {query} in {db_path} (Filter: {book_filter})")
            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,))
                match = cursor.fetchone()
                if match: logger.info(f"DEBUG: Found match in {db_path}: {match[2]} {match[3]}:{match[4]}")
                else: logger.info(f"DEBUG: No match in {db_path}")
                return match

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

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

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

        logger.info(f"DEBUG: Final payload contains {len(results_payload['wisdom_nodes'])} wisdom nodes.")
        return results_payload

    except Exception as e:
        logger.error(f"Oracle Tool Error: {e}")
        return {"error": str(e)}
    finally:
        os.chdir(original_cwd)
        if PSALM_DIR in sys.path: sys.path.remove(PSALM_DIR)

def format_wisdom(match, category):
    words, trans, book, ch, vs = match
    if not trans or trans == words:
        try:
            from deep_translator import GoogleTranslator
            trans = GoogleTranslator(source='auto', target='en').translate(words)
        except: trans = words
    return {
        "category": category,
        "reference": f"{book} {ch}:{vs}",
        "original": words,
        "english": trans
    }