feat(filamento): `get_chats_ordered_with_latest_messages_and_users()`

1 files changed, 185 insertions, 13 deletions

diff --git a/filamento/src/db.rs b/filamento/src/db.rs
index cf21325..d9206cc 100644
--- a/filamento/src/db.rs
+++ b/filamento/src/db.rs
@@ -1,12 +1,12 @@
 use std::{collections::HashSet, path::Path};
 
-use chrono::Utc;
+use chrono::{DateTime, Utc};
 use jid::JID;
 use sqlx::{SqlitePool, migrate};
 use uuid::Uuid;
 
 use crate::{
-    chat::{Chat, Message},
+    chat::{Body, Chat, Delivery, Message},
     error::{DatabaseError as Error, DatabaseOpenError},
     presence::Online,
     roster::Contact,
@@ -51,10 +51,9 @@ impl Db {
 
     pub(crate) async fn create_user(&self, user: User) -> Result<(), Error> {
         sqlx::query!(
-            "insert into users ( jid, nick, cached_status_message ) values ( ?, ?, ? )",
+            "insert into users ( jid, nick ) values ( ?, ? )",
             user.jid,
             user.nick,
-            user.cached_status_message
         )
         .execute(&self.db)
         .await?;
@@ -184,8 +183,7 @@ impl Db {
 
     pub(crate) async fn update_user(&self, user: User) -> Result<(), Error> {
         sqlx::query!(
-            "update users set cached_status_message = ?, nick = ? where jid = ?",
-            user.cached_status_message,
+            "update users set nick = ? where jid = ?",
             user.nick,
             user.jid
         )
@@ -499,23 +497,197 @@ impl Db {
         &self,
     ) -> Result<Vec<(Chat, Message)>, Error> {
         #[derive(sqlx::FromRow)]
-        pub struct ChatWithMessage {
+        pub struct RowChat {
+            chat_correspondent: JID,
+            chat_have_chatted: bool,
+        }
+        impl From<RowChat> for Chat {
+            fn from(value: RowChat) -> Self {
+                Self {
+                    correspondent: value.chat_correspondent,
+                    have_chatted: value.chat_have_chatted,
+                }
+            }
+        }
+        #[derive(sqlx::FromRow)]
+        pub struct RowMessage {
+            message_id: Uuid,
+            message_body: String,
+            message_delivery: Option<Delivery>,
+            message_timestamp: DateTime<Utc>,
+            message_from_jid: JID,
+        }
+        impl From<RowMessage> for Message {
+            fn from(value: RowMessage) -> Self {
+                Self {
+                    id: value.message_id,
+                    from: value.message_from_jid,
+                    delivery: value.message_delivery,
+                    timestamp: value.message_timestamp,
+                    body: Body {
+                        body: value.message_body,
+                    },
+                }
+            }
+        }
+
+        #[derive(sqlx::FromRow)]
+        pub struct ChatWithMessageRow {
             #[sqlx(flatten)]
-            pub chat: Chat,
+            pub chat: RowChat,
             #[sqlx(flatten)]
-            pub message: Message,
+            pub message: RowMessage,
+        }
+
+        pub struct ChatWithMessage {
+            chat: Chat,
+            message: Message,
+        }
+
+        impl From<ChatWithMessageRow> for ChatWithMessage {
+            fn from(value: ChatWithMessageRow) -> Self {
+                Self {
+                    chat: value.chat.into(),
+                    message: value.message.into(),
+                }
+            }
         }
 
         // TODO: sometimes chats have no messages.
-        // TODO: i don't know if this will assign the right uuid to the latest message or the chat's id. should probably check but i don't think it matters as nothing ever gets called with the id of the latest message in the chats list
-        // TODO: it does matter in fact, as message updates and delivery receipts need to go to the right latest_message
-        let chats: Vec<ChatWithMessage> = sqlx::query_as("select c.*, m.* from chats c join (select chat_id, max(timestamp) max_timestamp from messages group by chat_id) max_timestamps on c.id = max_timestamps.chat_id join messages m on max_timestamps.chat_id = m.chat_id and max_timestamps.max_timestamp = m.timestamp order by m.timestamp desc")
+        let chats: Vec<ChatWithMessageRow> = sqlx::query_as("select c.*, m.* from chats c join (select chat_id, max(timestamp) max_timestamp from messages group by chat_id) max_timestamps on c.id = max_timestamps.chat_id join messages m on max_timestamps.chat_id = m.chat_id and max_timestamps.max_timestamp = m.timestamp order by m.timestamp desc")
+            .fetch_all(&self.db)
+            .await?;
+
+        let chats = chats
+            .into_iter()
+            .map(|chat_with_message_row| {
+                let chat_with_message: ChatWithMessage = chat_with_message_row.into();
+                (chat_with_message.chat, chat_with_message.message)
+            })
+            .collect();
+
+        Ok(chats)
+    }
+
+    /// chats ordered by date of last message
+    // greatest-n-per-group
+    pub(crate) async fn read_chats_ordered_with_latest_messages_and_users(
+        &self,
+    ) -> Result<Vec<((Chat, User), (Message, User))>, Error> {
+        #[derive(sqlx::FromRow)]
+        pub struct RowChat {
+            chat_correspondent: JID,
+            chat_have_chatted: bool,
+        }
+        impl From<RowChat> for Chat {
+            fn from(value: RowChat) -> Self {
+                Self {
+                    correspondent: value.chat_correspondent,
+                    have_chatted: value.chat_have_chatted,
+                }
+            }
+        }
+        #[derive(sqlx::FromRow)]
+        pub struct RowMessage {
+            message_id: Uuid,
+            message_body: String,
+            message_delivery: Option<Delivery>,
+            message_timestamp: DateTime<Utc>,
+            message_from_jid: JID,
+        }
+        impl From<RowMessage> for Message {
+            fn from(value: RowMessage) -> Self {
+                Self {
+                    id: value.message_id,
+                    from: value.message_from_jid,
+                    delivery: value.message_delivery,
+                    timestamp: value.message_timestamp,
+                    body: Body {
+                        body: value.message_body,
+                    },
+                }
+            }
+        }
+        #[derive(sqlx::FromRow)]
+        pub struct RowChatUser {
+            chat_user_jid: JID,
+            chat_user_nick: Option<String>,
+            chat_user_avatar: Option<String>,
+        }
+        impl From<RowChatUser> for User {
+            fn from(value: RowChatUser) -> Self {
+                Self {
+                    jid: value.chat_user_jid,
+                    nick: value.chat_user_nick,
+                    avatar: value.chat_user_avatar,
+                }
+            }
+        }
+        #[derive(sqlx::FromRow)]
+        pub struct RowMessageUser {
+            message_user_jid: JID,
+            message_user_nick: Option<String>,
+            message_user_avatar: Option<String>,
+        }
+        impl From<RowMessageUser> for User {
+            fn from(value: RowMessageUser) -> Self {
+                Self {
+                    jid: value.message_user_jid,
+                    nick: value.message_user_nick,
+                    avatar: value.message_user_avatar,
+                }
+            }
+        }
+        #[derive(sqlx::FromRow)]
+        pub struct ChatWithMessageAndUsersRow {
+            #[sqlx(flatten)]
+            pub chat: RowChat,
+            #[sqlx(flatten)]
+            pub chat_user: RowChatUser,
+            #[sqlx(flatten)]
+            pub message: RowMessage,
+            #[sqlx(flatten)]
+            pub message_user: RowMessageUser,
+        }
+
+        impl From<ChatWithMessageAndUsersRow> for ChatWithMessageAndUsers {
+            fn from(value: ChatWithMessageAndUsersRow) -> Self {
+                Self {
+                    chat: value.chat.into(),
+                    chat_user: value.chat_user.into(),
+                    message: value.message.into(),
+                    message_user: value.message_user.into(),
+                }
+            }
+        }
+
+        pub struct ChatWithMessageAndUsers {
+            chat: Chat,
+            chat_user: User,
+            message: Message,
+            message_user: User,
+        }
+
+        let chats: Vec<ChatWithMessageAndUsersRow> = sqlx::query_as("select c.id as chat_id, c.correspondent as chat_correspondent, c.have_chatted as chat_have_chatted, m.id as message_id, m.body as message_body, m.delivery as message_delivery, m.timestamp as message_timestamp, m.from_jid as message_from_jid, cu.jid as chat_user_jid, cu.nick as chat_user_nick, cu.avatar as chat_user_avatar, mu.jid as message_user_jid, mu.nick as message_user_nick, mu.avatar as message_user_avatar from chats c join (select chat_id, max(timestamp) max_timestamp from messages group by chat_id) max_timestamps on c.id = max_timestamps.chat_id join messages m on max_timestamps.chat_id = m.chat_id and max_timestamps.max_timestamp = m.timestamp join users as cu on cu.jid = c.correspondent join users as mu on mu.jid = m.from_jid order by m.timestamp desc")
             .fetch_all(&self.db)
             .await?;
 
         let chats = chats
             .into_iter()
-            .map(|chat_with_message| (chat_with_message.chat, chat_with_message.message))
+            .map(|chat_with_message_and_users_row| {
+                let chat_with_message_and_users: ChatWithMessageAndUsers =
+                    chat_with_message_and_users_row.into();
+                (
+                    (
+                        chat_with_message_and_users.chat,
+                        chat_with_message_and_users.chat_user,
+                    ),
+                    (
+                        chat_with_message_and_users.message,
+                        chat_with_message_and_users.message_user,
+                    ),
+                )
+            })
             .collect();
 
         Ok(chats)