feat(backend): add /admin/online-qualifying-ranking

5 files changed, 146 insertions, 0 deletions

diff --git a/backend/admin/handler.go b/backend/admin/handler.go
index 8daead0..161f85c 100644
--- a/backend/admin/handler.go
+++ b/backend/admin/handler.go
@@ -63,6 +63,7 @@ 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("/online-qualifying-ranking", h.getOnlineQualifyingRanking)
 }
 
 func (h *Handler) getDashboard(c echo.Context) error {
@@ -388,3 +389,40 @@ func (h *Handler) postGameStart(c echo.Context) error {
 
 	return c.Redirect(http.StatusSeeOther, basePath+"/admin/games")
 }
+
+func (h *Handler) getOnlineQualifyingRanking(c echo.Context) error {
+	game1, err := strconv.Atoi(c.QueryParam("game_1"))
+	if err != nil {
+		return echo.NewHTTPError(http.StatusBadRequest, "Invalid game_1")
+	}
+	game2, err := strconv.Atoi(c.QueryParam("game_2"))
+	if err != nil {
+		return echo.NewHTTPError(http.StatusBadRequest, "Invalid game_2")
+	}
+
+	rows, err := h.q.GetQualifyingRanking(c.Request().Context(), db.GetQualifyingRankingParams{
+		GameID:   int32(game1),
+		GameID_2: int32(game2),
+	})
+	if err != nil {
+		return echo.NewHTTPError(http.StatusInternalServerError, err.Error())
+	}
+
+	entries := make([]echo.Map, len(rows))
+	for i, r := range rows {
+		entries[i] = echo.Map{
+			"Rank":         i + 1,
+			"Username":     r.Username,
+			"Score1":       r.CodeSize1,
+			"Score2":       r.CodeSize2,
+			"TotalScore":   r.TotalCodeSize,
+			"SubmittedAt1": r.SubmittedAt1.Time.In(jst).Format("2006-01-02T15:04"),
+			"SubmittedAt2": r.SubmittedAt2.Time.In(jst).Format("2006-01-02T15:04"),
+		}
+	}
+	return c.Render(http.StatusOK, "online_qualifying_ranking", echo.Map{
+		"BasePath": basePath,
+		"Title":    "Online Qualifying Ranking",
+		"Entries":  entries,
+	})
+}
diff --git a/backend/admin/templates/dashboard.html b/backend/admin/templates/dashboard.html
index 15b10ff..2d030ef 100644
--- a/backend/admin/templates/dashboard.html
+++ b/backend/admin/templates/dashboard.html
@@ -7,6 +7,9 @@
 <p>
   <a href="{{ .BasePath }}/admin/games">Games</a>
 </p>
+<p>
+  <a href="{{ .BasePath }}/admin/online-qualifying-ranking?game_1=7&game_2=8">Online Qualifying Ranking</a>
+</p>
 <form method="post" action="{{ .BasePath }}/logout">
   <button type="submit">Logout</button>
 </form>
diff --git a/backend/admin/templates/online_qualifying_ranking.html b/backend/admin/templates/online_qualifying_ranking.html
new file mode 100644
index 0000000..3b00046
--- /dev/null
+++ b/backend/admin/templates/online_qualifying_ranking.html
@@ -0,0 +1,31 @@
+{{ template "base.html" . }}
+
+{{ define "breadcrumb" }}
+<a href="{{ .BasePath }}/admin/dashboard">Dashboard</a>
+{{ end }}
+
+{{ define "content" }}
+<table>
+  <thead>
+    <tr>
+      <th scope="col">順位</th>
+      <th scope="col">プレイヤー</th>
+      <th scope="col">スコア</th>
+      <th scope="col">提出時刻</th>
+    </tr>
+  </thead>
+  <tbody>
+    {{ range .Entries }}
+      <tr>
+        <td>{{ .Rank }}</td>
+        <td>{{ .Username }}</td>
+        <td>{{ .Score1 }}</td>
+        <td>{{ .Score2 }}</td>
+        <td>{{ .TotalScore }}</td>
+        <td>{{ .SubmittedAt1 }}</td>
+        <td>{{ .SubmittedAt2 }}</td>
+      </tr>
+    {{ end }}
+  </tbody>
+</table>
+{{ end }}
diff --git a/backend/db/query.sql.go b/backend/db/query.sql.go
index 5e840fa..a478b69 100644
--- a/backend/db/query.sql.go
+++ b/backend/db/query.sql.go
@@ -273,6 +273,64 @@ func (q *Queries) GetLatestStatesOfMainPlayers(ctx context.Context, gameID int32
 	return items, nil
 }
 
+const getQualifyingRanking = `-- name: GetQualifyingRanking :many
+SELECT
+    u.username AS username,
+    s1.code_size AS code_size_1,
+    s2.code_size AS code_size_2,
+    (s1.code_size + s2.code_size) AS total_code_size,
+    s1.created_at AS submitted_at_1,
+    s2.created_at AS submitted_at_2
+FROM game_states gs1
+JOIN submissions s1 ON gs1.best_score_submission_id = s1.submission_id
+JOIN game_states gs2 ON gs1.user_id = gs2.user_id
+JOIN submissions s2 ON gs2.best_score_submission_id = s2.submission_id
+JOIN users u ON gs1.user_id = u.user_id
+WHERE gs1.game_id = $1 AND gs2.game_id = $2
+ORDER BY total_code_size ASC
+`
+
+type GetQualifyingRankingParams struct {
+	GameID   int32
+	GameID_2 int32
+}
+
+type GetQualifyingRankingRow struct {
+	Username      string
+	CodeSize1     int32
+	CodeSize2     int32
+	TotalCodeSize int32
+	SubmittedAt1  pgtype.Timestamp
+	SubmittedAt2  pgtype.Timestamp
+}
+
+func (q *Queries) GetQualifyingRanking(ctx context.Context, arg GetQualifyingRankingParams) ([]GetQualifyingRankingRow, error) {
+	rows, err := q.db.Query(ctx, getQualifyingRanking, arg.GameID, arg.GameID_2)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []GetQualifyingRankingRow
+	for rows.Next() {
+		var i GetQualifyingRankingRow
+		if err := rows.Scan(
+			&i.Username,
+			&i.CodeSize1,
+			&i.CodeSize2,
+			&i.TotalCodeSize,
+			&i.SubmittedAt1,
+			&i.SubmittedAt2,
+		); 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
     submissions.submission_id, submissions.game_id, submissions.user_id, submissions.code, submissions.code_size, submissions.status, submissions.created_at,
diff --git a/backend/query.sql b/backend/query.sql
index 2a95a23..4707eff 100644
--- a/backend/query.sql
+++ b/backend/query.sql
@@ -132,6 +132,22 @@ JOIN submissions ON game_states.best_score_submission_id = submissions.submissio
 WHERE game_states.game_id = $1
 ORDER BY submissions.code_size ASC, submissions.created_at ASC;
 
+-- name: GetQualifyingRanking :many
+SELECT
+    u.username AS username,
+    s1.code_size AS code_size_1,
+    s2.code_size AS code_size_2,
+    (s1.code_size + s2.code_size) AS total_code_size,
+    s1.created_at AS submitted_at_1,
+    s2.created_at AS submitted_at_2
+FROM game_states gs1
+JOIN submissions s1 ON gs1.best_score_submission_id = s1.submission_id
+JOIN game_states gs2 ON gs1.user_id = gs2.user_id
+JOIN submissions s2 ON gs2.best_score_submission_id = s2.submission_id
+JOIN users u ON gs1.user_id = u.user_id
+WHERE gs1.game_id = $1 AND gs2.game_id = $2
+ORDER BY total_code_size ASC;
+
 -- name: UpdateCode :exec
 INSERT INTO game_states (game_id, user_id, code, status)
 VALUES ($1, $2, $3, $4)