

-- 2. Bot Chats 表 - 存储 Bot 加入的群组信息
CREATE TABLE IF NOT EXISTS `cs_bot_chats` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `bot_id` INT(11) NOT NULL COMMENT 'Bot ID',
  `chat_id` VARCHAR(100) NOT NULL COMMENT 'Telegram Chat ID',
  `chat_type` VARCHAR(50) DEFAULT 'group' COMMENT '群组类型: group, supergroup, channel',
  `chat_title` VARCHAR(500) DEFAULT NULL COMMENT '群组标题',
  `chat_username` VARCHAR(255) DEFAULT NULL COMMENT '群组用户名',
  `chat_first_name` VARCHAR(255) DEFAULT NULL COMMENT '用户名字',
  `chat_last_name` VARCHAR(255) DEFAULT NULL COMMENT '用户姓氏',
  `invite_link` VARCHAR(500) DEFAULT NULL COMMENT '邀请链接',
  `is_forum` TINYINT(1) DEFAULT 0 COMMENT '是否为论坛群组',
  `last_activity_at` DATETIME DEFAULT NULL COMMENT '最后活动时间',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bot_chat` (`bot_id`, `chat_id`),
  KEY `bot_id` (`bot_id`),
  KEY `chat_id` (`chat_id`),
  FOREIGN KEY (`bot_id`) REFERENCES `cs_bots` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Bot 群组信息表';

-- 3. Messages 表 - 存储发送的消息记录
CREATE TABLE IF NOT EXISTS `cs_messages` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `bot_id` INT(11) NOT NULL COMMENT 'Bot ID',
  `message_text` TEXT NOT NULL COMMENT '消息内容',
  `parse_mode` VARCHAR(20) DEFAULT 'HTML' COMMENT '解析模式: HTML, Markdown, MarkdownV2',
  `send_status` VARCHAR(50) DEFAULT 'pending' COMMENT '发送状态: pending, sending, success, failed',
  `total_chats` INT(11) DEFAULT 0 COMMENT '目标群组总数',
  `success_count` INT(11) DEFAULT 0 COMMENT '成功发送数',
  `failed_count` INT(11) DEFAULT 0 COMMENT '失败发送数',
  `created_by` INT(11) DEFAULT NULL COMMENT '创建者用户ID',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `bot_id` (`bot_id`),
  KEY `send_status` (`send_status`),
  FOREIGN KEY (`bot_id`) REFERENCES `cs_bots` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='消息记录表';

-- 4. Message Logs 表 - 存储每条消息的发送详情
CREATE TABLE IF NOT EXISTS `cs_message_logs` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `message_id` INT(11) NOT NULL COMMENT '消息ID',
  `bot_id` INT(11) NOT NULL COMMENT 'Bot ID',
  `chat_id` VARCHAR(100) NOT NULL COMMENT 'Chat ID',
  `topic_id` INT(11) DEFAULT NULL COMMENT 'Topic ID (论坛主题)',
  `telegram_message_id` INT(11) DEFAULT NULL COMMENT 'Telegram 返回的消息ID',
  `status` VARCHAR(50) DEFAULT 'pending' COMMENT '状态: pending, success, failed',
  `error_message` TEXT DEFAULT NULL COMMENT '错误信息',
  `sent_at` DATETIME DEFAULT NULL COMMENT '发送时间',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `message_id` (`message_id`),
  KEY `bot_id` (`bot_id`),
  KEY `chat_id` (`chat_id`),
  KEY `status` (`status`),
  FOREIGN KEY (`message_id`) REFERENCES `cs_messages` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`bot_id`) REFERENCES `cs_bots` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='消息发送日志表';

