<?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 ---
require_once __DIR__ . '/validation_functions.php';

// NEU: Sofortiger Abbruch bei ungültigen URL-Parametern (z.B. ?db=prod3)
enforceValidRequest($debugLogFile);

$config = require_once __DIR__ . '/config.php';

// --- Fehlerberichterstattung ---
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 {
    // KORREKTUR: Nutzt jetzt die zentral validierte Tabelle aus der config.php
    $poiTable = $config['poiTable'] ?? ''; 
    internal_debug_log("Table from config: '" . $poiTable . "'", $debugLogFile);

    // DB-Variablen
    $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";
    
    // PDO-Verbindung herstellen
    $pdo = new PDO($dsn);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    internal_debug_log("Database connection successful using PDO.", $debugLogFile);

    // --- Eingabedaten verarbeiten ---
    $input = file_get_contents('php://input');
    $data = json_decode($input, true);

    if (json_last_error() !== JSON_ERROR_NONE) {
        throw new Exception("Ungültige JSON-Eingabe: " . json_last_error_msg());
    }

    // Pflichtfelder prüfen
    $requiredFields = ['first_name', 'last_name', 'year_of_birth', 'street', 'house_no', 'localdistrict', 'city', 'lat', 'lng', 'updated_by', 'updated_at'];
    foreach ($requiredFields as $field) {
        if (!isset($data[$field]) || $data[$field] === '') {
            throw new Exception("Fehlende Daten: '$field'");
        }
    }

    // Variablen-Zuweisung
    $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'];
    $lng = (float)$data['lng'];
    
    $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'];
    
    // --- Duplikat-Check ---
    $checkQuery = "
        SELECT COUNT(*) FROM public.{$poiTable}
        WHERE first_name = ? AND last_name = ? AND year_of_birth = ?
        AND street LIKE ? AND house_no = ? AND localdistrict = ?;
    ";
    
    $street_pattern = substr($street, 0, -4) . '%';
    $checkStmt = $pdo->prepare($checkQuery);
    $checkStmt->execute([$first_name, $last_name, $year_of_birth, $street_pattern, $house_no, $localdistrict]);
    
    if ($checkStmt->fetchColumn() > 0) {
        http_response_code(409);
        echo json_encode(["success" => false, "error" => "Dieser Stolperstein existiert bereits an dieser Adresse."]);
        exit;
    }

    // --- SQL-Insert ---
    $query = "
         INSERT INTO public.{$poiTable} (
            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), 
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 
            ST_SetSRID(ST_MakePoint(?, ?), 4326)
        )
        RETURNING gid;
    ";

    $params = [
        $lng, $lat, // geom
        $lat, $lng, $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,
        $lng, $lat // geom_geolocation
    ];

    $stmt = $pdo->prepare($query);
    $stmt->execute($params);    
    $gid = $stmt->fetchColumn();
    
    if ($gid) {
        echo json_encode(['success' => true, 'gid' => $gid]);
    } else {
        throw new Exception('Insert erfolgreich, aber GID fehlt.');
    }

} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode(['success' => false, 'error' => 'Datenbankfehler', 'details' => $e->getMessage()]);
} catch (Throwable $e) {
    http_response_code(500);
    echo json_encode(['success' => false, 'error' => 'Serverfehler', 'details' => $e->getMessage()]);
}

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