a geicko-2 based round robin ranking system designed to test c++ battleship submissions
battleship.dunkirk.sh
dunkirk.sh
1package storage
2
3import (
4 "database/sql"
5 "math"
6 "time"
7
8 _ "github.com/mattn/go-sqlite3"
9)
10
11var DB *sql.DB
12
13type LeaderboardEntry struct {
14 Username string
15 Wins int
16 Losses int
17 WinPct float64
18 Rating int
19 RD int
20 AvgMoves float64
21 Stage string
22 LastPlayed time.Time
23 IsPending bool
24 IsBroken bool
25 FailureMessage string
26}
27
28type Submission struct {
29 ID int
30 Username string
31 Filename string
32 UploadTime time.Time
33 Status string
34 IsActive bool
35 FailureMessage string
36}
37
38type SubmissionWithStats struct {
39 Submission
40 Rating int
41 RD int
42 Wins int
43 Losses int
44 WinPct float64
45 AvgMoves float64
46 LastPlayed time.Time
47 HasMatches bool
48}
49
50type Tournament struct {
51 ID int
52 CreatedAt time.Time
53 Status string
54 CurrentRound int
55 WinnerID int
56}
57
58type BracketMatch struct {
59 ID int
60 TournamentID int
61 Round int
62 Position int
63 Player1ID int
64 Player2ID int
65 WinnerID int
66 Player1Wins int
67 Player2Wins int
68 Player1Moves int
69 Player2Moves int
70 Status string
71 Player1Name string
72 Player2Name string
73}
74
75type MatchResult struct {
76 Player1Username string
77 Player2Username string
78 WinnerUsername string
79 AvgMoves int
80}
81
82type RatingHistoryPoint struct {
83 Rating int
84 RD int
85 Volatility float64
86 Timestamp time.Time
87 MatchID int
88}
89
90func InitDB(path string) (*sql.DB, error) {
91 db, err := sql.Open("sqlite3", path+"?parseTime=true")
92 if err != nil {
93 return nil, err
94 }
95
96 schema := `
97 CREATE TABLE IF NOT EXISTS users (
98 id INTEGER PRIMARY KEY AUTOINCREMENT,
99 username TEXT UNIQUE NOT NULL,
100 name TEXT NOT NULL,
101 bio TEXT,
102 link TEXT,
103 public_key TEXT UNIQUE NOT NULL,
104 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
105 last_login_at TIMESTAMP
106 );
107
108 CREATE TABLE IF NOT EXISTS submissions (
109 id INTEGER PRIMARY KEY AUTOINCREMENT,
110 username TEXT NOT NULL,
111 filename TEXT NOT NULL,
112 upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
113 status TEXT DEFAULT 'pending',
114 is_active BOOLEAN DEFAULT 1,
115 glicko_rating REAL DEFAULT 1500.0,
116 glicko_rd REAL DEFAULT 350.0,
117 glicko_volatility REAL DEFAULT 0.06,
118 failure_message TEXT
119 );
120
121 CREATE TABLE IF NOT EXISTS tournaments (
122 id INTEGER PRIMARY KEY AUTOINCREMENT,
123 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
124 status TEXT DEFAULT 'active',
125 current_round INTEGER DEFAULT 1,
126 winner_id INTEGER,
127 FOREIGN KEY (winner_id) REFERENCES submissions(id)
128 );
129
130 CREATE TABLE IF NOT EXISTS bracket_matches (
131 id INTEGER PRIMARY KEY AUTOINCREMENT,
132 tournament_id INTEGER,
133 round INTEGER,
134 position INTEGER,
135 player1_id INTEGER,
136 player2_id INTEGER,
137 winner_id INTEGER,
138 player1_wins INTEGER DEFAULT 0,
139 player2_wins INTEGER DEFAULT 0,
140 player1_moves INTEGER,
141 player2_moves INTEGER,
142 status TEXT DEFAULT 'pending',
143 timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
144 FOREIGN KEY (tournament_id) REFERENCES tournaments(id),
145 FOREIGN KEY (player1_id) REFERENCES submissions(id),
146 FOREIGN KEY (player2_id) REFERENCES submissions(id),
147 FOREIGN KEY (winner_id) REFERENCES submissions(id)
148 );
149
150 CREATE TABLE IF NOT EXISTS matches (
151 id INTEGER PRIMARY KEY AUTOINCREMENT,
152 player1_id INTEGER,
153 player2_id INTEGER,
154 winner_id INTEGER,
155 player1_wins INTEGER DEFAULT 0,
156 player2_wins INTEGER DEFAULT 0,
157 player1_moves INTEGER,
158 player2_moves INTEGER,
159 is_valid BOOLEAN DEFAULT 1,
160 timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
161 FOREIGN KEY (player1_id) REFERENCES submissions(id),
162 FOREIGN KEY (player2_id) REFERENCES submissions(id),
163 FOREIGN KEY (winner_id) REFERENCES submissions(id)
164 );
165
166 CREATE TABLE IF NOT EXISTS rating_history (
167 id INTEGER PRIMARY KEY AUTOINCREMENT,
168 submission_id INTEGER NOT NULL,
169 rating REAL NOT NULL,
170 rd REAL NOT NULL,
171 volatility REAL NOT NULL,
172 match_id INTEGER,
173 timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
174 FOREIGN KEY (submission_id) REFERENCES submissions(id),
175 FOREIGN KEY (match_id) REFERENCES matches(id)
176 );
177
178 CREATE INDEX IF NOT EXISTS idx_bracket_matches_tournament ON bracket_matches(tournament_id);
179 CREATE INDEX IF NOT EXISTS idx_bracket_matches_status ON bracket_matches(status);
180 CREATE INDEX IF NOT EXISTS idx_tournaments_status ON tournaments(status);
181 CREATE INDEX IF NOT EXISTS idx_matches_player1 ON matches(player1_id);
182 CREATE INDEX IF NOT EXISTS idx_matches_player2 ON matches(player2_id);
183 CREATE INDEX IF NOT EXISTS idx_matches_valid ON matches(is_valid);
184 CREATE INDEX IF NOT EXISTS idx_submissions_username ON submissions(username);
185 CREATE INDEX IF NOT EXISTS idx_submissions_status ON submissions(status);
186 CREATE INDEX IF NOT EXISTS idx_submissions_active ON submissions(is_active);
187 CREATE UNIQUE INDEX IF NOT EXISTS idx_matches_unique_pair ON matches(player1_id, player2_id, is_valid) WHERE is_valid = 1;
188 CREATE INDEX IF NOT EXISTS idx_rating_history_submission ON rating_history(submission_id, timestamp);
189 `
190
191 _, err = db.Exec(schema)
192 return db, err
193}
194
195func GetLeaderboard(limit int) ([]LeaderboardEntry, error) {
196 // Get submissions with matches
197 // Rankings use Glicko-2 with proper rating periods:
198 // - All round-robin matches are batched together before rating updates
199 // - This prevents last-submitter bias from path-dependent rating changes
200 query := `
201 SELECT
202 s.username,
203 COALESCE(s.glicko_rating, 1500.0) as rating,
204 COALESCE(s.glicko_rd, 350.0) as rd,
205 SUM(CASE WHEN m.player1_id = s.id THEN m.player1_wins WHEN m.player2_id = s.id THEN m.player2_wins ELSE 0 END) as total_wins,
206 SUM(CASE WHEN m.player1_id = s.id THEN m.player2_wins WHEN m.player2_id = s.id THEN m.player1_wins ELSE 0 END) as total_losses,
207 AVG(CASE WHEN m.player1_id = s.id THEN m.player1_moves ELSE m.player2_moves END) as avg_moves,
208 MAX(m.timestamp) as last_played,
209 0 as is_pending,
210 0 as is_broken,
211 '' as failure_message
212 FROM submissions s
213 LEFT JOIN matches m ON (m.player1_id = s.id OR m.player2_id = s.id) AND m.is_valid = 1
214 WHERE s.is_active = 1 AND s.status NOT IN ('compilation_failed', 'match_failed')
215 GROUP BY s.username, s.glicko_rating, s.glicko_rd
216 HAVING COUNT(m.id) > 0
217
218 UNION ALL
219
220 SELECT
221 s.username,
222 1500.0 as rating,
223 350.0 as rd,
224 0 as total_wins,
225 0 as total_losses,
226 999.0 as avg_moves,
227 s.upload_time as last_played,
228 1 as is_pending,
229 0 as is_broken,
230 '' as failure_message
231 FROM submissions s
232 LEFT JOIN matches m ON (m.player1_id = s.id OR m.player2_id = s.id) AND m.is_valid = 1
233 WHERE s.is_active = 1 AND s.status IN ('pending', 'testing', 'completed')
234 GROUP BY s.username, s.upload_time
235 HAVING COUNT(m.id) = 0
236
237 UNION ALL
238
239 SELECT
240 s.username,
241 0 as rating,
242 0 as rd,
243 0 as total_wins,
244 0 as total_losses,
245 999.0 as avg_moves,
246 s.upload_time as last_played,
247 0 as is_pending,
248 1 as is_broken,
249 COALESCE(s.failure_message, '') as failure_message
250 FROM submissions s
251 WHERE s.is_active = 1 AND s.status IN ('compilation_failed', 'match_failed')
252
253 ORDER BY is_broken ASC, is_pending ASC, rating DESC, total_wins DESC, avg_moves ASC
254 LIMIT ?
255 `
256
257 rows, err := DB.Query(query, limit)
258 if err != nil {
259 return nil, err
260 }
261 defer rows.Close()
262
263 var entries []LeaderboardEntry
264 for rows.Next() {
265 var e LeaderboardEntry
266 var lastPlayed string
267 var rating, rd float64
268 var isPending, isBroken int
269 err := rows.Scan(&e.Username, &rating, &rd, &e.Wins, &e.Losses, &e.AvgMoves, &lastPlayed, &isPending, &isBroken, &e.FailureMessage)
270 if err != nil {
271 return nil, err
272 }
273
274 e.Rating = int(rating)
275 e.RD = int(rd)
276 e.IsPending = isPending == 1
277 e.IsBroken = isBroken == 1
278
279 totalGames := e.Wins + e.Losses
280 if totalGames > 0 {
281 e.WinPct = float64(e.Wins) / float64(totalGames) * 100.0
282 }
283
284 e.LastPlayed, _ = time.Parse("2006-01-02 15:04:05", lastPlayed)
285 entries = append(entries, e)
286 }
287
288 return entries, rows.Err()
289}
290
291func AddSubmission(username, filename string) (int64, error) {
292 _, err := DB.Exec(
293 `UPDATE matches SET is_valid = 0
294 WHERE player1_id IN (SELECT id FROM submissions WHERE username = ?)
295 OR player2_id IN (SELECT id FROM submissions WHERE username = ?)`,
296 username, username,
297 )
298 if err != nil {
299 return 0, err
300 }
301
302 _, err = DB.Exec(
303 "UPDATE submissions SET is_active = 0 WHERE username = ?",
304 username,
305 )
306 if err != nil {
307 return 0, err
308 }
309
310 result, err := DB.Exec(
311 "INSERT INTO submissions (username, filename, is_active, glicko_rating, glicko_rd, glicko_volatility) VALUES (?, ?, 1, 1500.0, 350.0, 0.06)",
312 username, filename,
313 )
314 if err != nil {
315 return 0, err
316 }
317 return result.LastInsertId()
318}
319
320func AddMatch(player1ID, player2ID, winnerID, player1Wins, player2Wins, player1Moves, player2Moves int) (int64, error) {
321 result, err := DB.Exec(
322 "INSERT INTO matches (player1_id, player2_id, winner_id, player1_wins, player2_wins, player1_moves, player2_moves) VALUES (?, ?, ?, ?, ?, ?, ?)",
323 player1ID, player2ID, winnerID, player1Wins, player2Wins, player1Moves, player2Moves,
324 )
325 if err != nil {
326 return 0, err
327 }
328 return result.LastInsertId()
329}
330
331func UpdateSubmissionStatus(id int, status string) error {
332 _, err := DB.Exec("UPDATE submissions SET status = ? WHERE id = ?", status, id)
333 return err
334}
335
336func UpdateSubmissionStatusWithMessage(id int, status string, message string) error {
337 _, err := DB.Exec("UPDATE submissions SET status = ?, failure_message = ? WHERE id = ?", status, message, id)
338 return err
339}
340
341func GetPendingSubmissions() ([]Submission, error) {
342 rows, err := DB.Query(
343 "SELECT id, username, filename, upload_time, status FROM submissions WHERE status = 'pending' AND is_active = 1 ORDER BY upload_time",
344 )
345 if err != nil {
346 return nil, err
347 }
348 defer rows.Close()
349
350 var submissions []Submission
351 for rows.Next() {
352 var s Submission
353 err := rows.Scan(&s.ID, &s.Username, &s.Filename, &s.UploadTime, &s.Status)
354 if err != nil {
355 return nil, err
356 }
357 submissions = append(submissions, s)
358 }
359
360 return submissions, rows.Err()
361}
362
363func GetActiveSubmissions() ([]Submission, error) {
364 rows, err := DB.Query(
365 "SELECT id, username, filename, upload_time, status FROM submissions WHERE is_active = 1 AND status = 'completed' ORDER BY username",
366 )
367 if err != nil {
368 return nil, err
369 }
370 defer rows.Close()
371
372 var submissions []Submission
373 for rows.Next() {
374 var s Submission
375 err := rows.Scan(&s.ID, &s.Username, &s.Filename, &s.UploadTime, &s.Status)
376 if err != nil {
377 return nil, err
378 }
379 submissions = append(submissions, s)
380 }
381
382 return submissions, rows.Err()
383}
384
385func GetUserSubmissions(username string) ([]Submission, error) {
386 rows, err := DB.Query(
387 "SELECT id, username, filename, upload_time, status, is_active FROM submissions WHERE username = ? ORDER BY upload_time DESC LIMIT 10",
388 username,
389 )
390 if err != nil {
391 return nil, err
392 }
393 defer rows.Close()
394
395 var submissions []Submission
396 for rows.Next() {
397 var s Submission
398 err := rows.Scan(&s.ID, &s.Username, &s.Filename, &s.UploadTime, &s.Status, &s.IsActive)
399 if err != nil {
400 return nil, err
401 }
402 submissions = append(submissions, s)
403 }
404
405 return submissions, rows.Err()
406}
407
408func GetUserSubmissionsWithStats(username string) ([]SubmissionWithStats, error) {
409 query := `
410 SELECT
411 s.id,
412 s.username,
413 s.filename,
414 s.upload_time,
415 s.status,
416 s.is_active,
417 COALESCE(s.glicko_rating, 1500.0) as rating,
418 COALESCE(s.glicko_rd, 350.0) as rd,
419 COALESCE(SUM(CASE WHEN m.player1_id = s.id THEN m.player1_wins WHEN m.player2_id = s.id THEN m.player2_wins ELSE 0 END), 0) as total_wins,
420 COALESCE(SUM(CASE WHEN m.player1_id = s.id THEN m.player2_wins WHEN m.player2_id = s.id THEN m.player1_wins ELSE 0 END), 0) as total_losses,
421 COALESCE(AVG(CASE WHEN m.player1_id = s.id THEN m.player1_moves ELSE m.player2_moves END), 0) as avg_moves,
422 MAX(m.timestamp) as last_played,
423 COUNT(m.id) as match_count
424 FROM submissions s
425 LEFT JOIN matches m ON (m.player1_id = s.id OR m.player2_id = s.id) AND m.is_valid = 1
426 WHERE s.username = ?
427 GROUP BY s.id, s.username, s.filename, s.upload_time, s.status, s.is_active, s.glicko_rating, s.glicko_rd
428 ORDER BY s.upload_time DESC
429 LIMIT 10
430 `
431
432 rows, err := DB.Query(query, username)
433 if err != nil {
434 return nil, err
435 }
436 defer rows.Close()
437
438 var submissions []SubmissionWithStats
439 for rows.Next() {
440 var s SubmissionWithStats
441 var lastPlayed *string
442 var rating, rd float64
443 var matchCount int
444
445 err := rows.Scan(
446 &s.ID, &s.Username, &s.Filename, &s.UploadTime, &s.Status, &s.IsActive,
447 &rating, &rd, &s.Wins, &s.Losses, &s.AvgMoves, &lastPlayed, &matchCount,
448 )
449 if err != nil {
450 return nil, err
451 }
452
453 s.Rating = int(rating)
454 s.RD = int(rd)
455 s.HasMatches = matchCount > 0
456
457 totalGames := s.Wins + s.Losses
458 if totalGames > 0 {
459 s.WinPct = float64(s.Wins) / float64(totalGames) * 100.0
460 }
461
462 if lastPlayed != nil {
463 s.LastPlayed, _ = time.Parse("2006-01-02 15:04:05", *lastPlayed)
464 }
465
466 submissions = append(submissions, s)
467 }
468
469 return submissions, rows.Err()
470}
471
472func GetSubmissionByID(id int) (Submission, error) {
473 var sub Submission
474 err := DB.QueryRow(
475 "SELECT id, username, filename, upload_time, status FROM submissions WHERE id = ?",
476 id,
477 ).Scan(&sub.ID, &sub.Username, &sub.Filename, &sub.UploadTime, &sub.Status)
478 return sub, err
479}
480
481func HasMatchBetween(player1ID, player2ID int) (bool, error) {
482 var count int
483 err := DB.QueryRow(
484 `SELECT COUNT(*) FROM matches
485 WHERE is_valid = 1
486 AND ((player1_id = ? AND player2_id = ?) OR (player1_id = ? AND player2_id = ?))`,
487 player1ID, player2ID, player2ID, player1ID,
488 ).Scan(&count)
489 return count > 0, err
490}
491
492func GetAllMatches() ([]MatchResult, error) {
493 query := `
494 SELECT
495 s1.username as player1,
496 s2.username as player2,
497 sw.username as winner,
498 m.player1_moves as avg_moves
499 FROM matches m
500 JOIN submissions s1 ON m.player1_id = s1.id
501 JOIN submissions s2 ON m.player2_id = s2.id
502 JOIN submissions sw ON m.winner_id = sw.id
503 WHERE s1.is_active = 1 AND s2.is_active = 1 AND m.is_valid = 1
504 ORDER BY m.timestamp DESC
505 `
506
507 rows, err := DB.Query(query)
508 if err != nil {
509 return nil, err
510 }
511 defer rows.Close()
512
513 var matches []MatchResult
514 for rows.Next() {
515 var m MatchResult
516 err := rows.Scan(&m.Player1Username, &m.Player2Username, &m.WinnerUsername, &m.AvgMoves)
517 if err != nil {
518 return nil, err
519 }
520 matches = append(matches, m)
521 }
522
523 return matches, rows.Err()
524}
525
526func RecordRatingHistory(submissionID int, matchID int, rating, rd, volatility float64) error {
527 _, err := DB.Exec(
528 "INSERT INTO rating_history (submission_id, match_id, rating, rd, volatility) VALUES (?, ?, ?, ?, ?)",
529 submissionID, matchID, rating, rd, volatility,
530 )
531 return err
532}
533
534func GetRatingHistory(submissionID int) ([]RatingHistoryPoint, error) {
535 rows, err := DB.Query(`
536 SELECT rating, rd, volatility, timestamp, match_id
537 FROM rating_history
538 WHERE submission_id = ?
539 ORDER BY timestamp ASC
540 `, submissionID)
541 if err != nil {
542 return nil, err
543 }
544 defer rows.Close()
545
546 var history []RatingHistoryPoint
547 for rows.Next() {
548 var h RatingHistoryPoint
549 var rating, rd float64
550 var matchID sql.NullInt64
551 err := rows.Scan(&rating, &rd, &h.Volatility, &h.Timestamp, &matchID)
552 if err != nil {
553 return nil, err
554 }
555 h.Rating = int(rating)
556 h.RD = int(rd)
557 if matchID.Valid {
558 h.MatchID = int(matchID.Int64)
559 }
560 history = append(history, h)
561 }
562
563 return history, rows.Err()
564}
565
566func GetQueuedPlayerNames() []string {
567 rows, err := DB.Query(
568 "SELECT username FROM submissions WHERE (status = 'pending' OR status = 'testing') AND is_active = 1 ORDER BY upload_time",
569 )
570 if err != nil {
571 return []string{}
572 }
573 defer rows.Close()
574
575 var names []string
576 for rows.Next() {
577 var username string
578 if err := rows.Scan(&username); err == nil {
579 names = append(names, username)
580 }
581 }
582 return names
583}
584
585// Glicko-2 constants
586const (
587 glickoTau = 0.5
588 glickoEpsilon = 0.000001
589 glicko2Scale = 173.7178
590)
591
592type Glicko2Player struct {
593 Rating float64
594 RD float64
595 Volatility float64
596}
597
598type Glicko2Result struct {
599 OpponentRating float64
600 OpponentRD float64
601 Score float64
602}
603
604func toGlicko2Scale(rating, rd float64) (float64, float64) {
605 return (rating - 1500.0) / glicko2Scale, rd / glicko2Scale
606}
607
608func fromGlicko2Scale(mu, phi float64) (float64, float64) {
609 return mu*glicko2Scale + 1500.0, phi * glicko2Scale
610}
611
612func g(phi float64) float64 {
613 return 1.0 / math.Sqrt(1.0+3.0*phi*phi/(math.Pi*math.Pi))
614}
615
616func eFunc(mu, muJ, phiJ float64) float64 {
617 return 1.0 / (1.0 + math.Exp(-g(phiJ)*(mu-muJ)))
618}
619
620func updateGlicko2(player Glicko2Player, results []Glicko2Result) Glicko2Player {
621 mu, phi := toGlicko2Scale(player.Rating, player.RD)
622 sigma := player.Volatility
623
624 if len(results) == 0 {
625 phiStar := math.Sqrt(phi*phi + sigma*sigma)
626 rating, rd := fromGlicko2Scale(mu, phiStar)
627 return Glicko2Player{Rating: rating, RD: rd, Volatility: sigma}
628 }
629
630 var vInv float64
631 for _, result := range results {
632 muJ, phiJ := toGlicko2Scale(result.OpponentRating, result.OpponentRD)
633 gPhiJ := g(phiJ)
634 eVal := eFunc(mu, muJ, phiJ)
635 vInv += gPhiJ * gPhiJ * eVal * (1.0 - eVal)
636 }
637 v := 1.0 / vInv
638
639 var delta float64
640 for _, result := range results {
641 muJ, phiJ := toGlicko2Scale(result.OpponentRating, result.OpponentRD)
642 gPhiJ := g(phiJ)
643 eVal := eFunc(mu, muJ, phiJ)
644 delta += gPhiJ * (result.Score - eVal)
645 }
646 delta *= v
647
648 a := math.Log(sigma * sigma)
649 deltaSquared := delta * delta
650 phiSquared := phi * phi
651
652 fFunc := func(x float64) float64 {
653 eX := math.Exp(x)
654 num := eX * (deltaSquared - phiSquared - v - eX)
655 denom := 2.0 * (phiSquared + v + eX) * (phiSquared + v + eX)
656 return num/denom - (x-a)/(glickoTau*glickoTau)
657 }
658
659 A := a
660 var B float64
661 if deltaSquared > phiSquared+v {
662 B = math.Log(deltaSquared - phiSquared - v)
663 } else {
664 k := 1.0
665 for fFunc(a-k*glickoTau) < 0 {
666 k++
667 }
668 B = a - k*glickoTau
669 }
670
671 fA := fFunc(A)
672 fB := fFunc(B)
673
674 for math.Abs(B-A) > glickoEpsilon {
675 C := A + (A-B)*fA/(fB-fA)
676 fC := fFunc(C)
677
678 if fC*fB < 0 {
679 A = B
680 fA = fB
681 } else {
682 fA = fA / 2.0
683 }
684
685 B = C
686 fB = fC
687 }
688
689 sigmaNew := math.Exp(A / 2.0)
690 phiStar := math.Sqrt(phiSquared + sigmaNew*sigmaNew)
691 phiNew := 1.0 / math.Sqrt(1.0/(phiStar*phiStar)+1.0/v)
692
693 var muNew float64
694 for _, result := range results {
695 muJ, phiJ := toGlicko2Scale(result.OpponentRating, result.OpponentRD)
696 muNew += g(phiJ) * (result.Score - eFunc(mu, muJ, phiJ))
697 }
698 muNew = mu + phiNew*phiNew*muNew
699
700 rating, rd := fromGlicko2Scale(muNew, phiNew)
701 return Glicko2Player{Rating: rating, RD: rd, Volatility: sigmaNew}
702}
703
704func UpdateGlicko2Ratings(player1ID, player2ID, player1Wins, player2Wins int) error {
705 var p1Rating, p1RD, p1Vol, p2Rating, p2RD, p2Vol float64
706
707 err := DB.QueryRow(
708 "SELECT COALESCE(glicko_rating, 1500.0), COALESCE(glicko_rd, 350.0), COALESCE(glicko_volatility, 0.06) FROM submissions WHERE id = ?",
709 player1ID,
710 ).Scan(&p1Rating, &p1RD, &p1Vol)
711 if err != nil {
712 return err
713 }
714
715 err = DB.QueryRow(
716 "SELECT COALESCE(glicko_rating, 1500.0), COALESCE(glicko_rd, 350.0), COALESCE(glicko_volatility, 0.06) FROM submissions WHERE id = ?",
717 player2ID,
718 ).Scan(&p2Rating, &p2RD, &p2Vol)
719 if err != nil {
720 return err
721 }
722
723 totalGames := player1Wins + player2Wins
724 player1Score := float64(player1Wins) / float64(totalGames)
725 player2Score := float64(player2Wins) / float64(totalGames)
726
727 p1 := Glicko2Player{Rating: p1Rating, RD: p1RD, Volatility: p1Vol}
728 p1Results := []Glicko2Result{{OpponentRating: p2Rating, OpponentRD: p2RD, Score: player1Score}}
729 p1New := updateGlicko2(p1, p1Results)
730
731 p2 := Glicko2Player{Rating: p2Rating, RD: p2RD, Volatility: p2Vol}
732 p2Results := []Glicko2Result{{OpponentRating: p1Rating, OpponentRD: p1RD, Score: player2Score}}
733 p2New := updateGlicko2(p2, p2Results)
734
735 _, err = DB.Exec(
736 "UPDATE submissions SET glicko_rating = ?, glicko_rd = ?, glicko_volatility = ? WHERE id = ?",
737 p1New.Rating, p1New.RD, p1New.Volatility, player1ID,
738 )
739 if err != nil {
740 return err
741 }
742
743 _, err = DB.Exec(
744 "UPDATE submissions SET glicko_rating = ?, glicko_rd = ?, glicko_volatility = ? WHERE id = ?",
745 p2New.Rating, p2New.RD, p2New.Volatility, player2ID,
746 )
747 return err
748}
749
750// RecalculateAllGlicko2Ratings recalculates all Glicko-2 ratings from scratch
751// using proper rating periods where all matches for a player are batched together
752func RecalculateAllGlicko2Ratings() error {
753 // Reset all active submissions to initial ratings
754 _, err := DB.Exec(`
755 UPDATE submissions
756 SET glicko_rating = 1500.0, glicko_rd = 350.0, glicko_volatility = 0.06
757 WHERE is_active = 1 AND status = 'completed'
758 `)
759 if err != nil {
760 return err
761 }
762
763 // Snapshot all player ratings BEFORE any updates (critical for proper rating period)
764 initialRatings := make(map[int]Glicko2Player)
765 rows, err := DB.Query("SELECT id, glicko_rating, glicko_rd, glicko_volatility FROM submissions WHERE is_active = 1 AND status = 'completed'")
766 if err != nil {
767 return err
768 }
769 for rows.Next() {
770 var id int
771 var rating, rd, volatility float64
772 if err := rows.Scan(&id, &rating, &rd, &volatility); err != nil {
773 return err
774 }
775 initialRatings[id] = Glicko2Player{Rating: rating, RD: rd, Volatility: volatility}
776 }
777 rows.Close()
778
779 // For each player, collect ALL their match results and update once (proper rating period)
780 for playerID, player := range initialRatings {
781 // Collect ALL match results for this player in this rating period
782 var results []Glicko2Result
783
784 rows, err := DB.Query(`
785 SELECT
786 CASE WHEN player1_id = ? THEN player2_id ELSE player1_id END as opponent_id,
787 CASE WHEN player1_id = ? THEN player1_wins ELSE player2_wins END as my_wins,
788 CASE WHEN player1_id = ? THEN player2_wins ELSE player1_wins END as opponent_wins
789 FROM matches
790 WHERE (player1_id = ? OR player2_id = ?) AND is_valid = 1
791 ORDER BY timestamp ASC
792 `, playerID, playerID, playerID, playerID, playerID)
793
794 if err != nil {
795 continue
796 }
797
798 for rows.Next() {
799 var opponentID, myWins, opponentWins int
800 if err := rows.Scan(&opponentID, &myWins, &opponentWins); err != nil {
801 continue
802 }
803
804 // Get opponent's rating from initial snapshot (not from DB which may be updated)
805 opponent, ok := initialRatings[opponentID]
806 if !ok {
807 continue
808 }
809
810 totalGames := myWins + opponentWins
811 // Skip match pairs with no games played (prevents NaN from 0/0)
812 if totalGames == 0 {
813 continue
814 }
815 score := float64(myWins) / float64(totalGames)
816
817 results = append(results, Glicko2Result{
818 OpponentRating: opponent.Rating,
819 OpponentRD: opponent.RD,
820 Score: score,
821 })
822 }
823 rows.Close()
824
825 // Update this player's rating based on ALL results at once (proper rating period)
826 if len(results) > 0 {
827 newPlayer := updateGlicko2(player, results)
828
829 DB.Exec(
830 "UPDATE submissions SET glicko_rating = ?, glicko_rd = ?, glicko_volatility = ? WHERE id = ?",
831 newPlayer.Rating, newPlayer.RD, newPlayer.Volatility, playerID,
832 )
833 }
834 }
835
836 return nil
837}