pub mod r#types;

use sqlite::Connection;
use super::*;


pub struct Database {
    db: Connection,
    newest_message_id: Option<UID>,
}
impl Database {
    pub fn get_message(&self, id: usize) -> Option<Message> {
        let query ="SELECT * FROM messages WHERE id IS :id";
        let statement = self.db.prepare(query).unwrap().into_iter().bind((":id", id as i64)).unwrap();

        for row in statement.map(|row| row.unwrap()) {
            let msg = self.message_from_row(row);

            return Some(msg)
        }

        None
    }
    pub fn get_message_id_list(&self, id_start: UID, id_end: UID) -> Vec<UID> {
        let query = "SELECT id FROM messages WHERE id BETWEEN :id_start AND :id_end";
        let statement = self.db.prepare(query).unwrap().into_iter().bind::<&[(_, sqlite::Value)]>(&[
            (":id_start", id_start.into()),
            (":id_end", id_end.into()),
            ]).unwrap();


        let mut message_id_vec: Vec<UID> = vec![];
        for row in statement.map(|row| row.unwrap()) {
            let id = row.read::<i64, _>("id").into();

            message_id_vec.push(id);
        }

        return message_id_vec;
    }
    pub fn get_next_message_id(&self) -> UID {
        UID::from(self.get_table_length("messages"))
    }
    pub fn get_message_newest_id(&mut self) -> Option<UID> {
        match self.newest_message_id {
            None => {
                self.newest_message_id = match self.get_table_length("messages") {
                0 => None,
                n => Some(UID::new(n - 1)),
                }
            },
            Some(_) => ()
        }

        self.newest_message_id
    }

    fn message_from_row(&self, row: sqlite::Row) -> Message{
        let reply_to = row.read::<Option<i64>, _>("reply_to");
        let reply_to = match reply_to {
            Some(n) => Some(n.into()),
            None => None
        };

        let date = row.read::<i64, _>("date").into();
        let deleted = row.read::<i64, _>("deleted");
        let deleted = if deleted < 1 {
            false
        } else {
            true 
        };

        let msg = if deleted {
            ""
        } else {
            row.read::<&str, _>("message")
        };

        let sender = row.read::<i64, _>("sender").into();
        let id = row.read::<i64, _>("id").into();


        Message::construct(String::from(msg), sender, id, reply_to, date, deleted)
    }

    pub fn get_message_list(&self, id_start: UID, id_end: UID) -> Vec<Message> {
        let query = "SELECT * FROM messages WHERE id BETWEEN :id_start AND :id_end";
        let statement = self.db.prepare(query).unwrap().into_iter().bind::<&[(_, sqlite::Value)]>(&[
            (":id_start", id_start.into()),
            (":id_end", id_end.into()),
            ]).unwrap();


        let mut message_id_vec: Vec<Message> = vec![];
        for row in statement.map(|row| row.unwrap()) {
            let msg = self.message_from_row(row);

            message_id_vec.push(msg);
        }

        return message_id_vec;
    }

    pub fn send_message(&mut self, msg: &mut Message) -> Result<UID, &'static str> {
        // Check if user exists
        match self.get_user(msg.sender()) {
            Some(n) => match n.deleted() {
                true => return Err("User deleted"),
                false => ()
            },
            None => return Err("User not found"),
        }
        
        // Truncate whitespace on the ends of the message
        let trunc_message = msg.message().trim().to_string();
        msg.set_message(trunc_message);

        // Check if message is empty
        if msg.message().len() < 1 {
            return Err("Message cannot be empty")
        }

        let query = "INSERT INTO messages (date, sender, message, id, reply_to, deleted) VALUES (:date, :sender, :message, :id, :reply_to, :deleted)";
        let statement = self.db.prepare(query).unwrap().into_iter()
        .bind::<&[(_, sqlite::Value)]>(&[
            (":date", msg.date().into()),
            (":sender", msg.sender().into()),
            (":message", msg.message().into()),
            (":reply_to", match msg.reply_to() {
                Some(n) => n.into(),
                None => ().into()
            }),
            (":id", msg.id().into()),
            (":deleted", 0.into()),
            ]);

