Replace sub.guild_id = dl.guild_id with sub.guild_id = $1 in all
5 stats queries to satisfy PostgreSQL GROUP BY strictness.
PostgreSQL requires columns in correlated subqueries to appear in
the outer GROUP BY, but guild_id was not grouped. Since the outer
query already filters by WHERE guild_id = $1, using $1 directly
in the subquery is correct and avoids the error.
Affected: GetLeaderboard, GetUserStats, GetStatsInRange,
GetYearlyLeaderboard, GetUserYearlyStats
Replace GROUP BY user_id,username with GROUP BY user_id and a
correlated subquery that picks the latest username per user.
This fixes the case where a user changes their Discord display
name and appears as two separate leaderboard entries.
Affected queries: GetLeaderboard, GetUserStats, GetStatsInRange,
GetYearlyLeaderboard, GetUserYearlyStats.
Also keeps the write-time username sync in AddLog for long-term
data cleanup.
Signed-off-by: Blake Ridgway <blake@blakeridgway.com>
- db/db.go: Add write-time username sync in AddLog to prevent duplicate
leaderboard entries when users change display names. Revert correlated
subqueries back to GROUP BY user_id, username (simpler approach).
- db/db.go: Early return in onMessageCreate if bot already reacted (prevents
duplicate emoji reactions on Discord reconnection).
- bot/bot.go: Add /version slash command with build version injection.
- main.go: Add version variable with ldflags support.
- Makefile: Add dns-fix, test, vet, build-native, pg-*, boot targets.
Prepend test+vet to deploy pipeline. Add version ldflags to build.
- db/migrations/002_fix_usernames.sql: One-time SQL to backfill old usernames.
- scripts/fix-jail-dns.sh: Script to update jail resolv.conf from 8.8.8.8
to reachable nameservers (1.1.1.1, 9.9.9.9, 172.16.0.1).
Signed-off-by: Blake Ridgway <blake@blakeridgway.com>
