-- billing_types.term 重複補正SQL
-- 対象: 同一 billing_payee_profiles_id 内の term 重複
-- 前提: MySQL 8+ (ROW_NUMBER 使用)

-- 1) 事前確認: 重複している請求先IDと term
SELECT
  billing_payee_profiles_id,
  term,
  COUNT(*) AS duplicate_count
FROM billing_types
WHERE billing_payee_profiles_id IS NOT NULL
GROUP BY billing_payee_profiles_id, term
HAVING COUNT(*) > 1
ORDER BY billing_payee_profiles_id, term;

-- 2) 補正（実行）
--    同一請求先ごとに 1..N の連番で term を再採番します。
--    並び順: billing_start_date -> group_organizations.created_at -> id
START TRANSACTION;

CREATE TEMPORARY TABLE tmp_billing_types_term_reseq AS
SELECT
  bt.id,
  bt.billing_payee_profiles_id,
  ROW_NUMBER() OVER (
    PARTITION BY bt.billing_payee_profiles_id
    ORDER BY
      COALESCE(bt.billing_start_date, DATE('1000-01-01')),
      COALESCE(go.created_at, TIMESTAMP('1000-01-01 00:00:00')),
      bt.id
  ) AS new_term
FROM billing_types bt
LEFT JOIN group_organizations go ON go.id = bt.group_organization_id
WHERE bt.billing_payee_profiles_id IS NOT NULL;

UPDATE billing_types bt
JOIN tmp_billing_types_term_reseq t ON t.id = bt.id
SET bt.term = CAST(t.new_term AS CHAR)
WHERE bt.term IS NULL
   OR bt.term <> CAST(t.new_term AS CHAR);

DROP TEMPORARY TABLE tmp_billing_types_term_reseq;

COMMIT;

-- 3) 事後確認: 重複が残っていないこと
SELECT
  billing_payee_profiles_id,
  term,
  COUNT(*) AS duplicate_count
FROM billing_types
WHERE billing_payee_profiles_id IS NOT NULL
GROUP BY billing_payee_profiles_id, term
HAVING COUNT(*) > 1
ORDER BY billing_payee_profiles_id, term;

-- 4) 参考: 連番結果の確認
SELECT
  bt.id,
  bt.billing_payee_profiles_id,
  bt.term,
  bt.billing_start_date,
  bt.created_at,
  go.created_at AS group_organization_created_at
FROM billing_types bt
LEFT JOIN group_organizations go ON go.id = bt.group_organization_id
WHERE bt.billing_payee_profiles_id IS NOT NULL
ORDER BY
  bt.billing_payee_profiles_id,
  COALESCE(bt.billing_start_date, DATE('1000-01-01')),
  COALESCE(go.created_at, TIMESTAMP('1000-01-01 00:00:00')),
  bt.id;
