handle JP score-grade/rank sorting

3 files changed, 434 insertions, 77 deletions

diff --git a/backend/src/routes/score.ts b/backend/src/routes/score.ts
index 2190a38..1f6bdf6 100644
--- a/backend/src/routes/score.ts
+++ b/backend/src/routes/score.ts
@@ -3,6 +3,37 @@ import { prisma } from "../config/db";
 import { PAGE_SIZE } from "../config/constants";
 import crypto from "crypto";
 
+// Helper function to create a CASE statement for Japanese-style grade sorting
+const createGradeCaseStatement = (sortKeyString: string, directionString: string): string => {
+  const gradeOrder = [
+    'F', 'F+',
+    'E-', 'E', 'E+',
+    'D-', 'D', 'D+',
+    'C-', 'C', 'C+',
+    'B-', 'B', 'B+',
+    'A-', 'A', 'A+',
+    'AA-', 'AA', 'AA+',
+    'AAA-', 'AAA', 'AAA+',
+    'S-', 'S', 'S+',
+    'SS-', 'SS', 'SS+',
+    'SSS-', 'SSS', 'SSS+'
+  ];
+
+  let caseStatement = `CASE `;
+  gradeOrder.forEach((grade, index) => {
+    caseStatement += `WHEN UPPER(TRIM(s.data->>'${sortKeyString}')) = '${grade}' THEN ${index} `;
+  });
+  caseStatement += `ELSE 999 END`;
+
+  if (directionString === 'desc') {
+    caseStatement += ' DESC';
+  } else {
+    caseStatement += ' ASC';
+  }
+
+  return caseStatement;
+};
+
 export const handleScoreUpload = async (
   req: express.Request,
   res: express.Response,
@@ -187,20 +218,49 @@ export const handleGetScores = async (
           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
-          `,
+        // Check if we need grade-based sorting by sampling one score
+        const sampleScore = await prisma.$queryRawUnsafe<any[]>(
+          `SELECT data->>'${sortKeyString}' as value FROM "Score" WHERE "gameInternalName" = $1 AND "userId" = $2 AND data->>'${sortKeyString}' IS NOT NULL LIMIT 1`,
           gameInternalName,
-          userIdNumber,
-          (pageNumber - 1) * PAGE_SIZE,
-          PAGE_SIZE,
+          userIdNumber
         );
+
+        // If it is not a numerical value
+        const isGradeSort = sampleScore.length > 0 &&
+          sampleScore[0].value &&
+          /^[A-Z]+[+-]?$/i.test(sampleScore[0].value.trim());
+
+        if (isGradeSort) {
+          scores = await prisma.$queryRawUnsafe<any[]>(
+            `
+            SELECT DISTINCT ON ("chartId") *
+            FROM "Score"
+            WHERE "gameInternalName" = $1 AND "userId" = $2
+            ORDER BY "chartId", ${createGradeCaseStatement(sortKeyString, directionString)}
+            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
@@ -235,20 +295,47 @@ export const handleGetScores = async (
           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
-        `,
+        // Check if we need grade-based sorting by sampling one score
+        const sampleScore = await prisma.$queryRawUnsafe<any[]>(
+          `SELECT data->>'${sortKeyString}' as value FROM "Score" WHERE "gameInternalName" = $1 AND "userId" = $2 AND data->>'${sortKeyString}' IS NOT NULL LIMIT 1`,
           gameInternalName,
-          userIdNumber,
-          (pageNumber - 1) * PAGE_SIZE,
-          PAGE_SIZE,
+          userIdNumber
         );
+
+        const isGradeSort = sampleScore.length > 0 &&
+          sampleScore[0].value &&
+          /^[A-Z]+[+-]?$/i.test(sampleScore[0].value.trim());
+
+        if (isGradeSort) {
+          scores = await prisma.$queryRawUnsafe<any[]>(
+            `
+            SELECT * FROM "Score"
+            WHERE "gameInternalName" = $1 AND "userId" = $2
+            ORDER BY ${createGradeCaseStatement(sortKeyString, directionString)}
+            OFFSET $3
+            LIMIT $4
+          `,
+            gameInternalName,
+            userIdNumber,
+            (pageNumber - 1) * PAGE_SIZE,
+            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,
+          );
+        }
       }
     }
 
@@ -323,20 +410,47 @@ export const handleGetScoresByChartId = async (
           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,
+        // Check if we need grade-based sorting by sampling one score
+        const sampleScore = await prisma.$queryRawUnsafe<any[]>(
+          `SELECT s.data->>'${sortKeyString}' as value FROM "Score" s WHERE s."chartId" = $1 AND s.data->>'${sortKeyString}' IS NOT NULL LIMIT 1`,
+          chartIdString
         );
+
+        const isGradeSort = sampleScore.length > 0 &&
+          sampleScore[0].value &&
+          /^[A-Z]+[+-]?$/i.test(sampleScore[0].value.trim());
+
+        if (isGradeSort) {
+          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", ${createGradeCaseStatement(sortKeyString, directionString)}
+            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
@@ -375,20 +489,46 @@ export const handleGetScoresByChartId = async (
           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,
+        // Check if we need grade-based sorting by sampling one score
+        const sampleScore = await prisma.$queryRawUnsafe<any[]>(
+          `SELECT s.data->>'${sortKeyString}' as value FROM "Score" s WHERE s."chartId" = $1 AND s.data->>'${sortKeyString}' IS NOT NULL LIMIT 1`,
+          chartIdString
         );
+
+        const isGradeSort = sampleScore.length > 0 &&
+          sampleScore[0].value &&
+          /^[A-Z]+[+-]?$/i.test(sampleScore[0].value.trim());
+
+        if (isGradeSort) {
+          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 ${createGradeCaseStatement(sortKeyString, directionString)}
+            OFFSET $2
+            LIMIT $3
+          `,
+            chartIdString,
+            (pageNumber - 1) * PAGE_SIZE,
+            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,
+          );
+        }
       }
     }
 
