-- ============================================
-- 修复消耗单主表（lq_xh_hyhk）F_OvertimeSgfy 和 sgfy 历史数据
-- ============================================
-- 背景：主表加班手工费已改为「健康师原始手工费之和 × 加班系数」，科技部不参与加班。
-- 本脚本修复历史数据，使 F_OvertimeSgfy 和 sgfy 符合新逻辑。
--
-- 修复逻辑：
--   F_OvertimeSgfy = 健康师原始手工费之和 × F_OvertimeCoefficient
--   sgfy = F_OriginalSgfy + F_OvertimeSgfy
--
-- 健康师原始手工费：优先用 F_OriginalLaborCost，为空时用 F_LaborCost - F_OvertimeLaborCost
-- F_OriginalSgfy 为空时：用 健康师原始之和 + 科技部原始之和 补全
--
-- 执行前请先备份！建议在测试环境验证后再在生产环境执行。

-- ============================================
-- 1. 预览：查看需要修复的记录（只读，可先执行验证）
-- ============================================
/*
SELECT 
    h.F_Id,
    h.F_OriginalSgfy AS 当前原始手工费,
    h.F_OvertimeSgfy AS 当前加班手工费,
    h.sgfy AS 当前最终手工费,
    h.F_OvertimeCoefficient AS 加班系数,
    COALESCE(j.jks_sum, 0) AS 健康师原始手工费之和,
    COALESCE(j.jks_sum, 0) * COALESCE(h.F_OvertimeCoefficient, 0) AS 新加班手工费,
    COALESCE(h.F_OriginalSgfy, COALESCE(j.jks_sum, 0) + COALESCE(k.kjb_sum, 0)) + (COALESCE(j.jks_sum, 0) * COALESCE(h.F_OvertimeCoefficient, 0)) AS 新最终手工费
FROM lq_xh_hyhk h
LEFT JOIN (
    SELECT glkdbh,
        SUM(COALESCE(F_OriginalLaborCost, GREATEST(0, COALESCE(F_LaborCost, 0) - COALESCE(F_OvertimeLaborCost, 0)))) AS jks_sum
    FROM lq_xh_jksyj
    WHERE F_IsEffective = 1
    GROUP BY glkdbh
) j ON h.F_Id = j.glkdbh
LEFT JOIN (
    SELECT glkdbh,
        SUM(COALESCE(F_OriginalLaborCost, F_LaborCost)) AS kjb_sum
    FROM lq_xh_kjbsyj
    WHERE F_IsEffective = 1
    GROUP BY glkdbh
) k ON h.F_Id = k.glkdbh
WHERE h.F_IsEffective = 1
  AND (
      COALESCE(h.F_OvertimeSgfy, 0) != COALESCE(j.jks_sum, 0) * COALESCE(h.F_OvertimeCoefficient, 0)
      OR COALESCE(h.sgfy, 0) != COALESCE(h.F_OriginalSgfy, COALESCE(j.jks_sum, 0) + COALESCE(k.kjb_sum, 0)) + (COALESCE(j.jks_sum, 0) * COALESCE(h.F_OvertimeCoefficient, 0))
  )
LIMIT 50;
*/

-- ============================================
-- 2. 执行修复：更新 F_OvertimeSgfy 和 sgfy
-- ============================================
UPDATE lq_xh_hyhk h
INNER JOIN (
    SELECT glkdbh,
        SUM(COALESCE(F_OriginalLaborCost, GREATEST(0, COALESCE(F_LaborCost, 0) - COALESCE(F_OvertimeLaborCost, 0)))) AS jks_sum
    FROM lq_xh_jksyj
    WHERE F_IsEffective = 1
    GROUP BY glkdbh
) j ON h.F_Id = j.glkdbh
LEFT JOIN (
    SELECT glkdbh,
        SUM(COALESCE(F_OriginalLaborCost, F_LaborCost)) AS kjb_sum
    FROM lq_xh_kjbsyj
    WHERE F_IsEffective = 1
    GROUP BY glkdbh
) k ON h.F_Id = k.glkdbh
SET
    h.F_OvertimeSgfy = COALESCE(j.jks_sum, 0) * COALESCE(h.F_OvertimeCoefficient, 0),
    h.sgfy = COALESCE(h.F_OriginalSgfy, COALESCE(j.jks_sum, 0) + COALESCE(k.kjb_sum, 0)) + (COALESCE(j.jks_sum, 0) * COALESCE(h.F_OvertimeCoefficient, 0))
WHERE h.F_IsEffective = 1;

-- 说明：上述 UPDATE 使用 INNER JOIN j，因此仅更新「存在健康师业绩」的耗卡记录。
-- 若耗卡仅有科技部、无健康师，则 j 子查询无结果，该记录不会被更新。
-- 对于「仅科技部」的耗卡，正确逻辑应为：F_OvertimeSgfy=0，sgfy=F_OriginalSgfy，需单独处理。

-- ============================================
-- 3. 补充修复：仅科技部、无健康师的耗卡（F_OvertimeSgfy 应为 0，sgfy = 原始手工费）
-- ============================================
UPDATE lq_xh_hyhk h
LEFT JOIN (
    SELECT glkdbh
    FROM lq_xh_jksyj
    WHERE F_IsEffective = 1
    GROUP BY glkdbh
) j ON h.F_Id = j.glkdbh
LEFT JOIN (
    SELECT glkdbh,
        SUM(COALESCE(F_OriginalLaborCost, F_LaborCost)) AS kjb_sum
    FROM lq_xh_kjbsyj
    WHERE F_IsEffective = 1
    GROUP BY glkdbh
) k ON h.F_Id = k.glkdbh
SET
    h.F_OvertimeSgfy = 0,
    h.sgfy = COALESCE(h.F_OriginalSgfy, COALESCE(k.kjb_sum, 0), h.sgfy)
WHERE h.F_IsEffective = 1
  AND j.glkdbh IS NULL
  AND (COALESCE(h.F_OvertimeSgfy, 0) != 0);

-- ============================================
-- 4. 验证修复结果（可选）
-- ============================================
-- 检查是否仍有不一致记录（应返回 0）
/*
SELECT COUNT(*) AS 仍不一致记录数
FROM lq_xh_hyhk h
LEFT JOIN (
    SELECT glkdbh,
        SUM(COALESCE(F_OriginalLaborCost, GREATEST(0, COALESCE(F_LaborCost, 0) - COALESCE(F_OvertimeLaborCost, 0)))) AS jks_sum
    FROM lq_xh_jksyj
    WHERE F_IsEffective = 1
    GROUP BY glkdbh
) j ON h.F_Id = j.glkdbh
LEFT JOIN (
    SELECT glkdbh,
        SUM(COALESCE(F_OriginalLaborCost, F_LaborCost)) AS kjb_sum
    FROM lq_xh_kjbsyj
    WHERE F_IsEffective = 1
    GROUP BY glkdbh
) k ON h.F_Id = k.glkdbh
WHERE h.F_IsEffective = 1
  AND (
      COALESCE(h.F_OvertimeSgfy, 0) != COALESCE(j.jks_sum, 0) * COALESCE(h.F_OvertimeCoefficient, 0)
      OR COALESCE(h.sgfy, 0) != COALESCE(h.F_OriginalSgfy, COALESCE(j.jks_sum, 0) + COALESCE(k.kjb_sum, 0)) + (COALESCE(j.jks_sum, 0) * COALESCE(h.F_OvertimeCoefficient, 0))
  );
*/