<?php

namespace App\Exports;

use App\Models\Setting;
use App\Models\CashIn;
use App\Models\CashOut;
use App\Models\Expense;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\WithCharts;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Chart\Chart;
use PhpOffice\PhpSpreadsheet\Chart\DataSeries;
use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;
use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
use PhpOffice\PhpSpreadsheet\Chart\Title;

class BankCashFlowExport implements WithMultipleSheets
{
    protected $dateFrom;
    protected $dateTo;

    public function __construct($dateFrom = null, $dateTo = null)
    {
        $this->dateFrom = $dateFrom;
        $this->dateTo = $dateTo;
    }

    /**
     * Return separate sheets for Complete Summary, Cash-In, Cash-Out, and Expenses
     */
    public function sheets(): array
    {
        $dateFrom = $this->dateFrom;
        $dateTo = $this->dateTo;

        // Fetch non-hidden paid inflows
        $cashIns = CashIn::where('is_hidden', '!=', 1)
            ->where('payment_status', 'paid')
            ->with(['client', 'customAccount', 'paymentType', 'paymentMethod', 'bankAccount'])
            ->when($dateFrom, fn($q) => $q->whereDate('date', '>=', $dateFrom))
            ->when($dateTo, fn($q) => $q->whereDate('date', '<=', $dateTo))
            ->orderBy('date')
            ->get();

        // Fetch non-hidden paid outflows
        $cashOuts = CashOut::where('is_hidden', '!=', 1)
            ->where('payment_status', 'paid')
            ->with(['client', 'customAccount', 'paymentType', 'paymentMethod', 'bankAccount'])
            ->when($dateFrom, fn($q) => $q->whereDate('date', '>=', $dateFrom))
            ->when($dateTo, fn($q) => $q->whereDate('date', '<=', $dateTo))
            ->orderBy('date')
            ->get();

        // Fetch all expenses (expenses do not have is_hidden)
        $expenses = Expense::with(['category', 'bankAccount'])
            ->when($dateFrom, fn($q) => $q->whereDate('date', '>=', $dateFrom))
            ->when($dateTo, fn($q) => $q->whereDate('date', '<=', $dateTo))
            ->orderBy('date')
            ->get();

        try {
            $currencySymbol = Setting::where('key', 'currency_symbol')->value('value') ?? '£';
            $businessName = Setting::where('key', 'business_name')->value('value') ?? 'RentTrack Pro';
        } catch (\Exception $e) {
            $currencySymbol = '£';
            $businessName = 'RentTrack Pro';
        }

        return [
            new CompleteSummarySheet($cashIns, $cashOuts, $expenses, $dateFrom, $dateTo, $currencySymbol, $businessName),
            new CashInSheet($cashIns, $dateFrom, $dateTo, $currencySymbol, $businessName),
            new CashOutSheet($cashOuts, $dateFrom, $dateTo, $currencySymbol, $businessName),
            new ExpenseSheet($expenses, $dateFrom, $dateTo, $currencySymbol, $businessName),
        ];
    }
}

/**
 * 1. COMPLETE SUMMARY SHEET
 */
class CompleteSummarySheet implements WithEvents, WithTitle, WithCharts
{
    protected $cashIns;
    protected $cashOuts;
    protected $expenses;
    protected $dateFrom;
    protected $dateTo;
    protected $currencySymbol;
    protected $businessName;

    public function __construct($cashIns, $cashOuts, $expenses, $dateFrom, $dateTo, $currencySymbol, $businessName)
    {
        $this->cashIns = $cashIns;
        $this->cashOuts = $cashOuts;
        $this->expenses = $expenses;
        $this->dateFrom = $dateFrom;
        $this->dateTo = $dateTo;
        $this->currencySymbol = $currencySymbol;
        $this->businessName = $businessName;
    }

