"""
Analytics and learning insights routes
"""
import json
import logging
from datetime import datetime
from flask import Blueprint, request, jsonify
from utils import (require_api_key, encrypt_response, format_api_response, 
                   create_error_response, get_db_connection, fetch_as_dict, convert_analytics_numbers)
from auth.jwt_manager import require_auth, require_user_access, optional_auth
from flask import g


logger = logging.getLogger(__name__)
analytics_bp = Blueprint('analytics', __name__)


def update_user_analytics_summary(cursor, user_id, quiz_data):
    """Helper function to update user analytics summary"""
    try:
        # Check if user analytics record exists
        cursor.execute("SELECT id FROM user_analytics_summary WHERE user_id = %s", (user_id,))
        exists = cursor.fetchone()
        
        if exists:
            # Update existing record
            update_query = """
            UPDATE user_analytics_summary SET
                total_quizzes = total_quizzes + 1,
                total_questions = total_questions + %s,
                total_correct = total_correct + %s,
                total_time_spent = total_time_spent + %s,
                avg_score = (SELECT AVG(score_percentage) FROM quiz_sessions WHERE user_id = %s),
                last_activity = NOW(),
                updated_at = NOW()
            WHERE user_id = %s
            """
            cursor.execute(update_query, (
                quiz_data['total_questions'],
                quiz_data['correct_answers'],
                quiz_data.get('time_taken', 0),
                user_id,
                user_id
            ))
        else:
            # Create new record
            insert_query = """
            INSERT INTO user_analytics_summary (
                user_id, total_quizzes, total_questions, total_correct,
                total_time_spent, avg_score, last_activity, created_at, updated_at
            ) VALUES (%s, 1, %s, %s, %s, %s, NOW(), NOW(), NOW())
            """
            cursor.execute(insert_query, (
                user_id,
                quiz_data['total_questions'],
                quiz_data['correct_answers'],
                quiz_data.get('time_taken', 0),
                quiz_data['score_percentage']
            ))
    except Exception as e:
        logger.error(f"Error updating user analytics summary: {str(e)}")


def generate_learning_insights(time_patterns, improvement_trends):
    """Generate AI-powered learning insights"""
    insights = []
    
    if time_patterns:
        # Find best performance time
        best_time = max(time_patterns, key=lambda x: x['avg_performance'])
        insights.append({
            'type': 'optimal_time',
            'title': 'Optimal Study Time',
            'message': f"You perform best at {best_time['study_hour']}:00 with an average score of {best_time['avg_performance']:.1f}%",
            'recommendation': f"Try scheduling your most challenging quizzes around {best_time['study_hour']}:00"
        })
    
    # Add more insight logic here
    return insights


def generate_recommendations(user_id, cursor):
    """Generate personalized recommendations"""
    recommendations = []
    
    # Get recent performance
    cursor.execute("""
        SELECT AVG(score_percentage) as recent_avg 
        FROM quiz_sessions 
        WHERE user_id = %s 
        AND completed_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
    """, (user_id,))
    
    result = cursor.fetchone()
    if result and result[0]:
        recent_avg = result[0]
        if recent_avg < 70:
            recommendations.append({
                'type': 'improvement',
                'priority': 'high',
                'title': 'Focus on Fundamentals',
                'message': 'Your recent scores suggest focusing on core concepts',
                'action': 'Review topic materials before attempting more quizzes'
            })
    
    return recommendations


