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

ALTER TABLE punch_settle.punch_in MODIFY COLUMN reward_num int DEFAULT 1 NOT NULL COMMENT '奖励倍数';

ALTER TABLE punch_settle.punch_in ADD category INT DEFAULT 0 NOT NULL COMMENT '打卡类型(0-单次打卡，1-计数、2计时)';
ALTER TABLE punch_settle.punch_in CHANGE category category INT DEFAULT 0 NOT NULL COMMENT '打卡类型(0-单次打卡，1-计数、2计时)' AFTER reward_num;
ALTER TABLE punch_settle.punch_in ADD rule int NULL COMMENT '比较规则(0-大于，1-大于等于，2-小于，3-小于等于)';
ALTER TABLE punch_settle.punch_in CHANGE rule rule int NULL COMMENT '比较规则(0-大于，1-大于等于，2-小于，3-小于等于)' AFTER category;
ALTER TABLE punch_settle.punch_in ADD count_track int NULL COMMENT '次数记录';
ALTER TABLE punch_settle.punch_in CHANGE count_track count_track int NULL COMMENT '次数记录' AFTER rule;
ALTER TABLE punch_settle.punch_in ADD time_track TIME NULL COMMENT '时间记录';
ALTER TABLE punch_settle.punch_in CHANGE time_track time_track TIME NULL COMMENT '时间记录' AFTER count_track;
ALTER TABLE punch_settle.punch_in ADD description varchar(300) NULL COMMENT '任务描述';
ALTER TABLE punch_settle.punch_in CHANGE description description varchar(300) NULL COMMENT '任务描述' AFTER time_track;

ALTER TABLE punch_settle.punch_in_record ADD count_track int NULL COMMENT '次数记录';
ALTER TABLE punch_settle.punch_in_record CHANGE count_track count_track int NULL COMMENT '次数记录' AFTER punch_in_date;
ALTER TABLE punch_settle.punch_in_record ADD time_track TIME NULL COMMENT '时间记录';
ALTER TABLE punch_settle.punch_in_record CHANGE time_track time_track TIME NULL COMMENT '时间记录' AFTER count_track;

ALTER TABLE punch_settle.punch_in_record_settlement_rela ADD reward_num INT DEFAULT 1 NOT NULL COMMENT '奖励倍数';
ALTER TABLE punch_settle.punch_in_record_settlement_rela CHANGE reward_num reward_num INT DEFAULT 1 NOT NULL COMMENT '奖励倍数' AFTER settlement_id;
ALTER TABLE punch_settle.punch_in_record_settlement_rela ADD category INT DEFAULT 0 NOT NULL COMMENT '打卡类型(0-单次打卡，1-计数、2计时)';
ALTER TABLE punch_settle.punch_in_record_settlement_rela CHANGE category category INT DEFAULT 0 NOT NULL COMMENT '打卡类型(0-单次打卡，1-计数、2计时)' AFTER reward_num;
ALTER TABLE punch_settle.punch_in_record_settlement_rela ADD rule int NULL COMMENT '比较规则(0-大于，1-大于等于，2-小于，3-小于等于)';
ALTER TABLE punch_settle.punch_in_record_settlement_rela CHANGE rule rule int NULL COMMENT '比较规则(0-大于，1-大于等于，2-小于，3-小于等于)' AFTER category;

RENAME TABLE punch_settle.user_reward TO punch_settle.user_ext;
ALTER TABLE punch_settle.user_ext
COMMENT='用户拓展数据表';

