From c824b14c0f6fb372ca4e72fe244b3aade6c29541 Mon Sep 17 00:00:00 2001
From: Mischa POSLAWSKY <perl@shiar.org>
Date: Sun, 20 Oct 2019 06:07:07 +0200
Subject: [PATCH] issue: order by last update, changed on reply

Initial value determined by:

	UPDATE issues SET updated = greatest(created, closed, (
		SELECT max(created) FROM comments WHERE page = issues.link
	));
---
 issue/index.php     | 4 ++--
 widget/comments.sql | 1 +
 widget/reply.php    | 6 ++++++
 3 files changed, 9 insertions(+), 2 deletions(-)

diff --git a/issue/index.php b/issue/index.php
index d8f9c26..2c50624 100644
--- a/issue/index.php
+++ b/issue/index.php
@@ -39,7 +39,7 @@ if ($_POST) {
 		$_POST = [];
 }
 
-$query = $Db->query('SELECT * FROM issues ORDER BY created DESC');
+$query = $Db->query('SELECT * FROM issues ORDER BY updated DESC');
 
 ob_start();
 print '<ul>';
@@ -48,7 +48,7 @@ while ($row = $query->fetch()) {
 		"/$Page/{$row->id}/{$row->link}",
 		sprintf($row->closed ? '<strike>%s</strike>' : '%s',
 			htmlspecialchars($row->subject)),
-		showdate(array_slice(preg_split('/\D/', $row->created), 0, 3))
+		showdate(array_slice(preg_split('/\D/', $row->updated), 0, 3))
 	);
 	print "</li>\n";
 }
diff --git a/widget/comments.sql b/widget/comments.sql
index 5e33716..924c136 100644
--- a/widget/comments.sql
+++ b/widget/comments.sql
@@ -5,6 +5,7 @@ CREATE TABLE issues (
 	body       text,
 	created    timestamptz          DEFAULT now(),
 	closed     timestamptz          DEFAULT now(),
+	updated    timestamptz NOT NULL DEFAULT now(),
 	author     text,
 	id         serial      NOT NULL PRIMARY KEY
 );
diff --git a/widget/reply.php b/widget/reply.php
index 2bc9014..abb364c 100644
--- a/widget/reply.php
+++ b/widget/reply.php
@@ -16,6 +16,12 @@ if ($_POST) {
 		if (!$query->rowCount()) {
 			throw new Exception('Fout bij opslaan');
 		}
+		if (@list ($cat, $issue) = explode('/', $Page) and ctype_digit($issue)) {
+			$Db->query(
+				'UPDATE issues SET updated = now() WHERE page = ? AND id = ?',
+				[$cat, $issue]
+			);
+		}
 		$_POST['reply'] = NULL;
 	}
 	catch (Exception $e) {
-- 
2.30.0