CREATE TRIGGER `auto_enroll_free_courses_on_user_signup` AFTER INSERT ON `user`
 FOR EACH ROW BEGIN
    
    DECLARE done INT DEFAULT FALSE;
    DECLARE course_id_var INT;
    
    
    DECLARE free_courses_cursor CURSOR FOR 
        SELECT id 
        FROM courses 
        WHERE (price - discount) <= 0 
        AND status = 'active' 
        AND approval_status = 'approved'
        AND is_public = 1;
    
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    
    IF NEW.user_type = 'student' THEN
        
        
        OPEN free_courses_cursor;
        
        
        read_loop: LOOP
            FETCH free_courses_cursor INTO course_id_var;
            
            IF done THEN
                LEAVE read_loop;
            END IF;
            
            
            INSERT INTO enrollments (
                course_id,
                user_id,
                enrolled_at,
                progress,
                is_completed,
                payment_status,
                payment_amount,
                created_by,
                notes
            ) VALUES (
                course_id_var,
                NEW.id,
                NOW(),
                0,
                0,
                'free',
                0.00,
                NULL,
                CONCAT('Auto-enrolled in free course on user signup at ', NOW())
            );
            
        END LOOP;
        
        
        CLOSE free_courses_cursor;
        
    END IF;
    
END

CREATE TRIGGER `mark_user_coupon_used` AFTER INSERT ON `coupon_usage_history`
 FOR EACH ROW BEGIN
    
    UPDATE `user_coupon_assignments` 
    SET `is_used` = 1, `used_at` = NOW() 
    WHERE `user_id` = NEW.user_id AND `coupon_id` = NEW.coupon_id AND `is_used` = 0;
END

CREATE TRIGGER `set_enrollment_expiry` BEFORE INSERT ON `enrollments`
 FOR EACH ROW BEGIN
    
    IF NEW.payment_status = 'paid' AND NEW.expiry_date IS NULL THEN
        SET NEW.expiry_date = DATE_ADD(NOW(), INTERVAL 1 YEAR);
    END IF;
    
    
    IF NEW.enrolled_at IS NULL THEN
        SET NEW.enrolled_at = NOW();
    END IF;
END

CREATE TRIGGER `update_answer_likes_count` AFTER INSERT ON `doubt_answer_likes`
 FOR EACH ROW BEGIN
  UPDATE doubt_answers SET likes = likes + 1 WHERE id = NEW.answer_id;
END

CREATE TRIGGER `update_answer_likes_count_on_delete` AFTER DELETE ON `doubt_answer_likes`
 FOR EACH ROW BEGIN
  UPDATE doubt_answers SET likes = likes - 1 WHERE id = OLD.answer_id;
END

CREATE TRIGGER `update_coupon_balance` AFTER INSERT ON `coupon_usage_history`
 FOR EACH ROW BEGIN
    
    IF (SELECT `coupon_type` FROM `coupons` WHERE `id` = NEW.coupon_id) = 'balance' THEN
        UPDATE `coupons` 
        SET `balance` = `balance` - NEW.amount_used 
        WHERE `id` = NEW.coupon_id;
    END IF;
END

CREATE TRIGGER `update_coupon_usage_count` AFTER INSERT ON `coupon_usage_history`
 FOR EACH ROW BEGIN
    UPDATE `coupons` 
    SET `used_count` = `used_count` + 1 
    WHERE `id` = NEW.coupon_id;
END

CREATE TRIGGER `update_course_enrollment_count_on_delete` AFTER DELETE ON `enrollments`
 FOR EACH ROW BEGIN
    DECLARE new_count INT DEFAULT 0;
    
    SELECT COUNT(*) INTO new_count
    FROM enrollments 
    WHERE course_id = OLD.course_id;
    
    UPDATE courses 
    SET enrollments_count = new_count,
        updated_at = NOW()
    WHERE id = OLD.course_id;
END

