import sqlite3
import os
import json
import logging
import hashlib
import glob
from typing import Any

logger = logging.getLogger(__name__)

DB_PATH = "database/simulator.db"

def init_simulator_db():
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    with sqlite3.connect(DB_PATH) as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS simulation_runs (
                run_id INTEGER PRIMARY KEY AUTOINCREMENT,
                timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
                persona_count INTEGER,
                turns INTEGER,
                prompt_version TEXT,
                model_name TEXT,
                chatbot_url TEXT,
                conversion_rate TEXT,
                interest_rate TEXT,
                avg_score TEXT,
                summary_json TEXT,
                errors_json TEXT
            )
        """)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS simulation_details (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                run_id INTEGER,
                persona_name TEXT,
                archetype TEXT,
                age INTEGER,
                gender TEXT,
                budget TEXT,
                shopping_for TEXT,
                conversion_status TEXT,
                conversion_reason TEXT,
                scores_json TEXT,
                conversation_json TEXT,
                key_wins TEXT,
                key_fails TEXT,
                prompt_fix TEXT,
                products_json TEXT,
                FOREIGN KEY(run_id) REFERENCES simulation_runs(run_id)
            )
        """)
        conn.commit()

def get_prompt_version() -> str:
    """Read prompt files and generate a short hash version."""
    hasher = hashlib.md5()
    # Find all txt files in prompt_module
    files = glob.glob("agent/prompt_module/*.txt")
    for f in sorted(files):
        try:
            with open(f, "rb") as file:
                hasher.update(file.read())
        except Exception as e:
            logger.warning(f"Error reading {f} for hash: {e}")
            pass
    return "v-" + hasher.hexdigest()[:6]

def save_simulation_run(run_data: dict[str, Any], prompt_version: str = None) -> int:
    """Save full simulation run to DB."""
    if not prompt_version:
        prompt_version = get_prompt_version()
        
    summary = run_data.get("conversion_summary", {})
    meta = run_data.get("meta", {})
    avg_scores = summary.get("avg_scores", {})
    
    with sqlite3.connect(DB_PATH) as conn:
        cursor = conn.cursor()
        
        # Insert run
        cursor.execute("""
            INSERT INTO simulation_runs (
                persona_count, turns, prompt_version, model_name, chatbot_url,
                conversion_rate, interest_rate, avg_score, summary_json, errors_json
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            meta.get("personas_generated", 0),
            meta.get("turns_per_persona", 0),
            prompt_version,
            meta.get("model", ""),
            meta.get("chatbot_url", ""),
            summary.get("conversion_rate", "0%"),
            summary.get("interest_rate", "0%"),
            json.dumps(avg_scores),
            json.dumps(summary),
            json.dumps(run_data.get("errors", []))
        ))
        
        run_id = cursor.lastrowid
        
        # Insert details
        for r in run_data.get("persona_results", []):
            cursor.execute("""
                INSERT INTO simulation_details (
                    run_id, persona_name, archetype, age, gender, budget, shopping_for,
                    conversion_status, conversion_reason, scores_json, conversation_json,
                    key_wins, key_fails, prompt_fix, products_json
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                run_id,
                r.get("persona_name", ""),
                r.get("archetype", ""),
                r.get("age", 0),
                r.get("gender", ""),
                r.get("budget", ""),
                r.get("shopping_for", ""),
                r.get("conversion_status", ""),
                r.get("reasoning", ""),
                json.dumps(r.get("scores", {})),
                json.dumps(r.get("conversation", [])),
                json.dumps(r.get("key_wins", [])),
                json.dumps(r.get("key_fails", [])),
                r.get("prompt_fix_suggestion", ""),
                json.dumps(r.get("products_recommended", []))
            ))
            
        conn.commit()
        return run_id

def get_simulation_runs(limit: int = 50) -> list[dict]:
    with sqlite3.connect(DB_PATH) as conn:
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM simulation_runs ORDER BY timestamp DESC LIMIT ?", (limit,))
        return [dict(row) for row in cursor.fetchall()]

def get_simulation_run(run_id: int) -> dict | None:
    with sqlite3.connect(DB_PATH) as conn:
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM simulation_runs WHERE run_id = ?", (run_id,))
        run = cursor.fetchone()
        if not run:
            return None
            
        run_data = dict(run)
        
        cursor.execute("SELECT * FROM simulation_details WHERE run_id = ?", (run_id,))
        details = [dict(row) for row in cursor.fetchall()]
        
        # Parse JSON fields
        for field in ["summary_json", "errors_json", "avg_score"]:
            try:
                run_data[field] = json.loads(run_data[field]) if run_data.get(field) else {}
            except Exception:
                pass
                
        for detail in details:
            for field in ["scores_json", "conversation_json", "key_wins", "key_fails", "products_json"]:
                try:
                    detail[field] = json.loads(detail[field]) if detail.get(field) else []
                except Exception:
                    pass
                    
        run_data["details"] = details
        return run_data

def compare_runs(run_id_1: int, run_id_2: int) -> dict:
    run1 = get_simulation_run(run_id_1)
    run2 = get_simulation_run(run_id_2)
    if not run1 or not run2:
        return {"error": "Run not found"}
        
    # Basic comparison logic
    return {
        "run_1": {
            "run_id": run1["run_id"],
            "prompt_version": run1["prompt_version"],
            "conversion_rate": run1["conversion_rate"],
            "interest_rate": run1["interest_rate"],
        },
        "run_2": {
            "run_id": run2["run_id"],
            "prompt_version": run2["prompt_version"],
            "conversion_rate": run2["conversion_rate"],
            "interest_rate": run2["interest_rate"],
        }
    }

init_simulator_db()
