toktok_stores/stores-parent/doc/sql/ddl/modify/V1.0.sql

INSERT INTO `admin_role` VALUES (1, '管理员', '第二大的权限集合体，仅次于super', 1, 1, '', '2021-01-20 11:34:02');
INSERT INTO `admin_role` VALUES (2, '开发人员', '开发人员，只能看？', 2, 1, '', '2021-01-20 11:34:02');
INSERT INTO `admin_role` VALUES (3, '运营人员', '未分配提现和订单权限', 1, 1, '', '2021-01-20 11:34:03');

INSERT INTO `admin_user`( `account`, `password`, `nameCn`, `nameEn`, `lockFlag`, `delFlag`, `genUid`, `createTime`) VALUES ( 'super', '$2a$10$EfA1I9IMzk9ecGBgT/ig9.vOPbtydB6erkn4117y0s8GI4.V70sHe', '超级管理员', 'superman', 0, 0, 0, now());

INSERT INTO `toktok_stores`.`stores`( `name`, `address`, `manager`, `opt_user`, `create_time`, `update_time`) VALUES ( '米酷店', '广州市天河区万科云城米酷B4栋107-108铺（近天河智慧城地铁B出口）', NULL, '1', now(), NULL);
INSERT INTO `toktok_stores`.`stores`( `name`, `address`, `manager`, `opt_user`, `create_time`, `update_time`) VALUES ( '万胜围店', '广州市海珠区琶洲新村11栋02商铺（近万胜围地铁D出口）', NULL, '1', now(), NULL);
INSERT INTO `toktok_stores`.`stores`( `name`, `address`, `manager`, `opt_user`, `create_time`, `update_time`) VALUES ( '杨箕店', '越秀区杨箕大街富力东山新天地F区商业街109铺', NULL, '1', now(), NULL);

ALTER TABLE `toktok_stores`.`screen_projection`
ADD COLUMN `store_id` bigint(20) NULL COMMENT '门店id' AFTER `id`,
ADD COLUMN `seq` int(10) NOT NULL DEFAULT 0 COMMENT '排序' AFTER `img_url`;

------------------------------------------------------------------------------------------------------
ALTER TABLE `toktok_stores`.`screen_projection`
ADD COLUMN `carousel_figure` varchar(255) NULL COMMENT '轮播图' AFTER `img_url`;

------------------------------------------------------------------------------------------------------

ALTER TABLE `toktok_stores`.`dishes_specification`
CHANGE COLUMN `specification` `specification_id` bigint(20) NULL DEFAULT NULL COMMENT '规格id' AFTER `dishes_id`;

ALTER TABLE `toktok_stores`.`dishes_manage`
DROP COLUMN `status`;

ALTER TABLE `toktok_stores`.`dishes_manage`
DROP COLUMN `seq`,
ADD COLUMN `img` varchar(255) NULL COMMENT '图片链接' AFTER `english_name`;

ALTER TABLE `toktok_stores`.`store_account`
ADD COLUMN `bind_store_id` bigint(20) NULL COMMENT '绑定的门店id' AFTER `status`;

-------------------------------------------------------------------------------------------------------
INSERT INTO `toktok_stores`.`stores`( `name`, `address`, `manager`, `opt_user`, `create_time`, `update_time`) VALUES ( '天健店（广氮花园）', '广州市天河区天健汇商业广场A112铺（广氮花园附近）', NULL, '1', now(), NULL);
INSERT INTO `toktok_stores`.`stores`( `name`, `address`, `manager`, `opt_user`, `create_time`, `update_time`) VALUES ( '保利洲际店','广州市海珠区暄悦西街琶洲新村1栋1001铺之一（近万胜围地铁D出口)', NULL, '1', now(), NULL);
-------------------------------------------------------------------------------------------------------
ALTER TABLE `toktok_stores`.`screen_projection`
ADD COLUMN `projection_time` tinyint(2) NOT NULL DEFAULT 1 COMMENT '投影时间，0白天，1夜晚' AFTER `seq`;

--------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE `toktok_stores`.`screen_projection`
ADD COLUMN `cycle_time` int(3) NOT NULL DEFAULT 5 COMMENT '轮播时间' AFTER `carousel_figure`;

--------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE `toktok_stores`.`dishes_manage`
ADD COLUMN `sale_status` tinyint(3) NOT NULL DEFAULT 1 COMMENT '在售状态，1启售，2停售' AFTER `featured`;
--------------------------------------------
ALTER TABLE `toktok_stores`.`clock_group`
ADD COLUMN `loc_titles` varchar(255) NULL COMMENT '打卡地名，多个用;分隔' AFTER `type`;


