Set player_count in database for faster and easier logic.

Track ready to start and unjoined games in "active" header.

8 files changed, 274 insertions, 189 deletions

diff --git a/public/style.css b/public/style.css
index 19486a5..5d8940c 100644
--- a/public/style.css
+++ b/public/style.css
@@ -10,6 +10,7 @@ button, select, option {
 html, body { margin: 0 }
 h1 { margin: 16px 0 16px -1px; font-size: 24px; }
 h2 { margin: 16px 0 16px -1px; font-size: 20px; }
+h3 { margin: 16px 0 8px -1px; font-size: 16px; }
 a { color: blue; }
 .w { white-space: nowrap; }
 .r { text-align: right; }
@@ -91,6 +92,7 @@ table {
 	border-collapse: collapse;
 	border: 1px solid black;
 	box-shadow: 1px 2px 4px #0004;
+	margin: 1em 0;
 }
 thead, tfoot {
 	background-color: gainsboro;
diff --git a/schema.sql b/schema.sql
index feb99a9..eef0a26 100644
--- a/schema.sql
+++ b/schema.sql
@@ -8,6 +8,7 @@ create table if not exists titles (
 	title_id text primary key,
 	title_name text,
 	bgg integer,
+	is_symmetric boolean default 0,
 	is_hidden boolean default 0
 ) without rowid;
 
@@ -113,17 +114,34 @@ create view user_dynamic_view as
 				join game_state using(game_id)
 			where
 				status = 1
+				and user_count = player_count
 				and players.user_id = users.user_id
-				and active in ( players.role, 'Both', 'All' )
+				and active in ( players.role, 'Both' )
 		) + (
 			select
 				count(*)
 			from
 				players
 			where
-				players.user_id = users.user_id
-				and players.is_invite
-		) as active,
+				players.user_id = users.user_id and players.is_invite
+		) + (
+			select
+				count(*)
+			from
+				games
+			where
+				owner_id = users.user_id
+				and status = 0
+				and (
+					join_count = 0
+					or (
+						join_count = player_count
+						and not exists (
+							select 1 from players where players.game_id = games.game_id and players.is_invite
+						)
+					)
+				)
+		) as waiting,
 		is_banned
 	from
 		users
