li
4a94fe36cf
- 拆分 HomeController → TransactionController, ReportWebController, FollowPlanController - 新增 Service 层: TransactionService, ReportService, FollowPlanService - pk10.php JS 抽离为 4 个独立文件 (sound/race/bet/poll) - 前台新增报表查询页面 (/report) + 跟单计划页面 (/follow-plan) - 后台新增跟单计划管理 + 用户输赢明细统计 - 封盘状态显示倒计时 (x:xx) - 音效仅在开奖弹窗打开时播放 - 路由按模块分组整理 - autoload 支持 App\Services 命名空间
227 lines
11 KiB
SQL
227 lines
11 KiB
SQL
-- =========================================
|
|
-- TG Bot Integration - Database Migration v1
|
|
-- 目标：为 Telegram 机器人接入提供独立业务层，不直接侵入核心下注/账务表
|
|
-- 兼容：MySQL 5.7+
|
|
-- =========================================
|
|
|
|
START TRANSACTION;
|
|
|
|
-- 1) 机器人实例表
|
|
CREATE TABLE IF NOT EXISTS `bot_instances` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`name` varchar(64) NOT NULL COMMENT '机器人名称',
|
|
`bot_token` varchar(255) NOT NULL COMMENT 'Telegram Bot Token',
|
|
`bot_username` varchar(64) DEFAULT NULL COMMENT '机器人用户名',
|
|
`bot_key` varchar(64) NOT NULL COMMENT '公开访问标识，用于X-Bot-Key',
|
|
`bot_secret` varchar(128) NOT NULL COMMENT 'Bot API 共享密钥/HMAC secret',
|
|
`webhook_url` varchar(255) DEFAULT NULL COMMENT 'Webhook 地址（可为空）',
|
|
`run_mode` enum('polling','webhook') NOT NULL DEFAULT 'polling',
|
|
`status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1启用 0停用',
|
|
`remark` varchar(255) DEFAULT NULL,
|
|
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_bot_name` (`name`),
|
|
UNIQUE KEY `uk_bot_key` (`bot_key`),
|
|
UNIQUE KEY `uk_bot_username` (`bot_username`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='TG机器人实例';
|
|
|
|
-- 2) 下注格式规则表（每群可配置不同格式）
|
|
CREATE TABLE IF NOT EXISTS `bot_bet_format_rules` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`rule_code` varchar(64) NOT NULL COMMENT '规则编码，如 pk10_v1',
|
|
`name` varchar(64) NOT NULL COMMENT '规则名称',
|
|
`game_id` int(11) NOT NULL COMMENT '对应游戏ID',
|
|
`parser_type` varchar(32) NOT NULL DEFAULT 'regex' COMMENT 'regex/json/custom',
|
|
`rule_config` json DEFAULT NULL COMMENT '解析规则JSON',
|
|
`example_text` text COMMENT '示例文本',
|
|
`status` tinyint(1) NOT NULL DEFAULT 1,
|
|
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_rule_code` (`rule_code`),
|
|
KEY `idx_game_id` (`game_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='TG下注格式规则';
|
|
|
|
-- 3) 群配置表
|
|
CREATE TABLE IF NOT EXISTS `bot_groups` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`bot_id` int(11) NOT NULL COMMENT '关联 bot_instances.id',
|
|
`tg_group_id` varchar(32) NOT NULL COMMENT 'Telegram群ID，保留-100前缀',
|
|
`group_name` varchar(128) NOT NULL COMMENT '群名称快照',
|
|
`group_type` varchar(16) NOT NULL DEFAULT 'group' COMMENT 'group/supergroup/channel',
|
|
`game_id` int(11) NOT NULL COMMENT '群默认游戏',
|
|
`bet_format_rule_id` int(11) DEFAULT NULL COMMENT '关联 bot_bet_format_rules.id',
|
|
`remind_bet_success` tinyint(1) NOT NULL DEFAULT 1 COMMENT '下注成功提醒',
|
|
`remind_draw_result` tinyint(1) NOT NULL DEFAULT 1 COMMENT '开奖提醒',
|
|
`remind_close_countdown` tinyint(1) NOT NULL DEFAULT 1 COMMENT '封盘倒计时提醒',
|
|
`countdown_config` json DEFAULT NULL COMMENT '如 [60,30,10]',
|
|
`animation_enabled` tinyint(1) NOT NULL DEFAULT 0 COMMENT '开奖动画/截图开关',
|
|
`bet_enabled` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否允许群内下注同步',
|
|
`status` tinyint(1) NOT NULL DEFAULT 1,
|
|
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_tg_group_id` (`tg_group_id`),
|
|
KEY `idx_bot_id` (`bot_id`),
|
|
KEY `idx_game_id` (`game_id`),
|
|
KEY `idx_rule_id` (`bet_format_rule_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='TG群配置';
|
|
|
|
-- 4) 群总账号绑定表
|
|
CREATE TABLE IF NOT EXISTS `bot_group_wallets` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`group_id` int(11) NOT NULL COMMENT '关联 bot_groups.id',
|
|
`platform_user_id` int(11) NOT NULL COMMENT '网站总账号 users.id',
|
|
`platform_username_snapshot` varchar(64) DEFAULT NULL COMMENT '用户名快照',
|
|
`wallet_mode` enum('master_pool','per_member') NOT NULL DEFAULT 'master_pool' COMMENT '首发建议 master_pool',
|
|
`status` tinyint(1) NOT NULL DEFAULT 1,
|
|
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_group_wallet` (`group_id`),
|
|
KEY `idx_platform_user_id` (`platform_user_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='群绑定的网站总账号';
|
|
|
|
-- 5) 群成员映射表
|
|
CREATE TABLE IF NOT EXISTS `bot_group_members` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`group_id` int(11) NOT NULL,
|
|
`tg_user_id` varchar(32) NOT NULL,
|
|
`tg_username` varchar(64) DEFAULT NULL,
|
|
`tg_nickname` varchar(128) DEFAULT NULL,
|
|
`platform_user_id` int(11) DEFAULT NULL COMMENT '如后续启用 per_member 模式使用',
|
|
`role` enum('member','admin','shill') NOT NULL DEFAULT 'member',
|
|
`bet_enabled` tinyint(1) NOT NULL DEFAULT 1,
|
|
`last_seen_at` datetime DEFAULT NULL,
|
|
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_group_tg_user` (`group_id`,`tg_user_id`),
|
|
KEY `idx_platform_user_id` (`platform_user_id`),
|
|
KEY `idx_role` (`role`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='群成员映射';
|
|
|
|
-- 6) 托号表（冗余显式表，便于运营查询与统计排除）
|
|
CREATE TABLE IF NOT EXISTS `bot_shills` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`group_id` int(11) NOT NULL,
|
|
`tg_user_id` varchar(32) NOT NULL,
|
|
`note` varchar(255) DEFAULT NULL,
|
|
`enabled` tinyint(1) NOT NULL DEFAULT 1,
|
|
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_group_shill` (`group_id`,`tg_user_id`),
|
|
KEY `idx_enabled` (`enabled`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='托号配置';
|
|
|
|
-- 7) 消息下注订单表
|
|
CREATE TABLE IF NOT EXISTS `bot_bet_orders` (
|
|
`id` bigint(20) NOT NULL AUTO_INCREMENT,
|
|
`group_id` int(11) NOT NULL,
|
|
`tg_chat_id` varchar(32) NOT NULL,
|
|
`tg_message_id` bigint(20) NOT NULL,
|
|
`tg_user_id` varchar(32) NOT NULL,
|
|
`tg_username` varchar(64) DEFAULT NULL,
|
|
`platform_user_id` int(11) NOT NULL COMMENT '首发取群总账号',
|
|
`game_id` int(11) NOT NULL,
|
|
`period_number` varchar(50) NOT NULL,
|
|
`raw_text` text NOT NULL COMMENT '原始Telegram消息',
|
|
`parsed_payload_json` json DEFAULT NULL COMMENT '解析后的标准bets结构',
|
|
`bet_amount_total` decimal(15,2) NOT NULL DEFAULT '0.00',
|
|
`accepted_bet_count` int(11) NOT NULL DEFAULT 0,
|
|
`platform_order_ref` varchar(64) DEFAULT NULL COMMENT '平台侧订单引用，可为空后续回填',
|
|
`idempotency_key` varchar(128) NOT NULL COMMENT '如 tg:-100xxx:12345',
|
|
`sync_status` enum('pending','success','failed','duplicate') NOT NULL DEFAULT 'pending',
|
|
`sync_error` varchar(255) DEFAULT NULL,
|
|
`is_shill` tinyint(1) NOT NULL DEFAULT 0 COMMENT '下注者是否托号快照',
|
|
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_idempotency_key` (`idempotency_key`),
|
|
UNIQUE KEY `uk_chat_message` (`tg_chat_id`,`tg_message_id`),
|
|
KEY `idx_group_id` (`group_id`),
|
|
KEY `idx_platform_user_id` (`platform_user_id`),
|
|
KEY `idx_period_number` (`period_number`),
|
|
KEY `idx_sync_status` (`sync_status`),
|
|
KEY `idx_created_at` (`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='TG消息下注订单';
|
|
|
|
-- 8) Bot API 请求日志（审计+重放排查）
|
|
CREATE TABLE IF NOT EXISTS `bot_api_request_logs` (
|
|
`id` bigint(20) NOT NULL AUTO_INCREMENT,
|
|
`bot_id` int(11) DEFAULT NULL,
|
|
`group_id` int(11) DEFAULT NULL,
|
|
`request_uri` varchar(255) NOT NULL,
|
|
`http_method` varchar(10) NOT NULL,
|
|
`idempotency_key` varchar(128) DEFAULT NULL,
|
|
`request_body` mediumtext,
|
|
`response_body` mediumtext,
|
|
`response_code` int(11) DEFAULT NULL,
|
|
`client_ip` varchar(64) DEFAULT NULL,
|
|
`signature_ok` tinyint(1) NOT NULL DEFAULT 0,
|
|
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_bot_id` (`bot_id`),
|
|
KEY `idx_group_id` (`group_id`),
|
|
KEY `idx_idempotency_key` (`idempotency_key`),
|
|
KEY `idx_created_at` (`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Bot API请求日志';
|
|
|
|
-- 9) 推送日志（开奖/封盘/下注成功/上下分）
|
|
CREATE TABLE IF NOT EXISTS `bot_push_logs` (
|
|
`id` bigint(20) NOT NULL AUTO_INCREMENT,
|
|
`group_id` int(11) NOT NULL,
|
|
`period_number` varchar(50) DEFAULT NULL,
|
|
`push_type` enum('countdown','bet_success','draw','credit','debit','system') NOT NULL,
|
|
`payload_json` json DEFAULT NULL,
|
|
`tg_message_id` bigint(20) DEFAULT NULL,
|
|
`status` enum('pending','success','failed','skipped') NOT NULL DEFAULT 'pending',
|
|
`error_message` varchar(255) DEFAULT NULL,
|
|
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_group_id` (`group_id`),
|
|
KEY `idx_period_number` (`period_number`),
|
|
KEY `idx_push_type` (`push_type`),
|
|
KEY `idx_status` (`status`),
|
|
KEY `idx_created_at` (`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Bot推送日志';
|
|
|
|
-- 10) 群上下分申请/记录表
|
|
CREATE TABLE IF NOT EXISTS `bot_fund_requests` (
|
|
`id` bigint(20) NOT NULL AUTO_INCREMENT,
|
|
`group_id` int(11) NOT NULL,
|
|
`tg_user_id` varchar(32) DEFAULT NULL,
|
|
`platform_user_id` int(11) NOT NULL,
|
|
`request_type` enum('credit','debit') NOT NULL,
|
|
`amount` decimal(15,2) NOT NULL,
|
|
`reason` varchar(255) DEFAULT NULL,
|
|
`idempotency_key` varchar(128) NOT NULL,
|
|
`transaction_id` int(11) DEFAULT NULL COMMENT '关联 transactions.id',
|
|
`status` enum('pending','approved','rejected','failed') NOT NULL DEFAULT 'pending',
|
|
`operator_id` int(11) DEFAULT NULL COMMENT '后台审核人/系统操作者',
|
|
`operator_type` varchar(16) DEFAULT NULL COMMENT 'admin/employee/system',
|
|
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_fund_idempotency_key` (`idempotency_key`),
|
|
KEY `idx_group_id` (`group_id`),
|
|
KEY `idx_platform_user_id` (`platform_user_id`),
|
|
KEY `idx_transaction_id` (`transaction_id`),
|
|
KEY `idx_status` (`status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Bot上下分记录';
|
|
|
|
-- 11) 为 transactions 增加 Bot 来源标识（不破坏现有逻辑）
|
|
ALTER TABLE `transactions`
|
|
ADD COLUMN `source` varchar(20) DEFAULT NULL COMMENT '来源：web/admin/employee/bot' AFTER `operator_type`,
|
|
ADD COLUMN `source_ref` varchar(64) DEFAULT NULL COMMENT '来源引用：bot订单/请求号' AFTER `source`;
|
|
|
|
-- 12) 为 bets 增加 Bot 来源引用（便于订单追踪）
|
|
ALTER TABLE `bets`
|
|
ADD COLUMN `source` varchar(20) DEFAULT NULL COMMENT '来源：web/bot' AFTER `agent_id`,
|
|
ADD COLUMN `source_ref` varchar(64) DEFAULT NULL COMMENT '来源引用：idempotency_key/order_ref' AFTER `source`;
|
|
|
|
COMMIT;
|