    public function title(): string
    {
        return 'Complete Summary';
    }

    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function(AfterSheet $event) {
                $sheet = $event->sheet->getDelegate();
                $sheet->setShowGridlines(true);

                // Title Block
                $sheet->setCellValue('A2', strtoupper($this->businessName) . ' - CASH FLOW SUMMARY');
                $sheet->getStyle('A2')->getFont()->setBold(true)->setSize(16)->setName('Segoe UI');
                
                $periodText = 'Period: ' . ($this->dateFrom ? date('d M Y', strtotime($this->dateFrom)) : 'Beginning') . 
                              ' to ' . ($this->dateTo ? date('d M Y', strtotime($this->dateTo)) : 'Present');
                $sheet->setCellValue('A3', $periodText);
                $sheet->getStyle('A3')->getFont()->setItalic(true)->setSize(10)->setName('Segoe UI');

                // Custom UI Filter Bar Block
                $filterText = "🔍 SEARCH & FILTER:  |  Month: [Select 🔽 Col B]  |  Type: [Select 🔽 Col C]  |  Search Ref/Booking#: [Search 🔍 Col D]";
                $sheet->setCellValue('A4', $filterText);
                $sheet->mergeCells('A4:H4');
                $sheet->getRowDimension(4)->setRowHeight(26);
                $sheet->getStyle('A4:H4')->applyFromArray([
                    'font' => [
                        'bold' => true,
                        'name' => 'Segoe UI',
                        'size' => 9.5,
                        'color' => ['rgb' => '1E293B'],
                    ],
                    'fill' => [
                        'fillType' => Fill::FILL_SOLID,
                        'startColor' => ['rgb' => 'F1F5F9'],
                    ],
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                    ],
                    'borders' => [
                        'top' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'CBD5E1']],
                        'bottom' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'CBD5E1']],
                    ],
                ]);

                // Headers
                $headers = ['Date', 'Month', 'Type', 'Booking# / Reference', 'Description', 'Net Amount', 'VAT Amount', 'Total Amount'];
                foreach ($headers as $colIdx => $headerText) {
                    $colLetter = chr(65 + $colIdx);
                    $sheet->setCellValue($colLetter . '5', $headerText);
                }

                // Compile transactions list chronologically
                $transactions = collect();
                foreach ($this->cashIns as $in) {
                    $transactions->push([
                        'date' => $in->date,
                        'type' => 'Cash In',
                        'ref' => $in->booking_no ?: $in->transaction_id ?: 'N/A',
                        'desc' => ($in->customAccount ? 'Custom Account: ' . $in->customAccount->name : 'Client: ' . ($in->client ? $in->client->full_name : 'Unknown')) . ' - ' . ($in->paymentType ? $in->paymentType->name : 'Payment'),
                        'net' => $in->net_amount ?? $in->amount,
                        'vat' => $in->vat_amount ?? 0.00,
                        'total' => $in->amount,
                    ]);
                }
                foreach ($this->cashOuts as $out) {
                    $transactions->push([
                        'date' => $out->date,
                        'type' => $out->is_transfer ? 'Internal Transfer' : 'Cash Out',
                        'ref' => $out->booking_no ?: $out->transaction_id ?: 'N/A',
                        'desc' => $out->is_transfer 
                            ? 'Internal Transfer: ' . ($out->bankAccount?->name ?? 'Bank') . ' ➔ ' . ($out->transferToBankAccount?->name ?? 'Petty Cash')
                            : (($out->customAccount ? 'Custom Account: ' . $out->customAccount->name : 'Client: ' . ($out->client ? $out->client->full_name : 'Unknown')) . ' - ' . ($out->paymentType ? $out->paymentType->name : 'Refund')),
                        'net' => $out->net_amount ?? $out->amount,
                        'vat' => $out->vat_amount ?? 0.00,
                        'total' => $out->amount,
                    ]);
                }
                foreach ($this->expenses as $exp) {
                    $transactions->push([
                        'date' => $exp->date,
                        'type' => 'Expense',
                        'ref' => $exp->transaction_id ?: 'N/A',
                        'desc' => ($exp->category ? $exp->category->name : 'General Outgoing') . ' - Payee: ' . ($exp->payee_bank_name ?: 'N/A'),
                        'net' => $exp->net_amount ?? $exp->amount,
                        'vat' => $exp->vat_amount ?? 0.00,
                        'total' => $exp->amount,
                    ]);
                }
                $transactions = $transactions->sortBy('date');

                // Write transactions
                $currentRow = 6;
                foreach ($transactions as $tx) {
                    $sheet->setCellValue('A' . $currentRow, $tx['date']->format('d-M-Y'));
                    $sheet->setCellValue('B' . $currentRow, $tx['date']->format('F Y'));
                    $sheet->setCellValue('C' . $currentRow, $tx['type']);
                    $sheet->setCellValue('D' . $currentRow, $tx['ref']);
                    $sheet->setCellValue('E' . $currentRow, $tx['desc']);
                    $sheet->setCellValue('F' . $currentRow, $tx['net']);
                    $sheet->setCellValue('G' . $currentRow, $tx['vat']);
                    $sheet->setCellValue('H' . $currentRow, $tx['total']);

                    // Colors based on Transaction Type
                    if ($tx['type'] === 'Cash In') {
                        $bgColor = 'E2EFDA'; // Pastel green
                        $textColor = '375623';
                    } elseif ($tx['type'] === 'Cash Out') {
                        $bgColor = 'FCE4D6'; // Pastel orange/red
                        $textColor = 'C65911';
                    } elseif ($tx['type'] === 'Internal Transfer') {
                        $bgColor = 'F2F2F2'; // Pastel gray
                        $textColor = '595959';
                    } else {
                        $bgColor = 'DDEBF7'; // Pastel blue
                        $textColor = '1F4E78';
                    }

                    $sheet->getStyle("A{$currentRow}:H{$currentRow}")->applyFromArray([
                        'fill' => [
                            'fillType' => Fill::FILL_SOLID,
                            'startColor' => ['rgb' => $bgColor],
                        ],
                        'font' => [
                            'color' => ['rgb' => $textColor],
                            'name' => 'Segoe UI',
                        ],
                        'borders' => [
                            'allBorders' => [
                                'borderStyle' => Border::BORDER_THIN,
                                'color' => ['rgb' => 'E5E7EB'],
                            ],
                        ],
                    ]);

                    $currentRow++;
                }

                $lastDataRow = $currentRow - 1;
                if ($lastDataRow < 6) {
                    $sheet->setCellValue('A6', 'No records found.');
                    $sheet->mergeCells('A6:H6');
                    $lastDataRow = 6;
                    $currentRow = 7;
                }

                // Table Totals
                $sheet->setCellValue('A' . $currentRow, 'TOTAL');
                $sheet->setCellValue('F' . $currentRow, "=SUM(F6:F{$lastDataRow})");
                $sheet->setCellValue('G' . $currentRow, "=SUM(G6:G{$lastDataRow})");
                $sheet->setCellValue('H' . $currentRow, "=SUM(H6:H{$lastDataRow})");

                $sheet->getStyle("A{$currentRow}:H{$currentRow}")->applyFromArray([
                    'font' => [
                        'bold' => true,
                        'name' => 'Segoe UI',
                    ],
                    'borders' => [
                        'top' => ['borderStyle' => Border::BORDER_THIN],
                        'bottom' => ['borderStyle' => Border::BORDER_DOUBLE],
                    ],
                ]);

                // Side Summary Table
                $sheet->setCellValue('J5', 'SUMMARY METRIC');
                $sheet->setCellValue('K5', 'TOTAL');
                $sheet->setCellValue('J6', 'Total Cash-In');
                $sheet->setCellValue('K6', "=SUMIF(C6:C{$lastDataRow}, \"Cash In\", H6:H{$lastDataRow})");
                $sheet->setCellValue('J7', 'Total Cash-Out');
                $sheet->setCellValue('K7', "=SUMIF(C6:C{$lastDataRow}, \"Cash Out\", H6:H{$lastDataRow})");
                $sheet->setCellValue('J8', 'Total Expense');
                $sheet->setCellValue('K8', "=SUMIF(C6:C{$lastDataRow}, \"Expense\", H6:H{$lastDataRow})");
                $sheet->setCellValue('J9', 'Net Cash Flow');
                $sheet->setCellValue('K9', "=K6-K7-K8");

                // Side Summary Table Styling
                $sheet->getStyle('J5:K5')->applyFromArray([
                    'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF'], 'name' => 'Segoe UI'],
                    'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '1F2937']],
                    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
                ]);
                $sheet->getStyle("J6:K9")->applyFromArray([
                    'font' => ['name' => 'Segoe UI'],
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => Border::BORDER_THIN,
                            'color' => ['rgb' => 'E5E7EB'],
                        ],
                    ],
                ]);
                $sheet->getStyle('J9:K9')->getFont()->setBold(true);
                $sheet->getStyle('K9')->getBorders()->getBottom()->setBorderStyle(Border::BORDER_DOUBLE);

                // HMRC Compliance Note Info Box
                $noteText = "HMRC COMPLIANCE NOTE:\n" .
                            "Internal Transfers (Contra Entries) represent movement of funds between business accounts (e.g. ATM withdrawals to Petty Cash).\n\n" .
                            "Under HMRC rules, these transactions are net-neutral (neither income nor expense) and are excluded from Cash-Out and Net Cash Flow summaries to prevent double-counting of outflows.";
                $sheet->setCellValue('J11', $noteText);
                $sheet->mergeCells('J11:K19');
                $sheet->getStyle('J11:K19')->getAlignment()->setWrapText(true);
                $sheet->getStyle('J11:K19')->getAlignment()->setVertical(Alignment::VERTICAL_TOP);
                $sheet->getStyle('J11:K19')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
                $sheet->getStyle('J11:K19')->applyFromArray([
                    'fill' => [
                        'fillType' => Fill::FILL_SOLID,
                        'startColor' => ['rgb' => 'FFF2CC'],
                    ],
                    'font' => [
                        'name' => 'Segoe UI',
                        'size' => 9,
                        'color' => ['rgb' => '7F6000'],
                    ],
                    'borders' => [
                        'outline' => [
                            'borderStyle' => Border::BORDER_THIN,
                            'color' => ['rgb' => 'D6B656'],
                        ],
                    ],
                ]);

                // Styling Column Headers
                $sheet->getStyle('A5:H5')->applyFromArray([
                    'font' => [
                        'bold' => true,
                        'color' => ['rgb' => 'FFFFFF'],
                        'size' => 11,
                        'name' => 'Segoe UI',
                    ],
                    'fill' => [
                        'fillType' => Fill::FILL_SOLID,
                        'startColor' => ['rgb' => '1F2937'], // Premium Dark Black
                    ],
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                    ],
                ]);

                // Enable AutoFilter
                $sheet->setAutoFilter('A5:H' . $lastDataRow);

                // Cell Alignments
                $sheet->getStyle('A5:D' . ($currentRow))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle('E5:E' . ($currentRow))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
                $sheet->getStyle('F5:H' . ($currentRow))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
                $sheet->getStyle('J6:J9')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
                $sheet->getStyle('K6:K9')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

                // Row Heights
                $sheet->getRowDimension(5)->setRowHeight(28);
                for ($rowIdx = 6; $rowIdx <= $currentRow; $rowIdx++) {
                    $sheet->getRowDimension($rowIdx)->setRowHeight(22);
                }

                // Currency Formats
                $currencyFormat = '"' . $this->currencySymbol . '"#,##0.00';
                $sheet->getStyle('F6:H' . $currentRow)->getNumberFormat()->setFormatCode($currencyFormat);
                $sheet->getStyle('K6:K9')->getNumberFormat()->setFormatCode($currencyFormat);

                // Auto-width columns A-H
                foreach (range('A', 'H') as $col) {
                    $sheet->getColumnDimension($col)->setAutoSize(true);
                }
                
                // Explicit sizes for Side summary columns
                $sheet->getColumnDimension('I')->setWidth(4);
                $sheet->getColumnDimension('J')->setWidth(20);
                $sheet->getColumnDimension('K')->setWidth(18);

                // Freeze Pane at row 6
                $sheet->freezePane('A6');
            }
        ];
    }

    public function charts()
    {
        // X-axis: J6:J8
        $categories = [
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, '\'Complete Summary\'!$J$6:$J$8', null, 3),
        ];

        // Y-axis: K6:K8
        $values = [
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, '\'Complete Summary\'!$K$6:$K$8', null, 3),
        ];

        $series = new DataSeries(
            DataSeries::TYPE_BARCHART,
            DataSeries::GROUPING_CLUSTERED,
            [0],
            [],
            $categories,
            $values
        );

        $plotArea = new PlotArea(null, [$series]);
        $title = new Title('Cash Flow Comparison');
        $chart = new Chart('flow_chart', $title, null, $plotArea);

        $chart->setTopLeftPosition('M5');
        $chart->setBottomRightPosition('U18');

        return $chart;
    }
}

