use std::error::Error;

use crate::entities::*;
use chrono::DateTime;
use sqlite::{Connection, State};

pub fn setup(con: &Connection) {
    let _ = con.execute(
        "
            CREATE TABLE IF NOT EXISTS Accounts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                type INTEGER NOT NULL
            );

            CREATE TABLE IF NOT EXISTS TransactionTypes (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                description TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS Transactions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                account_id INTEGER NOT NULL,
                type_id INTEGER NOT NULL,
                amount FLOAT NOT NULL,
                date INTEGER NOT NULL,
                description TEXT,
                FOREIGN KEY(account_id) REFERENCES Accounts(id),
                FOREIGN KEY(type_id) REFERENCES TransactionTypes(id)
            );
        ",
    );
    let mut asset_stmt = con
        .prepare("SELECT COUNT(*) FROM pragma_table_info('Accounts') WHERE name = 'asset_price'")
        .unwrap();
    if let Ok(State::Row) = asset_stmt.next() {
        let _ = con.execute(
            "
            ALTER TABLE Accounts ADD COLUMN asset_price FLOAT NOT NULL DEFAULT 0.0;
            ALTER TABLE Transactions ADD COLUMN asset_amount FLOAT NOT NULL DEFAULT 0.0;
            ",
        );
    }
}

pub fn upsert_account(
    con: &Connection,
    ac: Account,
) -> Result<Account, Box<dyn std::error::Error>> {
    let query;
    let ac_type = ac.get_ac_type() as i64;
    if ac.get_id() == 0 {
        query = "INSERT INTO Accounts (name, type, asset_price) VALUES (:name, :type, :asset_price) RETURNING id;";
    } else {
        query = "UPDATE Accounts SET name = :name, type = :type, asset_price = :asset_price WHERE id = :id RETURNING id;";
    }

    let mut statement = con
        .prepare(query)
        .map_err(|e| format!("Could not prepare query: {}", e))?;
    statement.bind((":name", &ac.get_name() as &str))?;
    statement.bind((":type", ac_type))?;
    statement.bind((":asset_price", ac.get_asset_price()))?;

    if ac.get_id() != 0 {
        statement.bind((":id", ac.get_id()))?;
    }

    let id;
    if let Ok(State::Row) = statement.next() {
        id = statement.read::<i64, _>("id")?;
    } else {
        id = 0;
    }

    let ac_type_enum =
        AccountType::try_from(ac_type).map_err(|_| "Could not parse account type")?;

    return Ok(Account::new(
        id,
        ac.get_name(),
        ac.get_asset_price(),
        ac_type_enum,
    ));
}

pub fn get_account_total(id: i64, con: &Connection) -> Result<f64, Box<dyn Error>> {
    let mut query = "
SELECT
    SUM(
        CASE
            WHEN a.type = 2 THEN t.asset_amount * a.asset_price
            ELSE t.amount
        END
    ) AS total
FROM Transactions t
JOIN Accounts a ON t.account_id = a.id"
        .to_owned();
    if id != 0 {
        query.push_str(" WHERE account_id = :id")
    }

    let mut statement = con.prepare(query)?;

    if id != 0 {
        statement.bind((":id", id))?;
    }

    if let Ok(State::Row) = statement.next() {
        return Ok(statement.read::<f64, _>("total")?);
    } else {
        return Ok(0.0);
    }
}

pub fn get_accounts(con: &Connection) -> Result<Vec<Account>, Box<dyn std::error::Error>> {
    let query = "SELECT * FROM Accounts";
    let mut statement = con
        .prepare(query)
        .map_err(|e| format!("Could not prepare query: {}", e))?;
    let mut vec = Vec::<Account>::new();

    vec.push(Account::new(0, "All".to_string(), 0.0, AccountType::Cash));

    while let Ok(State::Row) = statement.next() {
        let type_i = statement.read::<i64, _>("type")?;
        let ac_type_enum =
            AccountType::try_from(type_i).map_err(|_| "Could not parse account type")?;
        vec.push(Account::new(
            statement.read::<i64, _>("id")?,
            statement.read::<String, _>("name")?,
            statement.read::<f64, _>("asset_price")?,
            ac_type_enum,
        ));
    }

    return Ok(vec);
}

pub fn get_transaction_type(con: &Connection, id: i64) -> Result<TransactionType, Box<dyn Error>> {
    let query = "SELECT * FROM TransactionTypes WHERE id = ?";
    let mut statement = con.prepare(query)?;
    statement.bind((1, id))?;

    if let Ok(State::Row) = statement.next() {
        return Ok(TransactionType::new(
            statement.read::<i64, _>("id")?,
            statement.read::<String, _>("description")?,
        ));
    } else {
        return Ok(TransactionType::new(0, "".to_string()));
    }
}

pub fn get_transaction_types(con: &Connection) -> Result<Vec<TransactionType>, Box<dyn Error>> {
    let query = "SELECT * FROM TransactionTypes";
    let mut statement = con.prepare(query)?;
    let mut vec = Vec::<TransactionType>::new();

    while let Ok(State::Row) = statement.next() {
        vec.push(TransactionType::new(
            statement.read::<i64, _>("id")?,
            statement.read::<String, _>("description")?,
        ));
    }

    return Ok(vec);
}

