ylrz_saas_pharmacy/java/sql/lobster_menu_canonical_init.sql

-- ============================================================================
-- 数字员工菜单规范同步初始化脚本（2026-06-27）
-- 与 fs-agent V20260727_01 / ApplyLobsterCanonicalMenus 配套
--
-- 销售端 company_menu / tenant_company_menu 保留 8 项 + 节点详情 29926 等：
--   32481,29949,29905,29950,29934,29906,3008,29912
-- 管理端 sys_menu / tenant_sys_menu 额外保留管理专属子菜单：
--   29933,29925,29963,29964,29951,29907,29908,29909,29965,29966
-- 下线冗余/重复菜单：29937,29939,29947,29938,29944,29936,29946,
--   29948,29922,29920,29941,29923,29924,29940,29942,29943,29945,3006,3007 等
--
-- 使用说明：
--   主库模板库执行 PART A
--   租户库 ylrz_saas 逐租户执行 PART B
-- ============================================================================

-- ######################################################################
-- PART A ：主库 sys_menu + company_menu
-- ######################################################################
-- A.1 清理角色关联中的非白名单菜单
DELETE FROM sys_role_menu WHERE menu_id IN (
  SELECT menu_id FROM (
    SELECT menu_id FROM sys_menu
    WHERE (menu_id = 29900 OR parent_id = 29900 OR parent_id = 29901)
      AND menu_id NOT IN (
        29900,32481,29949,29905,29950,29934,29906,3008,29912,29926,
        29933,29925,29963,29964,29951,29907,29908,29909,29965,29966
      )
  ) t
);

DELETE FROM company_role_menu WHERE menu_id IN (
  SELECT menu_id FROM (
    SELECT menu_id FROM company_menu
    WHERE (menu_id = 29900 OR parent_id = 29900 OR parent_id = 29901)
      AND menu_id NOT IN (29900,32481,29949,29905,29950,29934,29906,3008,29912,29926)
  ) t
);

-- A.2 删除非白名单菜单项
DELETE FROM sys_menu
WHERE (menu_id = 29900 OR parent_id = 29900 OR parent_id = 29901)
  AND menu_id NOT IN (
    29900,32481,29949,29905,29950,29934,29906,3008,29912,29926,
    29933,29925,29963,29964,29951,29907,29908,29909,29965,29966
  );

DELETE FROM sys_menu
WHERE menu_id IN (29937, 29939)
   OR path IN ('evolution', 'token-stats')
   OR component IN ('lobster/evolution/index', 'lobster/token-stats/index');

DELETE FROM company_menu
WHERE (menu_id = 29900 OR parent_id = 29900 OR parent_id = 29901)
  AND menu_id NOT IN (29900,32481,29949,29905,29950,29934,29906,3008,29912,29926);

DELETE FROM company_menu
WHERE menu_id IN (29937, 29939,29925,29951,29963,29964,29907,29908,29909,29933,29965,29966)
   OR path IN ('evolution', 'token-stats','semantic-keywords','sensitive-words','prompt','sales-corpus','api-registry','qdrant','industry-corpus-matrix','udd-debug');

