java-bbc/db/2024-02-26-客服改造.sql

# ！！看注释
# 安装wukongim，具体安装参考mall4d-bbc项目中的docker-compose配置或项目中[安装WuKongIm.md]文件

# 安装完成后使用postman或使用curl发送请求到悟空im添加机器人
# 请求格式为
# curl -H "Content-Type: application/json" -H "token: hn02le.34lkdLKD" -X POST -d '{"uids": ["-1_SYSTEM_UID_0"]}' "http://localhost:5001/user/systemuids_add"
# 返回"{ "status": 200 }"即表示设置成功，下述为参数详解
# -H 'token: hn02le.34lkdLKD'  其中hn02le.34lkdLKD为默认配置的token，在docker-compose中配置
# "uids": ["-1_SYSTEM_UID_0"] 其中-1_SYSTEM_UID_0为WuKongConstant.java里面SYSTEM_UID的值
# localhost: 修改为部署悟空im服务的服务器地址
# 5001为http api 端口，在wk.yaml中的httpAddr配置，默认为5001

USE yami_bbc;
# 移除不用表
DROP TABLE IF EXISTS `tz_im_msg_biz_employee_recent`;
DROP TABLE IF EXISTS `tz_im_msg_biz_user`;
DROP TABLE IF EXISTS `tz_im_msg_biz_user_history`;
DROP TABLE IF EXISTS `tz_im_msg_biz_skills`;

