<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use App\Models\MeterReading;
use Carbon\Carbon;

class MeterReadingExport implements FromCollection, WithHeadings, WithMapping, WithTitle, ShouldAutoSize, WithStyles
{
    protected $billingPeriod;
    protected $type;
    protected $companyId;
    protected $search;
    protected $propertyId;

    public function __construct($billingPeriod, $type = null, $companyId, $search = null, $propertyId = null)
    {
        $this->billingPeriod = $billingPeriod;
        $this->type = $type;
        $this->companyId = $companyId;
        $this->search = $search;
        $this->propertyId = $propertyId;
    }

    public function collection()
    {
        $query = MeterReading::with(['customer', 'unit.property'])
            ->whereHas('unit.property', function($q) {
                $q->where('CompanyID', $this->companyId);
            })
            ->whereNotNull('Reading')
            ->whereNotNull('Consumption');

        // Filter by billing period
        if ($this->billingPeriod) {
            $query->where('BillingPeriod', $this->billingPeriod);
        }

        // Filter by consumption type if specified
        if ($this->type) {
            $query->where('ConsType', $this->type);
        }

        // Filter by property if specified
        if ($this->propertyId) {
            $query->whereHas('unit', function($q) {
                $q->where('PropID', $this->propertyId);
            });
        }

        // Search by account number or customer name
        if ($this->search) {
            $searchTerm = '%' . $this->search . '%';
            $query->whereHas('customer', function($q) use ($searchTerm) {
                $q->where('AccountNo', 'LIKE', $searchTerm)
                  ->orWhere('CustomerName', 'LIKE', $searchTerm);
            });
        }

        return $query->orderBy('ReadingDate', 'desc')->get();
    }

    public function headings(): array
    {
        return [
            'Date',
            'Customer',
            'Account No',
            'Unit',
            'Reading',
            'Previous Reading',
            'Consumption',
            'Type',
            'Meter Number',
            'Billing Period'
        ];
    }

    public function map($reading): array
    {
        return [
            $reading->ReadingDate ? Carbon::parse($reading->ReadingDate)->format('M d, Y') : 'N/A',
            $reading->customer->CustomerName ?? 'N/A',
            $reading->customer->AccountNo ?? 'N/A',
            $reading->unit->UnitIdentity ?? 'N/A',
            (float)$reading->Reading,
            $reading->PrvReading ? (float)$reading->PrvReading : null,
            (float)$reading->Consumption,
            $reading->ConsType,
            $reading->MeterNo ?? 'N/A',
            $reading->BillingPeriod
        ];
    }

    public function title(): string
    {
        $period = $this->billingPeriod ? Carbon::createFromFormat('Ym', $this->billingPeriod)->format('M Y') : 'All Periods';
        return 'Processed Meter Readings - ' . $period;
    }

    public function styles(Worksheet $sheet)
    {
        return [
            // Style the first row as bold text
            1 => [
                'font' => ['bold' => true],
                'fill' => [
                    'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                    'startColor' => ['rgb' => 'D9EAD3']
                ]
            ],
            // Style numeric columns
            'E:F' => [
                'numberFormat' => [
                    'formatCode' => '#,##0.00'
                ]
            ],
            'G' => [
                'numberFormat' => [
                    'formatCode' => '#,##0.00'
                ]
            ]
        ];
    }
}
