feat: update map tables db schema (#157)

5 files changed, 33 insertions, 386 deletions

diff --git a/backend/database/init.sql b/backend/database/init.sql
index 1fda15d..c33821b 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -48,25 +48,14 @@ CREATE TABLE maps (
   FOREIGN KEY (chapter_id) REFERENCES chapters(id)
 );
 
-CREATE TABLE map_routes (
-  id SERIAL,
-  map_id SMALLINT NOT NULL,
-  category_id SMALLINT NOT NULL,
-  score_count SMALLINT NOT NULL,
-  description TEXT NOT NULL,
-  showcase TEXT NOT NULL DEFAULT,
-  PRIMARY KEY (id),
-  FOREIGN KEY (map_id) REFERENCES maps(id),
-  FOREIGN KEY (category_id) REFERENCES categories(id),
-  UNIQUE (map_id, category_id, score_count)
-);
-
 CREATE TABLE map_history (
   id SERIAL,
   map_id SMALLINT NOT NULL,
   category_id SMALLINT NOT NULL,
   user_name TEXT NOT NULL,
   score_count SMALLINT NOT NULL,
+  description TEXT NOT NULL,
+  showcase TEXT NOT NULL DEFAULT,
   record_date DATE NOT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (category_id) REFERENCES categories(id),
diff --git a/backend/database/route.sql b/backend/database/route.sql
deleted file mode 100644
index 8b23a8c..0000000
--- a/backend/database/route.sql
+++ /dev/null
@@ -1,283 +0,0 @@
-INSERT INTO map_routes(map_id,category_id,score_count,description,showcase) VALUES
--- Portal 2 Singleplayer
--- 1
-(3,1,3,'',''),
-(3,1,1,'',''),
-(3,1,0,'',''),
-(4,1,1,'',''),
-(4,1,0,'',''),
-(5,1,2,'',''),
-(5,1,1,'',''),
-(6,1,4,'',''),
-(6,1,3,'',''),
-(6,1,2,'',''),
-(7,1,0,'',''),
-(9,1,4,'',''),
-(9,1,3,'',''),
-(9,1,2,'',''),
-(9,1,0,'',''),
--- 2
-(10,1,2,'',''),
-(10,1,0,'',''),
-(11,1,2,'',''),
-(11,1,0,'',''),
-(12,1,2,'',''),
-(13,1,3,'',''),
-(13,1,2,'',''),
-(13,1,0,'',''),
-(14,1,0,'',''),
-(15,1,2,'',''),
-(16,1,2,'',''),
-(16,1,0,'',''),
-(17,1,2,'',''),
-(17,1,0,'',''),
--- 3
-(18,1,5,'',''),
-(18,1,4,'',''),
-(18,1,3,'',''),
-(18,1,2,'',''),
-(18,1,0,'',''),
-(19,1,2,'',''),
-(20,1,5,'',''),
-(20,1,4,'',''),
-(20,1,3,'',''),
-(20,1,2,'',''),
-(21,1,4,'',''),
-(21,1,2,'',''),
-(21,1,0,'',''),
-(22,1,0,'',''),
-(23,1,2,'',''),
-(23,1,0,'',''),
-(24,1,0,'',''),
-(25,1,0,'',''),
-(26,1,3,'',''),
-(26,1,2,'',''),
-(26,1,0,'',''),
--- 4
-(27,1,2,'',''),
-(27,1,0,'',''),
-(28,1,7,'',''),
-(28,1,2,'',''),
-(28,1,0,'',''),
-(29,1,0,'',''),
-(30,1,2,'',''),
-(31,1,0,'',''),
--- 5
-(32,1,6,'',''),
-(32,1,5,'',''),
-(33,1,7,'',''),
-(33,1,5,'',''),
-(33,1,4,'',''),
-(34,1,3,'',''),
-(34,1,2,'',''),
-(34,1,0,'',''),
-(35,1,2,'',''),
--- 6
-(36,1,6,'',''),
-(36,1,5,'',''),
-(36,1,4,'',''),
-(36,1,2,'',''),
-(37,1,7,'',''),
-(37,1,6,'',''),
-(37,1,5,'',''),
-(37,1,4,'',''),
-(38,1,2,'',''),
-(38,1,0,'',''),
-(39,1,6,'',''),
-(39,1,5,'',''),
-(39,1,4,'',''),
-(39,1,3,'',''),
-(40,1,7,'',''),
-(40,1,6,'',''),
-(40,1,4,'',''),
-(40,1,3,'',''),
-(40,1,2,'',''),
-(40,1,0,'',''),
-(41,1,7,'',''),
-(41,1,6,'',''),
-(41,1,5,'',''),
--- 7
-(42,1,4,'',''),
-(42,1,2,'',''),
-(43,1,5,'',''),
-(43,1,2,'',''),
-(43,1,0,'',''),
-(44,1,18,'',''),
-(44,1,13,'',''),
-(44,1,12,'',''),
-(44,1,11,'',''),
-(44,1,9,'',''),
-(45,1,23,'',''),
-(45,1,22,'',''),
-(45,1,17,'',''),
-(45,1,16,'',''),
-(45,1,15,'',''),
-(45,1,12,'',''),
-(45,1,10,'',''),
-(45,1,7,'',''),
-(45,1,4,'',''),
--- 8
-(46,1,6,'',''),
-(46,1,2,'',''),
-(47,1,2,'',''),
-(47,1,0,'',''),
-(48,1,5,'',''),
-(48,1,2,'',''),
-(48,1,0,'',''),
-(49,1,4,'',''),
-(49,1,2,'',''),
-(49,1,0,'',''),
-(50,1,4,'',''),
-(50,1,2,'',''),
-(50,1,0,'',''),
-(51,1,3,'',''),
-(51,1,2,'',''),
-(52,1,0,'',''),
-(53,1,9,'',''),
-(53,1,2,'',''),
-(53,1,0,'',''),
-(54,1,7,'',''),
-(54,1,6,'',''),
-(54,1,5,'',''),
-(54,1,4,'',''),
-(54,1,3,'',''),
-(55,1,7,'',''),
-(55,1,3,'',''),
-(55,1,2,'',''),
-(55,1,0,'',''),
-(56,1,9,'',''),
-(56,1,5,'',''),
-(56,1,4,'',''),
-(56,1,2,'',''),
--- 9
-(57,1,7,'',''),
-(57,1,5,'',''),
-(57,1,0,'',''),
-(58,1,2,'',''),
-(59,1,7,'',''),
-(59,1,6,'',''),
-(60,1,7,'',''),
-(60,1,6,'',''),
--- Portal 2 Cooperative
--- 1
-(63,1,0,'',''),
-(64,1,3,'',''),
-(64,1,2,'',''),
-(65,1,4,'',''),
-(65,1,3,'',''),
-(65,1,2,'',''),
-(66,1,3,'',''),
-(66,1,2,'',''),
-(67,1,0,'',''),
-(68,1,0,'',''),
--- 2
-(69,1,4,'',''),
-(70,1,6,'',''),
-(70,1,4,'',''),
-(70,1,2,'',''),
-(70,1,0,'',''),
-(71,1,3,'',''),
-(71,1,0,'',''),
-(72,1,4,'',''),
-(72,1,2,'',''),
-(73,1,9,'',''),
-(73,1,8,'',''),
-(73,1,7,'',''),
-(73,1,6,'',''),
-(73,1,4,'',''),
-(74,1,5,'',''),
-(74,1,7,'',''),
-(74,1,3,'',''),
-(74,1,2,'',''),
-(75,1,5,'',''),
-(75,1,4,'',''),
-(75,1,2,'',''),
-(75,1,0,'',''),
-(76,1,3,'',''),
-(76,1,0,'',''),
--- 3
-(77,1,3,'',''),
-(78,1,4,'',''),
-(78,1,3,'',''),
-(78,1,2,'',''),
-(79,1,5,'',''),
-(79,1,4,'',''),
-(79,1,2,'',''),
-(79,1,0,'',''),
-(80,1,5,'',''),
-(80,1,4,'',''),
-(81,1,7,'',''),
-(81,1,6,'',''),
-(81,1,5,'',''),
-(81,1,4,'',''),
-(82,1,4,'',''),
-(83,1,5,'',''),
-(83,1,2,'',''),
-(83,1,0,'',''),
-(84,1,6,'',''),
-(84,1,4,'',''),
-(84,1,2,'',''),
-(84,1,0,'',''),
--- 4
-(85,1,3,'',''),
-(85,1,0,'',''),
-(86,1,3,'',''),
-(86,1,0,'',''),
-(87,1,3,'',''),
-(87,1,2,'',''),
-(87,1,0,'',''),
-(88,1,4,'',''),
-(88,1,0,'',''),
-(89,1,0,'',''),
-(90,1,4,'',''),
-(90,1,2,'',''),
-(90,1,0,'',''),
-(91,1,2,'',''),
-(91,1,0,'',''),
-(92,1,5,'',''),
-(92,1,4,'',''),
-(92,1,2,'',''),
-(92,1,0,'',''),
-(93,1,2,'',''),
-(93,1,0,'',''),
--- 5
-(94,1,2,'',''),
-(94,1,0,'',''),
-(95,1,2,'',''),
-(96,1,4,'',''),
-(96,1,2,'',''),
-(97,1,7,'',''),
-(97,1,4,'',''),
-(97,1,2,'',''),
-(98,1,0,'',''),
-(99,1,3,'',''),
-(99,1,2,'',''),
-(99,1,0,'',''),
-(100,1,0,'',''),
-(101,1,2,'',''),
-(101,1,0,'',''),
--- 6
-(102,1,3,'',''),
-(102,1,2,'',''),
-(102,1,0,'',''),
-(103,1,0,'',''),
-(104,1,0,'',''),
-(105,1,8,'',''),
-(105,1,5,'',''),
-(105,1,4,'',''),
-(105,1,3,'',''),
-(105,1,2,'',''),
-(106,1,4,'',''),
-(106,1,3,'',''),
-(106,1,0,'',''),
-(107,1,2,'',''),
-(107,1,0,'',''),
-(108,1,0,'',''),
-(109,1,5,'',''),
-(109,1,0,'',''),
-(110,1,15,'',''),
-(110,1,6,'',''),
-(110,1,5,'',''),
-(110,1,4,'',''),
-(110,1,2,'',''),
-(110,1,0,'','');
\ No newline at end of file
diff --git a/backend/handlers/map.go b/backend/handlers/map.go
index f2ea8ac..bf7c821 100644
--- a/backend/handlers/map.go
+++ b/backend/handlers/map.go
@@ -87,12 +87,11 @@ func FetchMapSummary(c *gin.Context) {
                 return
         }
         // Get map routes and histories
-        sql = `SELECT r.id, c.id, c.name, h.user_name, h.score_count, h.record_date, r.description, r.showcase, COALESCE(avg(rating), 0.0) FROM map_routes r
-    INNER JOIN categories c ON r.category_id = c.id
-    INNER JOIN map_history h ON r.map_id = h.map_id AND r.category_id = h.category_id
-    LEFT JOIN map_ratings rt ON r.map_id = rt.map_id AND r.category_id = rt.category_id 
-        WHERE r.map_id = $1 AND h.score_count = r.score_count GROUP BY r.id, c.id, h.user_name, h.score_count, h.record_date, r.description, r.showcase
-        ORDER BY h.record_date ASC;`
+        sql = `SELECT mh.id, c.id, c.name, mh.user_name, mh.score_count, mh.record_date, mh.description, mh.showcase, COALESCE(avg(rating), 0.0) FROM map_history mh
+    INNER JOIN categories c ON mh.category_id = c.id
+    LEFT JOIN map_ratings rt ON mh.map_id = rt.map_id AND mh.category_id = rt.category_id 
+        WHERE mh.map_id = $1 AND mh.score_count = mh.score_count GROUP BY mh.id, c.id, mh.user_name, mh.score_count, mh.record_date, mh.description, mh.showcase
+        ORDER BY mh.record_date ASC;`
         rows, err := database.DB.Query(sql, id)
         if err != nil {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
diff --git a/backend/handlers/mod.go b/backend/handlers/mod.go
index 3709e1d..72a9fd8 100644
--- a/backend/handlers/mod.go
+++ b/backend/handlers/mod.go
@@ -16,7 +16,7 @@ type CreateMapSummaryRequest struct {
         Description string    `json:"description" binding:"required"`
         Showcase    string    `json:"showcase"`
         UserName    string    `json:"user_name" binding:"required"`
-        ScoreCount  *int      `json:"score_count"`
+        ScoreCount  int       `json:"score_count" binding:"required"`
         RecordDate  time.Time `json:"record_date" binding:"required"`
 }
 
@@ -25,7 +25,7 @@ type EditMapSummaryRequest struct {
         Description string    `json:"description" binding:"required"`
         Showcase    string    `json:"showcase"`
         UserName    string    `json:"user_name" binding:"required"`
-        ScoreCount  int       `json:"score_count"`
+        ScoreCount  int       `json:"score_count" binding:"required"`
         RecordDate  time.Time `json:"record_date" binding:"required"`
 }
 
@@ -93,17 +93,9 @@ func CreateMapSummary(c *gin.Context) {
                 return
         }
         // Update database with new data
-        sql = `INSERT INTO map_routes (map_id,category_id,score_count,description,showcase)
+        sql = `INSERT INTO map_history (map_id,category_id,user_name,score_count,description,showcase,record_date)
         VALUES ($1,$2,$3,$4,$5)`
-        _, err = tx.Exec(sql, mapID, request.CategoryID, request.ScoreCount, request.Description, request.Showcase)
-        if err != nil {
-                CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryCreateFail, fmt.Sprintf("INSERT#map_routes: %s", err.Error()))
-                c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
-                return
-        }
-        sql = `INSERT INTO map_history (map_id,category_id,user_name,score_count,record_date)
-        VALUES ($1,$2,$3,$4,$5)`
-        _, err = tx.Exec(sql, mapID, request.CategoryID, request.UserName, request.ScoreCount, request.RecordDate)
+        _, err = tx.Exec(sql, mapID, request.CategoryID, request.UserName, request.ScoreCount, request.Description, request.Showcase, request.RecordDate)
         if err != nil {
                 CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryCreateFail, fmt.Sprintf("INSERT#map_history: %s", err.Error()))
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
@@ -113,7 +105,7 @@ func CreateMapSummary(c *gin.Context) {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
                 return
         }
-        CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryCreateSuccess, fmt.Sprintf("MapID: %d | CategoryID: %d | ScoreCount: %d", mapID, request.CategoryID, *request.ScoreCount))
+        CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryCreateSuccess, fmt.Sprintf("MapID: %d | CategoryID: %d | ScoreCount: %d", mapID, request.CategoryID, request.ScoreCount))
         c.JSON(http.StatusOK, models.Response{
                 Success: true,
                 Message: "Successfully created map summary.",
@@ -145,7 +137,8 @@ func EditMapSummary(c *gin.Context) {
         }
         // Bind parameter and body
         id := c.Param("mapid")
-        mapID, err := strconv.Atoi(id)
+        // we get mapid in path parameters, but it's not really used anywhere here lol.
+        _, err := strconv.Atoi(id)
         if err != nil {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
                 return
@@ -163,34 +156,11 @@ func EditMapSummary(c *gin.Context) {
                 return
         }
         defer tx.Rollback()
-        // Fetch route category and score count
-        var categoryID, scoreCount, historyID int
-        sql := `SELECT mr.category_id, mr.score_count FROM map_routes mr INNER JOIN maps m ON m.id = mr.map_id WHERE m.id = $1 AND mr.id = $2`
-        err = database.DB.QueryRow(sql, mapID, request.RouteID).Scan(&categoryID, &scoreCount)
-        if err != nil {
-                CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryEditFail, fmt.Sprintf("(RouteID: %d) SELECT#map_routes: %s", request.RouteID, err.Error()))
-                c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
-                return
-        }
-        sql = `SELECT mh.id FROM map_history mh WHERE mh.score_count = $1 AND mh.category_id = $2 AND mh.map_id = $3`
-        err = database.DB.QueryRow(sql, scoreCount, categoryID, mapID).Scan(&historyID)
-        if err != nil {
-                CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryEditFail, fmt.Sprintf("(RouteID: %d) SELECT#map_history: %s", request.RouteID, err.Error()))
-                c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
-                return
-        }
         // Update database with new data
-        sql = `UPDATE map_routes SET score_count = $2, description = $3, showcase = $4 WHERE id = $1`
-        _, err = tx.Exec(sql, request.RouteID, request.ScoreCount, request.Description, request.Showcase)
-        if err != nil {
-                CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryEditFail, fmt.Sprintf("(RouteID: %d) UPDATE#map_routes: %s", request.RouteID, err.Error()))
-                c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
-                return
-        }
-        sql = `UPDATE map_history SET user_name = $2, score_count = $3, record_date = $4 WHERE id = $1`
-        _, err = tx.Exec(sql, historyID, request.UserName, request.ScoreCount, request.RecordDate)
+        sql := `UPDATE map_history SET user_name = $2, score_count = $3, record_date = $4, description = $5, showcase = $6 WHERE id = $1`
+        _, err = tx.Exec(sql, request.RouteID, request.UserName, request.ScoreCount, request.RecordDate, request.Description, request.Showcase)
         if err != nil {
-                CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryEditFail, fmt.Sprintf("(HistoryID: %d) UPDATE#map_history: %s", historyID, err.Error()))
+                CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryEditFail, fmt.Sprintf("(HistoryID: %d) UPDATE#map_history: %s", request.RouteID, err.Error()))
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
                 return
         }
@@ -198,7 +168,6 @@ func EditMapSummary(c *gin.Context) {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
                 return
         }
-        CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryEditSuccess, fmt.Sprintf("MapID: %d | CategoryID: %d | ScoreCount: %d", mapID, categoryID, scoreCount))
         c.JSON(http.StatusOK, models.Response{
                 Success: true,
                 Message: "Successfully updated map summary.",
@@ -230,7 +199,8 @@ func DeleteMapSummary(c *gin.Context) {
         }
         // Bind parameter and body
         id := c.Param("mapid")
-        mapID, err := strconv.Atoi(id)
+        // we get mapid in path parameters, but it's not really used anywhere here lol.
+        _, err := strconv.Atoi(id)
         if err != nil {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
                 return
@@ -248,38 +218,11 @@ func DeleteMapSummary(c *gin.Context) {
                 return
         }
         defer tx.Rollback()
-        // Fetch route category and score count
-        var checkMapID, scoreCount, categoryID, mapHistoryID int
-        sql := `SELECT m.id, mr.score_count, mr.category_id FROM maps m INNER JOIN map_routes mr ON m.id=mr.map_id WHERE m.id = $1 AND mr.id = $2`
-        err = database.DB.QueryRow(sql, mapID, request.RouteID).Scan(&checkMapID, &scoreCount, &categoryID)
-        if err != nil {
-                CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryDeleteFail, fmt.Sprintf("(RouteID: %d) SELECT#map_routes: %s", request.RouteID, err.Error()))
-                c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
-                return
-        }
-        if mapID != checkMapID {
-                c.JSON(http.StatusOK, models.ErrorResponse("Map ID does not exist."))
-                return
-        }
-        sql = `SELECT mh.id FROM maps m INNER JOIN map_routes mr ON m.id=mr.map_id INNER JOIN map_history mh ON m.id=mh.map_id WHERE m.id = $1 AND mh.category_id = $2 AND mh.score_count = $3`
-        err = database.DB.QueryRow(sql, mapID, categoryID, scoreCount).Scan(&mapHistoryID)
-        if err != nil {
-                CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryDeleteFail, fmt.Sprintf("(RouteID: %d) SELECT#map_history: %s", request.RouteID, err.Error()))
-                c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
-                return
-        }
         // Update database with new data
-        sql = `DELETE FROM map_routes mr WHERE mr.id = $1 `
+        sql := `DELETE FROM map_history mh WHERE mh.id = $1`
         _, err = tx.Exec(sql, request.RouteID)
         if err != nil {
-                CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryDeleteFail, fmt.Sprintf("(RouteID: %d) DELETE#map_routes: %s", request.RouteID, err.Error()))
-                c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
-                return
-        }
-        sql = `DELETE FROM map_history mh WHERE mh.id = $1`
-        _, err = tx.Exec(sql, mapHistoryID)
-        if err != nil {
-                CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryDeleteFail, fmt.Sprintf("(HistoryID: %d) DELETE#map_history: %s", mapHistoryID, err.Error()))
+                CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryDeleteFail, fmt.Sprintf("(HistoryID: %d) DELETE#map_history: %s", request.RouteID, err.Error()))
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
                 return
         }
@@ -287,7 +230,6 @@ func DeleteMapSummary(c *gin.Context) {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
                 return
         }
-        CreateLog(user.(models.User).SteamID, LogTypeMod, LogDescriptionMapSummaryDeleteSuccess, fmt.Sprintf("MapID: %d | CategoryID: %d | ScoreCount: %d", mapID, categoryID, scoreCount))
         c.JSON(http.StatusOK, models.Response{
                 Success: true,
                 Message: "Successfully delete map summary.",
diff --git a/backend/handlers/user.go b/backend/handlers/user.go
index 88bbe45..8febf3a 100644
--- a/backend/handlers/user.go
+++ b/backend/handlers/user.go
@@ -99,15 +99,15 @@ func Profile(c *gin.Context) {
         // Get user completion count
         sql = `SELECT 'records_sp' AS table_name, COUNT(sp.id)
         FROM records_sp sp JOIN (
-        SELECT mr.map_id, MIN(mr.score_count) AS min_score_count
-        FROM public.map_routes mr WHERE mr.category_id = 1 GROUP BY mr.map_id
+        SELECT mh.map_id, MIN(mh.score_count) AS min_score_count
+        FROM public.map_history mh WHERE mh.category_id = 1 GROUP BY mh.map_id
         ) AS subquery_sp ON sp.map_id = subquery_sp.map_id AND sp.score_count = subquery_sp.min_score_count
         WHERE sp.user_id = $1 AND sp.is_deleted = false
         UNION ALL
         SELECT 'records_mp' AS table_name, COUNT(mp.id)
         FROM public.records_mp mp JOIN (
-        SELECT mr.map_id, MIN(mr.score_count) AS min_score_count
-        FROM public.map_routes mr WHERE mr.category_id = 1 GROUP BY mr.map_id
+        SELECT mh.map_id, MIN(mh.score_count) AS min_score_count
+        FROM public.map_history mh WHERE mh.category_id = 1 GROUP BY mh.map_id
         ) AS subquery_mp ON mp.map_id = subquery_mp.map_id AND mp.score_count = subquery_mp.min_score_count
         WHERE (mp.host_id = $1 OR mp.partner_id = $1) AND mp.is_deleted = false`
         rows, err := database.DB.Query(sql, user.(models.User).SteamID)
@@ -293,7 +293,7 @@ func Profile(c *gin.Context) {
         }
         records := []ProfileRecords{}
         // Get singleplayer records
-        sql = `SELECT sp.id, m.game_id, m.chapter_id, sp.map_id, m."name", (SELECT mr.score_count FROM map_routes mr WHERE mr.map_id = sp.map_id ORDER BY mr.score_count ASC LIMIT 1) AS wr_count, sp.score_count, sp.score_time, sp.demo_id, sp.record_date
+        sql = `SELECT sp.id, m.game_id, m.chapter_id, sp.map_id, m."name", (SELECT mh.score_count FROM map_history mh WHERE mh.map_id = sp.map_id ORDER BY mh.score_count ASC LIMIT 1) AS wr_count, sp.score_count, sp.score_time, sp.demo_id, sp.record_date
         FROM records_sp sp INNER JOIN maps m ON sp.map_id = m.id WHERE sp.user_id = $1 AND sp.is_deleted = false ORDER BY sp.map_id, sp.score_count, sp.score_time`
         rows, err = database.DB.Query(sql, user.(models.User).SteamID)
         if err != nil {
@@ -347,7 +347,7 @@ func Profile(c *gin.Context) {
                 records[len(records)-1].Scores = append(records[len(records)-1].Scores, score)
         }
         // Get multiplayer records
-        sql = `SELECT mp.id, m.game_id, m.chapter_id, mp.map_id, m."name", (SELECT mr.score_count FROM map_routes mr WHERE mr.map_id = mp.map_id ORDER BY mr.score_count ASC LIMIT 1) AS wr_count,  mp.score_count, mp.score_time, CASE WHEN host_id = $1 THEN mp.host_demo_id WHEN partner_id = $1 THEN mp.partner_demo_id END demo_id, mp.record_date
+        sql = `SELECT mp.id, m.game_id, m.chapter_id, mp.map_id, m."name", (SELECT mh.score_count FROM map_history mh WHERE mh.map_id = mp.map_id ORDER BY mh.score_count ASC LIMIT 1) AS wr_count,  mp.score_count, mp.score_time, CASE WHEN host_id = $1 THEN mp.host_demo_id WHEN partner_id = $1 THEN mp.partner_demo_id END demo_id, mp.record_date
         FROM records_mp mp INNER JOIN maps m ON mp.map_id = m.id WHERE (mp.host_id = $1 OR mp.partner_id = $1) AND mp.is_deleted = false ORDER BY mp.map_id, mp.score_count, mp.score_time`
         rows, err = database.DB.Query(sql, user.(models.User).SteamID)
         if err != nil {
@@ -473,15 +473,15 @@ func FetchUser(c *gin.Context) {
         // Get user completion count
         sql = `SELECT 'records_sp' AS table_name, COUNT(sp.id)
         FROM records_sp sp JOIN (
-        SELECT mr.map_id, MIN(mr.score_count) AS min_score_count
-        FROM public.map_routes mr WHERE mr.category_id = 1 GROUP BY mr.map_id
+        SELECT mh.map_id, MIN(mh.score_count) AS min_score_count
+        FROM public.map_history mh WHERE mh.category_id = 1 GROUP BY mh.map_id
         ) AS subquery_sp ON sp.map_id = subquery_sp.map_id AND sp.score_count = subquery_sp.min_score_count
         WHERE sp.user_id = $1 AND sp.is_deleted = false
         UNION ALL
         SELECT 'records_mp' AS table_name, COUNT(mp.id)
         FROM public.records_mp mp JOIN (
-        SELECT mr.map_id, MIN(mr.score_count) AS min_score_count
-        FROM public.map_routes mr WHERE mr.category_id = 1 GROUP BY mr.map_id
+        SELECT mh.map_id, MIN(mh.score_count) AS min_score_count
+        FROM public.map_history mh WHERE mh.category_id = 1 GROUP BY mh.map_id
         ) AS subquery_mp ON mp.map_id = subquery_mp.map_id AND mp.score_count = subquery_mp.min_score_count
         WHERE (mp.host_id = $1 OR mp.partner_id = $1) AND mp.is_deleted = false`
         rows, err = database.DB.Query(sql, user.SteamID)
@@ -667,7 +667,7 @@ func FetchUser(c *gin.Context) {
         }
         records := []ProfileRecords{}
         // Get singleplayer records
-        sql = `SELECT sp.id, m.game_id, m.chapter_id, sp.map_id, m."name", (SELECT mr.score_count FROM map_routes mr WHERE mr.map_id = sp.map_id ORDER BY mr.score_count ASC LIMIT 1) AS wr_count, sp.score_count, sp.score_time, sp.demo_id, sp.record_date
+        sql = `SELECT sp.id, m.game_id, m.chapter_id, sp.map_id, m."name", (SELECT mh.score_count FROM map_history mh WHERE mh.map_id = sp.map_id ORDER BY mh.score_count ASC LIMIT 1) AS wr_count, sp.score_count, sp.score_time, sp.demo_id, sp.record_date
         FROM records_sp sp INNER JOIN maps m ON sp.map_id = m.id WHERE sp.user_id = $1 AND sp.is_deleted = false ORDER BY sp.map_id, sp.score_count, sp.score_time`
         rows, err = database.DB.Query(sql, user.SteamID)
         if err != nil {
@@ -721,7 +721,7 @@ func FetchUser(c *gin.Context) {
                 records[len(records)-1].Scores = append(records[len(records)-1].Scores, score)
         }
         // Get multiplayer records
-        sql = `SELECT mp.id, m.game_id, m.chapter_id, mp.map_id, m."name", (SELECT mr.score_count FROM map_routes mr WHERE mr.map_id = mp.map_id ORDER BY mr.score_count ASC LIMIT 1) AS wr_count,  mp.score_count, mp.score_time, CASE WHEN host_id = $1 THEN mp.host_demo_id WHEN partner_id = $1 THEN mp.partner_demo_id END demo_id, mp.record_date
+        sql = `SELECT mp.id, m.game_id, m.chapter_id, mp.map_id, m."name", (SELECT mh.score_count FROM map_history mh WHERE mh.map_id = mp.map_id ORDER BY mh.score_count ASC LIMIT 1) AS wr_count,  mp.score_count, mp.score_time, CASE WHEN host_id = $1 THEN mp.host_demo_id WHEN partner_id = $1 THEN mp.partner_demo_id END demo_id, mp.record_date
         FROM records_mp mp INNER JOIN maps m ON mp.map_id = m.id WHERE (mp.host_id = $1 OR mp.partner_id = $1) AND mp.is_deleted = false ORDER BY mp.map_id, mp.score_count, mp.score_time`
         rows, err = database.DB.Query(sql, user.SteamID)
         if err != nil {