soil-report-ai/lib/consultant.php

<?php
/**
 * lib/consultant.php
 *
 * Shared functions for the Consultant Dashboard.
 * Provides client listing with aggregated test counts and
 * alert generation from soil test values vs. specifications.
 */

// ─── Nutrient alert thresholds ────────────────────────────────────────────────
//
// These nutrient columns exist on both soil_records and soil_specifications.
// Spec values are treated as the minimum acceptable target.
// Where no spec row exists, we fall back to the hardcoded pH sanity ranges.

const ALERT_NUTRIENTS = [
    'ph_cacl2'  => 'pH (CaCl₂)',
    'ph_h2o'    => 'pH (H₂O)',
    'ec'        => 'EC',
    'NO3_N'     => 'Nitrate-N',
    'NH3_N'     => 'Ammonium-N',
    'p_morgan'  => 'Phosphorus',
    'k_morgan'  => 'Potassium',
    'ca_morgan' => 'Calcium',
    'mg_morgan' => 'Magnesium',
    's_morgan'  => 'Sulphur',
    'ocarbon'   => 'Organic Carbon',
    'b_cacl2'   => 'Boron',
    'zn_dtpa'   => 'Zinc',
    'mn_dtpa'   => 'Manganese',
    'cu_dtpa'   => 'Copper',
    'fe_dtpa'   => 'Iron',
];

// Hardcoded sanity ranges used when no soil_specifications row is found.
// Format: [min, max] — null means unchecked on that side.
const FALLBACK_RANGES = [
    'ph_cacl2' => [5.5, 7.5],
    'ph_h2o'   => [6.0, 8.0],
    'ec'       => [null, 1.5],
];

/**
 * Load all clients belonging to a consultant with aggregated test counts,
 * most-recent test date, and alert counts.
 *
 * @param PDO $pdo
 * @param int $userId  consultant's modx_user_id
 * @return array[]
 */
function getConsultantClients(PDO $pdo, int $userId): array
{
    $sql = "
        SELECT
            cr.id,
            cr.client,
            cr.company,
            cr.address,
            cr.state_postcode,
            cr.email,
            cr.phone,

            COUNT(DISTINCT sr.id)  AS soil_count,
            COUNT(DISTINCT pr.id)  AS plant_count,
            COUNT(DISTINCT wr.id)  AS water_count,

            MAX(sr.date_sampled)   AS last_soil_date,
            MAX(pr.date_sampled)   AS last_plant_date,
            MAX(wr.date_sampled)   AS last_water_date,

            -- Most recent activity across all test types
            GREATEST(
                COALESCE(MAX(sr.date_sampled), '1970-01-01'),
                COALESCE(MAX(pr.date_sampled), '1970-01-01'),
                COALESCE(MAX(wr.date_sampled), '1970-01-01')
            ) AS last_activity

        FROM client_records cr
        LEFT JOIN soil_records  sr ON CAST(sr.client_records_id AS UNSIGNED) = cr.id
        LEFT JOIN plant_records pr ON pr.client_records_id = cr.id
        LEFT JOIN water_records wr ON wr.client_records_id = cr.id
        WHERE cr.modx_user_id = ?
        GROUP BY cr.id
        ORDER BY last_activity DESC, cr.client ASC
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute([$userId]);
    $clients = $stmt->fetchAll();

    // Attach alert summary to each client
    foreach ($clients as &$client) {
        $client['alerts'] = getClientAlertSummary($pdo, $client['id']);
        $client['last_activity'] = $client['last_activity'] === '1970-01-01' ? null : $client['last_activity'];
    }
    unset($client);

    return $clients;
}

/**
 * Return ['critical' => int, 'watch' => int] for a client's most recent soil test.
 */
function getClientAlertSummary(PDO $pdo, int $clientId): array
{
    // Fetch the most recent soil test for this client
    $stmt = $pdo->prepare("
        SELECT * FROM soil_records
        WHERE CAST(client_records_id AS UNSIGNED) = ?
        ORDER BY date_sampled DESC, id DESC
        LIMIT 1
    ");
    $stmt->execute([$clientId]);
    $soilRow = $stmt->fetch();

    if (!$soilRow) {
        return ['critical' => 0, 'watch' => 0];
    }

    // Try to load matching spec row
    $spec = null;
    if (!empty($soilRow['soil_type'])) {
        $stmt2 = $pdo->prepare("
            SELECT * FROM soil_specifications
            WHERE soil_type = ?
            ORDER BY id ASC LIMIT 1
        ");
        $stmt2->execute([$soilRow['soil_type']]);
        $spec = $stmt2->fetch() ?: null;
    }

    return generateAlerts($soilRow, $spec)['summary'];
}

/**
 * Generate full alert list for a soil record vs its spec.
 *
 * @param array      $soilRow  Row from soil_records
 * @param array|null $spec     Row from soil_specifications (or null)
 * @return array  ['summary' => ['critical'=>int,'watch'=>int], 'items' => [...]]
 */
function generateAlerts(array $soilRow, ?array $spec): array
{
    $critical = 0;
    $watch    = 0;
    $items    = [];

    foreach (ALERT_NUTRIENTS as $col => $label) {
        $measured = isset($soilRow[$col]) && $soilRow[$col] !== ''
            ? (float) $soilRow[$col]
            : null;

        if ($measured === null) {
            continue;
        }

        // Determine range from spec or fallback
        $min = null;
        $max = null;

        if ($spec !== null && isset($spec[$col]) && $spec[$col] !== '') {
            // Spec value is the minimum target
            $min = (float) $spec[$col];
        } elseif (isset(FALLBACK_RANGES[$col])) {
            [$min, $max] = FALLBACK_RANGES[$col];
        }

        if ($min === null && $max === null) {
            continue; // no reference — skip
        }

        $severity = null;

        if ($min !== null && $measured < $min) {
            // Below minimum — how far below determines severity
            $ratio = $min > 0 ? ($measured / $min) : 0;
            $severity = $ratio < 0.5 ? 'critical' : 'watch';
        } elseif ($max !== null && $measured > $max) {
            $severity = 'watch'; // above max is always a watch
        }

        if ($severity === null) {
            continue;
        }

        if ($severity === 'critical') $critical++;
        else                          $watch++;

        $items[] = [
            'nutrient'  => $label,
            'col'       => $col,
            'measured'  => $measured,
            'min'       => $min,
            'max'       => $max,
            'severity'  => $severity,
        ];
    }

    // Sort: critical first
    usort($items, fn($a, $b) => $a['severity'] === 'critical' ? -1 : 1);

    return [
        'summary' => ['critical' => $critical, 'watch' => $watch],
        'items'   => $items,
    ];
}

/**
 * Format a date string for display ("12 Mar 2024"), returns '—' if null/empty.
 */
function fmtDate(?string $date): string
{
    if (!$date || $date === '1970-01-01') return '—';
    $ts = strtotime($date);
    return $ts ? date('j M Y', $ts) : '—';
}

/**
 * Return the Bootstrap badge class for an alert severity.
 */
function alertBadgeClass(int $critical, int $watch): string
{
    if ($critical > 0) return 'danger';
    if ($watch > 0)    return 'warning';
    return 'success';
}