json.php

<?php
/**
 * disc-cloud-tracker — latest-measurement-per-device JSON endpoint
 *
 * Returns, as a JSON array, the most recent row in `mesures` for each
 * distinct device (`DISC`).
 *
 * Note: the `check_disc` SQL view provided in schema.sql does exactly the
 * same thing more elegantly. The inline join below keeps this script
 * self-contained (no dependency on the view being installed).
 */

// ---------------------------------------------------------------------------
// Fail fast on missing critical environment variables (no placeholder fallback)
// ---------------------------------------------------------------------------
function require_env(string $name): string {
    $v = getenv($name);
    if ($v === false || $v === '' || $v === 'changeme') {
        http_response_code(500);
        error_log("FATAL: environment variable $name is not set");
        die("Configuration error");
    }
    return $v;
}

// ---------------------------------------------------------------------------
// Database configuration (from environment variables; DB_PASSWORD is required)
// ---------------------------------------------------------------------------
$DB_HOST     = getenv('DB_HOST')     ?: 'localhost';
$DB_PORT     = (int) (getenv('DB_PORT') ?: 3306);
$DB_NAME     = getenv('DB_NAME')     ?: 'disc';
$DB_USER     = getenv('DB_USER')     ?: 'disc';
$DB_PASSWORD = require_env('DB_PASSWORD');

// Connect to MySQL
$conn = new mysqli($DB_HOST, $DB_USER, $DB_PASSWORD, $DB_NAME, $DB_PORT);

if ($conn->connect_error) {
    http_response_code(500);
    die("Database connection failed: " . $conn->connect_error);
}

// Optimised self-join: fetch the latest row per DISC in a single query.
// (Equivalent to the `check_disc` view defined in schema.sql.)
$sql = "SELECT
    m1.id                   AS id,
    m1.V1                   AS V1,
    m1.V2                   AS V2,
    m1.POW                  AS POW,
    m1.TEMP                 AS TEMP,
    m1.R1                   AS R1,
    m1.GSM_DATA             AS GSM_DATA,
    m1.SERIAL_DATA_LATITUDE AS SERIAL_DATA_LATITUDE,
    m1.LATITUDE             AS LATITUDE,
    m1.LONGITUDE            AS LONGITUDE,
    m1.ACPLUG               AS ACPLUG,
    m1.DISC                 AS DISC,
    m1.timestamp            AS timestamp,
    m1.ADRESSE              AS ADRESSE
FROM mesures m1
JOIN (
    SELECT DISC, MAX(timestamp) AS max_timestamp
    FROM mesures
    GROUP BY DISC
) m2 ON m1.DISC = m2.DISC AND m1.timestamp = m2.max_timestamp
ORDER BY m1.DISC;";

$result = $conn->query($sql);

if ($result === false) {
    http_response_code(500);
    die("SQL error: " . $conn->error);
}

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

if ($result->num_rows > 0) {
    $data = array();
    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
    echo json_encode($data);
} else {
    echo json_encode([]);
}

$conn->close();