3 files changed, 92 insertions, 12 deletions

diff --git a/backend/database/history.sql b/backend/database/history.sql
index 96bde3d..cadb30c 100644
--- a/backend/database/history.sql
+++ b/backend/database/history.sql
@@ -25,6 +25,7 @@ INSERT INTO map_history(map_id,category_id,user_name,score_count,record_date) VA
 (13,1,'LookLikeAKango',3,'2011-10-05'),
 (13,1,'Imanex',2,'2011-12-08'),
 (13,1,'jyjey',0,'2012-08-22'),
+(14,0,'?',0,'2011-04-19'),
 (15,1,'Tyronis',2,'2011-10-05'),
 (16,1,'LookLikeAKango',2,'2011-10-05'),
 (16,1,'jyjey',0,'2012-08-25'),
diff --git a/backend/database/route.sql b/backend/database/route.sql
index 45941a5..8b23a8c 100644
--- a/backend/database/route.sql
+++ b/backend/database/route.sql
@@ -25,6 +25,7 @@ INSERT INTO map_routes(map_id,category_id,score_count,description,showcase) VALU
 (13,1,3,'',''),
 (13,1,2,'',''),
 (13,1,0,'',''),
+(14,1,0,'',''),
 (15,1,2,'',''),
 (16,1,2,'',''),
 (16,1,0,'',''),
