PunchSettle-Server/doc/sql/update-v3.sql

CREATE TABLE `new_user_stats` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `stats_time` char(10) NOT NULL COMMENT '统计时间（格式：yyyy-MM-dd）',
  `new_user_count` int NOT NULL COMMENT '新用户数量',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`),
  KEY `idx_NewUserStats_StatsTime` (`stats_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='新用户数据统计';

CREATE TABLE `points_stats` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户id',
  `stats_time` char(10) NOT NULL COMMENT '统计时间（格式：yyyy-MM-dd）',
  `settle_points` int NOT NULL COMMENT '每日积分的结算数',
  `consume_points` int NOT NULL COMMENT '每日积分的消耗数量',
  `total_points` int NOT NULL COMMENT '每日剩余总积分',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`),
  KEY `idx_PointsStats_StatsTime` (`stats_time`),
  KEY `idx_PointsStats_UserId` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='积分数据统计';

CREATE TABLE `reward` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户id',
  `reward_unique_id` bigint DEFAULT NULL COMMENT '奖励唯一id',
  `reward_status` varchar(10) DEFAULT NULL COMMENT '奖励状态(活跃-ACTIVE，归档-ARCHIVE)',
  `reward_version` int DEFAULT NULL COMMENT '奖励修改版本',
  `reward_name` varchar(100) NOT NULL COMMENT '奖励名称',
  `exchange_points` int NOT NULL COMMENT '兑换所需积分',
  `auto_status` varchar(10) NOT NULL COMMENT '是否启用自动兑换(ENABLED-启用，DISABLED-关闭)',
  `auto_exchange_count` int DEFAULT NULL COMMENT '自动兑换数量',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`),
  KEY `idx_Reward_UserId` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='奖励';

CREATE TABLE `reward_account_rela` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户id',
  `reward_unique_id` bigint NOT NULL COMMENT '奖励唯一id',
  `account_id` bigint NOT NULL COMMENT '账户id',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`),
  KEY `idx_RewardAccountRela_UserId` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='奖励与账户关联表';

CREATE TABLE `reward_history` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户id',
  `reward_id` bigint NOT NULL COMMENT '奖励id',
  `exchange_method` varchar(10) NOT NULL COMMENT '兑换类型(手动兑换-MANUAL，自动兑换-AUTO)',
  `exchange_count` int NOT NULL COMMENT '兑换次数',
  `exchange_total_points` int NOT NULL COMMENT '兑换所需总积分',
  `account_id` bigint NOT NULL COMMENT '兑换使用的账户id',
  `account_name` varchar(100) NOT NULL COMMENT '账户名称',
  `account_points_before_exchange` int NOT NULL COMMENT '兑换前账户中的积分',
  `account_points_after_exchange` int NOT NULL COMMENT '兑换后账户中的积分',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`),
  KEY `idx_RewardHis_UserId` (`user_id`),
  KEY `idx_RewardHis_CreationTime` (`creation_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='奖励兑换记录';

CREATE TABLE `punch_in_stats_month` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户id',
  `task_unique_id` bigint NOT NULL COMMENT '任务唯一ID',
  `stats_time` char(7) NOT NULL COMMENT '统计时间（格式：yyyy-MM）',
  `punch_in_total_count` int NOT NULL COMMENT '本月需打卡数',
  `punch_in_count` int NOT NULL COMMENT '本月已打卡数',
  `punch_in_done_count` int NOT NULL COMMENT '本月完成打卡数',
  `punch_in_rate` decimal(5,2) NOT NULL COMMENT '本月打卡率',
  `punch_in_done_rate` decimal(5,2) NOT NULL COMMENT '本月打卡完成率',
  `points` int NOT NULL COMMENT '本月获取积分数',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`),
  KEY `idx_PIStatsMonth_UserId` (`user_id`),
  KEY `idx_PIStatsMonth_TaskUniqueId` (`task_unique_id`),
  KEY `idx_PIStatsMonth_StatsTime` (`stats_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='打卡任务月数据统计';

CREATE TABLE `punch_in_stats_year` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户id',
  `task_unique_id` bigint NOT NULL COMMENT '任务唯一ID',
  `stats_time` char(4) NOT NULL COMMENT '统计时间（格式：yyyy）',
  `punch_in_total_count` int NOT NULL COMMENT '本年需打卡数',
  `punch_in_count` int NOT NULL COMMENT '本年已打卡数',
  `punch_in_done_count` int NOT NULL COMMENT '本年完成打卡数',
  `punch_in_rate` decimal(5,2) NOT NULL COMMENT '本年打卡率',
  `punch_in_done_rate` decimal(5,2) NOT NULL COMMENT '本年打卡完成率',
  `points` int NOT NULL COMMENT '本年获取积分数',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`),
  KEY `idx_PIStatsYear_UserId` (`user_id`),
  KEY `idx_PIStatsYear_TaskUniqueId` (`task_unique_id`),
  KEY `idx_PIStatsYear_StatsTime` (`stats_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='打卡任务年数据统计';

CREATE TABLE `account` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户id',
  `account_name` varchar(100) NOT NULL COMMENT '账户名称',
  `account_type` varchar(10) NOT NULL COMMENT '账户类型（BASIC-基本户，GENERAL-一般户）',
  `points` int NOT NULL COMMENT '奖励积分',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`),
  KEY `idx_Account_UserId` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='账户';

