1 files changed, 155 insertions, 0 deletions
diff --git a/rust/schist_queries/src/categories.rs b/rust/schist_queries/src/categories.rs
new file mode 100644
index 0000000..f54373c
--- /dev/null
+++ b/rust/schist_queries/src/categories.rs
@@ -0,0 +1,155 @@
+use anyhow::{Context, Result};
+use diesel::{QueryDsl, RunQueryDsl, SelectableHelper, SqliteConnection};
+use schist_models::{budget::Budget, category::Category, category_balance::CategoryBalance, category_budget::CategoryBudget, date_utc::DateUtc};
+use schist_schema::schema::categories::{self as categories_schema, dsl::categories as categories_table};
+
+use crate::{
+    budget_updates::{
+        get_all_budget_updates,
+        get_first_budget_update_with_date_and_category_id,
+        get_most_recent_budget_update_date_with_category_id,
+    },
+    category_transfers::{
+        sum_category_transfer_quantity_per_from_category_id,
+        sum_category_transfer_quantity_per_to_category_id,
+    },
+    transaction_categorisations::sum_transaction_categorisation_quantity_per_category_id,
+    utils::calculate_category_balance,
+};
+
+pub fn delete_all_categories(connection: &mut SqliteConnection) -> Result<usize> {
+    let num_rows_deleted = diesel::delete(categories_table)
+        .execute(connection)
+        .with_context(|| "failed to delete all categories")?;
+    Ok(num_rows_deleted)
+}
+
+pub fn get_all_categories(connection: &mut SqliteConnection) -> Result<Vec<Category>> {
+    let all_categories = categories_table
+        .select(Category::as_select())
+        .load(connection)
+        .with_context(|| "failed to get all categories")?;
+    Ok(all_categories)
+}
+
+pub fn get_all_category_balances(
+    connection: &mut SqliteConnection,
+) -> Result<Vec<CategoryBalance>> {
+    get_all_category_balances_without_context(connection)
+        .with_context(|| "failed to get all category balances")
+}
+
+fn get_all_category_balances_without_context(
+    connection: &mut SqliteConnection,
+) -> Result<Vec<CategoryBalance>> {
+    let categories = get_all_categories(connection)?;
+    let transaction_sums = sum_transaction_categorisation_quantity_per_category_id(connection)?;
+    let category_transfer_from_sums =
+        sum_category_transfer_quantity_per_from_category_id(connection)?;
+    let category_transfer_to_sums = sum_category_transfer_quantity_per_to_category_id(connection)?;
+    let budget_updates = get_all_budget_updates(connection)?;
+
+    let mut balances = Vec::<CategoryBalance>::new();
+    for category in categories {
+        let balance = calculate_category_balance(
+            &category,
+            &transaction_sums,
+            &category_transfer_from_sums,
+            &category_transfer_to_sums,
+            &budget_updates,
+        )?;
+
+        balances.push(CategoryBalance {
+            category_id: category.id,
+            balance: balance.try_into().unwrap(),
+        })
+    }
+
+    Ok(balances)
+}
+
+pub fn get_all_category_ids(connection: &mut SqliteConnection) -> Result<Vec<i32>> {
+    let all_category_ids = categories_table
+        .select(categories_schema::id)
+        .load(connection)
+        .with_context(|| "failed to get all category IDs")?;
+    Ok(all_category_ids)
+}
+
+pub fn get_most_recent_category_budget_per_category_id(
+    connection: &mut SqliteConnection,
+) -> Result<Vec<CategoryBudget>> {
+    get_most_recent_category_budget_per_category_id_without_context(connection)
+        .with_context(|| "failed to get most recent category budget per category ID")
+}
+
+fn get_most_recent_category_budget_per_category_id_without_context(
+    connection: &mut SqliteConnection,
+) -> Result<Vec<CategoryBudget>> {
+    let category_ids: Vec<i32> = get_all_category_ids(connection)?;
+
+    let mut budgets = Vec::<CategoryBudget>::new();
+    for category_id in category_ids {
+        let most_recent_budget_update_date =
+            get_most_recent_budget_update_date_with_category_id(category_id, connection)?;
+
+        budgets.push(match most_recent_budget_update_date {
+            None => CategoryBudget {
+                category_id,
+                budget: Option::None,
+            },
+            Some(most_recent_budget_update_date) =>
+                get_most_recent_category_budget_with_date_and_category_id(
+                    &most_recent_budget_update_date,
+                    category_id,
+                    connection,
+            )?,
+        })
+    }
+
+    Ok(budgets)
+}
+
+fn get_most_recent_category_budget_with_date_and_category_id(
+    most_recent_budget_update_date: &DateUtc,
+    category_id: i32,
+    connection: &mut SqliteConnection
+) -> Result<CategoryBudget> {
+    let most_recent_budget_update = get_first_budget_update_with_date_and_category_id(
+        &most_recent_budget_update_date,
+        category_id,
+        connection,
+    )?;
+
+    let budget = match most_recent_budget_update {
+        None => None,
+        Some(most_recent_budget_update) => Some(Budget::from(&most_recent_budget_update)),
+    };
+
+    Ok(CategoryBudget {
+        category_id,
+        budget,
+    })
+}
+
+pub fn insert_categories(
+    categories: &[Category],
+    connection: &mut SqliteConnection,
+) -> Result<usize> {
+    let num_categories_inserted = diesel::insert_into(categories_table)
+        .values(categories)
+        .execute(connection)
+        .with_context(|| insert_err_msg(categories))?;
+    Ok(num_categories_inserted)
+}
+
+fn insert_err_msg(categories: &[Category]) -> String {
+    format!(
+        "failed to insert categories: [{}]",
+        categories
+            .iter()
+            .map(|c| format!("\"{}\": \"{}\"", c.id, c.name))
+            .collect::<Vec<String>>()
+            .join(", ")
+    )
+}