-- ============================================================================
-- Stored Procedure: capture_student_exam_submission
-- ============================================================================
-- Database: LMS (Learning Management System)
-- Purpose: Automatically captures and stores student exam submissions by 
--          inserting each question along with the student's corresponding 
--          answer into the student_answer table whenever an exam is submitted.
-- 
-- This procedure is designed to be called when:
-- 1. A student submits an exam (via application trigger)
-- 2. For syncing data from student_answers to student_answer table
-- 3. For bulk migration of exam submissions
-- 
-- Usage:
--   CALL capture_student_exam_submission(p_exam_id, p_student_id, p_attempt_id);
-- ============================================================================

DELIMITER //

DROP PROCEDURE IF EXISTS capture_student_exam_submission//

-- ============================================================================
-- Main Procedure: Capture Student Exam Submission
-- ============================================================================
CREATE PROCEDURE capture_student_exam_submission(
    IN p_exam_id INT,
    IN p_student_id INT,
    IN p_attempt_id INT
)
BEGIN
    -- Declare variables
    DECLARE v_question_id INT;
    DECLARE v_exam_question_count INT DEFAULT 0;
    DECLARE v_inserted_count INT DEFAULT 0;
    DECLARE v_answer_text TEXT;
    DECLARE v_answer_choice VARCHAR(255);
    DECLARE v_is_correct BOOLEAN DEFAULT NULL;
    DECLARE v_score DECIMAL(10, 2) DEFAULT NULL;
    DECLARE v_status VARCHAR(50) DEFAULT 'submitted';
    DECLARE v_time_spent INT DEFAULT 0;
    DECLARE v_question_type VARCHAR(50);
    DECLARE v_points DECIMAL(10, 2);
    DECLARE v_option_id INT;
    DECLARE v_is_option_correct BOOLEAN;
    DECLARE v_ip_address VARCHAR(45);
    DECLARE v_attempt_number INT DEFAULT 1;
    
    -- Cursor for exam questions
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE exam_questions_cursor CURSOR FOR
        SELECT 
            eq.question_id,
            q.question_type,
            COALESCE(eq.points, 0) as points
        FROM exam_questions eq
        JOIN questions q ON eq.question_id = q.id
        WHERE eq.exam_id = p_exam_id
        ORDER BY eq.sort_order, eq.question_id;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- Validate inputs
    IF p_exam_id IS NULL OR p_student_id IS NULL OR p_attempt_id IS NULL THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Error: exam_id, student_id, and attempt_id are required parameters';
    END IF;
    
    -- Start transaction
    START TRANSACTION;
    
    BEGIN
        -- Get attempt details from exam_attempts
        SELECT 
            COALESCE(time_spent_seconds, 0),
            COALESCE(attempt_number, 1),
            COALESCE(ip_address, '')
        INTO
            v_time_spent,
            v_attempt_number,
            v_ip_address
        FROM exam_attempts 
        WHERE id = p_attempt_id AND student_id = p_student_id;
        
        -- If no attempt found, use default values
        IF v_time_spent IS NULL THEN
            SET v_time_spent = 0;
            SET v_attempt_number = 1;
        END IF;
        
        -- Open cursor to iterate through all exam questions
        OPEN exam_questions_cursor;
        
        question_loop: LOOP
            FETCH exam_questions_cursor INTO v_question_id, v_question_type, v_points;
            
            IF done THEN
                LEAVE question_loop;
            END IF;
            
            -- Initialize answer variables
            SET v_answer_text = NULL;
            SET v_answer_choice = NULL;
            SET v_is_correct = NULL;
            SET v_score = NULL;
            SET v_status = 'submitted';
            
            -- Try to get student's answer from student_answers table if it exists
            IF EXISTS (
                SELECT 1 FROM information_schema.tables 
                WHERE table_schema = DATABASE() 
                AND table_name = 'student_answers'
            ) THEN
                -- Get answer from student_answers table
                SELECT 
                    sa.answer_content,
                    sa.answer_content,
                    sa.is_correct,
                    COALESCE(sa.score, 0),
                    COALESCE(sa.status, 'submitted')
                INTO
                    v_answer_text,
                    v_answer_choice,
                    v_is_correct,
                    v_score,
                    v_status
                FROM student_answers sa
                WHERE sa.exam_id = p_exam_id
                  AND sa.student_id = p_student_id
                  AND sa.attempt_id = p_attempt_id
                  AND sa.question_id = v_question_id
                LIMIT 1;
            END IF;
            
            -- For multiple choice questions, get choice text and check correctness
            IF v_question_type IN ('mcq_single', 'true_false') THEN
                -- Get the option text for display
                SELECT qo.option_text 
                INTO v_answer_choice
                FROM question_options qo
                WHERE qo.question_id = v_question_id
                  AND qo.id = (SELECT sa.answer_content FROM student_answers sa 
                               WHERE sa.exam_id = p_exam_id 
                                 AND sa.student_id = p_student_id 
                                 AND sa.attempt_id = p_attempt_id 
                                 AND sa.question_id = v_question_id 
                               LIMIT 1)
                LIMIT 1;
                
                -- Check if the selected option is correct
                SELECT qo.is_correct INTO v_is_correct
                FROM question_options qo
                WHERE qo.question_id = v_question_id
                  AND qo.id = (SELECT sa.answer_content FROM student_answers sa 
                               WHERE sa.exam_id = p_exam_id 
                                 AND sa.student_id = p_student_id 
                                 AND sa.attempt_id = p_attempt_id 
                                 AND sa.question_id = v_question_id 
                               LIMIT 1)
                LIMIT 1;
                
                -- Set score based on correctness
                IF v_is_correct = TRUE THEN
                    SET v_score = v_points;
                ELSE
                    SET v_score = 0;
                END IF;
            END IF;
            
            -- For essay/short answer/code questions, mark as pending grading
            IF v_question_type IN ('essay', 'short_answer', 'code') THEN
                SET v_status = 'pending';
                SET v_score = NULL;
                SET v_is_correct = NULL;
                -- Keep the answer text
                SET v_answer_text = (SELECT sa.answer_content FROM student_answers sa 
                                     WHERE sa.exam_id = p_exam_id 
                                       AND sa.student_id = p_student_id 
                                       AND sa.attempt_id = p_attempt_id 
                                       AND sa.question_id = v_question_id 
                                     LIMIT 1);
            END IF;
            
            -- For multiple choice multiple answers
            IF v_question_type = 'mcq_multiple' THEN
                SET v_answer_text = (SELECT sa.answer_content FROM student_answers sa 
                                     WHERE sa.exam_id = p_exam_id 
                                       AND sa.student_id = p_student_id 
                                       AND sa.attempt_id = p_attempt_id 
                                       AND sa.question_id = v_question_id 
                                     LIMIT 1);
            END IF;
            
            -- Insert or update the answer in student_answer table
            INSERT INTO student_answer (
                student_id,
                exam_id,
                question_id,
                answer_text,
                answer_choice,
                is_correct,
                score,
                status,
                submitted_at,
                attempt_number,
                time_spent_seconds,
                ip_address,
                created_at
            )
            VALUES (
                p_student_id,
                p_exam_id,
                v_question_id,
                v_answer_text,
                v_answer_choice,
                v_is_correct,
                v_score,
                v_status,
                NOW(),
                v_attempt_number,
                v_time_spent,
                v_ip_address,
                NOW()
            )
            ON DUPLICATE KEY UPDATE
                answer_text = COALESCE(v_answer_text, answer_text),
                answer_choice = COALESCE(v_answer_choice, answer_choice),
                is_correct = COALESCE(v_is_correct, is_correct),
                score = COALESCE(v_score, score),
                status = v_status,
                submitted_at = NOW(),
                time_spent_seconds = v_time_spent,
                updated_at = NOW();
            
            SET v_inserted_count = v_inserted_count + 1;
            
        END LOOP;
        
        CLOSE exam_questions_cursor;
        
    END;
    
    COMMIT;
    
    -- Return success message
    SELECT 
        TRUE AS success,
        v_inserted_count AS answers_captured,
        CONCAT('Successfully captured ', v_inserted_count, ' answers for exam ', p_exam_id) AS message;

