"""
Home page data routes
Provides all necessary data for the home screen including sliders, recent activity, enrolled courses, etc.
"""
import json
import logging
from flask import Blueprint, request, jsonify
from utils import (require_api_key, encrypt_response, format_api_response, 
                   create_error_response, get_db_connection)
from auth.jwt_manager import require_auth, require_user_access, optional_auth
from database import ManagedDBConnection, get_cached_connection

logger = logging.getLogger(__name__)
home_bp = Blueprint('home', __name__)


@home_bp.route('/api/home/data', methods=['GET', 'OPTIONS'])
@require_api_key
@encrypt_response
def get_home_data():
    """
    Get all home page data in a single request
    Includes: sliders, recent watched, enrolled courses, featured courses, stats
    """
    # 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 = request.args.get('user_id')
        
        if not user_id:
            return create_error_response("User ID is required", 400)
        
        home_data = {}
        
        # Use context manager for proper connection management
        with ManagedDBConnection(get_cached_connection()) as conn:
            cursor = conn.cursor(dictionary=True)
            
            # 1. Get active home sliders
            try:
                cursor.execute("""
                    SELECT 
                        id, title, description, image_url, link_type, link_value,
                        button_text, background_color, display_order
                    FROM home_sliders
                    WHERE is_active = 1
                    AND (start_date IS NULL OR start_date <= NOW())
                    AND (end_date IS NULL OR end_date >= NOW())
                    ORDER BY display_order ASC, created_at DESC
                    LIMIT 10
                """)
                home_data['sliders'] = cursor.fetchall() or []
            except Exception as e:
                logger.error(f"Error fetching sliders: {e}")
                home_data['sliders'] = []
            
            # 2. Get recently watched videos (last 10)
            try:
                cursor.execute("""
                    SELECT 
                        rw.id,
                        rw.video_id,
                        rw.course_id,
                        rw.title as video_title,
                        rw.time_watched,
                        rw.duration_watched,
                        rw.watched_percentage,
                        v.video_url,
                        v.image_url as thumbnail_url,
                        TIME_TO_SEC(v.video_duration) as total_duration,
                        c.title as course_title,
                        c.image_url as course_image
                    FROM recently_watched rw
                    LEFT JOIN videos v ON rw.video_id = v.id
                    LEFT JOIN courses c ON rw.course_id = c.id
                    WHERE rw.user_id = %s
                    ORDER BY rw.time_watched DESC
                    LIMIT 10
                """, (user_id,))
                recent_videos = cursor.fetchall() or []
                
                # Calculate continue watching percentage
                for video in recent_videos:
                    if video['total_duration'] and video['duration_watched']:
                        video['progress_percentage'] = min(100, (video['duration_watched'] / video['total_duration']) * 100)
                    else:
                        video['progress_percentage'] = float(video.get('watched_percentage', 0))
                
                home_data['recently_watched'] = recent_videos
            except Exception as e:
                logger.error(f"Error fetching recently watched: {e}")
                home_data['recently_watched'] = []
            
            # 3. Get enrolled courses (active enrollments with confirmed payment)
            try:
                cursor.execute("""
                    SELECT 
                        c.id,
                        c.title,
                        c.description,
                        c.image_url,
                        c.level,
                        c.duration_hours,
                        e.progress as progress_percentage,
                        e.enrolled_at as enrollment_date,
                        e.updated_at as last_accessed,
                        i.name as institute_name,
                        inst.name as instructor_name,
                        (SELECT COUNT(*) FROM videos WHERE course_id = c.id) as total_videos,
                        (SELECT COUNT(DISTINCT rw.video_id) 
                         FROM recently_watched rw 
                         WHERE rw.user_id = %s AND rw.course_id = c.id) as watched_videos
                    FROM enrollments e
                    INNER JOIN courses c ON e.course_id = c.id
                    LEFT JOIN institutes i ON c.institute_id = i.id
                    LEFT JOIN instructors inst ON c.primary_instructor_id = inst.id
                    WHERE e.user_id = %s
                    AND e.is_completed = 0
                    AND LOWER(e.payment_status) IN ('free', 'paid')
                    ORDER BY e.updated_at DESC, e.enrolled_at DESC
                    LIMIT 10
                """, (user_id, user_id))
                enrolled = cursor.fetchall() or []
                
                # Calculate course progress
                for course in enrolled:
                    if course['total_videos'] > 0 and course['watched_videos']:
                        course['calculated_progress'] = (course['watched_videos'] / course['total_videos']) * 100
                    else:
                        course['calculated_progress'] = float(course.get('progress_percentage', 0) or 0)
                
                home_data['enrolled_courses'] = enrolled
            except Exception as e:
                logger.error(f"Error fetching enrolled courses: {e}")
                home_data['enrolled_courses'] = []
            
            # 4. Get featured/recommended courses
            try:
                cursor.execute("""
                    SELECT 
                        c.id,
                        c.title,
                        c.description,
                        c.image_url,
                        c.level,
                        c.duration_hours,
                        c.final_price,
                        c.rating,
                        i.name as institute_name,
                        inst.name as instructor_name,
                        c.enrollments_count
                    FROM courses c
                    LEFT JOIN institutes i ON c.institute_id = i.id
                    LEFT JOIN instructors inst ON c.primary_instructor_id = inst.id
                    WHERE c.approval_status = 'approved'
                    AND c.is_featured = 1
                    ORDER BY c.rating DESC, c.enrollments_count DESC
                    LIMIT 6
                """)
                home_data['featured_courses'] = cursor.fetchall() or []
            except Exception as e:
                logger.error(f"Error fetching featured courses: {e}")
                home_data['featured_courses'] = []
            
            # 5. Get user quick stats (only for paid/free enrollments)
            try:
                stats = {}
                
                # 5.1 Enrolled courses count (JOIN with courses to ensure valid course)
                try:
                    cursor.execute("""
                        SELECT COUNT(DISTINCT e.course_id) as count
                        FROM enrollments e
                        JOIN courses c ON e.course_id = c.id
                        WHERE e.user_id = %s
                        AND LOWER(e.payment_status) IN ('free', 'paid')
                    """, (user_id,))
                    enrollment_result = cursor.fetchone()
                    stats['enrolled_courses_count'] = enrollment_result['count'] if enrollment_result else 0
                except Exception as e:
                    logger.error(f"Error counting enrollments: {e}")
                    stats['enrolled_courses_count'] = 0

                # 5.2 Watch stats (Independent of enrollments)
                try:
                    cursor.execute("""
                        SELECT 
                            COUNT(DISTINCT video_id) as watched_videos_count,
                            COALESCE(SUM(duration_watched), 0) as total_watch_time
                        FROM recently_watched
                        WHERE user_id = %s
                    """, (user_id,))
                    watch_result = cursor.fetchone()
                    if watch_result:
                        stats['watched_videos_count'] = watch_result['watched_videos_count']
                        stats['total_watch_time'] = watch_result['total_watch_time'] or 0
                    else:
                        stats['watched_videos_count'] = 0
                        stats['total_watch_time'] = 0
                except Exception as e:
                    logger.error(f"Error fetching watch stats: {e}")
                    stats['watched_videos_count'] = 0
                    stats['total_watch_time'] = 0

                # 5.3 Quiz stats
                try:
                    cursor.execute("""
                        SELECT 
                            COUNT(*) as quizzes_taken,
                            AVG(score_percentage) as avg_quiz_score
                        FROM quiz_sessions 
                        WHERE user_id = %s
                    """, (user_id,))
                    quiz_result = cursor.fetchone()
                    if quiz_result:
                        stats['quizzes_taken'] = quiz_result['quizzes_taken']
                        stats['avg_quiz_score'] = quiz_result['avg_quiz_score'] or 0
                    else:
                        stats['quizzes_taken'] = 0
                        stats['avg_quiz_score'] = 0
                except Exception as e:
                    logger.error(f"Error fetching quiz stats: {e}")
                    stats['quizzes_taken'] = 0
                    stats['avg_quiz_score'] = 0
                
                # Format watch time (seconds to hours/minutes)
                total_seconds = int(stats.get('total_watch_time', 0))
                hours = total_seconds // 3600
                minutes = (total_seconds % 3600) // 60
                
                home_data['user_stats'] = {
                    'enrolled_courses': stats.get('enrolled_courses_count', 0),
                    'watched_videos': stats.get('watched_videos_count', 0),
                    'total_watch_time': total_seconds,
                    'watch_time_formatted': f"{hours}h {minutes}m" if hours > 0 else f"{minutes}m",
                    'quizzes_taken': stats.get('quizzes_taken', 0),
                    'avg_quiz_score': round(float(stats.get('avg_quiz_score', 0)), 1)
                }
            except Exception as e:
                logger.error(f"Error fetching user stats: {e}")
                home_data['user_stats'] = {
                    'enrolled_courses': 0,
                    'watched_videos': 0,
                    'total_watch_time': 0,
                    'watch_time_formatted': '0m',
                    'quizzes_taken': 0,
                    'avg_quiz_score': 0
                }
            
            # 6. Get recent achievements/milestones
            try:
                cursor.execute("""
                    SELECT 
                        id, title, icon, issued_date
                    FROM achievements
                    WHERE uid = %s
                    ORDER BY id DESC
                    LIMIT 5
                """, (user_id,))
                home_data['recent_achievements'] = cursor.fetchall() or []
            except Exception as e:
                logger.error(f"Error fetching achievements: {e}")
                home_data['recent_achievements'] = []
        
        return home_data, 200
        
    except Exception as e:
        logger.error(f"Error in get_home_data: {str(e)}", exc_info=True)
        return create_error_response(f"Failed to fetch home data: {str(e)}", 500)


