path: root/src/database.rs

use rusqlite::{Connection, Result, Rows, Statement};
use rusqlite::config::DbConfig::{*};
use chrono::Utc;
use std::path::PathBuf;
use text_io::scan;
use std::io::{stdout, Write};

pub struct Database {
    date: i64,
    person: String,
    amount: i32,
    note: Option<String>,
}

impl Database {
    pub fn new(person: String, amount: i32, note: Option<String>, is_debt: bool) -> Database {
        Database {
            date: Utc::now().timestamp(),
            amount: if !is_debt {amount} else {-amount},
            person,
            note,
        }
    }

    pub fn add_register(&self, filepath: &PathBuf) -> Result<()> {
        let conn = Connection::open(filepath)?;
        conn.set_db_config(SQLITE_DBCONFIG_ENABLE_FKEY, true)?;

        if !&self.check_agent_exists(&conn)? {
            println!("The Agent '{}' is not registered in the database", &self.person);
            return Ok(());
        }

        conn.execute("
            INSERT INTO Registers (agent_id, register_date, amount, note)
            VALUES (
                (SELECT id FROM Agents WHERE name=?1),
                ?2, ?3, ?4)",
                (&self.person,
                 &self.date,
                 self.amount,
                 &self.note))?;
        println!("Register added successfully");
        Ok(())
    }

