From abca372d8ef3d9ab0154c3706d88e0c3772bacc3 Mon Sep 17 00:00:00 2001 From: Pinapelz Date: Tue, 23 Sep 2025 14:48:00 -0700 Subject: add community scores API and frontend views --- backend/src/index.ts | 1 + backend/src/routes/score.ts | 145 ++++++++++++++++ frontend/src/App.tsx | 2 + frontend/src/components/NavBar.tsx | 88 ++++------ frontend/src/pages/AllScores.tsx | 341 +++++++++++++++++++++++++++++++++++++ 5 files changed, 524 insertions(+), 53 deletions(-) create mode 100644 frontend/src/pages/AllScores.tsx diff --git a/backend/src/index.ts b/backend/src/index.ts index 01fc8d9..ed9c45a 100644 --- a/backend/src/index.ts +++ b/backend/src/index.ts @@ -58,6 +58,7 @@ app.post('/api/uploadScore', requireAuth, scoreRoutes.handleScoreUpload); app.get('/api/scores', requireAuth, scoreRoutes.handleGetScores); app.delete('/api/scores', requireAuth, scoreRoutes.handleScoreDeletion); app.get('/api/scores/:chartId', requireAuth, scoreRoutes.handleGetScoresByChartId); +app.get('/api/allScores', requireAuth, scoreRoutes.handleGetAllGameScores); app.post('/api/admin/createGame', requireAuth, adminRoutes.handleCreateGame); diff --git a/backend/src/routes/score.ts b/backend/src/routes/score.ts index ffb104a..2190a38 100644 --- a/backend/src/routes/score.ts +++ b/backend/src/routes/score.ts @@ -421,3 +421,148 @@ export const handleGetScoresByChartId = async ( .json({ error: "Internal server error. Unable to fetch scores" }); } }; + +export const handleGetAllGameScores = async ( + req: express.Request, + res: express.Response, +) => { + try { + const { internalGameName, pageNum, sortKey, direction, pbOnly } = req.query; + if (!internalGameName || !pageNum) { + return res.status(400).json({ error: "Missing required parameters" }); + } + + const pageNumber = parseInt(pageNum as string); + const gameInternalName = internalGameName as string; + const sortKeyString = (sortKey as string) || "timestamp"; + const directionString = + (direction as string)?.toLowerCase() === "asc" ? "asc" : "desc"; + const pbOnlyFlag = pbOnly === "true"; + + if ( + directionString && + directionString !== "asc" && + directionString !== "desc" + ) { + return res.status(400).json({ error: "Invalid direction parameter" }); + } + + let scores; + let totalScores; + + if (pbOnlyFlag) { + // For pbOnly, we need to get the best score for each chart for each user + if (sortKeyString === "timestamp") { + scores = await prisma.$queryRawUnsafe( + ` + SELECT DISTINCT ON (s."chartId", s."userId") s.*, u.username + FROM "Score" s + JOIN "User" u ON s."userId" = u.id + WHERE s."gameInternalName" = $1 + ORDER BY s."chartId", s."userId", s."timestamp" ${directionString.toUpperCase()} + OFFSET $2 + LIMIT $3 + `, + gameInternalName, + (pageNumber - 1) * PAGE_SIZE, + PAGE_SIZE, + ); + } else { + scores = await prisma.$queryRawUnsafe( + ` + SELECT DISTINCT ON (s."chartId", s."userId") s.*, u.username + FROM "Score" s + JOIN "User" u ON s."userId" = u.id + WHERE s."gameInternalName" = $1 + ORDER BY s."chartId", s."userId", (s.data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()} + OFFSET $2 + LIMIT $3 + `, + gameInternalName, + (pageNumber - 1) * PAGE_SIZE, + PAGE_SIZE, + ); + } + + // Count distinct chart-user combinations for pagination + const combinationCountResult = await prisma.$queryRawUnsafe( + ` + SELECT COUNT(DISTINCT (s."chartId", s."userId")) as count + FROM "Score" s + WHERE s."gameInternalName" = $1 + `, + gameInternalName, + ); + totalScores = Number(combinationCountResult[0]?.count || 0); + } else { + totalScores = await prisma.score.count({ + where: { + gameInternalName, + }, + }); + + if (sortKeyString === "timestamp") { + scores = await prisma.score.findMany({ + where: { + gameInternalName, + }, + include: { + user: { + select: { + username: true, + }, + }, + }, + orderBy: { + timestamp: directionString, + }, + skip: (pageNumber - 1) * PAGE_SIZE, + take: PAGE_SIZE, + }); + } else { + // everything else attempt to rawsql it + scores = await prisma.$queryRawUnsafe( + ` + SELECT s.*, u.username FROM "Score" s + JOIN "User" u ON s."userId" = u.id + WHERE s."gameInternalName" = $1 + ORDER BY (s.data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()} + OFFSET $2 + LIMIT $3 + `, + gameInternalName, + (pageNumber - 1) * PAGE_SIZE, + PAGE_SIZE, + ); + } + } + + const num_pages = Math.ceil(totalScores / PAGE_SIZE); + if (!scores) { + return res.status(404).json({ + error: + "No scores found. Either no scores exist or the sortKey provided is invalid for the game, sortKey: " + + sortKeyString, + }); + } + + const safeScores = scores.map((score) => ({ + ...score, + username: score.user?.username || score.username, + timestamp: + typeof score.timestamp === "bigint" + ? Number(score.timestamp) + : score.timestamp, + })); + + res.status(200).json({ + scores: safeScores, + num_pages, + }); + } catch (error) { + console.error("Failed to fetch all game scores:", error); + res + .status(500) + .json({ error: "Internal server error. Unable to fetch scores" }); + } +}; diff --git a/frontend/src/App.tsx b/frontend/src/App.tsx index 9b0e058..934eecd 100644 --- a/frontend/src/App.tsx +++ b/frontend/src/App.tsx @@ -8,6 +8,7 @@ import Home from "./pages/Home"; import Score from "./pages/Score"; import Chart from "./pages/Chart"; import Admin from "./pages/Admin"; +import AllScores from "./pages/AllScores"; function App() { return ( @@ -21,6 +22,7 @@ function App() { } /> } /> } /> + } /> ); diff --git a/frontend/src/components/NavBar.tsx b/frontend/src/components/NavBar.tsx index 7e111d0..4be8607 100644 --- a/frontend/src/components/NavBar.tsx +++ b/frontend/src/components/NavBar.tsx @@ -5,58 +5,40 @@ export const NavBar = ({ currentPage, user, handleLogout }: { user: { username: string }; handleLogout: () => void; }) => { - const getMenuOptions = () => { - switch (currentPage) { - case 'dashboard': - return ( - <> - - Import Data - - - ); - case 'import': - return ( - <> - - Home - - - ); - case 'score': - return ( - <> - - Home - - - Import Data - - - ); - default: - return ( - - Import Data - - ); - } - }; + const menuOptions = ( + <> + + Home + + + Import Data + + + Community Scores + + + ); return (