ALTER TABLE punch_settle.user_ext ADD lottery_invest_amount decimal(10,2) DEFAULT 0 NOT NULL COMMENT '彩票投入金额(元)';
ALTER TABLE punch_settle.user_ext CHANGE lottery_invest_amount lottery_invest_amount decimal(10,2) DEFAULT 0 NOT NULL COMMENT '彩票投入金额(元)' AFTER claimed_reward_num;
ALTER TABLE punch_settle.user_ext ADD lottery_win_amount decimal(10,2) DEFAULT 0 NOT NULL COMMENT '彩票中奖金额(元)';
ALTER TABLE punch_settle.user_ext CHANGE lottery_win_amount lottery_win_amount decimal(10,2) DEFAULT 0 NOT NULL COMMENT '彩票中奖金额(元)' AFTER lottery_invest_amount;
ALTER TABLE punch_settle.user_ext MODIFY COLUMN total_reward_num int DEFAULT 0 NOT NULL COMMENT '总奖励数';

CREATE TABLE `lottery_invest_record` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户表主键',
  `source` varchar(100) NOT NULL COMMENT '来源',
  `category` varchar(100) NOT NULL COMMENT '种类',
  `invest_amount` decimal(10,2) NOT NULL DEFAULT '0.00' 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`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='彩票投入记录';

CREATE TABLE `lottery_win_record` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint NOT NULL COMMENT '用户表主键',
  `source` varchar(100) NOT NULL COMMENT '来源',
  `category` varchar(100) NOT NULL COMMENT '种类',
  `win_amount` decimal(10,2) NOT NULL DEFAULT '0.00' 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`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='彩票中奖记录';

CREATE TABLE `sys_dict` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(30) NOT NULL COMMENT '名称（键）',
  `value` varchar(100) NOT NULL COMMENT '值',
  `description` varchar(200) NOT NULL COMMENT '描述',
  `status` int DEFAULT NULL COMMENT '状态(1-启用，0-失效)',
  `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='系统字典表';

ALTER TABLE punch_settle.sys_dict MODIFY COLUMN status int DEFAULT 1 NULL COMMENT '状态(1-启用，0-失效)';

CREATE TABLE `sys_dict_item` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `dict_id` bigint NOT NULL COMMENT '字典主键',
  `name` varchar(30) NOT NULL COMMENT '名称（键）',
  `value` varchar(100) NOT NULL COMMENT '值',
  `description` varchar(200) NOT NULL COMMENT '描述',
  `status` int DEFAULT NULL COMMENT '状态(1-启用，0-失效)',
  `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='系统字典项表';

ALTER TABLE punch_settle.sys_dict_item MODIFY COLUMN status int DEFAULT 1 NULL COMMENT '状态(1-启用，0-失效)';

DROP TABLE punch_settle.settlement_notify_task;

ALTER TABLE punch_settle.`user` DROP COLUMN reward_num;
ALTER TABLE punch_settle.`user` DROP COLUMN claimed_reward_num;

ALTER TABLE punch_settle.`user` ADD total_reward_num INT DEFAULT 0 NOT NULL COMMENT '总奖励数';
ALTER TABLE punch_settle.`user` CHANGE total_reward_num total_reward_num INT DEFAULT 0 NOT NULL COMMENT '总奖励数' AFTER avatar;
ALTER TABLE punch_settle.`user` ADD unclaimed_reward_num INT DEFAULT 0 NOT NULL COMMENT '未领取奖励数';
ALTER TABLE punch_settle.`user` CHANGE unclaimed_reward_num unclaimed_reward_num INT DEFAULT 0 NOT NULL COMMENT '未领取奖励数' AFTER total_reward_num;
ALTER TABLE punch_settle.`user` ADD claimed_reward_num INT DEFAULT 0 NOT NULL COMMENT '已领取奖励数';
ALTER TABLE punch_settle.`user` CHANGE claimed_reward_num claimed_reward_num INT DEFAULT 0 NOT NULL COMMENT '已领取奖励数' AFTER unclaimed_reward_num;
ALTER TABLE punch_settle.`user` ADD lottery_invest_amount decimal(10,2) DEFAULT 0.00 NOT NULL COMMENT '彩票投入金额(元)';
ALTER TABLE punch_settle.`user` CHANGE lottery_invest_amount lottery_invest_amount decimal(10,2) DEFAULT 0.00 NOT NULL COMMENT '彩票投入金额(元)' AFTER claimed_reward_num;
ALTER TABLE punch_settle.`user` ADD lottery_win_amount decimal(10,2) DEFAULT 0.00 NOT NULL COMMENT '彩票中奖金额(元)';
ALTER TABLE punch_settle.`user` CHANGE lottery_win_amount lottery_win_amount decimal(10,2) DEFAULT 0.00 NOT NULL COMMENT '彩票中奖金额(元)' AFTER lottery_invest_amount;

