use anyhow::{Context, Result};
use diesel::{dsl::max, ExpressionMethods, OptionalExtension, QueryDsl, RunQueryDsl, SelectableHelper, SqliteConnection};
use schist_models::{budget_update::BudgetUpdate, date_utc::DateUtc};
use schist_schema::schema::budget_updates::{
    self as budget_updates_schema, dsl::budget_updates as budget_updates_table,
};
use schist_traits::nowlike::Nowlike;

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

pub fn get_all_budget_updates(
    connection: &mut SqliteConnection,
) -> Result<Vec<BudgetUpdate>> {
    let all_budget_updates = budget_updates_table
        .select(BudgetUpdate::as_select())
        .load::<BudgetUpdate>(connection)
        .with_context(|| "failed to get all budget updates")?;
    Ok(all_budget_updates)
}

pub fn get_first_budget_update_with_date_and_category_id(
    date: &DateUtc,
    category_id: i32,
    connection: &mut SqliteConnection,
) -> Result<Option<BudgetUpdate>> {
    let first_budget_update = budget_updates_table
        .select(BudgetUpdate::as_select())
        .filter(budget_updates_schema::date.eq(date.to_string()))
        .filter(budget_updates_schema::category_id.eq(category_id))
        .first(connection)
        .optional()
        .with_context(|| format!("failed to get first budget update with date {}", date.to_string()))?;
    Ok(first_budget_update)
}

pub fn get_most_recent_budget_update_date_with_category_id(
    category_id: i32,
    connection: &mut SqliteConnection,
) -> Result<Option<DateUtc>> {
    let most_recent_date = budget_updates_table
        .select(max(budget_updates_schema::date))
        .filter(budget_updates_schema::category_id.eq(category_id))
        .filter(budget_updates_schema::date.le(DateUtc::now()))
        .first(connection)
        .with_context(|| format!("failed to get most recent budget update date with category ID {}", category_id))?;
    Ok(most_recent_date)
}

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

fn insert_err_msg(budget_updates: &[BudgetUpdate]) -> String {
    format!(
        "failed to insert budget updates: [{}]",
        budget_updates
            .iter()
            .map(|a| a.id.to_string())
            .collect::<Vec<String>>()
            .join(", ")
    )
}