<?php
declare(strict_types=1);

/** PHP 8.5; benötigt ext-curl, ext-simplexml und ext-pdo_mysql. */
final class MyGpsImportException extends RuntimeException {}

function fetchMyGpsLiveXml(#[SensitiveParameter] string $apiKey): string
{
    if ($apiKey === '' || preg_match('/\s/', $apiKey)) {
        throw new MyGpsImportException('Bitte einen gültigen API-Key hinterlegen.');
    }
    $url = 'https://gps.my-gps.org/api/api_v2.php?' . http_build_query([
        'api_key' => $apiKey,
        'api_version' => '7-2-26',
        'api_action' => 'live',
    ], '', '&', PHP_QUERY_RFC3986);

    $curl = curl_init($url);
    $body = '';
    curl_setopt_array($curl, [
        CURLOPT_HTTPHEADER => ['Accept: application/xml'],
        CURLOPT_CONNECTTIMEOUT => 8,
        CURLOPT_TIMEOUT => 20,
        CURLOPT_FOLLOWLOCATION => false,
        CURLOPT_SSL_VERIFYPEER => true,
        CURLOPT_SSL_VERIFYHOST => 2,
        CURLOPT_USERAGENT => 'my-GPS-API-Example/1.0',
        CURLOPT_WRITEFUNCTION => static function ($handle, string $chunk) use (&$body): int {
            if (strlen($body) + strlen($chunk) > 4 * 1024 * 1024) {
                return 0; // Unerwartet grosse Antworten abbrechen.
            }
            $body .= $chunk;
            return strlen($chunk);
        },
    ]);
    if (defined('CURLOPT_PROTOCOLS_STR')) {
        curl_setopt($curl, CURLOPT_PROTOCOLS_STR, 'https');
    } else {
        curl_setopt($curl, CURLOPT_PROTOCOLS, CURLPROTO_HTTPS);
    }
    $ok = curl_exec($curl);
    $curlCode = curl_errno($curl);
    $httpCode = (int) curl_getinfo($curl, CURLINFO_HTTP_CODE);
    // CurlHandle wird freigegeben, wenn die Funktion endet.
    if ($ok === false) {
        // curl_error() und die URL nicht ausgeben: Sie koennten den Key enthalten.
        throw new MyGpsImportException('Abruf fehlgeschlagen (cURL-Code ' . $curlCode . ').');
    }
    if ($httpCode !== 200) {
        throw new MyGpsImportException('Die API antwortet mit HTTP-Status ' . $httpCode . '.');
    }
    return $body;
}

function myGpsField(SimpleXMLElement $node, string $field): string
{
    if (count($node->{$field}) !== 1) {
        throw new MyGpsImportException('Die XML-Antwort enthält ein fehlendes oder mehrfaches Feld: ' . $field);
    }
    return (string) $node->{$field};
}

function myGpsInteger(string $value, int $minimum, int $maximum): int
{
    $parsed = filter_var(trim($value), FILTER_VALIDATE_INT, [
        'options' => ['min_range' => $minimum, 'max_range' => $maximum],
    ]);
    if ($parsed === false) {
        throw new MyGpsImportException('Die API enthält einen ungültigen Zahlenwert.');
    }
    return $parsed;
}

function myGpsCoordinate(string $value, float $minimum, float $maximum): string
{
    $value = trim($value);
    if (!preg_match('/\A-?\d+(?:\.\d+)?\z/D', $value)
        || (float) $value < $minimum || (float) $value > $maximum) {
        throw new MyGpsImportException('Die API enthält eine ungültige Koordinate.');
    }
    return number_format((float) $value, 7, '.', '');
}

function myGpsText(string $value, int $maximum): string
{
    if (!preg_match('//u', $value) || preg_match_all('/./us', $value) > $maximum) {
        throw new MyGpsImportException('Ein Textfeld der API ist ungültig oder zu lang.');
    }
    return $value;
}

function parseMyGpsLiveXml(string $body): array
{
    // Keine DTD/Entities verarbeiten; NONET allein schuetzt nicht vor lokalen Dateien.
    if ($body === '' || strlen($body) > 4 * 1024 * 1024
        || preg_match('/<!\s*(?:DOCTYPE|ENTITY)\b/i', $body)) {
        throw new MyGpsImportException('Die API hat keine gültige XML-Antwort geliefert.');
    }
    $previous = libxml_use_internal_errors(true);
    try {
        $xml = simplexml_load_string($body, SimpleXMLElement::class, LIBXML_NONET | LIBXML_NOCDATA);
    } finally {
        libxml_clear_errors();
        libxml_use_internal_errors($previous);
    }
    if ($xml === false || $xml->getName() !== 'response') {
        throw new MyGpsImportException('Die API hat keine gültige XML-Antwort geliefert.');
    }
    $status = myGpsInteger(myGpsField($xml, 'status_code'), 100, 599);
    if ($status !== 200) {
        $messages = [
            404 => 'Der API-Key ist ungültig, pausiert oder gelöscht.',
            429 => 'Zu viele Anfragen. Den nächsten Abruf frühestens nach 60 Sekunden ausführen.',
            503 => 'Die API ist vorübergehend nicht verfügbar. Später erneut versuchen.',
            500 => 'Die API verlangt eine sichere HTTPS-Verbindung.',
        ];
        throw new MyGpsImportException($messages[$status] ?? 'Die API meldet einen Fehler.');
    }
    myGpsField($xml, 'status_description');
    $count = myGpsInteger(myGpsField($xml, 'item_count'), 0, 100000);
    if (count($xml->items) !== 1 || count($xml->items->item) !== $count) {
        throw new MyGpsImportException('Die Geräteanzahl passt nicht zur XML-Antwort.');
    }
    $rows = [];
    $seen = [];
    foreach ($xml->items->item as $item) {
        $id = trim(myGpsField($item, 'id'));
        if (!preg_match('/\A[a-zA-Z0-9_-]{1,64}\z/D', $id) || isset($seen[$id])) {
            throw new MyGpsImportException('Eine Geräte-ID ist ungültig oder mehrfach vorhanden.');
        }
        $seen[$id] = true;
        $valid = myGpsInteger(myGpsField($item, 'valid'), 0, 1);
        $timestamp = myGpsInteger(myGpsField($item, 'utc_timestamp_gps'), 0, PHP_INT_MAX);
        // MySQL DATETIME unterstuetzt Jahre bis 9999.
        $utcDate = $timestamp === 0 ? null : gmdate('Y-m-d H:i:s', $timestamp);
        if ($utcDate !== null && strlen($utcDate) !== 19) {
            throw new MyGpsImportException('Der GPS-Zeitstempel liegt außerhalb des unterstützten Bereichs.');
        }
        $plate = trim(myGpsField($item, 'license_plate'));
        $battery = myGpsInteger(myGpsField($item, 'battery'), -1, 100);
        $longitude = myGpsCoordinate(myGpsField($item, 'longitude'), -180, 180);
        $latitude = myGpsCoordinate(myGpsField($item, 'latitude'), -90, 90);
        $rows[] = [
            'device_id' => $id,
            'internal_name' => myGpsText(myGpsField($item, 'internal_name'), 255),
            'license_plate' => $plate === '-1' || $plate === '' ? null : myGpsText($plate, 64),
            'utc_timestamp_gps' => $timestamp === 0 ? null : $timestamp,
            'gps_recorded_at_utc' => $utcDate,
            'device_timezone' => myGpsInteger(myGpsField($item, 'device_timezone'), -14, 14),
            'longitude' => $valid === 1 ? $longitude : null,
            'latitude' => $valid === 1 ? $latitude : null,
            'valid' => $valid,
            'battery' => $battery === -1 ? null : $battery,
            'speed' => myGpsInteger(myGpsField($item, 'speed'), 0, 65535),
        ];
    }
    return $rows;
}

function saveMyGpsLiveRows(PDO $pdo, array $rows): int
{
    $sql = 'INSERT INTO gps_live_positions
        (device_id, internal_name, license_plate, utc_timestamp_gps, gps_recorded_at_utc,
         device_timezone, longitude, latitude, valid, battery, speed, fetched_at_utc)
        VALUES (:device_id, :internal_name, :license_plate, :utc_timestamp_gps, :gps_recorded_at_utc,
                :device_timezone, :longitude, :latitude, :valid, :battery, :speed, :fetched_at_utc)
        ON DUPLICATE KEY UPDATE
            internal_name=VALUES(internal_name), license_plate=VALUES(license_plate),
            utc_timestamp_gps=VALUES(utc_timestamp_gps), gps_recorded_at_utc=VALUES(gps_recorded_at_utc),
            device_timezone=VALUES(device_timezone), longitude=VALUES(longitude), latitude=VALUES(latitude),
            valid=VALUES(valid), battery=VALUES(battery), speed=VALUES(speed), fetched_at_utc=VALUES(fetched_at_utc)';
    $statement = $pdo->prepare($sql);
    $fetchedAt = gmdate('Y-m-d H:i:s');
    $pdo->beginTransaction();
    try {
        foreach ($rows as $row) {
            $row['fetched_at_utc'] = $fetchedAt;
            $statement->execute($row);
        }
        $pdo->commit();
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) { $pdo->rollBack(); }
        throw $e;
    }
    return count($rows);
}

