use anyhow::{Context, Result};
use diesel::{dsl::sum, QueryDsl, RunQueryDsl, SelectableHelper, SqliteConnection};
use schist_models::BucketTransaction;
use schist_schema::schema::bucket_transactions::{
    self as bucket_transactions_schema, dsl::bucket_transactions as bucket_transactions_table,
};

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

pub fn get_all_bucket_transactions(
    connection: &mut SqliteConnection,
) -> Result<Vec<BucketTransaction>> {
    let all_bucket_transactions = bucket_transactions_table
        .select(BucketTransaction::as_select())
        .load(connection)
        .with_context(|| "failed to get all bucket transactions")?;
    Ok(all_bucket_transactions)
}

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

fn insert_err_msg(bucket_transactions: &[BucketTransaction]) -> String {
    format!(
        "failed to insert bucket transactions: [{}]",
        bucket_transactions
            .iter()
            .map(|ct| ct.id.to_string())
            .collect::<Vec<String>>()
            .join(", ")
    )
}

pub fn sum_bucket_transaction_amount_per_bucket_id(
    connection: &mut SqliteConnection,
) -> Result<Vec<(i32, i64)>> {
    let sum = bucket_transactions_table
        .group_by(bucket_transactions_schema::bucket_id)
        .select((
            bucket_transactions_schema::bucket_id,
            sum(bucket_transactions_schema::amount),
        ))
        .load::<(i32, Option<i64>)>(connection)
        .map(|result| {
            result
                .iter()
                .map(|sum| (sum.0, sum.1.unwrap_or(0)))
                .collect()
        })
        .with_context(|| "failed to sum bucket transaction amount per bucket ID")?;
    Ok(sum)
}