# 新增表
DROP TABLE IF EXISTS `tz_im_auto_reply`;
CREATE TABLE `tz_im_auto_reply` (
    `auto_reply_id` bigint NOT NULL AUTO_INCREMENT COMMENT '自动回复id',
    `shop_id` bigint NOT NULL COMMENT '店铺id',
    `content` text COLLATE utf8mb4_general_ci COMMENT '自动回复内容',
    `status` tinyint NOT NULL DEFAULT '0' COMMENT '启用状态 0否1是',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`auto_reply_id`),
    KEY `shop_id` (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='客服自动回复';

DROP TABLE IF EXISTS `tz_im_channel`;
CREATE TABLE `tz_im_channel` (
    `channel_id` varchar(255) COLLATE utf8mb4_general_ci NOT NULL COMMENT '频道id',
    `shop_id` bigint NOT NULL COMMENT '店铺id',
    `user_id` varchar(100) COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户id',
    `employee_id` bigint NOT NULL COMMENT '白名单中管理员id',
    `subscribers` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订阅者id列表',
    `user_read_seq` bigint NOT NULL DEFAULT '0' COMMENT '用户最终已读消息序号',
    `shop_read_seq` bigint DEFAULT '0' COMMENT '商家/平台最终已读消息序号',
    `send_auto_reply` tinyint DEFAULT '1' COMMENT '商家/平台最后一条是否为自动回复信息 0否1是',
    `person_reply_time` datetime DEFAULT NULL COMMENT '商家/平台人工回复时间',
    PRIMARY KEY (`channel_id`),
    KEY `shop_id` (`shop_id`),
    KEY `user_id` (`user_id`),
    KEY `shop_id_employee_id` (`shop_id`,`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='频道关联表';


# 修改初始化数据
UPDATE tz_shop_detail SET user_id = '1' WHERE shop_id = 1;

# 更新权限
# 923为原平台菜单中url为'sys/message/im-msg-biz-skills/index'也即自动回复的菜单
DELETE FROM tz_sys_menu_lang WHERE `menu_id` IN (SELECT `menu_id` FROM tz_sys_menu WHERE `parent_id` = 923);
DELETE FROM tz_sys_menu WHERE `parent_id` = 923;
# 476为原商家菜单中url为'sys/message-manager/im-msg-biz-skills/index'也即自动回复的菜单
DELETE FROM tz_shop_menu_lang WHERE `menu_id` IN (SELECT `menu_id` FROM tz_shop_menu WHERE `parent_id` = 476);
DELETE FROM tz_shop_menu WHERE `parent_id` = 476;

# 新增权限
# 791为原平台菜单中url为'/imBox'也即消息盒子的菜单
INSERT INTO `tz_sys_menu`(`menu_id`,`parent_id`,`url`,`perms`,`type`,`icon`,`order_num`,`hidden`) VALUES
(1177,923,'','platform:imAutoReply:get',2,'',0,0),
(1178,923,'','platform:imAutoReply:save',2,'',0,0),
(1179,923,'','platform:imAutoReply:update',2,'',0,0),
(1181,791,'','platform:im:listTransfer',2,'',0,0),
(1182,791,'','platform:im:transfer',2,'',0,0),
(1183,791,'','platform:im:listConversation',2,'',0,0),
(1184,791,'','platform:im:listMessage',2,'',0,0),
(1185,791,'','platform:im:getInfo',2,'',0,0),
(1187,0,'','',0,'service',12,0);
INSERT INTO `tz_sys_menu_lang` (`menu_id`, `lang`, `name`) VALUES
(1177, 0, '查看'),
(1178, 0, '保存'),
(1179, 0, '更新'),
(1181, 0, '获取转接客服列表'),
(1182, 0, '转接客服'),
(1183, 0, '获取最近会话列表'),
(1184, 0, '获取频道消息列表'),
(1185, 0, '获取频道信息'),
(1187,0,'客服'),
(1187,1,'message');

# 216为原商家菜单中url为'message-box'也即消息盒子的菜单
INSERT INTO `tz_shop_menu`(`menu_id`,`parent_id`,`url`,`perms`,`type`,`icon`,`order_num`,`hidden`) VALUES
(723,476,'','shop:imAutoReply:get',2,'',0,0),
(724,476,'','shop:imAutoReply:save',2,'',0,0),
(725,476,'','shop:imAutoReply:update',2,'',0,0),
(727,216,'','shop:im:listTransfer',2,'',0,0),
(728,216,'','shop:im:transfer',2,'',0,0),
(729,216,'','shop:im:listConversation',2,'',0,0),
(730,216,'','shop:im:listMessage',2,'',0,0),
(731,216,'','shop:im:getInfo',2,'',0,0),
(732,0,'','',0,'service',12,0);
INSERT INTO `tz_shop_menu_lang` (`menu_id`, `lang`, `name`) VALUES
(723, 0, '查看'),
(724, 0, '保存'),
(725, 0, '更新'),
(727, 0, '获取转接客服列表'),
(728, 0, '转接客服'),
(729, 0, '获取最近会话列表'),
(730, 0, '获取频道消息列表'),
(731, 0, '获取频道信息'),
(732,0,'客服'),
(732,1,'message');

ALTER TABLE `tz_shop_company`
    MODIFY COLUMN `start_time` datetime DEFAULT NULL COMMENT '起始时间';


# 修改平台菜单，添加默认组件，移动轮播图
INSERT INTO `tz_sys_menu`(`menu_id`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `hidden`)
VALUES (1186, 865, '', '', 0, '', 2, 0);

INSERT INTO `tz_sys_menu_lang`(`menu_id`, `lang`, `name`)
VALUES (1186, 0, '默认组件');

UPDATE `tz_sys_menu`
SET `parent_id` = 1186,
    `url`       = 'fitment/default/index-img/index',
    `perms`     = '',
    `type`      = 1,
    `icon`      = '',
    `order_num` = 0,
    `hidden`    = 0
WHERE `menu_id` = 462;

# 修改商家菜单，添加默认组件，移动轮播图
INSERT INTO `tz_shop_menu`(`menu_id`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `hidden`)
VALUES (741, 351, '', '', 0, '', 2, 0);

INSERT INTO `tz_shop_menu_lang`(`menu_id`, `lang`, `name`)
VALUES (741, 0, '默认组件');

UPDATE `tz_shop_menu`
SET `parent_id` = 741,
    `url`       = 'fitment/default/index-img/index',
    `perms`     = '',
    `type`      = 1,
    `icon`      = '',
    `order_num` = 0,
    `hidden`    = 0
WHERE `menu_id` = 17;