-- Fix for enrollment triggers with invalid definer
-- Run this SQL script to fix the "definer does not exist" error

-- Step 1: Drop existing triggers that may have invalid definers
DROP TRIGGER IF EXISTS update_enrollments_count_after_enrollment_insert;
DROP TRIGGER IF EXISTS update_enrollments_count_after_enrollment_delete;
DROP TRIGGER IF EXISTS update_enrollments_count_after_enrollment_update;

-- Step 2: Recreate triggers with current user as definer
DELIMITER $$

-- Trigger: Update enrollments count after INSERT
CREATE TRIGGER update_enrollments_count_after_enrollment_insert 
AFTER INSERT ON enrollments 
FOR EACH ROW 
BEGIN
    DECLARE new_enrollments_count INT;

    -- Calculate the new enrollments count for the associated course
    SELECT COUNT(*) INTO new_enrollments_count
    FROM enrollments
    WHERE course_id = NEW.course_id;

    -- Update the course's enrollments count
    UPDATE courses
    SET enrollments_count = new_enrollments_count
    WHERE id = NEW.course_id;
END$$

-- Trigger: Update enrollments count after DELETE
CREATE TRIGGER update_enrollments_count_after_enrollment_delete 
AFTER DELETE ON enrollments 
FOR EACH ROW 
BEGIN
    DECLARE new_enrollments_count INT;

    -- Calculate the new enrollments count for the associated course
    SELECT COUNT(*) INTO new_enrollments_count
    FROM enrollments
    WHERE course_id = OLD.course_id;

    -- Update the course's enrollments count
    UPDATE courses
    SET enrollments_count = new_enrollments_count
    WHERE id = OLD.course_id;
END$$

-- Trigger: Update enrollments count after UPDATE
CREATE TRIGGER update_enrollments_count_after_enrollment_update 
AFTER UPDATE ON enrollments 
FOR EACH ROW 
BEGIN
    DECLARE new_enrollments_count INT;

    -- If course_id changed, update both old and new courses
    IF OLD.course_id != NEW.course_id THEN
        -- Update old course
        SELECT COUNT(*) INTO new_enrollments_count
        FROM enrollments
        WHERE course_id = OLD.course_id;
        
        UPDATE courses
        SET enrollments_count = new_enrollments_count
        WHERE id = OLD.course_id;
        
        -- Update new course
        SELECT COUNT(*) INTO new_enrollments_count
        FROM enrollments
        WHERE course_id = NEW.course_id;
        
        UPDATE courses
        SET enrollments_count = new_enrollments_count
        WHERE id = NEW.course_id;
    END IF;
END$$

DELIMITER ;

-- Verification: Check that triggers are recreated
SELECT 
    TRIGGER_NAME,
    EVENT_MANIPULATION,
    EVENT_OBJECT_TABLE,
    DEFINER
FROM information_schema.TRIGGERS 
WHERE EVENT_OBJECT_TABLE = 'enrollments'
ORDER BY TRIGGER_NAME;

-- Success message
SELECT 'Enrollment triggers recreated successfully!' AS Status;