@analytics_bp.route('/api/analytics/learning-patterns', methods=['GET', 'OPTIONS'])
@require_api_key
@encrypt_response
def get_learning_patterns():
    """Get user learning patterns data for analytics"""
    # Handle OPTIONS request for CORS preflight
    if request.method == 'OPTIONS':
        response = jsonify({'success': True})
        response.headers.add('Access-Control-Allow-Origin', '*')
        response.headers.add('Access-Control-Allow-Methods', 'GET, OPTIONS')
        response.headers.add('Access-Control-Allow-Headers', 'Content-Type, api_key')
        return response, 200
        
    try:
        user_id = request.args.get('user_id')
        
        if not user_id:
            return create_error_response("User ID is required", 400)
        
        # Connect to the database using context manager to ensure proper cleanup
        from database import ManagedDBConnection, get_cached_connection
        
        # Initialize patterns data structure
        patterns_data = {}
        
        # Use context manager to ensure connection is always closed
        with ManagedDBConnection(get_cached_connection()) as conn:
            cursor = conn.cursor(dictionary=True)
            
            # 1. Try to get daily activity (minutes spent per day of week)
            try:
                cursor.execute("""
                    SELECT 
                        DAYOFWEEK(date_watched) as day_of_week,
                        SUM(time_watched) as total_minutes
                    FROM 
                        recently_watched
                    WHERE 
                        user_id = %s
                        AND date_watched >= DATE_SUB(NOW(), INTERVAL 30 DAY)
                    GROUP BY 
                        DAYOFWEEK(date_watched)
                    ORDER BY 
                        day_of_week
                """, (user_id,))
                
                daily_activity = cursor.fetchall()
                
                # Create a dictionary mapping day numbers to day labels
                day_labels = {1: 'Sun', 2: 'Mon', 3: 'Tue', 4: 'Wed', 5: 'Thu', 6: 'Fri', 7: 'Sat'}
                
                # Initialize daily activity with default values
                day_values = {'Mon': 0, 'Tue': 0, 'Wed': 0, 'Thu': 0, 'Fri': 0, 'Sat': 0, 'Sun': 0}
                
                # Fill in actual values from the database
                if daily_activity:
                    for row in daily_activity:
                        if row['day_of_week'] in day_labels:
                            day_values[day_labels[row['day_of_week']]] = int(row['total_minutes'] or 0)
            except Exception as e:
                print(f"Error getting daily activity: {e}")
                # Fall back to mock data
                day_values = {'Mon': 45, 'Tue': 70, 'Wed': 30, 'Thu': 55, 'Fri': 85, 'Sat': 65, 'Sun': 20}
            
        patterns_data['dailyActivity'] = {
            'labels': list(day_values.keys()),
            'data': list(day_values.values())
        }
        
        # 2. Try to get preferred learning times (morning, afternoon, evening, night)
        try:
            cursor.execute("""
                SELECT 
                    CASE 
                        WHEN HOUR(time_start) BETWEEN 5 AND 11 THEN 'Morning'
                        WHEN HOUR(time_start) BETWEEN 12 AND 16 THEN 'Afternoon'
                        WHEN HOUR(time_start) BETWEEN 17 AND 20 THEN 'Evening'
                        ELSE 'Night'
                    END as time_of_day,
                    COUNT(*) as count
                FROM 
                    recently_watched
                WHERE 
                    user_id = %s
                    AND date_watched >= DATE_SUB(NOW(), INTERVAL 30 DAY)
                GROUP BY 
                    time_of_day
            """, (user_id,))
            
            time_of_day = cursor.fetchall()
            
            # Initialize with default values
            time_values = {'Morning': 0, 'Afternoon': 0, 'Evening': 0, 'Night': 0}
            
            # Fill in actual values from the database
            if time_of_day:
                for row in time_of_day:
                    if row['time_of_day'] in time_values:
                        time_values[row['time_of_day']] = int(row['count'] or 0)
        except Exception as e:
            print(f"Error getting preferred learning times: {e}")
            # Fall back to mock data
            time_values = {'Morning': 15, 'Afternoon': 35, 'Evening': 40, 'Night': 10}
        
        patterns_data['preferredLearningTimes'] = {
            'labels': list(time_values.keys()),
            'data': list(time_values.values())
        }
        
        # 3. Try to get completion rates (weekly)
        try:
            cursor.execute("""
                SELECT 
                    CONCAT('Week ', WEEK(date_watched)) as week_label,
                    ROUND(AVG(watched_percentage), 0) as avg_completion
                FROM 
                    recently_watched
                WHERE 
                    user_id = %s
                    AND date_watched >= DATE_SUB(NOW(), INTERVAL 4 WEEK)
                GROUP BY 
                    WEEK(date_watched)
                ORDER BY 
                    WEEK(date_watched) ASC
                LIMIT 4
            """, (user_id,))
            
            weekly_completion = cursor.fetchall()
            
            # Create default weeks if we have less than 4 weeks of data
            week_labels = []
            week_data = []
            
            if weekly_completion and len(weekly_completion) > 0:
                for row in weekly_completion:
                    week_labels.append(row['week_label'])
                    week_data.append(int(row['avg_completion'] or 0))
                
                # Fill remaining weeks with zeroes if needed
                while len(week_labels) < 4:
                    next_week_num = len(week_labels) + 1
                    week_labels.append(f'Week {next_week_num}')
                    week_data.append(0)
            else:
                # No data, use placeholder values
                week_labels = ['Week 1', 'Week 2', 'Week 3', 'Week 4']
                week_data = [60, 45, 75, 85]  # Default values
        except Exception as e:
            print(f"Error getting completion rates: {e}")
            # Fall back to mock data
            week_labels = ['Week 1', 'Week 2', 'Week 3', 'Week 4']
            week_data = [60, 45, 75, 85]
        
        patterns_data['completionRates'] = {
            'weekly': {
                'labels': week_labels,
                'data': week_data
            }
        }
        
        # 4. Try to get skill improvement data
        try:
            cursor.execute("""
                SELECT 
                    s.name as skill_name,
                    COALESCE(AVG(e.score), 0) as skill_level
                FROM 
                    skills s
                LEFT JOIN 
                    evaluations e ON s.id = e.skill_id AND e.user_id = %s
                WHERE
                    s.name IN ('HTML', 'CSS', 'JavaScript', 'React', 'Node.js', 'SQL')
                GROUP BY 
                    s.name
            """, (user_id,))
            
            skill_data = cursor.fetchall()
            
            # Default skills
            skill_names = ['HTML', 'CSS', 'JavaScript', 'React', 'Node.js', 'SQL']
            skill_levels = []
            
            if skill_data and len(skill_data) > 0:
                # Create a mapping of skill names to levels
                skill_map = {row['skill_name']: int(row['skill_level'] or 0) for row in skill_data}
                
                # Fill in the values, using default values for any missing skills
                for skill in skill_names:
                    if skill in skill_map:
                        skill_levels.append(skill_map[skill])
                    else:
                        # Default values for missing skills
                        if skill == 'HTML': 
                            skill_levels.append(80)
                        elif skill == 'CSS': 
                            skill_levels.append(65)
                        elif skill == 'JavaScript': 
                            skill_levels.append(50)
                        elif skill == 'React': 
                            skill_levels.append(45)
                        elif skill == 'Node.js': 
                            skill_levels.append(30)
                        else:
                            skill_levels.append(55)
            else:
                # No data, use placeholder values
                skill_levels = [80, 65, 50, 45, 30, 55]
        except Exception as e:
            print(f"Error getting skill improvement data: {e}")
            # Fall back to mock data
            skill_levels = [80, 65, 50, 45, 30, 55]
        
            patterns_data['skillImprovement'] = {
                'labels': skill_names,
                'data': skill_levels
            }
            
            # Close cursor before exiting context
            cursor.close()
        
        # Ensure format_api_response returns a valid dict
        formatted_data = {
            "success": True,
            "message": "Success",
            "data": patterns_data
        }
        
        return format_api_response(patterns_data, include_legacy_fields=formatted_data)
    except Exception as e:
        print(f"Error in get_learning_patterns: {e}")
        logger.error("Error fetching learning patterns: {}".format(str(e)))
        return create_error_response(str(e), 500)


