use anyhow::{Context, Result};
use diesel::{dsl::sum, QueryDsl, RunQueryDsl, SelectableHelper, SqliteConnection};
use schist_schema::schema::category_transfers::{
    self as category_transfers_schema,
    dsl::category_transfers as category_transfers_table,
};
use schist_models::category_transfer::CategoryTransfer;

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

pub fn get_all_category_transfers(connection: &mut SqliteConnection) -> Result<Vec<CategoryTransfer>> {
    let all_category_transfers = category_transfers_table
        .select(CategoryTransfer::as_select())
        .load(connection)
        .with_context(|| "failed to get all category transfers")?;
    Ok(all_category_transfers)
}

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

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

pub fn sum_category_transfer_quantity_per_from_category_id(
    connection: &mut SqliteConnection,
) -> Result<Vec<(i32, i64)>> {
    let sum = category_transfers_table
        .group_by(category_transfers_schema::from_category_id)
        .select((
            category_transfers_schema::from_category_id,
            sum(category_transfers_schema::quantity),
        ))
        .load::<(i32, Option<i64>)>(connection)
        .map(|result| result.iter().map(|sum| (sum.0, sum.1.unwrap_or(0))).collect())
        .with_context(|| "failed to sum category transfer quantity per from category ID")?;
    Ok(sum)
}

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