-- =============================================================================
-- 打开全部 OA 审批应用的「审批人去重」，并强制重新发布（2026-10-16）
-- MySQL 5.7
--
-- 行为：
-- 1. 每个已有「最新已发布」版本的应用：无论 approverDedup 当前是否已为 true，
--    一律复制为新的 published 行（表单、流程不变，开关强制 true），
--    旧的 is_latest_published 改为 0。
--    已在途实例仍指向旧 version id；新发起走新版本。
-- 2. 全部未删除草稿：就地把 approverDedup 设为 true（不把草稿未完成改动一并发布）。
-- 3. 从未发布过的应用：把版本号最大的那条草稿标为已发布，并打开开关。
--
-- 注意：本脚本会为每个已发布应用再升一版；短时间内重复执行会连续升版。
-- =============================================================================

DROP PROCEDURE IF EXISTS sp_oa_enable_approver_dedup;

DELIMITER $$

CREATE PROCEDURE sp_oa_enable_approver_dedup()
BEGIN
  DECLARE v_done INT DEFAULT 0;
  DECLARE v_src_id VARCHAR(64);
  DECLARE v_app_id VARCHAR(64);
  DECLARE v_tenant VARCHAR(64);
  DECLARE v_next_no INT;
  DECLARE v_new_id VARCHAR(64);
  DECLARE v_advanced TEXT;

  -- 强制：所有当前最新已发布版本都重新发布一版
  DECLARE cur CURSOR FOR
    SELECT id, app_id, tenant_no
    FROM oa_app_version
    WHERE del_flag = 0
      AND is_latest_published = '1';

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_src_id, v_app_id, v_tenant;
    IF v_done = 1 THEN
      LEAVE read_loop;
    END IF;

    SELECT IFNULL(MAX(version_no), 0) + 1
      INTO v_next_no
    FROM oa_app_version
    WHERE app_id = v_app_id
      AND tenant_no = v_tenant;

    SET v_new_id = UPPER(REPLACE(UUID(), '-', ''));

    SELECT CAST(
      JSON_SET(
        CASE
          WHEN advanced_settings_json IS NULL
            OR advanced_settings_json = ''
            OR JSON_VALID(advanced_settings_json) = 0
          THEN CAST('{}' AS JSON)
          ELSE CAST(advanced_settings_json AS JSON)
        END,
        '$.approverDedup',
        CAST('true' AS JSON)
      ) AS CHAR
    )
      INTO v_advanced
    FROM oa_app_version
    WHERE id = v_src_id;

    INSERT INTO oa_app_version (
      id, app_id, version_no, status, is_latest_published,
      form_schema_json, flow_tree_json, advanced_settings_json,
      tenant_no, order_num, remark,
      create_by, create_dept_id, create_time,
      update_by, update_time, del_flag
    )
    SELECT
      v_new_id, app_id, v_next_no, 'published', '1',
      form_schema_json, flow_tree_json, v_advanced,
      tenant_no, order_num, remark,
      create_by, create_dept_id, NOW(),
      'sql-approver-dedup', NOW(), 0
    FROM oa_app_version
    WHERE id = v_src_id;

    UPDATE oa_app_version
    SET is_latest_published = '0',
        update_by = 'sql-approver-dedup',
        update_time = NOW()
    WHERE id = v_src_id;
  END LOOP;
  CLOSE cur;

  -- 草稿只改开关，避免把未完成的表单/流程改动一起发布
  UPDATE oa_app_version
  SET advanced_settings_json = CAST(
        JSON_SET(
          CASE
            WHEN advanced_settings_json IS NULL
              OR advanced_settings_json = ''
              OR JSON_VALID(advanced_settings_json) = 0
            THEN CAST('{}' AS JSON)
            ELSE CAST(advanced_settings_json AS JSON)
          END,
          '$.approverDedup',
          CAST('true' AS JSON)
        ) AS CHAR
      ),
      update_by = 'sql-approver-dedup',
      update_time = NOW()
  WHERE del_flag = 0
    AND status = 'draft';

  -- 从未发布：提升当前最大版本号草稿
  UPDATE oa_app_version d
  INNER JOIN (
    SELECT app_id, tenant_no, MAX(version_no) AS version_no
    FROM oa_app_version
    WHERE del_flag = 0
      AND status = 'draft'
    GROUP BY app_id, tenant_no
  ) latest_draft
    ON latest_draft.app_id = d.app_id
   AND latest_draft.tenant_no = d.tenant_no
   AND latest_draft.version_no = d.version_no
  LEFT JOIN (
    SELECT app_id, tenant_no
    FROM oa_app_version
    WHERE del_flag = 0
      AND is_latest_published = '1'
    GROUP BY app_id, tenant_no
  ) published
    ON published.app_id = d.app_id
   AND published.tenant_no = d.tenant_no
  SET d.status = 'published',
      d.is_latest_published = '1',
      d.update_by = 'sql-approver-dedup',
      d.update_time = NOW()
  WHERE d.del_flag = 0
    AND d.status = 'draft'
    AND published.app_id IS NULL;
END$$

DELIMITER ;

CALL sp_oa_enable_approver_dedup();
DROP PROCEDURE IF EXISTS sp_oa_enable_approver_dedup;

-- 核对：最新已发布应由本脚本生成，且开关为 true
SELECT app_id,
       tenant_no,
       version_no,
       status,
       is_latest_published,
       update_by,
       JSON_UNQUOTE(JSON_EXTRACT(advanced_settings_json, '$.approverDedup')) AS approver_dedup
FROM oa_app_version
WHERE del_flag = 0
  AND is_latest_published = '1'
ORDER BY tenant_no, app_id;