From 50f7bf57f45a26362376be37e89128bac9195edc Mon Sep 17 00:00:00 2001
From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>
Date: Wed, 10 Jul 2024 21:27:31 +0300
Subject: feat: return portal counts for each cat in chapter select (#175)

---
 backend/handlers/map.go  | 56 ++++++++++++++++++++++++++++++++++++++++++------
 backend/models/models.go |  9 ++++++++
 2 files changed, 58 insertions(+), 7 deletions(-)

(limited to 'backend')

diff --git a/backend/handlers/map.go b/backend/handlers/map.go
index 8104243..3166d1d 100644
--- a/backend/handlers/map.go
+++ b/backend/handlers/map.go
@@ -27,8 +27,8 @@ type ChaptersResponse struct {
 }
 
 type ChapterMapsResponse struct {
-	Chapter models.Chapter    `json:"chapter"`
-	Maps    []models.MapShort `json:"maps"`
+	Chapter models.Chapter     `json:"chapter"`
+	Maps    []models.MapSelect `json:"maps"`
 }
 
 type GameMapsResponse struct {
@@ -479,20 +479,62 @@ 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, 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)
+	rows, err := database.DB.Query(`
+	SELECT 
+		m.id, 
+		m.name AS map_name, 
+		c.name AS chapter_name, 
+		m.is_disabled,
+		m.image,
+		cat.id,
+		cat.name,
+		mh.min_score_count AS score_count
+	FROM 
+		maps m
+	INNER JOIN 
+		chapters c ON m.chapter_id = c.id
+	INNER JOIN 
+		game_categories gc ON gc.game_id = c.game_id
+	INNER JOIN
+		categories cat ON cat.id = gc.category_id
+	INNER JOIN 
+		(
+			SELECT 
+				map_id, 
+				category_id, 
+				MIN(score_count) AS min_score_count
+			FROM 
+				map_history
+			GROUP BY 
+				map_id, 
+				category_id
+		) mh ON m.id = mh.map_id AND gc.category_id = mh.category_id
+	WHERE 
+		m.chapter_id = $1
+	ORDER BY 
+		m.id, gc.category_id, mh.min_score_count ASC;
+	`, chapterID)
 	if err != nil {
 		c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
 		return
 	}
-	var maps []models.MapShort
+	var maps []models.MapSelect
 	var chapterName string
+	var lastMapID int
 	for rows.Next() {
-		var mapShort models.MapShort
-		if err := rows.Scan(&mapShort.ID, &mapShort.Name, &chapterName, &mapShort.IsDisabled, &mapShort.Image, &mapShort.PortalCount); err != nil {
+		var mapShort models.MapSelect
+		var categoryPortal models.CategoryPortal
+		if err := rows.Scan(&mapShort.ID, &mapShort.Name, &chapterName, &mapShort.IsDisabled, &mapShort.Image, &categoryPortal.Category.ID, &categoryPortal.Category.Name, &categoryPortal.PortalCount); err != nil {
 			c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
 			return
 		}
-		maps = append(maps, mapShort)
+		if mapShort.ID == lastMapID {
+			maps[len(maps)-1].CategoryPortals = append(maps[len(maps)-1].CategoryPortals, categoryPortal)
+		} else {
+			mapShort.CategoryPortals = append(mapShort.CategoryPortals, categoryPortal)
+			maps = append(maps, mapShort)
+			lastMapID = mapShort.ID
+		}
 	}
 	response.Chapter.ID = intID
 	response.Chapter.Name = chapterName
diff --git a/backend/models/models.go b/backend/models/models.go
index 64d90d1..a114f2c 100644
--- a/backend/models/models.go
+++ b/backend/models/models.go
@@ -58,6 +58,15 @@ type MapShort struct {
 	Difficulty  int    `json:"difficulty"`
 }
 
+type MapSelect struct {
+	ID              int              `json:"id"`
+	Name            string           `json:"name"`
+	Image           string           `json:"image"`
+	IsDisabled      bool             `json:"is_disabled"`
+	Difficulty      int              `json:"difficulty"`
+	CategoryPortals []CategoryPortal `json:"category_portals"`
+}
+
 type MapSummary struct {
 	Routes []MapRoute `json:"routes"`
 }
-- 
cgit v1.2.3