aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--backend/database/categories.sql12
-rw-r--r--backend/database/init.sql9
-rw-r--r--backend/handlers/map.go42
-rw-r--r--backend/models/models.go24
-rw-r--r--docs/docs.go25
-rw-r--r--docs/swagger.json25
-rw-r--r--docs/swagger.yaml17
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
2(1, 'CM'), 2(1, 'CM'),
3(2, 'No SLA'), 3(2, 'No SLA'),
4(3, 'Inbounds SLA'), 4(3, 'Inbounds SLA'),
5(4, 'Any%'); \ No newline at end of file 5(4, 'Any%'),
6(5, 'All Courses');
7
8INSERT INTO game_categories(id, game_id, category_id) VALUES
9(1, 1, 1),
10(2, 1, 2),
11(3, 1, 3),
12(4, 1, 4),
13(5, 2, 1),
14(6, 2, 4),
15(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 (
36 PRIMARY KEY (id) 36 PRIMARY KEY (id)
37); 37);
38 38
39CREATE TABLE game_categories (
40 id SERIAL,
41 game_id SMALLINT NOT NULL,
42 category_id SMALLINT NOT NULL,
43 PRIMARY KEY (id),
44 FOREIGN KEY (game_id) REFERENCES games(id),
45 FOREIGN KEY (category_id) REFERENCES categories(id)
46);
47
39CREATE TABLE maps ( 48CREATE TABLE maps (
40 id SERIAL, 49 id SERIAL,
41 game_id SMALLINT NOT NULL, 50 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) {
339 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 339 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
340 return 340 return
341 } 341 }
342 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)
343 if err != nil {
344 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
345 return
346 }
347 for categoryPortalRows.Next() {
348 var categoryPortals models.CategoryPortal
349 if err := categoryPortalRows.Scan(&categoryPortals.Category.ID, &categoryPortals.Category.Name); err != nil {
350 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
351 return
352 }
353 getCategoryPortalCount := `
354 SELECT
355 SUM(mh.lowest_score_count) AS total_lowest_scores
356 FROM (
357 SELECT
358 map_id,
359 category_id,
360 MIN(score_count) AS lowest_score_count
361 FROM
362 map_history
363 GROUP BY
364 map_id,
365 category_id
366 ) mh
367 JOIN maps m ON mh.map_id = m.id
368 JOIN games g ON m.game_id = g.id
369 WHERE
370 mh.category_id = $1 and g.id = $2
371 GROUP BY
372 g.id,
373 g.name,
374 mh.category_id;
375 `
376 database.DB.QueryRow(getCategoryPortalCount, categoryPortals.Category.ID, game.ID).Scan(&categoryPortals.PortalCount)
377 // not checking for errors since there can be no record for category - just let it have 0
378 game.CategoryPortals = append(game.CategoryPortals, categoryPortals)
379 }
342 games = append(games, game) 380 games = append(games, game)
343 } 381 }
344 c.JSON(http.StatusOK, models.Response{ 382 c.JSON(http.StatusOK, models.Response{
@@ -441,7 +479,7 @@ func FetchChapterMaps(c *gin.Context) {
441 return 479 return
442 } 480 }
443 var response ChapterMapsResponse 481 var response ChapterMapsResponse
444 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) 482 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)
445 if err != nil { 483 if err != nil {
446 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 484 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
447 return 485 return
@@ -450,7 +488,7 @@ func FetchChapterMaps(c *gin.Context) {
450 var chapterName string 488 var chapterName string
451 for rows.Next() { 489 for rows.Next() {
452 var mapShort models.MapShort 490 var mapShort models.MapShort
453 if err := rows.Scan(&mapShort.ID, &mapShort.Name, &chapterName, &mapShort.IsDisabled, &mapShort.Image); err != nil { 491 if err := rows.Scan(&mapShort.ID, &mapShort.Name, &chapterName, &mapShort.IsDisabled, &mapShort.Image, &mapShort.PortalCount); err != nil {
454 c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) 492 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
455 return 493 return
456 } 494 }
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 {
50} 50}
51 51
52type MapShort struct { 52type MapShort struct {
53 ID int `json:"id"` 53 ID int `json:"id"`
54 Name string `json:"name"` 54 Name string `json:"name"`
55 Image string `json:"image"` 55 Image string `json:"image"`
56 IsDisabled bool `json:"is_disabled"` 56 IsDisabled bool `json:"is_disabled"`
57 PortalCount int `json:"portal_count"`
58 Difficulty int `json:"difficulty"`
57} 59}
58 60
59type MapSummary struct { 61type MapSummary struct {
@@ -87,10 +89,11 @@ type UserRanking struct {
87} 89}
88 90
89type Game struct { 91type Game struct {
90 ID int `json:"id"` 92 ID int `json:"id"`
91 Name string `json:"name"` 93 Name string `json:"name"`
92 Image string `json:"image"` 94 Image string `json:"image"`
93 IsCoop bool `json:"is_coop"` 95 IsCoop bool `json:"is_coop"`
96 CategoryPortals []CategoryPortal `json:"category_portals"`
94} 97}
95 98
96type Chapter struct { 99type Chapter struct {
@@ -100,6 +103,11 @@ type Chapter struct {
100 IsDisabled bool `json:"is_disabled"` 103 IsDisabled bool `json:"is_disabled"`
101} 104}
102 105
106type CategoryPortal struct {
107 Category Category `json:"category"`
108 PortalCount int `json:"portal_count"`
109}
110
103type Category struct { 111type Category struct {
104 ID int `json:"id"` 112 ID int `json:"id"`
105 Name string `json:"name"` 113 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 = `{
1418 "category_id", 1418 "category_id",
1419 "description", 1419 "description",
1420 "record_date", 1420 "record_date",
1421 "score_count",
1421 "user_name" 1422 "user_name"
1422 ], 1423 ],
1423 "properties": { 1424 "properties": {
@@ -1484,6 +1485,7 @@ const docTemplate = `{
1484 "description", 1485 "description",
1485 "record_date", 1486 "record_date",
1486 "route_id", 1487 "route_id",
1488 "score_count",
1487 "user_name" 1489 "user_name"
1488 ], 1490 ],
1489 "properties": { 1491 "properties": {
@@ -1863,6 +1865,17 @@ const docTemplate = `{
1863 } 1865 }
1864 } 1866 }
1865 }, 1867 },
1868 "models.CategoryPortal": {
1869 "type": "object",
1870 "properties": {
1871 "category": {
1872 "$ref": "#/definitions/models.Category"
1873 },
1874 "portal_count": {
1875 "type": "integer"
1876 }
1877 }
1878 },
1866 "models.Chapter": { 1879 "models.Chapter": {
1867 "type": "object", 1880 "type": "object",
1868 "properties": { 1881 "properties": {
@@ -1883,6 +1896,12 @@ const docTemplate = `{
1883 "models.Game": { 1896 "models.Game": {
1884 "type": "object", 1897 "type": "object",
1885 "properties": { 1898 "properties": {
1899 "category_portals": {
1900 "type": "array",
1901 "items": {
1902 "$ref": "#/definitions/models.CategoryPortal"
1903 }
1904 },
1886 "id": { 1905 "id": {
1887 "type": "integer" 1906 "type": "integer"
1888 }, 1907 },
@@ -1983,6 +2002,9 @@ const docTemplate = `{
1983 "models.MapShort": { 2002 "models.MapShort": {
1984 "type": "object", 2003 "type": "object",
1985 "properties": { 2004 "properties": {
2005 "difficulty": {
2006 "type": "integer"
2007 },
1986 "id": { 2008 "id": {
1987 "type": "integer" 2009 "type": "integer"
1988 }, 2010 },
@@ -1994,6 +2016,9 @@ const docTemplate = `{
1994 }, 2016 },
1995 "name": { 2017 "name": {
1996 "type": "string" 2018 "type": "string"
2019 },
2020 "portal_count": {
2021 "type": "integer"
1997 } 2022 }
1998 } 2023 }
1999 }, 2024 },
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 @@
1412 "category_id", 1412 "category_id",
1413 "description", 1413 "description",
1414 "record_date", 1414 "record_date",
1415 "score_count",
1415 "user_name" 1416 "user_name"
1416 ], 1417 ],
1417 "properties": { 1418 "properties": {
@@ -1478,6 +1479,7 @@
1478 "description", 1479 "description",
1479 "record_date", 1480 "record_date",
1480 "route_id", 1481 "route_id",
1482 "score_count",
1481 "user_name" 1483 "user_name"
1482 ], 1484 ],
1483 "properties": { 1485 "properties": {
@@ -1857,6 +1859,17 @@
1857 } 1859 }
1858 } 1860 }
1859 }, 1861 },
1862 "models.CategoryPortal": {
1863 "type": "object",
1864 "properties": {
1865 "category": {
1866 "$ref": "#/definitions/models.Category"
1867 },
1868 "portal_count": {
1869 "type": "integer"
1870 }
1871 }
1872 },
1860 "models.Chapter": { 1873 "models.Chapter": {
1861 "type": "object", 1874 "type": "object",
1862 "properties": { 1875 "properties": {
@@ -1877,6 +1890,12 @@
1877 "models.Game": { 1890 "models.Game": {
1878 "type": "object", 1891 "type": "object",
1879 "properties": { 1892 "properties": {
1893 "category_portals": {
1894 "type": "array",
1895 "items": {
1896 "$ref": "#/definitions/models.CategoryPortal"
1897 }
1898 },
1880 "id": { 1899 "id": {
1881 "type": "integer" 1900 "type": "integer"
1882 }, 1901 },
@@ -1977,6 +1996,9 @@
1977 "models.MapShort": { 1996 "models.MapShort": {
1978 "type": "object", 1997 "type": "object",
1979 "properties": { 1998 "properties": {
1999 "difficulty": {
2000 "type": "integer"
2001 },
1980 "id": { 2002 "id": {
1981 "type": "integer" 2003 "type": "integer"
1982 }, 2004 },
@@ -1988,6 +2010,9 @@
1988 }, 2010 },
1989 "name": { 2011 "name": {
1990 "type": "string" 2012 "type": "string"
2013 },
2014 "portal_count": {
2015 "type": "integer"
1991 } 2016 }
1992 } 2017 }
1993 }, 2018 },
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:
53 - category_id 53 - category_id
54 - description 54 - description
55 - record_date 55 - record_date
56 - score_count
56 - user_name 57 - user_name
57 type: object 58 type: object
58 handlers.DeleteMapSummaryRequest: 59 handlers.DeleteMapSummaryRequest:
@@ -97,6 +98,7 @@ definitions:
97 - description 98 - description
98 - record_date 99 - record_date
99 - route_id 100 - route_id
101 - score_count
100 - user_name 102 - user_name
101 type: object 103 type: object
102 handlers.LoginResponse: 104 handlers.LoginResponse:
@@ -330,6 +332,13 @@ definitions:
330 name: 332 name:
331 type: string 333 type: string
332 type: object 334 type: object
335 models.CategoryPortal:
336 properties:
337 category:
338 $ref: '#/definitions/models.Category'
339 portal_count:
340 type: integer
341 type: object
333 models.Chapter: 342 models.Chapter:
334 properties: 343 properties:
335 id: 344 id:
@@ -343,6 +352,10 @@ definitions:
343 type: object 352 type: object
344 models.Game: 353 models.Game:
345 properties: 354 properties:
355 category_portals:
356 items:
357 $ref: '#/definitions/models.CategoryPortal'
358 type: array
346 id: 359 id:
347 type: integer 360 type: integer
348 image: 361 image:
@@ -408,6 +421,8 @@ definitions:
408 type: object 421 type: object
409 models.MapShort: 422 models.MapShort:
410 properties: 423 properties:
424 difficulty:
425 type: integer
411 id: 426 id:
412 type: integer 427 type: integer
413 image: 428 image:
@@ -416,6 +431,8 @@ definitions:
416 type: boolean 431 type: boolean
417 name: 432 name:
418 type: string 433 type: string
434 portal_count:
435 type: integer
419 type: object 436 type: object
420 models.MapSummary: 437 models.MapSummary:
421 properties: 438 properties:
generated by cgit v1.2.3 (git 2.39.1) at 2025-11-08 23:22:14 +0000