CREATE TABLE `account_transfer_history` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户id',
  `sender_account_id` bigint NOT NULL COMMENT '转出账户id',
  `recipient_account_id` bigint NOT NULL COMMENT '转入账户id',
  `transfer_points` int NOT NULL COMMENT '转账积分',
  `sa_points_before_transfer` int NOT NULL COMMENT '转出账户转出前积分',
  `sa_points_after_transfer` int NOT NULL COMMENT '转出账户转出后积分',
  `ra_points_before_transfer` int NOT NULL COMMENT '转入账户转入前积分',
  `ra_points_after_transfer` int NOT NULL COMMENT '转入账户转入后积分',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`),
  KEY `idx_ATransferHis_UserId` (`user_id`),
  KEY `idx_ATransferHis_CreationTime` (`creation_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='账户转账记录';

CREATE TABLE `item_consume_win_history` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `action` varchar(30) NOT NULL COMMENT '动作（消费-CONSUME，中奖-WIN，撤销消费-REVERSE_CONSUME，撤销中奖-REVERSE_WIN）',
  `source` varchar(100) NOT NULL COMMENT '来源',
  `category` varchar(100) NOT NULL COMMENT '种类',
  `amount` decimal(10,2) NOT NULL COMMENT '金额（元）',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`),
  KEY `idx_ICWHis_UserId` (`user_id`),
  KEY `idx_ICWHis_CreationTime` (`creation_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='物品消费与中奖记录';

CREATE TABLE `punch_in_task_ext` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `punch_in_task_id` bigint NOT NULL COMMENT '打卡任务ID',
  `dimension` varchar(10) NOT NULL COMMENT '使用维度（一天-ONE_DAY，多天-MULTI_DAY）',
  `initial_value` int NOT NULL COMMENT '起始值（单位：次）',
  `extra_points` int NOT NULL COMMENT '奖励的积分（额外）',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`),
  KEY `idx_PITaskExt_UserId` (`user_id`),
  KEY `idx_PITaskExt_PunchInTaskId` (`punch_in_task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='打卡任务拓展表';

CREATE TABLE `punch_in_multi_task` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `multi_task_unique_id` bigint NOT NULL COMMENT '打卡多任务唯一ID',
  `multi_task_status` varchar(10) NOT NULL COMMENT '任务状态(活跃-ACTIVE，归档-ARCHIVE)',
  `multi_task_version` int NOT NULL COMMENT '任务修改版本',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `punch_in_method` varchar(10) NOT NULL COMMENT '打卡方式（次数-COUNT，比率-RATE）',
  `punch_in_done_count` int DEFAULT NULL COMMENT '打卡完成次数',
  `punch_in_done_rate` decimal(5,2) DEFAULT NULL COMMENT '打卡完成率',
  `points` int NOT NULL COMMENT '奖励的积分(基本)',
  `grace_status` varchar(10) NOT NULL COMMENT '是否启用宽限期(ENABLED-启用，DISABLED-关闭)',
  `grace_day` int DEFAULT NULL COMMENT '宽限期（单位：天）',
  `interrupted_day` int DEFAULT NULL COMMENT '打卡中断天数（单位：天）',
  `penalty_day` int DEFAULT NULL COMMENT '惩罚天数（单位：天）',
  `extra_method` varchar(10) NOT NULL COMMENT '额外奖励方式（无-NONE，固定-FIXED，区间-INTERVAL）',
  `extra_points` int DEFAULT NULL COMMENT '奖励的积分（额外）',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`),
  KEY `idx_PIMTask_UserId` (`user_id`),
  KEY `idx_PIMTask_MultiTaskUniqueId` (`multi_task_unique_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='打卡多任务表';

CREATE TABLE `punch_in_multi_task_ext` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `multi_task_unique_id` bigint NOT NULL COMMENT '多任务ID',
  `dimension` varchar(10) NOT NULL COMMENT '使用维度（一天-ONE_DAY，多天-MULTI_DAY）',
  `initial_value` int NOT NULL COMMENT '起始值（单位：次）',
  `extra_points` int NOT NULL COMMENT '奖励的积分（额外）',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`),
  KEY `idx_PIMTaskExt_UserId` (`user_id`),
  KEY `idx_PIMTaskExt_MTaskUniqueId` (`multi_task_unique_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='打卡多任务拓展表';

CREATE TABLE `punch_in_multi_task_rela` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `punch_in_multi_task_id` bigint NOT NULL COMMENT '打卡多任务ID',
  `punch_in_task_id` bigint NOT NULL COMMENT '打卡任务ID',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`),
  KEY `idx_PIMTaskRela_UserId` (`user_id`),
  KEY `idx_PIMTaskRela_PIMTaskId` (`punch_in_multi_task_id`),
  KEY `idx_PIMTaskRela_PITaskId` (`punch_in_task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='打卡多任务关联表';

