ylrz_saas_his_scrm/sql/organize_tenant_sys_menu_subtree.sql

-- ============================================================
-- tenant_sys_menu subtree organize (system / qw / store / ...)
-- Run AFTER organize_tenant_sys_menu.sql
-- Backup: CREATE TABLE tenant_sys_menu_bak2 AS SELECT * FROM tenant_sys_menu;
-- ============================================================

-- ============================================================
-- PART A: system (32372) - hide platform-only flat menus
-- ============================================================
UPDATE tenant_sys_menu SET visible = '1', status = '0'
WHERE parent_id = 32372
  AND menu_id IN (
    32385,32386,32387,32388,32389,32390,32391,32392,32393,32394,32395,32396,32397,32398,32399,
    32401,32402,32403,32404,32405,
    32698,32699,32700,32701,32702,32703,
    32756,32757,32758,32759,32760,32761,32762,32763,
    32837,32839,32840,32841,
    35108
  );

-- Move statistics pages out of sysOrg (35100) -> statistics root (32368)
UPDATE tenant_sys_menu SET parent_id = 32368, order_num = order_num
WHERE menu_id IN (29194, 32769, 32770, 32771);

-- sysOrg (35100): organization
UPDATE tenant_sys_menu SET parent_id = 35100, order_num = 10
WHERE menu_id IN (32431,32432,32433,32436,32437,32438,32443,32455,32456,32457,32473,32475,32476,32477);

-- sysPerm (35101): roles & menus
UPDATE tenant_sys_menu SET parent_id = 35101, order_num = 10
WHERE menu_id IN (32440,32447,32448,32449);

-- sysVoice (35102): SMS / voice / workflow
UPDATE tenant_sys_menu SET parent_id = 35102, order_num = 10
WHERE menu_id IN (32427,32451,32452,32453,32454,32470,32471,32472);

-- sysLog (35105): audit logs
UPDATE tenant_sys_menu SET parent_id = 35105, order_num = 10
WHERE menu_id IN (32439,32442);

-- sysConfig (35106): settings only (keep platform-neutral pages)
UPDATE tenant_sys_menu SET parent_id = 35106, order_num = 10
WHERE menu_id IN (32430,32434,32478,32479,32480,32832,32833);

-- Move finance pages system -> bill module
UPDATE tenant_sys_menu SET parent_id = 35114, order_num = 10 WHERE menu_id = 32441; -- money logs
UPDATE tenant_sys_menu SET parent_id = 35111, order_num = 10 WHERE menu_id IN (32445); -- recharge
UPDATE tenant_sys_menu SET parent_id = 35112, order_num = 10 WHERE menu_id = 32435; -- deduct
UPDATE tenant_sys_menu SET parent_id = 35113, order_num = 10 WHERE menu_id IN (32444,32446,32474); -- profit/redpacket

-- Move wx pages system -> wx module
UPDATE tenant_sys_menu SET parent_id = 35010, order_num = 10 WHERE menu_id = 32482;
UPDATE tenant_sys_menu SET parent_id = 35011, order_num = 10 WHERE menu_id = 32483;
UPDATE tenant_sys_menu SET parent_id = 35012, order_num = 10 WHERE menu_id = 32484;
UPDATE tenant_sys_menu SET parent_id = 35013, order_num = 10 WHERE menu_id = 32485;

-- Lobster duplicate under system -> lobster workflow group
UPDATE tenant_sys_menu SET parent_id = 35080, order_num = 99 WHERE menu_id = 32481;

