buff/.history/游铛铛-数据库设计_20251110113958.sql

-- ============================================
-- 游铛铛产品数据库设计
-- 数据库：buff
-- 服务器：47.99.164.66:3306
-- ============================================

-- 创建数据库
CREATE DATABASE IF NOT EXISTS `buff` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `buff`;

-- ============================================
-- 1. 软件表（software）
-- 功能：管理软件特征码和收费配置
-- ============================================
CREATE TABLE IF NOT EXISTS `software` (
    `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    `code` VARCHAR(50) UNIQUE NOT NULL COMMENT '软件特征码，如：buff-xp-steam-sh',
    `name` VARCHAR(100) NOT NULL COMMENT '软件名称',
    `description` TEXT COMMENT '软件描述',
    `daily_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '每天收费基数（元）',
    `is_active` TINYINT(1) DEFAULT 1 COMMENT '是否启用：1-启用，0-禁用',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX `idx_code` (`code`),
    INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='软件信息表';

-- ============================================
-- 2. 用户表（users）
-- 功能：存储注册用户信息
-- ============================================
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    `username` VARCHAR(50) UNIQUE NOT NULL COMMENT '用户名（唯一）',
    `password` VARCHAR(255) NOT NULL COMMENT '密码（加密存储）',
    `email` VARCHAR(100) COMMENT '邮箱',
    `machine_code` VARCHAR(64) COMMENT '绑定的机器码（MD5）',
    `card_id` INT COMMENT '当前激活的卡密ID',
    `expire_time` DATETIME COMMENT '到期时间',
    `last_login_time` DATETIME COMMENT '最后登录时间',
    `last_login_ip` VARCHAR(50) COMMENT '最后登录IP',
    `status` TINYINT(1) DEFAULT 1 COMMENT '状态：1-正常，0-禁用',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX `idx_username` (`username`),
    INDEX `idx_machine_code` (`machine_code`),
    INDEX `idx_card_id` (`card_id`),
    INDEX `idx_expire_time` (`expire_time`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

-- ============================================
-- 3. 卡密表（cards）
-- 功能：存储卡密信息和激活状态
-- ============================================
CREATE TABLE IF NOT EXISTS `cards` (
    `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    `card_code` VARCHAR(32) UNIQUE NOT NULL COMMENT '卡密代码（唯一）',
    `software_code` VARCHAR(50) NOT NULL COMMENT '软件特征码',
    `days` INT NOT NULL COMMENT '天数',
    `discount` DECIMAL(5,2) DEFAULT 1.00 COMMENT '折扣（0.01-1.00）',
    `user_id` INT COMMENT '激活用户ID（NULL表示未激活）',
    `activated_at` DATETIME COMMENT '激活时间',
    `expire_time` DATETIME COMMENT '到期时间（激活时间+天数）',
    `status` TINYINT(1) DEFAULT 0 COMMENT '状态：0-未激活，1-已激活，2-已过期',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX `idx_card_code` (`card_code`),
    INDEX `idx_software_code` (`software_code`),
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_expire_time` (`expire_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='卡密表';

-- ============================================
-- 4. 充值记录表（recharge_records）
-- 功能：记录用户充值历史
-- ============================================
CREATE TABLE IF NOT EXISTS `recharge_records` (
    `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    `user_id` INT NOT NULL COMMENT '用户ID',
    `card_id` INT COMMENT '使用的卡密ID',
    `software_code` VARCHAR(50) NOT NULL COMMENT '软件特征码',
    `days` INT NOT NULL COMMENT '充值天数',
    `amount` DECIMAL(10,2) COMMENT '充值金额（可选，用于统计）',
    `expire_time_before` DATETIME COMMENT '充值前到期时间',
    `expire_time_after` DATETIME COMMENT '充值后到期时间',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '充值时间',
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_card_id` (`card_id`),
    INDEX `idx_software_code` (`software_code`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='充值记录表';

-- ============================================
-- 5. Steam账号绑定表（steam_accounts）
-- 功能：存储用户绑定的Steam账号信息
-- ============================================
CREATE TABLE IF NOT EXISTS `steam_accounts` (
    `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    `user_id` INT NOT NULL COMMENT '用户ID',
    `steam_username` VARCHAR(100) NOT NULL COMMENT 'Steam用户名',
    `steam_id` VARCHAR(50) COMMENT 'Steam ID',
    `shared_secret` TEXT COMMENT 'Steam Guard共享密钥（加密存储）',
    `identity_secret` TEXT COMMENT 'Steam Guard身份密钥（加密存储）',
    `is_active` TINYINT(1) DEFAULT 1 COMMENT '是否启用：1-启用，0-禁用',
    `last_used_time` DATETIME COMMENT '最后使用时间',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '绑定时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_steam_username` (`steam_username`),
    INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Steam账号绑定表';

-- ============================================
-- 6. API密钥表（api_keys）
-- 功能：存储RSA公钥和私钥，支持密钥轮换
-- ============================================
CREATE TABLE IF NOT EXISTS `api_keys` (
    `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    `public_key` TEXT NOT NULL COMMENT 'RSA公钥（PEM格式）',
    `private_key` TEXT NOT NULL COMMENT 'RSA私钥（PEM格式，加密存储）',
    `key_version` INT NOT NULL COMMENT '密钥版本号',
    `is_active` TINYINT(1) DEFAULT 1 COMMENT '是否激活：1-激活，0-非激活',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `expire_at` DATETIME COMMENT '过期时间（创建时间+10天）',
    INDEX `idx_key_version` (`key_version`),
    INDEX `idx_is_active` (`is_active`),
    INDEX `idx_expire_at` (`expire_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='API密钥表';

-- ============================================
-- 7. 密钥轮换配置表（key_rotation_config）
-- 功能：配置密钥轮换周期等参数
-- ============================================
CREATE TABLE IF NOT EXISTS `key_rotation_config` (
    `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    `rotation_days` INT DEFAULT 10 COMMENT '密钥轮换周期（天）',
    `is_auto_rotate` TINYINT(1) DEFAULT 1 COMMENT '是否自动轮换：1-是，0-否',
    `last_rotation_time` DATETIME COMMENT '最后轮换时间',
    `next_rotation_time` DATETIME COMMENT '下次轮换时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='密钥轮换配置表';

-- ============================================
-- 8. 公告表（announcements）
-- 功能：存储产品公告信息
-- ============================================
CREATE TABLE IF NOT EXISTS `announcements` (
    `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    `title` VARCHAR(200) NOT NULL COMMENT '公告标题',
    `content` TEXT NOT NULL COMMENT '公告内容',
    `priority` TINYINT DEFAULT 0 COMMENT '优先级：0-普通，1-重要，2-紧急',
    `is_active` TINYINT(1) DEFAULT 1 COMMENT '是否启用：1-启用，0-禁用',
    `publish_time` DATETIME COMMENT '发布时间',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX `idx_is_active` (`is_active`),
    INDEX `idx_priority` (`priority`),
    INDEX `idx_publish_time` (`publish_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='公告表';

-- ============================================
-- 9. API日志表（api_logs）
-- 功能：记录所有API接口调用日志
-- ============================================
CREATE TABLE IF NOT EXISTS `api_logs` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    `user_id` INT COMMENT '用户ID（可为NULL，如登录接口）',
    `endpoint` VARCHAR(200) NOT NULL COMMENT '接口路径',
    `method` VARCHAR(10) NOT NULL COMMENT 'HTTP方法：GET、POST等',
    `request_data` TEXT COMMENT '请求数据（加密前或解密后）',
    `response_data` TEXT COMMENT '响应数据（加密前或解密后）',
    `ip_address` VARCHAR(50) COMMENT '客户端IP地址',
    `user_agent` VARCHAR(500) COMMENT '用户代理',
    `response_code` INT COMMENT '响应状态码',
    `execution_time` INT COMMENT '执行时间（毫秒）',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_endpoint` (`endpoint`),
    INDEX `idx_method` (`method`),
    INDEX `idx_ip_address` (`ip_address`),
    INDEX `idx_created_at` (`created_at`),
    INDEX `idx_response_code` (`response_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='API日志表';

-- ============================================
-- 10. 管理员表（admins）
-- 功能：存储管理后台管理员账号
-- ============================================
CREATE TABLE IF NOT EXISTS `admins` (
    `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    `username` VARCHAR(50) UNIQUE NOT NULL COMMENT '管理员用户名',
    `password` VARCHAR(255) NOT NULL COMMENT '密码（加密存储）',
    `real_name` VARCHAR(50) COMMENT '真实姓名',
    `email` VARCHAR(100) COMMENT '邮箱',
    `role` VARCHAR(20) DEFAULT 'admin' COMMENT '角色：admin-管理员，super_admin-超级管理员',
    `last_login_time` DATETIME COMMENT '最后登录时间',
    `last_login_ip` VARCHAR(50) COMMENT '最后登录IP',
    `status` TINYINT(1) DEFAULT 1 COMMENT '状态：1-正常，0-禁用',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX `idx_username` (`username`),
    INDEX `idx_role` (`role`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='管理员表';

-- ============================================
-- 11. 操作日志表（operation_logs）
-- 功能：记录管理后台的操作日志
-- ============================================
CREATE TABLE IF NOT EXISTS `operation_logs` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    `admin_id` INT COMMENT '管理员ID',
    `module` VARCHAR(50) COMMENT '操作模块',
    `action` VARCHAR(50) COMMENT '操作动作',
    `description` VARCHAR(500) COMMENT '操作描述',
    `old_data` TEXT COMMENT '操作前数据（JSON）',
    `new_data` TEXT COMMENT '操作后数据（JSON）',
    `ip_address` VARCHAR(50) COMMENT 'IP地址',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
    INDEX `idx_admin_id` (`admin_id`),
    INDEX `idx_module` (`module`),
    INDEX `idx_action` (`action`),
    INDEX `idx_created_at` (`created_at`),
    FOREIGN KEY (`admin_id`) REFERENCES `admins`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='操作日志表';

-- ============================================
-- 初始化数据
-- ============================================

-- 插入默认软件信息
INSERT INTO `software` (`code`, `name`, `description`, `daily_price`, `is_active`) VALUES
('buff-xp-steam-sh', '游铛铛-Buff选品Steam扫货', 'Buff平台选品和Steam平台扫货软件', 10.00, 1)
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);

-- 插入默认密钥轮换配置
INSERT INTO `key_rotation_config` (`rotation_days`, `is_auto_rotate`) VALUES
(10, 1)
ON DUPLICATE KEY UPDATE `rotation_days`=VALUES(`rotation_days`);

-- 插入默认管理员账号（密码：admin123，实际使用时需要加密）
-- 注意：实际部署时需要修改密码
INSERT INTO `admins` (`username`, `password`, `real_name`, `role`, `status`) VALUES
('admin', '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewY5GyYqBWVHxkd0', '系统管理员', 'super_admin', 1)
ON DUPLICATE KEY UPDATE `username`=VALUES(`username`);

-- ============================================
-- 数据库设计说明
-- ============================================
-- 1. 所有表使用utf8mb4字符集，支持emoji和特殊字符
-- 2. 所有时间字段使用DATETIME类型
-- 3. 密码字段使用VARCHAR(255)，支持各种加密算法
-- 4. 外键约束保证数据完整性
-- 5. 索引优化查询性能
-- 6. 软删除使用status字段，不物理删除数据
-- ============================================