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

<?php
/**
 * dashboard/crop-analysis/soil-test-data/soil-report.php
 *
 * Soil Analysis Report — editable sections with auto-save and Ollama AI interpretation.
 */

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

requireLogin();

$client_id = (int)  ($_GET['cid']  ?? 0);
$record_id = (int)  ($_GET['rid']  ?? 0);
$rand_id   = trim(  $_GET['rand']  ?? '');

if (!$record_id || $rand_id === '') {
    http_response_code(400);
    die('Invalid request parameters');
}

$acHa = 2.4710559990832394739; // kg/Ac → kg/ha conversion

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

    // Load soil record — verify ownership via rand token
    $stmt = $pdo->prepare('SELECT * FROM soil_records WHERE id = ? AND rand = ?');
    $stmt->execute([$record_id, $rand_id]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$row) {
        http_response_code(404);
        die('Soil record not found');
    }

    // Load specification ranges for this soil type
    $spec = [];
    if (!empty($row['soil_type'])) {
        $stmtSpec = $pdo->prepare('SELECT * FROM soil_specifications WHERE soil_type = ? LIMIT 1');
        $stmtSpec->execute([$row['soil_type']]);
        $spec = $stmtSpec->fetch(PDO::FETCH_ASSOC) ?: [];
    }

    // Load saved report comments (JSON blob in reports table)
    $savedComments = [];
    $stmtRpt = $pdo->prepare(
        'SELECT comment FROM reports WHERE record_id = ? AND modx_user_id = ? ORDER BY id DESC LIMIT 1'
    );
    $stmtRpt->execute([$record_id, $userId]);
    $savedRow = $stmtRpt->fetchColumn();
    if ($savedRow) {
        $decoded = json_decode($savedRow, true);
        if (is_array($decoded)) {
            $savedComments = $decoded;
        }
    }

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

// ── Escaped display vars ────────────────────────────────────────────────────
$client     = htmlspecialchars($row['client_name']   ?? '', ENT_QUOTES, 'UTF-8');
$address    = htmlspecialchars($row['site_address']   ?? '', ENT_QUOTES, 'UTF-8');
$state      = htmlspecialchars($row['state_postcode'] ?? '', ENT_QUOTES, 'UTF-8');
$email      = htmlspecialchars($row['email']          ?? '', ENT_QUOTES, 'UTF-8');
$labNo      = htmlspecialchars($row['lab_no']         ?? '', ENT_QUOTES, 'UTF-8');
$sampleDate = htmlspecialchars($row['date_sampled']   ?? '', ENT_QUOTES, 'UTF-8');
$sample     = htmlspecialchars($row['site_id']        ?? '', ENT_QUOTES, 'UTF-8');
$cropName   = htmlspecialchars($row['sample_id']      ?? '', ENT_QUOTES, 'UTF-8');
$soilType   = htmlspecialchars($row['soil_type']      ?? '', ENT_QUOTES, 'UTF-8');
$batchNo    = htmlspecialchars($row['batch_no']       ?? '', ENT_QUOTES, 'UTF-8');

$today     = date('jS F Y');
$pageTitle = 'Soil Report' . ($client !== '' ? ' — ' . $client : '');
$siteName  = 'Crop Monitor';

// ── Five-year plan element definitions ─────────────────────────────────────
// [label, soil_records column, min column (record), max column (record), unit]
// When min/max are empty the spec column of the same name provides the target.
$planElements = [
    ['Calcium',   'BS_ca_ppm', 'ca_ppm_min', 'ca_ppm_max', 'kg/ha'],
    ['Magnesium', 'BS_mg_ppm', 'mg_ppm_min', 'mg_ppm_max', 'kg/ha'],
    ['Potassium', 'BS_k_ppm',  'k_ppm_min',  'k_ppm_max',  'kg/ha'],
    ['Sodium',    'BS_na_ppm', 'na_ppm_min',  'na_ppm_max', 'kg/ha'],
    ['Phosphate', 'p_colwell', '',           '',            'kg/ha'],
    ['Sulfur',    's_morgan',  '',           '',            'kg/ha'],
    ['Boron',     'b_cacl2',   '',           '',            'kg/ha'],
    ['Manganese', 'mn_dtpa',   '',           '',            'kg/ha'],
    ['Zinc',      'zn_dtpa',   '',           '',            'kg/ha'],
    ['Copper',    'cu_dtpa',   '',           '',            'kg/ha'],
];

