-- ============================================================
-- COMPLETE FIX FOR ENROLLMENT ERRORS
-- Fixes both definer issues AND missing table columns
-- ============================================================

-- STEP 1: Fix user_activity_log table structure
-- ============================================================

-- Drop the incomplete table

-- Recreate with proper structure
CREATE TABLE `user_activity_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `activity_type` varchar(100) NOT NULL,
  `activity_data` JSON DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_activity_type` (`activity_type`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- STEP 2: 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 3: Drop ALL enrollment triggers (with wrong definer)
-- ============================================================

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 4: Recreate triggers with CORRECT definer and logic
-- ============================================================

DELIMITER $$

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

-- Trigger 2: Update enrollment count (AFTER DELETE)
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$$

-- Trigger 3: Update enrollment count (AFTER INSERT)
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$$

-- Trigger 4: Update enrollment count (AFTER UPDATE)
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 course_id changed, update both courses
    IF OLD.course_id != NEW.course_id THEN
        -- Update old course
        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;
        
        -- Update new course
        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$$

-- Trigger 5: Log user enrollment activity (AFTER INSERT)
CREATE TRIGGER update_user_enrollment_stats 
AFTER INSERT ON enrollments 
FOR EACH ROW 
BEGIN
    -- Log enrollment activity in user_activity_log
    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$$

DELIMITER ;

-- ============================================================
-- STEP 5: Verification
-- ============================================================

SELECT '=== user_activity_log Table Structure ===' AS Info;

DESCRIBE user_activity_log;

SELECT '' AS Spacer;
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 Procedures ===' AS Info;

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

SELECT '' AS Spacer;
SELECT '✅ All enrollment errors fixed!' AS Status;
SELECT '🔄 Restart Flask app & test enrollment' AS Action;
