Track forum thread read/unread status for logged in users.

4 files changed, 40 insertions, 8 deletions

diff --git a/schema.sql b/schema.sql
index 95a2cf7..6ee8388 100644
--- a/schema.sql
+++ b/schema.sql
@@ -164,6 +164,12 @@ create table if not exists posts (
 	body text
 );
 
+create table if not exists read_threads (
+	user_id integer,
+	thread_id integer,
+	primary key (user_id, thread_id)
+) without rowid;
+
 drop view if exists thread_view;
 create view thread_view as
 	select
@@ -383,6 +389,7 @@ begin
 	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 read_threads where user_id = old.user_id;
 	delete from contacts where me = old.user_id or you = 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;
@@ -396,4 +403,17 @@ 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
+	delete from read_threads where thread_id = old.thread_id;
+end;
+
+drop trigger if exists trigger_mark_threads_as_unread1;
+create trigger trigger_mark_threads_as_unread1 after insert on posts
+begin
+	delete from read_threads where user_id != new.author_id and thread_id = new.thread_id;
+end;
+
+drop trigger if exists trigger_mark_threads_as_unread2;
+create trigger trigger_mark_threads_as_unread2 after update on posts
+begin
+	delete from read_threads where user_id != new.author_id and thread_id = new.thread_id;
+end;
diff --git a/server.js b/server.js
index 6b3ce4d..69f5ebd 100644
--- a/server.js
+++ b/server.js
@@ -886,13 +886,15 @@ app.get('/outbox/delete', must_be_logged_in, function (req, res) {
 const FORUM_PAGE_SIZE = 15
 
 const FORUM_COUNT_THREADS = SQL("SELECT COUNT(*) FROM threads").pluck()
-const FORUM_LIST_THREADS = SQL("SELECT * FROM thread_view ORDER BY mtime DESC LIMIT ? OFFSET ?")
+const FORUM_LIST_THREADS_USER = SQL("SELECT *, (exists (select 1 from read_threads where user_id=? and read_threads.thread_id=thread_view.thread_id)) as is_read FROM thread_view ORDER BY mtime DESC LIMIT ? OFFSET ?")
+const FORUM_LIST_THREADS = SQL("SELECT *, 1 as is_read FROM thread_view ORDER BY mtime DESC LIMIT ? OFFSET ?")
 const FORUM_GET_THREAD = SQL("SELECT * FROM thread_view WHERE thread_id=?")
 const FORUM_LIST_POSTS = SQL("SELECT * FROM post_view WHERE thread_id=?")
 const FORUM_GET_POST = SQL("SELECT * FROM post_view WHERE post_id=?")
 const FORUM_NEW_THREAD = SQL("INSERT INTO threads (author_id,subject) VALUES (?,?)")
 const FORUM_NEW_POST = SQL("INSERT INTO posts (thread_id,author_id,body) VALUES (?,?,?)")
 const FORUM_EDIT_POST = SQL("UPDATE posts SET body=?, mtime=julianday() WHERE post_id=? AND author_id=? RETURNING thread_id").pluck()
+const FORUM_MARK_READ = SQL("insert or ignore into read_threads (user_id,thread_id) values (?,?)")
 
 const FORUM_DELETE_THREAD_POSTS = SQL("delete from posts where thread_id=?")
 const FORUM_DELETE_THREAD = SQL("delete from threads where thread_id=?")
@@ -901,7 +903,11 @@ const FORUM_DELETE_POST = SQL("delete from posts where post_id=?")
 function show_forum_page(req, res, page) {
 	let thread_count = FORUM_COUNT_THREADS.get()
 	let page_count = Math.ceil(thread_count / FORUM_PAGE_SIZE)
-	let threads = FORUM_LIST_THREADS.all(FORUM_PAGE_SIZE, FORUM_PAGE_SIZE * (page - 1))
+	let threads
+	if (req.user)
+		threads = FORUM_LIST_THREADS_USER.all(req.user.user_id, FORUM_PAGE_SIZE, FORUM_PAGE_SIZE * (page - 1))
+	else
+		threads = FORUM_LIST_THREADS.all(FORUM_PAGE_SIZE, FORUM_PAGE_SIZE * (page - 1))
 	for (let thread of threads)
 		thread.mtime = human_date(thread.mtime)
 	res.render('forum_view.pug', {
@@ -942,6 +948,8 @@ app.get('/forum/thread/:thread_id', function (req, res) {
 		posts[i].ctime = human_date(posts[i].ctime)
 		posts[i].mtime = human_date(posts[i].mtime)
 	}
+	if (req.user)
+		FORUM_MARK_READ.run(req.user.user_id, thread_id)
 	res.render('forum_thread.pug', {
 		user: req.user,
 		thread: thread,
diff --git a/views/forum_view.pug b/views/forum_view.pug
index 990d3d3..46c1b1e 100644
--- a/views/forum_view.pug
+++ b/views/forum_view.pug
@@ -6,6 +6,7 @@ html
 		title Forum
 		meta(http-equiv="refresh" content=3600)
 		style.
+			.unread { font-weight: bold }
 			tbody a { display: block }
 	body
 		include header
@@ -21,7 +22,8 @@ html
 				tbody
 					each row in threads
 						tr
-							td: a(href="/forum/thread/"+row.thread_id)= row.subject
+							td(class=row.is_read?"read":"unread")
+								a(href="/forum/thread/"+row.thread_id)= row.subject
 							td.r= row.count
 							td.w= row.mtime
 				tfoot
diff --git a/views/message_inbox.pug b/views/message_inbox.pug
index 29bd4d3..10c4626 100644
--- a/views/message_inbox.pug
+++ b/views/message_inbox.pug
@@ -5,7 +5,7 @@ html
 		include head
 		title Inbox
 		style.
-			.unread { background-color: lightyellow }
+			.unread { font-weight: bold }
 			td a { display: block }
 	body
 		include header
@@ -23,9 +23,11 @@ html
 						th Date
 				tbody
 					each row in messages
-						tr(class=row.is_read?"read":"unread")
-							td: a(href="/user/"+row.from_name)= row.from_name
-							td: a(href="/message/read/"+row.message_id)= row.subject
+						tr
+							td
+								a(href="/user/"+row.from_name)= row.from_name
+							td(class=row.is_read?"read":"unread")
+								a(href="/message/read/"+row.message_id)= row.subject
 							td= row.time
 					else
 						tr