-- ================================================================
-- Trigger Functions for Enrollment Management
-- Automatically handles enrollment counts and free course assignments
-- ================================================================

-- Drop existing triggers if they exist to avoid conflicts
DROP TRIGGER IF EXISTS `auto_enroll_free_courses_on_user_signup`;
DROP TRIGGER IF EXISTS `update_course_enrollment_count_on_insert`;
DROP TRIGGER IF EXISTS `update_course_enrollment_count_on_delete`;
DROP TRIGGER IF EXISTS `update_course_enrollment_count_on_update`;

-- ================================================================
-- 1. AUTO-ENROLLMENT TRIGGER FOR FREE COURSES
-- Automatically enrolls new users in all free courses (final_price = 0)
-- ================================================================

DELIMITER $$

CREATE TRIGGER `auto_enroll_free_courses_on_user_signup`
AFTER INSERT ON `user`
FOR EACH ROW
BEGIN
    -- Declare variables for cursor
    DECLARE done INT DEFAULT FALSE;
    DECLARE course_id_var INT;
    
    -- Declare cursor to get all free courses (where final_price = 0)
    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 cursor
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- Only proceed if the user is a student (avoid enrolling admins/instructors automatically)
    IF NEW.user_type = 'student' THEN
        
        -- Open cursor
        OPEN free_courses_cursor;
        
        -- Loop through all free courses
        read_loop: LOOP
            FETCH free_courses_cursor INTO course_id_var;
            
            IF done THEN
                LEAVE read_loop;
            END IF;
            
            -- Insert enrollment for free course
            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 cursor
        CLOSE free_courses_cursor;
        
    END IF;
    
END$$

DELIMITER ;

-- ================================================================
-- 2. ENROLLMENT COUNT UPDATE TRIGGERS
-- Automatically updates enrollments_count in courses table
-- ================================================================

-- Trigger for INSERT on enrollments
DELIMITER $$

CREATE TRIGGER `update_course_enrollment_count_on_insert`
AFTER INSERT ON `enrollments`
FOR EACH ROW
BEGIN
    DECLARE new_enrollments_count INT DEFAULT 0;
    
    -- Count total enrollments for the course
    SELECT COUNT(*) INTO new_enrollments_count
    FROM enrollments 
    WHERE course_id = NEW.course_id;
    
    -- Update the enrollments_count in the courses table
    UPDATE courses 
    SET enrollments_count = new_enrollments_count,
        updated_at = NOW()
    WHERE id = NEW.course_id;
    
END$$

DELIMITER ;

-- Trigger for DELETE on enrollments
DELIMITER $$

CREATE TRIGGER `update_course_enrollment_count_on_delete`
AFTER DELETE ON `enrollments`
FOR EACH ROW
BEGIN
    DECLARE new_enrollments_count INT DEFAULT 0;
    
    -- Count remaining enrollments for the course
    SELECT COUNT(*) INTO new_enrollments_count
    FROM enrollments 
    WHERE course_id = OLD.course_id;
    
    -- Update the enrollments_count in the courses table
    UPDATE courses 
    SET enrollments_count = new_enrollments_count,
        updated_at = NOW()
    WHERE id = OLD.course_id;
    
END$$

DELIMITER ;

-- Trigger for UPDATE on enrollments (in case course_id changes)
DELIMITER $$

CREATE TRIGGER `update_course_enrollment_count_on_update`
AFTER UPDATE ON `enrollments`
FOR EACH ROW
BEGIN
    DECLARE old_enrollments_count INT DEFAULT 0;
    DECLARE new_enrollments_count INT DEFAULT 0;
    
    -- If course_id changed, update both old and new courses
    IF OLD.course_id != NEW.course_id THEN
        
        -- Update count for OLD course
        SELECT COUNT(*) INTO old_enrollments_count
        FROM enrollments 
        WHERE course_id = OLD.course_id;
        
        UPDATE courses 
        SET enrollments_count = old_enrollments_count,
            updated_at = NOW()
        WHERE id = OLD.course_id;
        
        -- Update count for NEW course
        SELECT COUNT(*) INTO new_enrollments_count
        FROM enrollments 
        WHERE course_id = NEW.course_id;
        
        UPDATE courses 
        SET enrollments_count = new_enrollments_count,
            updated_at = NOW()
        WHERE id = NEW.course_id;
        
    END IF;
    