@@ -468,20 +608,47 @@ export const handleGetAllGameScores = async (
           PAGE_SIZE,
         );
       } else {
-        scores = await prisma.$queryRawUnsafe<any[]>(
-          `
-          SELECT DISTINCT ON (s."chartId", s."userId") s.*, u.username
-          FROM "Score" s
-          JOIN "User" u ON s."userId" = u.id
-          WHERE s."gameInternalName" = $1
-          ORDER BY s."chartId", s."userId", (s.data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()}
-          OFFSET $2
-          LIMIT $3
-          `,
-          gameInternalName,
-          (pageNumber - 1) * PAGE_SIZE,
-          PAGE_SIZE,
+        // Check if we need grade-based sorting by sampling one score
+        const sampleScore = await prisma.$queryRawUnsafe<any[]>(
+          `SELECT s.data->>'${sortKeyString}' as value FROM "Score" s WHERE s."gameInternalName" = $1 AND s.data->>'${sortKeyString}' IS NOT NULL LIMIT 1`,
+          gameInternalName
         );
+
+        const isGradeSort = sampleScore.length > 0 &&
+          sampleScore[0].value &&
+          /^[A-Z]+[+-]?$/i.test(sampleScore[0].value.trim());
+
+        if (isGradeSort) {
+          scores = await prisma.$queryRawUnsafe<any[]>(
+            `
+            SELECT DISTINCT ON (s."chartId", s."userId") s.*, u.username
+            FROM "Score" s
+            JOIN "User" u ON s."userId" = u.id
+            WHERE s."gameInternalName" = $1
+            ORDER BY s."chartId", s."userId", ${createGradeCaseStatement(sortKeyString, directionString)}
+            OFFSET $2
+            LIMIT $3
+            `,
+            gameInternalName,
+            (pageNumber - 1) * PAGE_SIZE,
+            PAGE_SIZE,
+          );
+        } else {
+          scores = await prisma.$queryRawUnsafe<any[]>(
+            `
+            SELECT DISTINCT ON (s."chartId", s."userId") s.*, u.username
+            FROM "Score" s
+            JOIN "User" u ON s."userId" = u.id
+            WHERE s."gameInternalName" = $1
+            ORDER BY s."chartId", s."userId", (s.data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()}
+            OFFSET $2
+            LIMIT $3
+            `,
+            gameInternalName,
+            (pageNumber - 1) * PAGE_SIZE,
+            PAGE_SIZE,
+          );
+        }
       }
 
       // Count distinct chart-user combinations for pagination
@@ -520,20 +687,46 @@ export const handleGetAllGameScores = async (
           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."gameInternalName" = $1
-          ORDER BY (s.data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()}
-          OFFSET $2
-          LIMIT $3
-        `,
-          gameInternalName,
-          (pageNumber - 1) * PAGE_SIZE,
-          PAGE_SIZE,
+        // Check if we need grade-based sorting by sampling one score
+        const sampleScore = await prisma.$queryRawUnsafe<any[]>(
+          `SELECT s.data->>'${sortKeyString}' as value FROM "Score" s WHERE s."gameInternalName" = $1 AND s.data->>'${sortKeyString}' IS NOT NULL LIMIT 1`,
+          gameInternalName
         );
+
+        const isGradeSort = sampleScore.length > 0 &&
+          sampleScore[0].value &&
+          /^[A-Z]+[+-]?$/i.test(sampleScore[0].value.trim());
+
+        if (isGradeSort) {
+          scores = await prisma.$queryRawUnsafe<any[]>(
+            `
+            SELECT s.*, u.username FROM "Score" s
+            JOIN "User" u ON s."userId" = u.id
+            WHERE s."gameInternalName" = $1
+            ORDER BY ${createGradeCaseStatement(sortKeyString, directionString)}
+            OFFSET $2
+            LIMIT $3
+          `,
+            gameInternalName,
+            (pageNumber - 1) * PAGE_SIZE,
+            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."gameInternalName" = $1
+            ORDER BY (s.data->>'${sortKeyString}')::numeric ${directionString.toUpperCase()}
+            OFFSET $2
+            LIMIT $3
+          `,
+            gameInternalName,
+            (pageNumber - 1) * PAGE_SIZE,
+            PAGE_SIZE,
+          );
+        }
       }
     }
 
