-- =====================================================
-- 迁移 V33: 将8个课程包转换为8个套餐
--
-- 这个迁移会：
-- 1. 为8个现有课程包创建对应的8个套餐
-- 2. 将每个课程包关联到其对应的套餐
-- 3. 更新所有关联关系
-- =====================================================

SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================
-- 第一步：创建8个套餐（使用新ID避免冲突）
-- 课程包ID: 3, 4, 5, 6, 7, 101, 102, 103
-- 新套餐ID: 203, 204, 205, 206, 207, 208, 209, 210
-- =====================================================

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
    203, `name`, `description`, `price`, `discount_price`, `discount_type`, `grade_levels`, 1, `status`,
    `submitted_at`, `submitted_by`, `reviewed_at`, `reviewed_by`, `review_comment`, `published_at`,
    `create_by`, `created_at`, `update_by`, `updated_at`, 0
FROM `course_package` WHERE `id` = 3;

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
    204, `name`, `description`, `price`, `discount_price`, `discount_type`, `grade_levels`, 1, `status`,
    `submitted_at`, `submitted_by`, `reviewed_at`, `reviewed_by`, `review_comment`, `published_at`,
    `create_by`, `created_at`, `update_by`, `updated_at`, 0
FROM `course_package` WHERE `id` = 4;

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
    205, `name`, `description`, `price`, `discount_price`, `discount_type`, `grade_levels`, 1, `status`,
    `submitted_at`, `submitted_by`, `reviewed_at`, `reviewed_by`, `review_comment`, `published_at`,
    `create_by`, `created_at`, `update_by`, `updated_at`, 0
FROM `course_package` WHERE `id` = 5;

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
    206, `name`, `description`, `price`, `discount_price`, `discount_type`, `grade_levels`, 1, `status`,
    `submitted_at`, `submitted_by`, `reviewed_at`, `reviewed_by`, `review_comment`, `published_at`,
    `create_by`, `created_at`, `update_by`, `updated_at`, 0
FROM `course_package` WHERE `id` = 6;

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
    207, `name`, `description`, `price`, `discount_price`, `discount_type`, `grade_levels`, 1, `status`,
    `submitted_at`, `submitted_by`, `reviewed_at`, `reviewed_by`, `review_comment`, `published_at`,
    `create_by`, `created_at`, `update_by`, `updated_at`, 0
FROM `course_package` WHERE `id` = 7;

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
    208, `name`, `description`, `price`, `discount_price`, `discount_type`, `grade_levels`, 1, `status`,
    `submitted_at`, `submitted_by`, `reviewed_at`, `reviewed_by`, `review_comment`, `published_at`,
    `create_by`, `created_at`, `update_by`, `updated_at`, 0
FROM `course_package` WHERE `id` = 101;

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
    209, `name`, `description`, `price`, `discount_price`, `discount_type`, `grade_levels`, 1, `status`,
    `submitted_at`, `submitted_by`, `reviewed_at`, `reviewed_by`, `review_comment`, `published_at`,
    `create_by`, `created_at`, `update_by`, `updated_at`, 0
FROM `course_package` WHERE `id` = 102;

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
    210, `name`, `description`, `price`, `discount_price`, `discount_type`, `grade_levels`, 1, `status`,
    `submitted_at`, `submitted_by`, `reviewed_at`, `reviewed_by`, `review_comment`, `published_at`,
    `create_by`, `created_at`, `update_by`, `updated_at`, 0
FROM `course_package` WHERE `id` = 103;

-- =====================================================
-- 第二步：建立套餐与课程包的关联
-- =====================================================

INSERT INTO `course_collection_package`
(`id`, `collection_id`, `package_id`, `sort_order`, `create_by`, `created_at`, `update_by`, `updated_at`, `deleted`)
VALUES
(1001, 203, 3, 1, 'system', NOW(), 'system', NOW(), 0),
(1002, 204, 4, 1, 'system', NOW(), 'system', NOW(), 0),
(1003, 205, 5, 1, 'system', NOW(), 'system', NOW(), 0),
(1004, 206, 6, 1, 'system', NOW(), 'system', NOW(), 0),
(1005, 207, 7, 1, 'system', NOW(), 'system', NOW(), 0),
(1006, 208, 101, 1, 'system', NOW(), 'system', NOW(), 0),
(1007, 209, 102, 1, 'system', NOW(), 'system', NOW(), 0),
(1008, 210, 103, 1, 'system', NOW(), 'system', NOW(), 0);

-- =====================================================
-- 第三步：更新租户套餐关联
-- =====================================================

-- 删除原有的套餐关联
DELETE FROM `tenant_package`;

-- 重新创建套餐关联，关联到新的8个套餐
-- 注意：需要同时提供 package_id 和 collection_id
INSERT INTO `tenant_package` (`id`, `tenant_id`, `package_id`, `collection_id`, `start_date`, `end_date`, `status`, `create_by`, `created_at`, `update_by`, `updated_at`, `deleted`)
VALUES
(1, 1, 3, 203, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 YEAR), 'ACTIVE', 'system', NOW(), 'system', NOW(), 0),
(2, 1, 4, 204, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 YEAR), 'ACTIVE', 'system', NOW(), 'system', NOW(), 0);

-- =====================================================
-- 第四步：清理旧的套餐数据
-- =====================================================

-- 删除旧的套餐（ID 100）
DELETE FROM `course_collection_package` WHERE `collection_id` = 100;
DELETE FROM `course_collection` WHERE `id` = 100;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================
-- V33 迁移完成
-- 现在有8个套餐，每个套餐包含1个原有的课程包
-- =====================================================
