generate sha-1 hash for chart on score import

2 files changed, 40 insertions, 6 deletions

diff --git a/backend/schema.prisma b/backend/schema.prisma
index 467d59d..a613628 100644
--- a/backend/schema.prisma
+++ b/backend/schema.prisma
@@ -30,14 +30,26 @@ model Game {
   formattedName  String  @unique
   description    String
   scores Score[]
+  charts Charts[]
 }
 
 model Score {
-  id     Int  @id @default(autoincrement())
+  id               Int     @id @default(autoincrement())
   gameInternalName String
-  userId Int
-  timestamp BigInt //in UNIX milliseconds
-  data   Json
-  game   Game @relation(fields: [gameInternalName], references: [internalName])
-  user   User @relation(fields: [userId], references: [id])
+  chartId          String
+  userId           Int
+  timestamp        BigInt // in UNIX milliseconds
+  data             Json
+  game   Game   @relation(fields: [gameInternalName], references: [internalName])
+  user   User   @relation(fields: [userId], references: [id])
+  chart  Charts @relation(fields: [chartId], references: [chartId])
+}
+
+model Charts {
+  chartId          String  @id // platform-wide unique hash
+  gameInternalName String
+  title        String
+  artist           String
+  game   Game   @relation(fields: [gameInternalName], references: [internalName])
+  scores Score[]
 }
diff --git a/backend/src/routes/score.ts b/backend/src/routes/score.ts
index e0f2281..d0fdc56 100644
--- a/backend/src/routes/score.ts
+++ b/backend/src/routes/score.ts
@@ -1,6 +1,7 @@
 import express from "express";
 import { prisma } from "../config/db";
 import { PAGE_SIZE } from "../config/constants";
+import crypto from "crypto";
 
 export const handleScoreUpload = async (
   req: express.Request,
@@ -42,6 +43,10 @@ export const handleScoreUpload = async (
     let skippedCount = 0;
 
     for (const scoreData of scoresArray) {
+      const chartIdHash = crypto
+        .createHash("sha1")
+        .update(`${internalGameName}${scoreData.title}${scoreData.artist}`)
+        .digest("hex");
       // Check if exact same score data already exists
       const existingScore = await prisma.score.findFirst({
         where: {
@@ -56,9 +61,26 @@ export const handleScoreUpload = async (
       if (existingScore) {
         skippedCount++;
       } else {
+        const chartExists = await prisma.charts.findFirst({
+          where: {
+            gameInternalName: internalGameName,
+            chartId: chartIdHash,
+          },
+        });
+        if(!chartExists){
+          await prisma.charts.create({
+            data: {
+              gameInternalName: internalGameName,
+              chartId: chartIdHash,
+              title: scoreData.title,
+              artist: scoreData.artist,
+            },
+          });
+        }
         scoresToCreate.push({
           gameInternalName: internalGameName,
           userId: userId,
+          chartId: chartIdHash,
           timestamp: scoreData.timestamp,
           data: scoreData,
         });