@analytics_bp.route('/api/analytics/recommendations', methods=['GET', 'OPTIONS'])
@require_api_key
@encrypt_response
def get_personalized_recommendations():
    """Get personalized course and skill recommendations for a user"""
    # Handle OPTIONS request for CORS preflight
    if request.method == 'OPTIONS':
        response = jsonify({'success': True})
        response.headers.add('Access-Control-Allow-Origin', '*')
        response.headers.add('Access-Control-Allow-Methods', 'GET, OPTIONS')
        response.headers.add('Access-Control-Allow-Headers', 'Content-Type, api_key')
        return response, 200
        
    try:
        user_id = request.args.get('user_id')
        
        if not user_id:
            return create_error_response("User ID is required", 400)
            
        # Connect to database using context manager to ensure proper cleanup
        from database import ManagedDBConnection, get_cached_connection
        
        with ManagedDBConnection(get_cached_connection()) as conn:
            cursor = conn.cursor(dictionary=True)
            
            # Initialize recommendations data structure
            recommendations_data = {
            "courseRecommendations": [],
            "skillGaps": [],
            "careerPathInsights": {
                "currentPath": "Frontend Developer",
                "nextMilestone": "Full-Stack Developer",
                "requiredSkills": ["Node.js", "Express", "MongoDB", "API Design"],
                "completion": 65
            }
        }
        
        # 1. Try to get recommended courses based on user's history and interests
        try:
            # First, get courses user is already enrolled in
            cursor.execute("""
                SELECT course_id FROM enrollments WHERE user_id = %s
            """, (user_id,))
            
            enrolled_courses = [row['course_id'] for row in cursor.fetchall()]
            enrolled_courses_str = ','.join(str(c) for c in enrolled_courses) if enrolled_courses else '0'
            
            # Get recommended courses based on tags/categories similar to user's enrolled courses
            cursor.execute("""
                SELECT 
                    c.id, 
                    c.title, 
                    c.description, 
                    c.level_name as level,
                    ROUND(SUM(v.video_duration) / 60) as total_hours,
                    ROUND(RAND() * 20 + 75) as match_score,
                    c.category
                FROM 
                    courses c
                JOIN
                    videos v ON c.id = v.course_id
                WHERE 
                    c.id NOT IN ({})
                    AND c.is_active = 1
                    AND c.category IN (
                        SELECT DISTINCT category FROM courses WHERE id IN ({})
                    )
                GROUP BY
                    c.id, c.title, c.description, c.level_name, c.category
                ORDER BY 
                    match_score DESC
                LIMIT 3
            """.format(enrolled_courses_str, enrolled_courses_str if enrolled_courses else '0'))
            
            recommended_courses = cursor.fetchall()
            
            # If no courses found based on categories, get top rated courses instead
            if not recommended_courses:
                cursor.execute("""
                    SELECT 
                        c.id, 
                        c.title, 
                        c.description, 
                        c.level_name as level,
                        ROUND(SUM(v.video_duration) / 60) as total_hours,
                        ROUND(RAND() * 20 + 75) as match_score,
                        c.category
                    FROM 
                        courses c
                    JOIN
                        videos v ON c.id = v.course_id
                    WHERE 
                        c.id NOT IN ({})
                        AND c.is_active = 1
                    GROUP BY
                        c.id, c.title, c.description, c.level_name, c.category
                    ORDER BY 
                        c.rating DESC, match_score DESC
                    LIMIT 3
                """.format(enrolled_courses_str))
                
                recommended_courses = cursor.fetchall()
            
            # Format course recommendations
            for i, course in enumerate(recommended_courses):
                # Create relevance message based on the course's category
                category = course.get('category', 'Web Development')
                if i == 0:
                    relevance = f"Based on your interest in {category}"
                elif i == 1:
                    relevance = f"Recommended to expand your {category} skills"
                else:
                    relevance = f"Popular course in {category}"
                
                recommendations_data["courseRecommendations"].append({
                    "id": course.get('id', i + 1),
                    "title": course.get('title', f"Course {i + 1}"),
                    "matchScore": course.get('match_score', 80 + i * 5),
                    "description": course.get('description', f"Description for course {i + 1}"),
                    "level": course.get('level', "Intermediate"),
                    "duration": f"{course.get('total_hours', 12)} hours",
                    "relevance": relevance
                })
        except Exception as e:
            print(f"Error getting course recommendations: {e}")
        
        # Add default recommendations if none found
        if not recommendations_data["courseRecommendations"]:
            recommendations_data["courseRecommendations"] = [
                {
                    "id": 1,
                    "title": "Advanced React Patterns",
                    "matchScore": 92,
                    "description": "Learn advanced design patterns used in large-scale React applications.",
                    "level": "Advanced",
                    "duration": "12 hours",
                    "relevance": "Based on your interest in React fundamentals and component architecture"
                },
                {
                    "id": 2,
                    "title": "Full-Stack Development with Next.js",
                    "matchScore": 87,
                    "description": "Build full-stack applications with Next.js, MongoDB, and Tailwind CSS.",
                    "level": "Intermediate",
                    "duration": "20 hours",
                    "relevance": "Complements your JavaScript and React skills"
                },
                {
                    "id": 3,
                    "title": "Data Visualization with D3.js",
                    "matchScore": 75,
                    "description": "Master interactive data visualizations using D3.js and SVG.",
                    "level": "Intermediate",
                    "duration": "14 hours",
                    "relevance": "Recommended based on your interest in frontend development"
                }
            ]
        
        # 2. Try to identify skill gaps based on quiz performance and course completion
        try:
            # Check if evaluations table exists
            cursor.execute("""
                SELECT DISTINCT
                    s.name as skill_name,
                    CASE
                        WHEN AVG(COALESCE(e.score, 0)) < 40 THEN 'You need more practice in this area'
                        WHEN AVG(COALESCE(e.score, 0)) < 70 THEN 'Could use additional coursework'
                        ELSE 'Ready for advanced topics'
                    END as gap_description,
                    CASE
                        WHEN AVG(COALESCE(e.score, 0)) < 40 THEN CONCAT('Take introductory courses on ', s.name)
                        WHEN AVG(COALESCE(e.score, 0)) < 70 THEN CONCAT('Practice with more ', s.name, ' projects')
                        ELSE CONCAT('Explore expert-level ', s.name, ' techniques')
                    END as recommendation
                FROM 
                    skills s
                LEFT JOIN 
                    evaluations e ON s.id = e.skill_id AND e.user_id = %s
                GROUP BY 
                    s.name
                HAVING 
                    AVG(COALESCE(e.score, 0)) < 70
                LIMIT 2
            """, (user_id,))
            
            skill_gaps = cursor.fetchall()
            
            # Format skill gaps
            for gap in skill_gaps:
                recommendations_data["skillGaps"].append({
                    "skill": gap['skill_name'],
                    "gap": gap['gap_description'],
                    "recommendedAction": gap['recommendation']
                })
        except Exception as e:
            print(f"Error identifying skill gaps: {e}")
        
        # If no skill gaps were found from database, provide default gaps
        if not recommendations_data["skillGaps"]:
            recommendations_data["skillGaps"] = [
                {
                    "skill": "TypeScript",
                    "gap": "Your JavaScript courses would be enhanced with TypeScript knowledge",
                    "recommendedAction": "Consider taking Introduction to TypeScript course"
                },
                {
                    "skill": "Testing",
                    "gap": "No courses completed on software testing methodologies",
                    "recommendedAction": "Explore courses on Testing with Jest or React Testing Library"
                }
            ]
        
            # Close cursor before exiting context
            cursor.close()
        
        # Ensure format_api_response returns a valid dict
        formatted_data = {
            "success": True,
            "message": "Success",
            "data": recommendations_data
        }
        
        return format_api_response(recommendations_data, include_legacy_fields=formatted_data)
    except Exception as e:
        print(f"Error in get_personalized_recommendations: {e}")
        logger.error("Error fetching personalized recommendations: {}".format(str(e)))
        return create_error_response(str(e), 500)


