-- =============================================================================
-- 金三角「幽灵战队」+ 业绩 jsj_id · 一次性执行脚本
-- 日期：2026-08-06 | GTID 环境（CREATE LIKE + INSERT 备份）
--
-- 【Navicat 分 3 次执行，避免漏 COMMIT】
--   第1步：选中「第1步 A+B」整段 → 运行（预览 + 备份）
--   第2步：选中「第2步 C」整段   → 运行（修复 + C5 验证）
--   第3步：看 C5 三项 remain_* 全是 0 → 选中「第3步 COMMIT」→ 运行
--   第4步：选中「第4步 提交后验证」→ 运行（抽检）
--
-- 【C5 任一项非 0】执行 ROLLBACK; 不要 COMMIT
--
-- 【首次跑预期 ROW_COUNT】c1≈18  c2_kd≈98  c2_xh≈355  c4≈1
-- 【已修复库再跑】c1/c2/c4 应为 0（幂等）
-- =============================================================================


-- #############################################################################
-- 第1步 A+B：预览 + 备份（事务外）
-- #############################################################################

-- A1~A6 预览
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 '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 '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 备份
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;


-- #############################################################################
-- 第2步 C：修复 + 验证（含 START TRANSACTION，本段末尾不要 COMMIT）
-- #############################################################################

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;

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;

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;

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：三项必须全是 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;


-- #############################################################################
-- 第3步：C5 三项全是 0 后，只选中下面 1 行执行
-- #############################################################################

COMMIT;


-- #############################################################################
-- 第4步：提交后验证（COMMIT 成功后再跑）
-- #############################################################################

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_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
UNION ALL
SELECT 'prod_fix_total', COUNT(*)
FROM lq_jinsanjiao_user
WHERE F_LastModifyUserId LIKE 'prod-fix-20260806%';

-- 郑美函 8 月：业绩应只在燃爆队
SELECT 'zheng_kd' AS item, jsj_id, COUNT(*) cnt, ROUND(SUM(CAST(jksyj AS DECIMAL(18,2))),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;

-- 燃爆队 8 月战队开单合计应含郑美函业绩
SELECT 'ranbao_team_kd' AS item, ROUND(SUM(CAST(jksyj AS DECIMAL(18,2))),2) AS team_billing
FROM lq_kd_jksyj
WHERE F_IsEffective = 1 AND jsj_id = '853054127826011397'
  AND yjsj >= '2026-08-01' AND yjsj < '2026-09-01';