27 files changed, 1715 insertions, 0 deletions

diff --git a/connect-better-sqlite3.js b/connect-better-sqlite3.js
new file mode 100644
index 0000000..4731897
--- /dev/null
+++ b/connect-better-sqlite3.js
@@ -0,0 +1,91 @@
+/*
+ * connect-better-sqlite3
+ *
+ * Copyright (c) 2010-2020 TJ Holowaychuk <tj@vision-media.ca>
+ * Copyright (c) 2011 tnantoka <bornneet@livedoor.com>
+ * Copyright (c) 2012 David Feinberg
+ * Copyright (c) 2021 Tor Andersson <tor@ccxvii.net>
+ *
+ * MIT Licensed
+ */
+
+module.exports = function (session) {
+	const SQLite = require('better-sqlite3');
+
+	function noop() {}
+	function now() { return Math.ceil(Date.now() / 1000); }
+	function seconds(date) { return Math.ceil(new Date(date).getTime() / 1000); }
+
+	class SQLiteStore extends session.Store {
+
+		constructor(options = {}) {
+			super(options);
+
+			let table = options.table || 'sessions';
+			let db_path = options.db || table;
+			if (db_path != ':memory:')
+				db_path = (options.dir || '.') + '/' + db_path;
+
+			let db = new SQLite(db_path, options.mode);
+			db.exec("CREATE TABLE IF NOT EXISTS "+table+" (sid PRIMARY KEY, expires INTEGER, sess TEXT)");
+			db.exec("DELETE FROM "+table+" WHERE ? > expires");
+			db.exec("VACUUM");
+
+			this.sql_destroy = db.prepare("DELETE FROM "+table+" WHERE sid = ?");
+			this.sql_get = db.prepare("SELECT sess FROM "+table+" WHERE sid = ? AND ? <= expires");
+			this.sql_set = db.prepare("INSERT OR REPLACE INTO "+table+" VALUES (?,?,?)");
+			this.sql_touch = db.prepare("UPDATE "+table+" SET expires = ? WHERE sid = ? AND ? <= expires");
+		}
+
+		destroy(sid, cb = noop) {
+			try {
+				this.sql_destroy.run(sid);
+				cb(null);
+			} catch (err) {
+				cb(err);
+			}
+		}
+
+		get(sid, cb = noop) {
+			try {
+				let sess = this.sql_get.get(sid, now());
+				if (sess)
+					return cb(null, JSON.parse(sess.sess));
+				return cb(null, null);
+			} catch (err) {
+				return cb(err, null);
+			}
+		}
+
+		set(sid, sess, cb = noop) {
+			try {
+				let expires;
+				if (sess && sess.cookie && sess.cookie.expires)
+					expires = seconds(sess.cookie.expires);
+				else
+					expires = now() + 86400;
+				this.sql_set.run(sid, expires, JSON.stringify(sess));
+				cb(null);
+			} catch (err) {
+				cb(err);
+			}
+		}
+
+		touch(sid, sess, cb = noop) {
+			try {
+				if (sess && sess.cookie && sess.cookie.expires) {
+					let expires = seconds(sess.cookie.expires);
+					this.sql_touch.run(expires, sid, now());
+					cb(null);
+				} else {
+					cb(null);
+				}
+			} catch (err) {
+				cb(err);
+			}
+		}
+
+	}
+
+	return SQLiteStore;
+}
diff --git a/package.json b/package.json
new file mode 100644
index 0000000..93c4049
--- /dev/null
+++ b/package.json
@@ -0,0 +1,19 @@
+{
+	"name": "rally-the-troops",
+	"version": "0.0.1",
+	"description": "Rally the Troops!",
+	"dependencies": {
+		"better-sqlite3": "^7.1.2",
+		"connect": "^3.7.0",
+		"connect-flash": "^0.1.1",
+		"cookie-parser": "^1.4.5",
+		"dotenv": "^8.2.0",
+		"ejs": "^3.1.5",
+		"express": "^4.17.1",
+		"express-session": "^1.17.1",
+		"passport": "^0.4.1",
+		"passport-local": "^1.0.0",
+		"passport.socketio": "^3.7.0",
+		"socket.io": "^3.0.4"
+	}
+}
diff --git a/public/images/rally-the-troops.png b/public/images/rally-the-troops.png
new file mode 100644
index 0000000..c792857
--- /dev/null
+++ b/public/images/rally-the-troops.png
diff --git a/public/images/rally-the-troops.svg b/public/images/rally-the-troops.svg
new file mode 100644
index 0000000..da7b407
--- /dev/null
+++ b/public/images/rally-the-troops.svg
@@ -0,0 +1 @@
+<svg style="height: 512px; width: 512px;" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><g class="" style="" transform="translate(0,0)"><path d="M462.9 19.12c-9.6 0-17.2 7.59-17.2 17.19 0 9.61 7.6 17.19 17.2 17.19s17.2-7.58 17.2-17.19c0-9.6-7.6-17.19-17.2-17.19zm-80.3 21.82c-160.3.8-218.1 217.46-362.93 96.26 3.25 36.8 88.43 78.4 88.43 78.4-26.03 20-34.78 24.7-71.99 25.5 104.09 86.7 338.69-99.8 408.39 40.1l-2.3-38.4-45.4-46.5 42.7.6-.6-10.2-50.7-32.2 48.4-7.2-.7-11.1-50-27.3 47.9-8.8-.6-10.69L381 66.66l50.5-5.85-.8-13.9c-17.1-4.2-33-6.05-48.1-5.97zm70.8 29.97l20.2 423.99 18.7-.9-20.2-423c-6.3 1.54-12.7 1.5-18.7-.1zM360 292.9l-43.6 70 21.3 25L322 493h18.9l15.2-102.3 28-20.2c-8.1-25.9-16.1-51.8-24.1-77.6zm-156.7 17.9l-28.8 69.8 20.5 20.2 2.2 92.2h18.7l-2.2-93 19.6-19.9-30-69.3zm-158.16 5l-16.4 61.9 17.65 13.2L61.24 493h18.87L64.89 388.3l13.22-17.6-32.93-54.9zm85.96 7.4l-28.2 57.5 15.1 17-6.7 95.3H130l6.8-95.3 15.9-14.2-21.6-60.3zM268 355.5l-19.5 68.4 19.4 15.2 5.8 53.9h18.9l-5.9-54.3 16.8-21.6c-11.9-20.5-23.7-41-35.5-61.6zm143.6.1l-18.9 68.6 20.3 15.5 6.2 53.3H438l-6.4-55.2 16-20.9z" fill="#000000" fill-opacity="1"></path></g></svg>
\ No newline at end of file
diff --git a/public/style.css b/public/style.css
new file mode 100644
index 0000000..dfd7754
--- /dev/null
+++ b/public/style.css
@@ -0,0 +1,58 @@
+button, select {
+	font-family: "Source Sans", "Verdana", "Dingbats", "Noto Emoji", sans-serif;
+	font-size: 16px;
+}
+html, input {
+	font-family: "Source Serif", "Georgia", "Dingbats", "Noto Emoji", serif;
+	font-size: 16px;
+}
+html, body { margin: 0; }
+h1 { margin-left: -2px; }
+h2 { margin-left: -1px; }
+.header {
+	display: flex;
+	align-items: center;
+	justify-content: space-between;
+	border-bottom: 2px solid brown;
+	padding-right: 1em;
+}
+.header img { display: block; margin: 4px 0 -2px 2px; }
+.header span { margin: 0 1em; }
+.header a { color: black; }
+.main { margin: 2em; }
+.main p { max-width: 50rem; }
+.main hr { max-width: 50rem; margin-right: auto; margin-left: 0; }
+.main hr { border: none; border-top: 2px dotted brown; }
+.main hr + p { font-style: italic; }
+table.wide { min-width: 50rem; }
+.error { color: brown; font-style: italic; white-space: pre-wrap; }
+.logo { float: left; margin: 0 20px 5px 0; box-shadow: 2px 2px 4px 0px rgba(0,0,0,0.5); }
+table { border-collapse: collapse; }
+th { text-align: left; background-color: gainsboro; }
+th, td { border: 1px solid black; }
+th, td { padding: 3px 1ex; }
+label { user-select: none; }
+button, input, select { font-size: 1rem; margin: 5px 0; }
+input[type="text"], input[type="password"] { padding: 5px; }
+select { padding-right: 20px; }
+form { display: inline; }
+.nowrap { white-space: nowrap; }
+.your_turn { background-color: lemonchiffon; }
+button, select {
+	margin: 5px 10px 5px 0;
+	padding: 1px 10px;
+	background-color: gainsboro;
+}
+button:disabled {
+	color: gray;
+	border: 2px solid gainsboro;
+	outline: 1px solid gray;
+}
+button:enabled, select {
+	border: 2px outset white;
+	outline: 1px solid black;
+}
+button:enabled:active:hover, select:active {
+	border: 2px inset white;
+	padding: 2px 9px 0px 11px;
+}
diff --git a/server.js b/server.js
new file mode 100644
index 0000000..f3af3d7
--- /dev/null
+++ b/server.js
@@ -0,0 +1,943 @@
+"use strict";
+
+const fs = require('fs');
+const express = require('express');
+const express_session = require('express-session');
+const passport = require('passport');
+const passport_local = require('passport-local');
+const passport_socket = require('passport.socketio');
+const body_parser = require('body-parser');
+const connect_flash = require('connect-flash');
+const crypto = require('crypto');
+const sqlite3 = require('better-sqlite3');
+const SQLiteStore = require('./connect-better-sqlite3')(express_session);
+
+require('dotenv').config();
+
+const SESSION_SECRET = "Caesar has a big head!";
+
+const MAX_OPEN_GAMES = 3;
+
+let sessionStore = 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); },
+};
+
+app.disable('etag');
+app.set('view engine', 'ejs');
+app.use(body_parser.urlencoded({extended:false}));
+app.use(express_session({
+	secret: SESSION_SECRET,
+	resave: false,
+	saveUninitialized: true,
+	store: sessionStore,
+	cookie: { maxAge: 7 * 24 * 60 * 60 * 1000 }
+}));
+app.use(connect_flash());
+
+io.use(passport_socket.authorize({
+	key: 'connect.sid',
+	secret: SESSION_SECRET,
+	store: sessionStore,
+}));
+
+const is_immutable = /\.(svg|png|jpg|jpeg|woff2)$/;
+
+function setHeaders(res, path) {
+        if (is_immutable.test(path))
+                res.set("Cache-Control", "public, max-age=86400, immutable");
+}
+
+app.use(express.static('public', { setHeaders: setHeaders }));
+
+function LOG(req, ...msg) {
+	let name;
+	if (req.isAuthenticated())
+		name = req.user.mail;
+	else
+		name = "guest";
+	let time = new Date().toISOString().substring(0,19).replace("T", " ");
+	console.log(time, req.connection.remoteAddress, name, ...msg);
+}
+
+function SLOG(socket, ...msg) {
+	let name = socket.request.user.mail;
+	let time = new Date().toISOString().substring(0,19).replace("T", " ");
+	console.log(time, socket.request.connection.remoteAddress, name,
+		socket.id, socket.title_id, socket.game_id, socket.role, ...msg);
+}
+
+function human_date(time) {
+	var date = time ? new Date(time + " UTC") : new Date(0);
+	var seconds = (Date.now() - date.getTime()) / 1000;
+	var days = Math.floor(seconds / 86400);
+	if (days == 0) {
+		if (seconds < 60) return "now";
+		if (seconds < 120) return "1 minute ago";
+		if (seconds < 3600) return Math.floor(seconds / 60) + " minutes ago";
+		if (seconds < 7200) return "1 hour ago";
+		if (seconds < 86400) return Math.floor(seconds / 3600) + " hours ago";
+	}
+	if (days == 1) return "Yesterday";
+	if (days < 14) return days + " days ago";
+	if (days < 31) return Math.ceil(days / 7) + " weeks ago";
+	return date.toISOString().substring(0,10);
+}
+
+function humanize(rows) {
+	for (let row of rows) {
+		row.ctime = human_date(row.ctime);
+		row.mtime = human_date(row.mtime);
+	}
+}
+
+function is_email(email) {
+	return email.match(/^[a-zA-Z0-9.!#$%&'*+/=?^_`{|}~-]+@[a-zA-Z0-9-]+(?:\.[a-zA-Z0-9-]+)*$/);
+}
+
+function clean_user_name(name) {
+	name = name.replace(/^ */,'').replace(/ *$/,'').replace(/  */g,' ');
+	if (name.length > 50)
+		name = name.substring(0, 50);
+	return name;
+}
+
+function hash_password(password, salt) {
+	let hash = crypto.createHash('sha256');
+	hash.update(password);
+	hash.update(salt);
+	return hash.digest('hex');
+}
+
+function get_avatar(mail) {
+	if (!mail)
+		mail = "foo@example.com";
+	let digest = crypto.createHash('md5').update(mail.trim().toLowerCase()).digest('hex');
+	return '//www.gravatar.com/avatar/' + digest + '?d=mp';
+}
+
+/*
+ * USER PROFILES
+ */
+
+const sql_blacklist_ip = db.prepare("SELECT COUNT(*) FROM blacklist_ip WHERE ip = ?").raw();
+const sql_blacklist_mail = db.prepare("SELECT COUNT(*) AS count FROM blacklist_mail WHERE ? LIKE mail").raw();
+
+function is_blacklisted(ip, mail) {
+	if (sql_blacklist_ip.get(ip)[0] != 0)
+		return true;
+	if (sql_blacklist_mail.get(mail)[0] != 0)
+		return true;
+	return false;
+}
+
+const sql_deserialize_user = db.prepare("SELECT user_id, name, mail FROM users WHERE user_id = ?");
+const sql_update_last_seen = db.prepare("UPDATE users SET aip = ?, atime = datetime('now') WHERE user_id = ?");
+const sql_login_select = db.prepare("SELECT user_id, name, mail, password, salt FROM users WHERE name = ? OR mail = ?");
+
+passport.serializeUser(function (user, done) {
+	return done(null, user.user_id);
+});
+
+passport.deserializeUser(function (user_id, done) {
+	try {
+		let row = sql_deserialize_user.get(user_id);
+		if (!row)
+			return done(null, false);
+		return done(null, row);
+	} catch (err) {
+		console.log(err);
+		return done(null, false);
+	}
+});
+
+function local_login(req, name_or_mail, password, done) {
+	try {
+		if (!is_email(name_or_mail))
+			name_or_mail = clean_user_name(name_or_mail);
+		LOG(req, "POST /login", name_or_mail);
+		let row = sql_login_select.get(name_or_mail, name_or_mail);
+		if (!row)
+			return setTimeout(() => done(null, false, req.flash('message', "User not found.")), 1000);
+		if (is_blacklisted(req.connection.remoteAddress, row.mail))
+			return setTimeout(() => done(null, false, req.flash('message', "Sorry, but this IP or account has been banned.")), 1000);
+		let hash = hash_password(password, row.salt);
+		if (hash != row.password)
+			return setTimeout(() => done(null, false, req.flash('message', "Wrong password.")), 1000);
+		sql_update_last_seen.run(req.connection.remoteAddress, row.user_id);
+		done(null, row);
+	} catch (err) {
+		done(null, false, req.flash('message', err.toString()));
+	}
+}
+
+const sql_signup_check = db.prepare("SELECT user_id, name FROM users WHERE name = ? OR mail = ?");
+const sql_signup_insert = db.prepare("INSERT INTO users (name, mail, password, salt, ctime, cip, atime, aip) VALUES (?,?,?,?,datetime('now'),?,datetime('now'),?)");
+const sql_signup_login = db.prepare("SELECT user_id, name FROM users WHERE name = ? AND password = ?");
+
+function local_signup(req, name, password, done) {
+	try {
+		let mail = req.body.mail;
+		name = clean_user_name(name);
+		LOG(req, "POST /signup", name, mail);
+		if (is_blacklisted(req.connection.remoteAddress, mail))
+			return setTimeout(() => done(null, false, req.flash('message', "Sorry, but this IP or account has been banned.")), 1000);
+		if (password.length < 4)
+			return done(null, false, req.flash('message', "Password is too short!"));
+		if (password.length > 100)
+			return done(null, false, req.flash('message', "Password is too long!"));
+		// TODO: actual verification if process.env.VERIFY_EMAIL
+		if (!is_email(mail))
+			return done(null, false, req.flash('message', "Invalid mail address!"));
+		let row = sql_signup_check.get(name, mail);
+		if (row)
+			return done(null, false, req.flash('message', "User name or mail is already taken."));
+		let salt = crypto.randomBytes(32).toString('hex');
+		let hash = hash_password(password, salt);
+		let ip = req.connection.remoteAddress;
+		sql_signup_insert.run(name, mail, hash, salt, ip, ip);
+		row = sql_signup_login.get(name, hash);
+		done(null, row);
+	} catch (err) {
+		done(null, false, req.flash('message', err.toString()));
+	}
+}
+
+passport.use('local-login', new passport_local.Strategy({ passReqToCallback: true }, local_login));
+passport.use('local-signup', new passport_local.Strategy({ passReqToCallback: true }, local_signup));
+
+app.use(passport.initialize());
+app.use(passport.session());
+
+function update_last_seen(req) {
+	sql_update_last_seen.run(req.connection.remoteAddress, req.user.user_id);
+}
+
+function must_be_logged_in(req, res, next) {
+	if (!req.isAuthenticated())
+		return res.redirect('/login');
+	if (sql_blacklist_ip.get(req.connection.remoteAddress)[0] != 0)
+		return res.redirect('/banned');
+	if (sql_blacklist_mail.get(req.user.mail)[0] != 0)
+		return res.redirect('/banned');
+	update_last_seen(req);
+	return next();
+}
+
+app.get('/favicon.ico', function (req, res) {
+	res.status(204).send();
+});
+
+app.get('/about', function (req, res) {
+	res.render('about.ejs', { user: req.user });
+});
+
+app.get('/logout', function (req, res) {
+	LOG(req, "GET /logout");
+	req.logout();
+	res.redirect('/login');
+});
+
+app.get('/banned', function (req, res) {
+	LOG(req, "GET /banned");
+	res.render('banned.ejs', { user: req.user, message: req.flash('message') });
+});
+
+app.get('/login', function (req, res) {
+	LOG(req, "GET /login");
+	res.render('login.ejs', { user: req.user, message: req.flash('message') });
+});
+
+app.get('/signup', function (req, res) {
+	LOG(req, "GET /signup");
+	res.render('signup.ejs', { user: req.user, message: req.flash('message') });
+});
+
+app.post('/login',
+	passport.authenticate('local-login', {
+		successRedirect: '/',
+		failureRedirect: '/login',
+		failureFlash: true
+	})
+);
+
+app.post('/signup',
+	passport.authenticate('local-signup', {
+		successRedirect: '/',
+		failureRedirect: '/signup',
+		failureFlash: true
+	})
+);
+
+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 });
+});
+
+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') });
+});
+
+app.post('/change_password', must_be_logged_in, function (req, res) {
+	try {
+		let name = clean_user_name(req.user.name);
+		let password = req.body.password;
+		let newpass = req.body.newpass;
+		LOG(req, "POST /change_password", name);
+		if (newpass.length < 4) {
+			req.flash('message', "Password is too short!");
+			return res.redirect('/change_password');
+		}
+		let salt_row = db.prepare("SELECT salt FROM users WHERE name = ?").get(name);
+		if (!salt_row) {
+			req.flash('message', "User not found.");
+			return res.redirect('/change_password');
+		}
+		let salt = salt_row.salt;
+		let hash = hash_password(password, salt);
+		let user_row = db.prepare("SELECT user_id, name FROM users WHERE name = ? AND password = ?").get(name, hash);
+		if (!user_row) {
+			req.flash('message', "Wrong password.");
+			return res.redirect('/change_password');
+		}
+		hash = hash_password(newpass, salt);
+		db.prepare("UPDATE users SET password = ? WHERE user_id = ?").run(hash, user_row.user_id);
+		return res.redirect('/profile');
+	} catch (err) {
+		console.log(err);
+		req.flash('message', err.message);
+		return res.redirect('/change_password');
+	}
+});
+
+/*
+ * GAME LOBBY
+ */
+
+let RULES = {};
+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");
+	} catch (err) {
+		console.log(err);
+	}
+}
+
+const QUERY_LIST_ONE_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.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_PUBLIC_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
+	FROM games
+	JOIN users ON games.owner = users.user_id
+	WHERE title_id = ? AND private = 0
+	ORDER BY status ASC, mtime DESC
+`);
+
+const QUERY_LIST_USER_GAMES = 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 = ?
+	ORDER BY status ASC, mtime DESC
+`);
+
+const QUERY_LIST_ALL_GAMES = db.prepare(`
+	SELECT
+		games.game_id,
+		games.title_id AS title_id,
+		titles.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.result,
+		games.active,
+		games.private
+	FROM games
+	JOIN users ON games.owner = users.user_id
+	LEFT JOIN titles ON games.title_id = titles.title_id
+	ORDER BY status ASC, mtime DESC
+`);
+
+const QUERY_PLAYERS = db.prepare(`
+	SELECT
+		players.game_id,
+		players.user_id,
+		players.role,
+		users.name
+	FROM players
+	JOIN users ON players.user_id = users.user_id
+	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 * FROM roles WHERE title_id = ?");
+const QUERY_GAME_OWNER = db.prepare("SELECT * FROM games WHERE game_id = ? AND owner = ?");
+const QUERY_TITLE_FROM_GAME = db.prepare("SELECT title_id FROM games WHERE game_id = ?");
+const QUERY_ROLE_FROM_GAME_AND_USER = db.prepare("SELECT role FROM players WHERE game_id = ? AND user_id = ?");
+
+const QUERY_JOIN_GAME = db.prepare("INSERT INTO players (user_id, game_id, role) VALUES (?,?,?)");
+const QUERY_PART_GAME = db.prepare("DELETE FROM players WHERE game_id = ? AND user_id = ? AND role = ?");
+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,ctime,mtime,description,status,state,chat)
+	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_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_DELETE_GAME = db.prepare("DELETE FROM games WHERE game_id = ?");
+
+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_USER_GAMES.all(req.user.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);
+	res.set("Cache-Control", "no-store");
+	res.render('profile.ejs', { user: req.user, avatar: avatar,
+		open_games: open_games,
+		active_games: active_games,
+		finished_games: finished_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;
+	let title = QUERY_TITLE.get(title_id);
+	if (!title) {
+		req.flash('message', 'That title does not exist.');
+		return res.redirect('/');
+	}
+	if (req.isAuthenticated()) {
+		let games = QUERY_LIST_PUBLIC_GAMES.all(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,
+			active_games: active_games,
+			finished_games: finished_games,
+			message: req.flash('message')
+		});
+	} else {
+		res.set("Cache-Control", "no-store");
+		res.render('info.ejs', { user: req.user, title: title,
+			open_games: [],
+			active_games: [],
+			finished_games: [],
+			message: req.flash('message')
+		});
+	}
+});
+
+app.get('/create/:title_id', must_be_logged_in, function (req, res) {
+	LOG(req, "GET /create/" + req.params.title_id);
+	let title_id = req.params.title_id;
+	let title = QUERY_TITLE.get(title_id);
+	if (!title) {
+		req.flash('message', 'That title does not exist.');
+		return res.redirect('/');
+	}
+	res.render('create.ejs', { user: req.user, message: req.flash('message'), title: title, scenarios: RULES[title_id].scenarios });
+});
+
+app.post('/create/:title_id', must_be_logged_in, function (req, res) {
+	let title_id = req.params.title_id;
+	let descr = req.body.description;
+	let priv = req.body.private == 'private';
+	let scenario = req.body.scenario;
+	let user_id = req.user.user_id;
+	LOG(req, "POST /create/" + req.params.title_id, scenario, priv, JSON.stringify(descr));
+	try {
+		let count = QUERY_COUNT_OPEN_GAMES.get(user_id);
+		if (count >= MAX_OPEN_GAMES) {
+			req.flash('message', "You have too many open games!");
+			return res.redirect('/create/'+title_id);
+		}
+		if (!(title_id in RULES)) {
+			req.flash('message', "That title doesn't exist.");
+			return res.redirect('/');
+		}
+		if (!RULES[title_id].scenarios.includes(scenario)) {
+			req.flash('message', "That scenario doesn't exist.");
+			return res.redirect('/create/'+title_id);
+		}
+		let info = QUERY_CREATE_GAME.run(user_id, title_id, scenario, priv ? 1 : 0, descr);
+		res.redirect('/join/'+info.lastInsertRowid);
+	} catch (err) {
+		req.flash('message', err.toString());
+		return res.redirect('/create/'+title_id);
+	}
+});
+
+app.get('/delete/:game_id', must_be_logged_in, function (req, res) {
+	let game_id = req.params.game_id;
+	LOG(req, "GET /delete/" + game_id);
+	try {
+		let game = QUERY_GAME_OWNER.get(game_id, req.user.user_id);
+		if (!game) {
+			req.flash('message', "Only the game owner can delete the game!");
+			return res.redirect('/join/'+game_id);
+		}
+		QUERY_DELETE_GAME.run(game_id);
+		res.redirect('/info/'+game.title_id);
+	} catch (err) {
+		req.flash('message', err.toString());
+		return res.redirect('/join/'+game_id);
+	}
+});
+
+app.get('/join/:game_id', must_be_logged_in, function (req, res) {
+	LOG(req, "GET /join/" + req.params.game_id);
+	let game_id = req.params.game_id | 0;
+	let game = QUERY_LIST_ONE_GAME.get(game_id);
+	if (!game) {
+		req.flash('message', "That game doesn't exist.");
+		return res.redirect('/');
+	}
+	let roles = QUERY_ROLES.all(game.title_id);
+	let players = QUERY_PLAYERS.all(game_id);
+	res.set("Cache-Control", "no-store");
+	res.render('join.ejs', {
+		user: req.user,
+		game: game,
+		roles: roles,
+		players: players,
+		solo: players.every(p => p.user_id == req.user.user_id),
+		message: req.flash('message')
+	});
+});
+
+app.get('/join/:game_id/:role', must_be_logged_in, function (req, res) {
+	LOG(req, "GET /join/" + req.params.game_id + "/" + req.params.role);
+	let game_id = req.params.game_id | 0;
+	let role = req.params.role;
+	try {
+		QUERY_JOIN_GAME.run(req.user.user_id, game_id, role);
+		return res.redirect('/join/'+game_id);
+	} catch (err) {
+		req.flash('message', err.toString());
+		return res.redirect('/join/'+game_id);
+	}
+});
+
+app.get('/part/:game_id/:part_id/:role', must_be_logged_in, function (req, res) {
+	LOG(req, "GET /part/" + req.params.game_id + "/" + req.params.part_id + "/" + req.params.role);
+	let game_id = req.params.game_id | 0;
+	let part_id = req.params.part_id | 0;
+	let role = req.params.role;
+	try {
+		QUERY_PART_GAME.run(game_id, part_id, role);
+		return res.redirect('/join/'+game_id);
+	} catch (err) {
+		req.flash('message', err.toString());
+		return res.redirect('/join/'+game_id);
+	}
+});
+
+app.get('/start/:game_id', must_be_logged_in, function (req, res) {
+	LOG(req, "GET /start/" + req.params.game_id);
+	let game_id = req.params.game_id | 0;
+	try {
+		let game = QUERY_GAME_OWNER.get(game_id, req.user.user_id);
+		if (!game) {
+			req.flash('message', "Only the game owner can start the game!");
+			return res.redirect('/join/'+game_id);
+		}
+		if (game.status != 0) {
+			req.flash('message', "The game is already started!");
+			return res.redirect('/join/'+game_id);
+		}
+		let players = QUERY_PLAYERS.all(game_id);
+		let state = RULES[game.title_id].setup(game.scenario, players);
+		QUERY_START_GAME.run(JSON.stringify(state), state.active, game_id);
+		let is_solo = players.every(p => p.user_id == players[0].user_id);
+		if (is_solo)
+			QUERY_UPDATE_GAME_SET_PRIVATE.run(game_id);
+		return res.redirect('/join/'+game_id);
+	} catch (err) {
+		req.flash('message', err.toString());
+		return res.redirect('/join/'+game_id);
+	}
+});
+
+app.get('/play/:game_id/:role', must_be_logged_in, function (req, res) {
+	LOG(req, "GET /play/" + req.params.game_id + "/" + req.params.role);
+	let game_id = req.params.game_id | 0;
+	let role = req.params.role;
+	try {
+		let title = QUERY_TITLE_FROM_GAME.get(game_id);
+		if (!title)
+			return res.redirect('/join/'+game_id);
+		res.redirect('/'+title.title_id+'/play.html?game='+game_id+'&role='+role);
+	} catch (err) {
+		req.flash('message', err.toString());
+		return res.redirect('/join/'+game_id);
+	}
+});
+
+app.get('/play/:game_id', must_be_logged_in, function (req, res) {
+	LOG(req, "GET /play/" + req.params.game_id);
+	let game_id = req.params.game_id | 0;
+	let user_id = req.user.user_id | 0;
+	try {
+		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.role);
+	} catch (err) {
+		req.flash('message', err.toString());
+		return res.redirect('/join/'+game_id);
+	}
+});
+
+/*
+ * GAME PLAYING
+ */
+
+const QUERY_SELECT_CHAT = db.prepare("SELECT chat FROM games WHERE game_id = ?");
+const QUERY_UPDATE_CHAT = db.prepare("UPDATE games SET chat = ? WHERE game_id = ?");
+const QUERY_SELECT_GAME_STATE = db.prepare("SELECT state FROM games WHERE game_id = ?");
+const QUERY_UPDATE_GAME_STATE = db.prepare("UPDATE games SET state = ?, active = ?, status = ?, result = ?, mtime = datetime('now') WHERE game_id = ?");
+const QUERY_CONNECT_GAME = db.prepare("SELECT title_id, state FROM games WHERE title_id = ? AND game_id = ?");
+const QUERY_RESTART_GAME = db.prepare("UPDATE games SET state = ?, mtime = datetime('now') WHERE game_id = ?");
+
+let clients = {};
+
+function send_state(socket, state) {
+	try {
+		let view = socket.rules.view(state, socket.role);
+		if (socket.log_length < view.log.length)
+			view.log_start = socket.log_length;
+		else
+			view.log_start = view.log.length;
+		socket.log_length = view.log.length;
+		view.log = view.log.slice(view.log_start);
+		socket.emit('state', view);
+	} catch (err) {
+		console.log(err);
+		return socket.emit('error', err.toString());
+	}
+}
+
+function get_game_state(game_id) {
+	let row = QUERY_SELECT_GAME_STATE.get(game_id);
+	if (!row)
+		throw new Error("No game with that ID");
+	return JSON.parse(row.state);
+}
+
+function put_game_state(game_id, state) {
+	let status = 1;
+	let result = null;
+	if (state.state == 'game_over') {
+		status = 2;
+		result = state.result;
+	}
+	QUERY_UPDATE_GAME_STATE.run(JSON.stringify(state), state.active, status, result, game_id);
+	for (let other of clients[game_id])
+		send_state(other, state);
+}
+
+function on_action(socket, action, arg) {
+	SLOG(socket, "--> ACTION", action, arg);
+	try {
+		let state = get_game_state(socket.game_id);
+		socket.rules.action(state, socket.role, action, arg);
+		put_game_state(socket.game_id, state);
+	} catch (err) {
+		console.log(err);
+		return socket.emit('error', err.toString());
+	}
+}
+
+function on_resign(socket) {
+	SLOG(socket, "--> RESIGN");
+	try {
+		let state = get_game_state(socket.game_id);
+		socket.rules.resign(state, socket.role);
+		put_game_state(socket.game_id, state);
+	} catch (err) {
+		console.log(err);
+		return socket.emit('error', err.toString());
+	}
+}
+
+function send_chat(socket, chat) {
+	if (chat && socket.chat_length < chat.length) {
+		SLOG(socket, "<-- CHAT LOG", socket.chat_length, "..", chat.length);
+		socket.emit('chat', socket.chat_length, chat.slice(socket.chat_length));
+		socket.chat_length = chat.length;
+	}
+}
+
+function on_getchat(socket, old_len) {
+	try {
+		socket.chat_length = old_len;
+		let row = QUERY_SELECT_CHAT.get(socket.game_id);
+		if (!row)
+			return socket.emit('error', "No game with that ID.");
+		let chat = JSON.parse(row.chat);
+		if (!chat)
+			chat = [];
+		send_chat(socket, chat);
+	} catch (err) {
+		console.log(err);
+		return socket.emit('error', err.toString());
+	}
+}
+
+function on_chat(socket, message) {
+	message = message.substring(0,4096);
+	SLOG(socket, "--> CHAT");
+	try {
+		let row = QUERY_SELECT_CHAT.get(socket.game_id);
+		if (!row)
+			return socket.emit('error', "No game with that ID.");
+		let chat = JSON.parse(row.chat);
+		if (!chat)
+			chat = [];
+		chat.push([new Date(), socket.user_name, message]);
+		QUERY_UPDATE_CHAT.run(JSON.stringify(chat), socket.game_id);
+		for (let other of clients[socket.game_id])
+			send_chat(other, chat);
+	} catch (err) {
+		console.log(err);
+		return socket.emit('error', err.toString());
+	}
+}
+
+function on_debug(socket) {
+	SLOG(socket, "<-- DEBUG");
+	try {
+		let row = QUERY_SELECT_GAME_STATE.get(socket.game_id);
+		if (!row)
+			return socket.emit('error', "No game with that ID.");
+		socket.emit('debug', row.state);
+	} catch (err) {
+		console.log(err);
+		return socket.emit('error', err.toString());
+	}
+}
+
+function on_save(socket) {
+	SLOG(socket, "<-- SAVE");
+	try {
+		let row = QUERY_SELECT_GAME_STATE.get(socket.game_id);
+		if (!row)
+			return socket.emit('error', "No game with that ID.");
+		socket.emit('save', row.state);
+	} catch (err) {
+		console.log(err);
+		return socket.emit('error', err.toString());
+	}
+}
+
+function on_restore(socket, state_text) {
+	SLOG(socket, '--> RESTORE', state_text);
+	try {
+		let state = JSON.parse(state_text);
+		QUERY_UPDATE_GAME_STATE.run(state_text, state.active, 1, null, socket.game_id);
+		for (let other of clients[socket.game_id])
+			send_state(other, state);
+	} catch (err) {
+		console.log(err);
+		return socket.emit('error', err.toString());
+	}
+}
+
+function broadcast_presence(game_id) {
+	let presence = {};
+	for (let socket of clients[game_id])
+		presence[socket.role] = true;
+	for (let socket of clients[game_id])
+		socket.emit('presence', presence);
+}
+
+io.on('connection', (socket) => {
+	socket.title_id = socket.handshake.query.title;
+	socket.game_id = socket.handshake.query.game | 0;
+	socket.user_id = socket.request.user.user_id | 0;
+	socket.user_name = socket.request.user.name;
+	socket.role = socket.handshake.query.role;
+	socket.log_length = 0;
+	socket.chat_length = 0;
+	socket.rules = RULES[socket.title_id];
+
+	SLOG(socket, "CONNECT");
+
+	try {
+		let game = QUERY_CONNECT_GAME.get(socket.title_id, socket.game_id);
+		if (!game)
+			return socket.emit('error', "That game does not exist.");
+
+		let players = QUERY_PLAYERS.all(socket.game_id);
+
+		if (socket.role != "Observer") {
+			let me;
+			if (socket.role && socket.role != 'undefined' && socket.role != 'null') {
+				me = players.find(p => p.user_id == socket.user_id && p.role == socket.role);
+				if (!me) {
+					socket.role = "Observer";
+					return socket.emit('error', "You aren't assigned that role!");
+				}
+			} else {
+				me = players.find(p => p.user_id == socket.user_id);
+				socket.role = me ? me.role : "Observer";
+			}
+		}
+
+		socket.emit('roles', socket.role, players);
+
+		if (clients[socket.game_id])
+			clients[socket.game_id].push(socket);
+		else
+			clients[socket.game_id] = [ socket ];
+
+		socket.on('disconnect', () => {
+			SLOG(socket, "DISCONNECT");
+			clients[socket.game_id].splice(clients[socket.game_id].indexOf(socket), 1);
+			if (socket.role != "Observer")
+				broadcast_presence(socket.game_id);
+		});
+
+		if (socket.role != "Observer") {
+			socket.on('action', (action, arg) => on_action(socket, action, arg));
+			socket.on('resign', () => on_resign(socket));
+			socket.on('getchat', (old_len) => on_getchat(socket, old_len));
+			socket.on('chat', (message) => on_chat(socket, message));
+
+			socket.on('debug', () => on_debug(socket));
+			socket.on('save', () => on_save(socket));
+			socket.on('restore', (state) => on_restore(socket, state));
+			socket.on('restart', (scenario) => {
+				let state = socket.rules.setup(scenario, players);
+				for (let other of clients[socket.game_id]) {
+					other.log_length = 0;
+					send_state(other, state);
+				}
+				let state_text = JSON.stringify(state);
+				QUERY_RESTART_GAME.run(state_text, socket.game_id);
+			});
+		}
+
+		broadcast_presence(socket.game_id);
+
+		send_state(socket, JSON.parse(game.state));
+
+	} catch (err) {
+		console.log(err);
+		socket.emit('error', err.message);
+	}
+});
+
+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); });
diff --git a/tools/editgame.sh b/tools/editgame.sh
new file mode 100644
index 0000000..7f21fcb
--- /dev/null
+++ b/tools/editgame.sh
@@ -0,0 +1,8 @@
+#!/bin/bash
+if [ -n "$1" -a -n "$VISUAL" ]
+then
+	sqlite3 db "update games set state=edit(state) where game_id = $1"
+else
+	echo "usage: bash tools/editgame.sh GAME"
+	echo "note: \$VISUAL must be set to your preferred editor"
+fi
diff --git a/tools/makecert.sh b/tools/makecert.sh
new file mode 100644
index 0000000..94d8bb7
--- /dev/null
+++ b/tools/makecert.sh
@@ -0,0 +1,6 @@
+#!/bin/bash
+#
+# Create a simple self-signed SSL certificate.
+#
+
+openssl req -nodes -new -x509 -keyout key.pem -out cert.pem
diff --git a/tools/purge.sh b/tools/purge.sh
new file mode 100644
index 0000000..8c1ea7e
--- /dev/null
+++ b/tools/purge.sh
@@ -0,0 +1,6 @@
+#!/bin/bash
+
+# Clean out stale games from the database.
+
+sqlite3 db "DELETE FROM games WHERE status = 0 AND mtime < datetime('now', '-7 days')"
+sqlite3 db "UPDATE games SET status = 3 WHERE status = 1 AND mtime < datetime('now', '-28 days')"
diff --git a/tools/readgame.sh b/tools/readgame.sh
new file mode 100644
index 0000000..5bf93de
--- /dev/null
+++ b/tools/readgame.sh
@@ -0,0 +1,10 @@
+#!/bin/bash
+if [ -n "$1" -a -n "$2" ]
+then
+	sqlite3 db "select writefile('$2',state) from games where game_id = $1"
+elif [ -n "$1" ]
+then
+	sqlite3 db "select state from games where game_id = $1"
+else
+	echo "usage: bash tools/readgame.sh GAME [ state.json ]"
+fi
diff --git a/tools/sql/schema.txt b/tools/sql/schema.txt
new file mode 100644
index 0000000..97e8b25
--- /dev/null
+++ b/tools/sql/schema.txt
@@ -0,0 +1,86 @@
+CREATE TABLE IF NOT EXISTS users (
+	user_id INTEGER PRIMARY KEY,
+	name TEXT UNIQUE,
+	mail TEXT UNIQUE,
+	password TEXT,
+	salt TEXT,
+	ctime TIMESTAMP,
+	cip TEXT,
+	atime TIMESTAMP,
+	aip TEXT
+);
+
+CREATE TABLE IF NOT EXISTS blacklist_ip ( ip TEXT PRIMARY KEY );
+CREATE TABLE IF NOT EXISTS blacklist_mail ( mail TEXT PRIMARY KEY );
+
+CREATE TABLE IF NOT EXISTS titles (
+	title_id TEXT UNIQUE,
+	title_name TEXT,
+	bgg INTEGER
+);
+
+CREATE TABLE IF NOT EXISTS roles (
+	title_id TEXT,
+	role TEXT,
+	UNIQUE ( title_id, role )
+);
+
+CREATE TABLE IF NOT EXISTS games (
+	game_id INTEGER PRIMARY KEY,
+	title_id TEXT,
+	scenario TEXT,
+	owner INTEGER,
+	private BOOLEAN,
+	ctime TIMESTAMP,
+	mtime TIMESTAMP,
+	description TEXT,
+	status INTEGER,
+	active TEXT,
+	result TEXT,
+	state TEXT,
+	chat TEXT
+);
+
+CREATE TABLE IF NOT EXISTS players (
+	user_id INTEGER,
+	game_id INTEGER,
+	role TEXT,
+	UNIQUE ( game_id, user_id, role ),
+	UNIQUE ( game_id, role )
+);
+
+DROP TRIGGER IF EXISTS no_part_on_active_game;
+CREATE TRIGGER no_part_on_active_game BEFORE DELETE ON players
+BEGIN
+	SELECT CASE
+	WHEN ( SELECT status FROM games WHERE game_id = old.game_id ) > 0
+	THEN RAISE(ABORT, "Cannot remove players from started games.")
+	END;
+END;
+
+DROP TRIGGER IF EXISTS no_join_on_active_game;
+CREATE TRIGGER no_join_on_active_game BEFORE INSERT ON players
+BEGIN
+	SELECT CASE
+	WHEN ( SELECT status FROM games WHERE game_id = new.game_id ) > 0
+	THEN RAISE(ABORT, "Cannot add players to started games.")
+	END;
+END;
+
+DROP TRIGGER IF EXISTS must_be_valid_role;
+CREATE TRIGGER must_be_valid_role BEFORE INSERT ON players
+BEGIN
+	SELECT CASE
+	WHEN ( SELECT COUNT(*) FROM roles, games WHERE
+		roles.title_id = games.title_id AND
+		games.game_id = new.game_id AND
+		roles.role = new.role ) <> 1
+	THEN RAISE(ABORT, "Invalid role for that title.")
+	END;
+END;
+
+DROP TRIGGER IF EXISTS purge_players;
+CREATE TRIGGER purge_players AFTER DELETE ON games
+BEGIN
+	DELETE FROM players WHERE game_id = old.game_id;
+END;
diff --git a/tools/start.sh b/tools/start.sh
new file mode 100644
index 0000000..eb5240a
--- /dev/null
+++ b/tools/start.sh
@@ -0,0 +1,2 @@
+#!/bin/bash
+forever start -a --uid rally --killSignal=SIGTERM -c 'nodemon --exitcrash' server.js
diff --git a/tools/stop.sh b/tools/stop.sh
new file mode 100644
index 0000000..39f920a
--- /dev/null
+++ b/tools/stop.sh
@@ -0,0 +1,2 @@
+#!/bin/bash
+forever stop rally
diff --git a/tools/writegame.sh b/tools/writegame.sh
new file mode 100644
index 0000000..82cff23
--- /dev/null
+++ b/tools/writegame.sh
@@ -0,0 +1,7 @@
+#!/bin/bash
+if [ -n "$1" -a -f "$2" ]
+then
+	sqlite3 db "update games set state=readfile('$2') where game_id = $1"
+else
+	echo "usage: bash tools/writegame.sh GAME state.json"
+fi
diff --git a/views/about.ejs b/views/about.ejs
new file mode 100644
index 0000000..ed1b54d
--- /dev/null
+++ b/views/about.ejs
@@ -0,0 +1,83 @@
+<%- include('header', { title: "Rally the Troops!" }) %>
+<style>li img{height:1.0em;vertical-align:middle}</style>
+
+<p>
+Rally the Troops! is created and maintained by Tor Andersson.
+It is an open source project, and you can find the code on <a href="https://github.com/ccxvii/rally-the-troops">GitHub</a>.
+
+<h2>
+Tips &amp; Tricks
+</h2>
+
+<ul>
+
+<li>
+Open a separate browser tab or window for each side when playing solo.
+
+<li>
+Use the middle mouse button to drag and scroll around the map.
+
+<li>
+The <i>Enter</i> and <i>Escape</i> keys open and close the chat box.
+
+<li>
+To invite your friends to a private game, send them the address of the join page.
+
+<li>
+Chat messages can only be seen by players who are part of a game.
+They are hidden from observers.
+
+<li>
+The <img src="/images/cog.svg"> menu has links to rules, player aids and other reference material.
+In some games you can also choose between alternative graphics and layout options.
+
+<li>
+The <img src="/images/earth-africa-europe.svg"> button hides all counters and markers,
+if you need to check something on the map that is obscured.
+
+<li>
+The <img src="/images/scroll-quill.svg"> button hides the game log and player status displays, so you can
+see more of the map.
+
+<li>
+The <img src="/images/chat-bubble.svg"> button lights up if you have unread chat messages.
+
+</ul>
+
+<h2>
+Licensing
+</h2>
+
+<p>
+All games are used with consent from their respective rights holders.
+
+<p>
+Icons are sourced from <a href="https://game-icons.net">game-icons.net</a>
+by Delapouite, Lorc, and others under the
+<a href="https://creativecommons.org/licenses/by/3.0/">CC BY 3.0</a> license.
+
+<!--
+<h2>
+Other Games
+</h2>
+
+<p>
+Here are some web sites where you can play other historical games:
+
+<dl>
+<dt><a href="https://www.boardgamearena.com/">boardgamearena.com</a>
+<dd>Polis
+<dd>Unconditional Surrender
+<dt><a href="http://civ.rol-play.com/">civ.rol-play.com</a>
+<dd>Advanced Civilization
+<dt><a href="https://paronglans.com/">paronglans.com</a>
+<dd>Julius Caesar
+<dt><a href="http://playfriedrich.com/">playfriedrich.com</a>
+<dd>Friedrich
+<dt><a href="https://www.yucata.de/">yucata.de</a>
+<dd>A Few Acres of Snow
+<dd>Pax Porfiriana
+<dd>Polis
+<dd>Sekigahara
+</dl>
+-->
diff --git a/views/banned.ejs b/views/banned.ejs
new file mode 100644
index 0000000..4c08bc2
--- /dev/null
+++ b/views/banned.ejs
@@ -0,0 +1,3 @@
+<%- include('header', { title: "Banned" }) %>
+<p>
+Sorry, but this IP or account has been banned.
diff --git a/views/change_password.ejs b/views/change_password.ejs
new file mode 100644
index 0000000..ab15a4a
--- /dev/null
+++ b/views/change_password.ejs
@@ -0,0 +1,15 @@
+<%- include('header', { title: "Change password" }) %>
+<form action="/change_password" method="post">
+<p>
+Name: <%= user.name %>
+<p>
+Mail: <%= user.mail %>
+<p>
+<label for="password">Old Password: </label><br>
+<input type="password" id="password" name="password" required>
+<p>
+<label for="newpass">New Password: </label><br>
+<input type="password" id="newpass" name="newpass" required>
+<p>
+<button type="submit">Change password</button>
+</form>
diff --git a/views/create.ejs b/views/create.ejs
new file mode 100644
index 0000000..a23fab5
--- /dev/null
+++ b/views/create.ejs
@@ -0,0 +1,22 @@
+<%- include('header', { title: title.title_name }) %>
+<style>form{display:block;margin-left:200px;}</style>
+<a href="/info/<%= title.title_id %>"><img class="logo" src="/<%= title.title_id %>/cover.jpg"></a>
+<form action="/create/<%= title.title_id %>" method="post">
+<p>
+Scenario:<br>
+<select id="scenario" name="scenario">
+<% scenarios.forEach((scenario) => { %>
+<option value="<%= scenario %>"><%= scenario %></option>
+<% }); %>
+</select>
+<p>
+Description:<br>
+<input type="text" autocomplete="off" id="description" name="description" size="50">
+<p>
+<label>
+<input type="checkbox" id="private" name="private" value="private">
+<span>Private</span>
+</label>
+<p>
+<button type="submit">Create</button>
+</form>
diff --git a/views/error.ejs b/views/error.ejs
new file mode 100644
index 0000000..b7d9632
--- /dev/null
+++ b/views/error.ejs
@@ -0,0 +1 @@
+<%- include('header', { title: "Error" }) %>
diff --git a/views/header.ejs b/views/header.ejs
new file mode 100644
index 0000000..9e101dd
--- /dev/null
+++ b/views/header.ejs
@@ -0,0 +1,32 @@
+<!DOCTYPE html>
+<html>
+<head>
+<meta name="viewport" content="width=device-width, height=device-height, initial-scale=1">
+<% if (typeof refresh != 'undefined' && refresh > 0) { %><meta http-equiv="refresh" content="<%= refresh %>"><% } %>
+<link rel="icon" href="/images/rally-the-troops.png">
+<link rel="stylesheet" href="/fonts/fonts.css">
+<link rel="stylesheet" href="/style.css">
+<title><%= title %></title>
+</head>
+<body>
+<div class="header">
+<div><a href="/"><img src="/images/rally-the-troops.svg" width="48" height="48"></a></div>
+<div>
+<span><a href="/about">About</a></span>
+<%
+	if (user) {
+		%><span><a href="/profile">Profile (<%= user.name %>)</a></span><%
+	} else {
+		%><span><a href="/signup">Signup</a></span><%
+		%><span><a href="/login">Login</a></span><%
+	}
+%>
+</div>
+</div>
+<div class="main">
+<h1><%= title %></h1>
+<%
+	if (typeof message != 'undefined' && message.length > 0) {
+		%><p class="error"><%= Array.isArray(message) ? message.join("\n") : message %></p><%
+	}
+%>
diff --git a/views/index.ejs b/views/index.ejs
new file mode 100644
index 0000000..a9abeff
--- /dev/null
+++ b/views/index.ejs
@@ -0,0 +1,19 @@
+<%- include('header', { title: "Rally the Troops!" }) %>
+<style>
+.list { display: flex; flex-wrap: wrap; justify-content: left; }
+.list a { margin: 1em; display: block; }
+.list img { box-shadow: 2px 2px 4px 0px rgba(0,0,0,0.5); }
+</style>
+
+<p>
+Rally the Troops! is a website where you can play historic games with other
+players.
+
+<p>
+Registration and use is free, and there are no ads.
+
+<div class="list">
+</div>
+
+<p>
+Join the <a href="https://discord.gg/CBrTh8k84A">Discord</a> server to find players or report bugs.
diff --git a/views/info.ejs b/views/info.ejs
new file mode 100644
index 0000000..27cb543
--- /dev/null
+++ b/views/info.ejs
@@ -0,0 +1,83 @@
+<%- include('header', { title: title.title_name, refresh: (user ? 300 : 0) }) %>
+<img class="logo" src="/<%= title.title_id %>/cover.jpg">
+<%- include('../public/' + title.title_id + '/about.html') %>
+<br clear=left>
+<p>
+Read more about the game on
+<a href="https://boardgamegeek.com/boardgame/<%= title.bgg %>">boardgamegeek.com</a>.
+
+<% if (user) { %>
+
+<h2>Open Games</h2>
+<table class="wide">
+<tr><th>ID<th>Scenario<th>Owner<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.description %>
+<td class="nowrap"><%= row.ctime %>
+<td><a href="/join/<%= row.game_id %>">Join</a>
+<% }); } else { %>
+<tr><td colspan="6">No open games.
+<% } %>
+</table>
+
+<p>
+<a href="/create/<%= title.title_id %>">Create a new game</a>.
+
+<% if (active_games.length > 0) { %>
+<h2>Active Games</h2>
+<table class="wide">
+<tr><th>ID<th>Scenario<th>Players<th>Description<th>Changed<th>Turn<th>
+<% active_games.forEach((row) => { %>
+<tr>
+<td><%= row.game_id %>
+<td><%= row.scenario %>
+<td><%= row.players.join(", ") %>
+<td><%= row.description %>
+<td class="nowrap"><%= row.mtime %>
+<%
+	if (row.your_turn) {
+		%><td class="your_turn"><%= row.active %><%
+	} 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><%
+	}
+%>
+<% }); %>
+</table>
+<% } %>
+
+<% if (finished_games.length > 0) { %>
+<h2>Finished Games</h2>
+<table class="wide">
+<tr><th>ID<th>Scenario<th>Players<th>Description<th>Finished<th>Result<th>
+<% finished_games.forEach((row) => { %>
+<tr>
+<td><%= row.game_id %>
+<td><%= row.scenario %>
+<td><%= row.players.join(", ") %>
+<td><%= row.description %>
+<td class="nowrap"><%= row.mtime %>
+<td><%= row.result %>
+<td><a href="/join/<%= row.game_id %>">View</a>
+<% }); %>
+</table>
+<% } %>
+
+<% } else { %>
+
+<p>
+Login to create or join a game.
+
+<% } %>
diff --git a/views/join.ejs b/views/join.ejs
new file mode 100644
index 0000000..810e0f4
--- /dev/null
+++ b/views/join.ejs
@@ -0,0 +1,85 @@
+<%- include('header', { title: game.title_name, refresh: game.status == 0 ? 15 : 0 }) %>
+<script>
+function confirm_delete(status) {
+	let warning = "Are you sure you want to DELETE this game?";
+	if (window.confirm(warning))
+		window.open("/delete/<%= game.game_id %>");
+}
+</script>
+
+<a href="/info/<%= game.title_id %>"><img class="logo" src="/<%= game.title_id %>/cover.jpg"></a>
+
+<p>
+Owner: <%= game.owner_name %>
+<p>
+Scenario: <%= game.scenario %>
+<p>
+Description: <%= game.description || "No description." %>
+
+<br clear=left>
+
+<p>
+<table>
+<tr>
+<%
+	roles.forEach((role) => {
+		%><th><%= role.role %><%
+	});
+%>
+<tr>
+<%
+	roles.forEach((role) => {
+		if (game.active == role.role || game.active == "Both" || game.active == "All") {
+			%><td style="min-width:9em" class="your_turn"><%
+		} else {
+			%><td style="min-width:9em"><%
+		}
+		let p = players.find(p => p.role == role.role);
+		if (game.status == 0) {
+			if (p) {
+				if ((p.user_id == user.user_id) || (game.owner_id == user.user_id)) {
+					%><a style="color:red;text-decoration:none" href="/part/<%= game.game_id %>/<%= p.user_id %>/<%= p.role %>">&#x274c;</a> <%
+					%><%= p.name %><%
+				} else {
+					%><%= p.name %><%
+				}
+			} else {
+				%><a href="/join/<%= game.game_id %>/<%= role.role %>">Join</a><%
+			}
+		} else {
+			if (p) {
+				if (p.user_id == user.user_id) {
+					%><a href="/play/<%= game.game_id %>/<%= p.role %>">Play</a><%
+				} else {
+					%><%= p.name %><%
+				}
+			} else {
+				%><i>Empty</i><%
+			}
+		}
+	});
+	if (game.status > 0 && !players.some(p => p.user_id == user.user_id)) {
+		%>
+		<tr><td colspan="<%= roles.length %>"><a href="/play/<%= game.game_id %>/Observer">View</a>
+		<%
+	}
+%>
+</table>
+
+<p>
+<%
+	if (game.status == 0) {
+		if (players.length == roles.length) {
+			if (game.owner_id == user.user_id) {
+				%><form action="/start/<%= game.game_id %>"><button type="submit">Start!</button></form><%
+			} else {
+				%>Waiting for <%= game.owner_name %> to start the game.<%
+			}
+		} else {
+			%>Waiting for players to join the game.<%
+		}
+	}
+	if (game.owner_id == user.user_id && (game.status == 0 || solo)) {
+		%><p><br><button onclick="confirm_delete()">Delete</button><%
+	}
+%>
diff --git a/views/login.ejs b/views/login.ejs
new file mode 100644
index 0000000..a5e2546
--- /dev/null
+++ b/views/login.ejs
@@ -0,0 +1,15 @@
+<%- include('header', { title: "Login" }) %>
+<% if (user) { %>
+<p>You're already logged in!
+<% } else { %>
+<form action="/login" method="post">
+<p>
+<label for="username">Name: </label><br>
+<input type="text" id="username" name="username" required>
+<p>
+<label for="password">Password: </label><br>
+<input type="password" id="password" name="password" required>
+<p>
+<button type="submit">Login</button>
+</form>
+<% } %>
diff --git a/views/profile.ejs b/views/profile.ejs
new file mode 100644
index 0000000..60f1ff9
--- /dev/null
+++ b/views/profile.ejs
@@ -0,0 +1,84 @@
+<%- include('header', { title: "Rally the Troops!", refresh: (active_games.length > 0 ? 300 : 0) }) %>
+<style>td.nowrap a { color: black; text-decoration: none; }</style>
+
+<img class="logo" src="<%= avatar %>" width="80" height="80">
+<p>
+Welcome, <%= user.name %>!
+<p>
+Your mail address is <%= user.mail %>.
+
+<br clear=left>
+
+<p>
+<a href="/change_password">Change password</a>
+
+<p>
+<a href="/logout">Logout</a>
+
+<% if (open_games.length > 0) { %>
+<h2>Open Games</h2>
+<table class="wide">
+<tr><th>ID<th>Game<th>Scenario<th>Players<th>Description<th>Created<th>
+<% 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.players.join(", ") %>
+<td><%= row.description %>
+<td class="nowrap"><%= row.ctime %>
+<td><a href="/join/<%= row.game_id %>">Join</a>
+<% }); %>
+</table>
+<% } %>
+
+<% if (active_games.length > 0) { %>
+<h2>Active Games</h2>
+<table class="wide">
+<tr><th>ID<th>Game<th>Scenario<th>Players<th>Description<th>Changed<th>Turn<th>
+<% 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.players.join(", ") %>
+<td><%= row.description %>
+<td class="nowrap"><%= row.mtime %>
+<%
+	if (row.your_turn) {
+		%><td class="your_turn"><%= row.active %><%
+	} else {
+		%><td><%= row.active %><%
+	}
+	if (row.players.reduce((n,p) => n + (p === user.name ? 1 : 0), 0) == 1) {
+		%><td><a href="/play/<%= row.game_id %>">Play</a><%
+	} else {
+		%><td><a href="/join/<%= row.game_id %>">Play</a><%
+	}
+%>
+<% }); %>
+</table>
+<% } %>
+
+<% if (finished_games.length > 0) { %>
+<h2>Finished Games</h2>
+<table class="wide">
+<tr><th>ID<th>Game<th>Scenario<th>Players<th>Description<th>Finished<th>Result<th>
+<% finished_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.players.join(", ") %>
+<td><%= row.description %>
+<td class="nowrap"><%= row.mtime %>
+<td><%= row.result %>
+<td><a href="/join/<%= row.game_id %>">View</a>
+<% }); %>
+</table>
+<% } %>
+
+<% if (open_games.length == 0 && active_games.length == 0 && finished_games.length == 0) { %>
+<p>
+You don't have any current or finished games.
+<% } %>
diff --git a/views/signup.ejs b/views/signup.ejs
new file mode 100644
index 0000000..819da3f
--- /dev/null
+++ b/views/signup.ejs
@@ -0,0 +1,18 @@
+<%- include('header', { title: "Signup" }) %>
+<% if (user) { %>
+<p>You're already logged in!
+<% } else { %>
+<form action="/signup" method="post">
+<p>
+<label for="username">Name: </label><br>
+<input type="text" id="username" name="username" required>
+<p>
+<label for="mail">Mail: </label><br>
+<input type="text" id="mail" name="mail" required>
+<p>
+<label for="password">Password: </label><br>
+<input type="password" id="password" name="password" required>
+<p>
+<button type="submit">Create Account</button>
+</form>
+<% } %>
diff --git a/views/users.ejs b/views/users.ejs
new file mode 100644
index 0000000..a8f5c7d
--- /dev/null
+++ b/views/users.ejs
@@ -0,0 +1,16 @@
+<%- include('header', { title: "User list" }) %>
+<style>
+td.avatar{padding:0;width:80px;}
+td.avatar img{display:block;width:80px;height:80px;}
+</style>
+
+<table class="wide">
+<tr><th>Avatar<th>Name<th>Member since<th>Last seen
+
+<% userList.forEach((row) => { %>
+<tr>
+<td class="avatar"><img src="<%= row.avatar %>">
+<td><%= row.name %>
+<td><%= row.ctime %>
+<td><%= row.atime %>
+<% }); %>