-- ============================================================================
-- Trigger: trg_after_exam_attempt_submit
-- ============================================================================
-- Database: LMS (Learning Management System)
-- Purpose: Automatically captures and stores student exam submissions into 
--          the student_answers table whenever an exam is submitted.
-- 
-- This trigger fires AFTER an exam_attempts record is updated to:
-- - 'submitted' status (student manually submits)
-- - 'completed' status (exam auto-submits on timeout)
-- 
-- The trigger will call the capture_student_exam_submission procedure
-- to insert each question with the student's answer into student_answers table.
-- ============================================================================

DELIMITER //

DROP TRIGGER IF EXISTS trg_after_exam_attempt_submit//

CREATE TRIGGER trg_after_exam_attempt_submit
AFTER UPDATE ON exam_attempts
FOR EACH ROW
BEGIN
    -- Only trigger when:
    -- 1. Status changes TO 'submitted' or 'completed'
    -- 2. This is an actual update (not just time_spent changes)
    -- 3. The student_id and exam_id are valid
    
    DECLARE v_answers_captured BOOLEAN DEFAULT FALSE;
    DECLARE v_existing_count INT DEFAULT 0;
    
    -- Check if status was changed to submitted or completed
    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
        
        -- Check if we already captured answers for this attempt
        -- to avoid duplicate captures on multiple updates
        SELECT COUNT(*) INTO v_existing_count
        FROM student_answers
        WHERE exam_id = NEW.exam_id
          AND student_id = NEW.student_id
          AND attempt_number = NEW.attempt_number
          AND submitted_at IS NOT NULL;
        
        -- Only capture if not already captured
        IF v_existing_count = 0 THEN
            -- Capture all answers for this submission
            CALL capture_student_exam_submission(
                NEW.exam_id,
                NEW.student_id,
                NEW.id
            );
        END IF;
        
    END IF;
    
END//


-- ============================================================================
-- Trigger: trg_before_exam_attempt_insert
-- ============================================================================
-- Purpose: Initialize answer records when a new exam attempt starts
-- This creates placeholder records in student_answers for all exam questions
-- ============================================================================

DROP TRIGGER IF EXISTS trg_before_exam_attempt_insert//

CREATE TRIGGER trg_before_exam_attempt_insert
AFTER INSERT ON exam_attempts
FOR EACH ROW
BEGIN
    -- Initialize empty answer records for each question in the exam
    -- This pre-populates the student_answers table so we have records ready
    
    INSERT IGNORE INTO student_answers (
        student_id,
        exam_id,
        attempt_id,
        question_id,
        answer_content,
        answer_type,
        status,
        is_draft,
        is_final,
        time_spent_seconds,
        ip_address,
        created_at
    )
    SELECT
        NEW.student_id,
        NEW.exam_id,
        NEW.id,
        eq.question_id,
        NULL,
        'text',
        'in_progress',
        1,
        0,
    FROM exam_questions eq
    WHERE eq.exam_id = NEW.exam_id;

END//


-- ============================================================================
-- Trigger: trg_student_answer_after_insert
-- ============================================================================
-- Purpose: Ensure submitted_at timestamp is set on insert
-- ============================================================================

DROP TRIGGER IF EXISTS trg_student_answer_after_insert//

CREATE TRIGGER trg_student_answer_after_insert
AFTER INSERT ON student_answers
FOR EACH ROW
BEGIN
    -- If submitted_at was not set, set it to now
    IF NEW.submitted_at IS NULL AND NEW.status IN ('submitted', 'graded') THEN
        UPDATE student_answers
        SET submitted_at = NOW()
        WHERE id = NEW.id;
    END IF;

END//


DELIMITER ;

-- ============================================================================
-- Summary of Triggers Created
-- ============================================================================
-- 1. trg_after_exam_attempt_submit
--    - Fires AFTER exam_attempts status is changed to 'submitted' or 'completed'
--    - Automatically calls capture_student_exam_submission to store answers
--    - Prevents duplicate captures by checking existing records
-- 
-- 2. trg_before_exam_attempt_insert  
--    - Fires AFTER a new exam attempt is created
--    - Pre-populates student_answers table with empty records for each question
-- 
-- 3. trg_student_answer_after_insert
--    - Fires AFTER a new answer is inserted into student_answers
--    - Ensures submitted_at timestamp is set for submitted/graded answers
-- ============================================================================
-- 
-- These triggers work together to automatically:
-- 1. Create placeholder answer records when exam starts
-- 2. Capture all answers when exam is submitted
-- 3. Ensure proper timestamps are set
-- ============================================================================