update user u, user_ext ue
set u.total_reward_num = ue.total_reward_num ,
u.unclaimed_reward_num = ue.unclaimed_reward_num ,
u.claimed_reward_num = ue.claimed_reward_num ,
u.lottery_invest_amount = ue.lottery_invest_amount ,
u.lottery_win_amount = ue.lottery_win_amount
where u.id = ue.user_id;

ALTER TABLE punch_settle.sys_dict CHANGE value dict_code varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '字典项编码';
ALTER TABLE punch_settle.sys_dict MODIFY COLUMN dict_code varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '字典项编码';
ALTER TABLE punch_settle.sys_dict CHANGE name dict_name varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '字典名称';
ALTER TABLE punch_settle.sys_dict MODIFY COLUMN dict_name varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '字典名称';
ALTER TABLE punch_settle.sys_dict CHANGE dict_code dict_code varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '字典项编码' AFTER id;
ALTER TABLE punch_settle.sys_dict MODIFY COLUMN dict_code varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '字典编码';
ALTER TABLE punch_settle.sys_dict
COMMENT='系统字典';
ALTER TABLE punch_settle.sys_dict_item CHANGE name item_code varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '字典项编码';
ALTER TABLE punch_settle.sys_dict_item MODIFY COLUMN item_code varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '字典项编码';
ALTER TABLE punch_settle.sys_dict_item CHANGE value item_name varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '字典项名称';
ALTER TABLE punch_settle.sys_dict_item MODIFY COLUMN item_name varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '字典项名称';
ALTER TABLE punch_settle.sys_dict MODIFY COLUMN description varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '描述';
ALTER TABLE punch_settle.sys_dict_item MODIFY COLUMN description varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '描述';
ALTER TABLE punch_settle.sys_dict_item MODIFY COLUMN status int DEFAULT 1 NOT NULL COMMENT '状态(1-启用，0-失效)';
ALTER TABLE punch_settle.sys_dict MODIFY COLUMN status int DEFAULT 1 NOT NULL COMMENT '状态(1-启用，0-失效)';
INSERT INTO punch_settle.sys_dict (id, dict_code, dict_name, description, status, created_by, creation_time, last_updated_by, last_update_time, version, delete_flag) VALUES(1, 'LOTTERY_SOURCE', '彩票来源', NULL, 1, 1, '2024-12-12 18:36:59', 1, '2024-12-12 18:37:42', 1, 0);
INSERT INTO punch_settle.sys_dict (id, dict_code, dict_name, description, status, created_by, creation_time, last_updated_by, last_update_time, version, delete_flag) VALUES(2, 'LOTTERY_SCRATCH_OFF_CATEGORY', '彩票刮刮乐种类', NULL, 1, 1, '2024-12-12 18:38:27', 1, '2024-12-12 18:38:27', 1, 0);
INSERT INTO punch_settle.sys_dict_item (id, dict_id, item_code, item_name, description, status, created_by, creation_time, last_updated_by, last_update_time, version, delete_flag) VALUES(1, 1, 'WELFARE_LOTTERY', '福利彩票', NULL, 1, 1, '2024-12-12 18:39:00', 1, '2024-12-12 18:39:00', 1, 0);
INSERT INTO punch_settle.sys_dict_item (id, dict_id, item_code, item_name, description, status, created_by, creation_time, last_updated_by, last_update_time, version, delete_flag) VALUES(2, 1, 'SPORTS_LOTTERY', '体育彩票', NULL, 1, 1, '2024-12-12 18:39:56', 1, '2024-12-12 18:39:56', 1, 0);
INSERT INTO punch_settle.sys_dict_item (id, dict_id, item_code, item_name, description, status, created_by, creation_time, last_updated_by, last_update_time, version, delete_flag) VALUES(3, 2, 'XINGYUN88', '幸运88', NULL, 1, 1, '2024-12-12 18:40:06', 1, '2024-12-12 18:40:06', 1, 0);
INSERT INTO punch_settle.sys_dict_item (id, dict_id, item_code, item_name, description, status, created_by, creation_time, last_updated_by, last_update_time, version, delete_flag) VALUES(4, 2, 'CHAOGEILI', '超给力', NULL, 1, 1, '2024-12-12 18:40:06', 1, '2024-12-12 18:40:06', 1, 0);

