-- Run this script in database `eduepos-plat`.
-- It adds `/delivery/deliveryerManage1` under the same module as the existing
-- `/delivery/deliveryerManage` menu, then copies menu/button permissions from
-- the existing rider management menu.

-- 1. Create menu by copying the existing rider management menu.
INSERT INTO sys_menu (
    MODEL_CODE,
    MENU_NAME,
    URL,
    LEVEL,
    ORDER_KEY,
    FATHER_ID,
    STATUS,
    CREATE_DATE
)
SELECT
    old_menu.MODEL_CODE,
    '新配送员管理',
    '/delivery/deliveryerManage1',
    old_menu.LEVEL,
    old_menu.ORDER_KEY + 1,
    old_menu.FATHER_ID,
    old_menu.STATUS,
    NOW()
FROM sys_menu old_menu
WHERE old_menu.URL = '/delivery/deliveryerManage'
  AND NOT EXISTS (
      SELECT 1
      FROM (
          SELECT MENU_ID
          FROM sys_menu
          WHERE URL = '/delivery/deliveryerManage1'
      ) exists_menu
  )
LIMIT 1;

-- 2. Copy selectable button permissions from the existing rider management menu.
INSERT INTO sys_menu_button (
    MENU_ID,
    BUTTON_CODE
)
SELECT
    new_menu.MENU_ID,
    old_button.BUTTON_CODE
FROM sys_menu old_menu
JOIN sys_menu new_menu ON new_menu.URL = '/delivery/deliveryerManage1'
JOIN sys_menu_button old_button ON old_button.MENU_ID = old_menu.MENU_ID
WHERE old_menu.URL = '/delivery/deliveryerManage'
  AND NOT EXISTS (
      SELECT 1
      FROM sys_menu_button exists_button
      WHERE exists_button.MENU_ID = new_menu.MENU_ID
        AND exists_button.BUTTON_CODE = old_button.BUTTON_CODE
  );

-- 3. Fallback: if the existing rider menu has no button rows, create common buttons.
INSERT INTO sys_menu_button (
    MENU_ID,
    BUTTON_CODE
)
SELECT
    new_menu.MENU_ID,
    buttons.BUTTON_CODE
FROM sys_menu new_menu
JOIN (
    SELECT 'add' AS BUTTON_CODE
    UNION ALL SELECT 'edit'
    UNION ALL SELECT 'delete'
) buttons
WHERE new_menu.URL = '/delivery/deliveryerManage1'
  AND NOT EXISTS (
      SELECT 1
      FROM sys_menu_button exists_button
      WHERE exists_button.MENU_ID = new_menu.MENU_ID
        AND exists_button.BUTTON_CODE = buttons.BUTTON_CODE
  );

-- 4. Grant the new menu to every role that already has the old rider management menu.
INSERT INTO sys_role (
    CHARACTOR_ID,
    MENU_ID
)
SELECT
    old_role.CHARACTOR_ID,
    new_menu.MENU_ID
FROM sys_role old_role
JOIN sys_menu old_menu ON old_menu.MENU_ID = old_role.MENU_ID
JOIN sys_menu new_menu ON new_menu.URL = '/delivery/deliveryerManage1'
WHERE old_menu.URL = '/delivery/deliveryerManage'
  AND NOT EXISTS (
      SELECT 1
      FROM sys_role exists_role
      WHERE exists_role.CHARACTOR_ID = old_role.CHARACTOR_ID
        AND exists_role.MENU_ID = new_menu.MENU_ID
  );

-- 5. Grant matching button permissions to roles copied in step 4.
INSERT INTO sys_role_menu_button (
    MENU_ID,
    ROLE_ID,
    BUTTON_CODE
)
SELECT
    new_menu.MENU_ID,
    old_button_role.ROLE_ID,
    old_button_role.BUTTON_CODE
FROM sys_role_menu_button old_button_role
JOIN sys_menu old_menu ON old_menu.MENU_ID = old_button_role.MENU_ID
JOIN sys_menu new_menu ON new_menu.URL = '/delivery/deliveryerManage1'
JOIN sys_menu_button new_button ON new_button.MENU_ID = new_menu.MENU_ID
    AND new_button.BUTTON_CODE = old_button_role.BUTTON_CODE
WHERE old_menu.URL = '/delivery/deliveryerManage'
  AND NOT EXISTS (
      SELECT 1
      FROM sys_role_menu_button exists_button_role
      WHERE exists_button_role.MENU_ID = new_menu.MENU_ID
        AND exists_button_role.ROLE_ID = old_button_role.ROLE_ID
        AND exists_button_role.BUTTON_CODE = old_button_role.BUTTON_CODE
  );

-- 6. Verify.
SELECT
    m.MENU_ID,
    m.MENU_NAME,
    m.URL,
    m.MODEL_CODE,
    m.ORDER_KEY,
    b.BUTTON_CODE,
    r.CHARACTOR_ID
FROM sys_menu m
LEFT JOIN sys_menu_button b ON b.MENU_ID = m.MENU_ID
LEFT JOIN sys_role r ON r.MENU_ID = m.MENU_ID
WHERE m.URL IN ('/delivery/deliveryerManage', '/delivery/deliveryerManage1')
ORDER BY m.URL, b.BUTTON_CODE, r.CHARACTOR_ID;