/**
 * 2. CASH IN SHEET
 */
class CashInSheet implements WithEvents, WithTitle
{
    protected $cashIns;
    protected $dateFrom;
    protected $dateTo;
    protected $currencySymbol;
    protected $businessName;

    public function __construct($cashIns, $dateFrom, $dateTo, $currencySymbol, $businessName)
    {
        $this->cashIns = $cashIns;
        $this->dateFrom = $dateFrom;
        $this->dateTo = $dateTo;
        $this->currencySymbol = $currencySymbol;
        $this->businessName = $businessName;
    }

    public function title(): string
    {
        return 'Cash-In';
    }

    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function(AfterSheet $event) {
                $sheet = $event->sheet->getDelegate();
                $sheet->setShowGridlines(true);

                // Title Block
                $sheet->setCellValue('A2', strtoupper($this->businessName) . ' - CASH-IN TRANSACTIONS');
                $sheet->getStyle('A2')->getFont()->setBold(true)->setSize(16)->setName('Segoe UI');
                
                $periodText = 'Period: ' . ($this->dateFrom ? date('d M Y', strtotime($this->dateFrom)) : 'Beginning') . 
                              ' to ' . ($this->dateTo ? date('d M Y', strtotime($this->dateTo)) : 'Present');
                $sheet->setCellValue('A3', $periodText);
                $sheet->getStyle('A3')->getFont()->setItalic(true)->setSize(10)->setName('Segoe UI');

                // Custom UI Filter Bar Block
                $filterText = "🔍 SEARCH & FILTER:  |  Month: [Select 🔽 Col B]  |  Client / Custom Account: [Select 🔽 Col E]  |  Ref/Booking#: [Search 🔍 Col D]";
                $sheet->setCellValue('A4', $filterText);
                $sheet->mergeCells('A4:G4');
                $sheet->getRowDimension(4)->setRowHeight(26);
                $sheet->getStyle('A4:G4')->applyFromArray([
                    'font' => [
                        'bold' => true,
                        'name' => 'Segoe UI',
                        'size' => 9.5,
                        'color' => ['rgb' => '1E293B'],
                    ],
                    'fill' => [
                        'fillType' => Fill::FILL_SOLID,
                        'startColor' => ['rgb' => 'F1F5F9'],
                    ],
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                    ],
                    'borders' => [
                        'top' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'CBD5E1']],
                        'bottom' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'CBD5E1']],
                    ],
                ]);

                // Headers
                $headers = ['Date', 'Month', 'Transaction ID', 'Booking# / Reference', 'Client Name / Custom Account', 'Method', 'Amount'];
                foreach ($headers as $colIdx => $headerText) {
                    $colLetter = chr(65 + $colIdx);
                    $sheet->setCellValue($colLetter . '5', $headerText);
                }

                // Write rows
                $currentRow = 6;
                foreach ($this->cashIns as $in) {
                    $sheet->setCellValue('A' . $currentRow, $in->date->format('d-M-Y'));
                    $sheet->setCellValue('B' . $currentRow, $in->date->format('F Y'));
                    $sheet->setCellValue('C' . $currentRow, $in->transaction_id ?? 'N/A');
                    $sheet->setCellValue('D' . $currentRow, $in->booking_no ?? 'N/A');
                    $sheet->setCellValue('E' . $currentRow, $in->customAccount ? $in->customAccount->name : ($in->client ? $in->client->full_name : 'Unknown'));
                    $sheet->setCellValue('F' . $currentRow, $in->paymentMethod ? $in->paymentMethod->name : 'N/A');
                    $sheet->setCellValue('G' . $currentRow, $in->amount);

                    // Alternating background styling (very light green vs white)
                    $bgColor = ($currentRow % 2 == 0) ? 'F4FAF4' : 'FFFFFF';

                    $sheet->getStyle("A{$currentRow}:G{$currentRow}")->applyFromArray([
                        'fill' => [
                            'fillType' => Fill::FILL_SOLID,
                            'startColor' => ['rgb' => $bgColor],
                        ],
                        'font' => [
                            'name' => 'Segoe UI',
                            'color' => ['rgb' => '111827'],
                        ],
                        'borders' => [
                            'allBorders' => [
                                'borderStyle' => Border::BORDER_THIN,
                                'color' => ['rgb' => 'E5E7EB'],
                            ],
                        ],
                    ]);

                    $currentRow++;
                }

                $lastDataRow = $currentRow - 1;
                if ($lastDataRow < 6) {
                    $sheet->setCellValue('A6', 'No records found.');
                    $sheet->mergeCells('A6:G6');
                    $lastDataRow = 6;
                    $currentRow = 7;
                }

                // Table Totals
                $sheet->setCellValue('A' . $currentRow, 'TOTAL');
                $sheet->setCellValue('G' . $currentRow, "=SUM(G6:G{$lastDataRow})");

                $sheet->getStyle("A{$currentRow}:G{$currentRow}")->applyFromArray([
                    'font' => ['bold' => true, 'name' => 'Segoe UI'],
                    'borders' => [
                        'top' => ['borderStyle' => Border::BORDER_THIN],
                        'bottom' => ['borderStyle' => Border::BORDER_DOUBLE],
                    ],
                ]);

                // Side KPI Card
                $sheet->mergeCells('I5:J5');
                $sheet->setCellValue('I5', 'TOTAL CASH-IN');
                $sheet->getStyle('I5:J5')->applyFromArray([
                    'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF'], 'name' => 'Segoe UI'],
                    'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '1F2937']],
                    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
                ]);

                $sheet->mergeCells('I6:J7');
                $sheet->setCellValue('I6', "=SUM(G6:G{$lastDataRow})");
                $sheet->getStyle('I6:J7')->applyFromArray([
                    'font' => ['bold' => true, 'size' => 16, 'color' => ['rgb' => '375623'], 'name' => 'Segoe UI'],
                    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER],
                    'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'F2F8F2']],
                    'borders' => [
                        'outline' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'D1D5DB']],
                    ],
                ]);

                // Styling Column Headers
                $sheet->getStyle('A5:G5')->applyFromArray([
                    'font' => [
                        'bold' => true,
                        'color' => ['rgb' => 'FFFFFF'],
                        'size' => 11,
                        'name' => 'Segoe UI',
                    ],
                    'fill' => [
                        'fillType' => Fill::FILL_SOLID,
                        'startColor' => ['rgb' => '1F2937'],
                    ],
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                    ],
                ]);

                // Enable AutoFilter
                $sheet->setAutoFilter('A5:G' . $lastDataRow);

                // Alignments
                $sheet->getStyle('A5:D' . ($currentRow))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle('E5:F' . ($currentRow))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
                $sheet->getStyle('G5:G' . ($currentRow))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
                $sheet->getStyle('I6')->getNumberFormat()->setFormatCode('"' . $this->currencySymbol . '"#,##0.00');

                // Row Heights
                $sheet->getRowDimension(5)->setRowHeight(28);
                for ($rowIdx = 6; $rowIdx <= $currentRow; $rowIdx++) {
                    $sheet->getRowDimension($rowIdx)->setRowHeight(22);
                }

                // Currency Formats
                $currencyFormat = '"' . $this->currencySymbol . '"#,##0.00';
                $sheet->getStyle('G6:G' . $currentRow)->getNumberFormat()->setFormatCode($currencyFormat);

                // Auto-width columns A-G
                foreach (range('A', 'G') as $col) {
                    $sheet->getColumnDimension($col)->setAutoSize(true);
                }
                
                // Explicit sizes for Side summary columns
                $sheet->getColumnDimension('H')->setWidth(4);
                $sheet->getColumnDimension('I')->setWidth(15);
                $sheet->getColumnDimension('J')->setWidth(15);

                $sheet->freezePane('A6');
            }
        ];
    }
}

