"""
Live AI Sessions Routes

This module handles database operations for live AI call sessions including:
- Session creation, storage, and retrieval
- Conversation history management
- Session continuation and resumption
- Session analytics and metrics
"""

from flask import Blueprint, request, jsonify, g
from datetime import datetime, timedelta
import json
import uuid
import logging
from typing import Dict, List, Optional

from utils.decorators import require_api_key
from auth.jwt_manager import require_auth
from utils.response_helpers import format_api_response, create_error_response
from database import get_cached_connection

logger = logging.getLogger(__name__)

# Create blueprint
live_ai_sessions_bp = Blueprint('live_ai_sessions', __name__, url_prefix='/api/live-ai-sessions')

# Database initialization
def init_live_ai_sessions_tables():
    """Initialize database tables for live AI sessions"""
    try:
        connection = get_cached_connection()
        cursor = connection.cursor()
        
        # Sessions table - stores main session data with emotional intelligence fields
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS live_ai_sessions (
                id INT PRIMARY KEY AUTO_INCREMENT,
                session_id VARCHAR(255) UNIQUE NOT NULL,
                user_id VARCHAR(255) NOT NULL,
                title TEXT,
                topic_id VARCHAR(255),
                course_id VARCHAR(255),
                language_code VARCHAR(50) DEFAULT 'en-US',
                language_name VARCHAR(100) DEFAULT 'English',
                system_prompt TEXT,
                study_context TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                ended_at DATETIME,
                is_active TINYINT DEFAULT 1,
                total_interactions INT DEFAULT 0,
                understanding_level DECIMAL(3,2) DEFAULT 0.50,
                duration_seconds INT DEFAULT 0,
                key_topics TEXT,
                session_metrics TEXT,
                -- New emotional intelligence fields
                emotional_state VARCHAR(50) DEFAULT 'neutral',
                confidence_level DECIMAL(3,2) DEFAULT 0.50,
                engagement_level DECIMAL(3,2) DEFAULT 0.50,
                learning_style VARCHAR(50) DEFAULT 'balanced',
                pace_preference VARCHAR(50) DEFAULT 'moderate',
                curiosity_score DECIMAL(3,2) DEFAULT 0.50,
                conversation_momentum DECIMAL(3,2) DEFAULT 0.50,
                breakthrough_moments TEXT,
                struggle_points TEXT,
                personality_traits TEXT,
                KEY idx_user_id (user_id),
                KEY idx_session_id (session_id),
                KEY idx_created_at (created_at),
                KEY idx_emotional_state (emotional_state),
                KEY idx_learning_style (learning_style)
            )
        """)
        
        # Session messages table - stores conversation history
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS live_ai_session_messages (
                id INT PRIMARY KEY AUTO_INCREMENT,
                session_id VARCHAR(255) NOT NULL,
                message_id VARCHAR(255) UNIQUE NOT NULL,
                role VARCHAR(50) NOT NULL,
                content TEXT NOT NULL,
                audio_url TEXT,
                timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
                message_order INT,
                metadata TEXT,
                KEY idx_session_id (session_id),
                KEY idx_timestamp (timestamp),
                FOREIGN KEY (session_id) REFERENCES live_ai_sessions (session_id) ON DELETE CASCADE
            )
        """)
        
        # Session analytics table - stores detailed analytics
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS live_ai_session_analytics (
                id INT PRIMARY KEY AUTO_INCREMENT,
                session_id VARCHAR(255) NOT NULL,
                user_id VARCHAR(255) NOT NULL,
                interaction_count INT DEFAULT 0,
                understanding_progression TEXT,
                topic_coverage TEXT,
                response_quality_scores TEXT,
                language_consistency_score DECIMAL(3,2) DEFAULT 1.00,
                session_satisfaction DECIMAL(2,1),
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                KEY idx_session_id_analytics (session_id),
                KEY idx_user_id_analytics (user_id),
                FOREIGN KEY (session_id) REFERENCES live_ai_sessions (session_id) ON DELETE CASCADE
            )
        """)
        
        connection.commit()
        logger.info("Live AI sessions tables initialized successfully")
        
    except Exception as e:
        logger.error(f"Error initializing live AI sessions tables: {e}")
        if connection:
            connection.rollback()
        raise
    finally:
        # CRITICAL: Always close cursor and connection to prevent leaks
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except:
                pass
        if 'connection' in locals() and connection:
            try:
                connection.close()
            except:
                pass

@live_ai_sessions_bp.route('/history', methods=['GET'])
@require_api_key
@require_auth
def get_session_history():
    """Get user's live AI session history"""
    try:
        user_id = getattr(g, 'current_user_id', None)
        if not user_id:
            return create_error_response("User ID not found", 401)
        
        # Get pagination parameters
        page = int(request.args.get('page', 1))
        limit = min(int(request.args.get('limit', 20)), 100)  # Max 100 per page
        offset = (page - 1) * limit
        
        # Get filter parameters
        is_active = request.args.get('is_active')
        language_code = request.args.get('language')
        
        # Build query
        where_clauses = ["user_id = %s"]
        params = [user_id]
        
        if is_active is not None:
            where_clauses.append("is_active = %s")
            params.append(1 if is_active.lower() == 'true' else 0)
            
        if language_code:
            where_clauses.append("language_code = %s")
            params.append(language_code)
        
        where_clause = " AND ".join(where_clauses)
        
        # Get sessions with basic info including emotional intelligence fields
        query = f"""
            SELECT 
                session_id,
                title,
                topic_id,
                course_id,
                language_code,
                language_name,
                created_at,
                updated_at,
                ended_at,
                is_active,
                total_interactions,
                understanding_level,
                duration_seconds,
                key_topics,
                emotional_state,
                confidence_level,
                engagement_level,
                learning_style,
                pace_preference,
                curiosity_score,
                conversation_momentum
            FROM live_ai_sessions 
            WHERE {where_clause}
            ORDER BY updated_at DESC
            LIMIT %s OFFSET %s
        """
        
        params.extend([limit, offset])
        connection = get_cached_connection()
        cursor = connection.cursor()
        cursor.execute(query, params)
        sessions_result = cursor.fetchall()
        
        # Get total count
        count_query = f"SELECT COUNT(*) FROM live_ai_sessions WHERE {where_clause}"
        cursor.execute(count_query, params[:-2])
        total_count = cursor.fetchone()[0]
        
        # Format sessions data including emotional intelligence fields
        sessions = []
        for row in sessions_result:
            session_data = {
                'session_id': row[0],
                'title': row[1] or f"Session {row[0][:8]}",
                'topic_id': row[2],
                'course_id': row[3],
                'language_code': row[4],
                'language_name': row[5],
                'created_at': row[6].isoformat() if row[6] else None,
                'updated_at': row[7].isoformat() if row[7] else None,
                'ended_at': row[8].isoformat() if row[8] else None,
                'is_active': bool(row[9]),
                'total_interactions': row[10],
                'understanding_level': float(row[11]) if row[11] is not None else 0.5,
                'duration_seconds': row[12],
                'key_topics': json.loads(row[13]) if row[13] else [],
                # New emotional intelligence fields
                'emotional_state': row[14] if row[14] else 'neutral',
                'confidence_level': float(row[15]) if row[15] is not None else 0.5,
                'engagement_level': float(row[16]) if row[16] is not None else 0.5,
                'learning_style': row[17] if row[17] else 'balanced',
                'pace_preference': row[18] if row[18] else 'moderate',
                'curiosity_score': float(row[19]) if row[19] is not None else 0.5,
                'conversation_momentum': float(row[20]) if row[20] is not None else 0.5
            }
            
            # Get last message preview
            last_message_query = """
                SELECT content, role, timestamp 
                FROM live_ai_session_messages 
                WHERE session_id = %s 
                ORDER BY message_order DESC, timestamp DESC 
                LIMIT 1
            """
            cursor.execute(last_message_query, (row[0],))
            last_message_result = cursor.fetchall()
            
            if last_message_result:
                session_data['last_message'] = {
                    'content': last_message_result[0][0][:100] + "..." if len(last_message_result[0][0]) > 100 else last_message_result[0][0],
                    'role': last_message_result[0][1],
                    'timestamp': last_message_result[0][2].isoformat() if last_message_result[0][2] else None
                }
            
            sessions.append(session_data)
        
        cursor.close()
        connection.close()
        
        return format_api_response({
            'sessions': sessions,
            'pagination': {
                'page': page,
                'limit': limit,
                'total': total_count,
                'has_next': offset + limit < total_count,
                'has_prev': page > 1
            }
        }, include_legacy_fields={'sessions': sessions, 'pagination': {'page': page, 'limit': limit, 'total': total_count, 'has_next': offset + limit < total_count, 'has_prev': page > 1}})
        
    except Exception as e:
        logger.error(f"Error getting session history: {e}")
        return create_error_response(str(e), 500)
    finally:
        # CRITICAL: Always close cursor and connection on error
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except:
                pass
        if 'connection' in locals() and connection:
            try:
                connection.close()
            except:
                pass

