pub mod r#types;

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


pub struct Database {
    db: Connection,
}
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 = row.read::<&str, _>("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 < 0 {
                false
            } else {
                true 
            };

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


            let msg = Message::construct(String::from(msg), sender, id, reply_to, date, deleted);
            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(&self) -> Option<UID> {
        match self.get_table_length("messages") {
            0 => None,
            n => Some(UID::new(n - 1)),
        }
    }

    pub fn send_message(&mut self, msg: &Message) -> Result<UID, &'static str> {
        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")
                    }
                },
            }
        }

        Ok(msg.id())
    }

    pub fn delete_message(&mut self, id: UID) -> Result<&'static str, &'static str> {
        let query = "UPDATE messages SET message=:message, deleted=:deleted WHERE id IS :id AND deleted IS false";
        let statement = self.db.prepare(query).unwrap().into_iter()
            .bind::<&[(_, sqlite::Value)]>(&[
            (":message", "".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) {
                        _ => return Err("Unknown error")
                    }
                },
            }
        }

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

    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");
            let id = row.read::<i64, _>("id").into();

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

        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> {
        let id: UID = self.get_user_count().into();

        let query = "INSERT INTO users (id, username) VALUES (:id, :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 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
        let query = "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY NOT NULL, username UNIQUE 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, reply_to INTEGER, deleted BOOL NOT NULL) WITHOUT ROWID";
        match db.execute(query) {
            Ok(_) => (),
            Err(n) => panic!("{n}"),
        }

        Database {
            db: db,
        }
    }
}