-- -----------------------------------------------------
-- 迁移 V38: 添加自增列到 course_collection
-- 注意：必须先删除外键约束，否则修改列会失败
-- 使用条件删除，避免外键不存在时报错
-- -----------------------------------------------------

-- 1. 先删除外键约束（如果存在）

-- 删除 tenant_package 表的外键约束 fk_tenant_package_collection
SET @has_fk_tenant := (
    SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE()
    AND TABLE_NAME = 'tenant_package'
    AND CONSTRAINT_NAME = 'fk_tenant_package_collection'
    AND CONSTRAINT_TYPE = 'FOREIGN KEY'
);

SET @sql := IF(@has_fk_tenant > 0,
    'ALTER TABLE `tenant_package` DROP FOREIGN KEY `fk_tenant_package_collection`',
    'SELECT "fk_tenant_package_collection not exists, skipping"'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 删除 course_collection_package 表的外键约束 fk_collection_package_collection
SET @has_fk_coll := (
    SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE()
    AND TABLE_NAME = 'course_collection_package'
    AND CONSTRAINT_NAME = 'fk_collection_package_collection'
    AND CONSTRAINT_TYPE = 'FOREIGN KEY'
);

SET @sql := IF(@has_fk_coll > 0,
    'ALTER TABLE `course_collection_package` DROP FOREIGN KEY `fk_collection_package_collection`',
    'SELECT "fk_collection_package_collection not exists, skipping"'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 删除 course_collection_package 表的外键约束 fk_collection_package_package
SET @has_fk_pkg := (
    SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE()
    AND TABLE_NAME = 'course_collection_package'
    AND CONSTRAINT_NAME = 'fk_collection_package_package'
    AND CONSTRAINT_TYPE = 'FOREIGN KEY'
);

SET @sql := IF(@has_fk_pkg > 0,
    'ALTER TABLE `course_collection_package` DROP FOREIGN KEY `fk_collection_package_package`',
    'SELECT "fk_collection_package_package not exists, skipping"'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2. 为 course_collection 表的 id 字段添加自增
ALTER TABLE `course_collection` MODIFY COLUMN `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID';

-- 3. 为 course_collection_package 表的 id 字段添加自增
ALTER TABLE `course_collection_package` MODIFY COLUMN `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 ID';
