soil-report-ai/controllers/ollamaGenerate.php

<?php
/**
 * controllers/ollamaGenerate.php
 *
 * AJAX POST handler: generates AI agronomic text using Ollama, grounded
 * with relevant passages retrieved from the soil science knowledge base
 * (William A. Albrecht et al.) via RAG (Retrieval-Augmented Generation).
 *
 * Flow:
 *  1. Load full soil record + specification ranges
 *  2. Build a structured data summary covering ALL measured elements
 *  3. Embed that summary via nomic-embed-text → retrieve top-K book passages
 *  4. Inject retrieved passages + data into a section-specific prompt
 *  5. Send to llama3.1 and return the generated text
 *
 * POST params:
 *   csrf_token  string
 *   rid         int     soil_records.id
 *   rand        string  soil_records.rand
 *   section     string  overview | ai_interpretation | foliar | microbial
 */

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

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

header('Content-Type: application/json');

// ── Config ───────────────────────────────────────────────────────────────────
define('OLLAMA_HOST',      'http://192.168.8.73:11434');
define('OLLAMA_MODEL',     'llama3.1:8b-instruct-q4_K_M');
define('EMBED_MODEL',      'nomic-embed-text');
define('RAG_TOP_K',        6);    // number of knowledge chunks to inject per request
define('OLLAMA_TIMEOUT',   180);  // seconds

// ── Auth + CSRF ───────────────────────────────────────────────────────────────
if (!isLoggedIn()) {
    http_response_code(401);
    echo json_encode(['success' => false, 'error' => 'Not authenticated']);
    exit;
}

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    http_response_code(405);
    echo json_encode(['success' => false, 'error' => 'Method not allowed']);
    exit;
}

if (!verifyCsrfToken($_POST['csrf_token'] ?? '')) {
    http_response_code(403);
    echo json_encode(['success' => false, 'error' => 'Invalid CSRF token']);
    exit;
}

$recordId = (int)trim($_POST['rid']   ?? '');
$randId   = trim($_POST['rand']       ?? '');
$section  = trim($_POST['section']    ?? '');

$validSections = ['overview', 'ai_interpretation', 'foliar', 'microbial'];
if (!$recordId || $randId === '' || !in_array($section, $validSections, true)) {
    http_response_code(400);
    echo json_encode(['success' => false, 'error' => 'Invalid parameters']);
    exit;
}

// ── Load soil record + spec ───────────────────────────────────────────────────
try {
    $pdo = getDBConnection();

    $stmt = $pdo->prepare('SELECT * FROM soil_records WHERE id = ? AND rand = ?');
    $stmt->execute([$recordId, $randId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$row) {
        http_response_code(404);
        echo json_encode(['success' => false, 'error' => 'Record not found']);
        exit;
    }

    $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) ?: [];
    }

} catch (PDOException $e) {
    error_log('DB error in ollamaGenerate.php: ' . $e->getMessage());
    http_response_code(500);
    echo json_encode(['success' => false, 'error' => 'Database error']);
    exit;
}

// ── Helper: safe float format ────────────────────────────────────────────────
function fv(mixed $v, int $dp = 2): string
{
    if ($v === null || $v === '') return 'N/A';
    return is_numeric($v) ? number_format((float)$v, $dp) : (string)$v;
}

// ── Helper: status vs spec range ─────────────────────────────────────────────
function rangeStatus(mixed $value, mixed $min, mixed $max): string
{
    if (!is_numeric($value)) return '';
    $v  = (float)$value;
    $lo = is_numeric($min) ? (float)$min : null;
    $hi = is_numeric($max) ? (float)$max : null;
    if ($lo !== null && $v < $lo) return '[DEFICIENT]';
    if ($hi !== null && $v > $hi) return '[EXCESS]';
    if ($lo !== null || $hi !== null) return '[IDEAL]';
    return '';
}

// ── Helper: resolve spec value from spec row then record row ─────────────────
function sv(array $spec, array $row, string $col): mixed
{
    if (isset($spec[$col]) && $spec[$col] !== '' && $spec[$col] !== null) return $spec[$col];
    if (isset($row[$col])  && $row[$col]  !== '' && $row[$col]  !== null) return $row[$col];
    return null;
}

$r = $row;
$s = $spec;