DROP TABLE punch_settle.user_ext;

DROP TABLE punch_settle.lottery_invest_record;

RENAME TABLE punch_settle.lottery_win_record TO punch_settle.lottery_scratch_record;
ALTER TABLE punch_settle.lottery_scratch_record
COMMENT='彩票刮刮乐记录';
ALTER TABLE punch_settle.lottery_scratch_record MODIFY COLUMN win_amount decimal(10,2) DEFAULT 0.00 NOT NULL COMMENT '金额（元）';
ALTER TABLE punch_settle.lottery_scratch_record ADD `type` int NOT NULL COMMENT '记录类型（0-投入/购买，1-中奖）';
ALTER TABLE punch_settle.lottery_scratch_record CHANGE `type` `type` int NOT NULL COMMENT '记录类型（0-投入/购买，1-中奖）' AFTER user_id;

ALTER TABLE punch_settle.lottery_scratch_record CHANGE `type` action_type int NOT NULL COMMENT '动作类型(0-投入/购买，1-中奖，2-撤销投入，3-撤销中奖)';

ALTER TABLE punch_settle.lottery_scratch_record ADD revoke_id bigint NULL COMMENT '撤销的刮刮乐记录ID';
ALTER TABLE punch_settle.lottery_scratch_record CHANGE revoke_id revoke_id bigint NULL COMMENT '撤销的刮刮乐记录ID' AFTER action_type;
ALTER TABLE punch_settle.lottery_scratch_record DROP COLUMN revoke_id;

ALTER TABLE punch_settle.lottery_scratch_record CHANGE win_amount amount decimal(10,2) DEFAULT 0.00 NOT NULL COMMENT '金额（元）';

ALTER TABLE punch_settle.punch_in_record ADD punch_in_status int DEFAULT 0 NOT NULL COMMENT '打卡状态(0-进行中，1-完成，2-未完成) ，结算后修改状态，单次打卡除外';
ALTER TABLE punch_settle.punch_in_record CHANGE punch_in_status punch_in_status int DEFAULT 0 NOT NULL COMMENT '打卡状态(0-进行中，1-完成，2-未完成) ，结算后修改状态，单次打卡除外' AFTER time_track;

update punch_settle.punch_in_record
set punch_in_status = 1;

update punch_settle.punch_in_record record,
punch_settle.punch_in_record_settlement_rela rela,
punch_settle.punch_in pi
set rela.reward_num = pi.reward_num
where rela.record_id = record.id
and record.punch_in_id = pi.id;


ALTER TABLE punch_settle.punch_in_record MODIFY COLUMN count_track int DEFAULT 0 NULL COMMENT '次数记录';
ALTER TABLE punch_settle.punch_in_record MODIFY COLUMN time_track time DEFAULT '00:00:00.000' NULL COMMENT '时间记录';