CREATE TABLE `punch_in_task_history` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `punch_in_task_unique_id` bigint NOT NULL COMMENT '打卡任务唯一ID',
  `punch_in_date` varchar(10) NOT NULL COMMENT '打卡日期',
  `punch_in_status` varchar(10) NOT NULL COMMENT '打卡状态(DONE-完成，UNDONE-未完成)',
  `count_track` int DEFAULT NULL COMMENT '次数记录',
  `time_track` time DEFAULT NULL COMMENT '时间记录',
  `consecutive_status` varchar(20) DEFAULT NULL COMMENT '连续打卡状态(正常打卡-NORMAL、中断--INTERRUPTED)',
  `consecutive_day` int DEFAULT NULL COMMENT '连续天数，第一天开始就等于1',
  `settle_task_history_id` bigint DEFAULT NULL COMMENT '结算任务执行ID',
  `settle_status` varchar(20) NOT NULL COMMENT '结算状态(未结算-UNSETTLED，宽限期跳过J-GRACE_SKIP，惩罚跳过-PENALTY_SKIP，已结算-SETTLED)',
  `settle_punch_in_task_id` bigint DEFAULT NULL COMMENT '结算时的打卡任务ID',
  `settle_points` int DEFAULT NULL COMMENT '结算奖励积分',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`),
  KEY `idx_PITaskHis_UserId` (`user_id`),
  KEY `idx_PITaskHis_PITaskUniqueId` (`punch_in_task_unique_id`),
  KEY `idx_PITaskHis_PunchInDate` (`punch_in_date`),
  KEY `idx_PITaskHis_CreationTime` (`creation_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='打卡任务记录表';

CREATE TABLE `punch_in_multi_task_history` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `punch_in_multi_task_unique_id` bigint NOT NULL COMMENT '打卡多任务唯一ID',
  `punch_in_date` varchar(10) NOT NULL COMMENT '打卡日期',
  `punch_in_status` varchar(10) NOT NULL COMMENT '打卡状态(DONE-完成，UNDONE-未完成)',
  `total_task_count` int NOT NULL COMMENT '总任务数',
  `punch_in_done_count` int NOT NULL COMMENT '打卡完成数',
  `punch_in_done_rate` decimal(5,2) NOT NULL COMMENT '打卡完成率',
  `consecutive_status` varchar(20) NOT NULL COMMENT '连续打卡状态(正常打卡-NORMAL、中断--INTERRUPTED)',
  `consecutive_day` int NOT NULL COMMENT '连续天数，第一天开始就等于1',
  `settle_task_history_id` bigint DEFAULT NULL COMMENT '结算任务执行ID',
  `settle_status` varchar(20) NOT NULL COMMENT '结算状态(未结算-UNSETTLED，宽限期跳过J-GRACE_SKIP，惩罚跳过-PENALTY_SKIP，已结算-SETTLED)',
  `settle_punch_in_multi_task_id` bigint DEFAULT NULL COMMENT '结算时的打卡多任务ID',
  `settle_points` int DEFAULT NULL COMMENT '结算奖励积分',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`),
  KEY `idx_PIMTaskHis_UserId` (`user_id`),
  KEY `idx_PIMTaskHis_PIMTUniqueId` (`punch_in_multi_task_unique_id`),
  KEY `idx_PIMTaskHis_PunchInDate` (`punch_in_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='打卡多任务记录表';

CREATE TABLE `punch_in_status` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `punch_in_multi_task_unique_id` bigint DEFAULT NULL COMMENT '打卡多任务唯一ID',
  `punch_in_task_unique_id` bigint DEFAULT NULL COMMENT '打卡任务唯一ID',
  `consecutive_status` varchar(20) NOT NULL COMMENT '连续打卡状态(正常打卡-NORMAL、中断--INTERRUPTED)',
  `consecutive_day` int NOT NULL COMMENT '连续天数，第一天开始就等于1',
  `start_date` date NOT NULL COMMENT '开始日期',
  `end_date` date DEFAULT NULL COMMENT '结束日期',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`),
  KEY `idx_PIStatus_UserId` (`user_id`),
  KEY `idx_PIStatus_PIMTaskUniqueId` (`punch_in_multi_task_unique_id`),
  KEY `idx_PIStatus_PITaskUniqueId` (`punch_in_task_unique_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='打卡任务状态表';

CREATE TABLE `punch_in_status_history` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `punch_in_multi_task_unique_id` bigint DEFAULT NULL COMMENT '打卡多任务唯一ID',
  `punch_in_task_unique_id` bigint DEFAULT NULL COMMENT '打卡任务唯一ID',
  `consecutive_status` varchar(20) NOT NULL COMMENT '连续打卡状态(正常打卡-NORMAL、中断--INTERRUPTED)',
  `consecutive_day` int NOT NULL COMMENT '连续天数，第一天开始就等于1',
  `start_date` date NOT NULL COMMENT '开始日期',
  `end_date` date DEFAULT NULL COMMENT '结束日期',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`),
  KEY `idx_PIStatusHis_UserId` (`user_id`),
  KEY `idx_PIStatusHis_PIMTUniqueId` (`punch_in_multi_task_unique_id`),
  KEY `idx_PIStatusHis_PITUniqueId` (`punch_in_task_unique_id`),
  KEY `idx_PIStatusHis_CreationTime` (`creation_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='打卡任务状态记录表';