@live_ai_sessions_bp.route('/session/<session_id>', methods=['GET'])
@require_api_key
@require_auth
def get_session_details(session_id):
    """Get detailed information about a specific session including full conversation history"""
    try:
        user_id = getattr(g, 'current_user_id', None)
        
        # Get session info including emotional intelligence fields
        session_query = """
            SELECT 
                session_id, title, topic_id, course_id, language_code, language_name,
                system_prompt, study_context, created_at, updated_at, ended_at,
                is_active, total_interactions, understanding_level, duration_seconds,
                key_topics, session_metrics, emotional_state, confidence_level,
                engagement_level, learning_style, pace_preference, curiosity_score,
                conversation_momentum, breakthrough_moments, struggle_points, personality_traits
            FROM live_ai_sessions 
            WHERE session_id = %s AND user_id = %s
        """
        
        connection = get_cached_connection()
        cursor = connection.cursor()
        cursor.execute(session_query, (session_id, user_id))
        session_result = cursor.fetchall()
        
        if not session_result:
            cursor.close()
            connection.close()
            return create_error_response("Session not found", 404)
        
        session_row = session_result[0]
        session_data = {
            'session_id': session_row[0],
            'title': session_row[1] or f"Session {session_row[0][:8]}",
            'topic_id': session_row[2],
            'course_id': session_row[3],
            'language_code': session_row[4],
            'language_name': session_row[5],
            'system_prompt': session_row[6],
            'study_context': session_row[7],
            'created_at': session_row[8].isoformat() if session_row[8] else None,
            'updated_at': session_row[9].isoformat() if session_row[9] else None,
            'ended_at': session_row[10].isoformat() if session_row[10] else None,
            'is_active': bool(session_row[11]),
            'total_interactions': session_row[12],
            'understanding_level': session_row[13],
            'duration_seconds': session_row[14],
            'key_topics': json.loads(session_row[15]) if session_row[15] else [],
            'session_metrics': json.loads(session_row[16]) if session_row[16] else {},
            # New emotional intelligence fields
            'emotional_state': session_row[17] if session_row[17] else 'neutral',
            'confidence_level': float(session_row[18]) if session_row[18] is not None else 0.5,
            'engagement_level': float(session_row[19]) if session_row[19] is not None else 0.5,
            'learning_style': session_row[20] if session_row[20] else 'balanced',
            'pace_preference': session_row[21] if session_row[21] else 'moderate',
            'curiosity_score': float(session_row[22]) if session_row[22] is not None else 0.5,
            'conversation_momentum': float(session_row[23]) if session_row[23] is not None else 0.5,
            'breakthrough_moments': json.loads(session_row[24]) if session_row[24] else [],
            'struggle_points': json.loads(session_row[25]) if session_row[25] else [],
            'personality_traits': json.loads(session_row[26]) if session_row[26] else {}
        }
        
        # Get conversation history
        messages_query = """
            SELECT message_id, role, content, audio_url, timestamp, message_order, metadata
            FROM live_ai_session_messages 
            WHERE session_id = %s
            ORDER BY message_order ASC, timestamp ASC
        """
        
        cursor.execute(messages_query, (session_id,))
        messages_result = cursor.fetchall()
        
        messages = []
        for msg_row in messages_result:
            message_data = {
                'message_id': msg_row[0],
                'role': msg_row[1],
                'content': msg_row[2],
                'audio_url': msg_row[3],
                'timestamp': msg_row[4].isoformat() if msg_row[4] else None,
                'message_order': msg_row[5],
                'metadata': json.loads(msg_row[6]) if msg_row[6] else {}
            }
            messages.append(message_data)
        
        session_data['conversation_history'] = messages
        
        # Get analytics if available
        analytics_query = """
            SELECT interaction_count, understanding_progression, topic_coverage, 
                   response_quality_scores, language_consistency_score, session_satisfaction
            FROM live_ai_session_analytics 
            WHERE session_id = %s
        """
        
        cursor.execute(analytics_query, (session_id,))
        analytics_result = cursor.fetchall()
        
        if analytics_result:
            analytics_row = analytics_result[0]
            session_data['analytics'] = {
                'interaction_count': analytics_row[0],
                'understanding_progression': json.loads(analytics_row[1]) if analytics_row[1] else [],
                'topic_coverage': json.loads(analytics_row[2]) if analytics_row[2] else {},
                'response_quality_scores': json.loads(analytics_row[3]) if analytics_row[3] else [],
                'language_consistency_score': analytics_row[4],
                'session_satisfaction': analytics_row[5]
            }
        
        cursor.close()
        connection.close()
        
        return format_api_response({'session': session_data}, include_legacy_fields={'session': session_data})
        
    except Exception as e:
        logger.error(f"Error getting session details: {e}")
        return create_error_response(str(e), 500)
    finally:
        # CRITICAL: Always close cursor and connection on error
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except:
                pass
        if 'connection' in locals() and connection:
            try:
                connection.close()
            except:
                pass

