soil-report-ai/api/updateweatherstation.php

<?php
/**
 * api/updateweatherstation.php
 *
 * Weather station data ingestion endpoint.
 * Authenticates the station using ID + PASSWORD against client_records,
 * then inserts a row into weather_station using PDO prepared statements.
 *
 * Expected GET params (Weather Underground protocol):
 *   ID, PASSWORD, dateutc, winddir, windspeedmph, humidity, tempf, ...
 */

require_once __DIR__ . '/../config/database.php';

header('Content-Type: text/plain');

// ---------------------------------------------------------------------------
// Authenticate station: ID + PASSWORD must match client_records.wustationid
// and client_records.wuapikey for the calling station.
// ---------------------------------------------------------------------------
$stationId = trim($_GET['ID']       ?? '');
$password  = trim($_GET['PASSWORD'] ?? '');

if ($stationId === '' || $password === '') {
    http_response_code(401);
    echo 'error: missing credentials';
    exit;
}

try {
    $pdo = getDBConnection();

    $stmt = $pdo->prepare(
        'SELECT id FROM client_records WHERE wustationid = ? AND wuapikey = ? LIMIT 1'
    );
    $stmt->execute([$stationId, $password]);

    if (!$stmt->fetch()) {
        http_response_code(401);
        echo 'error: invalid station credentials';
        exit;
    }
} catch (PDOException $e) {
    error_log('updateweatherstation auth error: ' . $e->getMessage());
    http_response_code(500);
    echo 'error: server error';
    exit;
}

// ---------------------------------------------------------------------------
// Parse and sanitise all numeric / string fields
// ---------------------------------------------------------------------------

/** Cast a GET param to float, returning null if missing or non-numeric. */
function getFloat(string $key): ?float
{
    $v = $_GET[$key] ?? null;
    if ($v === null || $v === '') return null;
    return is_numeric($v) ? (float) $v : null;
}

/** Cast a GET param to int, returning null if missing or non-numeric. */
function getInt(string $key): ?int
{
    $v = $_GET[$key] ?? null;
    if ($v === null || $v === '') return null;
    return is_numeric($v) ? (int) $v : null;
}

$action = trim($_GET['action'] ?? '');
$action = substr($action, 0, 50); // max 50 chars

$dateutc = trim($_GET['dateutc'] ?? '');
if ($dateutc === 'now' || $dateutc === '') {
    $dateutc = gmdate('Y-m-d H:i:s');
} else {
    // Validate / normalise datetime; reject anything that doesn't parse
    $ts = strtotime($dateutc);
    $dateutc = $ts !== false ? gmdate('Y-m-d H:i:s', $ts) : gmdate('Y-m-d H:i:s');
}

// Numeric sensor readings
$winddir          = getFloat('winddir');
$windspeedmph     = getFloat('windspeedmph');
$windgustmph      = getFloat('windgustmph');
$windgustdir      = getFloat('windgustdir');
$windspdmph_avg2m = getFloat('windspdmph_avg2m');
$winddir_avg2m    = getFloat('winddir_avg2m');
$windgustmph_10m  = getFloat('windgustmph_10m');
$windgustdir_10m  = getFloat('windgustdir_10m');
$humidity         = getFloat('humidity');
$dewptf           = getFloat('dewptf');
$tempf            = getFloat('tempf');
$temp2f           = getFloat('temp2f');
$temp3f           = getFloat('temp3f');
$temp4f           = getFloat('temp4f');
$rainin           = getFloat('rainin');
$dailyrainin      = getFloat('dailyrainin');
$baromin          = getFloat('baromin');
$soiltempf        = getFloat('soiltempf');
$soiltemp2f       = getFloat('soiltemp2f');
$soiltemp3f       = getFloat('soiltemp3f');
$soiltemp4f       = getFloat('soiltemp4f');
$soilmoisture     = getFloat('soilmoisture');
$soilmoisture2    = getFloat('soilmoisture2');
$soilmoisture3    = getFloat('soilmoisture3');
$soilmoisture4    = getFloat('soilmoisture4');
$leafwetness      = getFloat('leafwetness');
$leafwetness2     = getFloat('leafwetness2');
$solarradiation   = getFloat('solarradiation');
$UV               = getFloat('UV');
$visibility       = getFloat('visibility');
$indoortempf      = getFloat('indoortempf');
$indoorhumidity   = getFloat('indoorhumidity');