ALTER TABLE punch_settle.punch_in_record_settlement_rela DROP COLUMN reward_num;
ALTER TABLE punch_settle.punch_in_record_settlement_rela DROP COLUMN category;
ALTER TABLE punch_settle.punch_in_record_settlement_rela DROP COLUMN rule;

ALTER TABLE punch_settle.punch_in_record ADD settle_reward_num int DEFAULT 0 NULL COMMENT '结算时的奖励倍数';
ALTER TABLE punch_settle.punch_in_record ADD settle_category int DEFAULT 0 NULL COMMENT '结算时的打卡类型(0-单次打卡，1-计数、2计时)';
ALTER TABLE punch_settle.punch_in_record ADD settle_rule int DEFAULT 1 NULL COMMENT '结算时的比较规则(0-大于，1-大于等于，2-小于，3-小于等于)';
ALTER TABLE punch_settle.punch_in_record ADD settle_count_track int DEFAULT 0 NULL COMMENT '结算时的次数记录';

ALTER TABLE punch_settle.punch_in_record ADD settle_time_track TIMESTAMP NULL COMMENT '结算时的时间记录';
ALTER TABLE punch_settle.punch_in_record MODIFY COLUMN settle_time_track time DEFAULT '00:00:00.000' NULL COMMENT '结算时的时间记录';

ALTER TABLE punch_settle.punch_in_record CHANGE settle_reward_num settle_reward_num int DEFAULT 0 NULL COMMENT '结算时的奖励倍数' AFTER punch_in_status;
ALTER TABLE punch_settle.punch_in_record CHANGE settle_category settle_category int DEFAULT 0 NULL COMMENT '结算时的打卡类型(0-单次打卡，1-计数、2计时)' AFTER settle_reward_num;
ALTER TABLE punch_settle.punch_in_record CHANGE settle_rule settle_rule int DEFAULT 1 NULL COMMENT '结算时的比较规则(0-大于，1-大于等于，2-小于，3-小于等于)' AFTER settle_category;
ALTER TABLE punch_settle.punch_in_record CHANGE settle_count_track settle_count_track int DEFAULT 0 NULL COMMENT '结算时的次数记录' AFTER settle_rule;
ALTER TABLE punch_settle.punch_in_record CHANGE settle_time_track settle_time_track time DEFAULT '00:00:00' NULL COMMENT '结算时的时间记录' AFTER settle_count_track;

ALTER TABLE punch_settle.punch_in_record MODIFY COLUMN settle_reward_num int DEFAULT 0 NULL COMMENT '结算时任务上的奖励倍数';
ALTER TABLE punch_settle.punch_in_record MODIFY COLUMN settle_category int DEFAULT 0 NULL COMMENT '结算时任务上的打卡类型(0-单次打卡，1-计数、2计时)';
ALTER TABLE punch_settle.punch_in_record MODIFY COLUMN settle_rule int DEFAULT 1 NULL COMMENT '结算时任务上的比较规则(0-大于，1-大于等于，2-小于，3-小于等于)';
ALTER TABLE punch_settle.punch_in_record MODIFY COLUMN settle_count_track int DEFAULT 0 NULL COMMENT '结算时任务上的次数记录';
ALTER TABLE punch_settle.punch_in_record MODIFY COLUMN settle_time_track time DEFAULT '00:00:00' NULL COMMENT '结算时任务上的时间记录';

ALTER TABLE punch_settle.punch_in_record MODIFY COLUMN settle_reward_num int NULL COMMENT '结算时任务上的奖励倍数';
ALTER TABLE punch_settle.punch_in_record MODIFY COLUMN settle_category int NULL COMMENT '结算时任务上的打卡类型(0-单次打卡，1-计数、2计时)';
ALTER TABLE punch_settle.punch_in_record MODIFY COLUMN settle_rule int NULL COMMENT '结算时任务上的比较规则(0-大于，1-大于等于，2-小于，3-小于等于)';
ALTER TABLE punch_settle.punch_in_record MODIFY COLUMN settle_count_track int NULL COMMENT '结算时任务上的次数记录';
ALTER TABLE punch_settle.punch_in_record MODIFY COLUMN settle_time_track time NULL COMMENT '结算时任务上的时间记录';

