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

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

    public function collection()
    {
        $latestInventory = DB::table('inventory as i1')
                ->select('i1.*')
                ->whereRaw('i1.id = (
                    SELECT MAX(i2.id)
                    FROM inventory as i2
                    WHERE i2.name = i1.name
                )');
        $data = OBF::select([
                DB::raw("ROW_NUMBER() OVER () AS No"),
                'car_model.name AS Model', 
                'car_varient.name AS Variant',
                'obf.customer_name',
                DB::raw("CONCAT(UCASE(LEFT(REPLACE(branches.name, '_', ' '), 1)),LOWER(SUBSTRING(REPLACE(branches.name, '_', ' '), 2))) AS branch_name"),
                'inventory.vin_number AS vin_number',
                'Team_master.team_name AS Team_Leader',
                DB::raw("CONCAT(users.first_name,' ',users.last_name) AS KEC_Name"),
                DB::raw("CASE WHEN orders.invoice_no IS NOT NULL AND orders.invoice_date IS NOT NULL THEN orders.invoice_no ELSE '---' END as invoice_no"),
                'orders.invoice_date as invoice_date',
                'finance.name AS Financer',
                DB::raw("CONCAT(UCASE(LEFT(REPLACE(obf_finance.finance_type, '_', ' '), 1)),LOWER(SUBSTRING(REPLACE(obf_finance.finance_type, '_', ' '), 2))) AS Finance_Type"),
                'obf_finance.repo_rate AS Repo_Rate',
                'obf_finance.loan_amount AS Loan_Amount',
                'obf_finance.net_disbusment AS Net_Disbursement_Amount',
                'obf_finance.processing_fee AS Processing_Fee',
                'obf_finance.finance_discount AS Finance_Discount',
                'obf_finance.payout AS Payout_Percentage',
                'obf_finance.payout_amount AS Payout_Amount',
                'obf_finance.lumsum_amount AS Lumsum_Amount',
                'obf_finance.pay_booster AS Pay_Booster',
                'obf_finance.booster_percentage As Booster_Percentage',
                'obf_finance.total_payout_amount AS Total_Payout'
            ])
            ->leftJoin('products', 'obf.product_id', '=', 'products.id')
            ->leftJoinSub($latestInventory, 'inventory', function ($join) {
                $join->on('inventory.name', '=', 'products.id');
            })
            ->leftJoin('branches', 'obf.branch_id', '=', 'branches.id')
            ->leftJoin('car_varient', 'car_varient.id', '=', 'products.veriant')
            ->leftJoin('car_model', 'car_model.id', '=', 'products.car_model')
            ->leftJoin('obf_finance', 'obf.id', '=', 'obf_finance.obf_id')
            ->leftJoin('finance', 'obf_finance.finance_name', '=', 'finance.id')
            ->leftJoin('users','users.id','=','obf.sales_person_id')
            ->leftJoin('Team_master','Team_master.id','=','users.team_name')
            ->leftJoin('orders','orders.obf_id','=','obf.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()
                            ]);
                    });
                }
                
                // INVOICE → only invoice_date
                elseif ($this->slug === 'invoice') {
                    $q->whereBetween('orders.invoice_date', [
                        $this->fromDate->toDateTimeString(),
                        $this->toDate->toDateTimeString()
                    ]);
                }
            })
            
            ->where('obf_finance.finance_required', 'Yes')
            ->whereNotNull('obf_finance.obf_id')
    
            // remove deleted
            ->whereNotIn('obf.status', ['deleted','delete'])
    
            // only invoice created data
            ->whereNotNull('orders.invoice_no')
            ->where('orders.invoice_no','!=','')
    
            ->groupBy('obf.id')
            ->orderBy('finance.name','asc')
            ->get();

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

        return $data;
    }

    public function headings(): array
    {
        return [
            "No",
            "Model", 
            "Variant", 
            "Customer Name",
            "Location",
            "VIN Number",
            "Team Leader", 
            "KEC Name",
            "Invoice No",
            "Invoice Date",
            "Financer",
            "Finance Type",
            "Repo Rate",
            "Loan Amount",
            "Net Disbursement Amount",
            "Processing Fee",
            "Finance Discount",
            "Payout Percentage",
            "Payout Amount",
            "Lumsum Amount",
            "Pay Booster",
            "Booster Percentage",
            "Total Payout"
        ];
    }

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