open on unread

This reverts commit 01611fd719.

apps/ios/Shared/Model/SimpleXAPI.swift

@@ -320,15 +320,24 @@ private func apiChatsResponse(_ r: ChatResponse) throws -> [ChatData] {
 
 let loadItemsPerPage = 50
 
-func apiGetChat(type: ChatType, id: Int64, search: String = "") async throws -> Chat {
+func apiGetChat(type: ChatType, id: Int64, search: String = "") async throws -> (Chat, Int?) {
-    let r = await chatSendCmd(.apiGetChat(type: type, id: id, pagination: .last(count: loadItemsPerPage), search: search))
+    let r = await chatSendCmd(.apiGetChat(type: type, id: id, pagination: .initial(count: loadItemsPerPage), search: search))
-    if case let .apiChat(_, chat) = r { return Chat.init(chat) }
+    if case let .apiChat(_, chat, gap) = r { return (Chat.init(chat), gap) }
     throw r
 }
 
-func apiGetChatItems(type: ChatType, id: Int64, pagination: ChatPagination, search: String = "") async throws -> [ChatItem] {
+func apiGetChatItems(type: ChatType, id: Int64, pagination: ChatPagination, search: String = "") async throws -> ([ChatItem], Int?) {
     let r = await chatSendCmd(.apiGetChat(type: type, id: id, pagination: pagination, search: search))
-    if case let .apiChat(_, chat) = r { return chat.chatItems }
+    if case let .apiChat(_, chat, gap) = r { return (chat.chatItems, gap) }
+    if case .chatCmdError(_, _) = r {
+        if case .chatError(_, let chatError) = r {
+            if case .errorStore(let storeError) = chatError {
+                if case .chatItemNotFound(_) = storeError {
+                    itemNotFoundAlert()
+                }
+            }
+        }
+    }
     throw r
 }
 
@@ -341,7 +350,7 @@ func loadChat(chat: Chat, search: String = "", clearItems: Bool = true) async {
         if clearItems {
             await MainActor.run { im.reversedChatItems = [] }
         }
-        let chat = try await apiGetChat(type: cInfo.chatType, id: cInfo.apiId, search: search)
+        let (chat, _) = try await apiGetChat(type: cInfo.chatType, id: cInfo.apiId, search: search)
         await MainActor.run {
             im.reversedChatItems = chat.chatItems.reversed()
             m.updateChatInfo(chat.chatInfo)
@@ -418,6 +427,13 @@ func apiCreateChatItems(noteFolderId: Int64, composedMessages: [ComposedMessage]
     return nil
 }
 
+func itemNotFoundAlert() {
+    AlertManager.shared.showAlertMsg(
+        title: "Message no longer available",
+        message: "The quoted message you are trying to view has been deleted."
+    )
+}
+
 private func sendMessageErrorAlert(_ r: ChatResponse) {
     logger.error("send message error: \(String(describing: r))")
     AlertManager.shared.showAlertMsg(

apps/ios/Shared/Views/Chat/ChatItem/FramedItemView.swift

@@ -48,10 +48,18 @@ struct FramedItemView: View {
                 if let qi = chatItem.quotedItem {
                     ciQuoteView(qi)
                         .onTapGesture {
-                            if let ci = ItemsModel.shared.reversedChatItems.first(where: { $0.id == qi.itemId }) {
+                            if let itemId = qi.itemId {
-                                withAnimation {
+                                if !scrollToItem(itemId) {
-                                    scrollModel.scrollToItem(id: ci.id)
+                                    Task {
+                                        if await loadItemsAround(chat.chatInfo, itemId) != nil {
+                                            await MainActor.run {
+                                                let _ = scrollToItem(itemId)
+                                            }
+                                        }
+                                    }
                                 }
+                            } else {
+                                itemNotFoundAlert()
                             }
                         }
                 } else if let itemForwarded = chatItem.meta.itemForwarded {
@@ -323,6 +331,42 @@ struct FramedItemView: View {
             return videoWidth
         }
     }
+    
+    // Scroll to an item, if success returns true otherwise false
+    private func scrollToItem(_ itemId: Int64) -> Bool {
+        if let ci = ItemsModel.shared.reversedChatItems.first(where: { $0.id == itemId }) {
+            withAnimation {
+                scrollModel.scrollToItem(id: ci.id)
+            }
+            return true
+        } else {
+            return false
+        }
+    }
+    
+    private func loadItemsAround(_ cInfo: ChatInfo, _ chatItemId: Int64) async -> [ChatItem]? {
+        do {
+            var reversedPage = Array<ChatItem>()
+            let pagination: ChatPagination = .around(chatItemId: chatItemId, count: loadItemsPerPage * 2)
+            let (chatItems, _) = try await apiGetChatItems(
+                type: cInfo.chatType,
+                id: cInfo.apiId,
+                pagination: pagination,
+                search: ""
+            )
+            
+            reversedPage.append(contentsOf: chatItems.reversed())
+
+            await MainActor.run {
+                ItemsModel.shared.reversedChatItems.append(contentsOf: reversedPage)
+            }
+            
+            return reversedPage
+        } catch let error {
+            logger.error("apiGetChat error: \(responseError(error))")
+            return nil
+        }
+    }
 }
 
 @ViewBuilder func toggleSecrets<V: View>(_ ft: [FormattedText]?, _ showSecrets: Binding<Bool>, _ v: V) -> some View {

apps/ios/Shared/Views/Chat/ChatView.swift

@@ -363,6 +363,11 @@ struct ChatView: View {
             }
         }
         ChatView.FloatingButtonModel.shared.totalUnread = chat.chatStats.unreadCount
+        Task {
+            if let firstunreadItem = self.getFirstUnreadItem() {
+                scrollModel.scrollToUnread(id: firstunreadItem.id)
+            }
+        }
     }
 
     private func searchToolbar() -> some View {
@@ -471,6 +476,19 @@ struct ChatView: View {
             EmptyView()
         }
     }
+    
+    private func getFirstUnreadItem() -> ChatItem? {
+        logger.error("[scrolling] \(im.reversedChatItems.count)")
+
+        for i in stride(from: im.reversedChatItems.count - 1, through: 0, by: -1) {
+            let item = im.reversedChatItems[i]
+            if item.isRcvNew {
+                logger.error("[scrolling] First unread item: \(item.text)")
+                return item
+            }
+        }
+        return nil
+    }
 
     class FloatingButtonModel: ObservableObject {
         static let shared = FloatingButtonModel()
@@ -854,7 +872,7 @@ struct ChatView: View {
                         } else {
                             .last(count: loadItemsPerPage)
                         }
-                    let chatItems = try await apiGetChatItems(
+                    let (chatItems, _) = try await apiGetChatItems(
                         type: cInfo.chatType,
                         id: cInfo.apiId,
                         pagination: pagination,

apps/ios/Shared/Views/Chat/Group/GroupMemberInfoView.swift

@@ -340,7 +340,7 @@ struct GroupMemberInfoView: View {
         InfoViewButton(image: "message.fill", title: "message", width: width) {
             Task {
                 do {
-                    let chat = try await apiGetChat(type: .direct, id: contactId)
+                    let (chat, _) = try await apiGetChat(type: .direct, id: contactId)
                     chatModel.addChat(chat)
                     ItemsModel.shared.loadOpenChat(chat.id) {
                         dismissAllSheets(animated: true)

apps/ios/Shared/Views/Chat/ReverseList.swift

@@ -36,8 +36,11 @@ struct ReverseList<Content: View>: UIViewControllerRepresentable {
                 controller.scroll(to: items.firstIndex(where: { $0.id == id }), position: .bottom)
             case .bottom:
                 controller.scroll(to: 0, position: .top)
+            case let .unread(id):
+                controller.scrollToUnread(to: items.firstIndex(where: { $0.id == id }))
             }
         } else {
+            logger.error("[scrolling] not scrolling")
             controller.update(items: items)
         }
     }
@@ -78,7 +81,8 @@ struct ReverseList<Content: View>: UIViewControllerRepresentable {
             self.dataSource = UITableViewDiffableDataSource<Section, ChatItem>(
                 tableView: tableView
             ) { (tableView, indexPath, item) -> UITableViewCell? in
-                if indexPath.item > self.itemCount - 8 {
+                if indexPath.item > self.itemCount - 8, self.representer.scrollState == .atDestination {
+                    logger.error("[scrolling] requesting page")
                     self.representer.loadPage()
                 }
                 let cell = tableView.dequeueReusableCell(withIdentifier: cellReuseId, for: indexPath)
@@ -161,6 +165,18 @@ struct ReverseList<Content: View>: UIViewControllerRepresentable {
             )
             Task { representer.scrollState = .atDestination }
         }
+        
+        func scrollToUnread(to index: Int?) {
+            if let index = index {
+                if isVisible(indexPath: IndexPath(row: index, section: 0)) {
+                    self.scroll(to: 0, position: .top)
+                } else {
+                    self.scroll(to: index, position: .bottom)
+                }
+            } else {
+                self.scroll(to: index, position: .bottom)
+            }
+        }
 
         /// Scrolls to Item at index path
         /// - Parameter indexPath: Item to scroll to - will scroll to beginning of the list, if `nil`
@@ -169,7 +185,7 @@ struct ReverseList<Content: View>: UIViewControllerRepresentable {
             if #available(iOS 16.0, *) {
                 animated = true
             }
-            if let index, tableView.numberOfRows(inSection: 0) != 0 {
+            if let index, tableView.numberOfRows(inSection: 0) != 0, !isVisible(indexPath: IndexPath(row: index, section: 0)) {
                 tableView.scrollToRow(
                     at: IndexPath(row: index, section: 0),
                     at: position,
@@ -294,6 +310,7 @@ class ReverseListScrollModel: ObservableObject {
             case nextPage
             case item(ChatItem.ID)
             case bottom
+            case unread(ChatItem.ID)
         }
 
         case scrollingTo(Destination)
@@ -303,16 +320,25 @@ class ReverseListScrollModel: ObservableObject {
     @Published var state: State = .atDestination
 
     func scrollToNextPage() {
+        logger.error("[scrolling] to next page")
+
         state = .scrollingTo(.nextPage)
     }
 
     func scrollToBottom() {
+        logger.error("[scrolling] to bottom")
         state = .scrollingTo(.bottom)
     }
 
     func scrollToItem(id: ChatItem.ID) {
+        logger.error("[scrolling] to item \(id)")
         state = .scrollingTo(.item(id))
     }
+    
+    func scrollToUnread(id: ChatItem.ID) {
+        logger.error("[scrolling] to unread")
+        state = .scrollingTo(.unread(id))
+    }
 }
 
 fileprivate let cellReuseId = "hostingCell"

apps/ios/SimpleXChat/API.swift

@@ -214,7 +214,8 @@ public func chatResponse(_ s: String) -> ChatResponse {
                    let user: UserRef = try? decodeObject(jApiChat["user"] as Any),
                    let jChat = jApiChat["chat"] as? NSDictionary,
                    let chat = try? parseChatData(jChat) {
-                    return .apiChat(user: user, chat: chat)
+                    let gap = jApiChat["gap"] as? Int
+                    return .apiChat(user: user, chat: chat, gap: gap)
                 }
             } else if type == "chatCmdError" {
                 if let jError = jResp["chatCmdError"] as? NSDictionary {

apps/ios/SimpleXChat/APITypes.swift

@@ -546,7 +546,7 @@ public enum ChatResponse: Decodable, Error {
     case chatStopped
     case chatSuspended
     case apiChats(user: UserRef, chats: [ChatData])
-    case apiChat(user: UserRef, chat: ChatData)
+    case apiChat(user: UserRef, chat: ChatData, gap: Int?)
     case chatItemInfo(user: UserRef, chatItem: AChatItem, chatItemInfo: ChatItemInfo)
     case userProtoServers(user: UserRef, servers: UserProtoServers)
     case serverTestResult(user: UserRef, testServer: String, testFailure: ProtocolTestFailure?)
@@ -888,7 +888,7 @@ public enum ChatResponse: Decodable, Error {
             case .chatStopped: return noDetails
             case .chatSuspended: return noDetails
             case let .apiChats(u, chats): return withUser(u, String(describing: chats))
-            case let .apiChat(u, chat): return withUser(u, String(describing: chat))
+            case let .apiChat(u, chat, gap): return withUser(u, "gap: \(String(describing: gap)) \(String(describing: chat))")
             case let .chatItemInfo(u, chatItem, chatItemInfo): return withUser(u, "chatItem: \(String(describing: chatItem))\nchatItemInfo: \(String(describing: chatItemInfo))")
             case let .userProtoServers(u, servers): return withUser(u, "servers: \(String(describing: servers))")
             case let .serverTestResult(u, server, testFailure): return withUser(u, "server: \(server)\nresult: \(String(describing: testFailure))")
@@ -1133,12 +1133,16 @@ public enum ChatPagination {
     case last(count: Int)
     case after(chatItemId: Int64, count: Int)
     case before(chatItemId: Int64, count: Int)
-
+    case around(chatItemId: Int64, count: Int)
+    case initial(count: Int)
+    
     var cmdString: String {
         switch self {
         case let .last(count): return "count=\(count)"
         case let .after(chatItemId, count): return "after=\(chatItemId) count=\(count)"
         case let .before(chatItemId, count): return "before=\(chatItemId) count=\(count)"
+        case let .around(chatItemId, count): return "around=\(chatItemId) count=\(count)"
+        case let .initial(count): return "initial=\(count)"
         }
     }
 }

apps/simplex-directory-service/src/Directory/Service.hs

@@ -605,7 +605,7 @@ directoryService st DirectoryOpts {superUsers, serviceName, searchResults, testi
         listGroups count pending =
           readTVarIO (groupRegs st) >>= \groups -> do
             grs <-
-              if pending 
+              if pending
                 then filterM (fmap pendingApproval . readTVarIO . groupRegStatus) groups
                 else pure groups
             sendReply $ show (length grs) <> " registered group(s)" <> (if length grs > count then ", showing the last " <> show count else "")
@@ -643,7 +643,7 @@ getContact cc ctId = resp <$> sendChatCmd cc (APIGetChat (ChatRef CTDirect ctId)
   where
     resp :: ChatResponse -> Maybe Contact
     resp = \case
-      CRApiChat _ (AChat SCTDirect Chat {chatInfo = DirectChat ct}) -> Just ct
+      CRApiChat _ (AChat SCTDirect Chat {chatInfo = DirectChat ct}) _ -> Just ct
       _ -> Nothing
 
 getGroup :: ChatController -> GroupId -> IO (Maybe GroupInfo)

simplex-chat.cabal

@@ -150,6 +150,7 @@ library
       Simplex.Chat.Migrations.M20240920_user_order
       Simplex.Chat.Migrations.M20241008_indexes
       Simplex.Chat.Migrations.M20241010_contact_requests_contact_id
+      Simplex.Chat.Migrations.M20241023_chat_item_autoincrement_id
       Simplex.Chat.Mobile
       Simplex.Chat.Mobile.File
       Simplex.Chat.Mobile.Shared

src/Simplex/Chat.hs

@@ -735,14 +735,14 @@ processChatCommand' vr = \case
   APIGetChat (ChatRef cType cId) pagination search -> withUser $ \user -> case cType of
     -- TODO optimize queries calculating ChatStats, currently they're disabled
     CTDirect -> do
-      directChat <- withFastStore (\db -> getDirectChat db vr user cId pagination search)
+      (directChat, gap) <- withFastStore (\db -> getDirectChat db vr user cId pagination search)
-      pure $ CRApiChat user (AChat SCTDirect directChat)
+      pure $ CRApiChat user (AChat SCTDirect directChat) gap
     CTGroup -> do
-      groupChat <- withFastStore (\db -> getGroupChat db vr user cId pagination search)
+      (groupChat, gap) <- withFastStore (\db -> getGroupChat db vr user cId pagination search)
-      pure $ CRApiChat user (AChat SCTGroup groupChat)
+      pure $ CRApiChat user (AChat SCTGroup groupChat) gap
     CTLocal -> do
-      localChat <- withFastStore (\db -> getLocalChat db user cId pagination search)
+      (localChat, gap) <- withFastStore (\db -> getLocalChat db user cId pagination search)
-      pure $ CRApiChat user (AChat SCTLocal localChat)
+      pure $ CRApiChat user (AChat SCTLocal localChat) gap
     CTContactRequest -> pure $ chatCmdError (Just user) "not implemented"
     CTContactConnection -> pure $ chatCmdError (Just user) "not supported"
   APIGetChatItems pagination search -> withUser $ \user -> do
@@ -8301,6 +8301,8 @@ chatCommandP =
       (CPLast <$ "count=" <*> A.decimal)
         <|> (CPAfter <$ "after=" <*> A.decimal <* A.space <* "count=" <*> A.decimal)
         <|> (CPBefore <$ "before=" <*> A.decimal <* A.space <* "count=" <*> A.decimal)
+        <|> (CPAround <$ "around=" <*> A.decimal <* A.space <* "count=" <*> A.decimal)
+        <|> (CPInitial <$ "initial=" <*> A.decimal)
     paginationByTimeP =
       (PTLast <$ "count=" <*> A.decimal)
         <|> (PTAfter <$ "after=" <*> strP <* A.space <* "count=" <*> A.decimal)

src/Simplex/Chat/Controller.hs

@@ -572,7 +572,7 @@ data ChatResponse
   | CRChatSuspended
   | CRApiChats {user :: User, chats :: [AChat]}
   | CRChats {chats :: [AChat]}
-  | CRApiChat {user :: User, chat :: AChat}
+  | CRApiChat {user :: User, chat :: AChat, gap :: Maybe ChatGap}
   | CRChatItems {user :: User, chatName_ :: Maybe ChatName, chatItems :: [AChatItem]}
   | CRChatItemInfo {user :: User, chatItem :: AChatItem, chatItemInfo :: ChatItemInfo}
   | CRChatItemId User (Maybe ChatItemId)
@@ -839,6 +839,11 @@ data ChatPagination
   = CPLast Int
   | CPAfter ChatItemId Int
   | CPBefore ChatItemId Int
+  | CPAround ChatItemId Int
+  | CPInitial Int
+  deriving (Show)
+
+data ChatGap = ChatGap {index :: Maybe Int, size :: Int}
   deriving (Show)
 
 data PaginationByTime
@@ -1591,6 +1596,8 @@ $(JQ.deriveJSON (sumTypeJSON $ dropPrefix "RCSR") ''RemoteCtrlStopReason)
 
 $(JQ.deriveJSON (sumTypeJSON $ dropPrefix "RHSR") ''RemoteHostStopReason)
 
+$(JQ.deriveJSON defaultJSON ''ChatGap)
+
 $(JQ.deriveJSON (sumTypeJSON $ dropPrefix "CR") ''ChatResponse)
 
 $(JQ.deriveFromJSON defaultJSON ''ArchiveConfig)

src/Simplex/Chat/Messages.hs

@@ -239,6 +239,12 @@ chatItemTs (CChatItem _ ci) = chatItemTs' ci
 chatItemTs' :: ChatItem c d -> UTCTime
 chatItemTs' ChatItem {meta = CIMeta {itemTs}} = itemTs
 
+chatItemCreatedAt :: CChatItem c -> UTCTime
+chatItemCreatedAt (CChatItem _ ci) = chatItemCreatedAt' ci
+
+chatItemCreatedAt' :: ChatItem c d -> UTCTime
+chatItemCreatedAt' ChatItem {meta = CIMeta {createdAt}} = createdAt
+
 chatItemTimed :: ChatItem c d -> Maybe CITimed
 chatItemTimed ChatItem {meta = CIMeta {itemTimed}} = itemTimed
 

src/Simplex/Chat/Migrations/M20241023_chat_item_autoincrement_id.hs

@@ -0,0 +1,34 @@
+{-# LANGUAGE QuasiQuotes #-}
+
+module Simplex.Chat.Migrations.M20241023_chat_item_autoincrement_id where
+
+import Database.SQLite.Simple (Query)
+import Database.SQLite.Simple.QQ (sql)
+
+m20241023_chat_item_autoincrement_id :: Query
+m20241023_chat_item_autoincrement_id =
+    [sql|
+INSERT INTO sqlite_sequence (name, seq)
+SELECT 'chat_items', MAX(ROWID) FROM chat_items;
+
+PRAGMA writable_schema=1;
+
+UPDATE sqlite_master SET sql = replace(sql, 'INTEGER PRIMARY KEY', 'INTEGER PRIMARY KEY AUTOINCREMENT')
+WHERE name = 'chat_items' AND type = 'table';
+
+PRAGMA writable_schema=0;
+|]
+
+down_m20241023_chat_item_autoincrement_id :: Query
+down_m20241023_chat_item_autoincrement_id =
+    [sql|
+DELETE FROM sqlite_sequence WHERE name = 'chat_items';
+
+PRAGMA writable_schema=1;
+
+UPDATE sqlite_master
+SET sql = replace(sql, 'INTEGER PRIMARY KEY AUTOINCREMENT', 'INTEGER PRIMARY KEY')
+WHERE name = 'chat_items' AND type = 'table';
+
+PRAGMA writable_schema=0;
+|]

src/Simplex/Chat/Migrations/chat_schema.sql

@@ -360,7 +360,7 @@ CREATE TABLE pending_group_messages(
   updated_at TEXT NOT NULL DEFAULT(datetime('now'))
 );
 CREATE TABLE chat_items(
-  chat_item_id INTEGER PRIMARY KEY,
+  chat_item_id INTEGER PRIMARY KEY AUTOINCREMENT,
   user_id INTEGER NOT NULL REFERENCES users ON DELETE CASCADE,
   contact_id INTEGER REFERENCES contacts ON DELETE CASCADE,
   group_id INTEGER REFERENCES groups ON DELETE CASCADE,
@@ -399,6 +399,7 @@ CREATE TABLE chat_items(
   fwd_from_chat_item_id INTEGER REFERENCES chat_items ON DELETE SET NULL,
   via_proxy INTEGER
 );
+CREATE TABLE sqlite_sequence(name,seq);
 CREATE TABLE chat_item_messages(
   chat_item_id INTEGER NOT NULL REFERENCES chat_items ON DELETE CASCADE,
   message_id INTEGER NOT NULL UNIQUE REFERENCES messages ON DELETE CASCADE,
@@ -429,7 +430,6 @@ CREATE TABLE commands(
   created_at TEXT NOT NULL DEFAULT(datetime('now')),
   updated_at TEXT NOT NULL DEFAULT(datetime('now'))
 );
-CREATE TABLE sqlite_sequence(name,seq);
 CREATE TABLE settings(
   settings_id INTEGER PRIMARY KEY,
   chat_item_ttl INTEGER,

src/Simplex/Chat/Store/Messages.hs

@@ -138,7 +138,7 @@ import Data.Time (addUTCTime)
 import Data.Time.Clock (UTCTime (..), getCurrentTime)
 import Database.SQLite.Simple (NamedParam (..), Only (..), Query, (:.) (..))
 import Database.SQLite.Simple.QQ (sql)
-import Simplex.Chat.Controller (ChatListQuery (..), ChatPagination (..), PaginationByTime (..))
+import Simplex.Chat.Controller (ChatGap (ChatGap, index, size), ChatListQuery (..), ChatPagination (..), PaginationByTime (..))
 import Simplex.Chat.Markdown
 import Simplex.Chat.Messages
 import Simplex.Chat.Messages.CIContent
@@ -947,37 +947,62 @@ getContactConnectionChatPreviews_ db User {userId} pagination clq = case clq of
           aChat = AChat SCTContactConnection $ Chat (ContactConnection conn) [] stats
        in ACPD SCTContactConnection $ ContactConnectionPD updatedAt aChat
 
-getDirectChat :: DB.Connection -> VersionRangeChat -> User -> Int64 -> ChatPagination -> Maybe String -> ExceptT StoreError IO (Chat 'CTDirect)
+getDirectChat :: DB.Connection -> VersionRangeChat -> User -> Int64 -> ChatPagination -> Maybe String -> ExceptT StoreError IO (Chat 'CTDirect, Maybe ChatGap)
 getDirectChat db vr user contactId pagination search_ = do
   let search = fromMaybe "" search_
   ct <- getContact db vr user contactId
-  liftIO $ case pagination of
+  case pagination of
-    CPLast count -> getDirectChatLast_ db user ct count search
+    CPLast count -> liftIO $ (,Nothing) <$> getDirectChatLast_ db user ct count search
     CPAfter afterId count -> getDirectChatAfter_ db user ct afterId count search
     CPBefore beforeId count -> getDirectChatBefore_ db user ct beforeId count search
+    CPAround aroundId count -> getDirectChatAround_ db user ct aroundId count search
+    CPInitial count -> do
+      unless (null search) $ throwError $ SEInternalError "initial chat pagination doesn't support search"
+      getDirectChatInitial_ db user ct count
 
 -- the last items in reverse order (the last item in the conversation is the first in the returned list)
 getDirectChatLast_ :: DB.Connection -> User -> Contact -> Int -> String -> IO (Chat 'CTDirect)
-getDirectChatLast_ db user@User {userId} ct@Contact {contactId} count search = do
+getDirectChatLast_ db user ct count search = do
   let stats = ChatStats {unreadCount = 0, minUnreadItemId = 0, unreadChat = False}
-  chatItemIds <- getDirectChatItemIdsLast_
+  chatItemIds <- getDirectChatItemIdsLast_ db user ct count search
   currentTs <- getCurrentTime
   chatItems <- mapM (safeGetDirectItem db user ct currentTs) chatItemIds
   pure $ Chat (DirectChat ct) (reverse chatItems) stats
-  where
+
-    getDirectChatItemIdsLast_ :: IO [ChatItemId]
+getDirectChatItemIdsLast_ :: DB.Connection -> User -> Contact -> Int -> String -> IO [ChatItemId]
-    getDirectChatItemIdsLast_ =
+getDirectChatItemIdsLast_ db User {userId} Contact {contactId} count search =
-      map fromOnly
+  map fromOnly
-        <$> DB.query
+    <$> DB.query
-          db
+      db
-          [sql|
+      [sql|
-            SELECT chat_item_id
+        SELECT chat_item_id
-            FROM chat_items
+        FROM chat_items
-            WHERE user_id = ? AND contact_id = ? AND item_text LIKE '%' || ? || '%'
+        WHERE user_id = ? AND contact_id = ? AND item_text LIKE '%' || ? || '%'
-            ORDER BY created_at DESC, chat_item_id DESC
+        ORDER BY created_at DESC, chat_item_id DESC
-            LIMIT ?
+        LIMIT ?
-          |]
+      |]
-          (userId, contactId, search, count)
+      (userId, contactId, search, count)
+
+getDirectChatItemGapToLatest_ :: DB.Connection -> User -> Contact -> CChatItem 'CTDirect -> String -> IO Int
+getDirectChatItemGapToLatest_ db User {userId} Contact {contactId} chatItem search = do
+  count <-
+    maybeFirstRow fromOnly $
+      DB.queryNamed
+        db
+        [sql|
+        SELECT COUNT(1)
+        FROM chat_items
+        WHERE user_id = :userId AND contact_id = :contactId AND item_text LIKE '%' || :search || '%'
+          AND (created_at > :itemCreatedAt OR (created_at = :itemCreatedAt AND chat_item_id > :chatItemId))
+        ORDER BY created_at DESC, chat_item_id DESC
+      |]
+        [ ":userId" := userId,
+          ":contactId" := contactId,
+          ":search" := search,
+          ":itemCreatedAt" := chatItemCreatedAt chatItem,
+          ":chatItemId" := cchatItemId chatItem
+        ]
+  pure $ maybe 0 (\c -> max 0 (c - 1)) count
 
 safeGetDirectItem :: DB.Connection -> User -> Contact -> UTCTime -> ChatItemId -> IO (CChatItem 'CTDirect)
 safeGetDirectItem db user ct currentTs itemId =
@@ -1021,82 +1046,162 @@ getDirectChatItemLast db user@User {userId} contactId = do
         (userId, contactId)
   getDirectChatItem db user contactId chatItemId
 
-getDirectChatAfter_ :: DB.Connection -> User -> Contact -> ChatItemId -> Int -> String -> IO (Chat 'CTDirect)
+getDirectChatAfter_ :: DB.Connection -> User -> Contact -> ChatItemId -> Int -> String -> ExceptT StoreError IO (Chat 'CTDirect, Maybe ChatGap)
-getDirectChatAfter_ db user@User {userId} ct@Contact {contactId} afterChatItemId count search = do
+getDirectChatAfter_ db user ct@Contact {contactId} afterChatItemId count search = do
   let stats = ChatStats {unreadCount = 0, minUnreadItemId = 0, unreadChat = False}
-  chatItemIds <- getDirectChatItemIdsAfter_
+  afterChatItem <- getDirectChatItem db user contactId afterChatItemId
-  currentTs <- getCurrentTime
+  chatItemIds <- liftIO $ getDirectChatItemIdsAfter_ db user ct afterChatItemId count search (chatItemCreatedAt afterChatItem)
-  chatItems <- mapM (safeGetDirectItem db user ct currentTs) chatItemIds
+  currentTs <- liftIO getCurrentTime
-  pure $ Chat (DirectChat ct) chatItems stats
+  chatItems <- liftIO $ mapM (safeGetDirectItem db user ct currentTs) chatItemIds
+  gapToLatest <- liftIO $ getDirectChatItemGapToLatest_ db user ct (last chatItems) search
+  let chatGap = if gapToLatest > 0 then Just $ ChatGap {size = gapToLatest, index = Nothing} else Nothing
+  pure (Chat (DirectChat ct) chatItems stats, chatGap)
+
+getDirectChatItemIdsAfter_ :: DB.Connection -> User -> Contact -> ChatItemId -> Int -> String -> UTCTime -> IO [ChatItemId]
+getDirectChatItemIdsAfter_ db User {userId} Contact {contactId} afterChatItemId count search afterChatItemCreatedAt =
+  map fromOnly
+    <$> DB.query
+      db
+      [sql|
+        SELECT chat_item_id
+        FROM chat_items
+        WHERE user_id = ? AND contact_id = ? AND item_text LIKE '%' || ? || '%'
+          AND (created_at > ? OR (created_at = ? AND chat_item_id > ?))
+        ORDER BY created_at ASC, chat_item_id ASC
+        LIMIT ?
+      |]
+      (userId, contactId, search, afterChatItemCreatedAt, afterChatItemCreatedAt, afterChatItemId, count)
+
+getDirectChatBefore_ :: DB.Connection -> User -> Contact -> ChatItemId -> Int -> String -> ExceptT StoreError IO (Chat 'CTDirect, Maybe ChatGap)
+getDirectChatBefore_ db user ct@Contact {contactId} beforeChatItemId count search = do
+  let stats = ChatStats {unreadCount = 0, minUnreadItemId = 0, unreadChat = False}
+  beforeChatItem <- getDirectChatItem db user contactId beforeChatItemId
+  chatItemIds <- liftIO $ getDirectChatItemsIdsBefore_ db user ct beforeChatItemId count search (chatItemCreatedAt beforeChatItem)
+  currentTs <- liftIO getCurrentTime
+  chatItems <- liftIO $ mapM (safeGetDirectItem db user ct currentTs) chatItemIds
+  pure (Chat (DirectChat ct) (reverse chatItems) stats, Nothing)
+
+getDirectChatItemsIdsBefore_ :: DB.Connection -> User -> Contact -> ChatItemId -> Int -> String -> UTCTime -> IO [ChatItemId]
+getDirectChatItemsIdsBefore_ db User {userId} Contact {contactId} beforeChatItemId count search beforeChatItemCreatedAt =
+  map fromOnly
+    <$> DB.query
+      db
+      [sql|
+        SELECT chat_item_id
+        FROM chat_items
+        WHERE user_id = ? AND contact_id = ? AND item_text LIKE '%' || ? || '%'
+          AND (created_at < ? OR (created_at = ? AND chat_item_id < ?))
+        ORDER BY created_at DESC, chat_item_id DESC
+        LIMIT ?
+      |]
+      (userId, contactId, search, beforeChatItemCreatedAt, beforeChatItemCreatedAt, beforeChatItemId, count)
+
+getDirectChatAround_ :: DB.Connection -> User -> Contact -> ChatItemId -> Int -> String -> ExceptT StoreError IO (Chat 'CTDirect, Maybe ChatGap)
+getDirectChatAround_ db user ct@Contact {contactId} aroundItemId count search = do
+  let stats = ChatStats {unreadCount = 0, minUnreadItemId = 0, unreadChat = False}
+  let (fetchCountBefore, fetchCountAfter) = divideFetchCountAround_ (count - 1)
+  middleChatItem <- getDirectChatItem db user contactId aroundItemId
+  beforeIds <- liftIO $ getDirectChatItemsIdsBefore_ db user ct aroundItemId fetchCountBefore search (chatItemCreatedAt middleChatItem)
+  afterIds <- liftIO $ getDirectChatItemIdsAfter_ db user ct aroundItemId fetchCountAfter search (chatItemCreatedAt middleChatItem)
+  currentTs <- liftIO getCurrentTime
+  beforeChatItems <- liftIO $ reverse <$> mapM (safeGetDirectItem db user ct currentTs) beforeIds
+  afterChatItems <- liftIO $ mapM (safeGetDirectItem db user ct currentTs) afterIds
+  let chatItems = beforeChatItems <> [middleChatItem] <> afterChatItems
+  gapToLatest <- liftIO $ getDirectChatItemGapToLatest_ db user ct (last chatItems) search
+  let chatGap = if gapToLatest > 0 then Just $ ChatGap {size = gapToLatest, index = Nothing} else Nothing
+  pure (Chat (DirectChat ct) chatItems stats, chatGap)
+
+getDirectChatInitial_ :: DB.Connection -> User -> Contact -> Int -> ExceptT StoreError IO (Chat 'CTDirect, Maybe ChatGap)
+getDirectChatInitial_ db user@User {userId} ct@Contact {contactId} count = do
+  firstUnreadItemId_ <- liftIO getDirectChatMinUnreadItemId_
+  case firstUnreadItemId_ of
+    Just firstUnreadItemId -> do
+      (chat, gap) <- getDirectChatAround_ db user ct firstUnreadItemId count ""
+      case gap of
+        Just ChatGap {size} -> do
+          if size > snd (divideFetchCountAround_ count)
+            then getLatestItems_ chat size
+            else pure (chat, Nothing)
+        Nothing -> pure (chat, Nothing)
+    Nothing -> liftIO $ (,Nothing) <$> getDirectChatLast_ db user ct count ""
   where
-    getDirectChatItemIdsAfter_ :: IO [ChatItemId]
+    getLatestItems_ :: Chat 'CTDirect -> Int -> ExceptT StoreError IO (Chat 'CTDirect, Maybe ChatGap)
-    getDirectChatItemIdsAfter_ =
+    getLatestItems_ c@Chat {chatItems} gapToLatest = do
-      map fromOnly
+      currentTs <- liftIO getCurrentTime
-        <$> DB.query
+      latestItemIds <- liftIO $ getDirectChatItemIdsLast_ db user ct (min count gapToLatest) ""
+      latestItems <- liftIO $ mapM (safeGetDirectItem db user ct currentTs) latestItemIds
+      let allItems = chatItems <> latestItems
+      let chat = c {chatItems = allItems}
+      if gapToLatest > length latestItems
+        then pure (chat, Just $ ChatGap {size = gapToLatest - length latestItems, index = Just $ length chatItems})
+        else pure (chat, Nothing)
+    getDirectChatMinUnreadItemId_ :: IO (Maybe ChatItemId)
+    getDirectChatMinUnreadItemId_ =
+      fmap join . maybeFirstRow fromOnly $
+        DB.query
           db
           [sql|
-            SELECT chat_item_id
+            SELECT MIN(chat_item_id)
             FROM chat_items
-            WHERE user_id = ? AND contact_id = ? AND item_text LIKE '%' || ? || '%'
+            WHERE user_id = ? AND contact_id = ? AND item_status = ?
-              AND chat_item_id > ?
-            ORDER BY created_at ASC, chat_item_id ASC
-            LIMIT ?
           |]
-          (userId, contactId, search, afterChatItemId, count)
+          (userId, contactId, CISRcvNew)
 
-getDirectChatBefore_ :: DB.Connection -> User -> Contact -> ChatItemId -> Int -> String -> IO (Chat 'CTDirect)
+getGroupChat :: DB.Connection -> VersionRangeChat -> User -> Int64 -> ChatPagination -> Maybe String -> ExceptT StoreError IO (Chat 'CTGroup, Maybe ChatGap)
-getDirectChatBefore_ db user@User {userId} ct@Contact {contactId} beforeChatItemId count search = do
-  let stats = ChatStats {unreadCount = 0, minUnreadItemId = 0, unreadChat = False}
-  chatItemIds <- getDirectChatItemsIdsBefore_
-  currentTs <- getCurrentTime
-  chatItems <- mapM (safeGetDirectItem db user ct currentTs) chatItemIds
-  pure $ Chat (DirectChat ct) (reverse chatItems) stats
-  where
-    getDirectChatItemsIdsBefore_ :: IO [ChatItemId]
-    getDirectChatItemsIdsBefore_ =
-      map fromOnly
-        <$> DB.query
-          db
-          [sql|
-            SELECT chat_item_id
-            FROM chat_items
-            WHERE user_id = ? AND contact_id = ? AND item_text LIKE '%' || ? || '%'
-              AND chat_item_id < ?
-            ORDER BY created_at DESC, chat_item_id DESC
-            LIMIT ?
-          |]
-          (userId, contactId, search, beforeChatItemId, count)
-
-getGroupChat :: DB.Connection -> VersionRangeChat -> User -> Int64 -> ChatPagination -> Maybe String -> ExceptT StoreError IO (Chat 'CTGroup)
 getGroupChat db vr user groupId pagination search_ = do
   let search = fromMaybe "" search_
   g <- getGroupInfo db vr user groupId
   case pagination of
-    CPLast count -> liftIO $ getGroupChatLast_ db user g count search
+    CPLast count -> liftIO $ (,Nothing) <$> getGroupChatLast_ db user g count search
     CPAfter afterId count -> getGroupChatAfter_ db user g afterId count search
     CPBefore beforeId count -> getGroupChatBefore_ db user g beforeId count search
+    CPAround aroundId count -> getGroupChatAround_ db user g aroundId count search
+    CPInitial count -> do
+      unless (null search) $ throwError $ SEInternalError "initial chat pagination doesn't support search"
+      getGroupChatInitial_ db user g count
 
 getGroupChatLast_ :: DB.Connection -> User -> GroupInfo -> Int -> String -> IO (Chat 'CTGroup)
-getGroupChatLast_ db user@User {userId} g@GroupInfo {groupId} count search = do
+getGroupChatLast_ db user g count search = do
   let stats = ChatStats {unreadCount = 0, minUnreadItemId = 0, unreadChat = False}
-  chatItemIds <- getGroupChatItemIdsLast_
+  chatItemIds <- getGroupChatItemIdsLast_ db user g count search
   currentTs <- getCurrentTime
   chatItems <- mapM (safeGetGroupItem db user g currentTs) chatItemIds
   pure $ Chat (GroupChat g) (reverse chatItems) stats
-  where
+
-    getGroupChatItemIdsLast_ :: IO [ChatItemId]
+getGroupChatItemIdsLast_ :: DB.Connection -> User -> GroupInfo -> Int -> String -> IO [ChatItemId]
-    getGroupChatItemIdsLast_ =
+getGroupChatItemIdsLast_ db User {userId} GroupInfo {groupId} count search =
-      map fromOnly
+  map fromOnly
-        <$> DB.query
+    <$> DB.query
-          db
+      db
-          [sql|
+      [sql|
-            SELECT chat_item_id
+        SELECT chat_item_id
-            FROM chat_items
+        FROM chat_items
-            WHERE user_id = ? AND group_id = ? AND item_text LIKE '%' || ? || '%'
+        WHERE user_id = ? AND group_id = ? AND item_text LIKE '%' || ? || '%'
-            ORDER BY item_ts DESC, chat_item_id DESC
+        ORDER BY item_ts DESC, chat_item_id DESC
-            LIMIT ?
+        LIMIT ?
-          |]
+      |]
-          (userId, groupId, search, count)
+      (userId, groupId, search, count)
+
+getGroupChatItemGapToLatest_ :: DB.Connection -> User -> GroupInfo -> CChatItem 'CTGroup -> String -> IO Int
+getGroupChatItemGapToLatest_ db User {userId} GroupInfo {groupId} chatItem search = do
+  count <-
+    maybeFirstRow fromOnly $
+      DB.queryNamed
+        db
+        [sql|
+        SELECT COUNT(1)
+        FROM chat_items
+        WHERE user_id = :userId AND group_id = :groupId AND item_text LIKE '%' || :search || '%'
+          AND (created_at > :itemCreatedAt OR (created_at = :itemCreatedAt AND chat_item_id > :chatItemId))
+        ORDER BY created_at DESC, chat_item_id DESC
+      |]
+        [ ":userId" := userId,
+          ":groupId" := groupId,
+          ":search" := search,
+          ":itemCreatedAt" := chatItemCreatedAt chatItem,
+          ":chatItemId" := cchatItemId chatItem
+        ]
+  pure $ maybe 0 (\c -> max 0 (c - 1)) count
 
 safeGetGroupItem :: DB.Connection -> User -> GroupInfo -> UTCTime -> ChatItemId -> IO (CChatItem 'CTGroup)
 safeGetGroupItem db user g currentTs itemId =
@@ -1140,84 +1245,162 @@ getGroupMemberChatItemLast db user@User {userId} groupId groupMemberId = do
         (userId, groupId, groupMemberId)
   getGroupChatItem db user groupId chatItemId
 
-getGroupChatAfter_ :: DB.Connection -> User -> GroupInfo -> ChatItemId -> Int -> String -> ExceptT StoreError IO (Chat 'CTGroup)
+getGroupChatAfter_ :: DB.Connection -> User -> GroupInfo -> ChatItemId -> Int -> String -> ExceptT StoreError IO (Chat 'CTGroup, Maybe ChatGap)
-getGroupChatAfter_ db user@User {userId} g@GroupInfo {groupId} afterChatItemId count search = do
+getGroupChatAfter_ db user g@GroupInfo {groupId} afterChatItemId count search = do
   let stats = ChatStats {unreadCount = 0, minUnreadItemId = 0, unreadChat = False}
   afterChatItem <- getGroupChatItem db user groupId afterChatItemId
-  chatItemIds <- liftIO $ getGroupChatItemIdsAfter_ (chatItemTs afterChatItem)
+  chatItemIds <- liftIO $ getGroupChatItemIdsAfter_ db user g afterChatItemId count search (chatItemTs afterChatItem)
   currentTs <- liftIO getCurrentTime
   chatItems <- liftIO $ mapM (safeGetGroupItem db user g currentTs) chatItemIds
-  pure $ Chat (GroupChat g) chatItems stats
+  gapToLatest <- liftIO $ getGroupChatItemGapToLatest_ db user g (head chatItems) search
-  where
+  let chatGap = if gapToLatest > 0 then Just $ ChatGap {size = gapToLatest, index = Nothing} else Nothing
-    getGroupChatItemIdsAfter_ :: UTCTime -> IO [ChatItemId]
+  pure (Chat (GroupChat g) chatItems stats, chatGap)
-    getGroupChatItemIdsAfter_ afterChatItemTs =
-      map fromOnly
-        <$> DB.query
-          db
-          [sql|
-            SELECT chat_item_id
-            FROM chat_items
-            WHERE user_id = ? AND group_id = ? AND item_text LIKE '%' || ? || '%'
-              AND (item_ts > ? OR (item_ts = ? AND chat_item_id > ?))
-            ORDER BY item_ts ASC, chat_item_id ASC
-            LIMIT ?
-          |]
-          (userId, groupId, search, afterChatItemTs, afterChatItemTs, afterChatItemId, count)
 
-getGroupChatBefore_ :: DB.Connection -> User -> GroupInfo -> ChatItemId -> Int -> String -> ExceptT StoreError IO (Chat 'CTGroup)
+getGroupChatItemIdsAfter_ :: DB.Connection -> User -> GroupInfo -> ChatItemId -> Int -> String -> UTCTime -> IO [ChatItemId]
-getGroupChatBefore_ db user@User {userId} g@GroupInfo {groupId} beforeChatItemId count search = do
+getGroupChatItemIdsAfter_ db User {userId} GroupInfo {groupId} afterChatItemId count search afterChatItemTs =
+  map fromOnly
+    <$> DB.query
+      db
+      [sql|
+        SELECT chat_item_id
+        FROM chat_items
+        WHERE user_id = ? AND group_id = ? AND item_text LIKE '%' || ? || '%'
+          AND (item_ts > ? OR (item_ts = ? AND chat_item_id > ?))
+        ORDER BY item_ts ASC, chat_item_id ASC
+        LIMIT ?
+      |]
+      (userId, groupId, search, afterChatItemTs, afterChatItemTs, afterChatItemId, count)
+
+getGroupChatBefore_ :: DB.Connection -> User -> GroupInfo -> ChatItemId -> Int -> String -> ExceptT StoreError IO (Chat 'CTGroup, Maybe ChatGap)
+getGroupChatBefore_ db user g@GroupInfo {groupId} beforeChatItemId count search = do
   let stats = ChatStats {unreadCount = 0, minUnreadItemId = 0, unreadChat = False}
   beforeChatItem <- getGroupChatItem db user groupId beforeChatItemId
-  chatItemIds <- liftIO $ getGroupChatItemIdsBefore_ (chatItemTs beforeChatItem)
+  chatItemIds <- liftIO $ getGroupChatItemIdsBefore_ db user g beforeChatItemId count search (chatItemTs beforeChatItem)
   currentTs <- liftIO getCurrentTime
   chatItems <- liftIO $ mapM (safeGetGroupItem db user g currentTs) chatItemIds
-  pure $ Chat (GroupChat g) (reverse chatItems) stats
+  pure (Chat (GroupChat g) (reverse chatItems) stats, Nothing)
+
+getGroupChatItemIdsBefore_ :: DB.Connection -> User -> GroupInfo -> ChatItemId -> Int -> String -> UTCTime -> IO [ChatItemId]
+getGroupChatItemIdsBefore_ db User {userId} GroupInfo {groupId} beforeChatItemId count search beforeChatItemTs =
+  map fromOnly
+    <$> DB.query
+      db
+      [sql|
+        SELECT chat_item_id
+        FROM chat_items
+        WHERE user_id = ? AND group_id = ? AND item_text LIKE '%' || ? || '%'
+          AND (item_ts < ? OR (item_ts = ? AND chat_item_id < ?))
+        ORDER BY item_ts DESC, chat_item_id DESC
+        LIMIT ?
+      |]
+      (userId, groupId, search, beforeChatItemTs, beforeChatItemTs, beforeChatItemId, count)
+
+getGroupChatAround_ :: DB.Connection -> User -> GroupInfo -> ChatItemId -> Int -> String -> ExceptT StoreError IO (Chat 'CTGroup, Maybe ChatGap)
+getGroupChatAround_ db user g@GroupInfo {groupId} aroundItemId count search = do
+  let stats = ChatStats {unreadCount = 0, minUnreadItemId = 0, unreadChat = False}
+  let (fetchCountBefore, fetchCountAfter) = divideFetchCountAround_ (count - 1)
+  middleChatItem <- getGroupChatItem db user groupId aroundItemId
+  beforeIds <- liftIO $ getGroupChatItemIdsBefore_ db user g aroundItemId fetchCountBefore search (chatItemTs middleChatItem)
+  afterIds <- liftIO $ getGroupChatItemIdsAfter_ db user g aroundItemId fetchCountAfter search (chatItemTs middleChatItem)
+  currentTs <- liftIO getCurrentTime
+  beforeChatItems <- liftIO $ reverse <$> mapM (safeGetGroupItem db user g currentTs) beforeIds
+  afterChatItems <- liftIO $ mapM (safeGetGroupItem db user g currentTs) afterIds
+  let chatItems = beforeChatItems <> [middleChatItem] <> afterChatItems
+  gapToLatest <- liftIO $ getGroupChatItemGapToLatest_ db user g (last chatItems) search
+  let chatGap = if gapToLatest > 0 then Just $ ChatGap {size = gapToLatest, index = Nothing} else Nothing
+  pure (Chat (GroupChat g) chatItems stats, chatGap)
+
+getGroupChatInitial_ :: DB.Connection -> User -> GroupInfo -> Int -> ExceptT StoreError IO (Chat 'CTGroup, Maybe ChatGap)
+getGroupChatInitial_ db user@User {userId} g@GroupInfo {groupId} count = do
+  firstUnreadItemId_ <- liftIO getGroupChatMinUnreadItemId_
+  case firstUnreadItemId_ of
+    Just firstUnreadItemId -> do
+      (chat, gap) <- getGroupChatAround_ db user g firstUnreadItemId count ""
+      case gap of
+        Just ChatGap {size} -> do
+          if size > snd (divideFetchCountAround_ count)
+            then getLatestItems_ chat size
+            else pure (chat, Nothing)
+        Nothing -> pure (chat, Nothing)
+    Nothing -> liftIO $ (,Nothing) <$> getGroupChatLast_ db user g count ""
   where
-    getGroupChatItemIdsBefore_ :: UTCTime -> IO [ChatItemId]
+    getGroupChatMinUnreadItemId_ :: IO (Maybe ChatItemId)
-    getGroupChatItemIdsBefore_ beforeChatItemTs =
+    getGroupChatMinUnreadItemId_ =
-      map fromOnly
+      fmap join . maybeFirstRow fromOnly $
-        <$> DB.query
+        DB.query
           db
           [sql|
-            SELECT chat_item_id
+            SELECT MIN(chat_item_id)
             FROM chat_items
-            WHERE user_id = ? AND group_id = ? AND item_text LIKE '%' || ? || '%'
+            WHERE user_id = ? AND group_id = ? AND item_status = ?
-              AND (item_ts < ? OR (item_ts = ? AND chat_item_id < ?))
-            ORDER BY item_ts DESC, chat_item_id DESC
-            LIMIT ?
           |]
-          (userId, groupId, search, beforeChatItemTs, beforeChatItemTs, beforeChatItemId, count)
+          (userId, groupId, CISRcvNew)
+    getLatestItems_ :: Chat 'CTGroup -> Int -> ExceptT StoreError IO (Chat 'CTGroup, Maybe ChatGap)
+    getLatestItems_ c@Chat {chatItems} gapToLatest = do
+      currentTs <- liftIO getCurrentTime
+      latestItemIds <- liftIO $ getGroupChatItemIdsLast_ db user g (min count gapToLatest) ""
+      latestItems <- liftIO $ mapM (safeGetGroupItem db user g currentTs) latestItemIds
+      let allItems = chatItems <> latestItems
+      let chat = c {chatItems = allItems}
+      if gapToLatest > length latestItems
+        then pure (chat, Just $ ChatGap {size = gapToLatest - length latestItems, index = Just $ length chatItems})
+        else pure (chat, Nothing)
 
-getLocalChat :: DB.Connection -> User -> Int64 -> ChatPagination -> Maybe String -> ExceptT StoreError IO (Chat 'CTLocal)
+getLocalChat :: DB.Connection -> User -> Int64 -> ChatPagination -> Maybe String -> ExceptT StoreError IO (Chat 'CTLocal, Maybe ChatGap)
 getLocalChat db user folderId pagination search_ = do
   let search = fromMaybe "" search_
   nf <- getNoteFolder db user folderId
-  liftIO $ case pagination of
+  case pagination of
-    CPLast count -> getLocalChatLast_ db user nf count search
+    CPLast count -> liftIO $ (,Nothing) <$> getLocalChatLast_ db user nf count search
     CPAfter afterId count -> getLocalChatAfter_ db user nf afterId count search
     CPBefore beforeId count -> getLocalChatBefore_ db user nf beforeId count search
+    CPAround aroundId count -> getLocalChatAround_ db user nf aroundId count search
+    CPInitial count -> do
+      unless (null search) $ throwError $ SEInternalError "initial chat pagination doesn't support search"
+      getLocalChatInitial_ db user nf count
 
 getLocalChatLast_ :: DB.Connection -> User -> NoteFolder -> Int -> String -> IO (Chat 'CTLocal)
-getLocalChatLast_ db user@User {userId} nf@NoteFolder {noteFolderId} count search = do
+getLocalChatLast_ db user nf count search = do
   let stats = ChatStats {unreadCount = 0, minUnreadItemId = 0, unreadChat = False}
-  chatItemIds <- getLocalChatItemIdsLast_
+  chatItemIds <- getLocalChatItemIdsLast_ db user nf count search
   currentTs <- getCurrentTime
   chatItems <- mapM (safeGetLocalItem db user nf currentTs) chatItemIds
   pure $ Chat (LocalChat nf) (reverse chatItems) stats
-  where
+
-    getLocalChatItemIdsLast_ :: IO [ChatItemId]
+getLocalChatItemIdsLast_ :: DB.Connection -> User -> NoteFolder -> Int -> String -> IO [ChatItemId]
-    getLocalChatItemIdsLast_ =
+getLocalChatItemIdsLast_ db User {userId} NoteFolder {noteFolderId} count search =
-      map fromOnly
+  map fromOnly
-        <$> DB.query
+    <$> DB.query
-          db
+      db
-          [sql|
+      [sql|
-            SELECT chat_item_id
+        SELECT chat_item_id
-            FROM chat_items
+        FROM chat_items
-            WHERE user_id = ? AND note_folder_id = ? AND item_text LIKE '%' || ? || '%'
+        WHERE user_id = ? AND note_folder_id = ? AND item_text LIKE '%' || ? || '%'
-            ORDER BY created_at DESC, chat_item_id DESC
+        ORDER BY created_at DESC, chat_item_id DESC
-            LIMIT ?
+        LIMIT ?
-          |]
+      |]
-          (userId, noteFolderId, search, count)
+      (userId, noteFolderId, search, count)
+
+getLocalChatItemGapToLatest_ :: DB.Connection -> User -> NoteFolder -> CChatItem 'CTLocal -> String -> IO Int
+getLocalChatItemGapToLatest_ db User {userId} NoteFolder {noteFolderId} chatItem search = do
+  count <-
+    maybeFirstRow fromOnly $
+      DB.queryNamed
+        db
+        [sql|
+        SELECT COUNT(1)
+        FROM chat_items
+        WHERE user_id = :userId AND note_folder_id = :noteFolderId AND item_text LIKE '%' || :search || '%'
+          AND (created_at > :itemCreatedAt OR (created_at = :itemCreatedAt AND chat_item_id > :chatItemId))
+        ORDER BY created_at DESC, chat_item_id DESC
+      |]
+        [ ":userId" := userId,
+          ":noteFolderId" := noteFolderId,
+          ":search" := search,
+          ":itemCreatedAt" := chatItemCreatedAt chatItem,
+          ":chatItemId" := cchatItemId chatItem
+        ]
+  pure $ maybe 0 (\c -> max 0 (c - 1)) count
 
 safeGetLocalItem :: DB.Connection -> User -> NoteFolder -> UTCTime -> ChatItemId -> IO (CChatItem 'CTLocal)
 safeGetLocalItem db user NoteFolder {noteFolderId} currentTs itemId =
@@ -1245,51 +1428,106 @@ safeToLocalItem currentTs itemId = \case
                 file = Nothing
               }
 
-getLocalChatAfter_ :: DB.Connection -> User -> NoteFolder -> ChatItemId -> Int -> String -> IO (Chat 'CTLocal)
+getLocalChatAfter_ :: DB.Connection -> User -> NoteFolder -> ChatItemId -> Int -> String -> ExceptT StoreError IO (Chat 'CTLocal, Maybe ChatGap)
-getLocalChatAfter_ db user@User {userId} nf@NoteFolder {noteFolderId} afterChatItemId count search = do
+getLocalChatAfter_ db user nf@NoteFolder {noteFolderId} afterChatItemId count search = do
   let stats = ChatStats {unreadCount = 0, minUnreadItemId = 0, unreadChat = False}
-  chatItemIds <- getLocalChatItemIdsAfter_
+  afterChatItem <- getLocalChatItem db user noteFolderId afterChatItemId
-  currentTs <- getCurrentTime
+  chatItemIds <- liftIO $ getLocalChatItemIdsAfter_ db user nf afterChatItemId count search (chatItemCreatedAt afterChatItem)
-  chatItems <- mapM (safeGetLocalItem db user nf currentTs) chatItemIds
+  currentTs <- liftIO getCurrentTime
-  pure $ Chat (LocalChat nf) chatItems stats
+  chatItems <- liftIO $ mapM (safeGetLocalItem db user nf currentTs) chatItemIds
-  where
+  gapToLatest <- liftIO $ getLocalChatItemGapToLatest_ db user nf (head chatItems) search
-    getLocalChatItemIdsAfter_ :: IO [ChatItemId]
+  let chatGap = if gapToLatest > 0 then Just $ ChatGap {size = gapToLatest, index = Nothing} else Nothing
-    getLocalChatItemIdsAfter_ =
+  pure (Chat (LocalChat nf) chatItems stats, chatGap)
-      map fromOnly
-        <$> DB.query
-          db
-          [sql|
-            SELECT chat_item_id
-            FROM chat_items
-            WHERE user_id = ? AND note_folder_id = ? AND item_text LIKE '%' || ? || '%'
-              AND chat_item_id > ?
-            ORDER BY created_at ASC, chat_item_id ASC
-            LIMIT ?
-          |]
-          (userId, noteFolderId, search, afterChatItemId, count)
 
-getLocalChatBefore_ :: DB.Connection -> User -> NoteFolder -> ChatItemId -> Int -> String -> IO (Chat 'CTLocal)
+getLocalChatItemIdsAfter_ :: DB.Connection -> User -> NoteFolder -> ChatItemId -> Int -> String -> UTCTime -> IO [ChatItemId]
-getLocalChatBefore_ db user@User {userId} nf@NoteFolder {noteFolderId} beforeChatItemId count search = do
+getLocalChatItemIdsAfter_ db User {userId} NoteFolder {noteFolderId} afterChatItemId count search afterChatItemCreatedAt =
+  map fromOnly
+    <$> DB.query
+      db
+      [sql|
+        SELECT chat_item_id
+        FROM chat_items
+        WHERE user_id = ? AND note_folder_id = ? AND item_text LIKE '%' || ? || '%'
+          AND (created_at > ? OR (created_at = ? AND chat_item_id > ?))
+        ORDER BY created_at ASC, chat_item_id ASC
+        LIMIT ?
+      |]
+      (userId, noteFolderId, search, afterChatItemCreatedAt, afterChatItemCreatedAt, afterChatItemId, count)
+
+getLocalChatBefore_ :: DB.Connection -> User -> NoteFolder -> ChatItemId -> Int -> String -> ExceptT StoreError IO (Chat 'CTLocal, Maybe ChatGap)
+getLocalChatBefore_ db user nf@NoteFolder {noteFolderId} beforeChatItemId count search = do
   let stats = ChatStats {unreadCount = 0, minUnreadItemId = 0, unreadChat = False}
-  chatItemIds <- getLocalChatItemIdsBefore_
+  beforeChatItem <- getLocalChatItem db user noteFolderId beforeChatItemId
-  currentTs <- getCurrentTime
+  chatItemIds <- liftIO $ getLocalChatItemIdsBefore_ db user nf beforeChatItemId count search (chatItemCreatedAt beforeChatItem)
-  chatItems <- mapM (safeGetLocalItem db user nf currentTs) chatItemIds
+  currentTs <- liftIO getCurrentTime
-  pure $ Chat (LocalChat nf) (reverse chatItems) stats
+  chatItems <- liftIO $ mapM (safeGetLocalItem db user nf currentTs) chatItemIds
+  pure (Chat (LocalChat nf) (reverse chatItems) stats, Nothing)
+
+getLocalChatItemIdsBefore_ :: DB.Connection -> User -> NoteFolder -> ChatItemId -> Int -> String -> UTCTime -> IO [ChatItemId]
+getLocalChatItemIdsBefore_ db User {userId} NoteFolder {noteFolderId} beforeChatItemId count search beforeChatItemCreatedAt =
+  map fromOnly
+    <$> DB.query
+      db
+      [sql|
+        SELECT chat_item_id
+        FROM chat_items
+        WHERE user_id = ? AND note_folder_id = ? AND item_text LIKE '%' || ? || '%'
+          AND (created_at < ? OR (created_at = ? AND chat_item_id < ?))
+        ORDER BY created_at DESC, chat_item_id DESC
+        LIMIT ?
+      |]
+      (userId, noteFolderId, search, beforeChatItemCreatedAt, beforeChatItemCreatedAt, beforeChatItemId, count)
+
+getLocalChatAround_ :: DB.Connection -> User -> NoteFolder -> ChatItemId -> Int -> String -> ExceptT StoreError IO (Chat 'CTLocal, Maybe ChatGap)
+getLocalChatAround_ db user nf@NoteFolder {noteFolderId} aroundItemId count search = do
+  let stats = ChatStats {unreadCount = 0, minUnreadItemId = 0, unreadChat = False}
+  let (fetchCountBefore, fetchCountAfter) = divideFetchCountAround_ (count - 1)
+  middleChatItem <- getLocalChatItem db user noteFolderId aroundItemId
+  beforeIds <- liftIO $ getLocalChatItemIdsBefore_ db user nf aroundItemId fetchCountBefore search (chatItemCreatedAt middleChatItem)
+  afterIds <- liftIO $ getLocalChatItemIdsAfter_ db user nf aroundItemId fetchCountAfter search (chatItemCreatedAt middleChatItem)
+  currentTs <- liftIO getCurrentTime
+  beforeChatItems <- liftIO $ reverse <$> mapM (safeGetLocalItem db user nf currentTs) beforeIds
+  afterChatItems <- liftIO $ mapM (safeGetLocalItem db user nf currentTs) afterIds
+  let chatItems = beforeChatItems <> [middleChatItem] <> afterChatItems
+  gapToLatest <- liftIO $ getLocalChatItemGapToLatest_ db user nf (head chatItems) search
+  let chatGap = if gapToLatest > 0 then Just $ ChatGap {size = gapToLatest, index = Nothing} else Nothing
+  pure (Chat (LocalChat nf) chatItems stats, chatGap)
+
+getLocalChatInitial_ :: DB.Connection -> User -> NoteFolder -> Int -> ExceptT StoreError IO (Chat 'CTLocal, Maybe ChatGap)
+getLocalChatInitial_ db user@User {userId} nf@NoteFolder {noteFolderId} count = do
+  firstUnreadItemId_ <- liftIO getLocalChatMinUnreadItemId_
+  case firstUnreadItemId_ of
+    Just firstUnreadItemId -> do
+      (chat, gap) <- getLocalChatAround_ db user nf firstUnreadItemId count ""
+      case gap of
+        Just ChatGap {size} -> do
+          if size > snd (divideFetchCountAround_ count)
+            then getLatestItems_ chat size
+            else pure (chat, Nothing)
+        Nothing -> pure (chat, Nothing)
+    Nothing -> liftIO $ (,Nothing) <$> getLocalChatLast_ db user nf count ""
   where
-    getLocalChatItemIdsBefore_ :: IO [ChatItemId]
+    getLocalChatMinUnreadItemId_ :: IO (Maybe ChatItemId)
-    getLocalChatItemIdsBefore_ =
+    getLocalChatMinUnreadItemId_ =
-      map fromOnly
+      fmap join . maybeFirstRow fromOnly $
-        <$> DB.query
+        DB.query
           db
           [sql|
-            SELECT chat_item_id
+            SELECT MIN(chat_item_id)
             FROM chat_items
-            WHERE user_id = ? AND note_folder_id = ? AND item_text LIKE '%' || ? || '%'
+            WHERE user_id = ? AND note_folder_id = ? AND item_status = ?
-              AND chat_item_id < ?
-            ORDER BY created_at DESC, chat_item_id DESC
-            LIMIT ?
           |]
-          (userId, noteFolderId, search, beforeChatItemId, count)
+          (userId, noteFolderId, CISRcvNew)
+    getLatestItems_ :: Chat 'CTLocal -> Int -> ExceptT StoreError IO (Chat 'CTLocal, Maybe ChatGap)
+    getLatestItems_ c@Chat {chatItems} gapToLatest = do
+      currentTs <- liftIO getCurrentTime
+      latestItemIds <- liftIO $ getLocalChatItemIdsLast_ db user nf (min count gapToLatest) ""
+      latestItems <- liftIO $ mapM (safeGetLocalItem db user nf currentTs) latestItemIds
+      let allItems = chatItems <> latestItems
+      let chat = c {chatItems = allItems}
+      if gapToLatest > length latestItems
+        then pure (chat, Just $ ChatGap {size = gapToLatest - length latestItems, index = Just $ length chatItems})
+        else pure (chat, Nothing)
 
 toChatItemRef :: (ChatItemId, Maybe Int64, Maybe Int64, Maybe Int64) -> Either StoreError (ChatRef, ChatItemId)
 toChatItemRef = \case
@@ -1581,6 +1819,13 @@ getAllChatItems db vr user@User {userId} pagination search_ = do
       CPLast count -> liftIO $ getAllChatItemsLast_ count
       CPAfter afterId count -> liftIO . getAllChatItemsAfter_ afterId count . aChatItemTs =<< getAChatItem_ afterId
       CPBefore beforeId count -> liftIO . getAllChatItemsBefore_ beforeId count . aChatItemTs =<< getAChatItem_ beforeId
+      CPAround aroundId count -> liftIO . getAllChatItemsAround_ aroundId count . aChatItemTs =<< getAChatItem_ aroundId
+      CPInitial count -> do
+        unless (null search) $ throwError $ SEInternalError "initial chat pagination doesn't support search"
+        firstUnreadItemId <- liftIO getFirstUnreadItemId_
+        case firstUnreadItemId of
+          Just itemId -> liftIO . getAllChatItemsAround_ itemId count . aChatItemTs =<< getAChatItem_ itemId
+          Nothing -> liftIO $ getAllChatItemsLast_ count
   mapM (uncurry (getAChatItem db vr user)) itemRefs
   where
     search = fromMaybe "" search_
@@ -1624,6 +1869,31 @@ getAllChatItems db vr user@User {userId} pagination search_ = do
             LIMIT ?
           |]
           (userId, search, beforeTs, beforeTs, beforeId, count)
+    getChatItem chatId =
+      DB.query
+        db
+        [sql|
+          SELECT chat_item_id, contact_id, group_id, note_folder_id
+          FROM chat_items
+          WHERE chat_item_id = ?
+        |]
+        (Only chatId)
+    getAllChatItemsAround_ aroundId count aroundTs = do
+      let (fetchCountBefore, fetchCountAfter) = divideFetchCountAround_ (count - 1)
+      itemsBefore <- getAllChatItemsBefore_ aroundId fetchCountBefore aroundTs
+      item <- getChatItem aroundId
+      itemsAfter <- getAllChatItemsAfter_ aroundId fetchCountAfter aroundTs
+      pure $ itemsBefore <> item <> itemsAfter
+    getFirstUnreadItemId_ =
+      fmap join . maybeFirstRow fromOnly $
+        DB.query
+          db
+          [sql|
+            SELECT MIN(chat_item_id)
+            FROM chat_items
+            WHERE user_id = ? AND item_status = ?
+          |]
+          (userId, CISRcvNew)
 
 getChatItemIdsByAgentMsgId :: DB.Connection -> Int64 -> AgentMsgId -> IO [ChatItemId]
 getChatItemIdsByAgentMsgId db connId msgId =
@@ -2650,3 +2920,10 @@ getGroupHistoryItems db user@User {userId} GroupInfo {groupId} count = do
             LIMIT ?
           |]
           (userId, groupId, rcvMsgContentTag, sndMsgContentTag, count)
+
+divideFetchCountAround_ :: Int -> (Int, Int)
+divideFetchCountAround_ count =
+  let fetchCountEachSide = count `div` 2
+      fetchCountBefore = fetchCountEachSide + if count `mod` 2 /= 0 then 1 else 0
+      fetchCountAfter = fetchCountEachSide
+   in (fetchCountBefore, fetchCountAfter)

src/Simplex/Chat/Store/Migrations.hs

@@ -114,6 +114,7 @@ import Simplex.Chat.Migrations.M20240827_calls_uuid
 import Simplex.Chat.Migrations.M20240920_user_order
 import Simplex.Chat.Migrations.M20241008_indexes
 import Simplex.Chat.Migrations.M20241010_contact_requests_contact_id
+import Simplex.Chat.Migrations.M20241023_chat_item_autoincrement_id
 import Simplex.Messaging.Agent.Store.SQLite.Migrations (Migration (..))
 
 schemaMigrations :: [(String, Query, Maybe Query)]
@@ -227,7 +228,8 @@ schemaMigrations =
     ("20240827_calls_uuid", m20240827_calls_uuid, Just down_m20240827_calls_uuid),
     ("20240920_user_order", m20240920_user_order, Just down_m20240920_user_order),
     ("20241008_indexes", m20241008_indexes, Just down_m20241008_indexes),
-    ("20241010_contact_requests_contact_id", m20241010_contact_requests_contact_id, Just down_m20241010_contact_requests_contact_id)
+    ("20241010_contact_requests_contact_id", m20241010_contact_requests_contact_id, Just down_m20241010_contact_requests_contact_id),
+    ("20241023_chat_item_autoincrement_id", m20241023_chat_item_autoincrement_id, Just down_m20241023_chat_item_autoincrement_id)
   ]
 
 -- | The list of migrations in ascending order by date

src/Simplex/Chat/View.hs

@@ -93,7 +93,7 @@ responseToView hu@(currentRH, user_) ChatConfig {logLevel, showReactions, showRe
   CRChatSuspended -> ["chat suspended"]
   CRApiChats u chats -> ttyUser u $ if testView then testViewChats chats else [viewJSON chats]
   CRChats chats -> viewChats ts tz chats
-  CRApiChat u chat -> ttyUser u $ if testView then testViewChat chat else [viewJSON chat]
+  CRApiChat u chat gap -> ttyUser u $ if testView then testViewChat chat else [viewJSON chat] <> viewChatGap gap
   CRApiParsedMarkdown ft -> [viewJSON ft]
   CRUserProtoServers u userServers -> ttyUser u $ viewUserServers userServers testView
   CRServerTestResult u srv testFailure -> ttyUser u $ viewServerTestResult srv testFailure
@@ -555,6 +555,12 @@ viewUsersList us =
 viewGroupSubscribed :: GroupInfo -> [StyledString]
 viewGroupSubscribed g = [membershipIncognito g <> ttyFullGroup g <> ": connected to server(s)"]
 
+viewChatGap :: Maybe ChatGap -> [StyledString]
+viewChatGap Nothing = []
+viewChatGap (Just ChatGap {size})
+  | size < 1 = []
+  | otherwise = [sShow size <> " newer message(s) available"]
+
 showSMPServer :: SMPServer -> String
 showSMPServer ProtocolServer {host} = B.unpack $ strEncode host
 

tests/ChatTests/Direct.hs

@@ -210,6 +210,7 @@ testAddContact = versionTestMatrix2 runTestAddContact
           -- pagination
           alice #$> ("/_get chat @2 after=" <> itemId 1 <> " count=100", chat, [(0, "hello there"), (0, "how are you?")])
           alice #$> ("/_get chat @2 before=" <> itemId 2 <> " count=100", chat, features <> [(1, "hello there 🙂")])
+          alice #$> ("/_get chat @2 around=" <> itemId 2 <> " count=3", chat, [(1, "hello there 🙂"), (0, "hello there"), (0, "how are you?")])
           -- search
           alice #$> ("/_get chat @2 count=100 search=ello ther", chat, [(1, "hello there 🙂"), (0, "hello there")])
           -- read messages
@@ -791,7 +792,7 @@ testDirectMessageDelete =
       alice @@@ [("@bob", lastChatFeature)]
       alice #$> ("/_get chat @2 count=100", chat, chatFeatures)
 
-      -- alice: msg id 1
+      -- alice: msg id 3
       bob ##> ("/_update item @2 " <> itemId 2 <> " text hey alice")
       bob <# "@alice [edited] > hello 🙂"
       bob <## "      hey alice"
@@ -806,12 +807,12 @@ testDirectMessageDelete =
       alice @@@ [("@bob", "hey alice [marked deleted]")]
       alice #$> ("/_get chat @2 count=100", chat, chatFeatures <> [(0, "hey alice [marked deleted]")])
 
-      -- alice: deletes msg id 1 that was broadcast deleted by bob
+      -- alice: deletes msg id 3 that was broadcast deleted by bob
-      alice #$> ("/_delete item @2 " <> itemId 1 <> " internal", id, "message deleted")
+      alice #$> ("/_delete item @2 " <> itemId 3 <> " internal", id, "message deleted")
       alice @@@ [("@bob", lastChatFeature)]
       alice #$> ("/_get chat @2 count=100", chat, chatFeatures)
 
-      -- alice: msg id 1, bob: msg id 3 (quoting message alice deleted locally)
+      -- alice: msg id 4, bob: msg id 3 (quoting message alice deleted locally)
       bob `send` "> @alice (hello 🙂) do you receive my messages?"
       bob <# "@alice > hello 🙂"
       bob <## "      do you receive my messages?"
@@ -819,14 +820,14 @@ testDirectMessageDelete =
       alice <## "      do you receive my messages?"
       alice @@@ [("@bob", "do you receive my messages?")]
       alice #$> ("/_get chat @2 count=100", chat', chatFeatures' <> [((0, "do you receive my messages?"), Just (1, "hello 🙂"))])
-      alice #$> ("/_delete item @2 " <> itemId 1 <> " broadcast", id, "cannot delete this item")
+      alice #$> ("/_delete item @2 " <> itemId 4 <> " broadcast", id, "cannot delete this item")
 
-      -- alice: msg id 2, bob: msg id 4
+      -- alice: msg id 5, bob: msg id 4
       bob #> "@alice how are you?"
       alice <# "bob> how are you?"
 
-      -- alice: deletes msg id 2
+      -- alice: deletes msg id 5
-      alice #$> ("/_delete item @2 " <> itemId 2 <> " internal", id, "message deleted")
+      alice #$> ("/_delete item @2 " <> itemId 5 <> " internal", id, "message deleted")
 
       -- bob: marks deleted msg id 4 (that alice deleted locally)
       bob #$> ("/_delete item @2 " <> itemId 4 <> " broadcast", id, "message marked deleted")
@@ -2340,6 +2341,12 @@ testUserPrivacy =
                "bob> Voice messages: enabled",
                "bob> Audio/video calls: enabled"
              ]
+      alice ##> "/_get items around=11 count=3"
+      alice
+        <##? [ "bob> Message reactions: enabled",
+               "bob> Voice messages: enabled",
+               "bob> Audio/video calls: enabled"
+             ]
       alice ##> "/_get items after=12 count=10"
       alice
         <##? [ "@bob hello",

tests/ChatTests/Groups.hs

@@ -344,6 +344,7 @@ testGroupShared alice bob cath checkMessages directConnections = do
       -- so we take into account group event items as well as sent group invitations in direct chats
       alice #$> ("/_get chat #1 after=" <> msgItem1 <> " count=100", chat, [(0, "hi there"), (0, "hey team")])
       alice #$> ("/_get chat #1 before=" <> msgItem2 <> " count=100", chat, [(1, e2eeInfoNoPQStr), (0, "connected"), (0, "connected"), (1, "hello"), (0, "hi there")])
+      alice #$> ("/_get chat #1 around=" <> msgItem2 <> " count=6", chat, [(0, "connected"), (1, "hello"), (0, "hi there"), (0, "hey team")]) -- expecting only 4 items since there is only 1 item after
       alice #$> ("/_get chat #1 count=100 search=team", chat, [(0, "hey team")])
       bob @@@ [("@cath", "hey"), ("#team", "hey team"), ("@alice", "received invitation to join group team as admin")]
       bob #$> ("/_get chat #1 count=100", chat, groupFeatures <> [(0, "connected"), (0, "added cath (Catherine)"), (0, "connected"), (0, "hello"), (1, "hi there"), (0, "hey team")])

tests/ChatTests/Local.hs

@@ -51,7 +51,7 @@ testNotes tmp = withNewTestChat tmp "alice" aliceProfile $ \alice -> do
   alice ##> "/chats"
 
   alice /* "ahoy!"
-  alice ##> "/_update item *1 1 text Greetings."
+  alice ##> "/_update item *1 2 text Greetings."
   alice ##> "/tail *"
   alice <# "* Greetings."
 
@@ -102,6 +102,10 @@ testChatPagination tmp = withNewTestChat tmp "alice" aliceProfile $ \alice -> do
 
   alice #$> ("/_get chat *1 count=100", chat, [(1, "hello world"), (1, "memento mori"), (1, "knock-knock"), (1, "who's there?")])
   alice #$> ("/_get chat *1 count=1", chat, [(1, "who's there?")])
+  alice #$> ("/_get chat *1 around=2 count=1", chat, [(1, "memento mori")])
+  alice #$> ("/_get chat *1 around=2 count=3", chat, [(1, "hello world"), (1, "memento mori"), (1, "knock-knock")])
+  alice #$> ("/_get chat *1 around=3 count=10", chat, [(1, "hello world"), (1, "memento mori"), (1, "knock-knock"), (1, "who's there?")])
+  alice #$> ("/_get chat *1 around=4 count=3", chat, [(1, "knock-knock"), (1, "who's there?")])
   alice #$> ("/_get chat *1 after=2 count=10", chat, [(1, "knock-knock"), (1, "who's there?")])
   alice #$> ("/_get chat *1 after=2 count=2", chat, [(1, "knock-knock"), (1, "who's there?")])
   alice #$> ("/_get chat *1 after=1 count=2", chat, [(1, "memento mori"), (1, "knock-knock")])

tests/SchemaDump.hs

@@ -102,7 +102,9 @@ skipComparisonForDownMigrations =
     -- table and indexes move down to the end of the file
     "20231215_recreate_msg_deliveries",
     -- on down migration idx_msg_deliveries_agent_ack_cmd_id index moves down to the end of the file
-    "20240313_drop_agent_ack_cmd_id"
+    "20240313_drop_agent_ack_cmd_id",
+    -- on down migration chat_item_autoincrement_id makes sequence table creation move down on the file
+    "20241023_chat_item_autoincrement_id"
   ]
 
 getSchema :: FilePath -> FilePath -> IO String