update punch_settle.punch_in_record
set settle_reward_num = null,
settle_category = null,
settle_rule = null,
settle_count_track = null,
settle_time_track = null;

update punch_settle.punch_in pi, punch_settle.punch_in_record pir
set pir.settle_reward_num = pi.reward_num,
pir.settle_category = pi.category
where pi.id = pir.punch_in_id;

ALTER TABLE punch_settle.lottery_scratch_record DROP COLUMN user_id;

ALTER TABLE punch_settle.user_claim_reward_record DROP COLUMN user_id;

ALTER TABLE punch_settle.lottery_scratch_record ADD INDEX lottery_scratch_record_creation_time (creation_time);
ALTER TABLE punch_settle.lottery_scratch_record ADD INDEX lottery_scratch_record_created_by (created_by);

ALTER TABLE punch_settle.punch_in ADD INDEX punch_in_creation_time (creation_time);
ALTER TABLE punch_settle.punch_in ADD INDEX punch_in_created_by (created_by);

ALTER TABLE punch_settle.punch_in_record ADD INDEX punch_in_record_creation_time (creation_time);
ALTER TABLE punch_settle.punch_in_record ADD INDEX punch_in_record_created_by (created_by);
ALTER TABLE punch_settle.punch_in_record ADD INDEX punch_in_record_punch_in_id (punch_in_id);
ALTER TABLE punch_settle.punch_in_record ADD INDEX punch_in_record_punch_in_date (punch_in_date);

ALTER TABLE punch_settle.punch_in_record_settlement_rela ADD INDEX punch_in_record_settlement_rela_record_id (record_id);
ALTER TABLE punch_settle.punch_in_record_settlement_rela ADD INDEX punch_in_record_settlement_rela_settlement_id (settlement_id);

ALTER TABLE punch_settle.punch_in_settlement ADD INDEX punch_in_settlement_user_id (user_id);
ALTER TABLE punch_settle.punch_in_settlement ADD INDEX punch_in_settlement_settlement_time (settlement_time);

ALTER TABLE punch_settle.user ADD UNIQUE punch_in_settlement_open_id (open_id);

ALTER TABLE punch_settle.user_claim_reward_record ADD INDEX user_claim_reward_record_claim_reward_time (claim_reward_time);
ALTER TABLE punch_settle.user_claim_reward_record ADD INDEX user_claim_reward_record_created_by (created_by);

update lottery_scratch_record
set source = 'WELFARE_SCRATCH'
where id = 1;

-- MySQL dump 10.13  Distrib 8.0.19, for Win64 (x86_64)
--
-- Host: localhost    Database: punch_settle
-- ------------------------------------------------------
-- Server version	8.4.3

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `sys_dict_item`
--

