
import logging
from datetime import datetime, timedelta
from database import get_db_connection

logger = logging.getLogger(__name__)

def update_user_analytics_summary():
    """
    Cron Job: Daily
    Aggregates user activity (video watch time, quizzes) and updates 'user_analytics_summary'.
    Calculates current and longest streaks.
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        logger.info("Starting User Analytics Summary Update...")
        
        # 1. Get all active users from recent activity (last 30 days to save resources)
        # Using UNION to get users active in either videos or quizzes
        cursor.execute("""
            SELECT DISTINCT user_id FROM recently_watched 
            WHERE time_watched > DATE_SUB(NOW(), INTERVAL 30 DAY)
            UNION
            SELECT DISTINCT user_id FROM quiz_sessions 
            WHERE created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)
        """)
        active_users = cursor.fetchall()
        
        for user_row in active_users:
            user_id = user_row['user_id']
            _process_single_user_analytics(cursor, user_id)
            
        conn.commit()
        logger.info(f"User Analytics Helper completed. Processed {len(active_users)} users.")
        return {"success": True, "processed_count": len(active_users)}
        
    except Exception as e:
        logger.error(f"Error in update_user_analytics_summary: {str(e)}")
        return {"success": False, "error": str(e)}
    finally:
        cursor.close()
        conn.close()

def _process_single_user_analytics(cursor, user_id):
    """Helper to process a single user's stats and streak"""
    
    # A. Calculate Total Time & Quizzes
    cursor.execute("""
        SELECT 
            COALESCE(SUM(duration_watched), 0) as total_time,
            MAX(time_watched) as last_video_time
        FROM recently_watched WHERE user_id = %s
    """, (user_id,))
    video_stats = cursor.fetchone()
    
    cursor.execute("""
        SELECT 
            COUNT(*) as total_quizzes,
            COALESCE(SUM(total_questions), 0) as total_questions,
            COALESCE(SUM(score_percentage), 0) / NULLIF(COUNT(*), 0) as avg_score,
            MAX(created_at) as last_quiz_time
        FROM quiz_sessions WHERE user_id = %s
    """, (user_id,))
    quiz_stats = cursor.fetchone()
    
    # Determine last activity time
    last_video = video_stats.get('last_video_time')
    last_quiz = quiz_stats.get('last_quiz_time')
    
    if last_video and last_quiz:
        last_activity = max(last_video, last_quiz)
    elif last_video:
        last_activity = last_video
    else:
        last_activity = last_quiz

    # B. Calculate Streak
    current_streak, longest_streak = _calculate_streak(cursor, user_id)
    
    # C. Upsert into user_analytics_summary
    cursor.execute("""
        INSERT INTO user_analytics_summary 
        (user_id, total_time_spent, total_quizzes, total_questions, avg_score, 
         current_streak, longest_streak, last_activity, updated_at)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, NOW())
        ON DUPLICATE KEY UPDATE
        total_time_spent = VALUES(total_time_spent),
        total_quizzes = VALUES(total_quizzes),
        total_questions = VALUES(total_questions),
        avg_score = VALUES(avg_score),
        current_streak = VALUES(current_streak),
        longest_streak = VALUES(longest_streak),
        last_activity = VALUES(last_activity),
        updated_at = NOW()
    """, (
        user_id, 
        video_stats['total_time'], 
        quiz_stats['total_quizzes'], 
        quiz_stats['total_questions'], 
        quiz_stats['avg_score'] or 0,
        current_streak,
        longest_streak,
        last_activity
    ))

def _calculate_streak(cursor, user_id):
    """
    Calculates consecutive days of activity.
    """
    # Get distinct dates of activity
    cursor.execute("""
        SELECT DISTINCT DATE(time_watched) as activity_date 
        FROM recently_watched WHERE user_id = %s
        UNION
        SELECT DISTINCT DATE(created_at) as activity_date 
        FROM quiz_sessions WHERE user_id = %s
        ORDER BY activity_date DESC
    """, (user_id, user_id))
    
    dates = [row['activity_date'] for row in cursor.fetchall()]
    
    if not dates:
        return 0, 0
        
    current_streak = 0
    longest_streak = 0
    temp_streak = 0
    
    today = datetime.now().date()
    yesterday = today - timedelta(days=1)
    
    # Check if active today or yesterday (streak alive)
    if dates[0] == today or dates[0] == yesterday:
        current_streak = 1
        temp_streak = 1
        
        # Iterate backwards to find consecutive days
        for i in range(len(dates) - 1):
            diff = (dates[i] - dates[i+1]).days
            if diff == 1:
                temp_streak += 1
            else:
                longest_streak = max(longest_streak, temp_streak)
                if i == 0: # Streak broken immediately
                    pass 
                temp_streak = 1
                
        current_streak = temp_streak
    else:
        current_streak = 0 # Streak broken
        
    # Simple longest streak calculation if complex one fails (simplified for MVP)
    # Ideally should traverse full history for true longest
    longest_streak = max(longest_streak, current_streak)
    
    return current_streak, longest_streak

