from flask import jsonify, request, g
from analysis_user_activity import analyze_user_activity
from db_utils import get_db_connection, fetch_as_dict
from utils import (fetch_data, fetch_data_course_search, fetch_data_from_api, fetch_data_mocktest, 
                   fetch_data_videos, get_questions_from_db, format_api_response, create_error_response,
                   format_validation_error)
from database import optimized_execute  # Import query optimization
import logging

logger = logging.getLogger(__name__)

def my_courses(request):
    try:
        # Get user_id from JWT token if available, otherwise from request parameters
        user_id = getattr(g, 'current_user_id', None) or request.values.get('user_id')

        if not user_id:
            return format_validation_error(
                {"user_id": "required"},
                "User ID is required"
            )
        
        # Use context manager to ensure connection is properly closed
        with get_db_connection() as conn:
            cursor = conn.cursor()
            try:
                # Correct SQL JOIN query to fetch user's completed courses
                # Only include courses with confirmed payment (free or paid)
                query_i = """
                    SELECT 
                        courses.*, 
                        enrollments.progress, 
                        enrollments.payment_status,
                        COUNT(DISTINCT videos.id) AS video_count,
                        IFNULL(SUM(recently_watched.duration_watched), 0) AS total_duration_watched, 
                        IFNULL(SUM(TIME_TO_SEC(videos.video_duration)), 0) AS total_video_duration,
                        IFNULL(
                            (SUM(recently_watched.duration_watched) / SUM(TIME_TO_SEC(videos.video_duration))) * 100, 0
                        ) AS watch_percentage
                    FROM courses
                    JOIN enrollments ON courses.id = enrollments.course_id
                    LEFT JOIN recently_watched ON courses.id = recently_watched.course_id AND recently_watched.user_id = %s
                    LEFT JOIN videos ON courses.id = videos.course_id
                    WHERE enrollments.user_id = %s 
                    AND enrollments.payment_status IN ('free', 'paid')
                    GROUP BY courses.id
                    HAVING watch_percentage = 100  
                """
                optimized_execute(cursor, query_i, (user_id, user_id))
                my_course_completed = fetch_as_dict(cursor)

                # Correct SQL JOIN query to fetch user's ongoing courses
                # Only include courses with confirmed payment (free or paid)
                query_ii = """
                    SELECT 
                        courses.*, 
                        enrollments.progress, 
                        enrollments.payment_status,
                        COUNT(DISTINCT videos.id) AS video_count,
                        IFNULL(SUM(recently_watched.duration_watched), 0) AS total_duration_watched, 
                        IFNULL(SUM(TIME_TO_SEC(videos.video_duration)), 0) AS total_video_duration,
                        IFNULL(
                            (SUM(recently_watched.duration_watched) / SUM(TIME_TO_SEC(videos.video_duration))) * 100, 0
                        ) AS watch_percentage
                    FROM courses
                    JOIN enrollments ON courses.id = enrollments.course_id
                    LEFT JOIN recently_watched ON courses.id = recently_watched.course_id AND recently_watched.user_id = %s
                    LEFT JOIN videos ON courses.id = videos.course_id
                    WHERE enrollments.user_id = %s 
                    AND enrollments.payment_status IN ('free', 'paid')
                    GROUP BY courses.id
                    HAVING watch_percentage != 100 
                """

                optimized_execute(cursor, query_ii, (user_id, user_id))
                my_course_not_completed = fetch_as_dict(cursor)

                # Combine all courses into a single list (standard API format)
                # Flutter expects data to be a list directly, not nested objects
                all_courses = my_course_completed + my_course_not_completed
                
                # Debug: Log video counts
                logger.info(f"my_courses: Retrieved {len(all_courses)} courses")
                for i, course in enumerate(all_courses):
                    logger.info(f"  Course {i+1} '{course.get('title', 'Unknown')}': video_count={course.get('video_count', 'MISSING')}, watch_percentage={course.get('watch_percentage', 'MISSING')}%")
            finally:
                # Ensure cursor is closed even if an exception occurs
                if cursor:
                    cursor.close()
                    
        return jsonify(format_api_response(
            data=all_courses,
            message=f"Retrieved {len(all_courses)} user courses successfully"
        )), 200
    
    except Exception as e:
        logger.error(f"Error in my_courses: {str(e)}")
        return create_error_response(
            "Failed to retrieve user courses",
            status_code=500,
            error_code="MY_COURSES_ERROR",
            error_details=str(e) if logger.level == logging.DEBUG else None
        )
        


