from flask import jsonify
from db_utils import get_db_connection, fetch_as_dict
from database import optimized_execute
import logging

logger = logging.getLogger(__name__)


def video_tracking(request):
    try:
        # Get request parameters
        user_id = request.values.get('user_id')
        video_id = request.values.get('video_id')
        time_watched = request.values.get('time_watched')
        title = request.values.get('title')
        course_id = request.values.get('course_id')
        
        # Validate required parameters
        if not all([user_id, video_id, time_watched]):
            return jsonify({'error': 'Missing required parameters'}), 400
            
        logger.debug(f"Tracking video {video_id} for user {user_id}, time watched: {time_watched}")
        
        # Use context manager to ensure connection is properly closed
        with get_db_connection() as conn:
            cursor = conn.cursor()
            try:
                # Retrieve the title and total video time from the videos table
                optimized_execute(
                    cursor,
                    "SELECT title, TIME_TO_SEC(video_duration) as total_duration FROM videos WHERE id = %s", 
                    (video_id,)
                )
                video_data = fetch_as_dict(cursor)

                if not video_data:
                    # Return an error if the video does not exist
                    return jsonify({'error': 'Video not found'}), 404

                # Assuming total_duration is in seconds
                total_video_time = video_data[0]['total_duration']
                
                # Use title from request or fallback to DB title
                db_title = video_data[0].get('title', 'Unknown Video')
                title = title or db_title or 'Unknown Video'

                # Calculate watched percentage
                watched_percentage = (float(
                    time_watched) / float(total_video_time)) * 100 if total_video_time > 0 else 0

                # Check if there is already a record for this user and video
                optimized_execute(
                    cursor,
                    "SELECT * FROM recently_watched WHERE user_id = %s AND video_id = %s", 
                    (user_id, video_id,)
                )
                old = len(fetch_as_dict(cursor))

                if old > 0:
                    # If record exists, update the duration_watched and watched_percentage
                    optimized_execute(cursor, """
                        UPDATE recently_watched 
                        SET duration_watched = %s, time_watched = CURRENT_TIMESTAMP, watched_percentage = %s
                        WHERE user_id = %s AND video_id = %s
                    """, (time_watched, watched_percentage, user_id, video_id))
                else:
                    # If no record exists, insert a new one with default values for optional fields
                    optimized_execute(cursor, """
                        INSERT INTO recently_watched 
                        (user_id, video_id, time_watched, duration_watched, course_id, title, watched_percentage, device, platform, session_id, ip_address, user_agent)
                        VALUES (%s, %s, CURRENT_TIMESTAMP, %s, %s, %s, %s, 'Unknown', 'Unknown', 'Unknown', 'Unknown', 'Unknown')
                    """, (user_id, video_id, time_watched, course_id, title, watched_percentage))

                conn.commit()  # Commit changes to the database
                logger.debug(f"Successfully tracked video {video_id} for user {user_id}")
                
                # Update enrollment progress based on all watched videos for this course
                if course_id:
                    try:
                        # Calculate overall course progress as average of all video watched percentages
                        optimized_execute(cursor, """
                            UPDATE enrollments e
                            SET e.progress = (
                                SELECT COALESCE(
                                    (SELECT SUM(LEAST(rw.watched_percentage, 100)) / 
                                            (SELECT COUNT(*) FROM videos WHERE course_id = %s)
                                     FROM videos v
                                     LEFT JOIN recently_watched rw ON v.id = rw.video_id AND rw.user_id = %s
                                     WHERE v.course_id = %s
                                    ), 0
                                )
                            )
                            WHERE e.user_id = %s AND e.course_id = %s
                        """, (course_id, user_id, course_id, user_id, course_id))
                        conn.commit()
                        logger.debug(f"Updated enrollment progress for course {course_id}, user {user_id}")
                    except Exception as progress_error:
                        logger.warning(f"Failed to update enrollment progress: {progress_error}")
                        # Don't fail the whole request if progress update fails
            finally:
                # Ensure cursor is closed even if an exception occurs
                if cursor:
                    cursor.close()
                    
        return jsonify({'success': 'true'})
    except Exception as e:
        logger.error(f"Error in video_tracking: {str(e)}")
        return jsonify({'error': f"Failed to track video: {str(e)}"}), 500
