use anyhow::{Context, Result};
use diesel::{QueryDsl, RunQueryDsl, SelectableHelper, SqliteConnection};
use schist_models::{budget::Budget, category::Category, category_balance::CategoryBalance, category_budget::CategoryBudget, date_utc::DateUtc};
use schist_schema::schema::categories::{self as categories_schema, dsl::categories as categories_table};

use crate::{
    budget_updates::{
        get_all_budget_updates,
        get_first_budget_update_with_date_and_category_id,
        get_most_recent_budget_update_date_with_category_id,
    },
    category_transfers::{
        sum_category_transfer_quantity_per_from_category_id,
        sum_category_transfer_quantity_per_to_category_id,
    },
    transaction_categorisations::sum_transaction_categorisation_quantity_per_category_id,
    utils::calculate_category_balance,
};

pub fn delete_all_categories(connection: &mut SqliteConnection) -> Result<usize> {
    let num_rows_deleted = diesel::delete(categories_table)
        .execute(connection)
        .with_context(|| "failed to delete all categories")?;
    Ok(num_rows_deleted)
}

pub fn get_all_categories(connection: &mut SqliteConnection) -> Result<Vec<Category>> {
    let all_categories = categories_table
        .select(Category::as_select())
        .load(connection)
        .with_context(|| "failed to get all categories")?;
    Ok(all_categories)
}

pub fn get_all_category_balances(
    connection: &mut SqliteConnection,
) -> Result<Vec<CategoryBalance>> {
    get_all_category_balances_without_context(connection)
        .with_context(|| "failed to get all category balances")
}

fn get_all_category_balances_without_context(
    connection: &mut SqliteConnection,
) -> Result<Vec<CategoryBalance>> {
    let categories = get_all_categories(connection)?;
    let transaction_sums = sum_transaction_categorisation_quantity_per_category_id(connection)?;
    let category_transfer_from_sums =
        sum_category_transfer_quantity_per_from_category_id(connection)?;
    let category_transfer_to_sums = sum_category_transfer_quantity_per_to_category_id(connection)?;
    let budget_updates = get_all_budget_updates(connection)?;

    let mut balances = Vec::<CategoryBalance>::new();
    for category in categories {
        let balance = calculate_category_balance(
            &category,
            &transaction_sums,
            &category_transfer_from_sums,
            &category_transfer_to_sums,
            &budget_updates,
        )?;

        balances.push(CategoryBalance {
            category_id: category.id,
            balance: balance.try_into().unwrap(),
        })
    }

    Ok(balances)
}

pub fn get_all_category_ids(connection: &mut SqliteConnection) -> Result<Vec<i32>> {
    let all_category_ids = categories_table
        .select(categories_schema::id)
        .load(connection)
        .with_context(|| "failed to get all category IDs")?;
    Ok(all_category_ids)
}

pub fn get_most_recent_category_budget_per_category_id(
    connection: &mut SqliteConnection,
) -> Result<Vec<CategoryBudget>> {
    get_most_recent_category_budget_per_category_id_without_context(connection)
        .with_context(|| "failed to get most recent category budget per category ID")
}

fn get_most_recent_category_budget_per_category_id_without_context(
    connection: &mut SqliteConnection,
) -> Result<Vec<CategoryBudget>> {
    let category_ids: Vec<i32> = get_all_category_ids(connection)?;

    let mut budgets = Vec::<CategoryBudget>::new();
    for category_id in category_ids {
        let most_recent_budget_update_date =
            get_most_recent_budget_update_date_with_category_id(category_id, connection)?;

        budgets.push(match most_recent_budget_update_date {
            None => CategoryBudget {
                category_id,
                budget: Option::None,
            },
            Some(most_recent_budget_update_date) =>
                get_most_recent_category_budget_with_date_and_category_id(
                    &most_recent_budget_update_date,
                    category_id,
                    connection,
            )?,
        })
    }

    Ok(budgets)
}

fn get_most_recent_category_budget_with_date_and_category_id(
    most_recent_budget_update_date: &DateUtc,
    category_id: i32,
    connection: &mut SqliteConnection
) -> Result<CategoryBudget> {
    let most_recent_budget_update = get_first_budget_update_with_date_and_category_id(
        &most_recent_budget_update_date,
        category_id,
        connection,
    )?;

    let budget = match most_recent_budget_update {
        None => None,
        Some(most_recent_budget_update) => Some(Budget::from(&most_recent_budget_update)),
    };

    Ok(CategoryBudget {
        category_id,
        budget,
    })
}

pub fn insert_categories(
    categories: &[Category],
    connection: &mut SqliteConnection,
) -> Result<usize> {
    let num_categories_inserted = diesel::insert_into(categories_table)
        .values(categories)
        .execute(connection)
        .with_context(|| insert_err_msg(categories))?;
    Ok(num_categories_inserted)
}

fn insert_err_msg(categories: &[Category]) -> String {
    format!(
        "failed to insert categories: [{}]",
        categories
            .iter()
            .map(|c| format!("\"{}\": \"{}\"", c.id, c.name))
            .collect::<Vec<String>>()
            .join(", ")
    )
}