@live_ai_sessions_bp.route('/session/<session_id>/continue', methods=['POST'])
@require_api_key
@require_auth
def continue_session(session_id):
    """Continue an existing session"""
    try:
        user_id = getattr(g, 'current_user_id', None)
        
        # Verify session exists and belongs to user - include emotional intelligence fields
        session_query = """
            SELECT session_id, is_active, language_code, language_name, 
                   system_prompt, study_context, total_interactions, understanding_level,
                   emotional_state, confidence_level, engagement_level, learning_style,
                   pace_preference, curiosity_score, conversation_momentum,
                   breakthrough_moments, struggle_points, personality_traits
            FROM live_ai_sessions 
            WHERE session_id = %s AND user_id = %s
        """
        
        connection = get_cached_connection()
        cursor = connection.cursor()
        cursor.execute(session_query, (session_id, user_id))
        session_result = cursor.fetchall()
        
        if not session_result:
            cursor.close()
            connection.close()
            return create_error_response("Session not found", 404)
        
        session_row = session_result[0]
        
        # Update session as active
        update_query = """
            UPDATE live_ai_sessions 
            SET is_active = 1, updated_at = CURRENT_TIMESTAMP
            WHERE session_id = %s
        """
        cursor.execute(update_query, (session_id,))
        connection.commit()
        
        # Get recent conversation history (last 10 messages)
        messages_query = """
            SELECT role, content, timestamp
            FROM live_ai_session_messages 
            WHERE session_id = %s
            ORDER BY message_order DESC, timestamp DESC
            LIMIT 10
        """
        
        cursor.execute(messages_query, (session_id,))
        messages_result = cursor.fetchall()
        
        conversation_history = []
        for msg_row in reversed(messages_result):  # Reverse to get chronological order
            conversation_history.append({
                'role': msg_row[0],
                'content': msg_row[1],
                'timestamp': msg_row[2].isoformat() if msg_row[2] else None
            })
        
        cursor.close()
        connection.close()
        
        # Return session continuation data including emotional intelligence
        continuation_data = {
            'session_id': session_id,
            'language_code': session_row[2],
            'language_name': session_row[3],
            'system_prompt': session_row[4],
            'study_context': session_row[5],
            'total_interactions': session_row[6],
            'understanding_level': session_row[7],
            'conversation_history': conversation_history,
            # New emotional intelligence fields
            'emotional_state': session_row[8] if session_row[8] else 'neutral',
            'confidence_level': float(session_row[9]) if session_row[9] is not None else 0.5,
            'engagement_level': float(session_row[10]) if session_row[10] is not None else 0.5,
            'learning_style': session_row[11] if session_row[11] else 'balanced',
            'pace_preference': session_row[12] if session_row[12] else 'moderate',
            'curiosity_score': float(session_row[13]) if session_row[13] is not None else 0.5,
            'conversation_momentum': float(session_row[14]) if session_row[14] is not None else 0.5,
            'breakthrough_moments': json.loads(session_row[15]) if session_row[15] else [],
            'struggle_points': json.loads(session_row[16]) if session_row[16] else [],
            'personality_traits': json.loads(session_row[17]) if session_row[17] else {},
            'message': 'Session ready to continue'
        }
        
        return format_api_response({'continuation': continuation_data}, include_legacy_fields={'continuation': continuation_data})
        
    except Exception as e:
        logger.error(f"Error continuing session: {e}")
        return create_error_response(str(e), 500)
    finally:
        # CRITICAL: Always close cursor and connection on error
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except:
                pass
        if 'connection' in locals() and connection:
            try:
                connection.close()
            except:
                pass

