<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class PayoneFailedPaypalReport extends Command
{
    protected $signature = 'payone:failed-paypal-report
                            {--from=2026-04-02 : Start-Datum (YYYY-MM-DD)}
                            {--to= : End-Datum (YYYY-MM-DD), Standard: heute}
                            {--output=storage/reports/paypal-failed-report.csv : Ausgabedatei}';

    protected $description = 'Erstellt einen Schadenbericht über fehlgeschlagene PayPal-Zahlungen (Error 923)';

    public function handle(): int
    {
        $from = $this->option('from');
        $to = $this->option('to') ?: now()->format('Y-m-d');
        $outputPath = $this->option('output');

        $this->info("Schadenbericht PayPal-Ausfälle: {$from} bis {$to}");
        $this->newLine();

        $orders = $this->getAffectedOrders($from, $to);

        if ($orders->isEmpty()) {
            $this->warn('Keine fehlgeschlagenen PayPal-Zahlungen im angegebenen Zeitraum gefunden.');

            return self::SUCCESS;
        }

        $this->displaySummary($orders, $from, $to);

        $fullPath = base_path($outputPath);
        $dir = dirname($fullPath);
        if (! is_dir($dir)) {
            mkdir($dir, 0755, true);
        }

        $this->writeCsvReport($fullPath, $orders, $from, $to);
        $this->writeTxtReport(str_replace('.csv', '.txt', $fullPath), $orders, $from, $to);
        $this->writeEmailLists($dir, $orders);

        $this->newLine();
        $this->info("CSV-Bericht:          {$fullPath}");
        $this->info('TXT-Bericht:          ' . str_replace('.csv', '.txt', $fullPath));
        $this->info("E-Mail Berater:       {$dir}/emails-berater.csv");
        $this->info("E-Mail Shop-Kunden:   {$dir}/emails-shop-kunden.csv");

        return self::SUCCESS;
    }

    private function getAffectedOrders(string $from, string $to): \Illuminate\Support\Collection
    {
        return DB::table('shopping_orders')
            ->join('shopping_payments', function ($join) {
                $join->on('shopping_payments.shopping_order_id', '=', 'shopping_orders.id')
                    ->where('shopping_payments.clearingtype', '=', 'wlt')
                    ->where('shopping_payments.wallettype', '=', 'PPE');
            })
            ->join('payment_transactions', function ($join) {
                $join->on('payment_transactions.shopping_payment_id', '=', 'shopping_payments.id')
                    ->where('payment_transactions.errorcode', '=', 923);
            })
            ->join('shopping_users', 'shopping_users.id', '=', 'shopping_orders.shopping_user_id')
            ->whereBetween('payment_transactions.created_at', ["{$from} 00:00:00", "{$to} 23:59:59"])
            ->select(
                'shopping_orders.id as order_id',
                'shopping_orders.total_shipping',
                'shopping_orders.paid',
                'shopping_orders.txaction',
                'shopping_orders.mode',
                'shopping_orders.payment_for',
                'shopping_orders.auth_user_id',
                'shopping_orders.created_at as order_date',
                'shopping_users.billing_email',
                'shopping_users.billing_firstname',
                'shopping_users.billing_lastname',
                'shopping_payments.id as payment_id',
                'shopping_payments.reference',
                'shopping_payments.amount as amount_cents',
                'shopping_payments.currency',
                'payment_transactions.id as tx_id',
                'payment_transactions.errorcode',
                'payment_transactions.errormessage',
                'payment_transactions.created_at as error_date',
            )
            ->orderBy('payment_transactions.created_at')
            ->get();
    }

    private function displaySummary(\Illuminate\Support\Collection $rows, string $from, string $to): void
    {
        $uniqueOrders = $rows->unique('order_id');
        $paidOrders = $uniqueOrders->where('paid', 1);
        $unpaidOrders = $uniqueOrders->where('paid', 0);

        $this->table(
            ['Kennzahl', 'Wert'],
            [
                ['Zeitraum', "{$from} bis {$to}"],
                ['Fehlgeschlagene Transaktionen (Error 923)', $rows->count()],
                ['Betroffene Bestellungen (eindeutig)', $uniqueOrders->count()],
                ['Davon nachträglich bezahlt (andere Zahlungsart)', $paidOrders->count()],
                ['Nicht bezahlt (offen/verloren)', $unpaidOrders->count()],
                ['Summe nicht bezahlter Bestellungen', number_format($unpaidOrders->sum('total_shipping'), 2, ',', '.') . ' EUR'],
                ['Summe aller betroffenen Bestellungen', number_format($uniqueOrders->sum('total_shipping'), 2, ',', '.') . ' EUR'],
            ]
        );
    }

    private function writeCsvReport(string $path, \Illuminate\Support\Collection $rows, string $from, string $to): void
    {
        $fp = fopen($path, 'w');

        fprintf($fp, chr(0xEF) . chr(0xBB) . chr(0xBF));

        fputcsv($fp, [
            'Fehler-Datum',
            'Bestell-Nr',
            'Bestell-Datum',
            'Transaktions-ID',
            'Payment-Referenz',
            'Betrag (EUR)',
            'Fehlercode',
            'Fehlermeldung',
            'Modus',
            'Nachträglich bezahlt',
            'Aktueller Status',
        ], ';');

        $uniqueOrders = $rows->unique('order_id');
        $unpaidOrders = $uniqueOrders->where('paid', 0);

        foreach ($rows as $row) {
            fputcsv($fp, [
                $row->error_date,
                $row->order_id,
                $row->order_date,
                $row->tx_id,
                $row->reference,
                number_format($row->total_shipping, 2, ',', ''),
                $row->errorcode,
                $row->errormessage,
                $row->mode,
                $row->paid ? 'Ja' : 'Nein',
                $row->txaction,
            ], ';');
        }

        fputcsv($fp, [], ';');
        fputcsv($fp, ['ZUSAMMENFASSUNG'], ';');
        fputcsv($fp, ['Zeitraum', "{$from} bis {$to}"], ';');
        fputcsv($fp, ['Fehlgeschlagene Transaktionen', $rows->count()], ';');
        fputcsv($fp, ['Betroffene Bestellungen', $uniqueOrders->count()], ';');
        fputcsv($fp, ['Nicht bezahlt (offen/verloren)', $unpaidOrders->count()], ';');
        fputcsv($fp, ['Summe nicht bezahlter Bestellungen', number_format($unpaidOrders->sum('total_shipping'), 2, ',', '') . ' EUR'], ';');
        fputcsv($fp, ['Summe aller betroffenen Bestellungen', number_format($uniqueOrders->sum('total_shipping'), 2, ',', '') . ' EUR'], ';');

        fclose($fp);
    }

    private function writeTxtReport(string $path, \Illuminate\Support\Collection $rows, string $from, string $to): void
    {
        $uniqueOrders = $rows->unique('order_id');
        $paidOrders = $uniqueOrders->where('paid', 1);
        $unpaidOrders = $uniqueOrders->where('paid', 0);

        $lines = [];
        $lines[] = '================================================================================';
        $lines[] = '  SCHADENBERICHT: Fehlgeschlagene PayPal-Zahlungen (PAYONE Error 923)';
        $lines[] = '================================================================================';
        $lines[] = '';
        $lines[] = "Zeitraum:        {$from} bis {$to}";
        $lines[] = 'Erstellt am:     ' . now()->format('d.m.Y H:i:s');
        $lines[] = 'Ursache:         PayPal-Kontoverknüpfung bei PAYONE nicht migriert (Vertragsübernahme GmbH)';
        $lines[] = 'Portal-ID:       2030693';
        $lines[] = 'Merchant-ID:     42504';
        $lines[] = 'Sub-Account-ID:  43065';
        $lines[] = '';
        $lines[] = '--------------------------------------------------------------------------------';
        $lines[] = '  ZUSAMMENFASSUNG';
        $lines[] = '--------------------------------------------------------------------------------';
        $lines[] = '';
        $lines[] = sprintf('  Fehlgeschlagene Transaktionen (Error 923):    %d', $rows->count());
        $lines[] = sprintf('  Betroffene Bestellungen (eindeutig):          %d', $uniqueOrders->count());
        $lines[] = sprintf('  Davon nachträglich bezahlt (andere Methode):  %d', $paidOrders->count());
        $lines[] = sprintf('  Nicht bezahlt (offen/verloren):               %d', $unpaidOrders->count());
        $lines[] = '';
        $lines[] = sprintf('  Summe nicht bezahlter Bestellungen:           %s EUR', number_format($unpaidOrders->sum('total_shipping'), 2, ',', '.'));
        $lines[] = sprintf('  Summe nachträglich bezahlter Bestellungen:    %s EUR', number_format($paidOrders->sum('total_shipping'), 2, ',', '.'));
        $lines[] = sprintf('  Summe ALLER betroffenen Bestellungen:         %s EUR', number_format($uniqueOrders->sum('total_shipping'), 2, ',', '.'));
        $lines[] = '';

        $lines[] = '--------------------------------------------------------------------------------';
        $lines[] = '  AUFSCHLÜSSELUNG NACH TAG';
        $lines[] = '--------------------------------------------------------------------------------';
        $lines[] = '';

        $byDate = $rows->groupBy(fn($r) => substr($r->error_date, 0, 10));
        foreach ($byDate as $date => $dayRows) {
            $dayOrders = $dayRows->unique('order_id');
            $dayUnpaid = $dayOrders->where('paid', 0);
            $lines[] = sprintf(
                '  %s:  %3d Fehler | %3d Bestellungen | %3d nicht bezahlt | %s EUR offen',
                $date,
                $dayRows->count(),
                $dayOrders->count(),
                $dayUnpaid->count(),
                number_format($dayUnpaid->sum('total_shipping'), 2, ',', '.')
            );
        }

        $lines[] = '';
        $lines[] = '--------------------------------------------------------------------------------';
        $lines[] = '  NICHT BEZAHLTE BESTELLUNGEN (DETAIL)';
        $lines[] = '--------------------------------------------------------------------------------';
        $lines[] = '';
        $lines[] = sprintf(
            '  %-12s %-20s %-18s %-14s %s',
            'Bestell-Nr',
            'Datum',
            'Referenz',
            'Betrag (EUR)',
            'Status'
        );
        $lines[] = '  ' . str_repeat('-', 80);

        foreach ($unpaidOrders->sortBy('order_date') as $order) {
            $lines[] = sprintf(
                '  %-12s %-20s %-18s %14s %s',
                $order->order_id,
                $order->order_date,
                $order->reference,
                number_format($order->total_shipping, 2, ',', '.'),
                $order->txaction
            );
        }

        $lines[] = '  ' . str_repeat('-', 80);
        $lines[] = sprintf(
            '  %-12s %-20s %-18s %14s',
            'GESAMT',
            '',
            $unpaidOrders->count() . ' Bestellungen',
            number_format($unpaidOrders->sum('total_shipping'), 2, ',', '.')
        );

        $lines[] = '';
        $lines[] = '--------------------------------------------------------------------------------';
        $lines[] = '  NACHTRÄGLICH BEZAHLTE BESTELLUNGEN (andere Zahlungsart)';
        $lines[] = '--------------------------------------------------------------------------------';
        $lines[] = '';

        if ($paidOrders->isEmpty()) {
            $lines[] = '  Keine.';
        } else {
            $lines[] = sprintf(
                '  %-12s %-20s %-18s %-14s %s',
                'Bestell-Nr',
                'Datum',
                'Referenz',
                'Betrag (EUR)',
                'Status'
            );
            $lines[] = '  ' . str_repeat('-', 80);

            foreach ($paidOrders->sortBy('order_date') as $order) {
                $lines[] = sprintf(
                    '  %-12s %-20s %-18s %14s %s',
                    $order->order_id,
                    $order->order_date,
                    $order->reference,
                    number_format($order->total_shipping, 2, ',', '.'),
                    $order->txaction
                );
            }

            $lines[] = '  ' . str_repeat('-', 80);
            $lines[] = sprintf(
                '  %-12s %-20s %-18s %14s',
                'GESAMT',
                '',
                $paidOrders->count() . ' Bestellungen',
                number_format($paidOrders->sum('total_shipping'), 2, ',', '.')
            );
        }

        $lines[] = '';
        $this->appendEmailSectionToTxt($lines, $unpaidOrders);
        $lines[] = '';
        $lines[] = '================================================================================';
        $lines[] = '  Ende des Berichts';
        $lines[] = '================================================================================';
        $lines[] = '';

        file_put_contents($path, implode("\n", $lines));
    }

    private function appendEmailSectionToTxt(array &$lines, \Illuminate\Support\Collection $unpaidOrders): void
    {
        $berater = $unpaidOrders->filter(fn($o) => ! empty($o->auth_user_id))->sortBy('billing_email');
        $shopKunden = $unpaidOrders->filter(fn($o) => empty($o->auth_user_id))->sortBy('billing_email');

        $lines[] = '--------------------------------------------------------------------------------';
        $lines[] = '  BETROFFENE BERATER (mit Auth-User-ID) - nicht bezahlt';
        $lines[] = '--------------------------------------------------------------------------------';
        $lines[] = '';
        $lines[] = sprintf('  %-12s %-8s %-30s %-30s %14s', 'Bestell-Nr', 'User-ID', 'Name', 'E-Mail', 'Betrag (EUR)');
        $lines[] = '  ' . str_repeat('-', 100);

        $beraterSum = 0;
        foreach ($berater as $order) {
            $name = trim(($order->billing_firstname ?? '') . ' ' . ($order->billing_lastname ?? ''));
            $lines[] = sprintf(
                '  %-12s %-8s %-30s %-30s %14s',
                $order->order_id,
                $order->auth_user_id,
                mb_substr($name, 0, 28),
                mb_substr($order->billing_email ?? '-', 0, 28),
                number_format($order->total_shipping, 2, ',', '.')
            );
            $beraterSum += $order->total_shipping;
        }

        $lines[] = '  ' . str_repeat('-', 100);
        $lines[] = sprintf('  %-12s %-8s %-30s %-30s %14s', 'GESAMT', '', $berater->count() . ' Bestellungen', '', number_format($beraterSum, 2, ',', '.'));

        $lines[] = '';
        $lines[] = '--------------------------------------------------------------------------------';
        $lines[] = '  BETROFFENE SHOP-KUNDEN (ohne Auth-User-ID) - nicht bezahlt';
        $lines[] = '--------------------------------------------------------------------------------';
        $lines[] = '';
        $lines[] = sprintf('  %-12s %-30s %-30s %14s', 'Bestell-Nr', 'Name', 'E-Mail', 'Betrag (EUR)');
        $lines[] = '  ' . str_repeat('-', 90);

        $shopSum = 0;
        foreach ($shopKunden as $order) {
            $name = trim(($order->billing_firstname ?? '') . ' ' . ($order->billing_lastname ?? ''));
            $lines[] = sprintf(
                '  %-12s %-30s %-30s %14s',
                $order->order_id,
                mb_substr($name, 0, 28),
                mb_substr($order->billing_email ?? '-', 0, 28),
                number_format($order->total_shipping, 2, ',', '.')
            );
            $shopSum += $order->total_shipping;
        }

        $lines[] = '  ' . str_repeat('-', 90);
        $lines[] = sprintf('  %-12s %-30s %-30s %14s', 'GESAMT', $shopKunden->count() . ' Bestellungen', '', number_format($shopSum, 2, ',', '.'));
    }

    private function writeEmailLists(string $dir, \Illuminate\Support\Collection $rows): void
    {
        $unpaidOrders = $rows->unique('order_id')->where('paid', 0);

        $berater = $unpaidOrders->filter(fn($o) => ! empty($o->auth_user_id))->sortBy('order_date');
        $shopKunden = $unpaidOrders->filter(fn($o) => empty($o->auth_user_id))->sortBy('order_date');

        $this->writeEmailCsv("{$dir}/emails-berater.csv", $berater, true);
        $this->writeEmailCsv("{$dir}/emails-shop-kunden.csv", $shopKunden, false);

        $this->newLine();
        $this->table(
            ['Kategorie', 'Bestellungen', 'Eindeutige E-Mails', 'Summe (EUR)'],
            [
                [
                    'Berater (mit Auth-User-ID)',
                    $berater->count(),
                    $berater->pluck('billing_email')->filter()->unique()->count(),
                    number_format($berater->sum('total_shipping'), 2, ',', '.') . ' EUR',
                ],
                [
                    'Shop-Kunden (ohne Auth-User-ID)',
                    $shopKunden->count(),
                    $shopKunden->pluck('billing_email')->filter()->unique()->count(),
                    number_format($shopKunden->sum('total_shipping'), 2, ',', '.') . ' EUR',
                ],
            ]
        );
    }

    private function writeEmailCsv(string $path, \Illuminate\Support\Collection $orders, bool $includeUserId): void
    {
        $fp = fopen($path, 'w');
        fprintf($fp, chr(0xEF) . chr(0xBB) . chr(0xBF));

        $headers = ['Bestell-Nr', 'Bestell-Datum', 'Vorname', 'Nachname', 'E-Mail', 'Betrag (EUR)', 'Status'];
        if ($includeUserId) {
            array_splice($headers, 1, 0, 'Auth-User-ID');
        }
        fputcsv($fp, $headers, ';');

        foreach ($orders as $order) {
            $row = [
                $order->order_id,
                $order->order_date,
                $order->billing_firstname ?? '',
                $order->billing_lastname ?? '',
                $order->billing_email ?? '',
                number_format($order->total_shipping, 2, ',', ''),
                $order->txaction,
            ];
            if ($includeUserId) {
                array_splice($row, 1, 0, $order->auth_user_id);
            }
            fputcsv($fp, $row, ';');
        }

        fputcsv($fp, [], ';');
        fputcsv($fp, ['GESAMT', '', '', '', $orders->count() . ' Bestellungen', number_format($orders->sum('total_shipping'), 2, ',', '') . ' EUR'], ';');
        fputcsv($fp, ['Eindeutige E-Mail-Adressen', $orders->pluck('billing_email')->filter()->unique()->count()], ';');

        fclose($fp);
    }
}