feat: working on rankings, overall rank left (#51)

Former-commit-id: 3e2a57f4592259c94abef84f408405869c6df922
author: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com> 2023-09-03 16:15:51 +0300
committer: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com> 2023-09-03 16:15:51 +0300
commit: 48f60f8a5baeaa0814c890c7d3f6be56e144d334 (patch)
tree: 8ae964f019703692aa50bf9b4d7ce5b3361779c4 /backend/handlers
parent: fix: order mod logs by most recent (#55) (diff)
download: lphub-48f60f8a5baeaa0814c890c7d3f6be56e144d334.tar.gz
lphub-48f60f8a5baeaa0814c890c7d3f6be56e144d334.tar.bz2
lphub-48f60f8a5baeaa0814c890c7d3f6be56e144d334.zip
1 files changed, 341 insertions, 11 deletions
diff --git a/backend/handlers/user.go b/backend/handlers/user.go
index e5b824b..e0f1dff 100644
--- a/backend/handlers/user.go
+++ b/backend/handlers/user.go
@@ -81,12 +81,185 @@ func Profile(c *gin.Context) {
                c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
                return
        }
-        // TODO: Get rankings (all maps done in one game)
+        // Get rankings (all maps done in one game)
+        rankings := ProfileRankings{
+                Overall:      ProfileRankingsDetails{},
+                Singleplayer: ProfileRankingsDetails{},
+                Cooperative:  ProfileRankingsDetails{},
+        }
+        // Get total map count
+        sql = `SELECT count(id), (SELECT count(id) FROM maps m WHERE m.game_id = 2 AND m.is_disabled = false) FROM maps m WHERE m.game_id = 1  AND m.is_disabled = false;`
+        err = database.DB.QueryRow(sql).Scan(&rankings.Singleplayer.CompletionTotal, &rankings.Cooperative.CompletionTotal)
+        if err != nil {
+                c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
+                return
+        }
+        rankings.Overall.CompletionTotal = rankings.Singleplayer.CompletionTotal + rankings.Cooperative.CompletionTotal
+        // Get user completion count
+        sql = `SELECT 'records_sp' AS table_name, COUNT(rs.id) AS total_user_scores
+        FROM public.records_sp rs 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
+        ) AS subquery_sp ON rs.map_id = subquery_sp.map_id AND rs.score_count = subquery_sp.min_score_count
+        WHERE rs.user_id = $1
+        UNION ALL
+        SELECT 'records_mp' AS table_name, COUNT(rm.id) AS total_user_scores
+        FROM public.records_mp rm 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
+        ) AS subquery_mp ON rm.map_id = subquery_mp.map_id AND rm.score_count = subquery_mp.min_score_count
+        WHERE rm.host_id = $1 OR rm.partner_id = $1;`
+        rows, err := database.DB.Query(sql, user.(models.User).SteamID)
+        if err != nil {
+                c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
+                return
+        }
+        for rows.Next() {
+                var tableName string
+                var completionCount int
+                err = rows.Scan(&tableName, &completionCount)
+                if err != nil {
+                        c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
+                        return
+                }
+                if tableName == "records_sp" {
+                        rankings.Singleplayer.CompletionCount = completionCount
+                        continue
+                }
+                if tableName == "records_mp" {
+                        rankings.Cooperative.CompletionCount = completionCount
+                        continue
+                }
+        }
+        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.is_coop = FALSE AND 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 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
+        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)
+        if err != nil {
+                c.JSON(http.StatusBadRequest, 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)
+                if err != nil {
+                        c.JSON(http.StatusBadRequest, 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.is_coop = FALSE 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 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
+        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)
+        if err != nil {
+                c.JSON(http.StatusBadRequest, 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)
+                if err != nil {
+                        c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
+                        return
+                }
+                if completionCount != totalCount {
+                        placement++
+                        continue
+                }
+                if steamID != user.(models.User).SteamID {
+                        placement++
+                        continue
+                }
+                rankings.Cooperative.Rank = placement
+        }
+        // TODO: Get user ranking placement for overall if they qualify
+        // if (rankings.Singleplayer.Rank != 0) && (rankings.Cooperative.Rank != 0) {
+        //      sql = `SELECT steam_id, SUM(total_min_score_count) AS total_score
+        // FROM (
+        //      SELECT u.steam_id,
+        //                 (SELECT SUM(min_score_count) AS total_min_score_count FROM (
+        //                              SELECT
+        //                                      user_id,
+        //                                      MIN(score_count) AS min_score_count
+        //                              FROM records_sp
+        //                              GROUP BY user_id, map_id
+        //                      ) AS subquery
+        //                      WHERE user_id = u.steam_id) AS total_min_score_count
+        //      FROM records_sp sp
+        //      JOIN users u ON u.steam_id = sp.user_id
+        //      UNION ALL
+        //      SELECT u.steam_id,
+        //                 (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
+        //                              GROUP BY host_id, partner_id, map_id
+        //                      ) AS subquery
+        //                      WHERE host_id = u.steam_id OR partner_id = u.steam_id) AS total_min_score_count
+        //      FROM records_mp mp
+        //      JOIN users u ON u.steam_id = mp.host_id OR u.steam_id = mp.partner_id
+        // ) AS combined_scores
+        // GROUP BY steam_id ORDER BY total_score ASC;`
+        //      rows, err = database.DB.Query(sql)
+        //      if err != nil {
+        //              c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
+        //              return
+        //      }
+        //      placement = 1
+        //      for rows.Next() {
+        //              var steamID string
+        //              var userPortalCount int
+        //              err = rows.Scan(&steamID, &userPortalCount)
+        //              if err != nil {
+        //                      c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
+        //                      return
+        //              }
+        //              if completionCount != totalCount {
+        //                      placement++
+        //                      continue
+        //              }
+        //              if steamID != user.(models.User).SteamID {
+        //                      placement++
+        //                      continue
+        //              }
+        //              rankings.Cooperative.Rank = placement
+        //      }
+        // }
        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;`
-        rows, err := database.DB.Query(sql, user.(models.User).SteamID)
+        rows, err = database.DB.Query(sql, user.(models.User).SteamID)
        if err != nil {
                c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
                return
@@ -158,7 +331,7 @@ func Profile(c *gin.Context) {
                        CountryCode: user.(models.User).CountryCode,
                        Titles:      user.(models.User).Titles,
                        Links:       links,
-                        Rankings:    ProfileRankings{},
+                        Rankings:    rankings,
                        Records:     records,
                },
        })
@@ -197,23 +370,180 @@ func FetchUser(c *gin.Context) {
                c.JSON(http.StatusNotFound, models.ErrorResponse("User not found."))
                return
        }
-        // Get user titles
+        // Get rankings (all maps done in one game)
-        sql = `SELECT t.title_name, t.title_color FROM titles t
+        rankings := ProfileRankings{
-        INNER JOIN user_titles ut ON t.id=ut.title_id WHERE ut.user_id = $1`
+                Overall:      ProfileRankingsDetails{},
+                Singleplayer: ProfileRankingsDetails{},
+                Cooperative:  ProfileRankingsDetails{},
+        }
+        // Get total map count
+        sql = `SELECT count(id), (SELECT count(id) FROM maps m WHERE m.game_id = 2 AND m.is_disabled = false) FROM maps m WHERE m.game_id = 1  AND m.is_disabled = false;`
+        err = database.DB.QueryRow(sql).Scan(&rankings.Singleplayer.CompletionTotal, &rankings.Cooperative.CompletionTotal)
+        if err != nil {
+                c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
+                return
+        }
+        rankings.Overall.CompletionTotal = rankings.Singleplayer.CompletionTotal + rankings.Cooperative.CompletionTotal
+        // Get user completion count
+        sql = `SELECT 'records_sp' AS table_name, COUNT(rs.id) AS total_user_scores
+        FROM public.records_sp rs 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
+        ) AS subquery_sp ON rs.map_id = subquery_sp.map_id AND rs.score_count = subquery_sp.min_score_count
+        WHERE rs.user_id = $1
+        UNION ALL
+        SELECT 'records_mp' AS table_name, COUNT(rm.id) AS total_user_scores
+        FROM public.records_mp rm 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
+        ) AS subquery_mp ON rm.map_id = subquery_mp.map_id AND rm.score_count = subquery_mp.min_score_count
+        WHERE rm.host_id = $1 OR rm.partner_id = $1;`
        rows, err := database.DB.Query(sql, user.SteamID)
        if err != nil {
                c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
                return
        }
        for rows.Next() {
-                var title models.Title
+                var tableName string
-                if err := rows.Scan(&title.Name, &title.Color); err != nil {
+                var completionCount int
+                err = rows.Scan(&tableName, &completionCount)
+                if err != nil {
                        c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
                        return
                }
-                user.Titles = append(user.Titles, title)
+                if tableName == "records_sp" {
+                        rankings.Singleplayer.CompletionCount = completionCount
+                        continue
+                }
+                if tableName == "records_mp" {
+                        rankings.Cooperative.CompletionCount = completionCount
+                        continue
+                }
+        }
+        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.is_coop = FALSE AND 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 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
+        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)
+        if err != nil {
+                c.JSON(http.StatusBadRequest, 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)
+                if err != nil {
+                        c.JSON(http.StatusBadRequest, 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.is_coop = FALSE 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 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
+        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)
+        if err != nil {
+                c.JSON(http.StatusBadRequest, 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)
+                if err != nil {
+                        c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
+                        return
+                }
+                if completionCount != totalCount {
+                        placement++
+                        continue
+                }
+                if steamID != user.SteamID {
+                        placement++
+                        continue
+                }
+                rankings.Cooperative.Rank = placement
        }
-        // TODO: Get rankings (all maps done in one game)
+        // TODO: Get user ranking placement for overall if they qualify
+        // if (rankings.Singleplayer.Rank != 0) && (rankings.Cooperative.Rank != 0) {
+        //      sql = `SELECT steam_id, SUM(total_min_score_count) AS total_score
+        // FROM (
+        //      SELECT u.steam_id,
+        //                 (SELECT SUM(min_score_count) AS total_min_score_count FROM (
+        //                              SELECT
+        //                                      user_id,
+        //                                      MIN(score_count) AS min_score_count
+        //                              FROM records_sp
+        //                              GROUP BY user_id, map_id
+        //                      ) AS subquery
+        //                      WHERE user_id = u.steam_id) AS total_min_score_count
+        //      FROM records_sp sp
+        //      JOIN users u ON u.steam_id = sp.user_id
+        //      UNION ALL
+        //      SELECT u.steam_id,
+        //                 (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
+        //                              GROUP BY host_id, partner_id, map_id
+        //                      ) AS subquery
+        //                      WHERE host_id = u.steam_id OR partner_id = u.steam_id) AS total_min_score_count
+        //      FROM records_mp mp
+        //      JOIN users u ON u.steam_id = mp.host_id OR u.steam_id = mp.partner_id
+        // ) AS combined_scores
+        // GROUP BY steam_id ORDER BY total_score ASC;`
+        //      rows, err = database.DB.Query(sql)
+        //      if err != nil {
+        //              c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
+        //              return
+        //      }
+        //      placement = 1
+        //      for rows.Next() {
+        //              var steamID string
+        //              var userPortalCount int
+        //              err = rows.Scan(&steamID, &userPortalCount)
+        //              if err != nil {
+        //                      c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
+        //                      return
+        //              }
+        //              if completionCount != totalCount {
+        //                      placement++
+        //                      continue
+        //              }
+        //              if steamID != user.SteamID {
+        //                      placement++
+        //                      continue
+        //              }
+        //              rankings.Cooperative.Rank = placement
+        //      }
+        // }
        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
@@ -290,7 +620,7 @@ func FetchUser(c *gin.Context) {
                        CountryCode: user.CountryCode,
                        Titles:      user.Titles,
                        Links:       links,
-                        Rankings:    ProfileRankings{},
+                        Rankings:    rankings,
                        Records:     records,
                },
        })
