Show play stats on user pages.

Timeout and move time.
Most played games.

1 files changed, 51 insertions, 7 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;