| Server IP : 202.61.199.114 / Your IP : 216.73.217.139 Web Server : nginx/1.22.1 System : Linux de.arni-solutions.de 6.1.0-49-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.174-1 (2026-05-26) x86_64 User : web20 ( 1018) PHP Version : 8.4.23 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : ON Directory : /var/www/seaside2.pacim.de/web/app/services/ |
Upload File : |
<?php
/**
* Globale + bereichsspezifische Suche mit toleranter Normalisierung
* und einfacher Fuzzy-Logik (LIKE + Levenshtein für kurze Eingaben).
*
* Liefert immer ein einheitliches Array:
* ['exact' => [...], 'suggestions' => [...]]
*
* Jeder Treffer:
* ['type' => bookings|customers|invoices|events,
* 'id' => int, 'title' => str, 'subtitle' => str,
* 'badge' => str, 'url' => str, 'score' => float]
*/
class SearchService
{
// Pro-Kategorie-Limit für die Live-Suche: vorher hart auf 8 gekappt,
// damit die alte Dropdown-Anzeige sauber bleibt. Das Mega-Search-Overlay
// hat eigenen Scrollbereich und zeigt deutlich mehr Treffer.
private const MAX_RESULTS = 100;
private const MIN_QUERY_LEN = 2;
private PDO $pdo;
/** @var list<string> Auszublendende customer.source-Werte */
private array $hiddenSources = [];
/** Wenn aktiv, sammelt $debugLog jede Section + die zugehörige SQL/Dauer. */
private bool $debug = false;
/** @var list<array{section:string, sql:string, params:array, duration_ms:float, rows:int}> */
private array $debugLog = [];
public function __construct(Database $db)
{
$this->pdo = $db->getConnection();
}
public function setHiddenSources(array $sources): void
{
$this->hiddenSources = array_values(array_filter(array_map('strval', $sources)));
}
public function enableDebug(): void
{
$this->debug = true;
$this->debugLog = [];
}
/**
* @return list<array{section:string, sql:string, params:array, duration_ms:float, rows:int}>
*/
public function getDebugLog(): array
{
return $this->debugLog;
}
/**
* Misst die Dauer eines Statements + zählt Ergebnis-Zeilen + schreibt einen
* Debug-Eintrag, wenn der Debug-Modus aktiv ist. Gibt das Statement-Resultset
* (oder einen beliebigen Producer-Rückgabewert) durch.
*
* $section: kurzes Label (z. B. "index.fulltext", "bookings.like")
* $producer: callable, die das Statement ausführt und PDOStatement/Array liefert.
*
* Trick: wir reichen SQL + Params nur durch (für die Modal-Anzeige im Frontend).
*/
private function trace(string $section, string $sql, array $params, callable $producer)
{
if (!$this->debug) {
return $producer();
}
$t0 = microtime(true);
$result = $producer();
$ms = (microtime(true) - $t0) * 1000;
$rows = is_array($result) ? count($result) : (is_int($result) ? $result : 0);
$this->debugLog[] = [
'section' => $section,
'sql' => preg_replace('/\s+/', ' ', trim($sql)) ?? $sql,
'params' => $params,
'duration_ms' => round($ms, 2),
'rows' => $rows,
];
return $result;
}
/**
* Kompakte Buchungsliste eines Kunden — wird in der Mega-Search im
* Detail-Pane angezeigt, damit man von einem Kunden-Treffer direkt zu
* einer seiner Buchungen springen kann (Cursor-/Maus-Navigation).
*
* Liefert die letzten N Buchungen (neueste zuerst, Stornos exklusive),
* inkl. Event-Titel und formatiertem Kennzeichen.
*
* @return list<array{id:int, booking_no:string, travel_from:?string, travel_to:?string,
* event_title:?string, license_plate:?string, status:?string, url:string,
* days:int}>
*/
private function customerBookings(int $customerId, int $limit = 10): array
{
$sql = "SELECT b.id, b.booking_no, b.travel_from, b.travel_to,
b.license_plate, b.status, b.payment_status, b.checkin_status,
b.total_gross, e.title AS event_title,
ci.checked_in_at, ci.checked_out_at
FROM bookings b
LEFT JOIN events e ON e.id = b.event_id
LEFT JOIN checkins ci ON ci.booking_id = b.id
WHERE b.customer_id = :cid
ORDER BY COALESCE(b.travel_from, b.created_at) DESC, b.id DESC
LIMIT " . max(1, $limit);
$stmt = $this->pdo->prepare($sql);
$stmt->execute(['cid' => $customerId]);
$out = [];
foreach ($stmt->fetchAll() as $r) {
$from = (string)($r['travel_from'] ?? '');
$to = (string)($r['travel_to'] ?? '');
$days = ($from && $to) ? max(0, (int)((strtotime($to) - strtotime($from)) / 86400)) : 0;
$plate = (string)($r['license_plate'] ?? '');
// Zentrale Status-Ableitung — eine einzige Anzeige pro Zeile.
// Priorität: storno > abgereist > angereist > bezahlt > offen.
$derived = match (true) {
($r['status'] ?? '') === 'cancelled' => 'cancelled',
!empty($r['checked_out_at']) => 'departed',
!empty($r['checked_in_at']) || ($r['checkin_status'] ?? '') === 'checked_in' => 'arrived',
($r['payment_status'] ?? '') === 'paid' => 'paid',
default => 'open',
};
$out[] = [
'id' => (int)$r['id'],
'booking_no' => (string)$r['booking_no'],
'travel_from' => $from !== '' ? $from : null,
'travel_to' => $to !== '' ? $to : null,
'days' => $days,
'event_title' => $r['event_title'] !== null ? (string)$r['event_title'] : null,
'license_plate' => $plate !== '' ? formatLicensePlateDE($plate) : null,
'status' => (string)($r['status'] ?? ''),
'derived_status' => $derived,
'total_gross' => (float)($r['total_gross'] ?? 0),
'url' => '/?page=bookings/view&id=' . (int)$r['id'],
];
}
return $out;
}
/** SQL-Fragment + Params, um Buchungen/Kunden mit gewählter Quelle auszublenden. */
private function hideSourcesClause(string $custAlias = 'c', string $prefix = 'hss_'): array
{
if (empty($this->hiddenSources)) return ['', []];
$phs = [];
$params = [];
foreach ($this->hiddenSources as $i => $s) {
$k = $prefix . $i;
$phs[] = ':' . $k;
$params[$k] = (string)$s;
}
$sql = " AND COALESCE({$custAlias}.source,'') NOT IN (" . implode(',', $phs) . ")";
return [$sql, $params];
}
/**
* Füllt fehlende `normalized_license_plate`-Werte aus `license_plate` nach.
* Läuft einmal pro PHP-Prozess (static-Flag) und ist idempotent — falls
* keine NULL-Zeilen existieren, ist die UPDATE leer und in MySQL trivial.
* Reparatur für Bestände, in denen Inserts an `normalized_license_plate`
* vorbei geschrieben haben (z.B. Altimporte vor dem mapBooking-Fix).
*/
private function backfillNormalizedPlates(): void
{
static $done = false;
if ($done) return;
$done = true;
try {
$this->pdo->exec(
"UPDATE bookings
SET normalized_license_plate = UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
COALESCE(license_plate, ''),
' ', ''), '-', ''), '.', ''), '/', ''), ',', ''), '_', ''), CHAR(9), ''))
WHERE license_plate IS NOT NULL
AND license_plate <> ''
AND (normalized_license_plate IS NULL OR normalized_license_plate = '')"
);
} catch (Throwable) { /* schlucken — Suche soll trotzdem laufen */ }
}
/**
* Schnelle Suche über die search_index-Tabelle (Migration 047).
* Stufen:
* 1) Exakt-Treffer auf search_key (Kennzeichen, Rechnungs-/Buchungsnummer)
* 2) FULLTEXT BOOLEAN mit Prefix-Wildcard auf search_blob
* 3) LIKE-Prefix auf normalized_blob (für sehr kurze Eingaben < 4 Zeichen)
*/
private function searchViaIndex(string $rawQuery, string $normalized, string $type): array
{
// Welche Entity-Typen sind erlaubt?
$allowedTypes = match ($type) {
'bookings', 'arrivals', 'license_plate' => ['booking'],
'customers' => ['customer'],
'invoices' => ['invoice'],
'events' => ['event'],
default => ['booking', 'customer', 'invoice', 'event'],
};
$typePh = [];
$params = [];
foreach ($allowedTypes as $i => $t) {
$k = 'et' . $i; $typePh[] = ':' . $k; $params[$k] = $t;
}
$typeClause = "entity_type IN (" . implode(',', $typePh) . ")";
$key = SearchIndexer::normalizeKey($rawQuery);
$hits = [];
// (1) Exakt-/Prefix-Match auf search_key.
if ($key !== '' && strlen($key) >= 2) {
try {
$sql = "SELECT entity_type, entity_id, customer_id, title, subtitle, search_key, boost, url, row_meta
FROM search_index
WHERE {$typeClause}
AND search_key LIKE :key
ORDER BY (search_key = :keyEq) DESC, boost DESC
LIMIT 100";
$bindParams = $params + ['key' => $key . '%', 'keyEq' => $key];
$rows = $this->trace('index.search_key', $sql, $bindParams, function () use ($sql, $bindParams) {
$stmt = $this->pdo->prepare($sql);
$stmt->execute($bindParams);
return $stmt->fetchAll();
});
foreach ($rows as $r) {
$hits[$r['entity_type'] . ':' . $r['entity_id']] = $this->indexRowToHit($r, 200);
}
} catch (Throwable $e) {
// search_index existiert nicht (Migration 047 noch nicht eingespielt) — Fallback.
return [];
}
}
// (2) FULLTEXT BOOLEAN MODE — Wörter ≥ 3 Zeichen kriegen einen *-Wildcard.
$tokens = preg_split('/\s+/', trim($rawQuery)) ?: [];
$ftTerms = [];
foreach ($tokens as $t) {
$t = preg_replace('/[+\-<>~*"@()]/', '', $t) ?? '';
if (mb_strlen($t) >= 3) $ftTerms[] = '+' . $t . '*';
elseif (mb_strlen($t) >= 2) $ftTerms[] = '+' . $t;
}
if (!empty($ftTerms)) {
try {
$sql = "SELECT entity_type, entity_id, title, subtitle, search_key, boost, url,
MATCH(search_blob) AGAINST(:q IN BOOLEAN MODE) AS relevance
FROM search_index
WHERE {$typeClause}
AND MATCH(search_blob) AGAINST(:q IN BOOLEAN MODE)
ORDER BY relevance DESC, boost DESC
LIMIT 100";
$bindParams = $params + ['q' => implode(' ', $ftTerms)];
$rows = $this->trace('index.fulltext', $sql, $bindParams, function () use ($sql, $bindParams) {
$stmt = $this->pdo->prepare($sql);
$stmt->execute($bindParams);
return $stmt->fetchAll();
});
foreach ($rows as $r) {
$key2 = $r['entity_type'] . ':' . $r['entity_id'];
if (isset($hits[$key2])) continue;
$hits[$key2] = $this->indexRowToHit($r, (float)$r['relevance'] * 10);
}
} catch (Throwable) { /* falls FULLTEXT noch nicht da → next stage */ }
}
// (3) Token-Suche (Migration 051) — sargable Prefix-Suche pro Wort.
// Lookup auch bei 550k × 6 Tokens unter ~20 ms (range-scan auf prefix(24)).
// Multi-Token: jedes Token gibt einen Self-JOIN auf search_tokens → AND-Semantik.
// Beispiel "HH AB 1234" → Tokens [hh, ab, 1234] → JOIN t0×t1×t2 auf gleiche entity.
//
// WICHTIG: tokenize über normalizeBlob (lowercase + space-erhaltend), NICHT
// über $normalized (das ist Normalize::searchText → UPPERCASE ohne Leerzeichen).
if (count($hits) < 50 && trim($rawQuery) !== '') {
// Tokenize über normalizeBlob (lowercase + space-erhaltend).
// "HH AB 1234" → "hh ab 1234" → 3 Tokens, alle ≥ 2 Zeichen.
// "HHAB1234" → "hhab1234" → 1 Token (exakte Kennzeichen-Eingabe).
$tokenized = SearchIndexer::normalizeBlob($rawQuery);
$needleTokens = [];
foreach (preg_split('/\s+/', $tokenized) ?: [] as $t) {
if (strlen($t) >= 2) $needleTokens[] = $t;
}
if (!empty($needleTokens)) {
try {
$joins = '';
$wheres = ['t0.' . $typeClause]; // Typ-Filter nur einmal nötig
$tokParams = $params;
foreach ($needleTokens as $i => $t) {
$tokParams['tk' . $i] = $t . '%';
if ($i === 0) {
$wheres[] = "t0.token LIKE :tk0";
} else {
$joins .= " INNER JOIN search_tokens t{$i}
ON t{$i}.entity_type = t0.entity_type
AND t{$i}.entity_id = t0.entity_id
AND t{$i}.token LIKE :tk{$i}";
}
}
$sql = "SELECT DISTINCT si.entity_type, si.entity_id, si.customer_id, si.title, si.subtitle, si.search_key, si.boost, si.url, si.row_meta
FROM search_tokens t0
{$joins}
INNER JOIN search_index si
ON si.entity_type = t0.entity_type
AND si.entity_id = t0.entity_id
WHERE " . implode(' AND ', $wheres) . "
ORDER BY si.boost DESC
LIMIT 100";
$rows = $this->trace('index.tokens', $sql, $tokParams, function () use ($sql, $tokParams) {
$stmt = $this->pdo->prepare($sql);
$stmt->execute($tokParams);
return $stmt->fetchAll();
});
foreach ($rows as $r) {
$key2 = $r['entity_type'] . ':' . $r['entity_id'];
if (isset($hits[$key2])) continue;
$hits[$key2] = $this->indexRowToHit($r, 10);
}
} catch (Throwable $e) {
// search_tokens evtl. noch nicht angelegt → ignorieren, normalized_like fängt's ab.
error_log('search tokens lookup failed: ' . $e->getMessage());
}
}
}
// (4) Prefix-LIKE auf normalized_blob — letzter Fallback (nur Treffer
// am ANFANG des Blobs). Bewusst nur 'needle%' (sargable).
if (count($hits) < 20 && $normalized !== '' && strlen($normalized) >= 2) {
try {
$needle = str_replace(' ', '', $normalized);
$sql = "SELECT entity_type, entity_id, customer_id, title, subtitle, search_key, boost, url, row_meta
FROM search_index
WHERE {$typeClause}
AND normalized_blob LIKE :npStart
ORDER BY boost DESC
LIMIT 50";
$bindParams = $params + ['npStart' => $needle . '%'];
$rows = $this->trace('index.normalized_like', $sql, $bindParams, function () use ($sql, $bindParams) {
$stmt = $this->pdo->prepare($sql);
$stmt->execute($bindParams);
return $stmt->fetchAll();
});
foreach ($rows as $r) {
$key2 = $r['entity_type'] . ':' . $r['entity_id'];
if (isset($hits[$key2])) continue;
$hits[$key2] = $this->indexRowToHit($r, 5);
}
} catch (Throwable) { /* schlucken */ }
}
return array_values($hits);
}
private function indexRowToHit(array $row, float $score): array
{
$typeMap = [
'booking' => 'bookings',
'customer' => 'customers',
'invoice' => 'invoices',
'event' => 'events',
];
// Badge-Label für die Trefferzeile (rechte Spalte).
$badgeMap = [
'booking' => 'Buchung',
'customer' => 'Kunde',
'invoice' => 'Rechnung',
'event' => 'Event',
];
$title = (string)($row['title'] ?? '');
$subtitle = (string)($row['subtitle'] ?? '');
// row_meta enthält die Render-Felder (Plate, Name, Event-Titel, …);
// Detail-Pane lädt zusätzlich api/live-search/detail nach.
$meta = [];
if (!empty($row['row_meta'])) {
$decoded = json_decode((string)$row['row_meta'], true);
if (is_array($decoded)) $meta = $decoded;
}
// Fallback für Bestand vor Migration 052: row_meta ist NULL, aber title/subtitle
// existieren — daraus minimales Meta für die Treffer-Liste rekonstruieren.
if (empty($meta) && ($title !== '' || $subtitle !== '')) {
$meta = self::reconstructMetaFromTitle($row['entity_type'], $title, $subtitle);
}
// customer_id IMMER aus der dedizierten Spalte (Migration 054) übernehmen —
// zuverlässiger als JSON-Parsing aus row_meta. Wird für Dedup genutzt.
if (isset($row['customer_id']) && $row['customer_id'] !== null) {
$meta['customer_id'] = (int)$row['customer_id'];
}
return [
'type' => $typeMap[$row['entity_type']] ?? $row['entity_type'],
'id' => (int)$row['entity_id'],
'title' => $title,
'subtitle' => $subtitle,
'badge' => $badgeMap[$row['entity_type']] ?? '',
'url' => (string)$row['url'],
'score' => $score + (int)($row['boost'] ?? 0),
'meta' => $meta,
];
}
/**
* Wenn der Suchtreffer einen Kunden enthält, dessen ID auch über die
* Booking-Treffer in der Liste auftaucht, werden die Bookings entfernt —
* der User sieht sie dann nur noch rechts in der Detail-Pane des Kunden
* (api/live-search/detail liefert die Buchungs-Liste pro Kunde).
*
* @param list<array> $hits
* @return list<array>
*/
private static function dedupeBookingsWhenCustomerHit(array $hits): array
{
$customerIds = [];
foreach ($hits as $h) {
if (($h['type'] ?? '') === 'customers') {
$cid = (int)($h['id'] ?? 0);
if ($cid > 0) $customerIds[$cid] = true;
}
}
if (empty($customerIds)) return $hits;
$filtered = [];
foreach ($hits as $h) {
if (($h['type'] ?? '') === 'bookings') {
$bookingCustomerId = (int)($h['meta']['customer_id'] ?? 0);
if ($bookingCustomerId > 0 && isset($customerIds[$bookingCustomerId])) {
// Buchung wird über den Kunden-Treffer mit angezeigt.
continue;
}
}
$filtered[] = $h;
}
return $filtered;
}
/**
* Notfall-Rekonstruktion: baut aus den im search_index ohnehin vorhandenen
* Feldern title + subtitle ein minimales meta-Array zusammen.
* Wird nur genutzt, wenn row_meta noch NULL ist (Bestand vor Migration 052,
* vor dem nächsten Rebuild). Sobald der Rebuild gelaufen ist, kommt das
* vollständige row_meta direkt aus der DB.
*/
private static function reconstructMetaFromTitle(string $entityType, string $title, string $subtitle): array
{
$titleParts = array_map('trim', explode(' · ', $title));
$subParts = array_map('trim', explode(' · ', $subtitle));
switch ($entityType) {
case 'booking':
// Title: "BOOKINGNO · HH AB 1234"
// Subtitle: "Name · Event-Titel · Anreise YYYY-MM-DD"
return [
'booking_no' => $titleParts[0] ?? '',
'plate' => $titleParts[1] ?? '',
'name' => $subParts[0] ?? '',
'event' => $subParts[1] ?? '',
'event_title' => $subParts[1] ?? '',
];
case 'customer':
// Title: "Name · Firma"
// Subtitle: "Firma · Stadt · Email · Phone"
return [
'name' => $titleParts[0] ?? '',
'company' => $titleParts[1] ?? '',
'contact' => $subtitle,
];
case 'invoice':
return [
'invoice_no' => $title,
'name' => $subParts[0] ?? '',
'booking' => $subParts[1] ?? '',
];
case 'event':
return [
'title' => $title,
'ship' => $subParts[0] ?? '',
'terminal' => $subParts[1] ?? '',
];
}
return [];
}
/**
* Tippfehler-tolerante Suche über search_tokens + Levenshtein.
*
* Algorithmus:
* 1) Aus der normalisierten Eingabe das erste Wort-Token extrahieren.
* 2) Mit dem ersten 2-Zeichen-Präfix die candidate-Tokens aus search_tokens ziehen
* (sargable via idx_token_prefix → range scan, < 30 ms auch bei 550k Datensätzen × 6 Tokens).
* 3) Pro Kandidaten-Token Levenshtein-Distanz berechnen; Distanz ≤ 2 (bei query ≥ 4 Zeichen)
* bzw. ≤ 1 (bei kürzeren) wird als Match gewertet.
* 4) Für die N besten entity_ids den vollen Row-Datensatz aus search_index holen.
*
* Beispiel: "gieslinde" → Präfix "gi" → Kandidat "gislinde" (Distanz 1) → Match.
*/
private function fuzzyViaTokens(string $rawQuery, string $normalized, string $type): array
{
$needle = SearchIndexer::normalizeBlob($rawQuery);
$first = preg_split('/\s+/', $needle)[0] ?? '';
if (strlen($first) < 3) return []; // Zu kurz für Fuzzy — Trefferdichte sonst zu hoch.
$prefix = mb_substr($first, 0, 2);
$maxDistance = mb_strlen($first) >= 5 ? 2 : 1;
$allowedTypes = match ($type) {
'bookings', 'arrivals', 'license_plate' => ['booking'],
'customers' => ['customer'],
'invoices' => ['invoice'],
'events' => ['event'],
default => ['booking', 'customer', 'invoice', 'event'],
};
$typePh = [];
$params = [];
foreach ($allowedTypes as $i => $t) {
$k = 'fet' . $i; $typePh[] = ':' . $k; $params[$k] = $t;
}
try {
$sql = "SELECT DISTINCT token FROM search_tokens
WHERE entity_type IN (" . implode(',', $typePh) . ")
AND token LIKE :pf
LIMIT 3000";
$params['pf'] = $prefix . '%';
$candidateTokens = $this->trace('index.fuzzy_prefix', $sql, $params, function () use ($sql, $params) {
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll(PDO::FETCH_COLUMN);
});
} catch (Throwable) { return []; }
if (empty($candidateTokens)) return [];
// PHP-seitig: Levenshtein-Distanz, top-Kandidaten halten.
$scored = [];
foreach ($candidateTokens as $tok) {
$d = Normalize::distance($first, (string)$tok);
if ($d > $maxDistance) continue;
$scored[(string)$tok] = $d;
}
if (empty($scored)) return [];
asort($scored); // nach Distanz sortieren
$bestTokens = array_slice(array_keys($scored), 0, 20);
// Treffer-Entitäten zu diesen Tokens auflösen + Row-Daten aus search_index.
try {
$tokPh = [];
$params2 = [];
foreach ($bestTokens as $i => $t) {
$k = 'fbt' . $i; $tokPh[] = ':' . $k; $params2[$k] = $t;
}
$typePh2 = [];
foreach ($allowedTypes as $i => $t) {
$k = 'fet2_' . $i; $typePh2[] = ':' . $k; $params2[$k] = $t;
}
$sql2 = "SELECT DISTINCT si.entity_type, si.entity_id, si.customer_id,
si.title, si.subtitle, si.search_key, si.boost, si.url, si.row_meta
FROM search_tokens st
INNER JOIN search_index si
ON si.entity_type = st.entity_type
AND si.entity_id = st.entity_id
WHERE st.entity_type IN (" . implode(',', $typePh2) . ")
AND st.token IN (" . implode(',', $tokPh) . ")
ORDER BY si.boost DESC
LIMIT 50";
$rows = $this->trace('index.fuzzy_resolve', $sql2, $params2, function () use ($sql2, $params2) {
$stmt = $this->pdo->prepare($sql2);
$stmt->execute($params2);
return $stmt->fetchAll();
});
} catch (Throwable) { return []; }
$hits = [];
foreach ($rows as $r) {
$hits[] = $this->indexRowToHit($r, 50); // niedriger als exact-Match-Score
}
return $hits;
}
public function search(string $rawQuery, string $type = 'global', int $pastDays = 0): array
{
$query = trim($rawQuery);
if (mb_strlen($query) < self::MIN_QUERY_LEN) {
return ['exact' => [], 'suggestions' => [], 'normalized' => ''];
}
// Selbstheilung: einmal pro Request fehlende normalized_license_plate
// aus license_plate ableiten. Sonst findet die Live-Suche importierte
// Buchungen mit NULL-Normalwert nicht.
$this->backfillNormalizedPlates();
$normalized = Normalize::searchText($query);
// ── Schnellpfad: search_index-Tabelle (Migration 047). Liefert auf 550k
// Datensätzen Treffer in ~10-50 ms via FULLTEXT + key-Lookup.
// Sobald wir AUCH NUR EINEN Treffer aus dem Index haben, brechen wir hier ab —
// sonst läuft anschließend ein langsamer 4×LIKE-Fallback (Customers, Bookings,
// Invoices, Events), der bei 550k Datensätzen Sekunden braucht.
// Fallback auf die Legacy-LIKE-Pfade nur, wenn der Index NICHTS liefert
// (z.B. weil Migration 047 noch nicht eingespielt ist).
$indexHits = $this->searchViaIndex($query, $normalized, $type);
if (!empty($indexHits)) {
$indexHits = self::dedupeBookingsWhenCustomerHit($indexHits);
usort($indexHits, fn($a, $b) => $b['score'] <=> $a['score']);
return [
'exact' => array_slice($indexHits, 0, self::MAX_RESULTS),
'suggestions' => [],
'normalized' => $normalized,
];
}
// ── Fuzzy-Schnellpfad: Levenshtein-Ranking auf search_tokens.
// Greift NUR wenn der exakte Index-Pfad nichts liefert (z.B. Tippfehler
// wie "gieslinde" → "gislinde"). Sucht alle Tokens mit gemeinsamem 2-Zeichen-Präfix,
// rankt sie nach Levenshtein-Distanz und löst die top-N entity_ids
// direkt aus search_index auf.
$fuzzyHits = $this->fuzzyViaTokens($query, $normalized, $type);
if (!empty($fuzzyHits)) {
$fuzzyHits = self::dedupeBookingsWhenCustomerHit($fuzzyHits);
usort($fuzzyHits, fn($a, $b) => $b['score'] <=> $a['score']);
return [
'exact' => array_slice($fuzzyHits, 0, self::MAX_RESULTS),
'suggestions' => [],
'normalized' => $normalized,
];
}
$like = '%' . $query . '%';
$likeNorm = '%' . $normalized . '%';
$exact = [];
$suggestions = [];
$do = match ($type) {
'bookings', 'arrivals', 'license_plate' => ['bookings'],
'customers' => ['customers'],
'invoices' => ['invoices'],
'events' => ['events'],
default => ['bookings', 'customers', 'invoices', 'events'],
};
if (in_array('bookings', $do, true)) {
foreach ($this->searchBookings($like, $likeNorm, $normalized) as $b) {
$exact[] = $b;
}
}
if (in_array('customers', $do, true)) {
foreach ($this->searchCustomers($like) as $c) {
$exact[] = $c;
}
}
if (in_array('invoices', $do, true)) {
foreach ($this->searchInvoices($like) as $i) {
$exact[] = $i;
}
}
if (in_array('events', $do, true)) {
foreach ($this->searchEvents($query, $pastDays) as $e) {
$exact[] = $e;
}
}
// Wenn der Suchbegriff einen Kunden trifft, sollen seine Buchungen NICHT
// separat in der Liste auftauchen — der User sieht sie rechts im Detail.
$exact = self::dedupeBookingsWhenCustomerHit($exact);
usort($exact, fn($a, $b) => $b['score'] <=> $a['score']);
$exact = array_slice($exact, 0, self::MAX_RESULTS);
// Suggestions: bei kurzen Queries (≤ 16 Zeichen) IMMER fuzzy mitliefern.
// Wenn keine exakten Treffer → suggestions ersetzen die Ergebnisse.
// Wenn exakte Treffer da sind → suggestions erscheinen oben als „Meintest du"-Chips
// (nur, wenn sie nicht ohnehin schon in `exact` vorkommen).
if (strlen($normalized) <= 16) {
$rawSugg = $this->fuzzy($normalized, $do);
// Spezielle Kennzeichen-Fuzzy: deckt Tippfehler/fehlende Zeichen
// (z. B. „HROA42" → „HROKA342") über SQL-Präfix-Filter ab.
// Skaliert auch auf große Bestände — keine 200-Zeilen-Bremse.
if (in_array('bookings', $do, true) && strlen($normalized) >= 3) {
foreach ($this->fuzzyPlates($normalized) as $p) {
$rawSugg[] = $p;
}
}
if (!empty($exact)) {
$exactKeys = [];
foreach ($exact as $e) $exactKeys[$e['type'] . ':' . $e['id']] = true;
$suggestions = [];
$seen = [];
foreach ($rawSugg as $s) {
$key = $s['type'] . ':' . $s['id'];
if (isset($exactKeys[$key]) || isset($seen[$key])) continue;
$seen[$key] = true;
$suggestions[] = $s;
}
$suggestions = array_slice($suggestions, 0, 6);
} else {
// Auch ohne exact: dedupe innerhalb der suggestions.
$seen = [];
$suggestions = [];
foreach ($rawSugg as $s) {
$key = $s['type'] . ':' . $s['id'];
if (isset($seen[$key])) continue;
$seen[$key] = true;
$suggestions[] = $s;
}
}
}
return [
'exact' => $exact,
'suggestions' => $suggestions,
'normalized' => $normalized,
];
}
/**
* Lädt die volle „meta"-Information für einen einzelnen Treffer (für die Lazy-Detail-Pane).
* Wird vom Endpoint /?page=api/live-search/detail aufgerufen, sobald der Cursor/Mouse
* über einer Zeile landet. So müssen wir die Sub-Queries (customerBookings,
* product_ids, payments, …) nicht für ALLE 100 Treffer ziehen, sondern nur für den
* aktiv fokussierten.
*
* @return array|null Meta-Array oder null, wenn nicht gefunden.
*/
public function getDetail(string $type, int $id): ?array
{
if ($id <= 0) return null;
return match ($type) {
'bookings' => $this->detailForBooking($id),
'customers' => $this->detailForCustomer($id),
'invoices' => $this->detailForInvoice($id),
'events' => $this->detailForEvent($id),
default => null,
};
}
private function detailForBooking(int $id): ?array
{
$sql = "SELECT b.id, b.booking_no, b.license_plate, b.normalized_license_plate,
b.travel_from, b.travel_to, b.arrival_time, b.guest_count,
b.status, b.payment_status, b.checkin_status, b.event_id,
c.first_name, c.last_name, c.company, c.email, c.phone,
e.title AS event_title, e.ship_name,
(SELECT GROUP_CONCAT(product_id ORDER BY product_id)
FROM booking_products WHERE booking_id = b.id) AS product_ids,
(SELECT checked_in_at FROM checkins WHERE booking_id = b.id ORDER BY id DESC LIMIT 1) AS chk_in_at,
(SELECT checked_out_at FROM checkins WHERE booking_id = b.id ORDER BY id DESC LIMIT 1) AS chk_out_at
FROM bookings b
INNER JOIN customers c ON c.id = b.customer_id
INNER JOIN events e ON e.id = b.event_id
WHERE b.id = :id LIMIT 1";
$stmt = $this->pdo->prepare($sql);
$stmt->execute(['id' => $id]);
$r = $stmt->fetch();
if (!$r) return null;
$name = trim($r['first_name'] . ' ' . $r['last_name']);
$plate = formatLicensePlateDE($r['license_plate'] ?? '');
return [
'plate' => $plate,
'name' => $name,
'company' => $r['company'] ?? '',
'email' => $r['email'] ?? '',
'phone' => $r['phone'] ?? '',
'travel' => self::formatTravelPeriod($r),
'travel_from' => $r['travel_from'] ?? '',
'travel_to' => $r['travel_to'] ?? '',
'arrival_time' => $r['arrival_time'] ?? '',
'guest_count' => (int)($r['guest_count'] ?? 0),
'event_id' => (int)$r['event_id'],
'event_title' => (string)($r['event_title'] ?? ''),
'event' => trim(($r['event_title'] ?? '') . ($r['ship_name'] ? ' · ' . $r['ship_name'] : '')),
'booking_no' => $r['booking_no'],
'status' => $r['status'] ?? '',
'payment_status' => $r['payment_status'] ?? '',
'checkin_status' => $r['checkin_status'] ?? '',
'checked_in_at' => $r['chk_in_at'] ?? '',
'checked_out_at' => $r['chk_out_at'] ?? '',
'product_ids' => (string)($r['product_ids'] ?? ''),
];
}
private function detailForCustomer(int $id): ?array
{
$stmt = $this->pdo->prepare(
"SELECT id, first_name, last_name, company, email, phone, city, zip
FROM customers WHERE id = :id LIMIT 1"
);
$stmt->execute(['id' => $id]);
$r = $stmt->fetch();
if (!$r) return null;
$name = trim($r['first_name'] . ' ' . $r['last_name']);
return [
'name' => $name,
'first_name' => $r['first_name'] ?? '',
'last_name' => $r['last_name'] ?? '',
'company' => $r['company'] ?? '',
'email' => $r['email'] ?? '',
'phone' => $r['phone'] ?? '',
'contact' => trim(($r['email'] ?? '') . ($r['phone'] ? ' · ' . $r['phone'] : '')),
'address' => trim(($r['zip'] ?? '') . ' ' . ($r['city'] ?? '')),
'bookings' => $this->customerBookings($id),
];
}
private function detailForInvoice(int $id): ?array
{
$stmt = $this->pdo->prepare(
"SELECT i.id, i.invoice_no, i.invoice_date, i.total_gross, i.status,
c.first_name, c.last_name, c.company,
b.booking_no
FROM invoices i
INNER JOIN customers c ON c.id = i.customer_id
INNER JOIN bookings b ON b.id = i.booking_id
WHERE i.id = :id LIMIT 1"
);
$stmt->execute(['id' => $id]);
$r = $stmt->fetch();
if (!$r) return null;
$name = trim($r['first_name'] . ' ' . $r['last_name']);
return [
'invoice_no' => $r['invoice_no'],
'name' => $name . ($r['company'] ? ' · ' . $r['company'] : ''),
'booking' => 'Buchung ' . $r['booking_no'],
'amount' => formatCurrency((float)$r['total_gross']),
'status' => statusLabel($r['status']),
];
}
private function detailForEvent(int $id): ?array
{
$stmt = $this->pdo->prepare(
"SELECT id, title, ship_name, terminal, starts_at, ends_at
FROM events WHERE id = :id LIMIT 1"
);
$stmt->execute(['id' => $id]);
$r = $stmt->fetch();
if (!$r) return null;
return [
'title' => $r['title'],
'ship' => $r['ship_name'] ?? '',
'terminal' => $r['terminal'] ?? '',
'when' => $r['starts_at'] ?? '',
'period' => trim(($r['starts_at'] ?? '') . ' → ' . ($r['ends_at'] ?? '')),
];
}
private function searchBookings(string $like, string $likeNorm, string $normalized): array
{
// Auf bookings.source filtern, nicht customers.source — sonst verschwinden manuell
// angelegte Buchungen aus der Live-Suche, sobald der Kunde z.B. aus AIDA stammt.
//
// SPEED: keine N+1-Subqueries mehr für product_ids / checked_in_at / checked_out_at —
// die Detail-Pane lädt diese Felder lazy über api/live-search/detail nach.
// Auch event_id/payment_status/checkin_status werden nur noch geladen, wenn das Frontend
// sie braucht — der Initial-Liste reicht title + subtitle + plate.
//
// LIMIT von 100 auf 30 reduziert — die Live-Suche zeigt sowieso max. ~20 sichtbar.
[$hsSql, $hsParams] = $this->hideSourcesClause('b', 'hssb_');
$sql = "SELECT b.id, b.booking_no, b.license_plate, b.normalized_license_plate,
b.travel_from, b.travel_to, b.event_id, b.customer_id,
c.first_name, c.last_name, c.company,
e.title AS event_title, e.ship_name
FROM bookings b
INNER JOIN customers c ON c.id = b.customer_id
INNER JOIN events e ON e.id = b.event_id
WHERE (b.booking_no LIKE :q1
OR b.license_plate LIKE :q2
OR b.normalized_license_plate LIKE :qn) $hsSql
ORDER BY b.id DESC
LIMIT 30";
$bindParams = ['q1' => $like, 'q2' => $like, 'qn' => $likeNorm] + $hsParams;
$rows = $this->trace('bookings.like', $sql, $bindParams, function () use ($sql, $bindParams) {
$stmt = $this->pdo->prepare($sql);
$stmt->execute($bindParams);
return $stmt->fetchAll();
});
$out = [];
foreach ($rows as $r) {
$score = 0.0;
if ($normalized && $r['normalized_license_plate'] && str_contains($r['normalized_license_plate'], $normalized)) {
$score += 100;
if ($r['normalized_license_plate'] === $normalized) $score += 50;
}
if ($normalized && str_contains(Normalize::searchText($r['booking_no']), $normalized)) {
$score += 80;
}
$name = trim($r['first_name'] . ' ' . $r['last_name']);
$plate = formatLicensePlateDE($r['license_plate'] ?? '');
$out[] = [
'type' => 'bookings',
'id' => (int)$r['id'],
'title' => $r['booking_no'] . ($plate !== '' ? ' · ' . $plate : ''),
'subtitle' => $name . ($r['company'] ? ' · ' . $r['company'] : '') . ' · ' . $r['event_title'],
'badge' => 'Buchung',
'url' => '/?page=bookings/view&id=' . (int)$r['id'],
'score' => $score,
'meta' => [
'customer_id' => (int)($r['customer_id'] ?? 0),
'plate' => $plate,
'name' => $name,
'company' => $r['company'] ?? '',
'travel_from' => $r['travel_from'] ?? '',
'travel_to' => $r['travel_to'] ?? '',
'event_id' => (int)$r['event_id'],
'event_title' => (string)($r['event_title'] ?? ''),
'event' => trim(($r['event_title'] ?? '') . ($r['ship_name'] ? ' · ' . $r['ship_name'] : '')),
'booking_no' => $r['booking_no'],
// product_ids / checked_in_at / checked_out_at / payment_status / phone / email
// werden vom Detail-Endpoint nachgeladen — kein N+1 mehr.
],
];
}
return $out;
}
private static function formatTravelPeriod(array $r): string
{
// Bevorzugt: Anreisedatum, ansonsten travel_from → travel_to (Free-Text-Felder).
if (!empty($r['arrival_time'])) {
return formatDateTime($r['arrival_time']);
}
$from = trim((string)($r['travel_from'] ?? ''));
$to = trim((string)($r['travel_to'] ?? ''));
if ($from === '' && $to === '') return '';
if ($from !== '' && $to !== '') return $from . ' → ' . $to;
return $from . $to;
}
private function searchCustomers(string $like): array
{
// customers-Tabelle hat keinen Alias hier — Spalte direkt referenzieren.
$hsParams = [];
$hsSql = '';
if (!empty($this->hiddenSources)) {
$phs = [];
foreach ($this->hiddenSources as $i => $s) {
$k = 'hssc_' . $i;
$phs[] = ':' . $k;
$hsParams[$k] = (string)$s;
}
$hsSql = " AND COALESCE(source,'') NOT IN (" . implode(',', $phs) . ")";
}
$sql = "SELECT id, first_name, last_name, company, email, phone, city, zip
FROM customers
WHERE (first_name LIKE :q1
OR last_name LIKE :q2
OR company LIKE :q3
OR email LIKE :q4
OR phone LIKE :q5
OR CONCAT(first_name, ' ', last_name) LIKE :q6) $hsSql
ORDER BY last_name ASC
LIMIT 30";
$bindParams = ['q1' => $like, 'q2' => $like, 'q3' => $like, 'q4' => $like, 'q5' => $like, 'q6' => $like] + $hsParams;
$rows = $this->trace('customers.like', $sql, $bindParams, function () use ($sql, $bindParams) {
$stmt = $this->pdo->prepare($sql);
$stmt->execute($bindParams);
return $stmt->fetchAll();
});
$out = [];
foreach ($rows as $r) {
$name = trim($r['first_name'] . ' ' . $r['last_name']);
$out[] = [
'type' => 'customers',
'id' => (int)$r['id'],
'title' => $name . ($r['company'] ? ' · ' . $r['company'] : ''),
'subtitle' => $r['email'] . ' · ' . $r['phone'],
'badge' => 'Kunde',
'url' => '/?page=customers/view&id=' . (int)$r['id'],
'score' => 30,
'meta' => [
'name' => $name,
'first_name' => $r['first_name'] ?? '',
'last_name' => $r['last_name'] ?? '',
'company' => $r['company'] ?? '',
'email' => $r['email'] ?? '',
'phone' => $r['phone'] ?? '',
'contact' => trim(($r['email'] ?? '') . ($r['phone'] ? ' · ' . $r['phone'] : '')),
'address' => trim(($r['zip'] ?? '') . ' ' . ($r['city'] ?? '')),
// `bookings`-Liste wird lazy via api/live-search/detail nachgeladen
// (war hier N+1: pro Kunden-Treffer eine zusätzliche Sub-Query).
],
];
}
return $out;
}
private function searchInvoices(string $like): array
{
// Rechnungen analog Buchungen: auf bookings.source filtern.
[$hsSql, $hsParams] = $this->hideSourcesClause('b', 'hssi_');
$sql = "SELECT i.id, i.invoice_no, i.invoice_date, i.total_gross, i.status,
c.first_name, c.last_name, c.company,
b.booking_no
FROM invoices i
INNER JOIN customers c ON c.id = i.customer_id
INNER JOIN bookings b ON b.id = i.booking_id
WHERE (i.invoice_no LIKE :q1
OR b.booking_no LIKE :q2) $hsSql
ORDER BY i.id DESC
LIMIT 30";
$bindParams = ['q1' => $like, 'q2' => $like] + $hsParams;
$rows = $this->trace('invoices.like', $sql, $bindParams, function () use ($sql, $bindParams) {
$stmt = $this->pdo->prepare($sql);
$stmt->execute($bindParams);
return $stmt->fetchAll();
});
$out = [];
foreach ($rows as $r) {
$name = trim($r['first_name'] . ' ' . $r['last_name']);
$out[] = [
'type' => 'invoices',
'id' => (int)$r['id'],
'title' => $r['invoice_no'] . ' · ' . formatCurrency((float)$r['total_gross']),
'subtitle' => $name . ($r['company'] ? ' · ' . $r['company'] : '') . ' · Buchung ' . $r['booking_no'],
'badge' => 'Rechnung',
'url' => '/?page=invoices/show&id=' . (int)$r['id'],
'score' => 70,
'meta' => [
'invoice_no' => $r['invoice_no'],
'name' => $name . ($r['company'] ? ' · ' . $r['company'] : ''),
'booking' => 'Buchung ' . $r['booking_no'],
'amount' => formatCurrency((float)$r['total_gross']),
'status' => statusLabel($r['status']),
],
];
}
return $out;
}
/**
* Multi-Token-Suche für Events: jeder Token muss in einem der Felder
* (Titel, Schiff, Terminal, Monat, Jahr, Datum) vorkommen — AND-verknüpft.
* Erlaubt Queries wie "aida mai", "aidamar 26", "msc august".
*/
private function searchEvents(string $rawQuery, int $pastDays = 0): array
{
// 1) "N Tage" → Reisedauer-Filter extrahieren, aus der Query rausschneiden.
$durationFilter = null;
$rawQuery = preg_replace_callback('/(\d+)\s*tage?\b/iu', function ($m) use (&$durationFilter) {
$durationFilter = (int)$m[1];
return ' ';
}, trim($rawQuery));
$tokens = preg_split('/\s+/', mb_strtolower(trim($rawQuery)), -1, PREG_SPLIT_NO_EMPTY) ?: [];
if (empty($tokens) && $durationFilter === null) return [];
// 2) Deutsche Monatsnamen aus den Tokens ziehen → OR-Gruppe statt AND.
$monthNameList = ['januar','februar','märz','april','mai','juni','juli','august','september','oktober','november','dezember'];
$monthOrTokens = [];
$andTokens = [];
foreach ($tokens as $t) {
if (in_array($t, $monthNameList, true)) {
$monthOrTokens[] = $t;
} else {
$andTokens[] = $t;
}
}
// Doppelte raus
$monthOrTokens = array_values(array_unique($monthOrTokens));
$tokens = $andTokens;
// Datumstoken aufweichen: "16.5", "16.5.", "16/5", "16-05", "5/26", "2026-05-16" → normalisiere zu Punkten,
// strippe trailing dot, ergänze um Zero-Pad-Variante. Token bleibt als Substring-Match auf Haystack.
$expandToken = function (string $t): array {
$variants = [$t];
// Trennzeichen vereinheitlichen
$norm = str_replace(['/', '-'], '.', $t);
$norm = rtrim($norm, '.');
if ($norm !== $t) $variants[] = $norm;
// ISO YYYY-MM-DD wäre nach Replace zu YYYY.MM.DD → in d.m.Y umdrehen
if (preg_match('/^(\d{4})\.(\d{1,2})\.(\d{1,2})$/', $norm, $m)) {
$variants[] = sprintf('%d.%d.%s', (int)$m[3], (int)$m[2], $m[1]);
$variants[] = sprintf('%02d.%02d.%s', (int)$m[3], (int)$m[2], $m[1]);
}
// d.m / d.m.y / d.m.Y → Zero-Pad-Variante
if (preg_match('/^(\d{1,2})\.(\d{1,2})(?:\.(\d{2,4}))?$/', $norm, $m)) {
$d = (int)$m[1]; $mo = (int)$m[2]; $y = $m[3] ?? '';
$variants[] = sprintf('%d.%d', $d, $mo);
$variants[] = sprintf('%02d.%02d', $d, $mo);
if ($y !== '') {
$variants[] = sprintf('%d.%d.%s', $d, $mo, $y);
$variants[] = sprintf('%02d.%02d.%s', $d, $mo, $y);
}
}
// m.y / m.Y (Monat+Jahr ohne Tag) → Zero-Pad
if (preg_match('/^(\d{1,2})\.(\d{2,4})$/', $norm, $m) && strlen($m[2]) >= 2) {
$mo = (int)$m[1]; $y = $m[2];
$variants[] = sprintf('%d.%s', $mo, $y);
$variants[] = sprintf('%02d.%s', $mo, $y);
}
return array_values(array_unique($variants));
};
$monthNamesDe = [1=>'januar',2=>'februar',3=>'märz',4=>'april',5=>'mai',6=>'juni',
7=>'juli',8=>'august',9=>'september',10=>'oktober',11=>'november',12=>'dezember'];
// Nur aktive Events ab heute (heute eingeschlossen), aufsteigend sortiert.
// $pastDays > 0 erweitert das Fenster rückwirkend (z. B. Buchung nachträglich für gestriges Event).
$pastDays = max(0, $pastDays);
$stmt = $this->pdo->prepare("SELECT id, title, ship_name, terminal, starts_at, ends_at, status
FROM events
WHERE status <> 'cancelled'
AND DATE(starts_at) >= DATE_SUB(CURDATE(), INTERVAL :pd DAY)
ORDER BY starts_at ASC
LIMIT 5000");
$stmt->execute(['pd' => $pastDays]);
$out = [];
foreach ($stmt as $r) {
$tsStart = strtotime($r['starts_at']);
$tsEnd = strtotime($r['ends_at'] ?? $r['starts_at']);
$monthDeStart = $monthNamesDe[(int)date('n', $tsStart)] ?? '';
$monthDeEnd = $monthNamesDe[(int)date('n', $tsEnd)] ?? '';
// Haystack: Schiff/Titel/Terminal + viele Datums-Varianten (Start + Ende),
// gepad und ungepad, mit und ohne Jahr, ISO, sowie Monatsname.
$dateVariants = [];
foreach ([$tsStart, $tsEnd] as $ts) {
if (!$ts) continue;
$dateVariants[] = date('d.m.Y', $ts); // 16.05.2026
$dateVariants[] = date('d.m.y', $ts); // 16.05.26
$dateVariants[] = date('j.n.Y', $ts); // 16.5.2026
$dateVariants[] = date('j.n.y', $ts); // 16.5.26
$dateVariants[] = date('d.m.', $ts); // 16.05.
$dateVariants[] = date('j.n.', $ts); // 16.5.
$dateVariants[] = date('d.m', $ts); // 16.05
$dateVariants[] = date('j.n', $ts); // 16.5
$dateVariants[] = date('m.Y', $ts); // 05.2026
$dateVariants[] = date('n.Y', $ts); // 5.2026
$dateVariants[] = date('m.y', $ts); // 05.26
$dateVariants[] = date('n.y', $ts); // 5.26
$dateVariants[] = date('Y-m-d', $ts); // 2026-05-16
}
$haystack = mb_strtolower(implode(' ', array_merge([
$r['title'] ?? '',
$r['ship_name'] ?? '',
$r['terminal'] ?? '',
$monthDeStart, $monthDeEnd,
date('Y', $tsStart), date('y', $tsStart),
], $dateVariants)));
$haystackNorm = Normalize::searchText($haystack);
$allMatch = true;
foreach ($tokens as $t) {
$variants = $expandToken($t);
$hit = false;
foreach ($variants as $v) {
if (str_contains($haystack, $v)) { $hit = true; break; }
$vn = Normalize::searchText($v);
if ($vn !== '' && str_contains($haystackNorm, $vn)) { $hit = true; break; }
}
if (!$hit) { $allMatch = false; break; }
}
if (!$allMatch) continue;
// Monats-OR: mindestens ein Monatsname muss auf den Start-Monat des Events zutreffen.
if (!empty($monthOrTokens)) {
$monthHit = false;
foreach ($monthOrTokens as $mt) {
if ($mt === $monthDeStart) { $monthHit = true; break; }
}
if (!$monthHit) continue;
}
// Tage: vom Start-Tag 00:00 bis End-Tag 00:00 (kalendertage-Differenz).
$startDay = $tsStart ? strtotime(date('Y-m-d', $tsStart) . ' 00:00:00') : 0;
$endDay = $tsEnd ? strtotime(date('Y-m-d', $tsEnd) . ' 00:00:00') : 0;
$durationDays = ($startDay && $endDay) ? max(0, (int)round(($endDay - $startDay) / 86400)) : 0;
// Reisedauer-Filter (z.B. "5 Tage")
if ($durationFilter !== null && $durationDays !== $durationFilter) continue;
$period = ($tsStart && $tsEnd && date('Y', $tsStart) === date('Y', $tsEnd))
? date('d.m.', $tsStart) . ' bis ' . date('d.m.Y', $tsEnd)
: date('d.m.Y', $tsStart) . ' bis ' . date('d.m.Y', $tsEnd);
$out[] = [
'type' => 'events',
'id' => (int)$r['id'],
'title' => trim(($r['title'] ?? '') . ($r['ship_name'] ? ' · ' . $r['ship_name'] : '')),
'subtitle' => trim(($r['terminal'] ?? '') . ' · ' . $period),
'badge' => 'Event',
'url' => '/?page=events/view&id=' . (int)$r['id'],
'score' => 20,
'meta' => [
'title' => $r['title'] ?? '',
'ship' => $r['ship_name'] ?? '',
'terminal' => $r['terminal'] ?? '',
'when' => formatDateTime($r['starts_at']),
'when_date' => date('d.m.Y', $tsStart),
'month' => ucfirst($monthDeStart),
'year' => date('Y', $tsStart),
'duration_days' => $durationDays,
'period' => $period,
'start_date' => date('Y-m-d', $tsStart),
'end_date' => date('Y-m-d', $tsEnd),
// Kurzstatistik — wird unten in einer Query für alle Treffer ergänzt.
'bookings_total' => 0,
'bookings_paid' => 0,
'bookings_arrived' => 0,
],
];
if (count($out) >= self::MAX_RESULTS) break;
}
// Kurzstatistik pro Treffer-Event in EINER zusätzlichen Query nachladen — kein N+1.
if (!empty($out)) {
$ids = array_map(static fn ($r) => (int)$r['id'], $out);
$ph = implode(',', array_fill(0, count($ids), '?'));
$stmt = $this->pdo->prepare(
"SELECT event_id,
COUNT(*) AS total,
SUM(CASE WHEN payment_status = 'paid' THEN 1 ELSE 0 END) AS paid,
SUM(CASE WHEN checkin_status = 'checked_in' THEN 1 ELSE 0 END) AS arrived
FROM bookings
WHERE event_id IN ($ph)
AND status <> 'cancelled'
GROUP BY event_id"
);
$stmt->execute($ids);
$statsByEvent = [];
foreach ($stmt->fetchAll() as $sr) {
$statsByEvent[(int)$sr['event_id']] = $sr;
}
foreach ($out as &$item) {
$s = $statsByEvent[$item['id']] ?? null;
if ($s !== null) {
$item['meta']['bookings_total'] = (int)$s['total'];
$item['meta']['bookings_paid'] = (int)$s['paid'];
$item['meta']['bookings_arrived'] = (int)$s['arrived'];
}
}
unset($item);
}
return $out;
}
/**
* Plate-spezifisches Fuzzy: findet Kennzeichen mit Levenshtein-Distanz ≤ 1
* (kurz) bzw. ≤ 2 (lang) — vergleicht auf `normalized_license_plate`.
*
* SQL-Vorfilter: das erste Zeichen-Paar der Query als Präfix oder Substring.
* Damit bleibt der Kandidaten-Pool auch bei zehntausenden Buchungen klein
* (typisch 50–500), und Levenshtein in PHP läuft in unter 5 ms.
*
* Beispiel: Query „HROA42" → Präfix „HR" → liefert alle Plates die mit „HR"
* starten oder „HR" als Substring enthalten → Levenshtein-Vergleich findet
* „HROKA342" mit Distanz 2.
*/
private function fuzzyPlates(string $normalized): array
{
$nq = strlen($normalized);
if ($nq < 3) return [];
$maxDist = $nq <= 6 ? 1 : ($nq <= 10 ? 2 : 3);
// Präfix + Substring der ersten 2 Zeichen als Vorfilter — Plates haben
// fast immer Ortskennung am Anfang, daher hoher Hit-Rate.
$p2 = substr($normalized, 0, 2);
$p3 = $nq >= 3 ? substr($normalized, 0, 3) : $p2;
[$hsSql, $hsParams] = $this->hideSourcesClause('b', 'fpp_');
$sql = "SELECT b.id, b.booking_no, b.license_plate, b.normalized_license_plate,
b.travel_from, b.travel_to, b.arrival_time,
b.status, b.payment_status, b.checkin_status, b.event_id,
c.first_name, c.last_name, c.company, c.email, c.phone,
e.title AS event_title, e.ship_name,
(SELECT GROUP_CONCAT(product_id ORDER BY product_id)
FROM booking_products WHERE booking_id = b.id) AS product_ids,
(SELECT checked_in_at FROM checkins WHERE booking_id = b.id ORDER BY id DESC LIMIT 1) AS chk_in_at,
(SELECT checked_out_at FROM checkins WHERE booking_id = b.id ORDER BY id DESC LIMIT 1) AS chk_out_at
FROM bookings b
INNER JOIN customers c ON c.id = b.customer_id
INNER JOIN events e ON e.id = b.event_id
WHERE (b.normalized_license_plate LIKE :p2pre
OR b.normalized_license_plate LIKE :p2sub
OR b.normalized_license_plate LIKE :p3pre) $hsSql
ORDER BY b.id DESC
LIMIT 1000";
$stmt = $this->pdo->prepare($sql);
$stmt->execute([
'p2pre' => $p2 . '%',
'p2sub' => '%' . $p2 . '%',
'p3pre' => $p3 . '%',
] + $hsParams);
$out = [];
foreach ($stmt->fetchAll() as $r) {
$nlp = (string)($r['normalized_license_plate'] ?? '');
if ($nlp === '') continue;
// Reine Substring-Treffer überspringen (gehören in exact, nicht in fuzzy).
if (str_contains($nlp, $normalized)) continue;
$d = Normalize::distance($normalized, $nlp);
if ($d <= $maxDist) {
$name = trim($r['first_name'] . ' ' . $r['last_name']);
$plate = formatLicensePlateDE($r['license_plate'] ?? '');
$out[] = [
'type' => 'bookings',
'id' => (int)$r['id'],
'title' => $r['booking_no'] . ($plate !== '' ? ' · ' . $plate : ''),
'subtitle' => $name . ' · ' . $r['event_title'],
'badge' => 'Buchung',
'url' => '/?page=bookings/view&id=' . (int)$r['id'],
'score' => 1.0 / ($d + 1),
'meta' => [
'plate' => $plate,
'name' => $name,
'company' => $r['company'] ?? '',
'email' => $r['email'] ?? '',
'phone' => $r['phone'] ?? '',
'travel' => self::formatTravelPeriod($r),
'travel_from' => $r['travel_from'] ?? '',
'travel_to' => $r['travel_to'] ?? '',
'arrival_time' => $r['arrival_time'] ?? '',
'event_id' => (int)$r['event_id'],
'event_title' => (string)($r['event_title'] ?? ''),
'event' => trim(($r['event_title'] ?? '') . ($r['ship_name'] ? ' · ' . $r['ship_name'] : '')),
'booking_no' => $r['booking_no'],
'status' => $r['status'] ?? '',
'payment_status' => $r['payment_status'] ?? '',
'checkin_status' => $r['checkin_status'] ?? '',
'checked_in_at' => $r['chk_in_at'] ?? '',
'checked_out_at' => $r['chk_out_at'] ?? '',
'product_ids' => (string)($r['product_ids'] ?? ''),
],
];
}
}
usort($out, fn ($a, $b) => $b['score'] <=> $a['score']);
return array_slice($out, 0, 8);
}
private function fuzzy(string $normalized, array $types): array
{
$candidates = [];
if (in_array('bookings', $types, true)) {
$stmt = $this->pdo->query("SELECT b.id, b.booking_no, b.license_plate, b.normalized_license_plate,
b.travel_from, b.travel_to, b.arrival_time,
c.first_name, c.last_name, c.company,
e.title AS event_title, e.ship_name,
(SELECT GROUP_CONCAT(product_id ORDER BY product_id)
FROM booking_products WHERE booking_id = b.id) AS product_ids
FROM bookings b
INNER JOIN customers c ON c.id = b.customer_id
INNER JOIN events e ON e.id = b.event_id
ORDER BY b.id DESC LIMIT 200");
foreach ($stmt->fetchAll() as $r) {
$name = trim($r['first_name'] . ' ' . $r['last_name']);
$plate = formatLicensePlateDE($r['license_plate'] ?? '');
$candidates[] = [
'type' => 'bookings',
'id' => (int)$r['id'],
'haystack' => [Normalize::searchText($r['booking_no']), $r['normalized_license_plate'] ?? ''],
'title' => $r['booking_no'] . ($plate !== '' ? ' · ' . $plate : ''),
'subtitle' => $name . ' · ' . $r['event_title'],
'badge' => 'Buchung',
'url' => '/?page=bookings/view&id=' . (int)$r['id'],
'meta' => [
'plate' => $plate,
'name' => $name,
'company' => $r['company'] ?? '',
'travel' => self::formatTravelPeriod($r),
'travel_from' => $r['travel_from'] ?? '',
'event' => trim(($r['event_title'] ?? '') . ($r['ship_name'] ? ' · ' . $r['ship_name'] : '')),
'booking_no' => $r['booking_no'],
'product_ids' => (string)($r['product_ids'] ?? ''),
],
];
}
}
if (in_array('customers', $types, true)) {
$stmt = $this->pdo->query("SELECT id, first_name, last_name, company, email, phone, city, zip FROM customers ORDER BY id DESC LIMIT 200");
foreach ($stmt->fetchAll() as $r) {
$name = trim($r['first_name'] . ' ' . $r['last_name']);
$candidates[] = [
'type' => 'customers',
'id' => (int)$r['id'],
'haystack' => [
Normalize::searchText($r['first_name'] . $r['last_name']),
Normalize::searchText($r['company'] ?? ''),
Normalize::searchText($r['email']),
],
'title' => $name . ($r['company'] ? ' · ' . $r['company'] : ''),
'subtitle' => $r['email'],
'badge' => 'Kunde',
'url' => '/?page=customers/view&id=' . (int)$r['id'],
'meta' => [
'name' => $name,
'company' => $r['company'] ?? '',
'contact' => trim(($r['email'] ?? '') . ($r['phone'] ? ' · ' . $r['phone'] : '')),
'address' => trim(($r['zip'] ?? '') . ' ' . ($r['city'] ?? '')),
'bookings' => $this->customerBookings((int)$r['id']),
],
];
}
}
$scored = [];
foreach ($candidates as $c) {
$best = PHP_INT_MAX;
foreach ($c['haystack'] as $h) {
if ($h === '') continue;
$d = Normalize::distance($normalized, $h);
if ($d < $best) $best = $d;
}
// Akzeptieren wenn Distanz <= 30% der Länge oder <=2 für kurze
$max = max(2, (int)floor(strlen($normalized) * 0.35));
if ($best <= $max) {
$c['score'] = 1.0 / ($best + 1);
unset($c['haystack']);
$scored[] = $c;
}
}
usort($scored, fn($a, $b) => $b['score'] <=> $a['score']);
return array_slice($scored, 0, self::MAX_RESULTS);
}
}