-- Optimizations & New Triggers for Ashlya Academy
-- Execute each block separately or ensure DELIMITER is set correctly in your SQL client.

DELIMITER $$

-- 1. Optimized Auto-Enrollment (Replaces Cursor-based approach)
DROP TRIGGER IF EXISTS `auto_enroll_free_courses_on_user_signup_v2` $$
CREATE TRIGGER `auto_enroll_free_courses_on_user_signup_v2` AFTER INSERT ON `user`
FOR EACH ROW BEGIN
    IF NEW.user_type = 'student' THEN
        INSERT INTO enrollments (
            course_id, user_id, enrolled_at, progress, is_completed, 
            payment_status, payment_amount, created_by, notes
        )
        SELECT 
            id, NEW.id, NOW(), 0, 0, 
            'free', 0.00, NULL, 
            CONCAT('Auto-enrolled in free course on user signup at ', NOW())
        FROM courses 
        WHERE (price - discount) <= 0 
          AND status = 'active' 
          AND approval_status = 'approved'
          AND is_public = 1;
    END IF;
END $$


-- 2. Gamification: Award Course Completion Badge
DROP TRIGGER IF EXISTS `auto_award_course_completion_badge` $$
CREATE TRIGGER `auto_award_course_completion_badge` AFTER UPDATE ON `enrollments`
FOR EACH ROW BEGIN
    DECLARE badge_title VARCHAR(255) DEFAULT 'Course Finisher';
    DECLARE badge_icon VARCHAR(255) DEFAULT 'https://cdn-icons-png.freepik.com/256/2436/2436888.png';
    
    IF NEW.progress = 100 AND OLD.progress < 100 THEN
        INSERT INTO achievements (title, icon, uid, issued_date)
        SELECT badge_title, badge_icon, NEW.user_id, NOW()
        WHERE NOT EXISTS (
            SELECT 1 FROM achievements 
            WHERE uid = NEW.user_id AND title = badge_title AND issued_date = DATE(NOW())
        );
    END IF;
END $$


-- 3. Gamification: Track Learning Streak
DROP TRIGGER IF EXISTS `track_user_learning_streak_on_quiz` $$
CREATE TRIGGER `track_user_learning_streak_on_quiz` AFTER INSERT ON `quiz_sessions`
FOR EACH ROW BEGIN
    UPDATE `user` SET last_active_at = NOW() WHERE id = NEW.user_id;
END $$


-- 4. Notifications: Notify Instructor on New Doubt
DROP TRIGGER IF EXISTS `notify_instructor_on_new_doubt` $$
CREATE TRIGGER `notify_instructor_on_new_doubt` AFTER INSERT ON `doubts`
FOR EACH ROW BEGIN
    DECLARE instructor_id_var INT;
    DECLARE course_title_var VARCHAR(255);
    
    SELECT primary_instructor_id, title INTO instructor_id_var, course_title_var
    FROM courses WHERE id = NEW.course_id;
    
    IF instructor_id_var IS NOT NULL THEN
        INSERT INTO notifications (
            user_id, title, message, type, related_id, related_type, is_read, created_at
        ) VALUES (
            instructor_id_var,
            'New Doubt Posted',
            CONCAT('A new doubt has been posted in ', course_title_var, ': "', LEFT(NEW.title, 30), '..."'),
            'system',
            NEW.id,
            'doubt',
            0,
            NOW()
        );
    END IF;
END $$


-- 5. Notifications: Notify Student on Doubt Answer
DROP TRIGGER IF EXISTS `notify_student_on_doubt_answer` $$
CREATE TRIGGER `notify_student_on_doubt_answer` AFTER INSERT ON `doubt_answers`
FOR EACH ROW BEGIN
    DECLARE student_id_var INT;
    DECLARE doubt_title_var VARCHAR(255);
    
    SELECT user_id, title INTO student_id_var, doubt_title_var
    FROM doubts WHERE id = NEW.doubt_id;
    
    IF student_id_var != NEW.user_id THEN
        INSERT INTO notifications (
            user_id, title, message, type, related_id, related_type, is_read, created_at
        ) VALUES (
            student_id_var,
            'New Answer to your Doubt',
            CONCAT('Someone answered your doubt: "', LEFT(doubt_title_var, 30), '..."'),
            'system',
            NEW.doubt_id,
            'doubt',
            0,
            NOW()
        );
    END IF;
END $$


-- 6. Search: Sync AI Chat to Search Index
DROP TRIGGER IF EXISTS `sync_ai_chat_search_index` $$
CREATE TRIGGER `sync_ai_chat_search_index` AFTER INSERT ON `ai_chat_messages`
FOR EACH ROW BEGIN
    DECLARE user_id_var INT;

    SELECT CAST(user_id AS UNSIGNED) INTO user_id_var 
    FROM ai_chat_sessions 
    WHERE id = NEW.session_id;

    IF user_id_var IS NOT NULL THEN
        INSERT INTO ai_chat_search_index (
            session_id, message_id, user_id, searchable_content, created_at
        ) VALUES (
            NEW.session_id,
            NEW.id,
            user_id_var,
            LEFT(NEW.content, 500),
            NOW()
        );
    END IF;
END $$


-- 7. Data Integrity: Prevent Duplicate Active Enrollments
DROP TRIGGER IF EXISTS `prevent_duplicate_active_enrollments` $$
CREATE TRIGGER `prevent_duplicate_active_enrollments` BEFORE INSERT ON `enrollments`
FOR EACH ROW BEGIN
    IF EXISTS (
        SELECT 1 FROM enrollments 
        WHERE user_id = NEW.user_id 
          AND course_id = NEW.course_id 
          AND (expiry_date IS NULL OR expiry_date > NOW())
    ) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'User already has an active enrollment for this course';
    END IF;
END $$


-- 8. Data Integrity: Cascade Soft Delete Topics from Course
DROP TRIGGER IF EXISTS `cascade_soft_delete_topics` $$
CREATE TRIGGER `cascade_soft_delete_topics` AFTER UPDATE ON `courses`
FOR EACH ROW BEGIN
    IF NEW.status = 'archived' AND OLD.status != 'archived' THEN
        UPDATE topics 
        SET status = 'inactive' 
        WHERE course_id = NEW.id;
    END IF;
END $$

DELIMITER ;
