90 lines
5.8 KiB
SQL
90 lines
5.8 KiB
SQL
CREATE TABLE IF NOT EXISTS `wheel_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_wheel_config_sys_origin` (`sys_origin`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='常驻活动转盘配置';
|
||
|
||
CREATE TABLE IF NOT EXISTS `wheel_pool_config` (
|
||
`id` bigint NOT NULL COMMENT '主键ID',
|
||
`config_id` bigint NOT NULL COMMENT '主配置ID',
|
||
`sys_origin` varchar(32) NOT NULL COMMENT '来源系统',
|
||
`category` varchar(32) NOT NULL COMMENT '转盘分类 CLASSIC/LUXURY/ADVANCED',
|
||
`enabled` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否启用',
|
||
`price_one_gold` bigint NOT NULL DEFAULT '0' COMMENT '抽1次价格，金币',
|
||
`price_ten_gold` bigint NOT NULL DEFAULT '0' COMMENT '抽10次价格，金币',
|
||
`price_fifty_gold` bigint NOT NULL DEFAULT '0' COMMENT '抽50次价格，金币',
|
||
`sort` int NOT NULL DEFAULT '0' COMMENT '排序',
|
||
`create_time` datetime(3) DEFAULT NULL COMMENT '创建时间',
|
||
`update_time` datetime(3) DEFAULT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_wheel_pool_config` (`config_id`, `category`),
|
||
KEY `idx_wheel_pool_sys_origin` (`sys_origin`, `category`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='常驻活动转盘分类配置';
|
||
|
||
CREATE TABLE IF NOT EXISTS `wheel_reward_config` (
|
||
`id` bigint NOT NULL COMMENT '主键ID',
|
||
`config_id` bigint NOT NULL COMMENT '主配置ID',
|
||
`sys_origin` varchar(32) NOT NULL COMMENT '来源系统',
|
||
`category` varchar(32) NOT NULL COMMENT '转盘分类 CLASSIC/LUXURY/ADVANCED',
|
||
`reward_type` varchar(32) NOT NULL COMMENT '奖励类型 RESOURCE/GOLD',
|
||
`resource_id` bigint DEFAULT NULL COMMENT '资源ID',
|
||
`resource_type` varchar(64) NOT NULL DEFAULT '' COMMENT '资源类型',
|
||
`resource_name` varchar(255) NOT NULL DEFAULT '' COMMENT '资源名称快照',
|
||
`resource_url` varchar(512) NOT NULL DEFAULT '' COMMENT '资源动效URL快照',
|
||
`cover_url` varchar(512) NOT NULL DEFAULT '' COMMENT '封面图',
|
||
`duration_days` int NOT NULL DEFAULT '0' COMMENT '资源有效天数',
|
||
`display_gold_amount` bigint NOT NULL DEFAULT '0' COMMENT '资源奖励前端展示金币价格',
|
||
`gold_amount` bigint NOT NULL DEFAULT '0' COMMENT '金币数量',
|
||
`probability` int NOT NULL DEFAULT '0' COMMENT '中奖概率，百万分比，100%=1000000',
|
||
`total_limit` bigint NOT NULL DEFAULT '0' COMMENT '奖品总发放上限，0表示不限',
|
||
`user_limit` bigint NOT NULL DEFAULT '0' COMMENT '单用户发放上限，0表示不限',
|
||
`issued_count` bigint NOT NULL DEFAULT '0' COMMENT '已占用发放数量，PENDING/SUCCESS/FAILED发奖占用',
|
||
`sort` int NOT NULL DEFAULT '0' 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`),
|
||
KEY `idx_wheel_reward_config` (`config_id`, `category`, `sort`),
|
||
KEY `idx_wheel_reward_enabled` (`sys_origin`, `category`, `enabled`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='常驻活动转盘奖励配置';
|
||
|
||
CREATE TABLE IF NOT EXISTS `wheel_draw_record` (
|
||
`id` bigint NOT NULL COMMENT '主键ID',
|
||
`draw_no` varchar(64) NOT NULL COMMENT '抽奖单号',
|
||
`event_id` varchar(128) NOT NULL COMMENT '奖励发放幂等事件ID',
|
||
`sys_origin` varchar(32) NOT NULL COMMENT '来源系统',
|
||
`category` varchar(32) NOT NULL COMMENT '转盘分类 CLASSIC/LUXURY/ADVANCED',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`draw_times` int NOT NULL DEFAULT '1' COMMENT '本次抽奖次数',
|
||
`paid_gold` bigint NOT NULL DEFAULT '0' COMMENT '本次消耗金币',
|
||
`reward_config_id` bigint NOT NULL DEFAULT '0' COMMENT '命中奖项配置ID',
|
||
`reward_type` varchar(32) NOT NULL COMMENT '奖励类型 RESOURCE/GOLD',
|
||
`resource_id` bigint DEFAULT NULL COMMENT '资源ID快照',
|
||
`resource_type` varchar(64) NOT NULL DEFAULT '' COMMENT '资源类型快照',
|
||
`resource_name` varchar(255) NOT NULL DEFAULT '' COMMENT '资源名称快照',
|
||
`resource_url` varchar(512) NOT NULL DEFAULT '' COMMENT '资源动效URL快照',
|
||
`cover_url` varchar(512) NOT NULL DEFAULT '' COMMENT '封面图快照',
|
||
`duration_days` int NOT NULL DEFAULT '0' COMMENT '资源有效天数快照',
|
||
`display_gold_amount` bigint NOT NULL DEFAULT '0' COMMENT '资源奖励前端展示金币价格快照',
|
||
`gold_amount` bigint NOT NULL DEFAULT '0' COMMENT '金币数量快照',
|
||
`probability` int NOT NULL DEFAULT '0' COMMENT '概率快照，百万分比，100%=1000000',
|
||
`status` varchar(32) NOT NULL DEFAULT 'PENDING' COMMENT '状态 PENDING/GRANTING/SUCCESS/FAILED/PAY_FAILED',
|
||
`retry_count` int NOT NULL DEFAULT '0' COMMENT '发奖尝试次数',
|
||
`error_message` varchar(1024) NOT NULL DEFAULT '' COMMENT '失败原因',
|
||
`grant_time` 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_wheel_draw_record_event` (`event_id`),
|
||
KEY `idx_wheel_draw_record_draw_no` (`draw_no`),
|
||
KEY `idx_wheel_draw_record_category` (`category`),
|
||
KEY `idx_wheel_draw_reward_user` (`reward_config_id`, `user_id`, `status`),
|
||
KEY `idx_wheel_draw_record_user_time` (`sys_origin`, `user_id`, `create_time`),
|
||
KEY `idx_wheel_draw_record_status` (`status`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='常驻活动转盘抽奖记录';
|