From ac889169c777be38598680c7f468114cd9fb09fc Mon Sep 17 00:00:00 2001 From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com> Date: Mon, 24 Apr 2023 18:04:39 +0300 Subject: feat: map summary and leaderboard endpoints, new models and routes (#38) --- backend/controllers/mapController.go | 112 +++++++++++++++++++++++++++++------ backend/database/init.sql | 13 +++- backend/models/models.go | 37 ++++++++++-- backend/routes/routes.go | 7 ++- 4 files changed, 140 insertions(+), 29 deletions(-) (limited to 'backend') 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 ( "strconv" "github.com/gin-gonic/gin" + "github.com/lib/pq" "github.com/pektezol/leastportals/backend/database" "github.com/pektezol/leastportals/backend/models" ) -// GET Map +// GET Map Summary // -// @Summary Get map page with specified id. +// @Summary Get map summary with specified id. // @Tags maps // @Accept json // @Produce json // @Param id path int true "Map ID" -// @Success 200 {object} models.Response{data=models.Map} +// @Success 200 {object} models.Response{data=models.Map{data=models.MapSummary}} // @Failure 400 {object} models.Response -// @Router /maps/{id} [get] -func FetchMap(c *gin.Context) { +// @Router /maps/{id}/summary [get] +func FetchMapSummary(c *gin.Context) { id := c.Param("id") // Get map data var mapData models.Map + var mapSummaryData models.MapSummary + intID, err := strconv.Atoi(id) + if err != nil { + c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) + return + } + mapData.ID = intID + var routers pq.StringArray + sql := `SELECT g.name, c.name, m.name, m.description, m.showcase, + ( + SELECT user_name + FROM map_history + WHERE map_id = $1 + ORDER BY score_count + LIMIT 1 + ), + ( + SELECT array_agg(user_name) + FROM map_routers + WHERE map_id = $1 + AND score_count = ( + SELECT score_count + FROM map_history + WHERE map_id = $1 + ORDER BY score_count + LIMIT 1 + ) + GROUP BY map_routers.user_name + ORDER BY user_name + ), + ( + SELECT COALESCE(avg(rating), 0.0) + FROM map_ratings + WHERE map_id = $1 + ) + FROM maps m + INNER JOIN games g ON m.game_id = g.id + INNER JOIN chapters c ON m.chapter_id = c.id + WHERE m.id = $1;` + // TODO: CategoryScores + err = database.DB.QueryRow(sql, id).Scan(&mapData.GameName, &mapData.ChapterName, &mapData.MapName, &mapSummaryData.Description, &mapSummaryData.Showcase, &mapSummaryData.FirstCompletion, &routers, &mapSummaryData.Rating) + if err != nil { + c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) + return + } + mapSummaryData.Routers = routers + mapData.Data = mapSummaryData + // Return response + c.JSON(http.StatusOK, models.Response{ + Success: true, + Message: "Successfully retrieved map summary.", + Data: mapData, + }) +} + +// GET Map Leaderboards +// +// @Summary Get map leaderboards with specified id. +// @Tags maps +// @Accept json +// @Produce json +// @Param id path int true "Map ID" +// @Success 200 {object} models.Response{data=models.Map{data=models.MapRecords}} +// @Failure 400 {object} models.Response +// @Router /maps/{id}/leaderboards [get] +func FetchMapLeaderboards(c *gin.Context) { + id := c.Param("id") + // Get map data + var mapData models.Map + var mapRecordsData models.MapRecords var isDisabled bool intID, err := strconv.Atoi(id) if err != nil { @@ -30,8 +101,12 @@ func FetchMap(c *gin.Context) { return } mapData.ID = intID - sql := `SELECT map_name, wr_score, wr_time, is_coop, is_disabled FROM maps WHERE id = $1;` - err = database.DB.QueryRow(sql, id).Scan(&mapData.Name, &mapData.ScoreWR, &mapData.TimeWR, &mapData.IsCoop, &isDisabled) + sql := `SELECT g.name, c.name, m.name, is_disabled + FROM maps m + INNER JOIN games g ON m.game_id = g.id + INNER JOIN chapters c ON m.chapter_id = c.id + WHERE m.id = $1;` + err = database.DB.QueryRow(sql, id).Scan(&mapData.GameName, &mapData.ChapterName, &mapData.MapName, &isDisabled) if err != nil { c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) return @@ -40,13 +115,14 @@ func FetchMap(c *gin.Context) { c.JSON(http.StatusBadRequest, models.ErrorResponse("Map is not available for competitive boards.")) return } + // TODO: avatar and names for host & partner // Get records from the map - if mapData.IsCoop { + if mapData.GameName == "Portal 2 - Cooperative" { var records []models.RecordMP sql = `SELECT id, host_id, partner_id, score_count, score_time, host_demo_id, partner_demo_id, record_date FROM ( SELECT id, host_id, partner_id, score_count, score_time, host_demo_id, partner_demo_id, record_date, - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY score_count, score_time) AS rn + ROW_NUMBER() OVER (PARTITION BY host_id, partner_id ORDER BY score_count, score_time) AS rn FROM records_mp WHERE map_id = $1 ) sub @@ -60,7 +136,7 @@ func FetchMap(c *gin.Context) { ties := 0 for rows.Next() { var record models.RecordMP - err := rows.Scan(&record.RecordID, &record.HostID, &record.PartnerID, &record.ScoreCount, &record.ScoreTime, &record.HostDemoID, &record.PartnerDemoID, &record.RecordDate) + 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) if err != nil { c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) return @@ -74,16 +150,17 @@ func FetchMap(c *gin.Context) { records = append(records, record) placement++ } - mapData.Records = records + mapRecordsData.Records = records } else { var records []models.RecordSP - sql = `SELECT id, user_id, score_count, score_time, demo_id, record_date + sql = `SELECT id, user_id, users.user_name, users.avatar_link, score_count, score_time, demo_id, record_date FROM ( SELECT id, user_id, score_count, score_time, demo_id, record_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY score_count, score_time) AS rn FROM records_sp WHERE map_id = $1 ) sub + INNER JOIN users ON user_id = users.steam_id WHERE rn = 1;` rows, err := database.DB.Query(sql, id) if err != nil { @@ -94,7 +171,7 @@ func FetchMap(c *gin.Context) { ties := 0 for rows.Next() { var record models.RecordSP - err := rows.Scan(&record.RecordID, &record.UserID, &record.ScoreCount, &record.ScoreTime, &record.DemoID, &record.RecordDate) + err := rows.Scan(&record.RecordID, &record.UserID, &record.UserName, &record.UserAvatar, &record.ScoreCount, &record.ScoreTime, &record.DemoID, &record.RecordDate) if err != nil { c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) return @@ -108,16 +185,13 @@ func FetchMap(c *gin.Context) { records = append(records, record) placement++ } - mapData.Records = records + mapRecordsData.Records = records } + mapData.Data = mapRecordsData // Return response c.JSON(http.StatusOK, models.Response{ Success: true, - Message: "Successfully retrieved map data.", + Message: "Successfully retrieved map leaderboards.", Data: mapData, }) } - -func CreateMapCommunity(c *gin.Context) { - -} 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 ( FOREIGN KEY (user_id) REFERENCES users(steam_id) ); -CREATE TABLE map_rating ( +CREATE TABLE map_ratings ( id SERIAL, map_id SMALLINT NOT NULL, user_id TEXT NOT NULL, @@ -63,6 +63,17 @@ CREATE TABLE map_rating ( FOREIGN KEY (user_id) REFERENCES users(steam_id) ); +CREATE TABLE map_routers ( + id SMALLSERIAL, + map_id SMALLINT NOT NULL, + user_id TEXT, + user_name TEXT NOT NULL, + score_count SMALLINT NOT NULL, + PRIMARY KEY (id), + FOREIGN KEY (map_id) REFERENCES maps(id), + FOREIGN KEY (user_id) REFERENCES users(steam_id) +); + CREATE TABLE demos ( id UUID, 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 { } type Map struct { - ID int `json:"id"` - Name string `json:"name"` - ScoreWR int `json:"wr_score"` - TimeWR int `json:"wr_time"` - IsCoop bool `json:"is_coop"` - Records any `json:"records"` + ID int `json:"id"` + GameName string `json:"game_name"` + ChapterName string `json:"chapter_name"` + MapName string `json:"map_name"` + Data any `json:"data"` +} + +type MapSummary struct { + Description string `json:"description"` + Showcase string `json:"showcase"` + CategoryScores MapCategoryScores `json:"category_scores"` + Rating float32 `json:"rating"` + Routers []string `json:"routers"` + FirstCompletion string `json:"first_completion"` +} + +type MapCategoryScores struct { + CM int `json:"cm"` + NoSLA int `json:"no_sla"` + InboundsSLA int `json:"inbounds_sla"` + Any int `json:"any"` +} + +type MapRecords struct { + Records any `json:"records"` } type RecordSP struct { RecordID int `json:"record_id"` Placement int `json:"placement"` UserID string `json:"user_id"` + UserName string `json:"user_name"` + UserAvatar string `json:"user_avatar"` ScoreCount int `json:"score_count"` ScoreTime int `json:"score_time"` DemoID string `json:"demo_id"` @@ -46,7 +67,11 @@ type RecordMP struct { RecordID int `json:"record_id"` Placement int `json:"placement"` HostID string `json:"host_id"` + HostName string `json:"host_name"` + HostAvatar string `json:"host_avatar"` PartnerID string `json:"partner_id"` + PartnerName string `json:"partner_name"` + PartnerAvatar string `json:"partner_avatar"` ScoreCount int `json:"score_count"` ScoreTime int `json:"score_time"` 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) { v1.GET("/profile", middleware.CheckAuth, controllers.Profile) v1.PUT("/profile", middleware.CheckAuth, controllers.UpdateCountryCode) v1.POST("/profile", middleware.CheckAuth, controllers.UpdateUser) - v1.GET("/user/:id", middleware.CheckAuth, controllers.FetchUser) - v1.GET("/demo", controllers.DownloadDemoWithID) - v1.GET("/maps/:id", middleware.CheckAuth, controllers.FetchMap) + v1.GET("/users/:id", middleware.CheckAuth, controllers.FetchUser) + v1.GET("/demos", controllers.DownloadDemoWithID) + v1.GET("/maps/:id/summary", middleware.CheckAuth, controllers.FetchMapSummary) + v1.GET("/maps/:id/leaderboards", middleware.CheckAuth, controllers.FetchMapLeaderboards) v1.POST("/maps/:id/record", middleware.CheckAuth, controllers.CreateRecordWithDemo) v1.GET("/rankings", middleware.CheckAuth, controllers.Rankings) } -- cgit v1.2.3