From 05c560470887c86a30c82360d7be5caf0de1baa4 Mon Sep 17 00:00:00 2001
From: Mischa POSLAWSKY <perl@shiar.org>
Date: Tue, 21 Sep 2021 19:13:07 +0200
Subject: [PATCH] issue: store main description as comment row

Convert issue body to mandatory reply (recognised by oldest date):

	INSERT INTO comments
		SELECT concat(page, '/', id), body, created, author FROM issues;
	CREATE OR REPLACE VIEW messages ...;
---
 database.inc.php    |  2 +-
 issue/index.php     | 19 +++++++++++++++----
 widget/comments.sql |  3 ---
 widget/reply.php    |  5 +++++
 4 files changed, 21 insertions(+), 8 deletions(-)

diff --git a/database.inc.php b/database.inc.php
index aaf2023..12a82eb 100644
--- a/database.inc.php
+++ b/database.inc.php
@@ -42,7 +42,7 @@ class DB
 			foreach ($row as $col => $val) {
 				$cols[] = $this->_value($val, $params);
 			}
-			$sql = sprintf('INSERT INTO %s (%s) VALUES (%s)',
+			$sql = sprintf('INSERT INTO %s (%s) VALUES (%s) RETURNING id',
 				'"'.$table.'"',
 				implode(', ', array_keys($row)),
 				implode(', ', $cols)
diff --git a/issue/index.php b/issue/index.php
index 0c18142..a4b1314 100644
--- a/issue/index.php
+++ b/issue/index.php
@@ -14,7 +14,6 @@ if ($id and ctype_digit($id)) {
 	$replies = $Page->widget('reply');  # handle updates
 
 	$Page->title .= ': '.htmlspecialchars($Issue->subject);
-	$Page->teaser = $Issue->body;
 	$Page->body = $replies;  # find image
 	if ($Page->api) return;
 
@@ -36,7 +35,6 @@ if ($id and ctype_digit($id)) {
 	print "</dl></aside>\n\n";
 
 	print '<div>';
-	print $Issue->body;
 	print $replies;
 	print "</div>\n";
 	return;
@@ -51,17 +49,30 @@ if ($_POST and isset($_POST['subject'])) {
 		$query = $Db->set('issues', [
 			'page'    => $Page->handler,
 			'subject' => $_POST['subject'],
-			'body'    => messagehtml($_POST['body']),
+			'link'    => preg_replace('/\b(?:de|het|een)\s+|\W+/', '-', strtolower($_POST['subject'])),
 			'author'  => $User->login,
 		]);
 		if (!$query->rowCount()) {
 			throw new Exception('Issue niet opgeslagen.');
 		}
+		$row = $query->fetch();
+		if (!$row->id) {
+			throw new Exception('Issue niet goed opgeslagen.');
+		}
+		$query = $Db->set('comments', [
+			'page'    => "{$Page->handler}/{$row->id}",
+			'raw'     => $_POST['body'],
+			'message' => messagehtml($_POST['body']),
+			'author'  => $User->login,
+		]);
+		if (!$query->rowCount()) {
+			throw new Exception('Issueinhoud niet opgeslagen.');
+		}
 		$_POST = [];
 }
 
 $subsql = "SELECT count(*) FROM comments WHERE page=i.page||'/'||i.id";
-$cols = "*, ($subsql AND message IS NOT NULL) AS replycount";
+$cols = "*, ($subsql AND message IS NOT NULL) - 1 AS replycount";
 $cols .= ", ($subsql AND message ~ '<img') AS imagecount";
 $sql = "SELECT $cols FROM issues i WHERE page = ?";
 if (isset($_GET['open'])) {
diff --git a/widget/comments.sql b/widget/comments.sql
index a152079..b805a2a 100644
--- a/widget/comments.sql
+++ b/widget/comments.sql
@@ -2,7 +2,6 @@ CREATE TABLE issues (
 	page       text        NOT NULL DEFAULT 'issue',
 	link       text,
 	subject    text,
-	body       text,
 	created    timestamptz          DEFAULT now(),
 	closed     timestamptz,
 	updated    timestamptz NOT NULL DEFAULT now(),
@@ -31,6 +30,4 @@ CREATE TABLE journal (
 
 CREATE OR REPLACE VIEW messages AS (
 	SELECT *, regexp_replace(page, '.*/', '')::int issue FROM comments
-		UNION ALL
-	SELECT concat(page,'/',id), body, created, author, NULL, id FROM issues
 );
diff --git a/widget/reply.php b/widget/reply.php
index 685a611..66e9587 100644
--- a/widget/reply.php
+++ b/widget/reply.php
@@ -88,6 +88,11 @@ if ($_POST) {
 $cols = '*, (SELECT json_agg(journal.*) FROM journal WHERE comment_id = comments.id) AS journal';
 $query = $Db->query("SELECT $cols FROM comments WHERE page = ? ORDER BY created", [$Page->link]);
 
+if ($row = $query->fetch()) {
+	print $row->message;
+	$Page->teaser = $row->raw;
+}
+
 print '<ul class="replies">';
 
 $imagecount = 0;
-- 
2.30.0