def analyze_study_patterns():
    """
    Cron Job: Weekly (or Daily)
    Analyzes WHEN users study to populate 'user_study_patterns'.
    Useful for sending notifications at the right time.
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        logger.info("Starting Study Pattern Analysis...")
        
        # Clear old patterns (or we could update incrementally, but full refresh is cleaner for now)
        cursor.execute("TRUNCATE TABLE user_study_patterns")
        
        # Aggregation Query
        # Group by User, Day of Week (1=Sunday, 7=Saturday), and Hour (0-23)
        query = """
            INSERT INTO user_study_patterns 
            (user_id, day_of_week, study_hour, session_count, total_time)
            SELECT 
                user_id,
                DAYOFWEEK(time_watched) as day_of_week,
                HOUR(time_watched) as study_hour,
                COUNT(*) as session_count,
                SUM(duration_watched) as total_time
            FROM recently_watched
            WHERE time_watched > DATE_SUB(NOW(), INTERVAL 90 DAY)
            GROUP BY user_id, day_of_week, study_hour
            HAVING total_time > 60  -- Only count meaningful sessions (>1 min total)
        """
        
        cursor.execute(query)
        processed_count = cursor.rowcount
        
        conn.commit()
        logger.info(f"Study Pattern Analysis completed. Rows inserted: {processed_count}")
        return {"success": True, "rows_inserted": processed_count}
        
    except Exception as e:
        logger.error(f"Error in analyze_study_patterns: {str(e)}")
        return {"success": False, "error": str(e)}
    finally:
        cursor.close()
        conn.close()

def update_topic_analytics():
    """
    Cron Job: Weekly/Daily
    Aggregates quiz performance by topic to populate 'topic_analytics'.
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        logger.info("Starting Topic Analytics Update...")
        
        # 1. Clear old data (or incremental update)
        cursor.execute("TRUNCATE TABLE topic_analytics")
        
        # 2. Aggregate from quiz responses
        # Join with quiz_sessions to get user_id
        query = """
            INSERT INTO topic_analytics
            (user_id, topic_id, questions_attempted, questions_correct, avg_time_per_question, last_attempted)
            SELECT 
                qs.user_id,
                qqr.topic_id,
                COUNT(*) as questions_attempted,
                SUM(qqr.is_correct) as questions_correct,
                AVG(qqr.time_taken) as avg_time,
                MAX(qqr.created_at) as last_attempted
            FROM quiz_question_responses qqr
            JOIN quiz_sessions qs ON qqr.session_id = qs.id
            WHERE qqr.topic_id IS NOT NULL AND qs.user_id IS NOT NULL
            GROUP BY qs.user_id, qqr.topic_id
        """
        
        cursor.execute(query)
        processed_count = cursor.rowcount
        
        conn.commit()
        logger.info(f"Topic Analytics updated. Rows inserted: {processed_count}")
        return {"success": True, "rows_inserted": processed_count}
        
    except Exception as e:
        logger.error(f"Error in update_topic_analytics: {str(e)}")
        return {"success": False, "error": str(e)}
    finally:
        cursor.close()
        conn.close()

