Clean up SQL and use table views.

10 files changed, 176 insertions, 261 deletions

diff --git a/public/common/client.js b/public/common/client.js
index 28b4f8e..670fa07 100644
--- a/public/common/client.js
+++ b/public/common/client.js
@@ -220,7 +220,7 @@ function init_client(roles) {
 		document.querySelector(".grid_top").classList.add(player);
 		for (let i = 0; i < roles.length; ++i) {
 			let p = players.find(p => p.role == roles[i]);
-			document.querySelector(USER_SEL[i]).textContent = p ? p.name : "NONE";
+			document.querySelector(USER_SEL[i]).textContent = p ? p.user_name : "NONE";
 		}
 	});
 
diff --git a/public/join.js b/public/join.js
index d2d39ab..5353f11 100644
--- a/public/join.js
+++ b/public/join.js
@@ -84,24 +84,23 @@ function update() {
 		let element = document.getElementById(role_id);
 		if (player) {
 			if (game.status > 0) {
-				if (game.active === role || game.active === "Both" || game.active === "All")
-					element.className = "your_turn";
+				if (game.active_role === role || game.active_role === "Both" || game.active_role === "All")
+					element.className = "is_your_turn";
 				else
 					element.className = "";
 				if (player.user_id === user_id)
 					element.innerHTML = `<a href="/play/${game.game_id}/${role}">Play</a>`;
 				else
-					element.innerHTML = player.name;
+					element.innerHTML = player.user_name;
 			} else {
 				if ((player.user_id === user_id) || (game.owner_id === user_id))
-					element.innerHTML = `<a class="red" href="javascript:send('/part/${game.game_id}/${role}')">\u274c</a> ${player.name}`;
+					element.innerHTML = `<a class="red" href="javascript:send('/part/${game.game_id}/${role}')">\u274c</a> ${player.user_name}`;
 				else
-					element.innerHTML = player.name;
+					element.innerHTML = player.user_name;
 			}
 		} else {
 			if (game.status === 0)
-				//element.innerHTML = `<a class="join" href="javascript:send('/join/${game.game_id}/${role}')">Join</a>`;
-				element.innerHTML = `<a class="join" onclick="send('/join/${game.game_id}/${role}')" href="javascript:void 0">Join</a>`;
+				element.innerHTML = `<a class="join" href="javascript:send('/join/${game.game_id}/${role}')">Join</a>`;
 			else
 				element.innerHTML = "<i>Empty</i>";
 		}
@@ -122,7 +121,7 @@ function update() {
 	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 || solo) ? "" : "hide";
+		window.delete_button.classList = (game.status === 0 || game.is_solo) ? "" : "hide";
 	}
 
 	if (game.status === 0 && ready)
diff --git a/public/style.css b/public/style.css
index 0503574..d5c4230 100644
--- a/public/style.css
+++ b/public/style.css
@@ -39,7 +39,7 @@ input[type="text"], input[type="password"] { padding: 5px; }
 select { padding-right: 20px; }
 form { display: inline; }
 .nowrap { white-space: nowrap; }
-.your_turn { background-color: lemonchiffon; }
+.is_your_turn { background-color: lemonchiffon; }
 button, select {
 	margin: 5px 10px 5px 0;
 	padding: 1px 10px;
diff --git a/server.js b/server.js
index 3d8357c..9634a78 100644
--- a/server.js
+++ b/server.js
@@ -21,20 +21,21 @@ const MAX_OPEN_GAMES = 3;
 let session_store = new SQLiteStore();
 let db = new sqlite3(process.env.DATABASE || "./db");
 let app = express();
-let http_port = process.env.PORT || 8080;
 let https_port = process.env.HTTPS_PORT || 8443;
-let http = require('http').createServer(app);
 let https = require('https').createServer({
 	key: fs.readFileSync(process.env.SSL_KEY || "key.pem"),
 	cert: fs.readFileSync(process.env.SSL_CERT || "cert.pem")
 	}, app);
-let socket_io = require('socket.io');
-let io1 = socket_io(http);
-let io2 = socket_io(https);
-let io = {
-	use: function (fn) { io1.use(fn); io2.use(fn); },
-	on: function (ev,fn) { io1.on(ev,fn); io2.on(ev,fn); },
-};
+https.listen(https_port, '0.0.0.0', () => { console.log('listening HTTPS on *:' + https_port); });
+
+let io = require('socket.io')(https);
+
+// REDIRECT HTTP TO HTTPS
+let http_port = process.env.HTTP_PORT || 8080;
+let http_app = express();
+let http_server = require('http').createServer(http_app);
+http_app.use((req, res) => res.redirect(301, 'https://' + req.hostname + ":" + https_port + req.path));
+http_server.listen(http_port, '0.0.0.0', () => { console.log('listening HTTP on *:' + http_port); });
 
 let mailer = null;
 if (process.env.MAIL_HOST && process.env.MAIL_PORT) {
@@ -315,31 +316,6 @@ app.post('/signup',
 	})
 );
 
