implement chart view by ID, allow request by pbOnly

2 files changed, 230 insertions, 31 deletions

diff --git a/backend/src/index.ts b/backend/src/index.ts
index 114dfac..f51eaa2 100644
--- a/backend/src/index.ts
+++ b/backend/src/index.ts
@@ -56,6 +56,7 @@ app.get('/api/supportedGames', gameRoutes.handleGetSupportedGames);
 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.listen(port, () => {
   console.log(`Server listening on port ${port}`);
diff --git a/backend/src/routes/score.ts b/backend/src/routes/score.ts
index 54e4784..ffb104a 100644
--- a/backend/src/routes/score.ts
+++ b/backend/src/routes/score.ts
@@ -67,7 +67,7 @@ export const handleScoreUpload = async (
             chartId: chartIdHash,
           },
         });
-        if(!chartExists){
+        if (!chartExists) {
           await prisma.charts.create({
             data: {
               gameInternalName: internalGameName,
@@ -145,57 +145,254 @@ export const handleGetScores = async (
   res: express.Response,
 ) => {
   try {
-    const { userId, internalGameName, pageNum, sortKey, direction } = req.query;
+    const { userId, internalGameName, pageNum, sortKey, direction, pbOnly } =
+      req.query;
     if (!userId || !internalGameName || !pageNum) {
       return res.status(400).json({ error: "Missing required parameters" });
     }
-
     const pageNumber = parseInt(pageNum as string);
     const gameInternalName = internalGameName as string;
     const userIdNumber = parseInt(userId as string);
     const sortKeyString = (sortKey as string) || "timestamp";
     const directionString =
       (direction as string)?.toLowerCase() === "asc" ? "asc" : "desc";
+    const pbOnlyFlag = pbOnly === "true";
 
-    const num_pages = Math.ceil(
-      (await prisma.score.count({
-        where: {
-          gameInternalName,
-          userId: userIdNumber,
-        },
-      })) / PAGE_SIZE,
-    );
+    if (
+      directionString &&
+      directionString !== "asc" &&
+      directionString !== "desc"
+    ) {
+      return res.status(400).json({ error: "Invalid direction parameter" });
+    }
 
     let scores;
+    let totalScores;
 
-    if (sortKeyString === "timestamp") {
-      scores = await prisma.score.findMany({
+    if (pbOnlyFlag) {
+      // For pbOnly, we need to get the best score for each chart
+      if (sortKeyString === "timestamp") {
+        scores = await prisma.$queryRawUnsafe<any[]>(
+          `
+          SELECT DISTINCT ON ("chartId") *
+          FROM "Score"
+          WHERE "gameInternalName" = $1 AND "userId" = $2
+          ORDER BY "chartId", "timestamp" ${directionString.toUpperCase()}
+          OFFSET $3
+          LIMIT $4
+          `,
+          gameInternalName,
+          userIdNumber,
+          (pageNumber - 1) * PAGE_SIZE,
+          PAGE_SIZE,
+        );
+      } else {
+        scores = await prisma.$queryRawUnsafe<any[]>(
+          `
+          SELECT DISTINCT ON ("chartId") *
+          FROM "Score"
+          WHERE "gameInternalName" = $1 AND "userId" = $2
+          ORDER BY "chartId", (data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()}
+          OFFSET $3
+          LIMIT $4
+          `,
+          gameInternalName,
+          userIdNumber,
+          (pageNumber - 1) * PAGE_SIZE,
+          PAGE_SIZE,
+        );
+      }
+
+      // Count distinct charts for pagination
+      const chartCountResult = await prisma.$queryRawUnsafe<any[]>(
+        `
+        SELECT COUNT(DISTINCT "chartId") as count
+        FROM "Score"
+        WHERE "gameInternalName" = $1 AND "userId" = $2
+        `,
+        gameInternalName,
+        userIdNumber,
+      );
+      totalScores = Number(chartCountResult[0]?.count || 0);
+    } else {
+      totalScores = await prisma.score.count({
         where: {
           gameInternalName,
           userId: userIdNumber,
         },
-        orderBy: {
-          timestamp: directionString,
-        },
-        skip: (pageNumber - 1) * PAGE_SIZE,
-        take: PAGE_SIZE,
       });
-    } else {
-      // everything else attempt to rawsql it
-      scores = await prisma.$queryRawUnsafe<any[]>(
+
+      if (sortKeyString === "timestamp") {
+        scores = await prisma.score.findMany({
+          where: {
+            gameInternalName,
+            userId: userIdNumber,
+          },
+          orderBy: {
+            timestamp: directionString,
+          },
+          skip: (pageNumber - 1) * PAGE_SIZE,
+          take: PAGE_SIZE,
+        });
+      } else {
+        // everything else attempt to rawsql it
+        scores = await prisma.$queryRawUnsafe<any[]>(
+          `
+          SELECT * FROM "Score"
+          WHERE "gameInternalName" = $1 AND "userId" = $2
+          ORDER BY (data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()}
+          OFFSET $3
+          LIMIT $4
+        `,
+          gameInternalName,
+          userIdNumber,
+          (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,
+      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 scores:", error);
+    res
+      .status(500)
+      .json({ error: "Internal server error. Unable to fetch scores" });
+  }
+};
+
+
+export const handleGetScoresByChartId = async (
+  req: express.Request,
+  res: express.Response,
+) => {
+  try {
+    const { chartId } = req.params;
+    const { sortKey, direction, pageNum, pbOnly } = req.query;
+    const chartIdString = chartId as string;
+    const pageNumber = parseInt(pageNum 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) {
+      if (sortKeyString === "timestamp") {
+        scores = await prisma.$queryRawUnsafe<any[]>(
+          `
+          SELECT DISTINCT ON (s."userId") s.*, u.username
+          FROM "Score" s
+          JOIN "User" u ON s."userId" = u.id
+          WHERE s."chartId" = $1
+          ORDER BY s."userId", s."timestamp" ${directionString.toUpperCase()}
+          OFFSET $2
+          LIMIT $3
+          `,
+          chartIdString,
+          (pageNumber - 1) * PAGE_SIZE,
+          PAGE_SIZE,
+        );
+      } else {
+        scores = await prisma.$queryRawUnsafe<any[]>(
+          `
+          SELECT DISTINCT ON (s."userId") s.*, u.username
+          FROM "Score" s
+          JOIN "User" u ON s."userId" = u.id
+          WHERE s."chartId" = $1
+          ORDER BY s."userId", (s.data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()}
+          OFFSET $2
+          LIMIT $3
+          `,
+          chartIdString,
+          (pageNumber - 1) * PAGE_SIZE,
+          PAGE_SIZE,
+        );
+      }
+
+      // Count distinct users for pagination
+      const userCountResult = await prisma.$queryRawUnsafe<any[]>(
         `
-        SELECT * FROM "Score"
-        WHERE "gameInternalName" = $1 AND "userId" = $2
-        ORDER BY (data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()}
-        OFFSET $3
-        LIMIT $4
-      `,
-        gameInternalName,
-        userIdNumber,
-        (pageNumber - 1) * PAGE_SIZE,
-        PAGE_SIZE,
+        SELECT COUNT(DISTINCT "userId") as count
+        FROM "Score"
+        WHERE "chartId" = $1
+        `,
+        chartIdString,
       );
+      totalScores = Number(userCountResult[0]?.count || 0);
+    } else {
+      totalScores = await prisma.score.count({
+        where: {
+          chartId: chartIdString,
+        },
+      });
+
+      if (sortKeyString === "timestamp") {
+        scores = await prisma.score.findMany({
+          where: {
+            chartId: chartIdString,
+          },
+          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<any[]>(
+          `
+          SELECT s.*, u.username FROM "Score" s
+          JOIN "User" u ON s."userId" = u.id
+          WHERE s."chartId" = $1
+          ORDER BY (s.data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()}
+          OFFSET $2
+          LIMIT $3
+        `,
+          chartIdString,
+          (pageNumber - 1) * PAGE_SIZE,
+          PAGE_SIZE,
+        );
+      }
     }
+
+    const num_pages = Math.ceil(totalScores / PAGE_SIZE);
     if (!scores) {
       return res.status(404).json({
         error:
@@ -206,6 +403,7 @@ export const handleGetScores = async (
 
     const safeScores = scores.map((score) => ({
       ...score,
+      username: score.user?.username || score.username,
       timestamp:
         typeof score.timestamp === "bigint"
           ? Number(score.timestamp)