From 96f55dba15bcbba4e6d8f14035ecfd20ebcea8a8 Mon Sep 17 00:00:00 2001 From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com> Date: Mon, 27 Oct 2025 22:12:31 +0300 Subject: feat/backend: timeline stats endpoint --- backend/handlers/logs.go | 103 ----------------------- backend/handlers/record.go | 10 +-- backend/handlers/stats.go | 204 +++++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 209 insertions(+), 108 deletions(-) delete mode 100644 backend/handlers/logs.go create mode 100644 backend/handlers/stats.go (limited to 'backend/handlers') diff --git a/backend/handlers/logs.go b/backend/handlers/logs.go deleted file mode 100644 index 693c448..0000000 --- a/backend/handlers/logs.go +++ /dev/null @@ -1,103 +0,0 @@ -package handlers - -import ( - "net/http" - "time" - - "lphub/database" - "lphub/models" - - "github.com/gin-gonic/gin" -) - -type Log struct { - User models.UserShort `json:"user"` - Type string `json:"type"` - Description string `json:"description"` - Message string `json:"message"` - Date time.Time `json:"date"` -} - -type LogsResponse struct { - Logs []LogsResponseDetails `json:"logs"` -} - -type LogsResponseDetails struct { - User models.UserShort `json:"user"` - Log string `json:"detail"` - Message string `json:"message"` - Date time.Time `json:"date"` -} - -type ScoreLogsResponse struct { - Logs []ScoreLogsResponseDetails `json:"scores"` -} - -type ScoreLogsResponseDetails struct { - Game models.Game `json:"game"` - User models.UserShort `json:"user"` - Map models.MapShort `json:"map"` - ScoreCount int `json:"score_count"` - ScoreTime int `json:"score_time"` - DemoID string `json:"demo_id"` - Date time.Time `json:"date"` -} - -// GET Score Logs -// -// @Description Get score logs of every player. -// @Tags logs -// @Produce json -// @Success 200 {object} models.Response{data=ScoreLogsResponse} -// @Router /logs/score [get] -func ScoreLogs(c *gin.Context) { - response := ScoreLogsResponse{Logs: []ScoreLogsResponseDetails{}} - sql := `SELECT g.id, - g."name", - g.is_coop, - rs.map_id, - m.name AS map_name, - u.steam_id, - u.user_name, - rs.score_count, - rs.score_time, - rs.demo_id, - rs.record_date - FROM ( - SELECT id, map_id, user_id, score_count, score_time, demo_id, record_date - FROM records_sp WHERE is_deleted = false - - UNION ALL - - SELECT id, map_id, host_id AS user_id, score_count, score_time, host_demo_id AS demo_id, record_date - FROM records_mp WHERE is_deleted = false - - UNION ALL - - SELECT id, map_id, partner_id AS user_id, score_count, score_time, partner_demo_id AS demo_id, record_date - FROM records_mp WHERE is_deleted = false - ) AS rs - JOIN users u ON rs.user_id = u.steam_id - JOIN maps m ON rs.map_id = m.id - JOIN games g ON m.game_id = g.id - ORDER BY rs.record_date DESC LIMIT 100;` - rows, err := database.DB.Query(sql) - if err != nil { - c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) - return - } - for rows.Next() { - score := ScoreLogsResponseDetails{} - err = rows.Scan(&score.Game.ID, &score.Game.Name, &score.Game.IsCoop, &score.Map.ID, &score.Map.Name, &score.User.SteamID, &score.User.UserName, &score.ScoreCount, &score.ScoreTime, &score.DemoID, &score.Date) - if err != nil { - c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) - return - } - response.Logs = append(response.Logs, score) - } - c.JSON(http.StatusOK, models.Response{ - Success: true, - Message: "Successfully retrieved score logs.", - Data: response, - }) -} diff --git a/backend/handlers/record.go b/backend/handlers/record.go index 25a6c6d..07ae5a8 100644 --- a/backend/handlers/record.go +++ b/backend/handlers/record.go @@ -35,11 +35,11 @@ type RecordResponse struct { // @Tags maps / leaderboards // @Accept mpfd // @Produce json -// @Param mapid path int true "Map ID" -// @Param Authorization header string true "JWT Token" -// @Param host_demo formData file true "Host Demo" -// @Param partner_demo formData file false "Partner Demo" -// @Success 200 {object} models.Response{data=RecordResponse} +// @Param mapid path int true "Map ID" +// @Param Authorization header string true "JWT Token" +// @Param host_demo formData file true "Host Demo" +// @Param partner_demo formData file false "Partner Demo" +// @Success 200 {object} models.Response{data=RecordResponse} // @Router /maps/{mapid}/record [post] func CreateRecordWithDemo(c *gin.Context) { id := c.Param("mapid") diff --git a/backend/handlers/stats.go b/backend/handlers/stats.go new file mode 100644 index 0000000..8fe98f9 --- /dev/null +++ b/backend/handlers/stats.go @@ -0,0 +1,204 @@ +package handlers + +import ( + "net/http" + "time" + + "lphub/database" + "lphub/models" + + "github.com/gin-gonic/gin" +) + +type ScoresResponse struct { + Logs []ScoresDetails `json:"scores"` +} + +type ScoresDetails struct { + Game models.Game `json:"game"` + User models.UserShort `json:"user"` + Map models.MapShort `json:"map"` + ScoreCount int `json:"score_count"` + ScoreTime int `json:"score_time"` + DemoID string `json:"demo_id"` + Date time.Time `json:"date"` +} + +type TimelinePoint struct { + Date string `json:"date"` + Count int `json:"count"` +} + +type TimelineResponse struct { + Singleplayer []TimelinePoint `json:"timeline_singleplayer"` + Multiplayer []TimelinePoint `json:"timeline_multiplayer"` +} + +// GET Scores +// +// @Description Get score logs of every player. +// @Tags stats +// @Produce json +// @Success 200 {object} models.Response{data=ScoresResponse} +// @Router /stats/scores [get] +func Scores(c *gin.Context) { + response := ScoresResponse{Logs: []ScoresDetails{}} + sql := `SELECT g.id, + g."name", + g.is_coop, + rs.map_id, + m.name AS map_name, + u.steam_id, + u.user_name, + rs.score_count, + rs.score_time, + rs.demo_id, + rs.record_date + FROM ( + SELECT id, map_id, user_id, score_count, score_time, demo_id, record_date + FROM records_sp WHERE is_deleted = false + + UNION ALL + + SELECT id, map_id, host_id AS user_id, score_count, score_time, host_demo_id AS demo_id, record_date + FROM records_mp WHERE is_deleted = false + + UNION ALL + + SELECT id, map_id, partner_id AS user_id, score_count, score_time, partner_demo_id AS demo_id, record_date + FROM records_mp WHERE is_deleted = false + ) AS rs + JOIN users u ON rs.user_id = u.steam_id + JOIN maps m ON rs.map_id = m.id + JOIN games g ON m.game_id = g.id + ORDER BY rs.record_date DESC LIMIT 100;` + rows, err := database.DB.Query(sql) + if err != nil { + c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) + return + } + for rows.Next() { + score := ScoresDetails{} + err = rows.Scan(&score.Game.ID, &score.Game.Name, &score.Game.IsCoop, &score.Map.ID, &score.Map.Name, &score.User.SteamID, &score.User.UserName, &score.ScoreCount, &score.ScoreTime, &score.DemoID, &score.Date) + if err != nil { + c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) + return + } + response.Logs = append(response.Logs, score) + } + c.JSON(http.StatusOK, models.Response{ + Success: true, + Message: "Successfully retrieved score logs.", + Data: response, + }) +} + +// GET Timeline +// +// @Description Get the history of portal count world records over time. +// @Tags stats +// @Produce json +// @Success 200 {object} models.Response{data=TimelineResponse} +// @Router /stats/timeline [get] +func Timeline(c *gin.Context) { + result := TimelineResponse{ + Singleplayer: []TimelinePoint{}, + Multiplayer: []TimelinePoint{}, + } + spQuery := ` + WITH date_series AS ( + SELECT DISTINCT record_date as date + FROM map_history + WHERE category_id = 1 AND map_id <= 60 AND record_date >= '2013-01-31' + ORDER BY record_date + ), + map_best_at_date AS ( + SELECT + ds.date, + mh.map_id, + MIN(mh.score_count) as best_count + FROM date_series ds + CROSS JOIN (SELECT DISTINCT map_id FROM map_history WHERE category_id = 1 AND map_id <= 60) maps + LEFT JOIN map_history mh ON mh.map_id = maps.map_id + AND mh.category_id = 1 + AND mh.record_date <= ds.date + GROUP BY ds.date, mh.map_id + ) + SELECT + date, + SUM(best_count) as total_count + FROM map_best_at_date + GROUP BY date + ORDER BY date ASC; + ` + + mpQuery := ` + WITH date_series AS ( + SELECT DISTINCT record_date as date + FROM map_history + WHERE category_id = 1 AND map_id > 60 AND record_date >= '2011-12-21' + ORDER BY record_date + ), + map_best_at_date AS ( + SELECT + ds.date, + mh.map_id, + MIN(mh.score_count) as best_count + FROM date_series ds + CROSS JOIN (SELECT DISTINCT map_id FROM map_history WHERE category_id = 1 AND map_id > 60) maps + LEFT JOIN map_history mh ON mh.map_id = maps.map_id + AND mh.category_id = 1 + AND mh.record_date <= ds.date + GROUP BY ds.date, mh.map_id + ) + SELECT + date, + SUM(best_count) as total_count + FROM map_best_at_date + GROUP BY date + ORDER BY date ASC; + ` + + rows, err := database.DB.Query(spQuery) + if err != nil { + return + } + defer rows.Close() + + for rows.Next() { + var dateTime time.Time + var count int + if err := rows.Scan(&dateTime, &count); err != nil { + c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) + return + } + result.Singleplayer = append(result.Singleplayer, TimelinePoint{ + Date: dateTime.Format("2006-01-02"), + Count: count, + }) + } + + rows, err = database.DB.Query(mpQuery) + if err != nil { + return + } + defer rows.Close() + + for rows.Next() { + var dateTime time.Time + var count int + if err := rows.Scan(&dateTime, &count); err != nil { + c.JSON(http.StatusOK, models.ErrorResponse(err.Error())) + return + } + result.Multiplayer = append(result.Multiplayer, TimelinePoint{ + Date: dateTime.Format("2006-01-02"), + Count: count, + }) + } + c.JSON(http.StatusOK, models.Response{ + Success: true, + Message: "Successfully retrieved portal count timeline.", + Data: result, + }) +} -- cgit v1.2.3