feat: update map tables db schema (#157)

1 files changed, 14 insertions, 72 deletions

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.",