scout_rs 0.95.53

use anyhow::{anyhow, Result};

pub use crate::db_client::ConnectivityWithCoordinates;
use crate::db_client::{DatabaseConfig, ScoutDbClient};
use crate::models::*;

/// Response type for `get_tags_for_artifact` / `get_tags_for_event` RPCs (`tags_pretty_location`).
#[derive(Debug, Clone, serde::Deserialize, serde::Serialize)]
#[serde(rename_all = "snake_case")]
pub struct TagPrettyLocation {
    pub id: Option<i64>,
    pub inserted_at: Option<String>,
    pub artifact_id: Option<i64>,
    pub event_id: Option<i64>,
    pub detector: Option<String>,
    pub width: Option<f64>,
    pub height: Option<f64>,
    pub conf: Option<f64>,
    pub x: Option<f64>,
    pub y: Option<f64>,
    #[serde(rename = "class")]
    pub class: Option<String>,
    pub timestamp_observation: Option<String>,
    pub frame_index: Option<i64>,
    #[serde(default)]
    pub subject_id: Option<i64>,
    pub origin_latitude: Option<f64>,
    pub origin_longitude: Option<f64>,
    pub subject_latitude: Option<f64>,
    pub subject_longitude: Option<f64>,
}

fn parse_postgrest_error_message(body: &str) -> Option<String> {
    let v: serde_json::Value = serde_json::from_str(body).ok()?;
    let obj = v.as_object()?;
    let msg = obj.get("message")?.as_str()?;
    Some(msg.to_string())
}

// ===== CLIENT IMPLEMENTATION =====

#[derive(Debug)]
pub struct ScoutClient {
    pub config_db: DatabaseConfig,
    pub device: Option<DevicePrettyLocation>,
    pub herd: Option<Herd>,
    db_client: Option<ScoutDbClient>,
    is_offline: bool,
}

impl ScoutClient {
    /// Creates a new ScoutClient instance.
    ///
    /// # Arguments
    /// * `api_key` - The API key for the Scout service.
    pub fn new(config_db: DatabaseConfig) -> Self {
        Self {
            config_db,
            device: None,
            herd: None,
            db_client: None,
            is_offline: false,
        }
    }

    /// Initializes the client in offline mode with default placeholder values
    /// This allows using the sync engine without database connectivity
    pub fn initialize_offline(&mut self) {
        self.is_offline = true;

        // Create default device with placeholder values
        let device = DevicePrettyLocation::default();
        self.device = Some(device);

        // Create default herd with placeholder values
        let herd = Herd::default();
        self.herd = Some(herd);
    }

    /// Identifies the device and herd, then establishes direct database connection
    /// If in offline mode, sets default values and returns Ok(())
    pub async fn identify(&mut self) -> Result<()> {
        // If already in offline mode, just ensure defaults are set
        if self.is_offline {
            if self.device.is_none() {
                self.device = Some(DevicePrettyLocation::default());
            }
            if self.herd.is_none() {
                self.herd = Some(Herd::default());
            }
            return Ok(());
        }

        let mut db_client = ScoutDbClient::new(self.config_db.clone());
        db_client.connect()?;

        self.db_client = Some(db_client);

        let device = self.get_device_from_db().await?;

        let herd = self.get_herd_from_db(device.herd_id).await?;

        self.device = Some(device);
        self.herd = Some(herd);

        Ok(())
    }

    /// Gets device information using get_device_by_api_key function and parsing JSON response
    async fn get_device_from_db(&mut self) -> Result<DevicePrettyLocation> {
        let config_db = self.config_db.clone();
        let api_key = config_db.get_scout_api_key();
        let db_client = self.get_db_client()?;

        let client = db_client.get_client()?;
        let response = client
            .rpc(
                "get_device_by_api_key",
                serde_json::json!({
                    "device_api_key": api_key
                })
                .to_string(),
            )
            .execute()
            .await?;

        let status = response.status();
        let body = response.text().await?;

        if !status.is_success() {
            let msg = parse_postgrest_error_message(&body).unwrap_or_else(|| body.clone());
            return Err(anyhow!("Device lookup failed ({}): {}", status, msg));
        }

        if let Some(msg) = parse_postgrest_error_message(&body) {
            return Err(anyhow!("Device lookup failed: {}", msg));
        }

        let device_pretty: DevicePrettyLocation = serde_json::from_str(&body).map_err(|e| {
            anyhow!(
                "Failed to parse device response: {} - Response: {}",
                e,
                body
            )
        })?;

        Ok(device_pretty)
    }

    /// Gets herd information directly from database
    async fn get_herd_from_db(&mut self, herd_id: i64) -> Result<Herd> {
        let db_client = self.get_db_client()?;

        let results = db_client
            .query(|client| {
                client
                    .from("herds")
                    .select("*")
                    .eq("id", herd_id.to_string())
                    .limit(1)
            })
            .await?;

        if results.is_empty() {
            return Err(anyhow!("No herd found for ID: {}", herd_id));
        }

        Ok(results.into_iter().next().unwrap())
    }

    /// Gets the database client, ensuring it's available
    fn get_db_client(&mut self) -> Result<&mut ScoutDbClient> {
        if self.is_offline {
            return Err(anyhow!(
                "Database operations not available in offline mode. Call initialize_offline() to use offline mode."
            ));
        }
        self.db_client
            .as_mut()
            .ok_or_else(|| anyhow!("Database client not initialized. Call identify() first."))
    }

    /// Checks if the client has been identified and has a database connection
    /// Returns true if identified normally or if in offline mode
    pub fn is_identified(&self) -> bool {
        (self.db_client.is_some() && self.device.is_some() && self.herd.is_some())
            || self.is_offline
    }

    // ===== HELPER METHODS =====

    /// Checks if a session exists in the database by device_id, start timestamp, and end timestamp
    pub async fn does_session_exist(
        &mut self,
        device_id: i64,
        timestamp_start: &str,
    ) -> Result<bool> {
        let db_client = self.get_db_client()?;

        #[derive(Debug, serde::Deserialize)]
        #[allow(dead_code)]
        struct SessionIdOnly {
            id: i64,
        }

        let results: Vec<SessionIdOnly> = db_client
            .query(|client| {
                client
                    .from("sessions")
                    .select("id")
                    .eq("device_id", device_id.to_string())
                    .eq("timestamp_start", timestamp_start)
                    .limit(1)
            })
            .await?;

        Ok(!results.is_empty())
    }

    /// Convenience method to check if a session exists using a Session object
    pub async fn does_session_exist_from_session(&mut self, session: &Session) -> Result<bool> {
        self.does_session_exist(session.device_id, &session.timestamp_start)
            .await
    }

    /// Helper to create a success response
    fn success_response<T>(data: T) -> ResponseScout<T> {
        ResponseScout::new(ResponseScoutStatus::Success, Some(data))
    }

