Merge pull request #7077 from nextcloud/feature/noid/talk-attachments-table

Improve API for media tab to be able to filter results

19 files changed, 674 insertions, 35 deletions

diff --git a/appinfo/info.xml b/appinfo/info.xml
index c644d7811..f9afae1ff 100644
--- a/appinfo/info.xml
+++ b/appinfo/info.xml
@@ -16,7 +16,7 @@ And in the works for the [coming versions](https://github.com/nextcloud/spreed/m
 
 	]]></description>
 
-	<version>14.0.0-dev.2</version>
+	<version>14.0.0-dev.3</version>
 	<licence>agpl</licence>
 
 	<author>Aleksandra Lazarević</author>
diff --git a/appinfo/routes/routesChatController.php b/appinfo/routes/routesChatController.php
index 55f31b981..b9670c911 100644
--- a/appinfo/routes/routesChatController.php
+++ b/appinfo/routes/routesChatController.php
@@ -52,6 +52,8 @@ return [
 		['name' => 'Chat#mentions', 'url' => '/api/{apiVersion}/chat/{token}/mentions', 'verb' => 'GET', 'requirements' => $requirements],
 		/** @see \OCA\Talk\Controller\ChatController::shareObjectToChat() */
 		['name' => 'Chat#shareObjectToChat', 'url' => '/api/{apiVersion}/chat/{token}/share', 'verb' => 'POST', 'requirements' => $requirements],
+		/** @see \OCA\Talk\Controller\ChatController::getObjectsSharedInRoomOverview() */
+		['name' => 'Chat#getObjectsSharedInRoomOverview', 'url' => '/api/{apiVersion}/chat/{token}/share/overview', 'verb' => 'GET', 'requirements' => $requirements],
 		/** @see \OCA\Talk\Controller\ChatController::getObjectsSharedInRoom() */
 		['name' => 'Chat#getObjectsSharedInRoom', 'url' => '/api/{apiVersion}/chat/{token}/share', 'verb' => 'GET', 'requirements' => $requirements],
 	],
