-- =============================================================================
-- 修复健康师业绩表金三角ID(F_jsjid / jsj_id)为空的历史数据
--
-- 背景：
--   耗卡/开单/退卡的健康师业绩行，金三角ID由客户端异步调用
--   /api/Extend/lqycsdjsj/GetJsjInfoByUserMonth 取回后随表单一起提交。
--   当业绩日期为空（编辑入口未回填 hksj）、接口未返回即提交、或预约预填未带该字段时，
--   业绩行的金三角ID会落空，导致金三角业绩页少算，而数据看板（不按战队过滤）金额正常，
--   两边口径对不上。
--
-- 修复口径：
--   按「健康师用户ID(jkszh) + 业绩月份(业绩时间的 yyyyMM)」到 lq_jinsanjiao_user
--   取当月绑定的 jsj_id 回填，与金三角业绩页统计口径一致。
--   仅补空值，不覆盖已有值；T区等本就未绑定战队的账号自动跳过。
--
-- 时间范围：默认 2026-01-01 起，按需调整下面所有 @start_date。
-- 执行顺序：0 预览 -> 1 备份 -> 2 更新 -> 3 验证 -> 4 人工核对重复绑定
-- =============================================================================

SET @start_date = '2026-01-01';

-- =============================================================================
-- 0. 影响面预览（先看数，确认后再执行第 2 步）
-- =============================================================================

-- 0.1 一人一月唯一绑定视图（后续所有步骤共用此口径）
--     同一人同月存在多条绑定时（历史调队/重复配置），取「ACTIVE 优先 + 创建时间最新」的一条
DROP VIEW IF EXISTS v_tmp_jsj_user_month;
CREATE VIEW v_tmp_jsj_user_month AS
SELECT ju.user_id,
       ju.F_Month,
       SUBSTRING_INDEX(
           GROUP_CONCAT(ju.jsj_id ORDER BY CASE WHEN ju.status = 'ACTIVE' THEN 0 ELSE 1 END,
                                            ju.F_CreatorTime DESC),
           ',', 1) AS jsj_id
FROM lq_jinsanjiao_user ju
WHERE ju.F_DeleteMark = 0
  AND ju.jsj_id IS NOT NULL
  AND ju.jsj_id <> ''
GROUP BY ju.user_id, ju.F_Month;

-- 0.2 按月份预览待修复行数与金额
SELECT '耗卡' AS 业务, DATE_FORMAT(j.yjsj, '%Y%m') AS 月份,
       COUNT(*) AS 待修行数, COUNT(DISTINCT j.glkdbh) AS 涉及单据, ROUND(SUM(j.jksyj), 2) AS 业绩金额
FROM lq_xh_jksyj j
JOIN v_tmp_jsj_user_month b ON b.user_id = j.jkszh AND b.F_Month = DATE_FORMAT(j.yjsj, '%Y%m')
WHERE j.F_IsEffective = 1 AND (j.F_jsjid IS NULL OR j.F_jsjid = '') AND j.yjsj >= @start_date
GROUP BY 月份
UNION ALL
SELECT '开单', DATE_FORMAT(j.yjsj, '%Y%m'),
       COUNT(*), COUNT(DISTINCT j.glkdbh), ROUND(SUM(j.jksyj), 2)
FROM lq_kd_jksyj j
JOIN v_tmp_jsj_user_month b ON b.user_id = j.jkszh AND b.F_Month = DATE_FORMAT(j.yjsj, '%Y%m')
WHERE j.F_IsEffective = 1 AND (j.jsj_id IS NULL OR j.jsj_id = '') AND j.yjsj >= @start_date
GROUP BY DATE_FORMAT(j.yjsj, '%Y%m')
UNION ALL
SELECT '退卡', DATE_FORMAT(j.tksj, '%Y%m'),
       COUNT(*), COUNT(DISTINCT j.gltkbh), ROUND(SUM(j.jksyj), 2)
FROM lq_hytk_jksyj j
JOIN v_tmp_jsj_user_month b ON b.user_id = j.jkszh AND b.F_Month = DATE_FORMAT(j.tksj, '%Y%m')
WHERE j.F_IsEffective = 1 AND (j.F_jsjid IS NULL OR j.F_jsjid = '') AND j.tksj >= @start_date
GROUP BY DATE_FORMAT(j.tksj, '%Y%m')
ORDER BY 业务, 月份;

