add menu to allow score to be filtered by sortKey

2 files changed, 85 insertions, 53 deletions

diff --git a/backend/src/config/constants.ts b/backend/src/config/constants.ts
index 5ade0a7..8ab5cc7 100644
--- a/backend/src/config/constants.ts
+++ b/backend/src/config/constants.ts
@@ -1 +1 @@
-export const PAGE_SIZE = 30;
+export const PAGE_SIZE = 100;
diff --git a/backend/src/routes/score.ts b/backend/src/routes/score.ts
index 6e4dc19..e0f2281 100644
--- a/backend/src/routes/score.ts
+++ b/backend/src/routes/score.ts
@@ -1,29 +1,39 @@
-import express from 'express';
-import { prisma } from '../config/db';
-import { PAGE_SIZE } from '../config/constants';
+import express from "express";
+import { prisma } from "../config/db";
+import { PAGE_SIZE } from "../config/constants";
 
-export const handleScoreUpload = async (req: express.Request, res: express.Response) => {
+export const handleScoreUpload = async (
+  req: express.Request,
+  res: express.Response,
+) => {
   try {
     const { meta, scores } = req.body;
     const userId = req.session.userId;
     if (!userId) {
-      return res.status(401).json({ error: 'Unauthorized. Please log in to upload scores.' });
+      return res
+        .status(401)
+        .json({ error: "Unauthorized. Please log in to upload scores." });
     }
 
     // Basic universal validation
     if (!meta || !meta.game || !meta.service || !scores) {
-      return res.status(400).json({ error: 'Invalid request format. Expected meta with game/service and scores array' });
+      return res.status(400).json({
+        error:
+          "Invalid request format. Expected meta with game/service and scores array",
+      });
     }
     let game = await prisma.game.findUnique({
-      where: { internalName: meta.game }
+      where: { internalName: meta.game },
     });
     if (!game) {
       game = await prisma.game.findFirst({
-        where: { formattedName: meta.game }
+        where: { formattedName: meta.game },
       });
     }
     if (!game) {
-      return res.status(400).json({ error: `Game '${meta.game}' is not supported. Ensure that you are using the case-sensitive version of either the internal name or formatted name` });
+      return res.status(400).json({
+        error: `Game '${meta.game}' is not supported. Ensure that you are using the case-sensitive version of either the internal name or formatted name`,
+      });
     }
     const internalGameName = game.internalName;
     const scoresArray = Array.isArray(scores) ? scores : [scores];
@@ -38,9 +48,9 @@ export const handleScoreUpload = async (req: express.Request, res: express.Respo
           gameInternalName: internalGameName,
           userId: userId,
           data: {
-            equals: scoreData
-          }
-        }
+            equals: scoreData,
+          },
+        },
       });
 
       if (existingScore) {
@@ -50,92 +60,114 @@ export const handleScoreUpload = async (req: express.Request, res: express.Respo
           gameInternalName: internalGameName,
           userId: userId,
           timestamp: scoreData.timestamp,
-          data: scoreData
+          data: scoreData,
         });
       }
     }
 
-    const createdScores = scoresToCreate.length > 0
-      ? await prisma.score.createMany({
-          data: scoresToCreate
-        })
-      : { count: 0 };
+    const createdScores =
+      scoresToCreate.length > 0
+        ? await prisma.score.createMany({
+            data: scoresToCreate,
+          })
+        : { count: 0 };
 
     res.status(200).json({
-      message: 'Score upload processed successfully',
+      message: "Score upload processed successfully",
       game: meta.game,
       service: meta.service,
       scoreCount: createdScores.count,
       skippedCount: skippedCount,
-      totalProcessed: scoresArray.length
+      totalProcessed: scoresArray.length,
     });
-
   } catch (error) {
-    console.error('Score upload endpoint error:', error);
-    res.status(500).json({ error: 'Internal server error. Unable to process score upload' });
+    console.error("Score upload endpoint error:", error);
+    res
+      .status(500)
+      .json({ error: "Internal server error. Unable to process score upload" });
   }
-}
+};
 
-export const handleGetScores = async (req: express.Request, res: express.Response) => {
+export const handleGetScores = async (
+  req: express.Request,
+  res: express.Response,
+) => {
   try {
     const { userId, internalGameName, pageNum, sortKey, direction } = req.query;
     if (!userId || !internalGameName || !pageNum) {
-      return res.status(400).json({ error: 'Missing required parameters' });
+      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 sortKeyString = (sortKey as string) || "timestamp";
+    const directionString =
+      (direction as string)?.toLowerCase() === "asc" ? "asc" : "desc";
 
-    const num_pages = Math.ceil(await prisma.score.count({
-      where: {
-        gameInternalName,
-        userId: userIdNumber
-      }
-    }) / PAGE_SIZE);
+    const num_pages = Math.ceil(
+      (await prisma.score.count({
+        where: {
+          gameInternalName,
+          userId: userIdNumber,
+        },
+      })) / PAGE_SIZE,
+    );
 
     let scores;
 
-    if (sortKeyString === 'timestamp') {
+    if (sortKeyString === "timestamp") {
       scores = await prisma.score.findMany({
         where: {
           gameInternalName,
-          userId: userIdNumber
+          userId: userIdNumber,
         },
         orderBy: {
-          timestamp: directionString
+          timestamp: directionString,
         },
         skip: (pageNumber - 1) * PAGE_SIZE,
-        take: PAGE_SIZE
+        take: PAGE_SIZE,
       });
-    } else if (sortKeyString === 'score') {
-      // raw SQL for JSON field ordering
-      scores = await prisma.$queryRawUnsafe<any[]>(`
+    } else {
+      // everything else attempt to rawsql it
+      scores = await prisma.$queryRawUnsafe<any[]>(
+        `
         SELECT * FROM "Score"
         WHERE "gameInternalName" = $1 AND "userId" = $2
-        ORDER BY (data->>'score')::numeric ${directionString.toUpperCase()}
+        ORDER BY (data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()}
         OFFSET $3
         LIMIT $4
-      `, gameInternalName, userIdNumber, (pageNumber - 1) * PAGE_SIZE, PAGE_SIZE);
-    } else {
-      return res.status(400).json({ error: 'Invalid sort key' });
+      `,
+        gameInternalName,
+        userIdNumber,
+        (pageNumber - 1) * PAGE_SIZE,
+        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 => ({
+    const safeScores = scores.map((score) => ({
       ...score,
-      timestamp: typeof score.timestamp === 'bigint'
-        ? Number(score.timestamp)
-        : score.timestamp
+      timestamp:
+        typeof score.timestamp === "bigint"
+          ? Number(score.timestamp)
+          : score.timestamp,
     }));
 
     res.status(200).json({
       scores: safeScores,
-      num_pages
+      num_pages,
     });
   } catch (error) {
-    console.error('Failed to fetch scores:', error);
-    res.status(500).json({ error: 'Internal server error. Unable to fetch scores' });
+    console.error("Failed to fetch scores:", error);
+    res
+      .status(500)
+      .json({ error: "Internal server error. Unable to fetch scores" });
   }
 };