DROP TABLE IF EXISTS `sys_dict_item`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `sys_dict_item` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `dict_id` bigint NOT NULL COMMENT '字典主键',
  `data_type` int NOT NULL DEFAULT '0' COMMENT '数据类型（0-String，1-Number）',
  `item_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '字典项编码',
  `item_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '字典项名称',
  `description` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '描述',
  `status` int NOT NULL DEFAULT '1' COMMENT '状态(1-启用，0-失效)',
  `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=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='系统字典项表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `sys_dict_item`
--

LOCK TABLES `sys_dict_item` WRITE;
/*!40000 ALTER TABLE `sys_dict_item` DISABLE KEYS */;
INSERT INTO `sys_dict_item` VALUES (1,1,0,'WELFARE_SCRATCH','福利彩票',NULL,1,1,'2024-12-12 10:39:00',1,'2024-12-22 02:44:00',1,0),(2,1,0,'SPORTS_SCRATCH','体育彩票',NULL,1,1,'2024-12-12 10:39:56',1,'2024-12-22 02:44:00',1,0),(3,2,0,'XINGYUN88','幸运88',NULL,1,1,'2024-12-12 10:40:06',1,'2024-12-22 02:44:00',1,0),(4,2,0,'CHAOGEILI','超给力',NULL,1,1,'2024-12-12 10:40:06',1,'2024-12-22 02:44:00',1,0),(5,4,1,'0','投入',NULL,1,1,'2024-12-21 13:32:14',1,'2024-12-22 02:44:00',1,0),(6,4,1,'1','中奖',NULL,1,1,'2024-12-21 13:32:14',1,'2024-12-22 02:44:00',1,0),(7,5,1,'0','关闭',NULL,1,1,'2024-12-21 13:32:14',1,'2024-12-22 02:44:00',1,0),(8,5,1,'1','启用',NULL,1,1,'2024-12-21 13:32:14',1,'2024-12-22 02:44:00',1,0),(9,6,1,'0','单次打卡',NULL,1,1,'2024-12-21 13:32:14',1,'2024-12-22 02:44:00',1,0),(10,6,1,'1','计数',NULL,1,1,'2024-12-21 13:32:14',1,'2024-12-22 02:44:00',1,0),(11,6,1,'2','计时',NULL,1,1,'2024-12-21 13:32:14',1,'2024-12-22 02:44:00',1,0),(12,7,1,'0','大于等于',NULL,1,1,'2024-12-21 13:32:14',1,'2024-12-22 02:44:00',1,0),(13,7,1,'1','小于等于',NULL,1,1,'2024-12-21 13:32:14',1,'2024-12-22 02:52:59',1,0),(14,2,0,'XIXIANGFENG','喜相逢',NULL,1,1,'2024-12-12 10:40:06',1,'2024-12-22 02:44:00',1,0);
/*!40000 ALTER TABLE `sys_dict_item` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `sys_dict`
--

DROP TABLE IF EXISTS `sys_dict`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `sys_dict` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `dict_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '字典编码',
  `dict_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '字典名称',
  `description` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '描述',
  `status` int NOT NULL DEFAULT '1' COMMENT '状态(1-启用，0-失效)',
  `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=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='系统字典';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `sys_dict`
--

LOCK TABLES `sys_dict` WRITE;
/*!40000 ALTER TABLE `sys_dict` DISABLE KEYS */;
INSERT INTO `sys_dict` VALUES (1,'LOTTERY_SCRATCH_SOURCE','刮刮乐来源',NULL,1,1,'2024-12-12 10:36:59',1,'2024-12-21 13:27:19',1,0),(2,'WELFARE_SCRATCH','福利彩票刮刮乐种类',NULL,1,1,'2024-12-12 10:38:27',1,'2024-12-21 13:27:19',1,0),(3,'SPORTS_SCRATCH','体育彩票刮刮乐种类',NULL,1,1,'2024-12-21 13:27:12',1,'2024-12-21 13:27:12',1,0),(4,'SCRATCH_ACTION_TYPE','刮刮乐动作类型',NULL,1,1,'2024-12-21 13:27:12',1,'2024-12-21 13:27:12',1,0),(5,'ENABLE_STATUS','启用状态标志',NULL,1,1,'2024-12-21 13:27:12',1,'2024-12-21 13:27:12',1,0),(6,'PUNCH_IN_CATEGORY','打卡类型',NULL,1,1,'2024-12-21 13:27:12',1,'2024-12-21 13:27:12',1,0),(7,'PUNCH_IN_RULE','打卡比较规则',NULL,1,1,'2024-12-21 13:27:19',1,'2024-12-21 13:27:19',1,0);
/*!40000 ALTER TABLE `sys_dict` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-12-22 15:57:44