@analytics_bp.route('/api/analytics/user-stats', methods=['GET', 'OPTIONS'])
@require_api_key
@require_auth
@require_user_access
@encrypt_response
def get_user_analytics():
    """Get overall user analytics data"""
    # Handle OPTIONS request for CORS preflight
    if request.method == 'OPTIONS':
        response = jsonify({'success': True})
        response.headers.add('Access-Control-Allow-Origin', '*')
        response.headers.add('Access-Control-Allow-Methods', 'GET, OPTIONS')
        response.headers.add('Access-Control-Allow-Headers', 'Content-Type, api_key, Authorization')
        return response, 200
        
    try:
        user_id = g.current_user_id  # Get from JWT token instead of request
        
        if not user_id:
            return create_error_response("User ID is required", 400)
            
        # Connect to database using context manager to ensure proper cleanup
        from database import ManagedDBConnection, get_cached_connection
        
        with ManagedDBConnection(get_cached_connection()) as conn:
            cursor = conn.cursor(dictionary=True)
            
            # 1. Get user information
        cursor.execute("""
            SELECT id, fullname as name
            FROM user
            WHERE id = %s
        """, (user_id,))
        
        user_info = cursor.fetchone()
        
        if not user_info:
            # Fall back to mock data if user not found
            user_data = {
                "id": int(user_id),
                "name": "Student",
                "learningStats": {
                    "totalCoursesEnrolled": 5,
                    "totalCoursesCompleted": 2,
                    "totalHoursLearned": 48.5,
                    "totalQuizzesTaken": 12,
                    "averageQuizScore": 78,
                    "activeDays": 24,
                    "currentStreak": 3,
                    "bestStreak": 7,
                    "totalCertificatesEarned": 1
                },
                "recentCourseActivity": [
                    {
                        "courseId": 101,
                        "title": "Web Development Fundamentals",
                        "lastAccessed": "2023-08-12T15:30:00Z",
                        "progress": 85,
                        "nextLesson": "CSS Flexbox Layout"
                    },
                    {
                        "courseId": 103,
                        "title": "JavaScript Advanced Concepts",
                        "lastAccessed": "2023-08-10T18:15:00Z",
                        "progress": 32,
                        "nextLesson": "Promises and Async/Await"
                    },
                    {
                        "courseId": 102,
                        "title": "React for Beginners",
                        "lastAccessed": "2023-08-08T14:00:00Z",
                        "progress": 45,
                        "nextLesson": "State Management with Hooks"
                    }
                ]
            }
        else:
            # Get real data for the user
            user_data = {
                "id": user_info['id'],
                "name": user_info['name'] or "Student",
                "learningStats": {},
                "recentCourseActivity": []
            }
        
            # Close cursor before exiting context
            cursor.close()
        
        # Ensure format_api_response returns a valid dict
        formatted_data = {
            "success": True,
            "message": "Success",
            "data": user_data
        }
        
        return format_api_response(user_data, include_legacy_fields=formatted_data)
    except Exception as e:
        print(f"Error in get_user_analytics: {e}")
        logger.error("Error fetching user analytics data: {}".format(str(e)))
        return create_error_response(str(e), 500)


@analytics_bp.route('/api/analytics/user-progress/<user_id>', methods=['GET'])
@require_api_key
@encrypt_response
def get_user_progress_analytics(user_id):
    """
    Get comprehensive user progress analytics
    """
    try:
        with get_db_connection() as conn:
            cursor = conn.cursor()

            # Get overall user stats
            overall_stats_query = """
            SELECT 
                COUNT(*) as total_quizzes,
                AVG(score_percentage) as avg_score,
                SUM(total_questions) as total_questions_attempted,
                SUM(correct_answers) as total_correct_answers,
                SUM(time_taken) as total_time_spent,
                MAX(completed_at) as last_quiz_date,
                MIN(completed_at) as first_quiz_date
            FROM quiz_sessions 
            WHERE user_id = %s
            """
            cursor.execute(overall_stats_query, (user_id,))
            overall_stats_result = fetch_as_dict(cursor)
            overall_stats = overall_stats_result[0] if overall_stats_result and len(overall_stats_result) > 0 else {}

            # Get performance by quiz type (including model sets)
            performance_by_type_query = """
            SELECT 
                quiz_type,
                COUNT(*) as quiz_count,
                AVG(score_percentage) as avg_score,
                MAX(score_percentage) as best_score,
                MIN(score_percentage) as worst_score,
                AVG(CASE WHEN total_marks_possible > 0 THEN (marks_obtained / total_marks_possible) * 100 ELSE score_percentage END) as avg_marks_percentage
            FROM quiz_sessions 
            WHERE user_id = %s 
            GROUP BY quiz_type
            """
            cursor.execute(performance_by_type_query, (user_id,))
            performance_by_type = fetch_as_dict(cursor)

            # Get recent quiz sessions (last 10) - including model sets
            recent_sessions_query = """
            SELECT 
                qs.id as session_id, 
                qs.quiz_type, 
                qs.course_id, 
                qs.score_percentage, 
                qs.total_questions,
                qs.correct_answers, 
                qs.time_taken, 
                qs.completed_at,
                qs.modelset_id,
                qs.marks_obtained,
                qs.total_marks_possible,
                m.title as model_set_title
            FROM quiz_sessions qs
            LEFT JOIN modelset m ON qs.modelset_id = m.id
            WHERE qs.user_id = %s 
            ORDER BY qs.completed_at DESC 
            LIMIT 10
            """
            cursor.execute(recent_sessions_query, (user_id,))
            recent_sessions = fetch_as_dict(cursor)

            # Get topic-wise performance - match old format exactly
            topic_performance_query = """
            SELECT 
                qr.topic_id,
                t.title AS topic_title,
                t.parent_topic_id,
                t.level,
                t.full_path,
                COUNT(*) as questions_attempted,
                SUM(CASE WHEN qr.is_correct = 1 THEN 1 ELSE 0 END) as correct_answers,
                AVG(CASE WHEN qr.is_correct = 1 THEN 1.0 ELSE 0.0 END) * 100 as accuracy_percentage,
                AVG(qr.time_taken) as avg_time_per_question
            FROM quiz_question_responses qr
            JOIN quiz_sessions qs ON qr.session_id = qs.id
            LEFT JOIN topics t ON qr.topic_id = t.id
            WHERE qs.user_id = %s AND qr.topic_id IS NOT NULL
            GROUP BY qr.topic_id, t.title, t.parent_topic_id, t.level, t.full_path
            ORDER BY t.level, t.parent_topic_id, accuracy_percentage DESC
            """
            cursor.execute(topic_performance_query, (user_id,))
            topic_performance = fetch_as_dict(cursor)

            # Get learning progress over time (last 30 days) - match old format exactly
            progress_over_time_query = """
            SELECT 
                DATE(completed_at) as quiz_date,
                COUNT(*) as quizzes_taken,
                AVG(score_percentage) as avg_score
            FROM quiz_sessions 
            WHERE user_id = %s 
            AND completed_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
            GROUP BY DATE(completed_at)
            ORDER BY quiz_date
            """
            cursor.execute(progress_over_time_query, (user_id,))
            progress_over_time = fetch_as_dict(cursor)

            # Get weak areas (topics with low accuracy) - match old format exactly
            weak_areas_query = """
            SELECT 
                qr.topic_id,
                t.title AS topic_title,
                t.parent_topic_id,
                t.level,
                t.full_path,
                COUNT(*) as questions_attempted,
                AVG(CASE WHEN qr.is_correct = 1 THEN 1.0 ELSE 0.0 END) * 100 as accuracy_percentage
            FROM quiz_question_responses qr
            JOIN quiz_sessions qs ON qr.session_id = qs.id
            LEFT JOIN topics t ON qr.topic_id = t.id
            WHERE qs.user_id = %s AND qr.topic_id IS NOT NULL
            GROUP BY qr.topic_id, t.title, t.parent_topic_id, t.level, t.full_path
            HAVING accuracy_percentage < 70 AND questions_attempted >= 3
            ORDER BY accuracy_percentage ASC
            LIMIT 5
            """
            cursor.execute(weak_areas_query, (user_id,))
            weak_areas = fetch_as_dict(cursor)

            # --- NEW: Get AI Learning Insights ---
            cursor.execute("""
                SELECT title, message, recommendation, insight_type, priority, created_at
                FROM user_learning_insights
                WHERE user_id = %s
                ORDER BY created_at DESC
                LIMIT 10
            """, (user_id,))
            learning_insights = fetch_as_dict(cursor)

            # --- NEW: Get Streak Calendar Data ---
            cursor.execute("""
                SELECT streak_date, quizzes_taken, total_time, avg_score
                FROM user_learning_streaks
                WHERE user_id = %s
                AND streak_date >= DATE_SUB(NOW(), INTERVAL 365 DAY)
                ORDER BY streak_date ASC
            """, (user_id,))
            streak_calendar = fetch_as_dict(cursor)

            # Convert all numeric data to proper types
            response_data = {
                'user_id': user_id,
                'generated_at': datetime.now().isoformat(),
                "overall_stats": convert_analytics_numbers(overall_stats),
                "performance_by_type": convert_analytics_numbers(performance_by_type),
                "recent_sessions": convert_analytics_numbers(recent_sessions),
                "topic_performance": convert_analytics_numbers(topic_performance),
                "progress_over_time": convert_analytics_numbers(progress_over_time),
                "weak_areas": convert_analytics_numbers(weak_areas),
                "learning_insights": convert_analytics_numbers(learning_insights),
                "streak_calendar": convert_analytics_numbers(streak_calendar)
            }

            return format_api_response(
                data=response_data,
                message="User progress analytics retrieved successfully"
            )

    except Exception as e:
        logger.error(f"Error in get_user_progress_analytics: {str(e)}")
        return create_error_response(f"Failed to get analytics: {str(e)}", 500)