END//


-- ============================================================================
-- Simplified Procedure: Capture All Answers for Latest Attempt
-- ============================================================================
-- This procedure captures all answers for a student's latest exam attempt
-- It can be called with just exam_id and student_id
-- ============================================================================

DROP PROCEDURE IF EXISTS capture_student_exam_answers//

CREATE PROCEDURE capture_student_exam_answers(
    IN p_exam_id INT,
    IN p_student_id INT
)
BEGIN
    DECLARE v_attempt_id INT;
    DECLARE v_attempt_number INT;
    
    -- Validate inputs
    IF p_exam_id IS NULL OR p_student_id IS NULL THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Error: exam_id and student_id are required parameters';
    END IF;
    
    -- Get the latest submitted attempt
    SELECT id, attempt_number
    INTO v_attempt_id, v_attempt_number
    FROM exam_attempts
    WHERE exam_id = p_exam_id
      AND student_id = p_student_id
      AND status IN ('submitted', 'completed', 'graded')
    ORDER BY attempt_number DESC
    LIMIT 1;
    
    IF v_attempt_id IS NULL THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'No submitted attempt found for this exam and student';
    END IF;
    
    -- Call the main procedure
    CALL capture_student_exam_submission(p_exam_id, p_student_id, v_attempt_id);
    
