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())
    }

}