generate sha-1 hash for chart on score import

5 files changed, 55 insertions, 24 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,
         });
diff --git a/frontend/src/components/displays/DancerushScoreDisplay.tsx b/frontend/src/components/displays/DancerushScoreDisplay.tsx
index 16ba2b2..e99f6e9 100644
--- a/frontend/src/components/displays/DancerushScoreDisplay.tsx
+++ b/frontend/src/components/displays/DancerushScoreDisplay.tsx
@@ -1,4 +1,5 @@
 import React from "react";
+import { globalSkipKeys } from "../../types/constants";
 import dancerushEasyImg from "../../assets/games/dancerush/easy.webp";
 import dancerushNormalImg from "../../assets/games/dancerush/normal.webp";
 
@@ -41,13 +42,6 @@ const DancerushScoreDisplay: React.FC<ScoreDisplayProps> = ({
     miss: "Miss",
   };
 
-  const skipKeys = [
-    "id",
-    "internalname",
-    "internalName",
-    "gameInternalName",
-    "userId",
-  ];
   const primaryKeys = ["title", "artist", "song"];
   const mainStatKeys = [
     "score",
@@ -169,7 +163,7 @@ const DancerushScoreDisplay: React.FC<ScoreDisplayProps> = ({
 
   const getScoreEntries = (score: Score) => {
     const entries = Object.entries(score).filter(
-      ([key]) => !skipKeys.includes(key),
+      ([key]) => !globalSkipKeys.includes(key),
     );
 
     const primary = entries.filter(([key]) => primaryKeys.includes(key));
@@ -228,7 +222,7 @@ const DancerushScoreDisplay: React.FC<ScoreDisplayProps> = ({
   // Get all possible keys for table headers
   const allKeys = Array.from(
     new Set(scores.flatMap((score) => Object.keys(score))),
-  ).filter((key) => !skipKeys.includes(key));
+  ).filter((key) => !globalSkipKeys.includes(key));
 
   // Prioritize important keys for table display
   const tableKeys = [
diff --git a/frontend/src/components/displays/GenericScoreDisplay.tsx b/frontend/src/components/displays/GenericScoreDisplay.tsx
index 3358f8d..c30e475 100644
--- a/frontend/src/components/displays/GenericScoreDisplay.tsx
+++ b/frontend/src/components/displays/GenericScoreDisplay.tsx
@@ -1,4 +1,5 @@
 import React from "react";
+import { globalSkipKeys } from "../../types/constants";
 
 interface Score {
   // eslint-disable-next-line @typescript-eslint/no-explicit-any
@@ -53,13 +54,6 @@ const ScoreDisplay: React.FC<ScoreDisplayProps> = ({
     time: "Time",
   };
 
-  const skipKeys = [
-    "id",
-    "internalname",
-    "internalName",
-    "gameInternalName",
-    "userId",
-  ];
   const primaryKeys = ["title", "artist", "song"];
   const mainStatKeys = [
     "score",
@@ -165,7 +159,7 @@ const ScoreDisplay: React.FC<ScoreDisplayProps> = ({
 
   const getScoreEntries = (score: Score) => {
     const entries = Object.entries(score).filter(
-      ([key]) => !skipKeys.includes(key),
+      ([key]) => !globalSkipKeys.includes(key),
     );
 
     const primary = entries.filter(([key]) => primaryKeys.includes(key));
@@ -224,7 +218,7 @@ const ScoreDisplay: React.FC<ScoreDisplayProps> = ({
   // Get all possible keys for table headers
   const allKeys = Array.from(
     new Set(scores.flatMap((score) => Object.keys(score))),
-  ).filter((key) => !skipKeys.includes(key));
+  ).filter((key) => !globalSkipKeys.includes(key));
 
   // Prioritize important keys for table display
   const tableKeys = [
diff --git a/frontend/src/types/constants.ts b/frontend/src/types/constants.ts
index 1e44b21..b1309e9 100644
--- a/frontend/src/types/constants.ts
+++ b/frontend/src/types/constants.ts
@@ -4,6 +4,15 @@ export const EamuseImportInfo: Record<string, { scorePage: string }> = {
   },
 };
 
+export const globalSkipKeys = [
+  "id",
+  "internalname",
+  "internalName",
+  "gameInternalName",
+  "userId",
+  "chartId"
+];
+
 export function getFilterOptions(game: string): { value: string; label: string }[] {
   switch (game) {
     case "dancerush":