@analytics_bp.route('/api/analytics/learning-insights/<user_id>', methods=['GET'])
@require_api_key
@encrypt_response
def get_learning_insights(user_id):
    """
    Get AI-powered learning insights and recommendations
    """
    try:
        with get_db_connection() as conn:
            cursor = conn.cursor()

            # Get detailed performance patterns
            patterns_query = """
            SELECT 
                HOUR(qs.completed_at) as study_hour,
                AVG(qs.score_percentage) as avg_performance,
                COUNT(*) as session_count
            FROM quiz_sessions qs
            WHERE qs.user_id = %s
            GROUP BY HOUR(qs.completed_at)
            ORDER BY avg_performance DESC
            """
            cursor.execute(patterns_query, (user_id,))
            time_patterns = fetch_as_dict(cursor)

            # Get improvement trends
            improvement_query = """
            SELECT 
                quiz_type,
                DATE(completed_at) as quiz_date,
                AVG(score_percentage) as daily_avg_score
            FROM quiz_sessions 
            WHERE user_id = %s 
            AND completed_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
            GROUP BY quiz_type, DATE(completed_at)
            ORDER BY quiz_type, quiz_date
            """
            cursor.execute(improvement_query, (user_id,))
            improvement_trends = fetch_as_dict(cursor)

            # Generate insights based on data
            insights = generate_learning_insights(time_patterns, improvement_trends)
            
            # Convert all numeric data to proper types
            response_data = {
                'user_id': user_id,
                'time_patterns': convert_analytics_numbers(time_patterns),
                'improvement_trends': convert_analytics_numbers(improvement_trends),
                'insights': insights,
                'recommendations': generate_recommendations(user_id, cursor)
            }
            
            return format_api_response(
                data=response_data,
                message="Learning insights retrieved successfully",
                include_legacy_fields=response_data
            )

    except Exception as e:
        logger.error(f"Error getting learning insights: {str(e)}")
        return create_error_response(f"Failed to get insights: {str(e)}")


