From d35a9a945ecaeeceb21bd91eeeaa4c6ec0a15e24 Mon Sep 17 00:00:00 2001
From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>
Date: Fri, 5 Jul 2024 21:03:46 +0300
Subject: feat: return portal count from games and chapter map select (#155)

---
 backend/database/categories.sql | 12 +++++++++++-
 backend/database/init.sql       |  9 +++++++++
 backend/handlers/map.go         | 42 +++++++++++++++++++++++++++++++++++++++--
 backend/models/models.go        | 24 +++++++++++++++--------
 4 files changed, 76 insertions(+), 11 deletions(-)

(limited to 'backend')

diff --git a/backend/database/categories.sql b/backend/database/categories.sql
index 5cadad5..6bbf9b0 100644
--- a/backend/database/categories.sql
+++ b/backend/database/categories.sql
@@ -2,4 +2,14 @@ INSERT INTO categories(id, name) VALUES
 (1, 'CM'),
 (2, 'No SLA'),
 (3, 'Inbounds SLA'),
-(4, 'Any%');
\ No newline at end of file
+(4, 'Any%'),
+(5, 'All Courses');
+
+INSERT INTO game_categories(id, game_id, category_id) VALUES
+(1, 1, 1),
+(2, 1, 2),
+(3, 1, 3),
+(4, 1, 4),
+(5, 2, 1),
+(6, 2, 4),
+(7, 2, 5);
\ No newline at end of file
diff --git a/backend/database/init.sql b/backend/database/init.sql
index c33821b..77a88f5 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -36,6 +36,15 @@ CREATE TABLE categories (
   PRIMARY KEY (id)
 );
 
+CREATE TABLE game_categories (
+  id SERIAL,
+  game_id SMALLINT NOT NULL,
+  category_id SMALLINT NOT NULL,
+  PRIMARY KEY (id),
+  FOREIGN KEY (game_id) REFERENCES games(id),
+  FOREIGN KEY (category_id) REFERENCES categories(id)
+);
+
 CREATE TABLE maps (
   id SERIAL,
   game_id SMALLINT NOT NULL,
diff --git a/backend/handlers/map.go b/backend/handlers/map.go
index bf7c821..8104243 100644
--- a/backend/handlers/map.go
+++ b/backend/handlers/map.go
@@ -339,6 +339,44 @@ func FetchGames(c *gin.Context) {
 			c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
 			return
 		}
+		categoryPortalRows, err := database.DB.Query(`SELECT c.id, c.name FROM game_categories gc JOIN categories c ON gc.category_id = c.id WHERE gc.game_id = $1`, game.ID)
+		if err != nil {
+			c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
+			return
+		}
+		for categoryPortalRows.Next() {
+			var categoryPortals models.CategoryPortal
+			if err := categoryPortalRows.Scan(&categoryPortals.Category.ID, &categoryPortals.Category.Name); err != nil {
+				c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
+				return
+			}
+			getCategoryPortalCount := `
+			SELECT
+				SUM(mh.lowest_score_count) AS total_lowest_scores
+			FROM (
+				SELECT
+					map_id,
+					category_id,
+					MIN(score_count) AS lowest_score_count
+				FROM
+					map_history
+				GROUP BY
+					map_id,
+					category_id
+			) mh
+			JOIN maps m ON mh.map_id = m.id
+			JOIN games g ON m.game_id = g.id
+			WHERE
+				mh.category_id = $1 and g.id = $2
+			GROUP BY
+				g.id,
+				g.name,
+				mh.category_id;
+			`
+			database.DB.QueryRow(getCategoryPortalCount, categoryPortals.Category.ID, game.ID).Scan(&categoryPortals.PortalCount)
+			// not checking for errors since there can be no record for category - just let it have 0
+			game.CategoryPortals = append(game.CategoryPortals, categoryPortals)
+		}
 		games = append(games, game)
 	}
 	c.JSON(http.StatusOK, models.Response{
@@ -441,7 +479,7 @@ func FetchChapterMaps(c *gin.Context) {
 		return
 	}
 	var response ChapterMapsResponse
-	rows, err := database.DB.Query(`SELECT m.id, m.name, c.name, m.is_disabled, m.image FROM maps m INNER JOIN chapters c ON m.chapter_id = c.id WHERE chapter_id = $1`, chapterID)
+	rows, err := database.DB.Query(`SELECT m.id, m.name, c.name, m.is_disabled, m.image, MIN(mh.score_count) FROM maps m INNER JOIN chapters c ON m.chapter_id = c.id INNER JOIN map_history mh ON m.id = mh.map_id WHERE chapter_id = $1 GROUP BY m.id, c.name ORDER BY m.id;`, chapterID)
 	if err != nil {
 		c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
 		return
@@ -450,7 +488,7 @@ func FetchChapterMaps(c *gin.Context) {
 	var chapterName string
 	for rows.Next() {
 		var mapShort models.MapShort
-		if err := rows.Scan(&mapShort.ID, &mapShort.Name, &chapterName, &mapShort.IsDisabled, &mapShort.Image); err != nil {
+		if err := rows.Scan(&mapShort.ID, &mapShort.Name, &chapterName, &mapShort.IsDisabled, &mapShort.Image, &mapShort.PortalCount); err != nil {
 			c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
 			return
 		}
diff --git a/backend/models/models.go b/backend/models/models.go
index b766b97..64d90d1 100644
--- a/backend/models/models.go
+++ b/backend/models/models.go
@@ -50,10 +50,12 @@ type Map struct {
 }
 
 type MapShort struct {
-	ID         int    `json:"id"`
-	Name       string `json:"name"`
-	Image      string `json:"image"`
-	IsDisabled bool   `json:"is_disabled"`
+	ID          int    `json:"id"`
+	Name        string `json:"name"`
+	Image       string `json:"image"`
+	IsDisabled  bool   `json:"is_disabled"`
+	PortalCount int    `json:"portal_count"`
+	Difficulty  int    `json:"difficulty"`
 }
 
 type MapSummary struct {
@@ -87,10 +89,11 @@ type UserRanking struct {
 }
 
 type Game struct {
-	ID     int    `json:"id"`
-	Name   string `json:"name"`
-	Image  string `json:"image"`
-	IsCoop bool   `json:"is_coop"`
+	ID              int              `json:"id"`
+	Name            string           `json:"name"`
+	Image           string           `json:"image"`
+	IsCoop          bool             `json:"is_coop"`
+	CategoryPortals []CategoryPortal `json:"category_portals"`
 }
 
 type Chapter struct {
@@ -100,6 +103,11 @@ type Chapter struct {
 	IsDisabled bool   `json:"is_disabled"`
 }
 
+type CategoryPortal struct {
+	Category    Category `json:"category"`
+	PortalCount int      `json:"portal_count"`
+}
+
 type Category struct {
 	ID   int    `json:"id"`
 	Name string `json:"name"`
-- 
cgit v1.2.3