<?php
/**
 * Import-Skript: seaside_pas.sql → PaCIM SaaS
 *
 * Übernimmt Kunden, Events, Buchungen, Rechnungen, Rechnungspositionen
 * und Zahlungen aus einem alten PaCIM-Dump in die aktuelle Datenbank.
 *
 * Verwendung:
 *   php scripts/import-seaside.php [--sql=PATH] [--limit=N] [--dry-run] [--skip-restore] [--reset]
 *
 *   --sql=PATH       Pfad zur seaside_pas.sql (default: seaside_pas.sql im Projektroot)
 *   --limit=N        Max. Anzahl Kunden/Buchungen/Rechnungen pro Tabelle (für Tests, älteste zuerst)
 *   --latest=N       Nur die letzten N Einträge pro Quelltabelle importieren (neueste zuerst)
 *   --dry-run        Alles in einer Transaktion, am Ende ROLLBACK statt COMMIT
 *   --skip-restore   SQL nicht in pacim_import einspielen (wenn schon vorhanden)
 *   --reset          pacim_import VOR dem Restore droppen + neu anlegen
 *   --data-refresh   Bestehende Daten NICHT löschen; existierende Datensätze werden anhand
 *                    der Quell-ID (legacy_source_id) gefunden und aktualisiert, neue werden
 *                    ergänzt. Kind-Tabellen (booking_products, invoice_items, payments)
 *                    werden vor Insert auf Duplikate geprüft.
 *
 * Beispiele:
 *   php scripts/import-seaside.php --skip-restore --latest=500
 *   php scripts/import-seaside.php --skip-restore --latest=500 --dry-run
 *   php scripts/import-seaside.php --skip-restore --data-refresh
 *
 * Voraussetzung:
 *   - MySQL-Zugang in app/core/Database.php
 *   - Datenbank `pacim_import` darf erstellt werden (gleicher Server)
 */

if (PHP_SAPI !== 'cli') {
    fwrite(STDERR, "Nur als CLI ausführbar.\n");
    exit(1);
}

chdir(__DIR__ . '/..');
require_once __DIR__ . '/../app/core/Database.php';
require_once __DIR__ . '/../app/core/Normalize.php';

// --- Optionen ----------------------------------------------------------------
$opts = getopt('', ['sql::', 'limit::', 'latest::', 'dry-run', 'skip-restore', 'reset', 'data-refresh']);
$sqlFile     = (string)($opts['sql']   ?? __DIR__ . '/../seaside_pas.sql');
$limit       = isset($opts['limit'])   ? max(0, (int)$opts['limit'])  : 0;
$latest      = isset($opts['latest'])  ? max(0, (int)$opts['latest']) : 0;
$dryRun      = array_key_exists('dry-run', $opts);
$skipRestore = array_key_exists('skip-restore', $opts);
$reset       = array_key_exists('reset', $opts);
$dataRefresh = array_key_exists('data-refresh', $opts);
// "Letzte N pro Tabelle" hat Vorrang vor --limit und kippt die Sortierreihenfolge.
$sortDir = $latest > 0 ? 'DESC' : 'ASC';
$effectiveLimit = $latest > 0 ? $latest : $limit;

if (!file_exists($sqlFile)) {
    fwrite(STDERR, "SQL-Datei nicht gefunden: $sqlFile\n");
    exit(1);
}

$dst = (new Database())->getConnection();

// --- Quell-DB aus der gleichen MySQL-Instanz erreichen -----------------------
// Wir nutzen denselben Connection-String, aber mit DB-Switch via USE.
$srcDbName = 'pacim_import';

if ($reset) {
    echo "[reset] DROP DATABASE $srcDbName\n";
    $dst->exec("DROP DATABASE IF EXISTS `$srcDbName`");
}
$dst->exec("CREATE DATABASE IF NOT EXISTS `$srcDbName` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");