    /// Helper to create a failure response
    fn failure_response<T>() -> ResponseScout<T> {
        ResponseScout::new(ResponseScoutStatus::Failure, None)
    }

    /// Helper to handle database insert results
    fn handle_insert_result<T>(result: Vec<T>) -> Result<ResponseScout<T>> {
        if result.is_empty() {
            Ok(Self::failure_response())
        } else {
            Ok(Self::success_response(result.into_iter().next().unwrap()))
        }
    }

    /// Helper to handle database query results
    fn handle_query_result<T>(result: Vec<T>) -> ResponseScout<Vec<T>> {
        Self::success_response(result)
    }

    // ===== BACKWARD COMPATIBILITY METHODS =====

    /// Gets device information
    pub async fn get_device(&mut self) -> Result<ResponseScout<DevicePrettyLocation>> {
        if let Some(device) = &self.device {
            return Ok(ResponseScout::new(
                ResponseScoutStatus::Success,
                Some(device.clone()),
            ));
        }

        self.identify().await?;

        if let Some(device) = &self.device {
            Ok(ResponseScout::new(
                ResponseScoutStatus::Success,
                Some(device.clone()),
            ))
        } else {
            Ok(ResponseScout::new(ResponseScoutStatus::Failure, None))
        }
    }

    /// Gets herd information (backward compatibility method)
    pub async fn get_herd(&mut self, herd_id: Option<i64>) -> Result<ResponseScout<Herd>> {
        let herd_id = if let Some(id) = herd_id {
            id
        } else if let Some(device) = &self.device {
            device.herd_id
        } else {
            return Err(anyhow!("No herd_id provided and no device data available"));
        };

        if let Some(herd) = &self.herd {
            if herd.id == Some(herd_id) {
                return Ok(ResponseScout::new(
                    ResponseScoutStatus::Success,
                    Some(herd.clone()),
                ));
            }
        }

        if self.device.is_none() {
            self.identify().await?;
        }

        if let Some(herd) = &self.herd {
            if herd.id == Some(herd_id) {
                Ok(ResponseScout::new(
                    ResponseScoutStatus::Success,
                    Some(herd.clone()),
                ))
            } else {
                Ok(ResponseScout::new(ResponseScoutStatus::Failure, None))
            }
        } else {
            Ok(ResponseScout::new(ResponseScoutStatus::Failure, None))
        }
    }

    // ===== DIRECT DATABASE OPERATIONS =====

    /// Creates an event directly in the database
    pub async fn create_event(&mut self, event: &Event) -> Result<ResponseScout<Event>> {
        let db_client = self.get_db_client()?;
        let result = db_client.insert("events", event).await?;
        Self::handle_insert_result(result)
    }

    /// Creates tags for an event directly in the database
    /// RLS policies and foreign key constraints handle validation automatically
    pub async fn create_tags(
        &mut self,
        event_id: i64,
        tags: &[Tag],
    ) -> Result<ResponseScout<Vec<Tag>>> {
        let db_client = self.get_db_client()?;

        if tags.is_empty() {
            return Ok(ResponseScout::new(
                ResponseScoutStatus::Success,
                Some(Vec::new()),
            ));
        }

        // Prepare tags with event_id for bulk insert
        let tags_with_event_id: Vec<Tag> = tags
            .iter()
            .map(|tag| {
                let mut tag_with_event_id = tag.clone();
                tag_with_event_id.update_event_id(event_id);
                tag_with_event_id
            })
            .collect();

        let result = db_client
            .insert_bulk_batched_by_shape("tags", &tags_with_event_id)
            .await?;
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(result),
        ))
    }
}

/// Bulk-insert tags; groups rows by serialized JSON key set for PostgREST.
pub async fn push_tags_batched(db: &mut ScoutDbClient, tags: &[Tag]) -> Result<()> {
    if tags.is_empty() {
        return Ok(());
    }
    db.insert_bulk_batched_by_shape("tags", tags).await?;
    Ok(())
}

