-- =============================================================================
-- 金三角修复 · 最后执行段（C 段）
-- 前置：已跑完 A 预览 + B 备份（四个 bak_20260806_* 表已存在）
-- 用法：整段选中执行 → 看 C5 三项 remain_* 必须全是 0 → 再执行 COMMIT
--       若 C5 有非 0：执行 ROLLBACK
-- =============================================================================

START TRANSACTION;

SET SESSION group_concat_max_len = 102400;

DROP TEMPORARY TABLE IF EXISTS tmp_valid_jsj_binding;
CREATE TEMPORARY TABLE tmp_valid_jsj_binding AS
SELECT
  x.user_id,
  x.F_Month,
  SUBSTRING_INDEX(GROUP_CONCAT(x.jsj_id ORDER BY x.F_CreatorTime DESC, x.F_Id DESC), ',', 1) AS jsj_id
FROM (
  SELECT ju.user_id, ju.F_Month, ju.jsj_id, ju.F_Id, ju.F_CreatorTime
  FROM lq_jinsanjiao_user ju
  INNER JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id AND j.yf = ju.F_Month
  WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
) x
GROUP BY x.user_id, x.F_Month;

-- C1 软删幽灵 ACTIVE 绑定
UPDATE lq_jinsanjiao_user ju
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id
SET ju.F_DeleteMark = 1,
    ju.status = 'INACTIVE',
    ju.F_LastModifyTime = NOW(),
    ju.F_LastModifyUserId = 'prod-fix-20260806-orphan-binding'
WHERE ju.F_DeleteMark = 0
  AND ju.status = 'ACTIVE'
  AND j.F_Id IS NULL;

SELECT ROW_COUNT() AS c1_orphan_binding_updated;

-- C2 迁移幽灵业绩 → 有效战队
UPDATE lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j_old ON j_old.F_Id = k.jsj_id
INNER JOIN tmp_valid_jsj_binding v
  ON v.user_id = COALESCE(NULLIF(k.jks, ''), NULLIF(k.jkszh, ''))
 AND v.F_Month = DATE_FORMAT(k.yjsj, '%Y%m')
SET k.jsj_id = v.jsj_id
WHERE k.F_IsEffective = 1
  AND k.jsj_id IS NOT NULL AND k.jsj_id <> ''
  AND j_old.F_Id IS NULL
  AND v.jsj_id <> k.jsj_id;

SELECT ROW_COUNT() AS c2_kd_updated;

UPDATE lq_xh_jksyj x
LEFT JOIN lq_ycsd_jsj j_old ON j_old.F_Id = x.F_jsjid
INNER JOIN tmp_valid_jsj_binding v
  ON v.user_id = COALESCE(NULLIF(x.jks, ''), NULLIF(x.jkszh, ''))
 AND v.F_Month = DATE_FORMAT(x.yjsj, '%Y%m')
SET x.F_jsjid = v.jsj_id
WHERE x.F_IsEffective = 1
  AND x.F_jsjid IS NOT NULL AND x.F_jsjid <> ''
  AND j_old.F_Id IS NULL
  AND v.jsj_id <> x.F_jsjid;

SELECT ROW_COUNT() AS c2_xh_updated;

UPDATE lq_hytk_jksyj h
LEFT JOIN lq_ycsd_jsj j_old ON j_old.F_Id = h.F_jsjid
INNER JOIN tmp_valid_jsj_binding v
  ON v.user_id = COALESCE(NULLIF(h.jks, ''), NULLIF(h.jkszh, ''))
 AND v.F_Month = DATE_FORMAT(h.tksj, '%Y%m')
SET h.F_jsjid = v.jsj_id
WHERE (h.F_IsEffective = 1 OR h.F_IsEffective IS NULL)
  AND h.F_jsjid IS NOT NULL AND h.F_jsjid <> ''
  AND j_old.F_Id IS NULL
  AND v.jsj_id <> h.F_jsjid;

SELECT ROW_COUNT() AS c2_tk_updated;

-- C4 同人同月多 ACTIVE：只留最新
DROP TEMPORARY TABLE IF EXISTS tmp_keep_binding;
CREATE TEMPORARY TABLE tmp_keep_binding AS
SELECT ju.user_id, ju.F_Month,
       SUBSTRING_INDEX(GROUP_CONCAT(ju.F_Id ORDER BY ju.F_CreatorTime DESC, ju.F_Id DESC), ',', 1) AS keep_id
FROM lq_jinsanjiao_user ju
INNER JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id AND j.yf = ju.F_Month
WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
GROUP BY ju.user_id, ju.F_Month
HAVING COUNT(*) > 1;

DROP TEMPORARY TABLE IF EXISTS tmp_close_binding_ids;
CREATE TEMPORARY TABLE tmp_close_binding_ids AS
SELECT ju.F_Id
FROM lq_jinsanjiao_user ju
INNER JOIN tmp_keep_binding k
  ON k.user_id = ju.user_id AND k.F_Month = ju.F_Month
INNER JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id AND j.yf = ju.F_Month
WHERE ju.F_DeleteMark = 0
  AND ju.status = 'ACTIVE'
  AND ju.F_Id <> k.keep_id;

UPDATE lq_jinsanjiao_user ju
INNER JOIN tmp_close_binding_ids c ON c.F_Id = ju.F_Id
SET ju.F_DeleteMark = 1,
    ju.status = 'INACTIVE',
    ju.F_LastModifyTime = NOW(),
    ju.F_LastModifyUserId = 'prod-fix-20260806-multi-active';

SELECT ROW_COUNT() AS c4_multi_active_closed;

-- C5 验证（三项 cnt 必须全是 0）
SELECT 'remain_orphan_active_binding' AS check_item, COUNT(*) AS cnt
FROM lq_jinsanjiao_user ju
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id
WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE' AND j.F_Id IS NULL
UNION ALL
SELECT 'remain_migratable_orphan_kd', COUNT(*)
FROM lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j_old ON j_old.F_Id = k.jsj_id
INNER JOIN tmp_valid_jsj_binding v
  ON v.user_id = COALESCE(NULLIF(k.jks, ''), NULLIF(k.jkszh, ''))
 AND v.F_Month = DATE_FORMAT(k.yjsj, '%Y%m')
WHERE k.F_IsEffective = 1 AND k.jsj_id <> '' AND j_old.F_Id IS NULL AND v.jsj_id <> k.jsj_id
UNION ALL
SELECT 'remain_multi_active', COUNT(*)
FROM (
  SELECT ju.user_id, ju.F_Month
  FROM lq_jinsanjiao_user ju
  INNER JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id AND j.yf = ju.F_Month
  WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
  GROUP BY ju.user_id, ju.F_Month
  HAVING COUNT(*) > 1
) t;

-- 观察项（有值也允许 COMMIT，历史无法归队）
SELECT COUNT(*) AS orphan_kd_no_valid_binding
FROM lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = k.jsj_id
LEFT JOIN tmp_valid_jsj_binding v
  ON v.user_id = COALESCE(NULLIF(k.jks, ''), NULLIF(k.jkszh, ''))
 AND v.F_Month = DATE_FORMAT(k.yjsj, '%Y%m')
WHERE k.F_IsEffective = 1
  AND k.jsj_id IS NOT NULL AND k.jsj_id <> ''
  AND j.F_Id IS NULL
  AND v.jsj_id IS NULL;

-- ★ C5 三项 remain_* 全是 0 后，单独执行下一行（不要和上面一起盲跑）★
-- COMMIT;

-- 若 C5 有非 0，执行：
-- ROLLBACK;