function runMyGpsImport(): int
{
    if (PHP_SAPI !== 'cli') { http_response_code(404); return 1; }
    $apiKey = trim((string) getenv('MGPS_API_KEY'));
    $dsn = (string) getenv('MGPS_DB_DSN');
    $dbUser = (string) getenv('MGPS_DB_USER');
    $dbPassword = (string) getenv('MGPS_DB_PASSWORD');
    if ($apiKey === '' || strpos($dsn, 'mysql:') !== 0 || $dbUser === '') {
        fwrite(STDERR, "Bitte MGPS_API_KEY, MGPS_DB_DSN, MGPS_DB_USER und MGPS_DB_PASSWORD konfigurieren.\n");
        return 1;
    }
    // Verhindert ueberlappende Jobs fuer dieselbe Datenbank auf diesem Rechner.
    $lockFile = sys_get_temp_dir() . '/my-gps-import-' . hash('sha256', $dsn . '|' . $dbUser) . '.lock';
    $lock = fopen($lockFile, 'c');
    if ($lock === false || !flock($lock, LOCK_EX | LOCK_NB)) {
        fwrite(STDERR, "Ein Import läuft bereits oder die Lockdatei ist nicht verfügbar.\n");
        return 1;
    }
    try {
        $rows = parseMyGpsLiveXml(fetchMyGpsLiveXml($apiKey));
        $pdo = new PDO($dsn, $dbUser, $dbPassword, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]);
        $pdo->exec("SET time_zone = '+00:00'");
        $pdo->exec("SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'");
        $number = saveMyGpsLiveRows($pdo, $rows);
        fwrite(STDOUT, $number . " GPS-Datensätze aktualisiert.\n");
        return 0;
    } catch (MyGpsImportException $e) {
        fwrite(STDERR, $e->getMessage() . "\n");
        return 1;
    } catch (Throwable $e) {
        // Keine Anfrage-URL, Zugangsdaten oder Payload in die Ausgabe schreiben.
        fwrite(STDERR, "Der Import konnte nicht gespeichert werden. Bitte Datenbank und PHP-Erweiterungen prüfen.\n");
        return 1;
    } finally {
        flock($lock, LOCK_UN);
        fclose($lock);
    }
}