@analytics_bp.route('/api/analytics/quiz-session/<session_id>', methods=['GET'])
@require_api_key
@encrypt_response
def get_quiz_session_detail(session_id):
    """
    Get detailed quiz session data including questions and user answers
    """
    try:
        user_id = request.args.get('user_id')
        if not user_id:
            return create_error_response("User ID is required", 400)

        with get_db_connection() as conn:
            cursor = conn.cursor()

            # Try direct session ID lookup first
            session_query = """
            SELECT 
                qs.id as session_id,
                qs.user_id,
                qs.quiz_type,
                qs.course_id,
                qs.modelset_id,
                qs.score_percentage,
                qs.total_questions,
                qs.correct_answers,
                qs.time_taken,
                qs.completed_at
            FROM quiz_sessions qs
            WHERE qs.user_id = %s AND qs.id = %s
            LIMIT 1
            """
            
            cursor.execute(session_query, (user_id, session_id))
            session_data = fetch_as_dict(cursor)
            
            # If not found by ID, try parsing synthetic session_id (quiz_type-timestamp)
            if not session_data:
                try:
                    parts = session_id.split('-')
                    if len(parts) >= 2:
                        quiz_type = parts[0]
                        timestamp = int(parts[-1])
                        # Convert timestamp back to datetime
                        session_date = datetime.fromtimestamp(timestamp / 1000)
                        
                        # Find session within a reasonable time window (±1 hour)
                        cursor.execute("""
                        SELECT 
                            qs.id as session_id,
                            qs.user_id,
                            qs.quiz_type,
                            qs.course_id,
                            qs.modelset_id,
                            qs.score_percentage,
                            qs.total_questions,
                            qs.correct_answers,
                            qs.time_taken,
                            qs.completed_at
                        FROM quiz_sessions qs
                        WHERE qs.user_id = %s 
                        AND qs.quiz_type = %s
                        AND ABS(TIMESTAMPDIFF(SECOND, qs.completed_at, %s)) < 3600
                        ORDER BY ABS(TIMESTAMPDIFF(SECOND, qs.completed_at, %s)) ASC
                        LIMIT 1
                        """, (user_id, quiz_type, session_date, session_date))
                        
                        session_data = fetch_as_dict(cursor)
                except (ValueError, IndexError):
                    pass
            
            if not session_data:
                logger.warning(f"Session with ID {session_id} not found for user {user_id}. This might be a data mismatch or the session belongs to another user.")
                
                # For debugging, let's see what sessions this user has
                debug_query = """
                SELECT id, quiz_type, completed_at 
                FROM quiz_sessions 
                WHERE user_id = %s 
                ORDER BY completed_at DESC 
                LIMIT 5
                """
                cursor.execute(debug_query, (user_id,))
                available_sessions = fetch_as_dict(cursor)
                logger.info(f"Available sessions for user {user_id}: {available_sessions}")
                
                return create_error_response(f"Quiz session {session_id} not found for this user. If you just finished a quiz, please try refreshing.", 404)
            
            session = session_data[0]
            actual_session_id = session['session_id']
            modelset_id = session.get('modelset_id')
            
            # Get detailed question data for this session
            # Check if this is a model set quiz or regular quiz
            if modelset_id:
                # Model set quiz - get from modelset_question_responses
                logger.info(f"Fetching model set question responses for session {actual_session_id}, modelset {modelset_id}")
                
                # First, try to get attempt_id from modelset_attempts
                # Try multiple strategies to find the correct attempt
                
                # Strategy 1: Match by session_id = quiz_id
                cursor.execute("""
                    SELECT id as attempt_id FROM modelset_attempts 
                    WHERE session_id = (SELECT quiz_id FROM quiz_sessions WHERE id = %s)
                    LIMIT 1
                """, (actual_session_id,))
                attempt_result = cursor.fetchone()
                
                # Strategy 2: If not found, match by modelset_id, user_id, and timestamp proximity
                if not attempt_result:
                    logger.info(f"Strategy 1 failed, trying strategy 2: matching by modelset_id and user_id")
                    cursor.execute("""
                        SELECT ma.id as attempt_id 
                        FROM modelset_attempts ma
                        INNER JOIN quiz_sessions qs ON qs.id = %s
                        WHERE ma.modelset_id = %s 
                        AND ma.user_id = %s
                        AND ABS(TIMESTAMPDIFF(SECOND, ma.completed_at, qs.completed_at)) < 60
                        ORDER BY ABS(TIMESTAMPDIFF(SECOND, ma.completed_at, qs.completed_at)) ASC
                        LIMIT 1
                    """, (actual_session_id, modelset_id, user_id))
                    attempt_result = cursor.fetchone()
                
                # Strategy 3: If still not found, get most recent attempt for this modelset and user
                if not attempt_result:
                    logger.info(f"Strategy 2 failed, trying strategy 3: getting most recent attempt")
                    cursor.execute("""
                        SELECT id as attempt_id 
                        FROM modelset_attempts 
                        WHERE modelset_id = %s AND user_id = %s
                        ORDER BY completed_at DESC
                        LIMIT 1
                    """, (modelset_id, user_id))
                    attempt_result = cursor.fetchone()
                
                if attempt_result:
                    attempt_id = attempt_result[0]
                    logger.info(f"Found attempt_id: {attempt_id} for session {actual_session_id}")
                    questions_query = """
                    SELECT 
                        mqr.question_id,
                        mqr.selected_option as user_selected_option_id,
                        mqr.correct_option,
                        mqr.is_correct as is_user_correct,
                        mqr.time_taken,
                        COALESCE(q.question_text, 'Question text not available') as question_text,
                        COALESCE(q.difficulty, 'medium') as difficulty,
                        COALESCE(q.explanation, '') as explanation,
                        COALESCE(q.topic_id, 0) as topic_id,
                        COALESCE(t.title, 'Topic') as topic_title,
                        q.correct_answer_index
                    FROM modelset_question_responses mqr
                    LEFT JOIN questions q ON mqr.question_id = q.id
                    LEFT JOIN topics t ON q.topic_id = t.id
                    WHERE mqr.attempt_id = %s
                    ORDER BY mqr.id ASC
                    """
                    cursor.execute(questions_query, (attempt_id,))
                    questions_data = fetch_as_dict(cursor)
                    logger.info(f"Retrieved {len(questions_data)} model set questions")
                else:
                    logger.warning(f"No modelset_attempts found for session {actual_session_id}")
                    questions_data = []
            else:
                # Regular quiz - get from quiz_question_responses
                questions_query = """
                SELECT 
                    qr.question_id,
                    qr.selected_option as user_selected_option_id,
                    qr.correct_option,
                    qr.is_correct as is_user_correct,
                    qr.time_taken,
                    COALESCE(q.question_text, 'Question text not available') as question_text,
                    COALESCE(q.difficulty, 'medium') as difficulty,
                    COALESCE(q.explanation, '') as explanation,
                    COALESCE(qr.topic_id, 0) as topic_id,
                    'Topic' as topic_title,
                    q.correct_answer_index
                FROM quiz_question_responses qr
                LEFT JOIN questions q ON BINARY qr.question_id = BINARY CAST(q.id AS CHAR)
                WHERE qr.session_id = %s
                ORDER BY qr.id ASC
                """
                
                cursor.execute(questions_query, (actual_session_id,))
                questions_data = fetch_as_dict(cursor)
            
            # Process questions with stored option data - match old format exactly
            detailed_questions = []
            for question in questions_data:
                # Try to get options from stored data first
                options_data = []
                
                try:
                    # Try to get options from questions table
                    if question.get('question_id'):
                        options_query = """
                        SELECT options 
                        FROM questions 
                        WHERE id = %s OR CAST(id AS CHAR) = %s
                        LIMIT 1
                        """
                        cursor.execute(options_query, (question['question_id'], question['question_id']))
                        options_result = cursor.fetchone()
                        
                        if options_result and options_result[0]:
                            try:
                                parsed_options = json.loads(options_result[0]) if isinstance(options_result[0], str) else options_result[0]
                                
                                # Transform options array into proper structure for frontend
                                if isinstance(parsed_options, list):
                                    options_data = []
                                    correct_index = question.get('correct_answer_index', 0)
                                    
                                    for i, option_text in enumerate(parsed_options):
                                        option_id = str(i)  # Use index as option_id
                                        is_correct = i == correct_index  # Use correct_answer_index from questions table
                                        
                                        options_data.append({
                                            'option_id': option_id,
                                            'option_text': str(option_text),
                                            'is_correct': is_correct
                                        })
                                else:
                                    options_data = []
                            except (json.JSONDecodeError, TypeError):
                                options_data = []
                        
                except Exception as e:
                    logger.warning(f"Error processing options for question {question.get('question_id')}: {e}")
                    # Minimal fallback
                    options_data = [
                        {
                            'option_id': 'selected',
                            'option_text': 'Your answer',
                            'is_correct': question.get('is_user_correct', False)
                        }
                    ]
                
                # Get the correct option ID - use correct_answer_index if available, otherwise correct_option
                correct_index = question.get('correct_answer_index')
                if correct_index is not None:
                    correct_option_id = str(correct_index)
                else:
                    correct_option_id = question.get('correct_option', '')
                
                detailed_questions.append({
                    'question_id': question['question_id'],
                    'question_text': question['question_text'],
                    'options': convert_analytics_numbers(options_data),
                    'user_selected_option_id': question['user_selected_option_id'],
                    'correct_option_id': correct_option_id,
                    'is_user_correct': bool(question['is_user_correct']),
                    'difficulty': question['difficulty'],
                    'explanation': question['explanation'],
                    'topic_title': question['topic_title'],
                    'topic_id': question['topic_id'],
                    'time_spent': question['time_taken']
                })
            
            # Convert session data
            converted_session = convert_analytics_numbers(session)
            
            response_data = {
                'session_id': str(actual_session_id),
                'user_id': converted_session['user_id'],
                'quiz_type': converted_session['quiz_type'],
                'course_id': converted_session.get('course_id'),
                'score_percentage': converted_session['score_percentage'],
                'total_questions': converted_session['total_questions'],
                'correct_answers': converted_session['correct_answers'],
                'time_taken': converted_session['time_taken'],
                'completed_at': converted_session['completed_at'],
                'questions': detailed_questions
            }
            
            return format_api_response(
                data=response_data,
                message="Quiz session details retrieved successfully",
                include_legacy_fields=response_data
            )
            
    except Exception as e:
        logger.error(f"Error getting quiz session detail: {str(e)}")
        return create_error_response(f"Failed to get quiz session details: {str(e)}")