/**
 * 3. CASH OUT SHEET
 */
class CashOutSheet implements WithEvents, WithTitle
{
    protected $cashOuts;
    protected $dateFrom;
    protected $dateTo;
    protected $currencySymbol;
    protected $businessName;

    public function __construct($cashOuts, $dateFrom, $dateTo, $currencySymbol, $businessName)
    {
        $this->cashOuts = $cashOuts;
        $this->dateFrom = $dateFrom;
        $this->dateTo = $dateTo;
        $this->currencySymbol = $currencySymbol;
        $this->businessName = $businessName;
    }

    public function title(): string
    {
        return 'Cash-Out';
    }

    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function(AfterSheet $event) {
                $sheet = $event->sheet->getDelegate();
                $sheet->setShowGridlines(true);

                // Title Block
                $sheet->setCellValue('A2', strtoupper($this->businessName) . ' - CASH-OUT REFUNDS');
                $sheet->getStyle('A2')->getFont()->setBold(true)->setSize(16)->setName('Segoe UI');
                
                $periodText = 'Period: ' . ($this->dateFrom ? date('d M Y', strtotime($this->dateFrom)) : 'Beginning') . 
                              ' to ' . ($this->dateTo ? date('d M Y', strtotime($this->dateTo)) : 'Present');
                $sheet->setCellValue('A3', $periodText);
                $sheet->getStyle('A3')->getFont()->setItalic(true)->setSize(10)->setName('Segoe UI');

                // Custom UI Filter Bar Block
                $filterText = "🔍 SEARCH & FILTER:  |  Month: [Select 🔽 Col B]  |  Client / Custom Account: [Select 🔽 Col E]  |  Method: [Select 🔽 Col F]  |  Ref/Booking#: [Search 🔍 Col D]";
                $sheet->setCellValue('A4', $filterText);
                $sheet->mergeCells('A4:G4');
                $sheet->getRowDimension(4)->setRowHeight(26);
                $sheet->getStyle('A4:G4')->applyFromArray([
                    'font' => [
                        'bold' => true,
                        'name' => 'Segoe UI',
                        'size' => 9.5,
                        'color' => ['rgb' => '1E293B'],
                    ],
                    'fill' => [
                        'fillType' => Fill::FILL_SOLID,
                        'startColor' => ['rgb' => 'F1F5F9'],
                    ],
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                    ],
                    'borders' => [
                        'top' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'CBD5E1']],
                        'bottom' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'CBD5E1']],
                    ],
                ]);

                // Headers
                $headers = ['Date', 'Month', 'Transaction ID', 'Booking# / Reference', 'Client Name / Custom Account', 'Method', 'Amount'];
                foreach ($headers as $colIdx => $headerText) {
                    $colLetter = chr(65 + $colIdx);
                    $sheet->setCellValue($colLetter . '5', $headerText);
                }

                // Write rows
                $currentRow = 6;
                foreach ($this->cashOuts as $out) {
                    $sheet->setCellValue('A' . $currentRow, $out->date->format('d-M-Y'));
                    $sheet->setCellValue('B' . $currentRow, $out->date->format('F Y'));
                    $sheet->setCellValue('C' . $currentRow, $out->transaction_id ?? 'N/A');
                    $sheet->setCellValue('D' . $currentRow, $out->booking_no ?? 'N/A');
                    $sheet->setCellValue('E' . $currentRow, $out->is_transfer 
                        ? 'Internal Transfer: ' . ($out->bankAccount?->name ?? 'Bank') . ' ➔ ' . ($out->transferToBankAccount?->name ?? 'Petty Cash')
                        : ($out->customAccount ? $out->customAccount->name : ($out->client ? $out->client->full_name : 'Unknown')));
                    $sheet->setCellValue('F' . $currentRow, $out->is_transfer ? 'Transfer' : ($out->paymentMethod ? $out->paymentMethod->name : 'N/A'));
                    $sheet->setCellValue('G' . $currentRow, $out->amount);

                    // Alternating background styling (very light red vs white)
                    $bgColor = ($currentRow % 2 == 0) ? 'FAF4F4' : 'FFFFFF';

                    $sheet->getStyle("A{$currentRow}:G{$currentRow}")->applyFromArray([
                        'fill' => [
                            'fillType' => Fill::FILL_SOLID,
                            'startColor' => ['rgb' => $bgColor],
                        ],
                        'font' => [
                            'name' => 'Segoe UI',
                            'color' => ['rgb' => '111827'],
                        ],
                        'borders' => [
                            'allBorders' => [
                                'borderStyle' => Border::BORDER_THIN,
                                'color' => ['rgb' => 'E5E7EB'],
                            ],
                        ],
                    ]);

                    $currentRow++;
                }

                $lastDataRow = $currentRow - 1;
                if ($lastDataRow < 6) {
                    $sheet->setCellValue('A6', 'No records found.');
                    $sheet->mergeCells('A6:G6');
                    $lastDataRow = 6;
                    $currentRow = 7;
                }

                // Table Totals
                $sheet->setCellValue('A' . $currentRow, 'TOTAL');
                $sheet->setCellValue('G' . $currentRow, "=SUMIF(F6:F{$lastDataRow}, \"<>Transfer\", G6:G{$lastDataRow})");

                $sheet->getStyle("A{$currentRow}:G{$currentRow}")->applyFromArray([
                    'font' => ['bold' => true, 'name' => 'Segoe UI'],
                    'borders' => [
                        'top' => ['borderStyle' => Border::BORDER_THIN],
                        'bottom' => ['borderStyle' => Border::BORDER_DOUBLE],
                    ],
                ]);

                // Side KPI Card
                $sheet->mergeCells('I5:J5');
                $sheet->setCellValue('I5', 'TOTAL CASH-OUT');
                $sheet->getStyle('I5:J5')->applyFromArray([
                    'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF'], 'name' => 'Segoe UI'],
                    'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '1F2937']],
                    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
                ]);

                $sheet->mergeCells('I6:J7');
                $sheet->setCellValue('I6', "=SUMIF(F6:F{$lastDataRow}, \"<>Transfer\", G6:G{$lastDataRow})");
                $sheet->getStyle('I6:J7')->applyFromArray([
                    'font' => ['bold' => true, 'size' => 16, 'color' => ['rgb' => 'C65911'], 'name' => 'Segoe UI'],
                    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER],
                    'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'FDF2E9']],
                    'borders' => [
                        'outline' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'D1D5DB']],
                    ],
                ]);

                // HMRC Compliance Note Info Box
                $noteText = "HMRC COMPLIANCE NOTE:\n" .
                            "Internal Transfers (with Method: 'Transfer') represent funds moved between business bank accounts or petty cash.\n\n" .
                            "To ensure compliance with HMRC reporting guidelines, these entries are excluded from the Total Cash-Out sum above so that actual business outgoings/expenses are not overstated.";
                $sheet->setCellValue('I9', $noteText);
                $sheet->mergeCells('I9:J17');
                $sheet->getStyle('I9:J17')->getAlignment()->setWrapText(true);
                $sheet->getStyle('I9:J17')->getAlignment()->setVertical(Alignment::VERTICAL_TOP);
                $sheet->getStyle('I9:J17')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
                $sheet->getStyle('I9:J17')->applyFromArray([
                    'fill' => [
                        'fillType' => Fill::FILL_SOLID,
                        'startColor' => ['rgb' => 'FFF2CC'],
                    ],
                    'font' => [
                        'name' => 'Segoe UI',
                        'size' => 9,
                        'color' => ['rgb' => '7F6000'],
                    ],
                    'borders' => [
                        'outline' => [
                            'borderStyle' => Border::BORDER_THIN,
                            'color' => ['rgb' => 'D6B656'],
                        ],
                    ],
                ]);

                // Styling Column Headers
                $sheet->getStyle('A5:G5')->applyFromArray([
                    'font' => [
                        'bold' => true,
                        'color' => ['rgb' => 'FFFFFF'],
                        'size' => 11,
                        'name' => 'Segoe UI',
                    ],
                    'fill' => [
                        'fillType' => Fill::FILL_SOLID,
                        'startColor' => ['rgb' => '1F2937'],
                    ],
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                    ],
                ]);

                // Enable AutoFilter
                $sheet->setAutoFilter('A5:G' . $lastDataRow);

                // Alignments
                $sheet->getStyle('A5:D' . ($currentRow))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle('E5:F' . ($currentRow))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
                $sheet->getStyle('G5:G' . ($currentRow))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
                $sheet->getStyle('I6')->getNumberFormat()->setFormatCode('"' . $this->currencySymbol . '"#,##0.00');

                // Row Heights
                $sheet->getRowDimension(5)->setRowHeight(28);
                for ($rowIdx = 6; $rowIdx <= $currentRow; $rowIdx++) {
                    $sheet->getRowDimension($rowIdx)->setRowHeight(22);
                }

                // Currency Formats
                $currencyFormat = '"' . $this->currencySymbol . '"#,##0.00';
                $sheet->getStyle('G6:G' . $currentRow)->getNumberFormat()->setFormatCode($currencyFormat);

                // Auto-width columns A-G
                foreach (range('A', 'G') as $col) {
                    $sheet->getColumnDimension($col)->setAutoSize(true);
                }
                
                // Explicit sizes for Side summary columns
                $sheet->getColumnDimension('H')->setWidth(4);
                $sheet->getColumnDimension('I')->setWidth(15);
                $sheet->getColumnDimension('J')->setWidth(15);

                $sheet->freezePane('A6');
            }
        ];
    }
}

