SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE DATABASE IF NOT EXISTS hyapp_robot
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

USE hyapp_robot;

CREATE TABLE IF NOT EXISTS robot_game_robots (
  app_code VARCHAR(32) NOT NULL COMMENT '应用编码，用于多租户隔离',
  game_id VARCHAR(96) NOT NULL COMMENT '兼容后台请求的游戏 ID；账号池按 app_code + user_id 聚合使用',
  user_id BIGINT NOT NULL COMMENT '真实 App 机器人用户 ID',
  status VARCHAR(32) NOT NULL DEFAULT 'active' COMMENT '机器人状态 active/disabled',
  created_by_admin_id BIGINT NOT NULL DEFAULT 0 COMMENT '创建管理员 ID',
  created_at_ms BIGINT NOT NULL COMMENT '创建时间，UTC epoch ms',
  updated_at_ms BIGINT NOT NULL COMMENT '更新时间，UTC epoch ms',
  last_used_at_ms BIGINT NOT NULL DEFAULT 0 COMMENT '最近一次被匹配补位时间，UTC epoch ms',
  used_count BIGINT NOT NULL DEFAULT 0 COMMENT '累计被匹配补位次数',
  PRIMARY KEY(app_code, game_id, user_id),
  KEY idx_robot_game_status(app_code, game_id, status, updated_at_ms, user_id),
  KEY idx_robot_game_random_pool(app_code, status, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='robot-service 游戏机器人账号池';

CREATE TABLE IF NOT EXISTS robot_room_robots (
  app_code VARCHAR(32) NOT NULL COMMENT '应用编码，用于多租户隔离',
  room_scene VARCHAR(64) NOT NULL COMMENT '房间机器人场景，例如 voice_room',
  user_id BIGINT NOT NULL COMMENT '真实 App 机器人用户 ID',
  status VARCHAR(32) NOT NULL DEFAULT 'active' COMMENT '机器人状态 active/disabled',
  created_by_admin_id BIGINT NOT NULL DEFAULT 0 COMMENT '创建管理员 ID',
  created_at_ms BIGINT NOT NULL COMMENT '创建时间，UTC epoch ms',
  updated_at_ms BIGINT NOT NULL COMMENT '更新时间，UTC epoch ms',
  last_used_at_ms BIGINT NOT NULL DEFAULT 0 COMMENT '最近一次被挑选时间，UTC epoch ms',
  used_count BIGINT NOT NULL DEFAULT 0 COMMENT '累计被挑选次数',
  PRIMARY KEY(app_code, room_scene, user_id),
  KEY idx_robot_room_status(app_code, room_scene, status, updated_at_ms, user_id),
  KEY idx_robot_room_random_pool(app_code, status, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='robot-service 房间机器人账号池';

CREATE TABLE IF NOT EXISTS robot_service_migrations (
  migration_key VARCHAR(128) NOT NULL COMMENT '迁移幂等键',
  source_database VARCHAR(64) NOT NULL DEFAULT '' COMMENT '来源库',
  source_table VARCHAR(64) NOT NULL DEFAULT '' COMMENT '来源表',
  migrated_rows BIGINT NOT NULL DEFAULT 0 COMMENT '迁移来源聚合行数',
  status VARCHAR(32) NOT NULL COMMENT '迁移状态',
  created_at_ms BIGINT NOT NULL COMMENT '创建时间，UTC epoch ms',
  updated_at_ms BIGINT NOT NULL COMMENT '更新时间，UTC epoch ms',
  PRIMARY KEY(migration_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='robot-service 迁移幂等标记表';