@live_ai_sessions_bp.route('/session/<session_id>', methods=['DELETE'])
@require_api_key
@require_auth
def delete_session(session_id):
    """Delete a session and all its data"""
    try:
        user_id = getattr(g, 'current_user_id', None)
        
        # Verify session exists and belongs to user
        session_query = "SELECT session_id FROM live_ai_sessions WHERE session_id = %s AND user_id = %s"
        
        connection = get_cached_connection()
        cursor = connection.cursor()
        cursor.execute(session_query, (session_id, user_id))
        session_result = cursor.fetchall()
        
        if not session_result:
            cursor.close()
            connection.close()
            return create_error_response("Session not found", 404)
        
        # Delete in order (due to foreign keys)
        cursor.execute("DELETE FROM live_ai_session_analytics WHERE session_id = %s", (session_id,))
        cursor.execute("DELETE FROM live_ai_session_messages WHERE session_id = %s", (session_id,))
        cursor.execute("DELETE FROM live_ai_sessions WHERE session_id = %s", (session_id,))
        connection.commit()
        cursor.close()
        connection.close()
        
        return format_api_response({'message': 'Session deleted successfully'}, include_legacy_fields={'message': 'Session deleted successfully'})
        
    except Exception as e:
        logger.error(f"Error deleting session: {e}")
        return create_error_response(str(e), 500)
    finally:
        # CRITICAL: Always close cursor and connection on error
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except:
                pass
        if 'connection' in locals() and connection:
            try:
                connection.close()
            except:
                pass

