soil-report-ai/dashboard/consultant/client.php

<?php
/**
 * dashboard/consultant/client.php
 *
 * Per-client detail view for the Consultant Dashboard.
 *
 * Tabs:
 *   Soil Tests  — history table + pH trend + nutrient trend charts
 *   Plant Tests — history table
 *   Water Tests — history table
 *   Alerts      — out-of-range nutrients from latest soil test
 *   Timeline    — chronological feed across all test types
 *
 * URL params:
 *   cid  int  client_records.id
 */

require_once __DIR__ . '/../../config/database.php';
require_once __DIR__ . '/../../lib/auth.php';
require_once __DIR__ . '/../../lib/consultant.php';

if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

requireConsultant();

$pdo      = getDBConnection();
$userId   = (int) getCurrentUserId();
$clientId = (int) ($_GET['cid'] ?? 0);

if (!$clientId) {
    header('Location: /dashboard/consultant/index.php');
    exit;
}

// ── Load client record ────────────────────────────────────────────────────────

$stmt = $pdo->prepare("SELECT * FROM client_records WHERE id = ? AND modx_user_id = ?");
$stmt->execute([$clientId, $userId]);
$client = $stmt->fetch();

if (!$client) {
    http_response_code(404);
    die('Client not found or access denied.');
}

// ── Load soil tests ───────────────────────────────────────────────────────────

