-- 创建system_config表（纵表设计）
CREATE TABLE IF NOT EXISTS `system_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `config_key` varchar(100) NOT NULL COMMENT '配置键名',
  `config_value` varchar(500) NOT NULL COMMENT '配置值',
  `config_type` enum('int','string','bool','json') DEFAULT 'string' COMMENT '配置值类型',
  `description` varchar(255) 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=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统配置表（纵表设计）';

-- 为 api_keys 表添加 updated_at 字段
ALTER TABLE `api_keys` ADD COLUMN `updated_at` datetime DEFAULT NULL COMMENT '更新时间';

-- 创建 sessions 表
CREATE TABLE IF NOT EXISTS `sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `session_id` varchar(64) NOT NULL COMMENT '会话ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `machine_code` varchar(100) DEFAULT NULL COMMENT '机器码',
  `ip_address` varchar(45) DEFAULT NULL COMMENT 'IP地址',
  `user_agent` text 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户会话表';

-- 插入默认配置数据（纵表结构）
INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (1, 'heartbeat_interval_seconds', '30', 'int', '心跳间隔秒数（默认30秒）', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (2, 'key_overlap_minutes', '30', 'int', '新旧密钥共存分钟数', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (3, 'key_expire_minutes', '60', 'int', '密钥过期分钟数（从创建时间算起）', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (4, 'max_heartbeat_failures', '10', 'int', '最大心跳失败次数，超过后暂停检测', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (5, 'max_network_failures', '15', 'int', '网络异常最大失败次数，超过后登出', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (6, 'prevent_duplicate_login', '1', 'bool', '是否防止重复登录：0-否，1-是', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (7, 'timestamp_tolerance_seconds', '30', 'int', '时间戳容忍秒数（前后5分钟）', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-07 23:47:41');
INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (8, 'session_extend_enabled', '1', 'bool', '是否启用心跳延长session：0-否，1-是', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (9, 'session_extend_threshold_hours', '4', 'int', 'session延长阈值（小时），距离过期小于此值时延长', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (10, 'session_extend_hours', '2', 'int', '每次心跳延长的小时数', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (11, 'api_timeout_seconds', '30', 'int', 'API请求超时秒数', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (12, 'client_heartbeat_interval_seconds', '30', 'int', '客户端心跳间隔秒数', 1, 1, 0, '2026-01-06 17:21:40', '2026-01-06 17:21:40');
INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (13, 'sweep_batch_size', '10', 'int', '扫货批量大小', 1, 1, 0, '2026-01-06 20:09:54', '2026-01-06 20:09:54');
INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (14, 'sweep_purchase_interval', '30', 'int', '扫货购买间隔秒数', 1, 1, 0, '2026-01-06 20:09:54', '2026-01-06 20:09:54');
INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (15, 'sweep_max_purchases', '100', 'int', '扫货最大购买数量', 1, 1, 0, '2026-01-06 20:09:54', '2026-01-06 20:09:54');
INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (16, 'page_size', '10', 'int', '分页每页显示数量', 1, 1, 0, '2026-01-06 20:09:54', '2026-01-06 20:09:54');
INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (17, 'steam_auth_countdown_seconds', '30', 'int', 'Steam验证倒计时秒数', 1, 1, 0, '2026-01-06 20:11:16', '2026-01-06 20:11:16');
INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (19, 'init_key_expire_days', '1', 'int', '初始化密钥过期天数（应用启动时创建的密钥）', 1, 1, 0, '2026-01-08 09:52:03', '2026-01-08 09:52:31');
INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (20, 'key_rotation_check_interval_minutes', '5', 'int', '密钥轮换检查间隔分钟数', 1, 1, 0, '2026-01-08 09:57:32', '2026-01-08 09:57:48');
INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (21, 'session_cleanup_interval_hours', '2', 'int', 'Session清理间隔小时数', 1, 1, 0, '2026-01-08 09:58:57', '2026-01-08 09:59:22');
INSERT INTO `buff`.`system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `is_system`, `is_editable`, `is_deleted`, `created_at`, `updated_at`) VALUES (22, 'heartbeat_cleanup_interval_hours', '1', 'int', '心跳缓存清理间隔小时数', 1, 1, 0, '2026-01-08 09:58:57', '2026-01-08 09:59:22');