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 '结算时任务上的时间记录';