-- 0.3 剩余仍补不上的行（健康师当月无金三角绑定，多为 T区/科技部账号，属正常，无需处理）
SELECT j.jksxm AS 健康师, COUNT(*) AS 行数, ROUND(SUM(j.jksyj), 2) AS 业绩金额
FROM lq_xh_jksyj j
LEFT JOIN v_tmp_jsj_user_month b ON b.user_id = j.jkszh AND b.F_Month = DATE_FORMAT(j.yjsj, '%Y%m')
WHERE j.F_IsEffective = 1 AND (j.F_jsjid IS NULL OR j.F_jsjid = '')
  AND j.yjsj >= @start_date AND b.jsj_id IS NULL
GROUP BY j.jksxm
ORDER BY 行数 DESC;

-- =============================================================================
-- 1. 备份待修复行（回滚依据，务必先执行）
-- =============================================================================

DROP TABLE IF EXISTS bak_jsjid_fix_20260729;
CREATE TABLE bak_jsjid_fix_20260729 (
    biz_type    VARCHAR(20)  NOT NULL COMMENT '业务类型：耗卡/开单/退卡',
    row_id      VARCHAR(50)  NOT NULL COMMENT '业绩行主键',
    bill_id     VARCHAR(50)  NULL     COMMENT '关联单据ID',
    jkszh       VARCHAR(50)  NULL     COMMENT '健康师用户ID',
    jksxm       VARCHAR(50)  NULL     COMMENT '健康师姓名',
    perf_time   DATETIME     NULL     COMMENT '业绩时间',
    old_jsj_id  VARCHAR(50)  NULL     COMMENT '修复前金三角ID',
    new_jsj_id  VARCHAR(50)  NULL     COMMENT '修复后金三角ID',
    PRIMARY KEY (biz_type, row_id)
) COMMENT '健康师业绩金三角ID修复备份 20260729';

INSERT INTO bak_jsjid_fix_20260729
SELECT '耗卡', j.F_Id, j.glkdbh, j.jkszh, j.jksxm, j.yjsj, j.F_jsjid, b.jsj_id
FROM lq_xh_jksyj j
JOIN v_tmp_jsj_user_month b ON b.user_id = j.jkszh AND b.F_Month = DATE_FORMAT(j.yjsj, '%Y%m')
WHERE j.F_IsEffective = 1 AND (j.F_jsjid IS NULL OR j.F_jsjid = '') AND j.yjsj >= @start_date;

INSERT INTO bak_jsjid_fix_20260729
SELECT '开单', j.F_Id, j.glkdbh, j.jkszh, j.jksxm, j.yjsj, j.jsj_id, b.jsj_id
FROM lq_kd_jksyj j
JOIN v_tmp_jsj_user_month b ON b.user_id = j.jkszh AND b.F_Month = DATE_FORMAT(j.yjsj, '%Y%m')
WHERE j.F_IsEffective = 1 AND (j.jsj_id IS NULL OR j.jsj_id = '') AND j.yjsj >= @start_date;

INSERT INTO bak_jsjid_fix_20260729
SELECT '退卡', j.F_Id, j.gltkbh, j.jkszh, j.jksxm, j.tksj, j.F_jsjid, b.jsj_id
FROM lq_hytk_jksyj j
JOIN v_tmp_jsj_user_month b ON b.user_id = j.jkszh AND b.F_Month = DATE_FORMAT(j.tksj, '%Y%m')
WHERE j.F_IsEffective = 1 AND (j.F_jsjid IS NULL OR j.F_jsjid = '') AND j.tksj >= @start_date;

SELECT biz_type AS 业务, COUNT(*) AS 备份行数 FROM bak_jsjid_fix_20260729 GROUP BY biz_type;

-- =============================================================================
-- 2. 执行修复（确认第 0、1 步结果后再执行）
-- =============================================================================

-- 2.1 耗卡健康师业绩
UPDATE lq_xh_jksyj j
JOIN v_tmp_jsj_user_month b ON b.user_id = j.jkszh AND b.F_Month = DATE_FORMAT(j.yjsj, '%Y%m')
SET j.F_jsjid = b.jsj_id
WHERE j.F_IsEffective = 1 AND (j.F_jsjid IS NULL OR j.F_jsjid = '') AND j.yjsj >= @start_date;

-- 2.2 开单健康师业绩
UPDATE lq_kd_jksyj j
JOIN v_tmp_jsj_user_month b ON b.user_id = j.jkszh AND b.F_Month = DATE_FORMAT(j.yjsj, '%Y%m')
SET j.jsj_id = b.jsj_id
WHERE j.F_IsEffective = 1 AND (j.jsj_id IS NULL OR j.jsj_id = '') AND j.yjsj >= @start_date;

-- 2.3 退卡健康师业绩
UPDATE lq_hytk_jksyj j
JOIN v_tmp_jsj_user_month b ON b.user_id = j.jkszh AND b.F_Month = DATE_FORMAT(j.tksj, '%Y%m')
SET j.F_jsjid = b.jsj_id
WHERE j.F_IsEffective = 1 AND (j.F_jsjid IS NULL OR j.F_jsjid = '') AND j.tksj >= @start_date;

