<?php
// insert_poi.php

// --- Schritt 1: Debug-Logfile und Logging-Funktion zuerst definieren ---
$debugLogFile = __DIR__ . '/insert-poi-debug.log';
require_once __DIR__ . '/logging_functions.php';

// --- Schritt 2: Konfiguration und andere Funktionen laden ---
$config = require_once __DIR__ . '/config.php';
require_once __DIR__ . '/validation_functions.php';

// --- Fehlerberichterstattung (nur für Debugging im lokalen/Test-Umfeld!) ---
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

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

// === Überprüfen, ob die Anfragemethode POST ist ===
if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    internal_debug_log("Ungültige Anfragemethode: " . $_SERVER['REQUEST_METHOD'], $debugLogFile);
    http_response_code(405);
    echo json_encode(['success' => false, 'error' => 'Ungültige Anfragemethode.']);
    exit();
}

// === Lade Datenbankverbindungseinstellungen ===
if (file_exists(__DIR__ . '/db-connection.php')) {
    $dbConfig = require_once __DIR__ . '/db-connection.php';
    if (!is_array($dbConfig)) {
        internal_debug_log("Fehler: 'db-connection.php' gibt keine gültige Konfiguration zurück.", $debugLogFile);
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => "Serverfehler: 'db-connection.php' gibt keine gültige Konfiguration zurück."]);
        exit();
    }
} else {
    internal_debug_log("Fehler: Verbindungskonfigurationsdatei 'db-connection.php' fehlt.", $debugLogFile);
    http_response_code(500);
    echo json_encode(['success' => false, 'error' => "Serverfehler: 'db-connection.php' fehlt."]);
    exit();
}
internal_debug_log("Datenbankverbindung erfolgreich geladen.", $debugLogFile);