@analytics_bp.route('/api/analytics/quiz-sessions/list/<user_id>', methods=['GET'])
@require_api_key
@encrypt_response
def list_user_quiz_sessions(user_id):
    """
    List all quiz sessions for a user (for debugging)
    """
    try:
        with get_db_connection() as conn:
            cursor = conn.cursor()
            
            # Pagination params
            try:
                limit = min(max(int(request.args.get('limit', 20)), 1), 200)
            except Exception:
                limit = 20
            try:
                page = max(int(request.args.get('page', 1)), 1)
            except Exception:
                page = 1
            offset = (page - 1) * limit

            # Total count for pagination
            cursor.execute("""
                SELECT COUNT(*) as total
                FROM quiz_sessions
                WHERE user_id = %s
            """, (user_id,))
            total_row = cursor.fetchone()
            total_count = total_row[0] if total_row is not None and isinstance(total_row, tuple) else (
                (total_row.get('total') if isinstance(total_row, dict) else 0)
            )

            # Paged sessions
            sessions_query = """
            SELECT 
                id, quiz_type, quiz_id, course_id, 
                score_percentage, total_questions, correct_answers,
                time_taken, completed_at, created_at
            FROM quiz_sessions 
            WHERE user_id = %s 
            ORDER BY completed_at DESC 
            LIMIT %s OFFSET %s
            """
            cursor.execute(sessions_query, (user_id, limit, offset))
            sessions = fetch_as_dict(cursor)
            
            sessions_data = {
                'user_id': user_id,
                'sessions': convert_analytics_numbers(sessions),
                'total_count': total_count,
                'page': page,
                'per_page': limit
            }
            return format_api_response(
                data=sessions_data,
                message="User sessions retrieved successfully",
                include_legacy_fields=sessions_data
            )
            
    except Exception as e:
        logger.error(f"Error listing quiz sessions: {str(e)}")
        return create_error_response(f"Failed to list quiz sessions: {str(e)}")


@analytics_bp.route('/api/analytics/leaderboard', methods=['GET', 'OPTIONS'])
# @require_auth
# @encrypt_response
def get_leaderboard():
    """
    Get leaderboard data with filtering options
    """
    # if request.method == 'OPTIONS':
    #     response = jsonify({'success': True})
    #     response.headers.add('Access-Control-Allow-Origin', '*')
    #     response.headers.add('Access-Control-Allow-Methods', 'GET, OPTIONS')
    #     response.headers.add('Access-Control-Allow-Headers', 'Content-Type, api_key')
        
    #     return response, 200
        
    try:
        # Get filter parameters
        filter_by = request.args.get('filter_by', 'avg_score')  # avg_score, total_quizzes, study_time, streak
        limit = min(int(request.args.get('limit', 10)), 50)  # Max 50 users
        time_period = request.args.get('time_period', 'all')  # all, week, month
        quiz_type_filter = request.args.get('quiz_type', 'all')  # all, topic_quiz, practice, model_set
        
        # Connect to database using context manager to ensure proper cleanup
        from database import ManagedDBConnection, get_cached_connection
        
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
            
            # Build filters
        filters = []
        if time_period == 'week':
            filters.append("qs.completed_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)")
        elif time_period == 'month':
            filters.append("qs.completed_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)")
        
        if quiz_type_filter != 'all':
            filters.append(f"qs.quiz_type = '{quiz_type_filter}'")
        
        time_filter = " AND ".join(filters)
        if time_filter:
            time_filter = "AND " + time_filter
        
        # Base query to get user statistics
        base_query = f"""
        SELECT 
            u.id as user_id,
            u.fullname as name,
            u.profile_img_url,
            COUNT(DISTINCT qs.id) as total_quizzes,
            ROUND(AVG(qs.score_percentage), 1) as avg_score,
            SUM(qs.time_taken) as total_study_time,
            MAX(qs.score_percentage) as best_score,
            COUNT(DISTINCT DATE(qs.completed_at)) as active_days,
            DATEDIFF(NOW(), MIN(qs.completed_at)) as days_since_first_quiz
        FROM user u
        LEFT JOIN quiz_sessions qs ON u.id = qs.user_id {time_filter}
        WHERE u.id IN (
            SELECT DISTINCT user_id FROM quiz_sessions 
            WHERE completed_at IS NOT NULL {time_filter}
        )
        GROUP BY u.id, u.fullname, u.profile_img_url
        HAVING total_quizzes > 0
        """
        
        # Add ordering based on filter
        if filter_by == 'avg_score':
            order_clause = "ORDER BY avg_score DESC, total_quizzes DESC"
        elif filter_by == 'total_quizzes':
            order_clause = "ORDER BY total_quizzes DESC, avg_score DESC"
        elif filter_by == 'study_time':
            order_clause = "ORDER BY total_study_time DESC, avg_score DESC"
        elif filter_by == 'streak':
            order_clause = "ORDER BY active_days DESC, avg_score DESC"
        else:
            order_clause = "ORDER BY avg_score DESC, total_quizzes DESC"
        
        final_query = f"{base_query} {order_clause} LIMIT %s"
        
        cursor.execute(final_query, [limit])
        leaderboard_data = cursor.fetchall()
        
        # Calculate streaks for each user (simplified - active days in last 30 days)
        for user in leaderboard_data:
            try:
                cursor.execute("""
                    SELECT COUNT(DISTINCT DATE(completed_at)) as streak
                    FROM quiz_sessions 
                    WHERE user_id = %s 
                    AND completed_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
                """, (user['user_id'],))
                
                streak_result = cursor.fetchone()
                user['current_streak'] = streak_result['streak'] if streak_result else 0
                
                # Format study time to hours and minutes
                total_minutes = user['total_study_time'] or 0
                hours = total_minutes // 60
                minutes = total_minutes % 60
                user['formatted_study_time'] = f"{hours}h {minutes}m"
                
                # Add ranking badge based on position
                user['rank'] = leaderboard_data.index(user) + 1
                if user['rank'] == 1:
                    user['badge'] = '🥇'
                elif user['rank'] == 2:
                    user['badge'] = '🥈'
                elif user['rank'] == 3:
                    user['badge'] = '🥉'
                else:
                    user['badge'] = f"#{user['rank']}"
                
            except Exception as e:
                logger.error(f"Error calculating streak for user {user['user_id']}: {e}")
                user['current_streak'] = 0
                user['formatted_study_time'] = "0h 0m"
                user['rank'] = leaderboard_data.index(user) + 1
                user['badge'] = f"#{user['rank']}"
        
        # Get total number of active users for context
        cursor.execute(f"""
            SELECT COUNT(DISTINCT qs.user_id) as total_active_users
            FROM quiz_sessions qs
            WHERE qs.completed_at IS NOT NULL {time_filter}
        """)
        
        total_users_result = cursor.fetchone()
        total_active_users = total_users_result['total_active_users'] if total_users_result else 0
        
        # Close cursor before exiting context
        cursor.close()
        conn.close()
        
        response_data = {
            'leaderboard': convert_analytics_numbers(leaderboard_data),
            'filter_applied': filter_by,
            'time_period': time_period,
            'quiz_type_filter': quiz_type_filter,
            'total_active_users': total_active_users,
            'showing_top': len(leaderboard_data),
            'generated_at': datetime.now().isoformat()
        }
        
        return format_api_response(
            data=response_data,
            message="Leaderboard retrieved successfully",
            include_legacy_fields=response_data
        )
        
    except Exception as e:
        logger.error(f"Error in get_leaderboard: {str(e)}")
        return create_error_response(f"Failed to get leaderboard: {str(e)}")


