<?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\TMP_MeterReading;
use Carbon\Carbon;

class TMPMeterReadingExport 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 = TMP_MeterReading::query()
            ->whereHas('unit.property', function($q) {
                $q->where('CompanyID', $this->companyId);
            });

        // Eager load relationships
        $query->with([
            'unit' => function($q) {
                $q->select('UnitID', 'UnitIdentity as UnitName', 'PropID')
                  ->whereHas('property', function($q) {
                      $q->where('CompanyID', $this->companyId);
                  });
            },
            'customer' => function($q) {
                $q->select('CustomerID', 'CustomerName', 'AccountNo', 'UnitID')
                  ->whereHas('unit.property', function($q) {
                      $q->where('CompanyID', $this->companyId);
                  });
            }
        ]);

        // 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->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);
            });
        }

        // Get the unit IDs and types that already have processed readings to exclude them
        if ($this->billingPeriod) {
            $processedReadings = \App\Models\MeterReading::query()
                ->where('BillingPeriod', $this->billingPeriod)
                ->whereNotNull('Reading')
                ->whereNotNull('Consumption')
                ->select('UnitID', 'ConsType')
                ->get()
                ->groupBy(['UnitID', 'ConsType']);

            // Only include readings that don't have a corresponding processed reading
            $query->where(function($q) use ($processedReadings) {
                $q->whereNotIn('UnitID', $processedReadings->keys())
                  ->orWhere(function($q) use ($processedReadings) {
                      $q->whereIn('UnitID', $processedReadings->keys())
                        ->whereNotIn('ConsType', function($query) use ($processedReadings) {
                            $query->select('ConsType')
                                ->from('meterreading')
                                ->whereNotNull('Reading')
                                ->whereNotNull('Consumption')
                                ->whereColumn('UnitID', 'tmp_meterreading.UnitID')
                                ->whereColumn('BillingPeriod', 'tmp_meterreading.BillingPeriod');
                        });
                  });
            });
        }

        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',
            'Comments'
        ];
    }

    public function map($reading): array
    {
        $readingDate = 'N/A';
        if ($reading->ReadingDate) {
            try {
                $readingDate = is_string($reading->ReadingDate)
                    ? Carbon::parse($reading->ReadingDate)->format('M d, Y')
                    : $reading->ReadingDate->format('M d, Y');
            } catch (\Exception $e) {
                // Keep as N/A
            }
        }

        return [
            $readingDate,
            $reading->customer ? $reading->customer->CustomerName : 'N/A',
            $reading->customer ? $reading->customer->AccountNo : 'N/A',
            $reading->unit ? $reading->unit->UnitName : 'N/A',
            (float)($reading->Reading ?? 0),
            $reading->PrvReading ? (float)$reading->PrvReading : null,
            $reading->Consumption ? (float)$reading->Consumption : null,
            $reading->ConsType,
            $reading->MeterNo ?? 'N/A',
            $reading->BillingPeriod ?? 'N/A',
            $reading->ReadingComments ?? ''
        ];
    }

    public function title(): string
    {
        $period = $this->billingPeriod ? Carbon::createFromFormat('Ym', $this->billingPeriod)->format('M Y') : 'All Periods';
        return 'Unprocessed 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' => 'FFF2CC']
                ]
            ],
            // Style numeric columns
            'E:F' => [
                'numberFormat' => [
                    'formatCode' => '#,##0.00'
                ]
            ],
            'G' => [
                'numberFormat' => [
                    'formatCode' => '#,##0.00'
                ]
            ]
        ];
    }
}
