<?php

namespace App\Exports;

use App\Models\OBF;
use Illuminate\Support\Facades\DB;
use Illuminate\Validation\ValidationException;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Validators\Failure;

class ExportInsuranceReport implements FromCollection, WithHeadings, WithStyles, ShouldAutoSize
{
    protected $slug;
    protected $vin;
    protected $fromDate;
    protected $toDate;

    public function __construct($slug, $fromDate = null, $toDate = null) {
        $this->slug = $slug;
        $this->fromDate = $fromDate;
        $this->toDate = $toDate;
    }

    public function collection()
    {
        $data = OBF::select(
                'branches.name AS branch' ,
                'obf.customer_name',
                'car_model.name AS model', 
                'car_varient.name AS car_varient_name', 
                'inventory.vin_number',
                DB::raw("DATE_FORMAT(orders.invoice_date, '%d-%m-%Y') as invoice_date"),
                DB::raw("DATE_FORMAT(orders.invoice_date, '%b-%y') as invoice_month_year"), 
                'orders.invoice_no as invoice_no',
                'obf.ex_showroom_price',
                'obf.insurance_id',
                'insurance.name AS insurance_name',
                DB::raw("
                    CASE 
                        WHEN obf_insurance.insurance_exist = '1' THEN 'Inhouse'
                        WHEN obf_insurance.insurance_exist = '0' THEN 'Self'
                        ELSE ''
                    END as insurance_type
                "),
                'obf_insurance.policy_number',
                DB::raw("DATE_FORMAT(obf_insurance.date_of_insurance, '%d-%m-%Y') as date_of_insurance"),
                'obf_insurance.own_damage_premium',
                'obf_insurance.gross_premium_paid',
                DB::raw("
                    CASE 
                        WHEN obf_insurance.status = 'insurance_in_progress' THEN 'In-progress'
                        WHEN obf_insurance.status = 'insurance_completed' THEN 'Completed'
                        ELSE ''
                    END as status
                ")
            )
            ->leftJoin('products', 'obf.product_id', '=', 'products.id') 
            ->leftJoin('car_varient', 'car_varient.id', '=', 'products.veriant')
            ->leftJoin('car_model', 'car_model.id', '=', 'products.car_model')
            ->leftJoin('obf_insurance', 'obf.id', '=', 'obf_insurance.obf_id')
            ->leftjoin('branches' ,'obf.branch_id' ,'branches.id')
            ->leftjoin('orders' ,'orders.obf_id','obf.id')
            ->leftjoin('inventory' ,'obf.inventory_id' ,'inventory.id')
            ->leftjoin('insurance' ,'obf_insurance.company_name' ,'insurance.id')
            ->when($this->fromDate && $this->toDate, function ($q) {
                if ($this->slug === 'all') {
                    $q->where(function ($sub) {
                        $sub->whereBetween('orders.invoice_date', [
                                $this->fromDate->toDateTimeString(),
                                $this->toDate->toDateTimeString()
                            ])
                            ->orWhereBetween('orders.invoice_date', [
                                $this->fromDate->toDateTimeString(),
                                $this->toDate->toDateTimeString()
                            ]);
                    });
                }
                // INVOICE → only invoice_date
                elseif ($this->slug === 'invoice') {
                    $q->whereBetween('orders.invoice_date', [
                        $this->fromDate->toDateTimeString(),
                        $this->toDate->toDateTimeString()
                    ]);
                }
            })
            ->whereNotNull('obf_insurance.obf_id')
            ->groupBy('obf.id')
            ->orderBy('obf.id','desc')
            ->get();

        if ($data->isEmpty()) {
            throw new \ErrorException('No Data Found!');
        }
 
        return $data;
    }

    public function headings(): array
    {
        return [
            "Location",
            "Customer Name",
            "Model",
            "Variant",
            "Vin Number",
            "Invoice Date",
            "Invoice Date Month",
            "Invoice No.",
            "Ex. Sr. Price",
            "ERP Insurance Price",
            "Insurance Company Name",
            "Insurance Type",
            "Policy No.",
            "Date",
            "Net OD Premium",
            "Gross Premium Paid",
            "Insu. Dep. Status",
        ];
    }

    public function styles(Worksheet $sheet)
    {
        return [
            1 => ['font' => ['bold' => true]],
        ];
    }

    public function onFailure(Failure ...$failures)
    {
        $exception = ValidationException::withMessages(collect($failures)->map->toArray()->all());
        throw $exception;
    }
}
