websocket to polling

1 files changed, 328 insertions, 109 deletions

diff --git a/backend/db/query.sql.go b/backend/db/query.sql.go
index fdd40dd..980928a 100644
--- a/backend/db/query.sql.go
+++ b/backend/db/query.sql.go
@@ -1,6 +1,6 @@
 // Code generated by sqlc. DO NOT EDIT.
 // versions:
-//   sqlc v1.26.0
+//   sqlc v1.28.0
 // source: query.sql
 
 package db
@@ -34,8 +34,8 @@ func (q *Queries) AggregateTestcaseResults(ctx context.Context, submissionID int
 }
 
 const createSubmission = `-- name: CreateSubmission :one
-INSERT INTO submissions (game_id, user_id, code, code_size, code_hash)
-VALUES ($1, $2, $3, $4, $5)
+INSERT INTO submissions (game_id, user_id, code, code_size, status)
+VALUES ($1, $2, $3, $4, 'running')
 RETURNING submission_id
 `
 
@@ -44,7 +44,6 @@ type CreateSubmissionParams struct {
 	UserID   int32
 	Code     string
 	CodeSize int32
-	CodeHash string
 }
 
 func (q *Queries) CreateSubmission(ctx context.Context, arg CreateSubmissionParams) (int32, error) {
@@ -53,35 +52,12 @@ func (q *Queries) CreateSubmission(ctx context.Context, arg CreateSubmissionPara
 		arg.UserID,
 		arg.Code,
 		arg.CodeSize,
-		arg.CodeHash,
 	)
 	var submission_id int32
 	err := row.Scan(&submission_id)
 	return submission_id, err
 }
 
-const createSubmissionResult = `-- name: CreateSubmissionResult :exec
-INSERT INTO submission_results (submission_id, status, stdout, stderr)
-VALUES ($1, $2, $3, $4)
-`
-
-type CreateSubmissionResultParams struct {
-	SubmissionID int32
-	Status       string
-	Stdout       string
-	Stderr       string
-}
-
-func (q *Queries) CreateSubmissionResult(ctx context.Context, arg CreateSubmissionResultParams) error {
-	_, err := q.db.Exec(ctx, createSubmissionResult,
-		arg.SubmissionID,
-		arg.Status,
-		arg.Stdout,
-		arg.Stderr,
-	)
-	return err
-}
-
 const createTestcaseResult = `-- name: CreateTestcaseResult :exec
 INSERT INTO testcase_results (submission_id, testcase_id, status, stdout, stderr)
 VALUES ($1, $2, $3, $4, $5)
@@ -135,7 +111,7 @@ func (q *Queries) CreateUserAuth(ctx context.Context, arg CreateUserAuthParams)
 }
 
 const getGameByID = `-- name: GetGameByID :one
-SELECT game_id, game_type, state, display_name, duration_seconds, created_at, started_at, games.problem_id, problems.problem_id, title, description FROM games
+SELECT game_id, game_type, is_public, display_name, duration_seconds, created_at, started_at, games.problem_id, problems.problem_id, title, description, sample_code FROM games
 JOIN problems ON games.problem_id = problems.problem_id
 WHERE games.game_id = $1
 LIMIT 1
