<?php
// fetch_poi_cleaning_status_stats.php

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

// 1. Dateien laden
$debugLogFile = __DIR__ . '/fetch_poi_cleaning_status_stats_debug.log';
require_once __DIR__ . '/logging_functions.php';
require_once __DIR__ . '/validation_functions.php';

// 2. DER TÜRSTEHER-CHECK
// Prüft db-Parameter (prod/test). Bricht bei Ungültigkeit sofort ab.
enforceValidRequest($debugLogFile);

// 3. KONFIGURATION LADEN
$config = require_once __DIR__ . '/config.php'; 
$targetTable = $config['poiTable']; 

// 4. DATENBANKVERBINDUNG (PDO)
$db = require __DIR__ . '/db-connection.php';

try {
    $pdo = new PDO(
        "pgsql:host={$db['host']};port={$db['port']};dbname={$db['dbname']}",
        $db['user'],
        $db['password'],
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
    );
} catch (PDOException $e) {
    internal_debug_log("DB-Verbindung fehlgeschlagen: " . $e->getMessage(), $debugLogFile);
    http_response_code(500);
    echo json_encode(["error" => "Datenbankverbindung fehlgeschlagen."]);
    exit;
}

// ------------------------------------------------------------------
// === SQL-ABFRAGE FÜR DIE 5 KATEGORIEN ===
// ------------------------------------------------------------------

// ... (Dein bisheriger PHP Code bis zur Query) ...

$statsQuery = "
    SELECT 
        sub.label,
        COUNT(*) AS value
    FROM (
        SELECT 
            CASE 
                WHEN last_cleaned IS NULL THEN 'nie_null'
                WHEN last_cleaned >= NOW() - INTERVAL '1 month'  THEN '1_monat'
                WHEN last_cleaned >= NOW() - INTERVAL '3 months' THEN '2_3_monate'
                WHEN last_cleaned >= NOW() - INTERVAL '6 months' THEN '4_6_monate'
                ELSE 'ueber_6_monate'
            END AS label
        FROM public.{$targetTable}
    ) sub
    GROUP BY sub.label
    ORDER BY 
        CASE 
            WHEN sub.label = '1_monat' THEN 1        -- glänzend
            WHEN sub.label = '2_3_monate' THEN 2     -- gut
            WHEN sub.label = '4_6_monate' THEN 3     -- schlecht
            WHEN sub.label = 'ueber_6_monate' THEN 4 -- stumpf
            ELSE 5                                   -- unbekannt
        END ASC
";


try {
    $stmt = $pdo->prepare($statsQuery);
    $stmt->execute();
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    internal_debug_log("Statistik aus $targetTable erfolgreich geladen.", $debugLogFile);

    header('Content-Type: application/json');
    echo json_encode([
        'success' => true,
        'table'   => $targetTable,
        'data'    => $results
    ]);

} catch (PDOException $e) {
    internal_debug_log("SQL-Fehler: " . $e->getMessage(), $debugLogFile);
    http_response_code(500);
    echo json_encode(["success" => false, "error" => "SQL-Abfrage fehlgeschlagen."]);
}