use rusqlite::{Connection, Result, Rows, Statement};
use rusqlite::config::DbConfig::{*};
use chrono::Utc;
use std::path::PathBuf;

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)?;
        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 view_history(filepath: &PathBuf, filter: Option<String>) -> Result<()> {
        let conn = Connection::open(filepath)?;
        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 stmt: Statement;
        let mut regs: Rows;

        match filter {
            Some(name) => {
                hist_query.push_str("WHERE name = ?1");
                stmt = conn.prepare(hist_query.as_str())?;
                regs = stmt.query([name])?;
            },
            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(filepath: &PathBuf) -> Result<()> {
        let conn = Connection::open(filepath)?;
        let mut stmt = conn.prepare("
            SELECT a.name, sum(r.amount)
            FROM Registers r
            INNER 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)?;
            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))", ())
            .expect("SQL initialization error");
        println!("'{}' database created", &filepath.display());
        Ok(())
    }
}