-- =========================================
-- 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;