buff2.0/docs/buff.sql

/*
 Navicat Premium Dump SQL

 Source Server         : 47.99.164.66  hn02le.34lkdLKD
 Source Server Type    : MySQL
 Source Server Version : 50736 (5.7.36)
 Source Host           : 47.99.164.66:3306
 Source Schema         : buff

 Target Server Type    : MySQL
 Target Server Version : 50736 (5.7.36)
 File Encoding         : 65001

 Date: 22/02/2026 21:38:51
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for admins
-- ----------------------------
DROP TABLE IF EXISTS `admins`;
CREATE TABLE `admins` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `username` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '管理员用户名',
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码（加密存储）',
  `real_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '真实姓名',
  `email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '邮箱',
  `role` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'admin' COMMENT '角色：admin-管理员，super_admin-超级管理员',
  `last_login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
  `last_login_ip` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '最后登录IP',
  `status` tinyint(1) DEFAULT '1' COMMENT '状态：1-正常，0-禁用',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `idx_username` (`username`),
  KEY `idx_role` (`role`),
  KEY `idx_status` (`status`),
  KEY `idx_is_deleted` (`is_deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='管理员表';

-- ----------------------------
-- Table structure for admins_his
-- ----------------------------
DROP TABLE IF EXISTS `admins_his`;
CREATE TABLE `admins_his` (
  `id` int(11) NOT NULL COMMENT '原表主键ID',
  `username` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '管理员用户名',
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码（加密存储）',
  `real_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '真实姓名',
  `email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '邮箱',
  `role` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'admin' COMMENT '角色：admin-管理员，super_admin-超级管理员',
  `last_login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
  `last_login_ip` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '最后登录IP',
  `status` tinyint(1) DEFAULT '1' COMMENT '状态：1-正常，0-禁用',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `his_created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间',
  `his_operation` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '操作类型：INSERT/UPDATE/DELETE'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='管理员表历史记录';

-- ----------------------------
-- Table structure for announcements
-- ----------------------------
DROP TABLE IF EXISTS `announcements`;
CREATE TABLE `announcements` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `title` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '公告标题',
  `content` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '公告内容',
  `priority` tinyint(4) DEFAULT '0' COMMENT '优先级：0-普通，1-重要，2-紧急',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否启用：1-启用，0-禁用',
  `publish_time` datetime DEFAULT NULL COMMENT '发布时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_priority` (`priority`),
  KEY `idx_publish_time` (`publish_time`),
  KEY `idx_is_deleted` (`is_deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='公告表';

-- ----------------------------
-- Table structure for announcements_his
-- ----------------------------
DROP TABLE IF EXISTS `announcements_his`;
CREATE TABLE `announcements_his` (
  `id` int(11) NOT NULL COMMENT '原表主键ID',
  `title` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '公告标题',
  `content` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '公告内容',
  `priority` tinyint(4) DEFAULT '0' COMMENT '优先级：0-普通，1-重要，2-紧急',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否启用：1-启用，0-禁用',
  `publish_time` datetime DEFAULT NULL COMMENT '发布时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `his_created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间',
  `his_operation` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '操作类型：INSERT/UPDATE/DELETE'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='公告表历史记录';

-- ----------------------------
-- Table structure for api_keys
-- ----------------------------
DROP TABLE IF EXISTS `api_keys`;
CREATE TABLE `api_keys` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `public_key` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'RSA公钥（PEM格式）',
  `private_key` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'RSA私钥（PEM格式，加密存储）',
  `key_version` int(11) NOT NULL COMMENT '密钥版本号',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否激活：1-激活，0-非激活',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `expire_at` datetime DEFAULT NULL COMMENT '过期时间（创建时间+10天）',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_key_version` (`key_version`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_expire_at` (`expire_at`),
  KEY `idx_is_deleted` (`is_deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=387 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='API密钥表';

-- ----------------------------
-- Table structure for api_keys_his
-- ----------------------------
DROP TABLE IF EXISTS `api_keys_his`;
CREATE TABLE `api_keys_his` (
  `id` int(11) NOT NULL COMMENT '原表主键ID',
  `public_key` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'RSA公钥（PEM格式）',
  `private_key` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'RSA私钥（PEM格式，加密存储）',
  `key_version` int(11) NOT NULL COMMENT '密钥版本号',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否激活：1-激活，0-非激活',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `expire_at` datetime DEFAULT NULL COMMENT '过期时间（创建时间+10天）',
  `his_created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间',
  `his_operation` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '操作类型：INSERT/UPDATE/DELETE'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='API密钥表历史记录';

-- ----------------------------
-- Table structure for api_logs
-- ----------------------------
DROP TABLE IF EXISTS `api_logs`;
CREATE TABLE `api_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` int(11) DEFAULT NULL COMMENT '用户ID（可为NULL，如登录接口）',
  `endpoint` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '接口路径',
  `method` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'HTTP方法：GET、POST等',
  `request_data` text COLLATE utf8mb4_unicode_ci COMMENT '请求数据（加密前或解密后）',
  `response_data` text COLLATE utf8mb4_unicode_ci COMMENT '响应数据（加密前或解密后）',
  `ip_address` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '客户端IP地址',
  `user_agent` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户代理',
  `response_code` int(11) DEFAULT NULL COMMENT '响应状态码',
  `execution_time` int(11) DEFAULT NULL COMMENT '执行时间（毫秒）',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `admin_id` int(11) DEFAULT NULL COMMENT '管理员ID（管理端操作记录）',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_endpoint` (`endpoint`),
  KEY `idx_method` (`method`),
  KEY `idx_ip_address` (`ip_address`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_response_code` (`response_code`),
  KEY `idx_is_deleted` (`is_deleted`),
  KEY `idx_admin_id` (`admin_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14252 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='API日志表';

-- ----------------------------
-- Table structure for api_logs_his
-- ----------------------------
DROP TABLE IF EXISTS `api_logs_his`;
CREATE TABLE `api_logs_his` (
  `id` bigint(20) NOT NULL COMMENT '原表主键ID',
  `user_id` int(11) DEFAULT NULL COMMENT '用户ID（可为NULL，如登录接口）',
  `endpoint` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '接口路径',
  `method` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'HTTP方法：GET、POST等',
  `request_data` text COLLATE utf8mb4_unicode_ci COMMENT '请求数据（加密前或解密后）',
  `response_data` text COLLATE utf8mb4_unicode_ci COMMENT '响应数据（加密前或解密后）',
  `ip_address` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '客户端IP地址',
  `user_agent` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户代理',
  `response_code` int(11) DEFAULT NULL COMMENT '响应状态码',
  `execution_time` int(11) DEFAULT NULL COMMENT '执行时间（毫秒）',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `his_created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间',
  `his_operation` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '操作类型：INSERT/UPDATE/DELETE'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='API日志表历史记录';

-- ----------------------------
-- Table structure for api_nonces
-- ----------------------------
DROP TABLE IF EXISTS `api_nonces`;
CREATE TABLE `api_nonces` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `nonce` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '随机字符串',
  `endpoint` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '接口路径',
  `session_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '会话ID',
  `user_id` int(11) DEFAULT NULL COMMENT '用户ID',
  `expires_at` datetime NOT NULL COMMENT '过期时间',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_api_nonces_nonce` (`nonce`),
  KEY `idx_api_nonces_expires_at` (`expires_at`)
) ENGINE=InnoDB AUTO_INCREMENT=3070 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='API Nonce防重放表';

-- ----------------------------
-- Table structure for api_nonces_his
-- ----------------------------
DROP TABLE IF EXISTS `api_nonces_his`;
CREATE TABLE `api_nonces_his` (
  `id` int(11) NOT NULL COMMENT '原表主键ID',
  `nonce` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '随机字符串',
  `endpoint` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '接口路径',
  `session_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '会话ID',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `expires_at` datetime NOT NULL COMMENT '过期时间',
  `his_created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间',
  `his_operation` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '操作类型: INSERT/UPDATE/DELETE',
  PRIMARY KEY (`id`,`his_created_at`),
  KEY `idx_his_created_at` (`his_created_at`),
  KEY `idx_nonce` (`nonce`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='API Nonce表历史记录';

-- ----------------------------
-- Table structure for buff_goods_attr
-- ----------------------------
DROP TABLE IF EXISTS `buff_goods_attr`;
CREATE TABLE `buff_goods_attr` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `goods_id` varchar(64) NOT NULL COMMENT 'BUFF饰品ID',
  `name` varchar(255) NOT NULL COMMENT '饰品名称',
  `icon_url` varchar(512) DEFAULT NULL COMMENT '饰品图片URL地址',
  `type` varchar(64) DEFAULT NULL COMMENT '类型',
  `type_name` varchar(128) DEFAULT NULL COMMENT '类型中文名称',
  `category` varchar(64) DEFAULT NULL COMMENT '分类',
  `category_name` varchar(128) DEFAULT NULL COMMENT '分类中文名称',
  `category_group` varchar(64) DEFAULT NULL COMMENT '分类组',
  `category_group_name` varchar(128) DEFAULT NULL COMMENT '分类组中文名称',
  `rarity` varchar(64) DEFAULT NULL COMMENT '品质',
  `rarity_name` varchar(128) DEFAULT NULL COMMENT '品质中文名称',
  `quality` varchar(64) DEFAULT NULL COMMENT '类别',
  `quality_name` varchar(128) DEFAULT NULL COMMENT '类别中文名称',
  `exterior` varchar(64) DEFAULT NULL COMMENT '外观',
  `exterior_name` varchar(128) DEFAULT NULL COMMENT '外观中文名称',
  `weapon` varchar(64) DEFAULT NULL COMMENT '武器',
  `weapon_name` varchar(128) DEFAULT NULL COMMENT '武器中文名称',
  `series` varchar(128) DEFAULT NULL COMMENT '热门皮肤',
  `series_name` varchar(256) DEFAULT NULL COMMENT '热门皮肤中文名称',
  `custom` varchar(64) DEFAULT NULL COMMENT '颜色',
  `custom_name` varchar(128) DEFAULT NULL COMMENT '颜色中文名称',
  `itemset` varchar(128) DEFAULT NULL COMMENT '收藏品',
  `itemset_name` varchar(256) DEFAULT NULL COMMENT '收藏品中文名称',
  `extra_tag_ids` varchar(256) DEFAULT NULL COMMENT '已贴印花',
  `extra_tag_ids_name` varchar(512) DEFAULT NULL COMMENT '已贴印花中文名称',
  `custom_patch` varchar(128) DEFAULT NULL COMMENT '已贴布章',
  `custom_patch_name` varchar(256) DEFAULT NULL COMMENT '已贴布章中文名称',
  `custom_charm` varchar(128) DEFAULT NULL COMMENT '已装挂件',
  `custom_charm_name` varchar(256) DEFAULT NULL COMMENT '已装挂件中文名称',
  `stat_time` datetime DEFAULT NULL COMMENT '统计时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `uk_goods_id` (`goods_id`),
  KEY `idx_name` (`name`(100)),
  KEY `idx_stat_time` (`stat_time`)
) ENGINE=InnoDB AUTO_INCREMENT=41952 DEFAULT CHARSET=utf8mb4 COMMENT='BUFF饰品属性表';

-- ----------------------------
-- Table structure for buff_filter_option
-- ----------------------------
-- BUFF 选品筛选项配置表：从 buff.163.com/market/csgo 解析的下拉选项（步枪、手枪、品质、外观、收藏品等），供选品界面加载与维护。设计说明见 docs/buff_filter_option_design.md
DROP TABLE IF EXISTS `buff_filter_option`;
CREATE TABLE `buff_filter_option` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `game` varchar(32) NOT NULL DEFAULT 'csgo' COMMENT '游戏标识：csgo/dota2 等',
  `filter_type` varchar(32) NOT NULL COMMENT '筛选类型：category/rarity/quality/exterior/series/custom/itemset',
  `parent_value` varchar(128) DEFAULT NULL COMMENT '父节点 value，顶级或平铺为 NULL',
  `value` varchar(128) NOT NULL COMMENT '接口参数值（请求 BUFF/前端传参）',
  `label` varchar(128) NOT NULL COMMENT '显示名称（界面下拉展示）',
  `sort_order` int(11) NOT NULL DEFAULT '0' COMMENT '同类型同层级下排序，越小越靠前',
  `is_enabled` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否启用：1-是，0-否',
  `source` varchar(64) DEFAULT 'buff.163.com' COMMENT '数据来源',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_game_filter_value` (`game`,`filter_type`,`value`),
  KEY `idx_game_filter` (`game`,`filter_type`),
  KEY `idx_game_filter_parent` (`game`,`filter_type`,`parent_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='BUFF选品筛选项配置表';

-- ----------------------------
-- Table structure for buff_selection_config
-- ----------------------------
-- BUFF 选品配置表：按用户存储检索条件+选品条件，支持多套配置与默认配置。设计说明见 docs/buff_selection_config_table_design.md
DROP TABLE IF EXISTS `buff_selection_config`;
CREATE TABLE `buff_selection_config` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` int(10) unsigned NOT NULL COMMENT '用户ID，关联 users.id',
  `name` varchar(64) NOT NULL COMMENT '配置名称，同用户下未删除时唯一',
  `config_json` text NOT NULL COMMENT '整份配置 JSON：检索条件+选品条件',
  `is_default` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否默认配置：同用户至多一个为 1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '软删：0-否，1-是',
  `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_default` (`user_id`,`is_default`),
  KEY `idx_user_name` (`user_id`,`name`,`is_deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='BUFF选品配置表';

-- ----------------------------
-- Table structure for buff_selection_request
-- ----------------------------
-- 选品请求单：用户每次发起选品生成一条，按 user_id 隔离
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选品请求单表';

-- ----------------------------
-- Table structure for buff_selection_request_item
-- ----------------------------
-- 选品请求单-选中饰品明细：每条为某次请求选中的一件饰品，按 user_id 与 request_id 隔离
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选品请求单-选中饰品明细表';

-- ----------------------------
-- Table structure for buff_goods_attr_middle
-- ----------------------------
DROP TABLE IF EXISTS `buff_goods_attr_middle`;
CREATE TABLE `buff_goods_attr_middle` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `goods_id` varchar(64) NOT NULL COMMENT 'BUFF饰品ID',
  `name` varchar(255) NOT NULL COMMENT '饰品名称',
  `icon_url` varchar(512) DEFAULT NULL COMMENT '饰品图片URL地址',
  `type` varchar(64) DEFAULT NULL COMMENT '类型',
  `type_name` varchar(128) DEFAULT NULL COMMENT '类型中文名称',
  `category` varchar(64) DEFAULT NULL COMMENT '分类',
  `category_name` varchar(128) DEFAULT NULL COMMENT '分类中文名称',
  `category_group` varchar(64) DEFAULT NULL COMMENT '分类组',
  `category_group_name` varchar(128) DEFAULT NULL COMMENT '分类组中文名称',
  `rarity` varchar(64) DEFAULT NULL COMMENT '品质',
  `rarity_name` varchar(128) DEFAULT NULL COMMENT '品质中文名称',
  `quality` varchar(64) DEFAULT NULL COMMENT '类别',
  `quality_name` varchar(128) DEFAULT NULL COMMENT '类别中文名称',
  `exterior` varchar(64) DEFAULT NULL COMMENT '外观',
  `exterior_name` varchar(128) DEFAULT NULL COMMENT '外观中文名称',
  `weapon` varchar(64) DEFAULT NULL COMMENT '武器',
  `weapon_name` varchar(128) DEFAULT NULL COMMENT '武器中文名称',
  `series` varchar(128) DEFAULT NULL COMMENT '热门皮肤',
  `series_name` varchar(256) DEFAULT NULL COMMENT '热门皮肤中文名称',
  `custom` varchar(64) DEFAULT NULL COMMENT '颜色',
  `custom_name` varchar(128) DEFAULT NULL COMMENT '颜色中文名称',
  `itemset` varchar(128) DEFAULT NULL COMMENT '收藏品',
  `itemset_name` varchar(256) DEFAULT NULL COMMENT '收藏品中文名称',
  `extra_tag_ids` varchar(256) DEFAULT NULL COMMENT '已贴印花',
  `extra_tag_ids_name` varchar(512) DEFAULT NULL COMMENT '已贴印花中文名称',
  `custom_patch` varchar(128) DEFAULT NULL COMMENT '已贴布章',
  `custom_patch_name` varchar(256) DEFAULT NULL COMMENT '已贴布章中文名称',
  `custom_charm` varchar(128) DEFAULT NULL COMMENT '已装挂件',
  `custom_charm_name` varchar(256) DEFAULT NULL COMMENT '已装挂件中文名称',
  `stat_time` datetime DEFAULT NULL COMMENT '统计时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_goods_id` (`goods_id`),
  KEY `idx_name` (`name`(100)),
  KEY `idx_stat_time` (`stat_time`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='BUFF饰品属性表中间表';

-- ----------------------------
-- Table structure for buff_goods_daily_price
-- ----------------------------
DROP TABLE IF EXISTS `buff_goods_daily_price`;
CREATE TABLE `buff_goods_daily_price` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `goods_id` varchar(64) NOT NULL COMMENT 'BUFF饰品ID',
  `name` varchar(255) NOT NULL COMMENT '饰品名称',
  `min_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '在售最低价格',
  `buy_max_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '求购最高价格',
  `stat_time` datetime DEFAULT NULL COMMENT '统计时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `uk_goods_id_stat_time` (`goods_id`,`stat_time`),
  KEY `idx_goods_id` (`goods_id`),
  KEY `idx_name` (`name`(100)),
  KEY `idx_stat_time` (`stat_time`)
) ENGINE=InnoDB AUTO_INCREMENT=223576 DEFAULT CHARSET=utf8mb4 COMMENT='BUFF饰品每日销售价格表';

-- ----------------------------
-- Table structure for buff_goods_daily_price_middle
-- ----------------------------
DROP TABLE IF EXISTS `buff_goods_daily_price_middle`;
CREATE TABLE `buff_goods_daily_price_middle` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `goods_id` varchar(64) NOT NULL COMMENT 'BUFF饰品ID',
  `name` varchar(255) NOT NULL COMMENT '饰品名称',
  `min_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '在售最低价格',
  `buy_max_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '求购最高价格',
  `stat_time` datetime DEFAULT NULL COMMENT '统计时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_goods_id` (`goods_id`),
  KEY `idx_name` (`name`(100)),
  KEY `idx_stat_time` (`stat_time`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='BUFF饰品每日销售价格表历史中间表';

-- ----------------------------
-- Table structure for buff_goods_daily_tag_price
-- ----------------------------
DROP TABLE IF EXISTS `buff_goods_daily_tag_price`;
CREATE TABLE `buff_goods_daily_tag_price` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `goods_id` varchar(64) NOT NULL COMMENT 'BUFF饰品ID',
  `name` varchar(255) NOT NULL COMMENT '饰品名称',
  `min_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '在售最低价格',
  `buy_max_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '求购最高价格',
  `tag_id` varchar(32) DEFAULT NULL COMMENT 'BUFF饰品款式ID',
  `tag_name` varchar(128) DEFAULT NULL COMMENT 'BUFF饰品款式名称',
  `stat_time` datetime DEFAULT NULL COMMENT '统计时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `uk_goods_id_stat_time` (`goods_id`,`stat_time`),
  KEY `idx_goods_id` (`goods_id`),
  KEY `idx_name` (`name`(100)),
  KEY `idx_stat_time` (`stat_time`)
) ENGINE=InnoDB AUTO_INCREMENT=976 DEFAULT CHARSET=utf8mb4 COMMENT='BUFF饰品每日款式销售价格表';

-- ----------------------------
-- Table structure for buff_goods_daily_tag_price_middle
-- ----------------------------
DROP TABLE IF EXISTS `buff_goods_daily_tag_price_middle`;
CREATE TABLE `buff_goods_daily_tag_price_middle` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `goods_id` varchar(64) NOT NULL COMMENT 'BUFF饰品ID',
  `name` varchar(255) NOT NULL COMMENT '饰品名称',
  `min_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '在售最低价格',
  `buy_max_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '求购最高价格',
  `tag_id` varchar(32) DEFAULT NULL COMMENT 'BUFF饰品款式ID',
  `tag_name` varchar(128) DEFAULT NULL COMMENT 'BUFF饰品款式名称',
  `stat_time` datetime DEFAULT NULL COMMENT '统计时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_goods_id` (`goods_id`),
  KEY `idx_name` (`name`(100)),
  KEY `idx_stat_time` (`stat_time`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='BUFF饰品每日款式销售价格表历史中间表';

-- ----------------------------
-- Table structure for buff_goods_float_price
-- ----------------------------
DROP TABLE IF EXISTS `buff_goods_float_price`;
CREATE TABLE `buff_goods_float_price` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `goods_id` varchar(64) NOT NULL COMMENT 'BUFF饰品ID',
  `name` varchar(255) NOT NULL COMMENT '饰品名称',
  `qj_min_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '区间底价',
  `qj_buy_max_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '区间求购最高价',
  `qj_avg_min_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '区间底价前十均价',
  `qj_sell_num` int(11) unsigned DEFAULT '0' COMMENT '区间在售数量',
  `qj_buy_order_num` int(11) unsigned DEFAULT '0' COMMENT '区间求购数量',
  `min_float` decimal(8,2) unsigned DEFAULT '0.00' COMMENT '最小磨损值',
  `max_float` decimal(8,2) unsigned DEFAULT '0.00' COMMENT '最大磨损值',
  `tag_id` varchar(32) DEFAULT NULL COMMENT 'BUFF饰品款式ID',
  `tag_name` varchar(128) DEFAULT NULL COMMENT 'BUFF饰品款式名称',
  `stat_time` datetime DEFAULT NULL COMMENT '统计时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `uk_goods_id_tag_id_stat_time` (`goods_id`,`tag_id`,`stat_time`),
  KEY `idx_goods_id` (`goods_id`),
  KEY `idx_tag_id` (`tag_id`),
  KEY `idx_name` (`name`(100)),
  KEY `idx_stat_time` (`stat_time`)
) ENGINE=InnoDB AUTO_INCREMENT=73044 DEFAULT CHARSET=utf8mb4 COMMENT='BUFF饰品磨损区间价格表';

-- ----------------------------
-- Table structure for buff_goods_float_price_attr
-- ----------------------------
DROP TABLE IF EXISTS `buff_goods_float_price_attr`;
CREATE TABLE `buff_goods_float_price_attr` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `goods_id` varchar(64) NOT NULL COMMENT 'BUFF饰品ID',
  `name` varchar(255) NOT NULL COMMENT '饰品名称',
  `qj_min_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '底价',
  `qj_buy_max_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '求购最高价',
  `qj_avg_min_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '底价前十均价',
  `qj_sell_num` int(11) unsigned DEFAULT '0' COMMENT '在售数量',
  `qj_buy_order_num` int(11) unsigned DEFAULT '0' COMMENT '求购数量',
  `stat_time` datetime DEFAULT NULL COMMENT '统计时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `uk_goods_id_tag_id_stat_time` (`goods_id`,`stat_time`),
  KEY `idx_goods_id` (`goods_id`),
  KEY `idx_name` (`name`(100)),
  KEY `idx_stat_time` (`stat_time`)
) ENGINE=InnoDB AUTO_INCREMENT=30827 DEFAULT CHARSET=utf8mb4 COMMENT='BUFF饰品磨损区间价格属性表';

-- ----------------------------
-- Table structure for buff_goods_float_price_attr_middle
-- ----------------------------
DROP TABLE IF EXISTS `buff_goods_float_price_attr_middle`;
CREATE TABLE `buff_goods_float_price_attr_middle` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `goods_id` varchar(64) NOT NULL COMMENT 'BUFF饰品ID',
  `name` varchar(255) NOT NULL COMMENT '饰品名称',
  `qj_min_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '底价',
  `qj_buy_max_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '求购最高价',
  `qj_avg_min_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '底价前十均价',
  `qj_sell_num` int(11) unsigned DEFAULT '0' COMMENT '在售数量',
  `qj_buy_order_num` int(11) unsigned DEFAULT '0' COMMENT '求购数量',
  `stat_time` datetime DEFAULT NULL COMMENT '统计时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_goods_id` (`goods_id`),
  KEY `idx_name` (`name`(100)),
  KEY `idx_stat_time` (`stat_time`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='BUFF饰品磨损区间价格属性表中间表';

-- ----------------------------
-- Table structure for buff_goods_float_price_middle
-- ----------------------------
DROP TABLE IF EXISTS `buff_goods_float_price_middle`;
CREATE TABLE `buff_goods_float_price_middle` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `goods_id` varchar(64) NOT NULL COMMENT 'BUFF饰品ID',
  `name` varchar(255) NOT NULL COMMENT '饰品名称',
  `qj_min_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '区间底价',
  `qj_buy_max_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '区间求购最高价',
  `qj_avg_min_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '区间底价前十均价',
  `qj_sell_num` int(11) unsigned DEFAULT '0' COMMENT '区间在售数量',
  `qj_buy_order_num` int(11) unsigned DEFAULT '0' COMMENT '区间求购数量',
  `min_float` decimal(8,2) unsigned DEFAULT '0.00' COMMENT '最小磨损值',
  `max_float` decimal(8,2) unsigned DEFAULT '0.00' COMMENT '最大磨损值',
  `tag_id` varchar(32) DEFAULT NULL COMMENT 'BUFF饰品款式ID',
  `tag_name` varchar(128) DEFAULT NULL COMMENT 'BUFF饰品款式名称',
  `stat_time` datetime DEFAULT NULL COMMENT '统计时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_goods_id` (`goods_id`),
  KEY `idx_tag_id` (`tag_id`),
  KEY `idx_name` (`name`(100)),
  KEY `idx_stat_time` (`stat_time`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='BUFF饰品磨损区间价格表中间表';

-- ----------------------------
-- Table structure for buff_goods_info
-- ----------------------------
DROP TABLE IF EXISTS `buff_goods_info`;
CREATE TABLE `buff_goods_info` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `goods_id` varchar(64) NOT NULL COMMENT 'BUFF饰品ID',
  `name` varchar(255) NOT NULL COMMENT '饰品名称',
  `buff_url` varchar(512) DEFAULT NULL COMMENT 'BUFF饰品链接',
  `min_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '当前在售最低价格',
  `d7_avg_min_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '7天在售最低价格平均价',
  `d30_avg_min_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '30天在售最低价格平均价',
  `buy_max_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '求购最高价格',
  `steam_url` varchar(512) DEFAULT NULL COMMENT '饰品STEAM链接',
  `stat_time` datetime DEFAULT NULL COMMENT '统计时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `uk_goods_id` (`goods_id`),
  KEY `idx_name` (`name`(100)),
  KEY `idx_stat_time` (`stat_time`)
) ENGINE=InnoDB AUTO_INCREMENT=30827 DEFAULT CHARSET=utf8mb4 COMMENT='BUFF饰品总表';

-- ----------------------------
-- Table structure for buff_goods_info_middle
-- ----------------------------
DROP TABLE IF EXISTS `buff_goods_info_middle`;
CREATE TABLE `buff_goods_info_middle` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `goods_id` varchar(64) NOT NULL COMMENT 'BUFF饰品ID',
  `name` varchar(255) NOT NULL COMMENT '饰品名称',
  `buff_url` varchar(512) DEFAULT NULL COMMENT 'BUFF饰品链接',
  `min_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '当前在售最低价格',
  `d7_avg_min_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '7天在售最低价格平均价',
  `d30_avg_min_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '30天在售最低价格平均价',
  `buy_max_price` decimal(12,2) unsigned DEFAULT '0.00' COMMENT '求购最高价格',
  `steam_url` varchar(512) DEFAULT NULL COMMENT '饰品STEAM链接',
  `stat_time` datetime DEFAULT NULL COMMENT '统计时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_goods_id` (`goods_id`),
  KEY `idx_name` (`name`(100)),
  KEY `idx_stat_time` (`stat_time`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='BUFF饰品总表中间表';

-- ----------------------------
-- Table structure for buff_goods_liquidity
-- ----------------------------
DROP TABLE IF EXISTS `buff_goods_liquidity`;
CREATE TABLE `buff_goods_liquidity` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `goods_id` varchar(64) NOT NULL COMMENT 'BUFF饰品ID',
  `name` varchar(255) NOT NULL COMMENT '饰品名称',
  `tag_id` varchar(32) DEFAULT NULL COMMENT 'BUFF饰品款式ID',
  `tag_name` varchar(128) DEFAULT NULL COMMENT 'BUFF饰品款式名称',
  `liquidity_rank` varchar(16) DEFAULT NULL COMMENT '饰品流通性等级',
  `stat_time` datetime DEFAULT NULL COMMENT '统计时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `uk_goods_id_tag_id_stat_time` (`goods_id`,`tag_id`,`stat_time`),
  KEY `idx_goods_id` (`goods_id`),
  KEY `idx_tag_id` (`tag_id`),
  KEY `idx_name` (`name`(100)),
  KEY `idx_liquidity_rank` (`liquidity_rank`),
  KEY `idx_stat_time` (`stat_time`)
) ENGINE=InnoDB AUTO_INCREMENT=42660 DEFAULT CHARSET=utf8mb4 COMMENT='BUFF饰品流动性表';

-- ----------------------------
-- Table structure for buff_goods_liquidity_middle
-- ----------------------------
DROP TABLE IF EXISTS `buff_goods_liquidity_middle`;
CREATE TABLE `buff_goods_liquidity_middle` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `goods_id` varchar(64) NOT NULL COMMENT 'BUFF饰品ID',
  `name` varchar(255) NOT NULL COMMENT '饰品名称',
  `tag_id` varchar(32) DEFAULT NULL COMMENT 'BUFF饰品款式ID',
  `tag_name` varchar(128) DEFAULT NULL COMMENT 'BUFF饰品款式名称',
  `liquidity_rank` varchar(16) DEFAULT NULL COMMENT '饰品流通性等级',
  `stat_time` datetime DEFAULT NULL COMMENT '统计时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_goods_id` (`goods_id`),
  KEY `idx_tag_id` (`tag_id`),
  KEY `idx_name` (`name`(100)),
  KEY `idx_liquidity_rank` (`liquidity_rank`),
  KEY `idx_stat_time` (`stat_time`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='BUFF饰品流动性表历史中间表';

-- ----------------------------
-- Table structure for cards
-- ----------------------------
DROP TABLE IF EXISTS `cards`;
CREATE TABLE `cards` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `card_code` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '卡密代码（唯一）',
  `software_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '软件特征码',
  `days` int(11) NOT NULL COMMENT '天数',
  `discount` decimal(5,2) DEFAULT '1.00' COMMENT '折扣（0.01-1.00）',
  `user_id` int(11) DEFAULT NULL COMMENT '激活用户ID（NULL表示未激活）',
  `activated_at` datetime DEFAULT NULL COMMENT '激活时间',
  `expire_time` datetime DEFAULT NULL COMMENT '到期时间（激活时间+天数）',
  `status` tinyint(1) DEFAULT '0' COMMENT '状态：0-未激活，1-已激活，2-已过期',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `card_code` (`card_code`),
  KEY `idx_card_code` (`card_code`),
  KEY `idx_software_code` (`software_code`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_expire_time` (`expire_time`),
  KEY `idx_is_deleted` (`is_deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='卡密表';

-- ----------------------------
-- Table structure for cards_his
-- ----------------------------
DROP TABLE IF EXISTS `cards_his`;
CREATE TABLE `cards_his` (
  `id` int(11) NOT NULL COMMENT '原表主键ID',
  `card_code` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '卡密代码（唯一）',
  `software_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '软件特征码',
  `days` int(11) NOT NULL COMMENT '天数',
  `discount` decimal(5,2) DEFAULT '1.00' COMMENT '折扣（0.01-1.00）',
  `user_id` int(11) DEFAULT NULL COMMENT '激活用户ID（NULL表示未激活）',
  `activated_at` datetime DEFAULT NULL COMMENT '激活时间',
  `expire_time` datetime DEFAULT NULL COMMENT '到期时间（激活时间+天数）',
  `status` tinyint(1) DEFAULT '0' COMMENT '状态：0-未激活，1-已激活，2-已过期',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `his_created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间',
  `his_operation` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '操作类型：INSERT/UPDATE/DELETE'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='卡密表历史记录';

-- ----------------------------
-- Table structure for hmac_keys
-- ----------------------------
DROP TABLE IF EXISTS `hmac_keys`;
CREATE TABLE `hmac_keys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `username` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
  `machine_code` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '机器码',
  `hmac_key` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'HMAC密钥',
  `key_version` int(11) DEFAULT '1' COMMENT '密钥版本',
  `expires_at` datetime NOT NULL COMMENT '过期时间',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否活跃: 1=活跃, 0=停用',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_hmac_key_lookup` (`user_id`,`machine_code`,`is_active`),
  KEY `idx_hmac_key_expiry` (`expires_at`,`is_active`),
  KEY `idx_hmac_key_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='HMAC密钥表';

-- ----------------------------
-- Table structure for login_attempts
-- ----------------------------
DROP TABLE IF EXISTS `login_attempts`;
CREATE TABLE `login_attempts` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `username` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
  `success` tinyint(1) DEFAULT '0' COMMENT '是否成功: 1-成功, 0-失败',
  `machine_code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '机器码',
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'IP地址',
  `user_agent` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户代理',
  `failure_reason` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '失败原因',
  `attempted_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '尝试时间',
  PRIMARY KEY (`id`),
  KEY `idx_login_attempt_user` (`username`),
  KEY `idx_login_attempt_time` (`attempted_at`),
  KEY `idx_login_attempt_user_time` (`username`,`attempted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='登录尝试记录表';

-- ----------------------------
-- Table structure for operation_logs
-- ----------------------------
DROP TABLE IF EXISTS `operation_logs`;
CREATE TABLE `operation_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `admin_id` int(11) DEFAULT NULL COMMENT '管理员ID',
  `module` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '操作模块',
  `action` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '操作动作',
  `description` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '操作描述',
  `old_data` text COLLATE utf8mb4_unicode_ci COMMENT '操作前数据（JSON）',
  `new_data` text COLLATE utf8mb4_unicode_ci COMMENT '操作后数据（JSON）',
  `ip_address` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'IP地址',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
  PRIMARY KEY (`id`),
  KEY `idx_admin_id` (`admin_id`),
  KEY `idx_module` (`module`),
  KEY `idx_action` (`action`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_is_deleted` (`is_deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=333 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='操作日志表';

-- ----------------------------
-- Table structure for operation_logs_his
-- ----------------------------
DROP TABLE IF EXISTS `operation_logs_his`;
CREATE TABLE `operation_logs_his` (
  `id` bigint(20) NOT NULL COMMENT '原表主键ID',
  `admin_id` int(11) DEFAULT NULL COMMENT '管理员ID',
  `module` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '操作模块',
  `action` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '操作动作',
  `description` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '操作描述',
  `old_data` text COLLATE utf8mb4_unicode_ci COMMENT '操作前数据（JSON）',
  `new_data` text COLLATE utf8mb4_unicode_ci COMMENT '操作后数据（JSON）',
  `ip_address` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'IP地址',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
  `his_created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间',
  `his_operation` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '操作类型：INSERT/UPDATE/DELETE'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='操作日志表历史记录';

-- ----------------------------
-- Table structure for password_reset_requests
-- ----------------------------
DROP TABLE IF EXISTS `password_reset_requests`;
CREATE TABLE `password_reset_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `username` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户邮箱',
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending' COMMENT '状态: pending-待处理, processing-处理中, sent-已发送, failed-失败',
  `error_message` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '失败时错误信息',
  `created_at` datetime NOT NULL COMMENT '申请时间',
  `processed_at` datetime DEFAULT NULL COMMENT '处理完成时间',
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='找回密码工单表';

-- ----------------------------
-- Table structure for recharge_records
-- ----------------------------
DROP TABLE IF EXISTS `recharge_records`;
CREATE TABLE `recharge_records` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `card_id` int(11) DEFAULT NULL COMMENT '使用的卡密ID',
  `software_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '软件特征码',
  `days` int(11) NOT NULL COMMENT '充值天数',
  `amount` decimal(10,2) DEFAULT NULL COMMENT '充值金额（可选，用于统计）',
  `expire_time_before` datetime DEFAULT NULL COMMENT '充值前到期时间',
  `expire_time_after` datetime DEFAULT NULL COMMENT '充值后到期时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '充值时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_card_id` (`card_id`),
  KEY `idx_software_code` (`software_code`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_is_deleted` (`is_deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='充值记录表';

-- ----------------------------
-- Table structure for recharge_records_his
-- ----------------------------
DROP TABLE IF EXISTS `recharge_records_his`;
CREATE TABLE `recharge_records_his` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `card_id` int(11) DEFAULT NULL COMMENT '使用的卡密ID',
  `software_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '软件特征码',
  `days` int(11) NOT NULL COMMENT '充值天数',
  `amount` decimal(10,2) DEFAULT NULL COMMENT '充值金额（可选，用于统计）',
  `expire_time_before` datetime DEFAULT NULL COMMENT '充值前到期时间',
  `expire_time_after` datetime DEFAULT NULL COMMENT '充值后到期时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '充值时间',
  `his_created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间',
  `his_operation` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'INSERT' COMMENT '操作类型: INSERT/UPDATE/DELETE',
  `action_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'INSERT' COMMENT '操作类型: INSERT/UPDATE/DELETE',
  `changed_fields` text COLLATE utf8mb4_unicode_ci COMMENT '变更字段(JSON格式)',
  `operator` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '操作者: system/user_id',
  `record_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '原始记录ID',
  `created_at_his` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_recharge_his_record_id` (`record_id`),
  KEY `idx_recharge_his_action_type` (`action_type`),
  KEY `idx_recharge_his_created_at_his` (`created_at_his`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='充值记录表历史记录';

-- ----------------------------
-- Table structure for sessions
-- ----------------------------
DROP TABLE IF EXISTS `sessions`;
CREATE TABLE `sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `session_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '会话ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `machine_code` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '机器码',
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'IP地址',
  `user_agent` text COLLATE utf8mb4_unicode_ci COMMENT '用户代理',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否激活：0-否，1-是',
  `expires_at` datetime NOT NULL COMMENT '过期时间',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `last_activity` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后活动时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_sessions_session_id` (`session_id`),
  KEY `ix_sessions_user_id` (`user_id`),
  KEY `ix_sessions_is_active` (`is_active`),
  KEY `ix_sessions_expires_at` (`expires_at`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户会话表';

-- ----------------------------
-- Table structure for sessions_his
-- ----------------------------
DROP TABLE IF EXISTS `sessions_his`;
CREATE TABLE `sessions_his` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `session_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '会话ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `machine_code` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '机器码',
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'IP地址',
  `user_agent` text COLLATE utf8mb4_unicode_ci COMMENT '用户代理',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否激活：0-否，1-是',
  `expires_at` datetime NOT NULL COMMENT '过期时间',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `last_activity` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后活动时间',
  PRIMARY KEY (`id`),
  KEY `ix_sessions_user_id` (`user_id`),
  KEY `ix_sessions_is_active` (`is_active`),
  KEY `ix_sessions_expires_at` (`expires_at`),
  KEY `ix_sessions_session_id` (`session_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户会话表';

-- ----------------------------
-- Table structure for software
-- ----------------------------
DROP TABLE IF EXISTS `software`;
CREATE TABLE `software` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '软件特征码，如：buff-xp-steam-sh',
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '软件名称',
  `description` text COLLATE utf8mb4_unicode_ci COMMENT '软件描述',
  `daily_price` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '每天收费基数（元）',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否启用：1-启用，0-禁用',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `idx_code` (`code`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_is_deleted` (`is_deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='软件信息表';

-- ----------------------------
-- Table structure for software_his
-- ----------------------------
DROP TABLE IF EXISTS `software_his`;
CREATE TABLE `software_his` (
  `id` int(11) NOT NULL COMMENT '原表主键ID',
  `code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '软件特征码，如：buff-xp-steam-sh',
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '软件名称',
  `description` text COLLATE utf8mb4_unicode_ci COMMENT '软件描述',
  `daily_price` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '每天收费基数（元）',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否启用：1-启用，0-禁用',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `his_created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间',
  `his_operation` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '操作类型：INSERT/UPDATE/DELETE'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='软件信息表历史记录';

-- ----------------------------
-- Table structure for steam_accounts
-- ----------------------------
DROP TABLE IF EXISTS `steam_accounts`;
CREATE TABLE `steam_accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `steam_username` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Steam用户名',
  `steam_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Steam ID',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否启用：1-启用，0-禁用',
  `last_used_time` datetime DEFAULT NULL COMMENT '最后使用时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `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_steam_username` (`steam_username`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_is_deleted` (`is_deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Steam账号绑定表';

-- ----------------------------
-- Table structure for steam_accounts_his
-- ----------------------------
DROP TABLE IF EXISTS `steam_accounts_his`;
CREATE TABLE `steam_accounts_his` (
  `id` int(11) NOT NULL COMMENT '原表主键ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `steam_username` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Steam用户名',
  `steam_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Steam ID',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否启用：1-启用，0-禁用',
  `last_used_time` datetime DEFAULT NULL COMMENT '最后使用时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '绑定时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `his_created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间',
  `his_operation` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '操作类型：INSERT/UPDATE/DELETE'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Steam账号绑定表历史记录';

-- ----------------------------
-- Table structure for system_config
-- ----------------------------
DROP TABLE IF EXISTS `system_config`;
CREATE TABLE `system_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `config_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '配置键名',
  `config_value` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '配置值',
  `config_type` enum('int','string','bool','json') COLLATE utf8mb4_unicode_ci DEFAULT 'string' COMMENT '配置值类型',
  `app_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT 'backend-admin' COMMENT '所属应用：backend-admin、backend-client，多个用逗号拼接',
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '配置描述',
  `is_system` tinyint(1) DEFAULT '1' COMMENT '是否为系统配置：1-是，0-否',
  `is_editable` tinyint(1) DEFAULT '1' COMMENT '是否可编辑：1-是，0-否',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_config_key` (`config_key`,`is_deleted`) COMMENT '配置键唯一约束'
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统配置表（纵表设计）';

-- ----------------------------
-- Table structure for system_config_copy1
-- ----------------------------
DROP TABLE IF EXISTS `system_config_copy1`;
CREATE TABLE `system_config_copy1` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `config_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '配置键名',
  `config_value` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '配置值',
  `config_type` enum('int','string','bool','json') COLLATE utf8mb4_unicode_ci DEFAULT 'string' COMMENT '配置值类型',
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '配置描述',
  `is_system` tinyint(1) DEFAULT '1' COMMENT '是否为系统配置：1-是，0-否',
  `is_editable` tinyint(1) DEFAULT '1' COMMENT '是否可编辑：1-是，0-否',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_config_key` (`config_key`,`is_deleted`) COMMENT '配置键唯一约束'
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统配置表（纵表设计）';

-- ----------------------------
-- Table structure for system_config_his
-- ----------------------------
DROP TABLE IF EXISTS `system_config_his`;
CREATE TABLE `system_config_his` (
  `id` int(11) NOT NULL COMMENT '原表主键ID',
  `config_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '配置键名',
  `config_value` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '配置值',
  `config_type` enum('int','string','bool','json') COLLATE utf8mb4_unicode_ci DEFAULT 'string' COMMENT '配置值类型',
  `app_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT 'backend-admin' COMMENT '所属应用：backend-admin、backend-client，多个用逗号拼接',
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '配置描述',
  `is_system` tinyint(1) DEFAULT '1' COMMENT '是否为系统配置：1-是，0-否',
  `is_editable` tinyint(1) DEFAULT '1' COMMENT '是否可编辑：1-是，0-否',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  `his_created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间',
  `his_operation` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '操作类型：INSERT/UPDATE/DELETE',
  PRIMARY KEY (`id`,`his_created_at`),
  KEY `idx_system_config_his_config_key` (`config_key`),
  KEY `idx_system_config_his_his_created_at` (`his_created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统配置表历史记录（与 system_config 镜像）';

-- ----------------------------
-- Table structure for user_software_subscription
-- ----------------------------
DROP TABLE IF EXISTS `user_software_subscription`;
CREATE TABLE `user_software_subscription` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `software_id` int(11) NOT NULL COMMENT '软件ID',
  `card_id` int(11) DEFAULT NULL COMMENT '当前卡密ID',
  `expire_time` datetime DEFAULT NULL COMMENT '到期时间',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除: 0-否, 1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_software` (`user_id`,`software_id`),
  KEY `idx_user_software_subscription_user_id` (`user_id`),
  KEY `idx_user_software_subscription_software_id` (`software_id`),
  KEY `idx_user_software_subscription_expire_time` (`expire_time`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户软件订阅：多软件共用用户表时，每用户每软件一条';

-- ----------------------------
-- Table structure for user_steam_account_limit
-- ----------------------------
DROP TABLE IF EXISTS `user_steam_account_limit`;
CREATE TABLE `user_steam_account_limit` (
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `max_steam_accounts` int(11) NOT NULL DEFAULT '3' COMMENT '该用户最多可绑定的 Steam 账号数',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`user_id`),
  KEY `idx_max_steam_accounts` (`max_steam_accounts`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='每用户Steam账号绑定数量上限';

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `username` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名（唯一）',
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码（加密存储）',
  `email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '邮箱',
  `machine_code` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '绑定的机器码（MD5）',
  `card_id` int(11) DEFAULT NULL COMMENT '当前激活的卡密ID',
  `expire_time` datetime DEFAULT NULL COMMENT '到期时间',
  `last_login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
  `last_login_ip` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '最后登录IP',
  `status` tinyint(1) DEFAULT '1' COMMENT '状态：1-正常，0-禁用',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `idx_username` (`username`),
  KEY `idx_machine_code` (`machine_code`),
  KEY `idx_card_id` (`card_id`),
  KEY `idx_expire_time` (`expire_time`),
  KEY `idx_status` (`status`),
  KEY `idx_is_deleted` (`is_deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

-- ----------------------------
-- Table structure for users_his
-- ----------------------------
DROP TABLE IF EXISTS `users_his`;
CREATE TABLE `users_his` (
  `id` int(11) NOT NULL COMMENT '原表主键ID',
  `username` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名（唯一）',
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码（加密存储）',
  `email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '邮箱',
  `machine_code` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '绑定的机器码（MD5）',
  `card_id` int(11) DEFAULT NULL COMMENT '当前激活的卡密ID',
  `expire_time` datetime DEFAULT NULL COMMENT '到期时间',
  `last_login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
  `last_login_ip` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '最后登录IP',
  `status` tinyint(1) DEFAULT '1' COMMENT '状态：1-正常，0-禁用',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `his_created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间',
  `his_operation` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '操作类型：INSERT/UPDATE/DELETE'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表历史记录';

-- ----------------------------
-- View structure for buff_goods_comprehensive_view
-- ----------------------------
DROP VIEW IF EXISTS `buff_goods_comprehensive_view`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `buff_goods_comprehensive_view` AS select `bgfp`.`goods_id` AS `BUFF饰品ID`,`bgfp`.`name` AS `BUFF饰品名称`,`bgi`.`buff_url` AS `BUFF饰品链接`,`bga`.`icon_url` AS `饰品图片URL地址`,`bgi`.`min_price` AS `当前在售最低价格`,`bgi`.`d7_avg_min_price` AS `7天在售最低价格平均价`,`bgi`.`d30_avg_min_price` AS `30天在售最低价格平均价`,`bgi`.`buy_max_price` AS `求购最高价格`,`bgfpa`.`qj_sell_num` AS `在售数量`,`bgfpa`.`qj_buy_order_num` AS `求购数量`,`bgfpa`.`qj_avg_min_price` AS `底价前十均价`,`bgi`.`steam_url` AS `饰品STEAM链接`,`bgfp`.`min_float` AS `最小磨损值`,`bgfp`.`max_float` AS `最大磨损值`,`bgfp`.`qj_min_price` AS `区间底价`,`bgfp`.`qj_buy_max_price` AS `区间求购最高价`,`bgfp`.`qj_avg_min_price` AS `区间底价前十均价`,`bgfp`.`qj_sell_num` AS `区间在售数量`,`bgfp`.`qj_buy_order_num` AS `区间求购数量`,`bgfp`.`tag_id` AS `BUFF饰品款式ID`,`bgfp`.`tag_name` AS `BUFF饰品款式名称`,`bga`.`type` AS `type编码`,`bga`.`type_name` AS `type编码名称`,`bga`.`category_group` AS `分组编码`,`bga`.`category_group_name` AS `分组编码名称`,`bga`.`category` AS `分类编码`,`bga`.`category_name` AS `分类编码名称`,`bga`.`rarity` AS `品质`,`bga`.`rarity_name` AS `品质名称`,`bga`.`quality` AS `类别`,`bga`.`quality_name` AS `类别名称`,`bga`.`exterior` AS `外观`,`bga`.`exterior_name` AS `外观名称`,`bga`.`weapon` AS `武器`,`bga`.`weapon_name` AS `武器名称`,`bga`.`series` AS `热门皮肤`,`bga`.`series_name` AS `热门皮肤名称`,`bga`.`custom` AS `颜色`,`bga`.`custom_name` AS `颜色名称`,`bga`.`itemset` AS `收藏品`,`bga`.`itemset_name` AS `收藏品名称` from (((`buff_goods_float_price` `bgfp` left join `buff_goods_info` `bgi` on((`bgfp`.`goods_id` = `bgi`.`goods_id`))) left join `buff_goods_attr` `bga` on((`bgfp`.`goods_id` = `bga`.`goods_id`))) left join `buff_goods_float_price_attr` `bgfpa` on((`bgfp`.`goods_id` = `bgfpa`.`goods_id`)));

-- ----------------------------
-- View: 选品视图（基于综合视图，筛选指定分组/品质/类别/外观）
-- ----------------------------
DROP VIEW IF EXISTS `buff_goods_filtered_view`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `buff_goods_filtered_view` AS
SELECT * FROM `buff_goods_comprehensive_view`
WHERE `分组编码名称` IN ('步枪','手枪','微型冲锋枪','霰弹枪','机枪')
  AND `品质名称` IN ('隐秘','违禁','保密','受限','军规级','工业级','消费级')
  AND `类别名称` IN ('普通','StatTrak™')
  AND `外观名称` IN ('崭新出厂','略有磨损','久经沙场');

SET FOREIGN_KEY_CHECKS = 1;