Move more player/game info stuff into SQL.

Update invite_count with triggers.

Add games.is_ready generated column.
Add games.is_opposed generated column.

Add player_view with is_active column.

5 files changed, 66 insertions, 71 deletions

diff --git a/public/join.js b/public/join.js
index 6ae4deb..e0f6c3c 100644
--- a/public/join.js
+++ b/public/join.js
@@ -129,20 +129,12 @@ function is_enemy(p) {
 	return blacklist && blacklist.includes(p.user_id)
 }
 
-function is_active(player, role) {
-	return (game.active === role || game.active === "Both" || game.active === "All")
-}
-
-function is_solo() {
-	return players.every(p => p.user_id === players[0].user_id)
-}
-
 function user_link(player) {
 	return `<a class="black" href="/user/${player.name}">${player.name}</a>`
 }
 
 function play_link(player) {
-	return `\xbb <a href="/${game.title_id}/play.html?game=${game.game_id}&role=${encodeURIComponent(player.role)}">Play</a>`
+	return `\xbb <a href="/${game.title_id}/play.html?game=${game.game_id}&role=${encodeURIComponent(player.role)}">${player.name}</a>`
 }
 
 function action_link(player, action, color, text) {
@@ -193,7 +185,7 @@ function update_no_login() {
 		if (game.is_match) {
 			if (player) {
 				if (game.status === 1)
-					element.classList.toggle("is_active", is_active(player, role))
+					element.classList.toggle("is_active", player.is_active)
 				element.innerHTML = user_link(player)
 			} else {
 				element.innerHTML = `<i>Empty</i>`
@@ -215,7 +207,7 @@ function update_no_login() {
 					element.classList.add("is_invite")
 					element.innerHTML = user_link(player) + " ?"
 				} else {
-					element.classList.toggle("is_active", is_active(player, role))
+					element.classList.toggle("is_active", player.is_active)
 					element.innerHTML = user_link(player)
 				}
 				break
@@ -248,7 +240,7 @@ function update_login() {
 		if (game.is_match) {
 			if (player) {
 				if (game.status === 1)
-					element.classList.toggle("is_active", is_active(player, role))
+					element.classList.toggle("is_active", player.is_active)
 				if (player.user_id === user_id && (game.status === 1 || game.status === 2))
 					element.innerHTML = play_link(player)
 				else
@@ -283,7 +275,7 @@ function update_login() {
 					else
 						element.innerHTML = user_link(player) + " ?"
 				} else {
-					element.classList.toggle("is_active", is_active(player, role))
+					element.classList.toggle("is_active", player.is_active)
 					if (player.user_id === user_id)
 						element.innerHTML = play_link(player) + action_link(player, "part", "red", "\u274c")
 					else if (game.owner_id === user_id)
@@ -344,7 +336,7 @@ function update_login() {
 	if (game.owner_id === user_id) {
 		window.start_button.disabled = !ready
 		window.start_button.classList = (game.status === 0) ? "" : "hide"
-		window.delete_button.classList = (game.status === 0 || is_solo()) ? "" : "hide"
+		window.delete_button.classList = (game.status === 0 || game.user_count <= 1) ? "" : "hide"
 		if (game.status === 0 && ready)
 			start_blinker("READY TO START")
 		else
diff --git a/schema.sql b/schema.sql
index f780af4..831be32 100644
--- a/schema.sql
+++ b/schema.sql
@@ -373,7 +373,10 @@ create table if not exists games (
 	mtime datetime default current_timestamp,
 	moves integer default 0,
 	active text,
-	result text
+	result text,
+
+	is_opposed boolean generated as ( user_count = join_count and join_count > 1 ),
+	is_ready boolean generated as ( player_count = join_count and invite_count = 0 )
 );
 
 create index if not exists games_title_idx on games(title_id);
@@ -447,8 +450,7 @@ create view game_view as
 	select
 		games.*,
 		titles.title_name,
-		owner.name as owner_name,
-		user_count = join_count and join_count > 1 as is_opposed
+		owner.name as owner_name
 	from
 		games
 		join titles using(title_id)
@@ -456,6 +458,27 @@ create view game_view as
 			on owner.user_id = games.owner_id
 	;
 
+drop view if exists player_view;
+create view player_view as
+	select
+		game_id,
+		user_id,
+		name,
+		role,
+		is_invite,
+		(
+			case status
+			when 0 then owner_id = user_id
+			when 1 then active in ( 'Both', role )
+			else 0
+			end
+		) as is_active
+	from
+		games
+		join players using(game_id)
+		join users using(user_id)
+	;
+
 drop view if exists ready_to_start_reminder;
 create view ready_to_start_reminder as
 	select
@@ -464,14 +487,7 @@ create view ready_to_start_reminder as
 		games
 		join users on user_id = owner_id
 	where
-		status = 0
-		and join_count = player_count
-		and not exists (
-			select 1 from players
-			where
-				players.game_id = games.game_id
-				and is_invite
-		)
+		status = 0 and is_ready
 	;
 
 drop view if exists your_turn_reminder;
@@ -509,7 +525,8 @@ begin
 		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 )
+		user_count = ( select count(distinct user_id) from players where players.game_id = new.game_id ),
+		invite_count = ( select count(1) from players where players.game_id = new.game_id and players.is_invite )
 	where
 		games.game_id = new.game_id;
 end;
@@ -521,7 +538,19 @@ begin
 		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 )
+		user_count = ( select count(distinct user_id) from players where players.game_id = old.game_id ),
+		invite_count = ( select count(1) from players where players.game_id = old.game_id and players.is_invite )
+	where
+		games.game_id = old.game_id;
+end;
+
+drop trigger if exists trigger_accept_invite;
+create trigger trigger_accept_invite after update of is_invite on players
+begin
+	update
+		games
+	set
+		invite_count = ( select count(1) from players where players.game_id = new.game_id and players.is_invite )
 	where
 		games.game_id = old.game_id;
 end;
diff --git a/server.js b/server.js
index b30bbfd..278cc6e 100644
--- a/server.js
+++ b/server.js
@@ -176,6 +176,7 @@ app.locals.ENABLE_MAIL = !!mailer
 app.locals.ENABLE_WEBHOOKS = !!WEBHOOKS
 app.locals.ENABLE_FORUM = process.env.FORUM | 0
 
+app.locals.EMOJI_MATCH = "\u{1f3c6}"
 app.locals.EMOJI_LIVE = "\u{1f465}"
 app.locals.EMOJI_FAST = "\u{1f3c1}"
 app.locals.EMOJI_SLOW = "\u{1f40c}"
@@ -1269,6 +1270,8 @@ const SQL_SELECT_PLAYER_NAME = SQL("SELECT name FROM players JOIN users using(us
 const SQL_INSERT_PLAYER_ROLE = SQL("INSERT OR IGNORE INTO players (game_id,role,user_id,is_invite) VALUES (?,?,?,?)")
 const SQL_DELETE_PLAYER_ROLE = SQL("DELETE FROM players WHERE game_id=? AND role=?")
 
+const SQL_SELECT_PLAYERS_FULL = SQL("select * from player_view where game_id = ?")
+
 const SQL_COUNT_OPEN_GAMES = SQL(`SELECT COUNT(*) FROM games WHERE owner_id=? AND status=${STATUS_OPEN}`).pluck()
 const SQL_COUNT_ACTIVE_GAMES = SQL(`
 	select count(*) from games
@@ -1408,18 +1411,6 @@ function check_join_game_limit(user) {
 	return null
 }
 
-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
-	}
-}
-
 function annotate_game_info(game, user_id, unread) {
 	let options = parse_game_options(game.options)
 	game.human_options = format_options(game.options, options)
@@ -1430,24 +1421,18 @@ function annotate_game_info(game, user_id, unread) {
 	let your_role = null
 
 	let roles = get_game_roles(game.title_id, game.scenario, options)
+
+	game.players = SQL_SELECT_PLAYERS_FULL.all(game.game_id)
 	for (let p of game.players)
 		p.index = roles.indexOf(p.role)
 	game.players.sort((a, b) => a.index - b.index)
 
 	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"))
-			p_is_active = true
-
 		if (p.user_id === user_id) {
 			your_role = p.role
 			your_count++
-			if (p_is_active && game.is_ready)
+			if (p.is_active && game.is_ready && game.status < 2)
 				game.your_turn = true
 			if (p.is_invite)
 				game.your_turn = true
@@ -1455,9 +1440,9 @@ function annotate_game_info(game, user_id, unread) {
 
 		let link
 		if (p.is_invite)
-			link = `<span class="is_invite"><a href="/user/${p.name}">${p.name}?</a></span>`
-		else if (p_is_active || p_is_owner)
-			link = `<span class="is_active"><a href="/user/${p.name}">${p.name}</a></span>`
+			link = `<a class="is_invite" href="/user/${p.name}">${p.name}?</a>`
+		else if (p.is_active)
+			link = `<a class="is_active" href="/user/${p.name}">${p.name}</a>`
 		else
 			link = `<a href="/user/${p.name}">${p.name}</a>`
 
@@ -1466,10 +1451,8 @@ function annotate_game_info(game, user_id, unread) {
 		else
 			game.player_names = link
 
-		if (game.active === p.role)
-			game.active = link
 		if (game.result === p.role)
-			game.result = `${link} (${game.result})`
+			game.result = `<a href="/user/${p.name}">${p.name}</a> (${game.result})`
 	}
 
 	if (game.status === STATUS_OPEN && game.is_ready && game.owner_id === user_id)
@@ -1486,10 +1469,8 @@ function annotate_game_info(game, user_id, unread) {
 }
 
 function annotate_games(list, user_id, unread) {
-	for (let game of list) {
-		annotate_game_players(game)
+	for (let game of list)
 		annotate_game_info(game, user_id, unread)
-	}
 	return list
 }
 
@@ -1810,7 +1791,7 @@ app.get('/join/:game_id', function (req, res) {
 	game.human_options = format_options(game.options, options)
 
 	let roles = get_game_roles(game.title_id, game.scenario, options)
-	let players = SQL_SELECT_PLAYERS_JOIN.all(game_id)
+	let players = SQL_SELECT_PLAYERS_FULL.all(game_id)
 
 	let whitelist = null
 	let blacklist = null
@@ -1835,7 +1816,7 @@ app.get('/join/:game_id', function (req, res) {
 app.get('/join-events/:game_id', must_be_logged_in, function (req, res) {
 	let game_id = req.params.game_id | 0
 	let game = SQL_SELECT_GAME_VIEW.get(game_id)
-	let players = SQL_SELECT_PLAYERS_JOIN.all(game_id)
+	let players = SQL_SELECT_PLAYERS_FULL.all(game_id)
 
 	res.setHeader("Content-Type", "text/event-stream")
 	res.setHeader("Connection", "keep-alive")
diff --git a/views/head.pug b/views/head.pug
index 77ab73b..e47033a 100644
--- a/views/head.pug
+++ b/views/head.pug
@@ -61,14 +61,10 @@ mixin gamelist(list,hide_title=0)
 					else if (item.status === 2) className += " finished"
 					else if (item.status === 3) className += " archived"
 					if (item.is_unread) chat_icon = "\u{1f4dd}"
-					if (item.pace === 0) pace_icon = "", pace_text = ""
+					if (item.is_match) pace_icon = EMOJI_MATCH
 					else if (item.pace === 1) pace_icon = EMOJI_LIVE, pace_text = "Live!"
 					else if (item.pace === 2) pace_icon = EMOJI_FAST, pace_text = "Fast - many moves per day"
 					else if (item.pace === 3) pace_icon = EMOJI_SLOW, pace_text = "Slow - one move per day"
-					let invite_count = 0
-					for (let p of item.players)
-						if (p.is_invite)
-							invite_count++
 
 				div(class=className)
 					div.game_head
@@ -81,12 +77,9 @@ mixin gamelist(list,hide_title=0)
 
 						case item.status
 							when 0
-								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
+								a(class="command" href=`/join/${item.game_id}`) Join
 							when 1
-								if item.join_count !== item.player_count || invite_count > 0
+								if !item.is_ready
 									a(class="command" href="/join/"+item.game_id) Join
 								else if item.is_yours
 									if item.your_role
@@ -124,7 +117,7 @@ mixin gamelist(list,hide_title=0)
 								when 0
 									div Created: #{item.ctime}
 								when 1
-									div Changed: #{item.mtime}
+									div Last move: #{item.mtime}
 								when 2
 									div Finished: #{item.mtime}
 									div Result: !{item.result}
diff --git a/views/join.pug b/views/join.pug
index 2fc9287..37c245b 100644
--- a/views/join.pug
+++ b/views/join.pug
@@ -57,7 +57,7 @@ html
 			if game.status > 1
 				p Finished #{game.mtime}.
 			else if game.status > 0
-				p Last changed #{game.mtime}.
+				p Last move #{game.mtime}.
 
 			unless game.human_options === "None"
 				p Options: #{game.human_options}.