<?php
// fetch_pois_by_address.php
// Sucht Stolpersteine anhand von Adress-Ähnlichkeit.
// Wenn Hausnummer fehlt: Listet alle Adressen der Straße einzeln auf.

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

// === Logging und Konfiguration laden ===
$debugLogFile = __DIR__ . '/fetch_pois_by_address_debug.log';

require_once __DIR__ . '/logging_functions.php'; 
require_once __DIR__ . '/validation_functions.php'; 
require_once __DIR__ . '/fetch_poi_select.php';

// 1. DER TÜRSTEHER-CHECK
enforceValidRequest($debugLogFile);

internal_debug_log("--- " . basename(__FILE__) . " START ---", $debugLogFile);
header('Content-Type: application/json');

try {
    // --- Konfiguration laden ---
    $config = require __DIR__ . '/config.php';
    $targetTable = $config['poiTable'] ?? ''; 

    // --- Datenbankverbindung laden ---
    $dbConfig = require __DIR__ . '/db-connection.php';
    $host = $dbConfig['host'] ?? 'localhost';
    $port = $dbConfig['port'] ?? 5432;
    $dbname = $dbConfig['dbname'] ?? '';
    $user = $dbConfig['user'] ?? '';
    $password = $dbConfig['password'] ?? '';

    // ------------------------------------------------------------------
    // === EINGABEDATEN ZUSAMMENFÜHREN (GET + JSON POST) ===
    // ------------------------------------------------------------------
    $data = $_GET; 
    $jsonInput = json_decode(file_get_contents('php://input'), true);
    if (is_array($jsonInput)) {
        $data = array_merge($data, $jsonInput);
    }

    internal_debug_log("Merged Data: " . print_r($data, true), $debugLogFile);

    // --- Validierung und Normalisierung ---
    $street = trim($data['street'] ?? '');
    $houseNr = trim($data['houseNr'] ?? '');
    
    // Normalisierung: Nur Zahlen, Buchstaben und Bindestriche
    $houseNr = preg_replace('/[^0-9a-zA-Z-]/', '', $houseNr); 

    if (empty($street)) {
        throw new Exception('Fehlende Suchparameter (Straße erforderlich).');
    }

    // === Verbindung zur Datenbank herstellen ===
    $connString = "host=$host port=$port dbname=$dbname user=$user password=$password";
    $dbconn = @pg_connect($connString); 

    if (!$dbconn) {
        throw new Exception('Datenbankverbindung fehlgeschlagen: ' . pg_last_error());
    }
    pg_set_client_encoding($dbconn, "UTF8");

    // === SQL-Parameter & Query dynamisch vorbereiten ===
    $similarityThreshold = 0.4;
    $houseNrNormalized = str_replace(' ', '', $houseNr);
    
    // Wir bauen die Parameter-Liste dynamisch auf, um PostgreSQL-Fehler zu vermeiden
    $queryParams = [$street]; // $1 ist immer die Straße
    $paramIdx = 1;

    $houseCondition = "";
    if (!empty($houseNrNormalized)) {
        $paramIdx++; // Dies wird $2
        if (strpos($houseNrNormalized, '-') !== false) {
            $queryParams[] = explode('-', $houseNrNormalized)[0] . '%'; 
            $operator = "ILIKE";
        } else if (preg_match('/^\d+$/', $houseNrNormalized)) {
            $queryParams[] = $houseNrNormalized; 
            $operator = "=";
        } else {
            $queryParams[] = $houseNrNormalized . '%'; 
            $operator = "ILIKE";
        }
        $houseCondition = "AND REPLACE(house_no, ' ', '') {$operator} \${$paramIdx}";
    }

    // Parameter für die ILIKE-Suche und Sortierung am Ende anfügen
    $paramIdx++;
    $queryParams[] = $street; 
    $streetPlaceholder = "\${$paramIdx}";

    $query = "
        SELECT 
            street, 
            house_no,
            REPLACE(house_no, ' ', '') AS house_no_normalized,
            localdistrict,
            COUNT(*) AS stolperstein_count,
            AVG(ST_Y(geom_geolocation)) AS lat, 
            AVG(ST_X(geom_geolocation)) AS lng,
            STRING_AGG(first_name || ' ' || last_name || 
                CASE 
                    WHEN year_of_birth IS NOT NULL AND year_of_birth > 0 
                    THEN ' (geb. ' || year_of_birth || ')' 
                    ELSE '' 
                END, ' ||| ') AS aggregated_names
        FROM \"{$targetTable}\" 
        WHERE 
            (
                similarity(street, $1) > {$similarityThreshold}  
                OR street ILIKE {$streetPlaceholder} || '%'
            ) 
            {$houseCondition}
        GROUP BY
            street, house_no, localdistrict
        ORDER BY 
            similarity(street, {$streetPlaceholder}) DESC,
            substring(house_no FROM '^[0-9]+')::integer ASC NULLS LAST,
            house_no ASC
        LIMIT 25
    ";
    
    internal_debug_log("Executing SQL with Params: " . print_r($queryParams, true), $debugLogFile);

    $result = pg_query_params($dbconn, $query, $queryParams);

    if (!$result) {
        throw new Exception('Datenbankabfrage fehlgeschlagen: ' . pg_last_error($dbconn));
    }
    
    $rows = pg_fetch_all($result) ?: [];
    pg_free_result($result);

    // === Ergebnis transformieren ===
    $transformedRows = array_map(function($row) {
        $st = $row['street'] ?? '';
        $nr = $row['house_no'] ?? ''; 
        $dist = $row['localdistrict'] ?? '';
        $count = (int)($row['stolperstein_count'] ?? 0);
        
        return [
            'source'     => 'POI',
            'precision'  => 'grouped',
            'street'     => $st,
            'houseNr'    => $nr, 
            'district'   => $dist,
            'city'       => 'Berlin', 
            'lat'        => (float)($row['lat'] ?? 0),
            'lng'        => (float)($row['lng'] ?? 0),
            'poi_count'  => $count,
            'aggregated_names' => $row['aggregated_names'] ?? '',
            'display_name'     => "$st $nr" . ($dist ? ", $dist" : "") . " ($count)"
        ];
    }, $rows); 

    pg_close($dbconn);

    echo json_encode(['success' => true, 'data' => $transformedRows], JSON_PRETTY_PRINT);

} catch (Exception $e) {
    internal_debug_log("Error: " . $e->getMessage(), $debugLogFile);
    http_response_code(500);
    echo json_encode(['success' => false, 'error' => $e->getMessage()], JSON_PRETTY_PRINT);
}

internal_debug_log("--- END ---", $debugLogFile);
?>