-- 合同编号重复排查（只读，不改数据）
-- 库：shanxia2.0 / crm_contract
-- 用途：检查租户内 contract_no 是否仍有重复（含软删）

SET NAMES utf8mb4;

-- 1) 按 (contract_no, tenant_no) 分组：任何重复（含软删）
SELECT
  contract_no,
  tenant_no,
  COUNT(*) AS cnt,
  SUM(CASE WHEN del_flag = 0 THEN 1 ELSE 0 END) AS alive_cnt,
  SUM(CASE WHEN del_flag = 1 THEN 1 ELSE 0 END) AS deleted_cnt,
  GROUP_CONCAT(
    CONCAT(id, '/del', del_flag, '/', IFNULL(customer_name, ''))
    ORDER BY del_flag, create_time
    SEPARATOR ' | '
  ) AS detail
FROM crm_contract
GROUP BY contract_no, tenant_no
HAVING COUNT(*) > 1
ORDER BY cnt DESC, contract_no;

-- 2) 仅有效单据 (del_flag=0) 是否撞号
SELECT
  contract_no,
  tenant_no,
  COUNT(*) AS alive_cnt,
  GROUP_CONCAT(id ORDER BY create_time SEPARATOR ' | ') AS ids,
  GROUP_CONCAT(IFNULL(customer_name, '') ORDER BY create_time SEPARATOR ' | ') AS customers
FROM crm_contract
WHERE del_flag = 0
GROUP BY contract_no, tenant_no
HAVING COUNT(*) > 1
ORDER BY alive_cnt DESC, contract_no;

-- 3) 指定编号示例：SX/X-JT20260803001（含改号后的 ~DEL~ 变体）
SELECT
  id,
  contract_no,
  contract_name,
  customer_name,
  status,
  del_flag,
  create_time,
  update_time
FROM crm_contract
WHERE tenant_no = 'shanxia2.0'
  AND (
    contract_no = 'SX/X-JT20260803001'
    OR contract_no LIKE 'SX/X-JT20260803001~%'
  )
ORDER BY del_flag, create_time;

-- 4) 当前唯一索引是否已是全局唯一版
SHOW INDEX FROM crm_contract WHERE Key_name LIKE 'uk_contract%';