// ── Build comprehensive soil data block ───────────────────────────────────────
// Includes ALL measured elements with status against spec targets
$soilData = <<<TEXT
=====================================
SOIL TEST DATA — COMPLETE ANALYSIS
=====================================
Client:       {$r['client_name']}
Location:     {$r['site_address']}, {$r['state_postcode']}
Crop:         {$r['sample_id']}
Crop Type:    {$r['crop_type']}
Soil Type:    {$r['soil_type']}
Lab No:       {$r['lab_no']}
Batch:        {$r['batch_no']}
Date Sampled: {$r['date_sampled']}

--- SOIL PHYSICAL / REACTION ---
pH (H2O):                {fv($r['ph_h2o'],   1)}   [target: 6.2–6.8]  {rangeStatus($r['ph_h2o'], 6.2, 6.8)}
pH (CaCl2):              {fv($r['ph_cacl2'], 1)}
EC (mS/cm):              {fv($r['ec'],       2)}
Colour:                  {$r['colour']}
Texture:                 {$r['texture']}
Gravel (%):              {fv($r['gravel'], 1)}

--- ORGANIC MATTER ---
Organic Carbon (%):      {fv($r['ocarbon'], 1)}
Organic Matter (%):      {fv($r['omatter'], 1)}

--- CATION EXCHANGE ---
CEC (meq/100g):          {fv($r['cec'], 2)}
TEC (meq/100g):          {fv($r['tec'], 2)}
Paramagnetic:            {fv($r['paramag'], 0)}

--- NITROGEN ---
Nitrate-N (NO3-N ppm):   {fv($r['NO3_N'],   0)}   [target: 10–20 ppm]  {rangeStatus($r['NO3_N'], 10, 20)}
Ammonium-N (NH3-N ppm):  {fv($r['NH3_N'],   0)}
Total N (est. from C:N): C:N ratio {fv($r['c_n_ratio'], 1)}

--- PHOSPHORUS ---
P Colwell (ppm):         {fv($r['p_colwell'], 0)}
P Morgan (ppm):          {fv($r['p_morgan'],  0)}
P Mehlick (ppm):         {fv($r['p_mehlick'], 0)}
P Bray2 (ppm):           {fv($r['p_bray2'],   0)}

--- MAJOR CATIONS (ppm) ---
Calcium Ca  (ppm):       {fv($r['BS_ca_ppm'], 0)}   [min: {fv(sv($s,$r,'ca_ppm_min'),0)}, max: {fv(sv($s,$r,'ca_ppm_max'),0)}]  {rangeStatus($r['BS_ca_ppm'], sv($s,$r,'ca_ppm_min'), sv($s,$r,'ca_ppm_max'))}
Magnesium Mg (ppm):      {fv($r['BS_mg_ppm'], 0)}   [min: {fv(sv($s,$r,'mg_ppm_min'),0)}, max: {fv(sv($s,$r,'mg_ppm_max'),0)}]  {rangeStatus($r['BS_mg_ppm'], sv($s,$r,'mg_ppm_min'), sv($s,$r,'mg_ppm_max'))}
Potassium K  (ppm):      {fv($r['BS_k_ppm'],  0)}   [min: {fv(sv($s,$r,'k_ppm_min'), 0)}, max: {fv(sv($s,$r,'k_ppm_max'), 0)}]  {rangeStatus($r['BS_k_ppm'],  sv($s,$r,'k_ppm_min'),  sv($s,$r,'k_ppm_max'))}
Sodium Na    (ppm):      {fv($r['BS_na_ppm'], 0)}   [min: {fv(sv($s,$r,'na_ppm_min'),0)}, max: {fv(sv($s,$r,'na_ppm_max'),0)}]  {rangeStatus($r['BS_na_ppm'], sv($s,$r,'na_ppm_min'), sv($s,$r,'na_ppm_max'))}