diff --git a/docs/chat.md b/docs/chat.md
index 4dfe86788..43ee4b4e3 100644
--- a/docs/chat.md
+++ b/docs/chat.md
@@ -154,7 +154,29 @@ See [OCP\RichObjectStrings\Definitions](https://github.com/nextcloud/server/blob
 
 * Response: [See official OCS Share API docs](https://docs.nextcloud.com/server/latest/developer_manual/client_apis/OCS/ocs-share-api.html?highlight=sharing#create-a-new-share)
 
-## List media shared in a chat
+## List overview of items shared into a chat
+
+* Required capability: `rich-object-list-media`
+* Method: `GET`
+* Endpoint: `/chat/{token}/share/overview`
+* Data:
+
+  field | type | Description
+  ---|---|---
+  `limit` | int  | Number of chat messages with shares you want to get
+
+* Response:
+    - Note: if a file was shared multiple times it will be returned multiple times
+    - Status code:
+        + `200 OK`
+        + `404 Not Found` When the conversation could not be found for the participant
+        + `412 Precondition Failed` When the lobby is active and the user is not a moderator
+
+    - Data:
+        + An array per item type
+            - Array of messages as defined in [Receive chat messages of a conversation](#receive-chat-messages-of-a-conversation)
+
+## List items of type shared in a chat
 
 * Required capability: `rich-object-list-media`
 * Method: `GET`
@@ -163,8 +185,9 @@ See [OCP\RichObjectStrings\Definitions](https://github.com/nextcloud/server/blob
 
   field | type | Description
   ---|---|---
-  `lastKnownMessageId` | int | Serves as an offset for the query. The lastKnownMessageId for the next page is available in the `X-Chat-Last-Given` header.
-  `limit` | int | Number of chat messages with shares you want to get
+  `objectType` | string | One of the [Constants - Shared item types](constants.md#shared-item-types)
+  `lastKnownMessageId` | int  | Serves as an offset for the query. The lastKnownMessageId for the next page is available in the `X-Chat-Last-Given` header.
+  `limit` | int  | Number of chat messages with shares you want to get
 
 * Response:
     - Note: if a file was shared multiple times it will be returned multiple times
diff --git a/docs/constants.md b/docs/constants.md
index 11de91906..108bfb668 100644
--- a/docs/constants.md
+++ b/docs/constants.md
@@ -82,6 +82,17 @@ title: Constants
 * `bots` - Used by commands (actor-id is the used `/command`) and the changelog conversation (actor-id is `changelog`)
 * `bridged` - Users whose messages are bridged in by the [Matterbridge integration](matterbridge.md)
 
+## Chat
+
+### Shared item types
+* `audio` - Shared audio file
+* `deckcard` - Shared deck card
+* `file` - Shared files not falling into any other category
+* `location` - Shared geo location
+* `media` - Shared files with mimetype starting with image or video
+* `other` - Shared objects not falling into any other category
+* `voice` - Voice messages
+
 ## Signaling modes
 * `internal` - No external signaling server is used
 * `external` - A single external signaling server is used
diff --git a/lib/Chat/ChatManager.php b/lib/Chat/ChatManager.php
index 7340f0a5f..a83645ca2 100644
--- a/lib/Chat/ChatManager.php
+++ b/lib/Chat/ChatManager.php
@@ -31,6 +31,7 @@ use OCA\Talk\Events\ChatParticipantEvent;
 use OCA\Talk\Model\Attendee;
 use OCA\Talk\Participant;
 use OCA\Talk\Room;
+use OCA\Talk\Service\AttachmentService;
 use OCA\Talk\Service\ParticipantService;
 use OCA\Talk\Share\RoomShareProvider;
 use OCP\AppFramework\Utility\ITimeFactory;
@@ -91,6 +92,7 @@ class ChatManager {
 	protected $cache;
 	/** @var ICache */
 	protected $unreadCountCache;
+	protected AttachmentService $attachmentService;
 
 	public function __construct(CommentsManager $commentsManager,
 								IEventDispatcher $dispatcher,
@@ -101,7 +103,8 @@ class ChatManager {
 								ParticipantService $participantService,
 								Notifier $notifier,
 								ICacheFactory $cacheFactory,
-								ITimeFactory $timeFactory) {
+								ITimeFactory $timeFactory,
+								AttachmentService $attachmentService) {
 		$this->commentsManager = $commentsManager;
 		$this->dispatcher = $dispatcher;
 		$this->connection = $connection;
@@ -113,6 +116,7 @@ class ChatManager {
 		$this->cache = $cacheFactory->createDistributed('talk/lastmsgid');
 		$this->unreadCountCache = $cacheFactory->createDistributed('talk/unreadcount');
 		$this->timeFactory = $timeFactory;
+		$this->attachmentService = $attachmentService;
 	}
 
 	/**
@@ -186,6 +190,10 @@ class ChatManager {
 		}
 		$this->cache->remove($chat->getToken());
 
+		if ($messageType === 'object_shared' || $messageType === 'file_shared') {
+			$this->attachmentService->createAttachmentEntry($chat, $comment, $messageType, $messageDecoded['parameters'] ?? []);
+		}
+
 		return $comment;
 	}
 
@@ -348,6 +356,8 @@ class ChatManager {
 		$comment->setVerb('comment_deleted');
 		$this->commentsManager->save($comment);
 
+		$this->attachmentService->deleteAttachmentByMessageId((int) $comment->getId());
+
 		return $this->addSystemMessage(
 			$chat,
 			$participant->getAttendee()->getActorType(),
@@ -611,25 +621,26 @@ class ChatManager {
 		$this->shareProvider->deleteInRoom($chat->getToken());
 
 		$this->notifier->removePendingNotificationsForRoom($chat);
+
+		$this->attachmentService->deleteAttachmentsForRoom($chat);
 	}
 
 	/**
 	 * Search for comments with a given content
 	 *
 	 * @param Room $chat
-	 * @param int $offset
-	 * @param int $limit
+	 * @param int[] $commentIds
 	 * @return IComment[]
 	 */
-	public function getSharedObjectMessages(Room $chat, int $offset, int $limit): array {
-		return $this->commentsManager->getCommentsWithVerbForObjectSinceComment(
-			'chat',
-			(string) $chat->getId(),
-			['object_shared'],
-			$offset,
-			'desc',
-			$limit
-		);
+	public function getMessagesById(Room $chat, array $commentIds): array {
+		$comments = $this->commentsManager->getCommentsById($commentIds);
+
+		$comments = array_filter($comments, static function (IComment $comment) use ($chat) {
+			return $comment->getObjectType() === 'chat'
+				&& (int)$comment->getObjectId() === $chat->getId();
+		});
+
+		return $comments;
 	}
 
 	/**
diff --git a/lib/Chat/CommentsManager.php b/lib/Chat/CommentsManager.php
index 12a758e86..44a97e0ef 100644
--- a/lib/Chat/CommentsManager.php
+++ b/lib/Chat/CommentsManager.php
@@ -26,6 +26,7 @@ namespace OCA\Talk\Chat;
 use OC\Comments\Comment;
 use OC\Comments\Manager;
 use OCP\Comments\IComment;
+use OCP\DB\QueryBuilder\IQueryBuilder;
 
 class CommentsManager extends Manager {
 	/**
@@ -39,4 +40,27 @@ class CommentsManager extends Manager {
 		$comment->setMessage($message, ChatManager::MAX_CHAT_LENGTH);
 		return $comment;
 	}
+
+	/**
+	 * @param string[] $ids
+	 * @return IComment[]
+	 * @throws \OCP\DB\Exception
+	 */
+	public function getCommentsById(array $ids): array {
+		$commentIds = array_map('intval', $ids);
+
+		$query = $this->dbConn->getQueryBuilder();
+		$query->select('*')
+			->from('comments')
+			->where($query->expr()->in('id', $query->createNamedParameter($commentIds, IQueryBuilder::PARAM_INT_ARRAY)));
+
+		$comments = [];
+		$result = $query->execute();
+		while ($row = $result->fetch()) {
+			$comments[(int) $row['id']] = $this->getCommentFromData($row);
+		}
+		$result->closeCursor();
+
+		return $comments;
+	}
 }
diff --git a/lib/Chat/SystemMessage/Listener.php b/lib/Chat/SystemMessage/Listener.php
index 2308b061b..adf28a7f8 100644
--- a/lib/Chat/SystemMessage/Listener.php
+++ b/lib/Chat/SystemMessage/Listener.php
@@ -350,6 +350,7 @@ class Listener implements IEventListener {
 					unset($metaData['messageType']);
 				}
 			}
+			$metaData['mimeType'] = $share->getNode()->getMimeType();
 
 			$listener->sendSystemMessage($room, 'file_shared', ['share' => $share->getId(), 'metaData' => $metaData]);
 		};
diff --git a/lib/Controller/ChatController.php b/lib/Controller/ChatController.php
index 185051aac..2233f183d 100644
--- a/lib/Controller/ChatController.php
+++ b/lib/Controller/ChatController.php
@@ -30,11 +30,13 @@ use OCA\Talk\Chat\ChatManager;
 use OCA\Talk\Chat\MessageParser;
 use OCA\Talk\GuestManager;
 use OCA\Talk\MatterbridgeManager;
+use OCA\Talk\Model\Attachment;
 use OCA\Talk\Model\Attendee;
 use OCA\Talk\Model\Message;
 use OCA\Talk\Model\Session;
 use OCA\Talk\Participant;
 use OCA\Talk\Room;
+use OCA\Talk\Service\AttachmentService;
 use OCA\Talk\Service\ParticipantService;
 use OCA\Talk\Service\SessionService;
 use OCP\App\IAppManager;
@@ -78,6 +80,8 @@ class ChatController extends AEnvironmentAwareController {
 	/** @var SessionService */
 	private $sessionService;
 
+	protected AttachmentService $attachmentService;
+
 	/** @var GuestManager */
 	private $guestManager;
 
@@ -125,6 +129,7 @@ class ChatController extends AEnvironmentAwareController {
 								ChatManager $chatManager,
 								ParticipantService $participantService,
 								SessionService $sessionService,
+								AttachmentService $attachmentService,
 								GuestManager $guestManager,
 								MessageParser $messageParser,
 								IManager $autoCompleteManager,
@@ -145,6 +150,7 @@ class ChatController extends AEnvironmentAwareController {
 		$this->chatManager = $chatManager;
 		$this->participantService = $participantService;
 		$this->sessionService = $sessionService;
+		$this->attachmentService = $attachmentService;
 		$this->guestManager = $guestManager;
 		$this->messageParser = $messageParser;
 		$this->autoCompleteManager = $autoCompleteManager;
@@ -710,17 +716,30 @@ class ChatController extends AEnvironmentAwareController {
 	 * @RequireReadWriteConversation
 	 * @RequireModeratorOrNoLobby
 	 *
-	 * @param int $lastKnownMessageId
 	 * @param int $limit
 	 * @return DataResponse
 	 */
-	public function getObjectsSharedInRoom(int $lastKnownMessageId = 0, int $limit = 100): DataResponse {
-		$offset = max(0, $lastKnownMessageId);
-		$limit = min(200, $limit);
-
-		$comments = $this->chatManager->getSharedObjectMessages($this->room, $offset, $limit);
+	public function getObjectsSharedInRoomOverview(int $limit = 7): DataResponse {
+		$limit = min(20, $limit);
+
+		$objectTypes = [
+			Attachment::TYPE_AUDIO,
+			Attachment::TYPE_DECK_CARD,
+			Attachment::TYPE_FILE,
+			Attachment::TYPE_LOCATION,
+			Attachment::TYPE_MEDIA,
+			Attachment::TYPE_OTHER,
+			Attachment::TYPE_VOICE,
+		];
 
 		$messages = [];
+		$messageIdsByType = [];
+		foreach ($objectTypes as $objectType) {
+			$attachments = $this->attachmentService->getAttachmentsByType($this->room, $objectType, 0, $limit);
+			$messageIdsByType[$objectType] = array_map(static fn (Attachment $attachment): int => $attachment->getMessageId(), $attachments);
+		}
+		$comments = $this->chatManager->getMessagesById($this->room, array_merge(...$messageIdsByType));
+
 		foreach ($comments as $comment) {
 			$message = $this->messageParser->createMessage($this->room, $this->participant, $comment, $this->l);
 			$this->messageParser->parseMessage($message);
@@ -729,19 +748,69 @@ class ChatController extends AEnvironmentAwareController {
 				continue;
 			}
 
-			$messages[] = $message->toArray();
+			$messages[(int) $comment->getId()] = $message->toArray();
+		}
+
+		$messagesByType = [];
+		foreach ($objectTypes as $objectType) {
+			$messagesByType[$objectType] = [];
+
+			foreach ($messageIdsByType[$objectType] as $messageId) {
+				$messagesByType[$objectType][] = $messages[$messageId];
+			}
 		}
 
+		return new DataResponse($messagesByType, Http::STATUS_OK);
+	}
+
+	/**
+	 * @PublicPage
+	 * @RequireParticipant
+	 * @RequireReadWriteConversation
+	 * @RequireModeratorOrNoLobby
+	 *
+	 * @param string $objectType
+	 * @param int $lastKnownMessageId
+	 * @param int $limit
+	 * @return DataResponse
+	 */
+	public function getObjectsSharedInRoom(string $objectType, int $lastKnownMessageId = 0, int $limit = 100): DataResponse {
+		$offset = max(0, $lastKnownMessageId);
+		$limit = min(200, $limit);
+
+		$attachments = $this->attachmentService->getAttachmentsByType($this->room, $objectType, $offset, $limit);
+		$messageIds = array_map(static fn (Attachment $attachment): int => $attachment->getMessageId(), $attachments);
+
+		$messages = $this->getMessagesForRoom($this->room, $messageIds);
+
 		$response = new DataResponse($messages, Http::STATUS_OK);
 
-		$newLastKnown = end($comments);
-		if ($newLastKnown instanceof IComment) {
-			$response->addHeader('X-Chat-Last-Given', $newLastKnown->getId());
+		if (!empty($messages)) {
+			$newLastKnown = min(array_keys($messages));
+			$response->addHeader('X-Chat-Last-Given', $newLastKnown);
 		}
 
 		return $response;
 	}
 
+	protected function getMessagesForRoom(Room $room, array $messageIds): array {
+		$comments = $this->chatManager->getMessagesById($room, $messageIds);
+
+		$messages = [];
+		foreach ($comments as $comment) {
+			$message = $this->messageParser->createMessage($room, $this->participant, $comment, $this->l);
+			$this->messageParser->parseMessage($message);
+
+			if (!$message->getVisibility()) {
+				continue;
+			}
+
+			$messages[(int) $comment->getId()] = $message->toArray();
+		}
+
+		return $messages;
+	}
+
 	/**
 	 * @PublicPage
 	 * @RequireParticipant
diff --git a/lib/Migration/Version14000Date20211203132513.php b/lib/Migration/Version14000Date20211203132513.php
index 2c633e024..f2c68131e 100644
--- a/lib/Migration/Version14000Date20211203132513.php
+++ b/lib/Migration/Version14000Date20211203132513.php
@@ -1,6 +1,27 @@
 <?php
 
 declare(strict_types=1);
+/**
+ * @copyright Copyright (c) 2021, Joas Schilling <coding@schilljs.com>
+ *
+ * @author Joas Schilling <coding@schilljs.com>
+ *
+ * @license GNU AGPL version 3 or any later version
+ *
+ * This program is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation, either version 3 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU Affero General Public License for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program.  If not, see <http://www.gnu.org/licenses/>.
+ *
+ */
 
 namespace OCA\Talk\Migration;
 
diff --git a/lib/Migration/Version14000Date20220328153054.php b/lib/Migration/Version14000Date20220328153054.php
index a123108ba..242993653 100644
--- a/lib/Migration/Version14000Date20220328153054.php
+++ b/lib/Migration/Version14000Date20220328153054.php
@@ -39,7 +39,7 @@ class Version14000Date20220328153054 extends SimpleMigrationStep {
 	 * @return null|ISchemaWrapper
 	 */
 	public function changeSchema(IOutput $output, Closure $schemaClosure, array $options): ?ISchemaWrapper {
-		/** @var ISchemaWrapper */
+		/** @var ISchemaWrapper $schema */
 		$schema = $schemaClosure();
 
 		$table = $schema->getTable('talk_attendees');
diff --git a/lib/Migration/Version14000Date20220330141646.php b/lib/Migration/Version14000Date20220330141646.php
new file mode 100644
index 000000000..4277b878d
--- /dev/null
+++ b/lib/Migration/Version14000Date20220330141646.php
@@ -0,0 +1,198 @@
+<?php
+
+declare(strict_types=1);
+/**
+ * @copyright Copyright (c) 2022 Joas Schilling <coding@schilljs.com>
+ *
+ * @author Joas Schilling <coding@schilljs.com>
+ *
+ * @license GNU AGPL version 3 or any later version
+ *
+ * This program is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation, either version 3 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU Affero General Public License for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program.  If not, see <http://www.gnu.org/licenses/>.
+ *
+ */
+
+namespace OCA\Talk\Migration;
+
+use Closure;
+use Doctrine\DBAL\Types\Types;
+use OCA\Talk\Model\Attachment;
+use OCP\DB\ISchemaWrapper;
+use OCP\DB\QueryBuilder\IQueryBuilder;
+use OCP\IDBConnection;
+use OCP\Migration\IOutput;
+use OCP\Migration\SimpleMigrationStep;
+
+class Version14000Date20220330141646 extends SimpleMigrationStep {
+	protected IDBConnection $connection;
+
+	public function __construct(IDBConnection $connection) {
+		$this->connection = $connection;
+	}
+
+	/**
+	 * @param IOutput $output
+	 * @param Closure $schemaClosure The `\Closure` returns a `ISchemaWrapper`
+	 * @param array $options
+	 * @return null|ISchemaWrapper
+	 */
+	public function changeSchema(IOutput $output, Closure $schemaClosure, array $options): ?ISchemaWrapper {
+		/** @var ISchemaWrapper $schema */
+		$schema = $schemaClosure();
+
+		if (!$schema->hasTable('talk_attachments')) {
+			$table = $schema->createTable('talk_attachments');
+			$table->addColumn('id', Types::BIGINT, [
+				'autoincrement' => true,
+				'notnull' => true,
+			]);
+			$table->addColumn('room_id', Types::BIGINT, [
+				'notnull' => true,
+				'unsigned' => true,
+			]);
+			$table->addColumn('message_id', Types::BIGINT, [
+				'notnull' => true,
+				'unsigned' => true,
+			]);
+			$table->addColumn('message_time', Types::BIGINT, [
+				'notnull' => true,
+				'unsigned' => true,
+			]);
+			$table->addColumn('object_type', Types::STRING, [
+				'notnull' => true,
+				'length' => 64,
+			]);
+			$table->addColumn('actor_type', Types::STRING, [
+				'notnull' => true,
+				'length' => 64,
+			]);
+			$table->addColumn('actor_id', Types::STRING, [
+				'notnull' => true,
+				'length' => 64,
+			]);
+
+			$table->setPrimaryKey(['id']);
+
+			$table->addIndex(['room_id', 'object_type'], 'objects_in_room');
+
+			return $schema;
+		}
+		return null;
+	}
+
+	/**
+	 * @param IOutput $output
+	 * @param Closure $schemaClosure The `\Closure` returns a `ISchemaWrapper`
+	 * @param array $options
+	 */
+	public function postSchemaChange(IOutput $output, Closure $schemaClosure, array $options): void {
+		// FIXME need to loop over all chat messages :(
+
+		$insert = $this->connection->getQueryBuilder();
+		$insert->insert('talk_attachments')
+			->setValue('room_id', $insert->createParameter('room_id'))
+			->setValue('message_id', $insert->createParameter('message_id'))
+			->setValue('message_time', $insert->createParameter('message_time'))
+			->setValue('object_type', $insert->createParameter('object_type'))
+			->setValue('actor_type', $insert->createParameter('actor_type'))
+			->setValue('actor_id', $insert->createParameter('actor_id'));
+
+		$offset = -1;
+		$select = $this->connection->getQueryBuilder();
+		$select->select('id', 'creation_timestamp', 'object_id', 'actor_type', 'actor_id', 'message')
+			->from('comments')
+			->where($select->expr()->eq('object_type', $select->createParameter('object_type')))
+			->andWhere($select->expr()->eq('verb', $select->createParameter('verb')))
+			->andWhere($select->expr()->gt('id', $select->createParameter('offset')))
+			->orderBy('id', 'ASC')
+			->setMaxResults(1000);
+
+		$select->setParameter('object_type', 'chat')
+			->setParameter('verb', 'object_shared');
+
+		while ($offset !== 0) {
+			$offset = $this->chunkedWriting($insert, $select, max($offset, 0));
+		}
+	}
+
+	protected function chunkedWriting(IQueryBuilder $insert, IQueryBuilder $select, int $offset): int {
+		$select->setParameter('offset', $offset);
+
+		$attachments = [];
+		$result = $select->executeQuery();
+		while ($row = $result->fetch()) {
+			$attachment = [
+				'room_id' => (int) $row['object_id'],
+				'message_id' => (int) $row['id'],
+				'actor_type' => $row['actor_type'],
+				'actor_id' => $row['actor_id'],
+			];
+
+			$datetime = new \DateTime($row['creation_timestamp']);
+			$attachment['message_time'] = $datetime->getTimestamp();
+
+			$message = json_decode($row['message'], true);
+			$messageType = $message['message'] ?? '';
+			$parameters = $message['parameters'] ?? [];
+
+			if ($messageType === 'object_shared') {
+				$objectType = $parameters['objectType'] ?? '';
+				if ($objectType === 'geo-location') {
+					$attachment['object_type'] = Attachment::TYPE_LOCATION;
+				} elseif ($objectType === 'deck-card') {
+					$attachment['object_type'] = Attachment::TYPE_DECK_CARD;
+				} else {
+					$attachment['object_type'] = Attachment::TYPE_OTHER;
+				}
+			} else {
+				$messageType = $parameters['metaData']['messageType'] ?? '';
+				$mimetype = $parameters['metaData']['mimeType'] ?? '';
+
+				if ($messageType === 'voice-message') {
+					$attachment['object_type'] = Attachment::TYPE_VOICE;
+				} elseif (str_starts_with($mimetype, 'audio/')) {
+					$attachment['object_type'] = Attachment::TYPE_AUDIO;
+				} elseif (str_starts_with($mimetype, 'image/') || str_starts_with($mimetype, 'video/')) {
+					$attachment['object_type'] = Attachment::TYPE_MEDIA;
+				} else {
+					$attachment['object_type'] = Attachment::TYPE_FILE;
+				}
+			}
+
+			$attachments[] = $attachment;
+		}
+		$result->closeCursor();
+
+		if (empty($attachments)) {
+			return 0;
+		}
+
+		$this->connection->beginTransaction();
+		foreach ($attachments as $attachment) {
+			$insert
+				->setParameter('room_id', $attachment['room_id'], IQueryBuilder::PARAM_INT)
+				->setParameter('message_id', $attachment['message_id'], IQueryBuilder::PARAM_INT)
+				->setParameter('message_time', $attachment['message_time'], IQueryBuilder::PARAM_INT)
+				->setParameter('actor_type', $attachment['actor_type'])
+				->setParameter('actor_id', $attachment['actor_id'])
+				->setParameter('object_type', $attachment['object_type'])
+			;
+
+			$insert->executeStatement();
+		}
+		$this->connection->commit();
+
+		return end($attachments)['message_id'];
+	}
+}
diff --git a/lib/Model/Attachment.php b/lib/Model/Attachment.php
new file mode 100644
index 000000000..a808fa367
--- /dev/null
+++ b/lib/Model/Attachment.php
@@ -0,0 +1,92 @@
+<?php
+
+declare(strict_types=1);
+/**
+ * @copyright Copyright (c) 2022 Joas Schilling <coding@schilljs.com>
+ *
+ * @license GNU AGPL version 3 or any later version
+ *
+ * This program is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation, either version 3 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU Affero General Public License for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program.  If not, see <http://www.gnu.org/licenses/>.
+ *
+ */
+
+namespace OCA\Talk\Model;
+
+use OCP\AppFramework\Db\Entity;
+
+/**
+ * @method void setRoomId(int $roomId)
+ * @method int getRoomId()
+ * @method void setMessageId(int $messageId)
+ * @method int getMessageId()
+ * @method void setMessageTime(int $messageTime)
+ * @method int getMessageTime()
+ * @method void setObjectType(string $objectType)
+ * @method string getObjectType()
+ * @method void setActorType(string $actorType)
+ * @method string getActorType()
+ * @method void setActorId(string $actorId)
+ * @method string getActorId()
+ */
+class Attachment extends Entity {
+	public const TYPE_AUDIO = 'audio';
+	public const TYPE_DECK_CARD = 'deckcard';
+	public const TYPE_FILE = 'file';
+	public const TYPE_LOCATION = 'location';
+	public const TYPE_MEDIA = 'media';
+	public const TYPE_OTHER = 'other';
+	public const TYPE_VOICE = 'voice';
+
+	/** @var int */
+	protected $roomId;
+
+	/** @var int */
+	protected $messageId;
+
+	/** @var int */
+	protected $messageTime;
+
+	/** @var string */
+	protected $objectType;
+
+	/** @var string */
+	protected $actorType;
+
+	/** @var string */
+	protected $actorId;
+
+	public function __construct() {
+		$this->addType('roomId', 'int');
+		$this->addType('messageId', 'int');
+		$this->addType('messageTime', 'int');
+		$this->addType('objectType', 'string');
+		$this->addType('actorType', 'string');
+		$this->addType('actorId', 'string');
+	}
+
+	/**
+	 * @return array
+	 */
+	public function asArray(): array {
+		return [
+			'id' => $this->getId(),
+			'room_id' => $this->getRoomId(),
+			'message_id' => $this->getMessageId(),
+			'message_time' => $this->getMessageTime(),
+			'object_type' => $this->getObjectType(),
+			'actor_type' => $this->getActorType(),
+			'actor_id' => $this->getActorId(),
+		];
+	}
+}
diff --git a/lib/Model/AttachmentMapper.php b/lib/Model/AttachmentMapper.php
new file mode 100644
index 000000000..484239483
--- /dev/null
+++ b/lib/Model/AttachmentMapper.php
@@ -0,0 +1,99 @@
+<?php
+
+declare(strict_types=1);
+/**
+ * @copyright Copyright (c) 2022 Joas Schilling <coding@schilljs.com>
+ *
+ * @license GNU AGPL version 3 or any later version
+ *
+ * This program is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation, either version 3 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU Affero General Public License for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program.  If not, see <http://www.gnu.org/licenses/>.
+ *
+ */
+
+namespace OCA\Talk\Model;
+
+use OCP\AppFramework\Db\QBMapper;
+use OCP\AppFramework\Db\TTransactional;
+use OCP\DB\QueryBuilder\IQueryBuilder;
+use OCP\IDBConnection;
+
+/**
+ * @method Attachment mapRowToEntity(array $row)
+ * @method Attachment findEntity(IQueryBuilder $query)
+ * @method Attachment[] findEntities(IQueryBuilder $query)
+ */
+class AttachmentMapper extends QBMapper {
+	use TTransactional;
+
+	/**
+	 * @param IDBConnection $db
+	 */
+	public function __construct(IDBConnection $db) {
+		parent::__construct($db, 'talk_attachments', Attachment::class);
+	}
+
+	public function createAttachmentFromRow(array $row): Attachment {
+		return $this->mapRowToEntity([
+			'id' => (int) $row['id'],
+			'room_id' => (int) $row['room_id'],
+			'message_id' => (int) $row['message_id'],
+			'message_time' => (int) $row['message_time'],
+			'object_type' => (string) $row['object_type'],
+			'actor_type' => (string) $row['actor_type'],
+			'actor_id' => (string) $row['actor_id'],
+		]);
+	}
+
+	/**
+	 * @param int $roomId
+	 * @param string $objectType
+	 * @param int $offset
+	 * @param int $limit
+	 * @return Attachment[]
+	 * @throws \OCP\DB\Exception
+	 */
+	public function getAttachmentsByType(int $roomId, string $objectType, int $offset, int $limit): array {
+		$query = $this->db->getQueryBuilder();
+		$query->select('*')
+			->from($this->getTableName())
+			->where($query->expr()->eq('room_id', $query->createNamedParameter($roomId, IQueryBuilder::PARAM_INT)))
+			->andWhere($query->expr()->eq('object_type', $query->createNamedParameter($objectType)))
+			->setMaxResults($limit)
+			->orderBy('id', 'DESC');
+
+		if ($offset > 0) {
+			$query->andWhere($query->expr()->lt('message_id', $query->createNamedParameter($offset)));
+		}
+
+		return $this->findEntities($query);
+	}
+
+	public function deleteByMessageId(int $messageId): void {
+		$query = $this->db->getQueryBuilder();
+		$query->delete($this->getTableName())
+			->where($query->expr()->eq('message_id', $query->createNamedParameter($messageId, IQueryBuilder::PARAM_INT)));
+
+		$query->executeStatement();
+	}
+
+	public function deleteByRoomId(int $roomId): void {
+		$query = $this->db->getQueryBuilder();
+		$query->delete($this->getTableName())
+			->where($query->expr()->eq('room_id', $query->createNamedParameter($roomId, IQueryBuilder::PARAM_INT)));
+
+		$this->atomic(static function () use ($query) {
+			$query->executeStatement();
+		}, $this->db);
+	}
+}
diff --git a/lib/Service/AttachmentService.php b/lib/Service/AttachmentService.php
new file mode 100644
index 000000000..cfe3c0525
--- /dev/null
+++ b/lib/Service/AttachmentService.php
@@ -0,0 +1,70 @@
+<?php
+
+namespace OCA\Talk\Service;
+
+use OCA\Talk\Model\Attachment;
+use OCA\Talk\Model\AttachmentMapper;
+use OCA\Talk\Room;
+use OCP\Comments\IComment;
+
+class AttachmentService {
+	public AttachmentMapper $attachmentMapper;
+
+	public function __construct(AttachmentMapper $attachmentMapper) {
+		$this->attachmentMapper = $attachmentMapper;
+	}
+
+	public function createAttachmentEntry(Room $room, IComment $comment, string $messageType, array $parameters): void {
+		$attachment = new Attachment();
+		$attachment->setRoomId($room->getId());
+		$attachment->setActorType($comment->getActorType());
+		$attachment->setActorId($comment->getActorId());
+		$attachment->setMessageId((int) $comment->getId());
+		$attachment->setMessageTime($comment->getCreationDateTime()->getTimestamp());
+
+		if ($messageType === 'object_shared') {
+			$objectType = $parameters['objectType'] ?? '';
+			if ($objectType === 'geo-location') {
+				$attachment->setObjectType(Attachment::TYPE_LOCATION);
+			} elseif ($objectType === 'deck-card') {
+				$attachment->setObjectType(Attachment::TYPE_DECK_CARD);
+			} else {
+				$attachment->setObjectType(Attachment::TYPE_OTHER);
+			}
+		} else {
+			$messageType = $parameters['metaData']['messageType'] ?? '';
+			$mimetype = $parameters['metaData']['mimeType'] ?? '';
+
+			if ($messageType === 'voice-message') {
+				$attachment->setObjectType(Attachment::TYPE_VOICE);
+			} elseif (str_starts_with($mimetype, 'audio/')) {
+				$attachment->setObjectType(Attachment::TYPE_AUDIO);
+			} elseif (str_starts_with($mimetype, 'image/') || str_starts_with($mimetype, 'video/')) {
+				$attachment->setObjectType(Attachment::TYPE_MEDIA);
+			} else {
+				$attachment->setObjectType(Attachment::TYPE_FILE);
+			}
+		}
+
+		$this->attachmentMapper->insert($attachment);
+	}
+
+	/**
+	 * @param Room $room
+	 * @param string $objectType
+	 * @param int $offset
+	 * @param int $limit
+	 * @return Attachment[]
+	 */
+	public function getAttachmentsByType(Room $room, string $objectType, int $offset, int $limit): array {
+		return $this->attachmentMapper->getAttachmentsByType($room->getId(), $objectType, $offset, $limit);
+	}
+
+	public function deleteAttachmentByMessageId(int $messageId): void {
+		$this->attachmentMapper->deleteByMessageId($messageId);
+	}
+
+	public function deleteAttachmentsForRoom(Room $room): void {
+		$this->attachmentMapper->deleteByRoomId($room->getId());
+	}
+}
diff --git a/tests/integration/features/bootstrap/FeatureContext.php b/tests/integration/features/bootstrap/FeatureContext.php
index 1fc24a133..2d78ad37d 100644
--- a/tests/integration/features/bootstrap/FeatureContext.php
+++ b/tests/integration/features/bootstrap/FeatureContext.php
@@ -1554,16 +1554,17 @@ class FeatureContext implements Context, SnippetAcceptingContext {
 	}
 
 	/**
-	 * @Then /^user "([^"]*)" sees the following shared media in room "([^"]*)" with (\d+)(?: \((v1)\))?$/
+	 * @Then /^user "([^"]*)" sees the following shared (media|audio|voice|file|deckcard|location|other) in room "([^"]*)" with (\d+)(?: \((v1)\))?$/
 	 *
 	 * @param string $user
+	 * @param string $objectType
 	 * @param string $identifier
 	 * @param string $statusCode
 	 * @param string $apiVersion
 	 */
-	public function userSeesTheFollowingSharedMediaInRoom($user, $identifier, $statusCode, $apiVersion = 'v1', TableNode $formData = null): void {
+	public function userSeesTheFollowingSharedMediaInRoom($user, $objectType, $identifier, $statusCode, $apiVersion = 'v1', TableNode $formData = null): void {
 		$this->setCurrentUser($user);
-		$this->sendRequest('GET', '/apps/spreed/api/' . $apiVersion . '/chat/' . self::$identifierToToken[$identifier] . '/share');
+		$this->sendRequest('GET', '/apps/spreed/api/' . $apiVersion . '/chat/' . self::$identifierToToken[$identifier] . '/share?objectType=' . $objectType);
 		$this->assertStatusCode($this->response, $statusCode);
 
 		$this->compareDataResponse($formData);
diff --git a/tests/integration/features/chat/rich-object-share.feature b/tests/integration/features/chat/rich-object-share.feature
index 3b81ed7ce..467a72501 100644
--- a/tests/integration/features/chat/rich-object-share.feature
+++ b/tests/integration/features/chat/rich-object-share.feature
@@ -40,8 +40,10 @@ Feature: chat/public
       | roomType | 3 |
       | roomName | room |
     When user "participant1" shares rich-object "call" "R4nd0mT0k3n" '{"name":"Another room","call-type":"group"}' to room "public room" with 201 (v1)
-    And user "participant1" shares "welcome.txt" with room "public room" with OCS 100
-    Then user "participant1" sees the following shared media in room "public room" with 200
+    Then user "participant1" sees the following shared other in room "public room" with 200
       | room        | actorType | actorId      | actorDisplayName         | message  | messageParameters |
-      | public room | users     | participant1 | participant1-displayname | {file}   | "IGNORE" |
       | public room | users     | participant1 | participant1-displayname | {object} | {"actor":{"type":"user","id":"participant1","name":"participant1-displayname"},"object":{"name":"Another room","call-type":"group","type":"call","id":"R4nd0mT0k3n"}} |
+    When user "participant1" shares "welcome.txt" with room "public room" with OCS 100
+    Then user "participant1" sees the following shared file in room "public room" with 200
+      | room        | actorType | actorId      | actorDisplayName         | message  | messageParameters |
+      | public room | users     | participant1 | participant1-displayname | {file}   | "IGNORE" |
diff --git a/tests/integration/spreedcheats/lib/Controller/ApiController.php b/tests/integration/spreedcheats/lib/Controller/ApiController.php
index 13f9753cd..712041f15 100644
--- a/tests/integration/spreedcheats/lib/Controller/ApiController.php
+++ b/tests/integration/spreedcheats/lib/Controller/ApiController.php
@@ -51,6 +51,9 @@ class ApiController extends OCSController {
 	 */
 	public function resetSpreed(): DataResponse {
 		$delete = $this->db->getQueryBuilder();
+		$delete->delete('talk_attachments')->executeStatement();
+
+		$delete = $this->db->getQueryBuilder();
 		$delete->delete('talk_attendees')->executeStatement();
 
 		$delete = $this->db->getQueryBuilder();
diff --git a/tests/php/Chat/ChatManagerTest.php b/tests/php/Chat/ChatManagerTest.php
index 78f9cf693..7145ef8ab 100644
--- a/tests/php/Chat/ChatManagerTest.php
+++ b/tests/php/Chat/ChatManagerTest.php
@@ -31,6 +31,7 @@ use OCA\Talk\Model\Attendee;
 use OCA\Talk\Model\AttendeeMapper;
 use OCA\Talk\Participant;
 use OCA\Talk\Room;
+use OCA\Talk\Service\AttachmentService;
 use OCA\Talk\Service\ParticipantService;
 use OCA\Talk\Share\RoomShareProvider;
 use OCP\AppFramework\Utility\ITimeFactory;
@@ -67,6 +68,8 @@ class ChatManagerTest extends TestCase {
 	protected $notifier;
 	/** @var ITimeFactory|MockObject */
 	protected $timeFactory;
+	/** @var AttachmentService|MockObject */
+	protected $attachmentService;
 	/** @var ChatManager */
 	protected $chatManager;
 
@@ -81,6 +84,7 @@ class ChatManagerTest extends TestCase {
 		$this->participantService = $this->createMock(ParticipantService::class);
 		$this->notifier = $this->createMock(Notifier::class);
 		$this->timeFactory = $this->createMock(ITimeFactory::class);
+		$this->attachmentService = $this->createMock(AttachmentService::class);
 		$cacheFactory = $this->createMock(ICacheFactory::class);
 
 		$this->chatManager = new ChatManager(
@@ -93,7 +97,8 @@ class ChatManagerTest extends TestCase {
 			$this->participantService,
 			$this->notifier,
 			$cacheFactory,
-			$this->timeFactory
+			$this->timeFactory,
+			$this->attachmentService
 		);
 	}
 
@@ -117,6 +122,7 @@ class ChatManagerTest extends TestCase {
 					$this->notifier,
 					$cacheFactory,
 					$this->timeFactory,
+					$this->attachmentService,
 				])
 				->onlyMethods($methods)
 				->getMock();
@@ -132,7 +138,8 @@ class ChatManagerTest extends TestCase {
 			$this->participantService,
 			$this->notifier,
 			$cacheFactory,
-			$this->timeFactory
+			$this->timeFactory,
+			$this->attachmentService
 		);
 	}
 
diff --git a/tests/php/Controller/ChatControllerTest.php b/tests/php/Controller/ChatControllerTest.php
index 92473c7fd..ba24bf7f2 100644
--- a/tests/php/Controller/ChatControllerTest.php
+++ b/tests/php/Controller/ChatControllerTest.php
@@ -33,6 +33,7 @@ use OCA\Talk\Model\Attendee;
 use OCA\Talk\Model\Message;
 use OCA\Talk\Participant;
 use OCA\Talk\Room;
+use OCA\Talk\Service\AttachmentService;
 use OCA\Talk\Service\ParticipantService;
 use OCA\Talk\Service\SessionService;
 use OCP\App\IAppManager;
@@ -68,6 +69,8 @@ class ChatControllerTest extends TestCase {
 	protected $participantService;
 	/** @var SessionService|MockObject */
 	protected $sessionService;
+	/** @var AttachmentService|MockObject */
+	protected $attachmentService;
 	/** @var GuestManager|MockObject */
 	protected $guestManager;
 	/** @var MessageParser|MockObject */
@@ -111,6 +114,7 @@ class ChatControllerTest extends TestCase {
 		$this->chatManager = $this->createMock(ChatManager::class);
 		$this->participantService = $this->createMock(ParticipantService::class);
 		$this->sessionService = $this->createMock(SessionService::class);
+		$this->attachmentService = $this->createMock(AttachmentService::class);
 		$this->guestManager = $this->createMock(GuestManager::class);
 		$this->messageParser = $this->createMock(MessageParser::class);
 		$this->autoCompleteManager = $this->createMock(IManager::class);
@@ -146,6 +150,7 @@ class ChatControllerTest extends TestCase {
 			$this->chatManager,
 			$this->participantService,
 			$this->sessionService,
+			$this->attachmentService,
 			$this->guestManager,
 			$this->messageParser,
 			$this->autoCompleteManager,