def generate_learning_insights():
    """
    Cron Job: Weekly
    Analyzes 'topic_analytics' to generate textual insights for 'user_learning_insights'.
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        logger.info("Starting Learning Insights Generation...")
        
        # Clear old insights
        cursor.execute("TRUNCATE TABLE user_learning_insights")
        
        # 1. Identify WEAK topics (< 40% accuracy, > 5 attempts)
        cursor.execute("""
            SELECT t.user_id, t.topic_id, top.title as topic_name, 
                   (t.questions_correct / t.questions_attempted) * 100 as accuracy
            FROM topic_analytics t
            JOIN topics top ON t.topic_id = top.id
            WHERE t.questions_attempted >= 5 
            AND (t.questions_correct / t.questions_attempted) < 0.4
        """)
        weak_topics = cursor.fetchall()
        
        for item in weak_topics:
            message = f"You seem to be struggling with {item['topic_name']} (Accuracy: {int(item['accuracy'])}%)."
            recommendation = f"Review the {item['topic_name']} course materials or take a practice quiz."
            
            cursor.execute("""
                INSERT INTO user_learning_insights 
                (user_id, insight_type, title, message, recommendation, priority)
                VALUES (%s, 'weakness', 'Focus Area Identified', %s, %s, 'high')
            """, (item['user_id'], message, recommendation))

        # 2. Identify STRONG topics (> 80% accuracy, > 5 attempts)
        cursor.execute("""
            SELECT t.user_id, t.topic_id, top.title as topic_name, 
                   (t.questions_correct / t.questions_attempted) * 100 as accuracy
            FROM topic_analytics t
            JOIN topics top ON t.topic_id = top.id
            WHERE t.questions_attempted >= 5 
            AND (t.questions_correct / t.questions_attempted) > 0.8
        """)
        strong_topics = cursor.fetchall()
        
        for item in strong_topics:
            message = f"You're doing great in {item['topic_name']} (Accuracy: {int(item['accuracy'])}%)!"
            recommendation = "Challenge yourself with harder questions."
            
            cursor.execute("""
                INSERT INTO user_learning_insights 
                (user_id, insight_type, title, message, recommendation, priority)
                VALUES (%s, 'strength', 'Keep it up!', %s, %s, 'low')
            """, (item['user_id'], message, recommendation))
            
        conn.commit()
        count = len(weak_topics) + len(strong_topics)
        logger.info(f"Learning Insights generated. Total insights: {count}")
        return {"success": True, "insights_generated": count}
        
    except Exception as e:
        logger.error(f"Error in generate_learning_insights: {str(e)}")
        return {"success": False, "error": str(e)}
    finally:
        cursor.close()
        conn.close()

def update_mock_test_analytics():
    """
    Cron Job: Daily
    Analyzes recent mock test attempts (modelset_attempts) to find significant events:
    - High Score (> 80%)
    - Failed Attempt (< 40%)
    - Improvement (if possible to track)
    We will store these "events" in a 'user_learning_insights' entry 
    so the notification job can pick it up universally.
    """
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        logger.info("Starting Mock Test Analysis...")
        
        # Look for attempts from the last 24 hours
        
        # 1. High Scores
        cursor.execute("""
            SELECT ma.user_id, ma.modelset_id, ma.score_percentage, ma.id as attempt_id
            FROM modelset_attempts ma
            WHERE ma.created_at > DATE_SUB(NOW(), INTERVAL 1 DAY)
            AND ma.score_percentage >= 80
        """)
        high_scorers = cursor.fetchall()
        
        count = 0
        for attempt in high_scorers:
            title = "Mock Test Champion! 🏆"
            message = f"You scored {attempt['score_percentage']}% on your recent mock test. Great job!"
            recommendation = "Try the next difficult level."
            
            # Check for duplicate
            cursor.execute("""
                SELECT id FROM user_learning_insights 
                WHERE user_id = %s AND insight_type = 'mock_test_high' 
                AND message LIKE %s
                AND created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR)
            """, (attempt['user_id'], f"%{attempt['score_percentage']}%"))
            
            if not cursor.fetchone():
                cursor.execute("""
                    INSERT INTO user_learning_insights 
                    (user_id, insight_type, title, message, recommendation, priority, created_at)
                    VALUES (%s, 'mock_test_high', %s, %s, %s, 'low', NOW())
                """, (attempt['user_id'], title, message, recommendation))
                count += 1

        # 2. Low Scores (Encouragement)
        cursor.execute("""
            SELECT ma.user_id, ma.modelset_id, ma.score_percentage, ma.id as attempt_id
            FROM modelset_attempts ma
            WHERE ma.created_at > DATE_SUB(NOW(), INTERVAL 1 DAY)
            AND ma.score_percentage < 40
        """)
        low_scorers = cursor.fetchall()
        
        for attempt in low_scorers:
            title = "Don't Give Up! 💪"
            message = f"You scored {attempt['score_percentage']}% on the recent mock test."
            recommendation = "Review your incorrect answers and try again."
            
            cursor.execute("""
                SELECT id FROM user_learning_insights 
                WHERE user_id = %s AND insight_type = 'mock_test_low' 
                AND message LIKE %s
                AND created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR)
            """, (attempt['user_id'], f"%{attempt['score_percentage']}%"))
            
            if not cursor.fetchone():
                cursor.execute("""
                    INSERT INTO user_learning_insights 
                    (user_id, insight_type, title, message, recommendation, priority, created_at)
                    VALUES (%s, 'mock_test_low', %s, %s, %s, 'high', NOW())
                """, (attempt['user_id'], title, message, recommendation))
                count += 1
                
        conn.commit()
        logger.info(f"Mock Test Analysis completed. New insights: {count}")
        return {"success": True, "insights_generated": count}
        
    except Exception as e:
        logger.error(f"Error in update_mock_test_analytics: {str(e)}")
        return {"success": False, "error": str(e)}
    finally:
        cursor.close()
        conn.close()