def all_courses(request):
    try:
        # Get optional institute_id filter for multi-tenant support
        # Priority: Header > Query param > Form data
        institute_id = (
            request.headers.get('X-Institute-ID') or 
            request.args.get('institute_id') or 
            request.form.get('institute_id')
        )
        
        # Use context manager to ensure connection is properly closed
        with get_db_connection() as conn:
            cursor = conn.cursor(dictionary=True)
            try:
                # Enhanced query with institute and category information
                query = """
                    SELECT 
                        c.id,
                        c.title,
                        c.subtitle,
                        c.description,
                        CAST(c.price AS FLOAT) AS price,
                        CAST(c.discount AS FLOAT) AS discount,
                        CAST(c.final_price AS FLOAT) AS final_price,
                        c.image_url,
                        c.video_preview_url,
                        c.level,
                        c.category_id,
                        c.type,
                        c.language,
                        CAST(c.duration_hours AS FLOAT) AS duration_hours,
                        CAST(c.total_lessons AS UNSIGNED) AS total_lessons,
                        CAST(c.total_resources AS UNSIGNED) AS total_resources,
                        c.prerequisites,
                        c.outcomes,
                        c.target_audience,
                        c.publication_date,
                        c.status,
                        CAST(c.rating AS FLOAT) AS rating,
                        CAST(c.reviews_count AS UNSIGNED) AS reviews_count,
                        CAST(c.enrollments_count AS UNSIGNED) AS enrollments_count,
                        c.is_featured,
                        c.is_top_selling,
                        c.is_trending,
                        c.tags,
                        CAST(c.difficulty_score AS FLOAT) AS difficulty_score,
                        c.created_at,
                        c.*,
                        i.name AS institute_name,
                        i.slug AS institute_slug,
                        i.logo_url AS institute_logo,
                        cat.name AS category_name,
                        cat.description AS category_description,
                        COUNT(DISTINCT v.id) AS video_count,
                        CASE 
                            WHEN c.tags IS NOT NULL AND c.tags != '' 
                            THEN JSON_EXTRACT(CONCAT('[\"', REPLACE(c.tags, ',', '\",\"'), '\"]'), '$')
                            ELSE JSON_ARRAY()
                        END AS categories
                    FROM courses c
                    LEFT JOIN institutes i ON c.institute_id = i.id
                    LEFT JOIN categories cat ON c.category_id = cat.id
                    LEFT JOIN videos v ON c.id = v.course_id
                    WHERE c.status = 'active' AND (i.status = 'active' OR c.institute_id IS NULL)
                """
                
                # Add institute filter if provided
                params = []
                if institute_id:
                    query += " AND c.institute_id = %s"
                    params.append(institute_id)
                
                query += """
                    GROUP BY c.id
                    ORDER BY c.created_at DESC
                """
                
                if params:
                    optimized_execute(cursor, query, tuple(params))
                else:
                    optimized_execute(cursor, query)
                courses = cursor.fetchall()

                # Ensure numeric types for frontend
                numeric_fields = {
                    'price', 'discount', 'final_price', 'duration_hours',
                    'total_lessons', 'total_resources', 'rating', 'reviews_count',
                    'enrollments_count', 'difficulty_score'
                }

                for course in courses:
                    for field in numeric_fields:
                        course[field] = float(course[field]) if course[field] is not None else 0.0
                    
                    # Process categories JSON field
                    if course.get('categories'):
                        try:
                            if isinstance(course['categories'], str):
                                import json
                                course['categories'] = json.loads(course['categories'])
                        except (json.JSONDecodeError, TypeError):
                            course['categories'] = []
                    else:
                        course['categories'] = []
                    
                    # Add category name to categories array if not already present
                    if course.get('category_name') and course['category_name'] not in course['categories']:
                        course['categories'].append(course['category_name'])
            finally:
                # Ensure cursor is closed even if an exception occurs
                if cursor:
                    cursor.close()
                    
        return jsonify(format_api_response(
            data=courses,
            message=f"Retrieved {len(courses)} active courses"
        )), 200

    except Exception as e:
        logger.error(f"Error in all_courses: {str(e)}")
        return create_error_response(
            "Failed to load courses",
            status_code=500,
            error_code="ALL_COURSES_ERROR",
            error_details=str(e) if logger.level == logging.DEBUG else None
        )