--- BASE SATURATIONS (%) ---
Calcium  Ca (%):         {fv($r['BS_ca2'], 2)}%   [min: {fv(sv($s,$r,'cabs_min'),1)}, max: {fv(sv($s,$r,'cabs_max'),1)}]  {rangeStatus($r['BS_ca2'], sv($s,$r,'cabs_min'), sv($s,$r,'cabs_max'))}
Magnesium Mg (%):        {fv($r['BS_mg2'], 2)}%   [min: {fv(sv($s,$r,'mgbs_min'),1)}, max: {fv(sv($s,$r,'mgbs_max'),1)}]  {rangeStatus($r['BS_mg2'], sv($s,$r,'mgbs_min'), sv($s,$r,'mgbs_max'))}
Potassium  K  (%):       {fv($r['BS_k'],   2)}%   [min: {fv(sv($s,$r,'kbs_min'), 1)}, max: {fv(sv($s,$r,'kbs_max'), 1)}]  {rangeStatus($r['BS_k'],   sv($s,$r,'kbs_min'),  sv($s,$r,'kbs_max'))}
Sodium     Na (%):       {fv($r['BS_na'],  2)}%   [min: {fv(sv($s,$r,'nabs_min'),1)}, max: {fv(sv($s,$r,'nabs_max'),1)}]  {rangeStatus($r['BS_na'],  sv($s,$r,'nabs_min'), sv($s,$r,'nabs_max'))}
Other Bases (%):         {fv($r['BS_ob'],  2)}%   [recommended: {fv(sv($s,$r,'ob_rec'),1)}]
Hydrogen    (%):         {fv($r['BS_h'],   2)}%   [recommended: {fv(sv($s,$r,'h_rec'), 1)}]
Aluminium  Al3 (%):      {fv($r['BS_al3'], 2)}%

--- MORGANS EXTRACT (ppm) ---
Ca Morgan:               {fv($r['ca_morgan'], 2)}
Mg Morgan:               {fv($r['mg_morgan'], 2)}
K  Morgan:               {fv($r['k_morgan'],  2)}
Na Morgan:               {fv($r['na_morgan'], 2)}

--- MEHLICK-3 EXTRACT (ppm) ---
Ca Mehlick3:             {fv($r['ca_mehlick3'], 2)}
Mg Mehlick3:             {fv($r['mg_mehlick3'], 2)}
K  Mehlick3:             {fv($r['k_mehlick3'],  2)}
Na Mehlick3:             {fv($r['na_mehlick3'], 2)}
Al Mehlick3:             {fv($r['al_mehlick3'], 2)}

--- TRACE ELEMENTS (ppm) ---
Sulfur    S  (ppm):      {fv($r['s_morgan'], 2)}
Boron     B  (ppm):      {fv($r['b_cacl2'],  2)}
Manganese Mn (ppm):      {fv($r['mn_dtpa'],  2)}
Copper    Cu (ppm):      {fv($r['cu_dtpa'],  2)}
Zinc      Zn (ppm):      {fv($r['zn_dtpa'],  2)}
Iron      Fe (ppm):      {fv($r['fe_dtpa'],  2)}
Iron      Fe (total):    {fv($r['fe'],       2)}
Aluminium Al (ppm):      {fv($r['al'],       2)}
Silicon   Si (ppm):      {fv($r['sl_cacl2'], 2)}
Cobalt    Co (ppm):      {fv($r['co_dtpa'],  2)}
Molybdenum M (ppm):      {fv($r['m_dtpa'],   2)}
Selenium  Se (ppm):      {fv($r['se'],       2)}

--- RATIOS ---
Ca:Mg ratio:             {fv(is_numeric($r['ca_mehlick3']) && is_numeric($r['mg_mehlick3']) && (float)$r['mg_mehlick3'] != 0 ? round((float)$r['ca_mehlick3']/(float)$r['mg_mehlick3'],1) : null, 1)}   [recommended: {fv(sv($s,$r,'ca_mg_ratio'),1)}]
C:N  ratio:              {fv($r['c_n_ratio'], 1)}

--- DEFICIENT ELEMENTS SUMMARY ---
TEXT;

// Append a quick plain-English deficiency list to help the LLM focus
$deficiencies = [];
$excesses     = [];

$checkElements = [
    ['pH (H2O)',          $r['ph_h2o'],    6.2,  6.8],
    ['Nitrate-N',         $r['NO3_N'],     10,   20],
    ['Calcium (ppm)',     $r['BS_ca_ppm'], sv($s,$r,'ca_ppm_min'), sv($s,$r,'ca_ppm_max')],
    ['Magnesium (ppm)',   $r['BS_mg_ppm'], sv($s,$r,'mg_ppm_min'), sv($s,$r,'mg_ppm_max')],
    ['Potassium (ppm)',   $r['BS_k_ppm'],  sv($s,$r,'k_ppm_min'),  sv($s,$r,'k_ppm_max')],
    ['Sodium (ppm)',      $r['BS_na_ppm'], sv($s,$r,'na_ppm_min'), sv($s,$r,'na_ppm_max')],
    ['Ca sat (%)',        $r['BS_ca2'],    sv($s,$r,'cabs_min'),   sv($s,$r,'cabs_max')],
    ['Mg sat (%)',        $r['BS_mg2'],    sv($s,$r,'mgbs_min'),   sv($s,$r,'mgbs_max')],
    ['K sat (%)',         $r['BS_k'],      sv($s,$r,'kbs_min'),    sv($s,$r,'kbs_max')],
    ['Na sat (%)',        $r['BS_na'],     sv($s,$r,'nabs_min'),   sv($s,$r,'nabs_max')],
];

