feat: show submission date on ranking

8 files changed, 134 insertions, 115 deletions

diff --git a/backend/api/generated.go b/backend/api/generated.go
index a297c97..0616d51 100644
--- a/backend/api/generated.go
+++ b/backend/api/generated.go
@@ -79,8 +79,9 @@ type Problem struct {
 
 // RankingEntry defines model for RankingEntry.
 type RankingEntry struct {
-	Player User `json:"player"`
-	Score  int  `json:"score"`
+	Player      User  `json:"player"`
+	Score       int   `json:"score"`
+	SubmittedAt int64 `json:"submitted_at"`
 }
 
 // User defines model for User.
@@ -1127,30 +1128,30 @@ func (sh *strictHandler) PostLogin(ctx echo.Context) error {
 // Base64 encoded, gzipped, json marshaled Swagger object
 var swaggerSpec = []string{
 
-	"H4sIAAAAAAAC/+xYXW/bNhf+KwTfF+iNGttNEHTZVbq1RYeiMNoVw1AUAi0d20wpUiWpOl6h/z4cUl+U",
-	"5FpJm24plovAInm+Hz485GeaqCxXEqQ19OIzzZlmGVjQ7msLLAUds8JuleZ/McuVxHEu6UU1SSMqWQb0",
-	"gl4GqyKq4WPBNaT0wuoCImqSLWQMxe0+RwFjNZcbWpYRzZndxhuWQczTxgAOturr2QmKubSwAU1LVK3B",
-	"5EoacAE9Yelr+FiAsfiVKGlBup8szwVPnOuzK+OjbPX+X8OaXtD/zdpkzfysmT3VWlWmUjCJ5rnPEtoi",
-	"ujJWRvSZ0iuepiDv3nJrqozoK2WfqUKmd2/2lbJk7UyVEX0ra9TAdzAdWMPpSgIVeiHEtlY5aMs9FDIw",
-	"hm0Af8I1y3KByHkhPzHB27pFI1ht4feuUfK+WahWV5C4gj+9hqRA/95YZgtnE2SRoZhUEhDIhZSoNaKm",
-	"SBIwhkZ0p5XcxEyandtblmegCusX40cMLpzIgVxLJqqB91EnjFZdz/2IPne7qZ+MlJtcsH0sq9lWFa4n",
-	"izFNaaFdDWMDiZKpCeROz+fRYDtGtLPFm6WLgwv9cJu2xSd0JCuE5egt9KL20wM/uYnzYiV4Elj11FEt",
-	"XiklgLntkjEuY6/dRcQtZOYYLt8a73WljmnN9vida7USkB0TX1bLELeWaQtpzGzg7U9n5+ePzx7Ph0mN",
-	"6PXDjXrYjp6fDVDaUmeb1m5eorD+I6VtQ+llaAz4L5kFYxE4iPwRtCUq7aEMkq0iD7YghCI7pUX64Oex",
-	"UppE6VBygSmRhRBsNahqB06m2YJfpJfeju3n0flde9EoHUvBsi18b6N1aaubgd+33BBuCCNtpgfxV1OT",
-	"dpBxk/Gtc225FT3JKqoxOuhlquNorSmk7NC/sRS+ZvIDl5un0ur9MI/V/p+4Lw/gZtAu9ILwNmrxMSed",
-	"gRuw6W9qK8mvCkZ5KlEydj1PIDLjGduAmV2prTy5yjcHKI6lGQ8htWbCjFKcYCsQoRFj2XpND+6k1lJh",
-	"QA/g9+h0DIC4dJgBDOMofGorHSUDjmpirgMa1gfVcrlWrjX0cKaXYsWsVsaQ+gAlO1iRy+ULGtFPoI1v",
-	"KOYni5M5RqFykCzn9IKensxP5tT3qq7MM2RT92sDjq4RA443X6R4coLjQEeena763Thk2yWz0a67fN9r",
-	"ZR/N5zfqq0KINq5POt9c0zA430ZOGXOgCmG39pIbS9SaeIkyomfzxSEXmphnYY+HQqfHhTqtMPJAkWUM",
-	"6cS7UNkvo6qUs8/VSVkeK+o3qml0VC64GN0BBqZVfqTSkwp96VL83SqMEmfHJZobUQiJ52AJqxwegcQM",
-	"yWdWH6e5MiPoWCrf9ywF2//im4V/DCbuAvNEpfuvQMgtm4extmkcLuFVvhyHd4ipN/5usy6E2JMiT5mt",
-	"wfKvRxjCg9gtEOE6ZOIScxhrflVs6ib6S4SEiPN9t++5fwR+agL/EkH1Lxt98HklU8jqT1XoujIYEfGi",
-	"94e8OsiC+iZDNJhCWLJWmlT97GHAmWKVcTuN3t74tf8R3N0SnC/J/aE4DwsHxeoNaxRuO2aTbUBwR/vo",
-	"P1CkQ3Hmx+E494ulKUcRJpbBihuR3xCDI2w4rUm/FKLLKC0jGmzdcSZjXJL6Iege9Xg3DOwwfrV/opiE",
-	"3Oo544cAbSfuSdfH4Cnn2DWyVj4Fo0tXI1KL3MujOu/FgHATauMfcw6fwy/dkm91DubMmJ3S4btOM7p4",
-	"dEoPvAV9xQOPrO/RlemvOEBvGbVVH6D3DHuNfyed/0dD8UqmoDU42BGBIC26euvDPYDVWwOaeOCUZVn+",
-	"HQAA//+TXFqx2R0AAA==",
+	"H4sIAAAAAAAC/+xYXY/TOBf+K5bfV+ImNC0zGrHdq2EXECuEKli0WiEUuclp68Gxg+3Q6aL895XtfDlx",
+	"aWZg2B20czFqEp/v5zw+9mecirwQHLhWePkZF0SSHDRI+7QDkoFMSKl3QtK/iKaCm/eU42X9EUeYkxzw",
+	"El96qyIs4WNJJWR4qWUJEVbpDnJixPWhMAJKS8q3uKoiXBC9S7Ykh4RmrQHzslPffJ2gmHINW5C4Mqol",
+	"qEJwBTagJyR7DR9LUNo8pYJr4PYnKQpGU+t6fKVclJ3e/0vY4CX+X9wlK3ZfVfxUSlGbykClkhYuS8YW",
+	"krWxKsLPhFzTLAN+95Y7U1WEXwn9TJQ8u3uzr4RGG2uqivBb3qAGvoNpz5r5XEsYhU7IYFuKAqSmDgo5",
+	"KEW2YH7CNckLZpDzgn8ijHZ1iwJY7eD3rlXyvl0o1leQ2oI/vYa0NP690USX1ibwMjdiXHAwQC45N1oj",
+	"rMo0BaVwhPdS8G1CuNrb3tI0B1Fqt9g8JGDDiSzIJSesfvE+6oXRqRu4H+HntpuGycioKhg5JLz+2qky",
+	"69EipCkrpa1hoiAVPFOe3NnFPBq1Y4R7Ld4uXRxd6F53aVt8Mo7kJdPUeAuDqN3nkZ9UJUW5ZjT1rDrq",
+	"qBevhWBAbLvkhPLEabcRUQ25OoXLt8p5XasjUpKDeS6kWDPIT4mv6mUGt5pIDVlCtOftT+cXF4/PH8/H",
+	"SY3w9cOteNi9vTgfobSjzi6t/bxEfv0Dpe1CGWQoBPyXRIPSBjgG+QG0pSIboAzSnUAPdsCYQHshWfbg",
+	"51ApVSqkL7kwKeElY2Q9qmoPTqptwS/Sy6Bjh3m0fjdetEpDKVh1hR80Wp+2+hn4fUcVogoR1GV6FH/9",
+	"aVIHKfsxuXWuNdVsIFlHFaKDQaZ6jjaafMr2/Qul8DXhHyjfPuVaHsZ5rPt/Yl8ewU0gaeU6p/qbN2Dt",
+	"bQ86fTuh6K3nN6Dp38SOo18FBAkwFTyxw5QnEtOcbEHFV2LHZ1fF9gh3kiynPlY3hKkgdzKyBuYbUZps",
+	"Nvhoi3aWSgVyhOtHZ6EimaXjDJgwTuKysdJTMiK/NuYmoHF9jFrKN8LOnK5P8CVbEy2FUqjZmdEe1uhy",
+	"9QJH+BNI5SaV+Wwxm5soRAGcFBQv8dlsPptjNwTbMseGpu2vLVgYGgxYQn6RmS0ZLLlaVu6N6+/CvdAt",
+	"iYPjfPV+MCM/ms9vNLD5EG1dn7Rx2mlktHEGti91pAr+GPiSKo3EBjmJKsLn88UxF9qYY394NEJnp4V6",
+	"M7bljTwnhqecC7X9KqpLGX+ut+DqVFG/UU2jk3LeiesOMDCt8oFKTyr0pU3xd6uwkTg/LdEetXxIPAeN",
+	"SO1wABKxIZ+42acLoQLoWAk3UK0YOfzippB/DCb2ZPREZIevQMgtp5LQPBaGi39HUIXh7WPqjTs0bUrG",
+	"DqgsMqIbsPzrEWbggfQOELOjN7KJOY41typRzXT+JUIyiHMDvRvmfwR+agP/EkENTzFD8DklU8jqT1HK",
+	"pjImIuRE7w959ZAFzREJSVAl02gjJKrH2+OAc+PuNHp749b+R3B3S3DtCeSe4NDBwkKxvhwLwm1PdLrz",
+	"CO7kHP2HEelRnPpxOM7+IllGjQhhK2/FjchvjMEAG04b0i8Z6zNKx4jKjO7mS04oR80N0z2a8W4Y2HH8",
+	"Snf3MQm59T3JDwHaXtyTjo/eHdGpY2SjfApGV7ZGqBG5l1t1MYjBwI2JrbvMOb4Pv7RLvtU+WBCl9kL6",
+	"9zrt28WjM3zkLugrLnh4c46uTX/FBnrLqLX4AIP73WvzN+v9PxmKUzIFrd7GbhAIXBtXb725e7B6q0Ai",
+	"B5yqqqq/AwAA///orTnDMh4AAA==",
 }
 
 // GetSwagger returns the content of the embedded swagger specification file
diff --git a/backend/api/handler.go b/backend/api/handler.go
index 4514a25..2cb04c5 100644
--- a/backend/api/handler.go
+++ b/backend/api/handler.go
@@ -252,14 +252,15 @@ func (h *Handler) GetGameWatchRanking(ctx context.Context, request GetGameWatchR
 	for i, row := range rows {
 		ranking[i] = RankingEntry{
 			Player: User{
-				UserID:      int(row.UserID),
-				Username:    row.Username,
-				DisplayName: row.DisplayName,
-				IconPath:    row.IconPath,
-				IsAdmin:     row.IsAdmin,
-				Label:       toNullable(row.Label),
+				UserID:      int(row.User.UserID),
+				Username:    row.User.Username,
+				DisplayName: row.User.DisplayName,
+				IconPath:    row.User.IconPath,
+				IsAdmin:     row.User.IsAdmin,
+				Label:       toNullable(row.User.Label),
 			},
-			Score: int(row.CodeSize),
+			Score:       int(row.Submission.CodeSize),
+			SubmittedAt: row.Submission.CreatedAt.Time.Unix(),
 		}
 	}
 	return GetGameWatchRanking200JSONResponse{
diff --git a/backend/db/query.sql.go b/backend/db/query.sql.go
index c7f4fe9..5e840fa 100644
--- a/backend/db/query.sql.go
+++ b/backend/db/query.sql.go
@@ -274,7 +274,10 @@ func (q *Queries) GetLatestStatesOfMainPlayers(ctx context.Context, gameID int32
 }
 
 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, label, users.created_at, submission_id, submissions.game_id, submissions.user_id, submissions.code, code_size, submissions.status, submissions.created_at FROM game_states
+SELECT
+    submissions.submission_id, submissions.game_id, submissions.user_id, submissions.code, submissions.code_size, submissions.status, submissions.created_at,
+    users.user_id, users.username, users.display_name, users.icon_path, users.is_admin, users.label, users.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
@@ -282,25 +285,8 @@ 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
-	Label                 *string
-	CreatedAt             pgtype.Timestamp
-	SubmissionID          int32
-	GameID_2              int32
-	UserID_3              int32
-	Code_2                string
-	CodeSize              int32
-	Status_2              string
-	CreatedAt_2           pgtype.Timestamp
+	Submission Submission
+	User       User
 }
 
 func (q *Queries) GetRanking(ctx context.Context, gameID int32) ([]GetRankingRow, error) {
@@ -313,25 +299,20 @@ func (q *Queries) GetRanking(ctx context.Context, gameID int32) ([]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.Label,
-			&i.CreatedAt,
-			&i.SubmissionID,
-			&i.GameID_2,
-			&i.UserID_3,
-			&i.Code_2,
-			&i.CodeSize,
-			&i.Status_2,
-			&i.CreatedAt_2,
+			&i.Submission.SubmissionID,
+			&i.Submission.GameID,
+			&i.Submission.UserID,
+			&i.Submission.Code,
+			&i.Submission.CodeSize,
+			&i.Submission.Status,
+			&i.Submission.CreatedAt,
+			&i.User.UserID,
+			&i.User.Username,
+			&i.User.DisplayName,
+			&i.User.IconPath,
+			&i.User.IsAdmin,
+			&i.User.Label,
+			&i.User.CreatedAt,
 		); err != nil {
 			return nil, err
 		}
diff --git a/backend/query.sql b/backend/query.sql
index 61175ab..2a95a23 100644
--- a/backend/query.sql
+++ b/backend/query.sql
@@ -123,7 +123,10 @@ LEFT JOIN submissions ON game_states.best_score_submission_id = submissions.subm
 WHERE game_main_players.game_id = $1;
 
 -- name: GetRanking :many
-SELECT * FROM game_states
+SELECT
+    sqlc.embed(submissions),
+    sqlc.embed(users)
+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
diff --git a/frontend/app/api/schema.d.ts b/frontend/app/api/schema.d.ts
index b5ec26b..0cace21 100644
--- a/frontend/app/api/schema.d.ts
+++ b/frontend/app/api/schema.d.ts
@@ -207,6 +207,8 @@ export interface components {
             player: components["schemas"]["User"];
             /** @example 100 */
             score: number;
+            /** @example 946684800 */
+            submitted_at: number;
         };
     };
     responses: {
diff --git a/frontend/app/components/Gaming/RankingTable.tsx b/frontend/app/components/Gaming/RankingTable.tsx
new file mode 100644
index 0000000..e712ed9
--- /dev/null
+++ b/frontend/app/components/Gaming/RankingTable.tsx
@@ -0,0 +1,66 @@
+import React from "react";
+import type { components } from "../../api/schema";
+
+type RankingEntry = components["schemas"]["RankingEntry"];
+
+type Props = {
+	ranking: RankingEntry[];
+};
+
+function TableHeaderCell({ children }: { children: React.ReactNode }) {
+	return (
+		<th scope="col" className="px-6 py-3 text-left font-medium text-gray-800">
+			{children}
+		</th>
+	);
+}
+
+function TableBodyCell({ children }: { children: React.ReactNode }) {
+	return (
+		<td className="px-6 py-4 whitespace-nowrap text-gray-900">{children}</td>
+	);
+}
+
+function formatUnixTimestamp(timestamp: number) {
+	const date = new Date(timestamp * 1000);
+
+	const year = date.getFullYear();
+	const month = (date.getMonth() + 1).toString().padStart(2, "0");
+	const day = date.getDate().toString().padStart(2, "0");
+	const hours = date.getHours().toString().padStart(2, "0");
+	const minutes = date.getMinutes().toString().padStart(2, "0");
+
+	return `${year}-${month}-${day} ${hours}:${minutes}`;
+}
+
+export default function RankingTable({ ranking }: Props) {
+	return (
+		<div className="overflow-hidden border-2 border-blue-600 rounded-xl">
+			<table className="min-w-full divide-y divide-gray-400 border-collapse">
+				<thead className="bg-gray-50">
+					<tr>
+						<TableHeaderCell>順位</TableHeaderCell>
+						<TableHeaderCell>プレイヤー</TableHeaderCell>
+						<TableHeaderCell>スコア</TableHeaderCell>
+						<TableHeaderCell>提出時刻</TableHeaderCell>
+					</tr>
+				</thead>
+				<tbody className="bg-white divide-y divide-gray-300">
+					{ranking.map((entry, index) => (
+						<tr key={entry.player.user_id}>
+							<TableBodyCell>{index + 1}</TableBodyCell>
+							<TableBodyCell>
+								{entry.player.display_name}
+								{entry.player.label && ` (${entry.player.label})`}
+							</TableBodyCell>
+							<TableBodyCell>{entry.score}</TableBodyCell>
+							<TableBodyCell>
+								{formatUnixTimestamp(entry.submitted_at)}
+							</TableBodyCell>
+						</tr>
+					))}
+				</tbody>
+			</table>
+		</div>
+	);
+}
diff --git a/frontend/app/components/GolfWatchApps/GolfWatchAppGamingMultiplayer.tsx b/frontend/app/components/GolfWatchApps/GolfWatchAppGamingMultiplayer.tsx
index a2b2d21..f3a377b 100644
--- a/frontend/app/components/GolfWatchApps/GolfWatchAppGamingMultiplayer.tsx
+++ b/frontend/app/components/GolfWatchApps/GolfWatchAppGamingMultiplayer.tsx
@@ -3,6 +3,7 @@ import type { components } from "../../api/schema";
 import { gamingLeftTimeSecondsAtom } from "../../states/watch";
 import LeftTime from "../Gaming/LeftTime";
 import Problem from "../Gaming/Problem";
+import RankingTable from "../Gaming/RankingTable";
 
 type RankingEntry = components["schemas"]["RankingEntry"];
 
@@ -41,48 +42,7 @@ export default function GolfWatchAppGamingMultiplayer({
 				/>
 				<div className="p-4 flex flex-col gap-4">
 					<div className="text-center text-xl font-bold">順位表</div>
-					<div className="overflow-hidden border-2 border-blue-600 rounded-xl">
-						<table className="min-w-full divide-y divide-gray-400 border-collapse">
-							<thead className="bg-gray-50">
-								<tr>
-									<th
-										scope="col"
-										className="px-6 py-3 text-left font-medium text-gray-800"
-									>
-										順位
-									</th>
-									<th
-										scope="col"
-										className="px-6 py-3 text-left font-medium text-gray-800"
-									>
-										プレイヤー
-									</th>
-									<th
-										scope="col"
-										className="px-6 py-3 text-left font-medium text-gray-800"
-									>
-										スコア
-									</th>
-								</tr>
-							</thead>
-							<tbody className="bg-white divide-y divide-gray-300">
-								{ranking.map((entry, index) => (
-									<tr key={entry.player.user_id}>
-										<td className="px-6 py-4 whitespace-nowrap text-gray-900">
-											{index + 1}
-										</td>
-										<td className="px-6 py-4 whitespace-nowrap text-gray-900">
-											{entry.player.display_name}
-											{entry.player.label && ` (${entry.player.label})`}
-										</td>
-										<td className="px-6 py-4 whitespace-nowrap text-gray-900">
-											{entry.score}
-										</td>
-									</tr>
-								))}
-							</tbody>
-						</table>
-					</div>
+					<RankingTable ranking={ranking} />
 				</div>
 			</div>
 		</div>
diff --git a/openapi/api-server.yaml b/openapi/api-server.yaml
index 9b43bc9..e6842fd 100644
--- a/openapi/api-server.yaml
+++ b/openapi/api-server.yaml
@@ -392,6 +392,11 @@ components:
         score:
           type: integer
           example: 100
+        submitted_at:
+          type: integer
+          example: 946684800
+          x-go-type: int64
       required:
         - player
         - score
+        - submitted_at