use anyhow::{Context, Result};
use diesel::{dsl::sum, QueryDsl, RunQueryDsl, SelectableHelper, SqliteConnection};
use schist_models::bucket_transfer::BucketTransfer;
use schist_schema::schema::bucket_transfers::{
    self as bucket_transfers_schema, dsl::bucket_transfers as bucket_transfers_table,
};

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

pub fn get_all_bucket_transfers(connection: &mut SqliteConnection) -> Result<Vec<BucketTransfer>> {
    let all_bucket_transfers = bucket_transfers_table
        .select(BucketTransfer::as_select())
        .load(connection)
        .with_context(|| "failed to get all bucket transfers")?;
    Ok(all_bucket_transfers)
}

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

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

pub fn sum_bucket_transfer_quantity_per_from_bucket_id(
    connection: &mut SqliteConnection,
) -> Result<Vec<(i32, i64)>> {
    let sum = bucket_transfers_table
        .group_by(bucket_transfers_schema::from_bucket_id)
        .select((
            bucket_transfers_schema::from_bucket_id,
            sum(bucket_transfers_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 transfer quantity per from bucket ID")?;
    Ok(sum)
}

pub fn sum_bucket_transfer_quantity_per_to_bucket_id(
    connection: &mut SqliteConnection,
) -> Result<Vec<(i32, i64)>> {
    let sum = bucket_transfers_table
        .group_by(bucket_transfers_schema::to_bucket_id)
        .select((
            bucket_transfers_schema::to_bucket_id,
            sum(bucket_transfers_schema::amount),
        ))
        .load::<(i32, Option<i64>)>(connection)
        .map(|vec| {
            vec.iter()
                .map(|(id, sum)| (*id, sum.unwrap_or(0)))
                .collect()
        })
        .with_context(|| "failed to sum bucket transfer quantity per to bucket ID")?;
    Ok(sum)
}