<?php
declare(strict_types=1);

namespace Itdatex\EInvoiceCore;

/**
 * Sammelstelle aller DB-Zugriffe.
 * KEIN SQL ausserhalb dieser Klasse — das macht Migrationen und
 * Test-Doubles spaeter viel einfacher.
 *
 * Direction-Parameter (incoming/outgoing) entscheidet, in welche Richtung
 * insertInvoice() schreibt. Default 'incoming' fuer Bestands-Aufrufer.
 */
final class InvoiceRepository
{
    public function __construct( private readonly string $direction = 'incoming' ) {}

    public function tableInvoices(): string
    {
        global $wpdb;
        return $wpdb->prefix . 'reing_invoices';
    }

    public function tableFiles(): string
    {
        global $wpdb;
        return $wpdb->prefix . 'reing_files';
    }

    public function tableAudit(): string
    {
        global $wpdb;
        return $wpdb->prefix . 'reing_audit_log';
    }

    /* ------------------------------------------------------------ FILES */

    /**
     * Holt den chain_hash der zuletzt eingefuegten Datei.
     * Bei leerer Tabelle: Leerstring (Storage baut dann die Genesis-Zeile).
     */
    public function lastChainHash(): string
    {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Caching disabled: this value must reflect the very last insert (hash-chain integrity).
        $value = $wpdb->get_var( $wpdb->prepare(
            'SELECT chain_hash FROM %i ORDER BY id DESC LIMIT 1',
            $this->tableFiles()
        ) );
        return is_string( $value ) ? $value : '';
    }

    public function findFileBySha( string $sha256 ): ?object
    {
        global $wpdb;
        $row = $wpdb->get_row( $wpdb->prepare(
            'SELECT * FROM %i WHERE sha256 = %s LIMIT 1',
            $this->tableFiles(),
            $sha256
        ) );
        return $row ?: null;
    }

    /**
     * @param array{stored_name:string, original_name:string, mime:string, size_bytes:int, sha256:string, chain_hash:string} $data
     * @return int Insert-ID
     */
    public function insertFile( array $data, string $source = 'upload' ): int
    {
        global $wpdb;
        $wpdb->insert(
            $this->tableFiles(),
            [
                'stored_name'   => $data['stored_name'],
                'original_name' => $data['original_name'],
                'mime'          => $data['mime'],
                'size_bytes'    => $data['size_bytes'],
                'sha256'        => $data['sha256'],
                'chain_hash'    => $data['chain_hash'],
                'source'        => $source,
                'created_at'    => current_time( 'mysql' ),
            ],
            [ '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s' ]
        );
        return (int) $wpdb->insert_id;
    }

    public function findFile( int $id ): ?object
    {
        global $wpdb;
        $row = $wpdb->get_row( $wpdb->prepare(
            'SELECT * FROM %i WHERE id = %d LIMIT 1',
            $this->tableFiles(),
            $id
        ) );
        return $row ?: null;
    }

    /* --------------------------------------------------------- INVOICES */

    public function insertInvoice(
        int $fileId,
        InvoiceData $invoice,
        ValidationReport $report,
        string $rawMeta = ''
    ): int {
        global $wpdb;
        $now = current_time( 'mysql' );
        $wpdb->insert(
            $this->tableInvoices(),
            [
                'file_id'             => $fileId,
                'direction'           => $this->direction,
                'format'              => $invoice->format->value,
                'invoice_number'      => $invoice->invoiceNumber,
                'issue_date'          => $invoice->issueDate?->format( 'Y-m-d' ),
                'due_date'            => $invoice->dueDate?->format( 'Y-m-d' ),
                'seller_name'         => $invoice->sellerName,
                'seller_vat_id'       => $invoice->sellerVatId,
                'buyer_name'          => $invoice->buyerName,
                'buyer_reference'     => $invoice->buyerReference,
                'total_net'           => $invoice->totalNet,
                'total_tax'           => $invoice->totalTax,
                'total_gross'         => $invoice->totalGross,
                'currency'            => $invoice->currency,
                'line_count'          => $invoice->lineCount,
                'validation_status'   => self::statusFromReport( $report ),
                'validation_errors'   => $report->errorCount(),
                'validation_warnings' => $report->warningCount(),
                'validation_report'   => wp_json_encode( $report->toArray() ),
                'payment_status'      => 'open',
                'extracted_by'        => 'parser',
                'raw_meta'            => $rawMeta,
                'created_at'          => $now,
                'updated_at'          => $now,
            ],
            [
                '%d','%s','%s','%s','%s','%s','%s','%s','%s','%s',
                '%f','%f','%f','%s','%d','%s','%d','%d','%s','%s','%s','%s','%s','%s',
            ]
        );
        return (int) $wpdb->insert_id;
    }

