#!/bin/sh
# Backfill stale usernames in distance_logs so every row for a given user
# uses the most recent username. This prevents duplicate leaderboard entries
# if any user changed their Discord display name.
#
# Usage:
#   On the host:  sh scripts/backfill-usernames.sh
#   Remotely:     ssh root@172.16.0.101 "sh -s" < scripts/backfill-usernames.sh

JAIL_ROOT="/jails/cyclingbot"
PG_JAIL="postgres"
ENV_FILE="${JAIL_ROOT}/var/db/cyclingbot/.env"

if [ ! -f "$ENV_FILE" ]; then
    echo "Error: $ENV_FILE not found"
    exit 1
fi

# Extract DATABASE_URL from .env (ignore quoted/unquoted values)
DATABASE_URL=$(grep '^DATABASE_URL=' "$ENV_FILE" | head -1 | cut -d= -f2-)

if [ -z "$DATABASE_URL" ]; then
    echo "Error: DATABASE_URL not found in $ENV_FILE"
    exit 1
fi

echo "=== Backfilling stale usernames ==="
echo ""

jexec "$PG_JAIL" psql "$DATABASE_URL" -c "
UPDATE distance_logs dl
SET username = u.latest_username
FROM (
    SELECT user_id, guild_id, username AS latest_username
    FROM (
        SELECT user_id, guild_id, username,
               ROW_NUMBER() OVER (
                   PARTITION BY user_id, guild_id
                   ORDER BY logged_at DESC
               ) AS rn
        FROM distance_logs
    ) sub
    WHERE sub.rn = 1
) u
WHERE dl.user_id = u.user_id
  AND dl.guild_id = u.guild_id
  AND dl.username != u.latest_username;
"

echo ""
echo "=== Backfill complete ==="