CREATE TRIGGER `update_course_enrollment_count_on_insert` AFTER INSERT ON `enrollments`
 FOR EACH ROW BEGIN
    DECLARE new_count INT DEFAULT 0;
    
    SELECT COUNT(*) INTO new_count
    FROM enrollments 
    WHERE course_id = NEW.course_id;
    
    UPDATE courses 
    SET enrollments_count = new_count,
        updated_at = NOW()
    WHERE id = NEW.course_id;
END

CREATE TRIGGER `update_course_enrollment_count_on_update` AFTER UPDATE ON `enrollments`
 FOR EACH ROW BEGIN
    DECLARE old_count INT DEFAULT 0;
    DECLARE new_count INT DEFAULT 0;
    
    
    IF OLD.course_id != NEW.course_id THEN
        
        SELECT COUNT(*) INTO old_count
        FROM enrollments 
        WHERE course_id = OLD.course_id;
        
        UPDATE courses 
        SET enrollments_count = old_count,
            updated_at = NOW()
        WHERE id = OLD.course_id;
        
        
        SELECT COUNT(*) INTO new_count
        FROM enrollments 
        WHERE course_id = NEW.course_id;
        
        UPDATE courses 
        SET enrollments_count = new_count,
            updated_at = NOW()
        WHERE id = NEW.course_id;
    END IF;
END

