-- 海外费用既有写按钮最小修复；MySQL 8，目标只能是 baiyue_iam。
-- 仅恢复基线 5105024/5105025 及既有 4000001 PLATFORM_ADMIN 缺失授权。
-- 不创建角色/成员，不授予其他角色，不新增 overseas-fee:edit/review 别名。
-- 在支持 DELIMITER、遇错停止的客户端同连接整份执行；执行期间暂停相关角色权限维护。
-- 不在其他未提交事务中调用。所有业务DML及subject版本变更同事务，任何异常回滚。
USE baiyue_iam;
SET NAMES utf8mb4;
SELECT DATABASE() AS target_database;

DROP PROCEDURE IF EXISTS repair_overseas_fee_canonical_write;
DELIMITER $$
CREATE PROCEDURE repair_overseas_fee_canonical_write()
BEGIN
  DECLARE locked_role BIGINT;
  DECLARE changed_rows BIGINT DEFAULT 0;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;
  IF DATABASE()<>'baiyue_iam' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Wrong schema: expected baiyue_iam';
  END IF;
  IF (SELECT COUNT(*) FROM iam_role WHERE id=4000001 AND tenant_id=0 AND app_code='PLATFORM'
      AND role_code='PLATFORM_ADMIN' AND role_type='SYSTEM' AND status='ACTIVE' AND deleted=0)<>1
      OR EXISTS(SELECT 1 FROM iam_role WHERE role_code='PLATFORM_ADMIN' AND id<>4000001) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Existing PLATFORM_ADMIN identity/status mismatch; no role will be created';
  END IF;
  SELECT id INTO locked_role FROM iam_role WHERE id=4000001 FOR UPDATE;

  -- 新建缺失按钮使用当前客户应收父菜单；只接受已核对的既有财务菜单，不修复其他菜单。
  IF (SELECT COUNT(*) FROM iam_permission WHERE id=5105041 AND tenant_id=0 AND app_code='PLATFORM'
      AND permission_type='MENU' AND permission_code='platform:finance:customer-arap:list'
      AND status='ACTIVE' AND deleted=0)<>1
      OR EXISTS(SELECT 1 FROM iam_permission WHERE permission_code='platform:finance:customer-arap:list' AND id<>5105041) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Existing customer AR/AP parent menu is missing or mismatched';
  END IF;
  IF EXISTS(SELECT 1 FROM iam_permission WHERE
      (id=5105024 AND NOT(permission_code<=>'platform:finance:fee-source:review'))
      OR (id=5105025 AND NOT(permission_code<=>'platform:finance:fee-source:edit'))
      OR (permission_code='platform:finance:fee-source:review' AND id<>5105024)
      OR (permission_code='platform:finance:fee-source:edit' AND id<>5105025)
      OR (id IN(5105024,5105025) AND (tenant_id<>0 OR app_code<>'PLATFORM'
          OR permission_type<>'BUTTON' OR built_in<>1 OR parent_id NOT IN(5105000,5105041)
          OR route_path IS NOT NULL
          OR NOT(http_method<=>IF(id=5105024,'POST','PUT'))))) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Canonical fee button identity/type/metadata conflict';
  END IF;
  IF EXISTS(SELECT 1 FROM iam_permission child LEFT JOIN iam_permission parent ON parent.id=child.parent_id
      WHERE child.id IN(5105024,5105025) AND (parent.id IS NULL OR parent.tenant_id<>0
      OR parent.app_code<>'PLATFORM' OR parent.permission_type<>'MENU' OR parent.status<>'ACTIVE' OR parent.deleted<>0
      OR NOT(parent.permission_code<=>IF(child.parent_id=5105000,'platform:finance','platform:finance:customer-arap:list')))) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Existing fee button parent is invalid';
  END IF;
  IF EXISTS(SELECT 1 FROM iam_role_permission WHERE permission_id IN(5105024,5105025) AND tenant_id<>0)
      OR EXISTS(SELECT 1 FROM iam_membership_role mr LEFT JOIN iam_membership m ON m.id=mr.membership_id
          WHERE mr.role_id=4000001 AND (mr.tenant_id<>0 OR m.id IS NULL OR m.tenant_id<>0)) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Existing canonical permission or administrator membership scope mismatch';
  END IF;
  -- 恢复停用/缺失资源也会恢复其他角色的旧授权；这种情况必须单独核对，不能随本补丁扩权。
  IF EXISTS(SELECT 1 FROM iam_role_permission rp LEFT JOIN iam_permission p ON p.id=rp.permission_id
      WHERE rp.permission_id IN(5105024,5105025) AND rp.role_id<>4000001
      AND (p.id IS NULL OR p.status<>'ACTIVE' OR p.deleted<>0)) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Restoring fee button would affect another role; review separately';
  END IF;

  INSERT INTO iam_permission
    (id,tenant_id,app_code,permission_type,parent_id,permission_code,permission_name,route_path,http_method,sort_no,status,built_in,deleted)
  SELECT 5105024,0,'PLATFORM','BUTTON',5105041,'platform:finance:fee-source:review','审核财务费用来源',NULL,'POST',150,'ACTIVE',1,0
  WHERE NOT EXISTS(SELECT 1 FROM iam_permission WHERE id=5105024);
  SET changed_rows=changed_rows+ROW_COUNT();
  INSERT INTO iam_permission
    (id,tenant_id,app_code,permission_type,parent_id,permission_code,permission_name,route_path,http_method,sort_no,status,built_in,deleted)
  SELECT 5105025,0,'PLATFORM','BUTTON',5105041,'platform:finance:fee-source:edit','编辑财务费用来源',NULL,'PUT',160,'ACTIVE',1,0
  WHERE NOT EXISTS(SELECT 1 FROM iam_permission WHERE id=5105025);
  SET changed_rows=changed_rows+ROW_COUNT();
  UPDATE iam_permission SET status='ACTIVE',deleted=0
  WHERE id IN(5105024,5105025) AND (status<>'ACTIVE' OR deleted<>0);
  SET changed_rows=changed_rows+ROW_COUNT();
  INSERT INTO iam_role_permission(tenant_id,role_id,permission_id,created_at)
  SELECT 0,4000001,p.id,CURRENT_TIMESTAMP(3) FROM iam_permission p WHERE p.id IN(5105024,5105025)
    AND NOT EXISTS(SELECT 1 FROM iam_role_permission rp WHERE rp.role_id=4000001 AND rp.permission_id=p.id);
  SET changed_rows=changed_rows+ROW_COUNT();

  -- 只有真实变更才推进版本；枚举所有既有管理员成员，不依赖当前登录用户，也不遗漏停用成员。
  IF changed_rows>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=m.tenant_id AND mr.role_id=4000001
    WHERE u.auth_user_id IS NOT NULL
    ON DUPLICATE KEY UPDATE permission_version=permission_version+1;
  END IF;
  IF (SELECT COUNT(*) FROM iam_role_permission rp JOIN iam_permission p ON p.id=rp.permission_id
      WHERE rp.role_id=4000001 AND rp.tenant_id=0 AND p.id IN(5105024,5105025)
      AND p.tenant_id=0 AND p.app_code='PLATFORM' AND p.permission_type='BUTTON'
      AND p.status='ACTIVE' AND p.deleted=0)<>2 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Canonical fee write grants incomplete; repair rolled back';
  END IF;
  COMMIT;
  SELECT changed_rows AS repaired_catalog_or_grant_rows;
END$$
DELIMITER ;
CALL repair_overseas_fee_canonical_write();
DROP PROCEDURE repair_overseas_fee_canonical_write;

-- 核验1：必须恰好两行，角色4000001、tenant=0、BUTTON、ACTIVE、deleted=0。
SELECT r.id AS role_id,r.role_code,rp.tenant_id,p.id AS permission_id,p.permission_code,
       p.permission_type,p.status,p.deleted
FROM iam_role r JOIN iam_role_permission rp ON rp.role_id=r.id
JOIN iam_permission p ON p.id=rp.permission_id
WHERE r.id=4000001 AND p.id IN(5105024,5105025) ORDER BY p.id;
-- 核验2：仅查看既有管理员主体与当前版本，不创建或切换任何成员角色。
SELECT DISTINCT u.auth_user_id,m.id AS membership_id,u.status AS user_status,m.status AS membership_status,
       COALESCE(sv.permission_version,0) AS permission_version
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=m.tenant_id AND mr.role_id=4000001
LEFT JOIN iam_security_subject_version sv ON sv.auth_user_id=u.auth_user_id
ORDER BY u.auth_user_id,m.id;