62 lines
3.7 KiB
SQL
62 lines
3.7 KiB
SQL
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='房间流水奖励发放记录';
|