From 8323fdd73272a2882781aba3c499ba0be3dff2a6 Mon Sep 17 00:00:00 2001 From: UMTS at Teleco Date: Sat, 13 Dec 2025 02:51:15 +0100 Subject: committing to insanity --- src/core/tables.rs | 1570 ++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 1570 insertions(+) create mode 100644 src/core/tables.rs (limited to 'src/core/tables.rs') diff --git a/src/core/tables.rs b/src/core/tables.rs new file mode 100644 index 0000000..248dda0 --- /dev/null +++ b/src/core/tables.rs @@ -0,0 +1,1570 @@ +use crate::api::ApiClient; +use crate::models::{Join, OrderBy}; +use anyhow::Result; +use base64::engine::general_purpose::STANDARD as BASE64_STANDARD; +use base64::Engine as _; +use serde_json::{json, Value}; + +fn decode_base64_json(value: Option<&serde_json::Value>) -> Option { + let s = value.and_then(|v| v.as_str())?; + if s.is_empty() || s == "NULL" { + return None; + } + match BASE64_STANDARD.decode(s) { + Ok(bytes) => serde_json::from_slice::(&bytes).ok(), + Err(_) => None, + } +} + +fn compact_json(value: &serde_json::Value) -> String { + match value { + serde_json::Value::Null => String::new(), + serde_json::Value::String(s) => s.clone(), + _ => serde_json::to_string(value).unwrap_or_else(|_| value.to_string()), + } +} + +fn format_asset_change_short(action: &str, changed_fields: Option<&serde_json::Value>) -> String { + match action { + "INSERT" => "Created".to_string(), + "DELETE" => "Deleted".to_string(), + "UPDATE" => { + if let Some(serde_json::Value::Array(fields)) = changed_fields { + if fields.len() == 1 { + let field = fields[0].as_str().unwrap_or(""); + match field { + "status" => "Status changed".to_string(), + "zone_id" => "Moved".to_string(), + "name" => "Renamed".to_string(), + _ => field + .replace('_', " ") + .chars() + .next() + .map(|c| c.to_uppercase().collect::() + &field[1..]) + .unwrap_or_else(|| "Updated".to_string()), + } + } else if fields.len() <= 3 { + format!("{} fields", fields.len()) + } else { + format!("{} changes", fields.len()) + } + } else { + "Updated".to_string() + } + } + _ => action.to_string(), + } +} + +/// Get recent asset changes from the change log +pub fn get_asset_changes(api_client: &ApiClient, limit: u32) -> Result> { + log::debug!( + "Loading {} recent asset changes (with JOINs and formatting)...", + limit + ); + + // Attempt a JOIN query for richer context (asset tag, user name) + let joins = vec![ + Join { + table: "assets".into(), + on: "asset_change_log.record_id = assets.id".into(), + join_type: "LEFT".into(), + }, + Join { + table: "users".into(), + on: "asset_change_log.changed_by_id = users.id".into(), + join_type: "LEFT".into(), + }, + ]; + let columns = vec![ + "asset_change_log.id".into(), + "asset_change_log.table_name".into(), + "asset_change_log.action".into(), + "asset_change_log.record_id".into(), + "asset_change_log.changed_fields".into(), + "asset_change_log.old_values".into(), + "asset_change_log.new_values".into(), + "asset_change_log.changed_at".into(), + "asset_change_log.changed_by_username".into(), + "assets.asset_tag".into(), + "users.name as user_full_name".into(), + ]; + + let resp = api_client.select_with_joins( + "asset_change_log", + Some(columns), + None, // where_clause + None, // filter + Some(vec![OrderBy { + column: "asset_change_log.changed_at".into(), + direction: "DESC".into(), + }]), // order_by + Some(limit), // limit + Some(joins), // joins + )?; + + let mut rows = if resp.success { + resp.data.unwrap_or_default() + } else { + Vec::new() + }; + + // Fallback: simple query if JOIN returns nothing + if rows.is_empty() { + log::debug!("JOIN query returned 0 rows, falling back to simple query"); + let fallback = api_client.select( + "asset_change_log", + Some(vec!["*".into()]), + None, + Some(vec![OrderBy { + column: "changed_at".into(), + direction: "DESC".into(), + }]), + Some(5), + )?; + rows = if fallback.success { + fallback.data.unwrap_or_default() + } else { + Vec::new() + }; + } + + // Transform rows into display-friendly objects + let mut out = Vec::new(); + for (i, row) in rows.into_iter().enumerate() { + if i == 0 { + log::debug!( + "First asset_change_log row keys: {:?}", + row.as_object() + .map(|o| o.keys().cloned().collect::>()) + ); + } + + let action = row.get("action").and_then(|v| v.as_str()).unwrap_or(""); + let decoded_fields = decode_base64_json(row.get("changed_fields")); + let summary = format_asset_change_short(action, decoded_fields.as_ref()); + + let asset_tag = row + .get("asset_tag") + .and_then(|v| v.as_str()) + .map(|s| s.to_string()) + .unwrap_or_else(|| { + format!( + "ID:{}", + row.get("record_id").and_then(|v| v.as_i64()).unwrap_or(0) + ) + }); + + let display = serde_json::json!({ + "asset_tag": asset_tag, + "action": action, + "changes": summary, + "date": row.get("changed_at").and_then(|v| v.as_str()).unwrap_or(""), + "user": row.get("user_full_name").and_then(|v| v.as_str()).or_else(|| row.get("changed_by_username").and_then(|v| v.as_str())).unwrap_or("System"), + }); + out.push(display); + } + + Ok(out) +} + +/// Get recent issue tracker changes from the change log +pub fn get_issue_changes(api_client: &ApiClient, limit: u32) -> Result> { + log::debug!( + "Loading {} recent issue changes (with JOINs and formatting)...", + limit + ); + + let joins = vec![ + Join { + table: "issue_tracker".into(), + on: "issue_tracker_change_log.issue_id = issue_tracker.id".into(), + join_type: "LEFT".into(), + }, + Join { + table: "users".into(), + on: "issue_tracker_change_log.changed_by = users.id".into(), + join_type: "LEFT".into(), + }, + ]; + let columns = vec![ + "issue_tracker_change_log.id".into(), + "issue_tracker_change_log.issue_id".into(), + "issue_tracker_change_log.change_type".into(), + "issue_tracker_change_log.changed_fields".into(), + "issue_tracker_change_log.old_values".into(), + "issue_tracker_change_log.new_values".into(), + "issue_tracker_change_log.change_date".into(), + "issue_tracker.title".into(), + "issue_tracker.severity".into(), + "users.name as changed_by_name".into(), + ]; + + let resp = api_client.select_with_joins( + "issue_tracker_change_log", + Some(columns), + None, // where_clause + None, // filter + Some(vec![OrderBy { + column: "issue_tracker_change_log.change_date".into(), + direction: "DESC".into(), + }]), // order_by + Some(limit), // limit + Some(joins), // joins + )?; + + let rows = if resp.success { + resp.data.unwrap_or_default() + } else { + Vec::new() + }; + + let mut out = Vec::new(); + for row in rows { + // Try to parse changed_fields which may be JSON string array + let changed_fields = match row.get("changed_fields") { + Some(serde_json::Value::String(s)) => serde_json::from_str::(s).ok(), + Some(v @ serde_json::Value::Array(_)) => Some(v.clone()), + _ => None, + }; + + // Create a short summary similar to Python + let change_type = row + .get("change_type") + .and_then(|v| v.as_str()) + .unwrap_or("UPDATE"); + let summary = if change_type == "INSERT" { + "Created".to_string() + } else if change_type == "DELETE" { + "Deleted".to_string() + } else { + if let Some(serde_json::Value::Array(fields)) = changed_fields { + if fields.contains(&serde_json::Value::String("status".into())) { + if let Some(new_values) = row + .get("new_values") + .and_then(|v| v.as_str()) + .and_then(|s| serde_json::from_str::(s).ok()) + { + if let Some(status) = new_values.get("status").and_then(|v| v.as_str()) { + format!("Status → {}", status) + } else { + "Updated".to_string() + } + } else { + "Updated".to_string() + } + } else if fields.contains(&serde_json::Value::String("assigned_to".into())) { + "Reassigned".to_string() + } else if fields.contains(&serde_json::Value::String("severity".into())) { + "Priority changed".to_string() + } else if fields.contains(&serde_json::Value::String("title".into())) { + "Title updated".to_string() + } else if fields.contains(&serde_json::Value::String("description".into())) { + "Description updated".to_string() + } else if fields.len() == 1 { + let field = fields[0].as_str().unwrap_or("").replace('_', " "); + format!("{} updated", capitalize(&field)) + } else if fields.len() <= 3 { + format!("{} fields", fields.len()) + } else { + format!("{} changes", fields.len()) + } + } else { + "Updated".to_string() + } + }; + + let issue_title = row + .get("title") + .and_then(|v| v.as_str()) + .map(|s| s.to_string()) + .unwrap_or_else(|| { + format!( + "Issue #{}", + row.get("issue_id").and_then(|v| v.as_i64()).unwrap_or(0) + ) + }); + + let display = serde_json::json!({ + "issue": issue_title, + "changes": summary, + "date": row.get("change_date").and_then(|v| v.as_str()).unwrap_or(""), + "user": row.get("changed_by_name").and_then(|v| v.as_str()).unwrap_or("System"), + }); + out.push(display); + } + + Ok(out) +} + +fn capitalize(s: &str) -> String { + let mut c = s.chars(); + match c.next() { + Some(f) => f.to_uppercase().collect::() + c.as_str(), + None => String::new(), + } +} + +/// Get issues with useful labels +pub fn get_issues(api_client: &ApiClient, limit: Option) -> Result> { + let columns = Some(vec![ + "issue_tracker.id".into(), + "issue_tracker.issue_type".into(), + "issue_tracker.asset_id".into(), + "issue_tracker.borrower_id".into(), + "issue_tracker.title".into(), + "issue_tracker.description".into(), + "issue_tracker.severity".into(), + "issue_tracker.priority".into(), + "issue_tracker.status".into(), + "issue_tracker.solution".into(), + "issue_tracker.solution_plus".into(), + "issue_tracker.auto_detected".into(), + "issue_tracker.detection_trigger".into(), + "issue_tracker.replacement_asset_id".into(), + "issue_tracker.cost".into(), + "issue_tracker.notes".into(), + // Dashboard schema uses created_date / updated_date / resolved_date + "issue_tracker.created_date AS created_at".into(), + "issue_tracker.updated_date AS updated_at".into(), + "issue_tracker.resolved_date".into(), + // joins/labels + "assets.asset_tag".into(), + "assets.name as asset_name".into(), + "borrowers.name as borrower_name".into(), + // Assignee name + "users.name as assigned_to_name".into(), + ]); + let joins = Some(vec![ + Join { + table: "assets".into(), + on: "issue_tracker.asset_id = assets.id".into(), + join_type: "LEFT".into(), + }, + Join { + table: "borrowers".into(), + on: "issue_tracker.borrower_id = borrowers.id".into(), + join_type: "LEFT".into(), + }, + Join { + table: "users".into(), + on: "issue_tracker.assigned_to = users.id".into(), + join_type: "LEFT".into(), + }, + ]); + // Sort by updated_date (aliased as updated_at) + let order = Some(vec![OrderBy { + column: "issue_tracker.updated_date".into(), + direction: "DESC".into(), + }]); + let resp = + api_client.select_with_joins("issue_tracker", columns, None, None, order, limit, joins)?; + if resp.success { + Ok(resp.data.unwrap_or_default()) + } else { + anyhow::bail!("Failed to load issues: {:?}", resp.error) + } +} + +/// Get all assets from inventory with proper JOINs for categories, zones, and suppliers +#[allow(dead_code)] +pub fn get_all_assets( + api_client: &ApiClient, + limit: Option, + where_clause: Option, + filter: Option, +) -> Result> { + let columns = Some(vec![ + "assets.id".to_string(), + "assets.asset_tag".to_string(), + "assets.asset_numeric_id".to_string(), + "assets.asset_type".to_string(), + "assets.name".to_string(), + "assets.category_id".to_string(), + "assets.manufacturer".to_string(), + "assets.model".to_string(), + "assets.serial_number".to_string(), + "assets.status".to_string(), + "assets.zone_id".to_string(), + "assets.zone_plus".to_string(), + "assets.zone_note".to_string(), + "assets.supplier_id".to_string(), + "assets.price".to_string(), + "assets.purchase_date".to_string(), + "assets.warranty_until".to_string(), + "assets.expiry_date".to_string(), + "assets.quantity_available".to_string(), + "assets.quantity_total".to_string(), + "assets.quantity_used".to_string(), + "assets.lendable".to_string(), + "assets.minimum_role_for_lending".to_string(), + "assets.lending_status".to_string(), + "assets.current_borrower_id".to_string(), + // Due date stored on asset (flows keep it in sync) + "assets.due_date".to_string(), + "assets.previous_borrower_id".to_string(), + "assets.last_audit".to_string(), + "assets.last_audit_status".to_string(), + "assets.no_scan".to_string(), + "assets.notes".to_string(), + "assets.created_date".to_string(), + "assets.created_by".to_string(), + "assets.last_modified_date".to_string(), + "assets.last_modified_by".to_string(), + "assets.label_template_id".to_string(), + // JOINed fields + "categories.category_name".to_string(), + "label_templates.template_name AS label_template_name".to_string(), + "zones.zone_name".to_string(), + "zones.zone_code".to_string(), + "suppliers.name AS supplier_name".to_string(), + // Borrower joined from asset field + "current_borrower.name AS current_borrower_name".to_string(), + "previous_borrower.name AS previous_borrower_name".to_string(), + "created_by_user.username AS created_by_username".to_string(), + "modified_by_user.username AS last_modified_by_username".to_string(), + ]); + + let joins = Some(vec![ + Join { + table: "categories".to_string(), + on: "assets.category_id = categories.id".to_string(), + join_type: "LEFT".to_string(), + }, + Join { + table: "zones".to_string(), + on: "assets.zone_id = zones.id".to_string(), + join_type: "LEFT".to_string(), + }, + Join { + table: "suppliers".to_string(), + on: "assets.supplier_id = suppliers.id".to_string(), + join_type: "LEFT".to_string(), + }, + Join { + table: "borrowers AS current_borrower".to_string(), + on: "assets.current_borrower_id = current_borrower.id".to_string(), + join_type: "LEFT".to_string(), + }, + Join { + table: "borrowers AS previous_borrower".to_string(), + on: "assets.previous_borrower_id = previous_borrower.id".to_string(), + join_type: "LEFT".to_string(), + }, + Join { + table: "users AS created_by_user".to_string(), + on: "assets.created_by = created_by_user.id".to_string(), + join_type: "LEFT".to_string(), + }, + Join { + table: "users AS modified_by_user".to_string(), + on: "assets.last_modified_by = modified_by_user.id".to_string(), + join_type: "LEFT".to_string(), + }, + Join { + table: "label_templates".to_string(), + on: "assets.label_template_id = label_templates.id".to_string(), + join_type: "LEFT".to_string(), + }, + ]); + + let response = api_client.select_with_joins( + "assets", + columns, + where_clause, + filter, + None, + limit, + joins, + )?; + + if response.success { + if let Some(data) = response.data { + log::info!("Loaded {} assets successfully (with JOINs)", data.len()); + Ok(data) + } else { + Ok(vec![]) + } + } else { + log::error!("Failed to load assets: {:?}", response.error); + anyhow::bail!("Failed to load assets: {:?}", response.error) + } +} + +/// Get all zones (flat list) with parent relationships +pub fn get_all_zones_with_filter( + api_client: &ApiClient, + filter: Option, +) -> Result> { + use crate::models::QueryRequest; + + let columns = Some(vec![ + "zones.id".to_string(), + "zones.zone_code".to_string(), + "zones.mini_code".to_string(), + "zones.zone_name as name".to_string(), + "zones.zone_type".to_string(), + "zones.parent_id".to_string(), + "zones.zone_notes".to_string(), + "zones.include_in_parent".to_string(), + "zones.audit_timeout_minutes".to_string(), + ]); + + let request = QueryRequest { + action: "select".to_string(), + table: "zones".to_string(), + columns, + data: None, + r#where: None, + filter, + order_by: Some(vec![OrderBy { + column: "zones.zone_code".into(), + direction: "ASC".into(), + }]), + limit: None, + offset: None, + joins: None, + }; + + let response = api_client.query(&request)?; + + if response.success { + if let Some(data) = response.data { + if let Some(array) = data.as_array() { + Ok(array.clone()) + } else { + Ok(vec![]) + } + } else { + Ok(vec![]) + } + } else { + log::error!("Failed to load zones: {:?}", response.error); + anyhow::bail!("Failed to load zones: {:?}", response.error) + } +} + +/// Get assets in a specific zone (minimal fields) +pub fn get_assets_in_zone( + api_client: &ApiClient, + zone_id: i32, + limit: Option, +) -> Result> { + let columns = Some(vec![ + "assets.id".to_string(), + "assets.asset_numeric_id".to_string(), + "assets.asset_tag".to_string(), + "assets.name".to_string(), + "assets.status".to_string(), + "assets.lending_status".to_string(), + "assets.no_scan".to_string(), + "assets.audit_task_id".to_string(), + "assets.zone_id".to_string(), + ]); + let where_clause = Some(serde_json::json!({ "assets.zone_id": zone_id })); + let order = Some(vec![OrderBy { + column: "assets.name".into(), + direction: "ASC".into(), + }]); + let resp = api_client.select("assets", columns, where_clause, order, limit)?; + if resp.success { + Ok(resp.data.unwrap_or_default()) + } else { + anyhow::bail!("Failed to load assets for zone {}", zone_id) + } +} + +/// Find a zone by its zone_code (case-insensitive match on the exact code) +pub fn find_zone_by_code(api_client: &ApiClient, zone_code: &str) -> Result> { + if zone_code.trim().is_empty() { + return Ok(None); + } + + let columns = Some(vec![ + "id".into(), + "zone_code".into(), + "zone_name".into(), + "zone_type".into(), + "audit_timeout_minutes".into(), + "parent_id".into(), + ]); + let where_clause = Some(json!({ "zone_code": zone_code })); + + let response = api_client.select("zones", columns, where_clause, None, Some(1))?; + if response.success { + if let Some(data) = response.data { + if let Some(mut task) = data.into_iter().next() { + if let Some(map) = task.as_object_mut() { + if let Some(decoded) = decode_base64_json(map.get("json_sequence")) { + map.insert("json_sequence".into(), decoded); + } else if let Some(raw) = map.get("json_sequence").cloned() { + if let Value::String(s) = raw { + if let Ok(parsed) = serde_json::from_str::(&s) { + map.insert("json_sequence".into(), parsed); + } + } + } + } + Ok(Some(task)) + } else { + Ok(None) + } + } else { + Ok(None) + } + } else { + anyhow::bail!("Failed to lookup zone: {:?}", response.error) + } +} + +/// Find an asset by tag or numeric identifier (exact match) +pub fn find_asset_by_tag_or_numeric( + api_client: &ApiClient, + identifier: &str, +) -> Result> { + let trimmed = identifier.trim(); + if trimmed.is_empty() { + return Ok(None); + } + + let mut or_filters = vec![json!({ + "column": "assets.asset_tag", + "op": "=", + "value": trimmed + })]; + + if let Ok(numeric) = trimmed.parse::() { + or_filters.push(json!({ + "column": "assets.asset_numeric_id", + "op": "=", + "value": numeric + })); + } else { + // Allow matching numeric id stored as string just in case + or_filters.push(json!({ + "column": "assets.asset_numeric_id", + "op": "=", + "value": trimmed + })); + } + + let filter = json!({ "or": or_filters }); + let columns = Some(vec![ + "assets.id".to_string(), + "assets.asset_numeric_id".to_string(), + "assets.asset_tag".to_string(), + "assets.name".to_string(), + "assets.zone_id".to_string(), + "assets.status".to_string(), + "assets.no_scan".to_string(), + "assets.audit_task_id".to_string(), + ]); + + let response = + api_client.select_with_joins("assets", columns, None, Some(filter), None, Some(1), None)?; + + if response.success { + if let Some(data) = response.data { + Ok(data.into_iter().next()) + } else { + Ok(None) + } + } else { + anyhow::bail!("Failed to lookup asset: {:?}", response.error) + } +} + +/// Fetch a single audit task definition by ID +pub fn get_audit_task_definition(api_client: &ApiClient, task_id: i64) -> Result> { + let columns = Some(vec![ + "id".into(), + "task_name".into(), + "json_sequence".into(), + "created_at".into(), + "updated_at".into(), + ]); + + let where_clause = Some(json!({ "id": task_id })); + let response = api_client.select("audit_tasks", columns, where_clause, None, Some(1))?; + + if response.success { + if let Some(data) = response.data { + Ok(data.into_iter().next()) + } else { + Ok(None) + } + } else { + anyhow::bail!( + "Failed to load audit task {}: {:?}", + task_id, + response.error + ) + } +} + +/// Fetch audit task definitions with preview metadata for the audits UI +pub fn get_audit_tasks(api_client: &ApiClient, limit: Option) -> Result> { + let columns = Some(vec![ + "audit_tasks.id".into(), + "audit_tasks.task_name".into(), + "audit_tasks.json_sequence".into(), + "audit_tasks.created_at".into(), + "audit_tasks.updated_at".into(), + ]); + + let order_by = Some(vec![OrderBy { + column: "audit_tasks.updated_at".into(), + direction: "DESC".into(), + }]); + + let response = api_client.select("audit_tasks", columns, None, order_by, limit)?; + + if response.success { + let mut rows = response.data.unwrap_or_default(); + for row in &mut rows { + if let Some(map) = row.as_object_mut() { + let sequence_value = + if let Some(decoded) = decode_base64_json(map.get("json_sequence")) { + map.insert("json_sequence".into(), decoded.clone()); + decoded + } else { + let raw = map.get("json_sequence").cloned().unwrap_or(Value::Null); + if let Value::String(s) = &raw { + if let Ok(parsed) = serde_json::from_str::(s) { + map.insert("json_sequence".into(), parsed.clone()); + parsed + } else { + raw + } + } else { + raw + } + }; + + let preview = if sequence_value.is_null() { + String::new() + } else { + compact_json(&sequence_value) + }; + + let step_count = match &sequence_value { + Value::Array(arr) => arr.len() as i64, + Value::Object(obj) => obj.len() as i64, + _ => 0, + }; + + map.insert("sequence_preview".into(), Value::String(preview)); + map.insert("step_count".into(), Value::Number(step_count.into())); + } + } + + Ok(rows) + } else { + anyhow::bail!("Failed to load audit tasks: {:?}", response.error) + } +} + +/// Get active loans (borrowed/overdue/stolen), joined with borrower info +pub fn get_active_loans( + api_client: &ApiClient, + limit: Option, +) -> Result> { + // Query lending_history table with JOINs to get complete loan information + let columns = Some(vec![ + "lending_history.id".to_string(), + "lending_history.asset_id".to_string(), + "lending_history.borrower_id".to_string(), + "lending_history.checkout_date".to_string(), + "lending_history.due_date".to_string(), + "lending_history.return_date".to_string(), + "lending_history.notes".to_string(), + "assets.asset_tag".to_string(), + "assets.name".to_string(), + "assets.lending_status".to_string(), + "borrowers.name as borrower_name".to_string(), + "borrowers.class_name".to_string(), + ]); + let joins = Some(vec![ + Join { + table: "assets".into(), + on: "lending_history.asset_id = assets.id".into(), + join_type: "LEFT".into(), + }, + Join { + table: "borrowers".into(), + on: "lending_history.borrower_id = borrowers.id".into(), + join_type: "LEFT".into(), + }, + ]); + // Filter to active loans (no return date) + let filter = Some(serde_json::json!({ + "column": "lending_history.return_date", + "op": "is_null", + "value": null + })); + let order_by = Some(vec![OrderBy { + column: "lending_history.due_date".into(), + direction: "ASC".into(), + }]); + let resp = api_client.select_with_joins( + "lending_history", + columns, + None, + filter, + order_by, + limit, + joins, + )?; + if resp.success { + Ok(resp.data.unwrap_or_default()) + } else { + anyhow::bail!("Failed to load active loans: {:?}", resp.error) + } +} + +/// Get ALL loans (both active and returned), joined with borrower info +pub fn get_all_loans(api_client: &ApiClient, limit: Option) -> Result> { + // Query lending_history table with JOINs to get complete loan information + let columns = Some(vec![ + "lending_history.id".to_string(), + "lending_history.asset_id".to_string(), + "lending_history.borrower_id".to_string(), + "lending_history.checkout_date".to_string(), + "lending_history.due_date".to_string(), + "lending_history.return_date".to_string(), + "lending_history.notes".to_string(), + "assets.asset_tag".to_string(), + "assets.name".to_string(), + "assets.lending_status".to_string(), + "borrowers.name as borrower_name".to_string(), + "borrowers.class_name".to_string(), + ]); + let joins = Some(vec![ + Join { + table: "assets".into(), + on: "lending_history.asset_id = assets.id".into(), + join_type: "LEFT".into(), + }, + Join { + table: "borrowers".into(), + on: "lending_history.borrower_id = borrowers.id".into(), + join_type: "LEFT".into(), + }, + ]); + // No filter - get all loans + let order_by = Some(vec![ + OrderBy { + column: "lending_history.return_date".into(), + direction: "DESC".into(), + }, + OrderBy { + column: "lending_history.checkout_date".into(), + direction: "DESC".into(), + }, + ]); + let resp = api_client.select_with_joins( + "lending_history", + columns, + None, + None, + order_by, + limit, + joins, + )?; + if resp.success { + Ok(resp.data.unwrap_or_default()) + } else { + anyhow::bail!("Failed to load all loans: {:?}", resp.error) + } +} + +/// Get the most recent returned loan per asset for a given set of asset IDs +pub fn get_recent_returns_for_assets( + api_client: &ApiClient, + asset_ids: &[i64], + limit_per_asset: Option, + overall_limit: Option, +) -> Result> { + use crate::models::{Join, OrderBy, QueryRequest}; + + if asset_ids.is_empty() { + return Ok(vec![]); + } + + // Build a filter: return_date IS NOT NULL AND asset_id IN (...) + let filter = serde_json::json!({ + "and": [ + { "column": "lending_history.return_date", "op": "is_not_null", "value": null }, + { "column": "lending_history.asset_id", "op": "in", "value": asset_ids } + ] + }); + + let columns = Some(vec![ + "lending_history.id".to_string(), + "lending_history.asset_id".to_string(), + "lending_history.borrower_id".to_string(), + "lending_history.checkout_date".to_string(), + "lending_history.due_date".to_string(), + "lending_history.return_date".to_string(), + "borrowers.name as borrower_name".to_string(), + ]); + + let joins = Some(vec![Join { + table: "borrowers".to_string(), + on: "lending_history.borrower_id = borrowers.id".to_string(), + join_type: "LEFT".to_string(), + }]); + + // We sort by return_date DESC to get the most recent first + let order_by = Some(vec![OrderBy { + column: "lending_history.return_date".to_string(), + direction: "DESC".to_string(), + }]); + + let request = QueryRequest { + action: "select".to_string(), + table: "lending_history".to_string(), + columns, + data: None, + r#where: None, + filter: Some(filter), + order_by, + limit: overall_limit, + offset: None, + joins, + }; + + let resp = api_client.query(&request)?; + if resp.success { + let mut rows = if let Some(data) = resp.data { + if let Some(arr) = data.as_array() { + arr.clone() + } else { + vec![] + } + } else { + vec![] + }; + + // If a per-asset limit is desired, reduce here client-side + if let Some(max_per) = limit_per_asset { + use std::collections::HashMap; + let mut counts: HashMap = HashMap::new(); + rows.retain(|row| { + let aid = row.get("asset_id").and_then(|v| v.as_i64()).unwrap_or(-1); + let c = counts.entry(aid).or_insert(0); + if *c < max_per { + *c += 1; + true + } else { + false + } + }); + } + + Ok(rows) + } else { + anyhow::bail!("Failed to load recent returns: {:?}", resp.error) + } +} + +/// Summarize borrowers with active loan counts and overdue counts +pub fn get_borrowers_summary(api_client: &ApiClient) -> Result> { + // First, get all borrowers from the database + let all_borrowers_resp = api_client.select( + "borrowers", + Some(vec![ + "id".to_string(), + "name".to_string(), + "email".to_string(), + "phone_number".to_string(), + "class_name".to_string(), + "role".to_string(), + "notes".to_string(), + "banned".to_string(), + "unban_fine".to_string(), + ]), + None, + Some(vec![OrderBy { + column: "name".into(), + direction: "ASC".into(), + }]), + None, + )?; + + let all_borrowers = if all_borrowers_resp.success { + all_borrowers_resp.data.unwrap_or_default() + } else { + Vec::new() + }; + + // Fetch all active loans to calculate counts + let loans = get_active_loans(api_client, None)?; + use std::collections::HashMap; + // key: borrower_id, value: (total, overdue) + let mut loan_counts: HashMap = HashMap::new(); + for row in loans { + let borrower_id = row + .get("borrower_id") + .and_then(|v| v.as_i64()) + .unwrap_or(-1); + let status = row + .get("lending_status") + .and_then(|v| v.as_str()) + .unwrap_or(""); + let entry = loan_counts.entry(borrower_id).or_insert((0, 0)); + entry.0 += 1; // total + if status == "Overdue" || status == "Stolen" { + entry.1 += 1; + } + } + + // Combine borrower info with loan counts + let mut out = Vec::new(); + for borrower in all_borrowers { + let borrower_id = borrower.get("id").and_then(|v| v.as_i64()).unwrap_or(-1); + let name = borrower + .get("name") + .and_then(|v| v.as_str()) + .unwrap_or("") + .to_string(); + let email = borrower + .get("email") + .and_then(|v| v.as_str()) + .unwrap_or("") + .to_string(); + let phone = borrower + .get("phone_number") + .and_then(|v| v.as_str()) + .unwrap_or("") + .to_string(); + let class_name = borrower + .get("class_name") + .and_then(|v| v.as_str()) + .unwrap_or("") + .to_string(); + let role = borrower + .get("role") + .and_then(|v| v.as_str()) + .unwrap_or("") + .to_string(); + let notes = borrower + .get("notes") + .cloned() + .unwrap_or(serde_json::Value::Null); + let banned = borrower + .get("banned") + .and_then(|v| v.as_bool()) + .unwrap_or(false); + let unban_fine = borrower + .get("unban_fine") + .cloned() + .unwrap_or(serde_json::Value::Null); + + let (active_loans, overdue_loans) = + loan_counts.get(&borrower_id).copied().unwrap_or((0, 0)); + + out.push(serde_json::json!({ + "borrower_id": borrower_id, + "borrower_name": name, + "email": email, + "phone_number": phone, + "class_name": class_name, + "role": role, + "notes": notes, + "active_loans": active_loans, + "overdue_loans": overdue_loans, + "banned": banned, + "unban_fine": unban_fine, + })); + } + + // Sort by overdue desc, then active loans desc, then name asc + out.sort_by(|a, b| { + let ao = a.get("overdue_loans").and_then(|v| v.as_i64()).unwrap_or(0); + let bo = b.get("overdue_loans").and_then(|v| v.as_i64()).unwrap_or(0); + ao.cmp(&bo).reverse().then_with(|| { + let at = a.get("active_loans").and_then(|v| v.as_i64()).unwrap_or(0); + let bt = b.get("active_loans").and_then(|v| v.as_i64()).unwrap_or(0); + at.cmp(&bt).reverse().then_with(|| { + let an = a + .get("borrower_name") + .and_then(|v| v.as_str()) + .unwrap_or(""); + let bn = b + .get("borrower_name") + .and_then(|v| v.as_str()) + .unwrap_or(""); + an.cmp(bn) + }) + }) + }); + Ok(out) +} + +/// Get recent physical audits with zone and starter info +pub fn get_recent_audits( + api_client: &ApiClient, + limit: Option, +) -> Result> { + let columns = Some(vec![ + "physical_audits.id".into(), + "physical_audits.audit_type".into(), + "physical_audits.zone_id".into(), + "physical_audits.audit_name".into(), + "physical_audits.started_by".into(), + "physical_audits.started_at".into(), + "physical_audits.completed_at".into(), + "physical_audits.status".into(), + "physical_audits.timeout_minutes".into(), + "physical_audits.issues_found".into(), + "physical_audits.assets_expected".into(), + "physical_audits.assets_found".into(), + "physical_audits.notes".into(), + "physical_audits.cancelled_reason".into(), + // Joined labels + "zones.zone_code".into(), + "zones.zone_name".into(), + "users.name as started_by_name".into(), + ]); + let joins = Some(vec![ + Join { + table: "zones".into(), + on: "physical_audits.zone_id = zones.id".into(), + join_type: "LEFT".into(), + }, + Join { + table: "users".into(), + on: "physical_audits.started_by = users.id".into(), + join_type: "LEFT".into(), + }, + ]); + let order_by = Some(vec![OrderBy { + column: "physical_audits.started_at".into(), + direction: "DESC".into(), + }]); + let resp = api_client.select_with_joins( + "physical_audits", + columns, + None, + None, + order_by, + limit, + joins, + )?; + if resp.success { + let mut rows = resp.data.unwrap_or_default(); + for row in &mut rows { + if let Some(map) = row.as_object_mut() { + let zone_code = map.get("zone_code").and_then(|v| v.as_str()).unwrap_or(""); + let zone_name = map.get("zone_name").and_then(|v| v.as_str()).unwrap_or(""); + + let zone_display = if zone_name.is_empty() && zone_code.is_empty() { + "-".to_string() + } else if zone_name.is_empty() { + zone_code.to_string() + } else if zone_code.is_empty() { + zone_name.to_string() + } else { + format!("{} ({})", zone_name, zone_code) + }; + + let issues_value = + if let Some(decoded) = decode_base64_json(map.get("issues_found")) { + map.insert("issues_found".into(), decoded.clone()); + decoded + } else { + map.get("issues_found").cloned().unwrap_or(Value::Null) + }; + + let summary = if issues_value.is_null() { + String::new() + } else { + compact_json(&issues_value) + }; + + map.insert("zone_display".into(), Value::String(zone_display)); + map.insert("issues_summary".into(), Value::String(summary)); + } + } + + Ok(rows) + } else { + anyhow::bail!("Failed to load audits: {:?}", resp.error) + } +} + +/// Get recent physical audit logs with asset and zone info +pub fn get_recent_audit_logs( + api_client: &ApiClient, + limit: Option, +) -> Result> { + let columns = Some(vec![ + "physical_audit_logs.id".into(), + "physical_audit_logs.physical_audit_id".into(), + "physical_audit_logs.asset_id".into(), + "physical_audit_logs.audit_date".into(), + "physical_audit_logs.audited_by".into(), + "physical_audit_logs.status_found".into(), + "physical_audit_logs.audit_task_id".into(), + "physical_audit_logs.audit_task_responses".into(), + "physical_audit_logs.exception_type".into(), + "physical_audit_logs.exception_details".into(), + "physical_audit_logs.found_in_zone_id".into(), + "physical_audit_logs.auditor_action".into(), + "physical_audit_logs.notes".into(), + // Joins + "assets.asset_tag".into(), + "assets.name as asset_name".into(), + "zones.zone_code as found_zone_code".into(), + "zones.zone_name as found_zone_name".into(), + "users.name as audited_by_name".into(), + ]); + let joins = Some(vec![ + Join { + table: "assets".into(), + on: "physical_audit_logs.asset_id = assets.id".into(), + join_type: "LEFT".into(), + }, + Join { + table: "zones".into(), + on: "physical_audit_logs.found_in_zone_id = zones.id".into(), + join_type: "LEFT".into(), + }, + Join { + table: "users".into(), + on: "physical_audit_logs.audited_by = users.id".into(), + join_type: "LEFT".into(), + }, + ]); + let order_by = Some(vec![OrderBy { + column: "physical_audit_logs.audit_date".into(), + direction: "DESC".into(), + }]); + let resp = api_client.select_with_joins( + "physical_audit_logs", + columns, + None, + None, + order_by, + limit, + joins, + )?; + if resp.success { + let mut rows = resp.data.unwrap_or_default(); + for row in &mut rows { + if let Some(map) = row.as_object_mut() { + let asset_display = match ( + map.get("asset_tag").and_then(|v| v.as_str()), + map.get("asset_name").and_then(|v| v.as_str()), + ) { + (Some(tag), Some(name)) if !tag.is_empty() => format!("{} ({})", name, tag), + (_, Some(name)) => name.to_string(), + _ => "-".to_string(), + }; + + let found_zone_display = match ( + map.get("found_zone_code").and_then(|v| v.as_str()), + map.get("found_zone_name").and_then(|v| v.as_str()), + ) { + (Some(code), Some(name)) if !code.is_empty() => { + format!("{} ({})", name, code) + } + (_, Some(name)) => name.to_string(), + _ => "-".to_string(), + }; + + let responses_value = + if let Some(decoded) = decode_base64_json(map.get("audit_task_responses")) { + map.insert("audit_task_responses".into(), decoded.clone()); + decoded + } else { + map.get("audit_task_responses") + .cloned() + .unwrap_or(Value::Null) + }; + + let responses_text = if responses_value.is_null() { + String::new() + } else { + compact_json(&responses_value) + }; + + map.insert("asset_display".into(), Value::String(asset_display)); + map.insert( + "found_zone_display".into(), + Value::String(found_zone_display), + ); + map.insert("task_responses_text".into(), Value::String(responses_text)); + } + } + + Ok(rows) + } else { + anyhow::bail!("Failed to load audit logs: {:?}", resp.error) + } +} + +/// Get templates with useful joined labels +pub fn get_templates(api_client: &ApiClient, limit: Option) -> Result> { + let columns = Some(vec![ + "templates.id".into(), + "templates.template_code".into(), + "templates.asset_tag_generation_string".into(), + "templates.description".into(), + "templates.active".into(), + "templates.asset_type".into(), + "templates.name".into(), + "templates.category_id".into(), + "categories.category_name".into(), + "categories.category_code".into(), + "templates.manufacturer".into(), + "templates.model".into(), + "templates.zone_id".into(), + "zones.zone_code".into(), + "zones.zone_name".into(), + "templates.zone_plus".into(), + "templates.zone_note".into(), + "templates.status".into(), + "templates.price".into(), + // New financial & date base fields + "templates.purchase_date".into(), + "templates.purchase_date_now".into(), + "templates.warranty_until".into(), + // Auto-calc warranty fields + "templates.warranty_auto".into(), + "templates.warranty_auto_amount".into(), + "templates.warranty_auto_unit".into(), + "templates.expiry_date".into(), + // Auto-calc expiry fields + "templates.expiry_auto".into(), + "templates.expiry_auto_amount".into(), + "templates.expiry_auto_unit".into(), + "templates.quantity_total".into(), + "templates.quantity_used".into(), + "templates.supplier_id".into(), + "suppliers.name as supplier_name".into(), + "templates.lendable".into(), + "templates.lending_status".into(), + "templates.minimum_role_for_lending".into(), + "templates.audit_task_id".into(), + "audit_tasks.task_name as audit_task_name".into(), + "templates.no_scan".into(), + "templates.notes".into(), + "templates.additional_fields".into(), + "templates.created_at".into(), + // Label template fields + "templates.label_template_id".into(), + "label_templates.template_name as label_template_name".into(), + ]); + let joins = Some(vec![ + Join { + table: "categories".into(), + on: "templates.category_id = categories.id".into(), + join_type: "LEFT".into(), + }, + Join { + table: "zones".into(), + on: "templates.zone_id = zones.id".into(), + join_type: "LEFT".into(), + }, + Join { + table: "suppliers".into(), + on: "templates.supplier_id = suppliers.id".into(), + join_type: "LEFT".into(), + }, + Join { + table: "label_templates".into(), + on: "templates.label_template_id = label_templates.id".into(), + join_type: "LEFT".into(), + }, + Join { + table: "audit_tasks".into(), + on: "templates.audit_task_id = audit_tasks.id".into(), + join_type: "LEFT".into(), + }, + ]); + let order_by = Some(vec![OrderBy { + column: "templates.created_at".into(), + direction: "DESC".into(), + }]); + let resp = + api_client.select_with_joins("templates", columns, None, None, order_by, limit, joins)?; + if resp.success { + let mut rows = resp.data.unwrap_or_default(); + + for row in rows.iter_mut() { + if let Some(map) = row.as_object_mut() { + // Decode additional_fields JSON (handles base64-wrapped legacy payloads) + if let Some(decoded) = decode_base64_json(map.get("additional_fields")) { + map.insert("additional_fields".into(), decoded); + } else if let Some(Value::String(raw_json)) = map.get("additional_fields") { + if let Ok(parsed) = serde_json::from_str::(raw_json) { + map.insert("additional_fields".into(), parsed); + } + } + } + } + + Ok(rows) + } else { + anyhow::bail!("Failed to load templates: {:?}", resp.error) + } +} + +/// Get suppliers +pub fn get_suppliers(api_client: &ApiClient, limit: Option) -> Result> { + let columns = Some(vec![ + "suppliers.id".into(), + "suppliers.name".into(), + "suppliers.contact".into(), + "suppliers.email".into(), + "suppliers.phone".into(), + "suppliers.website".into(), + "suppliers.notes".into(), + "suppliers.created_at".into(), + ]); + let order_by = Some(vec![OrderBy { + column: "suppliers.name".into(), + direction: "ASC".into(), + }]); + let resp = api_client.select("suppliers", columns, None, order_by, limit)?; + if resp.success { + Ok(resp.data.unwrap_or_default()) + } else { + anyhow::bail!("Failed to load suppliers: {:?}", resp.error) + } +} + +/// Get printers +pub fn get_printers(api_client: &ApiClient) -> Result> { + let columns = Some(vec![ + "printer_settings.id".into(), + "printer_settings.printer_name".into(), + "printer_settings.description".into(), + "printer_settings.log".into(), + "printer_settings.can_be_used_for_reports".into(), + "printer_settings.min_powerlevel_to_use".into(), + "printer_settings.printer_plugin".into(), + "printer_settings.printer_settings".into(), + "printer_settings.created_at".into(), + ]); + let order_by = Some(vec![OrderBy { + column: "printer_settings.printer_name".into(), + direction: "ASC".into(), + }]); + let resp = api_client.select("printer_settings", columns, None, order_by, None)?; + if resp.success { + let mut rows = resp.data.unwrap_or_default(); + // Backend returns printer_settings as JSON object; convert to pretty string for editor display + for row in rows.iter_mut() { + if let Some(printer_settings_val) = row.get("printer_settings") { + // If it's already a JSON object, pretty-print it + if printer_settings_val.is_object() || printer_settings_val.is_array() { + if let Ok(pretty) = serde_json::to_string_pretty(printer_settings_val) { + if let Some(map) = row.as_object_mut() { + map.insert( + "printer_settings".to_string(), + serde_json::Value::String(pretty), + ); + } + } + } + // Fallback: try base64 decode for backward compatibility + else if let Some(decoded) = decode_base64_json(Some(printer_settings_val)) { + if let Ok(pretty) = serde_json::to_string_pretty(&decoded) { + if let Some(map) = row.as_object_mut() { + map.insert( + "printer_settings".to_string(), + serde_json::Value::String(pretty), + ); + } + } + } + } + } + Ok(rows) + } else { + anyhow::bail!("Failed to load printers: {:?}", resp.error) + } +} + +/// Get label templates +pub fn get_label_templates(api_client: &ApiClient) -> Result> { + let columns = Some(vec![ + "label_templates.id".into(), + "label_templates.template_code".into(), + "label_templates.template_name".into(), + "label_templates.layout_json".into(), + "label_templates.created_at".into(), + ]); + let order_by = Some(vec![OrderBy { + column: "label_templates.template_name".into(), + direction: "ASC".into(), + }]); + let resp = api_client.select("label_templates", columns, None, order_by, None)?; + if resp.success { + let mut rows = resp.data.unwrap_or_default(); + // Backend returns layout_json as JSON object; convert to pretty string for editor display + for row in rows.iter_mut() { + if let Some(layout_val) = row.get("layout_json") { + // If it's already a JSON object, pretty-print it + if layout_val.is_object() || layout_val.is_array() { + if let Ok(pretty) = serde_json::to_string_pretty(layout_val) { + if let Some(map) = row.as_object_mut() { + map.insert( + "layout_json".to_string(), + serde_json::Value::String(pretty), + ); + } + } + } + // Fallback: try base64 decode for backward compatibility + else if let Some(decoded) = decode_base64_json(Some(layout_val)) { + if let Ok(pretty) = serde_json::to_string_pretty(&decoded) { + if let Some(map) = row.as_object_mut() { + map.insert( + "layout_json".to_string(), + serde_json::Value::String(pretty), + ); + } + } + } + } + } + Ok(rows) + } else { + anyhow::bail!("Failed to load label templates: {:?}", resp.error) + } +} + +/// Get categories +pub fn get_categories( + api_client: &ApiClient, + limit: Option, +) -> Result> { + let columns = Some(vec![ + "categories.id".into(), + "categories.category_name".into(), + "categories.category_code".into(), + "categories.category_description".into(), + "categories.parent_id".into(), + "parent.category_name AS parent_category_name".into(), + ]); + let joins = Some(vec![Join { + join_type: "LEFT".into(), + table: "categories AS parent".into(), + on: "categories.parent_id = parent.id".into(), + }]); + let order_by = Some(vec![OrderBy { + column: "categories.category_name".into(), + direction: "ASC".into(), + }]); + let resp = + api_client.select_with_joins("categories", columns, None, None, order_by, limit, joins)?; + if resp.success { + Ok(resp.data.unwrap_or_default()) + } else { + anyhow::bail!("Failed to load categories: {:?}", resp.error) + } +} -- cgit v1.2.3-70-g09d2