-app.get('/users', function (req, res) {
-	LOG(req, "GET /users");
-	let rows = db.prepare("SELECT name, mail, ctime, atime FROM users ORDER BY atime DESC").all();
-	rows.forEach(row => {
-		row.avatar = get_avatar(row.mail);
-		row.ctime = human_date(row.ctime);
-		row.atime = human_date(row.atime);
-	});
-	res.render('users.ejs', { user: req.user, message: req.flash('message'), userList: rows });
-});
-
-const QUERY_STATS = db.prepare(`
-	SELECT title_name, scenario, result, count(*) AS count
-	FROM games
-	JOIN titles ON games.title_id=titles.title_id
-	WHERE status=2 AND private=0
-	GROUP BY title_name, scenario, result
-	`);
-
-app.get('/stats', function (req, res) {
-	LOG(req, "GET /stats");
-	let stats = QUERY_STATS.all();
-	res.render('stats.ejs', { user: req.user, message: req.flash('message'), stats: stats });
-});
-
 app.get('/change_password', must_be_logged_in, function (req, res) {
 	LOG(req, "GET /change_password");
 	res.render('change_password.ejs', { user: req.user, message: req.flash('message') });
@@ -499,115 +475,33 @@ app.post('/change_password', must_be_logged_in, function (req, res) {
  */
 
 let RULES = {};
-let PLAYER_COUNT = {};
-let QUERY_PLAYER_COUNT = db.prepare("SELECT COUNT(*) FROM roles WHERE title_id = ?").pluck();
 for (let title_id of db.prepare("SELECT * FROM titles").pluck().all()) {
 	console.log("Loading rules for " + title_id);
 	try {
 		RULES[title_id] = require("./public/" + title_id + "/rules.js");
-		PLAYER_COUNT[title_id] = QUERY_PLAYER_COUNT.get(title_id);
 	} catch (err) {
 		console.log(err);
 	}
 }
 
-const QUERY_GAME = db.prepare(`
-	SELECT
-		games.game_id,
-		games.title_id AS title_id,
-		titles.title_name AS title_name,
-		games.scenario AS scenario,
-		games.owner AS owner_id,
-		users.name AS owner_name,
-		games.ctime,
-		games.mtime,
-		games.description,
-		games.status,
-		games.private,
-		games.random,
-		games.result,
-		games.active
-	FROM games
-	JOIN users ON games.owner = users.user_id
-	JOIN titles ON games.title_id = titles.title_id
-	WHERE game_id = ?
-`);
-
-const QUERY_LIST_GAMES = db.prepare(`
-	SELECT
-		games.game_id,
-		games.title_id AS title_id,
-		games.scenario AS scenario,
-		games.owner AS owner_id,
-		users.name AS owner_name,
-		games.ctime,
-		games.mtime,
-		games.description,
-		games.status,
-		games.result,
-		games.active,
-		titles.title_name
-	FROM games
-	LEFT JOIN users ON games.owner = users.user_id
-	LEFT JOIN titles ON games.title_id = titles.title_id
-	WHERE private = 0 AND status < 2
-`);
-
+const QUERY_GAME = db.prepare("SELECT * FROM game_view WHERE game_id = ?");
 const QUERY_LIST_GAMES_OF_TITLE = db.prepare(`
-	SELECT
-		games.game_id,
-		games.title_id AS title_id,
-		games.scenario AS scenario,
-		games.owner AS owner_id,
-		users.name AS owner_name,
-		games.ctime,
-		games.mtime,
-		games.description,
-		games.status,
-		games.result,
-		games.active
-	FROM games
-	JOIN users ON games.owner = users.user_id
+	SELECT *, ( active_id = ? OR active_role = 'Both' OR active_role = 'All' ) AS is_your_turn FROM game_view
 	WHERE title_id = ? AND private = 0
 	ORDER BY status ASC, mtime DESC
 `);
-
 const QUERY_LIST_GAMES_OF_USER = db.prepare(`
-	SELECT DISTINCT
-		games.game_id,
-		games.title_id,
-		titles.title_name,
-		games.scenario AS scenario,
-		users.name AS owner_name,
-		games.description,
-		games.ctime,
-		games.mtime,
-		games.status,
-		games.result,
-		games.active
-	FROM games
-	LEFT JOIN players ON games.game_id = players.game_id
-	LEFT JOIN users ON games.owner = users.user_id
-	LEFT JOIN titles ON games.title_id = titles.title_id
-	WHERE games.owner = ? OR players.user_id = ?
+	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
 	ORDER BY status ASC, mtime DESC
 `);
-
-const QUERY_PLAYERS = db.prepare(`
-	SELECT
-		players.user_id,
-		players.role,
-		users.name
-	FROM players
-	JOIN users ON players.user_id = users.user_id
-	WHERE players.game_id = ?
-`);
-
+const QUERY_PLAYERS = db.prepare("SELECT role, user_id, user_name FROM player_view WHERE game_id = ?");
 const QUERY_PLAYERS_FULL = db.prepare(`
 	SELECT
 		players.user_id,
 		players.role,
-		users.name,
+		users.name AS user_name,
 		users.mail,
 		users.notifications
 	FROM players
@@ -615,18 +509,9 @@ const QUERY_PLAYERS_FULL = db.prepare(`
 	WHERE players.game_id = ?
 `);
 
-const QUERY_PLAYER_NAMES = db.prepare(`
-	SELECT
-		users.name AS name
-	FROM players
-	JOIN users ON players.user_id = users.user_id
-	WHERE players.game_id = ?
-	ORDER BY players.role
-`).pluck();
-
 const QUERY_TITLE = db.prepare("SELECT * FROM titles WHERE title_id = ?");
-const QUERY_ROLES = db.prepare("SELECT role FROM roles WHERE title_id = ?").pluck();
-const QUERY_GAME_OWNER = db.prepare("SELECT * FROM games WHERE game_id = ? AND owner = ?");
+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 = ?");
 const QUERY_TITLE_FROM_GAME = db.prepare("SELECT title_id FROM games WHERE game_id = ?").pluck();
 const QUERY_ROLE_FROM_GAME_AND_USER = db.prepare("SELECT role FROM players WHERE game_id = ? AND user_id = ?").pluck();
 const QUERY_IS_SOLO = db.prepare("SELECT COUNT(DISTINCT user_id) = 1 FROM players WHERE game_id = ?").pluck();
@@ -636,16 +521,14 @@ const QUERY_PART_GAME = db.prepare("DELETE FROM players WHERE game_id = ? AND ro
 const QUERY_START_GAME = db.prepare("UPDATE games SET status = 1, state = ?, active = ? WHERE game_id = ?");
 const QUERY_CREATE_GAME = db.prepare(`
 	INSERT INTO games
-	(owner,title_id,scenario,private,random,ctime,mtime,description,status,state)
+	(owner_id,title_id,scenario,private,random,ctime,mtime,description,status,state)
 	VALUES
 	(?,?,?,?,?,datetime('now'),datetime('now'),?,0,NULL)
 `);
 const QUERY_UPDATE_GAME_SET_PRIVATE = db.prepare("UPDATE games SET private = 1 WHERE game_id = ?");
 const QUERY_ASSIGN_ROLE = db.prepare("UPDATE players SET role = ? WHERE game_id = ? AND user_id = ? AND role = ?");
 
-const QUERY_IS_PLAYER = db.prepare("SELECT COUNT(*) FROM players WHERE game_id = ? AND user_id = ?").pluck();
-const QUERY_IS_ACTIVE = db.prepare("SELECT COUNT(*) FROM players WHERE game_id = ? AND role = ? AND user_id = ?").pluck();
-const QUERY_COUNT_OPEN_GAMES = db.prepare("SELECT COUNT(*) FROM games WHERE owner = ? AND status = 0").pluck();
+const QUERY_COUNT_OPEN_GAMES = db.prepare("SELECT COUNT(*) FROM games WHERE owner_id = ? AND status = 0").pluck();
 const QUERY_DELETE_GAME = db.prepare("DELETE FROM games WHERE game_id = ?");
 
 const QUERY_REMATCH_FIND = db.prepare(`
@@ -654,7 +537,7 @@ const QUERY_REMATCH_FIND = db.prepare(`
 
 const QUERY_REMATCH_CREATE = db.prepare(`
 	INSERT INTO games
-		(owner, title_id, scenario, private, random, ctime, mtime, description, status, state)
+		(owner_id, title_id, scenario, private, random, ctime, mtime, description, status, state)
 	SELECT
 		$user_id, title_id, scenario, private, random, datetime('now'), datetime('now'), $magic, 0, NULL
 	FROM games
@@ -667,23 +550,11 @@ app.get('/', function (req, res) {
 	res.render('index.ejs', { user: req.user, message: req.flash('message') });
 });
 
-function is_your_turn(game, user) {
-	if (!game.active || game.active == "None")
-		return false;
-	if (game.active == "All" || game.active == "Both")
-		return QUERY_IS_PLAYER.get(game.game_id, user.user_id);
-	return QUERY_IS_ACTIVE.get(game.game_id, game.active, user.user_id);
-}
-
 app.get('/profile', must_be_logged_in, function (req, res) {
 	LOG(req, "GET /profile");
 	let avatar = get_avatar(req.user.mail);
-	let games = QUERY_LIST_GAMES_OF_USER.all(req.user.user_id, req.user.user_id);
+	let games = QUERY_LIST_GAMES_OF_USER.all({user_id: req.user.user_id});
 	humanize(games);
-	for (let game of games) {
-		game.players = QUERY_PLAYER_NAMES.all(game.game_id);
-		game.your_turn = is_your_turn(game, req.user);
-	}
 	let open_games = games.filter(game => game.status == 0);
 	let active_games = games.filter(game => game.status == 1);
 	let finished_games = games.filter(game => game.status == 2);
@@ -696,24 +567,6 @@ app.get('/profile', must_be_logged_in, function (req, res) {
 	});
 });
 
-app.get('/games', must_be_logged_in, function (req, res) {
-	LOG(req, "GET /join");
-	let games = QUERY_LIST_GAMES.all();
-	humanize(games);
-	for (let game of games) {
-		game.players = QUERY_PLAYER_NAMES.all(game.game_id);
-		game.your_turn = is_your_turn(game, req.user);
-	}
-	let open_games = games.filter(game => game.status == 0);
-	let active_games = games.filter(game => game.status == 1);
-	res.set("Cache-Control", "no-store");
-	res.render('games.ejs', { user: req.user,
-		open_games: open_games,
-		active_games: active_games,
-		message: req.flash('message')
-	});
-});
-
 app.get('/info/:title_id', function (req, res) {
 	LOG(req, "GET /info/" + req.params.title_id);
 	let title_id = req.params.title_id;
@@ -723,17 +576,11 @@ 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(req.user.user_id, title_id);
 		humanize(games);
 		let open_games = games.filter(game => game.status == 0);
 		let active_games = games.filter(game => game.status == 1);
-		for (let game of active_games) {
-			game.players = QUERY_PLAYER_NAMES.all(game.game_id);
-			game.your_turn = is_your_turn(game, req.user);
-		}
 		let finished_games = games.filter(game => game.status == 2);
-		for (let game of finished_games)
-			game.players = QUERY_PLAYER_NAMES.all(game.game_id);
 		res.set("Cache-Control", "no-store");
 		res.render('info.ejs', { user: req.user, title: title,
 			open_games: open_games,
@@ -835,9 +682,8 @@ let join_clients = {};
 function update_join_clients_deleted(game_id) {
 	let list = join_clients[game_id];
 	if (list && list.length > 0) {
-		console.log("UPDATE JOIN DELETED", game_id, list.length)
+		console.log("JOIN: UPDATE GAME DELETED", game_id, list.title_id, list.length, players.map(p => p.user_name));
 		for (let res of list) {
-			console.log("PUSH JOIN DELETED", game_id);
 			res.write("retry: 15000\n");
 			res.write("event: deleted\n");
 			res.write("data: The game doesn't exist.\n\n");
@@ -848,10 +694,9 @@ function update_join_clients_deleted(game_id) {
 function update_join_clients_game(game_id) {
 	let list = join_clients[game_id];
 	if (list && list.length > 0) {
-		console.log("UPDATE JOIN GAME", game_id, list.length)
 		let game = QUERY_GAME.get(game_id);
+		console.log("JOIN: UPDATE GAME STATUS", game_id, list.title_id, list.length, game)
 		for (let res of list) {
-			console.log("PUSH JOIN GAME", game_id);
 			res.write("retry: 15000\n");
 			res.write("event: game\n");
 			res.write("data: " + JSON.stringify(game) + "\n\n");
@@ -861,12 +706,11 @@ function update_join_clients_game(game_id) {
 
 function update_join_clients_players(game_id) {
 	let list = join_clients[game_id];
-	if (list) {
-		console.log("UPDATE JOIN PLAYERS", game_id, list.length)
+	if (list && list.length > 0) {
 		let players = QUERY_PLAYERS.all(game_id);
 		let ready = RULES[list.title_id].ready(list.scenario, players);
+		console.log("JOIN: UPDATE PLAYERS", game_id, list.title_id, list.length, players.map(p => p.role + ": " + p.user_name), ready)
 		for (let res of list) {
-			console.log("PUSH JOIN PLAYERS", game_id);
 			res.write("retry: 15000\n");
 			res.write("event: players\n");
 			res.write("data: " + JSON.stringify(players) + "\n\n");
@@ -893,8 +737,7 @@ app.get('/join/:game_id', must_be_logged_in, function (req, res) {
 		game: game,
 		roles: roles,
 		players: players,
-		solo: players.every(p => p.user_id == req.user.user_id),
-		ready: players.length == roles.length,
+		ready: ready,
 		message: req.flash('message')
 	});
 });
@@ -920,8 +763,8 @@ app.get('/join-events/:game_id', must_be_logged_in, function (req, res) {
 	}
 	join_clients[game_id].push(res);
 
-	res.on('close', err => {
-		console.log("CLOSE JOIN EVENTS", err);
+	res.on('close', () => {
+		console.log("JOIN: CLOSE CONNECTION TO", game_id);
 		let list = join_clients[game_id];
 		let i = list.indexOf(res);
 		if (i >= 0)
@@ -974,7 +817,7 @@ function assign_random_roles(game, players) {
 	for (let p of players) {
 		let old_role = p.role;
 		p.role = pick_random_item(roles);
-		console.log("ASSIGN ROLE", "(" + p.name + ")", old_role, "->", p.role);
+		console.log("ASSIGN ROLE", "(" + p.user_name + ")", old_role, "->", p.role);
 		QUERY_ASSIGN_ROLE.run(p.role, game.game_id, p.user_id, old_role);
 	}
 }
@@ -1028,10 +871,13 @@ app.get('/play/:game_id', must_be_logged_in, function (req, res) {
 	let game_id = req.params.game_id | 0;
 	let user_id = req.user.user_id | 0;
 	try {
+		let title = QUERY_TITLE_FROM_GAME.get(game_id);
+		if (!title)
+			return res.redirect('/join/'+game_id);
 		let role = QUERY_ROLE_FROM_GAME_AND_USER.get(game_id, user_id);
 		if (!role)
-			return res.redirect('/play/'+game_id+'/Observer');
-		return res.redirect('/play/'+game_id+'/'+role);
+			res.redirect('/'+title+'/play.html?game='+game_id+'&role=Observer');
+		return res.redirect('/'+title+'/play.html?game='+game_id+'&role='+role);
 	} catch (err) {
 		req.flash('message', err.toString());
 		return res.redirect('/join/'+game_id);
@@ -1052,18 +898,12 @@ const sql_offline_user = db.prepare("SELECT * FROM users WHERE user_id = ? AND d
 const QUERY_LIST_YOUR_TURN = db.prepare(`
 	SELECT games.game_id, games.title_id, players.user_id, users.name, users.mail, users.notifications
 	FROM games
-	JOIN players ON games.game_id = players.game_id AND ( games.active = players.role OR games.active = 'All' OR games.active = 'Both' )
+	JOIN players ON games.game_id = players.game_id AND ( games.active = players.role OR games.active = 'Both' OR games.active = 'All' )
 	JOIN users ON users.user_id = players.user_id
 	WHERE games.status = 1 AND datetime('now') > datetime(games.mtime, '+1 hour')
 `);
 
-const QUERY_LIST_READY_TO_START = db.prepare(`
-	SELECT games.game_id, games.title_id, games.owner, COUNT(*) AS joined
-	FROM games
-	JOIN players ON games.game_id = players.game_id
-	WHERE games.status = 0
-	GROUP BY games.game_id
-`);
+const QUERY_LIST_UNSTARTED_GAMES = db.prepare("SELECT * FROM game_view WHERE status = 0");
 
 function mail_callback(err, info) {
 	console.log("MAIL SENT", err, info);
@@ -1159,9 +999,10 @@ function notify_your_turn_reminder() {
 }
 
 function notify_ready_to_start_reminder() {
-	for (let game of QUERY_LIST_READY_TO_START.all()) {
-		if (game.joined == PLAYER_COUNT[game.title_id]) {
-			let owner = sql_offline_user.get(game.owner, '+3 minutes');
+	for (let game of QUERY_LIST_UNSTARTED_GAMES.all()) {
+		let players = QUERY_PLAYERS.all(game.game_id);
+		if (RULES[game.title_id].ready(game.scenario, players)) {
+			let owner = sql_offline_user.get(game.owner_id, '+3 minutes');
 			if (owner) {
 				console.log("REMINDER: READY TO START", game.title_id, game.game_id, owner.name, owner.mail, owner.notifications);
 				if (owner.notifications)
@@ -1171,10 +1012,10 @@ function notify_ready_to_start_reminder() {
 	}
 }
 
-// Check and send 'your turn' reminders quarterly.
+// Check and send 'your turn' reminders every 15 minutes.
 setInterval(notify_your_turn_reminder, 15 * 60 * 1000);
 
-// Check and send ready to start notifications once a minute.
+// Check and send ready to start notifications every minute.
 setInterval(notify_ready_to_start_reminder, 60 * 1000);
 
 /*
@@ -1426,5 +1267,45 @@ io.on('connection', (socket) => {
 	}
 });
 
-http.listen(http_port, '0.0.0.0', () => { console.log('listening HTTP on *:' + http_port); });
-https.listen(https_port, '0.0.0.0', () => { console.log('listening HTTPS on *:' + https_port); });
+// EXTRAS
+
+const QUERY_STATS = db.prepare(`
+	SELECT title_name, scenario, result, count(*) AS count
+	FROM games
+	JOIN titles ON games.title_id=titles.title_id
+	WHERE status=2 AND private=0
+	GROUP BY title_name, scenario, result
+	`);
+
+app.get('/stats', function (req, res) {
+	LOG(req, "GET /stats");
+	let stats = QUERY_STATS.all();
+	res.render('stats.ejs', { user: req.user, message: req.flash('message'), stats: stats });
+});
+
+app.get('/users', function (req, res) {
+	LOG(req, "GET /users");
+	let rows = db.prepare("SELECT name, mail, ctime, atime FROM users ORDER BY atime DESC").all();
+	rows.forEach(row => {
+		row.avatar = get_avatar(row.mail);
+		row.ctime = human_date(row.ctime);
+		row.atime = human_date(row.atime);
+	});
+	res.render('users.ejs', { user: req.user, message: req.flash('message'), userList: rows });
+});
+
+const QUERY_LIST_GAMES = db.prepare("SELECT *, ( active_id = ? OR active_role = 'Both' OR active_role = 'All' ) AS is_your_turn FROM game_view WHERE private = 0 AND status < 2");
+
+app.get('/games', must_be_logged_in, function (req, res) {
+	LOG(req, "GET /join");
+	let games = QUERY_LIST_GAMES.all(req.user.user_id);
+	humanize(games);
+	let open_games = games.filter(game => game.status == 0);
+	let active_games = games.filter(game => game.status == 1);
+	res.set("Cache-Control", "no-store");
+	res.render('games.ejs', { user: req.user,
+		open_games: open_games,
+		active_games: active_games,
+		message: req.flash('message')
+	});
+});
diff --git a/tools/sql/schema.txt b/tools/sql/schema.txt
index 2f84e82..71b6f72 100644
--- a/tools/sql/schema.txt
+++ b/tools/sql/schema.txt
@@ -43,7 +43,7 @@ CREATE TABLE IF NOT EXISTS games (
 	game_id INTEGER PRIMARY KEY,
 	title_id TEXT,
 	scenario TEXT,
-	owner INTEGER,
+	owner_id INTEGER,
 	private BOOLEAN,
 	random BOOLEAN,
 	description TEXT,
@@ -111,3 +111,54 @@ BEGIN
 	DELETE FROM notifications WHERE game_id = old.game_id;
 	DELETE FROM chats WHERE game_id = old.game_id;
 END;
+
+DROP VIEW IF EXISTS player_view;
+DROP VIEW IF EXISTS player_list_view;
+DROP VIEW IF EXISTS game_view;
+
+CREATE VIEW player_view AS
+	SELECT players.game_id
+		, players.role AS role
+		, players.user_id AS user_id
+		, users.name AS user_name
+	FROM players
+	JOIN users ON players.user_id = users.user_id
+	;
+
+CREATE VIEW player_list_view AS
+	SELECT game_id
+		, group_concat(name, ', ') AS player_names
+		, COUNT(DISTINCT user_id) AS user_count
+		, COUNT(user_id) AS role_count
+	FROM players
+	JOIN users USING ( user_id )
+	GROUP BY game_id
+	;
+
+CREATE VIEW game_view AS
+	SELECT games.game_id
+		, games.title_id
+		, titles.title_name
+		, games.scenario
+		, games.owner_id
+		, owner.name AS owner_name
+		, players.player_names
+		, players.user_count = 1 AS is_solo
+		, players.user_count <> players.role_count AS is_shared
+		, games.private
+		, games.random
+		, games.description
+		, games.ctime
+		, games.mtime
+		, 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 fb58be8..4e6f2a4 100644
--- a/views/games.ejs
+++ b/views/games.ejs
@@ -5,52 +5,44 @@ td.nowrap a { color: black; text-decoration: none; }
 
 <h2>Open</h2>
 <table class="wide">
-<tr><th>ID<th>Title<th>Scenario<th>Owner<th>Description<th>Created<th>Players<th>
+<tr><th>ID<th>Title<th>Scenario<th>Players<th>Description<th>Created<th>
 <% if (open_games.length > 0) { %>
 <% open_games.forEach((row) => { %>
 <tr>
 <td><%= row.game_id %>
 <td class="nowrap"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a>
 <td><%= row.scenario %>
-<td><%= row.owner_name %>
+<td><%= row.player_names || row.owner_name %>
 <td><%= row.description %>
 <td class="nowrap"><%= row.ctime %>
-<td><%= row.players.join(", ") %>
 <td><a href="/join/<%= row.game_id %>">Join</a>
 <% }); } else { %>
-<tr><td colspan="6">No open games.
+<tr><td colspan="7">No open games.
 <% } %>
 </table>
 
 <h2>Active</h2>
 <table class="wide">
-<tr><th>ID<th>Title<th>Scenario<th>Description<th>Changed<th>Players<th>Active<th>
+<tr><th>ID<th>Title<th>Scenario<th>Players<th>Description<th>Changed<th>Active<th>
 <% if (active_games.length > 0) { %>
 <% active_games.forEach((row) => { %>
 <tr>
 <td><%= row.game_id %>
 <td class="nowrap"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a>
 <td><%= row.scenario %>
+<td><%= row.player_names %>
 <td><%= row.description %>
 <td class="nowrap"><%= row.mtime %>
-<td><%= row.players.join(", ") %>
 <%
-	if (row.your_turn) {
-		%><td class="your_turn"><%= row.active %><%
+	if (row.is_your_turn) {
+		%><td class="is_your_turn"><%= row.active_role %><%
 	} else {
-		%><td><%= row.active %><%
-	}
-	let me = row.players.reduce((n,p) => n + (p === user.name ? 1 : 0), 0);
-	if (me == 1) {
-		%><td><a href="/play/<%= row.game_id %>">Play</a><%
-	} else if (me > 1) {
-		%><td><a href="/join/<%= row.game_id %>">Play</a><%
-	} else {
-		%><td><a href="/join/<%= row.game_id %>">View</a><%
+		%><td><%= row.active_role %><%
 	}
+	%><td><a href="/join/<%= row.game_id %>">Enter</a><%
 %>
 <% }); } else { %>
-<tr><td colspan="6">No active games.
+<tr><td colspan="8">No active games.
 <% } %>
 </table>
 
diff --git a/views/info.ejs b/views/info.ejs
index 55b8faa..02ce2dd 100644
--- a/views/info.ejs
+++ b/views/info.ejs
@@ -9,13 +9,13 @@ Read more about the game on
 
 <h2>Open Games</h2>
 <table class="wide">
-<tr><th>ID<th>Scenario<th>Owner<th>Description<th>Created<th>
+<tr><th>ID<th>Scenario<th>Players<th>Description<th>Created<th>
 <% if (open_games.length > 0) { %>
 <% open_games.forEach((row) => { %>
 <tr>
 <td><%= row.game_id %>
 <td><%= row.scenario %>
-<td><%= row.owner_name %>
+<td><%= row.player_names || row.owner_name %>
 <td><%= row.description %>
 <td class="nowrap"><%= row.ctime %>
 <td><a href="/join/<%= row.game_id %>">Join</a>
@@ -35,23 +35,16 @@ Read more about the game on
 <tr>
 <td><%= row.game_id %>
 <td><%= row.scenario %>
-<td><%= row.players.join(", ") %>
+<td><%= row.player_names %>
 <td><%= row.description %>
 <td class="nowrap"><%= row.mtime %>
 <%
-	if (row.your_turn) {
-		%><td class="your_turn"><%= row.active %><%
+	if (row.is_your_turn) {
+		%><td class="is_your_turn"><%= row.active_role %><%
 	} else {
-		%><td><%= row.active %><%
-	}
-	let me = row.players.reduce((n,p) => n + (p === user.name ? 1 : 0), 0);
-	if (me == 1) {
-		%><td><a href="/play/<%= row.game_id %>">Play</a><%
-	} else if (me > 1) {
-		%><td><a href="/join/<%= row.game_id %>">Play</a><%
-	} else {
-		%><td><a href="/join/<%= row.game_id %>">View</a><%
+		%><td><%= row.active_role %><%
 	}
+	%><td><a href="/join/<%= row.game_id %>">Enter</a><%
 %>
 <% }); %>
 </table>
@@ -65,7 +58,7 @@ Read more about the game on
 <tr>
 <td><%= row.game_id %>
 <td><%= row.scenario %>
-<td><%= row.players.join(", ") %>
+<td><%= row.player_names %>
 <td><%= row.description %>
 <td class="nowrap"><%= row.mtime %>
 <td><%= row.result %>
diff --git a/views/join.ejs b/views/join.ejs
index 46c1e56..1d9347d 100644
--- a/views/join.ejs
+++ b/views/join.ejs
@@ -9,7 +9,6 @@ let game = <%- JSON.stringify(game) %>;
 let roles = <%- JSON.stringify(roles) %>;
 let players = <%- JSON.stringify(players) %>;
 let user_id = <%- user.user_id %>;
-let solo = <%- solo %>;
 let ready = <%- ready %>;
 </script>
 <script src="/join.js"></script>
diff --git a/views/login.ejs b/views/login.ejs
index b4089fc..3e1dd43 100644
--- a/views/login.ejs
+++ b/views/login.ejs
@@ -4,7 +4,7 @@
 <% } else { %>
 <form action="/login" method="post">
 <p>
-<label for="username">Name: </label><br>
+<label for="username">Name or mail: </label><br>
 <input type="text" id="username" name="username" required>
 <p>
 <label for="password">Password: </label><br>
diff --git a/views/profile.ejs b/views/profile.ejs
index 02f6e72..7829870 100644
--- a/views/profile.ejs
+++ b/views/profile.ejs
@@ -36,7 +36,7 @@ Your mail address is <%= user.mail %>.
 <td><%= row.game_id %>
 <td class="nowrap"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a>
 <td><%= row.scenario %>
-<td><%= row.players.join(", ") %>
+<td><%= row.player_names %>
 <td><%= row.description %>
 <td class="nowrap"><%= row.ctime %>
 <td><a href="/join/<%= row.game_id %>">Join</a>
@@ -53,19 +53,19 @@ Your mail address is <%= user.mail %>.
 <td><%= row.game_id %>
 <td class="nowrap"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a>
 <td><%= row.scenario %>
-<td><%= row.players.join(", ") %>
+<td><%= row.player_names %>
 <td><%= row.description %>
 <td class="nowrap"><%= row.mtime %>
 <%
-	if (row.your_turn) {
-		%><td class="your_turn"><%= row.active %><%
+	if (row.is_your_turn) {
+		%><td class="is_your_turn"><%= row.active_role %><%
 	} else {
-		%><td><%= row.active %><%
+		%><td><%= row.active_role %><%
 	}
-	if (row.players.reduce((n,p) => n + (p === user.name ? 1 : 0), 0) == 1) {
-		%><td><a href="/play/<%= row.game_id %>">Play</a><%
+	if (row.is_shared) {
+		%><td><a href="/join/<%= row.game_id %>">Enter</a><%
 	} else {
-		%><td><a href="/join/<%= row.game_id %>">Play</a><%
+		%><td><a href="/play/<%= row.game_id %>">Play</a><%
 	}
 %>
 <% }); %>
@@ -81,7 +81,7 @@ Your mail address is <%= user.mail %>.
 <td><%= row.game_id %>
 <td class="nowrap"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a>
 <td><%= row.scenario %>
-<td><%= row.players.join(", ") %>
+<td><%= row.player_names %>
 <td><%= row.description %>
 <td class="nowrap"><%= row.mtime %>
 <td><%= row.result %>