-- CORRECTED: Fix for enrollment triggers with proper column names
-- Based on actual table structure from ashlya_ashlya_academy_new.sql

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

DROP PROCEDURE IF EXISTS GetUserChatSummary;

DELIMITER $$
CREATE PROCEDURE GetUserChatSummary(IN p_user_id INT)
BEGIN
    SELECT 
        user_id,
        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 column names
-- ============================================================

DELIMITER $$

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

-- Trigger 2: Update course enrollment count (AFTER DELETE)
CREATE TRIGGER update_course_enrollment_count_on_delete 
AFTER DELETE ON enrollments 
FOR EACH ROW 
BEGIN
    UPDATE courses
    SET enrollments_count = (
        SELECT COUNT(*) 
        FROM enrollments 
        WHERE course_id = OLD.course_id
    )
    WHERE id = OLD.course_id;
END$$

-- Trigger 3: Update course enrollment count (AFTER INSERT)
CREATE TRIGGER update_course_enrollment_count_on_insert 
AFTER INSERT ON enrollments 
FOR EACH ROW 
BEGIN
    UPDATE courses
    SET enrollments_count = (
        SELECT COUNT(*) 
        FROM enrollments 
        WHERE course_id = NEW.course_id
    )
    WHERE id = NEW.course_id;
END$$

-- Trigger 4: Update course enrollment count (AFTER UPDATE)
CREATE TRIGGER update_course_enrollment_count_on_update 
AFTER UPDATE ON enrollments 
FOR EACH ROW 
BEGIN
    -- If course_id changed, update both old and new courses
    IF OLD.course_id != NEW.course_id THEN
        -- Update old course count
        UPDATE courses
        SET enrollments_count = (
            SELECT COUNT(*) 
            FROM enrollments 
            WHERE course_id = OLD.course_id
        )
        WHERE id = OLD.course_id;
        
        -- Update new course count
        UPDATE courses
        SET enrollments_count = (
            SELECT COUNT(*) 
            FROM enrollments 
            WHERE course_id = NEW.course_id
        )
        WHERE id = NEW.course_id;
    END IF;
END$$

DELIMITER ;

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

SELECT '=== Triggers on enrollments table ===' AS Info;

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

SELECT '' AS Spacer;
SELECT '=== Stored Procedure ===' AS Info;

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

SELECT '' AS Spacer;
SELECT '✅ All definer issues fixed successfully!' AS Status;
SELECT '🔄 Restart your Flask application now' AS Action;
