-- Comprehensive fix for ALL definer issues in the database
-- Run this to fix enrollment triggers and stored procedures

-- ============================================================
-- STEP 1: Fix Stored Procedure
-- ============================================================

DROP PROCEDURE IF EXISTS GetUserChatSummary;

DELIMITER $$
CREATE PROCEDURE GetUserChatSummary(IN p_user_id INT)
BEGIN
    SELECT 
        user_id,
        username,
        total_sessions,
        total_messages,
        total_ai_responses,
        avg_response_time,
        last_chat_date,
        most_used_model
    FROM user_chat_statistics
    WHERE user_id = p_user_id;
END$$
DELIMITER ;

-- ============================================================
-- STEP 2: Drop ALL enrollment triggers
-- ============================================================

DROP TRIGGER IF EXISTS set_enrollment_expiry;
DROP TRIGGER IF EXISTS update_course_enrollment_count_on_delete;
DROP TRIGGER IF EXISTS update_course_enrollment_count_on_insert;
DROP TRIGGER IF EXISTS update_course_enrollment_count_on_update;
DROP TRIGGER IF EXISTS update_enrollments_count_after_enrollment_delete;
DROP TRIGGER IF EXISTS update_enrollments_count_after_enrollment_insert;
DROP TRIGGER IF EXISTS update_enrollments_count_after_enrollment_update;
DROP TRIGGER IF EXISTS update_user_enrollment_stats;

-- ============================================================
-- STEP 3: Recreate triggers with correct definer
-- ============================================================

DELIMITER $$

-- Trigger 1: Set enrollment expiry date
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;
    
    -- Set default enrollment date if not provided
    IF NEW.enrollment_date IS NULL THEN
        SET NEW.enrollment_date = NOW();
    END IF;
END$$

-- Trigger 2: Update course enrollment count on DELETE
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 course enrollments count
    UPDATE courses
    SET enrollments_count = new_enrollments_count
    WHERE id = OLD.course_id;
END$$

-- Trigger 3: Update course enrollment count on INSERT
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 course enrollments count
    UPDATE courses
    SET enrollments_count = new_enrollments_count
    WHERE id = NEW.course_id;
END$$

-- Trigger 4: Update course enrollment count on UPDATE
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 old course count
        SELECT COUNT(*) INTO old_enrollments_count
        FROM enrollments
        WHERE course_id = OLD.course_id;
        
        UPDATE courses
        SET enrollments_count = old_enrollments_count
        WHERE id = OLD.course_id;
        
        -- Update new course count
        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$$

-- Trigger 5: Legacy - Update enrollments count (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 6: Legacy - Update enrollments count (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 7: Legacy - Update enrollments count (update)
CREATE TRIGGER update_enrollments_count_after_enrollment_update 
AFTER UPDATE ON enrollments 
FOR EACH ROW 
BEGIN
    DECLARE new_enrollments_count INT;

    -- Calculate the new enrollments count for the associated course
    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$$

-- Trigger 8: Update user enrollment statistics
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
    -- Currently just a placeholder for future analytics
    SELECT 1 INTO @dummy;
END$$

DELIMITER ;

-- ============================================================
-- STEP 4: Verification
-- ============================================================

SELECT 
    TRIGGER_NAME,
    EVENT_MANIPULATION,
    EVENT_OBJECT_TABLE,
    DEFINER,
    ACTION_TIMING
FROM information_schema.TRIGGERS 
WHERE EVENT_OBJECT_TABLE = 'enrollments'
ORDER BY TRIGGER_NAME;

SELECT 
    ROUTINE_NAME,
    ROUTINE_TYPE,
    DEFINER
FROM information_schema.ROUTINES
WHERE ROUTINE_NAME = 'GetUserChatSummary';

-- Success message
SELECT 'All definer issues fixed successfully!' AS Status,
       'Restart your Flask application now' AS Action;