@@ -274,7 +292,10 @@ create table if not exists games (
 	title_id text,
 	scenario text,
 	options text,
-	owner_id integer,
+	player_count integer,
+	join_count integer default 0,
+	user_count integer default 0,
+	owner_id integer default 0,
 	ctime datetime default current_timestamp,
 	is_private boolean default 0,
 	is_random boolean default 0,
@@ -359,6 +380,7 @@ create view game_view as
 		titles.title_name,
 		owner.name as owner_name,
 		coalesce(game_state.mtime, xtime) as mtime,
+		user_count = join_count and join_count > 1 as is_opposed,
 		game_state.active
 	from
 		games
@@ -368,77 +390,21 @@ create view game_view as
 			on owner.user_id = games.owner_id
 	;
 
-drop view if exists game_full_view;
-create view game_full_view as
-	select
-		*,
-		(
-			select
-				group_concat(name, ', ')
-			from
-				players
-				natural join users
-			where
-				players.game_id = game_view.game_id
-		) as player_names,
-		(
-			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
 		game_id, role, user_id, name, mail, notify
 	from
-		game_full_view
+		game_view
 		join players using(game_id)
 		join users using(user_id)
 	where
 		status = 1
-		and active in ('All', 'Both', role)
-		and is_solo = 0
+		and active in (role, 'Both')
+		and user_count > 1
 		and julianday() > julianday(mtime, '+1 hour')
 	;
 
-drop view if exists your_turn;
-create view your_turn as
-	select
-		game_id, user_id, role
-	from
-		players
-		join games using(game_id)
-		join game_state using(game_id)
-	where
-		status = 1
-		and active in ('All', 'Both', role)
-	;
-
 drop view if exists invite_reminder;
 create view invite_reminder as
 	select
@@ -450,6 +416,32 @@ create view invite_reminder as
 		is_invite = 1
 	;
 
+-- Trigger to update player counts when players join and part games
+
+drop trigger if exists trigger_join_game;
+create trigger trigger_join_game after insert on players
+begin
+	update
+		games
+	set
+		join_count = ( select count(1) from players where players.game_id = new.game_id ),
+		user_count = ( select count(distinct user_id) from players where players.game_id = new.game_id )
+	where
+		games.game_id = new.game_id;
+end;
+
+drop trigger if exists trigger_part_game;
+create trigger trigger_part_game after delete on players
+begin
+	update
+		games
+	set
+		join_count = ( select count(1) from players where players.game_id = old.game_id ),
+		user_count = ( select count(distinct user_id) from players where players.game_id = old.game_id )
+	where
+		games.game_id = old.game_id;
+end;
+
 -- Trigger to remove game data when filing a game as archived
 
 drop trigger if exists trigger_archive_game;
diff --git a/server.js b/server.js
index 6b38b7a..80b259d 100644
--- a/server.js
+++ b/server.js
@@ -1072,20 +1072,18 @@ function get_game_roles(title_id, scenario, options) {
 	return roles
 }
 
-function is_game_full(title_id, scenario, options, players) {
-	return get_game_roles(title_id, scenario, options).length === players.length
-}
-
-function is_game_ready(title_id, scenario, options, players) {
+function is_game_ready(player_count, players) {
+	if (player_count !== players.length)
+		return false
 	for (let p of players)
 		if (p.is_invite)
 			return false
-	return is_game_full(title_id, scenario, options, players)
+	return true
 }
 
 load_rules()
 
-const SQL_INSERT_GAME = SQL("INSERT INTO games (owner_id,title_id,scenario,options,is_private,is_random,notice) VALUES (?,?,?,?,?,?,?)")
+const SQL_INSERT_GAME = SQL("INSERT INTO games (owner_id,title_id,scenario,options,player_count,is_private,is_random,notice) VALUES (?,?,?,?,?,?,?,?)")
 const SQL_DELETE_GAME = SQL("DELETE FROM games WHERE game_id=? AND owner_id=?")
 
 const SQL_SELECT_UNREAD_CHAT_GAMES = SQL("select game_id from unread_chats where user_id = ?").pluck()
@@ -1146,7 +1144,6 @@ const SQL_SELECT_REPLAY = SQL(`
 
 const SQL_SELECT_GAME = SQL("SELECT * FROM games WHERE game_id=?")
 const SQL_SELECT_GAME_VIEW = SQL("SELECT * FROM game_view WHERE game_id=?")
-const SQL_SELECT_GAME_FULL_VIEW = SQL("SELECT * FROM game_full_view WHERE game_id=?")
 const SQL_SELECT_GAME_TITLE = SQL("SELECT title_id FROM games WHERE game_id=?").pluck()
 
 const SQL_SELECT_PLAYERS_ID = SQL("SELECT DISTINCT user_id FROM players WHERE game_id=?").pluck()
@@ -1164,9 +1161,9 @@ const SQL_COUNT_OPEN_GAMES = SQL(`SELECT COUNT(*) FROM games WHERE owner_id=? AN
 const SQL_SELECT_REMATCH = SQL(`SELECT game_id FROM games WHERE status < ${STATUS_FINISHED} AND notice=?`).pluck()
 const SQL_INSERT_REMATCH = SQL(`
 	INSERT INTO games
-		(owner_id, title_id, scenario, options, is_private, is_random, notice)
+		(owner_id, title_id, scenario, options, player_count, is_private, is_random, notice)
 	SELECT
-		$user_id, title_id, scenario, options, is_private, 0, $magic
+		$user_id, title_id, scenario, options, player_count, is_private, 0, $magic
 	FROM games
 	WHERE game_id = $game_id AND NOT EXISTS (
 		SELECT * FROM games WHERE notice=$magic
@@ -1175,20 +1172,58 @@ const SQL_INSERT_REMATCH = SQL(`
 
 const SQL_INSERT_REMATCH_PLAYERS = SQL("insert into players (game_id, user_id, role, is_invite) select ?, user_id, role, user_id!=? from players where game_id=?")
 
-const QUERY_LIST_PUBLIC_GAMES = SQL(`
-	SELECT * FROM game_view
-	WHERE is_private=0 AND status=?
-	AND EXISTS ( SELECT 1 FROM players WHERE players.game_id = game_view.game_id AND players.user_id = game_view.owner_id )
-	ORDER BY mtime DESC, ctime DESC
-	LIMIT ?
+const QUERY_LIST_PUBLIC_GAMES_OPEN = SQL(`
+	select * from game_view where status=0 and not is_private and join_count > 0 and join_count < player_count
+	order by mtime desc, ctime desc
+	`)
+
+const QUERY_LIST_PUBLIC_GAMES_READY = SQL(`
+	select * from game_view where status=0 and not is_private and join_count = player_count
+	order by mtime desc, ctime desc
+	`)
+
+const QUERY_LIST_PUBLIC_GAMES_REPLACEMENT = SQL(`
+	select * from game_view where status=1 and not is_private and join_count < player_count
+	order by mtime desc, ctime desc
+	`)
+
+const QUERY_LIST_PUBLIC_GAMES_ACTIVE = SQL(`
+	select * from game_view where status=1 and not is_private and join_count = player_count
+	order by mtime desc, ctime desc
+	limit 12
 	`)
 
-const QUERY_LIST_GAMES_OF_TITLE = SQL(`
-	SELECT * FROM game_view
-	WHERE is_private=0 AND title_id=? AND status=?
-	AND EXISTS ( SELECT 1 FROM players WHERE players.game_id = game_view.game_id AND players.user_id = game_view.owner_id )
-	ORDER BY mtime DESC, ctime DESC
-	LIMIT ?
+const QUERY_LIST_PUBLIC_GAMES_FINISHED = SQL(`
+	select * from game_view where status=2 and not is_private
+	order by mtime desc, ctime desc
+	limit 12
+	`)
+
+const QUERY_LIST_GAMES_OF_TITLE_OPEN = SQL(`
+	select * from game_view where title_id=? and not is_private and status=0 and join_count > 0 and join_count < player_count
+	order by mtime desc, ctime desc
+	`)
+
+const QUERY_LIST_GAMES_OF_TITLE_READY = SQL(`
+	select * from game_view where title_id=? and not is_private and status=0 and join_count = player_count
+	order by mtime desc, ctime desc
+	`)
+
+const QUERY_LIST_GAMES_OF_TITLE_REPLACEMENT = SQL(`
+	select * from game_view where title_id=? and not is_private and status=1 and join_count < player_count
+	order by mtime desc, ctime desc
+	`)
+
+const QUERY_LIST_GAMES_OF_TITLE_ACTIVE = SQL(`
+	select * from game_view where title_id=? and not is_private and status=1 and join_count = player_count
+	order by mtime desc, ctime desc
+	limit 12
+	`)
+
+const QUERY_LIST_GAMES_OF_TITLE_FINISHED = SQL(`
+	select * from game_view where title_id=? and not is_private and status=2
+	order by mtime desc, ctime desc
+	limit 12
 	`)
 
 const QUERY_NEXT_GAME_OF_USER = SQL(`
@@ -1198,7 +1233,7 @@ const QUERY_NEXT_GAME_OF_USER = SQL(`
 	join players using(game_id)
 	where
 		status = ${STATUS_ACTIVE}
-		and active in ('All', 'Both', role)
+		and active in (role, 'Both')
 		and user_id = ?
 	order by mtime
 	limit 1
@@ -1233,10 +1268,6 @@ const QUERY_LIST_FINISHED_GAMES_OF_USER = SQL(`
 	order by status asc, mtime desc
 	`)
 
-function is_solo(players) {
-	return players.every(p => p.user_id === players[0].user_id)
-}
-
 function format_options(options) {
 	function to_english(k) {
 		if (k === true || k === 1) return 'yes'
@@ -1252,36 +1283,36 @@ function format_options(options) {
 	}).join(", ")
 }
 
-function annotate_game(game, user_id, unread) {
-	let players = SQL_SELECT_PLAYERS_JOIN.all(game.game_id)
-	let options = JSON.parse(game.options)
-	let roles = get_game_roles(game.title_id, game.scenario, options)
-
-	if (game.options === '{}')
-		game.human_options = "None"
-	else
-		game.human_options = format_options(options)
-
-	for (let i = 0; i < players.length; ++i)
-		players[i].index = roles.indexOf(players[i].role)
-	players.sort((a, b) => a.index - b.index)
+function annotate_game_players(game) {
+	game.players = SQL_SELECT_PLAYERS_JOIN.all(game.game_id)
+	if (game.player_count === game.join_count) {
+		game.is_ready = true
+		for (let p of game.players)
+			if (p.is_invite)
+				game.is_ready = false
+	} else {
+		game.is_ready = false
+	}
+}
 
-	game.is_full = is_game_full(game.title_id, game.scenario, options, players)
-	game.is_ready = is_game_ready(game.title_id, game.scenario, options, players)
+function annotate_game_info(game, user_id, unread) {
 	game.is_unread = set_has(unread, game.game_id)
+	if (game.options && game.options !== '{}')
+		game.human_options = format_options(JSON.parse(game.options))
+	else
+		game.human_options = "None"
 
 	let your_count = 0
 	let your_role = null
-	game.player_names = ""
-	for (let i = 0; i < players.length; ++i) {
-		let p = players[i]
 
+	game.player_names = ""
+	for (let p of game.players) {
 		let p_is_owner = false
 		if (game.status === STATUS_OPEN && (game.owner_id === p.user_id))
 			p_is_owner = true
 
 		let p_is_active = false
-		if (game.status === STATUS_ACTIVE && (game.active === p.role || game.active === "Both" || game.active === "All"))
+		if (game.status === STATUS_ACTIVE && (game.active === p.role || game.active === "Both"))
 			p_is_active = true
 
 		if (p.user_id === user_id) {
@@ -1301,9 +1332,10 @@ function annotate_game(game, user_id, unread) {
 		else
 			link = `<a href="/user/${p.name}">${p.name}</a>`
 
-		if (game.player_names.length > 0)
-			game.player_names += ", "
-		game.player_names += link
+		if (game.player_names)
+			game.player_names += ", " + link
+		else
+			game.player_names = link
 
 		if (game.active === p.role)
 			game.active = link
@@ -1321,9 +1353,12 @@ function annotate_game(game, user_id, unread) {
 	game.mtime = human_date(game.mtime)
 }
 
-function annotate_games(games, user_id, unread) {
-	for (let i = 0; i < games.length; ++i)
-		annotate_game(games[i], user_id, unread)
+function annotate_games(list, user_id, unread) {
+	for (let game of list) {
+		annotate_game_players(game)
+		annotate_game_info(game, user_id, unread)
+	}
+	return list
 }
 
 app.get('/profile', must_be_logged_in, function (req, res) {
@@ -1378,17 +1413,33 @@ app.get('/games/finished/:who_name', function (req, res) {
 })
 
 app.get('/games/public', function (req, res) {
-	let games0 = QUERY_LIST_PUBLIC_GAMES.all(STATUS_OPEN, 1000)
-	let games1 = QUERY_LIST_PUBLIC_GAMES.all(STATUS_ACTIVE, 48)
+	let user_id = 0
+	let unread = null
 	if (req.user) {
-		let unread = SQL_SELECT_UNREAD_CHAT_GAMES.all(req.user.user_id)
-		annotate_games(games0, req.user.user_id, unread)
-		annotate_games(games1, req.user.user_id, unread)
-	} else {
-		annotate_games(games0, 0, null)
-		annotate_games(games1, 0, null)
+		user_id = req.user.user_id
+		unread = SQL_SELECT_UNREAD_CHAT_GAMES.all(req.user.user_id)
 	}
-	res.render('games_public.pug', { user: req.user, games0, games1 })
+
+	let open_games = QUERY_LIST_PUBLIC_GAMES_OPEN.all()
+	let ready_games = QUERY_LIST_PUBLIC_GAMES_READY.all()
+	let replacement_games = QUERY_LIST_PUBLIC_GAMES_REPLACEMENT.all()
+	let active_games = QUERY_LIST_PUBLIC_GAMES_ACTIVE.all()
+	let finished_games = QUERY_LIST_PUBLIC_GAMES_FINISHED.all()
+
+	annotate_games(open_games, user_id, unread)
+	annotate_games(ready_games, user_id, unread)
+	annotate_games(replacement_games, user_id, unread)
+	annotate_games(active_games, user_id, unread)
+	annotate_games(finished_games, user_id, unread)
+
+	res.render('games_public.pug', {
+		user: req.user,
+		open_games,
+		ready_games,
+		replacement_games,
+		active_games,
+		finished_games
+	})
 })
 
 function get_title_page(req, res, title_id) {
@@ -1398,17 +1449,35 @@ function get_title_page(req, res, title_id) {
 	let unread = null
 	if (req.user)
 		unread = SQL_SELECT_UNREAD_CHAT_GAMES.all(req.user.user_id)
-	let games0 = QUERY_LIST_GAMES_OF_TITLE.all(title_id, STATUS_OPEN, 1000)
-	let games1 = QUERY_LIST_GAMES_OF_TITLE.all(title_id, STATUS_ACTIVE, 1000)
-	let games2 = QUERY_LIST_GAMES_OF_TITLE.all(title_id, STATUS_FINISHED, 24)
-	annotate_games(games0, req.user ? req.user.user_id : 0, unread)
-	annotate_games(games1, req.user ? req.user.user_id : 0, unread)
-	annotate_games(games2, req.user ? req.user.user_id : 0, unread)
+	let user_id = req.user ? req.user.user_id : 0
+
+	// Title page shows 
+
+	let a = Date.now()
+
+	let open_games = QUERY_LIST_GAMES_OF_TITLE_OPEN.all(title_id)
+	let ready_games = QUERY_LIST_GAMES_OF_TITLE_READY.all(title_id)
+	let replacement_games = QUERY_LIST_GAMES_OF_TITLE_REPLACEMENT.all(title_id)
+	let active_games = QUERY_LIST_GAMES_OF_TITLE_ACTIVE.all(title_id)
+	let finished_games = QUERY_LIST_GAMES_OF_TITLE_FINISHED.all(title_id)
+
+	annotate_games(open_games, user_id, unread)
+	annotate_games(ready_games, user_id, unread)
+	annotate_games(replacement_games, user_id, unread)
+	annotate_games(active_games, user_id, unread)
+	annotate_games(finished_games, user_id, unread)
+
+	console.log("LIST GAMES", Date.now() - a)
+
 	res.render('info.pug', {
 		user: req.user,
 		title: title,
 		about_html: HTML_ABOUT[title_id],
-		games0, games1, games2
+		open_games,
+		ready_games,
+		replacement_games,
+		active_games,
+		finished_games
 	})
 }
 
@@ -1452,14 +1521,16 @@ app.post("/create/:title_id", must_be_logged_in, function (req, res) {
 	let notice = req.body.notice
 
 	let count = SQL_COUNT_OPEN_GAMES.get(user_id)
-	if (count >= 5)
+	if (count >= 7)
 		return res.send("You have too many open games!")
 	if (!(title_id in RULES))
 		return res.send("Invalid title.")
 	if (!RULES[title_id].scenarios.includes(scenario))
 		return res.send("Invalid scenario.")
 
-	let info = SQL_INSERT_GAME.run(user_id, title_id, scenario, options, priv ? 1 : 0, rand ? 1 : 0, notice)
+	let player_count = get_game_roles(title_id, scenario, JSON.parse(options)).length
+
+	let info = SQL_INSERT_GAME.run(user_id, title_id, scenario, options, player_count, priv ? 1 : 0, rand ? 1 : 0, notice)
 	res.redirect("/join/" + info.lastInsertRowid)
 })
 
@@ -1516,7 +1587,7 @@ function update_join_clients_players(game_id) {
 	let list = join_clients[game_id]
 	if (list && list.length > 0) {
 		let players = SQL_SELECT_PLAYERS_JOIN.all(game_id)
-		let ready = is_game_ready(list.title_id, list.scenario, list.options, players)
+		let ready = is_game_ready(list.player_count, players)
 		for (let {res} of list) {
 			res.write("retry: 15000\n")
 			res.write("event: players\n")
@@ -1546,7 +1617,7 @@ app.get('/join/:game_id', must_be_logged_in, function (req, res) {
 	let friends = null
 	if (game.owner_id === req.user.user_id)
 		friends = SQL_SELECT_CONTACT_FRIEND_NAMES.all(req.user.user_id)
-	let ready = (game.status === STATUS_OPEN) && is_game_ready(game.title_id, game.scenario, game.options, players)
+	let ready = (game.status === STATUS_OPEN) && is_game_ready(game.player_count, players)
 	game.ctime = human_date(game.ctime)
 	game.mtime = human_date(game.mtime)
 	res.render('join.pug', {
@@ -1568,9 +1639,7 @@ app.get('/join-events/:game_id', must_be_logged_in, function (req, res) {
 	}
 	if (!(game_id in join_clients)) {
 		join_clients[game_id] = []
-		join_clients[game_id].title_id = game.title_id
-		join_clients[game_id].scenario = game.scenario
-		join_clients[game_id].options = JSON.parse(game.options)
+		join_clients[game_id].player_count = game.player_count
 	}
 	join_clients[game_id].push({ res: res, user_id: req.user.user_id})
 
@@ -1673,7 +1742,7 @@ app.post('/start/:game_id', must_be_logged_in, function (req, res) {
 	if (game.status !== STATUS_OPEN)
 		return res.send("The game is already started.")
 	let players = SQL_SELECT_PLAYERS.all(game_id)
-	if (!is_game_ready(game.title_id, game.scenario, game.options, players))
+	if (!is_game_ready(game.player_count, players))
 		return res.send("Invalid scenario/options/player configuration!")
 	if (game.is_random) {
 		assign_random_roles(game, players)
@@ -1685,7 +1754,7 @@ app.post('/start/:game_id', must_be_logged_in, function (req, res) {
 	let state = RULES[game.title_id].setup(seed, game.scenario, options)
 
 	SQL_UPDATE_GAME_RESULT.run(1, null, game_id)
-	if (is_solo(players))
+	if (game.user_count !== game.player_count)
 		SQL_UPDATE_GAME_PRIVATE.run(game_id)
 	send_game_started_notification_to_offline_users(game_id)
 
@@ -1916,8 +1985,8 @@ function send_your_turn_notification_to_offline_users(game_id, old_active, activ
 
 	let players = SQL_SELECT_PLAYERS.all(game_id)
 	for (let p of players) {
-		let p_was_active = (old_active === p.role || old_active === 'Both' || old_active === 'All')
-		let p_is_active = (active === p.role || active === 'Both' || active === 'All')
+		let p_was_active = (old_active === p.role || old_active === 'Both')
+		let p_is_active = (active === p.role || active === 'Both')
 		if (!p_was_active && p_is_active) {
 			if (!is_player_online(game_id, p.user_id)) {
 				insert_last_notified(p, game_id)
@@ -1970,7 +2039,7 @@ function notify_invited_reminder() {
 function notify_ready_to_start_reminder() {
 	for (let game of SQL_SELECT_OPEN_GAMES.all()) {
 		let players = SQL_SELECT_PLAYERS.all(game.game_id)
-		if (is_game_ready(game.title_id, game.scenario, game.options, players)) {
+		if (is_game_ready(game.player_count, players)) {
 			if (!is_player_online(game.game_id, game.owner_id)) {
 				let owner = SQL_SELECT_USER_VIEW.get(game.owner_id)
 				if (should_send_reminder(owner, game.game_id)) {
@@ -2435,7 +2504,7 @@ wss.on('connection', (socket, req) => {
 
 const SQL_GAME_STATS = SQL(`
 	select
-		title_id, scenario,
+		title_id, player_count, scenario,
 		group_concat(result, '%') as result_role,
 		group_concat(n, '%') as result_count,
 		sum(n) as total
@@ -2443,22 +2512,26 @@ const SQL_GAME_STATS = SQL(`
 		(
 			select
 				title_id,
+				player_count,
 				scenario,
 				result,
 				count(1) as n
 			from
-				opposed_games
+				game_view
 			where
-				( status = ${STATUS_FINISHED} or status = ${STATUS_ARCHIVED} )
-				and
-				( title_id not in ( 'andean-abyss', 'pax-pamir', 'time-of-crisis' ) )
+				is_opposed
+				and ( status = ${STATUS_FINISHED} or status = ${STATUS_ARCHIVED} )
+				and ( title_id not in ( select title_id from titles where is_symmetric ) )
 			group by
 				title_id,
+				player_count,
 				scenario,
 				result
+			order by
+				n desc
 		)
 	group by
-		title_id, scenario
+		title_id, player_count, scenario
 	having
 		total > 12
 	`)
@@ -2485,22 +2558,42 @@ const SQL_USER_STATS = SQL(`
 		count(*) as total
 	from
 		players
-		natural join games
+		natural join game_view
 		natural join titles
 	where
-		user_id = ?
+		not is_symmetric
+		and user_id = ?
+		and is_opposed
 		and ( status = ${STATUS_FINISHED} or status = ${STATUS_ARCHIVED} )
-		and game_id in (select game_id from opposed_games)
 	group by
 		title_name,
 		scenario,
 		role
+	union
+	select
+		title_name,
+		scenario,
+		null as role,
+		sum(role=result) as won,
+		count(*) as total
+	from
+		players
+		natural join game_view
+		natural join titles
+	where
+		is_symmetric
+		and user_id = ?
+		and is_opposed
+		and ( status = ${STATUS_FINISHED} or status = ${STATUS_ARCHIVED} )
+	group by
+		title_name,
+		scenario
 	`)
 
 app.get('/user-stats/:who_name', function (req, res) {
 	let who = SQL_SELECT_USER_BY_NAME.get(req.params.who_name)
 	if (who) {
-		let stats = SQL_USER_STATS.all(who.user_id)
+		let stats = SQL_USER_STATS.all(who.user_id, who.user_id)
 		res.render('user_stats.pug', { user: req.user, who: who, stats: stats })
 	} else {
 		return res.status(404).send("Invalid user name.")
diff --git a/views/games_public.pug b/views/games_public.pug
index d49fc9e..9afc45c 100644
--- a/views/games_public.pug
+++ b/views/games_public.pug
@@ -1,8 +1,4 @@
 //- vim:ts=4:sw=4:
-- let open_games = games0.filter(game => game.status === 0 && !game.is_full)
-- let ready_games = games0.filter(game => game.status === 0 && game.is_full)
-- let replacement_games = games1.filter(game => game.status === 1 && !game.is_full)
-- let active_games = games1.filter(game => game.status === 1 && game.is_full)
 doctype html
 html
 	head
@@ -13,7 +9,7 @@ html
 	body
 		include header
 		article
-			h1 All Public Games
+			h1 Public Games
 
 			h2 Open
 			if open_games.length > 0
@@ -21,17 +17,21 @@ html
 			else
 				p No open games.
 
-			p
-				a(href="/create") Create a new game
-
 			if replacement_games.length > 0
-				h2 Need replacement
+				h2 Open (missing players)
 				+gamelist(replacement_games)
 
+			p
+				a(href="/create") Create a new game
+
 			if ready_games.length > 0
-				h2 Ready
+				h2 Open (waiting to start)
 				+gamelist(ready_games)
 
 			if active_games.length > 0
-				h2 Active
+				h2 Recently active
 				+gamelist(active_games)
+
+			if finished_games.length > 0
+				h2 Recently finished
+				+gamelist(finished_games)
diff --git a/views/head.pug b/views/head.pug
index 2322250..fa2b042 100644
--- a/views/head.pug
+++ b/views/head.pug
@@ -45,10 +45,11 @@ mixin gamelist(list,hide_title=0)
 					let className = "game_item"
 					if (item.your_turn) className += " your_turn"
 					if (item.is_unread) className += " unread"
-					if (item.status === 0 && !item.is_full) className += " open"
-					else if (item.status === 0 && item.is_full) className += " ready"
-					else if (item.status === 1 && !item.is_full) className += " replacement"
-					else if (item.status === 1 && item.is_full) className += " active"
+					if (item.status === 0 && item.join_count === 0) className += " open replacement"
+					else if (item.status === 0 && item.join_count !== item.player_count) className += " open"
+					else if (item.status === 0 && item.join_count === item.player_count) className += " ready"
+					else if (item.status === 1 && item.join_count !== item.player_count) className += " replacement"
+					else if (item.status === 1 && item.join_count === item.player_count) className += " active"
 					else if (item.status === 2) className += " finished"
 					else if (item.status === 3) className += " archived"
 
@@ -63,12 +64,14 @@ mixin gamelist(list,hide_title=0)
 
 						case item.status
 							when 0
-								if item.is_ready && item.your_turn
+								if item.join_count === item.player_count
 									a(class="command" href=`/join/${item.game_id}`) Start
 								else
 									a(class="command" href=`/join/${item.game_id}`) Join
 							when 1
-								if item.is_yours
+								if item.join_count !== item.player_count
+									a(class="command" href="/join/"+item.game_id) Join
+								else if item.is_yours
 									if item.your_role
 										a(class="command" href=`/${item.title_id}/play.html?game=${item.game_id}&role=${encodeURIComponent(item.your_role)}`) Play
 									else
diff --git a/views/header.pug b/views/header.pug
index c417665..454c35a 100644
--- a/views/header.pug
+++ b/views/header.pug
@@ -7,8 +7,8 @@ header
 			if ENABLE_FORUM
 				a(href="/forum") Forum
 			a(href="/games/public") Public
-			if user.active > 0
-				a(href="/games/active") Games (#{user.active})
+			if user.waiting > 0
+				a(href="/games/active") Games (#{user.waiting})
 			else
 				a(href="/games/active") Games
 			a(href="/contacts") Friends
diff --git a/views/info.pug b/views/info.pug
index 60eac7b..51fc81d 100644
--- a/views/info.pug
+++ b/views/info.pug
@@ -1,9 +1,4 @@
 //- vim:ts=4:sw=4:
-- let open_games = games0.filter(game => game.status === 0 && !game.is_full)
-- let ready_games = games0.filter(game => game.status === 0 && game.is_full)
-- let replacement_games = games1.filter(game => game.status === 1 && !game.is_full)
-- let active_games = games1.filter(game => game.status === 1 && game.is_full)
-- let finished_games = games2
 doctype html
 html
 	head
@@ -24,25 +19,23 @@ html
 
 			p Read more about the game on #[a(href="https://boardgamegeek.com/boardgame/"+title.bgg) boardgamegeek.com].
 
-			h2 Open
 			if open_games.length > 0
+				h2 Open
 				+gamelist(open_games, true)
-			else
-				p No open games.
 
 			if replacement_games.length > 0
-				h2 Need replacement
+				h2 Open (missing players)
 				+gamelist(replacement_games, true)
 
 			p
 				a(href="/create/"+title.title_id) Create a new game
 
 			if ready_games.length > 0
-				h2 Ready
+				h2 Open (waiting to start)
 				+gamelist(ready_games, true)
 
 			if active_games.length > 0
-				h2 Active
+				h2 Recently active
 				+gamelist(active_games, true)
 
 			if finished_games.length > 0
diff --git a/views/join.pug b/views/join.pug
index 0f9daef..2e6bf08 100644
--- a/views/join.pug
+++ b/views/join.pug
@@ -43,11 +43,13 @@ html
 
 			unless game.human_options === "None"
 				div Options: #{game.human_options}
-			div
-				if game.is_private
-					| Owner: <a href="/user/#{game.owner_name}">#{game.owner_name}</a> (private)
-				else
-					| Owner: <a href="/user/#{game.owner_name}">#{game.owner_name}</a>
+
+			if game.owner_id
+				div
+					if game.is_private
+						| Owner: <a href="/user/#{game.owner_name}">#{game.owner_name}</a> (private)
+					else
+						| Owner: <a href="/user/#{game.owner_name}">#{game.owner_name}</a>
 
 			div Created: #{game.ctime}
 			unless game.mtime === "never"