if (isset($_SERVER['SCRIPT_FILENAME']) && realpath($_SERVER['SCRIPT_FILENAME']) === __FILE__) {
    exit(runMyGpsImport());
}

-- Eine Zeile pro Geraet; der PHP-Import aktualisiert den letzten Datenstand.
-- Kompatibel mit MySQL 5.6 und neueren MySQL-Versionen.
-- Sonderwerte -1 / Zeitstempel 0 / ungueltige Koordinaten werden zu NULL.
CREATE TABLE IF NOT EXISTS `gps_live_positions` (
    `device_id` VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    `internal_name` VARCHAR(255) NOT NULL,
    `license_plate` VARCHAR(64) NULL DEFAULT NULL,
    `utc_timestamp_gps` BIGINT UNSIGNED NULL DEFAULT NULL,
    `gps_recorded_at_utc` DATETIME NULL DEFAULT NULL,
    `device_timezone` SMALLINT NOT NULL COMMENT 'UTC-Offset in Stunden aus der API',
    `longitude` DECIMAL(10,7) NULL DEFAULT NULL,
    `latitude` DECIMAL(9,7) NULL DEFAULT NULL,
    `valid` TINYINT UNSIGNED NOT NULL,
    `battery` TINYINT UNSIGNED NULL DEFAULT NULL COMMENT '0-100 Prozent; NULL = nicht verfuegbar',
    `speed` SMALLINT UNSIGNED NOT NULL COMMENT 'km/h',
    `fetched_at_utc` DATETIME NOT NULL COMMENT 'Zeitpunkt des Imports; UTC',
    PRIMARY KEY (`device_id`),
    KEY `idx_gps_recorded_at` (`gps_recorded_at_utc`),
    KEY `idx_gps_license_plate` (`license_plate`),
    KEY `idx_gps_fetched_at` (`fetched_at_utc`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Letzte GPS-API-Daten je Geraet; eine my-GPS-Kundenanbindung pro Tabelle';