@home_bp.route('/api/home/sliders', methods=['GET', 'OPTIONS'])
@require_api_key
def get_sliders():
    """Get only active sliders (lightweight endpoint)"""
    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:
        with ManagedDBConnection(get_cached_connection()) as conn:
            cursor = conn.cursor(dictionary=True)
            
            cursor.execute("""
                SELECT 
                    id, title, description, image_url, link_type, link_value,
                    button_text, background_color, display_order
                FROM home_sliders
                WHERE is_active = 1
                AND (start_date IS NULL OR start_date <= NOW())
                AND (end_date IS NULL OR end_date >= NOW())
                ORDER BY display_order ASC, created_at DESC
                LIMIT 10
            """)
            sliders = cursor.fetchall() or []
        
        return {
            'success': True,
            'sliders': sliders
        }, 200
        
    except Exception as e:
        logger.error(f"Error fetching sliders: {str(e)}")
        return create_error_response("Failed to fetch sliders", 500)


@home_bp.route('/api/home/continue-learning', methods=['GET', 'OPTIONS'])
@require_api_key
@require_auth
def get_continue_learning():
    """Get continue learning section data (recent watched + in-progress courses)"""
    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:
        from flask import g
        user_id = g.user_id
        
        with ManagedDBConnection(get_cached_connection()) as conn:
            cursor = conn.cursor(dictionary=True)
            
            # Get courses with recent activity
            cursor.execute("""
                SELECT DISTINCT
                    c.id as course_id,
                    c.title as course_title,
                    c.image_url,
                    rw.video_id as last_video_id,
                    rw.title as last_video_title,
                    rw.watched_percentage,
                    rw.time_watched,
                    e.progress as course_progress
                FROM recently_watched rw
                INNER JOIN courses c ON rw.course_id = c.id
                LEFT JOIN enrollments e ON e.course_id = c.id AND e.user_id = rw.user_id
                WHERE rw.user_id = %s
                AND rw.watched_percentage < 95
                ORDER BY rw.time_watched DESC
                LIMIT 5
            """, (user_id,))
            
            continue_data = cursor.fetchall() or []
        
        return {
            'success': True,
            'continue_learning': continue_data
        }, 200
        
    except Exception as e:
        logger.error(f"Error fetching continue learning data: {str(e)}")
        return create_error_response("Failed to fetch continue learning data", 500)
