from datetime import timedelta
from flask import jsonify, g
from db_utils import get_db_connection, fetch_as_dict
from collections import defaultdict
import logging

logger = logging.getLogger(__name__)


def course_insid(request):
    try:
        # Debug: Log all request data
        logger.info(f"course_inside request method: {request.method}")
        logger.info(f"course_inside request.is_json: {request.is_json}")
        logger.info(f"course_inside request.content_type: {request.content_type}")
        logger.info(f"course_inside request.values: {dict(request.values)}")
        logger.info(f"course_inside request.form: {dict(request.form)}")
        logger.info(f"course_inside request.args: {dict(request.args)}")
        
        # Get parameters from the request (handle both form data and JSON)
        if request.is_json:
            # Handle JSON request
            data = request.get_json() or {}
            logger.info(f"course_inside JSON data: {data}")
            course = data.get('course_id')
            request_user_id = data.get('user_id')
        else:
            # Handle form data request
            logger.info("course_inside processing as form data")
            course = request.values.get('course_id')
            request_user_id = request.values.get('user_id')
        
        # Get user_id from JWT token if available, otherwise from request parameters
        user_id = getattr(g, 'current_user_id', None) or request_user_id
        
        logger.info(f"course_inside extracted: course_id={course}, user_id={user_id}")
        
        if not course:
            logger.warning("course_id parameter is missing")
            return jsonify({"error": "course_id is required"}), 400
            
        logger.debug(f"Fetching course inside data for course_id: {course}")
        
        # Use context manager to ensure connection is properly closed
        with get_db_connection() as conn:
            cursor = conn.cursor()
            try:
                # Fetch course details with instructor and institute information
                cursor.execute("""
                    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.updated_at,
                        c.instructor_id,
                        c.instructor_name,
                        c.grade_level,
                        c.institute_id,
                        i.name AS institute_name,
                        i.slug AS institute_slug,
                        i.logo_url AS institute_logo,
                        i.institute_type,
                        cat.name AS category_name,
                        cat.description AS category_description
                    FROM courses c
                    LEFT JOIN institutes i ON c.institute_id = i.id
                    LEFT JOIN categories cat ON c.category_id = cat.id
                    WHERE c.id = %s
                """, (course,))
                course_details_raw = fetch_as_dict(cursor)
                course_details = course_details_raw[0] if course_details_raw else None
                
                if not course_details:
                    logger.error(f"Course not found: {course}")
                    return jsonify({"error": "Course not found"}), 404
                
                # Fetch all necessary data from database
                # Fetch total number of classes for the given course
                cursor.execute("SELECT * FROM videos WHERE course_id = %s", (course,))
                total_classes = len(fetch_as_dict(cursor))

                # Fetch total number of topics for the given course
                cursor.execute("SELECT COUNT(*) as topic_count FROM topics WHERE course_id = %s", (course,))
                topic_count_result = fetch_as_dict(cursor)
                total_topics = topic_count_result[0]['topic_count'] if topic_count_result else 0

                # Fetch videos related to the course with user progress
                cursor.execute("""
                    SELECT 
                        v.id, v.title, v.video_url, v.type, v.description, v.credit, 
                        v.for_which, v.level, v.image_url, v.count_exercises, 
                        v.count_students, v.count_plays, v.count_views, v.count_likes, 
                        v.count_comments, v.short_description, v.course_id, v.is_live, 
                        v.chat_id, v.is_active, v.free, v.topic_id,
                        TIME_TO_SEC(v.video_duration) AS video_duration,
                        TIME_TO_SEC(v.created_at) AS created_at_seconds, 
                        TIME_TO_SEC(v.updated_at) AS updated_at_seconds,
                        rw.watched_percentage,
                        rw.duration_watched,
                        rw.time_watched,
                        CASE WHEN rw.watched_percentage >= 90 THEN 1 ELSE 0 END AS watched
                    FROM videos v
                    LEFT JOIN recently_watched rw ON v.id = rw.video_id AND rw.user_id = %s
                    WHERE v.course_id = %s
                    ORDER BY v.id ASC
                """, (user_id, course))
                total_videos_raw = fetch_as_dict(cursor)
                
                # Process videos to format duration and add readable fields
                total_videos = []
                for video in total_videos_raw:
                    processed_video = video.copy()
                    
                    # Convert video_duration from seconds to readable format
                    if video['video_duration']:
                        minutes = int(video['video_duration']) // 60
                        seconds = int(video['video_duration']) % 60
                        processed_video['duration'] = f"{minutes}:{seconds:02d}"
                    else:
                        processed_video['duration'] = "00:00"
                    
                    # Ensure watched is boolean
                    processed_video['watched'] = bool(video.get('watched', 0))
                    
                    # Add progress percentage if available
                    processed_video['progress'] = video.get('watched_percentage', 0) or 0
                    
                    total_videos.append(processed_video)

                # Fetch reviews related to the course
                cursor.execute(
                    "SELECT * FROM course_reviews WHERE course_id = %s", (course,))
                reviews = fetch_as_dict(cursor)

                # Fetch total video duration for the course
                cursor.execute("""
                    SELECT SUM(TIME_TO_SEC(video_duration)) AS total_duration 
                    FROM videos 
                    WHERE course_id = %s
                """, (course,))
                result = fetch_as_dict(cursor)

                # Fetch topics related to the course with hierarchical structure and question counts
                cursor.execute("""
                    SELECT t.*, t.parent_topic_id, t.level, t.full_path,
                           COUNT(q.id) as question_count
                    FROM topics t
                    LEFT JOIN questions q ON t.id = q.topic_id AND q.user_id IS NULL
                    WHERE t.course_id = %s
                    GROUP BY t.id
                    ORDER BY t.level, t.parent_topic_id, t.order_sequence
                """, (course,))
                topics = fetch_as_dict(cursor)

                # Fetch upcoming events related to the course with speakers and schedules
                cursor.execute("""
                    SELECT 
                        e.*, 
                        s.speaker_name, 
                        s.speaker_image_url, 
                        s.speaker_description, 
                        TIME_TO_SEC(sch.schedule_time) AS schedule_time, 
                        sch.title AS schedule_title, 
                        sch.description AS schedule_description
                    FROM upcoming_events e
                    LEFT JOIN event_speakers s ON e.id = s.event_id
                    LEFT JOIN event_schedule sch ON e.id = sch.event_id
                    WHERE e.course_id = %s
                """, (course,))
                upcoming_event_data = fetch_as_dict(cursor)

                # Try new questions table structure first, fallback to old structure
                questions_data = []
                try:
                    # Try new questions table structure
                    cursor.execute("""
                        SELECT 
                            q.id as question_id, q.question_text, q.topic_id, q.options, 
                            q.correct_answer_index, q.difficulty, q.explanation,
                            t.title AS topic_title, t.parent_topic_id, t.level, t.full_path
                        FROM questions q
                        JOIN topics t ON q.topic_id = t.id
                        WHERE t.course_id = %s
                        ORDER BY t.id, q.id
                    """, (course,))
                    questions_data = fetch_as_dict(cursor)
                except Exception as e:
                    logger.warning(f"New questions table not found, trying old structure: {e}")
                    try:
                        # Fallback to old structure with separate options table
                        cursor.execute("""
                    SELECT 
                        q.id as question_id, q.question_text, q.topic_id, t.title AS topic_title,
                        t.parent_topic_id, t.level, t.full_path,
                        o.option_id, o.option_text, o.is_correct
                    FROM questions q
                            JOIN topics t ON q.topic_id = t.id
                            LEFT JOIN options o ON q.id = o.question_id
                            WHERE t.course_id = %s
                            ORDER BY t.level, t.parent_topic_id, t.id, q.id
                """, (course,))
                        questions_data = fetch_as_dict(cursor)
                    except Exception as e2:
                        logger.error(f"Both question table structures failed: {e2}")
                        questions_data = []
                
                # Fetch resources related to the video
                cursor.execute("SELECT * FROM resources WHERE course_id = %s ", (course,))
                resources = fetch_as_dict(cursor)
            finally:
                # Ensure cursor is closed even if an exception occurs
                if cursor:
                    cursor.close()
        
        # Process data outside the database connection
        # Check if total_duration is a timedelta object and convert to seconds
        if result and result[0]['total_duration']:
            total_duration = result[0]['total_duration']
            if isinstance(total_duration, timedelta):  # If it's a timedelta object
                total_video_duration = total_duration.total_seconds()  # Convert to seconds
            else:
                total_video_duration = total_duration  # Use the duration if already in seconds
        else:
            total_video_duration = 0  # Default to 0 if no duration found

        # Organize questions by topic - handle both new and old structures
        organized_quizzes = {}
        import json
        
        # Check if we have new structure data (contains 'options' and 'correct_answer_index')
        has_new_structure = questions_data and 'options' in questions_data[0] and 'correct_answer_index' in questions_data[0]
        
        if has_new_structure:
            # Handle new structure (options as JSON)
            for question_entry in questions_data:
                topic_id = question_entry['topic_id']

                # Initialize topic if it doesn't exist
                if topic_id not in organized_quizzes:
                    organized_quizzes[topic_id] = {
                        'topic_title': question_entry['topic_title'],
                        'questions': []
                    }

                # Parse options from JSON
                try:
                    if isinstance(question_entry['options'], str):
                        options_list = json.loads(question_entry['options'])
                    else:
                        options_list = question_entry['options']
                except (json.JSONDecodeError, TypeError):
                    # Fallback if JSON parsing fails
                    options_list = ['Option 1', 'Option 2', 'Option 3', 'Option 4']

                # Convert options list to the expected format
                formatted_options = []
                correct_index = int(question_entry['correct_answer_index']) if question_entry['correct_answer_index'] is not None else 0
                
                for i, option_text in enumerate(options_list):
                    formatted_options.append({
                        'option_id': str(i),
                        'option_text': str(option_text).strip(),
                        'is_correct': i == correct_index
                    })

                # Create question with formatted options
                question = {
                    'question_id': str(question_entry['question_id']),
                    'question_text': question_entry['question_text'],
                    'options': formatted_options,
                    'difficulty': question_entry.get('difficulty', 'medium'),
                    'explanation': question_entry.get('explanation', '')
                }
                
                organized_quizzes[topic_id]['questions'].append(question)
        else:
            # Handle old structure (separate options table)
            for entry in questions_data:
                topic_id = entry['topic_id']

                # Initialize topic if it doesn't exist
                if topic_id not in organized_quizzes:
                    organized_quizzes[topic_id] = {
                        'topic_title': entry['topic_title'],
                        'questions': []
                    }

                # Find existing question if it exists
                question = next(
                    (q for q in organized_quizzes[topic_id]['questions'] if q['question_id'] == entry['question_id']), None)

                # If the question is not found, create a new one
                if not question:
                    question = {
                        'question_id': str(entry['question_id']),
                        'question_text': entry['question_text'],
                        'options': [],
                        'difficulty': 'medium',  # Default for old structure
                        'explanation': ''  # Default for old structure
                    }
                    organized_quizzes[topic_id]['questions'].append(question)

                # Add the option to the question's options list (if option data exists)
                if entry.get('option_id') and entry.get('option_text'):
                    question['options'].append({
                        'option_id': str(entry['option_id']),
                        'option_text': entry['option_text'],
                        'is_correct': bool(entry['is_correct'])
                    })

        # Organize upcoming events with multiple speakers and schedules
        organized_events = defaultdict(lambda: {
                                    "title": "", "description": "", "date": "", "image_url": "", "speakers": [], "schedules": []})
        for entry in upcoming_event_data:
            event_id = entry['id']

            # Add event details (once)
            if not organized_events[event_id]['title']:  # Check if title is not set
                organized_events[event_id] = {
                    'title': entry['title'],
                    'id': entry['id'],
                    'description': entry['description'],
                    'date': entry['date'],
                    'image_url': entry['image_url'],
                    # Add any other event-related fields you have
                }

            # Ensure that 'speakers' key exists in the event (to avoid KeyError)
            if 'speakers' not in organized_events[event_id]:
                organized_events[event_id]['speakers'] = []

            # Add speaker details if they exist and haven't been added already
            if entry['speaker_name'] and entry['speaker_name'] not in [speaker['name'] for speaker in organized_events[event_id]['speakers']]:
                speaker = {
                    'name': entry['speaker_name'],
                    'image_url': entry['speaker_image_url'],
                    'description': entry['speaker_description'],
                }
                organized_events[event_id]['speakers'].append(speaker)

            # Ensure that 'schedules' key exists in the event (to avoid KeyError)
            if 'schedules' not in organized_events[event_id]:
                organized_events[event_id]['schedules'] = []

            # Add schedule details if they exist and haven't been added already
            if entry['schedule_title'] and entry['schedule_title'] not in [schedule['title'] for schedule in organized_events[event_id]['schedules']]:
                schedule = {
                    'time': entry['schedule_time'],
                    'title': entry['schedule_title'],
                    'description': entry['schedule_description'],
                }
                organized_events[event_id]['schedules'].append(schedule)

        # Convert the defaultdict to a list of events
        final_upcoming_events = list(organized_events.values())

        # Build the final response structure
        final_response = {
            # Course metadata
            "id": course_details["id"],
            "title": course_details["title"],
            "subtitle": course_details.get("subtitle"),
            "description": course_details.get("description"),
            "image_url": course_details.get("image_url"),
            "video_preview_url": course_details.get("video_preview_url"),
            "level": course_details.get("level"),
            "type": course_details.get("type"),
            "language": course_details.get("language"),
            "duration_hours": course_details.get("duration_hours"),
            "rating": course_details.get("rating"),
            "reviews_count": course_details.get("reviews_count"),
            "enrollments_count": course_details.get("enrollments_count"),
            "is_featured": course_details.get("is_featured"),
            "instructor_name": course_details.get("instructor_name"),
            "institute_name": course_details.get("institute_name"),
            "category_name": course_details.get("category_name"),
            "created_at": course_details.get("created_at"),
            "updated_at": course_details.get("updated_at"),
            
            # Course content data
            "total_classes": total_classes,
            "total_topics": total_topics,
            "resources": resources,
            "total_video_duration": total_video_duration,  # Now in seconds or numeric value
            "reviews": reviews,
            "events": final_upcoming_events,  # Return organized events as a list
            "topics": topics,
            "total_videos": total_videos,
            # Return quizzes as a list of topics with questions
            "quizes": list(organized_quizzes.values())
        }

        return jsonify(final_response), 200
    except Exception as e:
        logger.error(f"Error in course_insid: {str(e)}")
        return jsonify({"error": f"Failed to load course details: {str(e)}"}), 500
