-- 海外费用菜单定向修复，MySQL 8.0+，目标只能 baiyue_iam。
-- 将“海外费用管理”改为“海外费用明细”，并挂到“财务管理”下。
-- 权限码、路由和角色授权保持不变；可重复执行。
USE baiyue_iam;
SET NAMES utf8mb4;

DROP PROCEDURE IF EXISTS repair_overseas_fee_menu_parent;
DELIMITER $$
CREATE PROCEDURE repair_overseas_fee_menu_parent()
BEGIN
  DECLARE v_menu_changed INT DEFAULT 0;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  IF (SELECT COUNT(*) FROM iam_permission
      WHERE id=5105000 AND tenant_id=0 AND app_code='PLATFORM'
        AND permission_type='MENU' AND permission_code='platform:finance'
        AND status='ACTIVE' AND deleted=0)<>1 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT='Finance parent menu platform:finance is missing or inactive';
  END IF;

  IF EXISTS (SELECT 1 FROM iam_permission
      WHERE (id=5117200 AND (permission_code<>'platform:overseas-fee:list'
          OR tenant_id<>0 OR app_code<>'PLATFORM' OR permission_type<>'MENU'))
        OR (permission_code='platform:overseas-fee:list'
          AND (id<>5117200 OR tenant_id<>0 OR app_code<>'PLATFORM'
            OR permission_type<>'MENU'))) THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT='Overseas fee menu permission identity conflict';
  END IF;

  IF (SELECT COUNT(*) FROM iam_permission
      WHERE id=5117200 AND tenant_id=0 AND app_code='PLATFORM'
        AND permission_type='MENU' AND permission_code='platform:overseas-fee:list')<>1 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT='Overseas fee menu platform:overseas-fee:list is missing';
  END IF;

  START TRANSACTION;
  UPDATE iam_permission
  SET parent_id=5105000,
      permission_name='海外费用明细',
      route_path='/overseas-business/fees',
      http_method='GET',
      sort_no=60,
      status='ACTIVE',
      deleted=0,
      updated_at=CURRENT_TIMESTAMP(3)
  WHERE id=5117200 AND tenant_id=0 AND app_code='PLATFORM'
    AND permission_type='MENU' AND permission_code='platform:overseas-fee:list'
    AND (parent_id<>5105000 OR permission_name<>'海外费用明细'
      OR route_path<>'/overseas-business/fees' OR http_method<>'GET'
      OR sort_no<>60 OR status<>'ACTIVE' OR deleted<>0);
  SET v_menu_changed=ROW_COUNT();

  -- 菜单实际变化时递增已获该菜单权限用户的安全版本，刷新旧授权快照。
  IF v_menu_changed>0 THEN
    INSERT INTO iam_security_subject_version(auth_user_id,permission_version)
    SELECT DISTINCT u.auth_user_id,1
    FROM iam_user u
    JOIN iam_membership m ON m.user_id=u.id AND m.tenant_id=0
    JOIN iam_membership_role mr ON mr.membership_id=m.id AND mr.tenant_id=0
    JOIN iam_role r ON r.id=mr.role_id AND r.tenant_id=0
    JOIN iam_role_permission rp ON rp.role_id=r.id
      AND rp.permission_id=5117200 AND rp.tenant_id=0
    WHERE u.auth_user_id IS NOT NULL AND u.status='ACTIVE' AND u.deleted=0
      AND m.status='ACTIVE' AND m.deleted=0
      AND r.app_code='PLATFORM' AND r.status='ACTIVE' AND r.deleted=0
    ON DUPLICATE KEY UPDATE permission_version=permission_version+1;
  END IF;

  COMMIT;
END$$
DELIMITER ;
CALL repair_overseas_fee_menu_parent();
DROP PROCEDURE repair_overseas_fee_menu_parent;

SELECT id,parent_id,permission_code,permission_name,route_path,sort_no
FROM iam_permission WHERE id IN (5105000,5117200) ORDER BY id;