-- =============================================================================
-- 单人修复：冷忠翠（用户ID 13198577167）健康师业绩金三角ID为空
--
-- 背景：客户端异步取金三角ID，编辑入口未回填耗卡日期 / 接口未返回即提交时会落空，
--       导致金三角业绩页少算，数据看板正常，两边对不上。
-- 口径：她 2025-11 至 2026-07 每月绑定均为「精英队」841835190497051909，
--       按「用户ID + 业绩月份」到 lq_jinsanjiao_user 取当月绑定回填，仅补空值。
--
-- 默认范围：2026年7月（8 行 / ¥2711.33，即当前反馈的差异）
-- 全量范围：放开第 5 节
-- =============================================================================

SET @user_id    = '13198577167';
SET @start_time = '2026-07-01';
SET @end_time   = '2026-08-01';

-- =============================================================================
-- 1. 修复前预览
-- =============================================================================

SELECT hk.F_Id            AS 耗卡单号,
       hk.hksj            AS 耗卡时间,
       hk.mdmc            AS 门店,
       hk.hymc            AS 会员,
       j.F_ItemName       AS 品项,
       j.jksyj            AS 消耗业绩,
       j.F_jsjid          AS 当前金三角ID,
       ju.jsj_id          AS 应回填金三角ID,
       jsj.jsj            AS 战队
FROM lq_xh_jksyj j
JOIN lq_xh_hyhk hk ON hk.F_Id = j.glkdbh
LEFT JOIN lq_jinsanjiao_user ju
       ON ju.user_id = j.jkszh
      AND ju.F_Month = DATE_FORMAT(j.yjsj, '%Y%m')
      AND ju.F_DeleteMark = 0
LEFT JOIN lq_ycsd_jsj jsj ON jsj.F_Id = ju.jsj_id
WHERE j.F_IsEffective = 1
  AND j.jkszh = @user_id
  AND (j.F_jsjid IS NULL OR j.F_jsjid = '')
  AND j.yjsj >= @start_time
  AND j.yjsj <  @end_time
ORDER BY hk.hksj;

-- =============================================================================
-- 2. 备份
-- =============================================================================

-- 注意：GTID 模式禁用 CREATE TABLE ... SELECT，必须先建表再 INSERT SELECT
DROP TABLE IF EXISTS bak_lzc_jsjid_20260729;
CREATE TABLE bak_lzc_jsjid_20260729 (
    row_id     VARCHAR(50)    NOT NULL COMMENT '业绩行主键 lq_xh_jksyj.F_Id',
    glkdbh     VARCHAR(50)    NULL     COMMENT '关联耗卡单号',
    jkszh      VARCHAR(50)    NULL     COMMENT '健康师用户ID',
    jksxm      VARCHAR(50)    NULL     COMMENT '健康师姓名',
    yjsj       DATETIME       NULL     COMMENT '业绩时间',
    jksyj      DECIMAL(18, 2) NULL     COMMENT '健康师业绩',
    old_jsj_id VARCHAR(255)   NULL     COMMENT '修复前金三角ID',
    PRIMARY KEY (row_id)
) COMMENT '冷忠翠金三角ID修复备份 20260729';

INSERT INTO bak_lzc_jsjid_20260729 (row_id, glkdbh, jkszh, jksxm, yjsj, jksyj, old_jsj_id)
SELECT j.F_Id, j.glkdbh, j.jkszh, j.jksxm, j.yjsj, j.jksyj, j.F_jsjid
FROM lq_xh_jksyj j
WHERE j.F_IsEffective = 1
  AND j.jkszh = @user_id
  AND (j.F_jsjid IS NULL OR j.F_jsjid = '')
  AND j.yjsj >= @start_time
  AND j.yjsj <  @end_time;

SELECT COUNT(*) AS 备份行数, ROUND(SUM(jksyj), 2) AS 涉及业绩 FROM bak_lzc_jsjid_20260729;

-- =============================================================================
-- 3. 执行修复
-- =============================================================================

UPDATE lq_xh_jksyj j
JOIN lq_jinsanjiao_user ju
  ON ju.user_id = j.jkszh
 AND ju.F_Month = DATE_FORMAT(j.yjsj, '%Y%m')
 AND ju.F_DeleteMark = 0
 AND ju.jsj_id IS NOT NULL
 AND ju.jsj_id <> ''
SET j.F_jsjid = ju.jsj_id
WHERE j.F_IsEffective = 1
  AND j.jkszh = @user_id
  AND (j.F_jsjid IS NULL OR j.F_jsjid = '')
  AND j.yjsj >= @start_time
  AND j.yjsj <  @end_time;

-- =============================================================================
-- 4. 验证：修复后应全部归入「精英队」，无「未挂战队」行
-- =============================================================================

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 = @user_id
  AND j.yjsj >= @start_time
  AND j.yjsj <  @end_time
GROUP BY 战队;

-- =============================================================================
-- 5. 全量修复（可选）：她还有以下缺口，确认后放开执行
--    耗卡 202511(5行/722.77)  202512(1行/120)   202601(5行/2199.92)
--         202604(6行/1618.28) 202605(5行/1959.2) 202606(5行/0)
--    开单 202601(1行/0) 202603(2行/1399.6) 202606(2行/0)
-- =============================================================================

-- UPDATE lq_xh_jksyj j
-- JOIN lq_jinsanjiao_user ju
--   ON ju.user_id = j.jkszh AND ju.F_Month = DATE_FORMAT(j.yjsj, '%Y%m')
--  AND ju.F_DeleteMark = 0 AND ju.jsj_id IS NOT NULL AND ju.jsj_id <> ''
-- SET j.F_jsjid = ju.jsj_id
-- WHERE j.F_IsEffective = 1 AND j.jkszh = @user_id
--   AND (j.F_jsjid IS NULL OR j.F_jsjid = '');

-- UPDATE lq_kd_jksyj j
-- JOIN lq_jinsanjiao_user ju
--   ON ju.user_id = j.jkszh AND ju.F_Month = DATE_FORMAT(j.yjsj, '%Y%m')
--  AND ju.F_DeleteMark = 0 AND ju.jsj_id IS NOT NULL AND ju.jsj_id <> ''
-- SET j.jsj_id = ju.jsj_id
-- WHERE j.F_IsEffective = 1 AND j.jkszh = @user_id
--   AND (j.jsj_id IS NULL OR j.jsj_id = '');

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

-- UPDATE lq_xh_jksyj j
-- JOIN bak_lzc_jsjid_20260729 k ON k.row_id = j.F_Id
-- SET j.F_jsjid = k.old_jsj_id;