ALTER TABLE `toktok_stores`.`group_range`
MODIFY COLUMN `targtId` varchar(32) NOT NULL COMMENT '目标id' AFTER `groupId`;

ALTER TABLE `toktok_stores`.`group_range`
CHANGE COLUMN `groupId` `groupid` bigint(20) NOT NULL AFTER `type`,
CHANGE COLUMN `targtId` `targt_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '目标id' AFTER `groupid`;

ALTER TABLE `toktok_stores`.`schedule`
DROP COLUMN `groupid`;

ALTER TABLE `toktok_stores`.`work_schedule`
DROP COLUMN `m_shift_emp_id`,
DROP COLUMN `af_shift_emp_id`,
DROP COLUMN `ev_shift_emp_id`;

ALTER TABLE `toktok_stores`.`work_schedule`
MODIFY COLUMN `worke_date` int(10) NOT NULL COMMENT '排班日期yyyyMMdd' AFTER `id`;

ALTER TABLE `toktok_stores`.`attendance`
ADD COLUMN `attendance_status` tinyint(3) NULL COMMENT '考勤状态 1正常，2时间异常，3地点异常' AFTER `end_part_time`;

ALTER TABLE `toktok_stores`.`attendance`
MODIFY COLUMN `start_part_time` bigint(20) NULL DEFAULT NULL COMMENT '兼职开始时间,表示为距离当天0点的秒数。' AFTER `type`,
MODIFY COLUMN `end_part_time` bigint(20) NULL DEFAULT NULL COMMENT '兼职结束时间,表示为距离当天0点的秒数。' AFTER `start_part_time`;


ALTER TABLE `toktok_stores`.`attendance`
ADD COLUMN `allocate_department_id` bigint(20) NULL COMMENT '调配的门店id' AFTER `remark`,
ADD COLUMN `allocate_department_name` varchar(32) NULL COMMENT '调配的门店名称' AFTER `allocate_department_id`;

ALTER TABLE `toktok_stores`.`attendance`
ADD UNIQUE INDEX `work_schedule_id_and_employee_id`(`work_schedule_id`, `employe_id`) COMMENT 'work_schedule_id_and_employee_id 唯一索引';

ALTER TABLE `toktok_stores`.`work_schedule`
CHANGE COLUMN `worke_date` `work_date` int(10) NOT NULL COMMENT '排班日期yyyyMMdd' AFTER `id`,
ADD UNIQUE INDEX `work_date_and_department_id_index`(`work_date`, `department_id`) COMMENT 'work_date and department_id 唯一索引';
--------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE `toktok_stores`.`beer_properties`
ADD COLUMN `plato` double(10, 0) NULL COMMENT '麦汁浓度' AFTER `ibu`;

ALTER TABLE `toktok_stores`.`beer_properties`
MODIFY COLUMN `plato` double(10, 0) NULL DEFAULT 0 COMMENT '麦汁浓度' AFTER `ibu`;

ALTER TABLE `toktok_stores`.`beer_manage`
MODIFY COLUMN `material_id` varchar(64) NULL DEFAULT NULL COMMENT '啤酒原料信息id,多个之间用;间隔' AFTER `factory_id`;

ALTER TABLE `toktok_stores`.`beer_manage`
MODIFY COLUMN `dishes_id` bigint(20) NOT NULL FIRST;

update `beer_properties` SET plato = 0;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE `toktok_stores`.`beer_manage`
DROP COLUMN `beer_name`,
CHANGE COLUMN `dishes_id` `id` bigint(20) NOT NULL AUTO_INCREMENT FIRST,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`) USING BTREE;

ALTER TABLE `toktok_stores`.`beer_properties`
CHANGE COLUMN `dishes_id` `beer_id` bigint(20) NOT NULL COMMENT '啤酒id' FIRST,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`beer_id`) USING BTREE;

ALTER TABLE `toktok_stores`.`dishes_manage`
ADD COLUMN `target_id` bigint(20) NULL COMMENT '类型对应的基础数据id' AFTER `type`;

ALTER TABLE `toktok_stores`.`dishes_manage`
ADD UNIQUE INDEX `type_and_target_id_index`(`type`, `target_id`) COMMENT 'type target_id  唯一索引';

