Be more robust when deleting accounts.

Leave player assignment to not mess with Elo ratings and tournament data.

Reset ctime when game is actually started.

3 files changed, 44 insertions, 15 deletions

diff --git a/public/join.js b/public/join.js
index 20a95e7..75e7aca 100644
--- a/public/join.js
+++ b/public/join.js
@@ -275,6 +275,8 @@ function user_link(user_name) {
 }
 
 function player_link(player) {
+	if (!player.name)
+		return "null"
 	let link = user_link(player.name)
 	if (player.is_invite)
 		link = "<i>" + link + "</i> ?"
@@ -332,11 +334,18 @@ function create_game_list() {
 		create_game_list_item(list, "Pace", PACE_TEXT[game.pace])
 	create_game_list_item(list, "Notice", game.notice)
 
-	if (game.owner_id)
-		create_game_list_item(list, "Created", human_date(game.ctime) + " by " + user_link(game.owner_name))
-	else
-		create_game_list_item(list, "Created", human_date(game.ctime))
 
+	if (game.status === 0) {
+		if (game.owner_id)
+			create_game_list_item(list, "Created", human_date(game.ctime) + " by " + user_link(game.owner_name))
+		else
+			create_game_list_item(list, "Created", human_date(game.ctime))
+	} else {
+		if (game.owner_id)
+			create_game_list_item(list, "Started", human_date(game.ctime) + " by " + user_link(game.owner_name))
+		else
+			create_game_list_item(list, "Started", human_date(game.ctime))
+	}
 
 	create_game_list_item(list, "Moves", game.moves)
 
@@ -426,7 +435,9 @@ function update() {
 	}
 
 	if (game.status === 0) {
-		if (user_id)
+		if (game.is_ready)
+			window.game_enter.textContent = "Waiting to start."
+		else if (user_id)
 			window.game_enter.textContent = "Waiting for players to join."
 		else
 			window.game_enter.innerHTML = `<a href="/login">Login</a> or <a href="/signup">sign up</a> to join.`
diff --git a/schema.sql b/schema.sql
index ebe9a66..3ba249b 100644
--- a/schema.sql
+++ b/schema.sql
@@ -177,7 +177,7 @@ create view rating_view as
 		title_id, name, rating, count, last
 	from
 		ratings
-		join users using(user_id)
+		left join users using(user_id)
 	order by
 		title_id,
 		rating desc
@@ -499,7 +499,7 @@ create view player_view as
 	from
 		games
 		join players using(game_id)
-		join users using(user_id)
+		left join users using(user_id)
 		left join user_last_seen using(user_id)
 	;
 
@@ -538,7 +538,7 @@ create view game_export_view as
 						json_object('role', role, 'name', name)
 					)
 					from players
-					join users using(user_id)
+					left join users using(user_id)
 					where game_id = outer.game_id
 				),
 			'state',
@@ -683,9 +683,7 @@ begin
 	delete from posts where author_id = old.user_id;
 	delete from threads where author_id = old.user_id;
 	delete from game_chat where user_id = old.user_id;
-	delete from ratings where user_id = old.user_id;
-	delete from players where user_id = old.user_id and game_id in (select game_id from games where status <= 1);
-	update players set user_id = 0 where user_id = old.user_id;
+	delete from players where user_id = old.user_id and game_id in (select game_id from games where status = 0);
 	update games set owner_id = 0 where owner_id = old.user_id;
 end;
 
diff --git a/server.js b/server.js
index 5d26114..f500db7 100644
--- a/server.js
+++ b/server.js
@@ -583,15 +583,32 @@ app.post("/change-password", must_be_logged_in, function (req, res) {
 	return res.redirect("/profile")
 })
 
+const SQL_SELECT_MAY_DELETE_ACCOUNT = SQL(`
+	select exists (
+		select 1 from games join players using(game_id) where status <= 1 and user_id=?
+	)
+`).pluck()
+
+function may_delete_account(user_id) {
+	if (SQL_SELECT_MAY_DELETE_ACCOUNT.get(user_id))
+		return false
+	return true
+}
+
 app.get("/delete-account", must_be_logged_in, function (req, res) {
-	res.render("delete_account.pug", { user: req.user })
+	if (!may_delete_account(req.user.user_id))
+		return res.status(401).send("You may not delete your account while you have unfinished games.")
+	res.render("delete_account.pug", { user: req.user, flash })
 })
 
 const SQL_SELECT_GAME_ROLE_FOR_DELETED_USER = SQL(`
 	select game_id, role from players where user_id = ? and game_id in (select game_id from games where status <= 1)
-	`)
+`)
 
 app.post("/delete-account", must_be_logged_in, function (req, res) {
+	if (!may_delete_account(req.user.user_id))
+		res.status(401).send("You may not delete your account while you have unfinished games.")
+
 	let password = req.body.password
 	// Get full user record including password and salt
 	let user = SQL_SELECT_LOGIN.get(req.user.user_id)
@@ -1280,6 +1297,7 @@ const SQL_START_GAME = SQL(`
 	update games set
 		status = 1,
 		is_private = (is_private or user_count = 1 or user_count < player_count),
+		ctime = datetime(),
 		mtime = datetime(),
 		active = ?
 	where
@@ -1551,7 +1569,9 @@ function annotate_game_info(game, user_id, unread) {
 			time_left = Math.min(time_left, p.time_left)
 
 		let link
-		if (p.is_invite)
+		if (!p.name)
+			link = "null"
+		else if (p.is_invite)
 			link = `<a class="is_invite" href="/user/${p.name}">${p.name}?</a>`
 		else if (p.is_active)
 			link = `<a class="is_active" href="/user/${p.name}">${p.name}</a>`
@@ -2803,7 +2823,7 @@ function on_resign(socket) {
 }
 
 function do_resign(game_id, role, how) {
-	let game = SQL_SELECT_GAME_VIEW.get(game_id)
+	let game = SQL_SELECT_GAME.get(game_id)
 	let state = get_game_state(game_id)
 	let old_active = state.active