ecoles/api/dpe_lib.php

<?php
/**
 * Bibliothèque partagée DPE (ADEME) : création des tables, import du dataset
 * tertiaire et rattachement géographique aux établissements.
 *
 * Utilisée par les scripts CLI (import_dpe.php, match_dpe.php) et par
 * l'endpoint web super-admin (api/import_dpe.php), pour éviter toute
 * divergence de logique.
 */
declare(strict_types=1);
require_once __DIR__ . '/db.php';

const DPE_DATASET = 'https://data.ademe.fr/data-fair/api/v1/datasets/dpe01tertiaire/lines';
const DPE_FIELDS = 'numero_dpe,etiquette_dpe,etiquette_ges,conso_kwhep_m2_an,'
    . 'emission_ges_kg_co2_m2_an,surface_utile,periode_construction,categorie_erp,'
    . 'date_etablissement_dpe,date_fin_validite_dpe,code_insee_ban,code_postal_ban,'
    . 'adresse_ban,nom_commune_ban,_geopoint';

/** GET JSON via cURL (suit les redirections). */
function dpe_fetch_json(string $url): array
{
    $ch = curl_init($url);
    curl_setopt_array($ch, [
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_TIMEOUT        => 120,
        CURLOPT_USERAGENT      => 'ecoles-import/1.0',
        CURLOPT_HTTPHEADER     => ['Accept: application/json'],
    ]);
    $body = curl_exec($ch);
    if ($body === false) {
        $err = curl_error($ch);
        curl_close($ch);
        throw new RuntimeException('Requête échouée : ' . $err);
    }
    $code = curl_getinfo($ch, CURLINFO_RESPONSE_CODE);
    curl_close($ch);
    if ($code >= 400) {
        throw new RuntimeException("HTTP $code sur l'API ADEME.");
    }
    $data = json_decode($body, true);
    if (!is_array($data)) {
        throw new RuntimeException('Réponse JSON ADEME invalide.');
    }
    return $data;
}

/** Crée la table « dpe » si absente. */
function dpe_create_table(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS dpe (
            numero_dpe    VARCHAR(20) NOT NULL PRIMARY KEY,
            etiquette_dpe CHAR(1) NULL,
            etiquette_ges CHAR(1) NULL,
            conso         DECIMAL(10,1) NULL,   -- conso_kwhep_m2_an (énergie primaire kWh/m²/an)
            ges           DECIMAL(10,2) NULL,   -- emission_ges_kg_co2_m2_an
            surface       INT NULL,             -- surface_utile (m²)
            periode       VARCHAR(40) NULL,     -- periode_construction
            cat_erp       VARCHAR(60) NULL,     -- categorie_erp
            date_dpe      DATE NULL,            -- date_etablissement_dpe
            date_fin      DATE NULL,            -- date_fin_validite_dpe
            code_insee    VARCHAR(5) NULL,      -- code_insee_ban (clé de rattachement commune)
            cp            VARCHAR(5) NULL,
            adresse       VARCHAR(255) NULL,
            commune       VARCHAR(120) NULL,
            lat           DECIMAL(9,6) NULL,
            lon           DECIMAL(9,6) NULL,
            INDEX idx_insee (code_insee)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
    );
}

/**
 * Importe les DPE tertiaires de l'ADEME dans la table « dpe ».
 * @param string|null $dep  filtre département (code) ou null pour tout
 * @param callable    $log  fn(string) pour la progression
 * @return int              nombre de DPE importés
 */