impl ScoutClient {
    /// Creates an event with tags (compatibility method)
    pub async fn create_event_with_tags(
        &mut self,
        event: &Event,
        tags: &[Tag],
        _file_path: Option<&str>,
    ) -> Result<ResponseScout<Event>> {
        let event_response = self.create_event(event).await?;

        if event_response.status != ResponseScoutStatus::Success {
            return Ok(event_response);
        }

        let created_event = event_response.data.unwrap();

        if !tags.is_empty() {
            let tags_response = self.create_tags(created_event.id.unwrap(), tags).await?;
            if tags_response.status != ResponseScoutStatus::Success {
                return Ok(ResponseScout::new(ResponseScoutStatus::Failure, None));
            }
        }

        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(created_event),
        ))
    }

    /// Creates a session directly in the database
    pub async fn create_session(&mut self, session: &Session) -> Result<ResponseScout<Session>> {
        let db_client = self.get_db_client()?;
        let result = db_client.insert("sessions", session).await?;
        Self::handle_insert_result(result)
    }

    /// Creates connectivity data directly from the database
    pub async fn create_connectivity(
        &mut self,
        connectivity: &Connectivity,
    ) -> Result<ResponseScout<Connectivity>> {
        let db_client = self.get_db_client()?;
        let result = db_client.insert("connectivity", connectivity).await?;
        Self::handle_insert_result(result)
    }

    /// Gets sessions for a herd directly from the database
    pub async fn get_sessions_by_herd(
        &mut self,
        herd_id: i64,
    ) -> Result<ResponseScout<Vec<Session>>> {
        let db_client = self.get_db_client()?;
        let results = db_client
            .query(|client| {
                client
                    .from("sessions")
                    .select("*, devices!inner(herd_id)")
                    .eq("devices.herd_id", herd_id.to_string())
                    .order("timestamp_start.desc")
            })
            .await?;
        Ok(Self::handle_query_result(results))
    }

    /// Gets plans for a herd directly from the database
    pub async fn get_plans_by_herd(&mut self, herd_id: i64) -> Result<ResponseScout<Vec<Plan>>> {
        let db_client = self.get_db_client()?;
        let results = db_client
            .query(|client| {
                client
                    .from("plans")
                    .eq("herd_id", herd_id.to_string())
                    .order("inserted_at.desc")
            })
            .await?;

        // Return empty results if no plans found (don't panic)
        Ok(Self::handle_query_result(results))
    }

    /// Gets a specific plan by ID directly from the database
    pub async fn get_plan_by_id(&mut self, plan_id: i64) -> Result<ResponseScout<Plan>> {
        let db_client = self.get_db_client()?;

        let results = db_client
            .query(|client| {
                client
                    .from("plans")
                    .select("*")
                    .eq("id", plan_id.to_string())
                    .limit(1)
            })
            .await?;

        // Return failure status if no plan found (don't panic)
        if results.is_empty() {
            return Ok(ResponseScout::new(ResponseScoutStatus::Failure, None));
        }

        if results.len() > 1 {
            panic!(
                "Multiple plans found with ID: {}. Expected exactly one plan.",
                plan_id
            );
        }

        let plan = results.into_iter().next().unwrap();
        Ok(ResponseScout::new(ResponseScoutStatus::Success, Some(plan)))
    }

    /// Creates a plan directly in the database
    pub async fn create_plan(&mut self, plan: &Plan) -> Result<ResponseScout<Plan>> {
        let db_client = self.get_db_client()?;

        // Create a plan for insertion without ID field
        let plan_for_insert = PlanInsert {
            id: None,          // Will be auto-generated by database
            id_local: None,    // Add if you are upserting to sync engine
            inserted_at: None, // Database will use default value
            name: plan.name.clone(),
            instructions: plan.instructions.clone(),
            herd_id: plan.herd_id,
            plan_type: plan.plan_type.clone(),
        };

        let result = db_client.insert("plans", &plan_for_insert).await?;

        // Convert PlanInsert results back to Plan with generated IDs
        let plans: Vec<Plan> = result
            .into_iter()
            .map(|p| Plan {
                id: Some(p.id.unwrap_or(0)), // Use generated ID or fallback to 0
                id_local: None,
                inserted_at: p.inserted_at,
                name: p.name,
                instructions: p.instructions,
                herd_id: p.herd_id,
                plan_type: p.plan_type,
            })
            .collect();

        Self::handle_insert_result(plans)
    }

    /// Updates a plan directly in the database
    pub async fn update_plan(&mut self, plan_id: i64, plan: &Plan) -> Result<ResponseScout<Plan>> {
        let db_client = self.get_db_client()?;

        let result = db_client
            .update(plan, |client| {
                client.from("plans").eq("id", plan_id.to_string())
            })
            .await?;

        if result.is_empty() {
            return Ok(ResponseScout::new(ResponseScoutStatus::Failure, None));
        }

        let updated_plan = result.into_iter().next().unwrap();
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(updated_plan),
        ))
    }

    /// Deletes a plan directly from the database
    pub async fn delete_plan(&mut self, plan_id: i64) -> Result<ResponseScout<()>> {
        let db_client = self.get_db_client()?;

        db_client
            .delete(|client| client.from("plans").eq("id", plan_id.to_string()))
            .await?;

        Ok(ResponseScout::new(ResponseScoutStatus::Success, None))
    }

    /// Gets events for a session directly from the database
    pub async fn get_session_events(
        &mut self,
        session_id: i64,
    ) -> Result<ResponseScout<Vec<Event>>> {
        let db_client = self.get_db_client()?;
        let results = db_client
            .query(|client| {
                client
                    .from("events")
                    .eq("session_id", session_id.to_string())
                    .order("timestamp_observation.desc")
            })
            .await?;
        Ok(Self::handle_query_result(results))
    }

    /// Gets connectivity data for a session directly from the database
    pub async fn get_session_connectivity(
        &mut self,
        session_id: i64,
    ) -> Result<ResponseScout<Vec<Connectivity>>> {
        let db_client = self.get_db_client()?;
        let results = db_client
            .query(|client| {
                client
                    .from("connectivity")
                    .eq("session_id", session_id.to_string())
                    .order("timestamp_start.asc")
            })
            .await?;
        Ok(Self::handle_query_result(results))
    }

    /// Updates a session directly in the database
    pub async fn update_session(
        &mut self,
        session_id: i64,
        session: &Session,
    ) -> Result<ResponseScout<Session>> {
        let db_client = self.get_db_client()?;

        let result = db_client
            .update(session, |client| {
                client.from("sessions").eq("id", session_id.to_string())
            })
            .await?;

        if result.is_empty() {
            return Ok(ResponseScout::new(ResponseScoutStatus::Failure, None));
        }

        let updated_session = result.into_iter().next().unwrap();
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(updated_session),
        ))
    }

    /// Deletes a session directly from the database
    /// Database cascade deletion handles dependent records automatically
    pub async fn delete_session(&mut self, session_id: i64) -> Result<ResponseScout<()>> {
        let db_client = self.get_db_client()?;

        let session_deleted = db_client
            .delete(|client| client.from("sessions").eq("id", session_id.to_string()))
            .await;

        if session_deleted.is_err() {
            return Ok(ResponseScout::new(ResponseScoutStatus::Failure, None));
        }

        Ok(ResponseScout::new(ResponseScoutStatus::Success, None))
    }

    /// Deletes an event directly from the database
    /// Database cascade deletion handles dependent records automatically
    pub async fn delete_event(&mut self, event_id: i64) -> Result<ResponseScout<()>> {
        let db_client = self.get_db_client()?;

        let _event_deleted = db_client
            .delete(|client| client.from("events").eq("id", event_id.to_string()))
            .await?;

        Ok(ResponseScout::new(ResponseScoutStatus::Success, None))
    }

    /// Deletes a tag directly from the database
    pub async fn delete_tag(&mut self, tag_id: i64) -> Result<ResponseScout<()>> {
        let db_client = self.get_db_client()?;

        db_client
            .delete(|client| client.from("tags").eq("id", tag_id.to_string()))
            .await?;

        Ok(ResponseScout::new(ResponseScoutStatus::Success, None))
    }

    /// Deletes connectivity data directly from the database
    pub async fn delete_connectivity(&mut self, connectivity_id: i64) -> Result<ResponseScout<()>> {
        let db_client = self.get_db_client()?;

        db_client
            .delete(|client| {
                client
                    .from("connectivity")
                    .eq("id", connectivity_id.to_string())
            })
            .await?;

        Ok(ResponseScout::new(ResponseScoutStatus::Success, None))
    }

    // ===== ADDITIONAL OPERATIONS =====

    /// Gets all devices for a herd directly from the database
    pub async fn get_devices_by_herd(
        &mut self,
        herd_id: i64,
    ) -> Result<ResponseScout<Vec<Device>>> {
        let db_client = self.get_db_client()?;

        let results = db_client
            .query(|client| {
                client
                    .from("devices")
                    .eq("herd_id", herd_id.to_string())
                    .order("inserted_at.desc")
            })
            .await?;

        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(results),
        ))
    }

    /// Gets all devices that the current user/device has permission to view.
    /// RLS policies will automatically filter results based on permissions:
    /// - Devices can see themselves and other devices in the same herd
    /// - Users can see devices in herds they have view access to
    pub async fn get_all_devices(&mut self) -> Result<ResponseScout<Vec<Device>>> {
        let db_client = self.get_db_client()?;

        let results = db_client
            .query(|client| client.from("devices").order("inserted_at.desc"))
            .await?;

        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(results),
        ))
    }

    /// Gets devices in the same herd as the current device (peer devices).
    /// This method requires the client to be identified first.
    /// Returns an error if the device or herd is not set.
    pub async fn get_peer_devices(&mut self) -> Result<ResponseScout<Vec<Device>>> {
        // Check if client is identified with device info
        let device = self
            .device
            .as_ref()
            .ok_or_else(|| anyhow!("Device not identified - call identify() first"))?;

        // Check if device has a herd_id
        let herd_id = device.herd_id;
        if herd_id <= 0 {
            return Err(anyhow!(
                "Device herd_id is invalid - ensure device is properly configured"
            ));
        }

        // Get all devices in the same herd using existing method
        self.get_devices_by_herd(herd_id).await
    }

    /// Gets a specific event by ID directly from the database
    pub async fn get_event_by_id(&mut self, event_id: i64) -> Result<ResponseScout<Event>> {
        let db_client = self.get_db_client()?;

        let results = db_client
            .query(|client| {
                client
                    .from("events")
                    .select("*")
                    .eq("id", event_id.to_string())
                    .limit(1)
            })
            .await?;

        if results.is_empty() {
            return Ok(ResponseScout::new(ResponseScoutStatus::Failure, None));
        }

        let event = results.into_iter().next().unwrap();
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(event),
        ))
    }

    /// Gets a specific device by ID directly from the database
    pub async fn get_device_by_id(&mut self, device_id: i64) -> Result<ResponseScout<Device>> {
        let db_client = self.get_db_client()?;

        let results = db_client
            .query(|client| {
                client
                    .from("devices")
                    .select("*")
                    .eq("id", device_id.to_string())
                    .limit(1)
            })
            .await?;

        if results.is_empty() {
            return Ok(ResponseScout::new(ResponseScoutStatus::Failure, None));
        }

        let device = results.into_iter().next().unwrap();
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(device),
        ))
    }

    /// Gets a specific herd by ID directly from the database
    pub async fn get_herd_by_id(&mut self, herd_id: i64) -> Result<ResponseScout<Herd>> {
        let db_client = self.get_db_client()?;

        let results = db_client
            .query(|client| {
                client
                    .from("herds")
                    .select("*")
                    .eq("id", herd_id.to_string())
                    .limit(1)
            })
            .await?;

        if results.is_empty() {
            return Ok(ResponseScout::new(ResponseScoutStatus::Failure, None));
        }

        let device = results.into_iter().next().unwrap();
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(device),
        ))
    }

    /// Gets all events for a device directly from the database
    pub async fn get_device_events(&mut self, device_id: i64) -> Result<ResponseScout<Vec<Event>>> {
        let db_client = self.get_db_client()?;

        let results = db_client
            .query(|client| {
                client
                    .from("events")
                    .eq("device_id", device_id.to_string())
                    .order("timestamp_observation.desc")
            })
            .await?;

        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(results),
        ))
    }

    /// Gets events with tags for a device directly from the database
    pub async fn get_device_events_with_tags(
        &mut self,
        device_id: i64,
    ) -> Result<ResponseScout<Vec<Event>>> {
        let db_client = self.get_db_client()?;

        let results = db_client
            .query(|client| {
                client
                    .from("events")
                    .select("*, tags(*)")
                    .eq("device_id", device_id.to_string())
                    .order("timestamp_observation.desc")
            })
            .await?;

        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(results),
        ))
    }

    /// Gets events with tags for a device using the database function
    pub async fn get_device_events_with_tags_via_function(
        &mut self,
        device_id: i64,
        limit: i64,
    ) -> Result<ResponseScout<Vec<Event>>> {
        let db_client = self.get_db_client()?;

        let results = db_client
            .query(|client| {
                client.rpc(
                    "get_events_and_tags_for_device",
                    serde_json::json!({
                        "device_id_caller": device_id,
                        "limit_caller": limit
                    })
                    .to_string(),
                )
            })
            .await?;

        Ok(Self::handle_query_result(results))
    }

    /// Gets events within a time range directly from the database
    pub async fn get_events_in_timerange(
        &mut self,
        start_time: &str,
        end_time: &str,
    ) -> Result<ResponseScout<Vec<Event>>> {
        let db_client = self.get_db_client()?;

        let results = db_client
            .query(|client| {
                client
                    .from("events")
                    .gte("timestamp_observation", start_time)
                    .lte("timestamp_observation", end_time)
                    .order("timestamp_observation.desc")
            })
            .await?;

        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(results),
        ))
    }

    /// Gets events within a geographic area directly from the database
    pub async fn get_events_in_area(
        &mut self,
        min_lat: f64,
        max_lat: f64,
        min_lon: f64,
        max_lon: f64,
    ) -> Result<ResponseScout<Vec<Event>>> {
        let db_client = self.get_db_client()?;

        let results = db_client
            .query(|client| {
                client
                    .from("events")
                    .select("*")
                    .gte("latitude", min_lat.to_string())
                    .lte("latitude", max_lat.to_string())
                    .gte("longitude", min_lon.to_string())
                    .lte("longitude", max_lon.to_string())
                    .order("timestamp_observation.desc")
            })
            .await?;

        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(results),
        ))
    }

    /// Creates multiple events in a batch directly in the database
    pub async fn create_events_batch(
        &mut self,
        events: &[Event],
    ) -> Result<ResponseScout<Vec<Event>>> {
        let db_client = self.get_db_client()?;

        if events.is_empty() {
            return Ok(ResponseScout::new(
                ResponseScoutStatus::Success,
                Some(Vec::new()),
            ));
        }

        let result = db_client
            .insert_bulk_batched_by_shape("events", events)
            .await?;
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(result),
        ))
    }

    /// Creates multiple sessions in a batch directly in the database
    pub async fn create_sessions_batch(
        &mut self,
        sessions: &[Session],
    ) -> Result<ResponseScout<Vec<Session>>> {
        let db_client = self.get_db_client()?;

        if sessions.is_empty() {
            return Ok(ResponseScout::new(
                ResponseScoutStatus::Success,
                Some(Vec::new()),
            ));
        }

        let result = db_client
            .insert_bulk_batched_by_shape("sessions", sessions)
            .await?;
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(result),
        ))
    }

    /// Creates multiple connectivity entries in a batch directly in the database
    pub async fn create_connectivity_batch(
        &mut self,
        connectivity_entries: &[Connectivity],
    ) -> Result<ResponseScout<Vec<Connectivity>>> {
        let db_client = self.get_db_client()?;

        if connectivity_entries.is_empty() {
            return Ok(ResponseScout::new(
                ResponseScoutStatus::Success,
                Some(Vec::new()),
            ));
        }

        let result = db_client
            .insert_bulk_batched_by_shape("connectivity", connectivity_entries)
            .await?;
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(result),
        ))
    }

    /// Upserts multiple sessions in a batch (insert or update on conflict)
    pub async fn upsert_sessions_batch(
        &mut self,
        sessions: &[Session],
    ) -> Result<ResponseScout<Vec<Session>>> {
        let db_client = self.get_db_client()?;

        if sessions.is_empty() {
            return Ok(ResponseScout::new(
                ResponseScoutStatus::Success,
                Some(Vec::new()),
            ));
        }

        let result = db_client
            .upsert_bulk_batched_by_shape("sessions", sessions)
            .await?;
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(result),
        ))
    }

    /// Upserts multiple connectivity entries in a batch (insert or update on conflict)
    pub async fn upsert_connectivity_batch(
        &mut self,
        connectivity_entries: &[Connectivity],
    ) -> Result<ResponseScout<Vec<Connectivity>>> {
        let db_client = self.get_db_client()?;

        if connectivity_entries.is_empty() {
            return Ok(ResponseScout::new(
                ResponseScoutStatus::Success,
                Some(Vec::new()),
            ));
        }

        let result = db_client
            .upsert_bulk_batched_by_shape("connectivity", connectivity_entries)
            .await?;
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(result),
        ))
    }

    /// Upserts multiple events in a batch (insert or update on conflict)
    pub async fn upsert_events_batch(
        &mut self,
        events: &[Event],
    ) -> Result<ResponseScout<Vec<Event>>> {
        let db_client = self.get_db_client()?;

        if events.is_empty() {
            return Ok(ResponseScout::new(
                ResponseScoutStatus::Success,
                Some(Vec::new()),
            ));
        }

        let result = db_client
            .upsert_bulk_batched_by_shape("events", events)
            .await?;
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(result),
        ))
    }

    /// Upserts multiple tags in a batch (insert or update on conflict)
    pub async fn upsert_tags_batch(&mut self, tags: &[Tag]) -> Result<ResponseScout<Vec<Tag>>> {
        let db_client = self.get_db_client()?;

        if tags.is_empty() {
            return Ok(ResponseScout::new(
                ResponseScoutStatus::Success,
                Some(Vec::new()),
            ));
        }

        let result = db_client
            .upsert_bulk_batched_by_shape("tags", tags)
            .await?;
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(result),
        ))
    }

    /// Upserts multiple operators in a batch (insert or update on conflict)
    pub async fn upsert_operators_batch(
        &mut self,
        operators: &[data::v2::Operator],
    ) -> Result<ResponseScout<Vec<data::v2::Operator>>> {
        let db_client = self.get_db_client()?;

        if operators.is_empty() {
            return Ok(ResponseScout::new(
                ResponseScoutStatus::Success,
                Some(Vec::new()),
            ));
        }

        let result = db_client
            .upsert_bulk_batched_by_shape("operators", operators)
            .await?;
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(result),
        ))
    }

    /// Updates an event directly in the database
    pub async fn update_event(
        &mut self,
        event_id: i64,
        event: &Event,
    ) -> Result<ResponseScout<Event>> {
        let db_client = self.get_db_client()?;

        let result = db_client
            .update(event, |client| {
                client.from("events").eq("id", event_id.to_string())
            })
            .await?;

        if result.is_empty() {
            return Ok(ResponseScout::new(ResponseScoutStatus::Failure, None));
        }

        let updated_event = result.into_iter().next().unwrap();
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(updated_event),
        ))
    }

    /// Updates connectivity data directly in the database
    pub async fn update_connectivity(
        &mut self,
        connectivity_id: i64,
        connectivity: &Connectivity,
    ) -> Result<ResponseScout<Connectivity>> {
        let db_client = self.get_db_client()?;

        let result = db_client
            .update(connectivity, |client| {
                client
                    .from("connectivity")
                    .eq("id", connectivity_id.to_string())
            })
            .await?;

        if result.is_empty() {
            return Err(anyhow!(
                "Failed to update connectivity entry - no data returned"
            ));
        }

        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(result.into_iter().next().unwrap()),
        ))
    }

    /// Gets connectivity data with coordinates directly from the database
    pub async fn get_connectivity_with_coordinates(
        &mut self,
        session_id: i64,
    ) -> Result<ResponseScout<Vec<Connectivity>>> {
        let db_client = self.get_db_client()?;

        let results = db_client
            .query(|client| {
                client
                    .from("connectivity")
                    .eq("session_id", session_id.to_string())
                    .order("timestamp_start.asc")
            })
            .await?;

        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(results),
        ))
    }

    /// Gets connectivity records for an artifact (uses session when set, else device + time window).
    pub async fn get_connectivity_for_artifact(
        &mut self,
        artifact_id: i64,
        max_elements: Option<i32>,
    ) -> Result<ResponseScout<Vec<ConnectivityWithCoordinates>>> {
        let db_client = self.get_db_client()?;
        let client = db_client.get_client()?;
        let max = max_elements.unwrap_or(1000);

        let response = client
            .rpc(
                "get_connectivity_for_artifact",
                serde_json::json!({
                    "artifact_id_caller": artifact_id,
                    "max_elements_caller": max
                })
                .to_string(),
            )
            .execute()
            .await?;

        let status = response.status();
        let body = response.text().await?;
        if !status.is_success() {
            return Ok(ResponseScout::new(
                ResponseScoutStatus::Failure,
                Some(
                    serde_json::from_str(&body).unwrap_or_else(|_| {
                        vec![]
                    }),
                ),
            ));
        }

        let results: Vec<ConnectivityWithCoordinates> = serde_json::from_str(&body).map_err(|e| {
            anyhow!(
                "Failed to parse get_connectivity_for_artifact response: {} - {}",
                e,
                body
            )
        })?;

        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(results),
        ))
    }

    /// Gets artifact IDs for a session (same device, overlapping session interval).
    pub async fn get_artifact_ids_for_session(
        &mut self,
        session_id: i64,
    ) -> Result<ResponseScout<Vec<i64>>> {
        let db_client = self.get_db_client()?;
        let client = db_client.get_client()?;

        let response = client
            .rpc(
                "get_artifact_ids_for_session",
                serde_json::json!({ "session_id_caller": session_id }).to_string(),
            )
            .execute()
            .await?;

        let status = response.status();
        let body = response.text().await?;
        if !status.is_success() {
            return Ok(ResponseScout::new(
                ResponseScoutStatus::Failure,
                Some(vec![]),
            ));
        }

        let results: Vec<i64> = serde_json::from_str(&body).map_err(|e| {
            anyhow!(
                "Failed to parse get_artifact_ids_for_session response: {} - {}",
                e,
                body
            )
        })?;

        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(results),
        ))
    }

    /// Gets tags for an artifact with optional timestamp range and max items.
    pub async fn get_tags_for_artifact(
        &mut self,
        artifact_id: i64,
        start_timestamp: Option<&str>,
        end_timestamp: Option<&str>,
        max_elements: Option<i32>,
    ) -> Result<ResponseScout<Vec<TagPrettyLocation>>> {
        let db_client = self.get_db_client()?;
        let client = db_client.get_client()?;
        let max = max_elements.unwrap_or(1000);

        let mut params = serde_json::json!({
            "artifact_id_caller": artifact_id,
            "max_elements_caller": max
        });
        if let Some(s) = start_timestamp {
            params["start_timestamp_caller"] = serde_json::Value::String(s.to_string());
        }
        if let Some(e) = end_timestamp {
            params["end_timestamp_caller"] = serde_json::Value::String(e.to_string());
        }

        let response = client
            .rpc("get_tags_for_artifact", params.to_string())
            .execute()
            .await?;

        let status = response.status();
        let body = response.text().await?;
        if !status.is_success() {
            return Ok(ResponseScout::new(
                ResponseScoutStatus::Failure,
                Some(
                    serde_json::from_str(&body).unwrap_or_else(|_| vec![]),
                ),
            ));
        }

        let results: Vec<TagPrettyLocation> = serde_json::from_str(&body).map_err(|e| {
            anyhow!(
                "Failed to parse get_tags_for_artifact response: {} - {}",
                e,
                body
            )
        })?;

        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(results),
        ))
    }

    /// Gets tags for an event with optional timestamp range and max items (same RPC shape as [`Self::get_tags_for_artifact`]).
    pub async fn get_tags_for_event(
        &mut self,
        event_id: i64,
        start_timestamp: Option<&str>,
        end_timestamp: Option<&str>,
        max_elements: Option<i32>,
    ) -> Result<ResponseScout<Vec<TagPrettyLocation>>> {
        let db_client = self.get_db_client()?;
        let client = db_client.get_client()?;
        let max = max_elements.unwrap_or(1000);

        let mut params = serde_json::json!({
            "event_id_caller": event_id,
            "max_elements_caller": max
        });
        if let Some(s) = start_timestamp {
            params["start_timestamp_caller"] = serde_json::Value::String(s.to_string());
        }
        if let Some(e) = end_timestamp {
            params["end_timestamp_caller"] = serde_json::Value::String(e.to_string());
        }

        let response = client
            .rpc("get_tags_for_event", params.to_string())
            .execute()
            .await?;

        let status = response.status();
        let body = response.text().await?;
        if !status.is_success() {
            return Ok(ResponseScout::new(
                ResponseScoutStatus::Failure,
                Some(
                    serde_json::from_str(&body).unwrap_or_else(|_| vec![]),
                ),
            ));
        }

        let results: Vec<TagPrettyLocation> = serde_json::from_str(&body).map_err(|e| {
            anyhow!(
                "Failed to parse get_tags_for_event response: {} - {}",
                e,
                body
            )
        })?;

        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(results),
        ))
    }

    /// Ends a session by updating its timestamp_end directly in the database
    pub async fn end_session(
        &mut self,
        session_id: i64,
        timestamp_end: u64,
    ) -> Result<ResponseScout<()>> {
        let mut session = Session::new(
            0,
            timestamp_end,
            Some(timestamp_end),
            "".to_string(),
            None,
            0.0,
            0.0,
            0.0,
            0.0,
            0.0,
            0.0,
            0.0,
            0.0,
        );

        session.timestamp_end = Some(
            chrono::DateTime::from_timestamp(timestamp_end as i64, 0)
                .unwrap_or_else(|| chrono::Utc::now())
                .to_rfc3339(),
        );

        let response = self.update_session(session_id, &session).await?;
        if response.status == ResponseScoutStatus::Success {
            Ok(ResponseScout::new(ResponseScoutStatus::Success, None))
        } else {
            Ok(ResponseScout::new(ResponseScoutStatus::Failure, None))
        }
    }

    /// Gets statistics for a session directly from the database
    pub async fn get_session_statistics(
        &mut self,
        session_id: i64,
    ) -> Result<ResponseScout<Session>> {
        let db_client = self.get_db_client()?;

        let results = db_client
            .query(|client| {
                client
                    .from("sessions")
                    .select("*")
                    .eq("id", session_id.to_string())
                    .limit(1)
            })
            .await?;

        if results.is_empty() {
            return Ok(ResponseScout::new(ResponseScoutStatus::Failure, None));
        }

        let session = results.into_iter().next().unwrap();
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(session),
        ))
    }

    // ===== COMPATIBILITY METHODS =====

    /// Compatibility method for post_events_batch
    pub async fn post_events_batch(
        &mut self,
        events_and_files: &[(Event, Vec<Tag>, String)],
        _batch_size: usize,
    ) -> Result<ResponseScout<Vec<Event>>> {
        let mut created_events = Vec::new();

        for (event, tags, _file_path) in events_and_files {
            let event_response = self.create_event(event).await?;
            if event_response.status != ResponseScoutStatus::Success {
                return Ok(ResponseScout::new(ResponseScoutStatus::Failure, None));
            }

            let created_event = event_response.data.unwrap();
            if !tags.is_empty() {
                let tags_response = self.create_tags(created_event.id.unwrap(), tags).await?;
                if tags_response.status != ResponseScoutStatus::Success {
                    return Ok(ResponseScout::new(ResponseScoutStatus::Failure, None));
                }
            }
            created_events.push(created_event);
        }

        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(created_events),
        ))
    }

    /// Gets zones and actions for a herd directly from the database
    pub async fn get_zones_and_actions_by_herd(
        &mut self,
        herd_id: i64,
        limit: i64,
        offset: i64,
    ) -> Result<ResponseScout<Vec<Zone>>> {
        let db_client = self.get_db_client()?;

        let results = db_client
            .query(|client| {
                client
                    .from("zones_and_actions")
                    .eq("herd_id", herd_id.to_string())
                    .order("inserted_at.desc")
                    .range(offset as usize, (offset + limit - 1) as usize)
            })
            .await?;

        Ok(Self::handle_query_result(results))
    }

    // ===== ARTIFACT OPERATIONS =====

    /// Creates an artifact directly in the database
    pub async fn create_artifact(
        &mut self,
        artifact: &Artifact,
    ) -> Result<ResponseScout<Artifact>> {
        let db_client = self.get_db_client()?;
        let result = db_client.insert("artifacts", artifact).await?;
        Self::handle_insert_result(result)
    }

    /// Gets artifacts for a session directly from the database
    pub async fn get_artifacts_by_session(
        &mut self,
        session_id: i64,
    ) -> Result<ResponseScout<Vec<Artifact>>> {
        let db_client = self.get_db_client()?;

        let results = db_client
            .query(|client| {
                client
                    .from("artifacts")
                    .eq("session_id", session_id.to_string())
                    .order("created_at.desc")
            })
            .await?;

        Ok(Self::handle_query_result(results))
    }

    /// Gets all artifacts for a herd (via sessions) directly from the database
    pub async fn get_artifacts_by_herd(
        &mut self,
        herd_id: i64,
    ) -> Result<ResponseScout<Vec<Artifact>>> {
        let db_client = self.get_db_client()?;

        let client = db_client.get_client()?;
        let response = client
            .rpc(
                "get_artifacts_for_herd",
                serde_json::json!({
                    "herd_id_caller": herd_id,
                    "limit_caller": 1000,
                    "offset_caller": 0
                })
                .to_string(),
            )
            .execute()
            .await?;

        let body = response.text().await?;
        let results: Vec<Artifact> = serde_json::from_str(&body).map_err(|e| {
            anyhow!(
                "Failed to parse artifacts response: {} - Response: {}",
                e,
                body
            )
        })?;

        Ok(Self::handle_query_result(results))
    }

    /// Updates an artifact directly in the database
    pub async fn update_artifact(
        &mut self,
        artifact_id: i64,
        artifact: &Artifact,
    ) -> Result<ResponseScout<Artifact>> {
        let db_client = self.get_db_client()?;

        let result = db_client
            .update(artifact, |client| {
                client.from("artifacts").eq("id", artifact_id.to_string())
            })
            .await?;

        if result.is_empty() {
            return Ok(ResponseScout::new(ResponseScoutStatus::Failure, None));
        }

        let updated_artifact = result.into_iter().next().unwrap();
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(updated_artifact),
        ))
    }

    /// Deletes an artifact directly from the database
    pub async fn delete_artifact(&mut self, artifact_id: i64) -> Result<ResponseScout<()>> {
        let db_client = self.get_db_client()?;

        db_client
            .delete(|client| client.from("artifacts").eq("id", artifact_id.to_string()))
            .await?;

        Ok(ResponseScout::new(ResponseScoutStatus::Success, None))
    }

    /// Creates multiple artifacts in a batch directly in the database
    pub async fn create_artifacts_batch(
        &mut self,
        artifacts: &[Artifact],
    ) -> Result<ResponseScout<Vec<Artifact>>> {
        let db_client = self.get_db_client()?;

        if artifacts.is_empty() {
            return Ok(ResponseScout::new(
                ResponseScoutStatus::Success,
                Some(Vec::new()),
            ));
        }

        let result = db_client
            .insert_bulk_batched_by_shape("artifacts", artifacts)
            .await?;
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(result),
        ))
    }

    /// Upserts multiple artifacts in a batch (insert or update on conflict)
    pub async fn upsert_artifacts_batch(
        &mut self,
        artifacts: &[Artifact],
    ) -> Result<ResponseScout<Vec<Artifact>>> {
        let db_client = self.get_db_client()?;

        if artifacts.is_empty() {
            return Ok(ResponseScout::new(
                ResponseScoutStatus::Success,
                Some(Vec::new()),
            ));
        }

        let result = db_client
            .upsert_bulk_batched_by_shape("artifacts", artifacts)
            .await?;
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(result),
        ))
    }

    /// Inserts Vertex multimodal 001 embeddings into the dedicated embeddings table.
    /// Each row must have either event_id or artifact_id set; optional origin/sensor metadata can be included.
    pub async fn create_embeddings(
        &mut self,
        embeddings: &[EmbeddingVertexMultimodal001],
    ) -> Result<ResponseScout<Vec<EmbeddingVertexMultimodal001>>> {
        let db_client = self.get_db_client()?;

        if embeddings.is_empty() {
            return Ok(ResponseScout::new(
                ResponseScoutStatus::Success,
                Some(Vec::new()),
            ));
        }

        let result = db_client
            .insert_bulk_batched_by_shape("embeddings_vertex_multimodal_001", embeddings)
            .await?;
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(result),
        ))
    }

    /// Inserts SAM3 segmentation rows into `segmentations_sam3`.
    /// Each row must have either `event_id` or `artifact_id` set; `mask_rle` is required (COCO-style RLE JSON).
    pub async fn create_segmentations_sam3(
        &mut self,
        rows: &[SegmentationSam3],
    ) -> Result<ResponseScout<Vec<SegmentationSam3>>> {
        let db_client = self.get_db_client()?;

        if rows.is_empty() {
            return Ok(ResponseScout::new(
                ResponseScoutStatus::Success,
                Some(Vec::new()),
            ));
        }

        let result = db_client
            .insert_bulk_batched_by_shape("segmentations_sam3", rows)
            .await?;
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(result),
        ))
    }

    /// Creates a heartbeat record for a device
    pub async fn create_heartbeat(
        &mut self,
        heartbeat: &Heartbeat,
    ) -> Result<ResponseScout<Heartbeat>> {
        let db_client = self.get_db_client()?;
        let result = db_client.insert("heartbeats", heartbeat).await?;
        Self::handle_insert_result(result)
    }

    /// Gets all heartbeats for a specific device
    pub async fn get_heartbeats_by_device(
        &mut self,
        device_id: i64,
    ) -> Result<ResponseScout<Vec<Heartbeat>>> {
        let db_client = self.get_db_client()?;

        let results = db_client
            .query(|client| {
                client
                    .from("heartbeats")
                    .select("*")
                    .eq("device_id", device_id.to_string())
                    .order("timestamp.desc")
            })
            .await?;

        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            Some(results),
        ))
    }

    /// Deletes a heartbeat record by ID
    ///
    /// **Note:** This method is primarily intended for testing and cleanup purposes.
    /// In production, heartbeats are typically append-only for audit trail purposes.
    pub async fn delete_heartbeat(&mut self, heartbeat_id: i64) -> Result<ResponseScout<()>> {
        let db_client = self.get_db_client()?;

        let _heartbeat_deleted = db_client
            .delete(|client| client.from("heartbeats").eq("id", heartbeat_id.to_string()))
            .await?;

        Ok(ResponseScout::new(ResponseScoutStatus::Success, None))
    }

    // ===== HEALTH METRICS =====

    /// Creates a single health metric row (device_id, timestamp, metric_name, value, optional source/unit).
    pub async fn create_health_metric(
        &mut self,
        metric: &HealthMetric,
    ) -> Result<ResponseScout<HealthMetric>> {
        let db_client = self.get_db_client()?;
        let result = db_client.insert("health_metrics", metric).await?;
        Self::handle_insert_result(result)
    }

    /// Creates multiple health metrics in a batch.
    pub async fn create_health_metrics_batch(
        &mut self,
        metrics: &[HealthMetric],
    ) -> Result<ResponseScout<Vec<HealthMetric>>> {
        let db_client = self.get_db_client()?;
        if metrics.is_empty() {
            return Ok(ResponseScout::new(
                ResponseScoutStatus::Success,
                Some(Vec::new()),
            ));
        }
        let result = db_client
            .insert_bulk_batched_by_shape("health_metrics", metrics)
            .await?;
        Ok(ResponseScout::new(ResponseScoutStatus::Success, Some(result)))
    }

    /// Gets health metrics for a device, newest first. Optional limit.
    pub async fn get_health_metrics(
        &mut self,
        device_id: i64,
        limit: Option<u32>,
    ) -> Result<ResponseScout<Vec<HealthMetric>>> {
        let db_client = self.get_db_client()?;
        let results = db_client
            .query(|client| {
                let q = client
                    .from("health_metrics")
                    .select("*")
                    .eq("device_id", device_id.to_string())
                    .order("timestamp.desc");
                match limit {
                    Some(n) => q.limit(n as usize),
                    None => q,
                }
            })
            .await?;
        Ok(Self::handle_query_result(results))
    }

    /// Updates a health metric by id (partial update; only non-None fields applied).
    pub async fn update_health_metric(
        &mut self,
        id: i64,
        metric: &HealthMetric,
    ) -> Result<ResponseScout<HealthMetric>> {
        let db_client = self.get_db_client()?;
        let result = db_client
            .update(metric, |client| client.from("health_metrics").eq("id", id.to_string()))
            .await?;
        if result.is_empty() {
            return Ok(ResponseScout::new(ResponseScoutStatus::Failure, None));
        }
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            result.into_iter().next(),
        ))
    }

    /// Deletes a health metric by id.
    pub async fn delete_health_metric(&mut self, id: i64) -> Result<ResponseScout<()>> {
        let db_client = self.get_db_client()?;
        db_client
            .delete(|client| client.from("health_metrics").eq("id", id.to_string()))
            .await?;
        Ok(ResponseScout::new(ResponseScoutStatus::Success, None))
    }

    // ===== ANALYSIS USAGE =====

    pub async fn create_analysis_job(
        &mut self,
        job: &AnalysisJob,
    ) -> Result<ResponseScout<AnalysisJob>> {
        let db_client = self.get_db_client()?;
        let result = db_client.insert("analysis_jobs", job).await?;
        Self::handle_insert_result(result)
    }

    pub async fn create_analysis_jobs_batch(
        &mut self,
        jobs: &[AnalysisJob],
    ) -> Result<ResponseScout<Vec<AnalysisJob>>> {
        let db_client = self.get_db_client()?;
        if jobs.is_empty() {
            return Ok(ResponseScout::new(
                ResponseScoutStatus::Success,
                Some(Vec::new()),
            ));
        }
        let result = db_client
            .insert_bulk_batched_by_shape("analysis_jobs", jobs)
            .await?;
        Ok(ResponseScout::new(ResponseScoutStatus::Success, Some(result)))
    }

    pub async fn get_analysis_job(&mut self, id: i64) -> Result<ResponseScout<AnalysisJob>> {
        let db_client = self.get_db_client()?;
        match db_client
            .query_one(|client| client.from("analysis_jobs").select("*").eq("id", id.to_string()))
            .await
        {
            Ok(row) => Ok(ResponseScout::new(ResponseScoutStatus::Success, Some(row))),
            Err(_) => Ok(ResponseScout::new(ResponseScoutStatus::Failure, None)),
        }
    }

    pub async fn get_analysis_jobs(
        &mut self,
        status: Option<AnalysisWorkStatus>,
        limit: Option<u32>,
    ) -> Result<ResponseScout<Vec<AnalysisJob>>> {
        let db_client = self.get_db_client()?;
        let results = db_client
            .query(|client| {
                let q = client.from("analysis_jobs").select("*");
                let q = match status {
                    Some(st) => q.eq("status", st.as_str()),
                    None => q,
                };
                let q = q.order("inserted_at.desc");
                match limit {
                    Some(n) => q.limit(n as usize),
                    None => q,
                }
            })
            .await?;
        Ok(Self::handle_query_result(results))
    }

    pub async fn update_analysis_job(
        &mut self,
        id: i64,
        job: &AnalysisJob,
    ) -> Result<ResponseScout<AnalysisJob>> {
        let db_client = self.get_db_client()?;
        let result = db_client
            .update(job, |client| client.from("analysis_jobs").eq("id", id.to_string()))
            .await?;
        if result.is_empty() {
            return Ok(ResponseScout::new(ResponseScoutStatus::Failure, None));
        }
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            result.into_iter().next(),
        ))
    }

    pub async fn delete_analysis_job(&mut self, id: i64) -> Result<ResponseScout<()>> {
        let db_client = self.get_db_client()?;
        db_client
            .delete(|client| client.from("analysis_jobs").eq("id", id.to_string()))
            .await?;
        Ok(ResponseScout::new(ResponseScoutStatus::Success, None))
    }

    pub async fn create_analysis_task(
        &mut self,
        task: &AnalysisTask,
    ) -> Result<ResponseScout<AnalysisTask>> {
        let db_client = self.get_db_client()?;
        let result = db_client.insert("analysis_tasks", task).await?;
        Self::handle_insert_result(result)
    }

    pub async fn create_analysis_tasks_batch(
        &mut self,
        tasks: &[AnalysisTask],
    ) -> Result<ResponseScout<Vec<AnalysisTask>>> {
        let db_client = self.get_db_client()?;
        if tasks.is_empty() {
            return Ok(ResponseScout::new(
                ResponseScoutStatus::Success,
                Some(Vec::new()),
            ));
        }
        let result = db_client
            .insert_bulk_batched_by_shape("analysis_tasks", tasks)
            .await?;
        Ok(ResponseScout::new(ResponseScoutStatus::Success, Some(result)))
    }

    pub async fn get_analysis_task(&mut self, id: i64) -> Result<ResponseScout<AnalysisTask>> {
        let db_client = self.get_db_client()?;
        match db_client
            .query_one(|client| {
                client
                    .from("analysis_tasks")
                    .select("*")
                    .eq("id", id.to_string())
            })
            .await
        {
            Ok(row) => Ok(ResponseScout::new(ResponseScoutStatus::Success, Some(row))),
            Err(_) => Ok(ResponseScout::new(ResponseScoutStatus::Failure, None)),
        }
    }

    pub async fn get_analysis_tasks_for_job(
        &mut self,
        job_id: i64,
        limit: Option<u32>,
    ) -> Result<ResponseScout<Vec<AnalysisTask>>> {
        let db_client = self.get_db_client()?;
        let results = db_client
            .query(|client| {
                let q = client
                    .from("analysis_tasks")
                    .select("*")
                    .eq("job_id", job_id.to_string())
                    .order("id.desc");
                match limit {
                    Some(n) => q.limit(n as usize),
                    None => q,
                }
            })
            .await?;
        Ok(Self::handle_query_result(results))
    }

    pub async fn update_analysis_task(
        &mut self,
        id: i64,
        task: &AnalysisTask,
    ) -> Result<ResponseScout<AnalysisTask>> {
        let db_client = self.get_db_client()?;
        let result = db_client
            .update(task, |client| client.from("analysis_tasks").eq("id", id.to_string()))
            .await?;
        if result.is_empty() {
            return Ok(ResponseScout::new(ResponseScoutStatus::Failure, None));
        }
        Ok(ResponseScout::new(
            ResponseScoutStatus::Success,
            result.into_iter().next(),
        ))
    }

    pub async fn delete_analysis_task(&mut self, id: i64) -> Result<ResponseScout<()>> {
        let db_client = self.get_db_client()?;
        db_client
            .delete(|client| client.from("analysis_tasks").eq("id", id.to_string()))
            .await?;
        Ok(ResponseScout::new(ResponseScoutStatus::Success, None))
    }
}