From 5fec69af648e7c5c8a682132c9996f2883d1a99e Mon Sep 17 00:00:00 2001
From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>
Date: Sat, 17 Jun 2023 23:58:23 +0300
Subject: feat: map routes table (#1)

---
 backend/database/init.sql | 25 ++++++++++++-------------
 1 file changed, 12 insertions(+), 13 deletions(-)

(limited to 'backend/database/init.sql')

diff --git a/backend/database/init.sql b/backend/database/init.sql
index 51a4881..871aba2 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -33,14 +33,24 @@ CREATE TABLE maps (
   game_id SMALLINT NOT NULL,
   chapter_id SMALLINT NOT NULL,
   name TEXT NOT NULL,
-  description TEXT NOT NULL,
-  showcase TEXT NOT NULL,
   is_disabled BOOLEAN NOT NULL DEFAULT false,
   PRIMARY KEY (id),
   FOREIGN KEY (game_id) REFERENCES games(id),
   FOREIGN KEY (chapter_id) REFERENCES chapters(id)
 );
 
+CREATE TABLE map_routes (
+  id SMALLSERIAL,
+  map_id SMALLINT NOT NULL,
+  category_id SMALLINT NOT NULL,
+  score_count SMALLINT NOT NULL,
+  description TEXT NOT NULL,
+  showcase TEXT NOT NULL,
+  PRIMARY KEY (id),
+  FOREIGN KEY (map_id) REFERENCES maps(id),
+  FOREIGN KEY (category_id) REFERENCES categories(id)
+);
+
 CREATE TABLE map_history (
   id SMALLSERIAL,
   map_id SMALLINT NOT NULL,
@@ -61,17 +71,6 @@ CREATE TABLE map_ratings (
   FOREIGN KEY (user_id) REFERENCES users(steam_id)
 );
 
-CREATE TABLE map_routers (
-  id SMALLSERIAL,
-  map_id SMALLINT NOT NULL,
-  user_id TEXT,
-  user_name TEXT NOT NULL,
-  score_count SMALLINT NOT NULL,
-  PRIMARY KEY (id),
-  FOREIGN KEY (map_id) REFERENCES maps(id),
-  FOREIGN KEY (user_id) REFERENCES users(steam_id)
-);
-
 CREATE TABLE demos (
   id UUID,
   location_id TEXT NOT NULL,
-- 
cgit v1.2.3


From 40537dace937fc96c851c56f0cb629f36dddfd03 Mon Sep 17 00:00:00 2001
From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>
Date: Tue, 20 Jun 2023 19:55:31 +0300
Subject: feat: improved map summary response, all routes init (#43, #46)

---
 backend/controllers/mapController.go |  52 ++--
 backend/database/history.sql         | 524 +++++++++++++++++------------------
 backend/database/init.sql            |   4 +
 backend/database/route.sql           | 279 +++++++++++++++++++
 backend/models/models.go             |  14 +-
 5 files changed, 570 insertions(+), 303 deletions(-)
 create mode 100644 backend/database/route.sql

(limited to 'backend/database/init.sql')

diff --git a/backend/controllers/mapController.go b/backend/controllers/mapController.go
index 37b8e9b..e46b766 100644
--- a/backend/controllers/mapController.go
+++ b/backend/controllers/mapController.go
@@ -20,60 +20,44 @@ import (
 //	@Router		/maps/{id}/summary [get]
 func FetchMapSummary(c *gin.Context) {
 	id := c.Param("id")
-	// Get map data
-	response := models.MapSummaryResponse{Map: models.Map{}, Summary: models.MapSummary{History: []models.MapHistory{}, Routes: []models.MapRoute{}}}
+	response := models.MapSummaryResponse{Map: models.Map{}, Summary: models.MapSummary{Routes: []models.MapRoute{}}}
 	intID, err := strconv.Atoi(id)
 	if err != nil {
 		c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
 		return
 	}
+	// (
+	// 	SELECT COALESCE(avg(rating), 0.0)
+	// 	FROM route_ratings
+	// 	WHERE map_id = $1
+	// )
+	// Get map data
 	response.Map.ID = intID
-	sql := `SELECT m.id, g.name, c.name, m.name,
-	(
-		SELECT COALESCE(avg(rating), 0.0)
-		FROM map_ratings
-		WHERE map_id = $1
-	)
+	sql := `SELECT m.id, g.name, c.name, m.name
 	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, id).Scan(&response.Map.ID, &response.Map.GameName, &response.Map.ChapterName, &response.Map.MapName, &response.Summary.Rating)
+	err = database.DB.QueryRow(sql, id).Scan(&response.Map.ID, &response.Map.GameName, &response.Map.ChapterName, &response.Map.MapName)
 	if err != nil {
 		c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
 		return
 	}
-	sql = `SELECT user_name, score_count, record_date
-	FROM map_history
-	WHERE map_id = $1
-	ORDER BY record_date ASC`
+	// Get map routes and histories
+	sql = `SELECT c.id, c.name, h.user_name, h.score_count, h.record_date, r.score_count, r.description, r.showcase, COALESCE(avg(rating), 0.0) FROM map_routes r
+    INNER JOIN categories c ON r.category_id = c.id
+    INNER JOIN map_history h ON r.map_id = h.map_id AND r.category_id = h.category_id
+    LEFT JOIN map_ratings rt ON r.map_id = rt.map_id AND r.category_id = rt.category_id 
+	WHERE r.map_id = $1 AND h.score_count = r.score_count GROUP BY c.id, h.user_name, h.score_count, h.record_date, r.score_count, r.description, r.showcase
+	ORDER BY h.record_date ASC;`
 	rows, err := database.DB.Query(sql, id)
 	if err != nil {
 		c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
 		return
 	}
 	for rows.Next() {
-		history := models.MapHistory{}
-		err = rows.Scan(&history.RunnerName, &history.ScoreCount, &history.Date)
-		if err != nil {
-			c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
-			return
-		}
-		response.Summary.History = append(response.Summary.History, history)
-	}
-	sql = `SELECT c.id, c.name, mr.score_count, mr.description, mr.showcase
-	FROM map_routes mr
-	INNER JOIN categories c ON mr.category_id = c.id
-	WHERE mr.map_id = $1
-	ORDER BY mr.score_count DESC`
-	rows, err = database.DB.Query(sql, id)
-	if err != nil {
-		c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
-		return
-	}
-	for rows.Next() {
-		route := models.MapRoute{}
-		err = rows.Scan(&route.Category.ID, &route.Category.Name, &route.ScoreCount, &route.Description, &route.Showcase)
+		route := models.MapRoute{Category: models.Category{}, History: models.MapHistory{}}
+		err = rows.Scan(&route.Category.ID, &route.Category.Name, &route.History.RunnerName, &route.History.ScoreCount, &route.History.Date, &route.ScoreCount, &route.Description, &route.Showcase, &route.Rating)
 		if err != nil {
 			c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
 			return
diff --git a/backend/database/history.sql b/backend/database/history.sql
index 0840bf3..d7c4999 100644
--- a/backend/database/history.sql
+++ b/backend/database/history.sql
@@ -1,279 +1,279 @@
-INSERT INTO map_history(map_id,user_name,score_count,record_date) VALUES
+INSERT INTO map_history(map_id,category_id,user_name,score_count,record_date) VALUES
 -- Portal 2 Singleplayer
 -- 1
-(3,'slmid1995',3,'2011-10-05 00:00:00'),
-(3,'LookLikeAKango',1,'2011-10-06 00:00:00'),
-(3,'Bananasaurus Rex',0,'2011-10-24 00:00:00'),
-(4,'Tyronis',1,'2011-10-05 00:00:00'),
-(4,'Krzyhau',0,'2019-05-10 00:00:00'),
-(5,'LookLikeAKango',2,'2011-10-05 00:00:00'),
-(5,'Jetwash',1,'2013-12-03 00:00:00'),
-(6,'Stimich',4,'2011-10-08 00:00:00'),
-(6,'aepaePolakrn',3,'2011-10-19 00:00:00'),
-(6,'Krzyhau',2,'2020-10-10 00:00:00'),
-(9,'slmid1995',4,'2011-10-05 00:00:00'),
-(9,'Jokie',3,'2011-10-05 00:00:00'),
-(9,'Tyronis',2,'2011-10-05 00:00:00'),
-(9,'sicklebrick',0,'2013-03-13 00:00:00'),
+(3,1,'slmid1995',3,'2011-10-05 00:00:00'),
+(3,1,'LookLikeAKango',1,'2011-10-06 00:00:00'),
+(3,1,'Bananasaurus Rex',0,'2011-10-24 00:00:00'),
+(4,1,'Tyronis',1,'2011-10-05 00:00:00'),
+(4,1,'Krzyhau',0,'2019-05-10 00:00:00'),
+(5,1,'LookLikeAKango',2,'2011-10-05 00:00:00'),
+(5,1,'Jetwash',1,'2013-12-03 00:00:00'),
+(6,1,'Stimich',4,'2011-10-08 00:00:00'),
+(6,1,'aepaePolakrn',3,'2011-10-19 00:00:00'),
+(6,1,'Krzyhau',2,'2020-10-10 00:00:00'),
+(9,1,'slmid1995',4,'2011-10-05 00:00:00'),
+(9,1,'Jokie',3,'2011-10-05 00:00:00'),
+(9,1,'Tyronis',2,'2011-10-05 00:00:00'),
+(9,1,'sicklebrick',0,'2013-03-13 00:00:00'),
 -- 2
-(10,'Paraxade0',2,'2011-04-21 00:00:00'),
-(10,'PerOculos',0,'2011-04-21 00:00:00'),
-(11,'Tyronis',2,'2011-10-05 00:00:00'),
-(11,'Krzyhau',0,'2018-06-09 00:00:00'),
-(12,'slmid1995',2,'2011-10-04 00:00:00'),
-(13,'LookLikeAKango',3,'2011-10-05 00:00:00'),
-(13,'Imanex',2,'2011-12-08 00:00:00'),
-(13,'jyjey',0,'2012-08-22 00:00:00'),
-(15,'Tyronis',2,'2011-10-05 00:00:00'),
-(16,'LookLikeAKango',2,'2011-10-05 00:00:00'),
-(16,'jyjey',0,'2012-08-25 00:00:00'),
-(17,'rocoty',0,'2011-10-05 00:00:00'),
-(17,'Nidboj132',0,'2023-02-05 00:00:00'),
+(10,1,'Paraxade0',2,'2011-04-21 00:00:00'),
+(10,1,'PerOculos',0,'2011-04-21 00:00:00'),
+(11,1,'Tyronis',2,'2011-10-05 00:00:00'),
+(11,1,'Krzyhau',0,'2018-06-09 00:00:00'),
+(12,1,'slmid1995',2,'2011-10-04 00:00:00'),
+(13,1,'LookLikeAKango',3,'2011-10-05 00:00:00'),
+(13,1,'Imanex',2,'2011-12-08 00:00:00'),
+(13,1,'jyjey',0,'2012-08-22 00:00:00'),
+(15,1,'Tyronis',2,'2011-10-05 00:00:00'),
+(16,1,'LookLikeAKango',2,'2011-10-05 00:00:00'),
+(16,1,'jyjey',0,'2012-08-25 00:00:00'),
+(17,1,'rocoty',0,'2011-10-05 00:00:00'),
+(17,1,'Nidboj132',0,'2023-02-05 00:00:00'),
 -- 3
-(18,'The Last Tofus',5,'2011-05-08 00:00:00'),
-(18,'Schlepian',4,'2011-10-08 00:00:00'),
-(18,'szeimartin',3,'2013-10-08 00:00:00'),
-(18,'Krzyhau',2,'2020-05-15 00:00:00'),
-(18,'Krzyhau',0,'2022-07-02 00:00:00'),
-(19,'LookLikeAKango',2,'2011-10-06 00:00:00'),
-(20,'Djinndrache',5,'2011-10-20 00:00:00'),
-(20,'Schlepian',4,'2011-10-30 00:00:00'),
-(20,'Jetwash',3,'2014-09-04 00:00:00'),
-(20,'Krzyhau',2,'2022-04-24 00:00:00'),
-(21,'LookLikeAKango',4,'2011-10-06 00:00:00'),
-(21,'ncla',2,'2011-10-30 00:00:00'),
-(21,'PerOculos',0,'2019-07-08 00:00:00'),
-(22,'Tyronis',0,'2011-10-05 00:00:00'),
-(23,'LookLikeAKango',2,'2011-10-06 00:00:00'),
-(23,'Krzyhau',0,'2018-08-01 00:00:00'),
-(24,'LeviHB',0,'2011-04-30 00:00:00'),
-(25,'Tyronis',0,'2011-10-06 00:00:00'),
-(26,'Schlepian',3,'2011-10-30 00:00:00'),
-(26,'Tyronis',2,'2012-01-08 00:00:00'),
-(26,'PerOculos',0,'2016-06-08 00:00:00'),
+(18,1,'The Last Tofus',5,'2011-05-08 00:00:00'),
+(18,1,'Schlepian',4,'2011-10-08 00:00:00'),
+(18,1,'szeimartin',3,'2013-10-08 00:00:00'),
+(18,1,'Krzyhau',2,'2020-05-15 00:00:00'),
+(18,1,'Krzyhau',0,'2022-07-02 00:00:00'),
+(19,1,'LookLikeAKango',2,'2011-10-06 00:00:00'),
+(20,1,'Djinndrache',5,'2011-10-20 00:00:00'),
+(20,1,'Schlepian',4,'2011-10-30 00:00:00'),
+(20,1,'Jetwash',3,'2014-09-04 00:00:00'),
+(20,1,'Krzyhau',2,'2022-04-24 00:00:00'),
+(21,1,'LookLikeAKango',4,'2011-10-06 00:00:00'),
+(21,1,'ncla',2,'2011-10-30 00:00:00'),
+(21,1,'PerOculos',0,'2019-07-08 00:00:00'),
+(22,1,'Tyronis',0,'2011-10-05 00:00:00'),
+(23,1,'LookLikeAKango',2,'2011-10-06 00:00:00'),
+(23,1,'Krzyhau',0,'2018-08-01 00:00:00'),
+(24,1,'LeviHB',0,'2011-04-30 00:00:00'),
+(25,1,'Tyronis',0,'2011-10-06 00:00:00'),
+(26,1,'Schlepian',3,'2011-10-30 00:00:00'),
+(26,1,'Tyronis',2,'2012-01-08 00:00:00'),
+(26,1,'PerOculos',0,'2016-06-08 00:00:00'),
 -- 4
-(27,'LeviHB',2,'2011-05-01 00:00:00'),
-(27,'PerOculos',0,'2020-07-13 00:00:00'),
-(28,'LeviHB',7,'2011-05-01 00:00:00'),
-(28,'Andy M.J.',2,'2011-10-07 00:00:00'),
-(28,'Krzyhau',0,'2018-05-19 00:00:00'),
-(29,'LeviHB',0,'2011-05-01 00:00:00'),
-(30,'Schlepian',2,'2011-10-30 00:00:00'),
-(31,'Tyronis',0,'2011-10-06 00:00:00'),
+(27,1,'LeviHB',2,'2011-05-01 00:00:00'),
+(27,1,'PerOculos',0,'2020-07-13 00:00:00'),
+(28,1,'LeviHB',7,'2011-05-01 00:00:00'),
+(28,1,'Andy M.J.',2,'2011-10-07 00:00:00'),
+(28,1,'Krzyhau',0,'2018-05-19 00:00:00'),
+(29,1,'LeviHB',0,'2011-05-01 00:00:00'),
+(30,1,'Schlepian',2,'2011-10-30 00:00:00'),
+(31,1,'Tyronis',0,'2011-10-06 00:00:00'),
 -- 5
-(32,'Tyronis',6,'2011-10-21 00:00:00'),
-(32,'Nidboj132',5,'2022-04-24 00:00:00'),
-(33,'Tyronis',7,'2011-10-06 00:00:00'),
-(33,'ISimmo',5,'2011-11-02 00:00:00'),
-(33,'PerOculos',4,'2017-05-30 00:00:00'),
-(34,'Schlepian',3,'2011-11-01 00:00:00'),
-(34,'Krzyhau',2,'2020-10-14 00:00:00'),
-(34,'zach',0,'2022-11-02 00:00:00'),
-(35,'Krank',2,'2012-07-28 00:00:00'),
+(32,1,'Tyronis',6,'2011-10-21 00:00:00'),
+(32,1,'Nidboj132',5,'2022-04-24 00:00:00'),
+(33,1,'Tyronis',7,'2011-10-06 00:00:00'),
+(33,1,'ISimmo',5,'2011-11-02 00:00:00'),
+(33,1,'PerOculos',4,'2017-05-30 00:00:00'),
+(34,1,'Schlepian',3,'2011-11-01 00:00:00'),
+(34,1,'Krzyhau',2,'2020-10-14 00:00:00'),
+(34,1,'zach',0,'2022-11-02 00:00:00'),
+(35,1,'Krank',2,'2012-07-28 00:00:00'),
 -- 6
-(36,'Tyronis',6,'2011-10-06 00:00:00'),
-(36,'CalmlyFrenetic',5,'2011-10-09 00:00:00'),
-(36,'sicklebrick',4,'2012-09-13 00:00:00'),
-(36,'Nidboj132',2,'2023-03-04 00:00:00'),
-(37,'LookLikeAKango',7,'2011-10-06 00:00:00'),
-(37,'Schlepian',6,'2011-11-01 00:00:00'),
-(37,'Tyronis',5,'2012-01-28 00:00:00'),
-(37,'Nidboj132',4,'2021-08-22 00:00:00'),
-(38,'Andy M.J.',2,'2011-10-06 00:00:00'),
-(38,'Sanguine Dagger',0,'2012-03-19 00:00:00'),
-(39,'Lambda Core',6,'2011-05-13 00:00:00'),
-(39,'The Last Tofus',5,'2011-05-13 00:00:00'),
-(39,'LookLikeAKango',4,'2011-10-16 00:00:00'),
-(39,'Kittaye',3,'2013-03-25 00:00:00'),
-(40,'LookLikeAKango',7,'2011-10-07 00:00:00'),
-(40,'Schlepian',6,'2011-11-05 00:00:00'),
-(40,'Kittaye',4,'2013-04-01 00:00:00'),
-(40,'Kittaye',3,'2014-09-13 00:00:00'),
-(40,'szeimartin',2,'2014-09-13 00:00:00'),
-(40,'Kittaye',0,'2014-09-15 00:00:00'),
-(41,'CalmlyFrenetic',7,'2011-10-09 00:00:00'),
-(41,'Jaso',6,'2011-10-11 00:00:00'),
-(41,'Krank',5,'2012-07-17 00:00:00'),
+(36,1,'Tyronis',6,'2011-10-06 00:00:00'),
+(36,1,'CalmlyFrenetic',5,'2011-10-09 00:00:00'),
+(36,1,'sicklebrick',4,'2012-09-13 00:00:00'),
+(36,1,'Nidboj132',2,'2023-03-04 00:00:00'),
+(37,1,'LookLikeAKango',7,'2011-10-06 00:00:00'),
+(37,1,'Schlepian',6,'2011-11-01 00:00:00'),
+(37,1,'Tyronis',5,'2012-01-28 00:00:00'),
+(37,1,'Nidboj132',4,'2021-08-22 00:00:00'),
+(38,1,'Andy M.J.',2,'2011-10-06 00:00:00'),
+(38,1,'Sanguine Dagger',0,'2012-03-19 00:00:00'),
+(39,1,'Lambda Core',6,'2011-05-13 00:00:00'),
+(39,1,'The Last Tofus',5,'2011-05-13 00:00:00'),
+(39,1,'LookLikeAKango',4,'2011-10-16 00:00:00'),
+(39,1,'Kittaye',3,'2013-03-25 00:00:00'),
+(40,1,'LookLikeAKango',7,'2011-10-07 00:00:00'),
+(40,1,'Schlepian',6,'2011-11-05 00:00:00'),
+(40,1,'Kittaye',4,'2013-04-01 00:00:00'),
+(40,1,'Kittaye',3,'2014-09-13 00:00:00'),
+(40,1,'szeimartin',2,'2014-09-13 00:00:00'),
+(40,1,'Kittaye',0,'2014-09-15 00:00:00'),
+(41,1,'CalmlyFrenetic',7,'2011-10-09 00:00:00'),
+(41,1,'Jaso',6,'2011-10-11 00:00:00'),
+(41,1,'Krank',5,'2012-07-17 00:00:00'),
 -- 7
-(42,'LookLikeAKango',4,'2011-05-17 00:00:00'),
-(42,'ISimmo',2,'2011-11-07 00:00:00'),
-(43,'lmao4ever',5,'2011-10-30 00:00:00'),
-(43,'Jaso',2,'2011-11-09 00:00:00'),
-(43,'feliser',0,'2022-06-26 00:00:00'),
-(44,'LookLikeAKango',18,'2011-10-07 00:00:00'),
-(44,'Tyronis',13,'2011-10-30 00:00:00'),
-(44,'Tyronis',12,'2011-11-10 00:00:00'),
-(44,'Jetwash',11,'2017-06-12 00:00:00'),
-(44,'Krzyhau',9,'2022-01-02 00:00:00'),
-(45,'LookLikeAKango',23,'2011-10-08 00:00:00'),
-(45,'CalmlyFrenetic',22,'2011-10-09 00:00:00'),
-(45,'cgreactor',17,'2011-10-09 00:00:00'),
-(45,'CalmlyFrenetic',16,'2011-10-10 00:00:00'),
-(45,'LookLikeAKango',15,'2011-10-19 00:00:00'),
-(45,'Jaso',12,'2012-07-19 00:00:00'),
-(45,'Krank',10,'2013-01-31 00:00:00'),
-(45,'Kittaye',7,'2013-04-04 00:00:00'),
-(45,'PerOculos',4,'2014-09-13 00:00:00'),
+(42,1,'LookLikeAKango',4,'2011-05-17 00:00:00'),
+(42,1,'ISimmo',2,'2011-11-07 00:00:00'),
+(43,1,'lmao4ever',5,'2011-10-30 00:00:00'),
+(43,1,'Jaso',2,'2011-11-09 00:00:00'),
+(43,1,'feliser',0,'2022-06-26 00:00:00'),
+(44,1,'LookLikeAKango',18,'2011-10-07 00:00:00'),
+(44,1,'Tyronis',13,'2011-10-30 00:00:00'),
+(44,1,'Tyronis',12,'2011-11-10 00:00:00'),
+(44,1,'Jetwash',11,'2017-06-12 00:00:00'),
+(44,1,'Krzyhau',9,'2022-01-02 00:00:00'),
+(45,1,'LookLikeAKango',23,'2011-10-08 00:00:00'),
+(45,1,'CalmlyFrenetic',22,'2011-10-09 00:00:00'),
+(45,1,'cgreactor',17,'2011-10-09 00:00:00'),
+(45,1,'CalmlyFrenetic',16,'2011-10-10 00:00:00'),
+(45,1,'LookLikeAKango',15,'2011-10-19 00:00:00'),
+(45,1,'Jaso',12,'2012-07-19 00:00:00'),
+(45,1,'Krank',10,'2013-01-31 00:00:00'),
+(45,1,'Kittaye',7,'2013-04-04 00:00:00'),
+(45,1,'PerOculos',4,'2014-09-13 00:00:00'),
 -- 8
-(46,'sparkle1princess',6,'2012-03-24 00:00:00'),
-(46,'Krzyhau',2,'2019-11-21 00:00:00'),
-(47,'holydevel',2,'2011-10-06 00:00:00'),
-(47,'JesusCatFace',0,'2015-01-16 00:00:00'),
-(48,'LookLikeAKango',5,'2011-10-08 00:00:00'),
-(48,'Tyronis',2,'2011-10-08 00:00:00'),
-(48,'adzicents',0,'2011-10-09 00:00:00'),
-(49,'adzicents',4,'2011-10-07 00:00:00'),
-(49,'Schlepian',2,'2011-10-08 00:00:00'),
-(49,'Nidboj132',0,'2022-09-26 00:00:00'),
-(50,'LookLikeAKango',4,'2011-10-08 00:00:00'),
-(50,'Tyronis',2,'2011-10-11 00:00:00'),
-(50,'sicklebrick',0,'2013-03-20 00:00:00'),
-(51,'Andy M.J.',3,'2011-10-08 00:00:00'),
-(51,'LookLikeAKango',2,'2011-10-20 00:00:00'),
-(52,'Jaso',0,'2011-10-10 00:00:00'),
-(53,'LookLikeAKango',9,'2011-10-08 00:00:00'),
-(53,'LookLikeAKango',2,'2011-10-20 00:00:00'),
-(53,'Schlepian',0,'2011-11-06 00:00:00'),
-(54,'LookLikeAKango',7,'2011-06-01 00:00:00'),
-(54,'Jaso',6,'2011-10-09 00:00:00'),
-(54,'Schlepian',5,'2011-11-06 00:00:00'),
-(54,'Spyrunite',4,'2012-08-30 00:00:00'),
-(54,'Krzyhau',3,'2019-04-22 00:00:00'),
-(55,'LookLikeAKango',7,'2011-10-08 00:00:00'),
-(55,'CalmlyFrenetic',3,'2011-10-09 00:00:00'),
-(55,'Jaso',2,'2011-11-26 00:00:00'),
-(55,'PerOculos',0,'2021-02-06 00:00:00'),
-(56,'CalmlyFrenetic',9,'2011-10-08 00:00:00'),
-(56,'LookLikeAKango',5,'2011-10-09 00:00:00'),
-(56,'CalmlyFrenetic',4,'2011-10-09 00:00:00'),
-(56,'Jetwash',2,'2014-09-05 00:00:00'),
+(46,1,'sparkle1princess',6,'2012-03-24 00:00:00'),
+(46,1,'Krzyhau',2,'2019-11-21 00:00:00'),
+(47,1,'holydevel',2,'2011-10-06 00:00:00'),
+(47,1,'JesusCatFace',0,'2015-01-16 00:00:00'),
+(48,1,'LookLikeAKango',5,'2011-10-08 00:00:00'),
+(48,1,'Tyronis',2,'2011-10-08 00:00:00'),
+(48,1,'adzicents',0,'2011-10-09 00:00:00'),
+(49,1,'adzicents',4,'2011-10-07 00:00:00'),
+(49,1,'Schlepian',2,'2011-10-08 00:00:00'),
+(49,1,'Nidboj132',0,'2022-09-26 00:00:00'),
+(50,1,'LookLikeAKango',4,'2011-10-08 00:00:00'),
+(50,1,'Tyronis',2,'2011-10-11 00:00:00'),
+(50,1,'sicklebrick',0,'2013-03-20 00:00:00'),
+(51,1,'Andy M.J.',3,'2011-10-08 00:00:00'),
+(51,1,'LookLikeAKango',2,'2011-10-20 00:00:00'),
+(52,1,'Jaso',0,'2011-10-10 00:00:00'),
+(53,1,'LookLikeAKango',9,'2011-10-08 00:00:00'),
+(53,1,'LookLikeAKango',2,'2011-10-20 00:00:00'),
+(53,1,'Schlepian',0,'2011-11-06 00:00:00'),
+(54,1,'LookLikeAKango',7,'2011-06-01 00:00:00'),
+(54,1,'Jaso',6,'2011-10-09 00:00:00'),
+(54,1,'Schlepian',5,'2011-11-06 00:00:00'),
+(54,1,'Spyrunite',4,'2012-08-30 00:00:00'),
+(54,1,'Krzyhau',3,'2019-04-22 00:00:00'),
+(55,1,'LookLikeAKango',7,'2011-10-08 00:00:00'),
+(55,1,'CalmlyFrenetic',3,'2011-10-09 00:00:00'),
+(55,1,'Jaso',2,'2011-11-26 00:00:00'),
+(55,1,'PerOculos',0,'2021-02-06 00:00:00'),
+(56,1,'CalmlyFrenetic',9,'2011-10-08 00:00:00'),
+(56,1,'LookLikeAKango',5,'2011-10-09 00:00:00'),
+(56,1,'CalmlyFrenetic',4,'2011-10-09 00:00:00'),
+(56,1,'Jetwash',2,'2014-09-05 00:00:00'),
 -- 9
-(57,'JNS',7,'2011-07-21 00:00:00'),
-(57,'Krank',5,'2012-07-29 00:00:00'),
-(57,'Krzyhau',0,'2017-10-29 00:00:00'),
-(58,'Stimich',2,'2011-10-11 00:00:00'),
-(59,'Isimmo',7,'2011-11-04 00:00:00'),
-(59,'sicklebrick',6,'2013-03-20 00:00:00'),
-(60,'CalmlyFrenetic',7,'2011-10-19 00:00:00'),
-(60,'Tyronis',6,'2011-11-01 00:00:00'),
+(57,1,'JNS',7,'2011-07-21 00:00:00'),
+(57,1,'Krank',5,'2012-07-29 00:00:00'),
+(57,1,'Krzyhau',0,'2017-10-29 00:00:00'),
+(58,1,'Stimich',2,'2011-10-11 00:00:00'),
+(59,1,'Isimmo',7,'2011-11-04 00:00:00'),
+(59,1,'sicklebrick',6,'2013-03-20 00:00:00'),
+(60,1,'CalmlyFrenetic',7,'2011-10-19 00:00:00'),
+(60,1,'Tyronis',6,'2011-11-01 00:00:00'),
 -- Portal 2 Cooperative
 -- 1
-(63,'Mathias123961 & Sir Spawn Alot',0,'2011-08-01 00:00:00'),
-(64,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
-(64,'Chubfish & Exhale',2,'2011-11-01 00:00:00'),
-(65,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
-(65,'Nidboj132 & Oryn',3,'2022-02-03 00:00:00'),
-(66,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
-(66,'Schlepian & Chubfish',2,'2011-10-01 00:00:00'),
-(67,'Mathias123961 & Sir Spawn Alot',0,'2011-08-01 00:00:00'),
-(68,'Mathias123961 & Sir Spawn Alot',0,'2011-08-01 00:00:00'),
+(63,1,'Mathias123961 & Sir Spawn Alot',0,'2011-08-01 00:00:00'),
+(64,1,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
+(64,1,'Chubfish & Exhale',2,'2011-11-01 00:00:00'),
+(65,1,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
+(65,1,'Nidboj132 & Oryn',3,'2022-02-03 00:00:00'),
+(66,1,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
+(66,1,'Schlepian & Chubfish',2,'2011-10-01 00:00:00'),
+(67,1,'Mathias123961 & Sir Spawn Alot',0,'2011-08-01 00:00:00'),
+(68,1,'Mathias123961 & Sir Spawn Alot',0,'2011-08-01 00:00:00'),
 -- 2
-(69,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
-(70,'Mathias123961 & Sir Spawn Alot',6,'2011-08-01 00:00:00'),
-(70,'Schlepian & Chubfish',4,'2011-10-01 00:00:00'),
-(70,'Gocnak & z1mb0bw4y',2,'2012-08-03 00:00:00'),
-(70,'DM_ & VEGA',0,'2017-10-01 00:00:00'),
-(71,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
-(71,'Mathias123961 & Sir Spawn Alot',0,'2011-08-01 00:00:00'),
-(72,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
-(72,'Schlepian & LongJohnDickWeed',2,'2011-10-01 00:00:00'),
-(73,'Stimich & HiTMaRkS',9,'2011-05-09 00:00:00'),
-(73,'Mathias123961 & Sir Spawn Alot',8,'2011-08-01 00:00:00'),
-(73,'Schlepian & Lemonsunshine',7,'2011-11-01 00:00:00'),
-(73,'DM_ & LsDK_',6,'2018-01-01 00:00:00'),
-(73,'Krzyhau & Klooger',4,'2018-11-01 00:00:00'),
-(74,'Mathias123961 & Sir Spawn Alot',5,'2011-08-01 00:00:00'),
-(74,'Stimich & Pitkakorva',7,'2011-10-11 00:00:00'),
-(74,'Schlepian & Isimmo',3,'2011-10-28 00:00:00'),
-(74,'Zypeh & szeimartin',2,'2013-11-01 00:00:00'),
-(75,'Mathias123961 & Sir Spawn Alot',5,'2011-08-01 00:00:00'),
-(75,'Schlepian & Urination',4,'2011-10-01 00:00:00'),
-(75,'Schlepian & Lemonsunshine',2,'2012-02-01 00:00:00'),
-(75,'DM_ & follon',0,'2015-04-01 00:00:00'),
-(76,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
-(76,'Chubfish & Exhale',0,'2011-12-01 00:00:00'),
+(69,1,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
+(70,1,'Mathias123961 & Sir Spawn Alot',6,'2011-08-01 00:00:00'),
+(70,1,'Schlepian & Chubfish',4,'2011-10-01 00:00:00'),
+(70,1,'Gocnak & z1mb0bw4y',2,'2012-08-03 00:00:00'),
+(70,1,'DM_ & VEGA',0,'2017-10-01 00:00:00'),
+(71,1,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
+(71,1,'Mathias123961 & Sir Spawn Alot',0,'2011-08-01 00:00:00'),
+(72,1,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
+(72,1,'Schlepian & LongJohnDickWeed',2,'2011-10-01 00:00:00'),
+(73,1,'Stimich & HiTMaRkS',9,'2011-05-09 00:00:00'),
+(73,1,'Mathias123961 & Sir Spawn Alot',8,'2011-08-01 00:00:00'),
+(73,1,'Schlepian & Lemonsunshine',7,'2011-11-01 00:00:00'),
+(73,1,'DM_ & LsDK_',6,'2018-01-01 00:00:00'),
+(73,1,'Krzyhau & Klooger',4,'2018-11-01 00:00:00'),
+(74,1,'Mathias123961 & Sir Spawn Alot',5,'2011-08-01 00:00:00'),
+(74,1,'Stimich & Pitkakorva',7,'2011-10-11 00:00:00'),
+(74,1,'Schlepian & Isimmo',3,'2011-10-28 00:00:00'),
+(74,1,'Zypeh & szeimartin',2,'2013-11-01 00:00:00'),
+(75,1,'Mathias123961 & Sir Spawn Alot',5,'2011-08-01 00:00:00'),
+(75,1,'Schlepian & Urination',4,'2011-10-01 00:00:00'),
+(75,1,'Schlepian & Lemonsunshine',2,'2012-02-01 00:00:00'),
+(75,1,'DM_ & follon',0,'2015-04-01 00:00:00'),
+(76,1,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
+(76,1,'Chubfish & Exhale',0,'2011-12-01 00:00:00'),
 -- 3
-(77,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
-(78,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
-(78,'DM_ & marK',3,'2016-11-01 00:00:00'),
-(78,'Nidboj132 & Oryn',2,'2021-09-04 00:00:00'),
-(79,'ganonscrub & ?',5,'2011-04-01 00:00:00'),
-(79,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
-(79,'Chubfish & Exhale',2,'2012-08-04 00:00:00'),
-(80,'Mathias123961 & Sir Spawn Alot',5,'2011-08-01 00:00:00'),
-(80,'Chubfish & Exhale',4,'2011-12-01 00:00:00'),
-(81,'Mathias123961 & Sir Spawn Alot',7,'2011-08-01 00:00:00'),
-(81,'Schlepian & Lemonsunshine',6,'2011-10-01 00:00:00'),
-(81,'takz & dawn',5,'2011-11-01 00:00:00'),
-(81,'Nidboj132 & Oryn',4,'2021-03-25 00:00:00'),
-(82,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
-(83,'Mathias123961 & Sir Spawn Alot',5,'2011-08-01 00:00:00'),
-(83,'Schlepian & Lemonsunshine',2,'2011-10-01 00:00:00'),
-(83,'Chubfish & Exhale',0,'2011-12-01 00:00:00'),
-(84,'Mathias123961 & Sir Spawn Alot',6,'2011-08-01 00:00:00'),
-(84,'Schlepian & Chubfish',4,'2011-10-01 00:00:00'),
-(84,'Chubfish & Exhale',2,'2012-01-01 00:00:00'),
-(84,'DM_ & wS',0,'2015-05-01 00:00:00'),
+(77,1,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
+(78,1,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
+(78,1,'DM_ & marK',3,'2016-11-01 00:00:00'),
+(78,1,'Nidboj132 & Oryn',2,'2021-09-04 00:00:00'),
+(79,1,'ganonscrub & ?',5,'2011-04-01 00:00:00'),
+(79,1,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
+(79,1,'Chubfish & Exhale',2,'2012-08-04 00:00:00'),
+(80,1,'Mathias123961 & Sir Spawn Alot',5,'2011-08-01 00:00:00'),
+(80,1,'Chubfish & Exhale',4,'2011-12-01 00:00:00'),
+(81,1,'Mathias123961 & Sir Spawn Alot',7,'2011-08-01 00:00:00'),
+(81,1,'Schlepian & Lemonsunshine',6,'2011-10-01 00:00:00'),
+(81,1,'takz & dawn',5,'2011-11-01 00:00:00'),
+(81,1,'Nidboj132 & Oryn',4,'2021-03-25 00:00:00'),
+(82,1,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
+(83,1,'Mathias123961 & Sir Spawn Alot',5,'2011-08-01 00:00:00'),
+(83,1,'Schlepian & Lemonsunshine',2,'2011-10-01 00:00:00'),
+(83,1,'Chubfish & Exhale',0,'2011-12-01 00:00:00'),
+(84,1,'Mathias123961 & Sir Spawn Alot',6,'2011-08-01 00:00:00'),
+(84,1,'Schlepian & Chubfish',4,'2011-10-01 00:00:00'),
+(84,1,'Chubfish & Exhale',2,'2012-01-01 00:00:00'),
+(84,1,'DM_ & wS',0,'2015-05-01 00:00:00'),
 -- 4
-(85,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
-(85,'Chubfish & Exhale',0,'2011-10-01 00:00:00'),
-(86,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
-(86,'Chubfish & Exhale',0,'2011-12-01 00:00:00'),
-(87,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
-(87,'Schlepian & Gopherdude',2,'2011-10-01 00:00:00'),
-(87,'DM_ & follon',0,'2015-04-01 00:00:00'),
-(88,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
-(88,'Schlepian & Gopherdude',0,'2011-10-01 00:00:00'),
-(89,'Mathias123961 & Sir Spawn Alot',0,'2011-08-01 00:00:00'),
-(90,'Mathias123961 & Sir Spawn Alot',4,'2011-09-01 00:00:00'),
-(90,'Schlepian & Urination',2,'2011-10-01 00:00:00'),
-(90,'Klooger & Jetwash',0,'2016-08-01 00:00:00'),
-(91,'Mathias123961 & Sir Spawn Alot',2,'2011-08-01 00:00:00'),
-(91,'Undead & Zypeh',0,'2013-05-19 00:00:00'),
-(92,'txx478 & ?',5,'2011-05-01 00:00:00'),
-(92,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
-(92,'Schlepian & Gopherdude',2,'2011-10-01 00:00:00'),
-(92,'ncla & takz',0,'2012-02-01 00:00:00'),
-(93,'Mathias123961 & Sir Spawn Alot',2,'2011-08-01 00:00:00'),
-(93,'Schlepian & Gopherdude',0,'2011-10-01 00:00:00'),
+(85,1,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
+(85,1,'Chubfish & Exhale',0,'2011-10-01 00:00:00'),
+(86,1,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
+(86,1,'Chubfish & Exhale',0,'2011-12-01 00:00:00'),
+(87,1,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
+(87,1,'Schlepian & Gopherdude',2,'2011-10-01 00:00:00'),
+(87,1,'DM_ & follon',0,'2015-04-01 00:00:00'),
+(88,1,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
+(88,1,'Schlepian & Gopherdude',0,'2011-10-01 00:00:00'),
+(89,1,'Mathias123961 & Sir Spawn Alot',0,'2011-08-01 00:00:00'),
+(90,1,'Mathias123961 & Sir Spawn Alot',4,'2011-09-01 00:00:00'),
+(90,1,'Schlepian & Urination',2,'2011-10-01 00:00:00'),
+(90,1,'Klooger & Jetwash',0,'2016-08-01 00:00:00'),
+(91,1,'Mathias123961 & Sir Spawn Alot',2,'2011-08-01 00:00:00'),
+(91,1,'Undead & Zypeh',0,'2013-05-19 00:00:00'),
+(92,1,'txx478 & ?',5,'2011-05-01 00:00:00'),
+(92,1,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
+(92,1,'Schlepian & Gopherdude',2,'2011-10-01 00:00:00'),
+(92,1,'ncla & takz',0,'2012-02-01 00:00:00'),
+(93,1,'Mathias123961 & Sir Spawn Alot',2,'2011-08-01 00:00:00'),
+(93,1,'Schlepian & Gopherdude',0,'2011-10-01 00:00:00'),
 -- 5
-(94,'Chubfish & Exhale',2,'2011-10-01 00:00:00'),
-(94,'Klooger & Imanex',0,'2013-08-01 00:00:00'),
-(95,'Schlepian & Issimoi',2,'2011-10-01 00:00:00'),
-(96,'ThePortalPatrol & ?',4,'2011-04-01 00:00:00'),
-(96,'sparkle1princess & Zypeh',2,'2014-01-01 00:00:00'),
-(97,'Stimich & HiTMaRkS',7,'2011-05-13 00:00:00'),
-(97,'Schlepian & Lemonsunshine',4,'2011-10-01 00:00:00'),
-(97,'DM_ & wS',2,'2014-05-01 00:00:00'),
-(98,'Imanex & 00svo',0,'2011-11-01 00:00:00'),
-(99,'Schlepian & Gopherdude',3,'2011-10-01 00:00:00'),
-(99,'Imanex & Klooger',2,'2013-08-01 00:00:00'),
-(99,'DM_ & wS',0,'2015-05-01 00:00:00'),
-(100,'Schlepian & Bananasaurus Rex',0,'2011-10-01 00:00:00'),
-(101,'Chubfish & Exhale',2,'2011-12-01 00:00:00'),
-(101,'DM_ & follon',0,'2015-04-01 00:00:00'),
+(94,1,'Chubfish & Exhale',2,'2011-10-01 00:00:00'),
+(94,1,'Klooger & Imanex',0,'2013-08-01 00:00:00'),
+(95,1,'Schlepian & Issimoi',2,'2011-10-01 00:00:00'),
+(96,1,'ThePortalPatrol & ?',4,'2011-04-01 00:00:00'),
+(96,1,'sparkle1princess & Zypeh',2,'2014-01-01 00:00:00'),
+(97,1,'Stimich & HiTMaRkS',7,'2011-05-13 00:00:00'),
+(97,1,'Schlepian & Lemonsunshine',4,'2011-10-01 00:00:00'),
+(97,1,'DM_ & wS',2,'2014-05-01 00:00:00'),
+(98,1,'Imanex & 00svo',0,'2011-11-01 00:00:00'),
+(99,1,'Schlepian & Gopherdude',3,'2011-10-01 00:00:00'),
+(99,1,'Imanex & Klooger',2,'2013-08-01 00:00:00'),
+(99,1,'DM_ & wS',0,'2015-05-01 00:00:00'),
+(100,1,'Schlepian & Bananasaurus Rex',0,'2011-10-01 00:00:00'),
+(101,1,'Chubfish & Exhale',2,'2011-12-01 00:00:00'),
+(101,1,'DM_ & follon',0,'2015-04-01 00:00:00'),
 -- 6
-(102,'dawn & takz',3,'2011-11-18 00:00:00'),
-(102,'Chubfish & Exhale',2,'2012-01-01 00:00:00'),
-(102,'Imanex & Klooger',0,'2013-08-01 00:00:00'),
-(103,'Schlepian & Lemonsunshine',0,'2011-10-01 00:00:00'),
-(104,'Schlepian & Lemonsunshine',0,'2011-10-01 00:00:00'),
-(105,'Blaizerazer & ?',8,'2011-10-01 00:00:00'),
-(105,'Schlepian & Lemonsunshine',5,'2011-11-01 00:00:00'),
-(105,'Imanex & Klooger',4,'2013-08-01 00:00:00'),
-(105,'DM_ & wS',3,'2014-05-01 00:00:00'),
-(105,'DM_ & follon',2,'2015-04-01 00:00:00'),
-(106,'Schlepian & Bananasaurus Rex',4,'2011-10-01 00:00:00'),
-(106,'Gig & takz',3,'2012-06-01 00:00:00'),
-(106,'Imanex & Klooger',0,'2013-06-01 00:00:00'),
-(107,'Chubfish & Exhale',2,'2011-10-01 00:00:00'),
-(107,'DM_ & follon',0,'2015-04-01 00:00:00'),
-(108,'DaFox & P',0,'2011-12-01 00:00:00'),
-(109,'Schlepian & Tyronis',5,'2011-10-01 00:00:00'),
-(109,'Chubfish & Exhale',0,'2011-12-01 00:00:00'),
-(110,'Tyronis & mr.bob806',15,'2011-10-01 00:00:00'),
-(110,'Schlepian & Chubfish',6,'2011-11-01 00:00:00'),
-(110,'00svo & z1mb0bw4y',5,'2012-08-08 00:00:00'),
-(110,'00svo & z1mb0bw4y',4,'2012-08-10 00:00:00'),
-(110,'Klooger & z1mb0bw4y',2,'2014-02-01 00:00:00'),
-(110,'DM_ & follon',0,'2015-04-01 00:00:00');
\ No newline at end of file
+(102,1,'dawn & takz',3,'2011-11-18 00:00:00'),
+(102,1,'Chubfish & Exhale',2,'2012-01-01 00:00:00'),
+(102,1,'Imanex & Klooger',0,'2013-08-01 00:00:00'),
+(103,1,'Schlepian & Lemonsunshine',0,'2011-10-01 00:00:00'),
+(104,1,'Schlepian & Lemonsunshine',0,'2011-10-01 00:00:00'),
+(105,1,'Blaizerazer & ?',8,'2011-10-01 00:00:00'),
+(105,1,'Schlepian & Lemonsunshine',5,'2011-11-01 00:00:00'),
+(105,1,'Imanex & Klooger',4,'2013-08-01 00:00:00'),
+(105,1,'DM_ & wS',3,'2014-05-01 00:00:00'),
+(105,1,'DM_ & follon',2,'2015-04-01 00:00:00'),
+(106,1,'Schlepian & Bananasaurus Rex',4,'2011-10-01 00:00:00'),
+(106,1,'Gig & takz',3,'2012-06-01 00:00:00'),
+(106,1,'Imanex & Klooger',0,'2013-06-01 00:00:00'),
+(107,1,'Chubfish & Exhale',2,'2011-10-01 00:00:00'),
+(107,1,'DM_ & follon',0,'2015-04-01 00:00:00'),
+(108,1,'DaFox & P',0,'2011-12-01 00:00:00'),
+(109,1,'Schlepian & Tyronis',5,'2011-10-01 00:00:00'),
+(109,1,'Chubfish & Exhale',0,'2011-12-01 00:00:00'),
+(110,1,'Tyronis & mr.bob806',15,'2011-10-01 00:00:00'),
+(110,1,'Schlepian & Chubfish',6,'2011-11-01 00:00:00'),
+(110,1,'00svo & z1mb0bw4y',5,'2012-08-08 00:00:00'),
+(110,1,'00svo & z1mb0bw4y',4,'2012-08-10 00:00:00'),
+(110,1,'Klooger & z1mb0bw4y',2,'2014-02-01 00:00:00'),
+(110,1,'DM_ & follon',0,'2015-04-01 00:00:00');
\ No newline at end of file
diff --git a/backend/database/init.sql b/backend/database/init.sql
index 871aba2..76c3aa6 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -54,20 +54,24 @@ CREATE TABLE map_routes (
 CREATE TABLE map_history (
   id SMALLSERIAL,
   map_id SMALLINT NOT NULL,
+  category_id SMALLINT NOT NULL,
   user_name TEXT NOT NULL,
   score_count SMALLINT NOT NULL,
   record_date TIMESTAMP NOT NULL,
   PRIMARY KEY (id),
+  FOREIGN KEY (category_id) REFERENCES categories(id),
   FOREIGN KEY (map_id) REFERENCES maps(id)
 );
 
 CREATE TABLE map_ratings (
   id SERIAL,
   map_id SMALLINT NOT NULL,
+  category_id SMALLINT NOT NULL,
   user_id TEXT NOT NULL,
   rating SMALLINT NOT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (map_id) REFERENCES maps(id),
+  FOREIGN KEY (category_id) REFERENCES categories(id),
   FOREIGN KEY (user_id) REFERENCES users(steam_id)
 );
 
diff --git a/backend/database/route.sql b/backend/database/route.sql
new file mode 100644
index 0000000..6f090c7
--- /dev/null
+++ b/backend/database/route.sql
@@ -0,0 +1,279 @@
+INSERT INTO map_routes(map_id,category_id,score_count,description,showcase) VALUES
+-- Portal 2 Singleplayer
+-- 1
+(3,1,3,'',''),
+(3,1,1,'',''),
+(3,1,0,'',''),
+(4,1,1,'',''),
+(4,1,0,'',''),
+(5,1,2,'',''),
+(5,1,1,'',''),
+(6,1,4,'',''),
+(6,1,3,'',''),
+(6,1,2,'',''),
+(9,1,4,'',''),
+(9,1,3,'',''),
+(9,1,2,'',''),
+(9,1,0,'',''),
+-- 2
+(10,1,2,'',''),
+(10,1,0,'',''),
+(11,1,2,'',''),
+(11,1,0,'',''),
+(12,1,2,'',''),
+(13,1,3,'',''),
+(13,1,2,'',''),
+(13,1,0,'',''),
+(15,1,2,'',''),
+(16,1,2,'',''),
+(16,1,0,'',''),
+(17,1,0,'',''),
+(17,1,0,'',''),
+-- 3
+(18,1,5,'',''),
+(18,1,4,'',''),
+(18,1,3,'',''),
+(18,1,2,'',''),
+(18,1,0,'',''),
+(19,1,2,'',''),
+(20,1,5,'',''),
+(20,1,4,'',''),
+(20,1,3,'',''),
+(20,1,2,'',''),
+(21,1,4,'',''),
+(21,1,2,'',''),
+(21,1,0,'',''),
+(22,1,0,'',''),
+(23,1,2,'',''),
+(23,1,0,'',''),
+(24,1,0,'',''),
+(25,1,0,'',''),
+(26,1,3,'',''),
+(26,1,2,'',''),
+(26,1,0,'',''),
+-- 4
+(27,1,2,'',''),
+(27,1,0,'',''),
+(28,1,7,'',''),
+(28,1,2,'',''),
+(28,1,0,'',''),
+(29,1,0,'',''),
+(30,1,2,'',''),
+(31,1,0,'',''),
+-- 5
+(32,1,6,'',''),
+(32,1,5,'',''),
+(33,1,7,'',''),
+(33,1,5,'',''),
+(33,1,4,'',''),
+(34,1,3,'',''),
+(34,1,2,'',''),
+(34,1,0,'',''),
+(35,1,2,'',''),
+-- 6
+(36,1,6,'',''),
+(36,1,5,'',''),
+(36,1,4,'',''),
+(36,1,2,'',''),
+(37,1,7,'',''),
+(37,1,6,'',''),
+(37,1,5,'',''),
+(37,1,4,'',''),
+(38,1,2,'',''),
+(38,1,0,'',''),
+(39,1,6,'',''),
+(39,1,5,'',''),
+(39,1,4,'',''),
+(39,1,3,'',''),
+(40,1,7,'',''),
+(40,1,6,'',''),
+(40,1,4,'',''),
+(40,1,3,'',''),
+(40,1,2,'',''),
+(40,1,0,'',''),
+(41,1,7,'',''),
+(41,1,6,'',''),
+(41,1,5,'',''),
+-- 7
+(42,1,4,'',''),
+(42,1,2,'',''),
+(43,1,5,'',''),
+(43,1,2,'',''),
+(43,1,0,'',''),
+(44,1,18,'',''),
+(44,1,13,'',''),
+(44,1,12,'',''),
+(44,1,11,'',''),
+(44,1,9,'',''),
+(45,1,23,'',''),
+(45,1,22,'',''),
+(45,1,17,'',''),
+(45,1,16,'',''),
+(45,1,15,'',''),
+(45,1,12,'',''),
+(45,1,10,'',''),
+(45,1,7,'',''),
+(45,1,4,'',''),
+-- 8
+(46,1,6,'',''),
+(46,1,2,'',''),
+(47,1,2,'',''),
+(47,1,0,'',''),
+(48,1,5,'',''),
+(48,1,2,'',''),
+(48,1,0,'',''),
+(49,1,4,'',''),
+(49,1,2,'',''),
+(49,1,0,'',''),
+(50,1,4,'',''),
+(50,1,2,'',''),
+(50,1,0,'',''),
+(51,1,3,'',''),
+(51,1,2,'',''),
+(52,1,0,'',''),
+(53,1,9,'',''),
+(53,1,2,'',''),
+(53,1,0,'',''),
+(54,1,7,'',''),
+(54,1,6,'',''),
+(54,1,5,'',''),
+(54,1,4,'',''),
+(54,1,3,'',''),
+(55,1,7,'',''),
+(55,1,3,'',''),
+(55,1,2,'',''),
+(55,1,0,'',''),
+(56,1,9,'',''),
+(56,1,5,'',''),
+(56,1,4,'',''),
+(56,1,2,'',''),
+-- 9
+(57,1,7,'',''),
+(57,1,5,'',''),
+(57,1,0,'',''),
+(58,1,2,'',''),
+(59,1,7,'',''),
+(59,1,6,'',''),
+(60,1,7,'',''),
+(60,1,6,'',''),
+-- Portal 2 Cooperative
+-- 1
+(63,1,0,'',''),
+(64,1,3,'',''),
+(64,1,2,'',''),
+(65,1,4,'',''),
+(65,1,3,'',''),
+(66,1,3,'',''),
+(66,1,2,'',''),
+(67,1,0,'',''),
+(68,1,0,'',''),
+-- 2
+(69,1,4,'',''),
+(70,1,6,'',''),
+(70,1,4,'',''),
+(70,1,2,'',''),
+(70,1,0,'',''),
+(71,1,3,'',''),
+(71,1,0,'',''),
+(72,1,4,'',''),
+(72,1,2,'',''),
+(73,1,9,'',''),
+(73,1,8,'',''),
+(73,1,7,'',''),
+(73,1,6,'',''),
+(73,1,4,'',''),
+(74,1,5,'',''),
+(74,1,7,'',''),
+(74,1,3,'',''),
+(74,1,2,'',''),
+(75,1,5,'',''),
+(75,1,4,'',''),
+(75,1,2,'',''),
+(75,1,0,'',''),
+(76,1,3,'',''),
+(76,1,0,'',''),
+-- 3
+(77,1,3,'',''),
+(78,1,4,'',''),
+(78,1,3,'',''),
+(78,1,2,'',''),
+(79,1,5,'',''),
+(79,1,4,'',''),
+(79,1,2,'',''),
+(80,1,5,'',''),
+(80,1,4,'',''),
+(81,1,7,'',''),
+(81,1,6,'',''),
+(81,1,5,'',''),
+(81,1,4,'',''),
+(82,1,4,'',''),
+(83,1,5,'',''),
+(83,1,2,'',''),
+(83,1,0,'',''),
+(84,1,6,'',''),
+(84,1,4,'',''),
+(84,1,2,'',''),
+(84,1,0,'',''),
+-- 4
+(85,1,3,'',''),
+(85,1,0,'',''),
+(86,1,3,'',''),
+(86,1,0,'',''),
+(87,1,3,'',''),
+(87,1,2,'',''),
+(87,1,0,'',''),
+(88,1,4,'',''),
+(88,1,0,'',''),
+(89,1,0,'',''),
+(90,1,4,'',''),
+(90,1,2,'',''),
+(90,1,0,'',''),
+(91,1,2,'',''),
+(91,1,0,'',''),
+(92,1,5,'',''),
+(92,1,4,'',''),
+(92,1,2,'',''),
+(92,1,0,'',''),
+(93,1,2,'',''),
+(93,1,0,'',''),
+-- 5
+(94,1,2,'',''),
+(94,1,0,'',''),
+(95,1,2,'',''),
+(96,1,4,'',''),
+(96,1,2,'',''),
+(97,1,7,'',''),
+(97,1,4,'',''),
+(97,1,2,'',''),
+(98,1,0,'',''),
+(99,1,3,'',''),
+(99,1,2,'',''),
+(99,1,0,'',''),
+(100,1,0,'',''),
+(101,1,2,'',''),
+(101,1,0,'',''),
+-- 6
+(102,1,3,'',''),
+(102,1,2,'',''),
+(102,1,0,'',''),
+(103,1,0,'',''),
+(104,1,0,'',''),
+(105,1,8,'',''),
+(105,1,5,'',''),
+(105,1,4,'',''),
+(105,1,3,'',''),
+(105,1,2,'',''),
+(106,1,4,'',''),
+(106,1,3,'',''),
+(106,1,0,'',''),
+(107,1,2,'',''),
+(107,1,0,'',''),
+(108,1,0,'',''),
+(109,1,5,'',''),
+(109,1,0,'',''),
+(110,1,15,'',''),
+(110,1,6,'',''),
+(110,1,5,'',''),
+(110,1,4,'',''),
+(110,1,2,'',''),
+(110,1,0,'','');
\ No newline at end of file
diff --git a/backend/models/models.go b/backend/models/models.go
index 5c9b8f8..783c339 100644
--- a/backend/models/models.go
+++ b/backend/models/models.go
@@ -31,9 +31,7 @@ type MapShort struct {
 }
 
 type MapSummary struct {
-	Rating  float32      `json:"rating"`
-	History []MapHistory `json:"history"`
-	Routes  []MapRoute   `json:"routes"`
+	Routes []MapRoute `json:"routes"`
 }
 
 type MapHistory struct {
@@ -43,10 +41,12 @@ type MapHistory struct {
 }
 
 type MapRoute struct {
-	Category    Category `json:"category"`
-	ScoreCount  int      `json:"score_count"`
-	Description string   `json:"description"`
-	Showcase    string   `json:"showcase"`
+	Category    Category   `json:"category"`
+	History     MapHistory `json:"history"`
+	Rating      float32    `json:"rating"`
+	ScoreCount  int        `json:"score_count"`
+	Description string     `json:"description"`
+	Showcase    string     `json:"showcase"`
 }
 
 type MapRecords struct {
-- 
cgit v1.2.3


From 3ad53aeeffec00f85bf1f3168d0e9d61c65eecb7 Mon Sep 17 00:00:00 2001
From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>
Date: Mon, 10 Jul 2023 16:06:29 +0300
Subject: feat: is_coop field on games

Former-commit-id: bf3a5a262557ade646ce8e59db152be8c521ac06
---
 backend/controllers/mapController.go | 4 ++--
 backend/database/games.sql           | 6 +++---
 backend/database/init.sql            | 1 +
 backend/models/models.go             | 5 +++--
 4 files changed, 9 insertions(+), 7 deletions(-)

(limited to 'backend/database/init.sql')

diff --git a/backend/controllers/mapController.go b/backend/controllers/mapController.go
index 52b6623..11e56f6 100644
--- a/backend/controllers/mapController.go
+++ b/backend/controllers/mapController.go
@@ -193,7 +193,7 @@ func FetchMapLeaderboards(c *gin.Context) {
 //	@Failure		400	{object}	models.Response
 //	@Router			/games [get]
 func FetchGames(c *gin.Context) {
-	rows, err := database.DB.Query(`SELECT id, name FROM games`)
+	rows, err := database.DB.Query(`SELECT id, name, is_coop FROM games`)
 	if err != nil {
 		c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
 		return
@@ -201,7 +201,7 @@ func FetchGames(c *gin.Context) {
 	var games []models.Game
 	for rows.Next() {
 		var game models.Game
-		if err := rows.Scan(&game.ID, &game.Name); err != nil {
+		if err := rows.Scan(&game.ID, &game.Name, &game.IsCoop); err != nil {
 			c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
 			return
 		}
diff --git a/backend/database/games.sql b/backend/database/games.sql
index 5e2f4ee..0c2374c 100644
--- a/backend/database/games.sql
+++ b/backend/database/games.sql
@@ -1,3 +1,3 @@
-INSERT INTO games(id, name) VALUES
-(1, 'Portal 2 - Singleplayer'),
-(2, 'Portal 2 - Cooperative');
\ No newline at end of file
+INSERT INTO games(id, name, is_coop) VALUES
+(1, 'Portal 2 - Singleplayer', false),
+(2, 'Portal 2 - Cooperative', true);
\ No newline at end of file
diff --git a/backend/database/init.sql b/backend/database/init.sql
index 76c3aa6..c7d098c 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -11,6 +11,7 @@ CREATE TABLE users (
 CREATE TABLE games (
   id SMALLSERIAL,
   name TEXT NOT NULL,
+  is_coop BOOLEAN NOT NULL,
   PRIMARY KEY (id)
 );
 
diff --git a/backend/models/models.go b/backend/models/models.go
index 2524935..e63ff91 100644
--- a/backend/models/models.go
+++ b/backend/models/models.go
@@ -62,8 +62,9 @@ type UserRanking struct {
 }
 
 type Game struct {
-	ID   int    `json:"id"`
-	Name string `json:"name"`
+	ID     int    `json:"id"`
+	Name   string `json:"name"`
+	IsCoop bool   `json:"is_coop"`
 }
 
 type Chapter struct {
-- 
cgit v1.2.3


From cae45e130891cf97f93d743ace381eb8209df2a8 Mon Sep 17 00:00:00 2001
From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>
Date: Wed, 12 Jul 2023 09:56:11 +0300
Subject: fix: unique relation for map history and routes

Former-commit-id: f3ea2ce6784973385d63980b6680294bbcee0188
---
 backend/database/history.sql | 2 +-
 backend/database/init.sql    | 6 ++++--
 backend/database/route.sql   | 2 +-
 3 files changed, 6 insertions(+), 4 deletions(-)

(limited to 'backend/database/init.sql')

diff --git a/backend/database/history.sql b/backend/database/history.sql
index d7c4999..320d72f 100644
--- a/backend/database/history.sql
+++ b/backend/database/history.sql
@@ -27,7 +27,7 @@ INSERT INTO map_history(map_id,category_id,user_name,score_count,record_date) VA
 (15,1,'Tyronis',2,'2011-10-05 00:00:00'),
 (16,1,'LookLikeAKango',2,'2011-10-05 00:00:00'),
 (16,1,'jyjey',0,'2012-08-25 00:00:00'),
-(17,1,'rocoty',0,'2011-10-05 00:00:00'),
+(17,1,'rocoty',2,'2011-10-05 00:00:00'),
 (17,1,'Nidboj132',0,'2023-02-05 00:00:00'),
 -- 3
 (18,1,'The Last Tofus',5,'2011-05-08 00:00:00'),
diff --git a/backend/database/init.sql b/backend/database/init.sql
index c7d098c..196cb71 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -49,7 +49,8 @@ CREATE TABLE map_routes (
   showcase TEXT NOT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (map_id) REFERENCES maps(id),
-  FOREIGN KEY (category_id) REFERENCES categories(id)
+  FOREIGN KEY (category_id) REFERENCES categories(id),
+  UNIQUE (map_id, category_id, score_count)
 );
 
 CREATE TABLE map_history (
@@ -61,7 +62,8 @@ CREATE TABLE map_history (
   record_date TIMESTAMP NOT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (category_id) REFERENCES categories(id),
-  FOREIGN KEY (map_id) REFERENCES maps(id)
+  FOREIGN KEY (map_id) REFERENCES maps(id),
+  UNIQUE (map_id, category_id, score_count)
 );
 
 CREATE TABLE map_ratings (
diff --git a/backend/database/route.sql b/backend/database/route.sql
index 6f090c7..f26180e 100644
--- a/backend/database/route.sql
+++ b/backend/database/route.sql
@@ -27,7 +27,7 @@ INSERT INTO map_routes(map_id,category_id,score_count,description,showcase) VALU
 (15,1,2,'',''),
 (16,1,2,'',''),
 (16,1,0,'',''),
-(17,1,0,'',''),
+(17,1,2,'',''),
 (17,1,0,'',''),
 -- 3
 (18,1,5,'',''),
-- 
cgit v1.2.3


From 7c9ce0f6bc22dce98216a959ec11182e7af9e790 Mon Sep 17 00:00:00 2001
From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>
Date: Wed, 12 Jul 2023 12:11:56 +0300
Subject: fix: turn back to serials

Former-commit-id: 9c83d4ac7297c7306f4872d76334507fa09cbde9
---
 backend/database/init.sql | 12 ++++++------
 1 file changed, 6 insertions(+), 6 deletions(-)

(limited to 'backend/database/init.sql')

diff --git a/backend/database/init.sql b/backend/database/init.sql
index 196cb71..50e7c15 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -9,14 +9,14 @@ CREATE TABLE users (
 );
 
 CREATE TABLE games (
-  id SMALLSERIAL,
+  id SERIAL,
   name TEXT NOT NULL,
   is_coop BOOLEAN NOT NULL,
   PRIMARY KEY (id)
 );
 
 CREATE TABLE chapters (
-  id SMALLSERIAL,
+  id SERIAL,
   game_id SMALLINT NOT NULL,
   name TEXT NOT NULL,
   PRIMARY KEY (id),
@@ -24,13 +24,13 @@ CREATE TABLE chapters (
 );
 
 CREATE TABLE categories (
-  id SMALLSERIAL,
+  id SERIAL,
   name TEXT NOT NULL,
   PRIMARY KEY (id)
 );
 
 CREATE TABLE maps (
-  id SMALLSERIAL,
+  id SERIAL,
   game_id SMALLINT NOT NULL,
   chapter_id SMALLINT NOT NULL,
   name TEXT NOT NULL,
@@ -41,7 +41,7 @@ CREATE TABLE maps (
 );
 
 CREATE TABLE map_routes (
-  id SMALLSERIAL,
+  id SERIAL,
   map_id SMALLINT NOT NULL,
   category_id SMALLINT NOT NULL,
   score_count SMALLINT NOT NULL,
@@ -54,7 +54,7 @@ CREATE TABLE map_routes (
 );
 
 CREATE TABLE map_history (
-  id SMALLSERIAL,
+  id SERIAL,
   map_id SMALLINT NOT NULL,
   category_id SMALLINT NOT NULL,
   user_name TEXT NOT NULL,
-- 
cgit v1.2.3

