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<serde_json::Value> {
    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::<serde_json::Value>(&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::<String>() + &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<Vec<serde_json::Value>> {
    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::<Vec<_>>())
            );
        }

        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<Vec<serde_json::Value>> {
    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::<serde_json::Value>(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::<serde_json::Value>(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::<String>() + c.as_str(),
        None => String::new(),
    }
}

/// Get issues with useful labels
pub fn get_issues(api_client: &ApiClient, limit: Option<u32>) -> Result<Vec<serde_json::Value>> {
    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<u32>,
    where_clause: Option<serde_json::Value>,
    filter: Option<serde_json::Value>,
) -> Result<Vec<serde_json::Value>> {
    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<serde_json::Value>,
) -> Result<Vec<serde_json::Value>> {
    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<u32>,
) -> Result<Vec<serde_json::Value>> {
    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<Option<Value>> {
    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::<Value>(&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<Option<Value>> {
    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::<i64>() {
        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<Option<Value>> {
    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<u32>) -> Result<Vec<Value>> {
    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::<Value>(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<u32>,
) -> Result<Vec<serde_json::Value>> {
    // 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<u32>) -> Result<Vec<serde_json::Value>> {
    // 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<u32>,
    overall_limit: Option<u32>,
) -> Result<Vec<serde_json::Value>> {
    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<i64, u32> = 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<Vec<serde_json::Value>> {
    // 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<i64, (i32, i32)> = 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<u32>,
) -> Result<Vec<serde_json::Value>> {
    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<u32>,
) -> Result<Vec<serde_json::Value>> {
    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<u32>) -> Result<Vec<serde_json::Value>> {
    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::<Value>(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<u32>) -> Result<Vec<serde_json::Value>> {
    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<Vec<serde_json::Value>> {
    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<Vec<serde_json::Value>> {
    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<u32>,
) -> Result<Vec<serde_json::Value>> {
    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)
    }
}