use anyhow::{Context, Result};
use diesel::{dsl::sum, QueryDsl, RunQueryDsl, SelectableHelper, SqliteConnection};
use schist_models::transaction_categorisation::TransactionCategorisation;
use schist_schema::schema::transaction_categorisations::{
    self as transaction_categorisations_schema,
    dsl::transaction_categorisations as transaction_categorisations_table,
};

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

pub fn get_all_transaction_categorisations(connection: &mut SqliteConnection) -> Result<Vec<TransactionCategorisation>> {
    let all_transaction_categorisations = transaction_categorisations_table
        .select(TransactionCategorisation::as_select())
        .load(connection)
        .with_context(|| "failed to get all transaction categorisations")?;
    Ok(all_transaction_categorisations)
}

pub fn insert_transaction_categorisations(
    transaction_categorisations: &[TransactionCategorisation],
    connection: &mut SqliteConnection,
) -> Result<usize> {
    let num_rows_inserted = diesel::insert_into(transaction_categorisations_table)
        .values(transaction_categorisations)
        .execute(connection)
        .with_context(|| insert_err_msg(&transaction_categorisations))?;
    Ok(num_rows_inserted)
}

fn insert_err_msg(transaction_categorisations: &[TransactionCategorisation]) -> String {
    format!(
        "failed to insert transaction categorisations: [{}]",
        transaction_categorisations
            .iter()
            .map(|tc| tc.id.to_string())
            .collect::<Vec<String>>()
            .join(", ")
    )
}

pub fn sum_transaction_categorisation_quantity_per_category_id(
    connection: &mut SqliteConnection,
) -> Result<Vec<(i32, i64)>> {
    let sum = transaction_categorisations_table
        .group_by(transaction_categorisations_schema::category_id)
        .select((
            transaction_categorisations_schema::category_id,
            sum(transaction_categorisations_schema::quantity),
        ))
        .load::<(i32, Option<i64>)>(connection)
        .map(|vec| vec.iter().map(|(category_id, sum)| (*category_id, sum.unwrap_or(0))).collect())
        .with_context(|| "failed to sum transaction categorisation quantity per category ID")?;
    Ok(sum)
}