from google.cloud import bigquery
from datetime import datetime
import uuid
import streamlit as st
from utils import generate_uuid


client = bigquery.Client()

def get_quizzes(lecture_id):
    query = """
        SELECT Quiz_name, Quiz_id, Lecture_id, Created_at
        FROM `{project}.{dataset}.Quizzes`
        WHERE Lecture_id = @lecture_id
        ORDER BY Created_at DESC
    """.format(project=st.secrets.project, dataset=st.secrets.dataset)
    job_config = bigquery.QueryJobConfig(
        query_parameters=[
            bigquery.ScalarQueryParameter("lecture_id", "STRING", lecture_id)
        ]
    )
    query_job = client.query(query, job_config=job_config)
    
    results = query_job.result()  # Waits for the query to finish

    quizzes = []
    for row in results:
        quizzes.append({
            "Quiz_id": row.Quiz_id,
            "Quiz_name": row.Quiz_name,
            "Lecture_id": row.Lecture_id,
            "Created_at": row.Created_at.isoformat()
        })
    
    return quizzes

def check_quiz_completion(quiz_id):
    client = bigquery.Client()
    query = """
        SELECT Quiz_completed
        FROM `{project}.{dataset}.Quizzes`
        WHERE Quiz_id = @quiz_id
    """.format(project=st.secrets.project, dataset=st.secrets.dataset)
    job_config = bigquery.QueryJobConfig(
        query_parameters=[
            bigquery.ScalarQueryParameter("quiz_id", "STRING", quiz_id)
        ]
    )
    query_job = client.query(query, job_config=job_config)
    results = query_job.result()
    for row in results:
        return row.Quiz_completed

def get_quiz_data(quiz_id):
    client = bigquery.Client()
    # Simplified query to only fetch question and all related answers without identifying the correct answer
    quiz_query = """
        SELECT
            q.Question_id,
            q.Text AS Question_Text,
            a.Answer_id,
            a.Text AS Answer_Text
        FROM `{project}.{dataset}.Questions` q
        JOIN `{project}.{dataset}.Answers` a ON q.Question_id = a.Question_id
        WHERE q.Quiz_id = @quiz_id
        ORDER BY q.Question_id, a.Answer_id
    """.format(project=st.secrets.project, dataset=st.secrets.dataset)

    job_config = bigquery.QueryJobConfig(
        query_parameters=[
            bigquery.ScalarQueryParameter("quiz_id", "STRING", quiz_id)
        ]
    )

    query_job = client.query(quiz_query, job_config=job_config)
    results = query_job.result()

    # Initialize a dictionary to organize the quiz data
    organized_quiz = {}
    for row in results:
        question_id = row.Question_id
        if question_id not in organized_quiz:
            organized_quiz[question_id] = {
                "question_text": row.Question_Text,
                "answers": []
            }
        # Append each answer to the respective question
        organized_quiz[question_id]["answers"].append({
            "answer_id": row.Answer_id,
            "answer_text": row.Answer_Text
        })

    return organized_quiz

def insert_quiz_data(quiz_data, lecture_id, quiz_name):
    """
    Quiz data is in json format, lecture_id is passed from session state.
    """

    existing_quizzes = get_quizzes(lecture_id)
    if any(quiz['Quiz_name'] == quiz_name for quiz in existing_quizzes):
        return False  # Indicates that a quiz with this name already exists

    # Generate a new quiz ID and current timestamp
    quiz_id = generate_uuid()
    created_at = datetime.utcnow()
    # Insert into the Quizzes table
    quiz_query = """
        INSERT INTO `{project}.{dataset}.Quizzes` (Quiz_id, Quiz_name, Quiz_completed, Lecture_id, Created_at)
        VALUES (@quiz_id, @quiz_name, @quiz_completed, @lecture_id, @created_at)
    """.format(project=st.secrets.project, dataset=st.secrets.dataset)
    client.query(quiz_query, job_config=bigquery.QueryJobConfig(
        query_parameters=[
            bigquery.ScalarQueryParameter("quiz_id", "STRING", quiz_id),
            bigquery.ScalarQueryParameter("quiz_name", "STRING", quiz_name),
            bigquery.ScalarQueryParameter("lecture_id", "STRING", lecture_id),
            bigquery.ScalarQueryParameter("quiz_completed", "BOOL", False),
            bigquery.ScalarQueryParameter("created_at", "TIMESTAMP", created_at)
        ]
    ))

    # Loop through each question in the data
    for question in quiz_data:
        question_id = generate_uuid()
        correct_answer_id = generate_uuid()
        
        # Insert into the Questions table
        question_query = """
            INSERT INTO `{project}.{dataset}.Questions` (Question_id, Quiz_id, Text, Correct_answer_id)
            VALUES (@question_id, @quiz_id, @text, @correct_answer_id)
        """.format(project=st.secrets.project, dataset=st.secrets.dataset)
        client.query(question_query, job_config=bigquery.QueryJobConfig(
            query_parameters=[
                bigquery.ScalarQueryParameter("question_id", "STRING", question_id),
                bigquery.ScalarQueryParameter("quiz_id", "STRING", quiz_id),
                bigquery.ScalarQueryParameter("text", "STRING", question['question']),
                bigquery.ScalarQueryParameter("correct_answer_id", "STRING", correct_answer_id),
            ]
        ))

        # Insert answers for each question
        for choice in question['choices']:
            is_correct = (choice == question['answer'])
            answer_id = correct_answer_id if is_correct else generate_uuid()
            answer_query = """
                INSERT INTO `{project}.{dataset}.Answers` (Answer_id, Question_id, Text)
                VALUES (@answer_id, @question_id, @text)
            """.format(project=st.secrets.project, dataset=st.secrets.dataset)
            client.query(answer_query, job_config=bigquery.QueryJobConfig(
                query_parameters=[
                    bigquery.ScalarQueryParameter("answer_id", "STRING", answer_id),
                    bigquery.ScalarQueryParameter("question_id", "STRING", question_id),
                    bigquery.ScalarQueryParameter("text", "STRING", choice),
                ]
            ))

    print("Quiz data insertion complete.")
    return quiz_id

