<?php
declare(strict_types=1);

namespace Itdatex\Rechnungseingang;

if ( ! defined( 'ABSPATH' ) ) { exit; }

/**
 * Sammelstelle aller DB-Zugriffe.
 * KEIN SQL ausserhalb dieser Klasse — das macht Migrationen und
 * Test-Doubles spaeter viel einfacher.
 */
final class InvoiceRepository
{
    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;
        $value = $wpdb->get_var( "SELECT chain_hash FROM {$this->tableFiles()} ORDER BY id DESC LIMIT 1" );
        return is_string( $value ) ? $value : '';
    }

    public function findFileBySha( string $sha256 ): ?object
    {
        global $wpdb;
        $row = $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM {$this->tableFiles()} WHERE sha256 = %s LIMIT 1",
            $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 {$this->tableFiles()} WHERE id = %d LIMIT 1",
            $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'           => 'incoming',
                '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 {$this->tableInvoices()} WHERE id = %d LIMIT 1",
            $id
        ) );
        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 {$this->tableInvoices()} ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d",
            $limit, $offset
        ) ) ?: [];
    }

    public function countInvoices(): int
    {
        global $wpdb;
        return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$this->tableInvoices()}" );
    }

    /**
     * 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 ) );
        $sql = "SELECT * FROM {$this->tableInvoices()} WHERE " . implode( ' AND ', $where )
             . " ORDER BY issue_date ASC, id ASC LIMIT %d";
        $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;
        $sql = "SELECT * FROM {$this->tableInvoices()}
                WHERE payment_status = 'open'
                ORDER BY due_date IS NULL ASC, due_date ASC, id ASC
                LIMIT %d";
        $rows = $wpdb->get_results( $wpdb->prepare( $sql, 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 {$this->tableInvoices()}
             WHERE payment_status = 'open'
             ORDER BY due_date ASC
             LIMIT %d", 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: {$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.
     *
     * @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',
        ];
        /**
         * Filters the list of invoice columns that may be set via
         * `do_action( 'reing_apply_invoice_fields', $id, $fields )`.
         * Add-ons that need extra columns can extend the list here.
         *
         * @param string[] $fields Allowed column names.
         */
        $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 {$this->tableAudit()} WHERE invoice_id = %d ORDER BY id ASC",
            $invoiceId
        ) ) ?: [];
    }

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

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