<?php
require_once 'config/database.php';

$db = getDB();

$sql = "
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_answers
        WHERE attempt_id = NEW.id
          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
";

try {
    $db->exec($sql);
    echo "Trigger installed successfully\n";
} catch (Exception $e) {
    echo "Error installing trigger: " . $e->getMessage() . "\n";
}
?>