-- -----------------------------------------------------
-- 迁移 V28: 添加两层课程套餐结构
-- 将单层 course_package 结构改造为两层：
--   course_collection（课程套餐） → course_package（课程包） → course（课程）
-- -----------------------------------------------------

-- 1. 创建课程套餐表（course_collection）- 最上层
CREATE TABLE IF NOT EXISTS `course_collection` (
    `id` BIGINT NOT NULL COMMENT '主键ID',
    `name` VARCHAR(200) NOT NULL COMMENT '套餐名称',
    `description` TEXT COMMENT '套餐描述',
    `price` BIGINT COMMENT '价格（分）',
    `discount_price` BIGINT COMMENT '折后价格（分）',
    `discount_type` VARCHAR(20) COMMENT '折扣类型:PERCENTAGE、FIXED',
    `grade_levels` JSON COMMENT '适用年级（JSON数组）',
    `package_count` INT DEFAULT 0 COMMENT '包含的课程包数量',
    `status` VARCHAR(30) DEFAULT 'DRAFT' COMMENT '状态:DRAFT、PENDING_REVIEW、APPROVED、REJECTED、PUBLISHED、OFFLINE',
    `submitted_at` DATETIME COMMENT '提交时间',
    `submitted_by` BIGINT COMMENT '提交人ID',
    `reviewed_at` DATETIME COMMENT '审核时间',
    `reviewed_by` BIGINT COMMENT '审核人ID',
    `review_comment` TEXT COMMENT '审核意见',
    `published_at` DATETIME COMMENT '发布时间',
    `create_by` VARCHAR(50) COMMENT '创建人',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` VARCHAR(50) COMMENT '更新人',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '删除标识(0-未删除,1-已删除)',
    PRIMARY KEY (`id`),
    KEY `idx_status` (`status`),
    KEY `idx_published_at` (`published_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='课程套餐表（两层结构-最上层）';

-- 2. 创建课程套餐与课程包关联表（course_collection_package）
CREATE TABLE IF NOT EXISTS `course_collection_package` (
    `id` BIGINT NOT NULL COMMENT '主键ID',
    `collection_id` BIGINT NOT NULL COMMENT '课程套餐ID',
    `package_id` BIGINT NOT NULL COMMENT '课程包ID',
    `sort_order` INT DEFAULT 0 COMMENT '排序号',
    `create_by` VARCHAR(50) COMMENT '创建人',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` VARCHAR(50) COMMENT '更新人',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '删除标识(0-未删除,1-已删除)',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_collection_package` (`collection_id`, `package_id`),
    KEY `idx_collection_id` (`collection_id`),
    KEY `idx_package_id` (`package_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='课程套餐与课程包关联表';

-- 3. 修改 tenant_package 表结构
-- 添加新字段 collection_id
ALTER TABLE `tenant_package`
ADD COLUMN `collection_id` BIGINT COMMENT '课程套餐ID' AFTER `tenant_id`,
ADD INDEX `idx_collection_id` (`collection_id`);

-- 添加索引（外键约束已删除，改用应用层控制）
-- 外键约束已删除：fk_tenant_package_collection
-- 数据完整性由应用层 Service 层验证逻辑控制

-- 4. 数据迁移：将现有的 course_package 提升为两层结构
-- 步骤1：为每个现有的 course_package 创建对应的 course_collection
INSERT INTO `course_collection` (`id`, `name`, `description`, `price`, `discount_price`, `discount_type`, `grade_levels`, `package_count`, `status`, `submitted_at`, `submitted_by`, `reviewed_at`, `reviewed_by`, `review_comment`, `published_at`, `create_by`, `created_at`, `update_by`, `updated_at`, `deleted`)
SELECT
    `id`,
    `name`,
    `description`,
    `price`,
    `discount_price`,
    `discount_type`,
    `grade_levels`,
    1 as package_count,  -- 每个套餐初始包含1个课程包
    `status`,
    `submitted_at`,
    `submitted_by`,
    `reviewed_at`,
    `reviewed_by`,
    `review_comment`,
    `published_at`,
    `create_by`,
    `created_at`,
    `update_by`,
    `updated_at`,
    `deleted`
FROM `course_package`
WHERE `deleted` = 0;

-- 步骤2：创建 course_collection_package 关联（每个套餐关联自己）
INSERT INTO `course_collection_package` (`id`, `collection_id`, `package_id`, `sort_order`, `create_by`, `created_at`, `update_by`, `updated_at`, `deleted`)
SELECT
    `id`,
    `id` as collection_id,
    `id` as package_id,
    1 as sort_order,
    `create_by`,
    `created_at`,
    `update_by`,
    `updated_at`,
    `deleted`
FROM `course_package`
WHERE `deleted` = 0;

-- 步骤3：更新 tenant_package 表的 collection_id
UPDATE `tenant_package` tp
INNER JOIN `course_package` cp ON tp.`package_id` = cp.`id`
SET tp.`collection_id` = cp.`id`
WHERE tp.`deleted` = 0;

-- 5. 设置 auto_increment
-- 设置 course_collection 的 auto_increment 起始值
SET @max_collection_id = (SELECT MAX(`id`) FROM `course_collection`);
SET @sql_collection = CONCAT('ALTER TABLE `course_collection` AUTO_INCREMENT = ', IFNULL(@max_collection_id, 0) + 1);
PREPARE stmt FROM @sql_collection;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 设置 course_collection_package 的 auto_increment 起始值
SET @max_collection_package_id = (SELECT MAX(`id`) FROM `course_collection_package`);
SET @sql_collection_package = CONCAT('ALTER TABLE `course_collection_package` AUTO_INCREMENT = ', IFNULL(@max_collection_package_id, 0) + 1);
PREPARE stmt FROM @sql_collection_package;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
