<?php

class Invoice
{
    private PDO $pdo;

    public function __construct(Database $db)
    {
        $this->pdo = $db->getConnection();
    }

    public function all(array $hideSources = []): array
    {
        // Unbounded — wird nur noch von Sonderfällen (z. B. Exports) gebraucht.
        // Listenseite nutzt paginated().
        [$where, $params] = $this->buildListWhere($hideSources);
        $sql = "SELECT i.*,
                       b.booking_no,
                       c.first_name, c.last_name, c.company
                FROM invoices i
                INNER JOIN bookings b ON b.id = i.booking_id
                INNER JOIN customers c ON c.id = i.customer_id
                WHERE $where
                ORDER BY i.id DESC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    /**
     * Server-seitig paginiert. Sort nach i.id DESC (indexbasiert, neueste zuerst) —
     *  das alte CASE/SUBSTRING-Schema war non-sargable und hat bei großen Tabellen
     *  einen vollständigen Filesort erzwungen.
     */
    public function paginated(array $hideSources = [], int $perPage = 20, int $page = 1): array
    {
        $perPage = max(1, $perPage);
        $page    = max(1, $page);

        [$where, $params] = $this->buildListWhere($hideSources);

        $countStmt = $this->pdo->prepare(
            "SELECT COUNT(*) FROM invoices i
              INNER JOIN bookings b ON b.id = i.booking_id
              WHERE $where"
        );
        $countStmt->execute($params);
        $total = (int)$countStmt->fetchColumn();

        $pages  = max(1, (int)ceil($total / $perPage));
        $page   = max(1, min($pages, $page));
        $offset = ($page - 1) * $perPage;

        $sql = "SELECT i.id, i.invoice_no, i.invoice_date, i.status,
                       i.total_net, i.total_vat, i.total_gross,
                       i.booking_id, i.customer_id,
                       b.booking_no,
                       c.first_name, c.last_name, c.company
                  FROM invoices i
                  INNER JOIN bookings b  ON b.id = i.booking_id
                  INNER JOIN customers c ON c.id = i.customer_id
                  WHERE $where
                  ORDER BY i.id DESC
                  LIMIT $perPage OFFSET $offset";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $items = $stmt->fetchAll();

        return [
            'items'   => $items,
            'total'   => $total,
            'page'    => $page,
            'pages'   => $pages,
            'perPage' => $perPage,
            'from'    => $total === 0 ? 0 : $offset + 1,
            'to'      => min($total, $offset + $perPage),
        ];
    }

    private function buildListWhere(array $hideSources): array
    {
        $where  = '1=1';
        $params = [];
        if (!empty($hideSources)) {
            $phs = [];
            foreach ($hideSources as $i => $s) {
                $key = 'hs' . $i;
                $phs[] = ':' . $key;
                $params[$key] = (string)$s;
            }
            $where .= " AND COALESCE(b.source,'') NOT IN (" . implode(',', $phs) . ")";
        }
        return [$where, $params];
    }

    public function find(int $id): ?array
    {
        $sql = "SELECT i.*,
                       b.booking_no, b.status AS booking_status, b.total_net AS b_total_net, b.total_gross AS b_total_gross,
                       b.travel_from, b.travel_to, b.arrival_time, b.license_plate, b.notes,
                       c.first_name AS c_first_name, c.last_name AS c_last_name, c.company AS c_company,
                       c.street AS c_street, c.zip AS c_zip, c.city AS c_city,
                       c.email AS c_email, c.phone AS c_phone,
                       b.event_id,
                       e.title AS event_title, e.ship_name AS event_ship, e.terminal AS event_terminal,
                       e.starts_at AS event_starts_at, e.ends_at AS event_ends_at
                FROM invoices i
                INNER JOIN bookings b ON b.id = i.booking_id
                INNER JOIN customers c ON c.id = i.customer_id
                INNER JOIN events e ON e.id = b.event_id
                WHERE i.id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();
        return $row !== false ? $row : null;
    }

    /**
     * Rechnungen, die an einem bestimmten Tag erstellt wurden (invoice_date).
     * Optional auf ein Event und/oder eine bestimmte customer.source eingeschränkt.
     *
     * Zusätzlich:
     *  - $filters['event_ids']        — array<int>:    Multi-Event-Filter (b.event_id IN (...))
     *  - $filters['sources']          — array<string>: Multi-Quelle-Filter (b.source IN (...))
     *                                                  bzw. '__none__' für Buchungen ohne Quelle.
     *  - $filters['payment_methods']  — array<string>: Multi-Zahlungsart-Filter (EXISTS payments)
     *  - $filters['exclude_sources']  — array<string>: Quellen, die nie auftauchen dürfen
     *                                                  (sowohl b.source als auch c.source).
     */
    public function byDate(string $date, ?int $eventId = null, ?string $source = null, array $filters = []): array
    {
        // Sargable Range — nutzt idx_invoices_invoice_date (kein DATE()-Wrapper).
        $sql = "SELECT i.*,
                       b.booking_no, b.event_id, b.source AS booking_source,
                       c.first_name, c.last_name, c.company, c.source,
                       e.title AS event_title
                  FROM invoices i
                  INNER JOIN bookings b  ON b.id = i.booking_id
                  INNER JOIN customers c ON c.id = i.customer_id
                  LEFT  JOIN events    e ON e.id = b.event_id
                 WHERE i.invoice_date >= :d AND i.invoice_date < :d_next";
        $params = ['d' => $date, 'd_next' => date('Y-m-d', strtotime($date . ' +1 day'))];
        if ($eventId !== null) {
            $sql .= " AND b.event_id = :e";
            $params['e'] = $eventId;
        }
        if ($source !== null) {
            $sql .= " AND COALESCE(c.source,'') = :src";
            $params['src'] = $source;
        }

        $eventIds = array_values(array_filter(array_map('intval', $filters['event_ids'] ?? []), fn ($v) => $v > 0));
        if (!empty($eventIds)) {
            $phs = [];
            foreach ($eventIds as $i => $eid) { $k = 'fe' . $i; $phs[] = ':' . $k; $params[$k] = $eid; }
            $sql .= " AND b.event_id IN (" . implode(',', $phs) . ")";
        }

        $sources = array_values(array_filter(array_map('strval', $filters['sources'] ?? []), fn ($v) => $v !== ''));
        if (!empty($sources)) {
            $phs = [];
            $wantNone = false;
            foreach ($sources as $i => $s) {
                if ($s === '__none__') { $wantNone = true; continue; }
                $k = 'fs' . $i; $phs[] = ':' . $k; $params[$k] = $s;
            }
            $parts = [];
            if (!empty($phs)) $parts[] = "COALESCE(b.source,'') IN (" . implode(',', $phs) . ")";
            if ($wantNone)    $parts[] = "COALESCE(b.source,'') = ''";
            if (!empty($parts)) $sql .= " AND (" . implode(' OR ', $parts) . ")";
        }

        $methods = array_values(array_filter(array_map('strval', $filters['payment_methods'] ?? []), fn ($v) => $v !== ''));
        if (!empty($methods)) {
            $phs = [];
            foreach ($methods as $i => $m) { $k = 'fm' . $i; $phs[] = ':' . $k; $params[$k] = $m; }
            $sql .= " AND EXISTS (SELECT 1 FROM payments p WHERE p.invoice_id = i.id"
                  . " AND p.payment_method IN (" . implode(',', $phs) . "))";
        }

        $excludeSources = array_values(array_filter(array_map('strval', $filters['exclude_sources'] ?? []), fn ($v) => $v !== ''));
        if (!empty($excludeSources)) {
            // Zwei separate Placeholder-Sätze — MariaDB lehnt sonst HY093 ab,
            // wenn derselbe Platzhalter in zwei Klauseln auftaucht.
            $phsB = $phsC = [];
            foreach ($excludeSources as $i => $s) {
                $kb = 'fxb' . $i; $kc = 'fxc' . $i;
                $phsB[] = ':' . $kb; $phsC[] = ':' . $kc;
                $params[$kb] = $s;  $params[$kc] = $s;
            }
            $sql .= " AND COALESCE(b.source,'') NOT IN (" . implode(',', $phsB) . ")";
            $sql .= " AND COALESCE(c.source,'') NOT IN (" . implode(',', $phsC) . ")";
        }

        $sql .= " ORDER BY i.id DESC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function byCustomer(int $customerId): array
    {
        $sql = "SELECT i.*, b.booking_no
                FROM invoices i
                INNER JOIN bookings b ON b.id = i.booking_id
                WHERE i.customer_id = :id
                ORDER BY i.invoice_date DESC, i.id DESC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['id' => $customerId]);
        return $stmt->fetchAll();
    }

    public function byEvent(int $eventId): array
    {
        $sql = "SELECT i.*, b.booking_no
                FROM invoices i
                INNER JOIN bookings b ON b.id = i.booking_id
                WHERE b.event_id = :id
                ORDER BY i.id DESC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['id' => $eventId]);
        return $stmt->fetchAll();
    }