pub fn upsert_transaction(
    con: &Connection,
    tr: Transaction,
) -> Result<Transaction, Box<dyn Error>> {
    let query;
    if tr.get_id() == 0 {
        query = "INSERT INTO Transactions
        (account_id, type_id, amount, date, description, asset_amount)
        VALUES (:ac_id, :type_id, :amnt, :date, :desc, :asset_amnt)
        RETURNING id;";
    } else {
        query = "UPDATE Transactions
        SET account_id = :ac_id, type_id = :type_id, amount = :amnt, date = :date, description = :desc, asset_amount = :asset_amnt
        WHERE id = :id RETURNING id;";
    }

    let mut statement = con.prepare(query)?;
    statement.bind((":ac_id", tr.get_account_id()))?;
    statement.bind((":type_id", tr.get_type().get_id()))?;
    statement.bind((":amnt", tr.get_amount()))?;
    statement.bind((":date", tr.get_date().timestamp()))?;
    statement.bind((":desc", &tr.get_desc() as &str))?;
    statement.bind((":asset_amnt", tr.get_asset_amnt()))?;

    if tr.get_id() != 0 {
        statement.bind((":id", tr.get_id()))?;
    }

    let id;
    if let Ok(State::Row) = statement.next() {
        id = statement.read::<i64, _>("id")?;
    } else {
        id = 0;
    }

    return Ok(Transaction::new(
        id,
        tr.get_account_id(),
        tr.get_amount(),
        tr.get_date_utc(),
        tr.get_desc(),
        tr.get_type().get_id(),
        tr.get_asset_amnt(),
        con,
    ));
}

pub fn get_account_transactions(
    con: &Connection,
    ac_id: i64,
) -> Result<Vec<Transaction>, Box<dyn Error>> {
    if ac_id == 0 {
        return get_transactions(con);
    }

    let query = "SELECT * FROM Transactions WHERE account_id = :id ORDER BY Date DESC";
    let mut statement = con.prepare(query)?;
    statement.bind((":id", ac_id))?;
    let mut vec = Vec::<Transaction>::new();

    while let Ok(State::Row) = statement.next() {
        vec.push(Transaction::new(
            statement.read::<i64, _>("id")?,
            statement.read::<i64, _>("account_id")?,
            statement.read::<f64, _>("amount")?,
            DateTime::from_timestamp(statement.read::<i64, _>("date")?, 0).unwrap_or_default(),
            statement.read::<String, _>("description")?,
            statement.read::<i64, _>("type_id")?,
            statement.read::<f64, _>("asset_amount")?,
            con,
        ));
    }

    return Ok(vec);
}

pub fn get_transactions(con: &Connection) -> Result<Vec<Transaction>, Box<dyn Error>> {
    let query = "SELECT * FROM Transactions ORDER BY Date DESC";
    let mut statement = con.prepare(query)?;
    let mut vec = Vec::<Transaction>::new();

    while let Ok(State::Row) = statement.next() {
        vec.push(Transaction::new(
            statement.read::<i64, _>("id")?,
            statement.read::<i64, _>("account_id")?,
            statement.read::<f64, _>("amount")?,
            DateTime::from_timestamp(statement.read::<i64, _>("date")?, 0).unwrap_or_default(),
            statement.read::<String, _>("description")?,
            statement.read::<i64, _>("type_id")?,
            statement.read::<f64, _>("asset_amount")?,
            con,
        ));
    }

    return Ok(vec);
}

pub fn get_tr_type_price_over_time(
    con: &Connection,
    ac_id: i64,
    tr_type_id: i64,
) -> Result<Vec<(f64, f64)>, Box<dyn Error>> {
    let mut query = "
SELECT strftime('%Y%m', datetime(date, 'unixepoch')) as z, SUM(amount) as y
FROM Transactions
WHERE type_id = :tr_type
    "
    .to_owned();
    if ac_id != 0 {
        query.push_str(" AND account_id = :ac_id");
    }
    query.push_str(" GROUP BY z;");

    let mut statement = con.prepare(query)?;
    statement.bind((":tr_type", tr_type_id))?;
    if ac_id != 0 {
        statement.bind((":ac_id", ac_id))?;
    }
    let mut data = Vec::<(f64, f64)>::new();
    let mut x = 0.0;

    while let Ok(State::Row) = statement.next() {
        let y = statement.read::<f64, _>("y")? * -1.0; // Pour passer de dépense dans le négatif à un graphique qui monte
        data.push((x, y));
        x += 1.0;
    }

    return Ok(data);
}

pub fn get_cumulative_account_sum(
    con: &Connection,
    ac_id: i64,
) -> Result<Vec<(f64, f64)>, Box<dyn Error>> {
    let mut query = "
SELECT 
    SUM(amount) OVER (
        ORDER BY date
    ) y
FROM
    Transactions"
        .to_owned();
    if ac_id != 0 {
        query.push_str(" WHERE account_id = :ac_id");
    }

    let mut statement = con.prepare(query)?;
    if ac_id != 0 {
        statement.bind((":ac_id", ac_id))?;
    }
    let mut data = Vec::<(f64, f64)>::new();
    let mut x = 0.0;

    while let Ok(State::Row) = statement.next() {
        let y = statement.read::<f64, _>("y")?;
        data.push((x, y));
        x += 1.0;
    }

    return Ok(data);
}