import sqlite3
import json
import logging
import sys
import os

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

def inspect_cache_db(db_path):
    """Inspect an ELS cache database file for Quran ELS results with gematria sum 5642."""
    logger.info(f"Inspecting ELS cache database: {db_path}")
    
    if not os.path.exists(db_path):
        logger.error(f"Database file not found: {db_path}")
        return
        
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # Check if the table exists
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='els_cache'")
        if not cursor.fetchone():
            logger.error("No 'els_cache' table found in database")
            conn.close()
            return
            
        # Query for Quran results with gematria sum 5642
        cursor.execute("SELECT * FROM els_cache WHERE function_name LIKE '%quran%' AND args LIKE '%5642%'")
        results = cursor.fetchall()
        
        if not results:
            logger.info("No matching Quran ELS cache entries found with gematria sum 5642")
            
            # Try with just the function name
            cursor.execute("SELECT * FROM els_cache WHERE function_name LIKE '%quran%'")
            quran_results = cursor.fetchall()
            logger.info(f"Total Quran-related cache entries: {len(quran_results)}")
            
            if quran_results:
                # Show a few examples
                for i, row in enumerate(quran_results[:3]):
                    try:
                        # Try to extract gematria sum from args
                        args_dict = json.loads(row[2]) if row[2] else {}
                        step = args_dict.get('step', 'unknown')
                        logger.info(f"Example {i+1}: function={row[1]}, step={step}")
                        
                        # Try to parse the results to extract the ELS text
                        if row[4]:  # results column
                            els_results = json.loads(row[4])
                            if isinstance(els_results, list) and els_results:
                                logger.info(f"  First result text: {els_results[0].get('result_text', 'N/A')}")
                                logger.info(f"  Gematria: {els_results[0].get('gematria', 'N/A')}")
                    except (json.JSONDecodeError, IndexError) as e:
                        logger.error(f"Error parsing cache entry: {e}")
        else:
            logger.info(f"Found {len(results)} matching cache entries")
            
            # Display the matched entries
            for i, row in enumerate(results):
                try:
                    query_hash = row[0] if len(row) > 0 else 'N/A'
                    function_name = row[1] if len(row) > 1 else 'N/A'
                    args = row[2] if len(row) > 2 else '{}'
                    
                    logger.info(f"Entry {i+1}:")
                    logger.info(f"  Query hash: {query_hash}")
                    logger.info(f"  Function: {function_name}")
                    
                    # Parse the args
                    args_dict = json.loads(args)
                    pretty_args = json.dumps(args_dict, indent=2)
                    logger.info(f"  Args: {pretty_args}")
                    
                    # Parse and display the results
                    if len(row) > 4 and row[4]:
                        results_data = json.loads(row[4])
                        
                        if isinstance(results_data, list):
                            logger.info(f"  Results count: {len(results_data)}")
                            
                            for j, result in enumerate(results_data[:3]):  # Show up to 3 results
                                if isinstance(result, dict):
                                    result_text = result.get('result_text', 'N/A')
                                    gematria = result.get('gematria', 'N/A')
                                    step = result.get('step', 'N/A')
                                    logger.info(f"    Result {j+1}: text={result_text}, gematria={gematria}, step={step}")
                        elif isinstance(results_data, dict):
                            logger.info("  Results is a dictionary")
                            result_text = results_data.get('result_text', 'N/A')
                            gematria = results_data.get('gematria', 'N/A')
                            step = results_data.get('step', 'N/A')
                            logger.info(f"    Result: text={result_text}, gematria={gematria}, step={step}")
                        else:
                            logger.info("  Results is not a list")
                except Exception as e:
                    logger.error(f"Error processing cache entry: {e}")
                    
        conn.close()
        
    except sqlite3.Error as e:
        logger.error(f"SQLite error: {e}")
    except Exception as e:
        logger.error(f"Unexpected error: {e}")

if __name__ == "__main__":
    # Check daily_psalm cache
    daily_psalm_db = "/run/media/julian/ML2/Python/bos-network/daily_psalm/els_cache.db"
    inspect_cache_db(daily_psalm_db)
    
    print("\n" + "-"*50 + "\n")
    
    # Check daily-psalms-api cache
    api_db = "/run/media/julian/ML2/Python/bos-network/daily-psalms-api/els_cache.db"
    inspect_cache_db(api_db)