-- =============================================================================
-- 金三角「幽灵战队」+ 业绩 jsj_id · 完整执行脚本（A→B→C→验证→COMMIT）
-- 日期：2026-08-06
-- 适用：GTID 环境（阿里云 RDS）、测试/生产均可
--
-- 【一次性执行步骤】
--   1) 拷贝生产数据到目标库后，整库执行本脚本 A 段（预览，确认影响面）
--   2) 执行 B 段（备份；若 bak_20260806_* 已存在会先 DROP）
--   3) 执行 C 段（START TRANSACTION … 到 C5 验证）
--   4) C5 三项 remain_* 必须全是 0 → 单独执行 COMMIT;
--      有非 0 → 执行 ROLLBACK;
--   5) 可选执行 E 段（提交后抽检）
--
-- 【预期 C 段 ROW_COUNT 参考（生产拷贝首次跑）】
--   c1 ≈ 18   c2_kd ≈ 98   c2_xh ≈ 若干   c2_tk ≈ 少量   c4 ≈ 1
--   已修复过的库再次跑应为 0（幂等）
-- =============================================================================


-- #############################################################################
-- A. 只读预览
-- #############################################################################

SELECT 'A1_orphan_active_binding' AS 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;

SELECT ju.F_Id, ju.user_id, ju.user_name, ju.jsj_id, ju.F_Month, ju.is_leader, ju.F_CreatorTime
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
ORDER BY ju.F_Month DESC, ju.user_name
LIMIT 200;

SELECT 'A2_orphan_kd' AS item, COUNT(*) AS cnt,
       COALESCE(SUM(CAST(k.jksyj AS DECIMAL(18,2))), 0) AS amt
FROM lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = k.jsj_id
WHERE k.F_IsEffective = 1 AND k.jsj_id IS NOT NULL AND k.jsj_id <> '' AND j.F_Id IS NULL;

SELECT 'A3_orphan_xh' AS item, COUNT(*) AS cnt, COALESCE(SUM(x.jksyj), 0) AS amt
FROM lq_xh_jksyj x
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = x.F_jsjid
WHERE x.F_IsEffective = 1 AND x.F_jsjid IS NOT NULL AND x.F_jsjid <> '' AND j.F_Id IS NULL;

SELECT 'A4_orphan_tk' AS item, COUNT(*) AS cnt, COALESCE(SUM(h.jksyj), 0) AS amt
FROM lq_hytk_jksyj h
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = h.F_jsjid
WHERE (h.F_IsEffective = 1 OR h.F_IsEffective IS NULL)
  AND h.F_jsjid IS NOT NULL AND h.F_jsjid <> '' AND j.F_Id IS NULL;

SELECT 'A5_multi_active' AS item, COUNT(*) AS cnt
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;

SELECT 'A6_migratable_orphan_kd' AS item, COUNT(*) AS cnt,
       COALESCE(SUM(CAST(k.jksyj AS DECIMAL(18,2))), 0) AS amt
FROM lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j_old ON j_old.F_Id = k.jsj_id
INNER JOIN (
  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
) 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_old.F_Id IS NULL
  AND v.jsj_id <> k.jsj_id;


-- #############################################################################
-- B. 事务外备份（GTID：CREATE LIKE + INSERT）
-- #############################################################################

DROP TABLE IF EXISTS bak_20260806_jinsanjiao_user_orphan;
DROP TABLE IF EXISTS bak_20260806_kd_jksyj_orphan;
DROP TABLE IF EXISTS bak_20260806_xh_jksyj_orphan;
DROP TABLE IF EXISTS bak_20260806_hytk_jksyj_orphan;

CREATE TABLE bak_20260806_jinsanjiao_user_orphan LIKE lq_jinsanjiao_user;
INSERT INTO bak_20260806_jinsanjiao_user_orphan
SELECT ju.*
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;

CREATE TABLE bak_20260806_kd_jksyj_orphan LIKE lq_kd_jksyj;
INSERT INTO bak_20260806_kd_jksyj_orphan
SELECT k.*
FROM lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = k.jsj_id
WHERE k.F_IsEffective = 1 AND k.jsj_id IS NOT NULL AND k.jsj_id <> '' AND j.F_Id IS NULL;

CREATE TABLE bak_20260806_xh_jksyj_orphan LIKE lq_xh_jksyj;
INSERT INTO bak_20260806_xh_jksyj_orphan
SELECT x.*
FROM lq_xh_jksyj x
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = x.F_jsjid
WHERE x.F_IsEffective = 1 AND x.F_jsjid IS NOT NULL AND x.F_jsjid <> '' AND j.F_Id IS NULL;

CREATE TABLE bak_20260806_hytk_jksyj_orphan LIKE lq_hytk_jksyj;
INSERT INTO bak_20260806_hytk_jksyj_orphan
SELECT h.*
FROM lq_hytk_jksyj h
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = h.F_jsjid
WHERE (h.F_IsEffective = 1 OR h.F_IsEffective IS NULL)
  AND h.F_jsjid IS NOT NULL AND h.F_jsjid <> '' AND j.F_Id IS NULL;

SELECT 'bak_user' AS t, COUNT(*) AS cnt FROM bak_20260806_jinsanjiao_user_orphan
UNION ALL SELECT 'bak_kd', COUNT(*) FROM bak_20260806_kd_jksyj_orphan
UNION ALL SELECT 'bak_xh', COUNT(*) FROM bak_20260806_xh_jksyj_orphan
UNION ALL SELECT 'bak_tk', COUNT(*) FROM bak_20260806_hytk_jksyj_orphan;


-- #############################################################################
-- C. 事务内修复
-- #############################################################################

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;

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;


-- #############################################################################
-- D. 提交（C5 三项全是 0 后，单独执行本行）
-- #############################################################################
-- COMMIT;
-- ROLLBACK;


-- #############################################################################
-- E. 提交后抽检（可选）
-- #############################################################################

-- 郑美函 8 月业绩应只在燃爆队 853054127826011397
-- SELECT 'kd' src, jsj_id, COUNT(*) cnt, SUM(CAST(jksyj AS DECIMAL(18,2))) amt
-- FROM lq_kd_jksyj
-- WHERE F_IsEffective=1 AND yjsj>='2026-08-01' AND yjsj<'2026-09-01'
--   AND (jks='13989177235' OR jkszh='13989177235')
-- GROUP BY jsj_id;