<?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 ExportEWReport implements FromCollection, WithHeadings, WithStyles, ShouldAutoSize
{
    protected $slug;
    protected $vin;

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

    public function collection()
    {
        $data = OBF::select(
                    'car_model.name AS product', 
                    'car_varient.name AS car_varient_name', 
                    DB::raw("CONCAT(UCASE(LEFT(REPLACE(obf.status, '_', ' '), 1)), 
                    LOWER(SUBSTRING(REPLACE(obf.status, '_', ' '), 2))) AS status"),
                    'obf.ew_status',
                    'obf.customer_name',
                    'extand_warranties.years AS ew_years',
                    'extand_warranties.amount AS extand_warranties_amount',
                    'ew_process_dms.ew_invoice', 
                    'ew_process_dms.ew_invoice_date', 
                    'ew_process_dms.dms_invoice_no',
                    'ew_process_dms.dms_invoice_date', 
                    'ew_process_dms.dms_gatepass_date', 
                    'ew_process_dms.dms_location', 
                    'ew_process_dms.dms_ew_create_date', 
                    'ew_process_dms.dms_ew_certificate',
                    DB::raw("CONCAT(users.first_name,' ',users.last_name) AS sales_person_name"),
                    'Team_master.team_name'
                )
                ->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('extand_warranties', 'obf.extanded_warranty_id', '=', 'extand_warranties.id')
                ->leftJoin('ew_process_dms', 'obf.id', '=', 'ew_process_dms.obf_id')
                ->leftjoin('users','users.id','=','obf.sales_person_id')
                ->leftjoin('Team_master','Team_master.id','users.team_name')
                ->whereNotNull('obf.ew_status')
                ->whereNotNull('extand_warranties.years')
                ->groupBy(
                    'car_model.name',
                    'car_varient.name',
                    'obf.status',
                    'obf.ew_status',
                    'obf.customer_name',
                    'extand_warranties.years',
                    'extand_warranties.amount',
                    'ew_process_dms.ew_invoice',
                    'ew_process_dms.ew_invoice_date',
                    'ew_process_dms.dms_invoice_no',
                    'ew_process_dms.dms_invoice_date',
                    'ew_process_dms.dms_gatepass_date',
                    'ew_process_dms.dms_location',
                    'ew_process_dms.dms_ew_create_date',
                    'ew_process_dms.dms_ew_certificate'
                )
                ->when($this->vin, function ($query) {
                    return $query->where('obf.vin', 'like', "%{$this->vin}%"); 
                })
                ->get();

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

        return $data;
    }

    public function headings(): array
    {
        return ["Model",
                "Variant",
                "Status",
                "EW Status",
                "Customer Name",
                "EW Plan",
                "EW Amount",
                "EW Invoice",
                "EW Invoice Date",
                "DMS Status",
                "DMS Invoice No",
                "DMS Invoice Date",
                "DMS Gatepass Date",
                "DMS Location",
                "DMS EW Create Date",
                "Team Leader",
                "KEC Name",
                "DMS EW Certificate"
            ];
    }

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