aboutsummaryrefslogtreecommitdiff
path: root/backend/handlers
diff options
context:
space:
mode:
Diffstat (limited to 'backend/handlers')
-rw-r--r--backend/handlers/logs.go103
-rw-r--r--backend/handlers/record.go10
-rw-r--r--backend/handlers/stats.go204
3 files changed, 209 insertions, 108 deletions
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 @@
1package handlers
2
3import (
4 "net/http"
5 "time"
6
7 "lphub/database"
8 "lphub/models"
9
10 "github.com/gin-gonic/gin"
11)
12
13type Log struct {
14 User models.UserShort `json:"user"`
15 Type string `json:"type"`
16 Description string `json:"description"`
17 Message string `json:"message"`
18 Date time.Time `json:"date"`
19}
20
21type LogsResponse struct {
22 Logs []LogsResponseDetails `json:"logs"`
23}
24
25type LogsResponseDetails struct {
26 User models.UserShort `json:"user"`
27 Log string `json:"detail"`
28 Message string `json:"message"`
29 Date time.Time `json:"date"`
30}
31
32type ScoreLogsResponse struct {
33 Logs []ScoreLogsResponseDetails `json:"scores"`
34}
35
36type ScoreLogsResponseDetails struct {
37 Game models.Game `json:"game"`
38 User models.UserShort `json:"user"`
39 Map models.MapShort `json:"map"`
40 ScoreCount int `json:"score_count"`
41 ScoreTime int `json:"score_time"`
42 DemoID string `json:"demo_id"`
43 Date time.Time `json:"date"`
44}
45
46// GET Score Logs
47//
48// @Description Get score logs of every player.
49// @Tags logs
50// @Produce json
51// @Success 200 {object} models.Response{data=ScoreLogsResponse}
52// @Router /logs/score [get]
53func ScoreLogs(c *gin.Context) {
54 response := ScoreLogsResponse{Logs: []ScoreLogsResponseDetails{}}
55 sql := `SELECT g.id,
56 g."name",
57 g.is_coop,
58 rs.map_id,
59 m.name AS map_name,
60 u.steam_id,
61 u.user_name,
62 rs.score_count,
63 rs.score_time,
64 rs.demo_id,
65 rs.record_date
66 FROM (
67 SELECT id, map_id, user_id, score_count, score_time, demo_id, record_date
68 FROM records_sp WHERE is_deleted = false
69
70 UNION ALL
71
72 SELECT id, map_id, host_id AS user_id, score_count, score_time, host_demo_id AS demo_id, record_date
73 FROM records_mp WHERE is_deleted = false
74
75 UNION ALL
76
77 SELECT id, map_id, partner_id AS user_id, score_count, score_time, partner_demo_id AS demo_id, record_date
78 FROM records_mp WHERE is_deleted = false
79 ) AS rs
80 JOIN users u ON rs.user_id = u.steam_id
81 JOIN maps m ON rs.map_id = m.id
82 JOIN games g ON m.game_id = g.id
83 ORDER BY rs.record_date DESC LIMIT 100;`
84 rows, err := database.DB.Query(sql)
85 if err != nil {
86 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
87 return
88 }
89 for rows.Next() {
90 score := ScoreLogsResponseDetails{}
91 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)
92 if err != nil {
93 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
94 return
95 }
96 response.Logs = append(response.Logs, score)
97 }
98 c.JSON(http.StatusOK, models.Response{
99 Success: true,
100 Message: "Successfully retrieved score logs.",
101 Data: response,
102 })
103}
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 {
35// @Tags maps / leaderboards 35// @Tags maps / leaderboards
36// @Accept mpfd 36// @Accept mpfd
37// @Produce json 37// @Produce json
38// @Param mapid path int true "Map ID" 38// @Param mapid path int true "Map ID"
39// @Param Authorization header string true "JWT Token" 39// @Param Authorization header string true "JWT Token"
40// @Param host_demo formData file true "Host Demo" 40// @Param host_demo formData file true "Host Demo"
41// @Param partner_demo formData file false "Partner Demo" 41// @Param partner_demo formData file false "Partner Demo"
42// @Success 200 {object} models.Response{data=RecordResponse} 42// @Success 200 {object} models.Response{data=RecordResponse}
43// @Router /maps/{mapid}/record [post] 43// @Router /maps/{mapid}/record [post]
44func CreateRecordWithDemo(c *gin.Context) { 44func CreateRecordWithDemo(c *gin.Context) {
45 id := c.Param("mapid") 45 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 @@
1package handlers
2
3import (
4 "net/http"
5 "time"
6
7 "lphub/database"
8 "lphub/models"
9
10 "github.com/gin-gonic/gin"
11)
12
13type ScoresResponse struct {
14 Logs []ScoresDetails `json:"scores"`
15}
16
17type ScoresDetails struct {
18 Game models.Game `json:"game"`
19 User models.UserShort `json:"user"`
20 Map models.MapShort `json:"map"`
21 ScoreCount int `json:"score_count"`
22 ScoreTime int `json:"score_time"`
23 DemoID string `json:"demo_id"`
24 Date time.Time `json:"date"`
25}
26
27type TimelinePoint struct {
28 Date string `json:"date"`
29 Count int `json:"count"`
30}
31
32type TimelineResponse struct {
33 Singleplayer []TimelinePoint `json:"timeline_singleplayer"`
34 Multiplayer []TimelinePoint `json:"timeline_multiplayer"`
35}
36
37// GET Scores
38//
39// @Description Get score logs of every player.
40// @Tags stats
41// @Produce json
42// @Success 200 {object} models.Response{data=ScoresResponse}
43// @Router /stats/scores [get]
44func Scores(c *gin.Context) {
45 response := ScoresResponse{Logs: []ScoresDetails{}}
46 sql := `SELECT g.id,
47 g."name",
48 g.is_coop,
49 rs.map_id,
50 m.name AS map_name,
51 u.steam_id,
52 u.user_name,
53 rs.score_count,
54 rs.score_time,
55 rs.demo_id,
56 rs.record_date
57 FROM (
58 SELECT id, map_id, user_id, score_count, score_time, demo_id, record_date
59 FROM records_sp WHERE is_deleted = false
60
61 UNION ALL
62
63 SELECT id, map_id, host_id AS user_id, score_count, score_time, host_demo_id AS demo_id, record_date
64 FROM records_mp WHERE is_deleted = false
65
66 UNION ALL
67
68 SELECT id, map_id, partner_id AS user_id, score_count, score_time, partner_demo_id AS demo_id, record_date
69 FROM records_mp WHERE is_deleted = false
70 ) AS rs
71 JOIN users u ON rs.user_id = u.steam_id
72 JOIN maps m ON rs.map_id = m.id
73 JOIN games g ON m.game_id = g.id
74 ORDER BY rs.record_date DESC LIMIT 100;`
75 rows, err := database.DB.Query(sql)
76 if err != nil {
77 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
78 return
79 }
80 for rows.Next() {
81 score := ScoresDetails{}
82 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)
83 if err != nil {
84 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
85 return
86 }
87 response.Logs = append(response.Logs, score)
88 }
89 c.JSON(http.StatusOK, models.Response{
90 Success: true,
91 Message: "Successfully retrieved score logs.",
92 Data: response,
93 })
94}
95
96// GET Timeline
97//
98// @Description Get the history of portal count world records over time.
99// @Tags stats
100// @Produce json
101// @Success 200 {object} models.Response{data=TimelineResponse}
102// @Router /stats/timeline [get]
103func Timeline(c *gin.Context) {
104 result := TimelineResponse{
105 Singleplayer: []TimelinePoint{},
106 Multiplayer: []TimelinePoint{},
107 }
108 spQuery := `
109 WITH date_series AS (
110 SELECT DISTINCT record_date as date
111 FROM map_history
112 WHERE category_id = 1 AND map_id <= 60 AND record_date >= '2013-01-31'
113 ORDER BY record_date
114 ),
115 map_best_at_date AS (
116 SELECT
117 ds.date,
118 mh.map_id,
119 MIN(mh.score_count) as best_count
120 FROM date_series ds
121 CROSS JOIN (SELECT DISTINCT map_id FROM map_history WHERE category_id = 1 AND map_id <= 60) maps
122 LEFT JOIN map_history mh ON mh.map_id = maps.map_id
123 AND mh.category_id = 1
124 AND mh.record_date <= ds.date
125 GROUP BY ds.date, mh.map_id
126 )
127 SELECT
128 date,
129 SUM(best_count) as total_count
130 FROM map_best_at_date
131 GROUP BY date
132 ORDER BY date ASC;
133 `
134
135 mpQuery := `
136 WITH date_series AS (
137 SELECT DISTINCT record_date as date
138 FROM map_history
139 WHERE category_id = 1 AND map_id > 60 AND record_date >= '2011-12-21'
140 ORDER BY record_date
141 ),
142 map_best_at_date AS (
143 SELECT
144 ds.date,
145 mh.map_id,
146 MIN(mh.score_count) as best_count
147 FROM date_series ds
148 CROSS JOIN (SELECT DISTINCT map_id FROM map_history WHERE category_id = 1 AND map_id > 60) maps
149 LEFT JOIN map_history mh ON mh.map_id = maps.map_id
150 AND mh.category_id = 1
151 AND mh.record_date <= ds.date
152 GROUP BY ds.date, mh.map_id
153 )
154 SELECT
155 date,
156 SUM(best_count) as total_count
157 FROM map_best_at_date
158 GROUP BY date
159 ORDER BY date ASC;
160 `
161
162 rows, err := database.DB.Query(spQuery)
163 if err != nil {
164 return
165 }
166 defer rows.Close()
167
168 for rows.Next() {
169 var dateTime time.Time
170 var count int
171 if err := rows.Scan(&dateTime, &count); err != nil {
172 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
173 return
174 }
175 result.Singleplayer = append(result.Singleplayer, TimelinePoint{
176 Date: dateTime.Format("2006-01-02"),
177 Count: count,
178 })
179 }
180
181 rows, err = database.DB.Query(mpQuery)
182 if err != nil {
183 return
184 }
185 defer rows.Close()
186
187 for rows.Next() {
188 var dateTime time.Time
189 var count int
190 if err := rows.Scan(&dateTime, &count); err != nil {
191 c.JSON(http.StatusOK, models.ErrorResponse(err.Error()))
192 return
193 }
194 result.Multiplayer = append(result.Multiplayer, TimelinePoint{
195 Date: dateTime.Format("2006-01-02"),
196 Count: count,
197 })
198 }
199 c.JSON(http.StatusOK, models.Response{
200 Success: true,
201 Message: "Successfully retrieved portal count timeline.",
202 Data: result,
203 })
204}
generated by cgit v1.2.3 (git 2.39.1) at 2025-11-08 16:47:22 +0000