Add "delete account" screen.

4 files changed, 77 insertions, 2 deletions

diff --git a/schema.sql b/schema.sql
index 28f32e3..c8bb93c 100644
--- a/schema.sql
+++ b/schema.sql
@@ -44,6 +44,11 @@ create table if not exists users (
 	about text
 );
 
+insert or ignore into
+	users (user_id, name, mail, ctime)
+	values (0, 'Deleted', 'deleted@rally-the-troops.com', datetime('1970-01-01'))
+;
+
 create table if not exists user_last_seen (
 	user_id integer
 		primary key
@@ -396,7 +401,8 @@ create view your_turn as
 		and active in ('All', 'Both', role)
 	;
 
--- Manual key management if pragma foreign_keys = off
+-- Triggers to clean up without relying on foreign key cascades
+
 drop trigger if exists trigger_delete_on_games;
 create trigger trigger_delete_on_games after delete on games
 begin
@@ -406,3 +412,24 @@ begin
 	delete from last_notified where game_id = old.game_id;
 	delete from players where game_id = old.game_id;
 end;
+
+drop trigger if exists trigger_delete_on_users;
+create trigger trigger_delete_on_users after delete on users
+begin
+	delete from logins where user_id = old.user_id;
+	delete from tokens where user_id = old.user_id;
+	delete from user_last_seen where user_id = old.user_id;
+	delete from last_notified where user_id = old.user_id;
+	delete from messages where from_id = old.user_id or to_id = old.user_id;
+	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 players where user_id = old.user_id;
+	update games set owner_id = 0 where owner_id = old.user_id;
+end;
+
+drop trigger if exists trigger_delete_on_threads;
+create trigger trigger_delete_on_threads after delete on threads
+begin
+	delete from posts where thread_id = old.thread_id;
+end
diff --git a/server.js b/server.js
index 3ec457d..f99dd43 100644
--- a/server.js
+++ b/server.js
@@ -264,7 +264,9 @@ const SQL_EXISTS_USER_NAME = SQL("SELECT EXISTS ( SELECT 1 FROM users WHERE name
 const SQL_EXISTS_USER_MAIL = SQL("SELECT EXISTS ( SELECT 1 FROM users WHERE mail=? )").pluck()
 
 const SQL_INSERT_USER = SQL("INSERT INTO users (name,mail,password,salt,notify) VALUES (?,?,?,?,?) RETURNING user_id,name,mail,notify")
+const SQL_DELETE_USER = SQL("DELETE FROM users WHERE user_id = ?")
 
+const SQL_SELECT_LOGIN = SQL("SELECT * FROM user_login_view WHERE user_id=?")
 const SQL_SELECT_USER_BY_NAME = SQL("SELECT * FROM user_view WHERE name=?")
 const SQL_SELECT_LOGIN_BY_MAIL = SQL("SELECT * FROM user_login_view WHERE mail=?")
 const SQL_SELECT_LOGIN_BY_NAME = SQL("SELECT * FROM user_login_view WHERE name=?")
@@ -544,7 +546,7 @@ app.post('/change-password', must_be_logged_in, function (req, res) {
 	let oldpass = req.body.password
 	let newpass = req.body.newpass
 	// Get full user record including password and salt
-	let user = SQL_SELECT_LOGIN_BY_MAIL.get(req.user.mail)
+	let user = SQL_SELECT_LOGIN.get(req.user.user_id)
 	if (newpass.length < 4)
 		return res.render('change_password.pug', { user: req.user, flash: "Password is too short!" })
 	if (newpass.length > 100)
@@ -557,6 +559,21 @@ app.post('/change-password', must_be_logged_in, function (req, res) {
 	return res.redirect('/profile')
 })
 
+app.get('/delete-account', must_be_logged_in, function (req, res) {
+	res.render('delete_account.pug', { user: req.user })
+})
+
+app.post('/delete-account', must_be_logged_in, function (req, res) {
+	let password = req.body.password
+	// Get full user record including password and salt
+	let user = SQL_SELECT_LOGIN.get(req.user.user_id)
+	let hash = hash_password(password, user.salt)
+	if (hash !== user.password)
+		return res.render('delete_account.pug', { user: req.user, flash: "Wrong password!" })
+	SQL_DELETE_USER.run(req.user.user_id)
+	return res.send("Goodbye!")
+})
+
 app.get('/admin/ban-user/:who', must_be_administrator, function (req, res) {
 	let who = req.params.who
 	SQL_UPDATE_USER_IS_BANNED.run(1, who)
diff --git a/views/delete_account.pug b/views/delete_account.pug
new file mode 100644
index 0000000..905945c
--- /dev/null
+++ b/views/delete_account.pug
@@ -0,0 +1,29 @@
+//- vim:ts=4:sw=4:
+doctype html
+html
+	head
+		include head
+		title Delete account
+	body
+		include header
+		article
+			h1 Delete account
+			if flash
+				p.error= flash
+
+			form(method="post" action="/delete-account")
+				p Name: #{user.name}
+				p Mail: #{user.mail}
+				p
+					label Password:
+						br
+						input(type="password" name="password" required)
+				p
+					label Type DELETE to confirm:
+						br
+						input(type="text" name="confirm" pattern="^DELETE$" required)
+				p.warning WARNING: Deleting your account is permanent!
+				p All your games and messages will be lost forever.
+				p
+					button(onclick="window.location='/profile'") Cancel
+					button(type="submit") Delete!
diff --git a/views/profile.pug b/views/profile.pug
index 52bb0c0..7ed8eff 100644
--- a/views/profile.pug
+++ b/views/profile.pug
@@ -25,6 +25,8 @@ html
 				| <a href="/change-name">Change user name</a>
 				br
 				| <a href="/change-about">Change profile text</a>
+				br
+				| <a href="/delete-account">Delete account</a>
 
 			p <a href="/chat">Chat log</a>