buff2.0/docs/buff_client.sql

/*
 游铛铛客户端数据库模型 - SQL脚本
 创建日期: 2026-02-02
 文档版本: v1.0.0
 描述: 客户端exe相关数据库模型，包含用户认证、卡密充值、心跳配置等
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ==================== 用户认证模块 ====================

-- ----------------------------
-- Table structure for users (用户表)
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `username` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码(bcrypt加密)',
  `email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '邮箱',
  `machine_code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '机器码(MD5)',
  `card_id` int(11) DEFAULT NULL COMMENT '当前卡密ID',
  `expire_time` datetime DEFAULT NULL COMMENT '到期时间',
  `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 `uk_username` (`username`),
  KEY `idx_machine_code` (`machine_code`),
  KEY `idx_status` (`status`),
  KEY `idx_expire_time` (`expire_time`)
) ENGINE=InnoDB 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(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码',
  `email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '邮箱',
  `machine_code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '机器码',
  `card_id` int(11) DEFAULT NULL COMMENT '当前卡密ID',
  `expire_time` datetime DEFAULT NULL COMMENT '到期时间',
  `status` tinyint(1) DEFAULT '1' COMMENT '状态',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除',
  `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',
  PRIMARY KEY (`id`, `his_created_at`),
  KEY `idx_his_created_at` (`his_created_at`)
) ENGINE=InnoDB 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(UUID)',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `machine_code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '机器码',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否激活',
  `expires_at` datetime NOT 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_session_id` (`session_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_expires_at` (`expires_at`)
) ENGINE=InnoDB 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 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(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '机器码',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否激活',
  `expires_at` datetime NOT NULL COMMENT '过期时间',
  `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/RENEW',
  PRIMARY KEY (`id`, `his_created_at`),
  KEY `idx_his_created_at` (`his_created_at`)
) 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 '软件名称',
  `daily_price` decimal(10,2) DEFAULT '0.00' COMMENT '每天价格基数',
  `description` text COLLATE utf8mb4_unicode_ci 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 `uk_code` (`code`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB 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 '软件特征码',
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '软件名称',
  `daily_price` decimal(10,2) DEFAULT '0.00' COMMENT '每天价格基数',
  `description` text COLLATE utf8mb4_unicode_ci COMMENT '软件描述',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否启用',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除',
  `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',
  PRIMARY KEY (`id`, `his_created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='软件产品表历史记录';

-- ----------------------------
-- Table structure for cards (卡密表)
-- ----------------------------
DROP TABLE IF EXISTS `cards`;
CREATE TABLE `cards` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `card_code` varchar(50) 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` int(11) DEFAULT '100' COMMENT '折扣(100=原价)',
  `user_id` int(11) DEFAULT NULL COMMENT '激活用户ID',
  `activated_at` datetime DEFAULT NULL COMMENT '激活时间',
  `expire_time` datetime DEFAULT NULL COMMENT '到期时间',
  `status` tinyint(4) 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 `uk_card_code` (`card_code`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_software_code` (`software_code`)
) ENGINE=InnoDB 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(50) 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` int(11) DEFAULT '100' COMMENT '折扣',
  `user_id` int(11) DEFAULT NULL COMMENT '激活用户ID',
  `activated_at` datetime DEFAULT NULL COMMENT '激活时间',
  `expire_time` datetime DEFAULT NULL COMMENT '到期时间',
  `status` tinyint(4) DEFAULT '0' COMMENT '状态',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除',
  `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',
  PRIMARY KEY (`id`, `his_created_at`)
) ENGINE=InnoDB 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) NOT NULL COMMENT '卡密ID',
  `software_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '软件特征码',
  `days` int(11) NOT NULL COMMENT '充值天数',
  `expire_time_before` datetime DEFAULT NULL COMMENT '充值前到期时间',
  `expire_time_after` datetime DEFAULT NULL COMMENT '充值后到期时间',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_card_id` (`card_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB 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(11) NOT NULL COMMENT '原表主键ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `card_id` int(11) NOT NULL COMMENT '卡密ID',
  `software_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '软件特征码',
  `days` int(11) NOT NULL COMMENT '充值天数',
  `expire_time_before` datetime DEFAULT NULL COMMENT '充值前到期时间',
  `expire_time_after` datetime DEFAULT NULL COMMENT '充值后到期时间',
  `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',
  PRIMARY KEY (`id`, `his_created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='充值记录表历史记录';

-- ==================== 系统配置模块 ====================

-- ----------------------------
-- 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` text COLLATE utf8mb4_unicode_ci COMMENT '配置值',
  `config_type` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'string' COMMENT '类型: int/string/bool/json',
  `app_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '所属应用(backend-admin/backend-client)',
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '配置描述',
  `is_system` tinyint(1) DEFAULT '0' COMMENT '是否系统配置',
  `is_editable` tinyint(1) DEFAULT '1' 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`),
  UNIQUE KEY `uk_config_key_app` (`config_key`, `app_name`),
  KEY `idx_app_name` (`app_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统配置表';

-- ----------------------------
-- 客户端核心配置数据 (初始数据)
-- ----------------------------
INSERT INTO `system_config` (`config_key`, `config_value`, `config_type`, `app_name`, `description`, `is_system`) VALUES
-- 心跳相关配置
('heartbeat_interval_seconds', '10', 'int', 'backend-client', '心跳检测间隔秒数', 0),
('max_heartbeat_failures', '100', 'int', 'backend-client', '心跳最大失败次数', 0),
-- Session自动续期配置
('session_renewal_threshold', '1800', 'int', 'backend-client', 'Session续期阈值(秒)，剩余时间小于此值时触发续期', 0),
('session_extension_duration', '3600', 'int', 'backend-client', 'Session延长时间(秒)，续期时延长的时长', 0),
('session_default_expiry_seconds', '86400', 'int', 'backend-client', 'Session默认有效期(秒)，新登录时创建的有效期', 0),
-- 安全相关配置
('timestamp_tolerance_seconds', '300', 'int', 'backend-client', '时间戳容忍秒数(±5分钟)', 0),
('prevent_duplicate_login', 'true', 'bool', 'backend-client', '是否防止重复登录', 0),
-- 登录相关配置
('max_login_attempts', '5', 'int', 'backend-client', '最大登录失败次数', 0),
('login_lockout_minutes', '60', 'int', 'backend-client', '登录锁定分钟数', 0),
-- 公告相关配置
('announcement_cache_seconds', '300', 'int', 'backend-client', '公告缓存秒数', 0),
-- 密钥相关配置 (客户端只读)
('key_sync_interval_seconds', '60', 'int', 'backend-client', '密钥同步间隔秒数', 0);

-- ----------------------------
-- 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` text COLLATE utf8mb4_unicode_ci COMMENT '配置值',
  `config_type` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'string' COMMENT '类型',
  `app_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '所属应用',
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '配置描述',
  `is_system` tinyint(1) DEFAULT '0' COMMENT '是否系统配置',
  `is_editable` tinyint(1) DEFAULT '1' COMMENT '是否可编辑',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除',
  `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',
  PRIMARY KEY (`id`, `his_created_at`)
) 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`)
) ENGINE=InnoDB 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 '优先级',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否启用',
  `publish_time` datetime DEFAULT NULL COMMENT '发布时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除',
  `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',
  PRIMARY KEY (`id`, `his_created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='公告表历史记录';

-- ==================== Steam账号模块 ====================

-- ----------------------------
-- Table structure for steam_accounts (Steam账号绑定表)
-- ----------------------------
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(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Steam ID',
  `shared_secret` text COLLATE utf8mb4_unicode_ci COMMENT 'Steam Guard共享密钥(加密存储)',
  `identity_secret` text COLLATE utf8mb4_unicode_ci COMMENT 'Steam Guard身份密钥(加密存储)',
  `is_active` tinyint(1) DEFAULT '1' 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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Steam账号绑定表';

-- ----------------------------
-- Table structure for steam_accounts_his (Steam账号绑定表历史记录)
-- ----------------------------
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(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Steam ID',
  `shared_secret` text COLLATE utf8mb4_unicode_ci COMMENT 'Steam Guard共享密钥',
  `identity_secret` text COLLATE utf8mb4_unicode_ci COMMENT 'Steam Guard身份密钥',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否启用',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除',
  `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',
  PRIMARY KEY (`id`, `his_created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Steam账号绑定表历史记录';

-- ==================== API密钥管理模块 ====================

-- ----------------------------
-- Table structure for api_keys (API密钥表)
-- ----------------------------
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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='API密钥表';

-- ----------------------------
-- Table structure for api_keys_his (API密钥表历史记录)
-- ----------------------------
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公钥',
  `private_key` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'RSA私钥',
  `key_version` int(11) NOT NULL COMMENT '密钥版本号',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否激活',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `expire_at` datetime DEFAULT NULL COMMENT '过期时间',
  `his_created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间',
  `his_operation` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '操作类型: INSERT/UPDATE/DELETE',
  PRIMARY KEY (`id`, `his_created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='API密钥表历史记录';

-- ==================== API安全模块 ====================

-- ----------------------------
-- Table structure for api_nonces (API Nonce防重放表)
-- ----------------------------
DROP TABLE IF EXISTS `api_nonces`;
CREATE TABLE `api_nonces` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `nonce` varchar(64) 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 DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_nonce_endpoint` (`nonce`, `endpoint`),
  KEY `idx_expires_at` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='API Nonce防重放表';

-- ----------------------------
-- Table structure for api_nonces_his (API Nonce表历史记录)
-- ----------------------------
DROP TABLE IF EXISTS `api_nonces_his`;
CREATE TABLE `api_nonces_his` (
  `id` int(11) NOT NULL COMMENT '原表主键ID',
  `nonce` varchar(64) 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 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',
  PRIMARY KEY (`id`, `his_created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='API Nonce表历史记录';

-- ----------------------------
-- Table structure for api_logs (API请求日志表)
-- ----------------------------
DROP TABLE IF EXISTS `api_logs`;
CREATE TABLE `api_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` int(11) DEFAULT NULL COMMENT '用户ID(可为NULL)',
  `endpoint` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '接口路径',
  `method` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'HTTP方法',
  `request_data` text COLLATE utf8mb4_unicode_ci COMMENT '加密前的原始请求参数',
  `response_data` text COLLATE utf8mb4_unicode_ci COMMENT '解密后的原始响应数据',
  `ip_address` varchar(45) 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 '执行时间(毫秒)',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id_created_at` (`user_id`, `created_at`),
  KEY `idx_endpoint` (`endpoint`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='API请求日志表';

-- ----------------------------
-- Table structure for api_logs_his (API日志表历史记录)
-- ----------------------------
DROP TABLE IF EXISTS `api_logs_his`;
CREATE TABLE `api_logs_his` (
  `id` int(11) NOT NULL COMMENT '原表主键ID',
  `user_id` int(11) DEFAULT NULL COMMENT '用户ID',
  `endpoint` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '接口路径',
  `method` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'HTTP方法',
  `request_data` text COLLATE utf8mb4_unicode_ci COMMENT '请求参数',
  `response_data` text COLLATE utf8mb4_unicode_ci COMMENT '响应数据',
  `ip_address` varchar(45) 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 '执行时间(毫秒)',
  `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',
  PRIMARY KEY (`id`, `his_created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='API请求日志表历史记录';

SET FOREIGN_KEY_CHECKS = 1;