@analytics_bp.route('/api/analytics/user-profile-stats/<user_id>', methods=['GET', 'OPTIONS'])
@require_api_key
@optional_auth
def get_user_profile_stats(user_id):
    """Get user profile statistics including streak, rewards, and activity"""
    if request.method == 'OPTIONS':
        return '', 200
        
    try:
        with get_db_connection() as conn:
            cursor = conn.cursor(dictionary=True, buffered=True)
            
            # Get learning streak (consecutive days with activity)
            streak_query = """
            SELECT 
                COUNT(DISTINCT DATE(completed_at)) as learning_streak,
                MAX(completed_at) as last_activity
            FROM quiz_sessions 
            WHERE user_id = %s 
            AND completed_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
            """
            cursor.execute(streak_query, (user_id,))
            streak_data = cursor.fetchone() or {'learning_streak': 0, 'last_activity': None}
            
            # Get reward points (based on activity)
            rewards_query = """
            SELECT 
                COUNT(*) * 10 as quiz_points,
                COALESCE(SUM(score_percentage), 0) as score_points
            FROM quiz_sessions 
            WHERE user_id = %s AND completed_at IS NOT NULL
            """
            cursor.execute(rewards_query, (user_id,))
            rewards_data = cursor.fetchone() or {'quiz_points': 0, 'score_points': 0}
            
            reward_points = int(rewards_data['quiz_points'] + (rewards_data['score_points'] / 10))
            
            # Get course completion stats
            course_stats_query = """
            SELECT 
                COUNT(DISTINCT e.course_id) as total_courses,
                COUNT(DISTINCT CASE WHEN e.is_completed = 1 THEN e.course_id END) as completed_courses,
                AVG(e.progress) as avg_progress
            FROM enrollments e
            WHERE e.user_id = %s
            """
            cursor.execute(course_stats_query, (user_id,))
            course_stats = cursor.fetchone() or {'total_courses': 0, 'completed_courses': 0, 'avg_progress': 0}
            
            response_data = {
                'user_id': user_id,
                'learning_streak': streak_data['learning_streak'],
                'reward_points': reward_points,
                'total_courses': course_stats['total_courses'],
                'completed_courses': course_stats['completed_courses'],
                'avg_progress': round(course_stats['avg_progress'] or 0, 1),
                'last_activity': streak_data['last_activity'].isoformat() if streak_data['last_activity'] else None,
                'generated_at': datetime.now().isoformat()
            }
            
            return format_api_response(
                data=response_data,
                message="User profile stats retrieved successfully",
                include_legacy_fields=response_data
            )
            
    except Exception as e:
        logger.error(f"Error fetching user profile stats for user {user_id}: {e}")
        return create_error_response(
            message="Failed to fetch user profile statistics",
            status_code=500
        )

@analytics_bp.route('/api/analytics/debug/<user_id>', methods=['GET'])
@require_api_key
def debug_analytics(user_id):
    """
    Debug endpoint to check analytics data for a user
    """
    try:
        with get_db_connection() as conn:
            cursor = conn.cursor(dictionary=True)
            
            debug_info = {}
            
            # Check user_analytics_summary
            cursor.execute("SELECT * FROM user_analytics_summary WHERE user_id = %s", (user_id,))
            debug_info['user_summary'] = cursor.fetchone()
            
            # Check recent quiz sessions
            cursor.execute("""
                SELECT * FROM quiz_sessions 
                WHERE user_id = %s 
                ORDER BY completed_at DESC 
                LIMIT 5
            """, (user_id,))
            debug_info['recent_sessions'] = cursor.fetchall()
            
            # Check question responses for latest session
            if debug_info['recent_sessions']:
                latest_session_id = debug_info['recent_sessions'][0]['id']
                cursor.execute("""
                    SELECT * FROM quiz_question_responses 
                    WHERE session_id = %s
                """, (latest_session_id,))
                debug_info['latest_session_questions'] = cursor.fetchall()
            
            # Table existence check
            cursor.execute("SHOW TABLES LIKE 'quiz_%'")
            tables = cursor.fetchall()
            debug_info['analytics_tables'] = [list(table.values())[0] for table in tables]
            
            return format_api_response(
                data=debug_info,
                message="Debug information retrieved successfully",
                include_legacy_fields=debug_info
            )
            
    except Exception as e:
        logger.error(f"Debug analytics error: {str(e)}")
        return create_error_response(f"Debug error: {str(e)}")