-- A.3 重建 sys_menu（UNHEX 保证中文名称）
REPLACE INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, menu_type, icon, visible, status, is_frame, is_cache, perms, create_by, create_time, remark) VALUES
(29900, CONVERT(UNHEX('4149E695B0E5AD97E59198E5B7A5') USING utf8mb4), 0, 100, '/lobster', '', 'M', 'el-icon-cpu', '0', '0', 0, 0, NULL, 'admin', NOW(), 'AI Digital Employee'),
(32481, CONVERT(UNHEX('E5B7A5E4BD9CE6B581E6A8A1E69DBF') USING utf8mb4), 29900, 1, 'workflowLobster', 'company/workflowLobster/index', 'C', 'el-icon-share', '0', '0', 0, 0, 'workflow:lobster:edit', 'admin', NOW(), NULL),
(29949, CONVERT(UNHEX('E5B7A5E4BD9CE6B581E689A7E8A18C') USING utf8mb4), 29900, 2, 'workflow-execution', 'lobster/workflow-execution/index', 'C', 'el-icon-guide', '0', '0', 0, 0, 'workflow:lobster:edit', 'admin', NOW(), NULL),
(29905, CONVERT(UNHEX('E5AE9EE4BE8BE79B91E68EA7') USING utf8mb4), 29900, 3, 'instance', 'lobster/instance/index', 'C', 'el-icon-monitor', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), NULL),
(29950, CONVERT(UNHEX('E5AEA2E688B7E794BBE5838F') USING utf8mb4), 29900, 4, 'profile', 'lobster/profile/hub', 'C', 'el-icon-user', '0', '0', 0, 0, 'workflow:lobster:profile', 'admin', NOW(), NULL),
(29934, CONVERT(UNHEX('E5AFB9E5A496E8A792E889B2E9858DE7BDAE') USING utf8mb4), 29900, 5, 'identity-config', 'lobster/identity-config/index', 'C', 'el-icon-view', '0', '0', 0, 0, 'workflow:lobster:identity', 'admin', NOW(), NULL),
(29906, CONVERT(UNHEX('E8AF9DE69CAFE4BC98E58C96E5AEA1E6A0B8') USING utf8mb4), 29900, 6, 'optimization', 'lobster/optimization/index', 'C', 'el-icon-eye-open', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), 'unified optimization audit'),
(3008, CONVERT(UNHEX('E58AA8E68081E88A82E782B9E5AEA1E689B9') USING utf8mb4), 29900, 7, 'dynamic-impl', 'lobster/dynamic-impl/index', 'C', 'el-icon-connection', '0', '0', 0, 0, 'workflow:lobster:audit', 'admin', NOW(), NULL),
(29912, CONVERT(UNHEX('E8819AE59088E8818AE5A4A9') USING utf8mb4), 29900, 8, 'chat-aggregate', 'lobster/chat-aggregate/index', 'C', 'el-icon-message', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), NULL),
(29926, CONVERT(UNHEX('E88A82E782B9E8AFA6E68385') USING utf8mb4), 29900, 99, 'node-detail', 'lobster/node-detail/index', 'C', 'el-icon-document', '1', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), 'hidden route'),
(29933, CONVERT(UNHEX('E8AFADE4B989E585B3E994AEE8AF8D') USING utf8mb4), 29900, 19, 'semantic-keywords', 'lobster/semantic-keywords/index', 'C', 'el-icon-key', '0', '0', 0, 0, 'workflow:lobster:semantic', 'admin', NOW(), 'lobster_tenant_keywords'),
(29925, CONVERT(UNHEX('E6958FE6849FE8AF8DE5BA93') USING utf8mb4), 29900, 20, 'sensitive-words', 'lobster/sensitive-words/index', 'C', 'el-icon-warning', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), NULL),
(29963, CONVERT(UNHEX('E8BF9DE7A681E8AF8DE5BA93') USING utf8mb4), 29900, 21, 'forbidden-words', 'lobster/replace-words/index', 'C', 'el-icon-warning', '0', '0', 0, 0, 'workflow:lobster:replace', 'admin', NOW(), NULL),
(29964, CONVERT(UNHEX('E69893E99499E8AF8DE5BA93') USING utf8mb4), 29900, 22, 'typo-words', 'lobster/replace-words/index', 'C', 'el-icon-edit', '0', '0', 0, 0, 'workflow:lobster:replace', 'admin', NOW(), NULL),
(29951, CONVERT(UNHEX('E59091E9878FE79FA5E8AF86E5BA93') USING utf8mb4), 29900, 23, 'qdrant', 'lobster/qdrant/index', 'C', 'el-icon-collection', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), 'Qdrant vector KB'),
(29907, CONVERT(UNHEX('E68F90E7A4BAE8AF8DE7AEA1E79086') USING utf8mb4), 29900, 24, 'prompt', 'lobster/prompt/index', 'C', 'el-icon-edit', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), NULL),
(29908, CONVERT(UNHEX('E99480E586A0E8AFADE69699E5ADA6E4B9A0') USING utf8mb4), 29900, 25, 'sales-corpus', 'lobster/sales-corpus/index', 'C', 'el-icon-star', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), NULL),
(29909, CONVERT(UNHEX('E68EA5E58FA3E6B3A8E5868CE4B8ADE5BF83') USING utf8mb4), 29900, 26, 'api-registry', 'lobster/api-registry/index', 'C', 'el-icon-nested', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), NULL),
(29965, CONVERT(UNHEX('E8A18CE4B89AE8AFADE69699E79FA9E998B5') USING utf8mb4), 29900, 27, 'industry-corpus-matrix', 'lobster/industry-corpus-matrix/index', 'C', 'el-icon-grid', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), 'UDD industry corpus matrix'),
(29966, CONVERT(UNHEX('E6B7B1E5BAA6E5AFB9E8AF9DE8B083E8AF95') USING utf8mb4), 29900, 28, 'udd-debug', 'lobster/udd-debug/index', 'C', 'el-icon-bug', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), 'UDD debug');

