-- =====================================================
-- 迁移 V41: 中间表优化 - 移除联合唯一索引和 deleted 字段
-- 版本：V41
-- 创建时间：2026-03-19
-- 描述:
--   1. 中间表使用物理删除，不再保留 deleted 字段
--   2. 移除不必要的联合唯一索引，通过应用层控制数据一致性
--   3. 适用于单体应用、低并发场景
-- =====================================================

-- -----------------------------------------------------
-- 1. course_collection_package（课程套餐 - 课程包关联表）
-- -----------------------------------------------------
-- 说明：纯关联表，先删后增的更新模式，不需要唯一索引兜底

-- 使用存储过程删除索引（如果存在）
DROP PROCEDURE IF EXISTS drop_ccp_indexes;
DELIMITER $$
CREATE PROCEDURE drop_ccp_indexes()
BEGIN
    -- 删除联合唯一索引（如果存在）
    IF EXISTS (SELECT 1 FROM information_schema.TABLE_CONSTRAINTS
               WHERE CONSTRAINT_SCHEMA = DATABASE()
               AND TABLE_NAME = 'course_collection_package'
               AND CONSTRAINT_NAME = 'uk_collection_package') THEN
        ALTER TABLE `course_collection_package` DROP INDEX `uk_collection_package`;
    END IF;

    -- 删除 deleted 字段（如果存在）
    IF EXISTS (SELECT 1 FROM information_schema.COLUMNS
               WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = 'course_collection_package'
               AND COLUMN_NAME = 'deleted') THEN
        ALTER TABLE `course_collection_package` DROP COLUMN `deleted`;
    END IF;

    -- 删除普通索引（如果存在）
    IF EXISTS (SELECT 1 FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = 'course_collection_package'
               AND INDEX_NAME = 'idx_collection_id') THEN
        ALTER TABLE `course_collection_package` DROP INDEX `idx_collection_id`;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = 'course_collection_package'
               AND INDEX_NAME = 'idx_package_id') THEN
        ALTER TABLE `course_collection_package` DROP INDEX `idx_package_id`;
    END IF;
END$$
DELIMITER ;

-- 执行存储过程
CALL drop_ccp_indexes();

-- 添加普通索引
ALTER TABLE `course_collection_package`
ADD INDEX `idx_collection_id` (`collection_id`),
ADD INDEX `idx_package_id` (`package_id`);

-- 删除存储过程
DROP PROCEDURE IF EXISTS drop_ccp_indexes;

-- -----------------------------------------------------
-- 2. tenant_course（租户 - 课程关联表）
-- -----------------------------------------------------
-- 说明：纯关联表，授权关系，不需要唯一索引兜底

DROP PROCEDURE IF EXISTS drop_tc_indexes;
DELIMITER $$
CREATE PROCEDURE drop_tc_indexes()
BEGIN
    -- 删除联合唯一索引（如果存在）
    IF EXISTS (SELECT 1 FROM information_schema.TABLE_CONSTRAINTS
               WHERE CONSTRAINT_SCHEMA = DATABASE()
               AND TABLE_NAME = 'tenant_course'
               AND CONSTRAINT_NAME = 'uk_tenant_course') THEN
        ALTER TABLE `tenant_course` DROP INDEX `uk_tenant_course`;
    END IF;

    -- 删除 deleted 字段（如果存在）
    IF EXISTS (SELECT 1 FROM information_schema.COLUMNS
               WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = 'tenant_course'
               AND COLUMN_NAME = 'deleted') THEN
        ALTER TABLE `tenant_course` DROP COLUMN `deleted`;
    END IF;

    -- 删除普通索引（如果存在）
    IF EXISTS (SELECT 1 FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = 'tenant_course'
               AND INDEX_NAME = 'idx_tenant_id') THEN
        ALTER TABLE `tenant_course` DROP INDEX `idx_tenant_id`;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = 'tenant_course'
               AND INDEX_NAME = 'idx_course_id') THEN
        ALTER TABLE `tenant_course` DROP INDEX `idx_course_id`;
    END IF;
END$$
DELIMITER ;

-- 执行存储过程
CALL drop_tc_indexes();

-- 添加普通索引
ALTER TABLE `tenant_course`
ADD INDEX `idx_tenant_id` (`tenant_id`),
ADD INDEX `idx_course_id` (`course_id`);

-- 删除存储过程
DROP PROCEDURE IF EXISTS drop_tc_indexes;

-- -----------------------------------------------------
-- 3. class_teacher（班级 - 教师关联表）
-- -----------------------------------------------------
-- 说明：纯关联表，不需要 deleted 字段

DROP PROCEDURE IF EXISTS drop_ct_indexes;
DELIMITER $$
CREATE PROCEDURE drop_ct_indexes()
BEGIN
    -- 删除 deleted 字段（如果存在）
    IF EXISTS (SELECT 1 FROM information_schema.COLUMNS
               WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = 'class_teacher'
               AND COLUMN_NAME = 'deleted') THEN
        ALTER TABLE `class_teacher` DROP COLUMN `deleted`;
    END IF;

    -- 删除普通索引（如果存在）
    IF EXISTS (SELECT 1 FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = 'class_teacher'
               AND INDEX_NAME = 'idx_class_id') THEN
        ALTER TABLE `class_teacher` DROP INDEX `idx_class_id`;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = 'class_teacher'
               AND INDEX_NAME = 'idx_teacher_id') THEN
        ALTER TABLE `class_teacher` DROP INDEX `idx_teacher_id`;
    END IF;
END$$
DELIMITER ;

-- 执行存储过程
CALL drop_ct_indexes();

-- 添加普通索引
ALTER TABLE `class_teacher`
ADD INDEX `idx_class_id` (`class_id`),
ADD INDEX `idx_teacher_id` (`teacher_id`);

-- 删除存储过程
DROP PROCEDURE IF EXISTS drop_ct_indexes;
