47 lines
2.8 KiB
SQL
47 lines
2.8 KiB
SQL
CREATE TABLE IF NOT EXISTS `sign_in_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_sign_in_reward_sys_origin` (`sys_origin`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='签到奖励主配置';
|
|
|
|
CREATE TABLE IF NOT EXISTS `sign_in_reward_day_config` (
|
|
`id` bigint NOT NULL COMMENT '主键ID',
|
|
`config_id` bigint NOT NULL COMMENT '主配置ID',
|
|
`day_index` int NOT NULL COMMENT '循环日序号 1-7',
|
|
`gold_amount` bigint NOT NULL DEFAULT '0' COMMENT '金币奖励数量',
|
|
`reward_group_id` bigint DEFAULT NULL COMMENT '奖励组ID',
|
|
`reward_group_name` varchar(255) NOT NULL DEFAULT '' COMMENT '奖励组名称快照',
|
|
`create_time` datetime(3) DEFAULT NULL COMMENT '创建时间',
|
|
`update_time` datetime(3) DEFAULT NULL COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_sign_in_reward_day_config` (`config_id`, `day_index`),
|
|
KEY `idx_sign_in_reward_day_config` (`config_id`, `day_index`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='签到奖励7天循环配置';
|
|
|
|
CREATE TABLE IF NOT EXISTS `sign_in_reward_log` (
|
|
`id` bigint NOT NULL COMMENT '主键ID',
|
|
`event_id` varchar(128) NOT NULL COMMENT '幂等事件ID',
|
|
`sys_origin` varchar(32) NOT NULL COMMENT '来源系统',
|
|
`user_id` bigint NOT NULL COMMENT '用户ID',
|
|
`claim_date` varchar(10) NOT NULL DEFAULT '' COMMENT '签到日期 YYYY-MM-DD',
|
|
`day_index` int NOT NULL DEFAULT '0' COMMENT '本次命中的循环日序号',
|
|
`streak_count` int NOT NULL DEFAULT '0' COMMENT '连续签到天数',
|
|
`gold_amount` bigint NOT NULL DEFAULT '0' COMMENT '金币奖励数量',
|
|
`reward_group_id` bigint DEFAULT NULL COMMENT '奖励组ID',
|
|
`reward_group_name` varchar(255) NOT NULL DEFAULT '' COMMENT '奖励组名称快照',
|
|
`status` varchar(32) NOT NULL DEFAULT 'PENDING' COMMENT '处理状态',
|
|
`error_message` varchar(255) NOT NULL DEFAULT '' COMMENT '错误信息',
|
|
`create_time` datetime(3) DEFAULT NULL COMMENT '创建时间',
|
|
`update_time` datetime(3) DEFAULT NULL COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_sign_in_reward_event` (`event_id`),
|
|
UNIQUE KEY `uk_sign_in_reward_user_day` (`sys_origin`, `user_id`, `claim_date`),
|
|
KEY `idx_sign_in_reward_user_time` (`sys_origin`, `user_id`),
|
|
KEY `idx_sign_in_reward_claim` (`sys_origin`, `claim_date`, `status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='签到奖励发放日志';
|