@@ -144,7 +120,7 @@ LIMIT 1
 type GetGameByIDRow struct {
 	GameID          int32
 	GameType        string
-	State           string
+	IsPublic        bool
 	DisplayName     string
 	DurationSeconds int32
 	CreatedAt       pgtype.Timestamp
@@ -153,6 +129,7 @@ type GetGameByIDRow struct {
 	ProblemID_2     int32
 	Title           string
 	Description     string
+	SampleCode      string
 }
 
 func (q *Queries) GetGameByID(ctx context.Context, gameID int32) (GetGameByIDRow, error) {
@@ -161,7 +138,7 @@ func (q *Queries) GetGameByID(ctx context.Context, gameID int32) (GetGameByIDRow
 	err := row.Scan(
 		&i.GameID,
 		&i.GameType,
-		&i.State,
+		&i.IsPublic,
 		&i.DisplayName,
 		&i.DurationSeconds,
 		&i.CreatedAt,
@@ -170,10 +147,185 @@ func (q *Queries) GetGameByID(ctx context.Context, gameID int32) (GetGameByIDRow
 		&i.ProblemID_2,
 		&i.Title,
 		&i.Description,
+		&i.SampleCode,
+	)
+	return i, err
+}
+
+const getLatestState = `-- name: GetLatestState :one
+SELECT game_states.game_id, game_states.user_id, game_states.code, game_states.status, best_score_submission_id, submission_id, submissions.game_id, submissions.user_id, submissions.code, code_size, submissions.status, created_at FROM game_states
+LEFT JOIN submissions ON game_states.best_score_submission_id = submissions.submission_id
+WHERE game_states.game_id = $1 AND game_states.user_id = $2
+LIMIT 1
+`
+
+type GetLatestStateParams struct {
+	GameID int32
+	UserID int32
+}
+
+type GetLatestStateRow struct {
+	GameID                int32
+	UserID                int32
+	Code                  string
+	Status                string
+	BestScoreSubmissionID *int32
+	SubmissionID          *int32
+	GameID_2              *int32
+	UserID_2              *int32
+	Code_2                *string
+	CodeSize              *int32
+	Status_2              *string
+	CreatedAt             pgtype.Timestamp
+}
+
+func (q *Queries) GetLatestState(ctx context.Context, arg GetLatestStateParams) (GetLatestStateRow, error) {
+	row := q.db.QueryRow(ctx, getLatestState, arg.GameID, arg.UserID)
+	var i GetLatestStateRow
+	err := row.Scan(
+		&i.GameID,
+		&i.UserID,
+		&i.Code,
+		&i.Status,
+		&i.BestScoreSubmissionID,
+		&i.SubmissionID,
+		&i.GameID_2,
+		&i.UserID_2,
+		&i.Code_2,
+		&i.CodeSize,
+		&i.Status_2,
+		&i.CreatedAt,
 	)
 	return i, err
 }
 
+const getLatestStatesOfMainPlayers = `-- name: GetLatestStatesOfMainPlayers :many
+SELECT game_main_players.game_id, game_main_players.user_id, game_states.game_id, game_states.user_id, game_states.code, game_states.status, best_score_submission_id, submission_id, submissions.game_id, submissions.user_id, submissions.code, code_size, submissions.status, created_at FROM game_main_players
+LEFT JOIN game_states ON game_main_players.game_id = game_states.game_id AND game_main_players.user_id = game_states.user_id
+LEFT JOIN submissions ON game_states.best_score_submission_id = submissions.submission_id
+WHERE game_main_players.game_id = $1
+`
+
+type GetLatestStatesOfMainPlayersRow struct {
+	GameID                int32
+	UserID                int32
+	GameID_2              *int32
+	UserID_2              *int32
+	Code                  *string
+	Status                *string
+	BestScoreSubmissionID *int32
+	SubmissionID          *int32
+	GameID_3              *int32
+	UserID_3              *int32
+	Code_2                *string
+	CodeSize              *int32
+	Status_2              *string
+	CreatedAt             pgtype.Timestamp
+}
+
+func (q *Queries) GetLatestStatesOfMainPlayers(ctx context.Context, gameID int32) ([]GetLatestStatesOfMainPlayersRow, error) {
+	rows, err := q.db.Query(ctx, getLatestStatesOfMainPlayers, gameID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []GetLatestStatesOfMainPlayersRow
+	for rows.Next() {
+		var i GetLatestStatesOfMainPlayersRow
+		if err := rows.Scan(
+			&i.GameID,
+			&i.UserID,
+			&i.GameID_2,
+			&i.UserID_2,
+			&i.Code,
+			&i.Status,
+			&i.BestScoreSubmissionID,
+			&i.SubmissionID,
+			&i.GameID_3,
+			&i.UserID_3,
+			&i.Code_2,
+			&i.CodeSize,
+			&i.Status_2,
+			&i.CreatedAt,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const getRanking = `-- name: GetRanking :many
+SELECT game_states.game_id, game_states.user_id, game_states.code, game_states.status, best_score_submission_id, users.user_id, username, display_name, icon_path, is_admin, users.created_at, submission_id, submissions.game_id, submissions.user_id, submissions.code, code_size, submissions.status, submissions.created_at FROM game_states
+JOIN users ON game_states.user_id = users.user_id
+JOIN submissions ON game_states.best_score_submission_id = submissions.submission_id
+WHERE game_states.game_id = $1
+ORDER BY submissions.code_size ASC, submissions.created_at ASC
+`
+
+type GetRankingRow struct {
+	GameID                int32
+	UserID                int32
+	Code                  string
+	Status                string
+	BestScoreSubmissionID *int32
+	UserID_2              int32
+	Username              string
+	DisplayName           string
+	IconPath              *string
+	IsAdmin               bool
+	CreatedAt             pgtype.Timestamp
+	SubmissionID          int32
+	GameID_2              int32
+	UserID_3              int32
+	Code_2                string
+	CodeSize              int32
+	Status_2              string
+	CreatedAt_2           pgtype.Timestamp
+}
+
+func (q *Queries) GetRanking(ctx context.Context, gameID int32) ([]GetRankingRow, error) {
+	rows, err := q.db.Query(ctx, getRanking, gameID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []GetRankingRow
+	for rows.Next() {
+		var i GetRankingRow
+		if err := rows.Scan(
+			&i.GameID,
+			&i.UserID,
+			&i.Code,
+			&i.Status,
+			&i.BestScoreSubmissionID,
+			&i.UserID_2,
+			&i.Username,
+			&i.DisplayName,
+			&i.IconPath,
+			&i.IsAdmin,
+			&i.CreatedAt,
+			&i.SubmissionID,
+			&i.GameID_2,
+			&i.UserID_3,
+			&i.Code_2,
+			&i.CodeSize,
+			&i.Status_2,
+			&i.CreatedAt_2,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
 const getSubmissionCodeSizeByID = `-- name: GetSubmissionCodeSizeByID :one
 SELECT code_size FROM submissions
 WHERE submission_id = $1
@@ -258,42 +410,29 @@ func (q *Queries) GetUserIDByUsername(ctx context.Context, username string) (int
 	return user_id, err
 }
 
-const listGamePlayers = `-- name: ListGamePlayers :many
-SELECT game_id, game_players.user_id, users.user_id, username, display_name, icon_path, is_admin, created_at FROM game_players
-JOIN users ON game_players.user_id = users.user_id
-WHERE game_players.game_id = $1
-ORDER BY game_players.user_id
+const listAllGames = `-- name: ListAllGames :many
+SELECT game_id, game_type, is_public, display_name, duration_seconds, created_at, started_at, problem_id FROM games
+ORDER BY games.game_id
 `
 
-type ListGamePlayersRow struct {
-	GameID      int32
-	UserID      int32
-	UserID_2    int32
-	Username    string
-	DisplayName string
-	IconPath    *string
-	IsAdmin     bool
-	CreatedAt   pgtype.Timestamp
-}
-
-func (q *Queries) ListGamePlayers(ctx context.Context, gameID int32) ([]ListGamePlayersRow, error) {
-	rows, err := q.db.Query(ctx, listGamePlayers, gameID)
+func (q *Queries) ListAllGames(ctx context.Context) ([]Game, error) {
+	rows, err := q.db.Query(ctx, listAllGames)
 	if err != nil {
 		return nil, err
 	}
 	defer rows.Close()
-	var items []ListGamePlayersRow
+	var items []Game
 	for rows.Next() {
-		var i ListGamePlayersRow
+		var i Game
 		if err := rows.Scan(
 			&i.GameID,
-			&i.UserID,
-			&i.UserID_2,
-			&i.Username,
+			&i.GameType,
+			&i.IsPublic,
 			&i.DisplayName,
-			&i.IconPath,
-			&i.IsAdmin,
+			&i.DurationSeconds,
 			&i.CreatedAt,
+			&i.StartedAt,
+			&i.ProblemID,
 		); err != nil {
 			return nil, err
 		}
@@ -305,47 +444,68 @@ func (q *Queries) ListGamePlayers(ctx context.Context, gameID int32) ([]ListGame
 	return items, nil
 }
 
-const listGames = `-- name: ListGames :many
-SELECT game_id, game_type, state, display_name, duration_seconds, created_at, started_at, games.problem_id, problems.problem_id, title, description FROM games
-JOIN problems ON games.problem_id = problems.problem_id
-ORDER BY games.game_id
+const listMainPlayerIDs = `-- name: ListMainPlayerIDs :many
+SELECT user_id FROM game_main_players
+WHERE game_id = $1
+ORDER BY user_id
 `
 
-type ListGamesRow struct {
-	GameID          int32
-	GameType        string
-	State           string
-	DisplayName     string
-	DurationSeconds int32
-	CreatedAt       pgtype.Timestamp
-	StartedAt       pgtype.Timestamp
-	ProblemID       int32
-	ProblemID_2     int32
-	Title           string
-	Description     string
+func (q *Queries) ListMainPlayerIDs(ctx context.Context, gameID int32) ([]int32, error) {
+	rows, err := q.db.Query(ctx, listMainPlayerIDs, gameID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []int32
+	for rows.Next() {
+		var user_id int32
+		if err := rows.Scan(&user_id); err != nil {
+			return nil, err
+		}
+		items = append(items, user_id)
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const listMainPlayers = `-- name: ListMainPlayers :many
+SELECT game_id, game_main_players.user_id, users.user_id, username, display_name, icon_path, is_admin, created_at FROM game_main_players
+JOIN users ON game_main_players.user_id = users.user_id
+WHERE game_main_players.game_id = ANY($1::INT[])
+ORDER BY game_main_players.user_id
+`
+
+type ListMainPlayersRow struct {
+	GameID      int32
+	UserID      int32
+	UserID_2    int32
+	Username    string
+	DisplayName string
+	IconPath    *string
+	IsAdmin     bool
+	CreatedAt   pgtype.Timestamp
 }
 
-func (q *Queries) ListGames(ctx context.Context) ([]ListGamesRow, error) {
-	rows, err := q.db.Query(ctx, listGames)
+func (q *Queries) ListMainPlayers(ctx context.Context, dollar_1 []int32) ([]ListMainPlayersRow, error) {
+	rows, err := q.db.Query(ctx, listMainPlayers, dollar_1)
 	if err != nil {
 		return nil, err
 	}
 	defer rows.Close()
-	var items []ListGamesRow
+	var items []ListMainPlayersRow
 	for rows.Next() {
-		var i ListGamesRow
+		var i ListMainPlayersRow
 		if err := rows.Scan(
 			&i.GameID,
-			&i.GameType,
-			&i.State,
+			&i.UserID,
+			&i.UserID_2,
+			&i.Username,
 			&i.DisplayName,
-			&i.DurationSeconds,
+			&i.IconPath,
+			&i.IsAdmin,
 			&i.CreatedAt,
-			&i.StartedAt,
-			&i.ProblemID,
-			&i.ProblemID_2,
-			&i.Title,
-			&i.Description,
 		); err != nil {
 			return nil, err
 		}
@@ -357,18 +517,17 @@ func (q *Queries) ListGames(ctx context.Context) ([]ListGamesRow, error) {
 	return items, nil
 }
 
-const listGamesForPlayer = `-- name: ListGamesForPlayer :many
-SELECT games.game_id, game_type, state, display_name, duration_seconds, created_at, started_at, games.problem_id, problems.problem_id, title, description, game_players.game_id, user_id FROM games
+const listPublicGames = `-- name: ListPublicGames :many
+SELECT game_id, game_type, is_public, display_name, duration_seconds, created_at, started_at, games.problem_id, problems.problem_id, title, description, sample_code FROM games
 JOIN problems ON games.problem_id = problems.problem_id
-JOIN game_players ON games.game_id = game_players.game_id
-WHERE game_players.user_id = $1
+WHERE is_public = true
 ORDER BY games.game_id
 `
 
-type ListGamesForPlayerRow struct {
+type ListPublicGamesRow struct {
 	GameID          int32
 	GameType        string
-	State           string
+	IsPublic        bool
 	DisplayName     string
 	DurationSeconds int32
 	CreatedAt       pgtype.Timestamp
@@ -377,23 +536,22 @@ type ListGamesForPlayerRow struct {
 	ProblemID_2     int32
 	Title           string
 	Description     string
-	GameID_2        int32
-	UserID          int32
+	SampleCode      string
 }
 
-func (q *Queries) ListGamesForPlayer(ctx context.Context, userID int32) ([]ListGamesForPlayerRow, error) {
-	rows, err := q.db.Query(ctx, listGamesForPlayer, userID)
+func (q *Queries) ListPublicGames(ctx context.Context) ([]ListPublicGamesRow, error) {
+	rows, err := q.db.Query(ctx, listPublicGames)
 	if err != nil {
 		return nil, err
 	}
 	defer rows.Close()
-	var items []ListGamesForPlayerRow
+	var items []ListPublicGamesRow
 	for rows.Next() {
-		var i ListGamesForPlayerRow
+		var i ListPublicGamesRow
 		if err := rows.Scan(
 			&i.GameID,
 			&i.GameType,
-			&i.State,
+			&i.IsPublic,
 			&i.DisplayName,
 			&i.DurationSeconds,
 			&i.CreatedAt,
@@ -402,8 +560,7 @@ func (q *Queries) ListGamesForPlayer(ctx context.Context, userID int32) ([]ListG
 			&i.ProblemID_2,
 			&i.Title,
 			&i.Description,
-			&i.GameID_2,
-			&i.UserID,
+			&i.SampleCode,
 		); err != nil {
 			return nil, err
 		}
@@ -504,11 +661,56 @@ func (q *Queries) ListUsers(ctx context.Context) ([]User, error) {
 	return items, nil
 }
 
+const syncGameStateBestScoreSubmission = `-- name: SyncGameStateBestScoreSubmission :exec
+UPDATE game_states
+SET best_score_submission_id = (
+    SELECT submission_id FROM submissions AS s
+    WHERE s.game_id = $1 AND s.user_id = $2 AND s.status = 'success'
+    ORDER BY s.code_size ASC, s.created_at ASC
+    LIMIT 1
+)
+WHERE game_id = $1 AND user_id = $2
+`
+
+type SyncGameStateBestScoreSubmissionParams struct {
+	GameID int32
+	UserID int32
+}
+
+func (q *Queries) SyncGameStateBestScoreSubmission(ctx context.Context, arg SyncGameStateBestScoreSubmissionParams) error {
+	_, err := q.db.Exec(ctx, syncGameStateBestScoreSubmission, arg.GameID, arg.UserID)
+	return err
+}
+
+const updateCode = `-- name: UpdateCode :exec
+INSERT INTO game_states (game_id, user_id, code, status)
+VALUES ($1, $2, $3, $4)
+ON CONFLICT (game_id, user_id)
+DO UPDATE SET code = EXCLUDED.code
+`
+
+type UpdateCodeParams struct {
+	GameID int32
+	UserID int32
+	Code   string
+	Status string
+}
+
+func (q *Queries) UpdateCode(ctx context.Context, arg UpdateCodeParams) error {
+	_, err := q.db.Exec(ctx, updateCode,
+		arg.GameID,
+		arg.UserID,
+		arg.Code,
+		arg.Status,
+	)
+	return err
+}
+
 const updateGame = `-- name: UpdateGame :exec
 UPDATE games
 SET
     game_type = $2,
-    state = $3,
+    is_public = $3,
     display_name = $4,
     duration_seconds = $5,
     started_at = $6,
@@ -519,7 +721,7 @@ WHERE game_id = $1
 type UpdateGameParams struct {
 	GameID          int32
 	GameType        string
-	State           string
+	IsPublic        bool
 	DisplayName     string
 	DurationSeconds int32
 	StartedAt       pgtype.Timestamp
@@ -530,7 +732,7 @@ func (q *Queries) UpdateGame(ctx context.Context, arg UpdateGameParams) error {
 	_, err := q.db.Exec(ctx, updateGame,
 		arg.GameID,
 		arg.GameType,
-		arg.State,
+		arg.IsPublic,
 		arg.DisplayName,
 		arg.DurationSeconds,
 		arg.StartedAt,
@@ -555,19 +757,36 @@ func (q *Queries) UpdateGameStartedAt(ctx context.Context, arg UpdateGameStarted
 	return err
 }
 
-const updateGameState = `-- name: UpdateGameState :exec
-UPDATE games
-SET state = $2
-WHERE game_id = $1
+const updateGameStateStatus = `-- name: UpdateGameStateStatus :exec
+UPDATE game_states
+SET status = $3
+WHERE game_id = $1 AND user_id = $2
 `
 
-type UpdateGameStateParams struct {
+type UpdateGameStateStatusParams struct {
 	GameID int32
-	State  string
+	UserID int32
+	Status string
+}
+
+func (q *Queries) UpdateGameStateStatus(ctx context.Context, arg UpdateGameStateStatusParams) error {
+	_, err := q.db.Exec(ctx, updateGameStateStatus, arg.GameID, arg.UserID, arg.Status)
+	return err
+}
+
+const updateSubmissionStatus = `-- name: UpdateSubmissionStatus :exec
+UPDATE submissions
+SET status = $2
+WHERE submission_id = $1
+`
+
+type UpdateSubmissionStatusParams struct {
+	SubmissionID int32
+	Status       string
 }
 
-func (q *Queries) UpdateGameState(ctx context.Context, arg UpdateGameStateParams) error {
-	_, err := q.db.Exec(ctx, updateGameState, arg.GameID, arg.State)
+func (q *Queries) UpdateSubmissionStatus(ctx context.Context, arg UpdateSubmissionStatusParams) error {
+	_, err := q.db.Exec(ctx, updateSubmissionStatus, arg.SubmissionID, arg.Status)
 	return err
 }