END//


-- ============================================================================
-- Function: fn_get_student_exam_answer
-- ============================================================================
-- Returns a single student's answer for a specific exam and question
-- ============================================================================

DROP FUNCTION IF EXISTS fn_get_student_exam_answer//

CREATE FUNCTION fn_get_student_exam_answer(
    p_student_id INT,
    p_exam_id INT,
    p_question_id INT
)
RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE v_answer TEXT;
    
    SELECT 
        COALESCE(answer_text, answer_choice)
    INTO v_answer
    FROM student_answer
    WHERE student_id = p_student_id
      AND exam_id = p_exam_id
      AND question_id = p_question_id
    ORDER BY submitted_at DESC
    LIMIT 1;
    
    RETURN v_answer;
    
END//


-- ============================================================================
-- Function: fn_count_exam_submissions
-- ============================================================================
-- Counts the number of submissions captured for an exam
-- ============================================================================

DROP FUNCTION IF EXISTS fn_count_exam_submissions//

CREATE FUNCTION fn_count_exam_submissions(p_exam_id INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE v_count INT;
    
    SELECT COUNT(DISTINCT student_id)
    INTO v_count
    FROM student_answer
    WHERE exam_id = p_exam_id
      AND status IN ('submitted', 'graded');
    
    RETURN v_count;
    
END//


-- ============================================================================
-- Function: fn_get_exam_submission_status
-- ============================================================================
-- Gets the submission status for a student's exam
-- ============================================================================

DROP FUNCTION IF EXISTS fn_get_exam_submission_status//

CREATE FUNCTION fn_get_exam_submission_status(
    p_student_id INT,
    p_exam_id INT
)
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
    DECLARE v_status VARCHAR(50);
    
    SELECT 
        CASE 
            WHEN MAX(CASE WHEN status = 'graded' THEN 1 ELSE 0 END) = 1 THEN 'graded'
            WHEN MAX(CASE WHEN status = 'submitted' THEN 1 ELSE 0 END) = 1 THEN 'submitted'
            WHEN MAX(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) = 1 THEN 'pending'
            ELSE 'not_submitted'
        END
    INTO v_status
    FROM student_answer
    WHERE student_id = p_student_id
      AND exam_id = p_exam_id;
    
    RETURN v_status;
    
END//


DELIMITER ;

-- ============================================================================
-- Summary
-- ============================================================================
-- Created procedures and functions:
-- 1. capture_student_exam_submission(exam_id, student_id, attempt_id)
--    - Main procedure that captures all answers for a specific attempt
-- 2. capture_student_exam_answers(exam_id, student_id)
--    - Simplified procedure that uses latest submitted attempt
-- 3. fn_get_student_exam_answer(student_id, exam_id, question_id)
--    - Function to get a specific answer
-- 4. fn_count_exam_submissions(exam_id)
--    - Function to count submissions for an exam
-- 5. fn_get_exam_submission_status(student_id, exam_id)
--    - Function to get overall submission status
-- ============================================================================
