buff3.0/backend-client/migrations/20260317_create_buff_sweep_tables.sql

-- BUFF 扫货请求单、扫货成功明细、扫货日志表
-- 说明：
-- 1) 扫货基于最新一次选品结果；
-- 2) 扫货成功数据长期保留用于分页展示；
-- 3) 日志用于前端「扫货日志」弹窗实时查看。

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `buff_sweep_request` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` int(10) unsigned NOT NULL COMMENT '用户ID',
  `selection_request_id` bigint(20) unsigned NOT NULL COMMENT '本次扫货基于的选品请求单ID',
  `status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT '状态：pending/running/completed/failed/cancelled',
  `cancel_requested` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已请求停止',
  `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_status` (`status`),
  KEY `idx_selection_request_id` (`selection_request_id`),
  KEY `idx_user_created` (`user_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='BUFF扫货请求单表';

CREATE TABLE IF NOT EXISTS `buff_sweep_request_item` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `sweep_request_id` bigint(20) unsigned NOT NULL COMMENT '扫货请求单ID',
  `user_id` int(10) unsigned NOT NULL COMMENT '用户ID',
  `selection_request_id` bigint(20) unsigned NOT NULL COMMENT '选品请求单ID',
  `selection_item_id` bigint(20) unsigned NOT NULL COMMENT '选品明细ID',
  `goods_id` varchar(64) NOT NULL COMMENT 'BUFF饰品ID',
  `goods_name` varchar(256) DEFAULT NULL COMMENT '饰品名称',
  `icon_url` varchar(512) DEFAULT NULL COMMENT '饰品图片URL',
  `wear_range` varchar(64) DEFAULT NULL COMMENT '磨损区间',
  `purchase_wear` decimal(20,18) DEFAULT NULL COMMENT '已购饰品磨损值',
  `target_price_usd` decimal(12,2) DEFAULT NULL COMMENT '目标扫货价（美元）',
  `purchase_price_usd` decimal(12,2) DEFAULT NULL COMMENT '实际购买价（美元）',
  `steam_account` varchar(128) DEFAULT NULL COMMENT '购买使用的Steam账号',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_sweep_request_id` (`sweep_request_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_selection_request_id` (`selection_request_id`),
  KEY `idx_goods_id` (`goods_id`),
  KEY `idx_user_created` (`user_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='BUFF扫货成功明细表';

CREATE TABLE IF NOT EXISTS `buff_sweep_request_log` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `sweep_request_id` bigint(20) unsigned NOT NULL COMMENT '扫货请求单ID',
  `user_id` int(10) unsigned NOT NULL COMMENT '用户ID',
  `goods_id` varchar(64) NOT NULL COMMENT 'BUFF饰品ID',
  `goods_name` varchar(256) DEFAULT NULL COMMENT '饰品名称',
  `sequence_no` int(11) NOT NULL DEFAULT '0' COMMENT '处理序号',
  `result` varchar(32) NOT NULL COMMENT '结果：success/skipped/failed/stopped',
  `message` varchar(512) DEFAULT NULL COMMENT '描述',
  `target_price_usd` decimal(12,2) DEFAULT NULL COMMENT '目标扫货价（美元）',
  `purchase_price_usd` decimal(12,2) DEFAULT NULL COMMENT '实际购买价（美元）',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_sweep_request_id` (`sweep_request_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_sweep_sequence` (`sweep_request_id`,`sequence_no`),
  KEY `idx_sweep_created` (`sweep_request_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='BUFF扫货日志表';