diff --git a/scripts/dancearound/dancearound_play_history.js b/scripts/dancearound/dancearound_play_history.js
new file mode 100644
index 0000000..2a98994
--- /dev/null
+++ b/scripts/dancearound/dancearound_play_history.js
@@ -0,0 +1,164 @@
+// ==UserScript==
+// @name         DANCEAROUND Mirage Scraper
+// @namespace    http://tampermonkey.net/
+// @version      1.0
+// @description  DANCEAROUND e-amusement site to Mirage import JSON
+// @match        https://p.eagate.573.jp/game/around/1st/playdata/index.html*
+// @grant        none
+// @run-at       document-idle
+// ==/UserScript==
+
+(function () {
+    function waitFor(selector, timeout = 10000) {
+        return new Promise((resolve, reject) => {
+            const interval = 300;
+            let waited = 0;
+            const check = () => {
+                const el = document.querySelector(selector);
+                if (el) return resolve(el);
+                waited += interval;
+                if (waited >= timeout) return reject(`Timeout: ${selector}`);
+                setTimeout(check, interval);
+            };
+            check();
+        });
+    }
+
+    function getDifficulty(fumen, mdb) {
+        let difficulty, lamp;
+
+        switch (fumen) {
+            case "ADVANCED":
+                difficulty = mdb.fumens.ADVANCED.level;
+                lamp = "ADVANCED";
+                break;
+            case "BASIC":
+                difficulty = mdb.fumens.BASIC.level;
+                lamp = "BASIC";
+                break;
+            case "MASTER":
+                difficulty = mdb.fumens.MASTER.level;
+                lamp = "MASTER";
+                break;
+        }
+
+        return { difficulty, lamp };
+    }
+    function getLampText(status) {
+        switch (status) {
+            case 0:
+                return "C";
+            case 1:
+                return "B";
+            case 2:
+                return "A";
+            case 3:
+                return "AA";
+            case 4:
+                return "AAA";
+            case 5:
+                return "AAA+";
+        }
+    }
+
+    function getClearStatusText(status){
+      switch(status){
+        case 1:
+          return "FAILURE";
+        case 2:
+          return "PASSED";
+        case 3:
+          return "FULL COMBO";
+        case 4:
+          return "EXC";
+      }
+    }
+
+    async function fetchAndDownload() {
+        const url = "https://p.eagate.573.jp/game/around/1st/json/pdata_getdata.html";
+        const payload = new URLSearchParams({
+            service_kind: "play_hist",
+            pdata_kind: "play_hist",
+        });
+
+        try {
+            const response = await fetch(url, {
+                method: "POST",
+                credentials: "include",
+                headers: {
+                    "Content-Type": "application/x-www-form-urlencoded",
+                    "X-Requested-With": "XMLHttpRequest",
+                },
+                body: payload.toString(),
+            });
+
+            if (!response.ok) throw new Error(`HTTP ${response.status}`);
+
+            const data = await response.json();
+            const play_hist = data.data.easite_get_playerdata.music_hist.music;
+            const song_db = data.data.easite_get_playerdata.mdb;
+            let mirage = {
+                meta: {
+                    game: "dancearound",
+                    playtype: "Single",
+                    service: "e-amusement PLAY HISTORY",
+                },
+            };
+            const remappedList = play_hist.map((entry) => {
+                const diff = getDifficulty(entry.music_type, song_db[entry.music_id].difficulty)
+                const numPlayers = (entry.p1 && entry.p2) ? 2 : 1;
+                return {
+                    title: song_db[entry.music_id].title_name,
+                    artist: song_db[entry.music_id].artist_name,
+                    diff_lamp: diff.lamp,
+                    num_players: numPlayers,
+                    score: entry.score,
+                    lamp: getLampText(entry.rank),
+                    clear_status: getClearStatusText(entry.clear_status),
+                    difficulty: diff.difficulty,
+                    timestamp: entry.play_date,
+                    judgements: {
+                        "perfect": entry.perfect,
+                        "great": entry.great,
+                        "good": entry.good,
+                        "bad": entry.bad
+                    },
+                    optional: {
+                        maxCombo: entry.combo,
+                    }
+
+                };
+            });
+            mirage.scores = remappedList;
+
+            const blob = new Blob([JSON.stringify(mirage, null, 2)], {
+                type: "application/json",
+            });
+
+            const a = document.createElement("a");
+            a.href = URL.createObjectURL(blob);
+            a.download = "dancearound_scores_mirage_import.json";
+            a.click();
+        } catch (err) {
+            console.error("Fetch/download error:", err);
+            alert("Failed to fetch or process JSON. See console for details.");
+        }
+    }
+
+    waitFor("#id_ctpl_body")
+        .then((container) => {
+        const btn = document.createElement("button");
+        btn.textContent = "📥 DOWNLOAD PLAY HISTORY SCORE JSON";
+        btn.style.cssText = `
+      margin: 10px; padding: 8px 12px;
+      font-size: 14px; cursor: pointer;
+      background: #2563eb; color: white;
+      border: none; border-radius: 6px;
+      z-index: 9999;
+    `;
+        btn.onclick = fetchAndDownload;
+
+        container.prepend(btn);
+    })
+        .catch((err) => console.warn("Could not inject button:", err));
+})();
diff --git a/scripts/dancerush/dancerush_play_history.js b/scripts/dancerush/dancerush_play_history.js
index 5cc338f..098f038 100644
--- a/scripts/dancerush/dancerush_play_history.js
+++ b/scripts/dancerush/dancerush_play_history.js
@@ -89,7 +89,7 @@
             let mirage = {
                 meta: {
                     game: "DANCERUSH STARDOM",
-                    playtype: "Singl2e",
+                    playtype: "Single",
                     service: "e-amusement PLAY HISTORY",
                 },
             };