foreach ($checkElements as [$label, $val, $lo, $hi]) {
    if (!is_numeric($val)) continue;
    $v = (float)$val;
    if (is_numeric($lo) && $v < (float)$lo) $deficiencies[] = $label;
    if (is_numeric($hi) && $v > (float)$hi) $excesses[]     = $label;
}

$soilData .= "\nDeficient: " . (empty($deficiencies) ? 'None detected' : implode(', ', $deficiencies));
$soilData .= "\nIn Excess: " . (empty($excesses)     ? 'None detected' : implode(', ', $excesses));
$soilData .= "\n=====================================\n";

// ── RAG: embed the soil data query, retrieve relevant book passages ───────────
$knowledgeContext = '';
$ragChunks        = retrieveRelevantChunks($pdo, $soilData, $section, RAG_TOP_K);

if (!empty($ragChunks)) {
    $knowledgeContext = "\n\n===================================================\n"
                      . "RELEVANT PASSAGES FROM SOIL SCIENCE LITERATURE\n"
                      . "(William A. Albrecht and other authorities)\n"
                      . "===================================================\n";
    foreach ($ragChunks as $i => $chunk) {
        $knowledgeContext .= sprintf(
            "\n[%d] \"%s\" — %s (p.%d)\n%s\n",
            $i + 1,
            $chunk['source'],
            $chunk['author'],
            $chunk['page'],
            $chunk['chunk_text']
        );
    }
}

// ── Section-specific system prompts ──────────────────────────────────────────
$systemInstruction = "You are a certified agronomist specialising in soil fertility, "
    . "trained in the Albrecht method of soil balancing. "
    . "You have deep knowledge of soil chemistry, plant nutrition, and the relationship "
    . "between soil mineral balance and crop/livestock health. "
    . "Always ground your recommendations in the measured data. "
    . "For Australian conditions, reference typical soil types and climate where relevant. "
    . "Write in a professional but accessible tone suitable for a farmer-facing report. "
    . "When the knowledge passages conflict with your training, prefer the passages — they "
    . "are from authoritative soil science texts.";

$baseContext = $soilData . $knowledgeContext;

$prompts = [

    'overview' =>
        $systemInstruction . "\n\n" . $baseContext
        . "\n\nTASK: Write an executive overview of these soil test results (3–4 paragraphs). "
        . "Cover: (1) overall soil health and fertility level, "
        . "(2) the most significant deficiencies or imbalances and their likely effect on crop performance, "
        . "(3) any positive attributes of this soil. "
        . "Use the Albrecht philosophy as a framework where applicable. "
        . "Do not list specific product names in this section.",

    'ai_interpretation' =>
        $systemInstruction . "\n\n" . $baseContext
        . "\n\nTASK: Write a detailed technical interpretation of ALL elements in this soil test. "
        . "Structure your response with these sections:\n"
        . "1. SOIL REACTION (pH, EC, Paramagnetic)\n"
        . "2. ORGANIC MATTER & BIOLOGY (C, N, C:N ratio)\n"
        . "3. CATION EXCHANGE CAPACITY & BASE SATURATIONS\n"
        . "4. MAJOR ELEMENTS (Ca, Mg, K, Na, P — ppm and saturation %)\n"
        . "5. TRACE ELEMENTS (S, B, Mn, Cu, Zn, Fe, Al, Si, Co, Mo, Se)\n"
        . "6. ELEMENTAL RATIOS & INTERACTIONS (Ca:Mg, C:N, K:Mg antagonisms)\n"
        . "7. OVERALL SOIL BALANCE ASSESSMENT\n"
        . "For each element marked [DEFICIENT] or [EXCESS], explain the agronomic significance "
        . "and interactions with other elements. Reference the Albrecht literature where relevant.",

    'foliar' =>
        $systemInstruction . "\n\n" . $baseContext
        . "\n\nTASK: Design a foliar nutrition program to address the deficiencies shown. "
        . "Format the program as a table or numbered list with: "
        . "Growth Stage | Product Type | Active Element | Rate (L or kg/ha) | Timing/Frequency. "
        . "Prioritise elements marked [DEFICIENT]. "
        . "Note any antagonisms (e.g. Ca/Mg competition, Zn/P interaction, K/Mg lockout). "
        . "Keep product recommendations generic (e.g. 'chelated zinc', 'calcium nitrate') "
        . "rather than brand names. "
        . "Add a note on carrier water pH and adjuvant recommendations.",

    'microbial' =>
        $systemInstruction . "\n\n" . $baseContext
        . "\n\nTASK: Design a biological/microbial soil improvement program. "
        . "Consider the organic matter level, C:N ratio, pH, and base saturation balance shown. "
        . "Structure your response:\n"
        . "1. CURRENT BIOLOGY ASSESSMENT (based on OM, C:N, pH)\n"
        . "2. RECOMMENDED INOCULANTS (e.g. mycorrhizae, rhizobia, EM, compost tea)\n"
        . "3. CARBON FEEDING STRATEGY (humates, fish hydrolysate, molasses, cover crops)\n"
        . "4. TIMING & INTEGRATION with the soil balancing program\n"
        . "Reference Albrecht's work on the relationship between mineral balance and soil biology.",
];

