buff3.0/backend-client/migrations/20260228_create_buff_selection_request_tables.sql

-- BUFF 选品请求单 & 选品请求单-选中饰品明细
-- 用户发起一次选品即生成一条请求单，本次选中的饰品写入明细表；所有数据按 user_id 隔离。

SET NAMES utf8mb4;

-- ----------------------------
-- 选品请求单表：每次用户发起选品生成一条
-- ----------------------------
DROP TABLE IF EXISTS `buff_selection_request`;
CREATE TABLE `buff_selection_request` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` int(10) unsigned NOT NULL COMMENT '用户ID，关联 users.id，按用户隔离',
  `config_id` bigint(20) unsigned DEFAULT NULL COMMENT '本次选品使用的配置ID，关联 buff_selection_config.id',
  `status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT '状态：pending-待执行,running-执行中,completed-已完成,failed-失败',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_user_created` (`user_id`,`created_at`),
  KEY `idx_status` (`status`),
  KEY `idx_config_id` (`config_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='BUFF选品请求单表';

-- ----------------------------
-- 选品请求单-选中饰品明细表：每条记录为某次请求选中的一件饰品
-- ----------------------------
DROP TABLE IF EXISTS `buff_selection_request_item`;
CREATE TABLE `buff_selection_request_item` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `request_id` bigint(20) unsigned NOT NULL COMMENT '选品请求单ID，关联 buff_selection_request.id',
  `user_id` int(10) unsigned NOT NULL COMMENT '用户ID，与请求单一致，便于按用户隔离查询',
  `goods_id` varchar(64) NOT NULL COMMENT 'BUFF饰品ID',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_request_goods` (`request_id`,`goods_id`),
  KEY `idx_request_id` (`request_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_user_created` (`user_id`,`created_at`),
  KEY `idx_goods_id` (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='BUFF选品请求单-选中饰品明细表';