-- =====================================================
-- 迁移 V36: 重命名 course 表为 course_package
--
-- 统一命名：course → course_package（课程包表）
-- 同时更新所有外键关联
-- =====================================================

SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================
-- 第一步：重命名主表（幂等操作）
-- =====================================================

SELECT '=== 检查并重命名 course 表为 course_package ===' as info;

-- 只有当 course 表存在时才重命名
-- 如果 course_package 表已存在，则跳过重命名
SET @table_exists = (SELECT COUNT(*) FROM information_schema.tables
                     WHERE table_schema = DATABASE() AND table_name = 'course');

SET @target_exists = (SELECT COUNT(*) FROM information_schema.tables
                      WHERE table_schema = DATABASE() AND table_name = 'course_package');

-- 如果 course 表存在且 course_package 不存在，则重命名
-- 使用预处理语句实现条件逻辑
SET @rename_sql = IF(@table_exists > 0 AND @target_exists = 0,
                     'RENAME TABLE course TO course_package',
                     'SELECT ''course_table already renamed or does not exist'' as info');

PREPARE stmt FROM @rename_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- =====================================================
-- 第二步：更新关联表的外键
-- =====================================================

SELECT '=== 更新 course_lesson 表外键 ===' as info;

-- 删除旧的外键（如果存在）
-- MySQL 不支持 DROP FOREIGN KEY IF EXISTS，需要先检查是否存在
SET @fk_exists = (SELECT COUNT(*) FROM information_schema.key_column_usage
                 WHERE table_schema = DATABASE()
                 AND table_name = 'course_lesson'
                 AND constraint_name = 'fk_course_lesson_course_id');

SET @drop_fk_sql = IF(@fk_exists > 0,
                      'ALTER TABLE course_lesson DROP FOREIGN KEY fk_course_lesson_course_id',
                      'SELECT ''foreign key does not exist'' as info');

PREPARE stmt FROM @drop_fk_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 添加新的外键约束（指向 course_package）
-- 首先检查新约束是否已存在
SET @new_fk_exists = (SELECT COUNT(*) FROM information_schema.key_column_usage
                      WHERE table_schema = DATABASE()
                      AND table_name = 'course_lesson'
                      AND constraint_name = 'fk_course_lesson_package_id');

SET @add_fk_sql = IF(@new_fk_exists = 0,
                     'ALTER TABLE course_lesson ADD CONSTRAINT fk_course_lesson_package_id FOREIGN KEY (course_id) REFERENCES course_package(id) ON DELETE CASCADE',
                     'SELECT ''new foreign key already exists'' as info');

PREPARE stmt FROM @add_fk_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- =====================================================
-- 第三步：验证表结构
-- =====================================================

SELECT '=== 验证重命名后的表结构 ===' as info;

SHOW TABLES LIKE 'course_package';

-- =====================================================
-- 完成
-- =====================================================

SELECT '=== V36 迁移完成 ===' as info;

SET FOREIGN_KEY_CHECKS = 1;
