use std::{
    fmt::{Display, Formatter},
    fs,
    path::PathBuf,
};

use anyhow::{anyhow, Result};
use chrono::DateTime;
use colored::Colorize;
use rusqlite::{params, Connection};

#[derive(Debug)]
pub struct Tx {
    date: String,
    net_amount: f64,
    dividend: f64,
    tax: f64,
    remainder: f64,
}

impl Tx {
    pub fn new(date: String, net_amount: f64, dividend: f64, tax: f64, remainder: f64) -> Tx {
        Tx {
            date,
            net_amount,
            dividend,
            tax,
            remainder,
        }
    }
}

impl Display for Tx {
    fn fmt(&self, f: &mut Formatter) -> std::fmt::Result {
        let date =
            DateTime::parse_from_rfc3339(&self.date).expect("string to date conversion error");

        write!(
            f,
            "| {0: <15} | {1:<15.2} | {2:<15.2} | {3:<15.2} | {4:<15.2}",
            date.format("%d-%b-%y").to_string().bright_green(),
            self.net_amount,
            self.dividend,
            self.tax,
            self.remainder
        )
    }
}

pub fn db_connect() -> Result<Connection> {
    let mut db_file: PathBuf = dirs::home_dir().ok_or(anyhow!("Couldn't get home directory"))?;
    db_file.push(".vztools");

    if !db_file.exists() {
        println!("Creating db file: {}", db_file.to_str().unwrap());
        fs::create_dir_all(&db_file)?;
    }

    db_file.push("db.db3");

    let db_file_path = db_file
        .to_str()
        .expect("Couldn't convert db path to string");

    let conn = Connection::open(db_file_path)?;

    conn.execute(
        "CREATE TABLE if not exists txs (
          id    INTEGER PRIMARY KEY AUTOINCREMENT,
          date TEXT NOT NULL,
          net_amount REAL NOT NULL,
          dividend REAL NOT NULL,
          tax REAL NOT NULL,
          remainder REAL NOT NULL
      )",
        (), // empty list of parameters.
    )?;

    return Ok(conn);
}

pub fn insert_tx(conn: &Connection, tx: &Tx) -> Result<()> {
    conn.execute(
        "INSERT INTO txs (date, net_amount, dividend, tax, remainder) VALUES (?1, ?2, ?3, ?4, ?5)",
        params![tx.date, tx.net_amount, tx.dividend, tx.tax, tx.remainder],
    )?;

    return Ok(());
}

pub fn find_latest_tx(conn: &Connection) -> Result<Tx> {
    let mut stmt = conn.prepare(
        "SELECT id, date, net_amount, dividend, tax, remainder FROM txs ORDER BY id DESC LIMIT 1",
    )?;
    let person_iter = stmt.query_map([], |row| {
        Ok(Tx {
            date: row.get(1)?,
            net_amount: row.get(2)?,
            dividend: row.get(3)?,
            tax: row.get(4)?,
            remainder: row.get(5)?,
        })
    })?;

    for person in person_iter {
        return Ok(person?);
    }

    return Err(anyhow!("No tx found"));
}

pub fn find_all_tx(conn: &Connection) -> Result<Vec<Tx>> {
    let mut stmt = conn.prepare(
        "SELECT id, date, net_amount, dividend, tax, remainder FROM txs ORDER BY id DESC LIMIT 20",
    )?;
    let person_iter = stmt.query_map([], |row| {
        Ok(Tx {
            date: row.get(1)?,
            net_amount: row.get(2)?,
            dividend: row.get(3)?,
            tax: row.get(4)?,
            remainder: row.get(5)?,
        })
    })?;

    let mut people = Vec::new();
    for person in person_iter {
        people.push(person?);
    }

    return Ok(people);
}