Rewrite is_your_turn SQL statements.

Now they should work in all lobby views.

5 files changed, 43 insertions, 18 deletions

diff --git a/public/join.js b/public/join.js
index 4c2b0a8..a5a2e3a 100644
--- a/public/join.js
+++ b/public/join.js
@@ -77,9 +77,7 @@ function start_event_source() {
 }
 
 function is_your_turn(player, role) {
-	if (player.user_id === user_id)
-		return (game.active_role === role || game.active_role === "Both" || game.active_role === "All");
-	return false;
+	return (game.active_role === role || game.active_role === "Both" || game.active_role === "All");
 }
 
 function update() {
diff --git a/server.js b/server.js
index 83ec904..3102323 100644
--- a/server.js
+++ b/server.js
@@ -556,18 +556,37 @@ for (let title_id of db.prepare("SELECT * FROM titles").pluck().all()) {
 	}
 }
 
-const QUERY_GAME = db.prepare("SELECT * FROM game_view WHERE game_id = ?");
 const QUERY_LIST_GAMES_OF_TITLE = db.prepare(`
-	SELECT * FROM game_view
-	WHERE title_id = ? AND private = 0
+	SELECT *,
+		EXISTS (
+			SELECT 1 FROM players
+			WHERE players.game_id = game_view.game_id
+			AND user_id = $user_id
+			AND active_role IN ( 'All', 'Both', role )
+		) AS is_your_turn
+	FROM game_view
+	WHERE title_id = $title_id AND private = 0
 	ORDER BY status ASC, mtime DESC
 `);
+
 const QUERY_LIST_GAMES_OF_USER = db.prepare(`
-	SELECT DISTINCT game_view.*, ( active_id = $user_id OR active_role = 'Both' OR active_role = 'All' ) AS is_your_turn FROM game_view
-	LEFT JOIN players ON game_view.game_id = players.game_id
-	WHERE game_view.owner_id = $user_id OR players.user_id = $user_id
+	SELECT *,
+		EXISTS (
+			SELECT 1 FROM players
+			WHERE players.game_id = game_view.game_id
+			AND user_id = $user_id
+			AND active_role IN ( 'All', 'Both', role )
+		) AS is_your_turn
+	FROM game_view
+	WHERE owner_id = $user_id
+		OR EXISTS (
+			SELECT 1 FROM players
+			WHERE players.game_id = game_view.game_id
+			AND user_id = $user_id
+		)
 	ORDER BY status ASC, mtime DESC
 `);
+
 const QUERY_PLAYERS = db.prepare("SELECT role, user_id, user_name FROM player_view WHERE game_id = ?");
 const QUERY_PLAYERS_FULL = db.prepare(`
 	SELECT
@@ -581,6 +600,7 @@ const QUERY_PLAYERS_FULL = db.prepare(`
 	WHERE players.game_id = ?
 `);
 
+const QUERY_GAME = db.prepare("SELECT * FROM game_view WHERE game_id = ?");
 const QUERY_TITLE = db.prepare("SELECT * FROM titles WHERE title_id = ?");
 const QUERY_ROLES = db.prepare("SELECT role FROM roles WHERE title_id = ? ORDER BY rowid").pluck();
 const QUERY_GAME_OWNER = db.prepare("SELECT * FROM games WHERE game_id = ? AND owner_id = ?");
@@ -648,7 +668,7 @@ app.get('/info/:title_id', function (req, res) {
 		return res.redirect('/');
 	}
 	if (req.isAuthenticated()) {
-		let games = QUERY_LIST_GAMES_OF_TITLE.all(title_id);
+		let games = QUERY_LIST_GAMES_OF_TITLE.all({user_id: req.user.user_id, title_id: title_id});
 		humanize(games);
 		let open_games = games.filter(game => game.status === 0);
 		let active_games = games.filter(game => game.status === 1);
@@ -1379,11 +1399,22 @@ app.get('/users', function (req, res) {
 	res.render('users.ejs', { user: req.user, message: req.flash('message'), userList: rows });
 });
 
-const QUERY_LIST_GAMES = db.prepare("SELECT * FROM game_view WHERE private = 0 AND status < 2");
+const QUERY_LIST_GAMES = db.prepare(`
+	SELECT *,
+		EXISTS (
+			SELECT 1 FROM players
+			WHERE players.game_id = game_view.game_id
+			AND user_id = $user_id
+			AND active_role IN ( 'All', 'Both', role )
+		) AS is_your_turn
+	FROM game_view
+	WHERE private = 0 AND status < 2
+	ORDER BY status ASC, mtime DESC
+`);
 
 app.get('/games', must_be_logged_in, function (req, res) {
 	LOG(req, "GET /join");
-	let games = QUERY_LIST_GAMES.all();
+	let games = QUERY_LIST_GAMES.all({user_id: req.user.user_id});
 	humanize(games);
 	let open_games = games.filter(game => game.status === 0);
 	let active_games = games.filter(game => game.status === 1);
diff --git a/tools/sql/schema.txt b/tools/sql/schema.txt
index 71b6f72..e731c8c 100644
--- a/tools/sql/schema.txt
+++ b/tools/sql/schema.txt
@@ -153,12 +153,9 @@ CREATE VIEW game_view AS
 		, games.status
 		, games.result
 		, games.active AS active_role
-		, active.user_id AS active_id
-		, active.user_name AS active_name
 		-- , games.state
 	FROM games
 	JOIN titles USING ( title_id )
 	JOIN users AS owner ON games.owner_id = owner.user_id
 	LEFT JOIN player_list_view AS players USING ( game_id )
-	LEFT JOIN player_view AS active ON games.game_id = active.game_id AND games.active = active.role
 	;
diff --git a/views/games.ejs b/views/games.ejs
index a355910..e25df1f 100644
--- a/views/games.ejs
+++ b/views/games.ejs
@@ -33,10 +33,9 @@ td.nowrap a { color: black; text-decoration: none; }
 <td><%= row.player_names %>
 <td><%= row.description %>
 <td class="nowrap"><%= row.mtime %>
-<td><%= row.active_role %>
+<td class="<%= row.is_your_turn ? "is_your_turn" : "" %>"><%= row.active_role %>
 <td><a href="/join/<%= row.game_id %>">Enter</a>
 <% }); } else { %>
 <tr><td colspan="8">No active games.
 <% } %>
 </table>
-
diff --git a/views/info.ejs b/views/info.ejs
index fdfc974..778974e 100644
--- a/views/info.ejs
+++ b/views/info.ejs
@@ -38,7 +38,7 @@ Read more about the game on
 <td><%= row.player_names %>
 <td><%= row.description %>
 <td class="nowrap"><%= row.mtime %>
-<td><%= row.active_role %>
+<td class="<%= row.is_your_turn ? "is_your_turn" : "" %>"><%= row.active_role %>
 <td><a href="/join/<%= row.game_id %>">Enter</a>
 <% }); %>
 </table>