-- 修复会员沉睡天数数据
-- 问题：LastConsumeTime 与有效耗卡 MAX(hksj) 不一致（含零金额耗卡、LastConsumeTime 偏新/偏旧）
-- 解决方案：不限制消耗金额，按有效耗卡 MAX(hksj) 纠正 LastConsumeTime，并按运行时/批量口径重算 SleepDays、SleepStartTime
-- 沉睡阈值默认 60 天（与 MemberInfoUpdateConfig.SleepDaysThresholds 最小值一致）

-- 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
    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)),
    kh.F_SleepStartTime = CASE 
        WHEN GREATEST(0, DATEDIFF(CURDATE(), consume.last_consume_time)) >= 60
            AND kh.F_SleepStartTime IS NULL 
        THEN DATE(consume.last_consume_time)
        WHEN GREATEST(0, DATEDIFF(CURDATE(), consume.last_consume_time)) < 60
        THEN NULL
        ELSE kh.F_SleepStartTime
    END
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
    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)),
    kh.F_SleepStartTime = CASE 
        WHEN GREATEST(0, DATEDIFF(CURDATE(), consume.last_consume_time)) >= 60
            AND kh.F_SleepStartTime IS NULL 
        THEN DATE(consume.last_consume_time)
        WHEN GREATEST(0, DATEDIFF(CURDATE(), consume.last_consume_time)) < 60
        THEN NULL
        ELSE kh.F_SleepStartTime
    END
WHERE kh.F_IsEffective = 1
    AND (
        -- 纠正 LastConsumeTime 为空、偏旧或偏新的脏数据
        kh.F_LastConsumeTime IS NULL 
        OR kh.F_LastConsumeTime <> consume.last_consume_time
    );

-- 3. 无有效耗卡会员的 fallback（LastConsumeTime 置空，SleepDays 按 F_CreateTime 计算）
-- 建议优先调用 API：POST /api/Extend/LqKhxx/BatchUpdateMemberInfo
-- 若需 SQL 手工修复，可参考：
-- UPDATE lq_khxx kh
-- LEFT JOIN (
--     SELECT hy, MAX(hksj) AS last_consume_time
--     FROM lq_xh_hyhk
--     WHERE F_IsEffective = 1
--     GROUP BY hy
-- ) consume ON kh.F_Id = consume.hy
-- SET
--     kh.F_LastConsumeTime = NULL,
--     kh.F_SleepDays = GREATEST(0, DATEDIFF(CURDATE(), kh.F_CreateTime)),
--     kh.F_SleepStartTime = CASE
--         WHEN GREATEST(0, DATEDIFF(CURDATE(), kh.F_CreateTime)) >= 60 AND kh.F_SleepStartTime IS NULL
--         THEN DATE(kh.F_CreateTime)
--         WHEN GREATEST(0, DATEDIFF(CURDATE(), kh.F_CreateTime)) < 60
--         THEN NULL
--         ELSE kh.F_SleepStartTime
--     END
-- WHERE kh.F_IsEffective = 1
--     AND consume.hy IS NULL;