diff --git a/backend/handlers/user.go b/backend/handlers/user.go
index 777d60c..6b090a0 100644
--- a/backend/handlers/user.go
+++ b/backend/handlers/user.go
@@ -40,10 +40,12 @@ type ProfileRecords struct {
         MapID      int             `json:"map_id"`
         MapName    string          `json:"map_name"`
         MapWRCount int             `json:"map_wr_count"`
+        Placement  int             `json:"placement"`
         Scores     []ProfileScores `json:"scores"`
 }
 
 type ProfileScores struct {
+        RecordID   int       `json:"record_id"`
         DemoID     string    `json:"demo_id"`
         ScoreCount int       `json:"score_count"`
         ScoreTime  int       `json:"score_time"`
@@ -290,13 +292,28 @@ func Profile(c *gin.Context) {
         }
         records := []ProfileRecords{}
         // Get singleplayer records
-        sql = `SELECT 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
-        FROM records_sp sp INNER JOIN maps m ON sp.map_id = m.id WHERE sp.user_id = $1 ORDER BY sp.map_id, sp.score_count, sp.score_time;`
+        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
+        FROM records_sp sp INNER JOIN maps m ON sp.map_id = m.id WHERE sp.user_id = $1 ORDER BY sp.map_id, sp.score_count, sp.score_time`
         rows, err = database.DB.Query(sql, user.(models.User).SteamID)
         if err != nil {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
                 return
         }
+        sql = `WITH map_records AS (SELECT sp.user_id, sp.map_id, 
+        RANK() OVER (PARTITION BY sp.map_id ORDER BY sp.score_count, sp.score_time) AS placement
+        FROM records_sp sp) SELECT DISTINCT ON (map_id) placement FROM map_records WHERE user_id = $1`
+        placementsRows, err := database.DB.Query(sql, user.(models.User).SteamID)
+        if err != nil {
+                c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
+                return
+        }
+        placements := []int{}
+        placementIndex := 0
+        for placementsRows.Next() {
+                var placement int
+                placementsRows.Scan(&placement)
+                placements = append(placements, placement)
+        }
         for rows.Next() {
                 var gameID int
                 var categoryID int
@@ -304,7 +321,11 @@ func Profile(c *gin.Context) {
                 var mapName string
                 var mapWR int
                 score := ProfileScores{}
-                rows.Scan(&gameID, &categoryID, &mapID, &mapName, &mapWR, &score.ScoreCount, &score.ScoreTime, &score.DemoID, &score.Date)
+                err = rows.Scan(&score.RecordID, &gameID, &categoryID, &mapID, &mapName, &mapWR, &score.ScoreCount, &score.ScoreTime, &score.DemoID, &score.Date)
+                if err != nil {
+                        c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
+                        return
+                }
                 // More than one record in one map
                 if len(records) != 0 && mapID == records[len(records)-1].MapID {
                         records[len(records)-1].Scores = append(records[len(records)-1].Scores, score)
@@ -317,18 +338,35 @@ func Profile(c *gin.Context) {
                         MapID:      mapID,
                         MapName:    mapName,
                         MapWRCount: mapWR,
+                        Placement:  placements[placementIndex],
                         Scores:     []ProfileScores{},
                 })
+                placementIndex++
                 records[len(records)-1].Scores = append(records[len(records)-1].Scores, score)
         }
         // Get multiplayer records
-        sql = `SELECT 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
-        FROM records_mp mp INNER JOIN maps m ON mp.map_id = m.id WHERE mp.host_id = $1 OR mp.partner_id = $1 ORDER BY mp.map_id, mp.score_count, mp.score_time;`
+        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
+        FROM records_mp mp INNER JOIN maps m ON mp.map_id = m.id WHERE mp.host_id = $1 OR mp.partner_id = $1 ORDER BY mp.map_id, mp.score_count, mp.score_time`
         rows, err = database.DB.Query(sql, user.(models.User).SteamID)
         if err != nil {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
                 return
         }
+        sql = `WITH map_records AS (SELECT mp.host_id, mp.partner_id, mp.map_id, 
+        RANK() OVER (PARTITION BY mp.map_id ORDER BY mp.score_count, mp.score_time) AS placement
+        FROM records_mp mp) SELECT DISTINCT ON (map_id) placement FROM map_records WHERE host_id = $1 OR partner_id = $1`
+        placementsRows, err = database.DB.Query(sql, user.(models.User).SteamID)
+        if err != nil {
+                c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
+                return
+        }
+        placements = []int{}
+        placementIndex = 0
+        for placementsRows.Next() {
+                var placement int
+                placementsRows.Scan(&placement)
+                placements = append(placements, placement)
+        }
         for rows.Next() {
                 var gameID int
                 var categoryID int
@@ -336,7 +374,7 @@ func Profile(c *gin.Context) {
                 var mapName string
                 var mapWR int
                 score := ProfileScores{}
-                rows.Scan(&gameID, &categoryID, &mapID, &mapName, &mapWR, &score.ScoreCount, &score.ScoreTime, &score.DemoID, &score.Date)
+                rows.Scan(&score.RecordID, &gameID, &categoryID, &mapID, &mapName, &mapWR, &score.ScoreCount, &score.ScoreTime, &score.DemoID, &score.Date)
                 // More than one record in one map
                 if len(records) != 0 && mapID == records[len(records)-1].MapID {
                         records[len(records)-1].Scores = append(records[len(records)-1].Scores, score)
@@ -349,8 +387,10 @@ func Profile(c *gin.Context) {
                         MapID:      mapID,
                         MapName:    mapName,
                         MapWRCount: mapWR,
+                        Placement:  placements[placementIndex],
                         Scores:     []ProfileScores{},
                 })
+                placementIndex++
                 records[len(records)-1].Scores = append(records[len(records)-1].Scores, score)
         }
         c.JSON(http.StatusOK, models.Response{
@@ -612,13 +652,28 @@ func FetchUser(c *gin.Context) {
         }
         records := []ProfileRecords{}
         // Get singleplayer records
-        sql = `SELECT 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
-        FROM records_sp sp INNER JOIN maps m ON sp.map_id = m.id WHERE sp.user_id = $1 ORDER BY sp.map_id, sp.score_count, sp.score_time;`
+        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
+        FROM records_sp sp INNER JOIN maps m ON sp.map_id = m.id WHERE sp.user_id = $1 ORDER BY sp.map_id, sp.score_count, sp.score_time`
         rows, err = database.DB.Query(sql, user.SteamID)
         if err != nil {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
                 return
         }
+        sql = `WITH map_records AS (SELECT sp.user_id, sp.map_id, 
+        RANK() OVER (PARTITION BY sp.map_id ORDER BY sp.score_count, sp.score_time) AS placement
+        FROM records_sp sp) SELECT DISTINCT ON (map_id) placement FROM map_records WHERE user_id = $1`
+        placementsRows, err := database.DB.Query(sql, user.SteamID)
+        if err != nil {
+                c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
+                return
+        }
+        placements := []int{}
+        placementIndex := 0
+        for placementsRows.Next() {
+                var placement int
+                placementsRows.Scan(&placement)
+                placements = append(placements, placement)
+        }
         for rows.Next() {
                 var gameID int
                 var categoryID int
@@ -626,7 +681,11 @@ func FetchUser(c *gin.Context) {
                 var mapName string
                 var mapWR int
                 score := ProfileScores{}
-                rows.Scan(&gameID, &categoryID, &mapID, &mapName, &mapWR, &score.ScoreCount, &score.ScoreTime, &score.DemoID, &score.Date)
+                err = rows.Scan(&score.RecordID, &gameID, &categoryID, &mapID, &mapName, &mapWR, &score.ScoreCount, &score.ScoreTime, &score.DemoID, &score.Date)
+                if err != nil {
+                        c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
+                        return
+                }
                 // More than one record in one map
                 if len(records) != 0 && mapID == records[len(records)-1].MapID {
                         records[len(records)-1].Scores = append(records[len(records)-1].Scores, score)
@@ -639,18 +698,35 @@ func FetchUser(c *gin.Context) {
                         MapID:      mapID,
                         MapName:    mapName,
                         MapWRCount: mapWR,
+                        Placement:  placements[placementIndex],
                         Scores:     []ProfileScores{},
                 })
+                placementIndex++
                 records[len(records)-1].Scores = append(records[len(records)-1].Scores, score)
         }
         // Get multiplayer records
-        sql = `SELECT 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
-        FROM records_mp mp INNER JOIN maps m ON mp.map_id = m.id WHERE mp.host_id = $1 OR mp.partner_id = $1 ORDER BY mp.map_id, mp.score_count, mp.score_time;`
+        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
+        FROM records_mp mp INNER JOIN maps m ON mp.map_id = m.id WHERE mp.host_id = $1 OR mp.partner_id = $1 ORDER BY mp.map_id, mp.score_count, mp.score_time`
         rows, err = database.DB.Query(sql, user.SteamID)
         if err != nil {
                 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
                 return
         }
+        sql = `WITH map_records AS (SELECT mp.host_id, mp.partner_id, mp.map_id, 
+        RANK() OVER (PARTITION BY mp.map_id ORDER BY mp.score_count, mp.score_time) AS placement
+        FROM records_mp mp) SELECT DISTINCT ON (map_id) placement FROM map_records WHERE host_id = $1 OR partner_id = $1`
+        placementsRows, err = database.DB.Query(sql, user.SteamID)
+        if err != nil {
+                c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
+                return
+        }
+        placements = []int{}
+        placementIndex = 0
+        for placementsRows.Next() {
+                var placement int
+                placementsRows.Scan(&placement)
+                placements = append(placements, placement)
+        }
         for rows.Next() {
                 var gameID int
                 var categoryID int
@@ -658,7 +734,7 @@ func FetchUser(c *gin.Context) {
                 var mapName string
                 var mapWR int
                 score := ProfileScores{}
-                rows.Scan(&gameID, &categoryID, &mapID, &mapName, &mapWR, &score.ScoreCount, &score.ScoreTime, &score.DemoID, &score.Date)
+                rows.Scan(&score.RecordID, &gameID, &categoryID, &mapID, &mapName, &mapWR, &score.ScoreCount, &score.ScoreTime, &score.DemoID, &score.Date)
                 // More than one record in one map
                 if len(records) != 0 && mapID == records[len(records)-1].MapID {
                         records[len(records)-1].Scores = append(records[len(records)-1].Scores, score)
@@ -671,8 +747,10 @@ func FetchUser(c *gin.Context) {
                         MapID:      mapID,
                         MapName:    mapName,
                         MapWRCount: mapWR,
+                        Placement:  placements[placementIndex],
                         Scores:     []ProfileScores{},
                 })
+                placementIndex++
                 records[len(records)-1].Scores = append(records[len(records)-1].Scores, score)
         }
         c.JSON(http.StatusOK, models.Response{