-- One-time migration: backfill old username entries when users change display names.
-- The write-time UPDATE in db.go AddLog() handles future changes automatically.
-- Run once after deploying:  psql -d YOUR_DATABASE_URL -f db/migrations/002_fix_usernames.sql

UPDATE distance_logs d
SET username = (
    SELECT username
    FROM distance_logs sub
    WHERE sub.user_id = d.user_id
      AND sub.guild_id = d.guild_id
    ORDER BY logged_at DESC
    LIMIT 1
)
WHERE username <> (
    SELECT username
    FROM distance_logs sub
    WHERE sub.user_id = d.user_id
      AND sub.guild_id = d.guild_id
    ORDER BY logged_at DESC
    LIMIT 1
);