42 lines
1.5 KiB
SQL
42 lines
1.5 KiB
SQL
-- +goose Up
|
|
|
|
-- 客服会话按业务场景拆分（同一用户可同时有平台客服 / 摸大红客服等）
|
|
ALTER TABLE chat_conversations
|
|
ADD COLUMN support_scene VARCHAR(32) NOT NULL DEFAULT '' COMMENT '客服场景: general/mohong 等，非客服会话为空' AFTER type;
|
|
|
|
UPDATE chat_conversations
|
|
SET support_scene = 'general'
|
|
WHERE type = 'general_support' AND (support_scene = '' OR support_scene IS NULL);
|
|
|
|
CREATE INDEX idx_chat_conversations_type_scene ON chat_conversations (type, support_scene);
|
|
|
|
-- 摸大红客服分组（成员可在后台「客服分组」中调整；默认挂入所有 cs 角色）
|
|
INSERT INTO chat_support_groups (code, name, description, status, sort_order) VALUES
|
|
('mohong', '摸大红-客服', '用户从摸大红入口联系客服时，按本组成员在线与负载分配', 'active', 30)
|
|
ON DUPLICATE KEY UPDATE
|
|
name = VALUES(name),
|
|
description = VALUES(description),
|
|
sort_order = VALUES(sort_order);
|
|
|
|
INSERT IGNORE INTO chat_support_group_members (group_id, admin_user_id)
|
|
SELECT g.id, au.id
|
|
FROM chat_support_groups g
|
|
JOIN admin_user_roles aur ON 1 = 1
|
|
JOIN roles r ON r.id = aur.role_id
|
|
JOIN admin_users au ON au.id = aur.admin_user_id
|
|
WHERE g.code = 'mohong'
|
|
AND r.code = 'cs'
|
|
AND au.status = 'active';
|
|
|
|
-- +goose Down
|
|
|
|
DELETE gm FROM chat_support_group_members gm
|
|
JOIN chat_support_groups g ON g.id = gm.group_id
|
|
WHERE g.code = 'mohong';
|
|
|
|
DELETE FROM chat_support_groups WHERE code = 'mohong';
|
|
|
|
DROP INDEX idx_chat_conversations_type_scene ON chat_conversations;
|
|
|
|
ALTER TABLE chat_conversations DROP COLUMN support_scene;
|