// ── Call Ollama ───────────────────────────────────────────────────────────────
$payload = json_encode([
    'model'  => OLLAMA_MODEL,
    'prompt' => $prompts[$section],
    'stream' => false,
    'options' => [
        'temperature' => 0.3,   // lower = more factual / less creative
        'num_predict' => 2048,
    ],
]);

$ch = curl_init(OLLAMA_HOST . '/api/generate');
curl_setopt_array($ch, [
    CURLOPT_POST           => true,
    CURLOPT_POSTFIELDS     => $payload,
    CURLOPT_HTTPHEADER     => ['Content-Type: application/json'],
    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_TIMEOUT        => OLLAMA_TIMEOUT,
    CURLOPT_CONNECTTIMEOUT => 5,
]);

$response = curl_exec($ch);
$httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
$curlErr  = curl_error($ch);
curl_close($ch);

if ($curlErr || $response === false) {
    http_response_code(502);
    echo json_encode(['success' => false, 'error' => 'Could not connect to Ollama: ' . ($curlErr ?: 'no response')]);
    exit;
}

if ($httpCode !== 200) {
    http_response_code(502);
    echo json_encode(['success' => false, 'error' => 'Ollama returned HTTP ' . $httpCode]);
    exit;
}

$ollamaData = json_decode($response, true);
$text = trim($ollamaData['response'] ?? '');

if ($text === '') {
    http_response_code(502);
    echo json_encode(['success' => false, 'error' => 'Ollama returned an empty response']);
    exit;
}

echo json_encode([
    'success' => true,
    'text'    => $text,
    'rag_chunks_used' => count($ragChunks),
]);
exit;

// ── RAG retrieval ────────────────────────────────────────────────────────────

/**
 * Embed a query string, then retrieve the top-K most similar knowledge chunks.
 * Falls back to MySQL FULLTEXT search if no embeddings are in the table or
 * if the embedding API is unavailable.
 *
 * @param PDO    $pdo
 * @param string $queryText  The soil data summary used as the retrieval query
 * @param string $section    Current section (used to build keyword fallback)
 * @param int    $topK
 * @return array  Array of row arrays (source, author, page, chunk_text)
 */
function retrieveRelevantChunks(PDO $pdo, string $queryText, string $section, int $topK): array
{
    // Check if we have any chunks at all
    $count = (int)$pdo->query('SELECT COUNT(*) FROM knowledge_chunks')->fetchColumn();
    if ($count === 0) {
        return [];  // Knowledge base not yet populated
    }

    // ── Try vector similarity search first ──────────────────────────────────
    $queryEmbedding = getQueryEmbedding($queryText);

    if ($queryEmbedding !== null) {
        return vectorSearch($pdo, $queryEmbedding, $topK);
    }

    // ── Fallback: MySQL FULLTEXT search ─────────────────────────────────────
    return fulltextSearch($pdo, $section, $topK);
}

/**
 * Embed text via Ollama. Tries new /api/embed first, falls back to legacy
 * /api/embeddings. Returns float[] or null on failure.
 */
