From 7e63ef2fd6ce429d63ef73ee54199bc530da84fe Mon Sep 17 00:00:00 2001
From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>
Date: Thu, 31 Oct 2024 00:29:37 +0300
Subject: backend: fix all rankings and placements, use sql funcs

---
 backend/handlers/user.go | 493 +++++++++++++++++++----------------------------
 1 file changed, 193 insertions(+), 300 deletions(-)

(limited to 'backend/handlers/user.go')

diff --git a/backend/handlers/user.go b/backend/handlers/user.go
index dc058c8..021a47f 100644
--- a/backend/handlers/user.go
+++ b/backend/handlers/user.go
@@ -134,162 +134,118 @@ func Profile(c *gin.Context) {
 		}
 	}
 	rankings.Overall.CompletionCount = rankings.Singleplayer.CompletionCount + rankings.Cooperative.CompletionCount
-	// Get user ranking placement for singleplayer
-	sql = `SELECT u.steam_id, COUNT(DISTINCT map_id), 
-	(SELECT COUNT(maps.name) FROM maps INNER JOIN games g ON maps.game_id = g.id WHERE g."name" = 'Portal 2 - Singleplayer' AND maps.is_disabled = false), 
-	(SELECT SUM(min_score_count) AS total_min_score_count FROM (
-		SELECT user_id, MIN(score_count) AS min_score_count 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 WHERE is_deleted = false GROUP BY u.steam_id, u.user_name
-	HAVING COUNT(DISTINCT map_id) = (SELECT COUNT(maps.name) FROM maps INNER JOIN games g ON maps.game_id = g.id WHERE g.is_coop = FALSE AND is_disabled = false)
-	ORDER BY total_min_score_count ASC`
-	rows, err = database.DB.Query(sql)
+	// Get user rankings. We are basically doing the same thing in RankingsLPHUB endpoint lol.
+	rankingsList := RankingsResponse{
+		Singleplayer: []models.UserRanking{},
+		Multiplayer:  []models.UserRanking{},
+		Overall:      []models.UserRanking{},
+	}
+	// Singleplayer rankings
+	rows, err = database.DB.Query(`SELECT * FROM get_rankings_singleplayer();`)
 	if err != nil {
 		c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
 		return
 	}
-	placement := 1
 	for rows.Next() {
-		var steamID string
-		var completionCount int
-		var totalCount int
-		var userPortalCount int
-		err = rows.Scan(&steamID, &completionCount, &totalCount, &userPortalCount)
+		ranking := models.UserRanking{}
+		err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, &ranking.TotalScore)
 		if err != nil {
 			c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
 			return
 		}
-		if completionCount != totalCount {
-			placement++
-			continue
-		}
-		if steamID != user.(models.User).SteamID {
-			placement++
-			continue
-		}
-		rankings.Singleplayer.Rank = placement
-	}
-	// Get user ranking placement for multiplayer
-	sql = `SELECT u.steam_id, COUNT(DISTINCT map_id), 
-	(SELECT COUNT(maps.name) FROM maps INNER JOIN games g ON maps.game_id = g.id WHERE g."name" = 'Portal 2 - Cooperative' AND is_disabled = false), 
-	(SELECT SUM(min_score_count) AS total_min_score_count FROM (
-		SELECT host_id, partner_id, MIN(score_count) AS min_score_count 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 WHERE mp.is_deleted = false GROUP BY u.steam_id, u.user_name
-	HAVING  COUNT(DISTINCT map_id) = (SELECT COUNT(maps.name) FROM maps INNER JOIN games g ON maps.game_id = g.id WHERE g.is_coop = true AND is_disabled = false)
-	ORDER BY total_min_score_count ASC`
-	rows, err = database.DB.Query(sql)
+		rankingsList.Singleplayer = append(rankingsList.Singleplayer, ranking)
+	}
+	// Multiplayer rankings
+	rows, err = database.DB.Query(`SELECT * FROM get_rankings_multiplayer();`)
 	if err != nil {
 		c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
 		return
 	}