/**
 * 4. EXPENSE SHEET
 */
class ExpenseSheet implements WithEvents, WithTitle
{
    protected $expenses;
    protected $dateFrom;
    protected $dateTo;
    protected $currencySymbol;
    protected $businessName;

    public function __construct($expenses, $dateFrom, $dateTo, $currencySymbol, $businessName)
    {
        $this->expenses = $expenses;
        $this->dateFrom = $dateFrom;
        $this->dateTo = $dateTo;
        $this->currencySymbol = $currencySymbol;
        $this->businessName = $businessName;
    }

    public function title(): string
    {
        return 'Expense';
    }

    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function(AfterSheet $event) {
                $sheet = $event->sheet->getDelegate();
                $sheet->setShowGridlines(true);

                // Title Block
                $sheet->setCellValue('A2', strtoupper($this->businessName) . ' - OPERATIONAL EXPENSES');
                $sheet->getStyle('A2')->getFont()->setBold(true)->setSize(16)->setName('Segoe UI');
                
                $periodText = 'Period: ' . ($this->dateFrom ? date('d M Y', strtotime($this->dateFrom)) : 'Beginning') . 
                              ' to ' . ($this->dateTo ? date('d M Y', strtotime($this->dateTo)) : 'Present');
                $sheet->setCellValue('A3', $periodText);
                $sheet->getStyle('A3')->getFont()->setItalic(true)->setSize(10)->setName('Segoe UI');

                // Custom UI Filter Bar Block
                $filterText = "🔍 SEARCH & FILTER:  |  Month: [Select 🔽 Col B]  |  Parent Category: [Select 🔽 Col C]  |  Sub Category: [Select 🔽 Col D]  |  Ref/Notes Search: [Search 🔍 Col F]";
                $sheet->setCellValue('A4', $filterText);
                $sheet->mergeCells('A4:J4');
                $sheet->getRowDimension(4)->setRowHeight(26);
                $sheet->getStyle('A4:J4')->applyFromArray([
                    'font' => [
                        'bold' => true,
                        'name' => 'Segoe UI',
                        'size' => 9.5,
                        'color' => ['rgb' => '1E293B'],
                    ],
                    'fill' => [
                        'fillType' => Fill::FILL_SOLID,
                        'startColor' => ['rgb' => 'F1F5F9'],
                    ],
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                    ],
                    'borders' => [
                        'top' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'CBD5E1']],
                        'bottom' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'CBD5E1']],
                    ],
                ]);

                // Headers (Parent Category and Sub Category Split)
                $headers = ['Date', 'Month', 'Parent Category', 'Sub Category', 'Payee / Bank Details', 'Notes / Ref', 'Net Amount', 'VAT Rate (%)', 'VAT Amount', 'Total Amount'];
                foreach ($headers as $colIdx => $headerText) {
                    $colLetter = chr(65 + $colIdx);
                    $sheet->setCellValue($colLetter . '5', $headerText);
                }

                // Write rows
                $currentRow = 6;
                foreach ($this->expenses as $exp) {
                    $sheet->setCellValue('A' . $currentRow, $exp->date->format('d-M-Y'));
                    $sheet->setCellValue('B' . $currentRow, $exp->date->format('F Y'));
                    
                    // Split combined category into parent and sub category
                    $parentName = 'General';
                    $subName = 'General';
                    if ($exp->category) {
                        if ($exp->category->parent) {
                            $parentName = $exp->category->parent->name;
                            $subName = $exp->category->name;
                        } else {
                            $parentName = $exp->category->name;
                            $subName = 'General';
                        }
                    } else {
                        $parentName = 'Uncategorized';
                        $subName = 'N/A';
                    }

                    $sheet->setCellValue('C' . $currentRow, $parentName);
                    $sheet->setCellValue('D' . $currentRow, $subName);
                    $sheet->setCellValue('E' . $currentRow, ($exp->payee_bank_name ?: 'N/A') . ($exp->payee_account_no ? ' (A/C: ' . $exp->payee_account_no . ')' : ''));
                    $sheet->setCellValue('F' . $currentRow, $exp->notes ?: $exp->transaction_id ?: 'N/A');
                    $sheet->setCellValue('G' . $currentRow, $exp->net_amount ?? $exp->amount);
                    $sheet->setCellValue('H' . $currentRow, $exp->vat_rate ? ($exp->vat_rate . '%') : '0%');
                    $sheet->setCellValue('I' . $currentRow, $exp->vat_amount ?? 0.00);
                    $sheet->setCellValue('J' . $currentRow, $exp->amount);

                    // Alternating background styling (very light blue vs white)
                    $bgColor = ($currentRow % 2 == 0) ? 'F4F8FA' : 'FFFFFF';

                    $sheet->getStyle("A{$currentRow}:J{$currentRow}")->applyFromArray([
                        'fill' => [
                            'fillType' => Fill::FILL_SOLID,
                            'startColor' => ['rgb' => $bgColor],
                        ],
                        'font' => [
                            'name' => 'Segoe UI',
                            'color' => ['rgb' => '111827'],
                        ],
                        'borders' => [
                            'allBorders' => [
                                'borderStyle' => Border::BORDER_THIN,
                                'color' => ['rgb' => 'E5E7EB'],
                            ],
                        ],
                    ]);

                    $currentRow++;
                }

                $lastDataRow = $currentRow - 1;
                if ($lastDataRow < 6) {
                    $sheet->setCellValue('A6', 'No records found.');
                    $sheet->mergeCells('A6:J6');
                    $lastDataRow = 6;
                    $currentRow = 7;
                }

                // Table Totals
                $sheet->setCellValue('A' . $currentRow, 'TOTAL');
                $sheet->setCellValue('G' . $currentRow, "=SUM(G6:G{$lastDataRow})");
                $sheet->setCellValue('I' . $currentRow, "=SUM(I6:I{$lastDataRow})");
                $sheet->setCellValue('J' . $currentRow, "=SUM(J6:J{$lastDataRow})");

                $sheet->getStyle("A{$currentRow}:J{$currentRow}")->applyFromArray([
                    'font' => ['bold' => true, 'name' => 'Segoe UI'],
                    'borders' => [
                        'top' => ['borderStyle' => Border::BORDER_THIN],
                        'bottom' => ['borderStyle' => Border::BORDER_DOUBLE],
                    ],
                ]);

                // Side KPI Cards (TOTAL EXPENSE & TOTAL VAT)
                // Card 1: TOTAL EXPENSE
                $sheet->mergeCells('L5:M5');
                $sheet->setCellValue('L5', 'TOTAL EXPENSE');
                $sheet->getStyle('L5:M5')->applyFromArray([
                    'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF'], 'name' => 'Segoe UI'],
                    'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '1F2937']],
                    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
                ]);

                $sheet->mergeCells('L6:M7');
                $sheet->setCellValue('L6', "=SUM(J6:J{$lastDataRow})");
                $sheet->getStyle('L6:M7')->applyFromArray([
                    'font' => ['bold' => true, 'size' => 16, 'color' => ['rgb' => '1F4E78'], 'name' => 'Segoe UI'],
                    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER],
                    'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'EBF5FB']],
                    'borders' => [
                        'outline' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'D1D5DB']],
                    ],
                ]);

                // Card 2: TOTAL VAT
                $sheet->mergeCells('L9:M9');
                $sheet->setCellValue('L9', 'TOTAL VAT');
                $sheet->getStyle('L9:M9')->applyFromArray([
                    'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF'], 'name' => 'Segoe UI'],
                    'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '1F2937']],
                    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
                ]);

                $sheet->mergeCells('L10:M11');
                $sheet->setCellValue('L10', "=SUM(I6:I{$lastDataRow})");
                $sheet->getStyle('L10:M11')->applyFromArray([
                    'font' => ['bold' => true, 'size' => 16, 'color' => ['rgb' => '1F4E78'], 'name' => 'Segoe UI'],
                    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER],
                    'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'EBF5FB']],
                    'borders' => [
                        'outline' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'D1D5DB']],
                    ],
                ]);

                // Styling Column Headers
                $sheet->getStyle('A5:J5')->applyFromArray([
                    'font' => [
                        'bold' => true,
                        'color' => ['rgb' => 'FFFFFF'],
                        'size' => 11,
                        'name' => 'Segoe UI',
                    ],
                    'fill' => [
                        'fillType' => Fill::FILL_SOLID,
                        'startColor' => ['rgb' => '1F2937'],
                    ],
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                    ],
                ]);

                // Enable AutoFilter
                $sheet->setAutoFilter('A5:J' . $lastDataRow);

                // Alignments
                $sheet->getStyle('A5:B' . ($currentRow))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle('C5:F' . ($currentRow))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
                $sheet->getStyle('G5:G' . ($currentRow))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
                $sheet->getStyle('H5:H' . ($currentRow))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle('I5:J' . ($currentRow))->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
                
                $sheet->getStyle('L6')->getNumberFormat()->setFormatCode('"' . $this->currencySymbol . '"#,##0.00');
                $sheet->getStyle('L10')->getNumberFormat()->setFormatCode('"' . $this->currencySymbol . '"#,##0.00');

                // Row Heights
                $sheet->getRowDimension(5)->setRowHeight(28);
                for ($rowIdx = 6; $rowIdx <= $currentRow; $rowIdx++) {
                    $sheet->getRowDimension($rowIdx)->setRowHeight(22);
                }

                // Currency Formats
                $currencyFormat = '"' . $this->currencySymbol . '"#,##0.00';
                $sheet->getStyle('G6:G' . $currentRow)->getNumberFormat()->setFormatCode($currencyFormat);
                $sheet->getStyle('I6:J' . $currentRow)->getNumberFormat()->setFormatCode($currencyFormat);

                // Auto-width columns A-J
                foreach (range('A', 'J') as $col) {
                    $sheet->getColumnDimension($col)->setAutoSize(true);
                }
                
                // Explicit sizes for Side summary columns
                $sheet->getColumnDimension('K')->setWidth(4);
                $sheet->getColumnDimension('L')->setWidth(15);
                $sheet->getColumnDimension('M')->setWidth(15);

                $sheet->freezePane('A6');
            }
        ];
    }
}
