soil-report-ai/dashboard/crop-analysis/soil-test-data/soil-recommendations.php

<?php
/**
 * dashboard/crop-analysis/soil-test-data/soil-recommendations.php
 *
 * Soil Specifications / Recommendations page.
 * Displays and allows inline editing of soil_specifications reference ranges
 * for the current user.
 */

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

requireLogin();

// URL params retained for back-link context
$record_id = (int)($_GET['rid']  ?? 0);
$rand_id   = $_GET['rand'] ?? '';
$client_id = (int)($_GET['cid']  ?? 0);

// Columns not shown in the editable table
$hiddenCols = ['id', 'modx_user_id', 'crop', 'tec', 'paramag', 'texture', 'gravel', 'colour'];

try {
    $pdo    = getDBConnection();
    $userId = getCurrentUserId();

    $stmt = $pdo->prepare('SELECT * FROM soil_specifications WHERE modx_user_id = ?');
    $stmt->execute([$userId]);
    $specs = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $allColumns     = $specs ? array_keys($specs[0]) : [];
    $visibleColumns = array_values(array_diff($allColumns, $hiddenCols));

} catch (PDOException $e) {
    error_log('Database error in soil-recommendations.php: ' . $e->getMessage());
    die('Database error occurred');
}

$pageTitle = 'Soil Recommendations';
$siteName  = 'Crop Monitor';
$csrfToken = generateCsrfToken();

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

<div class="container-fluid px-4" id="content">

    <div class="d-flex align-items-center justify-content-between mt-4 mb-3">
        <h1 class="h3 mb-0">Soil Recommendations</h1>
        <?php if ($record_id && $rand_id): ?>
        <a href="/dashboard/crop-analysis/soil-test-data/soil-analysis.php?rid=<?= $record_id ?>&rand=<?= urlencode($rand_id) ?>&cid=<?= $client_id ?>"
           class="btn btn-outline-secondary btn-sm">
            &larr; Back to Analysis
        </a>
        <?php endif; ?>
    </div>

    <p class="text-muted mb-3">
        Variables used in Soil Analysis recommendation programs.
        Click any value in the table to edit it — changes save automatically.
    </p>

    <?php if (empty($specs)): ?>
    <div class="alert alert-info">No soil specification records found for your account.</div>
    <?php else: ?>

    <div class="card mb-4">
        <div class="card-header d-flex justify-content-between align-items-center">
            <span>Specification Ranges</span>
            <button type="button" class="btn btn-primary btn-sm"
                    data-bs-toggle="modal" data-bs-target="#addProductModal">
                + Add Soil Recommendation
            </button>
        </div>
        <div class="card-body p-0">
            <div class="table-responsive">
                <table class="table table-sm table-striped table-hover table-bordered mb-0">
                    <thead class="table-dark">
                        <tr>
                            <?php foreach ($visibleColumns as $col): ?>
                            <th class="text-center text-capitalize text-nowrap px-2">
                                <?= htmlspecialchars(str_replace('_', ' ', $col), ENT_QUOTES, 'UTF-8') ?>
                            </th>
                            <?php endforeach; ?>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($specs as $spec): ?>
                        <tr>
                            <?php foreach ($visibleColumns as $col):
                                $raw = $spec[$col] ?? '';
                                if ($raw === '' || $raw === 'N/A') {
                                    $display = '0.0';
                                } elseif (is_numeric($raw)) {
                                    $display = number_format((float)$raw, 2, '.', '');
                                } else {
                                    $display = $raw;
                                }
                            ?>
                            <td class="text-center spec-cell px-2"
                                contenteditable="true"
                                data-id="<?= (int)$spec['id'] ?>"
                                data-col="<?= htmlspecialchars($col, ENT_QUOTES, 'UTF-8') ?>">
                                <?= htmlspecialchars($display, ENT_QUOTES, 'UTF-8') ?>
                            </td>
                            <?php endforeach; ?>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <div id="save-status" class="text-muted small mb-3" style="min-height:1.25rem;"></div>

    <?php endif; ?>

</div><!-- /.container-fluid -->


