-- 修复会员沉睡天数数据
-- 问题：耗卡记录的手工费为0时，LastConsumeTime未更新，导致沉睡天数计算错误
-- 解决方案：根据最新的有效耗卡记录更新LastConsumeTime

-- 1. 修复单个会员（陈秋竹）
UPDATE lq_khxx kh
INNER JOIN (
    SELECT 
        hy,
        MAX(hksj) as last_consume_time
    FROM lq_xh_hyhk
    WHERE hymc = '陈秋竹'
        AND F_IsEffective = 1
        AND xfje > 0
    GROUP BY hy
) consume ON kh.F_Id = consume.hy
SET 
    kh.F_LastConsumeTime = consume.last_consume_time,
    kh.F_SleepDays = GREATEST(0, DATEDIFF(CURDATE(), consume.last_consume_time))
WHERE kh.khmc = '陈秋竹'
    AND kh.F_IsEffective = 1;

-- 2. 批量修复所有会员（根据最新的有效耗卡记录更新LastConsumeTime）
-- 注意：执行此SQL前请先备份数据库
UPDATE lq_khxx kh
INNER JOIN (
    SELECT 
        hy,
        MAX(hksj) as last_consume_time
    FROM lq_xh_hyhk
    WHERE F_IsEffective = 1
        AND xfje > 0
    GROUP BY hy
) consume ON kh.F_Id = consume.hy
SET 
    kh.F_LastConsumeTime = consume.last_consume_time,
    kh.F_SleepDays = GREATEST(0, DATEDIFF(CURDATE(), consume.last_consume_time))
WHERE kh.F_IsEffective = 1
    AND (
        -- 只更新需要修复的记录：LastConsumeTime为空或LastConsumeTime小于最新的耗卡时间
        kh.F_LastConsumeTime IS NULL 
        OR kh.F_LastConsumeTime < consume.last_consume_time
    );