    pub fn add_agent(&self, filepath: &PathBuf) -> Result<()> {
        let conn = Connection::open(filepath)?;
        conn.execute("
            INSERT INTO Agents (name)
            VALUES (?1)", (&self.person,))?;
        println!("agent '{}' successfully", &self.person);
        Ok(())
    }

    pub fn update_agent(&self, filepath: &PathBuf, new_name: String) -> Result <()> {
        let conn = Connection::open(filepath)?;

        if !&self.check_agent_exists(&conn)? {
            println!("The Agent '{}' is not registered in the database", &self.person);
            return Ok(());
        }

        conn.execute("UPDATE Agents SET name=?1 WHERE name=?2", (&new_name, &self.person))?;

        println!("Agent '{}' was updated to '{}'", &self.person, &new_name);
        Ok(())
    }

    pub fn delete_agent(&self, filepath: &PathBuf) -> Result<()> {
        let conn = Connection::open(filepath)?;
        let prompt = format!("The Agent '{}' will be removed are you sure?", &self.person);

        if !&self.check_agent_exists(&conn)? {
            println!("The Agent '{}' is not registered in the database", &self.person);
            return Ok(());
        }
        if !Database::ask_user_confirmaton(prompt.as_str()) {
            println!("Operation aborted");
            return Ok(());
        }
        conn.set_db_config(SQLITE_DBCONFIG_ENABLE_FKEY, true)?;
        conn.execute("DELETE FROM Agents WHERE name=?1", (&self.person,))?;
        println!("agent '{}' deleted successfully", &self.person);
        Ok(())
    }

    pub fn view_history(
        conn: Connection,
        agent_filter: Option<String>,
        note_filter: Option<Vec<String>>) -> Result<()>
    {
        let mut hist_query: String = "
            SELECT date(r.register_date, 'auto'), a.name, r.amount, r.note
            FROM Registers r
            INNER JOIN Agents a
            ON a.id = r.agent_id
        ".to_owned();

        let mut filter_note_string = String::new();
        let mut stmt: Statement;
        let mut regs: Rows;

        if let Some(notes) = note_filter.clone() {
            let mut i: u32 = 0;
            for note in notes.iter() {
                if i == 0 && agent_filter != None {
                    filter_note_string.push_str(" AND (note LIKE '");
                } else if i == 0 && agent_filter == None {
                    filter_note_string.push_str(" (note LIKE '");
                } else {
                    filter_note_string.push_str(" OR note LIKE '");
                }
                filter_note_string.push_str(note.as_str());
                filter_note_string.push_str("'");
                i+=1;
            }
            filter_note_string.push(')');
        }

        match (agent_filter, note_filter)  {
            (Some(name), Some(_)) => {
                hist_query.push_str("WHERE name = ?1");
                hist_query.push_str(filter_note_string.as_str());
                stmt = conn.prepare(hist_query.as_str())?;
                regs = stmt.query([name])?;
            },
            (Some(name), None) => {
                hist_query.push_str("WHERE name = ?1");
                stmt = conn.prepare(hist_query.as_str())?;
                regs = stmt.query([name])?;
            },
            (None, Some(_)) => {
                hist_query.push_str("WHERE");
                hist_query.push_str(filter_note_string.as_str());
                stmt = conn.prepare(hist_query.as_str())?;
                regs = stmt.query([])?;
            },
            (None, None) => {
                stmt = conn.prepare(hist_query.as_str())?;
                regs = stmt.query([])?;
            }
        };
        let mut total = 0;
        println!("+{:-<10}+{:-<20}+{:-<10}+{:-<20}+", "", "", "", "");
        println!("|{:^10}|{:^20}|{:^10}|{:^20}|", "date", "name", "amount", "note");
        println!("+{:-<10}+{:-<20}+{:-<10}+{:-<20}+", "", "", "", "");
        while let Some(row) = regs.next()? {
            let date: String = row.get(0)?;
            let name: String = row.get(1)?;
            let amount: i32 = row.get(2)?;
            let note: Option<String> = row.get(3)?;

            println!("|{}|{:>20}|{:>10}|{:>20}|", date, name, amount, note.unwrap_or("".to_string()));
            total += amount;
        }
        println!("+{:-<10}+{:-<20}+{:-<10}+{:-<20}+", "", "", "", "");
        println!("|{:^10}|{:>52}|", "total", total);
        println!("+{:-<10}+{:-<52}+", "", "");
        Ok(())
    }

    pub fn view_total(conn: Connection) -> Result<()> {
        let mut stmt = conn.prepare("
            SELECT a.name, sum(r.amount)
            FROM Registers r
            RIGHT JOIN Agents a
            ON a.id = r.agent_id
            GROUP BY a.name")?;
        let mut agents = stmt.query([])?;
        println!("+{:->20}+{:->11}+", "", "");
        println!("|{:^20}| {:^10}|", "name", "amount");
        println!("+{:->20}+{:->11}+", "", "");
        while let Some(row) = agents.next()? {
            let name: String = row.get(0)?;
            let amount: i32 = row.get(1).unwrap_or(0);
            println!("|{:>20}| {:>10}|", name, amount);
        }
        println!("+{:->20}+{:->11}+", "", "");
        Ok(())
    }

    pub fn init_database(filepath: &PathBuf) -> Result<()> {
        let conn = Connection::open(filepath).expect("Database file creation Error");
        conn.execute("
            CREATE TABLE Agents (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL UNIQUE)", ())
            .expect("SQL initialization error");

        conn.execute("
            CREATE TABLE Registers (
                id INTEGER PRIMARY KEY,
                agent_id INTEGER NOT NULL,
                register_date INTEGER NOT NULL,
                amount INTEGER NOT NULL,
                note TEXT,
                FOREIGN KEY(agent_id) REFERENCES Agents(id)
                    ON DELETE CASCADE)", ())
            .expect("SQL initialization error");
        println!("'{}' database created", &filepath.display());
        Ok(())
    }

    fn check_agent_exists(&self, conn: &Connection) -> Result<bool> {
        let mut stmt = conn.prepare("SELECT name FROM Agents WHERE name=?1")?;
        let mut reg = stmt.query([&self.person])?;

        if let Some(_) = reg.next()? {
            Ok(true)
        } else {
            Ok(false)
        }
    }

    fn ask_user_confirmaton(prompt: &str) -> bool {
        let mut input: String;
        let complete_prompt = format!("{} (Y/n)", prompt);
        loop {
            print!("{}: ", complete_prompt);
            stdout().flush().unwrap();
            scan!("{}\n", input);

            match input.to_lowercase().as_str() {
                "y"|"" => return true,
                "n" => return false,
                _ => println!("Invalid input")
            }
        }
    }
}