# 日期:2022-03-07
# 提交人:唐嘉悖
# sql解决问题: 新增集结打卡任务表
# sql语句
DROP TABLE IF EXISTS `member_clockin_mission`;
CREATE TABLE `member_clockin_mission` (
        `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID，业务ID',
        `name` varchar(500) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '集结打卡任务名称',
        `frequency` varchar(100) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '打卡频次类型(多个类型中间以","分隔): 1-每周一; 2-每周二; 3-每周三; 4-每周四; 5-每周五; 6-每周六; 7-每周日; 8-每天;',
        `clockinStartTime` varchar(50) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '打卡开始时间',
        `clockinEndTime` varchar(50) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '打卡结束时间',
        `startDate` date NOT NULL COMMENT '打卡任务生效日期',
        `endDate` date NOT NULL COMMENT '打卡任务截止日期',
        `thumbnail` varchar(2000) COLLATE utf8mb4_bin DEFAULT '' COMMENT '缩略图',
        `mediaInfo` varchar(2000) COLLATE utf8mb4_bin DEFAULT '' COMMENT '媒体素材信息',
        `remarks` varchar(2000) COLLATE utf8mb4_bin DEFAULT '' COMMENT '备注',
        `status` int(11) DEFAULT NULL COMMENT '打卡任务状态: 0-草稿; 1-待审核; 2-待发布; 3-已发布; 4-已驳回;',
        `visibility` int(11) DEFAULT NULL COMMENT '任务可见性: 0-隐藏; 1-可见;',
        `createUser` varchar(255) COLLATE utf8mb4_bin DEFAULT '' COMMENT '创建人',
        `updateUser` varchar(255) COLLATE utf8mb4_bin DEFAULT '' COMMENT '更新人',
        `createTime` datetime DEFAULT NULL COMMENT '创建时间',
        `updateTime` datetime DEFAULT NULL COMMENT '修改时间',
        `reason` varchar(2000) COLLATE utf8mb4_bin DEFAULT '' COMMENT '驳回理由',
        PRIMARY KEY (`id`) USING BTREE,
        KEY `idx_member_clockin_mission_name` (`name`) USING BTREE,
        KEY `idx_member_clockin_mission_frequency` (`frequency`) USING BTREE,
        KEY `idx_member_clockin_mission_clockinStartTime_clockinEndTime` (`clockinStartTime`, `clockinEndTime`) USING BTREE,
        KEY `idx_member_clockin_mission_startDate_endDate` (`startDate`, `endDate`) USING BTREE,
        KEY `idx_member_clockin_mission_status` (`status`) USING BTREE,
        KEY `idx_member_clockin_mission_visibility` (`visibility`) USING BTREE
) COMMENT='集结打卡任务表' ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


# 日期:2022-03-08
# 提交人:唐嘉悖
# sql解决问题: 新增集结打卡记录表
# sql语句
DROP TABLE IF EXISTS `member_clockin_log`;
CREATE TABLE `member_clockin_log` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID，业务ID',
    `missionId` bigint(20) NOT NULL COMMENT '集结打卡任务ID',
    `memberId` bigint(20) NOT NULL COMMENT '用户ID',
    `sequence` bigint(20) NOT NULL DEFAULT 0 COMMENT '打卡序列',
    `accumulate` int(11) NOT NULL DEFAULT 0 COMMENT '累计天数',
    `createUser` varchar(255) COLLATE utf8mb4_bin DEFAULT '' COMMENT '创建人',
    `createTime` datetime DEFAULT NULL COMMENT '创建时间',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `idx_member_clockin_log_missionId` (`missionId`) USING BTREE,
    KEY `idx_member_clockin_log_memberId` (`memberId`) USING BTREE,
    KEY `idx_member_clockin_log_createTime` (`createTime`) USING BTREE,
    KEY `idx_member_clockin_log_missionId_memberId` (`missionId`, `memberId`) USING BTREE,
    KEY `idx_member_clockin_log_missionId_memberId_createTime` (`missionId`, `memberId`, `createTime`) USING BTREE
) COMMENT='集结打卡记录表' ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;