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

echo "Installing exam attempt triggers...\n";

try {
    // Drop existing triggers
    $pdo->exec("DROP TRIGGER IF EXISTS trg_after_exam_attempt_submit");
    $pdo->exec("DROP TRIGGER IF EXISTS trg_before_exam_attempt_insert");
    $pdo->exec("DROP TRIGGER IF EXISTS trg_student_answer_after_insert");
    echo "✓ Dropped existing triggers\n";

    // Create trg_after_exam_attempt_submit
    $pdo->exec("
        CREATE TRIGGER trg_after_exam_attempt_submit
        AFTER UPDATE ON exam_attempts
        FOR EACH ROW
        BEGIN
            DECLARE v_existing_count INT DEFAULT 0;

            IF NEW.status IN ('submitted', 'completed')
               AND OLD.status != NEW.status
               AND NEW.student_id IS NOT NULL
               AND NEW.exam_id IS NOT NULL THEN

                SELECT COUNT(*) INTO v_existing_count
                FROM student_answer
                WHERE exam_id = NEW.exam_id
                  AND student_id = NEW.student_id
                  AND attempt_number = NEW.attempt_number
                  AND submitted_at IS NOT NULL;

                IF v_existing_count = 0 THEN
                    CALL capture_student_exam_submission(
                        NEW.exam_id,
                        NEW.student_id,
                        NEW.id
                    );
                END IF;
            END IF;
        END
    ");
    echo "✓ Created trg_after_exam_attempt_submit\n";

    // Create trg_before_exam_attempt_insert
    $pdo->exec("
        CREATE TRIGGER trg_before_exam_attempt_insert
        AFTER INSERT ON exam_attempts
        FOR EACH ROW
        BEGIN
            INSERT IGNORE 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
            )
            SELECT
                NEW.student_id,
                NEW.exam_id,
                eq.question_id,
                NULL,
                NULL,
                NULL,
                NULL,
                'in_progress',
                NULL,
                NEW.attempt_number,
                0,
                NEW.ip_address,
                NOW()
            FROM exam_questions eq
            WHERE eq.exam_id = NEW.exam_id;
        END
    ");
    echo "✓ Created trg_before_exam_attempt_insert\n";

    // Create trg_student_answer_after_insert
    $pdo->exec("
        CREATE TRIGGER trg_student_answer_after_insert
        AFTER INSERT ON student_answer
        FOR EACH ROW
        BEGIN
            IF NEW.submitted_at IS NULL AND NEW.status IN ('submitted', 'graded') THEN
                UPDATE student_answer
                SET submitted_at = NOW()
                WHERE answer_id = NEW.answer_id;
            END IF;
        END
    ");
    echo "✓ Created trg_student_answer_after_insert\n";

    echo "All triggers installed successfully!\n";

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