@live_ai_sessions_bp.route('/session/<session_id>/title', methods=['PUT'])
@require_api_key
@require_auth
def update_session_title(session_id):
    """Update session title"""
    try:
        user_id = getattr(g, 'current_user_id', None)
        data = request.get_json()
        
        if not data or 'title' not in data:
            return create_error_response("Title is required", 400)
        
        title = data['title'].strip()[:100]  # Limit title length
        
        # Update title
        update_query = """
            UPDATE live_ai_sessions 
            SET title = %s, updated_at = CURRENT_TIMESTAMP
            WHERE session_id = %s AND user_id = %s
        """
        
        connection = get_cached_connection()
        cursor = connection.cursor()
        cursor.execute(update_query, (title, session_id, user_id))
        
        if cursor.rowcount == 0:
            cursor.close()
            connection.close()
            return create_error_response("Session not found", 404)
        
        connection.commit()
        cursor.close()
        connection.close()
        
        return format_api_response({'message': 'Title updated successfully', 'title': title}, include_legacy_fields={'message': 'Title updated successfully', 'title': title})
        
    except Exception as e:
        logger.error(f"Error updating session title: {e}")
        return create_error_response(str(e), 500)
    finally:
        # CRITICAL: Always close cursor and connection on error
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except:
                pass
        if 'connection' in locals() and connection:
            try:
                connection.close()
            except:
                pass

