From 44606b2c510bb327982f60506fc0c968a51b6e80 Mon Sep 17 00:00:00 2001
From: Pinapelz <yukais@pinapelz.com>
Date: Wed, 5 Nov 2025 15:13:47 -0800
Subject: add score uploaded by game stats to profile page

---
 backend/src/routes/user.ts     | 26 +++++++++++++++++++++---
 frontend/src/pages/Profile.tsx | 46 +++++++++++++++++++++++++++++++++---------
 2 files changed, 60 insertions(+), 12 deletions(-)

diff --git a/backend/src/routes/user.ts b/backend/src/routes/user.ts
index b99964b..7e030fd 100644
--- a/backend/src/routes/user.ts
+++ b/backend/src/routes/user.ts
@@ -2,11 +2,17 @@
 import express from 'express';
 import { prisma } from '../config/db';
 
-interface recentPlayedGame {
+interface RecentPlayedGame {
   gameInternalName: string;
   timestamp: BigInt;
 }
 
+interface SafeGameCount {
+  gameInternalName: string;
+  formattedName: string;
+  count: number;
+}
+
 export const handleMeRoute = async (req: express.Request, res: express.Response) => {
   try {
     const { userId } = req.query;
@@ -17,7 +23,7 @@ export const handleMeRoute = async (req: express.Request, res: express.Response)
       where: { id: parseInt(userId as string) },
       select: { id: true, username: true, isAdmin: true, bio: true }
     });
-    const recentPlayedGames: recentPlayedGame[] = await prisma.$queryRaw`
+    const recentPlayedGames: RecentPlayedGame[] = await prisma.$queryRaw`
       SELECT DISTINCT ON (s."gameInternalName")
         g."formattedName",
         s."gameInternalName",
@@ -27,6 +33,16 @@ export const handleMeRoute = async (req: express.Request, res: express.Response)
       WHERE s."userId" = ${parseInt(userId as string)}
       ORDER BY s."gameInternalName", s."timestamp" DESC;
     `;
+    const scoreCountByGame: SafeGameCount[] = await prisma.$queryRaw`
+      SELECT
+        s."gameInternalName",
+        g."formattedName",
+        COUNT(*) as "count"
+      FROM "Score" s
+      INNER JOIN "Game" g ON g."internalName" = s."gameInternalName"
+      WHERE s."userId" = ${parseInt(userId as string)}
+      GROUP BY s."gameInternalName", g."formattedName"
+    `;
     const safeGames= recentPlayedGames.map((game) => ({
       ...game,
       timestamp:
@@ -34,9 +50,13 @@ export const handleMeRoute = async (req: express.Request, res: express.Response)
           ? Number(game.timestamp)
           : game.timestamp,
     }));
+    const safeScoreCountByGame = scoreCountByGame.map((game) => ({
+      ...game,
+      count: Number(game.count),
+    }));
     const isAdmin = user.id === 1 || user.isAdmin;
     const { isAdmin: _, ...safeUser } = user;
-    res.json({ user: safeUser, recentPlayedGames: safeGames, isAdmin });
+    res.json({ user: safeUser, recentPlayedGames: safeGames, scoreCountByGame: safeScoreCountByGame, isAdmin });
   } catch (error) {
     console.error('Me endpoint error:', error);
     res.status(500).json({ error: 'Internal server error' });
diff --git a/frontend/src/pages/Profile.tsx b/frontend/src/pages/Profile.tsx
index bffc710..83aa701 100644
--- a/frontend/src/pages/Profile.tsx
+++ b/frontend/src/pages/Profile.tsx
@@ -9,7 +9,13 @@ import Heatmap from "../components/Heatmap";
 import type { HeatmapData } from "../components/Heatmap";
 import type { User } from "../utils/authApi";
 
-interface recentPlayedGame {
+interface ScoreCountByGame {
+  gameInternalName: string;
+  formattedName: string;
+  count: number;
+}
+
+interface RecentPlayedGame {
   gameInternalName: string;
   formattedName: string;
   timestamp: number;
@@ -17,7 +23,8 @@ interface recentPlayedGame {
 
 interface UserData {
   user: User;
-  recentPlayedGames: recentPlayedGame[];
+  recentPlayedGames: RecentPlayedGame[];
+  scoreCountByGame: ScoreCountByGame[];
   isAdmin: boolean;
 }
 
@@ -132,9 +139,14 @@ const Profile = () => {
             {user.username}
           </h1>
           <p className="text-sm sm:text-base text-slate-400">
-            {userData?.user.bio ? userData.user.bio.replace(/</g, '&lt;').replace(/>/g, '&gt;') : "I'm a fairly non-descript person"}
+            {userData?.user?.bio ? userData.user.bio.replace(/</g, '&lt;').replace(/>/g, '&gt;') : "I'm a fairly non-descript person"}
           </p>
         </div>
+        {isBrowser ? (
+          <div className="flex flex-col items-center justify-center">
+            <Heatmap data={heatmapData.data} />
+          </div>
+        ) : null}
         <div className="mb-6 sm:mb-8">
           <h2 className="text-xl sm:text-2xl font-bold text-white mb-4">Recently Played Games</h2>
           <ul className="list-disc list-inside space-y-2 text-white">
@@ -146,12 +158,28 @@ const Profile = () => {
             ))}
           </ul>
         </div>
-        {isBrowser ? (
-          <div className="flex flex-col items-center justify-center">
-            <Heatmap data={heatmapData.data} />
-          </div>
-        ) : null}
-      </div>
+        <div className="mb-6 sm:mb-8">
+          <h2 className="text-xl sm:text-2xl font-bold text-white mb-4">Total Scores</h2>
+          <table className="table-auto w-full text-white border-collapse border border-slate-700">
+            <thead>
+              <tr>
+                <th className="border border-slate-700 px-4 py-2">Game</th>
+                <th className="border border-slate-700 px-4 py-2">Total Scores Uploaded</th>
+              </tr>
+            </thead>
+            <tbody>
+              {userData?.scoreCountByGame
+                .sort((a, b) => b.count - a.count)
+                .map((score, index) => (
+                  <tr key={index}>
+                    <td className="border border-slate-700 px-4 py-2">{score.formattedName}</td>
+                    <td className="border border-slate-700 px-4 py-2">{score.count}</td>
+                  </tr>
+                ))}
+            </tbody>
+          </table>
+        </div>
+    </div>
     </div>
   );
 };
-- 
cgit v1.2.3