-- =============================================================================
-- 3. 验证
-- =============================================================================

-- 3.1 备份行是否全部按预期回填
SELECT '耗卡' AS 业务,
       SUM(CASE WHEN j.F_jsjid = k.new_jsj_id THEN 1 ELSE 0 END) AS 已修复,
       SUM(CASE WHEN j.F_jsjid IS NULL OR j.F_jsjid = '' THEN 1 ELSE 0 END) AS 仍为空
FROM bak_jsjid_fix_20260729 k
JOIN lq_xh_jksyj j ON j.F_Id = k.row_id
WHERE k.biz_type = '耗卡'
UNION ALL
SELECT '开单',
       SUM(CASE WHEN j.jsj_id = k.new_jsj_id THEN 1 ELSE 0 END),
       SUM(CASE WHEN j.jsj_id IS NULL OR j.jsj_id = '' THEN 1 ELSE 0 END)
FROM bak_jsjid_fix_20260729 k
JOIN lq_kd_jksyj j ON j.F_Id = k.row_id
WHERE k.biz_type = '开单'
UNION ALL
SELECT '退卡',
       SUM(CASE WHEN j.F_jsjid = k.new_jsj_id THEN 1 ELSE 0 END),
       SUM(CASE WHEN j.F_jsjid IS NULL OR j.F_jsjid = '' THEN 1 ELSE 0 END)
FROM bak_jsjid_fix_20260729 k
JOIN lq_hytk_jksyj j ON j.F_Id = k.row_id
WHERE k.biz_type = '退卡';

-- 3.2 抽样核对：冷忠翠 2026年7月 耗卡业绩（修复前 8 行漏挂精英队、金额 2711.33）
SELECT COALESCE(jsj.jsj, '未挂战队') AS 战队,
       COUNT(*) AS 行数, ROUND(SUM(j.jksyj), 2) AS 消耗业绩
FROM lq_xh_jksyj j
LEFT JOIN lq_ycsd_jsj jsj ON jsj.F_Id = j.F_jsjid
WHERE j.F_IsEffective = 1 AND j.jkszh = '13198577167'
  AND j.yjsj >= '2026-07-01' AND j.yjsj < '2026-08-01'
GROUP BY 战队;

-- =============================================================================
-- 4. 人工核对：同一人同月绑定多个战队（配置层面问题，脚本按 ACTIVE + 最新创建时间取一条）
--    以下结果需业务确认真实归属，必要时先修 lq_jinsanjiao_user 再重跑第 2 步
-- =============================================================================

SELECT ju.user_name AS 姓名, ju.F_Month AS 月份, jsj.jsj AS 战队,
       ju.status AS 状态, ju.F_CreatorTime AS 创建时间
FROM lq_jinsanjiao_user ju
LEFT JOIN lq_ycsd_jsj jsj ON jsj.F_Id = ju.jsj_id
WHERE ju.F_DeleteMark = 0 AND ju.jsj_id IS NOT NULL AND ju.jsj_id <> ''
  AND (ju.user_id, ju.F_Month) IN (
      SELECT user_id, F_Month FROM (
          SELECT user_id, F_Month, COUNT(DISTINCT jsj_id) AS c
          FROM lq_jinsanjiao_user
          WHERE F_DeleteMark = 0 AND jsj_id IS NOT NULL AND jsj_id <> ''
          GROUP BY user_id, F_Month HAVING c > 1
      ) x
  )
ORDER BY ju.F_Month, ju.user_name, ju.F_CreatorTime;

-- =============================================================================
-- 5. 回滚（仅在修复结果有误时执行）
-- =============================================================================

-- UPDATE lq_xh_jksyj j JOIN bak_jsjid_fix_20260729 k ON k.row_id = j.F_Id AND k.biz_type = '耗卡'
-- SET j.F_jsjid = k.old_jsj_id;
-- UPDATE lq_kd_jksyj j JOIN bak_jsjid_fix_20260729 k ON k.row_id = j.F_Id AND k.biz_type = '开单'
-- SET j.jsj_id = k.old_jsj_id;
-- UPDATE lq_hytk_jksyj j JOIN bak_jsjid_fix_20260729 k ON k.row_id = j.F_Id AND k.biz_type = '退卡'
-- SET j.F_jsjid = k.old_jsj_id;

-- =============================================================================
-- 6. 收尾：删除临时视图
-- =============================================================================

-- DROP VIEW IF EXISTS v_tmp_jsj_user_month;