6 files changed, 97 insertions, 11 deletions

diff --git a/backend/api/routes.go b/backend/api/routes.go
index 339dc73..9e703f6 100644
--- a/backend/api/routes.go
+++ b/backend/api/routes.go
@@ -21,6 +21,7 @@ const (
         mapImagePath        string = "/maps/:mapid/image"
         mapLeaderboardsPath string = "/maps/:mapid/leaderboards"
         mapRecordPath       string = "/maps/:mapid/record"
+        mapRecordIDPath     string = "/maps/:mapid/record/:recordid"
         mapDiscussionsPath  string = "/maps/:mapid/discussions"
         mapDiscussionIDPath string = "/maps/:mapid/discussions/:discussionid"
         rankingsPath        string = "/rankings"
@@ -51,14 +52,18 @@ func InitRoutes(router *gin.Engine) {
                 v1.POST(profilePath, CheckAuth, handlers.UpdateUser)
                 v1.GET(usersPath, CheckAuth, handlers.FetchUser)
                 // Maps
+                // - Summary
                 v1.GET(mapSummaryPath, handlers.FetchMapSummary)
                 v1.POST(mapSummaryPath, CheckAuth, handlers.CreateMapSummary)
                 v1.PUT(mapSummaryPath, CheckAuth, handlers.EditMapSummary)
                 v1.DELETE(mapSummaryPath, CheckAuth, handlers.DeleteMapSummary)
                 v1.PUT(mapImagePath, CheckAuth, handlers.EditMapImage)
+                // - Leaderboards
                 v1.GET(mapLeaderboardsPath, handlers.FetchMapLeaderboards)
                 v1.POST(mapRecordPath, CheckAuth, handlers.CreateRecordWithDemo)
+                v1.DELETE(mapRecordIDPath, CheckAuth, handlers.DeleteRecord)
                 v1.GET(demosPath, handlers.DownloadDemoWithID)
+                // - Discussions
                 v1.GET(mapDiscussionsPath, handlers.FetchMapDiscussions)
                 v1.GET(mapDiscussionIDPath, handlers.FetchMapDiscussion)
                 v1.POST(mapDiscussionsPath, CheckAuth, handlers.CreateMapDiscussion)
diff --git a/backend/database/init.sql b/backend/database/init.sql
index 0f8196b..9d7b68c 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -130,6 +130,7 @@ CREATE TABLE records_sp (
   score_time INTEGER NOT NULL,
   demo_id UUID NOT NULL,
   record_date TIMESTAMP NOT NULL DEFAULT now(),
+  is_deleted BOOLEAN NOT NULL DEFAULT false,
   PRIMARY KEY (id),
   FOREIGN KEY (map_id) REFERENCES maps(id),
   FOREIGN KEY (user_id) REFERENCES users(steam_id),
@@ -146,6 +147,7 @@ CREATE TABLE records_mp (
   host_demo_id UUID NOT NULL,
   partner_demo_id UUID NOT NULL,
   record_date TIMESTAMP NOT NULL DEFAULT now(),
+  is_deleted BOOLEAN NOT NULL DEFAULT false,
   PRIMARY KEY (id),
   FOREIGN KEY (map_id) REFERENCES maps(id),
   FOREIGN KEY (host_id) REFERENCES users(steam_id),
diff --git a/backend/handlers/home.go b/backend/handlers/home.go
index c9742f2..5863218 100644
--- a/backend/handlers/home.go
+++ b/backend/handlers/home.go
@@ -49,11 +49,11 @@ func Rankings(c *gin.Context) {
                         SELECT
                         user_id,
                         MIN(score_count) AS min_score_count
-                        FROM records_sp
+                        FROM records_sp WHERE is_deleted = false
                         GROUP BY user_id, map_id
                         ) AS subquery
                 WHERE user_id = u.steam_id) 
-        FROM records_sp sp JOIN users u ON u.steam_id = sp.user_id GROUP BY u.steam_id, u.user_name`
+        FROM records_sp sp JOIN users u ON u.steam_id = sp.user_id WHERE is_deleted = false GROUP BY u.steam_id, u.user_name`
         rows, err := database.DB.Query(sql)
         if err != nil {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
@@ -81,11 +81,11 @@ func Rankings(c *gin.Context) {
                         host_id,
                         partner_id,
                         MIN(score_count) AS min_score_count
-                        FROM records_mp
+                        FROM records_mp WHERE is_deleted = false
                         GROUP BY host_id, partner_id, map_id
                         ) AS subquery
                 WHERE host_id = u.steam_id OR partner_id = u.steam_id) 
-        FROM records_mp mp JOIN users u ON u.steam_id = mp.host_id OR u.steam_id = mp.partner_id GROUP BY u.steam_id, u.user_name`
+        FROM records_mp mp JOIN users u ON u.steam_id = mp.host_id OR u.steam_id = mp.partner_id WHERE is_deleted = false GROUP BY u.steam_id, u.user_name`
         rows, err = database.DB.Query(sql)
         if err != nil {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
diff --git a/backend/handlers/logs.go b/backend/handlers/logs.go
index 6f59238..4c7b415 100644
--- a/backend/handlers/logs.go
+++ b/backend/handlers/logs.go
@@ -140,17 +140,17 @@ func ScoreLogs(c *gin.Context) {
                 rs.record_date
         FROM (
                 SELECT id, map_id, user_id, score_count, score_time, demo_id, record_date
-                FROM records_sp
+                FROM records_sp WHERE is_deleted = false
 
                 UNION ALL
 
                 SELECT id, map_id, host_id AS user_id, score_count, score_time, host_demo_id AS demo_id, record_date
-                FROM records_mp
+                FROM records_mp WHERE is_deleted = false
 
                 UNION ALL
 
                 SELECT id, map_id, partner_id AS user_id, score_count, score_time, partner_demo_id AS demo_id, record_date
-                FROM records_mp
+                FROM records_mp WHERE is_deleted = false
         ) AS rs
         JOIN users u ON rs.user_id = u.steam_id
         JOIN maps m ON rs.map_id = m.id
diff --git a/backend/handlers/map.go b/backend/handlers/map.go
index 3bf14cd..faccee4 100644
--- a/backend/handlers/map.go
+++ b/backend/handlers/map.go
@@ -104,7 +104,7 @@ func FetchMapSummary(c *gin.Context) {
                 if response.Map.IsCoop {
                         sql = `SELECT count(*) FROM ( SELECT host_id, partner_id, score_count, score_time,
                                 ROW_NUMBER() OVER (PARTITION BY host_id, partner_id ORDER BY score_count, score_time) AS rn
-                                FROM records_mp WHERE map_id = $1
+                                FROM records_mp WHERE map_id = $1 AND is_deleted = false
                                 ) sub WHERE sub.rn = 1 AND score_count = $2`
                         err = database.DB.QueryRow(sql, response.Map.ID, route.History.ScoreCount).Scan(&route.CompletionCount)
                         if err != nil {
@@ -114,7 +114,7 @@ func FetchMapSummary(c *gin.Context) {
                 } else {
                         sql = `SELECT count(*) FROM ( SELECT user_id, score_count, score_time, 
                                 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY score_count, score_time) AS rn
-                                FROM records_sp WHERE map_id = $1
+                                FROM records_sp WHERE map_id = $1 AND is_deleted = false
                                 ) sub WHERE rn = 1 AND score_count = $2`
                         err = database.DB.QueryRow(sql, response.Map.ID, route.History.ScoreCount).Scan(&route.CompletionCount)
                         if err != nil {
@@ -204,7 +204,7 @@ func FetchMapLeaderboards(c *gin.Context) {
                         record_date,
                         ROW_NUMBER() OVER (PARTITION BY host_id, partner_id ORDER BY score_count, score_time) AS rn
                 FROM records_mp
-                WHERE map_id = $1
+                WHERE map_id = $1 AND is_deleted = false
         ) sub
         JOIN users AS host ON sub.host_id = host.steam_id 
         JOIN users AS partner ON sub.partner_id = partner.steam_id 
@@ -255,7 +255,7 @@ func FetchMapLeaderboards(c *gin.Context) {
                   SELECT id, user_id, score_count, score_time, demo_id, record_date,
                                  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY score_count, score_time) AS rn
                   FROM records_sp
-                  WHERE map_id = $1
+                  WHERE map_id = $1 AND is_deleted = false
                 ) sub
                 INNER JOIN users ON user_id = users.steam_id
                 WHERE rn = 1
diff --git a/backend/handlers/record.go b/backend/handlers/record.go
index 4787422..70095bf 100644
--- a/backend/handlers/record.go
+++ b/backend/handlers/record.go
@@ -8,6 +8,7 @@ import (
         "mime/multipart"
         "net/http"
         "os"
+        "strconv"
 
         "github.com/gin-gonic/gin"
         "github.com/google/uuid"
@@ -209,6 +210,84 @@ func CreateRecordWithDemo(c *gin.Context) {
         })
 }
 
+func DeleteRecord(c *gin.Context) {
+        mapID, err := strconv.Atoi(c.Param("mapid"))
+        if err != nil {
+                c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
+                return
+        }
+        recordID, err := strconv.Atoi(c.Param("recordid"))
+        if err != nil {
+                c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
+                return
+        }
+        user, exists := c.Get("user")
+        if !exists {
+                c.JSON(http.StatusOK, models.ErrorResponse("User not logged in."))
+                return
+        }
+        // Validate map
+        var validateMapID int
+        var isCoop bool
+        sql := `SELECT m.id, g.is_coop FROM maps m INNER JOIN games g ON m.game_id = g.id
+        INNER JOIN chapters c ON m.chapter_id = c.id WHERE m.id = $1`
+        err = database.DB.QueryRow(sql, mapID).Scan(&validateMapID, &isCoop)
+        if err != nil {
+                c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
+                return
+        }
+        if mapID != validateMapID {
+                c.JSON(http.StatusOK, models.ErrorResponse("Selected map does not exist."))
+                return
+        }
+        if isCoop {
+                // Validate if cooperative record does exist
+                var validateRecordID int
+                sql = `SELECT mp.id FROM records_mp mp WHERE mp.id = $1 AND mp.map_id = $2 AND (mp.host_id = $3 OR mp.partner_id = $3) AND is_deleted = false`
+                err = database.DB.QueryRow(sql, recordID, mapID, user.(models.User).SteamID).Scan(&validateRecordID)
+                if err != nil {
+                        c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
+                        return
+                }
+                if recordID != validateRecordID {
+                        c.JSON(http.StatusOK, models.ErrorResponse("Selected record does not exist."))
+                        return
+                }
+                // Remove record
+                sql = `UPDATE records_mp SET is_deleted = true WHERE id = $1`
+                _, err = database.DB.Exec(sql, recordID)
+                if err != nil {
+                        c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
+                        return
+                }
+        } else {
+                // Validate if singleplayer record does exist
+                var validateRecordID int
+                sql = `SELECT sp.id FROM records_sp sp WHERE sp.id = $1 AND sp.map_id = $2 AND sp.user_id = $3 AND is_deleted = false`
+                err = database.DB.QueryRow(sql, recordID, mapID, user.(models.User).SteamID).Scan(&validateRecordID)
+                if err != nil {
+                        c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
+                        return
+                }
+                if recordID != validateRecordID {
+                        c.JSON(http.StatusOK, models.ErrorResponse("Selected record does not exist."))
+                        return
+                }
+                // Remove record
+                sql = `UPDATE records_sp SET is_deleted = true WHERE id = $1`
+                _, err = database.DB.Exec(sql, recordID)
+                if err != nil {
+                        c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
+                        return
+                }
+        }
+        c.JSON(http.StatusOK, models.Response{
+                Success: true,
+                Message: "Successfully deleted record.",
+                Data:    nil,
+        })
+}
+
 // GET Demo
 //
 //      @Description    Get demo with specified demo uuid.