<?php

namespace App\Jobs;

use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Facades\Mail;
use Illuminate\Support\Facades\Log;
use App\Mail\ExportNotification;
use App\Events\ExportCompleted;
use App\Events\ExportFailed;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExportPrimaNota implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    public $timeout = 1200;
    public $tries = 3;
    public $maxExceptions = 3;

    //protected $exportData;
    //protected $exportTotals;
    protected $records;
    protected $emailAddress;
    protected $emailSubject;
    protected $dateRange;
    protected $userId;
    protected $payments;
    protected $filters;

    /**
     * Create a new job instance.
     */
    //public function __construct($exportData, $exportTotals, $emailAddress, $emailSubject, $dateRange, $userId, $payments, $filters = [])
    public function __construct($records, $emailAddress, $emailSubject, $dateRange, $userId, $payments, $filters = [])
    {
        //$this->exportData = $exportData;
        //$this->exportTotals = $exportTotals;
        $this->records = $records;
        $this->emailAddress = $emailAddress;
        $this->emailSubject = $emailSubject;
        $this->dateRange = $dateRange;
        $this->userId = $userId;
        $this->payments = $payments;
        $this->filters = $filters;

        $this->onQueue('exports');
    }

    /**
     * Execute the job.
     */
    public function handle()
    {
        try {
            Log::info('Starting background export', [
                'user_id' => $this->userId,
                'email' => $this->emailAddress,
                'date_range' => $this->dateRange,
                //'total_records' => count($this->exportData)
            ]);

            ini_set('memory_limit', '1024M');

            $filename = 'prima_nota_' . date("Ymd_His") . '_' . $this->userId . '.xlsx';
            $tempPath = sys_get_temp_dir() . '/' . $filename;

            $this->createExcelFile($tempPath);

            if (!file_exists($tempPath) || filesize($tempPath) === 0) {
                throw new \Exception('Excel file creation failed');
            }

            $fileSize = filesize($tempPath);
            $maxSize = 25 * 1024 * 1024;

            if ($fileSize > $maxSize) {
                throw new \Exception('File too large for email attachment (' . round($fileSize / 1024 / 1024, 2) . 'MB > 25MB)');
            }

            $user = \App\Models\User::find($this->userId);

            $emailData = [
                'subject' => $this->emailSubject,
                'from_date' => $this->dateRange['from'],
                'to_date' => $this->dateRange['to'],
                // 'total_records' => count($this->exportData),
                'total_records' => count($this->records) - 1,
                'user_name' => $user ? $user->name : 'Utente',
                'generated_at' => now()->format('d/m/Y H:i:s'),
                'filters_applied' => $this->getFiltersDescription(),
                'file_size' => round($fileSize / 1024 / 1024, 2) . ' MB'
            ];

            Mail::to($this->emailAddress)->send(new ExportNotification($emailData, $tempPath, $filename));

            if (class_exists(ExportCompleted::class)) {
                broadcast(new ExportCompleted($this->userId, $filename, $this->emailAddress));
            }

            Log::info('Background export completed successfully', [
                'user_id' => $this->userId,
                'email' => $this->emailAddress,
                'filename' => $filename,
                'file_size' => $fileSize,
                'processing_time' => microtime(true) - LARAVEL_START ?? 0
            ]);
        } catch (\Exception $e) {
            Log::error('Background export failed', [
                'user_id' => $this->userId,
                'email' => $this->emailAddress,
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);

            if (class_exists(ExportFailed::class)) {
                broadcast(new ExportFailed($this->userId, $e->getMessage()));
            }

            throw $e;
        } finally {
            if (isset($tempPath) && file_exists($tempPath)) {
                unlink($tempPath);
            }

            gc_collect_cycles();
        }
    }

    /**
     * Handle a job failure.
     */
    public function failed(\Throwable $exception)
    {
        Log::error('Export job failed permanently', [
            'user_id' => $this->userId,
            'email' => $this->emailAddress,
            'attempts' => $this->attempts(),
            'error' => $exception->getMessage()
        ]);

        try {
            Mail::raw(
                "Il tuo export della Prima Nota non è riuscito dopo {$this->tries} tentativi.\n\n" .
                    "Errore: {$exception->getMessage()}\n\n" .
                    "Contatta il supporto tecnico se il problema persiste.",
                function ($message) {
                    $message->to($this->emailAddress)
                        ->subject('Export Prima Nota - Errore');
                }
            );
        } catch (\Exception $e) {
            Log::error('Failed to send failure notification email', [
                'user_id' => $this->userId,
                'error' => $e->getMessage()
            ]);
        }
    }

    /**
     * Create Excel file with export data
     */

    private function createExcelFile($filePath)
    {
        $startTime = microtime(true);

        $spreadsheet = new Spreadsheet();
        $activeWorksheet = $spreadsheet->getActiveSheet();

        $activeWorksheet->setCellValue('A1', "Data");
        $activeWorksheet->setCellValue('B1', "Tipologia");
        $activeWorksheet->setCellValue('C1', "Causale");
        $activeWorksheet->setCellValue('D1', "Nominativo");
        $activeWorksheet->setCellValue('E1', "Stato");
        $activeWorksheet->setCellValue('F1', "Entrata");
        $activeWorksheet->setCellValue('G1', "Uscita");
        $activeWorksheet->setCellValue('H1', "Origine");
        $activeWorksheet->setCellValue('I1', "Destinazione");
        $activeWorksheet->setCellValue('J1', "Metodo di pagamento");

        $activeWorksheet->getStyle('A1:J1')->getFill()
            ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
            ->getStartColor()->setARGB('FF0C6197');

        $activeWorksheet->getStyle('A1:J1')->getFont()->getColor()->setARGB('FFFFFFFF');

        $idx = 2;
        foreach($this->records as $record)
        {
            if ($record->date != '')
            {
                $activeWorksheet->setCellValue('A' . $idx, date("d/m/Y", strtotime($record->date)));
                $activeWorksheet->setCellValue('B' . $idx, $record->commercial ? 'Commerciale' : 'Non commerciale');
                $activeWorksheet->setCellValue('C' . $idx, $record->causal_name);
                $activeWorksheet->setCellValue('D' . $idx, $record->type == 'IN' ? ($record->member->first_name . " " . $record->member->last_name) : @$record->supplier->name);
                $activeWorksheet->setCellValue('E' . $idx, $record->deleted ? 'Annullata' : '');
                $activeWorksheet->setCellValue('F' . $idx, $record->type == 'IN' ? formatPrice($record->amount) : '');
                $activeWorksheet->setCellValue('G' . $idx, $record->type == 'OUT' ? formatPrice($record->amount) : '');
                $activeWorksheet->setCellValue('H' . $idx, $record->type == 'OUT' ? $record->origin : '');
                $activeWorksheet->setCellValue('I' . $idx, $record->type == 'IN' ? $record->destination : '');
                $activeWorksheet->setCellValue('J' . $idx, $record->payment_method->name);
            }
            else
            {
                $activeWorksheet->setCellValue('A' . $idx, "Totali");
                $activeWorksheet->setCellValue('B' . $idx, "");
                $activeWorksheet->setCellValue('C' . $idx, "");
                $activeWorksheet->setCellValue('D' . $idx, "");
                $activeWorksheet->setCellValue('E' . $idx, "");
                $activeWorksheet->setCellValue('F' . $idx, formatPrice($record->total_in));
                $activeWorksheet->setCellValue('G' . $idx, formatPrice($record->total_out));
                $activeWorksheet->setCellValue('H' . $idx, "");
                $activeWorksheet->setCellValue('I' . $idx, "");
                $activeWorksheet->setCellValue('J' . $idx, "");
            }
            $idx++;
        }

        $activeWorksheet->getColumnDimension('A')->setWidth(10);
        $activeWorksheet->getColumnDimension('B')->setWidth(30);
        $activeWorksheet->getColumnDimension('C')->setWidth(30);
        $activeWorksheet->getColumnDimension('D')->setWidth(30);
        $activeWorksheet->getColumnDimension('E')->setWidth(10);
        $activeWorksheet->getColumnDimension('F')->setWidth(10);
        $activeWorksheet->getColumnDimension('G')->setWidth(10);
        $activeWorksheet->getColumnDimension('H')->setWidth(20);
        $activeWorksheet->getColumnDimension('I')->setWidth(20);
        $activeWorksheet->getColumnDimension('J')->setWidth(30);

        $activeWorksheet->getStyle('A' . ($idx - 1) . ':J' . ($idx - 1))->getFont()->setBold(true);

        try {
            $writerStart = microtime(true);
            $writer = new Xlsx($spreadsheet);
            $writer->save($filePath);
            $writerTime = microtime(true) - $writerStart;

            Log::info('Job createExcelFile: File saved successfully', [
                'file_path' => $filePath,
                'file_exists' => file_exists($filePath),
                'file_size' => file_exists($filePath) ? filesize($filePath) : 0,
                'writer_time' => $writerTime,
                'total_time' => microtime(true) - $startTime,
                'memory_peak' => memory_get_peak_usage(true)
            ]);
        } catch (\Exception $e) {
            Log::error('Job createExcelFile: Error during file save', [
                'file_path' => $filePath,
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString(),
                'memory_usage' => memory_get_usage(true),
                'time_elapsed' => microtime(true) - $startTime
            ]);
            throw $e;
        }

        Log::info('Job createExcelFile: Cleaning up memory');
        unset($spreadsheet, $activeWorksheet, $writer);
        gc_collect_cycles();

        Log::info('Job createExcelFile: Completed successfully', [
            'total_time' => microtime(true) - $startTime,
            'memory_after_cleanup' => memory_get_usage(true)
        ]);
    }

    private function createExcelFileOLD($filePath)
    {
        Log::info('Job createExcelFile: Starting Excel file creation', [
            'file_path' => $filePath,
            //'export_data_count' => count($this->exportData),
            'payments_count' => count($this->payments),
            'memory_before' => memory_get_usage(true),
            'time_limit' => ini_get('max_execution_time')
        ]);

        $startTime = microtime(true);

        Log::info('Job createExcelFile: Preparing column letters');
        $letters = array('F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA');

        Log::info('Job createExcelFile: Creating Spreadsheet object');
        $spreadsheet = new Spreadsheet();
        $activeWorksheet = $spreadsheet->getActiveSheet();

        $activeWorksheet->setTitle('Prima Nota');

        Log::info('Job createExcelFile: Setting document properties');
        $spreadsheet->getProperties()
            ->setCreator('Prima Nota System')
            ->setLastModifiedBy('Sistema')
            ->setTitle('Prima Nota Export')
            ->setSubject('Export Prima Nota')
            ->setDescription('Export dei dati Prima Nota dal ' . $this->dateRange['from'] . ' al ' . $this->dateRange['to']);

        Log::info('Job createExcelFile: Setting basic headers');
        $activeWorksheet->setCellValue('A1', "Data");
        $activeWorksheet->setCellValue('B1', "Causale");
        $activeWorksheet->setCellValue('C1', "Dettaglio Causale");
        $activeWorksheet->setCellValue('D1', "Nominativo");
        $activeWorksheet->setCellValue('E1', "Stato");

        Log::info('Job createExcelFile: Setting payment method headers and merging cells');
        $idx = 0;
        foreach ($this->payments as $p) {
            if ($idx >= count($letters)) {
                Log::warning('Job createExcelFile: Reached letter limit during header setup', [
                    'payment_index' => $idx,
                    'payment_name' => $p['name']
                ]);
                break;
            }

            Log::debug('Job createExcelFile: Setting payment header', [
                'payment_name' => $p['name'],
                'column_index' => $idx,
                'column_letter' => $letters[$idx]
            ]);

            $activeWorksheet->setCellValue($letters[$idx] . '1', $p['name']);
            $activeWorksheet->mergeCells($letters[$idx] . '1:' . $letters[$idx + 1] . '1');
            $idx += 2;
        }

        Log::info('Job createExcelFile: Setting sub-headers (row 2)');
        $activeWorksheet->setCellValue('A2', "");
        $activeWorksheet->setCellValue('B2', "");
        $activeWorksheet->setCellValue('C2', "");
        $activeWorksheet->setCellValue('D2', "");
        $activeWorksheet->setCellValue('E2', "");

        $idx = 0;
        foreach ($this->payments as $p) {
            if ($idx >= count($letters) - 1) {
                Log::warning('Job createExcelFile: Reached letter limit during sub-header setup', [
                    'payment_index' => $idx,
                    'payment_name' => $p['name']
                ]);
                break;
            }

            if ($p['type'] == 'ALL') {
                $activeWorksheet->setCellValue($letters[$idx] . '2', "Entrate");
                $idx++;
                $activeWorksheet->setCellValue($letters[$idx] . '2', "Uscite");
                $idx++;
            } elseif ($p['type'] == 'IN') {
                $activeWorksheet->setCellValue($letters[$idx] . '2', "Entrate");
                $idx++;
                $activeWorksheet->setCellValue($letters[$idx] . '2', "");
                $idx++;
            } elseif ($p['type'] == 'OUT') {
                $activeWorksheet->setCellValue($letters[$idx] . '2', "");
                $idx++;
                $activeWorksheet->setCellValue($letters[$idx] . '2', "Uscite");
                $idx++;
            }
        }

        Log::info('Job createExcelFile: Applying header styles');
        $maxCol = min(count($letters) - 1, count($this->payments) * 2 + 4);
        $lastColumnLetter = $letters[$maxCol];

        $activeWorksheet->getStyle('A1:' . $lastColumnLetter . '2')
            ->getFont()->setBold(true);

        $activeWorksheet->getStyle('A1:' . $lastColumnLetter . '1')
            ->getFill()
            ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
            ->getStartColor()->setARGB('FF0C6197');

        $activeWorksheet->getStyle('A1:' . $lastColumnLetter . '1')
            ->getFont()->getColor()->setARGB('FFFFFFFF');

        Log::info('Job createExcelFile: Starting data row processing', [
            //'total_export_records' => count($this->exportData),
            'time_elapsed_so_far' => microtime(true) - $startTime
        ]);

        $count = 3;
        $batchSize = 500;
        $processed = 0;
        $batchNumber = 0;

        foreach ($this->exportData as $causal => $record) {
            if ($processed % 100 == 0) {
                Log::info('Job createExcelFile: Processing progress', [
                    'processed' => $processed,
                    'total' => count($this->exportData),
                    'current_row' => $count,
                    'memory_usage' => memory_get_usage(true),
                    'memory_peak' => memory_get_peak_usage(true),
                    'time_elapsed' => microtime(true) - $startTime
                ]);
            }

            try {
                $parts = explode("§", $causal);
                $d = $parts[0] ?? '';
                $c = $parts[1] ?? '';
                $j = $parts[2] ?? '';
                $det = $parts[3] ?? '';
                $deleted = $parts[4] ?? '';

                $detailParts = explode('|', $det);
                $exportDetail = count($detailParts) > 1 ? implode(', ', array_slice($detailParts, 1)) : $det;

                Log::debug('Job createExcelFile: Setting row data', [
                    'row' => $count,
                    'causal_parts' => count($parts)
                ]);

                $activeWorksheet->setCellValue('A' . $count, !empty($d) ? date("d/m/Y", strtotime($d)) : '');
                $activeWorksheet->setCellValue('B' . $count, $c);
                $activeWorksheet->setCellValue('C' . $count, $exportDetail);
                $activeWorksheet->setCellValue('D' . $count, $j);

                $stato = ($deleted === 'DELETED') ? 'ANNULLATA' : '';
                $activeWorksheet->setCellValue('E' . $count, $stato);

                if ($stato === 'ANNULLATA') {
                    $activeWorksheet->getStyle('E' . $count)->getFont()->getColor()->setARGB('FFFF0000');
                }

                Log::debug('Job createExcelFile: Setting payment data for row', ['row' => $count]);
                $idx = 0;
                foreach ($this->payments as $p) {
                    if ($idx >= count($letters) - 1) {
                        Log::warning('Job createExcelFile: Reached letter limit during payment data', [
                            'row' => $count,
                            'payment_index' => $idx
                        ]);
                        break;
                    }

                    if (isset($record[$p['name']])) {
                        $inValue = isset($record[$p['name']]["IN"]) ? $this->formatPrice($record[$p['name']]["IN"]) : "";
                        $outValue = isset($record[$p['name']]["OUT"]) ? $this->formatPrice($record[$p['name']]["OUT"]) : "";

                        $activeWorksheet->setCellValue($letters[$idx] . $count, $inValue);
                        $idx++;
                        $activeWorksheet->setCellValue($letters[$idx] . $count, $outValue);
                        $idx++;
                    } else {
                        $activeWorksheet->setCellValue($letters[$idx] . $count, "");
                        $idx++;
                        $activeWorksheet->setCellValue($letters[$idx] . $count, "");
                        $idx++;
                    }
                }

                $count++;
                $processed++;

                if ($processed % $batchSize === 0) {
                    $batchNumber++;
                    Log::info('Job createExcelFile: Batch completed, running garbage collection', [
                        'batch_number' => $batchNumber,
                        'processed' => $processed,
                        'memory_before_gc' => memory_get_usage(true)
                    ]);
                    gc_collect_cycles();
                    Log::info('Job createExcelFile: Garbage collection completed', [
                        'memory_after_gc' => memory_get_usage(true)
                    ]);
                }
            } catch (\Exception $e) {
                Log::error('Job createExcelFile: Error processing data row', [
                    'row' => $count,
                    'processed_so_far' => $processed,
                    'causal' => $causal,
                    'error' => $e->getMessage(),
                    'trace' => $e->getTraceAsString()
                ]);
                throw $e;
            }
        }

        Log::info('Job createExcelFile: Data processing completed, adding totals row', [
            'total_processed' => $processed,
            'final_row' => $count,
            'processing_time' => microtime(true) - $startTime
        ]);

        $count++;
        $activeWorksheet->setCellValue('A' . $count, 'TOTALE');
        $activeWorksheet->setCellValue('B' . $count, '');
        $activeWorksheet->setCellValue('C' . $count, '');
        $activeWorksheet->setCellValue('D' . $count, '');
        $activeWorksheet->setCellValue('E' . $count, '');

        Log::info('Job createExcelFile: Setting totals data');
        $idx = 0;
        foreach ($this->payments as $p) {
            if ($idx >= count($letters) - 1) {
                break;
            }

            if (isset($this->exportTotals[$p['name']])) {
                if ($p['type'] == 'ALL') {
                    $activeWorksheet->setCellValue($letters[$idx] . $count, $this->formatPrice($this->exportTotals[$p['name']]["IN"] ?? 0));
                    $idx++;
                    $activeWorksheet->setCellValue($letters[$idx] . $count, $this->formatPrice($this->exportTotals[$p['name']]["OUT"] ?? 0));
                    $idx++;
                } elseif ($p['type'] == 'IN') {
                    $activeWorksheet->setCellValue($letters[$idx] . $count, $this->formatPrice($this->exportTotals[$p['name']]["IN"] ?? 0));
                    $idx++;
                    $activeWorksheet->setCellValue($letters[$idx] . $count, "");
                    $idx++;
                } elseif ($p['type'] == 'OUT') {
                    $activeWorksheet->setCellValue($letters[$idx] . $count, "");
                    $idx++;
                    $activeWorksheet->setCellValue($letters[$idx] . $count, $this->formatPrice($this->exportTotals[$p['name']]["OUT"] ?? 0));
                    $idx++;
                }
            } else {
                $activeWorksheet->setCellValue($letters[$idx] . $count, "0,00");
                $idx++;
                $activeWorksheet->setCellValue($letters[$idx] . $count, "0,00");
                $idx++;
            }
        }

        Log::info('Job createExcelFile: Applying totals row styling');
        $activeWorksheet->getStyle('A' . $count . ':' . $lastColumnLetter . $count)
            ->getFont()->setBold(true);

        $activeWorksheet->getStyle('A' . $count . ':' . $lastColumnLetter . $count)
            ->getFill()
            ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
            ->getStartColor()->setARGB('FFF0F0F0');

        Log::info('Job createExcelFile: Setting column dimensions');
        $activeWorksheet->getColumnDimension('A')->setWidth(15);
        $activeWorksheet->getColumnDimension('B')->setWidth(25);
        $activeWorksheet->getColumnDimension('C')->setWidth(30);
        $activeWorksheet->getColumnDimension('D')->setWidth(25);
        $activeWorksheet->getColumnDimension('E')->setWidth(15);

        foreach ($letters as $l) {
            $activeWorksheet->getColumnDimension($l)->setWidth(15);
        }

        Log::info('Job createExcelFile: Setting freeze panes');
        $activeWorksheet->freezePane('A3');

        Log::info('Job createExcelFile: Creating Excel writer and saving file', [
            'file_path' => $filePath,
            'memory_before_save' => memory_get_usage(true)
        ]);

        try {
            $writerStart = microtime(true);
            $writer = new Xlsx($spreadsheet);
            $writer->save($filePath);
            $writerTime = microtime(true) - $writerStart;

            Log::info('Job createExcelFile: File saved successfully', [
                'file_path' => $filePath,
                'file_exists' => file_exists($filePath),
                'file_size' => file_exists($filePath) ? filesize($filePath) : 0,
                'writer_time' => $writerTime,
                'total_time' => microtime(true) - $startTime,
                'memory_peak' => memory_get_peak_usage(true)
            ]);
        } catch (\Exception $e) {
            Log::error('Job createExcelFile: Error during file save', [
                'file_path' => $filePath,
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString(),
                'memory_usage' => memory_get_usage(true),
                'time_elapsed' => microtime(true) - $startTime
            ]);
            throw $e;
        }

        Log::info('Job createExcelFile: Cleaning up memory');
        unset($spreadsheet, $activeWorksheet, $writer);
        gc_collect_cycles();

        Log::info('Job createExcelFile: Completed successfully', [
            'total_time' => microtime(true) - $startTime,
            'memory_after_cleanup' => memory_get_usage(true)
        ]);
    }

    // Generate column letters more efficiently
    private function generateColumnLetters($count)
    {
        $letters = [];
        for ($i = 0; $i < $count && $i < 100; $i++) { // Limit to prevent infinite loops
            if ($i < 26) {
                $letters[] = chr(65 + $i); // A-Z
            } else {
                $letters[] = 'A' . chr(65 + ($i - 26)); // AA, AB, AC...
            }
        }
        return $letters;
    }

    // Build headers more efficiently
    private function buildHeaders($activeWorksheet, $letters)
    {
        // Set basic headers
        $basicHeaders = [
            'A1' => 'Data',
            'B1' => 'Causale',
            'C1' => 'Dettaglio Causale',
            'D1' => 'Nominativo',
            'E1' => 'Stato'
        ];

        // Use fromArray for faster header setting
        $activeWorksheet->fromArray(array_values($basicHeaders), null, 'A1', true);

        // Set payment method headers
        $paymentHeaders = [];
        $subHeaders = [];
        $idx = 5; // Start after basic headers (F column = index 5)

        foreach ($this->payments as $p) {
            if ($idx >= count($letters)) break;

            $paymentHeaders[] = $p['name'];
            $activeWorksheet->mergeCells($letters[$idx] . '1:' . $letters[$idx + 1] . '1');

            // Sub headers for row 2
            if ($p['type'] == 'ALL') {
                $subHeaders[$letters[$idx] . '2'] = 'Entrate';
                $subHeaders[$letters[$idx + 1] . '2'] = 'Uscite';
            } elseif ($p['type'] == 'IN') {
                $subHeaders[$letters[$idx] . '2'] = 'Entrate';
                $subHeaders[$letters[$idx + 1] . '2'] = '';
            } elseif ($p['type'] == 'OUT') {
                $subHeaders[$letters[$idx] . '2'] = '';
                $subHeaders[$letters[$idx + 1] . '2'] = 'Uscite';
            }

            $idx += 2;
        }

        // Set payment headers
        $col = 5;
        foreach ($paymentHeaders as $header) {
            if ($col < count($letters)) {
                $activeWorksheet->setCellValue($letters[$col] . '1', $header);
            }
            $col += 2;
        }

        // Set sub headers
        foreach ($subHeaders as $cell => $value) {
            $activeWorksheet->setCellValue($cell, $value);
        }
    }

    // Build data rows with batch processing
    private function buildDataRows($activeWorksheet, $letters)
    {
        $rowNum = 3; // Start after headers
        $batchSize = 100; // Process in smaller batches
        $batch = [];
        $batchCount = 0;

        foreach ($this->exportData as $causal => $record) {
            $parts = explode("§", $causal);
            $d = $parts[0] ?? '';
            $c = $parts[1] ?? '';
            $j = $parts[2] ?? '';
            $det = $parts[3] ?? '';
            $deleted = $parts[4] ?? '';

            $detailParts = explode('|', $det);
            $exportDetail = count($detailParts) > 1 ? implode(', ', array_slice($detailParts, 1)) : $det;

            // Prepare row data
            $rowData = [
                !empty($d) ? date("d/m/Y", strtotime($d)) : '',
                $c,
                $exportDetail,
                $j,
                ($deleted === 'DELETED') ? 'ANNULLATA' : ''
            ];

            // Add payment method values
            $idx = 0;
            foreach ($this->payments as $p) {
                if ($idx >= count($letters) - 6) break; // Leave room for basic columns

                if (isset($record[$p['name']])) {
                    $inValue = isset($record[$p['name']]["IN"]) ? $this->formatPrice($record[$p['name']]["IN"]) : "";
                    $outValue = isset($record[$p['name']]["OUT"]) ? $this->formatPrice($record[$p['name']]["OUT"]) : "";
                    $rowData[] = $inValue;
                    $rowData[] = $outValue;
                } else {
                    $rowData[] = "";
                    $rowData[] = "";
                }
                $idx += 2;
            }

            $batch[] = $rowData;
            $batchCount++;

            // Process batch when it reaches batch size
            if ($batchCount >= $batchSize) {
                $this->writeBatchToWorksheet($activeWorksheet, $batch, $rowNum);
                $rowNum += $batchCount;
                $batch = [];
                $batchCount = 0;
                gc_collect_cycles(); // Force garbage collection
            }
        }

        // Process remaining batch
        if (!empty($batch)) {
            $this->writeBatchToWorksheet($activeWorksheet, $batch, $rowNum);
        }
    }

    // Write batch data efficiently
    private function writeBatchToWorksheet($activeWorksheet, $batch, $startRow)
    {
        if (empty($batch)) return;

        try {
            // Use fromArray for much faster bulk insertion
            $activeWorksheet->fromArray($batch, null, 'A' . $startRow, true);

            // Apply conditional formatting for deleted records
            foreach ($batch as $index => $row) {
                $currentRow = $startRow + $index;
                if (isset($row[4]) && $row[4] === 'ANNULLATA') {
                    $activeWorksheet->getStyle('E' . $currentRow)
                        ->getFont()->getColor()->setARGB('FFFF0000');
                }
            }
        } catch (\Exception $e) {
            Log::error('Error writing batch to worksheet', [
                'error' => $e->getMessage(),
                'batch_size' => count($batch),
                'start_row' => $startRow
            ]);

            // Fallback to individual cell setting
            foreach ($batch as $index => $row) {
                $currentRow = $startRow + $index;
                foreach ($row as $colIndex => $value) {
                    $col = $this->getColumnLetter($colIndex);
                    $activeWorksheet->setCellValue($col . $currentRow, $value);
                }
            }
        }
    }

    // Helper to get column letter by index
    private function getColumnLetter($index)
    {
        if ($index < 26) {
            return chr(65 + $index);
        } else {
            return 'A' . chr(65 + ($index - 26));
        }
    }

    // Build totals row efficiently
    private function buildTotalsRow($activeWorksheet, $letters)
    {
        $totalRows = count($this->exportData) + 3; // +3 for headers and spacing
        $totalRow = $totalRows + 1;

        $totalsData = ['TOTALE', '', '', '', ''];

        $idx = 0;
        foreach ($this->payments as $p) {
            if ($idx >= count($letters) - 6) break;

            if (isset($this->exportTotals[$p['name']])) {
                if ($p['type'] == 'ALL') {
                    $totalsData[] = $this->formatPrice($this->exportTotals[$p['name']]["IN"] ?? 0);
                    $totalsData[] = $this->formatPrice($this->exportTotals[$p['name']]["OUT"] ?? 0);
                } elseif ($p['type'] == 'IN') {
                    $totalsData[] = $this->formatPrice($this->exportTotals[$p['name']]["IN"] ?? 0);
                    $totalsData[] = "";
                } elseif ($p['type'] == 'OUT') {
                    $totalsData[] = "";
                    $totalsData[] = $this->formatPrice($this->exportTotals[$p['name']]["OUT"] ?? 0);
                }
            } else {
                $totalsData[] = "0,00";
                $totalsData[] = "0,00";
            }
            $idx += 2;
        }

        // Write totals row
        $activeWorksheet->fromArray([$totalsData], null, 'A' . $totalRow, true);
    }

    // Apply styles more efficiently
    private function applyStylesEfficiently($activeWorksheet, $letters)
    {
        $maxCol = min(count($letters) - 1, count($this->payments) * 2 + 4);
        $lastCol = $letters[$maxCol];
        $totalRows = count($this->exportData) + 4; // +4 for headers, spacing, and totals

        // Apply header styles
        $headerRange = 'A1:' . $lastCol . '2';
        $activeWorksheet->getStyle($headerRange)->getFont()->setBold(true);

        // Apply header background
        $activeWorksheet->getStyle('A1:' . $lastCol . '1')
            ->getFill()
            ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
            ->getStartColor()->setARGB('FF0C6197');

        $activeWorksheet->getStyle('A1:' . $lastCol . '1')
            ->getFont()->getColor()->setARGB('FFFFFFFF');

        // Apply totals row styles
        $totalsRange = 'A' . $totalRows . ':' . $lastCol . $totalRows;
        $activeWorksheet->getStyle($totalsRange)->getFont()->setBold(true);
        $activeWorksheet->getStyle($totalsRange)
            ->getFill()
            ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
            ->getStartColor()->setARGB('FFF0F0F0');
    }

    // Set column dimensions efficiently
    private function setColumnDimensions($activeWorksheet, $letters)
    {
        $dimensions = [
            'A' => 15,
            'B' => 25,
            'C' => 30,
            'D' => 25,
            'E' => 15
        ];

        foreach ($dimensions as $col => $width) {
            $activeWorksheet->getColumnDimension($col)->setWidth($width);
        }

        // Set payment method column widths
        for ($i = 5; $i < count($letters) && $i < 50; $i++) { // Limit to prevent excessive loops
            $activeWorksheet->getColumnDimension($letters[$i])->setWidth(15);
        }
    }
    /**
     * Format price for display
     */
    private function formatPrice($amount)
    {
        return number_format($amount, 2, ',', '.');
    }

    /**
     * Get description of applied filters
     */
    private function getFiltersDescription()
    {
        $descriptions = [];

        if (!empty($this->filters['member'])) {
            $descriptions[] = "Utente: {$this->filters['member']}";
        }

        if (!empty($this->filters['causals'])) {
            $descriptions[] = "Causali: " . (is_array($this->filters['causals']) ? implode(', ', $this->filters['causals']) : $this->filters['causals']);
        }

        if (!empty($this->filters['payment_methods'])) {
            $descriptions[] = "Metodi di pagamento: " . (is_array($this->filters['payment_methods']) ? implode(', ', $this->filters['payment_methods']) : $this->filters['payment_methods']);
        }

        if (!empty($this->filters['origins'])) {
            $descriptions[] = "Origini: " . (is_array($this->filters['origins']) ? implode(', ', $this->filters['origins']) : $this->filters['origins']);
        }

        if (!empty($this->filters['destinations'])) {
            $descriptions[] = "Destinazioni: " . (is_array($this->filters['destinations']) ? implode(', ', $this->filters['destinations']) : $this->filters['destinations']);
        }

        return empty($descriptions) ? 'Nessun filtro applicato' : implode(' | ', $descriptions);
    }

    public function executeWithTimeoutMonitoring($callback, $description = 'Operation')
    {
        $startTime = microtime(true);
        $maxExecutionTime = ini_get('max_execution_time');

        Log::info("Starting monitored operation: {$description}", [
            'start_time' => $startTime,
            'max_execution_time' => $maxExecutionTime,
            'memory_start' => memory_get_usage(true)
        ]);

        try {
            // Execute every 5 seconds to monitor progress
            $lastCheck = $startTime;
            $result = null;

            // For non-blocking operations, we can't easily interrupt,
            // but we can log progress
            register_tick_function(function () use ($startTime, $maxExecutionTime, $description, &$lastCheck) {
                $currentTime = microtime(true);
                if ($currentTime - $lastCheck >= 5) { // Log every 5 seconds
                    $elapsed = $currentTime - $startTime;
                    $remaining = $maxExecutionTime > 0 ? $maxExecutionTime - $elapsed : 'unlimited';

                    Log::info("Operation progress: {$description}", [
                        'elapsed_time' => $elapsed,
                        'remaining_time' => $remaining,
                        'memory_current' => memory_get_usage(true),
                        'memory_peak' => memory_get_peak_usage(true)
                    ]);

                    if ($maxExecutionTime > 0 && $elapsed > ($maxExecutionTime * 0.8)) {
                        Log::warning("Operation approaching timeout: {$description}", [
                            'elapsed_time' => $elapsed,
                            'max_time' => $maxExecutionTime,
                            'percentage_used' => ($elapsed / $maxExecutionTime) * 100
                        ]);
                    }

                    $lastCheck = $currentTime;
                }
            });

            declare(ticks=1000);
            $result = $callback();

            $totalTime = microtime(true) - $startTime;
            Log::info("Operation completed successfully: {$description}", [
                'total_time' => $totalTime,
                'memory_peak' => memory_get_peak_usage(true)
            ]);

            return $result;
        } catch (\Exception $e) {
            $totalTime = microtime(true) - $startTime;
            Log::error("Operation failed: {$description}", [
                'error' => $e->getMessage(),
                'total_time' => $totalTime,
                'memory_peak' => memory_get_peak_usage(true),
                'trace' => $e->getTraceAsString()
            ]);
            throw $e;
        }
    }

    private function checkTimeoutRisk($operationName, $startTime = null)
    {
        if ($startTime === null) {
            $startTime = $_SERVER['REQUEST_TIME_FLOAT'] ?? microtime(true);
        }

        $maxExecutionTime = ini_get('max_execution_time');
        if ($maxExecutionTime <= 0) {
            return false; // No limit set
        }

        $elapsed = microtime(true) - $startTime;
        $remaining = $maxExecutionTime - $elapsed;
        $percentageUsed = ($elapsed / $maxExecutionTime) * 100;

        Log::info("Timeout check: {$operationName}", [
            'elapsed_time' => $elapsed,
            'remaining_time' => $remaining,
            'percentage_used' => $percentageUsed,
            'memory_usage' => memory_get_usage(true)
        ]);

        if ($percentageUsed > 80) {
            Log::warning("High timeout risk detected: {$operationName}", [
                'elapsed_time' => $elapsed,
                'remaining_time' => $remaining,
                'percentage_used' => $percentageUsed
            ]);
            return true;
        }

        return false;
    }

    // SOLUTION 8: Log configuration and environment info at start

    public function logEnvironmentInfo()
    {
        Log::info('=== EXPORT ENVIRONMENT INFO ===', [
            'php_version' => PHP_VERSION,
            'memory_limit' => ini_get('memory_limit'),
            'max_execution_time' => ini_get('max_execution_time'),
            'max_input_time' => ini_get('max_input_time'),
            'post_max_size' => ini_get('post_max_size'),
            'upload_max_filesize' => ini_get('upload_max_filesize'),
            'default_socket_timeout' => ini_get('default_socket_timeout'),
            'current_memory_usage' => memory_get_usage(true),
            'current_memory_peak' => memory_get_peak_usage(true),
            'server_time' => date('Y-m-d H:i:s'),
            'timezone' => date_default_timezone_get(),
            'sapi_name' => php_sapi_name(),
            'loaded_extensions' => get_loaded_extensions()
        ]);
    }
}