Update stat views.

4 files changed, 127 insertions, 42 deletions

diff --git a/server.js b/server.js
index ee63b94..d39a14e 100644
--- a/server.js
+++ b/server.js
@@ -210,6 +210,15 @@ const SQL_VERIFY_TOKEN = SQL("SELECT EXISTS ( SELECT 1 FROM tokens WHERE user_id
 
 const SQL_COUNT_INBOX = SQL("SELECT COUNT(*) FROM messages WHERE to_id=? AND read=0 AND deleted_from_inbox=0").pluck();
 
+const SQL_USER_STATS = SQL(`
+	SELECT title_name, scenario, SUM(role=result) AS won, count(*) AS total
+	FROM players
+	NATURAL JOIN games
+	NATURAL JOIN titles
+	WHERE user_id=? AND status=2 AND game_id IN (SELECT game_id FROM opposed_games)
+	GROUP BY title_name, scenario
+	`);
+
 function is_blacklisted(mail) {
 	if (SQL_BLACKLIST_MAIL.get(mail) === 1)
 		return true;
@@ -512,6 +521,17 @@ app.post('/change_about', must_be_logged_in, function (req, res) {
 	return res.redirect('/profile');
 });
 
+app.get('/user/:who_name/stats', function (req, res) {
+	LOG(req, "GET /user/" + req.params.who_name + "/stats");
+	let who = SQL_SELECT_USER_BY_NAME.get(req.params.who_name);
+	if (who) {
+		let stats = SQL_USER_STATS.all(who.user_id);
+		res.render('user_stats.pug', { user: req.user, who: who, stats: stats });
+	} else {
+		return res.status(404).send("Invalid user name.");
+	}
+});
+
 app.get('/user/:who_name', function (req, res) {
 	LOG(req, "GET /user/" + req.params.who_name);
 	let who = SQL_SELECT_USER_PROFILE.get(req.params.who_name);
@@ -1594,7 +1614,7 @@ function on_chat(socket, message) {
 	try {
 		let chat = SQL_INSERT_GAME_CHAT.get(socket.game_id, socket.user_id, message);
 		chat[2] = socket.user_name;
-		SLOG(socket, "CHAT", JSON.stringify(chat));
+		SLOG(socket, "CHAT");
 		for (let other of clients[socket.game_id])
 			if (other.role !== "Observer")
 				other.emit('chat', chat);
@@ -1748,16 +1768,13 @@ io.on('connection', (socket) => {
  * HIDDEN EXTRAS
  */
 
-const QUERY_STATS = db.prepare("SELECT * FROM game_stat_view");
+const SQL_GAME_STATS = db.prepare("SELECT * FROM game_stat_view");
 
 app.get('/stats', function (req, res) {
 	LOG(req, "GET /stats");
-	let stats = QUERY_STATS.all();
+	let stats = SQL_GAME_STATS.all();
 	res.render('stats.pug', {
 		user: req.user,
 		stats: stats,
-		title_role_map: ROLES,
-		title_name_map: TITLES,
-		title_rule_map: RULES,
 	});
 });
diff --git a/tools/sql/schema.txt b/tools/sql/schema.txt
index a7cb6bb..13062ee 100644
--- a/tools/sql/schema.txt
+++ b/tools/sql/schema.txt
@@ -121,8 +121,8 @@ DROP VIEW IF EXISTS thread_view;
 CREATE VIEW thread_view AS
 	SELECT *
 		, author.name AS author_name
-		, ( SELECT COUNT(*) FROM posts WHERE posts.thread_id = threads.thread_id ) - 1 AS replies
-		, ( SELECT MAX(posts.mtime) FROM posts WHERE posts.thread_id = threads.thread_id ) AS mtime
+		, ( SELECT count(*) FROM posts WHERE posts.thread_id = threads.thread_id ) - 1 AS replies
+		, ( SELECT max(posts.mtime) FROM posts WHERE posts.thread_id = threads.thread_id ) AS mtime
 	FROM threads
 	JOIN users AS author ON threads.author_id = author.user_id
 	;
@@ -227,13 +227,26 @@ CREATE VIEW game_full_view AS
 			NATURAL JOIN users
 			WHERE players.game_id=game_view.game_id
 		) AS player_names,
-		( SELECT COUNT(DISTINCT user_id) = 1
+		( SELECT count(DISTINCT user_id) = 1
 			FROM players
 			WHERE players.game_id=game_view.game_id
 		) AS is_solo
 	FROM game_view
 	;
 
+DROP VIEW IF EXISTS opposed_games;
+CREATE VIEW opposed_games AS
+	SELECT *
+	FROM GAMES
+	WHERE
+		STATUS > 0 AND
+		(
+			SELECT count(DISTINCT user_id) > 1
+			FROM players
+			where players.game_id=games.game_id
+		)
+	;
+
 DROP VIEW IF EXISTS your_turn_reminder;
 CREATE VIEW your_turn_reminder AS
 	SELECT
@@ -287,6 +300,7 @@ BEGIN
 		AND new.role <> 'Random 4'
 		AND new.role <> 'Random 5'
 		AND new.role <> 'Random 6'
+		AND new.role <> 'Random 7'
 	;
 END;
 
@@ -301,11 +315,39 @@ BEGIN
 	DELETE FROM players WHERE game_id = old.game_id;
 END;
 
+-- Game Stats
+
+DROP VIEW IF EXISTS role_index_view;
+CREATE VIEW role_index_view(t,i,r) AS
+	SELECT title_id, row_number() OVER ( PARTITION BY title_id ), role
+	FROM roles;
+
 DROP VIEW IF EXISTS game_stat_view;
 CREATE VIEW game_stat_view AS
-	SELECT title_id, scenario, result, count(*) AS count
-	FROM game_full_view
+	SELECT title_name, NULL AS scenario
+		, NULL as total
+		, (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=1) AS r1
+		, (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=2) AS r2
+		, (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=3) AS r3
+		, (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=4) AS r4
+		, (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=5) AS r5
+		, (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=6) AS r6
+		, (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=7) AS r7
+	FROM titles
+	WHERE hidden=0
+	UNION
+	SELECT title_name, scenario
+		, count(*) as total
+		, sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=1) AS r1
+		, sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=2) AS r2
+		, sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=3) AS r3
+		, sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=4) AS r4
+		, sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=5) AS r5
+		, sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=6) AS r6
+		, sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=7) AS r7
+	FROM games
 	NATURAL JOIN titles
-	WHERE hidden=0 AND is_solo=0 AND status=2
-	GROUP BY title_name, scenario, result
+	WHERE hidden=0 AND status=2 AND game_id IN (SELECT game_id FROM opposed_games)
+	GROUP BY title_id, scenario
+	ORDER BY title_name, total DESC NULLS FIRST
 	;
diff --git a/views/stats.pug b/views/stats.pug
index 7634048..b6c8673 100644
--- a/views/stats.pug
+++ b/views/stats.pug
@@ -6,40 +6,28 @@ html
 		title Game Statistics
 		style.
 			table { table-layout: fixed; min-width: auto; }
+			td:not(:nth-child(1)) { text-align: right; }
 			td:nth-child(1) { width: 240px; }
 			td { width: 100px; }
-			tr.blank { height: 2rem; border: none; }
+			tr+tr.blank { height: 2rem; border: none; }
 	body
 		include header
 		article
 			h1 Game Statistics
 			table
-				-
-					function total(t,s) {
-						return stats
-							.filter(entry => entry.title_id === t && entry.scenario === s)
-							.reduce((acc, entry) => acc + entry.count, 0);
-					}
-					function result(t,s,r) {
-						let info = stats.find(entry => entry.title_id === t && entry.scenario === s && entry.result === r);
-						return info ? info.count : 0;
-					}
-				each title_name, title_id in title_name_map
-					unless title_name_map[title_id].hidden
-						- let scenarios = title_rule_map[title_id].scenarios;
-						- let roles = title_role_map[title_id].concat(["Draw"]);
-						tr
-							th= title_name_map[title_id].title_name
-							each role in roles
-								th= role
-						each scenario in scenarios
-							- let t = total(title_id, scenario);
-							tr
-								td #{scenario} (#{t})
-								each role in roles
-									if t > 0
-										-let r = result(title_id, scenario, role);
-										td= Math.round(r * 100 / t) + "%"
-									else
-										td -
+				- function p(t,r) { return r > 0 ? Math.round(r*100/t) + "%" : "" }
+				- function drawn(x) { return x.total-(x.r1+x.r2+x.r3+x.r4+x.r5+x.r6+x.r7) }
+				each row in stats
+					unless row.scenario
 						tr.blank
+						tr
+							th= row.title_name
+							th= row.r1
+							th= row.r2
+							th Draw
+					else
+						tr
+							td #{row.scenario} (#{row.total})
+							td= p(row.total, row.r1)
+							td= p(row.total, row.r2)
+							td= p(row.total, drawn(row))
diff --git a/views/user_stats.pug b/views/user_stats.pug
new file mode 100644
index 0000000..70211ef
--- /dev/null
+++ b/views/user_stats.pug
@@ -0,0 +1,38 @@
+//- vim:ts=4:sw=4:
+- let all_won = 0
+- let all_total = 0
+doctype html
+html
+	head
+		include head
+		title Stats for #{who.name}
+		style td:nth-child(3),td:nth-child(4){text-align:right}
+	body
+		include header
+		article
+			h1 Stats for #{who.name}
+
+			table(style="min-width:auto")
+				tr
+					th Title
+					th Scenario
+					th Won
+					th Total
+				each row in stats
+					tr
+						- all_won += row.won
+						- all_total += row.total
+						td= row.title_name
+						td= row.scenario
+						td= row.won
+						td= row.total
+				tr
+					th
+					th
+					th
+					th
+				tr
+					td
+					td
+					td= Math.round(all_won * 100 / all_total) + "%"
+					td= all_total