-- 修复积分统计数据
-- 根据 points_record 表的记录,重新计算每个用户的 totalEarned 和 totalSpent

-- 1. 更新 totalEarned (累计获得) - 统计所有 type='earn' 的记录
UPDATE user_points up
SET totalEarned = (
    SELECT COALESCE(SUM(points), 0)
    FROM points_record pr
    WHERE pr.user_id = up.user_id
    AND pr.type = 'earn'
);

-- 2. 更新 totalSpent (累计消费) - 统计所有 type='consume' 或 type='spend' 的记录
UPDATE user_points up
SET totalSpent = (
    SELECT COALESCE(SUM(ABS(points)), 0)
    FROM points_record pr
    WHERE pr.user_id = up.user_id
    AND pr.type IN ('consume', 'spend')
);

-- 3. 更新 balance (当前余额) - totalEarned - totalSpent
UPDATE user_points
SET balance = totalEarned - totalSpent;

-- 4. 验证修复结果
SELECT 
    up.id,
    up.user_id,
    up.balance as '当前余额',
    up.totalEarned as '累计获得',
    up.totalSpent as '累计消费',
    (SELECT COUNT(*) FROM points_record WHERE user_id = up.user_id AND type = 'earn') as '获得记录数',
    (SELECT COUNT(*) FROM points_record WHERE user_id = up.user_id AND type IN ('consume', 'spend')) as '消费记录数'
FROM user_points up
ORDER BY up.id;