<!-- ── Add Product Modal ─────────────────────────────────────────────────── -->
<div class="modal fade" id="addProductModal" tabindex="-1"
     aria-labelledby="addProductModalLabel" aria-hidden="true">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title" id="addProductModalLabel">Add New Product</h5>
                <button type="button" class="btn-close"
                        data-bs-dismiss="modal" aria-label="Close"></button>
            </div>
            <form method="post" action="/controllers/newProductSubmit.php">
                <input type="hidden" name="csrf_token"
                       value="<?= htmlspecialchars($csrfToken, ENT_QUOTES, 'UTF-8') ?>">
                <div class="modal-body">
                    <div class="row g-2">
                        <div class="col-md-8">
                            <label class="form-label" for="name">Product Name</label>
                            <input type="text" class="form-control" name="name"
                                   id="name" placeholder="Product Name" required>
                        </div>
                        <div class="col-md-4">
                            <label class="form-label" for="chemical">Chemical Symbol</label>
                            <input type="text" class="form-control" name="chemical"
                                   id="chemical" placeholder="e.g. CaSO4">
                        </div>
                    </div>

                    <hr class="my-3">
                    <p class="text-muted small mb-2">Nutrient composition (%)</p>

                    <div class="row g-2">
                        <?php
                        $nutrients = [
                            'N'  => 'Nitrogen',
                            'P'  => 'Phosphorus',
                            'K'  => 'Potassium',
                            'Na' => 'Sodium',
                            'Ca' => 'Calcium',
                            'Mg' => 'Magnesium',
                            'B'  => 'Boron',
                            'Zn' => 'Zinc',
                            'Cu' => 'Copper',
                            'Mn' => 'Manganese',
                            'Fe' => 'Iron',
                            'Co' => 'Cobalt',
                            'Mo' => 'Molybdenum',
                        ];
                        foreach ($nutrients as $key => $label):
                        ?>
                        <div class="col-6 col-md-4">
                            <label class="form-label small" for="field_<?= $key ?>">
                                <?= htmlspecialchars($label, ENT_QUOTES, 'UTF-8') ?> — <?= htmlspecialchars($key, ENT_QUOTES, 'UTF-8') ?>
                            </label>
                            <input type="number" step="any" min="0"
                                   class="form-control form-control-sm"
                                   name="<?= htmlspecialchars($key, ENT_QUOTES, 'UTF-8') ?>"
                                   id="field_<?= $key ?>"
                                   placeholder="0">
                        </div>
                        <?php endforeach; ?>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary"
                            data-bs-dismiss="modal">Close</button>
                    <button type="submit" class="btn btn-primary">Save Product</button>
                </div>
            </form>
        </div>
    </div>
</div>


<script>
(function () {
    const CSRF   = <?= json_encode($csrfToken) ?>;
    const status = document.getElementById('save-status');
    let   timer  = null;
    let   active = null;

    function setStatus(msg, cls) {
        status.textContent = msg;
        status.className   = 'text-' + cls + ' small mb-3';
    }

    document.querySelectorAll('.spec-cell').forEach(function (cell) {
        cell.addEventListener('focus', function () {
            this.style.background = '#d1f0d1';
        });

        cell.addEventListener('blur', function () {
            this.style.background = '';
            const el  = this;
            const id  = el.dataset.id;
            const col = el.dataset.col;
            const val = el.textContent.trim();

            clearTimeout(timer);
            timer = setTimeout(function () {
                active = el;
                setStatus('Saving…', 'secondary');

                fetch('/controllers/updateSoilSpecification.php', {
                    method: 'POST',
                    headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
                    body: new URLSearchParams({
                        csrf_token: CSRF,
                        spec_id:    id,
                        column:     col,
                        value:      val,
                    }),
                })
                .then(function (r) { return r.json(); })
                .then(function (data) {
                    if (data.success) {
                        var d = new Date();
                        setStatus('Saved — ' + d.toLocaleTimeString(), 'success');
                    } else {
                        setStatus('Error: ' + (data.error || 'save failed'), 'danger');
                    }
                })
                .catch(function () {
                    setStatus('Network error — change not saved', 'danger');
                });
            }, 600);
        });
    });
})();
</script>

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