/**
 * Calculate total kg/ha deficit for one element, given soil row + spec row.
 * Returns 0 if already at or above target.
 */
function calcDeficit(array $row, array $spec, string $col, string $minCol, string $maxCol): float
{
    global $acHa;
    $value  = (float)($row[$col] ?? 0);
    if ($maxCol !== '') {
        $maxVal = (float)($row[$maxCol] ?? 0);
    } else {
        $maxVal = (float)($spec[$col]   ?? 0);
    }
    $deficit = ($maxVal - $value) * $acHa;
    return $deficit > 0 ? round($deficit, 2) : 0.0;
}

include __DIR__ . '/../../../layouts/header.php';
?>
<link rel="stylesheet" href="/client-assets/home/css/graphPrint.css" media="print">
<style>
    * { margin: 0; padding: 0; box-sizing: border-box; }
        body { background: #fff; }
    @media print {
        @page {
            size: A4 portrait;
        }
        @page :left {
            margin-left: 0.5cm;
        }
        @page :right {
            margin-right: 0.5cm;
        }
        @page :top {
            margin-top: 0cm;
        }
        @page :bottom {
            margin-bottom: 0cm;
        }
        body {
            min-width: 992px !important;
        }
        .container {
            min-width: 992px !important;
        }
        .report-textarea {
            border:none;
        }
    }
    .element-required-module .card-group .col {
        padding: 0 10px;
    }
</style>

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

    <!-- ── Page heading ──────────────────────────────────────────────────── -->
    <div class="d-flex align-items-center justify-content-between mt-4 mb-3">
        <h1 class="h3 mb-0">Soil Analysis Report</h1>
        <div class="d-flex gap-2 d-print-none">
            <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; Analysis
            </a>
            <a href="/dashboard/crop-analysis/soil-test-data/soil-report-pdf.php?rid=<?= $record_id ?>&rand=<?= urlencode($rand_id) ?>"
               class="btn btn-outline-success btn-sm" target="_blank">
                <i class="fas fa-file-pdf me-1"></i>PDF — Report
            </a>
            <a href="/pdf-files/headlessChrome_pdf.php?type=soil&rid=<?= $record_id ?>&rand=<?= urlencode($rand_id) ?>&cid=<?= $client_id ?>"
               class="btn btn-success btn-sm">
                <i class="fas fa-file-pdf me-1"></i>PDF — Analysis &amp; Report
            </a>
            <button type="button" class="btn btn-primary btn-sm" id="btn-generate-all">
                <i class="fas fa-robot me-1"></i>Interpret All with AI
            </button>
        </div>
    </div>

    <!-- ── Client / Sample info card ─────────────────────────────────────── -->
    <div class="card mb-4">
        <div class="card-body py-2">
            <div class="row g-2">
                <div class="col-md-2">
                    <img class="img-fluid" src="/client-assets/images/crop-monitor.png" alt="Crop Monitor"
                         style="max-height:60px;">
                </div>
                <div class="col-md-10">
                    <div class="row row-cols-2 row-cols-md-3 g-1 small">
                        <div><strong>Client:</strong> <?= $client ?></div>
                        <div><strong>Sample ID:</strong> <?= $sample ?></div>
                        <div><strong>Date Sampled:</strong> <?= $sampleDate ?></div>
                        <div><strong>Address:</strong> <?= $address ?>, <?= $state ?></div>
                        <div><strong>Crop:</strong> <?= $cropName ?></div>
                        <div><strong>Lab No:</strong> <?= $labNo ?></div>
                        <div><strong>Soil Type:</strong> <?= $soilType ?></div>
                        <div><strong>Batch:</strong> <?= $batchNo ?></div>
                        <div><strong>Report Date:</strong> <?= htmlspecialchars($today, ENT_QUOTES, 'UTF-8') ?></div>
                    </div>
                </div>
            </div>
        </div>
    </div>

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

    <form class="report-form" method="post">
        <input type="hidden" name="csrf_token"
               value="<?= htmlspecialchars(generateCsrfToken(), ENT_QUOTES, 'UTF-8') ?>">
        <input type="hidden" name="rid"  value="<?= $record_id ?>">
        <input type="hidden" name="rand" value="<?= htmlspecialchars($rand_id, ENT_QUOTES, 'UTF-8') ?>">

        <!-- ── 1. Element requirements ────────────────────────────────────── -->
        <div class="card mb-4">
            <div class="card-header fw-bold">
                Total kilograms per hectare of each element needed to balance soil
            </div>
            <div class="card-body">
                <div class="row row-cols-2 row-cols-sm-3 row-cols-md-4 row-cols-lg-6 g-2">
                    <?php
                    echo soilAnalysisReportCalcs('Ca',  'BS_ca_ppm', 'ca_ppm_min', 'ca_ppm_max', 'Calcium',      'kg', 'col', $record_id, $rand_id);
                    echo soilAnalysisReportCalcs('Mg',  'BS_mg_ppm', 'mg_ppm_min', 'mg_ppm_max', 'Magnesium',    'kg', 'col', $record_id, $rand_id);
                    echo soilAnalysisReportCalcs('K',   'BS_k_ppm',  'k_ppm_min',  'k_ppm_max',  'Potassium',    'kg', 'col', $record_id, $rand_id);
                    echo soilAnalysisReportCalcs('Na',  'BS_na_ppm', 'na_ppm_min', 'na_ppm_max', 'Sodium',       'kg', 'col', $record_id, $rand_id);
                    echo soilAnalysisReportCalcs('P',   'p_colwell', '',           '',           'Phosphate',     'kg', 'col', $record_id, $rand_id);
                    echo soilAnalysisReportCalcs('S',   's_morgan',  '',           '',           'Sulfur',        'kg', 'col', $record_id, $rand_id);
                    echo soilAnalysisReportCalcs('Mn',  'mn_dtpa',   '',           '',           'Manganese',     'kg', 'col', $record_id, $rand_id);
                    echo soilAnalysisReportCalcs('Fe',  'fe_dtpa',   '',           '',           'Iron',          'kg', 'col', $record_id, $rand_id);
                    echo soilAnalysisReportCalcs('Zn',  'zn_dtpa',   '',           '',           'Zinc',          'kg', 'col', $record_id, $rand_id);
                    echo soilAnalysisReportCalcs('Cu',  'cu_dtpa',   '',           '',           'Copper',        'kg', 'col', $record_id, $rand_id);
                    echo soilAnalysisReportCalcs('AmN', 'NH3_N',     '',           '',           'Amm. Nitrogen', 'kg', 'col', $record_id, $rand_id);
                    echo soilAnalysisReportCalcs('B',   'b_cacl2',   '',           '',           'Boron',         'kg', 'col', $record_id, $rand_id);
                    echo soilAnalysisReportCalcs('NN',  'NO3_N',     '',           '',           'Nit. Nitrogen', 'kg', 'col', $record_id, $rand_id);
                    ?>
                </div>
            </div>
        </div>

        <!-- ── 2. Five-year balancing plan ───────────────────────────────── -->
        <div class="card mb-4">
            <div class="card-header fw-bold">
                Ideal Soil Balancing Program — 5-Year Plan (kg/ha per year)
            </div>
            <div class="card-body p-0">
                <div class="table-responsive">
                    <table class="table table-sm table-bordered table-hover mb-0">
                        <thead class="table-light">
                            <tr>
                                <th>Element</th>
                                <th class="text-center">Total Deficit</th>
                                <?php for ($y = 1; $y <= 5; $y++): ?>
                                <th class="text-center">Year <?= $y ?></th>
                                <?php endfor; ?>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($planElements as [$label, $col, $minCol, $maxCol, $unit]):
                                $total   = calcDeficit($row, $spec, $col, $minCol, $maxCol);
                                $perYear = $total > 0 ? round($total / 5, 2) : 0;
                            ?>
                            <tr>
                                <td><?= htmlspecialchars($label, ENT_QUOTES, 'UTF-8') ?></td>
                                <td class="text-center <?= $total > 0 ? 'text-danger fw-semibold' : 'text-success' ?>">
                                    <?= $total > 0 ? $total . ' ' . $unit : '&#10003; Adequate' ?>
                                </td>
                                <?php for ($y = 1; $y <= 5; $y++): ?>
                                <td class="text-center">
                                    <?= $perYear > 0 ? $perYear . ' ' . $unit : '—' ?>
                                </td>
                                <?php endfor; ?>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>

        <!-- ── 3. Overview / Executive Summary ───────────────────────────── -->
        <div class="card mb-4">
            <div class="card-header d-flex justify-content-between align-items-center fw-bold">
                <span>Overview</span>
                <button type="button" class="btn btn-outline-primary btn-sm ai-generate-btn"
                        data-section="overview" data-target="#overview">
                    <i class="fas fa-robot me-1"></i>Generate with AI
                </button>
            </div>
            <div class="card-body">
                <textarea id="overview" name="overview" class="form-control report-textarea" rows="6"
                          placeholder="Enter an overview of the soil analysis results..."
                ><?= htmlspecialchars($savedComments['overview'] ?? '', ENT_QUOTES, 'UTF-8') ?></textarea>
            </div>
        </div>

        <!-- ── 4. AI Interpretation ───────────────────────────────────────── -->
        <div class="card mb-4">
            <div class="card-header d-flex justify-content-between align-items-center fw-bold">
                <span>AI Soil Interpretation</span>
                <button type="button" class="btn btn-outline-primary btn-sm ai-generate-btn"
                        data-section="ai_interpretation" data-target="#ai_interpretation">
                    <i class="fas fa-robot me-1"></i>Interpret with AI
                </button>
            </div>
            <div class="card-body">
                <p class="text-muted small mb-2">
                    AI-generated agronomic interpretation. Review and edit before including in the final report.
                </p>
                <textarea id="ai_interpretation" name="ai_interpretation"
                          class="form-control report-textarea" rows="10"
                          placeholder="Click 'Interpret with AI' to generate an agronomic interpretation, or type manually..."
                ><?= htmlspecialchars($savedComments['ai_interpretation'] ?? '', ENT_QUOTES, 'UTF-8') ?></textarea>
            </div>
        </div>

        <!-- ── 5. Foliar program ──────────────────────────────────────────── -->
        <div class="card mb-4">
            <div class="card-header d-flex justify-content-between align-items-center fw-bold">
                <span>
                    <input type="text" name="header1" class="form-control form-control-sm d-inline-block w-auto fw-bold"
                           value="<?= htmlspecialchars($savedComments['header1'] ?? 'Foliar Program', ENT_QUOTES, 'UTF-8') ?>">
                </span>
                <button type="button" class="btn btn-outline-primary btn-sm ai-generate-btn"
                        data-section="foliar" data-target="#foliar_Details">
                    <i class="fas fa-robot me-1"></i>Generate with AI
                </button>
            </div>
            <div class="card-body">
                <textarea id="foliar_Details" name="foliar_Details"
                          class="form-control report-textarea" rows="6"
                          placeholder="Enter the foliar spray program details..."
                ><?= htmlspecialchars($savedComments['foliar_details'] ?? '', ENT_QUOTES, 'UTF-8') ?></textarea>
            </div>
        </div>

        <!-- ── 6. Microbial program ───────────────────────────────────────── -->
        <div class="card mb-4">
            <div class="card-header d-flex justify-content-between align-items-center fw-bold">
                <span>Microbial Program</span>
                <button type="button" class="btn btn-outline-primary btn-sm ai-generate-btn"
                        data-section="microbial" data-target="#microbe_Program">
                    <i class="fas fa-robot me-1"></i>Generate with AI
                </button>
            </div>
            <div class="card-body">
                <textarea id="microbe_Program" name="microbe_Program"
                          class="form-control report-textarea" rows="6"
                          placeholder="Enter the microbial / biological program details..."
                ><?= htmlspecialchars($savedComments['microbe_program'] ?? '', ENT_QUOTES, 'UTF-8') ?></textarea>
            </div>
        </div>

        <!-- ── 7. Disclaimer ──────────────────────────────────────────────── -->
        <div class="card mb-4 border-0 bg-light">
            <div class="card-body">
                <p class="text-muted mb-0" style="font-size:0.75rem;">
                    Any recommendations provided by Crop Monitor are advice only. We are not paid consultants
                    and accept no responsibility for any of our suggestions. No control can be exercised over
                    storage, handling, mixing, application, or use, or over weather, plant or soil conditions
                    before, during or after application — all of which may affect the performance of our
                    program. No responsibility for, or liability for, any failure in performance, losses,
                    damage or injuries consequential or otherwise arising from such storage, mixing,
                    application or use will be accepted under any circumstances whatsoever. The buyer assumes
                    all responsibility for the use of any of our products.
                </p>
            </div>
        </div>

    </form>

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


<script>
(function () {
    'use strict';

    var saveTimer  = null;
    var statusEl   = document.getElementById('save-status');
    var SAVE_URL   = '/dashboard/crop-analysis/updatecomment.php'
                   + '?rid=<?= $record_id ?>&rand=<?= urlencode($rand_id) ?>';
    var AI_URL     = '/controllers/ollamaGenerate.php';
    var CSRF_TOKEN = <?= json_encode(generateCsrfToken()) ?>;

    function setStatus(msg, cls) {
        statusEl.textContent = msg;
        statusEl.className   = 'small mb-2 text-' + (cls || 'secondary');
    }

    // ── Auto-save ────────────────────────────────────────────────────────── //
    document.querySelectorAll('.report-form .report-textarea, .report-form input[name="header1"]')
        .forEach(function (el) {
            el.addEventListener('input', function () {
                clearTimeout(saveTimer);
                saveTimer = setTimeout(saveReport, 1200);
            });
        });

    function saveReport() {
        var form = document.querySelector('.report-form');
        var data = new URLSearchParams(new FormData(form));
        setStatus('Saving…', 'secondary');

        fetch(SAVE_URL, { method: 'POST', body: data })
            .then(function (r) { return r.json(); })
            .then(function (d) {
                if (d.success) {
                    var t = new Date();
                    setStatus('Saved — ' + t.toLocaleTimeString(), 'success');
                } else {
                    setStatus('Save failed: ' + (d.message || 'unknown error'), 'danger');
                }
            })
            .catch(function () {
                setStatus('Network error — not saved', 'danger');
            });
    }

    document.querySelector('.report-form').addEventListener('submit', function (e) {
        e.preventDefault();
        saveReport();
    });

    // ── AI generation ────────────────────────────────────────────────────── //
    function generateSection(btn, section, targetSelector) {
        var textarea = document.querySelector(targetSelector);
        if (!textarea) return;

        var origHTML = btn.innerHTML;
        btn.disabled = true;
        btn.innerHTML = '<span class="spinner-border spinner-border-sm me-1"></span>Generating…';
        setStatus('Requesting AI interpretation…', 'secondary');

        fetch(AI_URL, {
            method: 'POST',
            headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
            body: new URLSearchParams({
                csrf_token: CSRF_TOKEN,
                rid:        <?= $record_id ?>,
                rand:       <?= json_encode($rand_id) ?>,
                section:    section,
            }),
        })
        .then(function (r) { return r.json(); })
        .then(function (d) {
            if (d.success && d.text) {
                textarea.value = d.text;
                textarea.dispatchEvent(new Event('input')); // trigger auto-save
                setStatus('AI text generated — review before publishing', 'success');
            } else {
                setStatus('AI error: ' + (d.error || 'no response returned'), 'danger');
            }
        })
        .catch(function () {
            setStatus('Could not reach AI service. Is Ollama running on port 11434?', 'danger');
        })
        .finally(function () {
            btn.disabled = false;
            btn.innerHTML = origHTML;
        });
    }

    document.querySelectorAll('.ai-generate-btn').forEach(function (btn) {
        btn.addEventListener('click', function () {
            generateSection(btn, btn.dataset.section, btn.dataset.target);
        });
    });

    // "Interpret All" — stagger requests so Ollama isn't flooded
    document.getElementById('btn-generate-all').addEventListener('click', function () {
        var sections = [
            { section: 'overview',          target: '#overview' },
            { section: 'ai_interpretation', target: '#ai_interpretation' },
            { section: 'foliar',            target: '#foliar_Details' },
            { section: 'microbial',         target: '#microbe_Program' },
        ];
        sections.forEach(function (s, i) {
            setTimeout(function () {
                var sectionBtn = document.querySelector('.ai-generate-btn[data-section="' + s.section + '"]');
                generateSection(sectionBtn || document.getElementById('btn-generate-all'), s.section, s.target);
            }, i * 4000);
        });
    });

})();
</script>

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