import sqlite3
import os
import json
import logging
import datetime
from pathlib import Path
from typing import Any

logger = logging.getLogger(__name__)

DB_PATH = Path(__file__).resolve().parents[3] / "database" / "simulator.db"

def init_reaction_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 reaction_runs (
                run_id TEXT PRIMARY KEY,
                campaign_type TEXT,
                campaign_content_preview TEXT,
                timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
                sentiment_positive_pct INTEGER,
                sentiment_neutral_pct INTEGER,
                sentiment_negative_pct INTEGER,
                total_reactions INTEGER,
                model_name TEXT,
                is_mock BOOLEAN,
                recommendations_json TEXT
            )
        """)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS reaction_details (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                run_id TEXT,
                persona_id TEXT,
                persona_name TEXT,
                comment TEXT,
                sentiment TEXT,
                likes_estimate INTEGER,
                shares_estimate INTEGER,
                key_concern TEXT,
                FOREIGN KEY(run_id) REFERENCES reaction_runs(run_id)
            )
        """)
        conn.commit()

def save_reaction_run(run_data: dict[str, Any], is_mock: bool = False) -> str:
    run_id = f"REAC-{datetime.datetime.now().strftime('%Y%m%d%H%M%S')}"
    
    sentiment = run_data.get("sentiment", {})
    
    with sqlite3.connect(DB_PATH) as conn:
        cur = conn.cursor()
        
        cur.execute("""
            INSERT INTO reaction_runs (
                run_id, campaign_type, campaign_content_preview, sentiment_positive_pct,
                sentiment_neutral_pct, sentiment_negative_pct, total_reactions,
                model_name, is_mock, recommendations_json
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            run_id,
            run_data.get("campaign_type", ""),
            run_data.get("campaign_content", "")[:200] if run_data.get("campaign_content") else run_data.get("campaign_content_preview", ""),
            sentiment.get("positive", 0),
            sentiment.get("neutral", 0),
            sentiment.get("negative", 0),
            sentiment.get("total_reactions", 0),
            run_data.get("model_name", ""),
            is_mock,
            json.dumps(run_data.get("recommendations", []))
        ))
        
        for r in run_data.get("reactions", []):
            cur.execute("""
                INSERT INTO reaction_details (
                    run_id, persona_id, persona_name, comment, sentiment,
                    likes_estimate, shares_estimate, key_concern
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                run_id,
                r.get("persona_id", ""),
                r.get("persona_name", ""),
                r.get("comment", ""),
                r.get("sentiment", ""),
                r.get("likes_estimate", 0),
                r.get("shares_estimate", 0),
                r.get("key_concern", "")
            ))
            
        conn.commit()
    return run_id

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

def get_reaction_run(run_id: str) -> dict | None:
    with sqlite3.connect(DB_PATH) as conn:
        conn.row_factory = sqlite3.Row
        cur = conn.cursor()
        cur.execute("SELECT * FROM reaction_runs WHERE run_id = ?", (run_id,))
        run = cur.fetchone()
        if not run:
            return None
            
        run_data = dict(run)
        
        cur.execute("SELECT * FROM reaction_details WHERE run_id = ?", (run_id,))
        details = [dict(row) for row in cur.fetchall()]
        
        try:
            run_data["recommendations_json"] = json.loads(run_data["recommendations_json"]) if run_data.get("recommendations_json") else []
        except Exception:
            run_data["recommendations_json"] = []
            
        run_data["reactions"] = details
        return run_data

def compare_reaction_runs(run_id_1: str, run_id_2: str) -> dict:
    run1 = get_reaction_run(run_id_1)
    run2 = get_reaction_run(run_id_2)
    if not run1 or not run2:
        return {"error": "Run not found"}
        
    return {
        "run_1": {
            "run_id": run1["run_id"],
            "positive_pct": run1["sentiment_positive_pct"],
            "negative_pct": run1["sentiment_negative_pct"],
        },
        "run_2": {
            "run_id": run2["run_id"],
            "positive_pct": run2["sentiment_positive_pct"],
            "negative_pct": run2["sentiment_negative_pct"],
        }
    }

init_reaction_db()
