use rusqlite::Connection;
use rusqlite::Result;
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 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(())
    }
}