// --- Gesamten Skriptinhalt in einen Try-Catch-Block einschließen ---
try {
    // --- Konfiguration und Datenbankverbindung mit PDO ---
    $tablePrefix = $config['tablePrefix'] ?? '';
    internal_debug_log("Table prefix loaded: '" . $tablePrefix . "'", $debugLogFile);

    // Die Variablen werden jetzt aus dem $dbConfig-Array gelesen
    $host = $dbConfig['host'] ?? 'localhost';
    $port = $dbConfig['port'] ?? 5432;
    $dbname = $dbConfig['dbname'] ?? '';
    $user = $dbConfig['user'] ?? '';
    $password = $dbConfig['password'] ?? '';

    $dsn = "pgsql:host=$host;port=$port;dbname=$dbname;user=$user;password=$password";
    internal_debug_log("Attempting PDO connection with DSN: " . $dsn, $debugLogFile);
    
    // PDO-Verbindung herstellen
    $pdo = new PDO($dsn);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Wichtig für die Fehlerbehandlung
    internal_debug_log("Database connection successful using PDO.", $debugLogFile);

    // --- Eingabedaten verarbeiten ---
    $input = file_get_contents('php://input');
    internal_debug_log("Received raw input: " . $input, $debugLogFile);
    $data = json_decode($input, true);

    if (json_last_error() !== JSON_ERROR_NONE) {
        $jsonError = json_last_error_msg();
        internal_debug_log("JSON Decode Error: " . $jsonError, $debugLogFile);
        throw new Exception("Ungültige JSON-Eingabe: " . $jsonError);
    }
    internal_debug_log("Decoded input data: " . print_r($data, true), $debugLogFile);

    // Überprüfen, ob die erforderlichen Felder vorhanden sind
    $requiredFields = ['first_name', 'last_name', 'year_of_birth', 'street', 'house_no', 'localdistrict', 'city', 'Lat', 'Lon', 'updated_by', 'updated_at'];
    foreach ($requiredFields as $field) {
        if (!isset($data[$field]) || $data[$field] === '') {
            internal_debug_log("Missing or empty required data: " . $field, $debugLogFile);
            throw new Exception("Fehlende Daten: '$field'");
        }
    }
   internal_debug_log("nr of expected input elements: " . count($requiredFields), $debugLogFile);
   internal_debug_log("nr of received input elements: " . count($data), $debugLogFile);

    // 9 Werte aus dem JSON in Variablen setzen
    $first_name = $data['first_name'];
    $last_name = $data['last_name'];
    $year_of_birth = $data['year_of_birth'];
    $street = $data['street'];
    $house_no = $data['house_no'];

    $localdistrict = $data['localdistrict'];
    $city = $data['city'];
    $Lat = (float)$data['Lat'];
    $Lon = (float)$data['Lon'];
    
    // Weitere 14 Felder mit Null-Coalescing-Operator
    $stone_position = $data['stone_position'] ?? null;
    $status = $data['status'] ?? null;
    $comment = $data['comment'] ?? null;
    $last_photo = $data['last_photo'] ?? null;
    $caretaker = $data['caretaker'] ?? null;

    $url_wikipedia = $data['URL_Wikipedia'] ?? null;
    $confirmed_by = $data['Confirmed_by'] ?? null;
    $manual_address_for_db = (isset($data['manual_address']) && (bool)$data['manual_address']) ? 't' : 'f';
    $url_kodb = $data['URL_KoDb'] ?? null;
    $last_cleaned = !empty($data['last_cleaned']) ? $data['last_cleaned'] : null;

    $cleaned_by = !empty($data['cleaned_by']) ? $data['cleaned_by'] : null;
    $instance_nr = $data['instance_nr'] ?? null;
    $updated_by = $data['updated_by'];
    $updated_at = $data['updated_at'];
    
    // --- SQL-Abfrage zur Überprüfung auf Duplikate ---
    $checkQuery = "
        SELECT COUNT(*) FROM public.{$tablePrefix}
        WHERE first_name = ?
        AND last_name = ?
        AND year_of_birth = ?
        AND street LIKE ?
        AND house_no = ?
        AND localdistrict = ?;
    ";
    
    $street_pattern = substr($street, 0, -4) . '%';
    internal_debug_log("Street pattern for duplicate check: '" . $street_pattern . "'", $debugLogFile);

    $checkStmt = $pdo->prepare($checkQuery);
    $checkStmt->execute([$first_name, $last_name, $year_of_birth, $street_pattern, $house_no, $localdistrict]);
    $count = $checkStmt->fetchColumn();

    if ($count > 0) {
        internal_debug_log("Duplicate entry found. Count: " . $count, $debugLogFile);
 //       throw new Exception('Dieser Stolperstein existiert bereits an dieser Adresse.');
 //       if ($duplicateFound) {
    http_response_code(409); // Konflikt
    echo json_encode([
        "success" => false,
        "error" => "Dieser Stolperstein existiert bereits an dieser Adresse."
    ]);
    exit;
//}
    }
    internal_debug_log("No duplicate entry found.", $debugLogFile);
    

    // --- SQL-Insert-Query vorbereiten und ausführen ---
    $query = "
         INSERT INTO public.{$tablePrefix} (
            geom, Lat, Lon, street, house_no, localdistrict, first_name, last_name, 
            year_of_birth, last_cleaned, cleaned_by, stone_position, status, comment, 
            last_photo, caretaker, url_wikipedia, confirmed_by, manual_address, url_kodb, 
            instance_nr, city, updated_at, updated_by, geom_geolocation
        )
        VALUES (
            ST_SetSRID(ST_MakePoint(?, ?), 4326),                -- 2 Platzhalter für geom (Lon, Lat)
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, -- 22 Platzhalter für Lat bis updated_by
            ST_SetSRID(ST_MakePoint(?, ?), 4326)                -- 2 Platzhalter für geom_geolocation (Lon, Lat)
        )
        RETURNING gid;
    ";
    internal_debug_log("SQL Insert Query: " . $query, $debugLogFile);

    $params = [
    // 2 geom (Lon, Lat)
    $Lon, $Lat,

    // 23 einfache Felder
    $Lat,
    $Lon,
    $street,
    $house_no,
    $localdistrict,

    $first_name,
    $last_name,
    $year_of_birth,
    $last_cleaned,
    $cleaned_by,
    
    $stone_position,
    $status,
    $comment,
    $last_photo,
    $caretaker,
    
    $url_wikipedia,
    $confirmed_by,
    $manual_address_for_db,
    $url_kodb,
    $instance_nr,
    
    $city,
    $updated_at,
    $updated_by,

    // 2 geom_geolocation (Lon, Lat)
    $Lon,
    $Lat
];
    
    // Die Parameteranzahl ist jetzt 27!
    internal_debug_log("Insert Params: " . implode(', ', array_map(function($p) { return var_export($p, true); }, $params)), $debugLogFile);
    if (count($params) !== 27) {
        throw new Exception("Falsche Anzahl von Parametern. Erwartet: 27 Erhalten: " . count($params));
    }

    $stmt = $pdo->prepare($query);
    $stmt->execute($params);    

    $gid = $stmt->fetchColumn();
    
    if ($gid) {
        $response = [
            'success' => true,
            'gid' => $gid
        ];
        internal_debug_log("Insert successful. New GID: " . $gid, $debugLogFile);
        echo json_encode($response);
    } else {
        throw new Exception('Insert war erfolgreich, aber GID konnte nicht abgerufen werden.');
    }

} catch (PDOException $e) {
    $errorMessage = "Database error: " . $e->getMessage() . " in " . $e->getFile() . " on line " . $e->getLine();
    internal_debug_log($errorMessage, $debugLogFile);
    http_response_code(500);
    echo json_encode(['success' => false, 'error' => 'Datenbankfehler', 'details' => $e->getMessage()]);
} catch (Throwable $e) {
    $errorMessage = "Unhandled exception/error: " . $e->getMessage() . " in " . $e->getFile() . " on line " . $e->getLine();
    internal_debug_log($errorMessage, $debugLogFile);
    http_response_code(500);
    echo json_encode(['success' => false, 'error' => 'Ein unerwarteter Serverfehler ist aufgetreten.', 'details' => $e->getMessage()]);
}

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