118 lines
6.8 KiB
SQL
118 lines
6.8 KiB
SQL
CREATE TABLE IF NOT EXISTS `task_center_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_task_center_config_sys_origin` (`sys_origin`),
|
||
KEY `idx_task_center_config_enabled` (`enabled`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='任务中心系统配置';
|
||
|
||
CREATE TABLE IF NOT EXISTS `task_center_task_config` (
|
||
`id` bigint NOT NULL COMMENT '主键ID',
|
||
`config_id` bigint NOT NULL COMMENT '任务中心配置ID',
|
||
`sys_origin` varchar(32) NOT NULL COMMENT '来源系统',
|
||
`task_code` varchar(64) NOT NULL COMMENT '任务编码',
|
||
`task_category` varchar(32) NOT NULL COMMENT '任务分类：DAILY/NEWBIE',
|
||
`enabled` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否启用',
|
||
`task_name` varchar(255) NOT NULL DEFAULT '' COMMENT '任务名称',
|
||
`task_desc` varchar(255) NOT NULL DEFAULT '' COMMENT '任务说明',
|
||
`condition_type` varchar(64) NOT NULL DEFAULT '' COMMENT '完成条件类型',
|
||
`target_value` bigint NOT NULL DEFAULT '0' COMMENT '目标值',
|
||
`target_unit` varchar(32) NOT NULL DEFAULT '' COMMENT '目标单位',
|
||
`reward_gold` bigint NOT NULL DEFAULT '0' COMMENT '奖励金币',
|
||
`jump_type` varchar(64) NOT NULL DEFAULT 'NONE' COMMENT '跳转类型',
|
||
`jump_page` varchar(512) NOT NULL DEFAULT '' COMMENT '跳转参数',
|
||
`task_icon` varchar(512) NOT NULL DEFAULT '' COMMENT '任务图标',
|
||
`cover` varchar(512) NOT NULL DEFAULT '' COMMENT '任务封面',
|
||
`sort_order` 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_task_center_task_code` (`sys_origin`, `task_code`),
|
||
KEY `idx_task_center_task_config_id` (`config_id`),
|
||
KEY `idx_task_center_task_sys_category` (`sys_origin`, `task_category`),
|
||
KEY `idx_task_center_task_enabled` (`enabled`),
|
||
KEY `idx_task_center_task_condition` (`condition_type`),
|
||
KEY `idx_task_center_task_sort` (`sort_order`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='任务中心任务配置';
|
||
|
||
CREATE TABLE IF NOT EXISTS `task_center_user_progress` (
|
||
`id` bigint NOT NULL COMMENT '主键ID',
|
||
`sys_origin` varchar(32) NOT NULL COMMENT '来源系统',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`task_id` bigint NOT NULL COMMENT '任务ID',
|
||
`task_code` varchar(64) NOT NULL COMMENT '任务编码',
|
||
`task_category` varchar(32) NOT NULL COMMENT '任务分类',
|
||
`cycle_key` varchar(32) NOT NULL COMMENT '任务周期',
|
||
`condition_type` varchar(64) NOT NULL DEFAULT '' COMMENT '完成条件类型',
|
||
`completed_value` bigint NOT NULL DEFAULT '0' COMMENT '已完成值',
|
||
`target_value` bigint NOT NULL DEFAULT '0' COMMENT '目标值',
|
||
`status` varchar(32) NOT NULL DEFAULT 'IN_PROGRESS' COMMENT '任务状态',
|
||
`reward_collected` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已领取',
|
||
`claimed_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_task_center_user_progress` (`sys_origin`, `user_id`, `task_id`, `cycle_key`),
|
||
KEY `idx_task_center_progress_user` (`sys_origin`, `user_id`),
|
||
KEY `idx_task_center_progress_task` (`task_id`),
|
||
KEY `idx_task_center_progress_task_code` (`task_code`),
|
||
KEY `idx_task_center_progress_cycle` (`cycle_key`),
|
||
KEY `idx_task_center_progress_status` (`status`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='任务中心用户进度';
|
||
|
||
CREATE TABLE IF NOT EXISTS `task_center_event` (
|
||
`id` bigint NOT NULL COMMENT '主键ID',
|
||
`sys_origin` varchar(32) NOT NULL COMMENT '来源系统',
|
||
`event_id` varchar(128) NOT NULL COMMENT '业务事件ID',
|
||
`event_type` varchar(64) NOT NULL COMMENT '事件类型',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`task_id` bigint NOT NULL COMMENT '命中任务ID',
|
||
`task_code` varchar(64) NOT NULL COMMENT '命中任务编码',
|
||
`task_category` varchar(32) NOT NULL COMMENT '任务分类',
|
||
`cycle_key` varchar(32) NOT NULL COMMENT '任务周期',
|
||
`delta_value` bigint NOT NULL DEFAULT '0' COMMENT '本次增量',
|
||
`completed_value` bigint NOT NULL DEFAULT '0' COMMENT '事件后完成值',
|
||
`raw_json` text COMMENT '原始事件内容',
|
||
`occurred_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_task_center_event_task` (`event_id`, `task_id`),
|
||
KEY `idx_task_center_event_sys` (`sys_origin`),
|
||
KEY `idx_task_center_event_id` (`event_id`),
|
||
KEY `idx_task_center_event_type` (`event_type`),
|
||
KEY `idx_task_center_event_user` (`user_id`),
|
||
KEY `idx_task_center_event_task_code` (`task_code`),
|
||
KEY `idx_task_center_event_occurred` (`occurred_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='任务中心事件流水';
|
||
|
||
CREATE TABLE IF NOT EXISTS `task_center_claim_record` (
|
||
`id` bigint NOT NULL COMMENT '主键ID',
|
||
`sys_origin` varchar(32) NOT NULL COMMENT '来源系统',
|
||
`user_id` bigint NOT NULL COMMENT '用户ID',
|
||
`task_id` bigint NOT NULL COMMENT '任务ID',
|
||
`task_code` varchar(64) NOT NULL COMMENT '任务编码',
|
||
`task_name` varchar(255) NOT NULL DEFAULT '' COMMENT '任务名称快照',
|
||
`task_category` varchar(32) NOT NULL COMMENT '任务分类',
|
||
`cycle_key` varchar(32) NOT NULL COMMENT '任务周期',
|
||
`reward_gold` bigint NOT NULL DEFAULT '0' COMMENT '奖励金币',
|
||
`wallet_event_id` varchar(160) NOT NULL COMMENT '钱包事件ID',
|
||
`status` varchar(32) NOT NULL COMMENT '领取状态',
|
||
`failure_reason` varchar(512) NOT NULL DEFAULT '' COMMENT '失败原因',
|
||
`claimed_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_task_center_claim_wallet_event` (`wallet_event_id`),
|
||
KEY `idx_task_center_claim_sys` (`sys_origin`),
|
||
KEY `idx_task_center_claim_user` (`user_id`),
|
||
KEY `idx_task_center_claim_task` (`task_id`),
|
||
KEY `idx_task_center_claim_task_code` (`task_code`),
|
||
KEY `idx_task_center_claim_cycle` (`cycle_key`),
|
||
KEY `idx_task_center_claim_status` (`status`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='任务中心领取记录';
|