        for x in statement.unwrap() {
            match x {
                Ok(_) => (),
                Err(n) => {
                    match n.code.unwrap_or(0) {
                        _ => return Err("Unknown error")
                    }
                },
            }
        }

        self.newest_message_id = Some(msg.id());

        Ok(msg.id())
    }

    pub fn delete_message(&mut self, id: UID) -> Result<&'static str, &'static str> {
        let query = "UPDATE messages SET deleted=:deleted WHERE id IS :id AND deleted IS false";
        let statement = self.db.prepare(query).unwrap().into_iter()
            .bind::<&[(_, sqlite::Value)]>(&[
            (":deleted", 1.into()),
            (":id", id.into()),
            ]);

        let change_count = self.db.change_count();

        for x in statement.unwrap() {
            match x {
                Ok(_) => (),
                Err(n) => {
                    match n.code.unwrap_or(0) {
                        _ => return Err("Unknown error")
                    }
                },
            }
        }

        if change_count > 0 {
            Ok("Deleted")
        } else {
            Err("Unabled to delete")
        }
    }

    pub fn set_user_field(&self, f: DataField, user: UID, data: String) -> Result<&'static str, &'static str> {
        let query = format!("UPDATE users SET {}=:data WHERE id IS :id AND deleted IS false", f);
        let statement = self.db.prepare(query).unwrap().into_iter()
        .bind::<&[(_, sqlite::Value)]>(&[
        (":id", user.into()),
        (":data", data.into()),
        ]);

        let change_count = self.db.change_count();

        for x in statement.unwrap() {
            match x {
                Ok(_) => (),
                Err(n) => {
                    match n.code.unwrap_or(0) {
                        _ => return Err("Unknown error")
                    }
                },
            }
        }

        if change_count > 0 {
            Ok("Updated")
        } else {
            Err("Unabled to update")
        }
    }

    pub fn set_user_status(&self, user: UID, status: Status) -> Result<&'static str, &'static str> {
        let query = "UPDATE users SET status=:status WHERE id IS :id AND deleted IS false";
        let statement = self.db.prepare(query).unwrap().into_iter()
        .bind::<&[(_, sqlite::Value)]>(&[
        (":id", user.into()),
        (":status", i64::from(status).into()),
        ]);

        let change_count = self.db.change_count();

        for x in statement.unwrap() {
            match x {
                Ok(_) => (),
                Err(n) => {
                    match n.code.unwrap_or(0) {
                        _ => return Err("Unknown error")
                    }
                },
            }
        }

        if change_count > 0 {
            Ok("Updated")
        } else {
            Err("Unabled to update")
        }
    }

    pub fn get_user(&self, id: UID) -> Option<User> {
        let query = "SELECT * FROM users WHERE id IS :id";
        let statement = self.db.prepare(query).unwrap().into_iter().bind::<&[(_, sqlite::Value)]>(&[(":id", id.into())]).unwrap();

        for row in statement.map(|row| row.unwrap()) {

            let username = row.read::<&str, _>("username").to_string();
            let id = row.read::<i64, _>("id").into();
            let desc = match row.read::<Option<&str>, _>("desc") {
                Some(n) => n.to_string(),
                None => "".to_string(),
            };
            let displayname = match row.read::<Option<&str>, _>("displayname") {
                Some(n) => n.to_string(),
                None => "".to_string(),
            };
            let statustext = match row.read::<Option<&str>, _>("statustext") {
                Some(n) => n.to_string(),
                None => "".to_string(),
            };
            let status = row.read::<i64, _>("status");
            let deleted = if row.read::<i64, _>("deleted") > 0 {
                true
            } else {
                false
            };

            return Some(User::construct(String::from(username), id, deleted, desc, displayname, statustext, status))
        }

        None
    }
    pub fn get_user_by_name(&self, name: String) -> Option<User> {
        let query = "SELECT * FROM users WHERE username IS :name";
        let statement = self.db.prepare(query).unwrap().into_iter()
        .bind::<&[(_, sqlite::Value)]>(&[(":name", name.into())]).unwrap();

        for row in statement.map(|row| row.unwrap()) {
            let username = row.read::<&str, _>("username");
            let id = row.read::<i64, _>("id").into();

            return Some(User::new(String::from(username), id))
        }

        None
    }

    pub fn create_user(&mut self, name: String) -> Result<UID, &'static str> {
        // Truncate whitespace on the ends of the username
        let name = name.trim().to_string();
        
        // No empty usernames
        if name.len() < 1 {
            return Err("Username cannot be empty")
        }

        let id: UID = self.get_user_count().into();

        let query = "INSERT INTO users (id, username, deleted, status, displayname) VALUES (:id, :name, false, 0, :name)";
        let statement = self.db.prepare(query).unwrap().into_iter()
        .bind::<&[(_, sqlite::Value)]>(&[(":id", id.into()),(":name", name.into())]);

        for x in statement.unwrap() {
            match x {
                Ok(_) => (),
                Err(n) => {
                    match n.code.unwrap_or(0) {
                        19 => return Err("Name taken"),
                        _ => return Err("Unknown error")
                    }
                },
            }
        }

        Ok(id)
    }

    pub fn delete_user(&mut self, id: UID) -> Result<&'static str, String> {
        let query = "UPDATE users SET username=:username, deleted=:deleted WHERE id IS :id AND deleted IS false";
        let statement = self.db.prepare(query).unwrap().into_iter()
            .bind::<&[(_, sqlite::Value)]>(&[
            (":username", "deleted".into()),
            (":deleted", 1.into()),
            (":id", id.into()),
            ]);

        let change_count = self.db.change_count();

        for x in statement.unwrap() {
            match x {
                Ok(_) => (),
                Err(n) => {
                    match n.code.unwrap_or(0) {
                        n => return Err(format!("Unknown Error: {}", n))
                    }
                },
            }
        }

        if change_count > 0 {
            Ok("Deleted")
        } else {
            Err("Unabled to delete".to_string())
        }
    }

    pub fn get_user_count(&self) -> u64 {
        self.get_table_length("users")
    }

    fn get_table_length(&self, table: &'static str) -> u64 {
        let query = format!("SELECT COUNT(*) FROM {}", table);
        let statement = self.db.prepare(query).unwrap().into_iter();

        for row in statement.map(|row| row.unwrap()) {
            let c = row.read::<i64, _>("COUNT(*)");

            let c = if c < 0 {
                0
            } else {
                c as u64
            };
                        
            return c;
        }

        0
    }

    pub fn new() -> Database {
        let db = match Connection::open("db.sqlite") {
            Ok(n) => n,
            Err(n) => panic!("{n}"),
        };

        // Setup the db
        match db.execute("PRAGMA foreign_keys = ON") {
            Ok(_) => (),
            Err(_) => panic!("Could not enable foreign_keys"),
        };

        let query = "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY NOT NULL, username TEXT UNIQUE NOT NULL CHECK (length(username) <= 20), deleted BOOL NOT NULL, displayname TEXT CHECK (length(displayname) <= 30), desc TEXT CHECK (length(desc) <= 500), statustext TEXT CHECK (length(statustext) <= 60), status INTEGER NOT NULL) WITHOUT ROWID";
        match db.execute(query) {
            Ok(_) => (),
            Err(n) => panic!("{n}"),
        }

        let query = "CREATE TABLE IF NOT EXISTS messages (id INTEGER PRIMARY KEY NOT NULL, sender INTEGER NOT NULL, date INTEGER NOT NULL, message TEXT CHECK (length(message) <= 6000), reply_to INTEGER, deleted BOOL NOT NULL, FOREIGN KEY (sender) REFERENCES users(id), FOREIGN KEY (reply_to) REFERENCES messages(id)) WITHOUT ROWID";
        match db.execute(query) {
            Ok(_) => (),
            Err(n) => panic!("{n}"),
        }

        Database {
            db: db,
            newest_message_id: None
        }
    }
}