CREATE TABLE `settle_points_history` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `settle_task_history_id` bigint NOT NULL COMMENT '结算任务执行ID',
  `user_id` bigint NOT NULL COMMENT '用户表主键',
  `settle_date` varchar(10) NOT NULL COMMENT '结算日期',
  `settle_points` int NOT NULL COMMENT '结算积分',
  `before_settle_points` int NOT NULL COMMENT '结算前用户拥有的积分',
  `after_settle_points` int NOT NULL COMMENT '结算后用户拥有的积分',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`),
  KEY `idx_SPointsHis_UserId` (`user_id`),
  KEY `idx_SPointsHis_SettleDate` (`settle_date`),
  KEY `idx_SPointsHis_STaskHisId` (`settle_task_history_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='结算积分记录表';

CREATE TABLE `sys_calendar` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `gregorian_date` char(10) NOT NULL COMMENT '公历日期，格式：yyyy-MM-dd',
  `lunar_date` char(10) DEFAULT NULL COMMENT '农历日期，格式：yyyy-MM-dd',
  `week_day` int DEFAULT NULL COMMENT '周内天数（1-7），周一为1，周日为7',
  `status` char(7) DEFAULT NULL COMMENT '状态：WORKDAY-上班， HOLIDAY-放假；上班：含正常工作日及补班；放假：含周末及节假日。',
  `festival` varchar(30) DEFAULT NULL COMMENT '国家法定节日：元旦节, 春节, 清明节, 劳动节, 端午节, 中秋节, 国庆节。',
  `bad_day` char(1) DEFAULT NULL COMMENT '是否补班：Y-是，N-否；需要补班，真是难受的一天！仅当需要补班时有该字段。',
  `description` varchar(300) DEFAULT NULL COMMENT '描述，表示什么时候补班，例如劳动节前补班、国庆节后补班等。仅需要补班时有该字段。',
  `statutory` char(1) DEFAULT NULL COMMENT '是否法定节假日：Y-是，N-否；如果是法定节假日则返回1，仅当是法定节假日时有该字段。',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`),
  KEY `idx_SysCalendar_GregorianDate` (`gregorian_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='系统日历表';

