backend: fix run ranks

4 files changed, 42 insertions, 29 deletions

diff --git a/backend/go.mod b/backend/go.mod
index f6eef48..ae50685 100644
--- a/backend/go.mod
+++ b/backend/go.mod
@@ -8,6 +8,7 @@ require (
 )
 
 require (
+        github.com/gin-contrib/cors v1.7.2
         github.com/golang-jwt/jwt/v4 v4.5.0
         github.com/google/uuid v1.6.0
         github.com/pektezol/steam_go v1.1.2
diff --git a/backend/go.sum b/backend/go.sum
index 10504e4..f117b31 100644
--- a/backend/go.sum
+++ b/backend/go.sum
@@ -31,6 +31,8 @@ github.com/felixge/httpsnoop v1.0.4 h1:NFTV2Zj1bL4mc9sqWACXbQFVBBg2W3GPvqp8/ESS2
 github.com/felixge/httpsnoop v1.0.4/go.mod h1:m8KPJKqk1gH5J9DgRY2ASl2lWCfGKXixSwevea8zH2U=
 github.com/gabriel-vasile/mimetype v1.4.5 h1:J7wGKdGu33ocBOhGy0z653k/lFKLFDPJMG8Gql0kxn4=
 github.com/gabriel-vasile/mimetype v1.4.5/go.mod h1:ibHel+/kbxn9x2407k1izTA1S81ku1z/DlgOW2QE0M4=
+github.com/gin-contrib/cors v1.7.2 h1:oLDHxdg8W/XDoN/8zamqk/Drgt4oVZDvaV0YmvVICQw=
+github.com/gin-contrib/cors v1.7.2/go.mod h1:SUJVARKgQ40dmrzgXEVxj2m7Ig1v1qIboQkPDTQ9t2E=
 github.com/gin-contrib/gzip v0.0.6 h1:NjcunTcGAj5CO1gn4N8jHOSIeRFHIbn51z6K+xaN4d4=
 github.com/gin-contrib/gzip v0.0.6/go.mod h1:QOJlmV2xmayAjkNS2Y8NQsMneuRShOU/kjovCXNuzzk=
 github.com/gin-contrib/sse v0.1.0 h1:Y/yl/+YNO8GZSjAhjMsSuLt29uWRFHdHYUb5lYOV9qE=
diff --git a/backend/handlers/user.go b/backend/handlers/user.go
index 908063f..17a7819 100644
--- a/backend/handlers/user.go
+++ b/backend/handlers/user.go
@@ -301,10 +301,12 @@ func Profile(c *gin.Context) {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
                 return
         }
-        sql = `WITH best_scores AS (SELECT sp.user_id, sp.map_id, MIN(sp.score_count) AS best_score_count, MIN(sp.score_time) AS best_score_time
-        FROM records_sp sp WHERE sp.is_deleted = false GROUP BY sp.user_id, sp.map_id)
-        SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count AND inner_scores.best_score_time < bs.best_score_time))) AS placement
-        FROM best_scores AS bs WHERE bs.user_id = $1 ORDER BY map_id, placement`
+        sql = `WITH best_scores AS (WITH RankedScores AS (SELECT sp.user_id, sp.map_id, sp.score_count, sp.score_time, ROW_NUMBER() OVER (
+    PARTITION BY sp.user_id, sp.map_id ORDER BY sp.score_count ASC, sp.score_time ASC) AS rank FROM records_sp sp WHERE sp.is_deleted = false)
+        SELECT user_id, map_id, score_count AS best_score_count, score_time AS best_score_time FROM RankedScores WHERE rank = 1)
+        SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id 
+        AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count 
+        AND inner_scores.best_score_time < bs.best_score_time))) AS placement FROM best_scores AS bs WHERE bs.user_id = $1 ORDER BY map_id, placement`
         placementsRows, err := database.DB.Query(sql, user.(models.User).SteamID)
         if err != nil {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
@@ -355,10 +357,12 @@ func Profile(c *gin.Context) {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
                 return
         }
-        sql = `WITH best_scores AS (SELECT mp.host_id, mp.partner_id, mp.map_id, MIN(mp.score_count) AS best_score_count, MIN(mp.score_time) AS best_score_time
-        FROM records_mp mp WHERE mp.is_deleted = false GROUP BY mp.host_id, mp.partner_id, mp.map_id)
-        SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count AND inner_scores.best_score_time < bs.best_score_time))) AS placement
-        FROM best_scores AS bs WHERE bs.host_id = $1 or bs.partner_id = $1 ORDER BY map_id, placement`
+        sql = `WITH best_scores AS (WITH RankedScores AS (SELECT mp.host_id, mp.partner_id, mp.map_id, mp.score_count, mp.score_time, ROW_NUMBER() OVER (
+        PARTITION BY mp.host_id, mp.partner_id, mp.map_id ORDER BY mp.score_count ASC, mp.score_time ASC) AS rank FROM records_mp mp WHERE mp.is_deleted = false)
+        SELECT host_id, partner_id, map_id, score_count AS best_score_count, score_time AS best_score_time FROM RankedScores WHERE rank = 1)
+        SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id 
+        AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count 
+        AND inner_scores.best_score_time < bs.best_score_time))) AS placement FROM best_scores AS bs WHERE bs.host_id = $1 or bs.partner_id = $1 ORDER BY map_id, placement`
         placementsRows, err = database.DB.Query(sql, user.(models.User).SteamID)
         if err != nil {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
@@ -675,10 +679,12 @@ func FetchUser(c *gin.Context) {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
                 return
         }
-        sql = `WITH best_scores AS (SELECT sp.user_id, sp.map_id, MIN(sp.score_count) AS best_score_count, MIN(sp.score_time) AS best_score_time
-        FROM records_sp sp WHERE sp.is_deleted = false GROUP BY sp.user_id, sp.map_id)
-        SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count AND inner_scores.best_score_time < bs.best_score_time))) AS placement
-        FROM best_scores AS bs WHERE bs.user_id = $1 ORDER BY map_id, placement`
+        sql = `WITH best_scores AS (WITH RankedScores AS (SELECT sp.user_id, sp.map_id, sp.score_count, sp.score_time, ROW_NUMBER() OVER (
+        PARTITION BY sp.user_id, sp.map_id ORDER BY sp.score_count ASC, sp.score_time ASC) AS rank FROM records_sp sp WHERE sp.is_deleted = false)
+        SELECT user_id, map_id, score_count AS best_score_count, score_time AS best_score_time FROM RankedScores WHERE rank = 1)
+        SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id 
+        AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count 
+        AND inner_scores.best_score_time < bs.best_score_time))) AS placement FROM best_scores AS bs WHERE bs.user_id = $1 ORDER BY map_id, placement`
         placementsRows, err := database.DB.Query(sql, user.SteamID)
         if err != nil {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
@@ -729,10 +735,12 @@ func FetchUser(c *gin.Context) {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
                 return
         }
-        sql = `WITH best_scores AS (SELECT mp.host_id, mp.partner_id, mp.map_id, MIN(mp.score_count) AS best_score_count, MIN(mp.score_time) AS best_score_time
-        FROM records_mp mp WHERE mp.is_deleted = false GROUP BY mp.host_id, mp.partner_id, mp.map_id)
-        SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count AND inner_scores.best_score_time < bs.best_score_time))) AS placement
-        FROM best_scores AS bs WHERE bs.host_id = $1 or bs.partner_id = $1 ORDER BY map_id, placement`
+        sql = `WITH best_scores AS (WITH RankedScores AS (SELECT mp.host_id, mp.partner_id, mp.map_id, mp.score_count, mp.score_time, ROW_NUMBER() OVER (
+        PARTITION BY mp.host_id, mp.partner_id, mp.map_id ORDER BY mp.score_count ASC, mp.score_time ASC) AS rank FROM records_mp mp WHERE mp.is_deleted = false)
+        SELECT host_id, partner_id, map_id, score_count AS best_score_count, score_time AS best_score_time FROM RankedScores WHERE rank = 1)
+        SELECT (SELECT COUNT(*) + 1 FROM best_scores AS inner_scores WHERE inner_scores.map_id = bs.map_id 
+        AND (inner_scores.best_score_count < bs.best_score_count OR (inner_scores.best_score_count = bs.best_score_count 
+        AND inner_scores.best_score_time < bs.best_score_time))) AS placement FROM best_scores AS bs WHERE bs.host_id = $1 or bs.partner_id = $1 ORDER BY map_id, placement`
         placementsRows, err = database.DB.Query(sql, user.SteamID)
         if err != nil {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
diff --git a/backend/main.go b/backend/main.go
index a50be7e..6499b51 100644
--- a/backend/main.go
+++ b/backend/main.go
@@ -4,11 +4,13 @@ import (
         "fmt"
         "log"
         "os"
+        "time"
 
         "lphub/api"
         "lphub/database"
         _ "lphub/docs"
 
+        "github.com/gin-contrib/cors"
         "github.com/gin-gonic/gin"
         "github.com/joho/godotenv"
 )
@@ -34,19 +36,19 @@ func main() {
         database.ConnectDB()
         api.InitRoutes(router)
         // for debugging
-        // router.Use(cors.New(cors.Config{
-        //      AllowOrigins:     []string{"*"},
-        //      AllowMethods:     []string{"GET", "POST", "DELETE", "PUT", "PATCH"},
-        //      AllowHeaders:     []string{"Origin"},
-        //      ExposeHeaders:    []string{"Content-Length"},
-        //      AllowCredentials: true,
-        //      MaxAge: 12 * time.Hour,
-        // }))
-        // router.Static("/static", "../frontend/build/static")
-        // router.StaticFile("/", "../frontend/build/index.html")
-        // router.NoRoute(func(c *gin.Context) {
-        //      c.File("../frontend/build/index.html")
-        // })
+        router.Use(cors.New(cors.Config{
+                AllowOrigins:     []string{"*"},
+                AllowMethods:     []string{"GET", "POST", "DELETE", "PUT", "PATCH"},
+                AllowHeaders:     []string{"Origin"},
+                ExposeHeaders:    []string{"Content-Length"},
+                AllowCredentials: true,
+                MaxAge:           12 * time.Hour,
+        }))
+        router.Static("/static", "../frontend/build/static")
+        router.StaticFile("/", "../frontend/build/index.html")
+        router.NoRoute(func(c *gin.Context) {
+                c.File("../frontend/build/index.html")
+        })
         router.MaxMultipartMemory = 200 << 20 // 200 mb limit for demos
         router.Run(fmt.Sprintf(":%s", os.Getenv("PORT")))
 }