<?php

namespace App\Exports;

use App\Models\OBF;
use App\Models\Order;
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 DB;

class ExportDeliverReport implements FromCollection, WithHeadings,WithStyles ,ShouldAutoSize
{
    /**
    * @return \Illuminate\Support\Collection
    */
    function __construct($slug) {
        $this->slug = $slug;
    }
    public function collection()
    {
        $data = Order::select(
            'orders.id',
            DB::raw("
                CASE 
                    WHEN orders.obf_id IS NOT NULL THEN obf.customer_name
                    WHEN orders.dealer_id IS NOT NULL THEN dealer_management.dealer_name
                    ELSE NULL
                END as customer_name
            "),
            'branches.name as branch',
            DB::raw("CONCAT(users.first_name,' ',users.last_name) AS sales_person_name"),
            DB::raw("CASE WHEN orders.inventory_id IS NOT NULL THEN car_model.name ELSE NULL END as model"),
            DB::raw("CASE WHEN orders.inventory_id IS NOT NULL THEN car_varient.name ELSE NULL END as car_varient_name"),
            'orders.vin_number',
            'orders.engine_number',
            DB::raw("CASE WHEN orders.obf_id IS NOT NULL THEN obf.on_road_price ELSE NULL END as on_road_price"),
            DB::raw("
                CASE 
                    WHEN orders.obf_id IS NOT NULL THEN (
                        IFNULL(obf_finance.finance_discount, 0) +
                        IFNULL(obf.way_off, 0) +
                        IFNULL(obf.foc_accessories, 0) +
                        IFNULL(obf.offer_ib_amount, 0) +
                        IFNULL(obf.offer_kin_amount, 0)
                    )
                    ELSE NULL
                END as total_discount
            "),
            DB::raw("
                CASE 
                    WHEN orders.obf_id IS NOT NULL THEN (
                        SELECT ROUND(SUM(cr.amount)) 
                        FROM cash_receipt cr 
                        WHERE cr.obf_id = obf.id
                    )
                    ELSE NULL
                END as total_received_amount
            "),
            DB::raw("
                CASE 
                    WHEN orders.obf_id IS NOT NULL THEN (
                        obf.on_road_price 
                        - IFNULL((SELECT SUM(cr.amount) FROM cash_receipt cr WHERE cr.obf_id = obf.id), 0)
                        - (
                            IFNULL(obf_finance.finance_discount, 0) +
                            IFNULL(obf.way_off, 0) +
                            IFNULL(obf.foc_accessories, 0) +
                            IFNULL(obf.offer_ib_amount, 0) +
                            IFNULL(obf.offer_kin_amount, 0)
                        )
                    )
                    ELSE NULL
                END as due_amount
            "),
            'orders.invoice_no',
            DB::raw("DATE_FORMAT(orders.invoice_date, '%d-%b-%Y') as invoice_date"),
            DB::raw("
                CONCAT(
                    UCASE(LEFT(REPLACE(orders.status, '_', ' '), 1)),
                    LOWER(SUBSTRING(REPLACE(orders.status, '_', ' '), 2))
                ) AS invoice_status
            "),
            DB::raw("
                CASE 
                    WHEN orders.obf_id IS NOT NULL THEN 
                        CONCAT(UCASE(LEFT(REPLACE(obf.insurance_status, '_', ' '), 1)),
                        LOWER(SUBSTRING(REPLACE(obf.insurance_status, '_', ' '), 2)))
                    ELSE NULL
                END as insurance_status
            "),
            DB::raw("
                CASE 
                    WHEN orders.obf_id IS NOT NULL THEN 
                        CONCAT(UCASE(LEFT(REPLACE(obf.rto_status, '_', ' '), 1)),
                        LOWER(SUBSTRING(REPLACE(obf.rto_status, '_', ' '), 2)))
                    ELSE NULL
                END as rto_status
            "),
            DB::raw("
                CASE 
                    WHEN orders.obf_id IS NOT NULL THEN 
                        CONCAT(UCASE(LEFT(REPLACE(obf.municipal_tax_status, '_', ' '), 1)),
                        LOWER(SUBSTRING(REPLACE(obf.municipal_tax_status, '_', ' '), 2)))
                    ELSE NULL
                END as municipal_status
            ")
        )
        ->leftJoin('obf','obf.id','=','orders.obf_id')
        ->leftJoin('branches','branches.id','=','orders.branch_id')
        ->leftJoin('inventory','inventory.id','=','orders.inventory_id')
        ->leftJoin('products', 'inventory.name', '=', 'products.id')
        ->leftJoin('car_varient', 'car_varient.id', '=', 'products.veriant')
        ->leftJoin('car_model', 'car_model.id', '=', 'products.car_model')
        ->leftJoin('users', function($join){
            $join->on('users.id','=','obf.sales_person_id');
        })
        ->leftJoin('obf_finance','obf_finance.obf_id','=','obf.id')
        ->leftJoin('dealer_management','dealer_management.id','=','orders.dealer_id')

        // ✅ Ensure both OBF + Dealer included
        ->where(function($q){
            $q->whereNotNull('orders.obf_id')
              ->orWhereNotNull('orders.dealer_id');
        })

        ->where('orders.status', 'delivered')
        ->whereDate('orders.invoice_date', '>=', '2026-01-01') 
        ->get();
    
        if ($data->isEmpty()) {
            throw new \ErrorException('No Data Found!');
        }
        
        return $data;
    }

    public function headings() :array{
        return ["No" , "Customer Name" ,"Branch" ,"KEC", "Model", "Varient", "VIN Number","Engine Number" ,"On Road Price" ,"Less: Discount", "Received Amount" ,"Due Amount","Invoice No" ,"Invoice Date" ,"Invoice Status", "Insurance Status", "RTO Status", "Municipal Status"];
    }

    public function styles(Worksheet $sheet)
    {
        return [
            // Style the first row as bold text.
            1    => ['font' => ['bold' => true]],
        ];
    }
}