-- Run this script in database `eduepos-plat`.
-- It adds the new menu `/admin/storemanage1` and makes its button permissions
-- selectable in Role Management -> System Permissions.

-- 1. Create menu by copying the existing store menu.
INSERT INTO sys_menu (
    MODEL_CODE,
    MENU_NAME,
    URL,
    LEVEL,
    ORDER_KEY,
    FATHER_ID,
    STATUS,
    CREATE_DATE
)
SELECT
    MODEL_CODE,
    '新门店列表',
    '/admin/storemanage1',
    LEVEL,
    ORDER_KEY + 1,
    FATHER_ID,
    STATUS,
    NOW()
FROM sys_menu
WHERE URL = '/admin/storemanage'
  AND NOT EXISTS (
      SELECT 1
      FROM (
          SELECT MENU_ID
          FROM sys_menu
          WHERE URL = '/admin/storemanage1'
      ) exists_menu
  )
LIMIT 1;

-- 2. Prefer copying button permissions from the existing store 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 = '/admin/storemanage1'
JOIN sys_menu_button old_button ON old_button.MENU_ID = old_menu.MENU_ID
WHERE old_menu.URL = '/admin/storemanage'
  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 old menu has no button rows, create the 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 = '/admin/storemanage1'
  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. Remove export if it was inserted by an earlier script.
DELETE b
FROM sys_menu_button b
JOIN sys_menu m ON m.MENU_ID = b.MENU_ID
WHERE m.URL = '/admin/storemanage1'
  AND b.BUTTON_CODE = 'export';

-- 5. Verify. This must return add/edit/delete rows for the new menu.
SELECT
    m.MENU_ID,
    m.MENU_NAME,
    m.URL,
    b.BUTTON_CODE
FROM sys_menu m
LEFT JOIN sys_menu_button b ON b.MENU_ID = m.MENU_ID
WHERE m.URL IN ('/admin/storemanage', '/admin/storemanage1')
ORDER BY m.URL, b.BUTTON_CODE;

-- Optional direct grant for one role. Replace ROLE_ID before running.
-- INSERT INTO sys_role (CHARACTOR_ID, MENU_ID)
-- SELECT ROLE_ID, MENU_ID
-- FROM sys_menu
-- WHERE URL = '/admin/storemanage1'
--   AND NOT EXISTS (
--       SELECT 1
--       FROM sys_role
--       WHERE CHARACTOR_ID = ROLE_ID
--         AND MENU_ID = sys_menu.MENU_ID
--   );
--
-- INSERT INTO sys_role_menu_button (MENU_ID, ROLE_ID, BUTTON_CODE)
-- SELECT MENU_ID, ROLE_ID, BUTTON_CODE
-- FROM sys_menu_button
-- WHERE MENU_ID = (SELECT MENU_ID FROM sys_menu WHERE URL = '/admin/storemanage1');