Messages.

8 files changed, 295 insertions, 2 deletions

diff --git a/server.js b/server.js
index 7f89252..f8ab0c3 100644
--- a/server.js
+++ b/server.js
@@ -191,6 +191,8 @@ const sql_login_select = db.prepare("SELECT user_id, name, mail, password, salt
 const sql_subscribe = db.prepare("UPDATE users SET notifications = 1 WHERE user_id = ?");
 const sql_unsubscribe = db.prepare("UPDATE users SET notifications = 0 WHERE user_id = ?");
 
+const sql_count_unread_messages = db.prepare("SELECT COUNT(*) FROM messages WHERE to_id = ? AND read = 0 AND deleted_from_inbox = 0").pluck();
+
 passport.serializeUser(function (user, done) {
 	return done(null, user.user_id);
 });
@@ -200,6 +202,7 @@ passport.deserializeUser(function (user_id, done) {
 		let row = sql_deserialize_user.get(user_id);
 		if (!row)
 			return done(null, false);
+		row.unread = () => sql_count_unread_messages.get(user_id);
 		return done(null, row);
 	} catch (err) {
 		console.log(err);
@@ -1442,7 +1445,7 @@ app.get('/stats', function (req, res) {
 
 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();
+	let rows = db.prepare("SELECT user_id, 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);
@@ -1616,3 +1619,154 @@ app.post('/forum/reply/:thread_id', must_be_logged_in, function (req, res) {
 	FORUM_NEW_POST.run(thread_id, user_id, body);
 	res.redirect('/forum/thread/'+thread_id);
 });
+
+// MESSAGES
+const MESSAGE_GET_USER = db.prepare("SELECT user_id, name, mail, notifications FROM users WHERE name = ?");
+const MESSAGE_GET_USER_ID = db.prepare("SELECT user_id FROM users WHERE name = ?").pluck();
+const MESSAGE_GET_USER_NAME = db.prepare("SELECT name FROM users WHERE user_id = ?").pluck();
+
+const MESSAGE_LIST_INBOX = db.prepare(`
+	SELECT message_id, from_name, subject, time, read
+	FROM message_view
+	WHERE to_id = ? AND deleted_from_inbox = 0
+	ORDER BY time DESC`);
+
+const MESSAGE_LIST_OUTBOX = db.prepare(`
+	SELECT message_id, to_name, subject, time, 1 as read
+	FROM message_view
+	WHERE from_id = ? AND deleted_from_outbox = 0
+	ORDER BY time DESC`);
+
+const MESSAGE_FETCH = db.prepare("SELECT * FROM message_view WHERE message_id = ? AND ( from_id = ? OR to_id = ? )");
+const MESSAGE_SEND = db.prepare("INSERT INTO messages ( from_id, to_id, subject, body ) VALUES ( ?, ?, ?, ? )");
+const MESSAGE_MARK_READ = db.prepare("UPDATE messages SET read = 1 WHERE message_id = ?");
+const MESSAGE_DELETE_INBOX = db.prepare("UPDATE messages SET deleted_from_inbox = 1 WHERE message_id = ? AND to_id = ?");
+const MESSAGE_DELETE_OUTBOX = db.prepare("UPDATE messages SET deleted_from_outbox = 1 WHERE message_id = ? AND from_id = ?");
+
+app.get('/inbox', must_be_logged_in, function (req, res) {
+	LOG(req, "GET /inbox");
+	let messages = MESSAGE_LIST_INBOX.all(req.user.user_id);
+	for (let i = 0; i < messages.length; ++i) {
+		messages[i].time = human_date(messages[i].time);
+	}
+	res.set("Cache-Control", "no-store");
+	res.render('message_inbox.ejs', {
+		user: req.user,
+		messages: messages,
+		message: req.flash('message'),
+	});
+});
+
+app.get('/outbox', must_be_logged_in, function (req, res) {
+	LOG(req, "GET /outbox");
+	let messages = MESSAGE_LIST_OUTBOX.all(req.user.user_id);
+	for (let i = 0; i < messages.length; ++i) {
+		messages[i].time = human_date(messages[i].time);
+	}
+	res.set("Cache-Control", "no-store");
+	res.render('message_outbox.ejs', {
+		user: req.user,
+		messages: messages,
+		message: req.flash('message'),
+	});
+});
+
+app.get('/message/read/:message_id', must_be_logged_in, function (req, res) {
+	LOG(req, "GET /message/" + req.params.message_id);
+	let message_id = req.params.message_id | 0;
+	let mail = MESSAGE_FETCH.get(message_id, req.user.user_id, req.user.user_id);
+	if (!mail) {
+		req.flash('message', "Cannot find that message.");
+		return res.redirect('/inbox');
+	}
+	if (mail.to_id === req.user.user_id)
+		MESSAGE_MARK_READ.run(message_id);
+	mail.time = human_date(mail.time);
+	res.render('message_read.ejs', {
+		user: req.user,
+		mail: mail,
+		message: req.flash('message'),
+	});
+});
+
+app.get('/message/send', must_be_logged_in, function (req, res) {
+	res.render('message_send.ejs', {
+		user: req.user,
+		to_id: 0,
+		to_name: "",
+		subject: "",
+		body: "",
+		message: req.flash('message'),
+	});
+});
+
+app.get('/message/send/:to_id', must_be_logged_in, function (req, res) {
+	LOG(req, "GET /message/send/" + req.params.to_id);
+	let to_id = req.params.to_id | 0;
+	if (to_id === 0)
+		to_id = MESSAGE_GET_USER_ID.get(req.params.to_id);
+	let to_name = MESSAGE_GET_USER_NAME.get(to_id);
+	if (!to_name)
+		to_name = "";
+	res.render('message_send.ejs', {
+		user: req.user,
+		to_id: to_id,
+		to_name: to_name,
+		subject: "",
+		body: "",
+		message: req.flash('message'),
+	});
+});
+
+app.post('/message/send', must_be_logged_in, function (req, res) {
+	LOG(req, "POST /message/send/" + req.params.to_id);
+	let to_name = req.body.to;
+	let subject = req.body.subject;
+	let body = req.body.body;
+	let to_user = MESSAGE_GET_USER.get(to_name);
+	if (!to_user) {
+		return res.render('message_send.ejs', {
+			user: req.user,
+			to_id: 0,
+			to_name: to_name,
+			subject: subject,
+			body: body,
+			message: "Cannot find that user."
+		});
+	}
+	MESSAGE_SEND.run(req.user.user_id, to_user.user_id, subject, body);
+	if (to_user.notifications) {
+		console.log("MAIL USER NOTIFICATION");
+	}
+	res.redirect('/inbox');
+});
+
+function quote_body(text) {
+	return "> " + text.split("\n").join("\n> ") + "\n\n";
+}
+
+app.get('/message/reply/:message_id', must_be_logged_in, function (req, res) {
+	LOG(req, "POST /message/reply/" + req.params.message_id);
+	let message_id = req.params.message_id | 0;
+	let mail = MESSAGE_FETCH.get(message_id, req.user.user_id, req.user.user_id);
+	if (!mail) {
+		req.flash('message', "Cannot find that message.");
+		return res.redirect('/inbox');
+	}
+	return res.render('message_send.ejs', {
+		user: req.user,
+		to_id: mail.from_id,
+		to_name: mail.from_name,
+		subject: mail.subject.startsWith("Re: ") ? mail.subject : "Re: " + mail.subject,
+		body: quote_body(mail.body),
+		message: req.flash('message')
+	});
+});
+
+app.get('/message/delete/:message_id', must_be_logged_in, function (req, res) {
+	LOG(req, "POST /message/delete/" + req.params.message_id);
+	let message_id = req.params.message_id | 0;
+	MESSAGE_DELETE_INBOX.run(message_id, req.user.user_id);
+	MESSAGE_DELETE_OUTBOX.run(message_id, req.user.user_id);
+	res.redirect('/inbox');
+});
diff --git a/tools/sql/schema.txt b/tools/sql/schema.txt
index 8c3e72f..cd1a7ee 100644
--- a/tools/sql/schema.txt
+++ b/tools/sql/schema.txt
@@ -103,6 +103,18 @@ CREATE TABLE IF NOT EXISTS posts (
 	body TEXT
 );
 
+CREATE TABLE IF NOT EXISTS messages (
+	message_id INTEGER PRIMARY KEY,
+	from_id INTEGER,
+	to_id INTEGER,
+	time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+	subject TEXT,
+	body TEXT,
+	read BOOLEAN DEFAULT 0,
+	deleted_from_inbox BOOLEAN DEFAULT 0,
+	deleted_from_outbox BOOLEAN DEFAULT 0
+);
+
 DROP TRIGGER IF EXISTS update_reply_count;
 CREATE TRIGGER update_reply_count AFTER INSERT ON posts
 BEGIN
@@ -239,3 +251,21 @@ CREATE VIEW post_view AS
 	FROM posts
 	JOIN users AS author ON posts.author_id = author.user_id
 	;
+
+DROP VIEW IF EXISTS message_view;
+CREATE VIEW message_view AS
+	SELECT messages.message_id
+		, messages.from_id
+		, users_from.name AS from_name
+		, messages.to_id
+		, users_to.name AS to_name
+		, messages.time
+		, messages.subject
+		, messages.body
+		, messages.read
+		, messages.deleted_from_inbox
+		, messages.deleted_from_outbox
+	FROM messages
+	JOIN users AS users_from ON messages.from_id = users_from.user_id
+	JOIN users AS users_to ON messages.to_id = users_to.user_id
+	;
diff --git a/views/header.ejs b/views/header.ejs
index 9c8ee0d..aed34d0 100644
--- a/views/header.ejs
+++ b/views/header.ejs
@@ -16,6 +16,12 @@
 <span><a href="/forum">Forum</a></span>
 <%
 	if (user) {
+		let unread = user.unread();
+		if (unread > 0) {
+			%><span><a href="/inbox">Inbox (<%= unread %>)</a></span><%
+		} else {
+			%><span><a href="/inbox">Inbox</a></span><%
+		}
 		%><span><a href="/profile">Profile (<%= user.name %>)</a></span><%
 	} else {
 		%><span><a href="/signup">Signup</a></span><%
diff --git a/views/message_inbox.ejs b/views/message_inbox.ejs
new file mode 100644
index 0000000..91490ac
--- /dev/null
+++ b/views/message_inbox.ejs
@@ -0,0 +1,23 @@
+<%- include('header', { title: "Inbox" }) %>
+<style>
+table { width: 100%; max-width: 50em; }
+tr.unread { background-color: lemonchiffon; }
+td.from { width: 5em; }
+td.time { text-align: right; width: 5em; }
+td a { color:black; text-decoration: none; }
+</style>
+<p>
+&#xbb; <a href="/message/send">Send message</a>
+<br>
+&#xbb; <a href="/outbox">Outbox</a>
+<table>
+<tr><th>From<th>Subject<th>Date
+<% if (messages.length > 0) { messages.forEach((row) => { %>
+<tr class="<%- row.read ? "read" : "unread" %>">
+<td class="nowrap from"><%= row.from_name %>
+<td class="subject"><a href="/message/read/<%- row.message_id %>"><%= row.subject %></a>
+<td class="nowrap time"><%= row.time %>
+<% }); } else { %>
+<tr><td colspan="3">No messages</td>
+<% } %>
+</table>
diff --git a/views/message_outbox.ejs b/views/message_outbox.ejs
new file mode 100644
index 0000000..c460bd3
--- /dev/null
+++ b/views/message_outbox.ejs
@@ -0,0 +1,23 @@
+<%- include('header', { title: "Outbox" }) %>
+<style>
+table { width: 100%; max-width: 50em; }
+tr.unread { background-color: lemonchiffon; }
+td.to { width: 5em; }
+td.time { text-align: right; width: 5em; }
+td a { color:black; text-decoration: none; }
+</style>
+<p>
+&#xbb; <a href="/message/send">Send message</a>
+<br>
+&#xbb; <a href="/inbox">Inbox</a>
+<table>
+<tr><th>To<th>Subject<th>Date
+<% if (messages.length > 0) { messages.forEach((row) => { %>
+<tr class="<%- row.read ? "read" : "unread" %>">
+<td class="nowrap to"><%= row.to_name %>
+<td class="subject"><a href="/message/read/<%- row.message_id %>"><%= row.subject %></a>
+<td class="nowrap time"><%= row.time %>
+<% }); } else { %>
+<tr><td colspan="3">No messages</td>
+<% } %>
+</table>
diff --git a/views/message_read.ejs b/views/message_read.ejs
new file mode 100644
index 0000000..f4a791e
--- /dev/null
+++ b/views/message_read.ejs
@@ -0,0 +1,27 @@
+<%- include('header', { title: mail.subject }) %>
+<style>
+table { width: 100%; max-width: 50em; }
+th { width: 5em; font-weight: normal; }
+td.body { white-space: pre-wrap; padding: 10px 10px; }
+</style>
+<script>
+function delete_message(id) {
+	let warning = "Are you sure you want to DELETE this message?";
+	if (window.confirm(warning))
+		window.location.href = "/message/delete/" + id;
+}
+function reply_message(id) {
+	window.location.href = "/message/reply/" + id;
+}
+</script>
+<table>
+<tr><th>From:<td> <%= mail.from_name %>
+<tr><th>To:<td> <%= mail.to_name %>
+<tr><th>Date:<td> <%= mail.time %>
+<tr><td colspan="2" class="body"><%= mail.body %></td>
+</table>
+<p>
+<% if ( mail.from_id !== user.user_id ) { %>
+<button onclick="reply_message(<%- mail.message_id %>)">Reply</button>
+<% } %>
+<button onclick="delete_message(<%- mail.message_id %>)">Delete</button>
diff --git a/views/message_send.ejs b/views/message_send.ejs
new file mode 100644
index 0000000..6f72d74
--- /dev/null
+++ b/views/message_send.ejs
@@ -0,0 +1,30 @@
+<%- include('header', { title: "Send Message" }) %>
+<style>
+input, textarea { width: 100%; max-width: 45em; }
+</style>
+<form action="/message/send" method="post">
+<p>
+To:<br>
+<input id="to" type="text" name="to" size="80" maxlength="80"
+	onkeypress="if(event.keyCode===13){document.querySelector('#subject').focus();return false;}"
+	value="<%= to_name %>"
+	<%= to_id === 0 ? "autofocus" : "" %>
+	required>
+
+<p>
+Subject:
+<br>
+<input id="subject" type="text" name="subject" size="80" maxlength="80"
+	onkeypress="if(event.keyCode===13){document.querySelector('#body').focus();return false;}"
+	value="<%= subject %>"
+	<%= to_id > 0 ? "autofocus" : "" %>
+	pattern=".*\S+.*"
+	required>
+
+<p>
+Body:
+<br>
+<textarea id="body" name="body" rows="20" cols="80" maxlength="32000" required><%= body %></textarea>
+<p>
+<button type="submit">Send</button>
+</form>
diff --git a/views/users.ejs b/views/users.ejs
index a8f5c7d..526b722 100644
--- a/views/users.ejs
+++ b/views/users.ejs
@@ -10,7 +10,7 @@ td.avatar img{display:block;width:80px;height:80px;}
 <% userList.forEach((row) => { %>
 <tr>
 <td class="avatar"><img src="<%= row.avatar %>">
-<td><%= row.name %>
+<td><a href="/user/<%= row.user_id %>"><%= row.name %></a>
 <td><%= row.ctime %>
 <td><%= row.atime %>
 <% }); %>