<?php
// check_urls.php
// 1. SYSTEM-SETUPS
set_time_limit(0); 
ob_implicit_flush(true);
while (ob_get_level()) ob_end_flush();

$debugLogFile = __DIR__ . '/url_check_debug.log';
require_once __DIR__ . '/logging_functions.php';
$config = require_once __DIR__ . '/config.php';
$db = require __DIR__ . '/db-connection.php';   

// --- KONFIGURATION ---
$batchSize   = 100;                 // Moderate Batch-Größe für Stabilität
$totalLimit  = 12000;                // HIER DEIN GESAMTLIMIT EINSTELLEN
$pauseInMs   = 3000;                // 3 Sekunden Pause zwischen Batches (schont DB & Zielserver)
$offset      = isset($_GET['offset']) ? (int)$_GET['offset'] : 0;
$targetTable = $config['poiTable']; 

// Abbruch-Bedingung für das Gesamtlimit
if ($offset >= $totalLimit) {
    echo "<h2>✅ Gesamtlimit von $totalLimit erreicht. Prüfung beendet.</h2>";
    exit;
}

/**
 * Normalisierung: Erzeugt Varianten für Umlaute (ae vs a)
 */
function getNormalizationVariants($text) {
    if (empty($text)) return [];
    $text = mb_strtolower($text, 'UTF-8');
    $search  = ['.', '(', ')', ',', ' ', '/', '-'];
    $replace = ['', '', '', '', ' ', ' ', ' ']; // Erstmal in Leerzeichen wandeln
    $clean = trim(str_replace($search, $replace, $text));

    $variants = [];
    // V1: mueller
    $v1 = str_replace(['ä', 'ö', 'ü', 'ß', ' '], ['ae', 'oe', 'ue', 'ss', '-'], $clean);
    // V2: muller
    $v2 = str_replace(['ä', 'ö', 'ü', 'ß', ' '], ['a', 'o', 'u', 'ss', '-'], $clean);

    return array_unique([$v1, $v2]);
}

try {
    // 2. DATENBANKVERBINDUNG MIT TIMEOUT-SCHUTZ
    $pdo = new PDO(
        "pgsql:host={$db['host']};port={$db['port']};dbname={$db['dbname']}",
        $db['user'],
        $db['password'], 
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_PERSISTENT => true,    // Verbindung offen halten
            PDO::ATTR_TIMEOUT => 30         // Längerer Connect-Timeout für OVH Cloud
        ]
    );

    // SQL Abfrage
    $sql = "SELECT gid, first_name, last_name, url_kodb 
            FROM public.{$targetTable} 
            WHERE url_kodb IS NOT NULL 
            ORDER BY gid ASC 
            LIMIT :limit OFFSET :offset";

    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':limit', $batchSize, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    echo "<div style='font-family:sans-serif;'>";
    echo "<h3>Prüfung: $offset bis " . ($offset + count($rows)) . " (Gesamtlimit: $totalLimit)</h3>";

    if (count($rows) === 0) {
        echo "<h2 style='color:green;'>✅ Alle verfügbaren Daten geprüft. Fertig!</h2>";
        exit;
    }

    foreach ($rows as $row) {
        $url = trim($row['url_kodb']);
        
        // cURL Check
        $ch = curl_init($url);
        curl_setopt_array($ch, [
            CURLOPT_RETURNTRANSFER => true, 
            CURLOPT_FOLLOWLOCATION => true,
            CURLOPT_NOBODY => true, 
            CURLOPT_TIMEOUT => 5, 
            CURLOPT_SSL_VERIFYPEER => false,
            CURLOPT_USERAGENT => 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36'
        ]);
        curl_exec($ch);
        $finalUrl = curl_getinfo($ch, CURLINFO_EFFECTIVE_URL);
        $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        curl_close($ch);

        $nameInUrl = end(explode('/', rtrim($finalUrl, '/')));
        
        // Tolerante Namensprüfung
        $variantsFirst = getNormalizationVariants($row['first_name']);
        $variantsLast  = getNormalizationVariants($row['last_name']);
        
        $matchFirst = false;
        foreach ($variantsFirst as $v) if (strpos($nameInUrl, $v) !== false) { $matchFirst = true; break; }
        
        $matchLast = false;
        foreach ($variantsLast as $v) if (strpos($nameInUrl, $v) !== false) { $matchLast = true; break; }

        // Mismatch-Logik für komplexe Nachnamen
        if (!$matchLast && (strpos($row['last_name'], ' ') !== false || strpos($row['last_name'], '-') !== false)) {
            $parts = preg_split('/[\s-]+/', $row['last_name']);
            foreach ($parts as $p) {
                if (mb_strlen($p) > 3) {
                    foreach (getNormalizationVariants($p) as $vp) {
                        if (strpos($nameInUrl, $vp) !== false) { $matchLast = true; break 2; }
                    }
                }
            }
        }

        if (!$matchFirst || !$matchLast || $httpCode >= 400) {
            $errorLabel = ($httpCode >= 400) ? "HTTP $httpCode" : "Namens-Fehler";
            echo "<div style='color:red; margin-bottom:5px; border-left:3px solid red; padding-left:10px;'>";
            echo "❌ <b>GID {$row['gid']}:</b> {$row['first_name']} {$row['last_name']} <br>";
            echo "<small>URL: $finalUrl ($errorLabel)</small></div>";
            
            internal_debug_log("GID {$row['gid']} MISMATCH: $errorLabel | $finalUrl", $debugLogFile);
        }
    }

    // 3. AUTOMATISCHE WEITERLEITUNG (JAVASCRIPT)
    $nextOffset = $offset + $batchSize;
    echo "<p style='color:#666;'><i>Nächste $batchSize Datensätze in " . ($pauseInMs/1000) . " Sek...</i></p>";
    echo "</div>"; // font-family close
    
    echo "<script>
            setTimeout(function(){
                window.location.href = '?offset=$nextOffset';
            }, $pauseInMs);
          </script>";

} catch (Exception $e) {
    echo "<h2 style='color:orange;'>Verbindungsproblem: " . $e->getMessage() . "</h2>";
    echo "<p>Versuche Neustart in 10 Sekunden...</p>";
    // Bei DB-Timeout: Gleichen Offset nochmal probieren
    echo "<script>setTimeout(function(){ window.location.href = '?offset=$offset'; }, 10000);</script>";
}