-- A.4 同步 company_menu（销售端子集）
INSERT IGNORE INTO company_menu (menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, company_id, remark)
SELECT s.menu_id, s.menu_name, s.parent_id, s.order_num, s.path, s.component, s.is_frame, s.is_cache, s.menu_type, s.visible, s.status, s.perms, s.icon, s.create_by, s.create_time, c.company_id, s.remark
FROM sys_menu s
CROSS JOIN (SELECT DISTINCT company_id FROM company_menu WHERE company_id IS NOT NULL) c
WHERE s.menu_id IN (29900,32481,29949,29905,29950,29934,29906,3008,29912,29926)
  AND NOT EXISTS (SELECT 1 FROM company_menu x WHERE x.menu_id = s.menu_id AND x.company_id = c.company_id);

UPDATE company_menu cm
INNER JOIN sys_menu sm ON cm.menu_id = sm.menu_id
SET cm.menu_name = sm.menu_name, cm.parent_id = sm.parent_id, cm.order_num = sm.order_num,
    cm.path = sm.path, cm.component = sm.component, cm.is_frame = sm.is_frame, cm.is_cache = sm.is_cache,
    cm.menu_type = sm.menu_type, cm.visible = sm.visible, cm.status = sm.status, cm.perms = sm.perms,
    cm.icon = sm.icon, cm.remark = sm.remark
WHERE sm.menu_id IN (29900,32481,29949,29905,29950,29934,29906,3008,29912,29926);

DELETE FROM company_menu
WHERE (menu_id = 29900 OR parent_id = 29900)
  AND menu_id NOT IN (29900,32481,29949,29905,29950,29934,29906,3008,29912,29926);

-- A.5 补全角色授权
INSERT IGNORE INTO sys_role_menu (role_id, menu_id)
SELECT r.role_id, s.menu_id FROM sys_role_menu r CROSS JOIN sys_menu s
WHERE r.menu_id = 29900 AND s.parent_id = 29900 AND s.menu_type = 'C' AND s.visible = '0'
  AND NOT EXISTS (SELECT 1 FROM sys_role_menu x WHERE x.role_id = r.role_id AND x.menu_id = s.menu_id);

INSERT IGNORE INTO company_role_menu (role_id, menu_id)
SELECT r.role_id, s.menu_id FROM company_role_menu r CROSS JOIN sys_menu s
WHERE r.menu_id = 29900 AND s.parent_id = 29900 AND s.menu_type = 'C' AND s.visible = '0'
  AND s.menu_id IN (29900,32481,29949,29905,29950,29934,29906,3008,29912)
  AND NOT EXISTS (SELECT 1 FROM company_role_menu x WHERE x.role_id = r.role_id AND x.menu_id = s.menu_id);


-- ######################################################################
-- PART B ：逐租户 tenant_sys_menu + tenant_company_menu（模板库执行）
-- ######################################################################

-- ######################################################################

DELETE FROM tenant_sys_menu
WHERE (menu_id = 29900 OR parent_id = 29900 OR parent_id = 29901)
  AND menu_id NOT IN (
    29900,32481,29949,29905,29950,29934,29906,3008,29912,29926,
    29933,29925,29963,29964,29951,29907,29908,29909,29965,29966
  );

DELETE FROM tenant_sys_menu
WHERE menu_id IN (29937, 29939)
   OR path IN ('evolution', 'token-stats')
   OR component IN ('lobster/evolution/index', 'lobster/token-stats/index');

DELETE FROM tenant_company_menu
WHERE (menu_id = 29900 OR parent_id = 29900 OR parent_id = 29901)
  AND menu_id NOT IN (29900,32481,29949,29905,29950,29934,29906,3008,29912,29926);

DELETE FROM tenant_company_menu
WHERE menu_id IN (29937, 29939,29925,29951,29963,29964,29907,29908,29909,29933,29965,29966)
   OR path IN ('evolution', 'token-stats','semantic-keywords','sensitive-words','prompt','sales-corpus','api-registry','qdrant','industry-corpus-matrix','udd-debug');