-	placement = 1
 	for rows.Next() {
-		var steamID string
-		var completionCount int
-		var totalCount int
-		var userPortalCount int
-		err = rows.Scan(&steamID, &completionCount, &totalCount, &userPortalCount)
+		ranking := models.UserRanking{}
+		err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, &ranking.TotalScore)
 		if err != nil {
 			c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
 			return
 		}
-		if completionCount != totalCount {
-			placement++
-			continue
+		rankingsList.Multiplayer = append(rankingsList.Multiplayer, ranking)
+	}
+	// Overall rankings
+	var hasOverallPlacement bool
+	for _, spRanking := range rankingsList.Singleplayer {
+		for _, mpRanking := range rankingsList.Multiplayer {
+			if spRanking.User.SteamID == mpRanking.User.SteamID {
+				if spRanking.User.SteamID == user.(models.User).SteamID {
+					hasOverallPlacement = true
+				}
+				totalScore := spRanking.TotalScore + mpRanking.TotalScore
+				overallRanking := models.UserRanking{
+					User:       spRanking.User,
+					TotalScore: totalScore,
+				}
+				rankingsList.Overall = append(rankingsList.Overall, overallRanking)
+				break
+			}
 		}
-		if steamID != user.(models.User).SteamID {
-			placement++
-			continue
+	}
+
+	placement := 1
+	ties := 0
+	for index := 0; index < len(rankingsList.Singleplayer); index++ {
+		if index != 0 && rankingsList.Singleplayer[index-1].TotalScore == rankingsList.Singleplayer[index].TotalScore {
+			ties++
+			rankingsList.Singleplayer[index].Placement = placement - ties
+		} else {
+			ties = 0
+			rankingsList.Singleplayer[index].Placement = placement
 		}
-		rankings.Cooperative.Rank = placement
-	}
-	// Get user ranking placement for overall if they qualify
-	if rankings.Singleplayer.Rank != 0 && rankings.Cooperative.Rank != 0 {
-		sql = `WITH user_sp AS (
-			SELECT u.steam_id,
-				   SUM(subquery.min_score_count) AS total_min_score_count
-			FROM users u
-			LEFT JOIN (
-				SELECT user_id, map_id, MIN(score_count) AS min_score_count
-				FROM records_sp WHERE is_deleted = false
-				GROUP BY user_id, map_id
-			) AS subquery ON subquery.user_id = u.steam_id
-			WHERE u.steam_id IN (
-				SELECT user_id
-				FROM records_sp sp
-				JOIN maps m ON sp.map_id = m.id
-				JOIN games g ON m.game_id = g.id
-				WHERE g.is_coop = FALSE AND m.is_disabled = FALSE AND sp.is_deleted = false
-				GROUP BY user_id
-				HAVING COUNT(DISTINCT sp.map_id) = (
-					SELECT COUNT(maps.name)
-					FROM maps
-					INNER JOIN games g ON maps.game_id = g.id
-					WHERE g.is_coop = FALSE AND maps.is_disabled = FALSE
-				)
-			)
-			GROUP BY u.steam_id
-		), user_mp AS (
-			SELECT u.steam_id,
-				   SUM(subquery.min_score_count) AS total_min_score_count
-			FROM users u
-			LEFT JOIN (
-				SELECT host_id, partner_id, map_id, MIN(score_count) AS min_score_count
-				FROM records_mp WHERE is_deleted = false
-				GROUP BY host_id, partner_id, map_id
-			) AS subquery ON subquery.host_id = u.steam_id OR subquery.partner_id = u.steam_id
-			WHERE u.steam_id IN (
-				SELECT host_id
-				FROM records_mp mp
-				JOIN maps m ON mp.map_id = m.id
-				JOIN games g ON m.game_id = g.id
-				WHERE g.is_coop = TRUE AND m.is_disabled = FALSE AND mp.is_deleted = false
-				GROUP BY host_id
-				HAVING COUNT(DISTINCT mp.map_id) = (
-					SELECT COUNT(maps.name)
-					FROM maps
-					INNER JOIN games g ON maps.game_id = g.id
-					WHERE g.is_coop = TRUE AND maps.is_disabled = FALSE
-				)
-				UNION
-				SELECT partner_id
-				FROM records_mp mp
-				JOIN maps m ON mp.map_id = m.id
-				JOIN games g ON m.game_id = g.id
-				WHERE g.is_coop = TRUE AND m.is_disabled = FALSE AND mp.is_deleted = false
-				GROUP BY partner_id
-				HAVING COUNT(DISTINCT mp.map_id) = (
-					SELECT COUNT(maps.name)
-					FROM maps
-					INNER JOIN games g ON maps.game_id = g.id
-					WHERE g.is_coop = TRUE AND maps.is_disabled = FALSE
-				)
-			)
-			GROUP BY u.steam_id
-		)
-		SELECT COALESCE(sp.steam_id, mp.steam_id) AS steam_id,
-			   sp.total_min_score_count + mp.total_min_score_count AS overall_total_min_score_count
-		FROM user_sp sp
-		INNER JOIN user_mp mp ON sp.steam_id = mp.steam_id
-		ORDER BY overall_total_min_score_count ASC`
-		rows, err = database.DB.Query(sql)
-		if err != nil {
-			c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
-			return
+		placement++
+	}
+
+	placement = 1
+	ties = 0
+	for index := 0; index < len(rankingsList.Multiplayer); index++ {
+		if index != 0 && rankingsList.Multiplayer[index-1].TotalScore == rankingsList.Multiplayer[index].TotalScore {
+			ties++
+			rankingsList.Multiplayer[index].Placement = placement - ties
+		} else {
+			ties = 0
+			rankingsList.Multiplayer[index].Placement = placement
 		}
-		placement = 1
-		for rows.Next() {
-			var steamID string
-			var userPortalCount int
-			err = rows.Scan(&steamID, &userPortalCount)
-			if err != nil {
-				c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
-				return
-			}
-			if steamID != user.(models.User).SteamID {
-				placement++
-				continue
+		placement++
+	}
+
+	placement = 1
+	ties = 0
+	for index := 0; index < len(rankingsList.Overall); index++ {
+		if index != 0 && rankingsList.Overall[index-1].TotalScore == rankingsList.Overall[index].TotalScore {
+			ties++
+			rankingsList.Overall[index].Placement = placement - ties
+		} else {
+			ties = 0
+			rankingsList.Overall[index].Placement = placement
+		}
+		placement++
+	}
+	// After we did that heavy calculation and got the rankings of ALL players, let's see if our user exists and grab the placements if they do.
+	for _, singleplayer := range rankingsList.Singleplayer {
+		if singleplayer.User.SteamID == user.(models.User).SteamID {
+			rankings.Singleplayer.Rank = singleplayer.Placement
+			break
+		}
+	}
+	for _, multiplayer := range rankingsList.Multiplayer {
+		if multiplayer.User.SteamID == user.(models.User).SteamID {
+			rankings.Cooperative.Rank = multiplayer.Placement
+			break
+		}
+	}
+	if hasOverallPlacement {
+		for _, overall := range rankingsList.Overall {
+			if overall.User.SteamID == user.(models.User).SteamID {
+				rankings.Overall.Rank = overall.Placement
+				break
 			}
-			rankings.Overall.Rank = placement
 		}
 	}
 	records := []ProfileRecords{}
@@ -301,13 +257,7 @@ func Profile(c *gin.Context) {
 		c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
 		return
 	}
-	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)
+	placementsRows, err := database.DB.Query(`SELECT * FROM get_placements_singleplayer($1);`, user.(models.User).SteamID)
 	if err != nil {
 		c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
 		return
@@ -315,8 +265,9 @@ func Profile(c *gin.Context) {
 	placements := []int{}
 	placementIndex := 0
 	for placementsRows.Next() {
+		var mapID int
 		var placement int
-		placementsRows.Scan(&placement)
+		placementsRows.Scan(&mapID, &placement)
 		placements = append(placements, placement)
 	}
 	for rows.Next() {
@@ -357,13 +308,7 @@ func Profile(c *gin.Context) {
 		c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
 		return
 	}
-	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)
+	placementsRows, err = database.DB.Query(`SELECT * FROM get_placements_multiplayer($1);`, user.(models.User).SteamID)
 	if err != nil {
 		c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
 		return
@@ -371,8 +316,9 @@ func Profile(c *gin.Context) {
 	placements = []int{}
 	placementIndex = 0
 	for placementsRows.Next() {
+		var mapID int
 		var placement int
-		placementsRows.Scan(&placement)
+		placementsRows.Scan(&mapID, &placement)
 		placements = append(placements, placement)
 	}
 	for rows.Next() {
@@ -512,162 +458,119 @@ func FetchUser(c *gin.Context) {
 		}
 	}
 	rankings.Overall.CompletionCount = rankings.Singleplayer.CompletionCount + rankings.Cooperative.CompletionCount
-	// Get user ranking placement for singleplayer
-	sql = `SELECT u.steam_id, COUNT(DISTINCT map_id), 
-	(SELECT COUNT(maps.name) FROM maps INNER JOIN games g ON maps.game_id = g.id WHERE g."name" = 'Portal 2 - Singleplayer' AND maps.is_disabled = false), 
-	(SELECT SUM(min_score_count) AS total_min_score_count FROM (
-		SELECT user_id, MIN(score_count) AS min_score_count 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 WHERE is_deleted = false GROUP BY u.steam_id, u.user_name
-	HAVING COUNT(DISTINCT map_id) = (SELECT COUNT(maps.name) FROM maps INNER JOIN games g ON maps.game_id = g.id WHERE g.is_coop = FALSE AND is_disabled = false)
-	ORDER BY total_min_score_count ASC`
-	rows, err = database.DB.Query(sql)
+	// Get user rankings. We are basically doing the same thing in RankingsLPHUB endpoint lol.
+	rankingsList := RankingsResponse{
+		Singleplayer: []models.UserRanking{},
+		Multiplayer:  []models.UserRanking{},
+		Overall:      []models.UserRanking{},
+	}
+	// Singleplayer rankings
+	rows, err = database.DB.Query(`SELECT * FROM get_rankings_singleplayer();`)
 	if err != nil {
 		c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
 		return
 	}
-	placement := 1
 	for rows.Next() {
-		var steamID string
-		var completionCount int
-		var totalCount int
-		var userPortalCount int
-		err = rows.Scan(&steamID, &completionCount, &totalCount, &userPortalCount)
+		ranking := models.UserRanking{}
+		err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, &ranking.TotalScore)
 		if err != nil {
 			c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
 			return
 		}
-		if completionCount != totalCount {
-			placement++
-			continue
-		}
-		if steamID != user.SteamID {
-			placement++
-			continue
-		}
-		rankings.Singleplayer.Rank = placement
-	}
-	// Get user ranking placement for multiplayer
-	sql = `SELECT u.steam_id, COUNT(DISTINCT map_id), 
-	(SELECT COUNT(maps.name) FROM maps INNER JOIN games g ON maps.game_id = g.id WHERE g."name" = 'Portal 2 - Cooperative' AND is_disabled = false), 
-	(SELECT SUM(min_score_count) AS total_min_score_count FROM (
-		SELECT host_id, partner_id, MIN(score_count) AS min_score_count 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 WHERE mp.is_deleted = false GROUP BY u.steam_id, u.user_name
-	HAVING  COUNT(DISTINCT map_id) = (SELECT COUNT(maps.name) FROM maps INNER JOIN games g ON maps.game_id = g.id WHERE g.is_coop = true AND is_disabled = false)
-	ORDER BY total_min_score_count ASC`
-	rows, err = database.DB.Query(sql)
+		rankingsList.Singleplayer = append(rankingsList.Singleplayer, ranking)
+	}
+	// Multiplayer rankings
+	rows, err = database.DB.Query(`SELECT * FROM get_rankings_multiplayer();`)
 	if err != nil {
 		c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
 		return
 	}
-	placement = 1
 	for rows.Next() {
-		var steamID string
-		var completionCount int
-		var totalCount int
-		var userPortalCount int
-		err = rows.Scan(&steamID, &completionCount, &totalCount, &userPortalCount)
+		ranking := models.UserRanking{}
+		err = rows.Scan(&ranking.User.SteamID, &ranking.User.UserName, &ranking.User.AvatarLink, &ranking.TotalScore)
 		if err != nil {
 			c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
 			return
 		}
-		if completionCount != totalCount {
-			placement++
-			continue
+		rankingsList.Multiplayer = append(rankingsList.Multiplayer, ranking)
+	}
+	// Overall rankings
+	var hasOverallPlacement bool
+	for _, spRanking := range rankingsList.Singleplayer {
+		for _, mpRanking := range rankingsList.Multiplayer {
+			if spRanking.User.SteamID == mpRanking.User.SteamID {
+				if spRanking.User.SteamID == user.SteamID {
+					hasOverallPlacement = true
+				}
+				totalScore := spRanking.TotalScore + mpRanking.TotalScore
+				overallRanking := models.UserRanking{
+					User:       spRanking.User,
+					TotalScore: totalScore,
+				}
+				rankingsList.Overall = append(rankingsList.Overall, overallRanking)
+				break
+			}
 		}
-		if steamID != user.SteamID {
-			placement++
-			continue
+	}
+
+	placement := 1
+	ties := 0
+	for index := 0; index < len(rankingsList.Singleplayer); index++ {
+		if index != 0 && rankingsList.Singleplayer[index-1].TotalScore == rankingsList.Singleplayer[index].TotalScore {
+			ties++
+			rankingsList.Singleplayer[index].Placement = placement - ties
+		} else {
+			ties = 0
+			rankingsList.Singleplayer[index].Placement = placement
 		}
-		rankings.Cooperative.Rank = placement
-	}
-	// Get user ranking placement for overall if they qualify
-	if rankings.Singleplayer.Rank != 0 && rankings.Cooperative.Rank != 0 {
-		sql = `WITH user_sp AS (
-			SELECT u.steam_id,
-				   SUM(subquery.min_score_count) AS total_min_score_count
-			FROM users u
-			LEFT JOIN (
-				SELECT user_id, map_id, MIN(score_count) AS min_score_count
-				FROM records_sp WHERE is_deleted = false
-				GROUP BY user_id, map_id
-			) AS subquery ON subquery.user_id = u.steam_id
-			WHERE u.steam_id IN (
-				SELECT user_id
-				FROM records_sp sp
-				JOIN maps m ON sp.map_id = m.id
-				JOIN games g ON m.game_id = g.id
-				WHERE g.is_coop = FALSE AND m.is_disabled = FALSE AND sp.is_deleted = false
-				GROUP BY user_id
-				HAVING COUNT(DISTINCT sp.map_id) = (
-					SELECT COUNT(maps.name)
-					FROM maps
-					INNER JOIN games g ON maps.game_id = g.id
-					WHERE g.is_coop = FALSE AND maps.is_disabled = FALSE
-				)
-			)
-			GROUP BY u.steam_id
-		), user_mp AS (
-			SELECT u.steam_id,
-				   SUM(subquery.min_score_count) AS total_min_score_count
-			FROM users u
-			LEFT JOIN (
-				SELECT host_id, partner_id, map_id, MIN(score_count) AS min_score_count
-				FROM records_mp WHERE is_deleted = false
-				GROUP BY host_id, partner_id, map_id
-			) AS subquery ON subquery.host_id = u.steam_id OR subquery.partner_id = u.steam_id
-			WHERE u.steam_id IN (
-				SELECT host_id
-				FROM records_mp mp
-				JOIN maps m ON mp.map_id = m.id
-				JOIN games g ON m.game_id = g.id
-				WHERE g.is_coop = TRUE AND m.is_disabled = FALSE AND mp.is_deleted = false
-				GROUP BY host_id
-				HAVING COUNT(DISTINCT mp.map_id) = (
-					SELECT COUNT(maps.name)
-					FROM maps
-					INNER JOIN games g ON maps.game_id = g.id
-					WHERE g.is_coop = TRUE AND maps.is_disabled = FALSE
-				)
-				UNION
-				SELECT partner_id
-				FROM records_mp mp
-				JOIN maps m ON mp.map_id = m.id
-				JOIN games g ON m.game_id = g.id
-				WHERE g.is_coop = TRUE AND m.is_disabled = FALSE AND mp.is_deleted = false
-				GROUP BY partner_id
-				HAVING COUNT(DISTINCT mp.map_id) = (
-					SELECT COUNT(maps.name)
-					FROM maps
-					INNER JOIN games g ON maps.game_id = g.id
-					WHERE g.is_coop = TRUE AND maps.is_disabled = FALSE
-				)
-			)
-			GROUP BY u.steam_id
-		)
-		SELECT COALESCE(sp.steam_id, mp.steam_id) AS steam_id,
-			   sp.total_min_score_count + mp.total_min_score_count AS overall_total_min_score_count
-		FROM user_sp sp
-		INNER JOIN user_mp mp ON sp.steam_id = mp.steam_id
-		ORDER BY overall_total_min_score_count ASC`
-		rows, err = database.DB.Query(sql)
-		if err != nil {
-			c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
-			return
+		placement++
+	}
+
+	placement = 1
+	ties = 0
+	for index := 0; index < len(rankingsList.Multiplayer); index++ {
+		if index != 0 && rankingsList.Multiplayer[index-1].TotalScore == rankingsList.Multiplayer[index].TotalScore {
+			ties++
+			rankingsList.Multiplayer[index].Placement = placement - ties
+		} else {
+			ties = 0
+			rankingsList.Multiplayer[index].Placement = placement
 		}
-		placement = 1
-		for rows.Next() {
-			var steamID string
-			var userPortalCount int
-			err = rows.Scan(&steamID, &userPortalCount)
-			if err != nil {
-				c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
-				return
-			}
-			if steamID != user.SteamID {
-				placement++
-				continue
+		placement++
+	}
+
+	placement = 1
+	ties = 0
+	for index := 0; index < len(rankingsList.Overall); index++ {
+		if index != 0 && rankingsList.Overall[index-1].TotalScore == rankingsList.Overall[index].TotalScore {
+			ties++
+			rankingsList.Overall[index].Placement = placement - ties
+		} else {
+			ties = 0
+			rankingsList.Overall[index].Placement = placement
+		}
+		placement++
+	}
+	// After we did that heavy calculation and got the rankings of ALL players, let's see if our user exists
+	// and grab the placements if they do.
+	for _, singleplayer := range rankingsList.Singleplayer {
+		if singleplayer.User.SteamID == user.SteamID {
+			rankings.Singleplayer.Rank = singleplayer.Placement
+			break
+		}
+	}
+	for _, multiplayer := range rankingsList.Multiplayer {
+		if multiplayer.User.SteamID == user.SteamID {
+			rankings.Cooperative.Rank = multiplayer.Placement
+			break
+		}
+	}
+	if hasOverallPlacement {
+		for _, overall := range rankingsList.Overall {
+			if overall.User.SteamID == user.SteamID {
+				rankings.Overall.Rank = overall.Placement
+				break
 			}
-			rankings.Overall.Rank = placement
 		}
 	}
 	records := []ProfileRecords{}
@@ -679,13 +582,7 @@ func FetchUser(c *gin.Context) {
 		c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
 		return
 	}
-	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)
+	placementsRows, err := database.DB.Query(`SELECT * FROM get_placements_singleplayer($1);`, user.SteamID)
 	if err != nil {
 		c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
 		return
@@ -693,8 +590,9 @@ func FetchUser(c *gin.Context) {
 	placements := []int{}
 	placementIndex := 0
 	for placementsRows.Next() {
+		var mapID int
 		var placement int
-		placementsRows.Scan(&placement)
+		placementsRows.Scan(&mapID, &placement)
 		placements = append(placements, placement)
 	}
 	for rows.Next() {
@@ -735,13 +633,7 @@ func FetchUser(c *gin.Context) {
 		c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
 		return
 	}
-	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)
+	placementsRows, err = database.DB.Query(`SELECT * FROM get_placements_multiplayer($1);`, user.SteamID)
 	if err != nil {
 		c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
 		return
@@ -749,8 +641,9 @@ func FetchUser(c *gin.Context) {
 	placements = []int{}
 	placementIndex = 0
 	for placementsRows.Next() {
+		var mapID int
 		var placement int
-		placementsRows.Scan(&placement)
+		placementsRows.Scan(&mapID, &placement)
 		placements = append(placements, placement)
 	}
 	for rows.Next() {
-- 
cgit v1.2.3