    public function findInvoice( int $id ): ?object
    {
        global $wpdb;
        $row = $wpdb->get_row( $wpdb->prepare(
            'SELECT * FROM %i WHERE id = %d LIMIT 1',
            $this->tableInvoices(),
            $id
        ) );
        return $row ?: null;
    }

    /**
     * Findet die Rechnung, die auf die uebergebene file_id zeigt.
     * Genutzt z.B. fuer Duplikat-Anzeigen ("Datei kennen wir schon, gehoert
     * zu Rechnung X") und fuer Download-Authorisierung ueber die direction.
     */
    public function findInvoiceByFileId( int $fileId ): ?object
    {
        global $wpdb;
        $row = $wpdb->get_row( $wpdb->prepare(
            'SELECT * FROM %i WHERE file_id = %d LIMIT 1',
            $this->tableInvoices(),
            $fileId
        ) );
        return $row ?: null;
    }

    /**
     * @return object[]
     */
    public function listInvoices( int $limit = 100, int $offset = 0 ): array
    {
        global $wpdb;
        return $wpdb->get_results( $wpdb->prepare(
            'SELECT * FROM %i ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d',
            $this->tableInvoices(),
            $limit, $offset
        ) ) ?: [];
    }

    public function countInvoices(): int
    {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Caching disabled: admin dashboard needs the live total, not a stale count.
        return (int) $wpdb->get_var( $wpdb->prepare(
            'SELECT COUNT(*) FROM %i',
            $this->tableInvoices()
        ) );
    }

    /**
     * Listet Rechnungen mit gesetzter direction. Ohne Argument: die direction
     * dieser Repository-Instanz (default 'incoming').
     *
     * @return object[]
     */
    public function listByDirection( ?string $direction = null, int $limit = 200 ): array
    {
        global $wpdb;
        $direction = $direction ?? $this->direction;
        $rows = $wpdb->get_results( $wpdb->prepare(
            'SELECT id, invoice_number, issue_date, due_date,
                    seller_name, buyer_name, total_gross, currency,
                    validation_status, payment_status, created_at
             FROM %i
             WHERE direction = %s
             ORDER BY created_at DESC, id DESC
             LIMIT %d',
            $this->tableInvoices(),
            $direction, max( 1, min( 5000, $limit ) )
        ) );
        return is_array( $rows ) ? $rows : [];
    }

    public function countByDirection( ?string $direction = null ): int
    {
        global $wpdb;
        $direction = $direction ?? $this->direction;
        return (int) $wpdb->get_var( $wpdb->prepare(
            'SELECT COUNT(*) FROM %i WHERE direction = %s',
            $this->tableInvoices(),
            $direction
        ) );
    }

    /**
     * Listet Rechnungen fuer den Export, mit optionalen Filtern.
     *
     * @param string|null $from   YYYY-MM-DD oder null
     * @param string|null $to     YYYY-MM-DD oder null
     * @param string|null $status 'valid' | 'warnings' | 'invalid' | 'unchecked' | null
     * @param int         $limit  Sicherheitslimit. Default 5000.
     *
     * @return object[]
     */
    public function listForExport( ?string $from, ?string $to, ?string $status, int $limit = 5000 ): array
    {
        global $wpdb;
        $where  = [ '1=1' ];
        $params = [];
        if ( $from && preg_match( '/^\d{4}-\d{2}-\d{2}$/', $from ) ) {
            $where[]  = 'issue_date >= %s';
            $params[] = $from;
        }
        if ( $to && preg_match( '/^\d{4}-\d{2}-\d{2}$/', $to ) ) {
            $where[]  = 'issue_date <= %s';
            $params[] = $to;
        }
        if ( $status && in_array( $status, [ 'valid', 'warnings', 'invalid', 'unchecked' ], true ) ) {
            $where[]  = 'validation_status = %s';
            $params[] = $status;
        }
        $params[] = max( 1, min( 50000, $limit ) );
        array_unshift( $params, $this->tableInvoices() );
        $sql = 'SELECT * FROM %i WHERE ' . implode( ' AND ', $where )
             . ' ORDER BY issue_date ASC, id ASC LIMIT %d';
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- $where contains only hardcoded fragments; all values are bound via %s / %d / %i placeholders in $params.
        $rows = $wpdb->get_results( $wpdb->prepare( $sql, ...$params ) );
        return is_array( $rows ) ? $rows : [];
    }