function dpe_import(PDO $pdo, ?string $dep, callable $log): int
{
    dpe_create_table($pdo);

    $url = DPE_DATASET . '?size=10000&sort=numero_dpe&select=' . rawurlencode(DPE_FIELDS);
    if ($dep !== null && $dep !== '') {
        $url .= '&qs=' . rawurlencode('code_departement_ban:"' . $dep . '"');
        $log("Filtre département = $dep.");
    }

    $ins = $pdo->prepare(
        'REPLACE INTO dpe
            (numero_dpe, etiquette_dpe, etiquette_ges, conso, ges, surface, periode,
             cat_erp, date_dpe, date_fin, code_insee, cp, adresse, commune, lat, lon)
         VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
    );

    $letter = static fn($v) => ($v !== null && $v !== '') ? strtoupper(substr((string) $v, 0, 1)) : null;
    $date   = static fn($v) => (is_string($v) && preg_match('/^\d{4}-\d{2}-\d{2}$/', $v)) ? $v : null;
    $numOrNull = static fn($v) => ($v === null || $v === '') ? null : (float) $v;

    $page = 0;
    $n = 0;
    $total = null;
    $pdo->beginTransaction();

    while ($url) {
        $res = dpe_fetch_json($url);
        $total ??= $res['total'] ?? null;
        $rows = $res['results'] ?? [];
        if (!$rows) {
            break;
        }
        foreach ($rows as $r) {
            $id = $r['numero_dpe'] ?? '';
            if ($id === '') {
                continue;
            }
            $lat = $lon = null;
            if (!empty($r['_geopoint']) && str_contains($r['_geopoint'], ',')) {
                [$lat, $lon] = array_map('floatval', explode(',', $r['_geopoint'], 2));
            }
            $ins->execute([
                substr($id, 0, 20),
                $letter($r['etiquette_dpe'] ?? null),
                $letter($r['etiquette_ges'] ?? null),
                $numOrNull($r['conso_kwhep_m2_an'] ?? null),
                $numOrNull($r['emission_ges_kg_co2_m2_an'] ?? null),
                isset($r['surface_utile']) && $r['surface_utile'] !== '' ? (int) round((float) $r['surface_utile']) : null,
                $r['periode_construction'] ?? null,
                $r['categorie_erp'] ?? null,
                $date($r['date_etablissement_dpe'] ?? null),
                $date($r['date_fin_validite_dpe'] ?? null),
                $r['code_insee_ban'] ?? null,
                $r['code_postal_ban'] ?? null,
                $r['adresse_ban'] ?? null,
                $r['nom_commune_ban'] ?? null,
                $lat, $lon,
            ]);
            $n++;
        }
        $pdo->commit();
        $pdo->beginTransaction();

        $page++;
        $log(sprintf('Page %d — %d DPE importés%s', $page, $n, $total ? " / $total" : ''));

        // Page suivante (curseur). data-fair renvoie l'URL complète dans « next ».
        $url = $res['next'] ?? null;
        if ($url) {
            usleep(800000); // ménage le quota anonyme de l'API (20 s de traitement / 60 s)
        }
    }
    $pdo->commit();

    return $n;
}

/** Crée la table de rattachement « etab_dpe » si absente. */
function dpe_ensure_link_table(PDO $pdo): void
{
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS etab_dpe (
            uai        VARCHAR(12) NOT NULL,
            numero_dpe VARCHAR(20) NOT NULL,
            distance_m SMALLINT NOT NULL,   -- distance école↔bâtiment (m)
            PRIMARY KEY (uai, numero_dpe),
            INDEX idx_uai (uai)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
    );
}

/**
 * Rattache les DPE aux établissements par proximité (même commune, ≤ $radius m).
 * @return array{pairs:int, ecoles:int}
 */
function dpe_match(PDO $pdo, int $radius, callable $log): array
{
    foreach (['dpe', 'etablissements'] as $t) {
        if (!$pdo->query('SHOW TABLES LIKE ' . $pdo->quote($t))->fetchColumn()) {
            throw new RuntimeException("Table « $t » absente — lancez d'abord l'import.");
        }
    }
    if (!$pdo->query("SHOW COLUMNS FROM etablissements LIKE 'comc'")->fetchColumn()) {
        throw new RuntimeException("Colonne « comc » absente — lancez d'abord import_comc.php.");
    }

    dpe_ensure_link_table($pdo);
    $log("Rattachement (rayon = {$radius} m)…");
    $pdo->exec('TRUNCATE TABLE etab_dpe');

    // Jointure restreinte à la commune (index idx_insee / idx_comc) puis filtre
    // par distance sphérique (ST_Distance_Sphere ⇒ MySQL 5.7.6+ / MariaDB 10.2.4+).
    $sql =
        'INSERT INTO etab_dpe (uai, numero_dpe, distance_m)
         SELECT e.id, d.numero_dpe,
                ROUND(ST_Distance_Sphere(POINT(e.lon, e.lat), POINT(d.lon, d.lat)))
           FROM etablissements e
           JOIN dpe d ON d.code_insee = e.comc
          WHERE e.comc IS NOT NULL AND e.lat IS NOT NULL AND e.lon IS NOT NULL
            AND d.lat IS NOT NULL AND d.lon IS NOT NULL
            AND ST_Distance_Sphere(POINT(e.lon, e.lat), POINT(d.lon, d.lat)) <= ?';
    $st = $pdo->prepare($sql);
    $st->execute([$radius]);

    return [
        'pairs'  => (int) $pdo->query('SELECT COUNT(*) FROM etab_dpe')->fetchColumn(),
        'ecoles' => (int) $pdo->query('SELECT COUNT(DISTINCT uai) FROM etab_dpe')->fetchColumn(),
    ];
}