$stmtSoil = $pdo->prepare("
    SELECT id, date_sampled, lab_no, sample_id, site_id, crop_type,
           soil_type, ph_cacl2, ph_h2o, NO3_N, p_morgan, k_morgan,
           ca_morgan, mg_morgan, ec, ocarbon, rand, status
    FROM soil_records
    WHERE CAST(client_records_id AS UNSIGNED) = ?
    ORDER BY date_sampled DESC, id DESC
");
$stmtSoil->execute([$clientId]);
$soilTests = $stmtSoil->fetchAll();

// ── Load plant tests ──────────────────────────────────────────────────────────

$stmtPlant = $pdo->prepare("
    SELECT id, date_sampled, lab_no, sample_id, site_id, crop_type,
           n, p, k, s, ca, mg, rand, status
    FROM plant_records
    WHERE client_records_id = ?
    ORDER BY date_sampled DESC, id DESC
");
$stmtPlant->execute([$clientId]);
$plantTests = $stmtPlant->fetchAll();

// ── Load water tests ──────────────────────────────────────────────────────────

$stmtWater = $pdo->prepare("
    SELECT id, date_sampled, lab_no, sample_id, site_id, crop_type,
           ph, cond_dsm, no3, p, k, ca, mg, na, rand, status
    FROM water_records
    WHERE client_records_id = ?
    ORDER BY date_sampled DESC, id DESC
");
$stmtWater->execute([$clientId]);
$waterTests = $stmtWater->fetchAll();

// ── Alerts from latest soil test ──────────────────────────────────────────────

$alertData = ['summary' => ['critical' => 0, 'watch' => 0], 'items' => []];
$latestSoil = null;

if (!empty($soilTests)) {
    // Fetch full row for latest test (we only selected subset above)
    $stmtFull = $pdo->prepare("SELECT * FROM soil_records WHERE id = ? LIMIT 1");
    $stmtFull->execute([$soilTests[0]['id']]);
    $latestSoil = $stmtFull->fetch();

    $spec = null;
    if (!empty($latestSoil['soil_type'])) {
        $stmtSpec = $pdo->prepare("SELECT * FROM soil_specifications WHERE soil_type = ? LIMIT 1");
        $stmtSpec->execute([$latestSoil['soil_type']]);
        $spec = $stmtSpec->fetch() ?: null;
    }
    $alertData = generateAlerts($latestSoil, $spec);
}

// ── Timeline: union across all test types ─────────────────────────────────────

$timeline = [];
foreach ($soilTests as $r) {
    $timeline[] = [
        'type'         => 'soil',
        'date'         => $r['date_sampled'],
        'lab_no'       => $r['lab_no'],
        'sample_id'    => $r['sample_id'],
        'site_id'      => $r['site_id'],
        'crop_type'    => $r['crop_type'],
        'id'           => $r['id'],
        'rand'         => $r['rand'],
    ];
}
foreach ($plantTests as $r) {
    $timeline[] = [
        'type'         => 'plant',
        'date'         => $r['date_sampled'],
        'lab_no'       => $r['lab_no'],
        'sample_id'    => $r['sample_id'],
        'site_id'      => $r['site_id'],
        'crop_type'    => $r['crop_type'],
        'id'           => $r['id'],
        'rand'         => $r['rand'],
    ];
}
foreach ($waterTests as $r) {
    $timeline[] = [
        'type'         => 'water',
        'date'         => $r['date_sampled'],
        'lab_no'       => $r['lab_no'],
        'sample_id'    => $r['sample_id'],
        'site_id'      => $r['site_id'],
        'crop_type'    => $r['crop_type'],
        'id'           => $r['id'],
        'rand'         => $r['rand'],
    ];
}
usort($timeline, fn($a, $b) => strcmp($b['date'] ?? '', $a['date'] ?? ''));

// ── Chart.js data (soil tests in chronological order for trend lines) ─────────

$chartSoil = array_reverse($soilTests); // oldest first
$chartLabels    = [];
$chartPhCaCl2   = [];
$chartPhH2O     = [];
$chartNO3N      = [];
$chartP         = [];
$chartK         = [];
$chartCa        = [];
$chartMg        = [];

foreach ($chartSoil as $r) {
    if (!$r['date_sampled']) continue;
    $chartLabels[]  = date('j M Y', strtotime($r['date_sampled']));
    $chartPhCaCl2[] = $r['ph_cacl2'] !== '' && $r['ph_cacl2'] !== null ? (float) $r['ph_cacl2'] : null;
    $chartPhH2O[]   = $r['ph_h2o']   !== '' && $r['ph_h2o']   !== null ? (float) $r['ph_h2o']   : null;
    $chartNO3N[]    = $r['NO3_N']    !== '' && $r['NO3_N']    !== null ? (float) $r['NO3_N']    : null;
    $chartP[]       = $r['p_morgan'] !== '' && $r['p_morgan'] !== null ? (float) $r['p_morgan'] : null;
    $chartK[]       = $r['k_morgan'] !== '' && $r['k_morgan'] !== null ? (float) $r['k_morgan'] : null;
    $chartCa[]      = $r['ca_morgan']!== '' && $r['ca_morgan']!== null ? (float) $r['ca_morgan']: null;
    $chartMg[]      = $r['mg_morgan']!== '' && $r['mg_morgan']!== null ? (float) $r['mg_morgan']: null;
}

// ── Page setup ────────────────────────────────────────────────────────────────

$clientName = htmlspecialchars($client['client']  ?? '—', ENT_QUOTES, 'UTF-8');
$company    = htmlspecialchars($client['company'] ?? '',  ENT_QUOTES, 'UTF-8');
$pageTitle  = $clientName . ' — Client Detail';
$siteName   = 'Crop Monitor';
$activeTab  = $_GET['tab'] ?? 'soil';

$totalAlerts = $alertData['summary']['critical'] + $alertData['summary']['watch'];

include __DIR__ . '/../../layouts/header.php';
include __DIR__ . '/../../layouts/navbar.php';
?>

<div id="layoutSidenav">
    <div id="layoutSidenav_nav">
        <?php include __DIR__ . '/../../layouts/consultant-sidebar.php'; ?>
    </div>
    <div id="layoutSidenav_content">
        <main>
            <div class="container-fluid px-4">

                <!-- ── Breadcrumb ─────────────────────────────────────────── -->
                <div class="d-flex align-items-center justify-content-between mt-4 mb-2 flex-wrap gap-2">
                    <div>
                        <ol class="breadcrumb mb-1">
                            <li class="breadcrumb-item"><a href="/dashboard/dashboard.php">Dashboard</a></li>
                            <li class="breadcrumb-item"><a href="/dashboard/consultant/index.php">Consultant</a></li>
                            <li class="breadcrumb-item active"><?= $clientName ?></li>
                        </ol>
                        <h1 class="h3 mb-0 fw-bold"><?= $clientName ?></h1>
                        <?php if ($company): ?>
                            <div class="text-muted"><?= $company ?></div>
                        <?php endif; ?>
                    </div>
                    <div class="d-flex gap-2 flex-wrap">
                        <?php if ($client['email']): ?>
                        <a href="mailto:<?= htmlspecialchars($client['email'], ENT_QUOTES, 'UTF-8') ?>"
                           class="btn btn-sm btn-outline-secondary">
                            <i class="fas fa-envelope me-1"></i>
                            <?= htmlspecialchars($client['email'], ENT_QUOTES, 'UTF-8') ?>
                        </a>
                        <?php endif; ?>
                        <?php if ($client['phone'] || $client['mobile']): ?>
                        <span class="btn btn-sm btn-outline-secondary disabled">
                            <i class="fas fa-phone me-1"></i>
                            <?= htmlspecialchars($client['phone'] ?: $client['mobile'], ENT_QUOTES, 'UTF-8') ?>
                        </span>
                        <?php endif; ?>
                    </div>
                </div>

                <!-- ── Client meta row ────────────────────────────────────── -->
                <?php if ($client['address'] || $client['state_postcode']): ?>
                <p class="text-muted small mb-3">
                    <i class="fas fa-map-marker-alt me-1"></i>
                    <?= htmlspecialchars(trim($client['address'] . ' ' . $client['state_postcode']), ENT_QUOTES, 'UTF-8') ?>
                </p>
                <?php endif; ?>

                <!-- ── Summary badges ─────────────────────────────────────── -->
                <div class="d-flex flex-wrap gap-2 mb-4">
                    <span class="badge rounded-pill bg-success bg-opacity-15 text-success border border-success border-opacity-25 px-3 py-2">
                        <i class="fas fa-globe-asia me-1"></i><?= count($soilTests) ?> Soil Tests
                    </span>
                    <span class="badge rounded-pill bg-info bg-opacity-15 text-info border border-info border-opacity-25 px-3 py-2">
                        <i class="fab fa-pagelines me-1"></i><?= count($plantTests) ?> Plant Tests
                    </span>
                    <span class="badge rounded-pill bg-primary bg-opacity-15 text-primary border border-primary border-opacity-25 px-3 py-2">
                        <i class="fas fa-tint me-1"></i><?= count($waterTests) ?> Water Tests
                    </span>
                    <?php if ($totalAlerts > 0): ?>
                    <span class="badge rounded-pill bg-<?= $alertData['summary']['critical'] > 0 ? 'danger' : 'warning' ?> px-3 py-2">
                        <i class="fas fa-exclamation-triangle me-1"></i>
                        <?= $alertData['summary']['critical'] ?> Critical &nbsp;·&nbsp; <?= $alertData['summary']['watch'] ?> Watch
                    </span>
                    <?php else: ?>
                    <span class="badge rounded-pill bg-success px-3 py-2">
                        <i class="fas fa-check me-1"></i>All nutrients in range
                    </span>
                    <?php endif; ?>
                </div>

                <!-- ── Tab nav ───────────────────────────────────────────── -->
                <ul class="nav nav-tabs mb-4" id="clientTabs" role="tablist">
                    <li class="nav-item">
                        <button class="nav-link <?= $activeTab === 'soil'     ? 'active' : '' ?>"
                                data-bs-toggle="tab" data-bs-target="#tab-soil" type="button">
                            <i class="fas fa-globe-asia me-1"></i>
                            Soil Tests
                            <span class="badge bg-secondary ms-1"><?= count($soilTests) ?></span>
                        </button>
                    </li>
                    <li class="nav-item">
                        <button class="nav-link <?= $activeTab === 'plant'    ? 'active' : '' ?>"
                                data-bs-toggle="tab" data-bs-target="#tab-plant" type="button">
                            <i class="fab fa-pagelines me-1"></i>
                            Plant Tests
                            <span class="badge bg-secondary ms-1"><?= count($plantTests) ?></span>
                        </button>
                    </li>
                    <li class="nav-item">
                        <button class="nav-link <?= $activeTab === 'water'    ? 'active' : '' ?>"
                                data-bs-toggle="tab" data-bs-target="#tab-water" type="button">
                            <i class="fas fa-tint me-1"></i>
                            Water Tests
                            <span class="badge bg-secondary ms-1"><?= count($waterTests) ?></span>
                        </button>
                    </li>
                    <li class="nav-item">
                        <button class="nav-link <?= $activeTab === 'alerts'   ? 'active' : '' ?>"
                                data-bs-toggle="tab" data-bs-target="#tab-alerts" type="button">
                            <i class="fas fa-exclamation-triangle me-1"></i>
                            Alerts
                            <?php if ($totalAlerts > 0): ?>
                            <span class="badge bg-<?= $alertData['summary']['critical'] > 0 ? 'danger' : 'warning' ?> ms-1">
                                <?= $totalAlerts ?>
                            </span>
                            <?php endif; ?>
                        </button>
                    </li>
                    <li class="nav-item">
                        <button class="nav-link <?= $activeTab === 'timeline' ? 'active' : '' ?>"
                                data-bs-toggle="tab" data-bs-target="#tab-timeline" type="button">
                            <i class="fas fa-history me-1"></i>Timeline
                        </button>
                    </li>
                </ul>

                <div class="tab-content" id="clientTabsContent">

                    <!-- ════════════════════════════════════════════════════
                         TAB: SOIL TESTS
                    ════════════════════════════════════════════════════ -->
                    <div class="tab-pane fade <?= $activeTab === 'soil' ? 'show active' : '' ?>"
                         id="tab-soil" role="tabpanel">

                        <?php if (empty($soilTests)): ?>
                            <p class="text-muted">No soil tests recorded for this client.</p>
                        <?php else: ?>

                        <!-- Soil test table -->
                        <div class="card shadow-sm border-0 mb-4">
                            <div class="card-header d-flex justify-content-between align-items-center">
                                <span class="fw-semibold">Soil Test History</span>
                                <a href="/dashboard/crop-analysis/soil-test-data/index.php"
                                   class="btn btn-sm btn-success">
                                    <i class="fas fa-plus me-1"></i>New Test
                                </a>
                            </div>
                            <div class="table-responsive">
                                <table class="table table-hover table-sm mb-0 align-middle">
                                    <thead class="table-light">
                                        <tr>
                                            <th>Date Sampled</th>
                                            <th>Lab No.</th>
                                            <th>Sample / Site</th>
                                            <th>Crop</th>
                                            <th class="text-center">pH CaCl₂</th>
                                            <th class="text-center">pH H₂O</th>
                                            <th class="text-center">NO₃-N</th>
                                            <th class="text-center">P</th>
                                            <th class="text-center">K</th>
                                            <th class="text-center">EC</th>
                                            <th></th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                    <?php foreach ($soilTests as $r):
                                        $link = '/dashboard/crop-analysis/soil-test-data/soil-analysis.php'
                                              . '?rid=' . (int)$r['id']
                                              . '&rand=' . urlencode($r['rand'])
                                              . '&cid=' . $clientId;
                                    ?>
                                        <tr>
                                            <td><?= fmtDate($r['date_sampled']) ?></td>
                                            <td class="text-muted small"><?= htmlspecialchars($r['lab_no'] ?? '—', ENT_QUOTES, 'UTF-8') ?></td>
                                            <td>
                                                <?= htmlspecialchars($r['sample_id'] ?? '', ENT_QUOTES, 'UTF-8') ?>
                                                <?php if ($r['site_id']): ?>
                                                    <br><span class="text-muted small"><?= htmlspecialchars($r['site_id'], ENT_QUOTES, 'UTF-8') ?></span>
                                                <?php endif; ?>
                                            </td>
                                            <td class="small"><?= htmlspecialchars($r['crop_type'] ?? '—', ENT_QUOTES, 'UTF-8') ?></td>
                                            <td class="text-center"><?= soilCell($r['ph_cacl2'], 5.5, 7.5) ?></td>
                                            <td class="text-center"><?= soilCell($r['ph_h2o'],   6.0, 8.0) ?></td>
                                            <td class="text-center"><?= numCell($r['NO3_N']) ?></td>
                                            <td class="text-center"><?= numCell($r['p_morgan']) ?></td>
                                            <td class="text-center"><?= numCell($r['k_morgan']) ?></td>
                                            <td class="text-center"><?= soilCell($r['ec'], null, 1.5) ?></td>
                                            <td>
                                                <a href="<?= $link ?>"
                                                   class="btn btn-xs btn-outline-success btn-sm">
                                                    View
                                                </a>
                                            </td>
                                        </tr>
                                    <?php endforeach; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>

                        <?php if (count($soilTests) >= 2): ?>
                        <!-- Charts -->
                        <div class="row g-4 mb-4">

                            <!-- pH trend -->
                            <div class="col-lg-6">
                                <div class="card shadow-sm border-0 h-100">
                                    <div class="card-header fw-semibold">
                                        <i class="fas fa-chart-line me-1 text-success"></i>pH Trend
                                    </div>
                                    <div class="card-body">
                                        <canvas id="chartPh" height="220"></canvas>
                                    </div>
                                </div>
                            </div>

                            <!-- Nutrient trend -->
                            <div class="col-lg-6">
                                <div class="card shadow-sm border-0 h-100">
                                    <div class="card-header fw-semibold">
                                        <i class="fas fa-chart-line me-1 text-info"></i>Key Nutrients (mg/kg)
                                    </div>
                                    <div class="card-body">
                                        <canvas id="chartNutrients" height="220"></canvas>
                                    </div>
                                </div>
                            </div>

                        </div>
                        <?php endif; ?>

                        <?php endif; ?>
                    </div>

                    <!-- ════════════════════════════════════════════════════
                         TAB: PLANT TESTS
                    ════════════════════════════════════════════════════ -->
                    <div class="tab-pane fade <?= $activeTab === 'plant' ? 'show active' : '' ?>"
                         id="tab-plant" role="tabpanel">

                        <?php if (empty($plantTests)): ?>
                            <p class="text-muted">No plant tests recorded for this client.</p>
                        <?php else: ?>
                        <div class="card shadow-sm border-0">
                            <div class="card-header d-flex justify-content-between align-items-center">
                                <span class="fw-semibold">Plant Test History</span>
                                <a href="/dashboard/crop-analysis/plant-test-data/index.php"
                                   class="btn btn-sm btn-info text-white">
                                    <i class="fas fa-plus me-1"></i>New Test
                                </a>
                            </div>
                            <div class="table-responsive">
                                <table class="table table-hover table-sm mb-0 align-middle">
                                    <thead class="table-light">
                                        <tr>
                                            <th>Date Sampled</th>
                                            <th>Lab No.</th>
                                            <th>Sample / Site</th>
                                            <th>Crop</th>
                                            <th class="text-center">N%</th>
                                            <th class="text-center">P%</th>
                                            <th class="text-center">K%</th>
                                            <th class="text-center">S%</th>
                                            <th class="text-center">Ca%</th>
                                            <th class="text-center">Mg%</th>
                                            <th></th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                    <?php foreach ($plantTests as $r):
                                        $link = '/dashboard/crop-analysis/plant-test-data/plant-analysis.php'
                                              . '?rid=' . (int)$r['id']
                                              . '&rand=' . urlencode($r['rand'])
                                              . '&cid=' . $clientId;
                                    ?>
                                        <tr>
                                            <td><?= fmtDate($r['date_sampled']) ?></td>
                                            <td class="text-muted small"><?= htmlspecialchars($r['lab_no'] ?? '—', ENT_QUOTES, 'UTF-8') ?></td>
                                            <td>
                                                <?= htmlspecialchars($r['sample_id'] ?? '', ENT_QUOTES, 'UTF-8') ?>
                                                <?php if ($r['site_id']): ?>
                                                    <br><span class="text-muted small"><?= htmlspecialchars($r['site_id'], ENT_QUOTES, 'UTF-8') ?></span>
                                                <?php endif; ?>
                                            </td>
                                            <td class="small"><?= htmlspecialchars($r['crop_type'] ?? '—', ENT_QUOTES, 'UTF-8') ?></td>
                                            <td class="text-center"><?= numCell($r['n']) ?></td>
                                            <td class="text-center"><?= numCell($r['p']) ?></td>
                                            <td class="text-center"><?= numCell($r['k']) ?></td>
                                            <td class="text-center"><?= numCell($r['s']) ?></td>
                                            <td class="text-center"><?= numCell($r['ca']) ?></td>
                                            <td class="text-center"><?= numCell($r['mg']) ?></td>
                                            <td>
                                                <a href="<?= $link ?>"
                                                   class="btn btn-sm btn-outline-info">View</a>
                                            </td>
                                        </tr>
                                    <?php endforeach; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                        <?php endif; ?>
                    </div>

                    <!-- ════════════════════════════════════════════════════
                         TAB: WATER TESTS
                    ════════════════════════════════════════════════════ -->
                    <div class="tab-pane fade <?= $activeTab === 'water' ? 'show active' : '' ?>"
                         id="tab-water" role="tabpanel">

                        <?php if (empty($waterTests)): ?>
                            <p class="text-muted">No water tests recorded for this client.</p>
                        <?php else: ?>
                        <div class="card shadow-sm border-0">
                            <div class="card-header d-flex justify-content-between align-items-center">
                                <span class="fw-semibold">Water Test History</span>
                                <a href="/dashboard/crop-analysis/water-test-data/index.php"
                                   class="btn btn-sm btn-primary">
                                    <i class="fas fa-plus me-1"></i>New Test
                                </a>
                            </div>
                            <div class="table-responsive">
                                <table class="table table-hover table-sm mb-0 align-middle">
                                    <thead class="table-light">
                                        <tr>
                                            <th>Date Sampled</th>
                                            <th>Lab No.</th>
                                            <th>Sample / Site</th>
                                            <th>Crop</th>
                                            <th class="text-center">pH</th>
                                            <th class="text-center">EC (dS/m)</th>
                                            <th class="text-center">NO₃</th>
                                            <th class="text-center">P</th>
                                            <th class="text-center">K</th>
                                            <th class="text-center">Na</th>
                                            <th></th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                    <?php foreach ($waterTests as $r):
                                        $link = '/dashboard/crop-analysis/water-test-data/water-report.php'
                                              . '?rid=' . (int)$r['id']
                                              . '&rand=' . urlencode($r['rand'])
                                              . '&cid=' . $clientId;
                                    ?>
                                        <tr>
                                            <td><?= fmtDate($r['date_sampled']) ?></td>
                                            <td class="text-muted small"><?= htmlspecialchars($r['lab_no'] ?? '—', ENT_QUOTES, 'UTF-8') ?></td>
                                            <td>
                                                <?= htmlspecialchars($r['sample_id'] ?? '', ENT_QUOTES, 'UTF-8') ?>
                                                <?php if ($r['site_id']): ?>
                                                    <br><span class="text-muted small"><?= htmlspecialchars($r['site_id'], ENT_QUOTES, 'UTF-8') ?></span>
                                                <?php endif; ?>
                                            </td>
                                            <td class="small"><?= htmlspecialchars($r['crop_type'] ?? '—', ENT_QUOTES, 'UTF-8') ?></td>
                                            <td class="text-center"><?= soilCell($r['ph'],       6.0, 8.0) ?></td>
                                            <td class="text-center"><?= soilCell($r['cond_dsm'], null, 1.5) ?></td>
                                            <td class="text-center"><?= numCell($r['no3']) ?></td>
                                            <td class="text-center"><?= numCell($r['p']) ?></td>
                                            <td class="text-center"><?= numCell($r['k']) ?></td>
                                            <td class="text-center"><?= numCell($r['na']) ?></td>
                                            <td>
                                                <a href="<?= $link ?>"
                                                   class="btn btn-sm btn-outline-primary">View</a>
                                            </td>
                                        </tr>
                                    <?php endforeach; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                        <?php endif; ?>
                    </div>

                    <!-- ════════════════════════════════════════════════════
                         TAB: ALERTS
                    ════════════════════════════════════════════════════ -->
                    <div class="tab-pane fade <?= $activeTab === 'alerts' ? 'show active' : '' ?>"
                         id="tab-alerts" role="tabpanel">

                        <?php if (empty($soilTests)): ?>
                            <p class="text-muted">No soil tests available to generate alerts.</p>
                        <?php elseif (empty($alertData['items'])): ?>
                            <div class="alert alert-success">
                                <i class="fas fa-check-circle me-2"></i>
                                All measured nutrients are within acceptable ranges based on the latest soil test
                                (<?= fmtDate($soilTests[0]['date_sampled']) ?>).
                            </div>
                        <?php else: ?>
                            <p class="text-muted small mb-3">
                                Based on soil test
                                <strong><?= htmlspecialchars($soilTests[0]['lab_no'] ?? '#' . $soilTests[0]['id'], ENT_QUOTES, 'UTF-8') ?></strong>
                                sampled <?= fmtDate($soilTests[0]['date_sampled']) ?>.
                                <?php if ($latestSoil && $latestSoil['soil_type']): ?>
                                    Soil type: <strong><?= htmlspecialchars($latestSoil['soil_type'], ENT_QUOTES, 'UTF-8') ?></strong>.
                                <?php endif; ?>
                            </p>

                            <div class="row g-3">
                            <?php foreach ($alertData['items'] as $alert):
                                $isCritical = $alert['severity'] === 'critical';
                                $colour     = $isCritical ? 'danger' : 'warning';
                                $icon       = $isCritical ? 'exclamation-circle' : 'exclamation-triangle';

                                // How far from target (%)
                                $pct = null;
                                if ($alert['min'] !== null && $alert['min'] > 0) {
                                    $pct = round(($alert['measured'] / $alert['min']) * 100);
                                }
                            ?>
                            <div class="col-md-6 col-xl-4">
                                <div class="card border-<?= $colour ?> border-opacity-50 h-100">
                                    <div class="card-body">
                                        <div class="d-flex justify-content-between align-items-center mb-2">
                                            <span class="fw-semibold">
                                                <i class="fas fa-<?= $icon ?> text-<?= $colour ?> me-1"></i>
                                                <?= htmlspecialchars($alert['nutrient'], ENT_QUOTES, 'UTF-8') ?>
                                            </span>
                                            <span class="badge bg-<?= $colour ?>">
                                                <?= strtoupper($alert['severity']) ?>
                                            </span>
                                        </div>

                                        <div class="d-flex justify-content-between text-muted small mb-1">
                                            <span>Measured</span>
                                            <span>Target min</span>
                                        </div>
                                        <div class="d-flex justify-content-between fw-bold mb-2">
                                            <span class="text-<?= $colour ?>"><?= $alert['measured'] ?></span>
                                            <span><?= $alert['min'] ?? '—' ?></span>
                                        </div>

                                        <?php if ($pct !== null): ?>
                                        <div class="progress" style="height:6px">
                                            <div class="progress-bar bg-<?= $colour ?>"
                                                 style="width:<?= min($pct, 100) ?>%"
                                                 title="<?= $pct ?>% of target">
                                            </div>
                                        </div>
                                        <div class="text-muted" style="font-size:.72rem; margin-top:3px">
                                            <?= $pct ?>% of target minimum
                                        </div>
                                        <?php endif; ?>

                                    </div>
                                </div>
                            </div>
                            <?php endforeach; ?>
                            </div>
                        <?php endif; ?>
                    </div>

                    <!-- ════════════════════════════════════════════════════
                         TAB: TIMELINE
                    ════════════════════════════════════════════════════ -->
                    <div class="tab-pane fade <?= $activeTab === 'timeline' ? 'show active' : '' ?>"
                         id="tab-timeline" role="tabpanel">

                        <?php if (empty($timeline)): ?>
                            <p class="text-muted">No tests recorded yet.</p>
                        <?php else: ?>

                        <?php
                        $typeConfig = [
                            'soil'  => ['label' => 'Soil',  'icon' => 'globe-asia',  'colour' => 'success'],
                            'plant' => ['label' => 'Plant', 'icon' => 'pagelines',   'colour' => 'info',    'fa' => 'fab'],
                            'water' => ['label' => 'Water', 'icon' => 'tint',        'colour' => 'primary'],
                        ];
                        $prevDate = null;
                        foreach ($timeline as $entry):
                            $cfg      = $typeConfig[$entry['type']];
                            $entryDate = $entry['date'] ? date('F Y', strtotime($entry['date'])) : 'Unknown';
                            $fa       = $cfg['fa'] ?? 'fas';

                            // Month/year separator
                            if ($entryDate !== $prevDate):
                                $prevDate = $entryDate;
                        ?>
                        <div class="text-muted small fw-semibold text-uppercase mb-2 mt-3 border-bottom pb-1">
                            <?= htmlspecialchars($entryDate, ENT_QUOTES, 'UTF-8') ?>
                        </div>
                        <?php endif; ?>

                        <div class="d-flex gap-3 mb-3 align-items-start">
                            <div class="rounded-circle bg-<?= $cfg['colour'] ?> bg-opacity-15 p-2 flex-shrink-0 mt-1"
                                 style="width:36px;height:36px;display:flex;align-items:center;justify-content:center">
                                <<?= $fa ?> class="fa-<?= $cfg['icon'] ?> text-<?= $cfg['colour'] ?> fa-sm"></i>
                            </div>
                            <div class="flex-fill">
                                <div class="d-flex justify-content-between align-items-start">
                                    <div>
                                        <span class="badge bg-<?= $cfg['colour'] ?> bg-opacity-75 me-1"><?= $cfg['label'] ?></span>
                                        <strong><?= htmlspecialchars($entry['sample_id'] ?: ($entry['site_id'] ?: ('Test #' . $entry['id'])), ENT_QUOTES, 'UTF-8') ?></strong>
                                        <?php if ($entry['site_id'] && $entry['site_id'] !== $entry['sample_id']): ?>
                                            <span class="text-muted small ms-1">— <?= htmlspecialchars($entry['site_id'], ENT_QUOTES, 'UTF-8') ?></span>
                                        <?php endif; ?>
                                    </div>
                                    <span class="text-muted small flex-shrink-0 ms-2"><?= fmtDate($entry['date']) ?></span>
                                </div>
                                <div class="text-muted small mt-1">
                                    <?php if ($entry['lab_no']): ?>
                                        Lab: <?= htmlspecialchars($entry['lab_no'], ENT_QUOTES, 'UTF-8') ?>
                                    <?php endif; ?>
                                    <?php if ($entry['crop_type']): ?>
                                        · <?= htmlspecialchars($entry['crop_type'], ENT_QUOTES, 'UTF-8') ?>
                                    <?php endif; ?>
                                </div>
                            </div>
                        </div>

                        <?php endforeach; ?>
                        <?php endif; ?>
                    </div>

                </div><!-- /.tab-content -->
            </div>
        </main>
    </div>
</div>

<?php if (count($soilTests) >= 2): ?>
<!-- Chart.js data -->
<script>
const chartLabels   = <?= json_encode($chartLabels) ?>;
const chartPhCaCl2  = <?= json_encode($chartPhCaCl2) ?>;
const chartPhH2O    = <?= json_encode($chartPhH2O) ?>;
const chartNO3N     = <?= json_encode($chartNO3N) ?>;
const chartP        = <?= json_encode($chartP) ?>;
const chartK        = <?= json_encode($chartK) ?>;
const chartCa       = <?= json_encode($chartCa) ?>;
const chartMg       = <?= json_encode($chartMg) ?>;

// ── pH Chart ─────────────────────────────────────────────────────────────────
const ctxPh = document.getElementById('chartPh');
if (ctxPh) {
    new Chart(ctxPh, {
        type: 'line',
        data: {
            labels: chartLabels,
            datasets: [
                {
                    label: 'Min (5.5)',
                    data: Array(chartLabels.length).fill(5.5),
                    borderColor: 'rgba(25,135,84,0.3)',
                    borderDash: [5, 5],
                    borderWidth: 1,
                    pointRadius: 0,
                    fill: '+1',
                    backgroundColor: 'rgba(25,135,84,0.06)',
                    spanGaps: true,
                },
                {
                    label: 'Max (7.5)',
                    data: Array(chartLabels.length).fill(7.5),
                    borderColor: 'rgba(25,135,84,0.3)',
                    borderDash: [5, 5],
                    borderWidth: 1,
                    pointRadius: 0,
                    fill: false,
                    spanGaps: true,
                },
                {
                    label: 'pH CaCl₂',
                    data: chartPhCaCl2,
                    borderColor: '#198754',
                    backgroundColor: 'rgba(25,135,84,0.08)',
                    tension: 0.3,
                    pointRadius: 4,
                    fill: false,
                    spanGaps: true,
                },
                {
                    label: 'pH H₂O',
                    data: chartPhH2O,
                    borderColor: '#0dcaf0',
                    backgroundColor: 'rgba(13,202,240,0.08)',
                    tension: 0.3,
                    pointRadius: 4,
                    fill: false,
                    spanGaps: true,
                },
            ],
        },
        options: {
            responsive: true,
            interaction: { mode: 'index', intersect: false },
            plugins: {
                legend: { position: 'top' },
                tooltip: {
                    callbacks: {
                        afterBody: () => 'Ideal range: 5.5 – 7.5',
                    },
                },
            },
            scales: {
                y: {
                    title: { display: true, text: 'pH' },
                    min: 4,
                    max: 9,
                },
            },
        },
    });
}

// ── Nutrients Chart ───────────────────────────────────────────────────────────
const ctxNut = document.getElementById('chartNutrients');
if (ctxNut) {
    new Chart(ctxNut, {
        type: 'line',
        data: {
            labels: chartLabels,
            datasets: [
                { label: 'NO₃-N',  data: chartNO3N, borderColor: '#0d6efd', tension: 0.3, pointRadius: 4, fill: false, spanGaps: true },
                { label: 'P',      data: chartP,    borderColor: '#fd7e14', tension: 0.3, pointRadius: 4, fill: false, spanGaps: true },
                { label: 'K',      data: chartK,    borderColor: '#6f42c1', tension: 0.3, pointRadius: 4, fill: false, spanGaps: true },
                { label: 'Ca',     data: chartCa,   borderColor: '#d63384', tension: 0.3, pointRadius: 4, fill: false, spanGaps: true },
                { label: 'Mg',     data: chartMg,   borderColor: '#20c997', tension: 0.3, pointRadius: 4, fill: false, spanGaps: true },
            ],
        },
        options: {
            responsive: true,
            interaction: { mode: 'index', intersect: false },
            plugins: { legend: { position: 'top' } },
            scales: {
                y: { title: { display: true, text: 'mg/kg' }, beginAtZero: true },
            },
        },
    });
}
</script>
<?php endif; ?>

<?php include __DIR__ . '/../../layouts/footer.php'; ?>

<?php
// ── Helper functions (local to this page) ─────────────────────────────────────

/**
 * Render a colour-coded table cell for a value with optional min/max range.
 * Green = in range, yellow = watch (near/above max), red = below min.
 */
function soilCell(?string $raw, ?float $min, ?float $max): string
{
    if ($raw === null || $raw === '') return '<span class="text-muted">—</span>';
    $v = (float) $raw;

    $class = '';
    if ($min !== null && $v < $min) {
        $class = $v < ($min * 0.5) ? 'text-danger fw-bold' : 'text-warning fw-bold';
    } elseif ($max !== null && $v > $max) {
        $class = 'text-warning fw-bold';
    } else {
        $class = 'text-success';
    }

    return '<span class="' . $class . '">' . htmlspecialchars($raw, ENT_QUOTES, 'UTF-8') . '</span>';
}

/**
 * Render a plain numeric cell — dash if empty.
 */
function numCell(?string $raw): string
{
    if ($raw === null || $raw === '') return '<span class="text-muted">—</span>';
    return htmlspecialchars($raw, ENT_QUOTES, 'UTF-8');
}
?>