aboutsummaryrefslogtreecommitdiff
path: root/backend
diff options
context:
space:
mode:
Diffstat (limited to 'backend')
-rw-r--r--backend/controllers/homeController.go6
-rw-r--r--backend/controllers/loginController.go2
-rw-r--r--backend/controllers/mapController.go130
-rw-r--r--backend/controllers/recordController.go59
-rw-r--r--backend/controllers/userController.go10
-rw-r--r--backend/database/categories.sql5
-rw-r--r--backend/database/chapters.sql17
-rw-r--r--backend/database/games.sql3
-rw-r--r--backend/database/history.sql279
-rw-r--r--backend/database/init.sql109
-rw-r--r--backend/database/maps.sql228
-rw-r--r--backend/middleware/auth.go2
-rw-r--r--backend/models/models.go49
-rw-r--r--backend/routes/routes.go7
14 files changed, 681 insertions, 225 deletions
diff --git a/backend/controllers/homeController.go b/backend/controllers/homeController.go
index 539d2c4..577f61a 100644
--- a/backend/controllers/homeController.go
+++ b/backend/controllers/homeController.go
@@ -29,7 +29,7 @@ func Home(c *gin.Context) {
29// @Failure 400 {object} models.Response 29// @Failure 400 {object} models.Response
30// @Router /demo [get] 30// @Router /demo [get]
31func Rankings(c *gin.Context) { 31func Rankings(c *gin.Context) {
32 rows, err := database.DB.Query(`SELECT steam_id, username FROM users;`) 32 rows, err := database.DB.Query(`SELECT steam_id, user_name FROM users;`)
33 if err != nil { 33 if err != nil {
34 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) 34 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
35 return 35 return
@@ -56,7 +56,7 @@ func Rankings(c *gin.Context) {
56 if uniqueSingleUserRecords == totalSingleMaps { 56 if uniqueSingleUserRecords == totalSingleMaps {
57 var ranking models.UserRanking 57 var ranking models.UserRanking
58 ranking.UserID = userID 58 ranking.UserID = userID
59 ranking.Username = username 59 ranking.UserName = username
60 sql := `SELECT DISTINCT map_id, score_count FROM records_sp WHERE user_id = $1 ORDER BY map_id, score_count;` 60 sql := `SELECT DISTINCT map_id, score_count FROM records_sp WHERE user_id = $1 ORDER BY map_id, score_count;`
61 rows, err := database.DB.Query(sql, userID) 61 rows, err := database.DB.Query(sql, userID)
62 if err != nil { 62 if err != nil {
@@ -90,7 +90,7 @@ func Rankings(c *gin.Context) {
90 if uniqueMultiUserRecords == totalMultiMaps { 90 if uniqueMultiUserRecords == totalMultiMaps {
91 var ranking models.UserRanking 91 var ranking models.UserRanking
92 ranking.UserID = userID 92 ranking.UserID = userID
93 ranking.Username = username 93 ranking.UserName = username
94 sql := `SELECT DISTINCT map_id, score_count FROM records_mp WHERE host_id = $1 OR partner_id = $2 ORDER BY map_id, score_count;` 94 sql := `SELECT DISTINCT map_id, score_count FROM records_mp WHERE host_id = $1 OR partner_id = $2 ORDER BY map_id, score_count;`
95 rows, err := database.DB.Query(sql, userID, userID) 95 rows, err := database.DB.Query(sql, userID, userID)
96 if err != nil { 96 if err != nil {
diff --git a/backend/controllers/loginController.go b/backend/controllers/loginController.go
index 6c0453b..4de4c00 100644
--- a/backend/controllers/loginController.go
+++ b/backend/controllers/loginController.go
@@ -56,7 +56,7 @@ func Login(c *gin.Context) {
56 user.LocCountryCode = "XX" 56 user.LocCountryCode = "XX"
57 } 57 }
58 // Insert new user to database 58 // Insert new user to database
59 database.DB.Exec(`INSERT INTO users (steam_id, username, avatar_link, country_code) 59 database.DB.Exec(`INSERT INTO users (steam_id, user_name, avatar_link, country_code)
60 VALUES ($1, $2, $3, $4)`, steamID, user.PersonaName, user.AvatarFull, user.LocCountryCode) 60 VALUES ($1, $2, $3, $4)`, steamID, user.PersonaName, user.AvatarFull, user.LocCountryCode)
61 } 61 }
62 // Generate JWT token 62 // Generate JWT token
diff --git a/backend/controllers/mapController.go b/backend/controllers/mapController.go
index 16dd669..bd85a97 100644
--- a/backend/controllers/mapController.go
+++ b/backend/controllers/mapController.go
@@ -3,26 +3,117 @@ package controllers
3import ( 3import (
4 "net/http" 4 "net/http"
5 "strconv" 5 "strconv"
6 "time"
6 7
7 "github.com/gin-gonic/gin" 8 "github.com/gin-gonic/gin"
9 "github.com/lib/pq"
8 "github.com/pektezol/leastportals/backend/database" 10 "github.com/pektezol/leastportals/backend/database"
9 "github.com/pektezol/leastportals/backend/models" 11 "github.com/pektezol/leastportals/backend/models"
10) 12)
11 13
12// GET Map 14// GET Map Summary
13// 15//
14// @Summary Get map page with specified id. 16// @Summary Get map summary with specified id.
15// @Tags maps 17// @Tags maps
16// @Accept json 18// @Accept json
17// @Produce json 19// @Produce json
18// @Param id path int true "Map ID" 20// @Param id path int true "Map ID"
19// @Success 200 {object} models.Response{data=models.Map} 21// @Success 200 {object} models.Response{data=models.Map{data=models.MapSummary}}
20// @Failure 400 {object} models.Response 22// @Failure 400 {object} models.Response
21// @Router /maps/{id} [get] 23// @Router /maps/{id}/summary [get]
22func FetchMap(c *gin.Context) { 24func FetchMapSummary(c *gin.Context) {
23 id := c.Param("id") 25 id := c.Param("id")
24 // Get map data 26 // Get map data
25 var mapData models.Map 27 var mapData models.Map
28 var mapSummaryData models.MapSummary
29 var mapHistoryData []models.MapHistory
30 intID, err := strconv.Atoi(id)
31 if err != nil {
32 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
33 return
34 }
35 mapData.ID = intID
36 var routers pq.StringArray
37 sql := `SELECT g.name, c.name, m.name, m.description, m.showcase,
38 (
39 SELECT array_agg(user_name)
40 FROM map_routers
41 WHERE map_id = $1
42 AND score_count = (
43 SELECT score_count
44 FROM map_history
45 WHERE map_id = $1
46 ORDER BY score_count
47 LIMIT 1
48 )
49 GROUP BY map_routers.user_name
50 ORDER BY user_name
51 ),
52 (
53 SELECT COALESCE(avg(rating), 0.0)
54 FROM map_ratings
55 WHERE map_id = $1
56 )
57 FROM maps m
58 INNER JOIN games g ON m.game_id = g.id
59 INNER JOIN chapters c ON m.chapter_id = c.id
60 WHERE m.id = $1;`
61 // TODO: CategoryScores
62 err = database.DB.QueryRow(sql, id).Scan(&mapData.GameName, &mapData.ChapterName, &mapData.MapName, &mapSummaryData.Description, &mapSummaryData.Showcase, &routers, &mapSummaryData.Rating)
63 if err != nil {
64 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
65 return
66 }
67 var historyNames pq.StringArray
68 var historyScores pq.Int32Array
69 var historyDates pq.StringArray
70 sql = `SELECT array_agg(user_name), array_agg(score_count), array_agg(record_date)
71 FROM map_history
72 WHERE map_id = $1;`
73 err = database.DB.QueryRow(sql, id).Scan(&historyNames, &historyScores, &historyDates)
74 if err != nil {
75 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
76 return
77 }
78 for i := 0; i < len(historyNames); i++ {
79 var history models.MapHistory
80 history.RunnerName = historyNames[i]
81 history.ScoreCount = int(historyScores[i])
82 layout := "2006-01-02 15:04:05"
83 date, err := time.Parse(layout, historyDates[i])
84 if err != nil {
85 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
86 return
87 }
88 history.Date = date
89 mapHistoryData = append(mapHistoryData, history)
90 }
91 mapSummaryData.History = mapHistoryData
92 mapSummaryData.Routers = routers
93 mapData.Data = mapSummaryData
94 // Return response
95 c.JSON(http.StatusOK, models.Response{
96 Success: true,
97 Message: "Successfully retrieved map summary.",
98 Data: mapData,
99 })
100}
101
102// GET Map Leaderboards
103//
104// @Summary Get map leaderboards with specified id.
105// @Tags maps
106// @Accept json
107// @Produce json
108// @Param id path int true "Map ID"
109// @Success 200 {object} models.Response{data=models.Map{data=models.MapRecords}}
110// @Failure 400 {object} models.Response
111// @Router /maps/{id}/leaderboards [get]
112func FetchMapLeaderboards(c *gin.Context) {
113 id := c.Param("id")
114 // Get map data
115 var mapData models.Map
116 var mapRecordsData models.MapRecords
26 var isDisabled bool 117 var isDisabled bool
27 intID, err := strconv.Atoi(id) 118 intID, err := strconv.Atoi(id)
28 if err != nil { 119 if err != nil {
@@ -30,8 +121,12 @@ func FetchMap(c *gin.Context) {
30 return 121 return
31 } 122 }
32 mapData.ID = intID 123 mapData.ID = intID
33 sql := `SELECT map_name, wr_score, wr_time, is_coop, is_disabled FROM maps WHERE id = $1;` 124 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) 125 FROM maps m
126 INNER JOIN games g ON m.game_id = g.id
127 INNER JOIN chapters c ON m.chapter_id = c.id
128 WHERE m.id = $1;`
129 err = database.DB.QueryRow(sql, id).Scan(&mapData.GameName, &mapData.ChapterName, &mapData.MapName, &isDisabled)
35 if err != nil { 130 if err != nil {
36 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) 131 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
37 return 132 return
@@ -40,13 +135,14 @@ func FetchMap(c *gin.Context) {
40 c.JSON(http.StatusBadRequest, models.ErrorResponse("Map is not available for competitive boards.")) 135 c.JSON(http.StatusBadRequest, models.ErrorResponse("Map is not available for competitive boards."))
41 return 136 return
42 } 137 }
138 // TODO: avatar and names for host & partner
43 // Get records from the map 139 // Get records from the map
44 if mapData.IsCoop { 140 if mapData.GameName == "Portal 2 - Cooperative" {
45 var records []models.RecordMP 141 var records []models.RecordMP
46 sql = `SELECT id, host_id, partner_id, score_count, score_time, host_demo_id, partner_demo_id, record_date 142 sql = `SELECT id, host_id, partner_id, score_count, score_time, host_demo_id, partner_demo_id, record_date
47 FROM ( 143 FROM (
48 SELECT id, host_id, partner_id, score_count, score_time, host_demo_id, partner_demo_id, record_date, 144 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 145 ROW_NUMBER() OVER (PARTITION BY host_id, partner_id ORDER BY score_count, score_time) AS rn
50 FROM records_mp 146 FROM records_mp
51 WHERE map_id = $1 147 WHERE map_id = $1
52 ) sub 148 ) sub
@@ -74,16 +170,17 @@ func FetchMap(c *gin.Context) {
74 records = append(records, record) 170 records = append(records, record)
75 placement++ 171 placement++
76 } 172 }
77 mapData.Records = records 173 mapRecordsData.Records = records
78 } else { 174 } else {
79 var records []models.RecordSP 175 var records []models.RecordSP
80 sql = `SELECT id, user_id, score_count, score_time, demo_id, record_date 176 sql = `SELECT id, user_id, users.user_name, users.avatar_link, score_count, score_time, demo_id, record_date
81 FROM ( 177 FROM (
82 SELECT id, user_id, score_count, score_time, demo_id, record_date, 178 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 179 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY score_count, score_time) AS rn
84 FROM records_sp 180 FROM records_sp
85 WHERE map_id = $1 181 WHERE map_id = $1
86 ) sub 182 ) sub
183 INNER JOIN users ON user_id = users.steam_id
87 WHERE rn = 1;` 184 WHERE rn = 1;`
88 rows, err := database.DB.Query(sql, id) 185 rows, err := database.DB.Query(sql, id)
89 if err != nil { 186 if err != nil {
@@ -94,7 +191,7 @@ func FetchMap(c *gin.Context) {
94 ties := 0 191 ties := 0
95 for rows.Next() { 192 for rows.Next() {
96 var record models.RecordSP 193 var record models.RecordSP
97 err := rows.Scan(&record.RecordID, &record.UserID, &record.ScoreCount, &record.ScoreTime, &record.DemoID, &record.RecordDate) 194 err := rows.Scan(&record.RecordID, &record.UserID, &record.UserName, &record.UserAvatar, &record.ScoreCount, &record.ScoreTime, &record.DemoID, &record.RecordDate)
98 if err != nil { 195 if err != nil {
99 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) 196 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
100 return 197 return
@@ -108,16 +205,13 @@ func FetchMap(c *gin.Context) {
108 records = append(records, record) 205 records = append(records, record)
109 placement++ 206 placement++
110 } 207 }
111 mapData.Records = records 208 mapRecordsData.Records = records
112 } 209 }
210 mapData.Data = mapRecordsData
113 // Return response 211 // Return response
114 c.JSON(http.StatusOK, models.Response{ 212 c.JSON(http.StatusOK, models.Response{
115 Success: true, 213 Success: true,
116 Message: "Successfully retrieved map data.", 214 Message: "Successfully retrieved map leaderboards.",
117 Data: mapData, 215 Data: mapData,
118 }) 216 })
119} 217}
120
121func CreateMapCommunity(c *gin.Context) {
122
123}
diff --git a/backend/controllers/recordController.go b/backend/controllers/recordController.go
index 3b4bdc7..bafa844 100644
--- a/backend/controllers/recordController.go
+++ b/backend/controllers/recordController.go
@@ -43,11 +43,11 @@ func CreateRecordWithDemo(c *gin.Context) {
43 return 43 return
44 } 44 }
45 // Check if map is sp or mp 45 // Check if map is sp or mp
46 var wrScore int 46 var gameID int
47 var wrTime int
48 var isCoop bool 47 var isCoop bool
49 var isDisabled bool 48 var isDisabled bool
50 err := database.DB.QueryRow(`SELECT wr_score, wr_time, is_coop, is_disabled FROM maps WHERE id = $1;`, mapId).Scan(&wrScore, &wrTime, &isCoop, &isDisabled) 49 sql := `SELECT game_id, is_disabled FROM maps WHERE id = $1;`
50 err := database.DB.QueryRow(sql, mapId).Scan(&gameID, &isDisabled)
51 if err != nil { 51 if err != nil {
52 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) 52 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
53 return 53 return
@@ -56,6 +56,9 @@ func CreateRecordWithDemo(c *gin.Context) {
56 c.JSON(http.StatusBadRequest, models.ErrorResponse("Map is not available for competitive boards.")) 56 c.JSON(http.StatusBadRequest, models.ErrorResponse("Map is not available for competitive boards."))
57 return 57 return
58 } 58 }
59 if gameID == 2 {
60 isCoop = true
61 }
59 // Get record request 62 // Get record request
60 var record models.RecordRequest 63 var record models.RecordRequest
61 score_count, err := strconv.Atoi(c.PostForm("score_count")) 64 score_count, err := strconv.Atoi(c.PostForm("score_count"))
@@ -104,6 +107,14 @@ func CreateRecordWithDemo(c *gin.Context) {
104 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) 107 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
105 return 108 return
106 } 109 }
110 // Create database transaction for inserts
111 tx, err := database.DB.Begin()
112 if err != nil {
113 c.JSON(http.StatusInternalServerError, models.ErrorResponse(err.Error()))
114 return
115 }
116 // Defer to a rollback in case anything fails
117 defer tx.Rollback()
107 fileID := "" 118 fileID := ""
108 for i, header := range files { 119 for i, header := range files {
109 uuid := uuid.New().String() 120 uuid := uuid.New().String()
@@ -131,7 +142,7 @@ func CreateRecordWithDemo(c *gin.Context) {
131 if i == 1 { 142 if i == 1 {
132 partnerDemoUUID = uuid 143 partnerDemoUUID = uuid
133 } 144 }
134 _, err = database.DB.Exec(`INSERT INTO demos (id,location_id) VALUES ($1,$2)`, uuid, file.Id) 145 _, err = tx.Exec(`INSERT INTO demos (id,location_id) VALUES ($1,$2)`, uuid, file.Id)
135 if err != nil { 146 if err != nil {
136 deleteFile(srv, file.Id) 147 deleteFile(srv, file.Id)
137 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) 148 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
@@ -152,37 +163,41 @@ func CreateRecordWithDemo(c *gin.Context) {
152 partnerID = user.(models.User).SteamID 163 partnerID = user.(models.User).SteamID
153 hostID = record.PartnerID 164 hostID = record.PartnerID
154 } 165 }
155 _, err := database.DB.Exec(sql, mapId, record.ScoreCount, record.ScoreTime, hostID, partnerID, hostDemoUUID, partnerDemoUUID) 166 _, err := tx.Exec(sql, mapId, record.ScoreCount, record.ScoreTime, hostID, partnerID, hostDemoUUID, partnerDemoUUID)
156 if err != nil { 167 if err != nil {
157 deleteFile(srv, fileID) 168 deleteFile(srv, fileID)
158 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) 169 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
159 return 170 return
160 } 171 }
161 // If a new world record based on portal count 172 // If a new world record based on portal count
162 if record.ScoreCount < wrScore { 173 // if record.ScoreCount < wrScore {
163 _, err := database.DB.Exec(`UPDATE maps SET wr_score = $1, wr_time = $2 WHERE id = $3;`, record.ScoreCount, record.ScoreTime, mapId) 174 // _, err := tx.Exec(`UPDATE maps SET wr_score = $1, wr_time = $2 WHERE id = $3;`, record.ScoreCount, record.ScoreTime, mapId)
164 if err != nil { 175 // if err != nil {
165 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) 176 // c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
166 return 177 // return
167 } 178 // }
168 } 179 // }
169 } else { 180 } else {
170 sql := `INSERT INTO records_sp(map_id,score_count,score_time,user_id,demo_id) 181 sql := `INSERT INTO records_sp(map_id,score_count,score_time,user_id,demo_id)
171 VALUES($1, $2, $3, $4, $5);` 182 VALUES($1, $2, $3, $4, $5);`
172 _, err := database.DB.Exec(sql, mapId, record.ScoreCount, record.ScoreTime, user.(models.User).SteamID, hostDemoUUID) 183 _, err := tx.Exec(sql, mapId, record.ScoreCount, record.ScoreTime, user.(models.User).SteamID, hostDemoUUID)
173 if err != nil { 184 if err != nil {
174 deleteFile(srv, fileID) 185 deleteFile(srv, fileID)
175 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) 186 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
176 return 187 return
177 } 188 }
178 // If a new world record based on portal count 189 // If a new world record based on portal count
179 if record.ScoreCount < wrScore { 190 // if record.ScoreCount < wrScore {
180 _, err := database.DB.Exec(`UPDATE maps SET wr_score = $1, wr_time = $2 WHERE id = $3;`, record.ScoreCount, record.ScoreTime, mapId) 191 // _, err := tx.Exec(`UPDATE maps SET wr_score = $1, wr_time = $2 WHERE id = $3;`, record.ScoreCount, record.ScoreTime, mapId)
181 if err != nil { 192 // if err != nil {
182 c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error())) 193 // c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
183 return 194 // return
184 } 195 // }
185 } 196 // }
197 }
198 if err = tx.Commit(); err != nil {
199 c.JSON(http.StatusInternalServerError, models.ErrorResponse(err.Error()))
200 return
186 } 201 }
187 c.JSON(http.StatusOK, models.Response{ 202 c.JSON(http.StatusOK, models.Response{
188 Success: true, 203 Success: true,
@@ -198,10 +213,10 @@ func CreateRecordWithDemo(c *gin.Context) {
198// @Tags demo 213// @Tags demo
199// @Accept json 214// @Accept json
200// @Produce octet-stream 215// @Produce octet-stream
201// @Param uuid path int true "Demo UUID" 216// @Param uuid query int true "Demo UUID"
202// @Success 200 {file} binary "Demo File" 217// @Success 200 {file} binary "Demo File"
203// @Failure 400 {object} models.Response 218// @Failure 400 {object} models.Response
204// @Router /demo [get] 219// @Router /demos [get]
205func DownloadDemoWithID(c *gin.Context) { 220func DownloadDemoWithID(c *gin.Context) {
206 uuid := c.Query("uuid") 221 uuid := c.Query("uuid")
207 var locationID string 222 var locationID string
diff --git a/backend/controllers/userController.go b/backend/controllers/userController.go
index c86a739..5ad800d 100644
--- a/backend/controllers/userController.go
+++ b/backend/controllers/userController.go
@@ -88,7 +88,7 @@ func Profile(c *gin.Context) {
88 Data: models.ProfileResponse{ 88 Data: models.ProfileResponse{
89 Profile: true, 89 Profile: true,
90 SteamID: user.(models.User).SteamID, 90 SteamID: user.(models.User).SteamID,
91 Username: user.(models.User).Username, 91 UserName: user.(models.User).UserName,
92 AvatarLink: user.(models.User).AvatarLink, 92 AvatarLink: user.(models.User).AvatarLink,
93 CountryCode: user.(models.User).CountryCode, 93 CountryCode: user.(models.User).CountryCode,
94 ScoresSP: scoresSP, 94 ScoresSP: scoresSP,
@@ -108,7 +108,7 @@ func Profile(c *gin.Context) {
108// @Success 200 {object} models.Response{data=models.ProfileResponse} 108// @Success 200 {object} models.Response{data=models.ProfileResponse}
109// @Failure 400 {object} models.Response 109// @Failure 400 {object} models.Response
110// @Failure 404 {object} models.Response 110// @Failure 404 {object} models.Response
111// @Router /user/{id} [get] 111// @Router /users/{id} [get]
112func FetchUser(c *gin.Context) { 112func FetchUser(c *gin.Context) {
113 id := c.Param("id") 113 id := c.Param("id")
114 // Check if id is all numbers and 17 length 114 // Check if id is all numbers and 17 length
@@ -120,7 +120,7 @@ func FetchUser(c *gin.Context) {
120 // Check if user exists 120 // Check if user exists
121 var user models.User 121 var user models.User
122 err := database.DB.QueryRow(`SELECT * FROM users WHERE steam_id = $1;`, id).Scan( 122 err := database.DB.QueryRow(`SELECT * FROM users WHERE steam_id = $1;`, id).Scan(
123 &user.SteamID, &user.Username, &user.AvatarLink, &user.CountryCode, 123 &user.SteamID, &user.UserName, &user.AvatarLink, &user.CountryCode,
124 &user.CreatedAt, &user.UpdatedAt) 124 &user.CreatedAt, &user.UpdatedAt)
125 if user.SteamID == "" { 125 if user.SteamID == "" {
126 // User does not exist 126 // User does not exist
@@ -190,7 +190,7 @@ func FetchUser(c *gin.Context) {
190 Data: models.ProfileResponse{ 190 Data: models.ProfileResponse{
191 Profile: true, 191 Profile: true,
192 SteamID: user.SteamID, 192 SteamID: user.SteamID,
193 Username: user.Username, 193 UserName: user.UserName,
194 AvatarLink: user.AvatarLink, 194 AvatarLink: user.AvatarLink,
195 CountryCode: user.CountryCode, 195 CountryCode: user.CountryCode,
196 ScoresSP: scoresSP, 196 ScoresSP: scoresSP,
@@ -236,7 +236,7 @@ func UpdateUser(c *gin.Context) {
236 Data: models.ProfileResponse{ 236 Data: models.ProfileResponse{
237 Profile: true, 237 Profile: true,
238 SteamID: user.(models.User).SteamID, 238 SteamID: user.(models.User).SteamID,
239 Username: profile.PersonaName, 239 UserName: profile.PersonaName,
240 AvatarLink: profile.AvatarFull, 240 AvatarLink: profile.AvatarFull,
241 CountryCode: profile.LocCountryCode, 241 CountryCode: profile.LocCountryCode,
242 }, 242 },
diff --git a/backend/database/categories.sql b/backend/database/categories.sql
new file mode 100644
index 0000000..5cadad5
--- /dev/null
+++ b/backend/database/categories.sql
@@ -0,0 +1,5 @@
1INSERT INTO categories(id, name) VALUES
2(1, 'CM'),
3(2, 'No SLA'),
4(3, 'Inbounds SLA'),
5(4, 'Any%'); \ No newline at end of file
diff --git a/backend/database/chapters.sql b/backend/database/chapters.sql
new file mode 100644
index 0000000..f01ae9c
--- /dev/null
+++ b/backend/database/chapters.sql
@@ -0,0 +1,17 @@
1INSERT INTO chapters(id, game_id, name) VALUES
2(1, 1, 'The Coutesy Call'),
3(2, 1, 'The Cold Boot'),
4(3, 1, 'The Return'),
5(4, 1, 'The Surprise'),
6(5, 1, 'The Escape'),
7(6, 1, 'The Fall'),
8(7, 1, 'The Reunion'),
9(8, 1, 'The Itch'),
10(9, 1, 'The Part Where He Kills You'),
11(10, 2, 'Introduction'),
12(11, 2, 'Team Building'),
13(12, 2, 'Mass And Velocity'),
14(13, 2, 'Hard-Light Surfaces'),
15(14, 2, 'Excursion Funnels'),
16(15, 2, 'Mobility Gels'),
17(16, 2, 'Art Therapy'); \ No newline at end of file
diff --git a/backend/database/games.sql b/backend/database/games.sql
new file mode 100644
index 0000000..5e2f4ee
--- /dev/null
+++ b/backend/database/games.sql
@@ -0,0 +1,3 @@
1INSERT INTO games(id, name) VALUES
2(1, 'Portal 2 - Singleplayer'),
3(2, 'Portal 2 - Cooperative'); \ No newline at end of file
diff --git a/backend/database/history.sql b/backend/database/history.sql
new file mode 100644
index 0000000..0840bf3
--- /dev/null
+++ b/backend/database/history.sql
@@ -0,0 +1,279 @@
1INSERT INTO map_history(map_id,user_name,score_count,record_date) VALUES
2-- Portal 2 Singleplayer
3-- 1
4(3,'slmid1995',3,'2011-10-05 00:00:00'),
5(3,'LookLikeAKango',1,'2011-10-06 00:00:00'),
6(3,'Bananasaurus Rex',0,'2011-10-24 00:00:00'),
7(4,'Tyronis',1,'2011-10-05 00:00:00'),
8(4,'Krzyhau',0,'2019-05-10 00:00:00'),
9(5,'LookLikeAKango',2,'2011-10-05 00:00:00'),
10(5,'Jetwash',1,'2013-12-03 00:00:00'),
11(6,'Stimich',4,'2011-10-08 00:00:00'),
12(6,'aepaePolakrn',3,'2011-10-19 00:00:00'),
13(6,'Krzyhau',2,'2020-10-10 00:00:00'),
14(9,'slmid1995',4,'2011-10-05 00:00:00'),
15(9,'Jokie',3,'2011-10-05 00:00:00'),
16(9,'Tyronis',2,'2011-10-05 00:00:00'),
17(9,'sicklebrick',0,'2013-03-13 00:00:00'),
18-- 2
19(10,'Paraxade0',2,'2011-04-21 00:00:00'),
20(10,'PerOculos',0,'2011-04-21 00:00:00'),
21(11,'Tyronis',2,'2011-10-05 00:00:00'),
22(11,'Krzyhau',0,'2018-06-09 00:00:00'),
23(12,'slmid1995',2,'2011-10-04 00:00:00'),
24(13,'LookLikeAKango',3,'2011-10-05 00:00:00'),
25(13,'Imanex',2,'2011-12-08 00:00:00'),
26(13,'jyjey',0,'2012-08-22 00:00:00'),
27(15,'Tyronis',2,'2011-10-05 00:00:00'),
28(16,'LookLikeAKango',2,'2011-10-05 00:00:00'),
29(16,'jyjey',0,'2012-08-25 00:00:00'),
30(17,'rocoty',0,'2011-10-05 00:00:00'),
31(17,'Nidboj132',0,'2023-02-05 00:00:00'),
32-- 3
33(18,'The Last Tofus',5,'2011-05-08 00:00:00'),
34(18,'Schlepian',4,'2011-10-08 00:00:00'),
35(18,'szeimartin',3,'2013-10-08 00:00:00'),
36(18,'Krzyhau',2,'2020-05-15 00:00:00'),
37(18,'Krzyhau',0,'2022-07-02 00:00:00'),
38(19,'LookLikeAKango',2,'2011-10-06 00:00:00'),
39(20,'Djinndrache',5,'2011-10-20 00:00:00'),
40(20,'Schlepian',4,'2011-10-30 00:00:00'),
41(20,'Jetwash',3,'2014-09-04 00:00:00'),
42(20,'Krzyhau',2,'2022-04-24 00:00:00'),
43(21,'LookLikeAKango',4,'2011-10-06 00:00:00'),
44(21,'ncla',2,'2011-10-30 00:00:00'),
45(21,'PerOculos',0,'2019-07-08 00:00:00'),
46(22,'Tyronis',0,'2011-10-05 00:00:00'),
47(23,'LookLikeAKango',2,'2011-10-06 00:00:00'),
48(23,'Krzyhau',0,'2018-08-01 00:00:00'),
49(24,'LeviHB',0,'2011-04-30 00:00:00'),
50(25,'Tyronis',0,'2011-10-06 00:00:00'),
51(26,'Schlepian',3,'2011-10-30 00:00:00'),
52(26,'Tyronis',2,'2012-01-08 00:00:00'),
53(26,'PerOculos',0,'2016-06-08 00:00:00'),
54-- 4
55(27,'LeviHB',2,'2011-05-01 00:00:00'),
56(27,'PerOculos',0,'2020-07-13 00:00:00'),
57(28,'LeviHB',7,'2011-05-01 00:00:00'),
58(28,'Andy M.J.',2,'2011-10-07 00:00:00'),
59(28,'Krzyhau',0,'2018-05-19 00:00:00'),
60(29,'LeviHB',0,'2011-05-01 00:00:00'),
61(30,'Schlepian',2,'2011-10-30 00:00:00'),
62(31,'Tyronis',0,'2011-10-06 00:00:00'),
63-- 5
64(32,'Tyronis',6,'2011-10-21 00:00:00'),
65(32,'Nidboj132',5,'2022-04-24 00:00:00'),
66(33,'Tyronis',7,'2011-10-06 00:00:00'),
67(33,'ISimmo',5,'2011-11-02 00:00:00'),
68(33,'PerOculos',4,'2017-05-30 00:00:00'),
69(34,'Schlepian',3,'2011-11-01 00:00:00'),
70(34,'Krzyhau',2,'2020-10-14 00:00:00'),
71(34,'zach',0,'2022-11-02 00:00:00'),
72(35,'Krank',2,'2012-07-28 00:00:00'),
73-- 6
74(36,'Tyronis',6,'2011-10-06 00:00:00'),
75(36,'CalmlyFrenetic',5,'2011-10-09 00:00:00'),
76(36,'sicklebrick',4,'2012-09-13 00:00:00'),
77(36,'Nidboj132',2,'2023-03-04 00:00:00'),
78(37,'LookLikeAKango',7,'2011-10-06 00:00:00'),
79(37,'Schlepian',6,'2011-11-01 00:00:00'),
80(37,'Tyronis',5,'2012-01-28 00:00:00'),
81(37,'Nidboj132',4,'2021-08-22 00:00:00'),
82(38,'Andy M.J.',2,'2011-10-06 00:00:00'),
83(38,'Sanguine Dagger',0,'2012-03-19 00:00:00'),
84(39,'Lambda Core',6,'2011-05-13 00:00:00'),
85(39,'The Last Tofus',5,'2011-05-13 00:00:00'),
86(39,'LookLikeAKango',4,'2011-10-16 00:00:00'),
87(39,'Kittaye',3,'2013-03-25 00:00:00'),
88(40,'LookLikeAKango',7,'2011-10-07 00:00:00'),
89(40,'Schlepian',6,'2011-11-05 00:00:00'),
90(40,'Kittaye',4,'2013-04-01 00:00:00'),
91(40,'Kittaye',3,'2014-09-13 00:00:00'),
92(40,'szeimartin',2,'2014-09-13 00:00:00'),
93(40,'Kittaye',0,'2014-09-15 00:00:00'),
94(41,'CalmlyFrenetic',7,'2011-10-09 00:00:00'),
95(41,'Jaso',6,'2011-10-11 00:00:00'),
96(41,'Krank',5,'2012-07-17 00:00:00'),
97-- 7
98(42,'LookLikeAKango',4,'2011-05-17 00:00:00'),
99(42,'ISimmo',2,'2011-11-07 00:00:00'),
100(43,'lmao4ever',5,'2011-10-30 00:00:00'),
101(43,'Jaso',2,'2011-11-09 00:00:00'),
102(43,'feliser',0,'2022-06-26 00:00:00'),
103(44,'LookLikeAKango',18,'2011-10-07 00:00:00'),
104(44,'Tyronis',13,'2011-10-30 00:00:00'),
105(44,'Tyronis',12,'2011-11-10 00:00:00'),
106(44,'Jetwash',11,'2017-06-12 00:00:00'),
107(44,'Krzyhau',9,'2022-01-02 00:00:00'),
108(45,'LookLikeAKango',23,'2011-10-08 00:00:00'),
109(45,'CalmlyFrenetic',22,'2011-10-09 00:00:00'),
110(45,'cgreactor',17,'2011-10-09 00:00:00'),
111(45,'CalmlyFrenetic',16,'2011-10-10 00:00:00'),
112(45,'LookLikeAKango',15,'2011-10-19 00:00:00'),
113(45,'Jaso',12,'2012-07-19 00:00:00'),
114(45,'Krank',10,'2013-01-31 00:00:00'),
115(45,'Kittaye',7,'2013-04-04 00:00:00'),
116(45,'PerOculos',4,'2014-09-13 00:00:00'),
117-- 8
118(46,'sparkle1princess',6,'2012-03-24 00:00:00'),
119(46,'Krzyhau',2,'2019-11-21 00:00:00'),
120(47,'holydevel',2,'2011-10-06 00:00:00'),
121(47,'JesusCatFace',0,'2015-01-16 00:00:00'),
122(48,'LookLikeAKango',5,'2011-10-08 00:00:00'),
123(48,'Tyronis',2,'2011-10-08 00:00:00'),
124(48,'adzicents',0,'2011-10-09 00:00:00'),
125(49,'adzicents',4,'2011-10-07 00:00:00'),
126(49,'Schlepian',2,'2011-10-08 00:00:00'),
127(49,'Nidboj132',0,'2022-09-26 00:00:00'),
128(50,'LookLikeAKango',4,'2011-10-08 00:00:00'),
129(50,'Tyronis',2,'2011-10-11 00:00:00'),
130(50,'sicklebrick',0,'2013-03-20 00:00:00'),
131(51,'Andy M.J.',3,'2011-10-08 00:00:00'),
132(51,'LookLikeAKango',2,'2011-10-20 00:00:00'),
133(52,'Jaso',0,'2011-10-10 00:00:00'),
134(53,'LookLikeAKango',9,'2011-10-08 00:00:00'),
135(53,'LookLikeAKango',2,'2011-10-20 00:00:00'),
136(53,'Schlepian',0,'2011-11-06 00:00:00'),
137(54,'LookLikeAKango',7,'2011-06-01 00:00:00'),
138(54,'Jaso',6,'2011-10-09 00:00:00'),
139(54,'Schlepian',5,'2011-11-06 00:00:00'),
140(54,'Spyrunite',4,'2012-08-30 00:00:00'),
141(54,'Krzyhau',3,'2019-04-22 00:00:00'),
142(55,'LookLikeAKango',7,'2011-10-08 00:00:00'),
143(55,'CalmlyFrenetic',3,'2011-10-09 00:00:00'),
144(55,'Jaso',2,'2011-11-26 00:00:00'),
145(55,'PerOculos',0,'2021-02-06 00:00:00'),
146(56,'CalmlyFrenetic',9,'2011-10-08 00:00:00'),
147(56,'LookLikeAKango',5,'2011-10-09 00:00:00'),
148(56,'CalmlyFrenetic',4,'2011-10-09 00:00:00'),
149(56,'Jetwash',2,'2014-09-05 00:00:00'),
150-- 9
151(57,'JNS',7,'2011-07-21 00:00:00'),
152(57,'Krank',5,'2012-07-29 00:00:00'),
153(57,'Krzyhau',0,'2017-10-29 00:00:00'),
154(58,'Stimich',2,'2011-10-11 00:00:00'),
155(59,'Isimmo',7,'2011-11-04 00:00:00'),
156(59,'sicklebrick',6,'2013-03-20 00:00:00'),
157(60,'CalmlyFrenetic',7,'2011-10-19 00:00:00'),
158(60,'Tyronis',6,'2011-11-01 00:00:00'),
159-- Portal 2 Cooperative
160-- 1
161(63,'Mathias123961 & Sir Spawn Alot',0,'2011-08-01 00:00:00'),
162(64,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
163(64,'Chubfish & Exhale',2,'2011-11-01 00:00:00'),
164(65,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
165(65,'Nidboj132 & Oryn',3,'2022-02-03 00:00:00'),
166(66,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
167(66,'Schlepian & Chubfish',2,'2011-10-01 00:00:00'),
168(67,'Mathias123961 & Sir Spawn Alot',0,'2011-08-01 00:00:00'),
169(68,'Mathias123961 & Sir Spawn Alot',0,'2011-08-01 00:00:00'),
170-- 2
171(69,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
172(70,'Mathias123961 & Sir Spawn Alot',6,'2011-08-01 00:00:00'),
173(70,'Schlepian & Chubfish',4,'2011-10-01 00:00:00'),
174(70,'Gocnak & z1mb0bw4y',2,'2012-08-03 00:00:00'),
175(70,'DM_ & VEGA',0,'2017-10-01 00:00:00'),
176(71,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
177(71,'Mathias123961 & Sir Spawn Alot',0,'2011-08-01 00:00:00'),
178(72,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
179(72,'Schlepian & LongJohnDickWeed',2,'2011-10-01 00:00:00'),
180(73,'Stimich & HiTMaRkS',9,'2011-05-09 00:00:00'),
181(73,'Mathias123961 & Sir Spawn Alot',8,'2011-08-01 00:00:00'),
182(73,'Schlepian & Lemonsunshine',7,'2011-11-01 00:00:00'),
183(73,'DM_ & LsDK_',6,'2018-01-01 00:00:00'),
184(73,'Krzyhau & Klooger',4,'2018-11-01 00:00:00'),
185(74,'Mathias123961 & Sir Spawn Alot',5,'2011-08-01 00:00:00'),
186(74,'Stimich & Pitkakorva',7,'2011-10-11 00:00:00'),
187(74,'Schlepian & Isimmo',3,'2011-10-28 00:00:00'),
188(74,'Zypeh & szeimartin',2,'2013-11-01 00:00:00'),
189(75,'Mathias123961 & Sir Spawn Alot',5,'2011-08-01 00:00:00'),
190(75,'Schlepian & Urination',4,'2011-10-01 00:00:00'),
191(75,'Schlepian & Lemonsunshine',2,'2012-02-01 00:00:00'),
192(75,'DM_ & follon',0,'2015-04-01 00:00:00'),
193(76,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
194(76,'Chubfish & Exhale',0,'2011-12-01 00:00:00'),
195-- 3
196(77,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
197(78,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
198(78,'DM_ & marK',3,'2016-11-01 00:00:00'),
199(78,'Nidboj132 & Oryn',2,'2021-09-04 00:00:00'),
200(79,'ganonscrub & ?',5,'2011-04-01 00:00:00'),
201(79,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
202(79,'Chubfish & Exhale',2,'2012-08-04 00:00:00'),
203(80,'Mathias123961 & Sir Spawn Alot',5,'2011-08-01 00:00:00'),
204(80,'Chubfish & Exhale',4,'2011-12-01 00:00:00'),
205(81,'Mathias123961 & Sir Spawn Alot',7,'2011-08-01 00:00:00'),
206(81,'Schlepian & Lemonsunshine',6,'2011-10-01 00:00:00'),
207(81,'takz & dawn',5,'2011-11-01 00:00:00'),
208(81,'Nidboj132 & Oryn',4,'2021-03-25 00:00:00'),
209(82,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
210(83,'Mathias123961 & Sir Spawn Alot',5,'2011-08-01 00:00:00'),
211(83,'Schlepian & Lemonsunshine',2,'2011-10-01 00:00:00'),
212(83,'Chubfish & Exhale',0,'2011-12-01 00:00:00'),
213(84,'Mathias123961 & Sir Spawn Alot',6,'2011-08-01 00:00:00'),
214(84,'Schlepian & Chubfish',4,'2011-10-01 00:00:00'),
215(84,'Chubfish & Exhale',2,'2012-01-01 00:00:00'),
216(84,'DM_ & wS',0,'2015-05-01 00:00:00'),
217-- 4
218(85,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
219(85,'Chubfish & Exhale',0,'2011-10-01 00:00:00'),
220(86,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
221(86,'Chubfish & Exhale',0,'2011-12-01 00:00:00'),
222(87,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
223(87,'Schlepian & Gopherdude',2,'2011-10-01 00:00:00'),
224(87,'DM_ & follon',0,'2015-04-01 00:00:00'),
225(88,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
226(88,'Schlepian & Gopherdude',0,'2011-10-01 00:00:00'),
227(89,'Mathias123961 & Sir Spawn Alot',0,'2011-08-01 00:00:00'),
228(90,'Mathias123961 & Sir Spawn Alot',4,'2011-09-01 00:00:00'),
229(90,'Schlepian & Urination',2,'2011-10-01 00:00:00'),
230(90,'Klooger & Jetwash',0,'2016-08-01 00:00:00'),
231(91,'Mathias123961 & Sir Spawn Alot',2,'2011-08-01 00:00:00'),
232(91,'Undead & Zypeh',0,'2013-05-19 00:00:00'),
233(92,'txx478 & ?',5,'2011-05-01 00:00:00'),
234(92,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
235(92,'Schlepian & Gopherdude',2,'2011-10-01 00:00:00'),
236(92,'ncla & takz',0,'2012-02-01 00:00:00'),
237(93,'Mathias123961 & Sir Spawn Alot',2,'2011-08-01 00:00:00'),
238(93,'Schlepian & Gopherdude',0,'2011-10-01 00:00:00'),
239-- 5
240(94,'Chubfish & Exhale',2,'2011-10-01 00:00:00'),
241(94,'Klooger & Imanex',0,'2013-08-01 00:00:00'),
242(95,'Schlepian & Issimoi',2,'2011-10-01 00:00:00'),
243(96,'ThePortalPatrol & ?',4,'2011-04-01 00:00:00'),
244(96,'sparkle1princess & Zypeh',2,'2014-01-01 00:00:00'),
245(97,'Stimich & HiTMaRkS',7,'2011-05-13 00:00:00'),
246(97,'Schlepian & Lemonsunshine',4,'2011-10-01 00:00:00'),
247(97,'DM_ & wS',2,'2014-05-01 00:00:00'),
248(98,'Imanex & 00svo',0,'2011-11-01 00:00:00'),
249(99,'Schlepian & Gopherdude',3,'2011-10-01 00:00:00'),
250(99,'Imanex & Klooger',2,'2013-08-01 00:00:00'),
251(99,'DM_ & wS',0,'2015-05-01 00:00:00'),
252(100,'Schlepian & Bananasaurus Rex',0,'2011-10-01 00:00:00'),
253(101,'Chubfish & Exhale',2,'2011-12-01 00:00:00'),
254(101,'DM_ & follon',0,'2015-04-01 00:00:00'),
255-- 6
256(102,'dawn & takz',3,'2011-11-18 00:00:00'),
257(102,'Chubfish & Exhale',2,'2012-01-01 00:00:00'),
258(102,'Imanex & Klooger',0,'2013-08-01 00:00:00'),
259(103,'Schlepian & Lemonsunshine',0,'2011-10-01 00:00:00'),
260(104,'Schlepian & Lemonsunshine',0,'2011-10-01 00:00:00'),
261(105,'Blaizerazer & ?',8,'2011-10-01 00:00:00'),
262(105,'Schlepian & Lemonsunshine',5,'2011-11-01 00:00:00'),
263(105,'Imanex & Klooger',4,'2013-08-01 00:00:00'),
264(105,'DM_ & wS',3,'2014-05-01 00:00:00'),
265(105,'DM_ & follon',2,'2015-04-01 00:00:00'),
266(106,'Schlepian & Bananasaurus Rex',4,'2011-10-01 00:00:00'),
267(106,'Gig & takz',3,'2012-06-01 00:00:00'),
268(106,'Imanex & Klooger',0,'2013-06-01 00:00:00'),
269(107,'Chubfish & Exhale',2,'2011-10-01 00:00:00'),
270(107,'DM_ & follon',0,'2015-04-01 00:00:00'),
271(108,'DaFox & P',0,'2011-12-01 00:00:00'),
272(109,'Schlepian & Tyronis',5,'2011-10-01 00:00:00'),
273(109,'Chubfish & Exhale',0,'2011-12-01 00:00:00'),
274(110,'Tyronis & mr.bob806',15,'2011-10-01 00:00:00'),
275(110,'Schlepian & Chubfish',6,'2011-11-01 00:00:00'),
276(110,'00svo & z1mb0bw4y',5,'2012-08-08 00:00:00'),
277(110,'00svo & z1mb0bw4y',4,'2012-08-10 00:00:00'),
278(110,'Klooger & z1mb0bw4y',2,'2014-02-01 00:00:00'),
279(110,'DM_ & follon',0,'2015-04-01 00:00:00'); \ No newline at end of file
diff --git a/backend/database/init.sql b/backend/database/init.sql
index 7e225f0..51a4881 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -1,16 +1,6 @@
1DROP TABLE IF EXISTS showcases;
2DROP TABLE IF EXISTS titles;
3DROP TABLE IF EXISTS records_sp;
4DROP TABLE IF EXISTS records_mp;
5DROP TABLE IF EXISTS maps;
6DROP TABLE IF EXISTS users;
7DROP TABLE IF EXISTS countries;
8
9DROP TABLE IF EXISTS demos;
10
11CREATE TABLE users ( 1CREATE TABLE users (
12 steam_id TEXT, 2 steam_id TEXT,
13 username TEXT NOT NULL, 3 user_name TEXT NOT NULL,
14 avatar_link TEXT NOT NULL, 4 avatar_link TEXT NOT NULL,
15 country_code CHAR(2) NOT NULL, 5 country_code CHAR(2) NOT NULL,
16 created_at TIMESTAMP NOT NULL DEFAULT now(), 6 created_at TIMESTAMP NOT NULL DEFAULT now(),
@@ -18,14 +8,68 @@ CREATE TABLE users (
18 PRIMARY KEY (steam_id) 8 PRIMARY KEY (steam_id)
19); 9);
20 10
11CREATE TABLE games (
12 id SMALLSERIAL,
13 name TEXT NOT NULL,
14 PRIMARY KEY (id)
15);
16
17CREATE TABLE chapters (
18 id SMALLSERIAL,
19 game_id SMALLINT NOT NULL,
20 name TEXT NOT NULL,
21 PRIMARY KEY (id),
22 FOREIGN KEY (game_id) REFERENCES games(id)
23);
24
25CREATE TABLE categories (
26 id SMALLSERIAL,
27 name TEXT NOT NULL,
28 PRIMARY KEY (id)
29);
30
21CREATE TABLE maps ( 31CREATE TABLE maps (
22 id SMALLSERIAL, 32 id SMALLSERIAL,
23 map_name TEXT NOT NULL, 33 game_id SMALLINT NOT NULL,
24 wr_score SMALLINT NOT NULL, 34 chapter_id SMALLINT NOT NULL,
25 wr_time INTEGER NOT NULL, 35 name TEXT NOT NULL,
26 is_coop BOOLEAN NOT NULL, 36 description TEXT NOT NULL,
37 showcase TEXT NOT NULL,
27 is_disabled BOOLEAN NOT NULL DEFAULT false, 38 is_disabled BOOLEAN NOT NULL DEFAULT false,
28 PRIMARY KEY (id) 39 PRIMARY KEY (id),
40 FOREIGN KEY (game_id) REFERENCES games(id),
41 FOREIGN KEY (chapter_id) REFERENCES chapters(id)
42);
43
44CREATE TABLE map_history (
45 id SMALLSERIAL,
46 map_id SMALLINT NOT NULL,
47 user_name TEXT NOT NULL,
48 score_count SMALLINT NOT NULL,
49 record_date TIMESTAMP NOT NULL,
50 PRIMARY KEY (id),
51 FOREIGN KEY (map_id) REFERENCES maps(id)
52);
53
54CREATE TABLE map_ratings (
55 id SERIAL,
56 map_id SMALLINT NOT NULL,
57 user_id TEXT NOT NULL,
58 rating SMALLINT NOT NULL,
59 PRIMARY KEY (id),
60 FOREIGN KEY (map_id) REFERENCES maps(id),
61 FOREIGN KEY (user_id) REFERENCES users(steam_id)
62);
63
64CREATE TABLE map_routers (
65 id SMALLSERIAL,
66 map_id SMALLINT NOT NULL,
67 user_id TEXT,
68 user_name TEXT NOT NULL,
69 score_count SMALLINT NOT NULL,
70 PRIMARY KEY (id),
71 FOREIGN KEY (map_id) REFERENCES maps(id),
72 FOREIGN KEY (user_id) REFERENCES users(steam_id)
29); 73);
30 74
31CREATE TABLE demos ( 75CREATE TABLE demos (
@@ -78,36 +122,3 @@ CREATE TABLE countries (
78 country_name TEXT NOT NULL, 122 country_name TEXT NOT NULL,
79 PRIMARY KEY (country_code) 123 PRIMARY KEY (country_code)
80); 124);
81
82-- CREATE TABLE community_maps (
83-- id SMALLSERIAL,
84-- map_id SMALLINT NOT NULL,
85-- category TEXT NOT NULL,
86-- tutorial TEXT NOT NULL,
87-- video_link TEXT NOT NULL,
88-- PRIMARY KEY (id),
89-- FOREIGN KEY (map_id) REFERENCES maps(id)
90-- );
91
92-- TODO
93-- CREATE TABLE community_history (
94-- id SERIAL,
95-- map_id SMALLINT NOT NULL,
96-- user_id TEXT NOT NULL,
97-- score_count SMALLINT NOT NULL,
98-- created_at TIMESTAMP NOT NULL DEFAULT now(),
99-- PRIMARY KEY (id),
100-- FOREIGN KEY (user_id) REFERENCES users(steam_id),
101-- FOREIGN KEY (map_id) REFERENCES maps(id)
102-- );
103
104-- CREATE TABLE community_posts (
105-- id SERIAL,
106-- map_id SMALLINT NOT NULL,
107-- user_id TEXT NOT NULL,
108-- post TEXT NOT NULL,
109-- created_at TIMESTAMP NOT NULL DEFAULT now(),
110-- PRIMARY KEY (id),
111-- FOREIGN KEY (user_id) REFERENCES users(steam_id),
112-- FOREIGN KEY (map_id) REFERENCES maps(id)
113-- ); \ No newline at end of file
diff --git a/backend/database/maps.sql b/backend/database/maps.sql
index 127eb58..50689e2 100644
--- a/backend/database/maps.sql
+++ b/backend/database/maps.sql
@@ -1,129 +1,129 @@
1DELETE FROM maps; 1INSERT INTO maps(game_id, chapter_id, name, description, showcase, is_disabled) VALUES
2INSERT INTO maps (map_name, wr_score, wr_time, is_coop, is_disabled) VALUES 2-- Portal 2 Singleplayer
3-- Singleplayer
4-- 1 3-- 1
5('Container Ride',0,-1,false,true), 4(1,1,'Container Ride','','',true),
6('Portal Carousel',0,-1,false,true), 5(1,1,'Portal Carousel','','',true),
7('Portal Gun',0,-1,false,false), 6(1,1,'Portal Gun','','',false),
8('Smooth Jazz',0,-1,false,false), 7(1,1,'Smooth Jazz','','',false),
9('Cube Momentum',1,-1,false,false), 8(1,1,'Cube Momentum','','',false),
10('Future Starter',2,-1,false,false), 9(1,1,'Future Starter','','',false),
11('Secret Panel',0,-1,false,false), 10(1,1,'Secret Panel','','',false),
12('Wakeup',0,-1,false,true), 11(1,1,'Wakeup','','',true),
13('Incinerator',0,-1,false,false), 12(1,1,'Incinerator','','',false),
14-- 2 13-- 2
15('Laser Intro',0,-1,false,false), 14(1,2,'Laser Intro','','',false),
16('Laser Stairs',0,-1,false,false), 15(1,2,'Laser Stairs','','',false),
17('Dual Lasers',2,-1,false,false), 16(1,2,'Dual Lasers','','',false),
18('Laser Over Goo',0,-1,false,false), 17(1,2,'Laser Over Goo','','',false),
19('Catapult Intro',0,-1,false,true), 18(1,2,'Catapult Intro','','',true),
20('Trust Fling',2,-1,false,false), 19(1,2,'Trust Fling','','',false),
21('Pit Flings',0,-1,false,false), 20(1,2,'Pit Flings','','',false),
22('Fizzler Intro',2,-1,false,false), 21(1,2,'Fizzler Intro','','',false),
23-- 3 22-- 3
24('Ceiling Catapult',0,-1,false,false), 23(1,3,'Ceiling Catapult','','',false),
25('Ricochet',2,-1,false,false), 24(1,3,'Ricochet','','',false),
26('Bridge Intro',2,-1,false,false), 25(1,3,'Bridge Intro','','',false),
27('Bridge The Gap',0,-1,false,false), 26(1,3,'Bridge The Gap','','',false),
28('Turret Intro',0,-1,false,false), 27(1,3,'Turret Intro','','',false),
29('Laser Relays',0,-1,false,false), 28(1,3,'Laser Relays','','',false),
30('Turret Blocker',0,-1,false,false), 29(1,3,'Turret Blocker','','',false),
31('Laser vs Turret',0,-1,false,false), 30(1,3,'Laser vs Turret','','',false),
32('Pull The Rug',0,-1,false,false), 31(1,3,'Pull The Rug','','',false),
33-- 4 32-- 4
34('Column Blocker',0,-1,false,false), 33(1,4,'Column Blocker','','',false),
35('Laser Chaining',0,-1,false,false), 34(1,4,'Laser Chaining','','',false),
36('Triple Laser',0,-1,false,false), 35(1,4,'Triple Laser','','',false),
37('Jail Break',2,-1,false,false), 36(1,4,'Jail Break','','',false),
38('Escape',0,-1,false,false), 37(1,4,'Escape','','',false),
39-- 5 38-- 5
40('Turret Factory',5,-1,false,false), 39(1,5,'Turret Factory','','',false),
41('Turret Sabotage',4,-1,false,false), 40(1,5,'Turret Sabotage','','',false),
42('Neurotoxin Sabotage',0,-1,false,false), 41(1,5,'Neurotoxin Sabotage','','',false),
43('Core',2,-1,false,false), 42(1,5,'Core','','',false),
44-- 6 43-- 6
45('Underground',4,-1,false,false), 44(1,6,'Underground','','',false),
46('Cave Johnson',4,-1,false,false), 45(1,6,'Cave Johnson','','',false),
47('Repulsion Intro',0,-1,false,false), 46(1,6,'Repulsion Intro','','',false),
48('Bomb Flings',3,-1,false,false), 47(1,6,'Bomb Flings','','',false),
49('Crazy Box',0,-1,false,false), 48(1,6,'Crazy Box','','',false),
50('PotatOS',5,-1,false,false), 49(1,6,'PotatOS','','',false),
51-- 7 50-- 7
52('Propulsion Intro',2,-1,false,false), 51(1,7,'Propulsion Intro','','',false),
53('Propulsion Flings',0,-1,false,false), 52(1,7,'Propulsion Flings','','',false),
54('Conversion Intro',9,-1,false,false), 53(1,7,'Conversion Intro','','',false),
55('Three Gels',4,-1,false,false), 54(1,7,'Three Gels','','',false),
56-- 8 55-- 8
57('Test',2,-1,false,false), 56(1,8,'Test','','',false),
58('Funnel Intro',0,-1,false,false), 57(1,8,'Funnel Intro','','',false),
59('Ceiling Button',0,-1,false,false), 58(1,8,'Ceiling Button','','',false),
60('Wall Button',0,-1,false,false), 59(1,8,'Wall Button','','',false),
61('Polarity',0,-1,false,false), 60(1,8,'Polarity','','',false),
62('Funnel Catch',2,-1,false,false), 61(1,8,'Funnel Catch','','',false),
63('Stop The Box',0,-1,false,false), 62(1,8,'Stop The Box','','',false),
64('Laser Catapult',0,-1,false,false), 63(1,8,'Laser Catapult','','',false),
65('Laser Platform',3,-1,false,false), 64(1,8,'Laser Platform','','',false),
66('Propulsion Catch',0,-1,false,false), 65(1,8,'Propulsion Catch','','',false),
67('Repulsion Polarity',2,-1,false,false), 66(1,8,'Repulsion Polarity','','',false),
68-- 9 67-- 9
69('Finale 1',0,-1,false,false), 68(1,9,'Finale 1','','',false),
70('Finale 2',2,-1,false,false), 69(1,9,'Finale 2','','',false),
71('Finale 3',6,-1,false,false), 70(1,9,'Finale 3','','',false),
72('Finale 4',6,-1,false,false), 71(1,9,'Finale 4','','',false),
73-- Coop 72-- Portal 2 Cooperative
73-- 0
74(2,10,'Calibration','','',false),
75(2,10,'Hub','','',true),
74-- 1 76-- 1
75('Calibration',4,-1,true,false), 77(2,11,'Doors','','',false),
76('Hub',0,-1,false,true), 78(2,11,'Buttons','','',false),
77('Doors',0,-1,true,false), 79(2,11,'Lasers','','',false),
78('Buttons',2,-1,true,false), 80(2,11,'Rat Maze','','',false),
79('Lasers',3,-1,true,false), 81(2,11,'Laser Crusher','','',false),
80('Rat Maze',2,-1,true,false), 82(2,11,'Behind The Scenes','','',false),
81('Laser Crusher',0,-1,true,false),
82('Behind The Scenes',0,-1,true,false),
83-- 2 83-- 2
84('Flings',4,-1,true,false), 84(2,12,'Flings','','',false),
85('Infinifling',0,-1,true,false), 85(2,12,'Infinifling','','',false),
86('Team Retrieval',0,-1,true,false), 86(2,12,'Team Retrieval','','',false),
87('Vertical Flings',2,-1,true,false), 87(2,12,'Vertical Flings','','',false),
88('Catapults',4,-1,true,false), 88(2,12,'Catapults','','',false),
89('Multifling',2,-1,true,false), 89(2,12,'Multifling','','',false),
90('Fling Crushers',0,-1,true,false), 90(2,12,'Fling Crushers','','',false),
91('Industrial Fan',0,-1,true,false), 91(2,12,'Industrial Fan','','',false),
92-- 3 92-- 3
93('Cooperative Bridges',3,-1,true,false), 93(2,13,'Cooperative Bridges','','',false),
94('Bridge Swap',2,-1,true,false), 94(2,13,'Bridge Swap','','',false),
95('Fling Block',2,-1,true,false), 95(2,13,'Fling Block','','',false),
96('Catapult Block',4,-1,true,false), 96(2,13,'Catapult Block','','',false),
97('Bridge Fling',4,-1,true,false), 97(2,13,'Bridge Fling','','',false),
98('Turret Walls',4,-1,true,false), 98(2,13,'Turret Walls','','',false),
99('Turret Assasin',0,-1,true,false), 99(2,13,'Turret Assasin','','',false),
100('Bridge Testing',0,-1,true,false), 100(2,13,'Bridge Testing','','',false),
101-- 4 101-- 4
102('Cooperative Funnels',0,-1,true,false), 102(2,14,'Cooperative Funnels','','',false),
103('Funnel Drill',0,-1,true,false), 103(2,14,'Funnel Drill','','',false),
104('Funnel Catch',0,-1,true,false), 104(2,14,'Funnel Catch','','',false),
105('Funnel Laser',0,-1,true,false), 105(2,14,'Funnel Laser','','',false),
106('Cooperative Polarity',0,-1,true,false), 106(2,14,'Cooperative Polarity','','',false),
107('Funnel Hop',0,-1,true,false), 107(2,14,'Funnel Hop','','',false),
108('Advanced Polarity',0,-1,true,false), 108(2,14,'Advanced Polarity','','',false),
109('Funnel Maze',0,-1,true,false), 109(2,14,'Funnel Maze','','',false),
110('Turret Warehouse',0,-1,true,false), 110(2,14,'Turret Warehouse','','',false),
111-- 5 111-- 5
112('Repulsion Jumps',0,-1,true,false), 112(2,15,'Repulsion Jumps','','',false),
113('Double Bounce',2,-1,true,false), 113(2,15,'Double Bounce','','',false),
114('Bridge Repulsion',2,-1,true,false), 114(2,15,'Bridge Repulsion','','',false),
115('Wall Repulsion',2,-1,true,false), 115(2,15,'Wall Repulsion','','',false),
116('Propulsion Crushers',0,-1,true,false), 116(2,15,'Propulsion Crushers','','',false),
117('Turret Ninja',0,-1,true,false), 117(2,15,'Turret Ninja','','',false),
118('Propulsion Retrieval',0,-1,true,false), 118(2,15,'Propulsion Retrieval','','',false),
119('Vault Entrance',0,-1,true,false), 119(2,15,'Vault Entrance','','',false),
120-- 6 120-- 6
121('Seperation',0,-1,true,false), 121(2,16,'Seperation','','',false),
122('Triple Axis',0,-1,true,false), 122(2,16,'Triple Axis','','',false),
123('Catapult Catch',0,-1,true,false), 123(2,16,'Catapult Catch','','',false),
124('Bridge Gels',2,-1,true,false), 124(2,16,'Bridge Gels','','',false),
125('Maintenance',0,-1,true,false), 125(2,16,'Maintenance','','',false),
126('Bridge Catch',0,-1,true,false), 126(2,16,'Bridge Catch','','',false),
127('Double Lift',0,-1,true,false), 127(2,16,'Double Lift','','',false),
128('Gel Maze',0,-1,true,false), 128(2,16,'Gel Maze','','',false),
129('Crazier Box',0,-1,true,false); \ No newline at end of file 129(2,16,'Crazier Box','','',false); \ No newline at end of file
diff --git a/backend/middleware/auth.go b/backend/middleware/auth.go
index b5ad762..cd00a30 100644
--- a/backend/middleware/auth.go
+++ b/backend/middleware/auth.go
@@ -37,7 +37,7 @@ func CheckAuth(c *gin.Context) {
37 // Get user from DB 37 // Get user from DB
38 var user models.User 38 var user models.User
39 database.DB.QueryRow(`SELECT * FROM users WHERE steam_id = $1;`, claims["sub"]).Scan( 39 database.DB.QueryRow(`SELECT * FROM users WHERE steam_id = $1;`, claims["sub"]).Scan(
40 &user.SteamID, &user.Username, &user.AvatarLink, 40 &user.SteamID, &user.UserName, &user.AvatarLink,
41 &user.CountryCode, &user.CreatedAt, &user.UpdatedAt) 41 &user.CountryCode, &user.CreatedAt, &user.UpdatedAt)
42 if user.SteamID == "" { 42 if user.SteamID == "" {
43 c.Next() 43 c.Next()
diff --git a/backend/models/models.go b/backend/models/models.go
index 2d14a06..be6265f 100644
--- a/backend/models/models.go
+++ b/backend/models/models.go
@@ -16,7 +16,7 @@ type LoginResponse struct {
16 16
17type User struct { 17type User struct {
18 SteamID string `json:"steam_id"` 18 SteamID string `json:"steam_id"`
19 Username string `json:"username"` 19 UserName string `json:"user_name"`
20 AvatarLink string `json:"avatar_link"` 20 AvatarLink string `json:"avatar_link"`
21 CountryCode string `json:"country_code"` 21 CountryCode string `json:"country_code"`
22 CreatedAt time.Time `json:"created_at"` 22 CreatedAt time.Time `json:"created_at"`
@@ -24,18 +24,45 @@ 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 History []MapHistory `json:"history"`
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"`
52}
53
54type MapHistory struct {
55 RunnerName string `json:"runner_name"`
56 ScoreCount int `json:"score_count"`
57 Date time.Time `json:"date"`
33} 58}
34 59
35type RecordSP struct { 60type RecordSP struct {
36 RecordID int `json:"record_id"` 61 RecordID int `json:"record_id"`
37 Placement int `json:"placement"` 62 Placement int `json:"placement"`
38 UserID string `json:"user_id"` 63 UserID string `json:"user_id"`
64 UserName string `json:"user_name"`
65 UserAvatar string `json:"user_avatar"`
39 ScoreCount int `json:"score_count"` 66 ScoreCount int `json:"score_count"`
40 ScoreTime int `json:"score_time"` 67 ScoreTime int `json:"score_time"`
41 DemoID string `json:"demo_id"` 68 DemoID string `json:"demo_id"`
@@ -46,7 +73,11 @@ type RecordMP struct {
46 RecordID int `json:"record_id"` 73 RecordID int `json:"record_id"`
47 Placement int `json:"placement"` 74 Placement int `json:"placement"`
48 HostID string `json:"host_id"` 75 HostID string `json:"host_id"`
76 HostName string `json:"host_name"`
77 HostAvatar string `json:"host_avatar"`
49 PartnerID string `json:"partner_id"` 78 PartnerID string `json:"partner_id"`
79 PartnerName string `json:"partner_name"`
80 PartnerAvatar string `json:"partner_avatar"`
50 ScoreCount int `json:"score_count"` 81 ScoreCount int `json:"score_count"`
51 ScoreTime int `json:"score_time"` 82 ScoreTime int `json:"score_time"`
52 HostDemoID string `json:"host_demo_id"` 83 HostDemoID string `json:"host_demo_id"`
@@ -63,7 +94,7 @@ type RecordRequest struct {
63 94
64type UserRanking struct { 95type UserRanking struct {
65 UserID string `json:"user_id"` 96 UserID string `json:"user_id"`
66 Username string `json:"username"` 97 UserName string `json:"user_name"`
67 TotalScore int `json:"total_score"` 98 TotalScore int `json:"total_score"`
68} 99}
69 100
@@ -75,7 +106,7 @@ type RankingsResponse struct {
75type ProfileResponse struct { 106type ProfileResponse struct {
76 Profile bool `json:"profile"` 107 Profile bool `json:"profile"`
77 SteamID string `json:"steam_id"` 108 SteamID string `json:"steam_id"`
78 Username string `json:"username"` 109 UserName string `json:"user_name"`
79 AvatarLink string `json:"avatar_link"` 110 AvatarLink string `json:"avatar_link"`
80 CountryCode string `json:"country_code"` 111 CountryCode string `json:"country_code"`
81 ScoresSP []ScoreResponse `json:"scores_sp"` 112 ScoresSP []ScoreResponse `json:"scores_sp"`
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-10 11:51:35 +0000