<?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 ExportRSAReport 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.rsa_status',
                    'rsa.name AS rsa_name',
                    'obf.customer_name',
                    'rsa.amount AS rsa_amount',
                    'rsa_process_dms.rsa_invoice', 
                    'rsa_process_dms.rsa_invoice_date', 
                    'rsa_process_dms.dms_invoice_no',
                    'rsa_process_dms.dms_invoice_date', 
                    'rsa_process_dms.dms_gatepass_date', 
                    'rsa_process_dms.dms_location', 
                    'rsa_process_dms.dms_rsa_create_date', 
                    'rsa_process_dms.dms_rsa_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('rsa', 'obf.rsa_master_obf', '=', 'rsa.id')
                ->leftJoin('rsa_process_dms', 'obf.id', '=', 'rsa_process_dms.obf_id')
                ->leftjoin('users','users.id','=','obf.sales_person_id')
                ->leftjoin('Team_master','Team_master.id','users.team_name')
                ->whereNotNull('obf.rsa_status')
                ->whereNotNull('obf.rsa_master_obf')
                ->whereNotNull('rsa_process_dms.obf_id')
                // ->whereNotNull('rsa.name')   
                // ->where('rsa.name', '<>', '') 
                // ->groupBy('car_model.name', 'car_varient.name', 'obf.status', 'obf.rsa_status', 'rsa.name','rsa.amount','rsa_process_dms.rsa_invoice','rsa_process_dms.rsa_invoice_date','rsa_process_dms.dms_invoice_no','rsa_process_dms.dms_invoice_date','rsa_process_dms.dms_gatepass_date','rsa_process_dms.dms_location','rsa_process_dms.dms_rsa_create_date','rsa_process_dms.dms_rsa_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",
        "RSA Status",
        "RSA Plan",
        "Customer Name",
        "RSA Amount",
        "RSA Invoice",
        "RSA Invoice Date",
        "DMS Invoice No",
        "DMS Invoice Date",
        "DMS Gatepass Date",
        "DMS Location",
        "DMS RSA Create Date",
        "DMS RSA Certificate",
        "Sales Person",
        "Team Leader",];
    }

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