-----------------------------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE `toktok_stores`.`stores`
ADD COLUMN `lng` varchar(20) NULL COMMENT '经度' AFTER `address`,
ADD COLUMN `lat` varchar(20) NULL COMMENT '纬度' AFTER `lng`;

ALTER TABLE `toktok_stores`.`stores`
ADD COLUMN `business_start_time` datetime(0) NULL COMMENT '营业开始时间' AFTER `opt_user`,
ADD COLUMN `business_end_time` datetime(0) NULL COMMENT '营业结束时间' AFTER `business_start_time`;


ALTER TABLE `toktok_stores`.`beer_type_manage`
ADD COLUMN `img` varchar(255) NULL COMMENT '图片url' AFTER `introduce`;

ALTER TABLE `toktok_stores`.`stores`
ADD COLUMN `status` tinyint(4) NOT NULL DEFAULT 1 COMMENT '1正常;2停业' AFTER `business_end_time`;

--------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE `toktok_stores`.`quanliang_group`
ADD COLUMN `status` tinyint(3) NULL COMMENT '1正常，2已删除' AFTER `is_watch`;

ALTER TABLE `toktok_stores`.`banner`
MODIFY COLUMN `jump_url` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '跳转地址' AFTER `jump_type`;

---------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE `toktok_stores`.`meal_standard`
ADD COLUMN `status` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态1在用，2已删除' AFTER `comment_id`;

ALTER TABLE `toktok_stores`.`meal_standard`
ADD COLUMN `sort` int(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '排序权重' AFTER `comment_id`;

---------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE `toktok_stores`.`meal_standard`
ADD COLUMN `key_word` varchar(255) NULL COMMENT '关键词' AFTER `sort`;
---------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE `toktok_stores`.`stores`
ADD COLUMN `department_id` bigint(20) NULL COMMENT '部门id' AFTER `jump_url`;

ALTER TABLE `toktok_stores`.`employee`
ADD COLUMN `pwd` varchar(18) NULL COMMENT '登录密码' AFTER `main_department`,
ADD COLUMN `temp_exp_date` datetime(0) NULL COMMENT '临时身份过期时间' AFTER `pwd`,
ADD COLUMN `inc_date` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间' AFTER `temp_exp_date`;

----------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE `toktok_stores`.`dishes_specification`
ADD COLUMN `vip_price` decimal(10, 2) NULL COMMENT '会员价' AFTER `price`;

--------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE `toktok_stores`.`stores`
ADD COLUMN `paper_menu` varchar(800) NULL COMMENT '纸质菜单' AFTER `manager`;

--------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE `toktok_stores`.`quanliang_group`
ADD COLUMN `group_chat_id` varchar(64) NULL COMMENT '企业微信群组id' AFTER `group_id`,
ADD COLUMN `owner_userid` varchar(64) NULL COMMENT '群主' AFTER `owner_name`;

ALTER TABLE `toktok_stores`.`group_chat_send_msg`
ADD COLUMN `send_date` varchar(10) NOT NULL COMMENT '指定消息发送的日期' AFTER `sender_userid`;

---------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE `toktok_stores`.`screen_projection`
ADD COLUMN `bg_img_url` varchar(255) NULL COMMENT '背景图片' AFTER `img_url`,
ADD COLUMN `video_url` varchar(255) NULL COMMENT '视频地址' AFTER `bg_img_url`,
ADD COLUMN `view_type` tinyint(4) NOT NULL DEFAULT 0 COMMENT '展示类型' AFTER `status`;

update screen_projection set view_type = 1 where img_url is null or img_url = "";
update screen_projection set view_type = 2 where (img_url is not null and img_url != "")
and (carousel_figure != "" and carousel_figure is not null);
update screen_projection set view_type = 0  where (img_url is not null and img_url != "")
and (carousel_figure = "" or carousel_figure is null);

ALTER TABLE `toktok_stores`.`screen_projection`
ADD COLUMN `gen_type` tinyint(4) NOT NULL DEFAULT 0 COMMENT '自动生成类型' AFTER `view_type`;

update screen_projection set gen_type = 1 where serial_number like '%1' and projection_time = 1;
update screen_projection set gen_type = 1 where serial_number like '%2' and projection_time = 1;
update screen_projection set gen_type = 2 where serial_number like '%3' and projection_time = 1;
update screen_projection set gen_type = 4 where serial_number like '%4' and projection_time = 1;
update screen_projection set gen_type = 3 where serial_number like '%2' and projection_time = 0;
update screen_projection set gen_type = 4 where serial_number like '%3' and projection_time = 0;
update screen_projection set gen_type = 2 where serial_number like '%4' and projection_time = 0;

INSERT INTO `toktok_stores`.`sys_config`( `name`, `value`, `des`, `create_time`, `update_time`) VALUES ('store_menu_day_coffee', 'https://cdn.toktok.beer/menu/fixe_menu/20230525/coffee_nuode.png', '门店白天咖啡菜单', now(), NULL);
INSERT INTO `toktok_stores`.`sys_config`( `name`, `value`, `des`, `create_time`, `update_time`) VALUES ('store_menu_day_fruit_tea', 'https://cdn.toktok.beer/menu/fixe_menu/20230504/tea_menu2.png', '门店白天茶饮菜单', now(), NULL);
INSERT INTO `toktok_stores`.`sys_config`( `name`, `value`, `des`, `create_time`, `update_time`) VALUES ('store_menu_night_fruit_tea', 'https://cdn.toktok.beer/menu/fixe_menu/20230529/night_tea_menu.png', '门店夜晚茶饮菜单', now(), NULL);

--------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE `toktok_stores`.`backlog`
ADD COLUMN `start_time` datetime(0) NOT NULL COMMENT '开始时间' AFTER `type`,
ADD COLUMN `tip_time` datetime(0) NOT NULL COMMENT '提醒时间' AFTER `start_time`;

ALTER TABLE `toktok_stores`.`backlog_bind`
MODIFY COLUMN `imgs` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '图片链接' AFTER `backlog_id`,
ADD COLUMN `start_time` datetime(0) NOT NULL COMMENT '开始时间' AFTER `status`,
ADD COLUMN `tip_time` datetime(0) NOT NULL COMMENT '提醒时间' AFTER `start_time`,
ADD COLUMN `submit_emp_id` bigint(20) NULL COMMENT '提交人' AFTER `tip_time`,
ADD COLUMN `finish_time` datetime(0) NULL COMMENT '完成时间' AFTER `submit_emp_id`;

ALTER TABLE `toktok_stores`.`backlog`
DROP COLUMN `type`,
DROP COLUMN `start_time`,
DROP COLUMN `tip_time`;

ALTER TABLE `toktok_stores`.`backlog_loop_bind`
MODIFY COLUMN `loop_type` tinyint(4) NOT NULL DEFAULT 0 COMMENT '循环类型' AFTER `store_id`,
ADD COLUMN `status` tinyint(4) NOT NULL DEFAULT 0 COMMENT '状态' AFTER `loop_type`;

ALTER TABLE `toktok_stores`.`backlog_loop_bind`
CHANGE COLUMN `store_backlog_loop_id` `backlog_loop_bind_id` bigint(20) NOT NULL AUTO_INCREMENT FIRST,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`backlog_loop_bind_id`) USING BTREE;