if (!$skipRestore) {
    echo "[restore] Lade $sqlFile in $srcDbName … (kann je nach Größe dauern)\n";
    // Wir nutzen das mysql-CLI für effizienten Restore.
    // --force: Funktions-/Trigger-Definitionsfehler ignorieren — wir importieren
    // anschließend nur Daten-Tabellen, nicht stored functions.
    $cmd = sprintf('mysql -uroot -proot --force %s < %s 2>&1',
        escapeshellarg($srcDbName), escapeshellarg($sqlFile));
    $output = []; $rc = 0;
    exec($cmd, $output, $rc);
    // Bei --force kann rc auch 0 sein, selbst wenn Warnungen kamen.
    // Verifizieren: existiert pacim_customer in der Ziel-DB?
    $check = $dst->query("SELECT COUNT(*) FROM information_schema.tables
                          WHERE table_schema = '$srcDbName' AND table_name = 'pacim_customer'")->fetchColumn();
    if (!$check) {
        fwrite(STDERR, "mysql-Restore fehlgeschlagen — pacim_customer wurde nicht angelegt.\n");
        fwrite(STDERR, "Output:\n" . implode("\n", array_slice($output, 0, 20)) . "\n");
        exit(2);
    }
    echo "  · Restore OK (".count($output)." Meldungen, davon evtl. ignorierte Function-Errors)\n";
}

// Separate PDO-Connection auf die Quell-DB
$src = new PDO('mysql:host=localhost;dbname=' . $srcDbName . ';charset=utf8mb4', 'root', 'root', [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
]);

// --- Helper ------------------------------------------------------------------
function lim(int $limit): string { return $limit > 0 ? " LIMIT $limit" : ''; }

/**
 * Trimt einen Wert. Liefert NULL, wenn der Wert leer ist oder genau "0" enthält.
 * Für Stammdaten-Felder gedacht (Adresse, Telefon, Mail).
 */
function cleanField(mixed $v): ?string
{
    $s = trim((string)$v);
    if ($s === '' || $s === '0') return null;
    return $s;
}

function pickEmail(array $row): ?string
{
    $e = trim((string)($row['customer_email'] ?? ''));
    if ($e === '' || $e === '0') return null;
    return filter_var($e, FILTER_VALIDATE_EMAIL) ? $e : null;
}

/**
 * Versucht aus customer_name einen Vor- und Nachnamen zu extrahieren.
 * Liefert [first, last] oder null, wenn nicht aufteilbar.
 */
function splitFullName(string $full): ?array
{
    $full = trim($full);
    if ($full === '' || $full === '0') return null;
    // Häufiges Muster "Nachname, Vorname"
    if (str_contains($full, ',')) {
        [$last, $first] = array_map('trim', explode(',', $full, 2));
        if ($first !== '' && $last !== '') return [$first, $last];
    }
    // Andernfalls am letzten Whitespace splitten — letztes Wort = Nachname.
    $parts = preg_split('/\s+/', $full);
    if (count($parts) < 2) return null;
    $last  = array_pop($parts);
    $first = implode(' ', $parts);
    if ($first === '' || $last === '') return null;
    return [$first, $last];
}

function mapPaymentMethod(int $code): string
{
    return match ($code) {
        1 => 'bank_transfer',
        2 => 'card',
        3 => 'cash',
        4 => 'other',
        default => 'other',
    };
}

/**
 * Liefert eine eindeutige Nummer, indem an die Original-Nummer so lange "0" angehängt
 * wird, bis sie noch nicht im &$used-Set steckt — und sie wird in $used aufgenommen.
 * Wenn die maximale Spaltenlänge erreicht ist, wird zum Anhängen vom Originalstamm
 * rechts gekürzt. Als letzte Sicherung kommt ein "-N"-Suffix.
 */
function dedupeNo(string $original, array &$used, int $maxLen, string $fallback): string
{
    $original = trim($original);
    if ($original === '') $original = $fallback;
    $candidate = substr($original, 0, $maxLen);
    $iter = 0;
    while (isset($used[$candidate])) {
        $iter++;
        if ($iter > $maxLen * 2) break;
        $tail = str_repeat('0', $iter);
        $room = max(1, $maxLen - strlen($tail));
        $candidate = substr($original, 0, $room) . $tail;
    }
    if (isset($used[$candidate])) {
        $i = 1;
        while (isset($used[$candidate]) && $i < 9999) {
            $suffix = '-' . $i;
            $candidate = substr($original, 0, $maxLen - strlen($suffix)) . $suffix;
            $i++;
        }
    }
    $used[$candidate] = true;
    return $candidate;
}

// --- Statistiken -------------------------------------------------------------
$stats = [
    'products'   => 0,
    'price_tiers'=> 0,
    'customers'  => 0,
    'customers_named_split' => 0,
    'customers_skipped_no_name' => 0,
    'events'     => 0,
    'bookings'   => 0,
    'invoices'   => 0,
    'items'      => 0,
    'payments'   => 0,
    'skipped'    => 0,
];

// In-Memory Mapping (alte ID → neue ID)
$mapCustomer = [];
$mapEvent    = [];
$mapInvoice  = []; // pacim_invoice.invoice_id → PaCIM invoices.id (erste Buchung)
$mapInvoiceFirstBooking = []; // pacim_invoice.invoice_id → first PaCIM booking.id
$aidaCustomers = []; // old customer_id => true (kommen via AIDA → bereits via bank_transfer bezahlt)
$usedBookingNos = []; // String-Set, wird in dedupeNo() gepflegt
$usedInvoiceNos = [];
$invoicePosProductCache = []; // invoice_id => [product_id, ...] (nur Produkte, die im neuen Schema existieren)

// --- 0a) Datenbank leeren (nur ohne --data-refresh) ---
//   Behalten: payment_methods (Zahlungsarten), users (Benutzer), settings (Systemeinstellungen).
//   TRUNCATE löst implizite COMMITs aus → muss VOR der Haupt-Transaktion laufen.
if (!$dataRefresh) {
    $tablesToWipe = [
        'payments',
        'invoice_items',
        'invoices',
        'recycled_invoice_numbers',
        'booking_products',
        'checkins',
        'messages',
        'pdf_documents',
        'daily_closings',
        'history_log',
        'bookings',
        'product_price_tiers',
        'products',
        'events',
        'organizers',
        'customers',
    ];
    echo "[wipe] Daten-Tabellen leeren (Zahlungsarten, Benutzer, Settings bleiben unangetastet)\n";
    $dst->exec('SET FOREIGN_KEY_CHECKS = 0');
    foreach ($tablesToWipe as $t) {
        $dst->exec("TRUNCATE TABLE `$t`");
    }
    $dst->exec('SET FOREIGN_KEY_CHECKS = 1');
} else {
    echo "[data-refresh] Bestehende Daten werden NICHT geleert — Upsert anhand legacy_source_id.\n";
}

// --- 0b) Produkte (außerhalb der Haupt-Transaktion, da SET/ALTER implizite COMMITs auslösen) ---
echo "[products] …\n";
$dst->exec('SET FOREIGN_KEY_CHECKS = 0');

$stmt = $src->query("SELECT * FROM pacim_products ORDER BY product_id ASC");
$insertProduct = $dst->prepare("INSERT INTO products
    (id, title, description, type, price_net, vat_rate, price_gross, pricing_mode, is_active)
    VALUES (:id, :ti, :de, 'parking', :pn, :vr, :pg, :pm, 1)
    ON DUPLICATE KEY UPDATE
        title=VALUES(title), description=VALUES(description), price_net=VALUES(price_net),
        vat_rate=VALUES(vat_rate), price_gross=VALUES(price_gross), pricing_mode=VALUES(pricing_mode)");
$importedProductIds = [];
$productVat = [];
// IDs, die Preisstaffelung nutzen sollen (Halle=1, Außen=2).
$tierProductIds = [1, 2];
foreach ($stmt as $p) {
    $pid  = (int)$p['product_id'];
    $net  = (float)$p['product_price'];
    $vat  = (int)$p['product_tax'] ?: 19;
    $gross = round($net * (1 + $vat / 100), 2);
    $insertProduct->execute([
        'id' => $pid,
        'ti' => $p['product_title'] ?: 'Produkt #' . $pid,
        'de' => $p['product_description'] ?? null,
        'pn' => $net,
        'vr' => $vat,
        'pg' => $gross,
        'pm' => in_array($pid, $tierProductIds, true) ? 'tier' : 'fixed',
    ]);
    $importedProductIds[$pid] = true;
    $productVat[$pid] = $vat;
    $stats['products'] = ($stats['products'] ?? 0) + 1;
}
echo "  · " . count($importedProductIds) . " Produkte importiert (IDs beibehalten)\n";

// --- Preisstaffelung aus pacim_parking_prices ------------------------------
// Nur Jahre ab 2026 übernehmen. Offene Tagesbereiche ("21+") → max_days=NULL und per_day=1.
echo "[price_tiers] …\n";
if ($dataRefresh) {
    // Tiers für die importierten Produkte neu setzen — alte raus, neue rein.
    $dst->exec('SET FOREIGN_KEY_CHECKS = 0');
    $dst->exec('DELETE FROM product_price_tiers WHERE valid_year >= 2026');
}
$tierStmt = $src->query("SELECT product_id, days_range, price, year FROM pacim_parking_prices
                         WHERE year >= 2026 ORDER BY product_id, year, price_id");
$insertTier = $dst->prepare("INSERT INTO product_price_tiers
    (product_id, valid_year, min_days, max_days, price_gross, per_day)
    VALUES (:pi, :vy, :mn, :mx, :pg, :pd)");
foreach ($tierStmt as $t) {
    $pid = (int)$t['product_id'];
    if (!isset($importedProductIds[$pid])) continue;
    $range = trim((string)$t['days_range']);
    if ($range === '') continue;
    $perDay = 0;
    if (preg_match('/^(\d+)\s*\+$/', $range, $m)) {
        $min = (int)$m[1]; $max = null;
        $perDay = 1; // offener Bereich → Preis gilt pro Tag
    } elseif (preg_match('/^(\d+)\s*-\s*(\d+)$/', $range, $m)) {
        $min = (int)$m[1]; $max = (int)$m[2];
    } else {
        // unbekanntes Muster — überspringen
        continue;
    }
    $net   = (float)$t['price'];
    $vat   = $productVat[$pid] ?? 19;
    $gross = round($net * (1 + $vat / 100), 2);
    $insertTier->execute([
        'pi' => $pid,
        'vy' => (int)$t['year'],
        'mn' => $min,
        'mx' => $max,
        'pg' => $gross,
        'pd' => $perDay,
    ]);
    $stats['price_tiers']++;
}
$dst->exec('SET FOREIGN_KEY_CHECKS = 1');
echo "  · {$stats['price_tiers']} Preisstaffel-Einträge importiert\n";

$dst->beginTransaction();
try {

    // --- 1) Kunden -----------------------------------------------------------
    echo "[customers] …\n";
    $existing = $dst->query("SELECT id, email, legacy_source_id FROM customers")->fetchAll();
    $byEmail = [];
    $bySourceId = [];
    foreach ($existing as $c) {
        if (!empty($c['email'])) $byEmail[strtolower($c['email'])] = (int)$c['id'];
        if (!empty($c['legacy_source_id'])) $bySourceId[(int)$c['legacy_source_id']] = (int)$c['id'];
    }

    $stmt = $src->query("SELECT * FROM pacim_customer ORDER BY customer_id $sortDir" . lim($effectiveLimit));
    $insertCust = $dst->prepare("INSERT INTO customers
        (company, first_name, last_name, street, zip, city, phone, email, status, source, legacy_source_id)
        VALUES (:co, :fn, :ln, :st, :zp, :ci, :ph, :em, 'active', :so, :lsi)");
    $updateCust = $dst->prepare("UPDATE customers SET
        company=:co, first_name=:fn, last_name=:ln, street=:st, zip=:zp, city=:ci,
        phone=:ph, email=:em, source=:so WHERE id=:id");
    $stats['customers_updated'] = 0;

    foreach ($stmt as $r) {
        $sourceId = (int)$r['customer_id'];

        // 1) Namen — wenn Vor-/Nachname leer, aus customer_name versuchen aufzuteilen.
        $first = trim((string)($r['customer_firstname'] ?? ''));
        $last  = trim((string)($r['customer_lastname']  ?? ''));
        if ($first === '' || $last === '') {
            $split = splitFullName((string)($r['customer_name'] ?? ''));
            if ($split) {
                [$first, $last] = $split;
                $stats['customers_named_split']++;
            }
        }
        if ($first === '' || $last === '') {
            $stats['customers_skipped_no_name']++;
            continue;
        }

        $email = pickEmail($r);
        $isAidaSrc = strcasecmp(trim((string)($r['customer_source'] ?? '')), 'AIDA') === 0;
        $payload = [
            'co' => cleanField($r['customer_company'] ?? null),
            'fn' => mb_substr($first, 0, 100),
            'ln' => mb_substr($last,  0, 100),
            'st' => cleanField($r['customer_street']   ?? null),
            'zp' => cleanField($r['customer_zipcode']  ?? null),
            'ci' => cleanField($r['customer_city']     ?? null),
            'ph' => cleanField($r['customer_phone']    ?? null),
            'em' => $email,
            'so' => cleanField($r['customer_source']   ?? null) ?? 'Import',
        ];

        // a) Im Refresh-Modus: über legacy_source_id den bestehenden Datensatz finden → UPDATE.
        if ($dataRefresh && isset($bySourceId[$sourceId])) {
            $nid = $bySourceId[$sourceId];
            $updateCust->execute($payload + ['id' => $nid]);
            $mapCustomer[$sourceId] = $nid;
            if ($email !== null) $byEmail[strtolower($email)] = $nid;
            if ($isAidaSrc) $aidaCustomers[$sourceId] = true;
            $stats['customers_updated']++;
            continue;
        }

        // b) E-Mail-Dedup (gegen bestehende oder zuvor im Lauf importierte Datensätze)
        if ($email !== null) {
            $key = strtolower($email);
            if (isset($byEmail[$key])) {
                $nid = $byEmail[$key];
                $mapCustomer[$sourceId] = $nid;
                // legacy_source_id nachtragen, damit künftige --data-refresh-Läufe ihn finden.
                $dst->prepare("UPDATE customers SET legacy_source_id = :lsi
                               WHERE id = :id AND legacy_source_id IS NULL")
                    ->execute(['lsi' => $sourceId, 'id' => $nid]);
                $bySourceId[$sourceId] = $nid;
                if ($isAidaSrc) $aidaCustomers[$sourceId] = true;
                $stats['skipped']++;
                continue;
            }
        }

        // c) Neu anlegen
        $insertCust->execute($payload + ['lsi' => $sourceId]);
        $nid = (int)$dst->lastInsertId();
        $mapCustomer[$sourceId] = $nid;
        $bySourceId[$sourceId] = $nid;
        if ($email !== null) $byEmail[strtolower($email)] = $nid;
        if ($isAidaSrc) $aidaCustomers[$sourceId] = true;
        $stats['customers']++;
    }
    echo "  · neu={$stats['customers']}  aktualisiert={$stats['customers_updated']}  bestehend übersprungen={$stats['skipped']}"
       . "  ohne_namen_skipped={$stats['customers_skipped_no_name']}"
       . "  name_split={$stats['customers_named_split']}\n";

    // --- 2) Events -----------------------------------------------------------
    echo "[events] …\n";
    $eventBySourceId = [];
    foreach ($dst->query("SELECT id, legacy_source_id FROM events WHERE legacy_source_id IS NOT NULL") as $row) {
        $eventBySourceId[(int)$row['legacy_source_id']] = (int)$row['id'];
    }

    // Veranstalter sicherstellen (idempotent) + ID-Map laden.
    $dst->exec("INSERT INTO organizers (name, status)
                VALUES ('AIDA Cruises','active'),('TUI Cruises','active'),('MSC Cruises','active')
                ON DUPLICATE KEY UPDATE status='active'");
    $organizerByName = [];
    foreach ($dst->query("SELECT id, name FROM organizers") as $row) {
        $organizerByName[$row['name']] = (int)$row['id'];
    }
    $organizerForShip = static function (string $ship) use ($organizerByName): ?int {
        $ship = trim($ship);
        if ($ship === '') return null;
        if (preg_match('/^AIDA/i', $ship))         return $organizerByName['AIDA Cruises'] ?? null;
        if (preg_match('/^Mein Schiff/i', $ship))  return $organizerByName['TUI Cruises']  ?? null;
        if (preg_match('/^MSC/i', $ship))          return $organizerByName['MSC Cruises']  ?? null;
        return null;
    };

    $stmt = $src->query("SELECT * FROM pacim_events ORDER BY event_calendar_id $sortDir" . lim($effectiveLimit));
    $insertEvent = $dst->prepare("INSERT INTO events
        (title, ship_name, terminal, organizer_id, starts_at, ends_at, arrival_from, arrival_to, status, legacy_source_id, created_at)
        VALUES (:t, :s, :tm, :oi, :sa, :ea, :af, :at, 'active', :lsi, NOW())");
    $updateEvent = $dst->prepare("UPDATE events SET
        title=:t, ship_name=:s, terminal=:tm, organizer_id=:oi, starts_at=:sa, ends_at=:ea,
        arrival_from=:af, arrival_to=:at WHERE id=:id");
    $stats['events_updated'] = 0;

    foreach ($stmt as $r) {
        $sourceId = (int)$r['event_calendar_id'];
        $shipName = $r['event_calendar_title'] ?: '';
        $payload = [
            't'  => $r['event_calendar_title'] ?: 'Event',
            's'  => $shipName,
            'tm' => 'Warnemünde',
            'oi' => $organizerForShip($shipName),
            'sa' => $r['event_calendar_begin'],
            'ea' => $r['event_calendar_end'],
            'af' => $r['event_calendar_begin'],
            'at' => $r['event_calendar_end'],
        ];
        if ($dataRefresh && isset($eventBySourceId[$sourceId])) {
            $nid = $eventBySourceId[$sourceId];
            $updateEvent->execute($payload + ['id' => $nid]);
            $mapEvent[$sourceId] = $nid;
            $stats['events_updated']++;
        } else {
            $insertEvent->execute($payload + ['lsi' => $sourceId]);
            $nid = (int)$dst->lastInsertId();
            $mapEvent[$sourceId] = $nid;
            $eventBySourceId[$sourceId] = $nid;
            $stats['events']++;
        }
    }
    echo "  · neu={$stats['events']}  aktualisiert={$stats['events_updated']}\n";

    // --- 3) Buchungen + Rechnungen + Items ----------------------------------
    echo "[bookings + invoices] …\n";
    $allBookings = $src->query("SELECT * FROM pacim_booking
                                ORDER BY booking_invoice $sortDir, booking_id $sortDir"
                                . lim($effectiveLimit ? $effectiveLimit * 2 : 0))->fetchAll();

    // bookings per invoice gruppieren
    $byInvoice = [];
    foreach ($allBookings as $b) {
        $byInvoice[(int)$b['booking_invoice']][] = $b;
    }

    // Invoice-Daten laden
    $invStmt = $src->query("SELECT * FROM pacim_invoice ORDER BY invoice_id $sortDir"
                            . lim($effectiveLimit ? $effectiveLimit * 2 : 0));
    $invoices = [];
    foreach ($invStmt as $row) $invoices[(int)$row['invoice_id']] = $row;

    $insertBooking = $dst->prepare("INSERT INTO bookings
        (booking_no, legacy_booking_no, legacy_source_id, customer_id, event_id, travel_from, travel_to, arrival_time, license_plate, normalized_license_plate,
         guest_count, total_net, total_vat, total_gross,
         discount_label, discount_amount, status, payment_status, checkin_status, notes, created_at, updated_at)
        VALUES (:bn, :lbn, :lsi, :cu, :ev, :tf, :tt, :at, :lp, :nlp,
                :gc, :tn, :tv, :tg, :dl, :da, :st, :ps, :cs, :no, :ca, :ua)");
    $updateBooking = $dst->prepare("UPDATE bookings SET
        customer_id=:cu, event_id=:ev, travel_from=:tf, travel_to=:tt, arrival_time=:at,
        license_plate=:lp, normalized_license_plate=:nlp, guest_count=:gc,
        total_net=:tn, total_vat=:tv, total_gross=:tg, discount_label=:dl, discount_amount=:da,
        status=:st, payment_status=:ps, checkin_status=:cs, notes=:no, updated_at=:ua
        WHERE id=:id");

    $insertItem = $dst->prepare("INSERT INTO booking_products
        (booking_id, product_id, quantity, price_net, vat_rate, price_gross, total_gross)
        VALUES (:bi, :pi, :q, :pn, :vr, :pg, :tg)");
    $checkItem = $dst->prepare("SELECT 1 FROM booking_products WHERE booking_id=:bi AND product_id=:pi LIMIT 1");

    $insertInvoice = $dst->prepare("INSERT INTO invoices
        (invoice_no, legacy_source_id, booking_id, customer_id, invoice_date, status, total_net, total_vat, total_gross)
        VALUES (:no, :lsi, :bi, :cu, :id, :st, :tn, :tv, :tg)");
    $updateInvoice = $dst->prepare("UPDATE invoices SET
        booking_id=:bi, customer_id=:cu, invoice_date=:id, status=:st,
        total_net=:tn, total_vat=:tv, total_gross=:tg
        WHERE id=:rid");

    $insertInvoiceItem = $dst->prepare("INSERT INTO invoice_items
        (invoice_id, product_id, title, description, quantity, price_net, vat_rate, price_gross, total_net, total_vat, total_gross)
        VALUES (:ii, :pi, :ti, :de, :q, :pn, :vr, :pg, :tn, :tv, :tg)");
    $deleteInvoiceItems = $dst->prepare("DELETE FROM invoice_items WHERE invoice_id = :ii");

    $insertPayment = $dst->prepare("INSERT INTO payments
        (booking_id, amount, payment_method, paid_at, note)
        VALUES (:bi, :am, :pm, :pa, :nt)");
    $checkPayment = $dst->prepare("SELECT 1 FROM payments
        WHERE booking_id=:bi AND amount=:am AND payment_method=:pm AND paid_at=:pa LIMIT 1");

    // --- Indexe für Refresh-Modus: bestehende Buchungen/Rechnungen nach legacy_source_id ---
    $bookingBySourceId = [];
    $invoiceBySourceId = [];
    if ($dataRefresh) {
        foreach ($dst->query("SELECT id, legacy_source_id, booking_no FROM bookings WHERE legacy_source_id IS NOT NULL") as $row) {
            $bookingBySourceId[(int)$row['legacy_source_id']] = ['id' => (int)$row['id'], 'no' => $row['booking_no']];
        }
        foreach ($dst->query("SELECT id, legacy_source_id, invoice_no FROM invoices WHERE legacy_source_id IS NOT NULL") as $row) {
            $invoiceBySourceId[(int)$row['legacy_source_id']] = ['id' => (int)$row['id'], 'no' => $row['invoice_no']];
        }
        // Vorhandene Nummern als "in Benutzung" markieren, damit dedupeNo bei neuen Datensätzen nicht kollidiert.
        foreach ($dst->query("SELECT booking_no FROM bookings") as $row) $usedBookingNos[$row['booking_no']] = true;
        foreach ($dst->query("SELECT invoice_no FROM invoices") as $row) $usedInvoiceNos[$row['invoice_no']] = true;
    }
    $stats['bookings_updated'] = 0;
    $stats['invoices_updated'] = 0;
    $stats['items_skipped']    = 0;
    $stats['payments_skipped'] = 0;

    // Produkt-Mapping aus pacim_products: 1=Halle, 2=Außen, 8=VALET (siehe pacim_products).
    $defaultProductHalle  = isset($importedProductIds[1]) ? 1 : null;
    $defaultProductAussen = isset($importedProductIds[2]) ? 2 : null;
    $defaultProductValet  = isset($importedProductIds[8]) ? 8 : null;

    foreach ($invoices as $invId => $inv) {
        $bks = $byInvoice[$invId] ?? [];
        $price = (float)$inv['invoice_price'];
        $isCancelled = (int)$inv['invoice_status'] === 3;
        $isPaid      = (int)$inv['invoice_status'] === 1;
        $coupon      = (int)$inv['invoice_coupon'];

        // Wenn keine Buchungen → kein Datensatz importieren (Hängende Invoice)
        if (empty($bks)) { continue; }

        $invoiceCustOldId = (int)($inv['invoice_customer'] ?? $bks[0]['booking_customer']);
        $custNewId = $mapCustomer[$invoiceCustOldId] ?? null;
        if (!$custNewId) {
            // Falls Kunde nicht gemappt (durch Limit übersprungen), skip
            continue;
        }
        $isAida = isset($aidaCustomers[$invoiceCustOldId]);

        // Pro pacim_booking eine PaCIM-Buchung anlegen
        $bookingPriceShare = round($price / max(1, count($bks)), 2);
        $netShare    = round($bookingPriceShare / 1.19, 2);
        $vatShare    = round($bookingPriceShare - $netShare, 2);

        $rabattLabel  = null;
        $rabattAmount = 0;
        if (!$isCancelled && $coupon === 1) {
            $rabattLabel  = '5 EUR Bestandskunde';
            $rabattAmount = 5.0;
        } elseif (!$isCancelled && $coupon === 2) {
            $rabattLabel  = '10% Frühbucherrabatt';
            $rabattAmount = round($bookingPriceShare * 0.1, 2);
        }
        $bookingGross = max(0, round($bookingPriceShare - $rabattAmount, 2));
        $bookingNet   = round($bookingGross / 1.19, 2);
        $bookingVat   = round($bookingGross - $bookingNet, 2);

        // Volle Rechnungspositionen für diese pacim_invoice cachen (product_id + Menge + Netto-Preis)
        // — daraus werden gleich pro Buchung die booking_products erzeugt.
        if (!isset($invoicePosProductCache[$invId])) {
            $posRowStmt = $src->prepare("SELECT product_id, invoice_pos_quantity, invoice_pos_price
                                          FROM pacim_invoice_pos
                                          WHERE invoice_id = :id AND product_id IS NOT NULL AND product_id > 0");
            $posRowStmt->execute(['id' => $invId]);
            $invoicePosProductCache[$invId] = array_values(array_filter(
                $posRowStmt->fetchAll(PDO::FETCH_ASSOC),
                fn ($r) => isset($importedProductIds[(int)$r['product_id']])
            ));
        }
        $invoicePosRows = $invoicePosProductCache[$invId];

        $firstBookingId = null;
        foreach ($bks as $b) {
            $evNew = $mapEvent[(int)($b['booking_event_id'] ?? 0)] ?? null;
            if (!$evNew) continue; // ohne Event geht nichts (FK)
            $bSourceId = (int)$b['booking_id'];
            $legacyBn  = trim((string)($b['booking_no'] ?? ''));

            // Anreisezeit: booking_begin (DATE) + booking_cruise_checkin (TIME) zu DATETIME zusammensetzen.
            // Wenn keine cruise_checkin-Zeit vorhanden → arrival_time bleibt NULL (kein Fallback).
            $arrivalAt = null;
            if ($b['booking_begin']) {
                $cruiseTime = trim((string)($b['booking_cruise_checkin'] ?? ''));
                if ($cruiseTime !== '' && $cruiseTime !== '00:00:00'
                    && preg_match('/^\d{1,2}:\d{2}(:\d{2})?$/', $cruiseTime)) {
                    if (strlen($cruiseTime) === 5) $cruiseTime .= ':00';
                    $arrivalAt = $b['booking_begin'] . ' ' . $cruiseTime;
                }
            }
            $createdAt = (!empty($b['booking_datetime']) && $b['booking_datetime'] !== '0000-00-00 00:00:00')
                ? $b['booking_datetime']
                : date('Y-m-d H:i:s');

            $commonPayload = [
                'cu'  => $custNewId,
                'ev'  => $evNew,
                'tf'  => $b['booking_begin'] ?: null,
                'tt'  => $b['booking_end']   ?: null,
                'at'  => $arrivalAt,
                'lp'  => $b['booking_serial'] ?? '',
                'nlp' => Normalize::licensePlate((string)($b['booking_serial'] ?? '')),
                'gc'  => max(1, min(255, (int)($b['booking_guests'] ?? 2))),
                'tn'  => $bookingNet,
                'tv'  => $bookingVat,
                'tg'  => $bookingGross,
                'dl'  => $rabattLabel,
                'da'  => $rabattAmount,
                'st'  => $isCancelled ? 'cancelled' : 'confirmed',
                'ps'  => ($isAida && !$isCancelled) ? 'paid' : ($isPaid ? 'paid' : 'open'),
                'cs'  => $b['booking_checkout'] ? 'checked_out' : ($b['booking_checkin'] ? 'checked_in' : 'pending'),
                'no'  => trim((string)($inv['invoice_description'] ?? '') . ' ' . (string)($b['booking_info'] ?? '')) ?: null,
                'ua'  => $createdAt,
            ];

            if ($dataRefresh && isset($bookingBySourceId[$bSourceId])) {
                // UPDATE bestehender Datensatz
                $newBookingId = $bookingBySourceId[$bSourceId]['id'];
                $updateBooking->execute($commonPayload + ['id' => $newBookingId]);
                $stats['bookings_updated']++;
            } else {
                // INSERT — booking_no aus Original übernehmen (mit Dedupe-Zero)
                $bn = dedupeNo($legacyBn !== '' ? $legacyBn : ('B-' . $bSourceId),
                               $usedBookingNos, 20, 'B-' . $bSourceId);
                $insertBooking->execute($commonPayload + [
                    'bn'  => $bn,
                    'lbn' => $legacyBn !== '' ? substr($legacyBn, 0, 64) : null,
                    'lsi' => $bSourceId,
                    'ca'  => $createdAt,
                ]);
                $newBookingId = (int)$dst->lastInsertId();
                $bookingBySourceId[$bSourceId] = ['id' => $newBookingId, 'no' => $bn];
                $stats['bookings']++;
            }
            $firstBookingId = $firstBookingId ?? $newBookingId;

            // Buchungs-Produkte:
            //   1) Bevorzugt: jede Zeile aus pacim_invoice_pos (passend zu booking_invoice)
            //      wird als eigenes booking_products-Item übernommen.
            //      net = invoice_pos_price, gross = net * 1.19, total = qty * gross.
            //   2) Fallback (keine pos-Zeilen): 1 Item aus booking_valet/booking_type ableiten.
            $itemsToInsert = [];
            if (!empty($invoicePosRows)) {
                foreach ($invoicePosRows as $pos) {
                    $pid       = (int)$pos['product_id'];
                    $qty       = max(1, (int)round((float)$pos['invoice_pos_quantity']));
                    $netUnit   = round((float)$pos['invoice_pos_price'], 2);
                    $grossUnit = round($netUnit * 1.19, 2);
                    $itemsToInsert[] = [
                        'pi' => $pid,
                        'q'  => $qty,
                        'pn' => $netUnit,
                        'pg' => $grossUnit,
                        'tg' => round($qty * $grossUnit, 2),
                    ];
                }
            } else {
                $isValetBk = (int)($b['booking_valet'] ?? 0) === 1;
                if ($isValetBk && $defaultProductValet) {
                    $fbPid = $defaultProductValet;
                } elseif ((int)($b['booking_type'] ?? 0) === 1 && $defaultProductHalle) {
                    $fbPid = $defaultProductHalle;
                } elseif ((int)($b['booking_type'] ?? 0) === 2 && $defaultProductAussen) {
                    $fbPid = $defaultProductAussen;
                } else {
                    $fbPid = $defaultProductAussen ?: $defaultProductHalle;
                }
                if ($fbPid) {
                    $itemsToInsert[] = [
                        'pi' => $fbPid,
                        'q'  => 1,
                        'pn' => round($bookingPriceShare / 1.19, 2),
                        'pg' => $bookingPriceShare,
                        'tg' => $bookingPriceShare,
                    ];
                }
            }

            foreach ($itemsToInsert as $it) {
                // Duplikat-Schutz: (booking_id, product_id) — auch beim Update prüfen.
                $checkItem->execute(['bi' => $newBookingId, 'pi' => $it['pi']]);
                if ($checkItem->fetchColumn()) {
                    $stats['items_skipped']++;
                    continue;
                }
                $insertItem->execute([
                    'bi' => $newBookingId,
                    'pi' => $it['pi'],
                    'q'  => $it['q'],
                    'pn' => $it['pn'],
                    'vr' => 19.0,
                    'pg' => $it['pg'],
                    'tg' => $it['tg'],
                ]);
                $stats['items']++;
            }

            // 0-€-Buchungen: Summen aus den booking_products neu berechnen.
            //   total_net   = SUM(quantity * price_net)
            //   total_gross = SUM(total_gross)
            //   total_vat   = total_gross - total_net
            $dst->prepare("
                UPDATE bookings b
                   SET total_net   = COALESCE((SELECT SUM(quantity * price_net) FROM booking_products WHERE booking_id = b.id), 0),
                       total_gross = COALESCE((SELECT SUM(total_gross)          FROM booking_products WHERE booking_id = b.id), 0),
                       total_vat   = COALESCE((SELECT SUM(total_gross) - SUM(quantity * price_net) FROM booking_products WHERE booking_id = b.id), 0)
                 WHERE b.id = :id AND b.total_gross = 0
            ")->execute(['id' => $newBookingId]);

            // AIDA-Kunden: synthetische Zahlung pro Buchung (Bank-Transfer, paid_at = Reisebeginn).
            if ($isAida && !$isCancelled && $bookingGross > 0) {
                $paidAt = ($b['booking_begin'] ?: date('Y-m-d')) . ' 00:00:00';
                $payExists = false;
                if ($dataRefresh) {
                    $checkPayment->execute(['bi' => $newBookingId, 'am' => $bookingGross,
                                             'pm' => 'bank_transfer', 'pa' => $paidAt]);
                    $payExists = (bool)$checkPayment->fetchColumn();
                }
                if ($payExists) {
                    $stats['payments_skipped']++;
                } else {
                    $insertPayment->execute([
                        'bi' => $newBookingId,
                        'am' => $bookingGross,
                        'pm' => 'bank_transfer',
                        'pa' => $paidAt,
                        'nt' => 'AIDA — automatisch erfasst (Import)',
                    ]);
                    $stats['payments']++;
                }
            }
        }

        // Rechnung anlegen (1× pro pacim_invoice — verlinkt mit dem ersten neuen Booking)
        if ($firstBookingId !== null && !empty($inv['invoice_no'])) {
            $invDate = $inv['invoice_date'] && $inv['invoice_date'] !== '0000-00-00'
                ? $inv['invoice_date']
                : ($inv['invoice_datetime'] && $inv['invoice_datetime'] !== '0000-00-00 00:00:00'
                    ? substr($inv['invoice_datetime'], 0, 10)
                    : date('Y-m-d'));
            $invNet   = round($price / 1.19, 2);
            $invVat   = round($price - $invNet, 2);
            $invGross = $price;

            if ($dataRefresh && isset($invoiceBySourceId[$invId])) {
                // UPDATE bestehende Rechnung; invoice_no bleibt erhalten.
                $newInvoiceId = $invoiceBySourceId[$invId]['id'];
                $updateInvoice->execute([
                    'bi' => $firstBookingId,
                    'cu' => $custNewId,
                    'id' => $invDate,
                    'st' => $isCancelled ? 'cancelled' : 'issued',
                    'tn' => $invNet,
                    'tv' => $invVat,
                    'tg' => $invGross,
                    'rid' => $newInvoiceId,
                ]);
                // Bestehende Items komplett ersetzen (sauberer als per-Item-Dedup)
                $deleteInvoiceItems->execute(['ii' => $newInvoiceId]);
                $mapInvoice[$invId] = $newInvoiceId;
                $mapInvoiceFirstBooking[$invId] = $firstBookingId;
                $stats['invoices_updated']++;
            } else {
                // INSERT — invoice_no aus Original übernehmen (mit Dedupe-Zero)
                $invNo = dedupeNo((string)$inv['invoice_no'], $usedInvoiceNos, 50, 'INV-' . $invId);
                $insertInvoice->execute([
                    'no'  => $invNo,
                    'lsi' => $invId,
                    'bi'  => $firstBookingId,
                    'cu'  => $custNewId,
                    'id'  => $invDate,
                    'st'  => $isCancelled ? 'cancelled' : 'issued',
                    'tn'  => $invNet,
                    'tv'  => $invVat,
                    'tg'  => $invGross,
                ]);
                $newInvoiceId = (int)$dst->lastInsertId();
                $invoiceBySourceId[$invId] = ['id' => $newInvoiceId, 'no' => $invNo];
                $mapInvoice[$invId] = $newInvoiceId;
                $mapInvoiceFirstBooking[$invId] = $firstBookingId;
                $stats['invoices']++;
            }
            {

                // Produkt-Fallback aus pacim_booking ableiten (erste Buchung):
                //   booking_valet=1 → VALET, booking_type=1 → Halle, booking_type=2 → Außen.
                $bFirst = $bks[0] ?? null;
                $fbPid = null;
                $fbTitle = 'Parkplatz';
                if ($bFirst) {
                    if ((int)($bFirst['booking_valet'] ?? 0) === 1 && $defaultProductValet) {
                        $fbPid = $defaultProductValet;  $fbTitle = 'VALET-Parkplatz';
                    } elseif ((int)($bFirst['booking_type'] ?? 0) === 1 && $defaultProductHalle) {
                        $fbPid = $defaultProductHalle;  $fbTitle = 'Halle-Parkplatz';
                    } elseif ((int)($bFirst['booking_type'] ?? 0) === 2 && $defaultProductAussen) {
                        $fbPid = $defaultProductAussen; $fbTitle = 'Außen-Parkplatz';
                    }
                }
                if ($fbPid === null) $fbPid = $defaultProductAussen ?: $defaultProductHalle;

                // Invoice-Items: aus pacim_invoice_pos
                $posStmt = $src->prepare("SELECT * FROM pacim_invoice_pos WHERE invoice_id = :id");
                $posStmt->execute(['id' => $invId]);
                $hasPos = false;
                foreach ($posStmt as $pos) {
                    $hasPos = true;
                    $qty = (float)$pos['invoice_pos_quantity'] ?: 1;
                    $netPerUnit = (float)$pos['invoice_pos_price'];
                    $grossPerUnit = round($netPerUnit * 1.19, 2);
                    $totalNet  = round($qty * $netPerUnit, 2);
                    $totalGr   = round($qty * $grossPerUnit, 2);
                    // product_id übernehmen, wenn im neuen products-Set vorhanden;
                    // sonst aus Buchung ableiten (Halle/Außen/VALET).
                    $oldPid = (int)($pos['product_id'] ?? 0);
                    $newPid = ($oldPid > 0 && isset($importedProductIds[$oldPid])) ? $oldPid : $fbPid;
                    $insertInvoiceItem->execute([
                        'ii' => $newInvoiceId,
                        'pi' => $newPid,
                        'ti' => $pos['invoice_pos_title']    ?: $fbTitle,
                        'de' => $pos['invoice_pos_description'] ?? '',
                        'q'  => (int)round($qty),
                        'pn' => $netPerUnit,
                        'vr' => 19.0,
                        'pg' => $grossPerUnit,
                        'tn' => $totalNet,
                        'tv' => round($totalGr - $totalNet, 2),
                        'tg' => $totalGr,
                    ]);
                }
                if (!$hasPos) {
                    // Fallback-Item: kompletter Rechnungsbetrag, Produkt aus Buchung abgeleitet.
                    $netT = round($price / 1.19, 2);
                    $insertInvoiceItem->execute([
                        'ii' => $newInvoiceId,
                        'pi' => $fbPid,
                        'ti' => $fbTitle,
                        'de' => $inv['invoice_description'] ?? null,
                        'q'  => 1,
                        'pn' => $netT,
                        'vr' => 19.0,
                        'pg' => $price,
                        'tn' => $netT,
                        'tv' => round($price - $netT, 2),
                        'tg' => $price,
                    ]);
                }

                // Rabatt-Posten
                if ($rabattAmount > 0) {
                    $rNet = round($rabattAmount / 1.19, 2);
                    $insertInvoiceItem->execute([
                        'ii' => $newInvoiceId,
                        'pi' => null,
                        'ti' => $rabattLabel,
                        'de' => 'Rabatt',
                        'q'  => 1,
                        'pn' => -$rNet,
                        'vr' => 19.0,
                        'pg' => -$rabattAmount,
                        'tn' => -$rNet,
                        'tv' => -round($rabattAmount - $rNet, 2),
                        'tg' => -$rabattAmount,
                    ]);
                    // Invoice-Total um Rabatt reduzieren
                    $dst->prepare("UPDATE invoices SET total_gross = total_gross - :a WHERE id = :id")
                        ->execute(['a' => $rabattAmount, 'id' => $newInvoiceId]);
                }

                // 0-€-Rechnungen: Summen aus den Items neu berechnen.
                $dst->prepare("
                    UPDATE invoices i
                       SET total_net   = COALESCE((SELECT SUM(total_net)   FROM invoice_items WHERE invoice_id = i.id), 0),
                           total_vat   = COALESCE((SELECT SUM(total_vat)   FROM invoice_items WHERE invoice_id = i.id), 0),
                           total_gross = COALESCE((SELECT SUM(total_gross) FROM invoice_items WHERE invoice_id = i.id), 0)
                     WHERE i.id = :id AND i.total_gross = 0
                ")->execute(['id' => $newInvoiceId]);
            }
        }

        // Zahlungen — AIDA-Kunden haben bereits synthetische Zahlungen pro Buchung erhalten.
        if ($firstBookingId !== null && !$isAida) {
            $payStmt = $src->prepare("SELECT * FROM pacim_payments WHERE payment_invoice = :id");
            $payStmt->execute(['id' => $invId]);
            foreach ($payStmt as $p) {
                $paAt = $p['payment_timestamp'] ?: ($p['payment_date'] . ' 00:00:00');
                $pm   = mapPaymentMethod((int)$p['payment_type']);
                $payExists = false;
                if ($dataRefresh) {
                    $checkPayment->execute(['bi' => $firstBookingId, 'am' => $p['payment_value'],
                                             'pm' => $pm, 'pa' => $paAt]);
                    $payExists = (bool)$checkPayment->fetchColumn();
                }
                if ($payExists) {
                    $stats['payments_skipped']++;
                } else {
                    $insertPayment->execute([
                        'bi' => $firstBookingId,
                        'am' => $p['payment_value'],
                        'pm' => $pm,
                        'pa' => $paAt,
                        'nt' => $p['payment_notice'] ?? null,
                    ]);
                    $stats['payments']++;
                }
            }
        }
    }
    echo "  · bookings={$stats['bookings']} (upd={$stats['bookings_updated']})"
       . "  invoices={$stats['invoices']} (upd={$stats['invoices_updated']})"
       . "  items={$stats['items']} (skip={$stats['items_skipped']})"
       . "  payments={$stats['payments']} (skip={$stats['payments_skipped']})\n";

    if ($dryRun) {
        echo "[dry-run] ROLLBACK\n";
        $dst->rollBack();
    } else {
        $dst->commit();
        echo "[done] COMMIT\n";
    }
} catch (Throwable $e) {
    $dst->rollBack();
    fwrite(STDERR, "FEHLER: " . $e->getMessage() . " @ " . $e->getFile() . ":" . $e->getLine() . "\n");
    exit(3);
}

echo "\nZusammenfassung:\n";
foreach ($stats as $k => $v) echo "  $k: $v\n";
echo "\nFertig.\n";