feat: implement game entry

6 files changed, 360 insertions, 13 deletions

diff --git a/backend/api/generated.go b/backend/api/generated.go
index 4455723..921f4b3 100644
--- a/backend/api/generated.go
+++ b/backend/api/generated.go
@@ -17,9 +17,34 @@ import (
 
 	"github.com/getkin/kin-openapi/openapi3"
 	"github.com/labstack/echo/v4"
+	"github.com/oapi-codegen/runtime"
 	strictecho "github.com/oapi-codegen/runtime/strictmiddleware/echo"
 )
 
+// Defines values for GameState.
+const (
+	Closed         GameState = "closed"
+	Finished       GameState = "finished"
+	Gaming         GameState = "gaming"
+	Prepare        GameState = "prepare"
+	Starting       GameState = "starting"
+	WaitingEntries GameState = "waiting_entries"
+	WaitingStart   GameState = "waiting_start"
+)
+
+// Game defines model for Game.
+type Game struct {
+	DisplayName     string    `json:"display_name"`
+	DurationSeconds int       `json:"duration_seconds"`
+	GameId          int       `json:"game_id"`
+	Problem         *Problem  `json:"problem,omitempty"`
+	StartedAt       *int      `json:"started_at,omitempty"`
+	State           GameState `json:"state"`
+}
+
+// GameState defines model for Game.State.
+type GameState string
+
 // JwtPayload defines model for JwtPayload.
 type JwtPayload struct {
 	DisplayName string  `json:"display_name"`
@@ -29,6 +54,19 @@ type JwtPayload struct {
 	Username    string  `json:"username"`
 }
 
+// Problem defines model for Problem.
+type Problem struct {
+	Description string `json:"description"`
+	ProblemId   int    `json:"problem_id"`
+	Title       string `json:"title"`
+}
+
+// GetGamesParams defines parameters for GetGames.
+type GetGamesParams struct {
+	PlayerId      *int   `form:"player_id,omitempty" json:"player_id,omitempty"`
+	Authorization string `json:"Authorization"`
+}
+
 // PostLoginJSONBody defines parameters for PostLogin.
 type PostLoginJSONBody struct {
 	Password string `json:"password"`
@@ -40,6 +78,9 @@ type PostLoginJSONRequestBody PostLoginJSONBody
 
 // ServerInterface represents all server handlers.
 type ServerInterface interface {
+	// List games
+	// (GET /games)
+	GetGames(ctx echo.Context, params GetGamesParams) error
 	// User login
 	// (POST /login)
 	PostLogin(ctx echo.Context) error
@@ -50,6 +91,43 @@ type ServerInterfaceWrapper struct {
 	Handler ServerInterface
 }
 
+// GetGames converts echo context to params.
+func (w *ServerInterfaceWrapper) GetGames(ctx echo.Context) error {
+	var err error
+
+	// Parameter object where we will unmarshal all parameters from the context
+	var params GetGamesParams
+	// ------------- Optional query parameter "player_id" -------------
+
+	err = runtime.BindQueryParameter("form", true, false, "player_id", ctx.QueryParams(), &params.PlayerId)
+	if err != nil {
+		return echo.NewHTTPError(http.StatusBadRequest, fmt.Sprintf("Invalid format for parameter player_id: %s", err))
+	}
+
+	headers := ctx.Request().Header
+	// ------------- Required header parameter "Authorization" -------------
+	if valueList, found := headers[http.CanonicalHeaderKey("Authorization")]; found {
+		var Authorization string
+		n := len(valueList)
+		if n != 1 {
+			return echo.NewHTTPError(http.StatusBadRequest, fmt.Sprintf("Expected one value for Authorization, got %d", n))
+		}
+
+		err = runtime.BindStyledParameterWithOptions("simple", "Authorization", valueList[0], &Authorization, runtime.BindStyledParameterOptions{ParamLocation: runtime.ParamLocationHeader, Explode: false, Required: true})
+		if err != nil {
+			return echo.NewHTTPError(http.StatusBadRequest, fmt.Sprintf("Invalid format for parameter Authorization: %s", err))
+		}
+
+		params.Authorization = Authorization
+	} else {
+		return echo.NewHTTPError(http.StatusBadRequest, fmt.Sprintf("Header parameter Authorization is required, but not found"))
+	}
+
+	// Invoke the callback with all the unmarshaled arguments
+	err = w.Handler.GetGames(ctx, params)
+	return err
+}
+
 // PostLogin converts echo context to params.
 func (w *ServerInterfaceWrapper) PostLogin(ctx echo.Context) error {
 	var err error
@@ -87,10 +165,41 @@ func RegisterHandlersWithBaseURL(router EchoRouter, si ServerInterface, baseURL
 		Handler: si,
 	}
 
+	router.GET(baseURL+"/games", wrapper.GetGames)
 	router.POST(baseURL+"/login", wrapper.PostLogin)
 
 }
 
+type GetGamesRequestObject struct {
+	Params GetGamesParams
+}
+
+type GetGamesResponseObject interface {
+	VisitGetGamesResponse(w http.ResponseWriter) error
+}
+
+type GetGames200JSONResponse struct {
+	Games []Game `json:"games"`
+}
+
+func (response GetGames200JSONResponse) VisitGetGamesResponse(w http.ResponseWriter) error {
+	w.Header().Set("Content-Type", "application/json")
+	w.WriteHeader(200)
+
+	return json.NewEncoder(w).Encode(response)
+}
+
+type GetGames403JSONResponse struct {
+	Message string `json:"message"`
+}
+
+func (response GetGames403JSONResponse) VisitGetGamesResponse(w http.ResponseWriter) error {
+	w.Header().Set("Content-Type", "application/json")
+	w.WriteHeader(403)
+
+	return json.NewEncoder(w).Encode(response)
+}
+
 type PostLoginRequestObject struct {
 	Body *PostLoginJSONRequestBody
 }
@@ -123,6 +232,9 @@ func (response PostLogin401JSONResponse) VisitPostLoginResponse(w http.ResponseW
 
 // StrictServerInterface represents all server handlers.
 type StrictServerInterface interface {
+	// List games
+	// (GET /games)
+	GetGames(ctx context.Context, request GetGamesRequestObject) (GetGamesResponseObject, error)
 	// User login
 	// (POST /login)
 	PostLogin(ctx context.Context, request PostLoginRequestObject) (PostLoginResponseObject, error)
@@ -140,6 +252,31 @@ type strictHandler struct {
 	middlewares []StrictMiddlewareFunc
 }
 
+// GetGames operation middleware
+func (sh *strictHandler) GetGames(ctx echo.Context, params GetGamesParams) error {
+	var request GetGamesRequestObject
+
+	request.Params = params
+
+	handler := func(ctx echo.Context, request interface{}) (interface{}, error) {
+		return sh.ssi.GetGames(ctx.Request().Context(), request.(GetGamesRequestObject))
+	}
+	for _, middleware := range sh.middlewares {
+		handler = middleware(handler, "GetGames")
+	}
+
+	response, err := handler(ctx, request)
+
+	if err != nil {
+		return err
+	} else if validResponse, ok := response.(GetGamesResponseObject); ok {
+		return validResponse.VisitGetGamesResponse(ctx.Response())
+	} else if response != nil {
+		return fmt.Errorf("unexpected response type: %T", response)
+	}
+	return nil
+}
+
 // PostLogin operation middleware
 func (sh *strictHandler) PostLogin(ctx echo.Context) error {
 	var request PostLoginRequestObject
@@ -172,14 +309,20 @@ func (sh *strictHandler) PostLogin(ctx echo.Context) error {
 // Base64 encoded, gzipped, json marshaled Swagger object
 var swaggerSpec = []string{
 
-	"H4sIAAAAAAAC/6RSzY7aQAx+lcjnKAToKbetemHVA1LVU1Uhk5gwdDKejp1lo1XevZqkZElB2kpwIJnJ",
-	"Z/v78RuU3Hh25FSgeAMpj9Tg8Pp81i12lrGKJx/YU1BDw7fKiLfY7Rw2FM/0io23BAU889ElX5ggBe18",
-	"vBENxtXQp2BKdjuPepyXLEyDNcnixEeXnXx9t1R2WDXGzSoPaIUm8J7ZErqIboXCzlQz8HK1nqDGKdUU",
-	"LtBbFZHKLY0+hUC/WxOoguLHNOWqSTp35or3z6kb709UKvSxnXEHjpPV6DD3ye5RA4skkWJwaJMz7ZOn",
-	"7QZSeKEghh0UkGfLLI/s2ZNDb6CAdZZnOaQQ7R0iWliuR788i8ZnDBDVsNtUUMCWRb8OkFEViX7mqovA",
-	"kp2SG2rQe2vKoWpxEnbvO3K7FB5Fzhzmtk+3y9X6XrAP+v/X5mn0fZvfqzS0NFyIZycj71WeP6Ba+RfN",
-	"1xJe4y+7+v9QytjkPvmKpAzG6xj9t7YsSeTQWtsl2OqRnEaqVEU3P+XLB6Q0JIL1P1Fs3AtaUyVloCrO",
-	"Qisfyrk0+h9Bl/6XNBMOyRRnhEvbNBg6KOC7UEjGte77vv8TAAD//y1cIMC8BAAA",
+	"H4sIAAAAAAAC/6xUTW/jNhD9K8K0R8FW4iDY+pai6CKLPRhoe1oExlgcS0wlkssZJesG+u8FqQ9btoqk",
+	"SHKIZGo+3nvzOC+Q29pZQ0YY1i/AeUk1xtfPWFN4Om8dedEUT5VmV+Fha/qv9ANrVxGsY3xyBSnIwYXf",
+	"LF6bAtoUVONRtDVbptwaxZO81W02pmgjVJAPOQXWtNVqEno1F+i83VVUh8CfPe1hDT8tj5yWPaHlpg9r",
+	"U2BBL6S2KJPqv9zc3n66+ZTNwmFB6fiapob1N8gry6QghWfUok2xJSM+aHQ8iX0gICSHnqDvHESJ/LqX",
+	"vTaaS1LwkJ6IibnoJ7oUs03B0/dGe1IBxaDSADCdzmdG+oexpN09Ui6B3Jdn2eChsqj+z7y/2NIkv1ma",
+	"m7jOrdk6lHKastQ1FsTLR1uaxaMrZlN5i6rWZpK5x4ppDN5ZWxGaEN0w+QubXK/mRhhCL1kEKK/KPHQ5",
+	"KXKh9Ih7TuHN0aRn8hLnXrswoimuP0vNieYEk8HgM1r1n950T0RLdca9RzV3ac8EOGk0VEon2C9JhxLa",
+	"7G1o2feGu2qH4i1zEoB5g1XyTLvkbnMPKTyR5ygDZIurRRYwW0cGnYY1rBbZIgt3CaWMwi2D9eNbQfEe",
+	"B1Wj1e9VWEYkn2NASPFYk5BnWH97geAs+N6QP0AKnR8gzHGYcLcwIuozDdu0zy4JFflj+l0jpfX6n9ge",
+	"TpUT39BMyVHlhxDMzhruuFxnWXjk1giZSAudq3QeKy8fuXPJsd7UTKMkWqjm1zZi3O/tODf0Hg+zC4b/",
+	"Y7oT78JXzZLYfdJltCncZKt3cKmJGYszw/5u/U4rRSYZp/2qdYdCb+Ew1o9VuKlr9IeBW0+sTWFZ2aJb",
+	"UM7yjPk2luVrDOmgEMuvVh3eoYZD5mfrp9d8PL26Xs1th3cuvH6vja3nBZx6vf1QP4v9m87W4o/wtzj5",
+	"/yqVrshbpv9Hk+fEvG+q6pBgIyUZCVBJdXa++mg735snrLRKck8q9MKKP9TOQ/1hmon1yTjOqcP/YvJJ",
+	"Z+u2bdt/AwAA//+RpToKFwoAAA==",
 }
 
 // GetSwagger returns the content of the embedded swagger specification file
diff --git a/backend/api/handlers.go b/backend/api/handlers.go
index 162e30a..9856ce9 100644
--- a/backend/api/handlers.go
+++ b/backend/api/handlers.go
@@ -11,6 +11,8 @@ import (
 	"github.com/nsfisis/iosdc-2024-albatross-backend/db"
 )
 
+var _ StrictServerInterface = (*ApiHandler)(nil)
+
 type ApiHandler struct {
 	q *db.Queries
 }
@@ -28,22 +30,20 @@ func (h *ApiHandler) PostLogin(ctx context.Context, request PostLoginRequestObje
 	if err != nil {
 		return PostLogin401JSONResponse{
 			Message: "Invalid username or password",
-		}, echo.NewHTTPError(http.StatusUnauthorized, "Invalid username or password")
+		}, nil
 	}
 
 	user, err := h.q.GetUserById(ctx, int32(userId))
 	if err != nil {
 		return PostLogin401JSONResponse{
 			Message: "Invalid username or password",
-		}, echo.NewHTTPError(http.StatusUnauthorized, "Invalid username or password")
+		}, nil
 	}
 
 	jwt, err := auth.NewJWT(&user)
 	if err != nil {
 		// TODO
-		return PostLogin401JSONResponse{
-			Message: "Internal Server Error",
-		}, echo.NewHTTPError(http.StatusInternalServerError, "Internal Server Error")
+		return nil, echo.NewHTTPError(http.StatusInternalServerError)
 	}
 
 	return PostLogin200JSONResponse{
@@ -51,6 +51,89 @@ func (h *ApiHandler) PostLogin(ctx context.Context, request PostLoginRequestObje
 	}, nil
 }
 
+func (h *ApiHandler) GetGames(ctx context.Context, request GetGamesRequestObject) (GetGamesResponseObject, error) {
+	user := ctx.Value("user").(*auth.JWTClaims)
+	playerId := request.Params.PlayerId
+	if !user.IsAdmin {
+		if playerId == nil || *playerId != user.UserID {
+			return GetGames403JSONResponse{
+				Message: "Forbidden",
+			}, nil
+		}
+	}
+	if playerId == nil {
+		gameRows, err := h.q.ListGames(ctx)
+		if err != nil {
+			return nil, echo.NewHTTPError(http.StatusInternalServerError)
+		}
+		games := make([]Game, len(gameRows))
+		for i, row := range gameRows {
+			var startedAt *int
+			if row.StartedAt.Valid {
+				startedAtTimestamp := int(row.StartedAt.Time.Unix())
+				startedAt = &startedAtTimestamp
+			}
+			var problem *Problem
+			if row.ProblemID.Valid {
+				if !row.Title.Valid || !row.Description.Valid {
+					panic("inconsistent data")
+				}
+				problem = &Problem{
+					ProblemId:   int(row.ProblemID.Int32),
+					Title:       row.Title.String,
+					Description: row.Description.String,
+				}
+			}
+			games[i] = Game{
+				GameId:          int(row.GameID),
+				State:           GameState(row.State),
+				DisplayName:     row.DisplayName,
+				DurationSeconds: int(row.DurationSeconds),
+				StartedAt:       startedAt,
+				Problem:         problem,
+			}
+		}
+		return GetGames200JSONResponse{
+			Games: games,
+		}, nil
+	} else {
+		gameRows, err := h.q.ListGamesForPlayer(ctx, int32(*playerId))
+		if err != nil {
+			return nil, echo.NewHTTPError(http.StatusInternalServerError)
+		}
+		games := make([]Game, len(gameRows))
+		for i, row := range gameRows {
+			var startedAt *int
+			if row.StartedAt.Valid {
+				startedAtTimestamp := int(row.StartedAt.Time.Unix())
+				startedAt = &startedAtTimestamp
+			}
+			var problem *Problem
+			if row.ProblemID.Valid {
+				if !row.Title.Valid || !row.Description.Valid {
+					panic("inconsistent data")
+				}
+				problem = &Problem{
+					ProblemId:   int(row.ProblemID.Int32),
+					Title:       row.Title.String,
+					Description: row.Description.String,
+				}
+			}
+			games[i] = Game{
+				GameId:          int(row.GameID),
+				State:           GameState(row.State),
+				DisplayName:     row.DisplayName,
+				DurationSeconds: int(row.DurationSeconds),
+				StartedAt:       startedAt,
+				Problem:         problem,
+			}
+		}
+		return GetGames200JSONResponse{
+			Games: games,
+		}, nil
+	}
+}
+
 func _assertJwtPayloadIsCompatibleWithJWTClaims() {
 	var c auth.JWTClaims
 	var p JwtPayload
diff --git a/backend/db/query.sql.go b/backend/db/query.sql.go
index 12651d2..20a7dc1 100644
--- a/backend/db/query.sql.go
+++ b/backend/db/query.sql.go
@@ -68,3 +68,107 @@ func (q *Queries) GetUserById(ctx context.Context, userID int32) (User, error) {
 	)
 	return i, err
 }
+
+const listGames = `-- name: ListGames :many
+SELECT game_id, state, display_name, duration_seconds, created_at, started_at, games.problem_id, problems.problem_id, title, description FROM games
+LEFT JOIN problems ON games.problem_id = problems.problem_id
+`
+
+type ListGamesRow struct {
+	GameID          int32
+	State           string
+	DisplayName     string
+	DurationSeconds int32
+	CreatedAt       pgtype.Timestamp
+	StartedAt       pgtype.Timestamp
+	ProblemID       pgtype.Int4
+	ProblemID_2     pgtype.Int4
+	Title           pgtype.Text
+	Description     pgtype.Text
+}
+
+func (q *Queries) ListGames(ctx context.Context) ([]ListGamesRow, error) {
+	rows, err := q.db.Query(ctx, listGames)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []ListGamesRow
+	for rows.Next() {
+		var i ListGamesRow
+		if err := rows.Scan(
+			&i.GameID,
+			&i.State,
+			&i.DisplayName,
+			&i.DurationSeconds,
+			&i.CreatedAt,
+			&i.StartedAt,
+			&i.ProblemID,
+			&i.ProblemID_2,
+			&i.Title,
+			&i.Description,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const listGamesForPlayer = `-- name: ListGamesForPlayer :many
+SELECT games.game_id, 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
+LEFT 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
+`
+
+type ListGamesForPlayerRow struct {
+	GameID          int32
+	State           string
+	DisplayName     string
+	DurationSeconds int32
+	CreatedAt       pgtype.Timestamp
+	StartedAt       pgtype.Timestamp
+	ProblemID       pgtype.Int4
+	ProblemID_2     pgtype.Int4
+	Title           pgtype.Text
+	Description     pgtype.Text
+	GameID_2        int32
+	UserID          int32
+}
+
+func (q *Queries) ListGamesForPlayer(ctx context.Context, userID int32) ([]ListGamesForPlayerRow, error) {
+	rows, err := q.db.Query(ctx, listGamesForPlayer, userID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []ListGamesForPlayerRow
+	for rows.Next() {
+		var i ListGamesForPlayerRow
+		if err := rows.Scan(
+			&i.GameID,
+			&i.State,
+			&i.DisplayName,
+			&i.DurationSeconds,
+			&i.CreatedAt,
+			&i.StartedAt,
+			&i.ProblemID,
+			&i.ProblemID_2,
+			&i.Title,
+			&i.Description,
+			&i.GameID_2,
+			&i.UserID,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
diff --git a/backend/go.mod b/backend/go.mod
index e19bfc3..7e47d35 100644
--- a/backend/go.mod
+++ b/backend/go.mod
@@ -17,6 +17,7 @@ require (
 require (
 	filippo.io/edwards25519 v1.1.0 // indirect
 	github.com/antlr4-go/antlr/v4 v4.13.0 // indirect
+	github.com/apapsch/go-jsonmerge/v2 v2.0.0 // indirect
 	github.com/cubicdaiya/gonp v1.0.4 // indirect
 	github.com/cznic/mathutil v0.0.0-20181122101859-297441e03548 // indirect
 	github.com/davecgh/go-spew v1.1.1 // indirect
diff --git a/backend/go.sum b/backend/go.sum
index c4c4e16..bc38c89 100644
--- a/backend/go.sum
+++ b/backend/go.sum
@@ -1,9 +1,13 @@
 filippo.io/edwards25519 v1.1.0 h1:FNf4tywRC1HmFuKW5xopWpigGjJKiJSV0Cqo0cJWDaA=
 filippo.io/edwards25519 v1.1.0/go.mod h1:BxyFTGdWcka3PhytdK4V28tE5sGfRvvvRV7EaN4VDT4=
 github.com/BurntSushi/toml v0.3.1/go.mod h1:xHWCNGjB5oqiDr8zfno3MHue2Ht5sIBksp03qcyfWMU=
+github.com/RaveNoX/go-jsoncommentstrip v1.0.0/go.mod h1:78ihd09MekBnJnxpICcwzCMzGrKSKYe4AqU6PDYYpjk=
 github.com/antlr4-go/antlr/v4 v4.13.0 h1:lxCg3LAv+EUK6t1i0y1V6/SLeUi0eKEKdhQAlS8TVTI=
 github.com/antlr4-go/antlr/v4 v4.13.0/go.mod h1:pfChB/xh/Unjila75QW7+VU4TSnWnnk9UTnmpPaOR2g=
+github.com/apapsch/go-jsonmerge/v2 v2.0.0 h1:axGnT1gRIfimI7gJifB699GoE/oq+F2MU7Dml6nw9rQ=
+github.com/apapsch/go-jsonmerge/v2 v2.0.0/go.mod h1:lvDnEdqiQrp0O42VQGgmlKpxL1AP2+08jFMw88y4klk=
 github.com/benbjohnson/clock v1.1.0/go.mod h1:J11/hYXuz8f4ySSvYwY0FKfm+ezbsZBKZxNJlLklBHA=
+github.com/bmatcuk/doublestar v1.1.1/go.mod h1:UD6OnuiIn0yFxxA2le/rnRU1G4RaI4UvFv1sNto9p6w=
 github.com/cpuguy83/go-md2man/v2 v2.0.3/go.mod h1:tgQtvFlXSQOSOSIRvRPT7W67SCa46tRHOmNcaadrF8o=
 github.com/cubicdaiya/gonp v1.0.4 h1:ky2uIAJh81WiLcGKBVD5R7KsM/36W6IqqTy6Bo6rGws=
 github.com/cubicdaiya/gonp v1.0.4/go.mod h1:iWGuP/7+JVTn02OWhRemVbMmG1DOUnmrGTYYACpOI0I=
@@ -64,6 +68,7 @@ github.com/jinzhu/inflection v1.0.0 h1:K317FqzuhWc8YvSVlFMCCUb36O/S9MCKRDI7QkRKD
 github.com/jinzhu/inflection v1.0.0/go.mod h1:h+uFLlag+Qp1Va5pdKtLDYj+kHp5pxUVkryuEj+Srlc=
 github.com/josharian/intern v1.0.0 h1:vlS4z54oSdjm0bgjRigI+G1HpF+tI+9rE5LLzOg8HmY=
 github.com/josharian/intern v1.0.0/go.mod h1:5DoeVV0s6jJacbCEi61lwdGj/aVlrQvzHFFd8Hwg//Y=
+github.com/juju/gnuflag v0.0.0-20171113085948-2ce1bb71843d/go.mod h1:2PavIy+JPciBPrBUjwbNvtwB6RQlve+hkpll6QSNmOE=
 github.com/kr/pretty v0.1.0/go.mod h1:dAy3ld7l9f0ibDNOQOHHMYYIIbhfbHSm3C4ZsoJORNo=
 github.com/kr/pretty v0.3.1 h1:flRD4NNwYAUpkphVc1HcthR4KEIFJ65n8Mw5qdRn3LE=
 github.com/kr/pretty v0.3.1/go.mod h1:hoEshYVHaxMs3cyo3Yncou5ZscifuDolrwPKZanG3xk=
@@ -124,6 +129,7 @@ github.com/spf13/cobra v1.8.0 h1:7aJaZx1B85qltLMc546zn58BxxfZdR/W22ej9CFoEf0=
 github.com/spf13/cobra v1.8.0/go.mod h1:WXLWApfZ71AjXPya3WOlMsY9yMs7YeiHhFVlvLyhcho=
 github.com/spf13/pflag v1.0.5 h1:iy+VFUOCP1a+8yFto/drg2CJ5u0yRoB7fZw3DKv/JXA=
 github.com/spf13/pflag v1.0.5/go.mod h1:McXfInJRrz4CZXVZOBLb0bTZqETkiAhM9Iw0y3An2Bg=
+github.com/spkg/bom v0.0.0-20160624110644-59b7046e48ad/go.mod h1:qLr4V1qq6nMqFKkMo8ZTx3f+BZEkzsRUY10Xsm2mwU0=
 github.com/sqlc-dev/sqlc v1.26.0 h1:bW6TA1vVdi2lfqsEddN5tSznRMYcWez7hf+AOqSiEp8=
 github.com/sqlc-dev/sqlc v1.26.0/go.mod h1:k2F3RWilLCup3D0XufrzZENCyXjtplALmHDmOt4v5bs=
 github.com/stoewer/go-strcase v1.2.0 h1:Z2iHWqGXH00XYgqDmNgQbIBxf3wrNq0F3feEy0ainaU=
diff --git a/backend/query.sql b/backend/query.sql
index 165c2c9..9b038a5 100644
--- a/backend/query.sql
+++ b/backend/query.sql
@@ -8,3 +8,13 @@ SELECT * FROM users
 JOIN user_auths ON users.user_id = user_auths.user_id
 WHERE users.username = $1
 LIMIT 1;
+
+-- name: ListGames :many
+SELECT * FROM games
+LEFT JOIN problems ON games.problem_id = problems.problem_id;
+
+-- name: ListGamesForPlayer :many
+SELECT * FROM games
+LEFT 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;