CREATE TABLE `sys_schedule_task` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `task_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '定时任务编码',
  `task_name` varchar(100) NOT NULL COMMENT '定时任务名称',
  `execute_status` varchar(10) NOT NULL COMMENT '是否启用定时任务(ENABLE-开启、DISABLE-关闭)',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 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=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='定时任务执行配置表';

CREATE TABLE `sys_schedule_task_history` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `task_code` varchar(100) NOT NULL COMMENT '定时任务编码',
  `task_name` varchar(100) NOT NULL COMMENT '定时任务名称',
  `launch_method` varchar(10) NOT NULL COMMENT '启动方式（AUTO-自动，MANUAL-手动）',
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '任务开始时间',
  `end_time` timestamp NULL DEFAULT NULL COMMENT '任务结束时间',
  `process_status` varchar(10) NOT NULL COMMENT '执行状态（RUNNING-执行中，SUCCESS-成功，FAIL-失败）',
  `error_message` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '错误信息',
  `created_by` bigint NOT NULL COMMENT '创建人',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_updated_by` bigint NOT NULL COMMENT '最后更新人',
  `last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `version` bigint NOT NULL DEFAULT '1' COMMENT '版本号',
  `delete_flag` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标志（0-未删除，1-已删除）',
  PRIMARY KEY (`id`),
  KEY `idx_SScheduleTaskHis_TaskCode` (`task_code`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='定时任务执行记录表';

ALTER TABLE `user` DROP COLUMN `avatar`;
ALTER TABLE `user` CHANGE total_reward_num total_points int DEFAULT 0 NOT NULL COMMENT '总积分';
ALTER TABLE `user` MODIFY COLUMN total_points int DEFAULT 0 NOT NULL COMMENT '总积分';
ALTER TABLE `user` CHANGE unclaimed_reward_num unused_points int DEFAULT 0 NOT NULL COMMENT '未使用积分';
ALTER TABLE `user` MODIFY COLUMN unused_points int DEFAULT 0 NOT NULL COMMENT '未使用积分';
ALTER TABLE `user` CHANGE claimed_reward_num used_points int DEFAULT 0 NOT NULL COMMENT '已使用积分';
ALTER TABLE `user` MODIFY COLUMN used_points int DEFAULT 0 NOT NULL COMMENT '已使用积分';
ALTER TABLE `user` CHANGE lottery_invest_amount total_consume_amount decimal(10,2) DEFAULT 0.00 NOT NULL COMMENT '总消费金额(元)';
ALTER TABLE `user` MODIFY COLUMN total_consume_amount decimal(10,2) DEFAULT 0.00 NOT NULL COMMENT '总消费金额(元)';
ALTER TABLE `user` CHANGE lottery_win_amount total_win_amount decimal(10,2) DEFAULT 0.00 NOT NULL COMMENT '总中奖金额(元)';
ALTER TABLE `user` MODIFY COLUMN total_win_amount decimal(10,2) DEFAULT 0.00 NOT NULL COMMENT '总中奖金额(元)';

RENAME TABLE punch_settle.punch_in TO punch_settle.punch_in_task;

ALTER TABLE punch_settle.punch_in_task CHANGE reward_num points int DEFAULT 1 NOT NULL COMMENT '奖励的积分（基本）';
ALTER TABLE punch_settle.punch_in_task MODIFY COLUMN points int DEFAULT 1 NOT NULL COMMENT '奖励的积分（基本）';

ALTER TABLE `punch_in_task`
ADD COLUMN `task_unique_id` bigint NULL COMMENT '任务唯一ID',
ADD COLUMN `task_status` varchar(10) NULL COMMENT '任务状态(活跃-ACTIVE，归档-ARCHIVE)',
ADD COLUMN `task_version` int NULL COMMENT '任务修改版本',
ADD COLUMN `end_date` date DEFAULT NULL  COMMENT '结束日期',
ADD COLUMN `archive_status` varchar(10) NULL  COMMENT '是否归档(活跃-ACTIVE，归档-ARCHIVE)',
ADD COLUMN `display_order` int NULL  COMMENT '显示顺序',
ADD COLUMN `display_time` time DEFAULT NULL  COMMENT '显示时间',
ADD COLUMN `punch_in_method` varchar(10) DEFAULT NULL  COMMENT '打卡方式(单次打卡-SINGLE，计数-COUNT、计时-TIMING)',
ADD COLUMN `compare_rule` varchar(10) DEFAULT NULL  COMMENT '比较规则(大于等于-GTE，小于等于-LTE)',
ADD COLUMN `holiday_status` varchar(10) NULL  COMMENT '是否启用节假日奖励(ENABLED-启用，DISABLED-关闭)',
ADD COLUMN `holiday_count_track` int DEFAULT NULL COMMENT '次数记录（节假日用）',
ADD COLUMN `holiday_time_track` time DEFAULT NULL COMMENT '时间记录（节假日用）',
ADD COLUMN `full_attendance_status` varchar(10) DEFAULT NULL COMMENT '是否启用全勤奖励(ENABLED-启用，DISABLED-关闭)',
ADD COLUMN `full_attendance_period` varchar(10) DEFAULT NULL COMMENT '全勤周期（周-WEEK，月-MONTH）',
ADD COLUMN `full_attendance_fault_tolerance_cnt` int DEFAULT NULL COMMENT '全勤容错次数',
ADD COLUMN `repeat_category` varchar(10) DEFAULT NULL COMMENT '重复周期类型（每日-EVERYDAY、法定工作日-WORKDAY、法定节假日（含周末）-HOLIDAY、自定义（周一至周日）-CUSTOM）',
ADD COLUMN `repeat_custom_day` varchar(13) DEFAULT NULL COMMENT '自定义重复日（周一-1,周二-2,周三-3,周四-4,周五-5,周六-6,周日-7）',
ADD COLUMN `extra_method` varchar(10) DEFAULT NULL COMMENT '额外奖励方式（无-NONE，固定-FIXED，区间-INTERVAL）',
ADD COLUMN `extra_time_step` int DEFAULT NULL COMMENT '额外的时间间隔（单位：分钟）',
ADD COLUMN `grace_status` varchar(10) DEFAULT NULL COMMENT '是否启用宽限期(ENABLED-启用，DISABLED-关闭)',
ADD COLUMN `grace_day` int DEFAULT NULL COMMENT '宽限期（单位：天）',
ADD COLUMN `interrupted_day` int DEFAULT NULL COMMENT '打卡中断天数（单位：天）',
ADD COLUMN `penalty_day` int DEFAULT NULL COMMENT '惩罚天数（单位：天）',
ADD COLUMN `auto_status` varchar(10) DEFAULT NULL COMMENT '是否启用自动打卡(ENABLED-启用，DISABLED-关闭)';

ALTER TABLE punch_settle.punch_in_task CHANGE created_by created_by bigint NOT NULL COMMENT '创建人' AFTER auto_status;
ALTER TABLE punch_settle.punch_in_task CHANGE creation_time creation_time timestamp DEFAULT CURRENT_TIMESTAMP  NOT NULL COMMENT '创建时间' AFTER created_by;
ALTER TABLE punch_settle.punch_in_task CHANGE last_updated_by last_updated_by bigint NOT NULL COMMENT '最后更新人' AFTER creation_time;
ALTER TABLE punch_settle.punch_in_task CHANGE last_update_time last_update_time timestamp DEFAULT CURRENT_TIMESTAMP  on update CURRENT_TIMESTAMP NOT NULL COMMENT '最后更新时间' AFTER last_updated_by;
ALTER TABLE punch_settle.punch_in_task CHANGE version version bigint DEFAULT 1 NOT NULL COMMENT '版本号' AFTER last_update_time;
ALTER TABLE punch_settle.punch_in_task CHANGE delete_flag delete_flag tinyint DEFAULT 0 NOT NULL COMMENT '逻辑删除标志（0-未删除，1-已删除）' AFTER version;
ALTER TABLE punch_settle.punch_in_task CHANGE task_unique_id task_unique_id bigint NULL COMMENT '任务唯一ID' AFTER id;
ALTER TABLE punch_settle.punch_in_task CHANGE task_status task_status varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '任务状态(活跃-ACTIVE，归档-ARCHIVE)' AFTER task_unique_id;
ALTER TABLE punch_settle.punch_in_task CHANGE task_version task_version int NULL COMMENT '任务修改版本' AFTER task_status;
ALTER TABLE punch_settle.punch_in_task CHANGE description description varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '任务描述' AFTER task_name;
ALTER TABLE punch_settle.punch_in_task CHANGE points points int DEFAULT 1 NOT NULL COMMENT '奖励的积分（基本）' AFTER description;
ALTER TABLE punch_settle.punch_in_task CHANGE end_date end_date date NULL COMMENT '结束日期' AFTER points;
ALTER TABLE punch_settle.punch_in_task CHANGE archive_status archive_status varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '是否归档(活跃-ACTIVE，归档-ARCHIVE)' AFTER end_date;
ALTER TABLE punch_settle.punch_in_task CHANGE display_order display_order int NULL COMMENT '显示顺序' AFTER archive_status;
ALTER TABLE punch_settle.punch_in_task CHANGE display_time display_time time NULL COMMENT '显示时间' AFTER display_order;
ALTER TABLE punch_settle.punch_in_task CHANGE punch_in_method punch_in_method varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '打卡方式(单次打卡-SINGLE，计数-COUNT、计时-TIMING)' AFTER rule;
ALTER TABLE punch_settle.punch_in_task CHANGE compare_rule compare_rule varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '比较规则(大于等于-GTE，小于等于-LTE)' AFTER punch_in_method;
update punch_settle.punch_in_task
set task_unique_id = id,
task_status = 'ACTIVE',
task_version = 1;

UPDATE punch_settle.punch_in_task SET archive_status = 'ACTIVE' WHERE archive_flag = 0;
UPDATE punch_settle.punch_in_task SET archive_status = 'ARCHIVE' WHERE archive_flag = 1;
UPDATE punch_settle.punch_in_task SET punch_in_method = 'SINGLE' WHERE category = 0;
UPDATE punch_settle.punch_in_task SET punch_in_method = 'COUNT' WHERE category = 1;
UPDATE punch_settle.punch_in_task SET punch_in_method = 'TIMING' WHERE category = 2;
update punch_settle.punch_in_task set compare_rule = 'GTE' WHERE rule = 0 and category != 0;
update punch_settle.punch_in_task set compare_rule = 'LTE' WHERE rule = 1 and category != 0;
ALTER TABLE punch_settle.punch_in_task DROP COLUMN archive_flag;
ALTER TABLE punch_settle.punch_in_task DROP COLUMN category;
ALTER TABLE punch_settle.punch_in_task DROP COLUMN rule;
-- 节假日没有节假日的判断目标数据无法初始化，因此初始化为关闭
UPDATE punch_settle.punch_in_task SET holiday_status = 'DISABLED';
UPDATE punch_settle.punch_in_task SET full_attendance_status = 'DISABLED' WHERE full_attendance_flag = 0;
UPDATE punch_settle.punch_in_task
SET full_attendance_status = 'ENABLED',
full_attendance_fault_tolerance_cnt = 1,
full_attendance_period = 'WEEK'
WHERE full_attendance_flag = 1;
ALTER TABLE punch_settle.punch_in_task DROP COLUMN full_attendance_flag;
ALTER TABLE punch_settle.punch_in_task DROP COLUMN weekend_double_flag;
UPDATE punch_settle.punch_in_task SET repeat_category = 'EVERYDAY';
UPDATE punch_settle.punch_in_task SET extra_method = 'NONE';
UPDATE punch_settle.punch_in_task SET grace_status = 'DISABLED';
UPDATE punch_settle.punch_in_task SET auto_status = 'DISABLED';
ALTER TABLE punch_settle.punch_in_task MODIFY COLUMN archive_status varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '是否归档(活跃-ACTIVE，归档-ARCHIVE)';
ALTER TABLE punch_settle.punch_in_task MODIFY COLUMN task_unique_id bigint NOT NULL COMMENT '任务唯一ID';
ALTER TABLE punch_settle.punch_in_task MODIFY COLUMN task_status varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '任务状态(活跃-ACTIVE，归档-ARCHIVE)';
ALTER TABLE punch_settle.punch_in_task MODIFY COLUMN task_version int NOT NULL COMMENT '任务修改版本';
ALTER TABLE punch_settle.punch_in_task MODIFY COLUMN punch_in_method varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '打卡方式(单次打卡-SINGLE，计数-COUNT、计时-TIMING)';
ALTER TABLE punch_settle.punch_in_task MODIFY COLUMN holiday_status varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '是否启用节假日奖励(ENABLED-启用，DISABLED-关闭)';
ALTER TABLE punch_settle.punch_in_task MODIFY COLUMN full_attendance_status varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '是否启用全勤奖励(ENABLED-启用，DISABLED-关闭)';
ALTER TABLE punch_settle.punch_in_task MODIFY COLUMN repeat_category varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '重复周期类型（每日-EVERYDAY、法定工作日-WORKDAY、法定节假日（含周末）-HOLIADY、自定义（周一至周日）-CUSTOM）';
ALTER TABLE punch_settle.punch_in_task MODIFY COLUMN extra_method varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '额外奖励方式（无-NONE，固定-FIXED，区间-INTERVAL）';
ALTER TABLE punch_settle.punch_in_task MODIFY COLUMN grace_status varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '是否启用宽限期(ENABLED-启用，DISABLED-关闭)';
ALTER TABLE punch_settle.punch_in_task MODIFY COLUMN auto_status varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '是否启用自动打卡(ENABLED-启用，DISABLED-关闭)';

ALTER TABLE punch_settle.punch_in_multi_task MODIFY COLUMN punch_in_method varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '打卡方式（次数-COUNT，比率-RATE）';
ALTER TABLE punch_settle.punch_in_multi_task MODIFY COLUMN grace_status varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '是否启用宽限期(ENABLED-启用，DISABLED-关闭)';
ALTER TABLE punch_settle.punch_in_multi_task MODIFY COLUMN extra_method varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '额外奖励方式（无-NONE，固定-FIXED，区间-INTERVAL）';

ALTER TABLE punch_settle.sys_dict MODIFY COLUMN status varchar(10) DEFAULT 'DISABLED' NOT NULL COMMENT '状态(ENABLED-启用，DISABLED-失效)';
UPDATE punch_settle.sys_dict SET status = 'ENABLED' WHERE status = '1';
UPDATE punch_settle.sys_dict SET status = 'DISABLED' WHERE status = '0';
ALTER TABLE punch_settle.sys_dict ADD UNIQUE INDEX UK_SysDict_DictCode (dict_code);

ALTER TABLE punch_settle.sys_dict_item ADD UNIQUE INDEX UK_DictItem_DictId_ItemCode (dict_id, item_code);
ALTER TABLE punch_settle.sys_dict_item ADD INDEX idx_SysDictItem_ItemCode (item_code);
ALTER TABLE punch_settle.sys_dict_item MODIFY COLUMN status varchar(10) DEFAULT 'DISABLED' NOT NULL COMMENT '状态(ENABLED-启用，DISABLED-失效)';
UPDATE punch_settle.sys_dict_item SET status = 'ENABLED' WHERE status = '1';
UPDATE punch_settle.sys_dict_item SET status = 'DISABLED' WHERE status = '0';
ALTER TABLE punch_settle.sys_dict_item MODIFY COLUMN data_type varchar(10) DEFAULT 'STRING' NOT NULL COMMENT '数据类型（STRING-字符串，INTEGER-整型数值）';
UPDATE punch_settle.sys_dict_item SET data_type = 'INTEGER' WHERE data_type = '1';
UPDATE punch_settle.sys_dict_item SET data_type = 'STRING' WHERE data_type = '0';

INSERT INTO item_consume_win_history(id, user_id, action, source, category, amount, created_by, creation_time, last_updated_by, last_update_time, version, delete_flag)
SELECT id, created_by, action_type, source, category, amount, created_by, creation_time, last_updated_by, last_update_time, version, delete_flag
FROM lottery_scratch_record;
UPDATE punch_settle.item_consume_win_history SET action = 'CONSUME' WHERE action = '0';
UPDATE punch_settle.item_consume_win_history SET action = 'WIN' WHERE action = '1';
UPDATE punch_settle.item_consume_win_history SET action = 'REVERSE_CONSUME' WHERE action = '2';
UPDATE punch_settle.item_consume_win_history SET action = 'REVERSE_WIN' WHERE action = '3';
DROP TABLE IF EXISTS lottery_scratch_record;

RENAME TABLE punch_settle.settlement_task TO punch_settle.settle_task_history;
ALTER TABLE punch_settle.settle_task_history COMMENT '结算任务执行记录表';
ALTER TABLE punch_settle.settle_task_history ADD INDEX idx_SettleTaskHis_SettleDate (settle_date);
ALTER TABLE punch_settle.settle_task_history DROP COLUMN start_time;
ALTER TABLE punch_settle.settle_task_history DROP COLUMN end_time;
ALTER TABLE punch_settle.settle_task_history DROP COLUMN error_message;
ALTER TABLE punch_settle.settle_task_history CHANGE processed_num processed_total_num int DEFAULT 0 NOT NULL COMMENT '待处理结算数量';
ALTER TABLE punch_settle.settle_task_history MODIFY COLUMN processed_total_num int DEFAULT 0 NOT NULL COMMENT '待处理总结算数量';

INSERT INTO punch_settle.settle_points_history(id, settle_task_history_id, user_id, settle_date, settle_points, before_settle_points, after_settle_points, created_by, creation_time, last_updated_by, last_update_time, version, delete_flag)
SELECT pis.id, settlement_task_id, user_id, sth.settle_date , settle_reward_num, before_settle_reward_num, after_settle_reward_num, pis.created_by, pis.creation_time, pis.last_updated_by, pis.last_update_time, pis.version, pis.delete_flag
FROM punch_in_settlement pis
left join settle_task_history sth on pis.settlement_task_id = sth.id;
DROP TABLE IF EXISTS punch_in_settlement;

-- 删除无效的用户数据
delete from user where id not in (select created_by from punch_in_task group by created_by);
-- 初始化账户数据，account数从user导入
insert into account(user_id, account_name, account_type, points, created_by, creation_time, last_updated_by, last_update_time, version, delete_flag)
select id, '基本户', 'BASIC', unused_points, id, CURRENT_TIMESTAMP(), id, CURRENT_TIMESTAMP(), 1, 0
from user;
-- 初始化基本奖励数据
insert into reward(user_id, reward_name, exchange_points, auto_status, created_by, creation_time, last_updated_by, last_update_time, version, delete_flag)
select id, '刮一次刮刮乐', 1, 'DISABLED', id, CURRENT_TIMESTAMP(), id, CURRENT_TIMESTAMP(), 1, 0
from user;
update reward
set reward_unique_id = id,
reward_version = 1,
reward_status = 'ACTIVE';
ALTER TABLE punch_settle.reward MODIFY COLUMN reward_unique_id bigint NOT NULL COMMENT '奖励唯一id';
ALTER TABLE punch_settle.reward MODIFY COLUMN reward_status varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '奖励状态(活跃-ACTIVE，归档-ARCHIVE)';
ALTER TABLE punch_settle.reward MODIFY COLUMN reward_version int NOT NULL COMMENT '奖励修改版本';

ALTER TABLE punch_settle.reward_history DROP COLUMN account_name;
insert into reward_history(user_id, reward_id, exchange_method, exchange_count, exchange_total_points, account_id, account_points_before_exchange, account_points_after_exchange, created_by, creation_time, last_updated_by, last_update_time, version, delete_flag)
select created_by,
(select id from reward r where r.user_id = rr.created_by and r.reward_name = '刮一次刮刮乐'),
'MANUAL', claim_reward_num, claim_reward_num,
(SELECT ID FROM account a where a.user_id = rr.created_by and a.account_type = 'BASIC'),
rr.before_claim_reward_num , rr.after_claim_reward_num ,
created_by, creation_time, last_updated_by, last_update_time, version, delete_flag
from user_claim_reward_record rr;
DROP TABLE IF EXISTS user_claim_reward_record;

insert into punch_in_task_history(id, user_id, punch_in_task_unique_id,
punch_in_date, punch_in_status, count_track, time_track, settle_task_history_id,
settle_status, settle_punch_in_task_id, created_by, creation_time,
last_updated_by, last_update_time, version, delete_flag)
select pir.id, pir.created_by, punch_in_id, punch_in_date, punch_in_status,
count_track, time_track, pirsr.settlement_id , 'SETTLED', pir.punch_in_id , pir.created_by,
pir.creation_time, pir.last_updated_by, pir.last_update_time, pir.version, pir.delete_flag
from punch_in_record pir
left join punch_in_record_settlement_rela pirsr on pir.id = pirsr.record_id;

update punch_in_task_history set punch_in_status = 'DONE' where punch_in_status = '1';
update punch_in_task_history set punch_in_status = 'UNDONE' where punch_in_status = '2';
update punch_in_task_history set punch_in_status = 'UNDONE' where punch_in_status = '0';
DROP TABLE IF EXISTS punch_in_record;
DROP TABLE IF EXISTS punch_in_record_settlement_rela;

RENAME TABLE punch_settle.reward_history TO punch_settle.reward_exchange_history;

update punch_settle.sys_dict_item set description = item_name;
ALTER TABLE punch_settle.sys_dict_item CHANGE item_name item_value varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '字典项值';
ALTER TABLE punch_settle.sys_dict_item MODIFY COLUMN item_value varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '字典项值';
update punch_settle.sys_dict_item
set item_value = item_code
where data_type = 'INTEGER';
update punch_settle.sys_dict_item set item_code = 'INVEST' where id = 5;
update punch_settle.sys_dict_item set item_code = 'WIN' where id = 6;
update punch_settle.sys_dict_item set item_code = 'ENABLED' where id = 7;
update punch_settle.sys_dict_item set item_code = 'DISABLED' where id = 8;
update punch_settle.sys_dict_item set item_code = 'SINGLE' where id = 9;
update punch_settle.sys_dict_item set item_code = 'COUNT' where id = 10;
update punch_settle.sys_dict_item set item_code = 'TIMING' where id = 11;
update punch_settle.sys_dict_item set item_code = 'GTE' where id = 12;
update punch_settle.sys_dict_item set item_code = 'LET' where id = 13;