PunchSettle-Server/doc/sql/punch_settle-ddl.sql

-- punch_settle.punch_in definition
CREATE DATABASE `punch_settle` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

CREATE TABLE `punch_in` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `task_name` varchar(128) NOT NULL COMMENT '任务名称',
  `reward_num` int NOT NULL COMMENT '奖励倍数',
  `weekend_double_flag` tinyint NOT NULL DEFAULT '0' COMMENT '是否启用周末双倍标志(0-不是，1-是)',
  `full_attendance_flag` tinyint NOT NULL DEFAULT '0' COMMENT '是否启用全勤奖励标志(0-不是，1-是)',
  `archive_flag` tinyint NOT NULL DEFAULT '0' COMMENT '是否归档标志(0-不是，1-是)',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='打卡任务表';


-- punch_settle.punch_in_record definition

CREATE TABLE `punch_in_record` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `punch_in_id` bigint NOT NULL COMMENT '打卡任务表主键',
  `punch_in_date` varchar(10) NOT NULL COMMENT '打卡日期',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='打卡任务记录表';

-- punch_settle.punch_in_record_settlement_rela definition

CREATE TABLE `punch_in_record_settlement_rela` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `record_id` bigint NOT NULL COMMENT '打卡任务记录表ID',
  `settlement_id` bigint NOT NULL COMMENT '打卡任务结算表ID',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='打卡任务记录与结算关联表';


-- punch_settle.punch_in_settlement definition

CREATE TABLE `punch_in_settlement` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户表主键',
  `settle_reward_num` int NOT NULL COMMENT '结算奖励数',
  `before_settle_reward_num` int NOT NULL DEFAULT '0' COMMENT '结算前用户拥有的奖励数',
  `after_settle_reward_num` int NOT NULL DEFAULT '0' COMMENT '结算后用户拥有的奖励数',
  `settlement_task_id` bigint NOT NULL COMMENT '结算任务表id',
  `settlement_time` timestamp NOT NULL COMMENT '结算时间',
  `notify_id` bigint DEFAULT NULL COMMENT '通知表ID',
  `notify_status` varchar(20) DEFAULT NULL COMMENT '结算通知状态(fail-通知失败，success-成功通知，pending-待通知)',
  `notify_time` timestamp NULL DEFAULT NULL COMMENT '结算通知时间',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='打卡任务结算表';


-- punch_settle.settlement_notify_task definition

CREATE TABLE `settlement_notify_task` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `start_time` timestamp NULL DEFAULT NULL COMMENT '任务开始时间',
  `end_time` timestamp NULL DEFAULT NULL COMMENT '任务结束时间',
  `task_status` varchar(20) NOT NULL DEFAULT 'processing' COMMENT '任务状态(success-成功，fail-失败，processing-处理中)',
  `notify_num` int NOT NULL DEFAULT '0' COMMENT '通知数量',
  `notify_success_num` int NOT NULL DEFAULT '0' COMMENT '通知成功数量',
  `notify_fail_num` int NOT NULL DEFAULT '0' COMMENT '通知失败数量',
  `error_message` text COMMENT '失败异常信息',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='通知定时任务执行记录表';


-- punch_settle.settlement_task definition

CREATE TABLE `settlement_task` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `settle_date` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '结算日期',
  `start_time` timestamp NULL DEFAULT NULL COMMENT '任务开始时间',
  `end_time` timestamp NULL DEFAULT NULL COMMENT '任务结束时间',
  `processed_num` int NOT NULL DEFAULT '0' COMMENT '待处理结算数量',
  `processed_settle_num` int NOT NULL DEFAULT '0' COMMENT '处理已结算数量',
  `processed_unsettle_num` int NOT NULL DEFAULT '0' COMMENT '处理没结算数量',
  `error_message` text COMMENT '失败异常信息',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='奖励结算定时任务执行记录表';


-- punch_settle.`user` definition

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `open_id` varchar(128) NOT NULL COMMENT '微信id',
  `nickname` varchar(100) DEFAULT NULL COMMENT '微信昵称',
  `avatar` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '微信头像url',
  `reward_num` int NOT NULL DEFAULT '0',
  `claimed_reward_num` int NOT NULL DEFAULT '0' COMMENT '已领取奖励数',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';


-- punch_settle.user_reward definition

CREATE TABLE `user_reward` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户表主键',
  `total_reward_num` int NOT NULL COMMENT '总奖励数',
  `unclaimed_reward_num` int NOT NULL DEFAULT '0' COMMENT '未领取奖励数',
  `claimed_reward_num` int NOT NULL DEFAULT '0' COMMENT '已领取奖励数',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT=' 用户奖励数据表';


-- punch_settle.user_claim_reward_record definition

CREATE TABLE `user_claim_reward_record` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户表主键',
  `claim_reward_num` int NOT NULL DEFAULT '0' COMMENT '本次领取奖励数',
  `claim_reward_time` timestamp NOT NULL COMMENT '领取奖励时间',
  `before_claim_reward_num` int NOT NULL DEFAULT '0' COMMENT '领取前用户拥有的奖励数',
  `after_claim_reward_num` int NOT NULL DEFAULT '0' COMMENT '领取后用户拥有的奖励数',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='领取奖励记录表';