author	Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>	2023-09-03 16:15:51 +0300
committer	Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>	2023-09-03 16:15:51 +0300
commit	48f60f8a5baeaa0814c890c7d3f6be56e144d334 (patch)
tree	8ae964f019703692aa50bf9b4d7ce5b3361779c4 /backend/handlers
parent	fix: order mod logs by most recent (#55) (diff)
download	lphub-48f60f8a5baeaa0814c890c7d3f6be56e144d334.tar.gz lphub-48f60f8a5baeaa0814c890c7d3f6be56e144d334.tar.bz2 lphub-48f60f8a5baeaa0814c890c7d3f6be56e144d334.zip

diff --git a/backend/handlers/user.go b/backend/handlers/user.go index e5b824b..e0f1dff 100644 --- a/backend/handlers/user.go +++ b/backend/handlers/user.go
@@ -81,12 +81,185 @@ func Profile(c *gin.Context) {
81	c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))	81	c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
82	return	82	return
83	}	83	}
84	// TODO: Get rankings (all maps done in one game)	84	// Get rankings (all maps done in one game)
		85	rankings := ProfileRankings{
		86	Overall: ProfileRankingsDetails{},
		87	Singleplayer: ProfileRankingsDetails{},
		88	Cooperative: ProfileRankingsDetails{},
		89	}
		90	// Get total map count
		91	sql = `SELECT count(id), (SELECT count(id) FROM maps m WHERE m.game_id = 2 AND m.is_disabled = false) FROM maps m WHERE m.game_id = 1 AND m.is_disabled = false;`
		92	err = database.DB.QueryRow(sql).Scan(&rankings.Singleplayer.CompletionTotal, &rankings.Cooperative.CompletionTotal)
		93	if err != nil {
		94	c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
		95	return
		96	}
		97	rankings.Overall.CompletionTotal = rankings.Singleplayer.CompletionTotal + rankings.Cooperative.CompletionTotal
		98	// Get user completion count
		99	sql = `SELECT 'records_sp' AS table_name, COUNT(rs.id) AS total_user_scores
		100	FROM public.records_sp rs JOIN (
		101	SELECT mr.map_id, MIN(mr.score_count) AS min_score_count
		102	FROM public.map_routes mr WHERE mr.category_id = 1 GROUP BY mr.map_id
		103	) AS subquery_sp ON rs.map_id = subquery_sp.map_id AND rs.score_count = subquery_sp.min_score_count
		104	WHERE rs.user_id = $1
		105	UNION ALL
		106	SELECT 'records_mp' AS table_name, COUNT(rm.id) AS total_user_scores
		107	FROM public.records_mp rm JOIN (
		108	SELECT mr.map_id, MIN(mr.score_count) AS min_score_count
		109	FROM public.map_routes mr WHERE mr.category_id = 1 GROUP BY mr.map_id
		110	) AS subquery_mp ON rm.map_id = subquery_mp.map_id AND rm.score_count = subquery_mp.min_score_count
		111	WHERE rm.host_id = $1 OR rm.partner_id = $1;`
		112	rows, err := database.DB.Query(sql, user.(models.User).SteamID)
		113	if err != nil {
		114	c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
		115	return
		116	}
		117	for rows.Next() {
		118	var tableName string
		119	var completionCount int
		120	err = rows.Scan(&tableName, &completionCount)
		121	if err != nil {
		122	c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
		123	return
		124	}
		125	if tableName == "records_sp" {
		126	rankings.Singleplayer.CompletionCount = completionCount
		127	continue
		128	}
		129	if tableName == "records_mp" {
		130	rankings.Cooperative.CompletionCount = completionCount
		131	continue
		132	}
		133	}
		134	rankings.Overall.CompletionCount = rankings.Singleplayer.CompletionCount + rankings.Cooperative.CompletionCount
		135	// Get user ranking placement for singleplayer
		136	sql = `SELECT u.steam_id, COUNT(DISTINCT map_id),
		137	(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),
		138	(SELECT SUM(min_score_count) AS total_min_score_count FROM (
		139	SELECT user_id, MIN(score_count) AS min_score_count FROM records_sp GROUP BY user_id, map_id) AS subquery WHERE user_id = u.steam_id)
		140	FROM records_sp sp JOIN users u ON u.steam_id = sp.user_id GROUP BY u.steam_id, u.user_name
		141	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)
		142	ORDER BY total_min_score_count ASC;`
		143	rows, err = database.DB.Query(sql)
		144	if err != nil {
		145	c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
		146	return
		147	}
		148	placement := 1
		149	for rows.Next() {
		150	var steamID string
		151	var completionCount int
		152	var totalCount int
		153	var userPortalCount int
		154	err = rows.Scan(&steamID, &completionCount, &totalCount, &userPortalCount)
		155	if err != nil {
		156	c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
		157	return
		158	}
		159	if completionCount != totalCount {
		160	placement++
		161	continue
		162	}
		163	if steamID != user.(models.User).SteamID {
		164	placement++
		165	continue
		166	}
		167	rankings.Singleplayer.Rank = placement
		168	}
		169	// Get user ranking placement for multiplayer
		170	sql = `SELECT u.steam_id, COUNT(DISTINCT map_id),
		171	(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),
		172	(SELECT SUM(min_score_count) AS total_min_score_count FROM (
		173	SELECT host_id, partner_id, MIN(score_count) AS min_score_count FROM records_mp GROUP BY host_id, partner_id, map_id) AS subquery WHERE host_id = u.steam_id OR partner_id = u.steam_id)
		174	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
		175	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)
		176	ORDER BY total_min_score_count ASC;`
		177	rows, err = database.DB.Query(sql)
		178	if err != nil {
		179	c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
		180	return
		181	}
		182	placement = 1
		183	for rows.Next() {
		184	var steamID string
		185	var completionCount int
		186	var totalCount int
		187	var userPortalCount int
		188	err = rows.Scan(&steamID, &completionCount, &totalCount, &userPortalCount)
		189	if err != nil {
		190	c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
		191	return
		192	}
		193	if completionCount != totalCount {
		194	placement++
		195	continue
		196	}
		197	if steamID != user.(models.User).SteamID {
		198	placement++
		199	continue
		200	}
		201	rankings.Cooperative.Rank = placement
		202	}
		203	// TODO: Get user ranking placement for overall if they qualify
		204	// if (rankings.Singleplayer.Rank != 0) && (rankings.Cooperative.Rank != 0) {
		205	// sql = `SELECT steam_id, SUM(total_min_score_count) AS total_score
		206	// FROM (
		207	// SELECT u.steam_id,
		208	// (SELECT SUM(min_score_count) AS total_min_score_count FROM (
		209	// SELECT
		210	// user_id,
		211	// MIN(score_count) AS min_score_count
		212	// FROM records_sp
		213	// GROUP BY user_id, map_id
		214	// ) AS subquery
		215	// WHERE user_id = u.steam_id) AS total_min_score_count
		216	// FROM records_sp sp
		217	// JOIN users u ON u.steam_id = sp.user_id
		218	// UNION ALL
		219	// SELECT u.steam_id,
		220	// (SELECT SUM(min_score_count) AS total_min_score_count FROM (
		221	// SELECT
		222	// host_id,
		223	// partner_id,
		224	// MIN(score_count) AS min_score_count
		225	// FROM records_mp
		226	// GROUP BY host_id, partner_id, map_id
		227	// ) AS subquery
		228	// WHERE host_id = u.steam_id OR partner_id = u.steam_id) AS total_min_score_count
		229	// FROM records_mp mp
		230	// JOIN users u ON u.steam_id = mp.host_id OR u.steam_id = mp.partner_id
		231	// ) AS combined_scores
		232	// GROUP BY steam_id ORDER BY total_score ASC;`
		233	// rows, err = database.DB.Query(sql)
		234	// if err != nil {
		235	// c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
		236	// return
		237	// }
		238	// placement = 1
		239	// for rows.Next() {
		240	// var steamID string
		241	// var userPortalCount int
		242	// err = rows.Scan(&steamID, &userPortalCount)
		243	// if err != nil {
		244	// c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
		245	// return
		246	// }
		247	// if completionCount != totalCount {
		248	// placement++
		249	// continue
		250	// }
		251	// if steamID != user.(models.User).SteamID {
		252	// placement++
		253	// continue
		254	// }
		255	// rankings.Cooperative.Rank = placement
		256	// }
		257	// }