ALTER TABLE `toktok_stores`.`backlog_bind`
CHANGE COLUMN `store_backlog_id` `backlog_bind_id` bigint(20) NOT NULL AUTO_INCREMENT FIRST,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`backlog_bind_id`) USING BTREE;

ALTER TABLE `toktok_stores`.`backlog_loop_bind`
CHANGE COLUMN `store_id` `busi_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '业务id' AFTER `backlog_id`,
ADD COLUMN `bind_type` tinyint(4) NOT NULL DEFAULT 0 COMMENT '绑定类型' AFTER `backlog_id`;

ALTER TABLE `toktok_stores`.`backlog_bind`
CHANGE COLUMN `store_id` `busi_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '门店id' AFTER `backlog_bind_id`,
ADD COLUMN `bind_type` tinyint(4) NOT NULL DEFAULT 0 COMMENT '绑定类型' AFTER `backlog_bind_id`;

ALTER TABLE `toktok_stores`.`backlog_bind`
ADD COLUMN `backlog_loop_bind_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '分发绑定待办事项id' AFTER `backlog_id`;

ALTER TABLE `toktok_stores`.`backlog`
CHANGE COLUMN `tittle` `title` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '事项标题' AFTER `backlog_id`;

ALTER TABLE `toktok_stores`.`backlog_bind`
DROP COLUMN `tip_time`,
ADD COLUMN `cutoff_time` datetime(0) NOT NULL COMMENT '截止时间' AFTER `start_time`;

ALTER TABLE `toktok_stores`.`backlog_loop_bind`
CHANGE COLUMN `tip_time` `cutoff_time` datetime(0) NOT NULL COMMENT '截止时间' AFTER `start_time`;

--------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE `toktok_stores`.`stores`
ADD COLUMN `group_live_code` varchar(255) NULL COMMENT '企业微信群活码' AFTER `status`;

--------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE `toktok_stores`.`group_chat_send_msg`
ADD COLUMN `quanliang_group_id` bigint(20) NULL COMMENT '圈量群组id' AFTER `imgs`;

--------------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE `toktok_stores`.`train_record`
ADD COLUMN `train_plan_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '培训计划编号' AFTER `train_record_id`;

