start dms

2 files changed, 213 insertions, 0 deletions

diff --git a/src/database/chat.rs b/src/database/chat.rs
new file mode 100644
index 0000000..7364211
--- /dev/null
+++ b/src/database/chat.rs
@@ -0,0 +1,211 @@
+use std::time::{SystemTime, UNIX_EPOCH, Duration};
+
+use tracing::instrument;
+
+use crate::types::chat::{ChatRoom, ChatMessage};
+
+use super::Database;
+
+impl Database {
+    
+    #[instrument(skip(self))]
+    pub fn init_chat(&self) -> Result<(), rusqlite::Error> {
+        let sql = "
+            CREATE TABLE IF NOT EXISTS chat_rooms (
+                room_id     INTEGER PRIMARY KEY AUTOINCREMENT,
+                name        VARCHAR(255) NOT NULL
+            );
+        ";
+        self.0.execute(sql, ())?;
+        
+        let sql2 = "
+            CREATE TABLE IF NOT EXISTS chat_users (
+                room_id     INTEGER NOT NULL,
+                user_id     INTEGER NOT NULL,
+                FOREIGN KEY(room_id) REFERENCES chat_rooms(room_id),
+                FOREIGN KEY(user_id) REFERENCES users(user_id),
+                PRIMARY KEY (room_id, user_id)
+            );
+        ";
+        self.0.execute(sql2, ())?;
+        
+        let sql3 = "
+            CREATE TABLE IF NOT EXISTS chat_messages (
+                message_id  INTEGER PRIMARY KEY AUTOINCREMENT,
+                user_id     INTEGER NOT NULL,
+                room_id     INTEGER NOT NULL,
+                date        INTEGER NOT NULL,
+                content     VARCHAR(500) NOT NULL,
+                FOREIGN KEY(user_id) REFERENCES users(user_id),
+                FOREIGN KEY(room_id) REFERENCES chat_rooms(room_id)
+            );
+        ";
+        self.0.execute(sql3, ())?;
+        
+        let sql4 = "CREATE INDEX IF NOT EXISTS chat_message_ids ON chat_messages(room_id);";
+        self.0.execute(sql4, ())?;
+
+        Ok(())
+    }
+
+    #[instrument(skip(self))]
+    pub fn get_rooms(&self, user_id: u64) -> Result<Vec<ChatRoom>, rusqlite::Error> {
+        tracing::trace!("Retrieving rooms");
+        let mut stmt = self.0.prepare(
+            "
+            SELECT * FROM chat_rooms
+            WHERE room_id IN (
+                SELECT room_id 
+                FROM chat_users
+                WHERE user_id = ?
+            );
+        ",
+        )?;
+        let row = stmt.query_map([user_id], |row| {
+            let room_id = row.get(0)?;
+            let name = row.get(1)?;
+
+            let mut stmt2 = self.0.prepare(
+                "
+                SELECT user_id FROM chat_users
+                WHERE room_id = ?;
+                "
+            )?;
+
+            let mut users = Vec::new();
+            let _ = stmt2.query_map([room_id], |row2| { 
+                let user_id = row2.get(0)?;
+                users.push(user_id);
+                Ok(())
+            })?;
+
+            let room = ChatRoom {
+                room_id,
+                users,
+                name
+            };
+            Ok(room)
+        })?;
+        Ok(row.into_iter().flatten().collect())
+    }
+
+    #[instrument(skip(self))]
+    pub fn create_room(&self, users: Vec<u64>, name: String) -> Result<ChatRoom, rusqlite::Error> {
+        tracing::trace!("Creating new room");
+        let mut stmt = self.0.prepare(
+            "INSERT INTO chat_rooms (name) VALUES (?) RETURNING *;"
+        )?;
+        let mut room = stmt.query_row([name], |row| {
+            let room_id = row.get(0)?;
+            let name = row.get(1)?;
+            Ok(ChatRoom {
+                room_id,
+                users: Vec::new(),
+                name
+            })
+        })?;
+
+        let mut stmt2 = self.0.prepare(
+            "INSERT INTO chat_users (room_id, user_id) VALUES (?, ?);"
+        )?;
+
+        for user_id in users {
+            stmt2.execute([room.room_id, user_id])?;
+            room.users.push(user_id);
+        }
+
+        Ok(room)
+    }
+    
+    #[instrument(skip(self))]
+    pub fn add_user_to_room(&self, room_id: u64, user_id: u64) -> Result<bool, rusqlite::Error> {
+        tracing::trace!("Adding user to room");
+        let mut stmt = self.0.prepare(
+            "INSERT OR REPLACE INTO chat_users (room_id, user_id) VALUES(?,?);"
+        )?;
+
+        let changes = stmt.execute([room_id, user_id])?;
+
+        Ok(changes == 1)
+    }
+ 
+    #[instrument(skip(self))]
+    pub fn remove_user_from_room(&self, room_id: u64, user_id: u64) -> Result<bool, rusqlite::Error> {
+        tracing::trace!("Removing user from room");
+        let mut stmt = self.0.prepare(
+            "DELETE FROM chat_users WHERE room_id = ? AND user_id = ?;"
+        )?;
+
+        let changes = stmt.execute([room_id, user_id])?;
+
+        Ok(changes == 1)
+    }
+    
+    #[instrument(skip(self))]
+    pub fn create_message(&self, room_id: u64, user_id: u64, content: String) -> Result<ChatMessage, rusqlite::Error> {
+        tracing::trace!("Creating new chat message");
+        let date = u64::try_from(
+            SystemTime::now()
+                .duration_since(UNIX_EPOCH)
+                .unwrap_or(Duration::ZERO)
+                .as_millis(),
+        )
+        .unwrap_or(0);
+
+        let mut stmt = self.0.prepare(
+            "INSERT INTO chat_messages (user_id, room_id, date, content) VALUES (?,?,?,?) RETURNING *;"
+        )?;
+
+        let msg = stmt.query_row((user_id, room_id, date, content), |row| {
+            let message_id = row.get(0)?;
+            let room_id = row.get(1)?;
+            let user_id = row.get(2)?;
+            let date = row.get(3)?;
+            let content = row.get(4)?;
+
+            Ok(ChatMessage {
+                message_id,
+                room_id,
+                user_id,
+                date,
+                content
+            })
+        })?;
+        
+        Ok(msg)
+    }
+
+    #[instrument(skip(self))]
+    pub fn load_old_chat_messages(&self, room_id: u64, newest_message: u64, page: u64) -> Result<Vec<ChatMessage>, rusqlite::Error> {
+        tracing::trace!("Loading old chat messages");
+        let mut stmt = self.0.prepare(
+        "
+        SELECT * FROM chat_messages
+        WHERE room_id = ?
+        AND message_id < newest_message
+        ORDER BY message_id ASC
+        LIMIT ?
+        OFFSET ?
+        "
+        )?;
+
+        let messages = stmt.query_map((room_id, 20, 20 * page), |row| {
+            let message_id = row.get(0)?;
+            let room_id = row.get(1)?;
+            let user_id = row.get(2)?;
+            let date = row.get(3)?;
+            let content = row.get(4)?;
+
+            Ok(ChatMessage {
+                message_id,
+                room_id,
+                user_id,
+                date,
+                content
+            })
+        })?;
+
+        Ok(messages.into_iter().flatten().collect())
+    }
+
+}
diff --git a/src/database/mod.rs b/src/database/mod.rs
index 67e05c6..7d0928f 100644
--- a/src/database/mod.rs
+++ b/src/database/mod.rs
@@ -1,6 +1,7 @@
 use rusqlite::Connection;
 use tracing::instrument;
 
+pub mod chat;
 pub mod comments;
 pub mod friends;
 pub mod likes;
@@ -32,5 +33,6 @@ pub fn init() -> Result<(), rusqlite::Error> {
     db.init_likes()?;
     db.init_comments()?;
     db.init_friends()?;
+    db.init_chat()?;
     Ok(())
 }