<?php

namespace App\Services;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;

class ExportService
{
    /**
     * Create a styled spreadsheet with company branding header.
     */
    public function createStyledSpreadsheet(string $title): Spreadsheet
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        
        // Company Header - Row 1
        $sheet->setCellValue('A1', 'UEPL - Universal Engineering Pvt Ltd');
        $sheet->mergeCells('A1:H1');
        $sheet->getStyle('A1')->applyFromArray([
            'font' => [
                'bold' => true,
                'size' => 16,
                'color' => ['rgb' => '1a3563']
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER
            ]
        ]);
        
        // Report Title - Row 2
        $sheet->setCellValue('A2', $title . ' - Generated: ' . date('d-m-Y H:i'));
        $sheet->mergeCells('A2:H2');
        $sheet->getStyle('A2')->applyFromArray([
            'font' => [
                'bold' => true,
                'size' => 12,
                'color' => ['rgb' => '4e73df']
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER
            ]
        ]);
        
        // Add spacing row
        $sheet->getRowDimension(3)->setRowHeight(10);
        
        return $spreadsheet;
    }

    /**
     * Style the header row (usually row 4 after company header).
     */
    public function styleHeaderRow($sheet, int $row, int $colCount): void
    {
        $lastCol = $this->getColumnLetter($colCount);
        $range = "A{$row}:{$lastCol}{$row}";
        
        $sheet->getStyle($range)->applyFromArray([
            'font' => [
                'bold' => true,
                'color' => ['rgb' => 'FFFFFF']
            ],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['rgb' => '4e73df']
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['rgb' => '1a3563']
                ]
            ]
        ]);
        
        $sheet->getRowDimension($row)->setRowHeight(25);
    }

    /**
     * Style data rows with alternating colors and borders.
     */
    public function styleDataRows($sheet, int $startRow, int $endRow, int $colCount): void
    {
        $lastCol = $this->getColumnLetter($colCount);
        
        for ($row = $startRow; $row <= $endRow; $row++) {
            $range = "A{$row}:{$lastCol}{$row}";
            
            $bgColor = ($row % 2 == 0) ? 'f8f9fc' : 'FFFFFF';
            
            $sheet->getStyle($range)->applyFromArray([
                'fill' => [
                    'fillType' => Fill::FILL_SOLID,
                    'startColor' => ['rgb' => $bgColor]
                ],
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => Border::BORDER_THIN,
                        'color' => ['rgb' => 'dddddd']
                    ]
                ],
                'alignment' => [
                    'vertical' => Alignment::VERTICAL_CENTER
                ]
            ]);
        }
    }

    /**
     * Style totals row.
     */
    public function styleTotalsRow($sheet, int $row, int $colCount): void
    {
        $lastCol = $this->getColumnLetter($colCount);
        $range = "A{$row}:{$lastCol}{$row}";
        
        $sheet->getStyle($range)->applyFromArray([
            'font' => [
                'bold' => true
            ],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['rgb' => 'e3e6f0']
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_MEDIUM,
                    'color' => ['rgb' => '4e73df']
                ]
            ]
        ]);
    }

    /**
     * Auto-size columns based on content.
     */
    public function autoSizeColumns($sheet, int $colCount): void
    {
        for ($i = 1; $i <= $colCount; $i++) {
            $col = $this->getColumnLetter($i);
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }
    }

    /**
     * Download the spreadsheet as an Excel file.
     */
    public function download(Spreadsheet $spreadsheet, string $filename)
    {
        $writer = new Xlsx($spreadsheet);
        
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        
        $writer->save('php://output');
        exit;
    }

    /**
     * Convert column number to letter (1=A, 2=B, ..., 27=AA).
     */
    private function getColumnLetter(int $colNum): string
    {
        $letter = '';
        while ($colNum > 0) {
            $mod = ($colNum - 1) % 26;
            $letter = chr(65 + $mod) . $letter;
            $colNum = intval(($colNum - $mod) / 26);
        }
        return $letter;
    }
}
