-- -----------------------------------------------------
-- 迁移 V39: 删除外键约束（幂等版本）
-- 说明：V38 已包含删除外键的逻辑，此脚本保留用于兼容性
-- 使用条件删除，避免重复执行时报错
-- -----------------------------------------------------

-- MySQL 8.0+ 支持 ALTER TABLE ... DROP FOREIGN KEY IF EXISTS
-- 但为了兼容性，使用以下方式

-- 删除 tenant_package 表的外键约束（如果存在）
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 "Foreign key fk_tenant_package_collection does not exist, skipping"'
);

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

-- 删除 course_collection_package 表的外键约束 fk_collection_package_collection（如果存在）
SET @has_fk_collection := (
    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_collection > 0,
    'ALTER TABLE `course_collection_package` DROP FOREIGN KEY `fk_collection_package_collection`',
    'SELECT "Foreign key fk_collection_package_collection does not exist, skipping"'
);

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

-- 删除 course_collection_package 表的外键约束 fk_collection_package_package（如果存在）
SET @has_fk_package := (
    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_package > 0,
    'ALTER TABLE `course_collection_package` DROP FOREIGN KEY `fk_collection_package_package`',
    'SELECT "Foreign key fk_collection_package_package does not exist, skipping"'
);

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