def mark_quiz_as_completed(quiz_id):
    query = """
        UPDATE `{project}.{dataset}.Quizzes`
        SET Quiz_completed = TRUE
        WHERE Quiz_id = @quiz_id
    """.format(project=st.secrets.project, dataset=st.secrets.dataset)
    params = [bigquery.ScalarQueryParameter("quiz_id", "STRING", quiz_id)]
    job_config = bigquery.QueryJobConfig(query_parameters=params)
    client.query(query, job_config=job_config)

def insert_user_response(question_id, selected_answer_id):
    response_id = generate_uuid()
    query = """
        INSERT INTO `{project}.{dataset}.UserResponses` (Response_id, Question_id, Selected_answer_id)
        VALUES (@response_id, @question_id, @selected_answer_id)
    """.format(project=st.secrets.project, dataset=st.secrets.dataset)
    params = [
        bigquery.ScalarQueryParameter("response_id", "STRING", response_id),
        bigquery.ScalarQueryParameter("question_id", "STRING", question_id),
        bigquery.ScalarQueryParameter("selected_answer_id", "STRING", selected_answer_id),
    ]
    job_config = bigquery.QueryJobConfig(query_parameters=params)
    try:
        client.query(query, job_config=job_config)
    except Exception as e:
        print(f"Failed to insert due to: {e}")
        raise


def get_quiz_results(quiz_id):
    client = bigquery.Client()
    # Corrected query to fetch question details along with the correct and user-selected answers
    query = """
        SELECT 
            q.Question_id,
            q.Text AS Question_text,
            q.Correct_answer_id,
            a_correct.Text AS Correct_answer_text,
            a_user.Answer_id AS User_answer_id,
            a_user.Text AS User_answer_text
        FROM `{project}.{dataset}.Questions` q
        JOIN `{project}.{dataset}.Answers` a_correct ON q.Correct_answer_id = a_correct.Answer_id
        LEFT JOIN `{project}.{dataset}.UserResponses` ur ON q.Question_id = ur.Question_id
        LEFT JOIN `{project}.{dataset}.Answers` a_user ON ur.Selected_answer_id = a_user.Answer_id
        WHERE q.Quiz_id = @quiz_id
    """.format(project=st.secrets.project, dataset=st.secrets.dataset)
    job_config = bigquery.QueryJobConfig(
        query_parameters=[
            bigquery.ScalarQueryParameter("quiz_id", "STRING", quiz_id)
        ]
    )
    query_job = client.query(query, job_config=job_config)
    results = query_job.result()
    
    feedback_data = []
    for row in results:
        feedback_data.append({
            "question_text": row.Question_text,
            "correct_answer_text": row.Correct_answer_text,
            "user_answer_text": row.User_answer_text
        })
    print(feedback_data)
    return feedback_data


def insert_feedback(quiz_id, feedback_text):
    print("Inserting feedback...")
    client = bigquery.Client()
    feedback_id = generate_uuid()
    query = """
        INSERT INTO `{project}.{dataset}..Feedback` (Feedback_id, Quiz_id, Text)
        VALUES (@feedback_id, @quiz_id, @feedback_text)
    """.format(project=st.secrets.project, dataset=st.secrets.dataset)
    job_config = bigquery.QueryJobConfig(
        query_parameters=[
            bigquery.ScalarQueryParameter("feedback_id", "STRING", feedback_id),
            bigquery.ScalarQueryParameter("quiz_id", "STRING", quiz_id),
            bigquery.ScalarQueryParameter("feedback_text", "STRING", feedback_text),
        ]
    )
    client.query(query, job_config=job_config)

def get_feedback(quiz_id):
    client = bigquery.Client()
    query = """
        SELECT Text FROM `{project}.{dataset}.Feedback` WHERE Quiz_id = @quiz_id LIMIT 1
    """.format(project=st.secrets.project, dataset=st.secrets.dataset)
    job_config = bigquery.QueryJobConfig(
        query_parameters=[
            bigquery.ScalarQueryParameter("quiz_id", "STRING", quiz_id)
        ]
    )
    query_job = client.query(query, job_config=job_config)
    results = query_job.result()
    feedback_text = ''
    for row in results:
        feedback_text = row.Text
    return feedback_text