From 611d93ea8a5dcc7dd92ec412e8fb28078438b31b Mon Sep 17 00:00:00 2001 From: nsfisis Date: Wed, 17 Sep 2025 00:45:46 +0900 Subject: feat(backend): add admin pages for submissions --- backend/admin/handler.go | 83 +++++++++++++++++++++++ backend/admin/templates/game_edit.html | 3 + backend/admin/templates/games.html | 1 + backend/admin/templates/submission_detail.html | 37 +++++++++++ backend/admin/templates/submissions.html | 35 ++++++++++ backend/db/query.sql.go | 92 ++++++++++++++++++++++++++ backend/query.sql | 18 +++++ 7 files changed, 269 insertions(+) create mode 100644 backend/admin/templates/submission_detail.html create mode 100644 backend/admin/templates/submissions.html diff --git a/backend/admin/handler.go b/backend/admin/handler.go index 2a50cb0..5316250 100644 --- a/backend/admin/handler.go +++ b/backend/admin/handler.go @@ -68,6 +68,8 @@ func (h *Handler) RegisterHandlers(g *echo.Group) { g.GET("/games/:gameID", h.getGameEdit) g.POST("/games/:gameID", h.postGameEdit) g.POST("/games/:gameID/start", h.postGameStart) + g.GET("/games/:gameID/submissions", h.getSubmissions) + g.GET("/games/:gameID/submissions/:submissionID", h.getSubmissionDetail) g.GET("/problems", h.getProblems) g.GET("/problems/new", h.getProblemNew) @@ -576,6 +578,87 @@ func (h *Handler) postGameStart(c echo.Context) error { return c.Redirect(http.StatusSeeOther, h.conf.BasePath+"admin/games") } +func (h *Handler) getSubmissions(c echo.Context) error { + gameID, err := strconv.Atoi(c.Param("gameID")) + if err != nil { + return echo.NewHTTPError(http.StatusBadRequest, "Invalid game_id") + } + + submissions, err := h.q.GetSubmissionsByGameID(c.Request().Context(), int32(gameID)) + if err != nil { + return echo.NewHTTPError(http.StatusInternalServerError, err.Error()) + } + + entries := make([]echo.Map, len(submissions)) + for i, r := range submissions { + entries[i] = echo.Map{ + "SubmissionID": r.SubmissionID, + "UserID": r.UserID, + "Status": r.Status, + "CodeSize": r.CodeSize, + "CreatedAt": r.CreatedAt.Time.In(jst).Format("2006-01-02T15:04"), + } + } + + return c.Render(http.StatusOK, "submissions", echo.Map{ + "BasePath": h.conf.BasePath, + "Title": "Submissions", + "GameID": gameID, + "Submissions": entries, + }) +} + +func (h *Handler) getSubmissionDetail(c echo.Context) error { + gameID, err := strconv.Atoi(c.Param("gameID")) + if err != nil { + return echo.NewHTTPError(http.StatusBadRequest, "Invalid game_id") + } + + submissionID, err := strconv.Atoi(c.Param("submissionID")) + if err != nil { + return echo.NewHTTPError(http.StatusBadRequest, "Invalid submission_id") + } + + submission, err := h.q.GetSubmissionByID(c.Request().Context(), int32(submissionID)) + if err != nil { + if errors.Is(err, pgx.ErrNoRows) { + return echo.NewHTTPError(http.StatusNotFound) + } + return echo.NewHTTPError(http.StatusInternalServerError, err.Error()) + } + + testcaseResultRows, err := h.q.GetTestcaseResultsBySubmissionID(c.Request().Context(), int32(submissionID)) + if err != nil { + return echo.NewHTTPError(http.StatusInternalServerError, err.Error()) + } + testcaseResults := make([]echo.Map, len(testcaseResultRows)) + for i, r := range testcaseResultRows { + testcaseResults[i] = echo.Map{ + "TestcaseResultID": r.TestcaseResultID, + "TestcaseID": r.TestcaseID, + "Status": r.Status, + "CreatedAt": r.CreatedAt.Time.In(jst).Format("2006-01-02T15:04"), + "Stdout": r.Stdout, + "Stderr": r.Stderr, + } + } + + return c.Render(http.StatusOK, "submission_detail", echo.Map{ + "BasePath": h.conf.BasePath, + "Title": "Submission Detail", + "GameID": gameID, + "Submission": echo.Map{ + "SubmissionID": submission.SubmissionID, + "UserID": submission.UserID, + "Status": submission.Status, + "CodeSize": submission.CodeSize, + "CreatedAt": submission.CreatedAt.Time.In(jst).Format("2006-01-02T15:04"), + "Code": submission.Code, + }, + "TestcaseResults": testcaseResults, + }) +} + func (h *Handler) getProblems(c echo.Context) error { rows, err := h.q.ListProblems(c.Request().Context()) if err != nil { diff --git a/backend/admin/templates/game_edit.html b/backend/admin/templates/game_edit.html index b171343..a6900e8 100644 --- a/backend/admin/templates/game_edit.html +++ b/backend/admin/templates/game_edit.html @@ -66,4 +66,7 @@ +
+ View Submissions +
{{ end }} diff --git a/backend/admin/templates/games.html b/backend/admin/templates/games.html index d642f07..63f27b7 100644 --- a/backend/admin/templates/games.html +++ b/backend/admin/templates/games.html @@ -21,6 +21,7 @@ {{ if .IsPublic }}
  • Watch
    • {{ end }} +
  • Submissions
    • {{ end }} diff --git a/backend/admin/templates/submission_detail.html b/backend/admin/templates/submission_detail.html new file mode 100644 index 0000000..406c0b4 --- /dev/null +++ b/backend/admin/templates/submission_detail.html @@ -0,0 +1,37 @@ +{{ template "base.html" . }} + +{{ define "breadcrumb" }} +Dashboard | +Games | +Game {{ .GameID }} | +Submissions +{{ end }} + +{{ define "content" }} +

    Submission {{ .Submission.SubmissionID }}

    + +

    Basics

    + + +

    Code

    +
    {{ .Submission.Code }}
    + +

    Testcase Results

    +{{ range .TestcaseResults }} +

    Testcase Result {{ .TestcaseResultID }}

    + +
    Stdout
    + 
    {{ .Stdout }}
    +
    Stderr
    + 
    {{ .Stderr }}
    +{{ end }} +{{ end }} diff --git a/backend/admin/templates/submissions.html b/backend/admin/templates/submissions.html new file mode 100644 index 0000000..6870c2a --- /dev/null +++ b/backend/admin/templates/submissions.html @@ -0,0 +1,35 @@ +{{ template "base.html" . }} + +{{ define "breadcrumb" }} +Dashboard | +Games | +Game {{ .GameID }} +{{ end }} + +{{ define "content" }} +

    Submissions for Game {{ .GameID }}

    + + + + + + + + + + + + + {{ range .Submissions }} + + + + + + + + + {{ end }} + +
    IDUserStatusCode SizeCreated AtView
    {{ .SubmissionID }}{{ .UserID }}{{ .Status }}{{ .CodeSize }}{{ .CreatedAt }}View
    +{{ end }} diff --git a/backend/db/query.sql.go b/backend/db/query.sql.go index 05c841d..e807be4 100644 --- a/backend/db/query.sql.go +++ b/backend/db/query.sql.go @@ -500,6 +500,63 @@ func (q *Queries) GetRanking(ctx context.Context, gameID int32) ([]GetRankingRow return items, nil } +const getSubmissionByID = `-- name: GetSubmissionByID :one +SELECT submission_id, game_id, user_id, code, code_size, status, created_at +FROM submissions +WHERE submission_id = $1 +LIMIT 1 +` + +func (q *Queries) GetSubmissionByID(ctx context.Context, submissionID int32) (Submission, error) { + row := q.db.QueryRow(ctx, getSubmissionByID, submissionID) + var i Submission + err := row.Scan( + &i.SubmissionID, + &i.GameID, + &i.UserID, + &i.Code, + &i.CodeSize, + &i.Status, + &i.CreatedAt, + ) + return i, err +} + +const getSubmissionsByGameID = `-- name: GetSubmissionsByGameID :many +SELECT submission_id, game_id, user_id, code, code_size, status, created_at +FROM submissions +WHERE game_id = $1 +ORDER BY created_at DESC +` + +func (q *Queries) GetSubmissionsByGameID(ctx context.Context, gameID int32) ([]Submission, error) { + rows, err := q.db.Query(ctx, getSubmissionsByGameID, gameID) + if err != nil { + return nil, err + } + defer rows.Close() + var items []Submission + for rows.Next() { + var i Submission + if err := rows.Scan( + &i.SubmissionID, + &i.GameID, + &i.UserID, + &i.Code, + &i.CodeSize, + &i.Status, + &i.CreatedAt, + ); err != nil { + return nil, err + } + items = append(items, i) + } + if err := rows.Err(); err != nil { + return nil, err + } + return items, nil +} + const getTestcaseByID = `-- name: GetTestcaseByID :one SELECT testcase_id, problem_id, stdin, stdout FROM testcases WHERE testcase_id = $1 @@ -518,6 +575,41 @@ func (q *Queries) GetTestcaseByID(ctx context.Context, testcaseID int32) (Testca return i, err } +const getTestcaseResultsBySubmissionID = `-- name: GetTestcaseResultsBySubmissionID :many +SELECT testcase_result_id, submission_id, testcase_id, status, stdout, stderr, created_at +FROM testcase_results +WHERE submission_id = $1 +ORDER BY created_at +` + +func (q *Queries) GetTestcaseResultsBySubmissionID(ctx context.Context, submissionID int32) ([]TestcaseResult, error) { + rows, err := q.db.Query(ctx, getTestcaseResultsBySubmissionID, submissionID) + if err != nil { + return nil, err + } + defer rows.Close() + var items []TestcaseResult + for rows.Next() { + var i TestcaseResult + if err := rows.Scan( + &i.TestcaseResultID, + &i.SubmissionID, + &i.TestcaseID, + &i.Status, + &i.Stdout, + &i.Stderr, + &i.CreatedAt, + ); err != nil { + return nil, err + } + items = append(items, i) + } + if err := rows.Err(); err != nil { + return nil, err + } + return items, nil +} + const getUserAuthByUsername = `-- name: GetUserAuthByUsername :one SELECT users.user_id, username, display_name, icon_path, is_admin, label, created_at, user_auth_id, user_auths.user_id, auth_type, password_hash FROM users JOIN user_auths ON users.user_id = user_auths.user_id diff --git a/backend/query.sql b/backend/query.sql index 9baf593..0d84652 100644 --- a/backend/query.sql +++ b/backend/query.sql @@ -258,3 +258,21 @@ WHERE testcase_id = $1; -- name: DeleteTestcase :exec DELETE FROM testcases WHERE testcase_id = $1; + +-- name: GetSubmissionsByGameID :many +SELECT * +FROM submissions +WHERE game_id = $1 +ORDER BY created_at DESC; + +-- name: GetSubmissionByID :one +SELECT * +FROM submissions +WHERE submission_id = $1 +LIMIT 1; + +-- name: GetTestcaseResultsBySubmissionID :many +SELECT * +FROM testcase_results +WHERE submission_id = $1 +ORDER BY created_at; -- cgit v1.2.3-70-g09d2