a geicko-2 based round robin ranking system designed to test c++ battleship submissions
battleship.dunkirk.sh
dunkirk.sh
1package main
2
3import (
4 "database/sql"
5 "time"
6
7 _ "github.com/mattn/go-sqlite3"
8)
9
10var globalDB *sql.DB
11
12type LeaderboardEntry struct {
13 Username string
14 Wins int
15 Losses int
16 AvgMoves float64
17 Stage string
18 LastPlayed time.Time
19}
20
21type Submission struct {
22 ID int
23 Username string
24 Filename string
25 UploadTime time.Time
26 Status string // pending, testing, completed, failed
27}
28
29type Tournament struct {
30 ID int
31 CreatedAt time.Time
32 Status string // active, completed
33 CurrentRound int
34 WinnerID int // ID of winning submission
35}
36
37type BracketMatch struct {
38 ID int
39 TournamentID int
40 Round int
41 Position int
42 Player1ID int
43 Player2ID int
44 WinnerID int
45 Player1Wins int
46 Player2Wins int
47 Player1Moves int
48 Player2Moves int
49 Status string // pending, in_progress, completed
50 Player1Name string // For display
51 Player2Name string
52}
53
54func initDB(path string) (*sql.DB, error) {
55 db, err := sql.Open("sqlite3", path+"?parseTime=true")
56 if err != nil {
57 return nil, err
58 }
59
60 schema := `
61 CREATE TABLE IF NOT EXISTS submissions (
62 id INTEGER PRIMARY KEY AUTOINCREMENT,
63 username TEXT NOT NULL,
64 filename TEXT NOT NULL,
65 upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
66 status TEXT DEFAULT 'pending',
67 is_active BOOLEAN DEFAULT 1
68 );
69
70 CREATE TABLE IF NOT EXISTS tournaments (
71 id INTEGER PRIMARY KEY AUTOINCREMENT,
72 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
73 status TEXT DEFAULT 'active',
74 current_round INTEGER DEFAULT 1,
75 winner_id INTEGER,
76 FOREIGN KEY (winner_id) REFERENCES submissions(id)
77 );
78
79 CREATE TABLE IF NOT EXISTS bracket_matches (
80 id INTEGER PRIMARY KEY AUTOINCREMENT,
81 tournament_id INTEGER,
82 round INTEGER,
83 position INTEGER,
84 player1_id INTEGER,
85 player2_id INTEGER,
86 winner_id INTEGER,
87 player1_wins INTEGER DEFAULT 0,
88 player2_wins INTEGER DEFAULT 0,
89 player1_moves INTEGER,
90 player2_moves INTEGER,
91 status TEXT DEFAULT 'pending',
92 timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
93 FOREIGN KEY (tournament_id) REFERENCES tournaments(id),
94 FOREIGN KEY (player1_id) REFERENCES submissions(id),
95 FOREIGN KEY (player2_id) REFERENCES submissions(id),
96 FOREIGN KEY (winner_id) REFERENCES submissions(id)
97 );
98
99 CREATE TABLE IF NOT EXISTS matches (
100 id INTEGER PRIMARY KEY AUTOINCREMENT,
101 player1_id INTEGER,
102 player2_id INTEGER,
103 winner_id INTEGER,
104 player1_wins INTEGER DEFAULT 0,
105 player2_wins INTEGER DEFAULT 0,
106 player1_moves INTEGER,
107 player2_moves INTEGER,
108 timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
109 FOREIGN KEY (player1_id) REFERENCES submissions(id),
110 FOREIGN KEY (player2_id) REFERENCES submissions(id),
111 FOREIGN KEY (winner_id) REFERENCES submissions(id)
112 );
113
114 CREATE INDEX IF NOT EXISTS idx_bracket_matches_tournament ON bracket_matches(tournament_id);
115 CREATE INDEX IF NOT EXISTS idx_bracket_matches_status ON bracket_matches(status);
116 CREATE INDEX IF NOT EXISTS idx_tournaments_status ON tournaments(status);
117 CREATE INDEX IF NOT EXISTS idx_matches_player1 ON matches(player1_id);
118 CREATE INDEX IF NOT EXISTS idx_matches_player2 ON matches(player2_id);
119 CREATE INDEX IF NOT EXISTS idx_submissions_username ON submissions(username);
120 CREATE INDEX IF NOT EXISTS idx_submissions_status ON submissions(status);
121 CREATE INDEX IF NOT EXISTS idx_submissions_active ON submissions(is_active);
122 `
123
124 _, err = db.Exec(schema)
125 return db, err
126}
127
128func getLeaderboard(limit int) ([]LeaderboardEntry, error) {
129 query := `
130 SELECT
131 s.username,
132 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,
133 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,
134 AVG(CASE WHEN m.player1_id = s.id THEN m.player1_moves ELSE m.player2_moves END) as avg_moves,
135 MAX(m.timestamp) as last_played
136 FROM submissions s
137 LEFT JOIN matches m ON (m.player1_id = s.id OR m.player2_id = s.id)
138 WHERE s.is_active = 1
139 GROUP BY s.username
140 HAVING COUNT(m.id) > 0
141 ORDER BY total_wins DESC, total_losses ASC, avg_moves ASC
142 LIMIT ?
143 `
144
145 rows, err := globalDB.Query(query, limit)
146 if err != nil {
147 return nil, err
148 }
149 defer rows.Close()
150
151 var entries []LeaderboardEntry
152 for rows.Next() {
153 var e LeaderboardEntry
154 var lastPlayed string
155 err := rows.Scan(&e.Username, &e.Wins, &e.Losses, &e.AvgMoves, &lastPlayed)
156 if err != nil {
157 return nil, err
158 }
159
160 // Parse the timestamp string
161 e.LastPlayed, _ = time.Parse("2006-01-02 15:04:05", lastPlayed)
162
163 entries = append(entries, e)
164 }
165
166 return entries, rows.Err()
167}
168
169func addSubmission(username, filename string) (int64, error) {
170 // Mark old submission as inactive
171 _, err := globalDB.Exec(
172 "UPDATE submissions SET is_active = 0 WHERE username = ?",
173 username,
174 )
175 if err != nil {
176 return 0, err
177 }
178
179 // Insert new submission
180 result, err := globalDB.Exec(
181 "INSERT INTO submissions (username, filename, is_active) VALUES (?, ?, 1)",
182 username, filename,
183 )
184 if err != nil {
185 return 0, err
186 }
187 return result.LastInsertId()
188}
189
190func addMatch(player1ID, player2ID, winnerID, player1Wins, player2Wins, player1Moves, player2Moves int) error {
191 _, err := globalDB.Exec(
192 "INSERT INTO matches (player1_id, player2_id, winner_id, player1_wins, player2_wins, player1_moves, player2_moves) VALUES (?, ?, ?, ?, ?, ?, ?)",
193 player1ID, player2ID, winnerID, player1Wins, player2Wins, player1Moves, player2Moves,
194 )
195 return err
196}
197
198func updateSubmissionStatus(id int, status string) error {
199 _, err := globalDB.Exec("UPDATE submissions SET status = ? WHERE id = ?", status, id)
200 return err
201}
202
203func getPendingSubmissions() ([]Submission, error) {
204 rows, err := globalDB.Query(
205 "SELECT id, username, filename, upload_time, status FROM submissions WHERE status = 'pending' AND is_active = 1 ORDER BY upload_time",
206 )
207 if err != nil {
208 return nil, err
209 }
210 defer rows.Close()
211
212 var submissions []Submission
213 for rows.Next() {
214 var s Submission
215 err := rows.Scan(&s.ID, &s.Username, &s.Filename, &s.UploadTime, &s.Status)
216 if err != nil {
217 return nil, err
218 }
219 submissions = append(submissions, s)
220 }
221
222 return submissions, rows.Err()
223}
224
225func getActiveSubmissions() ([]Submission, error) {
226 rows, err := globalDB.Query(
227 "SELECT id, username, filename, upload_time, status FROM submissions WHERE is_active = 1 AND status = 'completed' ORDER BY username",
228 )
229 if err != nil {
230 return nil, err
231 }
232 defer rows.Close()
233
234 var submissions []Submission
235 for rows.Next() {
236 var s Submission
237 err := rows.Scan(&s.ID, &s.Username, &s.Filename, &s.UploadTime, &s.Status)
238 if err != nil {
239 return nil, err
240 }
241 submissions = append(submissions, s)
242 }
243
244 return submissions, rows.Err()
245}
246
247func getUserSubmissions(username string) ([]Submission, error) {
248 rows, err := globalDB.Query(
249 "SELECT id, username, filename, upload_time, status FROM submissions WHERE username = ? ORDER BY upload_time DESC LIMIT 10",
250 username,
251 )
252 if err != nil {
253 return nil, err
254 }
255 defer rows.Close()
256
257 var submissions []Submission
258 for rows.Next() {
259 var s Submission
260 err := rows.Scan(&s.ID, &s.Username, &s.Filename, &s.UploadTime, &s.Status)
261 if err != nil {
262 return nil, err
263 }
264 submissions = append(submissions, s)
265 }
266
267 return submissions, rows.Err()
268}
269
270
271type MatchResult struct {
272 Player1Username string
273 Player2Username string
274 WinnerUsername string
275 AvgMoves int
276}
277
278func getAllMatches() ([]MatchResult, error) {
279 query := `
280 SELECT
281 s1.username as player1,
282 s2.username as player2,
283 sw.username as winner,
284 m.player1_moves as avg_moves
285 FROM matches m
286 JOIN submissions s1 ON m.player1_id = s1.id
287 JOIN submissions s2 ON m.player2_id = s2.id
288 JOIN submissions sw ON m.winner_id = sw.id
289 WHERE s1.is_active = 1 AND s2.is_active = 1
290 ORDER BY m.timestamp DESC
291 `
292
293 rows, err := globalDB.Query(query)
294 if err != nil {
295 return nil, err
296 }
297 defer rows.Close()
298
299 var matches []MatchResult
300 for rows.Next() {
301 var m MatchResult
302 err := rows.Scan(&m.Player1Username, &m.Player2Username, &m.WinnerUsername, &m.AvgMoves)
303 if err != nil {
304 return nil, err
305 }
306 matches = append(matches, m)
307 }
308
309 return matches, rows.Err()
310}
311
312// Tournament functions
313
314func getActiveTournament() (*Tournament, error) {
315 var t Tournament
316 var winnerID sql.NullInt64
317 err := globalDB.QueryRow(
318 "SELECT id, created_at, status, current_round, winner_id FROM tournaments WHERE status = 'active' ORDER BY id DESC LIMIT 1",
319 ).Scan(&t.ID, &t.CreatedAt, &t.Status, &t.CurrentRound, &winnerID)
320
321 if err == sql.ErrNoRows {
322 return nil, nil
323 }
324 if winnerID.Valid {
325 t.WinnerID = int(winnerID.Int64)
326 }
327 return &t, err
328}
329
330func getLatestTournament() (*Tournament, error) {
331 var t Tournament
332 var winnerID sql.NullInt64
333 err := globalDB.QueryRow(
334 "SELECT id, created_at, status, current_round, winner_id FROM tournaments ORDER BY id DESC LIMIT 1",
335 ).Scan(&t.ID, &t.CreatedAt, &t.Status, &t.CurrentRound, &winnerID)
336
337 if err == sql.ErrNoRows {
338 return nil, nil
339 }
340 if winnerID.Valid {
341 t.WinnerID = int(winnerID.Int64)
342 }
343 return &t, err
344}
345
346func createTournament() (*Tournament, error) {
347 result, err := globalDB.Exec("INSERT INTO tournaments (status, current_round) VALUES ('active', 1)")
348 if err != nil {
349 return nil, err
350 }
351
352 id, _ := result.LastInsertId()
353 return &Tournament{
354 ID: int(id),
355 Status: "active",
356 CurrentRound: 1,
357 }, nil
358}
359
360func updateTournamentRound(tournamentID, round int) error {
361 _, err := globalDB.Exec("UPDATE tournaments SET current_round = ? WHERE id = ?", round, tournamentID)
362 return err
363}
364
365func completeTournament(tournamentID, winnerID int) error {
366 _, err := globalDB.Exec("UPDATE tournaments SET status = 'completed', winner_id = ? WHERE id = ?", winnerID, tournamentID)
367 return err
368}
369
370func addBracketMatch(tournamentID, round, position, player1ID, player2ID int) error {
371 _, err := globalDB.Exec(
372 "INSERT INTO bracket_matches (tournament_id, round, position, player1_id, player2_id, status) VALUES (?, ?, ?, ?, ?, 'pending')",
373 tournamentID, round, position, player1ID, player2ID,
374 )
375 return err
376}
377
378func getPendingBracketMatches(tournamentID int) ([]BracketMatch, error) {
379 query := `
380 SELECT
381 bm.id, bm.tournament_id, bm.round, bm.position,
382 bm.player1_id, bm.player2_id, bm.winner_id,
383 bm.player1_wins, bm.player2_wins,
384 bm.player1_moves, bm.player2_moves, bm.status,
385 s1.username as player1_name, s2.username as player2_name
386 FROM bracket_matches bm
387 JOIN submissions s1 ON bm.player1_id = s1.id
388 JOIN submissions s2 ON bm.player2_id = s2.id
389 WHERE bm.tournament_id = ? AND bm.status = 'pending'
390 ORDER BY bm.round, bm.position
391 `
392
393 rows, err := globalDB.Query(query, tournamentID)
394 if err != nil {
395 return nil, err
396 }
397 defer rows.Close()
398
399 var matches []BracketMatch
400 for rows.Next() {
401 var m BracketMatch
402 var winnerID sql.NullInt64
403 var player1Moves, player2Moves sql.NullInt64
404 err := rows.Scan(
405 &m.ID, &m.TournamentID, &m.Round, &m.Position,
406 &m.Player1ID, &m.Player2ID, &winnerID,
407 &m.Player1Wins, &m.Player2Wins,
408 &player1Moves, &player2Moves, &m.Status,
409 &m.Player1Name, &m.Player2Name,
410 )
411 if err != nil {
412 return nil, err
413 }
414 if winnerID.Valid {
415 m.WinnerID = int(winnerID.Int64)
416 }
417 if player1Moves.Valid {
418 m.Player1Moves = int(player1Moves.Int64)
419 }
420 if player2Moves.Valid {
421 m.Player2Moves = int(player2Moves.Int64)
422 }
423 matches = append(matches, m)
424 }
425
426 return matches, rows.Err()
427}
428
429func getAllBracketMatches(tournamentID int) ([]BracketMatch, error) {
430 query := `
431 SELECT
432 bm.id, bm.tournament_id, bm.round, bm.position,
433 bm.player1_id, bm.player2_id, bm.winner_id,
434 bm.player1_wins, bm.player2_wins,
435 bm.player1_moves, bm.player2_moves, bm.status,
436 s1.username as player1_name, s2.username as player2_name
437 FROM bracket_matches bm
438 LEFT JOIN submissions s1 ON bm.player1_id = s1.id
439 LEFT JOIN submissions s2 ON bm.player2_id = s2.id
440 WHERE bm.tournament_id = ?
441 ORDER BY bm.round, bm.position
442 `
443
444 rows, err := globalDB.Query(query, tournamentID)
445 if err != nil {
446 return nil, err
447 }
448 defer rows.Close()
449
450 var matches []BracketMatch
451 for rows.Next() {
452 var m BracketMatch
453 var player1Name, player2Name sql.NullString
454 var winnerID, player1Moves, player2Moves sql.NullInt64
455 err := rows.Scan(
456 &m.ID, &m.TournamentID, &m.Round, &m.Position,
457 &m.Player1ID, &m.Player2ID, &winnerID,
458 &m.Player1Wins, &m.Player2Wins,
459 &player1Moves, &player2Moves, &m.Status,
460 &player1Name, &player2Name,
461 )
462 if err != nil {
463 return nil, err
464 }
465 if winnerID.Valid {
466 m.WinnerID = int(winnerID.Int64)
467 }
468 if player1Moves.Valid {
469 m.Player1Moves = int(player1Moves.Int64)
470 }
471 if player2Moves.Valid {
472 m.Player2Moves = int(player2Moves.Int64)
473 }
474 if player1Name.Valid {
475 m.Player1Name = player1Name.String
476 }
477 if player2Name.Valid {
478 m.Player2Name = player2Name.String
479 }
480 matches = append(matches, m)
481 }
482
483 return matches, rows.Err()
484}
485
486func updateBracketMatchResult(matchID, winnerID, player1Wins, player2Wins, player1Moves, player2Moves int) error {
487 _, err := globalDB.Exec(
488 `UPDATE bracket_matches
489 SET winner_id = ?, player1_wins = ?, player2_wins = ?,
490 player1_moves = ?, player2_moves = ?, status = 'completed'
491 WHERE id = ?`,
492 winnerID, player1Wins, player2Wins, player1Moves, player2Moves, matchID,
493 )
494 return err
495}
496
497func isRoundComplete(tournamentID, round int) (bool, error) {
498 var pendingCount int
499 err := globalDB.QueryRow(
500 "SELECT COUNT(*) FROM bracket_matches WHERE tournament_id = ? AND round = ? AND status != 'completed'",
501 tournamentID, round,
502 ).Scan(&pendingCount)
503
504 return pendingCount == 0, err
505}
506