-- EnglishSpeak: chuẩn hóa student_content_progress.xp_earned
-- Quy tắc: xp_earned chỉ lưu Completion XP của Activity.
-- Practice XP / New Best XP vẫn giữ nguyên trong student_xp_events
-- và không bị trừ khỏi students.lifetime_xp.

UPDATE student_content_progress AS scp
LEFT JOIN (
    SELECT
        student_id,
        activity_id,
        MAX(xp_amount) AS completion_xp
    FROM student_xp_events
    WHERE event_type = 'completion'
    GROUP BY student_id, activity_id
) AS completion_events
    ON completion_events.student_id = scp.student_id
   AND completion_events.activity_id = scp.content_id
SET scp.xp_earned = COALESCE(completion_events.completion_xp, 0)
WHERE scp.content_type = 'activity';
