aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorArda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>2023-04-24 18:04:39 +0300
committerArda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>2023-04-24 18:04:39 +0300
commitac889169c777be38598680c7f468114cd9fb09fc (patch)
tree96b27bb904290b0c04c5c53fd929bf48ec5527c0
parentdoc: change route name (diff)
downloadlphub-ac889169c777be38598680c7f468114cd9fb09fc.tar.gz
lphub-ac889169c777be38598680c7f468114cd9fb09fc.tar.bz2
lphub-ac889169c777be38598680c7f468114cd9fb09fc.zip
feat: map summary and leaderboard endpoints, new models and routes (#38)
Diffstat
-rw-r--r--backend/controllers/mapController.go112
-rw-r--r--backend/database/init.sql13
-rw-r--r--backend/models/models.go37
-rw-r--r--backend/routes/routes.go7
4 files changed, 140 insertions, 29 deletions
diff --git a/backend/controllers/mapController.go b/backend/controllers/mapController.go
index 16dd669..a04854e 100644
--- a/backend/controllers/mapController.go
+++ b/backend/controllers/mapController.go
@@ -5,24 +5,95 @@ import (
5 "strconv" 5 "strconv"
6 6
7 "github.com/gin-gonic/gin" 7 "github.com/gin-gonic/gin"
8 "github.com/lib/pq"
8 "github.com/pektezol/leastportals/backend/database" 9 "github.com/pektezol/leastportals/backend/database"
9 "github.com/pektezol/leastportals/backend/models" 10 "github.com/pektezol/leastportals/backend/models"
10) 11)
11 12
12// GET Map 13// GET Map Summary
13// 14//
14// @Summary Get map page with specified id. 15// @Summary Get map summary with specified id.
15// @Tags maps 16// @Tags maps
16// @Accept json 17// @Accept json
17// @Produce json 18// @Produce json
18// @Param id path int true "Map ID" 19// @Param id path int true "Map ID"
19// @Success 200 {object} models.Response{data=models.Map} 20// @Success 200 {object} models.Response{data=models.Map{data=models.MapSummary}}
20// @Failure 400 {object} models.Response 21// @Failure 400 {object} models.Response
21// @Router /maps/{id} [get] 22// @Router /maps/{id}/summary [get]
22func FetchMap(c *gin.Context) { 23func FetchMapSummary(c *gin.Context) {
23 id := c.Param("id") 24 id := c.Param("id")
24 // Get map data 25 // Get map data
25 var mapData models.Map 26 var mapData models.Map
27 var mapSummaryData models.MapSummary
28 intID, err := strconv.Atoi(id)
29 if err != nil {
30 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
31 return
32 }
33 mapData.ID = intID
34 var routers pq.StringArray
35 sql := `SELECT g.name, c.name, m.name, m.description, m.showcase,
36 (
37 SELECT user_name
38 FROM map_history
39 WHERE map_id = $1
40 ORDER BY score_count
41 LIMIT 1
42 ),
43 (
44 SELECT array_agg(user_name)
45 FROM map_routers
46 WHERE map_id = $1
47 AND score_count = (
48 SELECT score_count
49 FROM map_history
50 WHERE map_id = $1
51 ORDER BY score_count
52 LIMIT 1
53 )
54 GROUP BY map_routers.user_name
55 ORDER BY user_name
56 ),
57 (
58 SELECT COALESCE(avg(rating), 0.0)
59 FROM map_ratings
60 WHERE map_id = $1
61 )
62 FROM maps m
63 INNER JOIN games g ON m.game_id = g.id
64 INNER JOIN chapters c ON m.chapter_id = c.id
65 WHERE m.id = $1;`
66 // TODO: CategoryScores
67 err = database.DB.QueryRow(sql, id).Scan(&mapData.GameName, &mapData.ChapterName, &mapData.MapName, &mapSummaryData.Description, &mapSummaryData.Showcase, &mapSummaryData.FirstCompletion, &routers, &mapSummaryData.Rating)
68 if err != nil {
69 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
70 return
71 }
72 mapSummaryData.Routers = routers
73 mapData.Data = mapSummaryData
74 // Return response
75 c.JSON(http.StatusOK, models.Response{
76 Success: true,
77 Message: "Successfully retrieved map summary.",
78 Data: mapData,
79 })
80}
81
82// GET Map Leaderboards
83//
84// @Summary Get map leaderboards with specified id.
85// @Tags maps
86// @Accept json
87// @Produce json
88// @Param id path int true "Map ID"
89// @Success 200 {object} models.Response{data=models.Map{data=models.MapRecords}}
90// @Failure 400 {object} models.Response
91// @Router /maps/{id}/leaderboards [get]
92func FetchMapLeaderboards(c *gin.Context) {
93 id := c.Param("id")
94 // Get map data
95 var mapData models.Map
96 var mapRecordsData models.MapRecords
26 var isDisabled bool 97 var isDisabled bool
27 intID, err := strconv.Atoi(id) 98 intID, err := strconv.Atoi(id)
28 if err != nil { 99 if err != nil {
@@ -30,8 +101,12 @@ func FetchMap(c *gin.Context) {
30 return 101 return
31 } 102 }
32 mapData.ID = intID 103 mapData.ID = intID
33 sql := `SELECT map_name, wr_score, wr_time, is_coop, is_disabled FROM maps WHERE id = $1;` 104 sql := `SELECT g.name, c.name, m.name, is_disabled
34 err = database.DB.QueryRow(sql, id).Scan(&mapData.Name, &mapData.ScoreWR, &mapData.TimeWR, &mapData.IsCoop, &isDisabled) 105 FROM maps m
106 INNER JOIN games g ON m.game_id = g.id
107 INNER JOIN chapters c ON m.chapter_id = c.id
108 WHERE m.id = $1;`
109 err = database.DB.QueryRow(sql, id).Scan(&mapData.GameName, &mapData.ChapterName, &mapData.MapName, &isDisabled)
35 if err != nil { 110 if err != nil {
36 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) 111 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
37 return 112 return
@@ -40,13 +115,14 @@ func FetchMap(c *gin.Context) {
40 c.JSON(http.StatusBadRequest, models.ErrorResponse("Map is not available for competitive boards.")) 115 c.JSON(http.StatusBadRequest, models.ErrorResponse("Map is not available for competitive boards."))
41 return 116 return
42 } 117 }
118 // TODO: avatar and names for host & partner
43 // Get records from the map 119 // Get records from the map
44 if mapData.IsCoop { 120 if mapData.GameName == "Portal 2 - Cooperative" {
45 var records []models.RecordMP 121 var records []models.RecordMP
46 sql = `SELECT id, host_id, partner_id, score_count, score_time, host_demo_id, partner_demo_id, record_date 122 sql = `SELECT id, host_id, partner_id, score_count, score_time, host_demo_id, partner_demo_id, record_date
47 FROM ( 123 FROM (
48 SELECT id, host_id, partner_id, score_count, score_time, host_demo_id, partner_demo_id, record_date, 124 SELECT id, host_id, partner_id, score_count, score_time, host_demo_id, partner_demo_id, record_date,
49 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY score_count, score_time) AS rn 125 ROW_NUMBER() OVER (PARTITION BY host_id, partner_id ORDER BY score_count, score_time) AS rn
50 FROM records_mp 126 FROM records_mp
51 WHERE map_id = $1 127 WHERE map_id = $1
52 ) sub 128 ) sub
@@ -60,7 +136,7 @@ func FetchMap(c *gin.Context) {
60 ties := 0 136 ties := 0
61 for rows.Next() { 137 for rows.Next() {
62 var record models.RecordMP 138 var record models.RecordMP
63 err := rows.Scan(&record.RecordID, &record.HostID, &record.PartnerID, &record.ScoreCount, &record.ScoreTime, &record.HostDemoID, &record.PartnerDemoID, &record.RecordDate) 139 err := rows.Scan(&record.RecordID, &record.HostID, &record.HostName, &record.HostAvatar, &record.PartnerID, &record.PartnerName, &record.PartnerAvatar, &record.ScoreCount, &record.ScoreTime, &record.HostDemoID, &record.PartnerDemoID, &record.RecordDate)
64 if err != nil { 140 if err != nil {
65 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) 141 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
66 return 142 return
@@ -74,16 +150,17 @@ func FetchMap(c *gin.Context) {
74 records = append(records, record) 150 records = append(records, record)
75 placement++ 151 placement++
76 } 152 }
77 mapData.Records = records 153 mapRecordsData.Records = records
78 } else { 154 } else {
79 var records []models.RecordSP 155 var records []models.RecordSP
80 sql = `SELECT id, user_id, score_count, score_time, demo_id, record_date 156 sql = `SELECT id, user_id, users.user_name, users.avatar_link, score_count, score_time, demo_id, record_date
81 FROM ( 157 FROM (
82 SELECT id, user_id, score_count, score_time, demo_id, record_date, 158 SELECT id, user_id, score_count, score_time, demo_id, record_date,
83 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY score_count, score_time) AS rn 159 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY score_count, score_time) AS rn
84 FROM records_sp 160 FROM records_sp
85 WHERE map_id = $1 161 WHERE map_id = $1
86 ) sub 162 ) sub
163 INNER JOIN users ON user_id = users.steam_id
87 WHERE rn = 1;` 164 WHERE rn = 1;`
88 rows, err := database.DB.Query(sql, id) 165 rows, err := database.DB.Query(sql, id)
89 if err != nil { 166 if err != nil {
@@ -94,7 +171,7 @@ func FetchMap(c *gin.Context) {
94 ties := 0 171 ties := 0
95 for rows.Next() { 172 for rows.Next() {
96 var record models.RecordSP 173 var record models.RecordSP
97 err := rows.Scan(&record.RecordID, &record.UserID, &record.ScoreCount, &record.ScoreTime, &record.DemoID, &record.RecordDate) 174 err := rows.Scan(&record.RecordID, &record.UserID, &record.UserName, &record.UserAvatar, &record.ScoreCount, &record.ScoreTime, &record.DemoID, &record.RecordDate)
98 if err != nil { 175 if err != nil {
99 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) 176 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
100 return 177 return
@@ -108,16 +185,13 @@ func FetchMap(c *gin.Context) {
108 records = append(records, record) 185 records = append(records, record)
109 placement++ 186 placement++
110 } 187 }
111 mapData.Records = records 188 mapRecordsData.Records = records
112 } 189 }
190 mapData.Data = mapRecordsData
113 // Return response 191 // Return response
114 c.JSON(http.StatusOK, models.Response{ 192 c.JSON(http.StatusOK, models.Response{
115 Success: true, 193 Success: true,
116 Message: "Successfully retrieved map data.", 194 Message: "Successfully retrieved map leaderboards.",
117 Data: mapData, 195 Data: mapData,
118 }) 196 })
119} 197}
120
121func CreateMapCommunity(c *gin.Context) {
122
123}
diff --git a/backend/database/init.sql b/backend/database/init.sql
index a82404a..53c9262 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -53,7 +53,7 @@ CREATE TABLE map_history (
53 FOREIGN KEY (user_id) REFERENCES users(steam_id) 53 FOREIGN KEY (user_id) REFERENCES users(steam_id)
54); 54);
55 55
56CREATE TABLE map_rating ( 56CREATE TABLE map_ratings (
57 id SERIAL, 57 id SERIAL,
58 map_id SMALLINT NOT NULL, 58 map_id SMALLINT NOT NULL,
59 user_id TEXT NOT NULL, 59 user_id TEXT NOT NULL,
@@ -63,6 +63,17 @@ CREATE TABLE map_rating (
63 FOREIGN KEY (user_id) REFERENCES users(steam_id) 63 FOREIGN KEY (user_id) REFERENCES users(steam_id)
64); 64);
65 65
66CREATE TABLE map_routers (
67 id SMALLSERIAL,
68 map_id SMALLINT NOT NULL,
69 user_id TEXT,
70 user_name TEXT NOT NULL,
71 score_count SMALLINT NOT NULL,
72 PRIMARY KEY (id),
73 FOREIGN KEY (map_id) REFERENCES maps(id),
74 FOREIGN KEY (user_id) REFERENCES users(steam_id)
75);
76
66CREATE TABLE demos ( 77CREATE TABLE demos (
67 id UUID, 78 id UUID,
68 location_id TEXT NOT NULL, 79 location_id TEXT NOT NULL,
diff --git a/backend/models/models.go b/backend/models/models.go
index 2d14a06..b9ea9f0 100644
--- a/backend/models/models.go
+++ b/backend/models/models.go
@@ -24,18 +24,39 @@ type User struct {
24} 24}
25 25
26type Map struct { 26type Map struct {
27 ID int `json:"id"` 27 ID int `json:"id"`
28 Name string `json:"name"` 28 GameName string `json:"game_name"`
29 ScoreWR int `json:"wr_score"` 29 ChapterName string `json:"chapter_name"`
30 TimeWR int `json:"wr_time"` 30 MapName string `json:"map_name"`
31 IsCoop bool `json:"is_coop"` 31 Data any `json:"data"`
32 Records any `json:"records"` 32}
33
34type MapSummary struct {
35 Description string `json:"description"`
36 Showcase string `json:"showcase"`
37 CategoryScores MapCategoryScores `json:"category_scores"`
38 Rating float32 `json:"rating"`
39 Routers []string `json:"routers"`
40 FirstCompletion string `json:"first_completion"`
41}
42
43type MapCategoryScores struct {
44 CM int `json:"cm"`
45 NoSLA int `json:"no_sla"`
46 InboundsSLA int `json:"inbounds_sla"`
47 Any int `json:"any"`
48}
49
50type MapRecords struct {
51 Records any `json:"records"`
33} 52}
34 53
35type RecordSP struct { 54type RecordSP struct {
36 RecordID int `json:"record_id"` 55 RecordID int `json:"record_id"`
37 Placement int `json:"placement"` 56 Placement int `json:"placement"`
38 UserID string `json:"user_id"` 57 UserID string `json:"user_id"`
58 UserName string `json:"user_name"`
59 UserAvatar string `json:"user_avatar"`
39 ScoreCount int `json:"score_count"` 60 ScoreCount int `json:"score_count"`
40 ScoreTime int `json:"score_time"` 61 ScoreTime int `json:"score_time"`
41 DemoID string `json:"demo_id"` 62 DemoID string `json:"demo_id"`
@@ -46,7 +67,11 @@ type RecordMP struct {
46 RecordID int `json:"record_id"` 67 RecordID int `json:"record_id"`
47 Placement int `json:"placement"` 68 Placement int `json:"placement"`
48 HostID string `json:"host_id"` 69 HostID string `json:"host_id"`
70 HostName string `json:"host_name"`
71 HostAvatar string `json:"host_avatar"`
49 PartnerID string `json:"partner_id"` 72 PartnerID string `json:"partner_id"`
73 PartnerName string `json:"partner_name"`
74 PartnerAvatar string `json:"partner_avatar"`
50 ScoreCount int `json:"score_count"` 75 ScoreCount int `json:"score_count"`
51 ScoreTime int `json:"score_time"` 76 ScoreTime int `json:"score_time"`
52 HostDemoID string `json:"host_demo_id"` 77 HostDemoID string `json:"host_demo_id"`
diff --git a/backend/routes/routes.go b/backend/routes/routes.go
index 7d9c04b..b9f07db 100644
--- a/backend/routes/routes.go
+++ b/backend/routes/routes.go
@@ -21,9 +21,10 @@ func InitRoutes(router *gin.Engine) {
21 v1.GET("/profile", middleware.CheckAuth, controllers.Profile) 21 v1.GET("/profile", middleware.CheckAuth, controllers.Profile)
22 v1.PUT("/profile", middleware.CheckAuth, controllers.UpdateCountryCode) 22 v1.PUT("/profile", middleware.CheckAuth, controllers.UpdateCountryCode)
23 v1.POST("/profile", middleware.CheckAuth, controllers.UpdateUser) 23 v1.POST("/profile", middleware.CheckAuth, controllers.UpdateUser)
24 v1.GET("/user/:id", middleware.CheckAuth, controllers.FetchUser) 24 v1.GET("/users/:id", middleware.CheckAuth, controllers.FetchUser)
25 v1.GET("/demo", controllers.DownloadDemoWithID) 25 v1.GET("/demos", controllers.DownloadDemoWithID)
26 v1.GET("/maps/:id", middleware.CheckAuth, controllers.FetchMap) 26 v1.GET("/maps/:id/summary", middleware.CheckAuth, controllers.FetchMapSummary)
27 v1.GET("/maps/:id/leaderboards", middleware.CheckAuth, controllers.FetchMapLeaderboards)
27 v1.POST("/maps/:id/record", middleware.CheckAuth, controllers.CreateRecordWithDemo) 28 v1.POST("/maps/:id/record", middleware.CheckAuth, controllers.CreateRecordWithDemo)
28 v1.GET("/rankings", middleware.CheckAuth, controllers.Rankings) 29 v1.GET("/rankings", middleware.CheckAuth, controllers.Rankings)
29 } 30 }
generated by cgit v1.2.3 (git 2.39.1) at 2025-11-08 21:31:57 +0000