from datetime import timedelta
from flask import jsonify
from database import get_cached_connection
from home import fetch_as_dict
from utils import fetch_assets, fetch_data, fetch_data_course, fetch_data_course_id, fetch_data_videos_wcourse_id, fetch_video_discussion, fetch_videos_description


def insert_discussion(request):
    conn = get_cached_connection()
    cursor = conn.cursor()
    sender_id = request.values.get('user_id')
    sender_name = request.values.get('sender_name')

    for_which = request.values.get('course')
    video_id = request.values.get('video_id')
    content = request.values.get('content')

    try:
        sql = """INSERT INTO video_discussion (sender_name, sender_id, content, video_id, for_which) VALUES (%s, %s, %s, %s, %s)"""
        cursor.execute(sql, (sender_name, sender_id,
                       content, video_id, for_which))
        conn.commit()
        response = {'response': 'Success'}

    except:
        response = {'response': 'Failure'}

    cursor.close()
    conn.close()
    return response


def video_details(request):
    conn = get_cached_connection()
    cursor = conn.cursor()

    course_idd = request.values.get('course_id')
    user_id = request.values.get('user_id')
    video_id = request.values.get('video_id')

    # Initialize cursor
    cursor = conn.cursor()

    # Fetch total number of classes for the given course
    cursor.execute("SELECT * FROM videos WHERE course_id = %s", (course_idd,))
    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_idd,))
    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
    cursor.execute("""
        SELECT 
            id, title, video_url, type, description, credit, 
            for_which, level, image_url, count_exercises, 
            count_students, count_plays, count_views, count_likes, 
            count_comments, short_description, course_id, is_live, 
            chat_id, is_active, free, topic_id,
            TIME_TO_SEC(video_duration) AS video_duration,
            TIME_TO_SEC(created_at) AS created_at_seconds, 
            TIME_TO_SEC(updated_at) AS updated_at_seconds
        FROM videos 
        WHERE course_id = %s
    """, (course_idd,))
    total_videos = fetch_as_dict(cursor)

    # Fetch reviews related to the course
    cursor.execute(
        "SELECT * FROM course_reviews WHERE course_id = %s and video_id = %s", (course_idd, video_id))
    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_idd,))

    result = fetch_as_dict(cursor)

    # Fetch topics related to the course with hierarchical structure
    cursor.execute("SELECT *, parent_topic_id, level, full_path FROM topics WHERE course_id = %s ORDER BY level, parent_topic_id, order_sequence", (course_idd,))
    topics = fetch_as_dict(cursor)

    # Fetch upcoming events related to the course
    cursor.execute(
        "SELECT * FROM upcoming_events WHERE course_id = %s", (course_idd,))
    upcoming_events = fetch_as_dict(cursor)

    # Fetch resources related to the video
    cursor.execute(
        "SELECT * FROM resources WHERE course_id = %s ", (course_idd,))
    resources = fetch_as_dict(cursor)

    # Fetch questions and their options for the course
    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  -- Join questions with topics using topic_id
        LEFT JOIN options o ON q.id = o.question_id  -- Left join options for each question
        WHERE t.course_id = %s  -- Filter by course_id (which is inside the Topics table)
        ORDER BY t.id, q.id  -- Order questions by topic_id first, then by question_id
    """, (course_idd,))
    questions_and_options = fetch_as_dict(cursor)

    # 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 and options by topic
    organized_quizzes = {}
    for entry in questions_and_options:
        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': entry['question_id'],
                'question_text': entry['question_text'],
                'options': []
            }
            organized_quizzes[topic_id]['questions'].append(question)

        # Add the option to the question's options list
        question['options'].append({
            'option_id': entry['option_id'],
            'option_text': entry['option_text'],
            'is_correct': entry['is_correct']
        })

    # Build the final response structure
    final_response = {
        "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": upcoming_events,
        "topics": topics,
        "total_videos": total_videos,
        # Return quizzes as a list of topics with questions
        "quizes": list(organized_quizzes.values())
    }

    cursor.close()
    conn.close()
    return jsonify(final_response), 200