    /**
     * Liefert alle Rechnungen mit payment_status='open', sortiert nach
     * due_date ASC (NULLs zuletzt). Limit per Default 200 — fuer das
     * Dashboard reicht das, mehr wuerde sowieso unuebersichtlich.
     *
     * @return object[]
     */
    public function listOpenInvoices( int $limit = 200 ): array
    {
        global $wpdb;
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM %i
                WHERE payment_status = 'open'
                ORDER BY due_date IS NULL ASC, due_date ASC, id ASC
                LIMIT %d",
            $this->tableInvoices(),
            max( 1, min( 5000, $limit ) )
        ) );
        return is_array( $rows ) ? $rows : [];
    }

    /**
     * Aggregiert offene Rechnungen in vier Faelligkeits-Buckets + 'no_date':
     *   - overdue:    due_date < heute
     *   - this_week:  heute ≤ due_date ≤ heute + 7
     *   - this_month: heute + 7 < due_date ≤ Monatsende
     *   - later:      due_date > Monatsende
     *   - no_date:    due_date IS NULL
     *
     * @return array<string, array{count:int, sum:float, currency:string}>
     */
    public function summarizeOpenInvoices(): array
    {
        global $wpdb;
        $today      = current_time( 'Y-m-d' );
        $weekEnd    = gmdate( 'Y-m-d', strtotime( $today . ' +7 days' ) );
        $monthEnd   = gmdate( 'Y-m-t', strtotime( $today ) );

        // Single Query, dann lokal aggregieren — bleibt ohnehin schnell, weil
        // der Dashboard nur offene Rechnungen anschaut.
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT due_date, total_gross, currency
             FROM %i
             WHERE payment_status = 'open'
             ORDER BY due_date ASC
             LIMIT %d",
            $this->tableInvoices(),
            5000
        ) );
        $rows = is_array( $rows ) ? $rows : [];

        $buckets = [
            'overdue'    => [ 'count' => 0, 'sum' => 0.0, 'currency' => 'EUR' ],
            'this_week'  => [ 'count' => 0, 'sum' => 0.0, 'currency' => 'EUR' ],
            'this_month' => [ 'count' => 0, 'sum' => 0.0, 'currency' => 'EUR' ],
            'later'      => [ 'count' => 0, 'sum' => 0.0, 'currency' => 'EUR' ],
            'no_date'    => [ 'count' => 0, 'sum' => 0.0, 'currency' => 'EUR' ],
        ];

        foreach ( $rows as $r ) {
            $key = self::bucketForDate( (string) ( $r->due_date ?? '' ), $today, $weekEnd, $monthEnd );
            $buckets[ $key ]['count']++;
            $buckets[ $key ]['sum'] += (float) ( $r->total_gross ?? 0 );
        }
        return $buckets;
    }

    private static function bucketForDate( string $due, string $today, string $weekEnd, string $monthEnd ): string
    {
        if ( $due === '' || $due === '0000-00-00' ) {
            return 'no_date';
        }
        if ( $due < $today )    return 'overdue';
        if ( $due <= $weekEnd ) return 'this_week';
        if ( $due <= $monthEnd ) return 'this_month';
        return 'later';
    }

    public function setPaymentStatus( int $id, string $status ): void
    {
        global $wpdb;
        if ( ! in_array( $status, [ 'open', 'paid', 'disputed' ], true ) ) {
            throw new \InvalidArgumentException( 'Ungueltiger payment_status: ' . esc_html( $status ) );
        }
        $wpdb->update(
            $this->tableInvoices(),
            [ 'payment_status' => $status, 'updated_at' => current_time( 'mysql' ) ],
            [ 'id' => $id ],
            [ '%s', '%s' ],
            [ '%d' ]
        );
    }

    /**
     * Whitelist von Feldern, die ueber die Apply-Schnittstelle aktualisiert
     * werden duerfen. Auf wp.org-Review-Sicherheit ausgelegt: explizite Liste,
     * kein "alle Spalten zulassen". Add-ons koennen die Liste filtern.
     *
     * Hook-Name 'reing_applyable_fields' bleibt aus Kompat-Gruenden — beim
     * naechsten breaking-Release auf neutralen Namen umstellen.
     *
     * @return string[]
     */
    public static function applyableFields(): array
    {
        $defaults = [
            'invoice_number',
            'issue_date',
            'due_date',
            'seller_name',
            'seller_vat_id',
            'buyer_name',
            'total_net',
            'total_tax',
            'total_gross',
            'currency',
        ];
        $filtered = apply_filters( 'reing_applyable_fields', $defaults );
        return is_array( $filtered ) ? array_values( array_unique( array_map( 'strval', $filtered ) ) ) : $defaults;
    }

    /**
     * Updated einzelne, gewhitelistete Felder einer existierenden Rechnung.
     * Type-Casting + Datums-Sanitisierung passiert hier — Aufrufer schicken
     * Rohwerte aus z.B. einer KI-Antwort, wir prozessieren defensiv.
     *
     * @param array<string, mixed> $fields
     * @return string[] Liste der tatsaechlich aktualisierten Spaltennamen.
     */
    public function updateFields( int $id, array $fields ): array
    {
        global $wpdb;
        $whitelist = self::applyableFields();
        $data = [];
        $format = [];
        foreach ( $whitelist as $col ) {
            if ( ! array_key_exists( $col, $fields ) ) {
                continue;
            }
            $raw = $fields[ $col ];
            if ( $raw === null || $raw === '' ) {
                continue;
            }
            switch ( $col ) {
                case 'issue_date':
                case 'due_date':
                    $d = self::normalizeDate( $raw );
                    if ( $d !== null ) {
                        $data[ $col ] = $d;
                        $format[]     = '%s';
                    }
                    break;
                case 'total_net':
                case 'total_tax':
                case 'total_gross':
                    if ( is_numeric( $raw ) ) {
                        $data[ $col ] = (float) $raw;
                        $format[]     = '%f';
                    }
                    break;
                case 'currency':
                    $c = strtoupper( preg_replace( '/[^A-Za-z]/', '', (string) $raw ) ?? '' );
                    if ( strlen( $c ) === 3 ) {
                        $data[ $col ] = $c;
                        $format[]     = '%s';
                    }
                    break;
                default:
                    $s = trim( (string) $raw );
                    if ( $s !== '' ) {
                        $data[ $col ] = $s;
                        $format[]     = '%s';
                    }
            }
        }
        if ( ! $data ) {
            return [];
        }
        $data['updated_at']   = current_time( 'mysql' );
        $format[]             = '%s';
        $data['extracted_by'] = 'ai_draft';
        $format[]             = '%s';

        $wpdb->update(
            $this->tableInvoices(),
            $data,
            [ 'id' => $id ],
            $format,
            [ '%d' ]
        );

        return array_values( array_diff( array_keys( $data ), [ 'updated_at', 'extracted_by' ] ) );
    }

    private static function normalizeDate( $value ): ?string
    {
        $value = trim( (string) $value );
        if ( $value === '' ) {
            return null;
        }
        // Akzeptiere YYYY-MM-DD oder DD.MM.YYYY
        if ( preg_match( '/^\d{4}-\d{2}-\d{2}$/', $value ) ) {
            return $value;
        }
        if ( preg_match( '/^(\d{1,2})\.(\d{1,2})\.(\d{4})$/', $value, $m ) ) {
            return sprintf( '%04d-%02d-%02d', (int) $m[3], (int) $m[2], (int) $m[1] );
        }
        $ts = strtotime( $value );
        return $ts === false ? null : gmdate( 'Y-m-d', $ts );
    }

    /* ------------------------------------------------------------ AUDIT */

    public function appendAudit( ?int $invoiceId, string $actor, string $action, string $detail = '' ): void
    {
        global $wpdb;
        $wpdb->insert(
            $this->tableAudit(),
            [
                'invoice_id' => $invoiceId,
                'actor'      => $actor,
                'action'     => $action,
                'detail'     => $detail,
                'created_at' => current_time( 'mysql' ),
            ],
            [ '%d', '%s', '%s', '%s', '%s' ]
        );
    }

    /**
     * @return object[]
     */
    public function auditForInvoice( int $invoiceId ): array
    {
        global $wpdb;
        return $wpdb->get_results( $wpdb->prepare(
            'SELECT * FROM %i WHERE invoice_id = %d ORDER BY id ASC',
            $this->tableAudit(),
            $invoiceId
        ) ) ?: [];
    }

    /* ---------------------------------------------------------- HELPER */

    public static function statusFromReport( ValidationReport $r ): string
    {
        if ( $r->errorCount() > 0 ) return 'invalid';
        if ( $r->warningCount() > 0 ) return 'warnings';
        return 'valid';
    }
}