def get_categories(request):
    """Get all available course categories"""
    try:
        with get_db_connection() as conn:
            cursor = conn.cursor(dictionary=True)
            try:
                query = """
                    SELECT 
                        id,
                        name,
                        description,
                        created_at,
                        updated_at,
                        (SELECT COUNT(*) FROM courses WHERE category_id = categories.id AND status = 'active') as course_count
                    FROM categories
                    ORDER BY name ASC
                """
                optimized_execute(cursor, query)
                categories = cursor.fetchall()
                
                return jsonify(format_api_response(
                    data=categories,
                    message=f"Retrieved {len(categories)} categories"
                )), 200
                
            finally:
                if cursor:
                    cursor.close()
                    
    except Exception as e:
        logger.error(f"Error in get_categories: {str(e)}")
        return create_error_response(
            "Failed to load categories",
            status_code=500,
            error_code="CATEGORIES_ERROR",
            error_details=str(e) if logger.level == logging.DEBUG else None
        )


def get_filter_options(request):
    """Get all available filter options for the marketplace"""
    try:
        with get_db_connection() as conn:
            cursor = conn.cursor(dictionary=True)
            try:
                # Get categories
                cursor.execute("""
                    SELECT id, name FROM categories 
                    WHERE (SELECT COUNT(*) FROM courses WHERE category_id = categories.id AND status = 'active') > 0
                    ORDER BY name ASC
                """)
                categories = cursor.fetchall()
                
                # Get levels
                cursor.execute("""
                    SELECT DISTINCT level FROM courses 
                    WHERE status = 'active' AND level IS NOT NULL
                    ORDER BY 
                        CASE level 
                            WHEN 'Beginner' THEN 1
                            WHEN 'Intermediate' THEN 2
                            WHEN 'Advanced' THEN 3
                            WHEN 'Expert' THEN 4
                            ELSE 5
                        END
                """)
                levels = [row['level'] for row in cursor.fetchall()]
                
                # Get languages
                cursor.execute("""
                    SELECT DISTINCT language FROM courses 
                    WHERE status = 'active' AND language IS NOT NULL
                    ORDER BY language ASC
                """)
                languages = [row['language'] for row in cursor.fetchall()]
                
                # Get course types
                cursor.execute("""
                    SELECT DISTINCT type FROM courses 
                    WHERE status = 'active' AND type IS NOT NULL
                    ORDER BY type ASC
                """)
                types = [row['type'] for row in cursor.fetchall()]
                
                # Get institutes (active ones with courses)
                cursor.execute("""
                    SELECT DISTINCT i.id, i.name, i.slug, i.institute_type
                    FROM institutes i
                    INNER JOIN courses c ON i.id = c.institute_id
                    WHERE i.status = 'active' AND c.status = 'active'
                    ORDER BY i.name ASC
                """)
                institutes = cursor.fetchall()
                
                # Get price range
                cursor.execute("""
                    SELECT 
                        MIN(final_price) as min_price,
                        MAX(final_price) as max_price
                    FROM courses 
                    WHERE status = 'active' AND final_price > 0
                """)
                price_range = cursor.fetchone()
                
                filter_data = {
                    "categories": categories,
                    "levels": levels,
                    "languages": languages,
                    "types": types,
                    "institutes": institutes,
                    "price_range": {
                        "min": float(price_range['min_price']) if price_range['min_price'] else 0,
                        "max": float(price_range['max_price']) if price_range['max_price'] else 50000
                    }
                }
                
                return jsonify(format_api_response(
                    data=filter_data,
                    message="Filter options retrieved successfully"
                )), 200
                
            finally:
                if cursor:
                    cursor.close()
                    
    except Exception as e:
        logger.error(f"Error in get_filter_options: {str(e)}")
        return create_error_response(
            "Failed to load filter options",
            status_code=500,
            error_code="FILTER_OPTIONS_ERROR",
            error_details=str(e) if logger.level == logging.DEBUG else None
        )