    public function findByBooking(int $bookingId): ?array
    {
        $stmt = $this->pdo->prepare("SELECT * FROM invoices WHERE booking_id = :id ORDER BY id ASC LIMIT 1");
        $stmt->execute(['id' => $bookingId]);
        $row = $stmt->fetch();
        return $row !== false ? $row : null;
    }

    /**
     * Alle Rechnungen einer Buchung — chronologisch (älteste zuerst).
     */
    public function allByBooking(int $bookingId): array
    {
        $stmt = $this->pdo->prepare("SELECT * FROM invoices WHERE booking_id = :id ORDER BY id ASC");
        $stmt->execute(['id' => $bookingId]);
        return $stmt->fetchAll();
    }

    /**
     * Liefert die jüngste Entwurfs-Rechnung einer Buchung (status='draft').
     * AIDA-Zusatzposten landen in einem solchen Entwurf, bis die Zahlung gestellt ist.
     */
    public function findDraftForBooking(int $bookingId): ?array
    {
        $stmt = $this->pdo->prepare("SELECT * FROM invoices
                                      WHERE booking_id = :id AND status = 'draft'
                                      ORDER BY id DESC LIMIT 1");
        $stmt->execute(['id' => $bookingId]);
        $row = $stmt->fetch();
        return $row !== false ? $row : null;
    }

    /**
     * Hängt ein booking_product als invoice_item an einen Entwurf an. Existiert
     * für die Buchung noch kein Entwurf, wird einer erstellt (status='draft',
     * invoice_no=NULL, invoice_date=NULL — beides wird erst bei Zahlung gefüllt).
     * Aktualisiert die Entwurfs-Summen entsprechend.
     */
    public function addItemToDraftForBooking(int $bookingId, int $bookingProductId): int
    {
        $stmt = $this->pdo->prepare("SELECT bp.*, p.title AS product_title, p.description AS product_description
                                       FROM booking_products bp
                                       INNER JOIN products p ON p.id = bp.product_id
                                      WHERE bp.id = :id");
        $stmt->execute(['id' => $bookingProductId]);
        $bp = $stmt->fetch();
        if (!$bp) throw new RuntimeException('Buchungs-Produkt nicht gefunden.');

        $stmt = $this->pdo->prepare("SELECT customer_id FROM bookings WHERE id = :id");
        $stmt->execute(['id' => $bookingId]);
        $customerId = (int)$stmt->fetchColumn();
        if ($customerId <= 0) throw new RuntimeException('Buchung nicht gefunden.');

        $this->pdo->beginTransaction();
        try {
            $draft = $this->findDraftForBooking($bookingId);
            if (!$draft) {
                $stmt = $this->pdo->prepare("INSERT INTO invoices
                        (invoice_no, booking_id, customer_id, invoice_date, status, total_net, total_vat, total_gross)
                        VALUES (NULL, :booking_id, :customer_id, NULL, 'draft', 0, 0, 0)");
                $stmt->execute([
                    'booking_id'  => $bookingId,
                    'customer_id' => $customerId,
                ]);
                $draftId = (int)$this->pdo->lastInsertId();
            } else {
                $draftId = (int)$draft['id'];
            }

            $qty   = (int)$bp['quantity'];
            $net   = (float)$bp['price_net'];
            $vat   = (float)$bp['vat_rate'];
            $gross = (float)$bp['price_gross'];
            $totalNet   = round($qty * $net, 2);
            $totalGross = round($qty * $gross, 2);

            $itemStmt = $this->pdo->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 (:invoice_id, :product_id, :title, :description, :quantity, :price_net, :vat_rate, :price_gross, :total_net, :total_vat, :total_gross)");
            $itemStmt->execute([
                'invoice_id'  => $draftId,
                'product_id'  => (int)$bp['product_id'],
                'title'       => $bp['product_title'],
                'description' => $bp['product_description'],
                'quantity'    => $qty,
                'price_net'   => $net,
                'vat_rate'    => $vat,
                'price_gross' => $gross,
                'total_net'   => $totalNet,
                'total_vat'   => round($totalGross - $totalNet, 2),
                'total_gross' => $totalGross,
            ]);

            // Summen neu berechnen — nur über die Items dieser Rechnung.
            $tot = $this->pdo->prepare("SELECT
                    COALESCE(SUM(total_net), 0)   AS n,
                    COALESCE(SUM(total_vat), 0)   AS v,
                    COALESCE(SUM(total_gross), 0) AS g
                  FROM invoice_items WHERE invoice_id = :id");
            $tot->execute(['id' => $draftId]);
            $t = $tot->fetch();
            $this->pdo->prepare("UPDATE invoices SET total_net = :n, total_vat = :v, total_gross = :g WHERE id = :id")
                ->execute([
                    'n'  => $t['n'],
                    'v'  => $t['v'],
                    'g'  => $t['g'],
                    'id' => $draftId,
                ]);

            $this->pdo->commit();
            return $draftId;
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) $this->pdo->rollBack();
            throw $e;
        }
    }

    /**
     * Promoviert bezahlte Entwurfs-Rechnungen, deren Anreisetag in der Vergangenheit
     * liegt oder heute ist und 22:00 Uhr erreicht ist, auf Status 'finalized'.
     * Eine Entwurfs-Rechnung gilt als bereit, wenn ihr eine Rechnungsnummer
     * zugewiesen wurde (passiert beim vollständigen Bezahlen).
     *
     * @return int Anzahl der promovierten Rechnungen
     */
    public function promoteDraftsAfterArrival(): int
    {
        $sql = "UPDATE invoices i
                INNER JOIN bookings b ON b.id = i.booking_id
                   SET i.status = 'finalized'
                 WHERE i.status = 'draft'
                   AND i.invoice_no IS NOT NULL AND i.invoice_no <> ''
                   AND (
                       b.travel_from_date <  CURDATE()
                    OR (b.travel_from_date = CURDATE() AND TIME(NOW()) >= '22:00:00')
                   )";
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute();
            return $stmt->rowCount();
        } catch (Throwable $e) {
            return 0;
        }
    }

    public function items(int $invoiceId): array
    {
        $stmt = $this->pdo->prepare("SELECT * FROM invoice_items WHERE invoice_id = :id ORDER BY id ASC");
        $stmt->execute(['id' => $invoiceId]);
        return $stmt->fetchAll();
    }

    /**
     * Rechnungspräfix aus den Einstellungen — z.B. "RE", "INV", "R".
     * Default "RE", falls keine Setting vorhanden.
     */
    private function invoicePrefix(): string
    {
        static $cached = null;
        if ($cached !== null) return $cached;
        try {
            $stmt = $this->pdo->prepare("SELECT config_value FROM settings WHERE config_key = 'invoice_prefix' LIMIT 1");
            $stmt->execute();
            $v = (string)$stmt->fetchColumn();
            $cached = trim($v) !== '' ? trim($v) : 'RE';
        } catch (Throwable $e) {
            $cached = 'RE';
        }
        return $cached;
    }

    public function generateInvoiceNumber(): string
    {
        // 1) Recycelte Nummern bevorzugt (FIFO) — bereits vergebene Nummern,
        //    die nach Storno wieder freigegeben wurden, werden zuerst wieder ausgegeben.
        $stmt = $this->pdo->prepare("SELECT id, invoice_no FROM recycled_invoice_numbers
                                     WHERE used_at IS NULL
                                     ORDER BY id ASC LIMIT 1");
        $stmt->execute();
        $recycled = $stmt->fetch();
        if ($recycled) {
            $upd = $this->pdo->prepare("UPDATE recycled_invoice_numbers SET used_at = NOW() WHERE id = :id");
            $upd->execute(['id' => (int)$recycled['id']]);
            return $recycled['invoice_no'];
        }

        // 2) Format = "<PRÄFIX>#####". Präfix kommt aus den Einstellungen, der
        //    Zähler wird durch DB-Suche aller invoice_no, die mit dem Präfix beginnen,
        //    ermittelt: alles außer Ziffern wegparsen, Maximum suchen, +1.
        //    Beispiel: Präfix "RE" → "RE00001", "RE00002", … "RE00125".
        $prefix    = $this->invoicePrefix();
        $prefixLen = strlen($prefix);
        $maxN      = 0;

        $st = $this->pdo->prepare("SELECT invoice_no FROM invoices WHERE invoice_no LIKE :p");
        $st->execute(['p' => $prefix . '%']);
        foreach ($st->fetchAll(PDO::FETCH_COLUMN) as $no) {
            $rest   = substr((string)$no, $prefixLen);
            $digits = preg_replace('/[^0-9]/', '', $rest);
            if ($digits !== '') {
                $n = (int)$digits;
                if ($n > $maxN) $maxN = $n;
            }
        }
        // Auch den Recycling-Pool berücksichtigen, damit nichts kollidiert.
        $st = $this->pdo->prepare("SELECT invoice_no FROM recycled_invoice_numbers WHERE invoice_no LIKE :p");
        $st->execute(['p' => $prefix . '%']);
        foreach ($st->fetchAll(PDO::FETCH_COLUMN) as $no) {
            $rest   = substr((string)$no, $prefixLen);
            $digits = preg_replace('/[^0-9]/', '', $rest);
            if ($digits !== '') {
                $n = (int)$digits;
                if ($n > $maxN) $maxN = $n;
            }
        }

        $next = $maxN + 1;
        return $prefix . str_pad((string)$next, 5, '0', STR_PAD_LEFT);
    }

    /**
     * Voll-Löschung einer Rechnung inkl. invoice_items.
     */
    public function delete(int $id): bool
    {
        $this->pdo->beginTransaction();
        try {
            $stmt = $this->pdo->prepare("DELETE FROM invoice_items WHERE invoice_id = :id");
            $stmt->execute(['id' => $id]);
            $stmt = $this->pdo->prepare("DELETE FROM invoices WHERE id = :id");
            $stmt->execute(['id' => $id]);
            $this->pdo->commit();
            SearchIndexer::purge('invoice', $id);
            return true;
        } catch (Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }

    /**
     * Stellt eine Rechnungsnummer in den Recycling-Pool.
     */
    public function recycleNumber(string $invoiceNo, ?string $recycledBy = null): bool
    {
        // Aus jeder beliebigen Nummern-Form (alt: "RE-2024-0125", neu: "20240125",
        //  oder Mischformen wie "PAS-125") den Integer-Counter durch Strippen
        //  aller Nicht-Ziffern ableiten.
        $digits = preg_replace('/[^0-9]/', '', $invoiceNo);
        if ($digits === '') return false;
        $number = (int)$digits;
        // year-Spalte ist in der Tabelle NOT NULL — wir tragen das aktuelle Jahr
        //  ein, hat aber keine Bedeutung mehr für die Vergabe.
        $year = (int)date('Y');
        $stmt = $this->pdo->prepare("INSERT INTO recycled_invoice_numbers
                (invoice_no, year, number, recycled_by)
                VALUES (:no, :y, :n, :u)
                ON DUPLICATE KEY UPDATE used_at = NULL, recycled_at = NOW(), recycled_by = VALUES(recycled_by)");
        return $stmt->execute([
            'no' => $invoiceNo,
            'y'  => $year,
            'n'  => $number,
            'u'  => $recycledBy,
        ]);
    }

    /**
     * Synchronisiert eine bestehende Rechnung mit der aktuellen Buchung —
     * löscht und legt invoice_items neu an (inkl. Rabatt-Posten) und passt
     * invoice.total_* an. Liefert true, wenn eine Rechnung gefunden wurde.
     *
     * Wird nach nachträglichen Rabatt-Änderungen aufgerufen, damit
     * Buchungs-Brutto, Rechnungs-Brutto und Items konsistent bleiben.
     */
    public function rebuildForBooking(int $bookingId): bool
    {
        $invoice = $this->findByBooking($bookingId);
        if (!$invoice) return false;
        // Bereits festgeschriebene Rechnungen nicht mehr überschreiben — sonst würde
        // ein nachträglicher Posten die bereits bezahlte/abgeschlossene Rechnung
        // mutieren. Für solche Fälle wird stattdessen ein neuer Entwurf gepflegt.
        if (in_array((string)($invoice['status'] ?? ''), ['finalized', 'paid', 'cancelled'], true)) {
            return false;
        }
        $invoiceId = (int)$invoice['id'];

        $stmt = $this->pdo->prepare("SELECT * FROM bookings WHERE id = :id");
        $stmt->execute(['id' => $bookingId]);
        $booking = $stmt->fetch();
        if (!$booking) return false;

        $stmt = $this->pdo->prepare("SELECT bp.*, p.title AS product_title, p.description AS product_description
                                     FROM booking_products bp
                                     INNER JOIN products p ON p.id = bp.product_id
                                     WHERE bp.booking_id = :id
                                     ORDER BY bp.id ASC");
        $stmt->execute(['id' => $bookingId]);
        $items = $stmt->fetchAll();

        $this->pdo->beginTransaction();
        try {
            $this->pdo->prepare("DELETE FROM invoice_items WHERE invoice_id = :id")
                ->execute(['id' => $invoiceId]);

            $itemStmt = $this->pdo->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 (:invoice_id, :product_id, :title, :description, :quantity, :price_net, :vat_rate, :price_gross, :total_net, :total_vat, :total_gross)");

            $itemsSumNet = 0.0;
            $weightedVatNumer = 0.0;
            foreach ($items as $it) {
                $qty   = (int)$it['quantity'];
                $net   = (float)$it['price_net'];
                $vat   = (float)$it['vat_rate'];
                $gross = (float)$it['price_gross'];
                $totalNet   = round($qty * $net, 2);
                $totalGross = round($qty * $gross, 2);
                $itemsSumNet += $totalNet;
                $weightedVatNumer += $totalNet * $vat;
                $itemStmt->execute([
                    'invoice_id'  => $invoiceId,
                    'product_id'  => (int)$it['product_id'],
                    'title'       => $it['product_title'],
                    'description' => $it['product_description'],
                    'quantity'    => $qty,
                    'price_net'   => $net,
                    'vat_rate'    => $vat,
                    'price_gross' => $gross,
                    'total_net'   => $totalNet,
                    'total_vat'   => round($totalGross - $totalNet, 2),
                    'total_gross' => $totalGross,
                ]);
            }

            $discount = (float)($booking['discount_amount'] ?? 0);
            if ($discount > 0) {
                $avgVat = $itemsSumNet > 0 ? round($weightedVatNumer / $itemsSumNet, 2) : 0.0;
                $rabattNet = round($discount / (1 + $avgVat / 100), 2);
                $rabattVat = round($discount - $rabattNet, 2);
                $itemStmt->execute([
                    'invoice_id'  => $invoiceId,
                    'product_id'  => null,
                    'title'       => $booking['discount_label'] ?? 'Rabatt',
                    'description' => 'Rabatt',
                    'quantity'    => 1,
                    'price_net'   => -$rabattNet,
                    'vat_rate'    => $avgVat,
                    'price_gross' => -$discount,
                    'total_net'   => -$rabattNet,
                    'total_vat'   => -$rabattVat,
                    'total_gross' => -$discount,
                ]);
            }

            $this->pdo->prepare("UPDATE invoices SET
                    total_net = :n, total_vat = :v, total_gross = :g,
                    pdf_path = NULL, html_path = NULL
                WHERE id = :id")
                ->execute([
                    'n'  => $booking['total_net'],
                    'v'  => $booking['total_vat'],
                    'g'  => $booking['total_gross'],
                    'id' => $invoiceId,
                ]);

            $this->pdo->commit();
            return true;
        } catch (Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }

    /**
     * Erstellt eine Rechnung aus einer Buchung. Wirft Exception, wenn schon eine existiert.
     */
    public function createFromBooking(int $bookingId): int
    {
        $existing = $this->findByBooking($bookingId);
        if ($existing) {
            throw new RuntimeException('Für diese Buchung existiert bereits eine Rechnung (' . $existing['invoice_no'] . ').');
        }

        $stmt = $this->pdo->prepare("SELECT * FROM bookings WHERE id = :id");
        $stmt->execute(['id' => $bookingId]);
        $booking = $stmt->fetch();
        if (!$booking) {
            throw new RuntimeException('Buchung nicht gefunden.');
        }

        $stmt = $this->pdo->prepare("SELECT bp.*, p.title AS product_title, p.description AS product_description
                                     FROM booking_products bp
                                     INNER JOIN products p ON p.id = bp.product_id
                                     WHERE bp.booking_id = :id
                                     ORDER BY bp.id ASC");
        $stmt->execute(['id' => $bookingId]);
        $items = $stmt->fetchAll();

        $this->pdo->beginTransaction();
        try {
            $invoiceNo = $this->generateInvoiceNumber();

            // Rechnungsdatum = bookings.travel_from (Anreisedatum). Fallback heute,
            //  wenn aus irgendeinem Grund kein Reisedatum gesetzt ist.
            $tf = (string)($booking['travel_from'] ?? '');
            $invDate = ($tf !== '' && substr($tf, 0, 10) !== '0000-00-00') ? substr($tf, 0, 10) : date('Y-m-d');
            $insert = $this->pdo->prepare("INSERT INTO invoices
                (invoice_no, booking_id, customer_id, invoice_date, status, total_net, total_vat, total_gross)
                VALUES (:invoice_no, :booking_id, :customer_id, :invoice_date, :status, :total_net, :total_vat, :total_gross)");
            $insert->execute([
                'invoice_no'   => $invoiceNo,
                'booking_id'   => $bookingId,
                'customer_id'  => (int)$booking['customer_id'],
                'invoice_date' => $invDate,
                'status'       => 'issued',
                'total_net'    => $booking['total_net'],
                'total_vat'    => $booking['total_vat'],
                'total_gross'  => $booking['total_gross'],
            ]);
            $invoiceId = (int)$this->pdo->lastInsertId();

            $itemStmt = $this->pdo->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 (:invoice_id, :product_id, :title, :description, :quantity, :price_net, :vat_rate, :price_gross, :total_net, :total_vat, :total_gross)");

            $itemsSumNet = 0.0;
            $itemsSumGross = 0.0;
            $weightedVatNumer = 0.0;
            foreach ($items as $it) {
                $qty   = (int)$it['quantity'];
                $net   = (float)$it['price_net'];
                $vat   = (float)$it['vat_rate'];
                $gross = (float)$it['price_gross'];
                $totalNet   = round($qty * $net, 2);
                $totalGross = round($qty * $gross, 2);
                $itemsSumNet   += $totalNet;
                $itemsSumGross += $totalGross;
                $weightedVatNumer += $totalNet * $vat;
                $itemStmt->execute([
                    'invoice_id'  => $invoiceId,
                    'product_id'  => (int)$it['product_id'],
                    'title'       => $it['product_title'],
                    'description' => $it['product_description'],
                    'quantity'    => $qty,
                    'price_net'   => $net,
                    'vat_rate'    => $vat,
                    'price_gross' => $gross,
                    'total_net'   => $totalNet,
                    'total_vat'   => round($totalGross - $totalNet, 2),
                    'total_gross' => $totalGross,
                ]);
            }

            // Rabatt als negativen Posten anfügen — Items + Rabatt = invoice.total_gross.
            $discountAmount = (float)($booking['discount_amount'] ?? 0);
            if ($discountAmount > 0) {
                $discountLabel = (string)($booking['discount_label'] ?? 'Rabatt');
                $avgVat = $itemsSumNet > 0 ? round($weightedVatNumer / $itemsSumNet, 2) : 0.0;
                $rabattNet = round($discountAmount / (1 + $avgVat / 100), 2);
                $rabattVat = round($discountAmount - $rabattNet, 2);
                $itemStmt->execute([
                    'invoice_id'  => $invoiceId,
                    'product_id'  => null,
                    'title'       => $discountLabel,
                    'description' => 'Rabatt',
                    'quantity'    => 1,
                    'price_net'   => -$rabattNet,
                    'vat_rate'    => $avgVat,
                    'price_gross' => -$discountAmount,
                    'total_net'   => -$rabattNet,
                    'total_vat'   => -$rabattVat,
                    'total_gross' => -$discountAmount,
                ]);
            }

            $this->pdo->commit();
            $this->syncSearchIndex($invoiceId);
            return $invoiceId;
        } catch (Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }

    public function markPaid(int $id): bool
    {
        $stmt = $this->pdo->prepare("UPDATE invoices SET status = 'paid' WHERE id = :id");
        $ok = $stmt->execute(['id' => $id]);
        if ($ok) $this->syncSearchIndex($id);
        return $ok;
    }

    /**
     * Re-synchronisiert eine Rechnung in den Such-Index (nach create/update).
     */
    public function syncSearchIndex(int $id): void
    {
        $stmt = $this->pdo->prepare(
            "SELECT i.id, i.invoice_no, i.invoice_date, i.updated_at,
                    b.booking_no,
                    c.first_name, c.last_name, c.company
               FROM invoices i
               INNER JOIN bookings  b ON b.id = i.booking_id
               INNER JOIN customers c ON c.id = i.customer_id
              WHERE i.id = :id"
        );
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();
        if ($row) SearchIndexer::syncInvoice($row);
    }

    /**
     * Rechnungsdatum = bookings.travel_from der verknüpften Buchung.
     * Fallback heute, falls travel_from leer/NULL ist.
     */
    public function touchInvoiceDate(int $id, ?string $date = null): bool
    {
        if ($date === null) {
            $stmt = $this->pdo->prepare(
                "SELECT b.travel_from FROM invoices i
                   INNER JOIN bookings b ON b.id = i.booking_id
                  WHERE i.id = :id LIMIT 1"
            );
            $stmt->execute(['id' => $id]);
            $tf = (string)($stmt->fetchColumn() ?: '');
            $date = ($tf !== '' && substr($tf, 0, 10) !== '0000-00-00') ? substr($tf, 0, 10) : date('Y-m-d');
        }
        $stmt = $this->pdo->prepare("UPDATE invoices SET invoice_date = :d WHERE id = :id");
        $ok = $stmt->execute(['d' => $date, 'id' => $id]);
        if ($ok) $this->syncSearchIndex($id);
        return $ok;
    }

    /**
     * Prüft, ob die Rechnung ein Entwurf ohne Nummer ist und die Zahlungen
     * total_gross decken — wenn ja, wird eine Rechnungsnummer vergeben und
     * invoice_date auf heute gesetzt. Status bleibt 'draft' (Promotion auf
     * 'finalized' passiert separat via promoteDraftsAfterArrival).
     * Liefert die vergebene Nummer oder null, wenn nichts zu tun war.
     */
    public function assignNumberIfDraftFullyPaid(int $invoiceId): ?string
    {
        $stmt = $this->pdo->prepare("SELECT id, invoice_no, status, total_gross FROM invoices WHERE id = :id");
        $stmt->execute(['id' => $invoiceId]);
        $inv = $stmt->fetch();
        if (!$inv) return null;
        if ((string)($inv['status'] ?? '') !== 'draft') return null;
        if (trim((string)($inv['invoice_no'] ?? '')) !== '') return null;
        $gross = (float)$inv['total_gross'];
        if ($gross <= 0) return null;
        // Summe aller Zahlungen, die direkt dieser Rechnung zugeordnet sind.
        $stmt = $this->pdo->prepare("SELECT COALESCE(SUM(amount), 0) FROM payments WHERE invoice_id = :id");
        $stmt->execute(['id' => $invoiceId]);
        $paid = (float)$stmt->fetchColumn();
        if ($paid + 0.005 < $gross) return null;
        $no = $this->generateInvoiceNumber();
        $stmt = $this->pdo->prepare("UPDATE invoices SET invoice_no = :n WHERE id = :id");
        $stmt->execute(['n' => $no, 'id' => $invoiceId]);
        // Rechnungsdatum aus der zugehörigen Buchung ableiten (travel_from).
        $this->touchInvoiceDate($invoiceId);
        $this->syncSearchIndex($invoiceId);
        return $no;
    }

    public function updateStatus(int $id, string $status): bool
    {
        $stmt = $this->pdo->prepare("UPDATE invoices SET status = :s WHERE id = :id");
        $ok = $stmt->execute(['s' => $status, 'id' => $id]);
        if ($ok) $this->syncSearchIndex($id);
        return $ok;
    }

    public function updateHtmlPath(int $id, string $path): bool
    {
        $stmt = $this->pdo->prepare("UPDATE invoices SET html_path = :p WHERE id = :id");
        return $stmt->execute(['p' => $path, 'id' => $id]);
    }

    public function updatePdfPath(int $id, string $path): bool
    {
        $stmt = $this->pdo->prepare("UPDATE invoices SET pdf_path = :p WHERE id = :id");
        return $stmt->execute(['p' => $path, 'id' => $id]);
    }
}