import logging
import sqlite3

from backend.services.usb_monitor import get_db_path
logger = logging.getLogger(__name__)

async def update_career_index(persona: str, session_summary: dict):
    # Log to SQLite if there's a valid session
    session_id = session_summary.get("id")
    game_id = session_summary.get("game_id", "unknown")
    avg_dqi = session_summary.get("avg_dqi", 50.0)
    decision_count = session_summary.get("decision_count", 0)
    
    if session_id:
        try:
            with sqlite3.connect(get_db_path()) as conn:
                conn.execute('PRAGMA journal_mode=WAL')
                conn.execute('''
                    UPDATE gaming_sessions 
                    SET avg_dqi_score = ?, decision_count = ?, persona_coaching = ?, ended_at = CURRENT_TIMESTAMP
                    WHERE id = ?
                ''', (avg_dqi, decision_count, persona, session_id))
        except Exception as e:
            logger.error(f"Failed to update session summary: {e}")
            
    # Compute rolling DQI trend
    trend = await compute_rolling_dqi_trend(game_id, window_sessions=20)
    
    return {
        "current_avg_dqi": trend["current_avg"],
        "trend_direction": trend["direction"],
        "strongest_skill_area": "Positioning",
        "weakest_skill_area": "Utility Timing",
    }

async def compute_rolling_dqi_trend(game_id: str, window_sessions: int = 20) -> dict:
    try:
        with sqlite3.connect(get_db_path()) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.execute('''
                SELECT avg_dqi_score FROM gaming_sessions 
                WHERE game_id = ? AND avg_dqi_score IS NOT NULL
                ORDER BY ended_at DESC LIMIT ?
            ''', (game_id, window_sessions))
            rows = cursor.fetchall()
        
        if not rows:
            return {"current_avg": 50.0, "direction": "plateauing"}
            
        scores = [r["avg_dqi_score"] for r in rows]
        avg = sum(scores) / len(scores)
        
        direction = "plateauing"
        if len(scores) >= 5:
            recent_avg = sum(scores[:5]) / 5
            old_avg = sum(scores[-5:]) / 5
            if recent_avg > old_avg + 5:
                direction = "improving"
            elif recent_avg < old_avg - 5:
                direction = "declining"
                
        return {"current_avg": avg, "direction": direction}
    except Exception as e:
        logger.error(f"Error computing trend: {e}")
        return {"current_avg": 50.0, "direction": "plateauing"}