@live_ai_sessions_bp.route('/stats', methods=['GET'])
@require_api_key
@require_auth
def get_user_session_stats():
    """Get user's overall session statistics"""
    try:
        user_id = getattr(g, 'current_user_id', None)
        
        # Get overall stats
        stats_query = """
            SELECT 
                COUNT(*) as total_sessions,
                COUNT(CASE WHEN is_active = 1 THEN 1 END) as active_sessions,
                SUM(total_interactions) as total_interactions,
                SUM(duration_seconds) as total_duration,
                AVG(understanding_level) as avg_understanding,
                MIN(created_at) as first_session,
                MAX(updated_at) as last_activity
            FROM live_ai_sessions 
            WHERE user_id = %s
        """
        
        connection = get_cached_connection()
        cursor = connection.cursor()
        cursor.execute(stats_query, (user_id,))
        stats_result = cursor.fetchall()
        
        if not stats_result:
            cursor.close()
            connection.close()
            return format_api_response({
                'total_sessions': 0,
                'active_sessions': 0,
                'total_interactions': 0,
                'total_duration': 0,
                'avg_understanding': 0.5,
                'first_session': None,
                'last_activity': None
            }, include_legacy_fields={
                'total_sessions': 0,
                'active_sessions': 0,
                'total_interactions': 0,
                'total_duration': 0,
                'avg_understanding': 0.5,
                'first_session': None,
                'last_activity': None
            })
        
        stats_row = stats_result[0]
        
        # Get language distribution
        language_query = """
            SELECT language_name, COUNT(*) as count
            FROM live_ai_sessions 
            WHERE user_id = %s
            GROUP BY language_name
            ORDER BY count DESC
        """
        
        cursor.execute(language_query, (user_id,))
        language_result = cursor.fetchall()
        
        language_distribution = {}
        for lang_row in language_result:
            language_distribution[lang_row[0]] = lang_row[1]
        
        # Get recent activity (sessions in last 7 days)
        recent_activity_query = """
            SELECT DATE(created_at) as date, COUNT(*) as sessions
            FROM live_ai_sessions 
            WHERE user_id = %s AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
            GROUP BY DATE(created_at)
            ORDER BY date DESC
        """
        
        cursor.execute(recent_activity_query, (user_id,))
        recent_activity_result = cursor.fetchall()
        
        recent_activity = {}
        for activity_row in recent_activity_result:
            recent_activity[activity_row[0]] = activity_row[1]
        
        cursor.close()
        connection.close()
        
        stats_data = {
            'total_sessions': stats_row[0] or 0,
            'active_sessions': stats_row[1] or 0,
            'total_interactions': stats_row[2] or 0,
            'total_duration_seconds': stats_row[3] or 0,
            'avg_understanding_level': float(stats_row[4] or 0.5),
            'first_session': stats_row[5].isoformat() if stats_row[5] else None,
            'last_activity': stats_row[6].isoformat() if stats_row[6] else None,
            'language_distribution': language_distribution,
            'recent_activity': recent_activity
        }
        return format_api_response(stats_data, include_legacy_fields=stats_data)
        
    except Exception as e:
        logger.error(f"Error getting user session stats: {e}")
        return create_error_response(str(e), 500)
    finally:
        # CRITICAL: Always close cursor and connection on error
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except:
                pass
        if 'connection' in locals() and connection:
            try:
                connection.close()
            except:
                pass

# Initialize tables when module is imported
try:
    init_live_ai_sessions_tables()
except Exception as e:
    logger.error(f"Failed to initialize live AI sessions tables: {e}")