// Short string fields
$weather = substr(trim($_GET['weather'] ?? ''), 0, 100);
$clouds  = substr(trim($_GET['clouds']  ?? ''), 0, 100);

// ---------------------------------------------------------------------------
// Insert
// ---------------------------------------------------------------------------
try {
    $stmt = $pdo->prepare('
        INSERT INTO `weather_station`
            (action, ID, dateutc,
             winddir, windspeedmph, windgustmph, windgustdir,
             windspdmph_avg2m, winddir_avg2m, windgustmph_10m, windgustdir_10m,
             humidity, dewptf, tempf, temp2f, temp3f, temp4f,
             rainin, dailyrainin, baromin, weather, clouds,
             soiltempf, soiltemp2f, soiltemp3f, soiltemp4f,
             soilmoisture, soilmoisture2, soilmoisture3, soilmoisture4,
             leafwetness, leafwetness2, solarradiation, UV, visibility,
             indoortempf, indoorhumidity)
        VALUES
            (:action, :ID, :dateutc,
             :winddir, :windspeedmph, :windgustmph, :windgustdir,
             :windspdmph_avg2m, :winddir_avg2m, :windgustmph_10m, :windgustdir_10m,
             :humidity, :dewptf, :tempf, :temp2f, :temp3f, :temp4f,
             :rainin, :dailyrainin, :baromin, :weather, :clouds,
             :soiltempf, :soiltemp2f, :soiltemp3f, :soiltemp4f,
             :soilmoisture, :soilmoisture2, :soilmoisture3, :soilmoisture4,
             :leafwetness, :leafwetness2, :solarradiation, :UV, :visibility,
             :indoortempf, :indoorhumidity)
    ');

    $stmt->execute([
        ':action'          => $action,
        ':ID'              => $stationId,
        ':dateutc'         => $dateutc,
        ':winddir'         => $winddir,
        ':windspeedmph'    => $windspeedmph,
        ':windgustmph'     => $windgustmph,
        ':windgustdir'     => $windgustdir,
        ':windspdmph_avg2m'=> $windspdmph_avg2m,
        ':winddir_avg2m'   => $winddir_avg2m,
        ':windgustmph_10m' => $windgustmph_10m,
        ':windgustdir_10m' => $windgustdir_10m,
        ':humidity'        => $humidity,
        ':dewptf'          => $dewptf,
        ':tempf'           => $tempf,
        ':temp2f'          => $temp2f,
        ':temp3f'          => $temp3f,
        ':temp4f'          => $temp4f,
        ':rainin'          => $rainin,
        ':dailyrainin'     => $dailyrainin,
        ':baromin'         => $baromin,
        ':weather'         => $weather,
        ':clouds'          => $clouds,
        ':soiltempf'       => $soiltempf,
        ':soiltemp2f'      => $soiltemp2f,
        ':soiltemp3f'      => $soiltemp3f,
        ':soiltemp4f'      => $soiltemp4f,
        ':soilmoisture'    => $soilmoisture,
        ':soilmoisture2'   => $soilmoisture2,
        ':soilmoisture3'   => $soilmoisture3,
        ':soilmoisture4'   => $soilmoisture4,
        ':leafwetness'     => $leafwetness,
        ':leafwetness2'    => $leafwetness2,
        ':solarradiation'  => $solarradiation,
        ':UV'              => $UV,
        ':visibility'      => $visibility,
        ':indoortempf'     => $indoortempf,
        ':indoorhumidity'  => $indoorhumidity,
    ]);

    echo 'success';
} catch (PDOException $e) {
    error_log('updateweatherstation insert error: ' . $e->getMessage());
    http_response_code(500);
    echo 'error: server error';
}