<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);

// config laden
require_once 'config.php';

// Verbindungsaufbau zur Datenbank
$config = include('db-connection.php');
$host = $config['host'];
$port = $config['port'];
$dbname = $config['dbname'];
$user = $config['user'];
$password = $config['password'];

// Verbindung aufbauen
$dbconn = pg_connect("host=$host port=$port dbname=$dbname user=$user password=$password");
if (!$dbconn) {
    echo json_encode(['success' => false, 'error' => 'Verbindungsaufbau fehlgeschlagen: ' . pg_last_error()]);
    exit;
}

// Eingabedaten aus JSON-Body
$data = file_get_contents('php://input');

// Zeige die rohen JSON-Daten zum Debuggen
file_put_contents('raw_data.txt', $data . PHP_EOL, FILE_APPEND);

// Überprüfen und konvertieren der Eingabedaten
$data = mb_convert_encoding($data, 'UTF-8', 'auto');
pg_set_client_encoding($dbconn, "UTF8");

// JSON dekodieren
$data = json_decode($data, true);
if ($data === null) {
    echo json_encode(['success' => false, 'error' => 'Ungültiges JSON-Format']);
    exit;
}

// Überprüfen, ob die erforderlichen Felder vorhanden sind
$requiredFields = ['first_name', 'last_name', 'year_of_birth', 'street', 'house_no', 'localdistrict', 'city', 'geom', 'Lat', 'Lon'];
foreach ($requiredFields as $field) {
    if (!isset($data[$field])) {
        echo json_encode(['success' => false, 'error' => "Fehlende Daten: $field"]);
        exit;
    }
}

// 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'];
$geom = $data['geom'];
$Lat = $data['Lat'];
$Lon = $data['Lon'];

$stone_position = isset($data['stone_position']) ? $data['stone_position'] : null;
$status = isset($data['status']) ? $data['status'] : null;
$comment = isset($data['comment']) ? $data['comment'] : null;
$last_photo = isset($data['last_photo']) ? pg_escape_bytea($data['last_photo']) : null;
$caretaker = isset($data['caretaker']) ? $data['caretaker'] : null;
$url_wikipedia = isset($data['URL_Wikipedia']) ? $data['URL_Wikipedia'] : null;
$confirmed_by = isset($data['Confirmed_by']) ? $data['Confirmed_by'] : null;
$manual_address = isset($data['manual_address']) ? (bool)$data['manual_address'] : false;
$url_kodb = isset($data['URL_KoDb']) ? $data['URL_KoDb'] : null;

$last_cleaned = isset($data['last_cleaned']) ? $data['last_cleaned'] : null;
$cleaned_by = isset($data['cleaned_by']) ? $data['cleaned_by'] : null;
$instance_nr = isset($data['instance_nr']) ? $data['instance_nr'] : null;

// Überprüfen, ob der Stolperstein bereits existiert
$check_query = "
    SELECT gid 
    FROM public.{$tablePrefix} 
    WHERE first_name = $1 
      AND last_name = $2 
      AND year_of_birth = $3 
      AND street = $4 
      AND house_no = $5 
      AND localdistrict = $6
    LIMIT 1;
";

// Parameter für die Überprüfung
$check_params = array($first_name, $last_name, $year_of_birth, $street, $house_no, $localdistrict);

// Überprüfung ausführen
$check_result = pg_query_params($dbconn, $check_query, $check_params);
if (!$check_result) {
    $error_message = pg_last_error($dbconn);  // Fehlernachricht abrufen
    echo json_encode(['success' => false, 'error' => 'Fehler bei der Überprüfung: ' . $error_message]);
    exit;
}

if (pg_num_rows($check_result) > 0) {
    echo json_encode([
        'success' => false,
        'error' => 'Dieser Stolperstein existiert bereits an dieser Adresse.'
    ]);
    exit;
}

// SQL-Insert-Query vorbereiten
$query = "
    INSERT INTO public.{$tablePrefix}  (
        first_name, last_name, year_of_birth, street, house_no, localdistrict, city, geom, Lat, Lon, 
        stone_position, status, comment, last_photo, caretaker, URL_Wikipedia, Confirmed_by, 
        manual_address, URL_KoDb, last_cleaned, cleaned_by, instance_nr
    )
    VALUES (
        $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, 
        $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22
    )
    RETURNING gid;
";

// Parameter für das Query
$params = array(
    $first_name, $last_name, $year_of_birth, $street, $house_no, $localdistrict, $city, 
    $geom, $Lat, $Lon, $stone_position, $status, $comment, $last_photo, 
    $caretaker, $url_wikipedia, $confirmed_by, $manual_address, $url_kodb, $last_cleaned, $cleaned_by, $instance_nr
);

// Sicherstellen, dass alle 22 Werte im Array vorhanden sind
if (count($params) != 22) {
    $errorDetails = [];
    foreach ($params as $index => $param) {
        $errorDetails[] = "Parameter " . ($index + 1) . ": " . var_export($param, true);
    }
    echo json_encode(['success' => false, 'error' => 'Es fehlen Parameter für den INSERT!', 'details' => $errorDetails]);
    exit;
}

// Query ausführen und Debug-Informationen in die Datei schreiben
file_put_contents('raw_data.txt', '---------------' . PHP_EOL, FILE_APPEND);
file_put_contents('raw_data.txt', $query . PHP_EOL, FILE_APPEND);
file_put_contents('raw_data.txt', print_r($params, true) . PHP_EOL, FILE_APPEND);

// Query ausführen
$result = pg_query_params($dbconn, $query, $params);
if (!$result) {
    $error_message = pg_last_error($dbconn);  // Fehlernachricht abrufen
    // Fehler in der Datei speichern
    file_put_contents('raw_data.txt', "Fehler bei SQL-Abfrage: " . $error_message . PHP_EOL, FILE_APPEND);
    // Fehler auch im Browser ausgeben
    echo json_encode(['success' => false, 'error' => $error_message]);
    exit;
}

// Das Ergebnis des Inserts holen
$row = pg_fetch_assoc($result);

// Erfolgreiche Antwort mit der GID zurückgeben
$response = [
    'success' => true,
    'gid' => $row['gid']  // Der zurückgegebene Wert von GID
];

// Content-Type Header setzen
header('Content-Type: application/json');
echo json_encode($response);

// Verbindung schließen
pg_free_result($result);
pg_close($dbconn);
?>