END$$

DELIMITER ;

-- ================================================================
-- 3. COURSE STATUS UPDATE TRIGGER
-- Updates course popularity metrics based on enrollment changes
-- ================================================================

DELIMITER $$

CREATE TRIGGER `update_course_popularity_metrics`
AFTER UPDATE ON `courses`
FOR EACH ROW
BEGIN
    -- Update is_trending flag based on enrollment growth
    IF NEW.enrollments_count > OLD.enrollments_count THEN
        -- If enrollments increased by more than 10% or 50+ new enrollments
        IF (NEW.enrollments_count - OLD.enrollments_count) >= 50 OR 
           (NEW.enrollments_count > OLD.enrollments_count * 1.1 AND OLD.enrollments_count > 0) THEN
            
            UPDATE courses 
            SET is_trending = 1,
                updated_at = NOW()
            WHERE id = NEW.id;
            
        END IF;
    END IF;
    
    -- Update is_top_selling flag based on enrollment count
    IF NEW.enrollments_count >= 1000 THEN
        UPDATE courses 
        SET is_top_selling = 1,
            updated_at = NOW()
        WHERE id = NEW.id;
    END IF;
    
END$$

DELIMITER ;

-- ================================================================
-- 4. INSTITUTE COURSE COUNT UPDATE TRIGGER
-- Updates total_courses count in institutes table
-- ================================================================

DELIMITER $$

CREATE TRIGGER `update_institute_total_courses`
AFTER UPDATE ON `courses`
FOR EACH ROW
BEGIN
    DECLARE total_course_count INT DEFAULT 0;
    
    -- Only update if approval_status changed or institute_id changed
    IF (OLD.approval_status != NEW.approval_status) OR 
       (OLD.institute_id != NEW.institute_id) OR
       (OLD.status != NEW.status) THEN
        
        -- Update count for NEW institute (if exists)
        IF NEW.institute_id IS NOT NULL THEN
            SELECT COUNT(*) INTO total_course_count
            FROM courses 
            WHERE institute_id = NEW.institute_id 
            AND approval_status = 'approved'
            AND status = 'active';
            
            UPDATE institutes 
            SET total_courses = total_course_count
            WHERE id = NEW.institute_id;
        END IF;
        
        -- Update count for OLD institute (if different and exists)
        IF OLD.institute_id IS NOT NULL AND OLD.institute_id != NEW.institute_id THEN
            SELECT COUNT(*) INTO total_course_count
            FROM courses 
            WHERE institute_id = OLD.institute_id 
            AND approval_status = 'approved'
            AND status = 'active';
            
            UPDATE institutes 
            SET total_courses = total_course_count
            WHERE id = OLD.institute_id;
        END IF;
        
    END IF;
    
END$$

DELIMITER ;

-- ================================================================
-- 5. USER ENROLLMENT STATISTICS TRIGGER
-- Updates user statistics when enrollments change
-- ================================================================

DELIMITER $$

CREATE TRIGGER `update_user_enrollment_stats`
AFTER INSERT ON `enrollments`
FOR EACH ROW
BEGIN
    -- Update user total enrollments (if such a field exists)
    -- This can be extended based on your user analytics requirements
    
    -- Log the enrollment activity
    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,
            'auto_enrolled', CASE WHEN NEW.payment_status = 'free' THEN true ELSE false END
        ),
        NOW()
    ) ON DUPLICATE KEY UPDATE created_at = NOW();
    
END$$

DELIMITER ;

-- ================================================================
-- 6. NOTIFICATION TRIGGER FOR NEW ENROLLMENTS
-- Creates notifications for relevant parties
-- ================================================================

