From b26157e6e30742980f9585b64fd4b4eade080bf4 Mon Sep 17 00:00:00 2001
From: Michael <heluecht@pirati.ca>
Date: Mon, 1 Feb 2021 20:11:25 +0000
Subject: [PATCH] Update function for "post-user-notification"

---
 database.sql                  | 23 ++++++++++++++++++-----
 include/api.php               |  9 ++++-----
 static/dbstructure.config.php |  2 +-
 update.php                    | 14 ++++++++++++++
 4 files changed, 37 insertions(+), 11 deletions(-)

diff --git a/database.sql b/database.sql
index 9fab6668d8..a1e5b00d92 100644
--- a/database.sql
+++ b/database.sql
@@ -709,17 +709,17 @@ CREATE TABLE IF NOT EXISTS `item` (
 	`deleted` boolean NOT NULL DEFAULT '0' COMMENT 'item has been deleted',
 	`uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner id which owns this copy of the item',
 	`contact-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'contact.id',
-	`wall` boolean NOT NULL DEFAULT '0' COMMENT 'This item was posted to the wall of uid',
+	`unseen` boolean NOT NULL DEFAULT '1' COMMENT 'item has not been seen',
 	`origin` boolean NOT NULL DEFAULT '0' COMMENT 'item originated at this site',
-	`pubmail` boolean NOT NULL DEFAULT '0' COMMENT '',
+	`psid` int unsigned COMMENT 'ID of the permission set of this post',
 	`starred` boolean NOT NULL DEFAULT '0' COMMENT 'item has been favourited',
-	`unseen` boolean NOT NULL DEFAULT '1' COMMENT 'item has not been seen',
-	`mention` boolean NOT NULL DEFAULT '0' COMMENT 'The owner of this item was mentioned in it',
+	`wall` boolean NOT NULL DEFAULT '0' COMMENT 'This item was posted to the wall of uid',
+	`pubmail` boolean NOT NULL DEFAULT '0' COMMENT '',
 	`forum_mode` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '',
-	`psid` int unsigned COMMENT 'ID of the permission set of this post',
 	`event-id` int unsigned COMMENT 'Used to link to the event.id',
 	`type` varchar(20) COMMENT '',
 	`bookmark` boolean COMMENT '',
+	`mention` boolean NOT NULL DEFAULT '0' COMMENT 'The owner of this item was mentioned in it',
 	`resource-id` varchar(32) COMMENT 'Deprecated',
 	`uri-hash` varchar(80) COMMENT 'Deprecated',
 	`iaid` int unsigned COMMENT 'Deprecated',
@@ -1186,6 +1186,19 @@ CREATE TABLE IF NOT EXISTS `post-user` (
 	FOREIGN KEY (`psid`) REFERENCES `permissionset` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
 ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='User specific post data';
 
+--
+-- TABLE post-user-notification
+--
+CREATE TABLE IF NOT EXISTS `post-user-notification` (
+	`uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri',
+	`uid` mediumint unsigned NOT NULL COMMENT 'Owner id which owns this copy of the item',
+	`notification-type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '',
+	 PRIMARY KEY(`uid`,`uri-id`),
+	 INDEX `uri-id` (`uri-id`),
+	FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
+	FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
+) DEFAULT COLLATE utf8mb4_general_ci COMMENT='User post notifications';
+
 --
 -- TABLE process
 --
diff --git a/include/api.php b/include/api.php
index 8543eef9e7..789a13d866 100644
--- a/include/api.php
+++ b/include/api.php
@@ -2170,16 +2170,15 @@ function api_statuses_mentions($type)
 
 	$start = max(0, ($page - 1) * $count);
 
-	$query = "`gravity` IN (?, ?) AND `uri-id` IN (SELECT `uri-id` FROM `post-user`
-		WHERE (`hidden` IS NULL OR NOT `hidden`) AND
-			`uid` = ? AND `notification-type` & ? != 0)
-			AND `id` > ?";
+	$query = "`gravity` IN (?, ?) AND `uri-id` IN
+		(SELECT `uri-id` FROM `post-user-notification` WHERE `uid` = ? AND `notification-type` & ? != 0 ORDER BY `uri-id`)
+		AND (`uid` = 0 OR (`uid` = ? AND NOT `global`)) AND `id` > ?";
 
 	$condition = [GRAVITY_PARENT, GRAVITY_COMMENT, api_user(),
 		UserItem::NOTIF_EXPLICIT_TAGGED | UserItem::NOTIF_IMPLICIT_TAGGED |
 		UserItem::NOTIF_THREAD_COMMENT | UserItem::NOTIF_DIRECT_COMMENT |
 		UserItem::NOTIF_DIRECT_THREAD_COMMENT,
-		$since_id];
+		api_user(), $since_id];
 
 	if ($max_id > 0) {
 		$query .= " AND `id` <= ?";
diff --git a/static/dbstructure.config.php b/static/dbstructure.config.php
index 1e4bd602cf..776d24486f 100644
--- a/static/dbstructure.config.php
+++ b/static/dbstructure.config.php
@@ -1237,7 +1237,7 @@ return [
 		],
 	],
 	"post-user-notification" => [
-		"comment" => "User specific post data",
+		"comment" => "User post notifications",
 		"fields" => [
 			"uri-id" => ["type" => "int unsigned", "not null" => "1", "primary" => "1", "foreign" => ["item-uri" => "id"], "comment" => "Id of the item-uri table entry that contains the item uri"],
 			"uid" => ["type" => "mediumint unsigned", "not null" => "1", "primary" => "1", "foreign" => ["user" => "uid"], "comment" => "Owner id which owns this copy of the item"],
diff --git a/update.php b/update.php
index 05064d8e04..698d043a3c 100644
--- a/update.php
+++ b/update.php
@@ -727,5 +727,19 @@ function update_1397()
 		FROM `thread` LEFT JOIN `user-item` ON `user-item`.`iid` = `thread`.`iid`")) {
 		return Update::FAILED;
 	}
+
+	if (!DBA::e("INSERT INTO `post-user-notification`(`uri-id`, `uid`, `notification-type`)
+		SELECT `uri-id`, `user-item`.`uid`, `notification-type` FROM `user-item`
+			INNER JOIN `item` ON `item`.`id` = `user-item`.`iid` WHERE `notification-type` != 0
+		ON DUPLICATE KEY UPDATE `notification-type` = `user-item`.`notification-type`")) {
+		return Update::FAILED;
+	}
+
+	if (!DBA::e("INSERT IGNORE INTO `post-user-notification`(`uri-id`, `uid`, `notification-type`)
+		SELECT `uri-id`, `uid`, `notification-type` FROM `post-user` WHERE `notification-type` != 0
+		ON DUPLICATE KEY UPDATE `uri-id` = `post-user`.`uri-id`, `uid` = `post-user`.`uid`, `notification-type` = `post-user`.`notification-type`")) {
+		return Update::FAILED;
+	}
+
 	return Update::SUCCESS;
 }
-- 
2.39.5