water/server/plugin/saiadmin/db/20260923_add_water_mini_identity_menu.sql

-- 水泉智配：新增“供水小程序账号绑定”页面与子权限。可重复执行。
SET NAMES utf8mb4;

-- 一级菜单“水泉智配”。若已存在则跳过；缺失时由后台菜单处理器再补齐。
INSERT INTO `sa_system_menu` (
    `parent_id`,`name`,`code`,`slug`,`type`,`path`,`component`,`method`,`icon`,`sort`,
    `link_url`,`is_iframe`,`is_keep_alive`,`is_hidden`,`is_fixed_tab`,`is_full_page`,
    `generate_id`,`generate_key`,`status`,`remark`,`created_by`,`updated_by`,
    `create_time`,`update_time`,`delete_time`
)
SELECT 0, 'water-admin', 'WaterAdmin', '', 1, '/water-overview', 'water/overview/index', '', 'ri:droplet-line', 80,
       NULL, 2, 2, 2, 2, 2,
       0, NULL, 1, '云泉智配供水一站式后台入口', 1, 1,
       NOW(), NOW(), NULL
WHERE NOT EXISTS (
    SELECT 1 FROM `sa_system_menu` WHERE `code` = 'WaterAdmin' AND `delete_time` IS NULL
);

-- 绑定用户列表页面
INSERT INTO `sa_system_menu` (
    `parent_id`,`name`,`code`,`slug`,`type`,`path`,`component`,`method`,`icon`,`sort`,
    `link_url`,`is_iframe`,`is_keep_alive`,`is_hidden`,`is_fixed_tab`,`is_full_page`,
    `generate_id`,`generate_key`,`status`,`remark`,`created_by`,`updated_by`,
    `create_time`,`update_time`,`delete_time`
)
SELECT p.id, '小程序账号绑定', 'WaterMiniIdentity', 'core:water:mini-users:read', 2,
       '/water/mini-identity', 'water/mini-identity/index', 'GET', 'ri:user-shared-line', 130,
       NULL, 2, 2, 2, 2, 2,
       0, NULL, 1, '列出已登录小程序用户，向企业/司机/代理档案授予身份', 1, 1,
       NOW(), NOW(), NULL
FROM `sa_system_menu` p
WHERE p.code = 'WaterAdmin' AND p.delete_time IS NULL
  AND NOT EXISTS (
      SELECT 1 FROM `sa_system_menu` m WHERE m.code = 'WaterMiniIdentity' AND m.delete_time IS NULL
  );

-- 写入授权子权限（type=3）
INSERT INTO `sa_system_menu` (
    `parent_id`,`name`,`code`,`slug`,`type`,`path`,`component`,`method`,`icon`,`sort`,
    `link_url`,`is_iframe`,`is_keep_alive`,`is_hidden`,`is_fixed_tab`,`is_full_page`,
    `generate_id`,`generate_key`,`status`,`remark`,`created_by`,`updated_by`,
    `create_time`,`update_time`,`delete_time`
)
SELECT p.id, perms.name, perms.code, perms.slug, 3, NULL, NULL, perms.method, NULL, perms.sort,
       NULL, 2, 2, 2, 2, 2,
       0, NULL, 1, perms.remark, 1, 1,
       NOW(), NOW(), NULL
FROM `sa_system_menu` p
CROSS JOIN (
    SELECT 'grant-mini-identity' AS name,
           'WaterMiniIdentityGrant' AS code,
           'core:water:mini-grants:write' AS slug,
           'POST' AS method,
           100 AS sort,
           'bind revoke mini program identity' AS remark
) perms
WHERE p.code = 'WaterMiniIdentity' AND p.delete_time IS NULL
  AND NOT EXISTS (
      SELECT 1 FROM `sa_system_menu` m WHERE m.slug = perms.slug AND m.delete_time IS NULL
  );

-- 已有该页面权限的角色，自动继承“授权/撤销”子权限
INSERT INTO `sa_system_role_menu` (`role_id`, `menu_id`)
SELECT DISTINCT rm.role_id, child.id
FROM `sa_system_role_menu` rm
INNER JOIN `sa_system_menu` page
        ON page.id = rm.menu_id
       AND page.code = 'WaterMiniIdentity'
       AND page.delete_time IS NULL
INNER JOIN `sa_system_menu` child
        ON child.parent_id = page.id
       AND child.type = 3
       AND child.delete_time IS NULL
LEFT JOIN `sa_system_role_menu` existing
       ON existing.role_id = rm.role_id
      AND existing.menu_id = child.id
WHERE existing.id IS NULL;