Show play stats on user pages.

Timeout and move time.
Most played games.

3 files changed, 91 insertions, 11 deletions

diff --git a/schema.sql b/schema.sql
index 33cd32c..119ac7e 100644
--- a/schema.sql
+++ b/schema.sql
@@ -119,13 +119,57 @@ create view user_login_view as
 
 drop view if exists user_profile_view;
 create view user_profile_view as
-	select
-		user_id, name, mail, notify, ctime, atime, about, is_banned
-	from
-		users
-		left join user_first_seen using(user_id)
-		left join user_last_seen using(user_id)
-		left join user_about using(user_id)
+	with
+		timeout as (
+			select
+				user_id,
+				count(1) as timeout_total,
+				max(time) as timeout_last
+			from
+				user_timeout
+			group by
+				user_id
+		),
+		move_time as (
+			select
+				user_id,
+				avg(time) as move_time_avg
+			from
+				user_move_time
+			group by
+				user_id
+		),
+		profile as (
+			select
+				user_id, name, mail, notify, ctime, atime, about, is_banned,
+				coalesce(timeout_total, 0) as timeout_total,
+				coalesce(timeout_last, 0) as timeout_last,
+				move_time_avg
+			from
+				users
+				left join user_first_seen using(user_id)
+				left join user_last_seen using(user_id)
+				left join user_about using(user_id)
+				left join timeout using(user_id)
+				left join move_time using(user_id)
+		)
+		select
+			profile.*,
+			(
+				select
+					count(1)
+				from
+					players
+					join games using(game_id)
+				where
+					players.user_id = profile.user_id
+					and games.is_opposed
+					and games.status > 1
+					and games.result != 'None'
+					and games.mtime > timeout_last
+			) as games_since_timeout
+		from
+			profile
 	;
 
 drop view if exists user_dynamic_view;
diff --git a/server.js b/server.js
index 98f4164..3faa6b0 100644
--- a/server.js
+++ b/server.js
@@ -827,13 +827,22 @@ app.get("/user/:who_name", function (req, res) {
 	let who = SQL_SELECT_USER_PROFILE.get(req.params.who_name)
 	if (who) {
 		let games = QUERY_LIST_PUBLIC_GAMES_OF_USER.all({ user_id: who.user_id })
+		let ratings = SQL_USER_RATINGS.all(who.user_id)
 		annotate_games(games, 0, null)
 		let active_pools = TM_POOL_LIST_USER_ACTIVE.all(who.user_id)
 		let finished_pools = TM_POOL_LIST_USER_RECENT_FINISHED.all(who.user_id)
 		let relation = 0
 		if (req.user)
 			relation = SQL_SELECT_RELATION.get(req.user.user_id, who.user_id) | 0
-		res.render("user.pug", { user: req.user, who, relation, games, active_pools, finished_pools })
+		res.render("user.pug", {
+			user: req.user,
+			who,
+			relation,
+			games,
+			active_pools,
+			finished_pools,
+			ratings,
+		})
 	} else {
 		return res.status(404).send("User not found.")
 	}
@@ -4189,12 +4198,12 @@ const SQL_USER_STATS = SQL(`
 	`)
 
 const SQL_USER_RATINGS = SQL(`
-	select title_name, rating, count, date(last) as last
+	select title_id, title_name, rating, count, date(last) as last
 	from ratings
 	join titles using(title_id)
 	where user_id = ?
-	and count >= 5
-	order by rating desc
+	and count >= 3
+	order by count desc
 	`)
 
 const SQL_GAME_RATINGS = SQL(`
diff --git a/views/user.pug b/views/user.pug
index fc55b42..4aecb0b 100644
--- a/views/user.pug
+++ b/views/user.pug
@@ -42,6 +42,33 @@ html
 						br
 						a(href="/contacts/add-enemy/"+who.name) Add to blacklist
 
+			h3 Response time
+			div Average response time: #{(who.move_time_avg * 24).toFixed(2)} hours
+
+			h3 Timeouts
+			div Total number of timeouts: #{who.timeout_total}
+			div Games completed since last timeout: #{who.games_since_timeout}
+
+			if ratings.length > 0
+				h3 Most played games
+				table
+					thead
+						tr
+							th Title
+							th Count
+							th Last played
+							if user && user.user_id === 1
+								th Elo
+					tbody
+						each row in ratings
+							tr
+								td
+									a.black(href="/" + row.title_id)= row.title_name
+								td.r= row.count
+								td.r= row.last
+								if user && user.user_id === 1
+									td.r= row.rating
+
 			+tourlist(null, active_pools, finished_pools)
 
 			if open_games.length > 0