zkxw/docs/sql/migrations/2026-10-10-presign-convert-unique.sql

-- 预签约订单转正式协议唯一约束
-- MySQL 5.7；重复执行安全。历史重复或错误同名索引会给出明确修复提示。

SET NAMES utf8mb4;

DELIMITER $$

DROP PROCEDURE IF EXISTS `ensure_presign_convert_unique`$$
CREATE PROCEDURE `ensure_presign_convert_unique`()
BEGIN
  DECLARE duplicate_sources INT DEFAULT 0;
  DECLARE idx_entries INT DEFAULT 0;
  DECLARE idx_non_unique INT DEFAULT 1;
  DECLARE idx_columns VARCHAR(512) DEFAULT '';

  SELECT COUNT(*) INTO duplicate_sources
  FROM (
    SELECT `tenant_no`, `source_presign_order_id`
    FROM `biz_collection_protocol`
    WHERE `source_presign_order_id` IS NOT NULL
      AND `source_presign_order_id` <> ''
    GROUP BY `tenant_no`, `source_presign_order_id`
    HAVING COUNT(*) > 1
  ) duplicate_rows;

  IF duplicate_sources > 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'duplicate presign conversions found; remediation: reconcile duplicate formal protocols before rerunning migration';
  END IF;

  SELECT
    COUNT(*),
    COALESCE(MIN(NON_UNIQUE), 1),
    COALESCE(GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ','), '')
  INTO idx_entries, idx_non_unique, idx_columns
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'biz_collection_protocol'
    AND INDEX_NAME = 'uk_collection_protocol_presign_source';

  IF idx_entries = 0 THEN
    ALTER TABLE `biz_collection_protocol`
      ADD UNIQUE INDEX `uk_collection_protocol_presign_source`
      (`tenant_no`, `source_presign_order_id`);
  ELSEIF idx_entries <> 2
      OR idx_non_unique <> 0
      OR idx_columns <> 'tenant_no,source_presign_order_id' THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'wrong index uk_collection_protocol_presign_source; remediation: drop or rename it, then rerun migration';
  END IF;
END$$

CALL `ensure_presign_convert_unique`()$$
DROP PROCEDURE `ensure_presign_convert_unique`$$

DELIMITER ;