INSERT INTO `toktok_stores`.`sys_config`(`id`, `name`, `value`, `des`, `create_time`, `update_time`) VALUES (15, 'question_template_url', 'https://cdn.toktok.beer/stores/question/template/ce07a810d3f84661baa865464ee98a2d.xlsx', '题库模版链接', '2023-10-11 16:32:36', NULL);
INSERT INTO `toktok_stores`.`sys_config`(`id`, `name`, `value`, `des`, `create_time`, `update_time`) VALUES (16, 'reimbursement_process_url', 'https://cdn.toktok.beer/stores/reimbursement_process/5a3ba1a0702d4ee898edd93f9ee296c8.pdf', '报销流程', '2023-10-11 17:05:48', NULL);

ALTER TABLE `toktok_stores`.`answer_record`
ADD COLUMN `all_opt` varchar(255) NULL COMMENT '所有选项json串' AFTER `right_answer`,
ADD COLUMN `right_opt_ids` varchar(255) NULL COMMENT '正确选项id列表，多个之间用;分隔' AFTER `all_opt`;

ALTER TABLE `toktok_stores`.`train_plan`
ADD COLUMN `status` tinyint(3) NOT NULL COMMENT '可用状态' AFTER `answer_time`;

ALTER TABLE `toktok_stores`.`train_plan`
ADD COLUMN `pass_score` int(10) NOT NULL DEFAULT 0 COMMENT '及格分数' AFTER `answer_time`;

ALTER TABLE `toktok_stores`.`train_record`
ADD COLUMN `pass_status` tinyint(3) NOT NULL DEFAULT 0 COMMENT '及格状态' AFTER `score`;

ALTER TABLE `toktok_stores`.`train_record`
MODIFY COLUMN `score` int(10) NOT NULL DEFAULT '0' COMMENT '分数' AFTER `employee_id`;

ALTER TABLE `toktok_stores`.`train_record`
ADD COLUMN `question_num` int(0) NOT NULL DEFAULT 0 COMMENT '问题数' AFTER `employee_id`,
ADD COLUMN `curr_answer_num` int(0) NOT NULL DEFAULT 0 COMMENT '当前答题数' AFTER `question_num`;

ALTER TABLE `toktok_stores`.`answer_record`
ADD COLUMN `employee_id` bigint(20) NOT NULL AFTER `train_record_id`;

ALTER TABLE `toktok_stores`.`answer_record`
ADD UNIQUE INDEX `record_and_employee_and_question_index`(`train_record_id`, `employee_id`, `question_id`) COMMENT '培训记录id 员工id 问题id 唯一索引';

--------------------------------------------------------------------------------------------------------------------------------

ALTER TABLE `toktok_stores`.`meal_standard_permission`
ADD COLUMN `employee_role_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '角色id' AFTER `meal_standard_ids`;

ALTER TABLE `toktok_stores`.`employee`
ADD COLUMN `employee_role_id` bigint(20) NULL DEFAULT 0 COMMENT '员工角色id' AFTER `extattr`;

ALTER TABLE `toktok_stores`.`meal_standard_permission`
MODIFY COLUMN `meal_standard_ids` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '出餐规范节点id，用;分隔' AFTER `meal_standard_permission_id`;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ALTER TABLE `toktok_stores`.`stores_dishes`
ADD COLUMN `menu_type` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '菜单类型' AFTER `dishes_id`;

ALTER TABLE `toktok_stores`.`stores`
MODIFY COLUMN `paper_menu` varchar(1500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '纸质菜单' AFTER `manager`;

ALTER TABLE `toktok_stores`.`stores_dishes`
DROP INDEX `stores_dishes_index`,
ADD INDEX `stores_dishes_index`(`store_id`, `dishes_id`) USING BTREE COMMENT '门店id菜品id索引';