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/stats.go | 204 ++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 204 insertions(+) create mode 100644 backend/handlers/stats.go (limited to 'backend/handlers/stats.go') 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