-- Unique path for company/* menus (fix path=company collision)
UPDATE tenant_sys_menu
SET path = SUBSTRING_INDEX(SUBSTRING_INDEX(component, '/', 2), '/', -1)
WHERE component LIKE 'company/%/%'
  AND parent_id IN (35100,35101,35102,35105,35106,35111,35112,35113,35114);

UPDATE tenant_sys_menu SET path = 'companyIndex' WHERE menu_id = 32473;
UPDATE tenant_sys_menu SET path = 'companySet' WHERE menu_id = 32832;
UPDATE tenant_sys_menu SET path = 'userProfile' WHERE menu_id = 32833;

-- ============================================================
-- PART B: qw (32361) - reparent into secondary groups
-- ============================================================

-- qwMsg (35001)
UPDATE tenant_sys_menu SET parent_id = 35001, order_num = 10
WHERE menu_id IN (32704,32705,32739,32740,32749,32745);

-- qwCustomer (35002)
UPDATE tenant_sys_menu SET parent_id = 35002, order_num = 10
WHERE menu_id IN (32708,32714,32715,32716,32717,32718,32719,32720,32721,32722,32723,32724,32725,32726,32755);

-- qwGroup (35003)
UPDATE tenant_sys_menu SET parent_id = 35003, order_num = 10
WHERE menu_id IN (32733,32734,32735,32736,32737,32738);

-- qwMoments (35004)
UPDATE tenant_sys_menu SET parent_id = 35004, order_num = 10
WHERE menu_id IN (32727,32728,32729,32730,32731,32732);

-- qwDrainage (35005)
UPDATE tenant_sys_menu SET parent_id = 35005, order_num = 10
WHERE menu_id IN (32706);

-- qwTag (35006)
UPDATE tenant_sys_menu SET parent_id = 35006, order_num = 10
WHERE menu_id IN (32900,32751,32752);

-- qwSetting (35007)
UPDATE tenant_sys_menu SET parent_id = 35007, order_num = 10
WHERE menu_id IN (32707,32713,32741,32744,32746,32753,32754);

-- Unique path for qw/* (fix path=qw collision)
UPDATE tenant_sys_menu
SET path = SUBSTRING_INDEX(SUBSTRING_INDEX(component, '/', 2), '/', -1)
WHERE component LIKE 'qw/%'
  AND parent_id IN (35001,35002,35003,35004,35005,35006,35007,32361);

UPDATE tenant_sys_menu SET path = 'qwExternalContact' WHERE menu_id = 32755;
UPDATE tenant_sys_menu SET path = 'QwWorkTaskQw' WHERE menu_id = 32705;

-- ============================================================
-- PART C: store (32369) - hide legacy duplicate + reparent store/*
-- ============================================================

-- Hide hisStore duplicate set (keep store/* canonical)
UPDATE tenant_sys_menu SET visible = '1', status = '0'
WHERE menu_id BETWEEN 32591 AND 32643;

UPDATE tenant_sys_menu SET visible = '1', status = '0'
WHERE menu_id IN (32766,32767,32768,32826);

-- storeOrder (35040): orders & reports
UPDATE tenant_sys_menu SET parent_id = 35040, order_num = 10
WHERE menu_id IN (
  32772,32773,32777,32778,32781,32787,
  32806,32807,32808,32809,32810,32811
);

-- storeProduct (35041): products & shipping
UPDATE tenant_sys_menu SET parent_id = 35041, order_num = 10
WHERE menu_id IN (
  32790,32791,32792,32794,32795,32796,
  32812,32813,32814,32815,32816,32817,32818,32819,32820
);

-- storeOps (35042): shop ops / coupon / home / export
UPDATE tenant_sys_menu SET parent_id = 35042, order_num = 10
WHERE menu_id IN (
  32774,32775,32776,32779,32780,32782,32783,32784,32785,32786,32788,32789,32793,32797,
  32801,32802,32803,32804,32805,32821,32822,32823,32824,32825
);

-- Unique path for store/* (fix path=store collision)
UPDATE tenant_sys_menu
SET path = SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(component, '/index/index', '/index'), '/', 2), '/', -1)
WHERE component LIKE 'store/%'
  AND parent_id IN (35040,35041,35042,32369);

-- ============================================================
-- PART D: other noisy modules (quick fixes)
-- ============================================================

-- CRM: move flat pages into crmCustomer (35020)
UPDATE tenant_sys_menu SET parent_id = 35020, order_num = 10
WHERE menu_id IN (32521,32522,32523,32524,32525,32526,32527)
  AND parent_id = 32347;

UPDATE tenant_sys_menu
SET path = SUBSTRING_INDEX(SUBSTRING_INDEX(component, '/', 2), '/', -1)
WHERE component LIKE 'crm/%' AND parent_id IN (35020,35021,35023,32347);

-- fastGpt: move chat pages into aiChat (35070)
UPDATE tenant_sys_menu SET parent_id = 35070, order_num = 10
WHERE menu_id BETWEEN 32528 AND 32537 AND parent_id = 32348;

UPDATE tenant_sys_menu
SET path = SUBSTRING_INDEX(SUBSTRING_INDEX(component, '/', 2), '/', -1)
WHERE component LIKE 'fastGpt/%' AND parent_id IN (35070,35071,35072,35073,35074,35075,32348);

-- live: move flat pages into liveOps (35050)
UPDATE tenant_sys_menu SET parent_id = 35050, order_num = 10
WHERE menu_id BETWEEN 32645 AND 32675 AND parent_id = 32353;

UPDATE tenant_sys_menu
SET path = SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(component, '/index/index', '/index'), '/', 2), '/', -1)
WHERE component LIKE 'live/%' AND parent_id IN (35050,35051,35052,35053,32353);

-- course: move flat pages into courseContent (35060)
UPDATE tenant_sys_menu SET parent_id = 35060, order_num = 10
WHERE menu_id BETWEEN 32486 AND 32519 AND parent_id = 32345;

UPDATE tenant_sys_menu
SET path = SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(component, '/index/index', '/index'), '/', 2), '/', -1)
WHERE component LIKE 'course/%' AND parent_id IN (35060,35061,35062,35063,32345);

-- member: move flat pages under member root (keep flat but unique path)
UPDATE tenant_sys_menu
SET path = SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(component, '/index/index', '/index'), '/', 2), '/', -1)
WHERE component LIKE 'user/%' AND parent_id = 32357;

-- statistics: unique path
UPDATE tenant_sys_menu
SET path = SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(component, '/index/index', '/index'), '/', 2), '/', -1)
WHERE parent_id = 32368 AND component IS NOT NULL AND component <> '';

-- ============================================================
-- PART E: optional - add missing core system pages (tenant admin)
-- Skip if already exists. Adjust menu_id if conflict.
-- ============================================================
-- INSERT INTO tenant_sys_menu (menu_id, menu_name, parent_id, order_num, path, component, menu_type, visible, status, is_frame, is_cache, create_by, create_time)
-- VALUES
-- (35201, '???????', 35101, 1, 'user', 'system/user/index', 'C', '0', '0', 1, 0, 'admin', NOW()),
-- (35202, '???????', 35101, 2, 'role', 'system/role/index', 'C', '0', '0', 1, 0, 'admin', NOW()),
-- (35203, '???????', 35101, 3, 'menu', 'system/menu/index', 'C', '0', '0', 1, 0, 'admin', NOW()),
-- (35204, '???????', 35100, 1, 'dept', 'system/dept/index', 'C', '0', '0', 1, 0, 'admin', NOW()),
-- (35205, '??????', 35100, 2, 'post', 'system/post/index', 'C', '0', '0', 1, 0, 'admin', NOW()),
-- (35206, '??????', 35106, 3, 'dict', 'system/dict/index', 'C', '0', '0', 1, 0, 'admin', NOW()),
-- (35207, '????????', 35106, 4, 'config', 'system/config/index', 'C', '0', '0', 1, 0, 'admin', NOW()),
-- (35208, '??????', 35106, 5, 'notice', 'system/notice/index', 'C', '0', '0', 1, 0, 'admin', NOW()),
-- (35209, '?????', 35106, 6, 'keyword', 'system/keyword/index', 'C', '0', '0', 1, 0, 'admin', NOW());

-- ============================================================
-- Verification
-- ============================================================
-- SELECT menu_id, menu_name, parent_id, path, component, visible
-- FROM tenant_sys_menu WHERE parent_id = 32372 AND visible = '0' ORDER BY order_num, menu_id;

-- SELECT menu_id, menu_name, parent_id, path FROM tenant_sys_menu
-- WHERE parent_id IN (35100,35101,35102,35105,35106) AND visible = '0' ORDER BY parent_id, order_num;

-- SELECT parent_id, path, COUNT(*) cnt FROM tenant_sys_menu
-- WHERE parent_id IN (35001,35002,35003,35004,35005,35006,35007) AND menu_type <> 'F'
-- GROUP BY parent_id, path HAVING cnt > 1;

-- SELECT parent_id, path, COUNT(*) cnt FROM tenant_sys_menu
-- WHERE parent_id IN (35040,35041,35042) AND menu_type <> 'F'
-- GROUP BY parent_id, path HAVING cnt > 1;