feat: return portal count from games and chapter map select (#155)

7 files changed, 143 insertions, 11 deletions

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"`
diff --git a/docs/docs.go b/docs/docs.go
index 01eb022..688d7ed 100644
--- a/docs/docs.go
+++ b/docs/docs.go
@@ -1418,6 +1418,7 @@ const docTemplate = `{
                 "category_id",
                 "description",
                 "record_date",
+                "score_count",
                 "user_name"
             ],
             "properties": {
@@ -1484,6 +1485,7 @@ const docTemplate = `{
                 "description",
                 "record_date",
                 "route_id",
+                "score_count",
                 "user_name"
             ],
             "properties": {
@@ -1863,6 +1865,17 @@ const docTemplate = `{
                 }
             }
         },
+        "models.CategoryPortal": {
+            "type": "object",
+            "properties": {
+                "category": {
+                    "$ref": "#/definitions/models.Category"
+                },
+                "portal_count": {
+                    "type": "integer"
+                }
+            }
+        },
         "models.Chapter": {
             "type": "object",
             "properties": {
@@ -1883,6 +1896,12 @@ const docTemplate = `{
         "models.Game": {
             "type": "object",
             "properties": {
+                "category_portals": {
+                    "type": "array",
+                    "items": {
+                        "$ref": "#/definitions/models.CategoryPortal"
+                    }
+                },
                 "id": {
                     "type": "integer"
                 },
@@ -1983,6 +2002,9 @@ const docTemplate = `{
         "models.MapShort": {
             "type": "object",
             "properties": {
+                "difficulty": {
+                    "type": "integer"
+                },
                 "id": {
                     "type": "integer"
                 },
@@ -1994,6 +2016,9 @@ const docTemplate = `{
                 },
                 "name": {
                     "type": "string"
+                },
+                "portal_count": {
+                    "type": "integer"
                 }
             }
         },
diff --git a/docs/swagger.json b/docs/swagger.json
index e2e842f..6fe6320 100644
--- a/docs/swagger.json
+++ b/docs/swagger.json
@@ -1412,6 +1412,7 @@
                 "category_id",
                 "description",
                 "record_date",
+                "score_count",
                 "user_name"
             ],
             "properties": {
@@ -1478,6 +1479,7 @@
                 "description",
                 "record_date",
                 "route_id",
+                "score_count",
                 "user_name"
             ],
             "properties": {
@@ -1857,6 +1859,17 @@
                 }
             }
         },
+        "models.CategoryPortal": {
+            "type": "object",
+            "properties": {
+                "category": {
+                    "$ref": "#/definitions/models.Category"
+                },
+                "portal_count": {
+                    "type": "integer"
+                }
+            }
+        },
         "models.Chapter": {
             "type": "object",
             "properties": {
@@ -1877,6 +1890,12 @@
         "models.Game": {
             "type": "object",
             "properties": {
+                "category_portals": {
+                    "type": "array",
+                    "items": {
+                        "$ref": "#/definitions/models.CategoryPortal"
+                    }
+                },
                 "id": {
                     "type": "integer"
                 },
@@ -1977,6 +1996,9 @@
         "models.MapShort": {
             "type": "object",
             "properties": {
+                "difficulty": {
+                    "type": "integer"
+                },
                 "id": {
                     "type": "integer"
                 },
@@ -1988,6 +2010,9 @@
                 },
                 "name": {
                     "type": "string"
+                },
+                "portal_count": {
+                    "type": "integer"
                 }
             }
         },
diff --git a/docs/swagger.yaml b/docs/swagger.yaml
index 7f370ee..6041c1d 100644
--- a/docs/swagger.yaml
+++ b/docs/swagger.yaml
@@ -53,6 +53,7 @@ definitions:
     - category_id
     - description
     - record_date
+    - score_count
     - user_name
     type: object
   handlers.DeleteMapSummaryRequest:
@@ -97,6 +98,7 @@ definitions:
     - description
     - record_date
     - route_id
+    - score_count
     - user_name
     type: object
   handlers.LoginResponse:
@@ -330,6 +332,13 @@ definitions:
       name:
         type: string
     type: object
+  models.CategoryPortal:
+    properties:
+      category:
+        $ref: '#/definitions/models.Category'
+      portal_count:
+        type: integer
+    type: object
   models.Chapter:
     properties:
       id:
@@ -343,6 +352,10 @@ definitions:
     type: object
   models.Game:
     properties:
+      category_portals:
+        items:
+          $ref: '#/definitions/models.CategoryPortal'
+        type: array
       id:
         type: integer
       image:
@@ -408,6 +421,8 @@ definitions:
     type: object
   models.MapShort:
     properties:
+      difficulty:
+        type: integer
       id:
         type: integer
       image:
@@ -416,6 +431,8 @@ definitions:
         type: boolean
       name:
         type: string
+      portal_count:
+        type: integer
     type: object
   models.MapSummary:
     properties: