agent-supplements-rec 0.1.0

use std::path::PathBuf;

use rusqlite::Connection;

use crate::errors::SupplementsError;

/// Resolve the database path: --db flag > LABSTORE_DB env > ~/.labstore/labstore.db
pub fn resolve_db_path(cli_path: Option<&str>) -> Result<PathBuf, SupplementsError> {
    if let Some(p) = cli_path {
        return Ok(PathBuf::from(p));
    }

    if let Ok(env_path) = std::env::var("LABSTORE_DB") {
        return Ok(PathBuf::from(env_path));
    }

    let home = std::env::var("HOME")
        .map_err(|_| SupplementsError::Config("HOME environment variable not set".into()))?;
    let dir = PathBuf::from(home).join(".labstore");
    Ok(dir.join("labstore.db"))
}

/// Open the shared database (read-only — we never write).
pub fn open(db_path: &PathBuf) -> Result<Connection, SupplementsError> {
    let conn = Connection::open_with_flags(
        db_path,
        rusqlite::OpenFlags::SQLITE_OPEN_READ_ONLY | rusqlite::OpenFlags::SQLITE_OPEN_NO_MUTEX,
    )?;
    conn.execute_batch("PRAGMA foreign_keys=ON;")?;
    Ok(conn)
}

/// Look up patient by slug, return (id, name, sex, dob).
pub fn get_patient(
    conn: &Connection,
    slug: &str,
) -> Result<(i64, String, String, String), SupplementsError> {
    conn.query_row(
        "SELECT id, name, sex, dob FROM patients WHERE slug = ?1 AND archived = 0",
        [slug],
        |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?, row.get(3)?)),
    )
    .map_err(|e| match e {
        rusqlite::Error::QueryReturnedNoRows => {
            SupplementsError::PatientNotFound(slug.to_string())
        }
        other => SupplementsError::Db(other),
    })
}

/// Load the latest biomarker values for a patient: Vec<(standardized_name, value, unit)>
pub fn load_latest_biomarkers(
    conn: &Connection,
    patient_id: i64,
) -> Result<Vec<(String, f64, String)>, SupplementsError> {
    let mut stmt = conn.prepare(
        "SELECT b.standardized_name, b.value, b.unit
         FROM biomarkers b
         JOIN lab_sessions s ON b.session_id = s.id
         WHERE s.patient_id = ?1
         ORDER BY s.date DESC",
    )?;

    let mut seen = std::collections::HashSet::new();
    let mut results = Vec::new();

    let rows = stmt.query_map([patient_id], |row| {
        Ok((
            row.get::<_, String>(0)?,
            row.get::<_, f64>(1)?,
            row.get::<_, String>(2)?,
        ))
    })?;

    for row in rows {
        let (name, value, unit) = row?;
        // First occurrence = most recent (ORDER BY date DESC)
        if seen.insert(name.clone()) {
            results.push((name, value, unit));
        }
    }

    Ok(results)
}