85	records := []ProfileRecords{}	258	records := []ProfileRecords{}
86	// Get singleplayer records	259	// Get singleplayer records
87	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	260	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
88	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;`	261	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;`
89	rows, err := database.DB.Query(sql, user.(models.User).SteamID)	262	rows, err = database.DB.Query(sql, user.(models.User).SteamID)
90	if err != nil {	263	if err != nil {
91	c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))	264	c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
92	return	265	return
@@ -158,7 +331,7 @@ func Profile(c *gin.Context) {
158	CountryCode: user.(models.User).CountryCode,	331	CountryCode: user.(models.User).CountryCode,
159	Titles: user.(models.User).Titles,	332	Titles: user.(models.User).Titles,
160	Links: links,	333	Links: links,
161	Rankings: ProfileRankings{},	334	Rankings: rankings,
162	Records: records,	335	Records: records,
163	},	336	},
164	})	337	})
@@ -197,23 +370,180 @@ func FetchUser(c *gin.Context) {
197	c.JSON(http.StatusNotFound, models.ErrorResponse("User not found."))	370	c.JSON(http.StatusNotFound, models.ErrorResponse("User not found."))
198	return	371	return
199	}	372	}
200	// Get user titles	373	// Get rankings (all maps done in one game)
201	sql = `SELECT t.title_name, t.title_color FROM titles t	374	rankings := ProfileRankings{
202	INNER JOIN user_titles ut ON t.id=ut.title_id WHERE ut.user_id = $1`	375	Overall: ProfileRankingsDetails{},
		376	Singleplayer: ProfileRankingsDetails{},
		377	Cooperative: ProfileRankingsDetails{},
		378	}
		379	// Get total map count
		380	sql = `SELECT count(id), (SELECT count(id) FROM maps m WHERE m.game_id = 2 AND m.is_disabled = false) FROM maps m WHERE m.game_id = 1 AND m.is_disabled = false;`
		381	err = database.DB.QueryRow(sql).Scan(&rankings.Singleplayer.CompletionTotal, &rankings.Cooperative.CompletionTotal)
		382	if err != nil {
		383	c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
		384	return
		385	}
		386	rankings.Overall.CompletionTotal = rankings.Singleplayer.CompletionTotal + rankings.Cooperative.CompletionTotal
		387	// Get user completion count
		388	sql = `SELECT 'records_sp' AS table_name, COUNT(rs.id) AS total_user_scores
		389	FROM public.records_sp rs JOIN (
		390	SELECT mr.map_id, MIN(mr.score_count) AS min_score_count
		391	FROM public.map_routes mr WHERE mr.category_id = 1 GROUP BY mr.map_id
		392	) AS subquery_sp ON rs.map_id = subquery_sp.map_id AND rs.score_count = subquery_sp.min_score_count
		393	WHERE rs.user_id = $1
		394	UNION ALL
		395	SELECT 'records_mp' AS table_name, COUNT(rm.id) AS total_user_scores
		396	FROM public.records_mp rm JOIN (
		397	SELECT mr.map_id, MIN(mr.score_count) AS min_score_count
		398	FROM public.map_routes mr WHERE mr.category_id = 1 GROUP BY mr.map_id
		399	) AS subquery_mp ON rm.map_id = subquery_mp.map_id AND rm.score_count = subquery_mp.min_score_count
		400	WHERE rm.host_id = $1 OR rm.partner_id = $1;`
