<?php
require_once 'config/database.php';
$pdo = getDB();

echo "Installing capture_student_exam_submission procedure...\n";

try {
    // Drop existing procedure
    $pdo->exec("DROP PROCEDURE IF EXISTS capture_student_exam_submission");
    echo "✓ Dropped existing procedure\n";

    // Create the procedure
    $pdo->exec("
        CREATE PROCEDURE capture_student_exam_submission(
            IN p_exam_id INT,
            IN p_student_id INT,
            IN p_attempt_id INT
        )
        BEGIN
            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;

            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;

            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;

            BEGIN
                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 v_time_spent IS NULL THEN
                    SET v_time_spent = 0;
                    SET v_attempt_number = 1;
                END IF;

                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;

                    SET v_answer_text = NULL;
                    SET v_answer_choice = NULL;
                    SET v_is_correct = NULL;
                    SET v_score = NULL;
                    SET v_status = 'submitted';

                    SELECT
                        COALESCE(sa.answer_content, ''),
                        COALESCE(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;

                    IF v_question_type IN ('mcq_single', 'true_false') THEN
                        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;

                        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;

                        IF v_is_correct = TRUE THEN
                            SET v_score = v_points;
                        ELSE
                            SET v_score = 0;
                        END IF;
                    END IF;

                    IF v_question_type IN ('essay', 'short_answer', 'code') THEN
                        -- Keep the status from student_answers (should be 'pending_manual_review')
                        -- but ensure it's not auto-graded
                        SET v_score = NULL;
                        SET v_is_correct = NULL;
                        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 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,
                        COALESCE(v_status, 'submitted'),
                        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 = COALESCE(v_status, 'submitted'),
                        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;

            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
    ");
    echo "✓ Created capture_student_exam_submission procedure\n";

    echo "Procedure installed successfully!\n";

} catch (Exception $e) {
    echo "Error installing procedure: " . $e->getMessage() . "\n";
}
?>