REPLACE INTO tenant_sys_menu (menu_id, menu_name, parent_id, order_num, path, component, menu_type, icon, visible, status, is_frame, is_cache, perms, create_by, create_time, remark) VALUES
(29900, CONVERT(UNHEX('4149E695B0E5AD97E59198E5B7A5') USING utf8mb4), 0, 100, '/lobster', '', 'M', 'el-icon-cpu', '0', '0', 0, 0, NULL, 'admin', NOW(), 'AI Digital Employee'),
(32481, CONVERT(UNHEX('E5B7A5E4BD9CE6B581E6A8A1E69DBF') USING utf8mb4), 29900, 1, 'workflowLobster', 'company/workflowLobster/index', 'C', 'el-icon-share', '0', '0', 0, 0, 'workflow:lobster:edit', 'admin', NOW(), NULL),
(29949, CONVERT(UNHEX('E5B7A5E4BD9CE6B581E689A7E8A18C') USING utf8mb4), 29900, 2, 'workflow-execution', 'lobster/workflow-execution/index', 'C', 'el-icon-guide', '0', '0', 0, 0, 'workflow:lobster:edit', 'admin', NOW(), NULL),
(29905, CONVERT(UNHEX('E5AE9EE4BE8BE79B91E68EA7') USING utf8mb4), 29900, 3, 'instance', 'lobster/instance/index', 'C', 'el-icon-monitor', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), NULL),
(29950, CONVERT(UNHEX('E5AEA2E688B7E794BBE5838F') USING utf8mb4), 29900, 4, 'profile', 'lobster/profile/hub', 'C', 'el-icon-user', '0', '0', 0, 0, 'workflow:lobster:profile', 'admin', NOW(), NULL),
(29934, CONVERT(UNHEX('E5AFB9E5A496E8A792E889B2E9858DE7BDAE') USING utf8mb4), 29900, 5, 'identity-config', 'lobster/identity-config/index', 'C', 'el-icon-view', '0', '0', 0, 0, 'workflow:lobster:identity', 'admin', NOW(), NULL),
(29906, CONVERT(UNHEX('E8AF9DE69CAFE4BC98E58C96E5AEA1E6A0B8') USING utf8mb4), 29900, 6, 'optimization', 'lobster/optimization/index', 'C', 'el-icon-eye-open', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), 'unified optimization audit'),
(3008, CONVERT(UNHEX('E58AA8E68081E88A82E782B9E5AEA1E689B9') USING utf8mb4), 29900, 7, 'dynamic-impl', 'lobster/dynamic-impl/index', 'C', 'el-icon-connection', '0', '0', 0, 0, 'workflow:lobster:audit', 'admin', NOW(), NULL),
(29912, CONVERT(UNHEX('E8819AE59088E8818AE5A4A9') USING utf8mb4), 29900, 8, 'chat-aggregate', 'lobster/chat-aggregate/index', 'C', 'el-icon-message', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), NULL),
(29926, CONVERT(UNHEX('E88A82E782B9E8AFA6E68385') USING utf8mb4), 29900, 99, 'node-detail', 'lobster/node-detail/index', 'C', 'el-icon-document', '1', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), 'hidden route'),
(29933, CONVERT(UNHEX('E8AFADE4B989E585B3E994AEE8AF8D') USING utf8mb4), 29900, 19, 'semantic-keywords', 'lobster/semantic-keywords/index', 'C', 'el-icon-key', '0', '0', 0, 0, 'workflow:lobster:semantic', 'admin', NOW(), 'lobster_tenant_keywords'),
(29925, CONVERT(UNHEX('E6958FE6849FE8AF8DE5BA93') USING utf8mb4), 29900, 20, 'sensitive-words', 'lobster/sensitive-words/index', 'C', 'el-icon-warning', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), NULL),
(29963, CONVERT(UNHEX('E8BF9DE7A681E8AF8DE5BA93') USING utf8mb4), 29900, 21, 'forbidden-words', 'lobster/replace-words/index', 'C', 'el-icon-warning', '0', '0', 0, 0, 'workflow:lobster:replace', 'admin', NOW(), NULL),
(29964, CONVERT(UNHEX('E69893E99499E8AF8DE5BA93') USING utf8mb4), 29900, 22, 'typo-words', 'lobster/replace-words/index', 'C', 'el-icon-edit', '0', '0', 0, 0, 'workflow:lobster:replace', 'admin', NOW(), NULL),
(29951, CONVERT(UNHEX('E59091E9878FE79FA5E8AF86E5BA93') USING utf8mb4), 29900, 23, 'qdrant', 'lobster/qdrant/index', 'C', 'el-icon-collection', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), 'Qdrant vector KB'),
(29907, CONVERT(UNHEX('E68F90E7A4BAE8AF8DE7AEA1E79086') USING utf8mb4), 29900, 24, 'prompt', 'lobster/prompt/index', 'C', 'el-icon-edit', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), NULL),
(29908, CONVERT(UNHEX('E99480E586A0E8AFADE69699E5ADA6E4B9A0') USING utf8mb4), 29900, 25, 'sales-corpus', 'lobster/sales-corpus/index', 'C', 'el-icon-star', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), NULL),
(29909, CONVERT(UNHEX('E68EA5E58FA3E6B3A8E5868CE4B8ADE5BF83') USING utf8mb4), 29900, 26, 'api-registry', 'lobster/api-registry/index', 'C', 'el-icon-nested', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), NULL),
(29965, CONVERT(UNHEX('E8A18CE4B89AE8AFADE69699E79FA9E998B5') USING utf8mb4), 29900, 27, 'industry-corpus-matrix', 'lobster/industry-corpus-matrix/index', 'C', 'el-icon-grid', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), 'UDD industry corpus matrix'),
(29966, CONVERT(UNHEX('E6B7B1E5BAA6E5AFB9E8AF9DE8B083E8AF95') USING utf8mb4), 29900, 28, 'udd-debug', 'lobster/udd-debug/index', 'C', 'el-icon-bug', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), 'UDD debug');