203	rows, err := database.DB.Query(sql, user.SteamID)	401	rows, err := database.DB.Query(sql, user.SteamID)
204	if err != nil {	402	if err != nil {
205	c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))	403	c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
206	return	404	return
207	}	405	}
208	for rows.Next() {	406	for rows.Next() {
209	var title models.Title	407	var tableName string
210	if err := rows.Scan(&title.Name, &title.Color); err != nil {	408	var completionCount int
		409	err = rows.Scan(&tableName, &completionCount)
		410	if err != nil {
211	c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))	411	c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
212	return	412	return
213	}	413	}
214	user.Titles = append(user.Titles, title)	414	if tableName == "records_sp" {
		415	rankings.Singleplayer.CompletionCount = completionCount
		416	continue
		417	}
		418	if tableName == "records_mp" {
		419	rankings.Cooperative.CompletionCount = completionCount
		420	continue
		421	}
		422	}
		423	rankings.Overall.CompletionCount = rankings.Singleplayer.CompletionCount + rankings.Cooperative.CompletionCount
		424	// Get user ranking placement for singleplayer
		425	sql = `SELECT u.steam_id, COUNT(DISTINCT map_id),
		426	(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),
		427	(SELECT SUM(min_score_count) AS total_min_score_count FROM (
		428	SELECT user_id, MIN(score_count) AS min_score_count FROM records_sp GROUP BY user_id, map_id) AS subquery WHERE user_id = u.steam_id)
		429	FROM records_sp sp JOIN users u ON u.steam_id = sp.user_id GROUP BY u.steam_id, u.user_name
		430	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)
		431	ORDER BY total_min_score_count ASC;`
		432	rows, err = database.DB.Query(sql)
		433	if err != nil {
		434	c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
		435	return
		436	}
		437	placement := 1
		438	for rows.Next() {
		439	var steamID string
		440	var completionCount int
		441	var totalCount int
		442	var userPortalCount int
		443	err = rows.Scan(&steamID, &completionCount, &totalCount, &userPortalCount)
		444	if err != nil {
		445	c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
		446	return
		447	}
		448	if completionCount != totalCount {
		449	placement++
		450	continue
		451	}
		452	if steamID != user.SteamID {
		453	placement++
		454	continue
		455	}
		456	rankings.Singleplayer.Rank = placement
		457	}
		458	// Get user ranking placement for multiplayer
		459	sql = `SELECT u.steam_id, COUNT(DISTINCT map_id),
		460	(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),
		461	(SELECT SUM(min_score_count) AS total_min_score_count FROM (
		462	SELECT host_id, partner_id, MIN(score_count) AS min_score_count FROM records_mp GROUP BY host_id, partner_id, map_id) AS subquery WHERE host_id = u.steam_id OR partner_id = u.steam_id)
		463	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
		464	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)
		465	ORDER BY total_min_score_count ASC;`
		466	rows, err = database.DB.Query(sql)
		467	if err != nil {
		468	c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
		469	return
		470	}
		471	placement = 1
		472	for rows.Next() {
		473	var steamID string
		474	var completionCount int
		475	var totalCount int
		476	var userPortalCount int
		477	err = rows.Scan(&steamID, &completionCount, &totalCount, &userPortalCount)
		478	if err != nil {
		479	c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
		480	return
		481	}
		482	if completionCount != totalCount {
		483	placement++
		484	continue
		485	}
		486	if steamID != user.SteamID {
		487	placement++
		488	continue
		489	}
		490	rankings.Cooperative.Rank = placement
215	}	491	}
216	// TODO: Get rankings (all maps done in one game)	492	// TODO: Get user ranking placement for overall if they qualify
		493	// if (rankings.Singleplayer.Rank != 0) && (rankings.Cooperative.Rank != 0) {
		494	// sql = `SELECT steam_id, SUM(total_min_score_count) AS total_score
		495	// FROM (
		496	// SELECT u.steam_id,
		497	// (SELECT SUM(min_score_count) AS total_min_score_count FROM (
		498	// SELECT
		499	// user_id,
		500	// MIN(score_count) AS min_score_count
		501	// FROM records_sp
		502	// GROUP BY user_id, map_id
		503	// ) AS subquery
		504	// WHERE user_id = u.steam_id) AS total_min_score_count
		505	// FROM records_sp sp
		506	// JOIN users u ON u.steam_id = sp.user_id
		507	// UNION ALL
		508	// SELECT u.steam_id,
		509	// (SELECT SUM(min_score_count) AS total_min_score_count FROM (
		510	// SELECT
		511	// host_id,
		512	// partner_id,
		513	// MIN(score_count) AS min_score_count
		514	// FROM records_mp
		515	// GROUP BY host_id, partner_id, map_id
		516	// ) AS subquery
		517	// WHERE host_id = u.steam_id OR partner_id = u.steam_id) AS total_min_score_count
		518	// FROM records_mp mp
		519	// JOIN users u ON u.steam_id = mp.host_id OR u.steam_id = mp.partner_id
		520	// ) AS combined_scores
		521	// GROUP BY steam_id ORDER BY total_score ASC;`
		522	// rows, err = database.DB.Query(sql)
		523	// if err != nil {
		524	// c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
		525	// return
		526	// }
		527	// placement = 1
		528	// for rows.Next() {
		529	// var steamID string
		530	// var userPortalCount int
		531	// err = rows.Scan(&steamID, &userPortalCount)
		532	// if err != nil {
		533	// c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
		534	// return
		535	// }
		536	// if completionCount != totalCount {
		537	// placement++
		538	// continue
		539	// }
		540	// if steamID != user.SteamID {
		541	// placement++
		542	// continue
		543	// }
		544	// rankings.Cooperative.Rank = placement
		545	// }
		546	// }
217	records := []ProfileRecords{}	547	records := []ProfileRecords{}
218	// Get singleplayer records	548	// Get singleplayer records
219	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	549	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
@@ -290,7 +620,7 @@ func FetchUser(c *gin.Context) {
290	CountryCode: user.CountryCode,	620	CountryCode: user.CountryCode,
291	Titles: user.Titles,	621	Titles: user.Titles,
292	Links: links,	622	Links: links,
293	Rankings: ProfileRankings{},	623	Rankings: rankings,
294	Records: records,	624	Records: records,
295	},	625	},
296	})	626	})