CREATE TRIGGER `update_course_popularity_metrics` BEFORE UPDATE ON `courses`
 FOR EACH ROW BEGIN
  -- example: keep updated_at fresh
  SET NEW.updated_at = CURRENT_TIMESTAMP;

  -- ✅ compute metrics by assigning NEW values (DON'T update courses table here)
  -- Change these formulas/column names to your schema:
  -- SET NEW.popularity_score =
  --   (NEW.enrollments_count * 5) + (NEW.count_views * 1) + (NEW.count_likes * 2) + (NEW.count_comments * 3);

END

CREATE TRIGGER `update_doubt_likes_count` AFTER INSERT ON `doubt_likes`
 FOR EACH ROW BEGIN
  UPDATE doubts SET likes = likes + 1 WHERE id = NEW.doubt_id;
END

CREATE TRIGGER `update_doubt_likes_count_on_delete` AFTER DELETE ON `doubt_likes`
 FOR EACH ROW BEGIN
  UPDATE doubts SET likes = likes - 1 WHERE id = OLD.doubt_id;
END

CREATE TRIGGER `update_duration_hours_after_topic_delete` AFTER DELETE ON `topics`
 FOR EACH ROW BEGIN
    DECLARE new_duration_hours INT;

    
    SELECT SUM(duration_minutes) / 60
    INTO new_duration_hours
    FROM topics
    WHERE course_id = OLD.course_id;

    
    UPDATE courses
    SET duration_hours = new_duration_hours
    WHERE id = OLD.course_id;
END

CREATE TRIGGER `update_duration_hours_after_topic_insert` AFTER INSERT ON `topics`
 FOR EACH ROW BEGIN
    DECLARE new_duration_hours INT;

    
    SELECT SUM(duration_minutes) / 60
    INTO new_duration_hours
    FROM topics
    WHERE course_id = NEW.course_id;

    
    UPDATE courses
    SET duration_hours = new_duration_hours
    WHERE id = NEW.course_id;
END

CREATE TRIGGER `update_duration_hours_after_topic_update` AFTER UPDATE ON `topics`
 FOR EACH ROW BEGIN
    DECLARE new_duration_hours INT;

    
    SELECT SUM(duration_minutes) / 60
    INTO new_duration_hours
    FROM topics
    WHERE course_id = NEW.course_id;

    
    UPDATE courses
    SET duration_hours = new_duration_hours
    WHERE id = NEW.course_id;
END

CREATE TRIGGER `update_duration_minutes_after_video_delete` AFTER DELETE ON `videos`
 FOR EACH ROW BEGIN
    DECLARE new_duration_minutes INT;

    
    SELECT SUM(TIME_TO_SEC(video_duration)) / 60
    INTO new_duration_minutes
    FROM videos
    WHERE topic_id = OLD.topic_id;

    
    UPDATE topics
    SET duration_minutes = new_duration_minutes
    WHERE id = OLD.topic_id;
END

CREATE TRIGGER `update_duration_minutes_after_video_insert` AFTER INSERT ON `videos`
 FOR EACH ROW BEGIN
    DECLARE new_duration_minutes INT;

    
    SELECT SUM(TIME_TO_SEC(video_duration)) / 60
    INTO new_duration_minutes
    FROM videos
    WHERE topic_id = NEW.topic_id;

    
    UPDATE topics
    SET duration_minutes = new_duration_minutes
    WHERE id = NEW.topic_id;
END

CREATE TRIGGER `update_duration_minutes_after_video_update` AFTER UPDATE ON `videos`
 FOR EACH ROW BEGIN
    DECLARE new_duration_minutes INT;

    
    SELECT SUM(TIME_TO_SEC(video_duration)) / 60
    INTO new_duration_minutes
    FROM videos
    WHERE topic_id = NEW.topic_id;

    
    UPDATE topics
    SET duration_minutes = new_duration_minutes
    WHERE id = NEW.topic_id;
END

CREATE TRIGGER `update_institute_course_count` AFTER INSERT ON `courses`
 FOR EACH ROW BEGIN
  IF NEW.institute_id IS NOT NULL THEN
    UPDATE institutes
    SET total_courses = total_courses + 1
    WHERE id = NEW.institute_id;
  END IF;
END

CREATE TRIGGER `update_institute_course_count_del` AFTER DELETE ON `courses`
 FOR EACH ROW BEGIN
  IF OLD.institute_id IS NOT NULL THEN
    UPDATE institutes
    SET total_courses = GREATEST(total_courses - 1, 0)
    WHERE id = OLD.institute_id;
  END IF;
END

CREATE TRIGGER `update_institute_instructor_count` AFTER INSERT ON `instructors`
 FOR EACH ROW BEGIN 
    IF NEW.institute_id IS NOT NULL THEN
        UPDATE institutes 
        SET total_instructors = (
            SELECT COUNT(*) FROM instructors 
            WHERE institute_id = NEW.institute_id AND status = 'active'
        ) 
        WHERE id = NEW.institute_id;
    END IF;
END

CREATE TRIGGER `update_institute_rating` AFTER INSERT ON `institute_reviews`
 FOR EACH ROW BEGIN 
    UPDATE institutes 
    SET average_rating = (
        SELECT AVG(rating) FROM institute_reviews 
        WHERE institute_id = NEW.institute_id AND status = 'approved'
    ),
    total_reviews = (
        SELECT COUNT(*) FROM institute_reviews 
        WHERE institute_id = NEW.institute_id AND status = 'approved'
    )
    WHERE id = NEW.institute_id;
END

CREATE TRIGGER `update_institute_total_courses` AFTER UPDATE ON `courses`
 FOR EACH ROW BEGIN
  IF OLD.institute_id <> NEW.institute_id THEN

    IF OLD.institute_id IS NOT NULL THEN
      UPDATE institutes
      SET total_courses = GREATEST(total_courses - 1, 0)
      WHERE id = OLD.institute_id;
    END IF;

    IF NEW.institute_id IS NOT NULL THEN
      UPDATE institutes
      SET total_courses = total_courses + 1
      WHERE id = NEW.institute_id;
    END IF;

  END IF;
END

CREATE TRIGGER `update_reviews_count_after_review_delete` AFTER DELETE ON `course_reviews`
 FOR EACH ROW BEGIN
    DECLARE new_reviews_count INT;

    
    SELECT COUNT(*) INTO new_reviews_count
    FROM course_reviews
    WHERE course_id = OLD.course_id;

    
    UPDATE courses
    SET reviews_count = new_reviews_count
    WHERE id = OLD.course_id;
END

CREATE TRIGGER `update_reviews_count_after_review_insert` AFTER INSERT ON `course_reviews`
 FOR EACH ROW BEGIN
    DECLARE new_reviews_count INT;

    
    SELECT COUNT(*) INTO new_reviews_count
    FROM course_reviews
    WHERE course_id = NEW.course_id;

    
    UPDATE courses
    SET reviews_count = new_reviews_count
    WHERE id = NEW.course_id;
END

CREATE TRIGGER `update_reviews_count_after_review_update` AFTER UPDATE ON `course_reviews`
 FOR EACH ROW BEGIN
    DECLARE new_reviews_count INT;

    
    SELECT COUNT(*) INTO new_reviews_count
    FROM course_reviews
    WHERE course_id = NEW.course_id;

    
    UPDATE courses
    SET reviews_count = new_reviews_count
    WHERE id = NEW.course_id;
END

CREATE TRIGGER `update_tag_count` AFTER INSERT ON `doubt_tag_relation`
 FOR EACH ROW BEGIN
  UPDATE doubt_tags SET count = count + 1 WHERE id = NEW.tag_id;
END

CREATE TRIGGER `update_tag_count_on_delete` AFTER DELETE ON `doubt_tag_relation`
 FOR EACH ROW BEGIN
  UPDATE doubt_tags SET count = count - 1 WHERE id = OLD.tag_id;
END

CREATE TRIGGER `update_user_analytics_after_quiz_session` AFTER INSERT ON `quiz_sessions`
 FOR EACH ROW BEGIN
    
    INSERT INTO user_analytics_summary (
        user_id, total_quizzes, total_questions, total_correct,
        total_time_spent, avg_score, best_score, last_activity
    )
    VALUES (
        NEW.user_id, 1, NEW.total_questions, NEW.correct_answers,
        NEW.time_taken, NEW.score_percentage, NEW.score_percentage, NEW.completed_at
    )
    ON DUPLICATE KEY UPDATE
        total_quizzes = total_quizzes + 1,
        total_questions = total_questions + NEW.total_questions,
        total_correct = total_correct + NEW.correct_answers,
        total_time_spent = total_time_spent + NEW.time_taken,
        avg_score = (SELECT AVG(score_percentage) FROM quiz_sessions WHERE user_id = NEW.user_id),
        best_score = GREATEST(best_score, NEW.score_percentage),
        last_activity = NEW.completed_at,
        updated_at = CURRENT_TIMESTAMP;
        
    
    INSERT INTO user_learning_streaks (
        user_id, streak_date, quizzes_taken, avg_score, total_time
    )
    VALUES (
        NEW.user_id, DATE(NEW.completed_at), 1, NEW.score_percentage, NEW.time_taken
    )
    ON DUPLICATE KEY UPDATE
        quizzes_taken = quizzes_taken + 1,
        avg_score = (avg_score * (quizzes_taken - 1) + NEW.score_percentage) / quizzes_taken,
        total_time = total_time + NEW.time_taken;
        
    
    IF NEW.topic_ids IS NOT NULL THEN
        
        
        SET @topic_update = 'Topic analytics update needed';
    END IF;
END

CREATE TRIGGER `update_user_enrollment_stats` AFTER INSERT ON `enrollments`
 FOR EACH ROW BEGIN
    
    INSERT INTO user_activity_log (
        user_id,
        activity_type,
        activity_data,
        created_at
    ) VALUES (
        NEW.user_id,
        'course_enrollment',
        JSON_OBJECT(
            'course_id', NEW.course_id,
            'payment_status', NEW.payment_status,
            'payment_amount', NEW.payment_amount,
            'enrollment_id', LAST_INSERT_ID()
        ),
        NOW()
    );
END
