CREATE TABLE IF NOT EXISTS `room_turnover_reward_config` (
  `id` bigint NOT NULL COMMENT '主键ID',
  `sys_origin` varchar(32) NOT NULL COMMENT '来源系统',
  `enabled` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否启用',
  `timezone` varchar(64) NOT NULL DEFAULT 'Asia/Riyadh' COMMENT '结算时区',
  `create_time` datetime(3) DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime(3) DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_room_turnover_reward_sys_origin` (`sys_origin`),
  KEY `idx_room_turnover_reward_enabled` (`enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='房间流水奖励主配置';

CREATE TABLE IF NOT EXISTS `room_turnover_reward_level` (
  `id` bigint NOT NULL COMMENT '主键ID',
  `config_id` bigint NOT NULL COMMENT '主配置ID',
  `level` int NOT NULL COMMENT '奖励等级',
  `turnover_threshold` bigint NOT NULL COMMENT '流水门槛',
  `reward_gold` bigint NOT NULL COMMENT '奖励房主金币',
  `enabled` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否启用',
  `create_time` datetime(3) DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime(3) DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_room_turnover_reward_level` (`config_id`, `level`),
  UNIQUE KEY `uk_room_turnover_reward_threshold` (`config_id`, `turnover_threshold`),
  KEY `idx_room_turnover_reward_level_config` (`config_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='房间流水奖励等级配置';

CREATE TABLE IF NOT EXISTS `room_turnover_reward_record` (
  `id` bigint NOT NULL COMMENT '主键ID',
  `config_id` bigint NOT NULL COMMENT '主配置ID',
  `sys_origin` varchar(32) NOT NULL COMMENT '来源系统',
  `cycle_key` varchar(32) NOT NULL COMMENT '周期标识，取周一 yyyyMMdd',
  `period_start_at` datetime(3) NOT NULL COMMENT '周期开始时间',
  `period_end_at` datetime(3) NOT NULL COMMENT '周期结束时间',
  `room_id` bigint NOT NULL COMMENT '房间ID',
  `room_account` varchar(64) DEFAULT NULL COMMENT '房间账号快照',
  `room_name` varchar(255) DEFAULT NULL COMMENT '房间名称快照',
  `room_cover` varchar(1024) DEFAULT NULL COMMENT '房间封面快照',
  `owner_user_id` bigint NOT NULL COMMENT '房主用户ID',
  `country_code` varchar(32) DEFAULT NULL COMMENT '国家编码',
  `country_name` varchar(128) DEFAULT NULL COMMENT '国家名称',
  `turnover_amount` bigint NOT NULL DEFAULT '0' COMMENT '本周期房间流水',
  `level` int NOT NULL COMMENT '命中的最高奖励等级',
  `turnover_threshold` bigint NOT NULL COMMENT '命中的流水门槛',
  `reward_gold` bigint NOT NULL DEFAULT '0' COMMENT '奖励金币',
  `track_id` varchar(128) NOT NULL COMMENT '钱包发奖幂等ID',
  `status` varchar(32) NOT NULL DEFAULT 'PENDING' COMMENT '发奖状态',
  `retry_count` int NOT NULL DEFAULT '0' COMMENT '重试次数',
  `last_error` varchar(1024) DEFAULT NULL COMMENT '最近失败原因',
  `sent_at` datetime(3) DEFAULT NULL COMMENT '发放时间',
  `create_time` datetime(3) DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime(3) DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_room_turnover_reward_record_room` (`sys_origin`, `cycle_key`, `room_id`),
  UNIQUE KEY `uk_room_turnover_reward_track` (`track_id`),
  KEY `idx_room_turnover_reward_record_cycle` (`sys_origin`, `cycle_key`),
  KEY `idx_room_turnover_reward_record_config` (`config_id`),
  KEY `idx_room_turnover_reward_record_room` (`room_id`),
  KEY `idx_room_turnover_reward_record_owner` (`owner_user_id`),
  KEY `idx_room_turnover_reward_record_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='房间流水奖励发放记录';