DELIMITER $$

CREATE TRIGGER `create_enrollment_notifications`
AFTER INSERT ON `enrollments`
FOR EACH ROW
BEGIN
    DECLARE course_title VARCHAR(255);
    DECLARE instructor_id INT;
    DECLARE institute_id INT;
    
    -- Get course details
    SELECT title, instructor_id, institute_id 
    INTO course_title, instructor_id, institute_id
    FROM courses 
    WHERE id = NEW.course_id;
    
    -- Create notification for course instructor (if exists)
    IF instructor_id IS NOT NULL THEN
        INSERT INTO notifications (
            user_id,
            title,
            message,
            type,
            related_id,
            related_type,
            created_at
        ) VALUES (
            instructor_id,
            'New Student Enrolled',
            CONCAT('A new student has enrolled in your course: ', course_title),
            'enrollment',
            NEW.course_id,
            'course',
            NOW()
        ) ON DUPLICATE KEY UPDATE created_at = NOW();
    END IF;
    
    -- Create welcome notification for the student
    INSERT INTO notifications (
        user_id,
        title,
        message,
        type,
        related_id,
        related_type,
        created_at
    ) VALUES (
        NEW.user_id,
        'Welcome to Your New Course!',
        CONCAT('You have successfully enrolled in: ', course_title, '. Start learning now!'),
        'welcome',
        NEW.course_id,
        'course',
        NOW()
    ) ON DUPLICATE KEY UPDATE created_at = NOW();
    
END$$

DELIMITER ;

-- ================================================================
-- 7. ENROLLMENT EXPIRY CHECK TRIGGER
-- Sets expiry dates for paid courses (optional)
-- ================================================================

DELIMITER $$

CREATE TRIGGER `set_enrollment_expiry`
BEFORE INSERT ON `enrollments`
FOR EACH ROW
BEGIN
    -- Set expiry date for paid courses (1 year from enrollment)
    IF NEW.payment_status = 'paid' AND NEW.expiry_date IS NULL THEN
        SET NEW.expiry_date = DATE_ADD(NOW(), INTERVAL 1 YEAR);
    END IF;
    
    -- Free courses never expire
    IF NEW.payment_status = 'free' THEN
        SET NEW.expiry_date = NULL;
    END IF;
    
END$$

DELIMITER ;

-- ================================================================
-- INITIAL DATA FIXES
-- Fix any existing courses with incorrect enrollment counts
-- ================================================================

-- Update enrollment counts for all existing courses
UPDATE courses c 
SET enrollments_count = (
    SELECT COUNT(*) 
    FROM enrollments e 
    WHERE e.course_id = c.id
),
updated_at = NOW();

-- Update institute course counts
UPDATE institutes i 
SET total_courses = (
    SELECT COUNT(*) 
    FROM courses c 
    WHERE c.institute_id = i.id 
    AND c.approval_status = 'approved'
    AND c.status = 'active'
);

-- ================================================================
-- TESTING QUERIES (Optional - for verification)
-- ================================================================

/*
-- Test the auto-enrollment trigger by creating a test user
INSERT INTO user (username, password, email, user_type, contactno, fullname, doc) 
VALUES ('testuser', 'testpass', 'test@example.com', 'student', '1234567890', 'Test User', '2025-01-01');

-- Check if the user was automatically enrolled in free courses
SELECT u.username, c.title, e.payment_status, e.enrolled_at
FROM user u
JOIN enrollments e ON u.id = e.user_id
JOIN courses c ON e.course_id = c.id
WHERE u.username = 'testuser';

-- Check course enrollment counts
SELECT id, title, enrollments_count, 
    (SELECT COUNT(*) FROM enrollments WHERE course_id = courses.id) as actual_count
FROM courses
WHERE enrollments_count != (SELECT COUNT(*) FROM enrollments WHERE course_id = courses.id);
*/

-- ================================================================
-- End of Trigger Functions for Enrollment Management
-- ================================================================
