eb889236ef
The 🔁 emoji was being added for duplicate rides. Now we just log the duplicate server-side without adding any reaction to the message. Signed-off-by: Blake Ridgway <blake@blakeridgway.com>
53 lines
1.4 KiB
Bash
53 lines
1.4 KiB
Bash
#!/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 ==="
|
|
|