REPLACE INTO tenant_company_menu (menu_id, menu_name, parent_id, order_num, path, component, menu_type, icon, visible, status, is_frame, is_cache, perms, create_by, create_time, remark) VALUES
(29900, CONVERT(UNHEX('4149E695B0E5AD97E59198E5B7A5') USING utf8mb4), 0, 100, '/lobster', '', 'M', 'el-icon-cpu', '0', '0', 0, 0, NULL, 'admin', NOW(), 'AI Digital Employee'),
(32481, CONVERT(UNHEX('E5B7A5E4BD9CE6B581E6A8A1E69DBF') USING utf8mb4), 29900, 1, 'workflowLobster', 'company/workflowLobster/index', 'C', 'el-icon-share', '0', '0', 0, 0, 'workflow:lobster:edit', 'admin', NOW(), NULL),
(29949, CONVERT(UNHEX('E5B7A5E4BD9CE6B581E689A7E8A18C') USING utf8mb4), 29900, 2, 'workflow-execution', 'lobster/workflow-execution/index', 'C', 'el-icon-guide', '0', '0', 0, 0, 'workflow:lobster:edit', 'admin', NOW(), NULL),
(29905, CONVERT(UNHEX('E5AE9EE4BE8BE79B91E68EA7') USING utf8mb4), 29900, 3, 'instance', 'lobster/instance/index', 'C', 'el-icon-monitor', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), NULL),
(29950, CONVERT(UNHEX('E5AEA2E688B7E794BBE5838F') USING utf8mb4), 29900, 4, 'profile', 'lobster/profile/hub', 'C', 'el-icon-user', '0', '0', 0, 0, 'workflow:lobster:profile', 'admin', NOW(), NULL),
(29934, CONVERT(UNHEX('E5AFB9E5A496E8A792E889B2E9858DE7BDAE') USING utf8mb4), 29900, 5, 'identity-config', 'lobster/identity-config/index', 'C', 'el-icon-view', '0', '0', 0, 0, 'workflow:lobster:identity', 'admin', NOW(), NULL),
(29906, CONVERT(UNHEX('E8AF9DE69CAFE4BC98E58C96E5AEA1E6A0B8') USING utf8mb4), 29900, 6, 'optimization', 'lobster/optimization/index', 'C', 'el-icon-eye-open', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), 'unified optimization audit'),
(3008, CONVERT(UNHEX('E58AA8E68081E88A82E782B9E5AEA1E689B9') USING utf8mb4), 29900, 7, 'dynamic-impl', 'lobster/dynamic-impl/index', 'C', 'el-icon-connection', '0', '0', 0, 0, 'workflow:lobster:audit', 'admin', NOW(), NULL),
(29912, CONVERT(UNHEX('E8819AE59088E8818AE5A4A9') USING utf8mb4), 29900, 8, 'chat-aggregate', 'lobster/chat-aggregate/index', 'C', 'el-icon-message', '0', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), NULL),
(29926, CONVERT(UNHEX('E88A82E782B9E8AFA6E68385') USING utf8mb4), 29900, 99, 'node-detail', 'lobster/node-detail/index', 'C', 'el-icon-document', '1', '0', 0, 0, 'workflow:lobster:query', 'admin', NOW(), 'hidden route');