From 9f7b5a836c8aeb83b66e0b41b8533443b5e99380 Mon Sep 17 00:00:00 2001
From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>
Date: Sun, 20 Aug 2023 12:36:34 +0300
Subject: fix: change map history from timestamp to date

Former-commit-id: 8a46950e919891f380dda5502e40434383c91245
---
 backend/database/init.sql | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'backend/database/init.sql')

diff --git a/backend/database/init.sql b/backend/database/init.sql
index 50e7c15..11d4944 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -59,7 +59,7 @@ CREATE TABLE map_history (
   category_id SMALLINT NOT NULL,
   user_name TEXT NOT NULL,
   score_count SMALLINT NOT NULL,
-  record_date TIMESTAMP NOT NULL,
+  record_date DATE NOT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (category_id) REFERENCES categories(id),
   FOREIGN KEY (map_id) REFERENCES maps(id),
-- 
cgit v1.2.3


From ca973edc28b5fe543c583217896590f4a2e98897 Mon Sep 17 00:00:00 2001
From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>
Date: Thu, 24 Aug 2023 22:34:05 +0300
Subject: fix: mod flag for easy check (#49)

Former-commit-id: 06ee23ee9659834252d3cb5c3c255797e9f93b62
---
 .gitignore                            |  3 +-
 backend/controllers/modController.go  | 44 +++++-----------
 backend/controllers/userController.go | 95 +++++++++++++++++------------------
 backend/database/init.sql             | 15 +++++-
 backend/middleware/auth.go            | 13 +++--
 backend/models/models.go              | 14 +++++-
 6 files changed, 95 insertions(+), 89 deletions(-)

(limited to 'backend/database/init.sql')

diff --git a/.gitignore b/.gitignore
index 1434a43..10d4fda 100644
--- a/.gitignore
+++ b/.gitignore
@@ -2,4 +2,5 @@
 .vscode
 *.sh
 *.txt
-*.dem
\ No newline at end of file
+*.dem
+*.json
\ No newline at end of file
diff --git a/backend/controllers/modController.go b/backend/controllers/modController.go
index 7ce5cb4..7acdb5d 100644
--- a/backend/controllers/modController.go
+++ b/backend/controllers/modController.go
@@ -49,18 +49,13 @@ type EditMapImageRequest struct {
 //	@Router			/maps/{id}/summary [post]
 func CreateMapSummary(c *gin.Context) {
 	// Check if user exists
-	user, exists := c.Get("user")
+	_, exists := c.Get("user")
 	if !exists {
 		c.JSON(http.StatusUnauthorized, models.ErrorResponse("User not logged in."))
 		return
 	}
-	var moderator bool
-	for _, title := range user.(models.User).Titles {
-		if title == "Moderator" {
-			moderator = true
-		}
-	}
-	if !moderator {
+	mod, exists := c.Get("mod")
+	if !exists || !mod.(bool) {
 		c.JSON(http.StatusUnauthorized, models.ErrorResponse("Insufficient permissions."))
 		return
 	}
@@ -135,18 +130,13 @@ func CreateMapSummary(c *gin.Context) {
 //	@Router			/maps/{id}/summary [put]
 func EditMapSummary(c *gin.Context) {
 	// Check if user exists
-	user, exists := c.Get("user")
+	_, exists := c.Get("user")
 	if !exists {
 		c.JSON(http.StatusUnauthorized, models.ErrorResponse("User not logged in."))
 		return
 	}
-	var moderator bool
-	for _, title := range user.(models.User).Titles {
-		if title == "Moderator" {
-			moderator = true
-		}
-	}
-	if !moderator {
+	mod, exists := c.Get("mod")
+	if !exists || !mod.(bool) {
 		c.JSON(http.StatusUnauthorized, models.ErrorResponse("Insufficient permissions."))
 		return
 	}
@@ -221,18 +211,13 @@ func EditMapSummary(c *gin.Context) {
 //	@Router			/maps/{id}/summary [delete]
 func DeleteMapSummary(c *gin.Context) {
 	// Check if user exists
-	user, exists := c.Get("user")
+	_, exists := c.Get("user")
 	if !exists {
 		c.JSON(http.StatusUnauthorized, models.ErrorResponse("User not logged in."))
 		return
 	}
-	var moderator bool
-	for _, title := range user.(models.User).Titles {
-		if title == "Moderator" {
-			moderator = true
-		}
-	}
-	if !moderator {
+	mod, exists := c.Get("mod")
+	if !exists || !mod.(bool) {
 		c.JSON(http.StatusUnauthorized, models.ErrorResponse("Insufficient permissions."))
 		return
 	}
@@ -311,18 +296,13 @@ func DeleteMapSummary(c *gin.Context) {
 //	@Router			/maps/{id}/image [put]
 func EditMapImage(c *gin.Context) {
 	// Check if user exists
-	user, exists := c.Get("user")
+	_, exists := c.Get("user")
 	if !exists {
 		c.JSON(http.StatusUnauthorized, models.ErrorResponse("User not logged in."))
 		return
 	}
-	var moderator bool
-	for _, title := range user.(models.User).Titles {
-		if title == "Moderator" {
-			moderator = true
-		}
-	}
-	if !moderator {
+	mod, exists := c.Get("mod")
+	if !exists || !mod.(bool) {
 		c.JSON(http.StatusUnauthorized, models.ErrorResponse("Insufficient permissions."))
 		return
 	}
diff --git a/backend/controllers/userController.go b/backend/controllers/userController.go
index 0dae155..64f144a 100644
--- a/backend/controllers/userController.go
+++ b/backend/controllers/userController.go
@@ -17,8 +17,39 @@ type ProfileResponse struct {
 	UserName    string          `json:"user_name"`
 	AvatarLink  string          `json:"avatar_link"`
 	CountryCode string          `json:"country_code"`
-	ScoresSP    []ScoreResponse `json:"scores_sp"`
-	ScoresMP    []ScoreResponse `json:"scores_mp"`
+	Titles      []models.Title  `json:"titles"`
+	Links       models.Links    `json:"links"`
+	Rankings    ProfileRankings `json:"rankings"`
+	Records     ProfileRecords  `json:"records"`
+}
+
+type ProfileRankings struct {
+	Overall      ProfileRankingsDetails `json:"overall"`
+	Singleplayer ProfileRankingsDetails `json:"singleplayer"`
+	Cooperative  ProfileRankingsDetails `json:"cooperative"`
+}
+
+type ProfileRankingsDetails struct {
+	Rank            int `json:"rank"`
+	CompletionCount int `json:"completion_count"`
+	CompletionTotal int `json:"completion_total"`
+}
+
+type ProfileRecords struct {
+	P2Singleplayer ProfileRecordsDetails `json:"portal2_singleplayer"`
+	P2Cooperative  ProfileRecordsDetails `json:"portal2_cooperative"`
+}
+
+type ProfileRecordsDetails struct {
+	MapID  int             `json:"map_id"`
+	Scores []ProfileScores `json:"scores"`
+}
+
+type ProfileScores struct {
+	DemoID     string    `json:"demo_id"`
+	ScoreCount int       `json:"score_count"`
+	ScoreTime  int       `json:"score_time"`
+	Date       time.Time `json:"date"`
 }
 
 type ScoreResponse struct {
@@ -44,58 +75,22 @@ func Profile(c *gin.Context) {
 		c.JSON(http.StatusUnauthorized, models.ErrorResponse("User not logged in."))
 		return
 	}
-	// Retrieve singleplayer records
-	var scoresSP []ScoreResponse
-	sql := `SELECT id, map_id, score_count, score_time, demo_id, record_date FROM records_sp WHERE user_id = $1 ORDER BY map_id`
+	// Get user titles
+	titles := []models.Title{}
+	sql := `SELECT t.title_name, t.title_color FROM titles t
+	INNER JOIN user_titles ut ON t.id=ut.title_id WHERE ut.user_id = $1`
 	rows, err := database.DB.Query(sql, user.(models.User).SteamID)
 	if err != nil {
 		c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
 		return
 	}
-	var recordsSP []models.RecordSP
-	for rows.Next() {
-		var mapID int
-		var record models.RecordSP
-		rows.Scan(&record.RecordID, &mapID, &record.ScoreCount, &record.ScoreTime, &record.DemoID, &record.RecordDate)
-		// More than one record in one map
-		if len(scoresSP) != 0 && mapID == scoresSP[len(scoresSP)-1].MapID {
-			scoresSP[len(scoresSP)-1].Records = append(scoresSP[len(scoresSP)-1].Records.([]models.RecordSP), record)
-			continue
-		}
-		// New map
-		recordsSP = []models.RecordSP{}
-		recordsSP = append(recordsSP, record)
-		scoresSP = append(scoresSP, ScoreResponse{
-			MapID:   mapID,
-			Records: recordsSP,
-		})
-	}
-	// Retrieve multiplayer records
-	var scoresMP []ScoreResponse
-	sql = `SELECT id, map_id, host_id, partner_id, score_count, score_time, host_demo_id, partner_demo_id, record_date FROM records_mp
-	WHERE host_id = $1 OR partner_id = $2 ORDER BY map_id`
-	rows, err = database.DB.Query(sql, user.(models.User).SteamID, user.(models.User).SteamID)
-	if err != nil {
-		c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
-		return
-	}
-	var recordsMP []models.RecordMP
 	for rows.Next() {
-		var mapID int
-		var record models.RecordMP
-		rows.Scan(&record.RecordID, &mapID, &record.HostID, &record.PartnerID, &record.ScoreCount, &record.ScoreTime, &record.HostDemoID, &record.PartnerDemoID, &record.RecordDate)
-		// More than one record in one map
-		if len(scoresMP) != 0 && mapID == scoresMP[len(scoresMP)-1].MapID {
-			scoresMP[len(scoresMP)-1].Records = append(scoresMP[len(scoresMP)-1].Records.([]models.RecordMP), record)
-			continue
+		var title models.Title
+		if err := rows.Scan(&title.Name, &title.Color); err != nil {
+			c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
+			return
 		}
-		// New map
-		recordsMP = []models.RecordMP{}
-		recordsMP = append(recordsMP, record)
-		scoresMP = append(scoresMP, ScoreResponse{
-			MapID:   mapID,
-			Records: recordsMP,
-		})
+		titles = append(titles, title)
 	}
 	c.JSON(http.StatusOK, models.Response{
 		Success: true,
@@ -106,8 +101,10 @@ func Profile(c *gin.Context) {
 			UserName:    user.(models.User).UserName,
 			AvatarLink:  user.(models.User).AvatarLink,
 			CountryCode: user.(models.User).CountryCode,
-			ScoresSP:    scoresSP,
-			ScoresMP:    scoresMP,
+			Titles:      user.(models.User).Titles,
+			Links:       models.Links{},
+			Rankings:    ProfileRankings{},
+			Records:     ProfileRecords{},
 		},
 	})
 	return
diff --git a/backend/database/init.sql b/backend/database/init.sql
index 11d4944..25de872 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -3,6 +3,10 @@ CREATE TABLE users (
   user_name TEXT NOT NULL,
   avatar_link TEXT NOT NULL,
   country_code CHAR(2) NOT NULL,
+  p2sr TEXT NOT NULL DEFAULT '-',
+  steam TEXT NOT NULL DEFAULT '-',
+  youtube TEXT NOT NULL DEFAULT '-',
+  twitch TEXT NOT NULL DEFAULT '-',
   created_at TIMESTAMP NOT NULL DEFAULT now(),
   updated_at TIMESTAMP NOT NULL DEFAULT now(),
   PRIMARY KEY (steam_id)
@@ -117,9 +121,16 @@ CREATE TABLE records_mp (
 );
 
 CREATE TABLE titles (
-  user_id TEXT,
+  id SERIAL,
   title_name TEXT NOT NULL,
-  PRIMARY KEY (user_id),
+  title_color CHAR(6) NOT NULL,
+  PRIMARY KEY (id)
+);
+
+CREATE TABLE user_titles (
+  title_id INT NOT NULL,
+  user_id TEXT NOT NULL,
+  FOREIGN KEY (title_id) REFERENCES titles(id),
   FOREIGN KEY (user_id) REFERENCES users(steam_id)
 );
 
diff --git a/backend/middleware/auth.go b/backend/middleware/auth.go
index 6a057da..e2c84fa 100644
--- a/backend/middleware/auth.go
+++ b/backend/middleware/auth.go
@@ -44,14 +44,19 @@ func CheckAuth(c *gin.Context) {
 			return
 		}
 		// Get user titles from DB
-		user.Titles = []string{}
-		rows, _ := database.DB.Query(`SELECT title_name FROM titles t INNER JOIN user_titles ut ON t.id=ut.title_id WHERE ut.user_id = $1`, user.SteamID)
+		var moderator bool
+		user.Titles = []models.Title{}
+		rows, _ := database.DB.Query(`SELECT t.title_name, t.title_color FROM titles t INNER JOIN user_titles ut ON t.id=ut.title_id WHERE ut.user_id = $1`, user.SteamID)
 		for rows.Next() {
-			var title string
-			rows.Scan(&title)
+			var title models.Title
+			rows.Scan(&title.Name, &title.Color)
+			if title.Name == "Moderator" {
+				moderator = true
+			}
 			user.Titles = append(user.Titles, title)
 		}
 		c.Set("user", user)
+		c.Set("mod", moderator)
 		c.Next()
 	} else {
 		c.Next()
diff --git a/backend/models/models.go b/backend/models/models.go
index e21ba6a..f124db5 100644
--- a/backend/models/models.go
+++ b/backend/models/models.go
@@ -25,7 +25,7 @@ type User struct {
 	CountryCode string    `json:"country_code"`
 	CreatedAt   time.Time `json:"created_at"`
 	UpdatedAt   time.Time `json:"updated_at"`
-	Titles      []string  `json:"titles"`
+	Titles      []Title   `json:"titles"`
 }
 
 type UserShort struct {
@@ -92,6 +92,18 @@ type Category struct {
 	Name string `json:"name"`
 }
 
+type Title struct {
+	Name  string `json:"name"`
+	Color string `json:"color"`
+}
+
+type Links struct {
+	P2SR    string `json:"p2sr"`
+	Steam   string `json:"stream"`
+	YouTube string `json:"youtube"`
+	Twitch  string `json:"twitch"`
+}
+
 type RecordSP struct {
 	RecordID   int       `json:"record_id"`
 	Placement  int       `json:"placement"`
-- 
cgit v1.2.3


From 69c5423f7954b641109166e03ad0ab174b3d55c6 Mon Sep 17 00:00:00 2001
From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>
Date: Sat, 2 Sep 2023 11:09:42 +0300
Subject: feat: testing logging system (#55)

Former-commit-id: d8c5fda30ab08b42218aead1febdf83200948763
---
 backend/api/routes.go     |   2 +
 backend/database/init.sql |  10 ++++
 backend/handlers/login.go |   4 ++
 backend/handlers/logs.go  | 114 ++++++++++++++++++++++++++++++++++++++++++++++
 4 files changed, 130 insertions(+)
 create mode 100644 backend/handlers/logs.go

(limited to 'backend/database/init.sql')

diff --git a/backend/api/routes.go b/backend/api/routes.go
index 4dd8660..ac622d4 100644
--- a/backend/api/routes.go
+++ b/backend/api/routes.go
@@ -36,5 +36,7 @@ func InitRoutes(router *gin.Engine) {
 		v1.GET("/games", handlers.FetchGames)
 		v1.GET("/games/:id", handlers.FetchChapters)
 		v1.GET("/chapters/:id", handlers.FetchChapterMaps)
+		v1.GET("/logs/score", handlers.ScoreLogs)
+		v1.GET("/logs/mod", CheckAuth, handlers.ModLogs)
 	}
 }
diff --git a/backend/database/init.sql b/backend/database/init.sql
index 25de872..abace5c 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -139,3 +139,13 @@ CREATE TABLE countries (
   country_name TEXT NOT NULL,
   PRIMARY KEY (country_code)
 );
+
+CREATE TABLE logs (
+  id SERIAL,
+  user_id TEXT NOT NULL,
+  type TEXT NOT NULL,
+  description TEXT NOT NULL,
+  date TIMESTAMP NOT NULL DEFAULT now(),
+  PRIMARY KEY (id),
+  FOREIGN KEY (user_id) REFERENCES users(steam_id)
+);
\ No newline at end of file
diff --git a/backend/handlers/login.go b/backend/handlers/login.go
index 4b151c2..80f697e 100644
--- a/backend/handlers/login.go
+++ b/backend/handlers/login.go
@@ -38,6 +38,7 @@ func Login(c *gin.Context) {
 	default:
 		steamID, err := openID.ValidateAndGetId()
 		if err != nil {
+			CreateLog(steamID, LogTypeLogin, LogDescriptionLoginFailValidate)
 			c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
 			return
 		}
@@ -48,6 +49,7 @@ func Login(c *gin.Context) {
 		if checkSteamID == 0 {
 			user, err := GetPlayerSummaries(steamID, os.Getenv("API_KEY"))
 			if err != nil {
+				CreateLog(steamID, LogTypeLogin, LogDescriptionLoginFailSummary)
 				c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
 				return
 			}
@@ -77,10 +79,12 @@ func Login(c *gin.Context) {
 		// Sign and get the complete encoded token as a string using the secret
 		tokenString, err := token.SignedString([]byte(os.Getenv("SECRET_KEY")))
 		if err != nil {
+			CreateLog(steamID, LogTypeLogin, LogDescriptionLoginFailToken)
 			c.JSON(http.StatusBadRequest, models.ErrorResponse("Failed to generate token."))
 			return
 		}
 		c.SetCookie("token", tokenString, 3600*24*30, "/", "", true, true)
+		CreateLog(steamID, LogTypeLogin, LogDescriptionLoginSuccess)
 		c.Redirect(http.StatusTemporaryRedirect, "/")
 		// c.JSON(http.StatusOK, models.Response{
 		// 	Success: true,
diff --git a/backend/handlers/logs.go b/backend/handlers/logs.go
new file mode 100644
index 0000000..2b5713d
--- /dev/null
+++ b/backend/handlers/logs.go
@@ -0,0 +1,114 @@
+package handlers
+
+import (
+	"fmt"
+	"net/http"
+
+	"github.com/gin-gonic/gin"
+	"github.com/pektezol/leastportalshub/backend/database"
+	"github.com/pektezol/leastportalshub/backend/models"
+)
+
+const (
+	LogTypeMod   string = "Mod"
+	LogTypeScore string = "Score"
+	LogTypeLogin string = "Login"
+
+	LogDescriptionLoginSuccess      string = "Success"
+	LogDescriptionLoginFailToken    string = "TokenFail"
+	LogDescriptionLoginFailValidate string = "ValidateFail"
+	LogDescriptionLoginFailSummary  string = "SummaryFail"
+)
+
+type Log struct {
+	User        models.UserShort `json:"user"`
+	Type        string           `json:"type"`
+	Description string           `json:"description"`
+}
+
+type LogsResponse struct {
+	Logs []LogsResponseDetails `json:"logs"`
+}
+
+type LogsResponseDetails struct {
+	User models.UserShort `json:"user"`
+	Log  string           `json:"detail"`
+}
+
+func ModLogs(c *gin.Context) {
+	mod, exists := c.Get("mod")
+	if !exists || !mod.(bool) {
+		c.JSON(http.StatusUnauthorized, models.ErrorResponse("Insufficient permissions."))
+		return
+	}
+	response := LogsResponse{Logs: []LogsResponseDetails{}}
+	sql := `SELECT u.user_name, l.user_id, l.type, l.description 
+	FROM logs l INNER JOIN users u ON l.user_id = u.steam_id WHERE type != 'Score'`
+	rows, err := database.DB.Query(sql)
+	if err != nil {
+		c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
+		return
+	}
+	for rows.Next() {
+		log := Log{}
+		err = rows.Scan(log.User.UserName, log.User.SteamID, log.Type, log.Description)
+		if err != nil {
+			c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
+			return
+		}
+		detail := fmt.Sprintf("%s.%s", log.Type, log.Description)
+		response.Logs = append(response.Logs, LogsResponseDetails{
+			User: models.UserShort{
+				SteamID:  log.User.SteamID,
+				UserName: log.User.UserName,
+			},
+			Log: detail,
+		})
+	}
+	c.JSON(http.StatusOK, models.Response{
+		Success: true,
+		Message: "Successfully retrieved logs.",
+		Data:    response,
+	})
+}
+
+func ScoreLogs(c *gin.Context) {
+	response := LogsResponse{Logs: []LogsResponseDetails{}}
+	sql := `SELECT u.user_name, l.user_id, l.type, l.description 
+	FROM logs l INNER JOIN users u ON l.user_id = u.steam_id WHERE type = 'Score'`
+	rows, err := database.DB.Query(sql)
+	if err != nil {
+		c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
+		return
+	}
+	for rows.Next() {
+		log := Log{}
+		err = rows.Scan(log.User.UserName, log.User.SteamID, log.Type, log.Description)
+		if err != nil {
+			c.JSON(http.StatusBadRequest, models.ErrorResponse(err.Error()))
+			return
+		}
+		detail := fmt.Sprintf("%s.%s", log.Type, log.Description)
+		response.Logs = append(response.Logs, LogsResponseDetails{
+			User: models.UserShort{
+				SteamID:  log.User.SteamID,
+				UserName: log.User.UserName,
+			},
+			Log: detail,
+		})
+	}
+	c.JSON(http.StatusOK, models.Response{
+		Success: true,
+		Message: "Successfully retrieved score logs.",
+		Data:    response,
+	})
+}
+
+func CreateLog(user_id string, log_type string, log_description string) (err error) {
+	sql := `INSERT INTO logs (user_id, "type", description) VALUES($1, $2, $3)`
+	_, err = database.DB.Exec(sql)
+	if err != nil {
+		return err
+	}
+	return nil
+}
-- 
cgit v1.2.3