function getQueryEmbedding(string $text): ?array
{
    $queryText = substr($text, 0, 2000);

    // ── New API (/api/embed, Ollama >= 0.1.26) ───────────────────────────────
    $ch = curl_init(OLLAMA_HOST . '/api/embed');
    curl_setopt_array($ch, [
        CURLOPT_POST           => true,
        CURLOPT_POSTFIELDS     => json_encode(['model' => EMBED_MODEL, 'input' => $queryText]),
        CURLOPT_HTTPHEADER     => ['Content-Type: application/json'],
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_TIMEOUT        => 15,
        CURLOPT_CONNECTTIMEOUT => 3,
    ]);
    $resp = curl_exec($ch);
    $code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);

    if ($resp && $code === 200) {
        $data = json_decode($resp, true);
        $emb  = $data['embeddings'][0] ?? null;
        if (is_array($emb) && count($emb) > 0) return $emb;
    }

    // ── Legacy API (/api/embeddings) ─────────────────────────────────────────
    $ch = curl_init(OLLAMA_HOST . '/api/embeddings');
    curl_setopt_array($ch, [
        CURLOPT_POST           => true,
        CURLOPT_POSTFIELDS     => json_encode(['model' => EMBED_MODEL, 'prompt' => $queryText]),
        CURLOPT_HTTPHEADER     => ['Content-Type: application/json'],
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_TIMEOUT        => 15,
        CURLOPT_CONNECTTIMEOUT => 3,
    ]);
    $resp2 = curl_exec($ch);
    $code2 = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);

    if ($resp2 && $code2 === 200) {
        $data2 = json_decode($resp2, true);
        $emb2  = $data2['embedding'] ?? null;
        if (is_array($emb2) && count($emb2) > 0) return $emb2;
    }

    return null;
}

/**
 * Load all chunk embeddings from DB, compute cosine similarity, return top-K.
 * For corpora up to ~10k chunks this is fast enough in PHP.
 */
function vectorSearch(PDO $pdo, array $queryVec, int $topK): array
{
    $stmt = $pdo->query(
        'SELECT id, source, author, page, chunk_text, embedding FROM knowledge_chunks'
    );

    $scores = [];

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $chunkVec = json_decode($row['embedding'], true);
        if (!is_array($chunkVec)) continue;

        $sim = cosineSimilarity($queryVec, $chunkVec);
        $scores[] = [
            'score'      => $sim,
            'source'     => $row['source'],
            'author'     => $row['author'],
            'page'       => $row['page'],
            'chunk_text' => $row['chunk_text'],
        ];
    }

    // Sort descending by score, return top-K
    usort($scores, fn($a, $b) => $b['score'] <=> $a['score']);
    return array_slice($scores, 0, $topK);
}

/**
 * MySQL FULLTEXT fallback when embeddings aren't available.
 */
function fulltextSearch(PDO $pdo, string $section, int $topK): array
{
    // Section-specific keyword hints for the search
    $keywords = [
        'overview'          => 'soil fertility mineral balance calcium magnesium',
        'ai_interpretation' => 'base saturation calcium magnesium potassium pH organic matter',
        'foliar'            => 'foliar nutrition trace elements deficiency correction spray',
        'microbial'         => 'soil biology microbial organic matter carbon nitrogen humus',
    ];

    $query = $keywords[$section] ?? 'soil fertility mineral nutrition';

    try {
        $stmt = $pdo->prepare(
            'SELECT source, author, page, chunk_text,
                    MATCH(chunk_text) AGAINST(? IN NATURAL LANGUAGE MODE) AS score
             FROM knowledge_chunks
             WHERE MATCH(chunk_text) AGAINST(? IN NATURAL LANGUAGE MODE)
             ORDER BY score DESC
             LIMIT ?'
        );
        $stmt->execute([$query, $query, $topK]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (PDOException $e) {
        error_log('RAG fulltext search failed: ' . $e->getMessage());
        return [];
    }
}

/**
 * Cosine similarity between two equal-length float vectors.
 */
function cosineSimilarity(array $a, array $b): float
{
    $dot = 0.0;
    $normA = 0.0;
    $normB = 0.0;
    $len = min(count($a), count($b));

    for ($i = 0; $i < $len; $i++) {
        $dot   += $a[$i] * $b[$i];
        $normA += $a[$i] * $a[$i];
        $normB += $b[$i] * $b[$i];
    }

    $denom = sqrt($normA) * sqrt($normB);
    return $denom > 0 ? $dot / $denom : 0.0;
}