-- =============================================================================
-- 补挂金三角业绩 jsj_id / F_jsjid（2026年1–7月）— V3 全公司分批版
-- =============================================================================
-- 与 V2 区别：映射表按「健康师 + 业绩月份」匹配，可一次脚本覆盖多个月。
-- 生产迁库后可补挂规模（仅当月有 ACTIVE 绑定且字段为空）：
--   开单：2439 条 | 耗卡：3891 条 | 合计约 6330 条
-- 若只修 7 月，请用：补挂金三角业绩_jsjid_202607_v2_分批.sql
-- =============================================================================

SET NAMES utf8mb4;
SET SESSION net_read_timeout = 600;
SET SESSION net_write_timeout = 600;
SET SESSION wait_timeout = 600;
SET SESSION innodb_lock_wait_timeout = 120;

SET @range_start = '2026-01-01 00:00:00';
SET @range_end   = '2026-07-31 23:59:59';

-- =============================================================================
-- 步骤 1：构建映射表（每人每月一条战队，与 GoldTriangleBindingHelper 一致）
-- =============================================================================
DROP TABLE IF EXISTS tmp_jsj_user_bind_multi;

CREATE TABLE tmp_jsj_user_bind_multi (
  user_id    VARCHAR(64) NOT NULL,
  perf_month VARCHAR(6)  NOT NULL,
  jsj_id     VARCHAR(64) NOT NULL,
  team_name  VARCHAR(128) NULL,
  PRIMARY KEY (user_id, perf_month),
  KEY idx_jsj_id (jsj_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO tmp_jsj_user_bind_multi (user_id, perf_month, jsj_id, team_name)
SELECT
  x.user_id,
  x.F_Month,
  MIN(x.jsj_id) AS jsj_id,
  MIN(j.jsj)    AS team_name
FROM (
  SELECT ju.user_id, ju.F_Month, ju.jsj_id
  FROM lq_jinsanjiao_user ju
  INNER JOIN (
    SELECT user_id, F_Month, MAX(F_CreatorTime) AS max_ct
    FROM lq_jinsanjiao_user
    WHERE F_DeleteMark = 0
      AND status = 'ACTIVE'
      AND F_Month >= '202601'
      AND F_Month <= '202607'
      AND jsj_id IS NOT NULL
      AND jsj_id <> ''
    GROUP BY user_id, F_Month
  ) latest
    ON latest.user_id = ju.user_id
   AND latest.F_Month = ju.F_Month
   AND latest.max_ct = ju.F_CreatorTime
  INNER JOIN lq_ycsd_jsj j0
    ON j0.F_Id = ju.jsj_id
   AND j0.yf = ju.F_Month
  WHERE ju.F_DeleteMark = 0
    AND ju.status = 'ACTIVE'
    AND ju.F_Month >= '202601'
    AND ju.F_Month <= '202607'
    AND ju.jsj_id IS NOT NULL
    AND ju.jsj_id <> ''
) x
INNER JOIN lq_ycsd_jsj j ON j.F_Id = x.jsj_id
GROUP BY x.user_id, x.F_Month;

SELECT COUNT(*) AS bind_user_month_cnt FROM tmp_jsj_user_bind_multi;

-- =============================================================================
-- 步骤 2：待补数量（按月份）
-- =============================================================================
SELECT DATE_FORMAT(k.yjsj, '%Y%m') AS ym, COUNT(*) AS kd_todo
FROM lq_kd_jksyj k
INNER JOIN tmp_jsj_user_bind_multi b
  ON b.user_id = COALESCE(NULLIF(TRIM(k.jkszh), ''), NULLIF(TRIM(k.jks), ''))
 AND b.perf_month = DATE_FORMAT(k.yjsj, '%Y%m')
WHERE k.F_IsEffective = 1
  AND (k.jsj_id IS NULL OR k.jsj_id = '')
  AND k.yjsj >= @range_start
  AND k.yjsj <= @range_end
GROUP BY DATE_FORMAT(k.yjsj, '%Y%m')
ORDER BY ym;

SELECT DATE_FORMAT(x.yjsj, '%Y%m') AS ym, COUNT(*) AS xh_todo
FROM lq_xh_jksyj x
INNER JOIN tmp_jsj_user_bind_multi b
  ON b.user_id = COALESCE(NULLIF(TRIM(x.jkszh), ''), NULLIF(TRIM(x.jks), ''))
 AND b.perf_month = DATE_FORMAT(x.yjsj, '%Y%m')
WHERE x.F_IsEffective = 1
  AND (x.F_jsjid IS NULL OR x.F_jsjid = '')
  AND x.yjsj >= @range_start
  AND x.yjsj <= @range_end
GROUP BY DATE_FORMAT(x.yjsj, '%Y%m')
ORDER BY ym;

-- =============================================================================
-- 步骤 3C：分批补开单（每次 100 行 —— 重复执行直到 kd_remain = 0）
-- =============================================================================
UPDATE lq_kd_jksyj k
INNER JOIN (
  SELECT k2.F_Id, b2.jsj_id
  FROM lq_kd_jksyj k2
  INNER JOIN tmp_jsj_user_bind_multi b2
    ON b2.user_id = COALESCE(NULLIF(TRIM(k2.jkszh), ''), NULLIF(TRIM(k2.jks), ''))
   AND b2.perf_month = DATE_FORMAT(k2.yjsj, '%Y%m')
  WHERE k2.F_IsEffective = 1
    AND (k2.jsj_id IS NULL OR k2.jsj_id = '')
    AND k2.yjsj >= @range_start
    AND k2.yjsj <= @range_end
  ORDER BY k2.F_Id
  LIMIT 100
) batch ON batch.F_Id = k.F_Id
SET k.jsj_id = batch.jsj_id;

SELECT COUNT(*) AS kd_remain
FROM lq_kd_jksyj k
INNER JOIN tmp_jsj_user_bind_multi b
  ON b.user_id = COALESCE(NULLIF(TRIM(k.jkszh), ''), NULLIF(TRIM(k.jks), ''))
 AND b.perf_month = DATE_FORMAT(k.yjsj, '%Y%m')
WHERE k.F_IsEffective = 1
  AND (k.jsj_id IS NULL OR k.jsj_id = '')
  AND k.yjsj >= @range_start
  AND k.yjsj <= @range_end;

-- =============================================================================
-- 步骤 3D：分批补耗卡（每次 100 行 —— 重复执行直到 xh_remain = 0）
-- =============================================================================
UPDATE lq_xh_jksyj x
INNER JOIN (
  SELECT x2.F_Id, b2.jsj_id
  FROM lq_xh_jksyj x2
  INNER JOIN tmp_jsj_user_bind_multi b2
    ON b2.user_id = COALESCE(NULLIF(TRIM(x2.jkszh), ''), NULLIF(TRIM(x2.jks), ''))
   AND b2.perf_month = DATE_FORMAT(x2.yjsj, '%Y%m')
  WHERE x2.F_IsEffective = 1
    AND (x2.F_jsjid IS NULL OR x2.F_jsjid = '')
    AND x2.yjsj >= @range_start
    AND x2.yjsj <= @range_end
  ORDER BY x2.F_Id
  LIMIT 100
) batch ON batch.F_Id = x.F_Id
SET x.F_jsjid = batch.jsj_id;

SELECT COUNT(*) AS xh_remain
FROM lq_xh_jksyj x
INNER JOIN tmp_jsj_user_bind_multi b
  ON b.user_id = COALESCE(NULLIF(TRIM(x.jkszh), ''), NULLIF(TRIM(x.jks), ''))
 AND b.perf_month = DATE_FORMAT(x.yjsj, '%Y%m')
WHERE x.F_IsEffective = 1
  AND (x.F_jsjid IS NULL OR x.F_jsjid = '')
  AND x.yjsj >= @range_start
  AND x.yjsj <= @range_end;

-- =============================================================================
-- 步骤 4：清理（验证通过后）
-- =============================================================================
-- DROP TABLE IF EXISTS tmp_jsj_user_bind_multi;