<?php

namespace App\Exports;

use App\Models\Bill;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class BillSummaryExport implements FromQuery, WithHeadings, WithMapping, WithStyles
{
    protected $startDate;
    protected $endDate;
    protected $propertyId;

    public function __construct($startDate, $endDate, $propertyId = null)
    {
        $this->startDate = $startDate;
        $this->endDate = $endDate;
        $this->propertyId = $propertyId;
    }

    public function query()
    {
        $query = Bill::with(['customer', 'property', 'unit'])
            ->whereBetween('BillingDate', [$this->startDate, $this->endDate])
            ->orderBy('BillingDate', 'desc');
            
        if ($this->propertyId) {
            $query->whereHas('unit', function($q) {
                $q->where('PropID', $this->propertyId);
            });
        }
        
        return $query;
    }

    public function headings(): array
    {
        return [
            'Bill #',
            'Date',
            'Customer',
            'Property',
            'Unit',
            'Amount',
            'Paid',
            'Balance',
            'Status'
        ];
    }

    public function map($bill): array
    {
        return [
            $bill->BillNo,
            $bill->BillingDate->format('M d, Y'),
            $bill->customer->FullName ?? 'N/A',
            $bill->property->PropName ?? 'N/A',
            $bill->unit->UnitNo ?? 'N/A',
            number_format($bill->Amount, 2),
            number_format($bill->PaidAmount, 2),
            number_format($bill->Balance, 2),
            $bill->Balance <= 0 ? 'Paid' : ($bill->PaidAmount > 0 ? 'Partial' : 'Unpaid')
        ];
    }

    public function styles(Worksheet $sheet)
    {
        // Set header row style
        $sheet->getStyle('A1:I1')->applyFromArray([
            'font' => ['bold' => true],
            'fill' => [
                'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                'startColor' => ['rgb' => 'E5E7EB'],
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                ],
            ],
        ]);

        // Set auto size for columns
        foreach (range('A', 'I') as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }

        // Set number format for amount columns
        $sheet->getStyle('F2:H' . ($sheet->getHighestRow()))->getNumberFormat()->setFormatCode('#,##0.00');

        // Add total row
        $lastRow = $sheet->getHighestRow() + 1;
        $sheet->setCellValue('E' . $lastRow, 'TOTALS:');
        $sheet->setCellValue('F' . $lastRow, '=SUM(F2:F' . ($lastRow - 1) . ')');
        $sheet->setCellValue('G' . $lastRow, '=SUM(G2:G' . ($lastRow - 1) . ')');
        $sheet->setCellValue('H' . $lastRow, '=SUM(H2:H' . ($lastRow - 1) . ')');
        
        // Style the total row
        $sheet->getStyle('A' . $lastRow . ':I' . $lastRow)->applyFromArray([
            'font' => ['bold' => true],
            'fill' => [
                'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                'startColor' => ['rgb' => 'F3F4F6'],
            ],
            'borders' => [
                'top' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                ],
                'bottom' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_DOUBLE,
                ],
            ],
        ]);

        // Format the total row numbers
        $sheet->getStyle('F' . $lastRow . ':H' . $lastRow)->getNumberFormat()->setFormatCode('#,##0.00');
    }
}
