kknongchang_2506/database/migrations/modify_shop_items_table.sql

-- 修改商店商品表结构
-- 执行时间：开发阶段直接修改
-- 注意：此脚本会删除现有数据，仅适用于开发环境

-- 1. 添加新字段
ALTER TABLE `kku_shop_items` 
ADD COLUMN `category_name` varchar(100) DEFAULT NULL COMMENT '分类名称（字符串格式，区别于现有分类机制）' AFTER `category_id`,
ADD COLUMN `consume_group_id` int unsigned DEFAULT NULL COMMENT '消耗组ID，外键关联kku_game_consume_groups表' AFTER `category_name`,
ADD COLUMN `reward_group_id` int unsigned DEFAULT NULL COMMENT '奖励组ID，外键关联kku_game_reward_groups表' AFTER `consume_group_id`;

-- 2. 删除旧字段
ALTER TABLE `kku_shop_items` 
DROP COLUMN `item_id`,
DROP COLUMN `item_quantity`,
DROP COLUMN `price`,
DROP COLUMN `currency_id`;

-- 3. 删除旧索引
ALTER TABLE `kku_shop_items` 
DROP INDEX `shop_items_item_id_index`,
DROP INDEX `shop_items_currency_id_index`;

-- 4. 添加新索引
ALTER TABLE `kku_shop_items` 
ADD INDEX `shop_items_consume_group_id_index` (`consume_group_id`),
ADD INDEX `shop_items_reward_group_id_index` (`reward_group_id`);

-- 5. 添加外键约束（可选，根据需要启用）
-- ALTER TABLE `kku_shop_items` 
-- ADD CONSTRAINT `fk_shop_items_consume_group` FOREIGN KEY (`consume_group_id`) REFERENCES `kku_game_consume_groups` (`id`) ON DELETE SET NULL,
-- ADD CONSTRAINT `fk_shop_items_reward_group` FOREIGN KEY (`reward_group_id`) REFERENCES `kku_game_reward_groups` (`id`) ON DELETE SET NULL;