<?php

namespace App\Exports;

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

class ExportDayBookReport implements FromCollection, WithHeadings, WithStyles, ShouldAutoSize,WithMapping{
    function __construct($slug, $fromDate = null, $toDate = null) {
        $this->slug = $slug;
        $this->fromDate = $fromDate;
        $this->toDate = $toDate;
    }


    public function collection() {
        ini_set('memory_limit', '1024M');
        ini_set('max_execution_time', 300);
    
        $data = OBF::from('obf as obf')->select(
            // === your original select unchanged ===
            'branches.name AS location',
            DB::raw("CONCAT(us.first_name,' ',us.last_name) AS kec_name"),
            'Team_master.team_name as tl_name',
            DB::raw("DATE_FORMAT(orders.invoice_date, '%b-%y') as booking_month_year"),    
            DB::raw("DATE_FORMAT(obf.booking_date, '%Y-%M-%d') as booking_date"),
            'pricelist_master.price_list_name',
            'obf.customer_name',
            'obf.company_name',
            'obf.registration',
            'district.name as district_name',
            'common_state.name as state_name',
            'obf.einvoicing_applicable',
            'obf.gst',
            'obf.ship_to_customer_name',
            'obf.ship_to_address',
            'obf.customer_type as rto_type',
            'customer_type_master.name as customer_type_master',
            'obf.contact_number',
            'obf.pan_number',
            'obf.adhar_number',
            'obf.aadhar_pan_link',
            DB::raw("DATE_FORMAT(orders.invoice_date, '%Y-%M-%d') as ibs_invoice_date"),
            'orders.invoice_no as ibs_invoice_no',
            'car_model.name AS model', 
            'car_varient.name AS varient', 
            'fule_type.title as fuel_type',
            'inventory.vin_number',
            'inventory.engine_number',
            DB::raw("CASE WHEN pricelist_master.is_csd = 'No' THEN pricelist_master.ex_showroom_price WHEN pricelist_master.is_csd = 'Yes' THEN (pricelist_master.basic + pricelist_master.gst_amount) END AS ex_showroom_price"),
            // DB::raw('(COALESCE(pricelist_master.ex_showroom_price, 0) - (COALESCE(make_deal.deal_amt, 0) + COALESCE(make_deal.gst_tax_deal, 0) + COALESCE(make_deal.cess_tax_deal, 0))) as discount_with_gst'),
            DB::raw('GREATEST(0,(COALESCE(pricelist_master.ex_showroom_price, 0) - (COALESCE(make_deal.deal_amt, 0) + COALESCE(make_deal.gst_tax_deal, 0) + COALESCE(make_deal.cess_tax_deal, 0)))) as discount_with_gst'),
            DB::raw('(COALESCE(make_deal.deal_amt, 0) + COALESCE(make_deal.gst_tax_deal, 0) + COALESCE(make_deal.cess_tax_deal, 0)) as after_dis_ex_showroom_price'),
            'make_deal.deal_amt as basic',
            DB::raw('make_deal.gst_tax_deal / 2 as cgst'),
            DB::raw('make_deal.gst_tax_deal / 2 as sgst'),
            DB::raw('make_deal.gst_tax_deal as igst'),
            'make_deal.cess_tax_deal as cess',
            DB::raw('(COALESCE(make_deal.deal_amt, 0) + COALESCE(make_deal.gst_tax_deal, 0) + COALESCE(make_deal.cess_tax_deal, 0)) as total_after_make_deal'),
            'make_deal.tcs_tax_deal as tcs',
            'make_deal.rto_deal as rto',
            'obf_rto.vehicle_number',
            'make_deal.rto_cutom_handling_charges_deal as rto_custom_handling_charges',
            'make_deal.municipal_tax_deal as municipal_tax',
            'make_deal.insurance_deal as insurance',
            'insurance.type AS insurance_type',
            'insurance.name AS insurance_name',
            DB::raw("CASE WHEN rsa.rsa_ccs = 'RSA' THEN rsa.amount ELSE 0 END as rsa"),
            DB::raw("CASE WHEN ccs.rsa_ccs = 'CCS' THEN ccs.amount ELSE 0 END as ccs"),
            'extand_warranties.amount as ew',
            'mcp.amount as mcp',
            'fasttags.amount as fastag',
            DB::raw("(IFNULL((SELECT SUM(accessories.price) FROM accessories WHERE FIND_IN_SET(accessories.id, obf.accessory_id)), 0) + IFNULL(obf.other_accesssories_prise, 0)) as accessory_amount"),
            DB::raw("(
                COALESCE(make_deal.deal_amt, 0) +
                COALESCE(make_deal.gst_tax_deal, 0) +
                COALESCE(make_deal.cess_tax_deal, 0) +
                COALESCE(make_deal.tcs_tax_deal, 0) +
                COALESCE(make_deal.rto_deal, 0) +
                COALESCE(make_deal.rto_cutom_handling_charges_deal, 0) +
                COALESCE(make_deal.municipal_tax_deal, 0) +
                COALESCE(make_deal.insurance_deal, 0) +
                COALESCE(CASE WHEN rsa.rsa_ccs = 'RSA' THEN rsa.amount ELSE 0 END, 0) +
                COALESCE(CASE WHEN ccs.rsa_ccs = 'CCS' THEN ccs.amount ELSE 0 END, 0) +
                COALESCE(extand_warranties.amount, 0) +
                COALESCE(mcp.amount, 0) +
                COALESCE(fasttags.amount, 0) +
                COALESCE((IFNULL((SELECT SUM(accessories.price) FROM accessories WHERE FIND_IN_SET(accessories.id, obf.accessory_id)), 0) + IFNULL(obf.other_accesssories_prise, 0)), 0)
            ) as total"),
            DB::raw('cr.booking_amount as booking_amount'),
            DB::raw('cr.dp_amount as dp_amount'),
            'finance.name AS financer_name',
            'obf_finance.finance_type',
            'obf_rto.vehicle_number',
            DB::raw('cr.finance_discount as finance_discount'),
            DB::raw('ex.total_exchange_amount as exchange_amount'),
            DB::raw("CONCAT(ex.company_name, ' - ', ex.variant) AS exchange_name"),
            'ex.registration_number',
            DB::raw('cr.access_sort_amount as access_sort_amount'),
            DB::raw('cr.other_amount as other_amount'),
            DB::raw('(cr.booking_amount + cr.dp_amount + cr.finance_discount + COALESCE(ex.total_exchange_amount,0) + cr.access_sort_amount + cr.other_amount) as total_amt_received'),
            DB::raw("(
                COALESCE(make_deal.deal_amt, 0) +
                COALESCE(make_deal.gst_tax_deal, 0) +
                COALESCE(make_deal.cess_tax_deal, 0) +
                COALESCE(make_deal.tcs_tax_deal, 0) +
                COALESCE(make_deal.rto_deal, 0) +
                COALESCE(make_deal.rto_cutom_handling_charges_deal, 0) +
                COALESCE(make_deal.municipal_tax_deal, 0) +
                COALESCE(make_deal.insurance_deal, 0) +
                COALESCE(CASE WHEN rsa.rsa_ccs = 'RSA' THEN rsa.amount ELSE 0 END, 0) +
                COALESCE(CASE WHEN ccs.rsa_ccs = 'CCS' THEN ccs.amount ELSE 0 END, 0) +
                COALESCE(extand_warranties.amount, 0) +
                COALESCE(mcp.amount, 0) +
                COALESCE(fasttags.amount, 0) +
                COALESCE((IFNULL((SELECT SUM(accessories.price) FROM accessories WHERE FIND_IN_SET(accessories.id, obf.accessory_id)),0) + IFNULL(obf.other_accesssories_prise, 0)),0)
            ) - (cr.booking_amount + cr.dp_amount + cr.finance_discount + COALESCE(ex.total_exchange_amount,0) + cr.access_sort_amount + cr.other_amount) AS refund_amount"),
            DB::raw("0 as refund_amt_process"),
            DB::raw("0 as refund_amt_date"),
            DB::raw("0 as refund_diff"),
            DB::raw("0 as date"),
            DB::raw("0 as payment_ref_no"),
            'obf.on_road_price',
            DB::raw("(
                COALESCE(make_deal.deal_amt, 0) +
                COALESCE(make_deal.gst_tax_deal, 0) +
                COALESCE(make_deal.cess_tax_deal, 0) +
                COALESCE(make_deal.tcs_tax_deal, 0) +
                COALESCE(make_deal.rto_deal, 0) +
                COALESCE(make_deal.rto_cutom_handling_charges_deal, 0) +
                COALESCE(make_deal.municipal_tax_deal, 0) +
                COALESCE(make_deal.insurance_deal, 0) +
                COALESCE(CASE WHEN rsa.rsa_ccs = 'RSA' THEN rsa.amount ELSE 0 END, 0) +
                COALESCE(CASE WHEN ccs.rsa_ccs = 'CCS' THEN ccs.amount ELSE 0 END, 0) +
                COALESCE(extand_warranties.amount, 0) +
                COALESCE(mcp.amount, 0) +
                COALESCE(fasttags.amount, 0) +
                COALESCE((IFNULL((SELECT SUM(accessories.price) FROM accessories WHERE FIND_IN_SET(accessories.id, obf.accessory_id)),0) + IFNULL(obf.other_accesssories_prise, 0)),0)
            ) as make_deal"),
            DB::raw("(obf.on_road_price - (
                COALESCE(make_deal.deal_amt, 0) +
                COALESCE(make_deal.gst_tax_deal, 0) +
                COALESCE(make_deal.cess_tax_deal, 0) +
                COALESCE(make_deal.tcs_tax_deal, 0) +
                COALESCE(make_deal.rto_deal, 0) +
                COALESCE(make_deal.rto_cutom_handling_charges_deal, 0) +
                COALESCE(make_deal.municipal_tax_deal, 0) +
                COALESCE(make_deal.insurance_deal, 0) +
                COALESCE(CASE WHEN rsa.rsa_ccs = 'RSA' THEN rsa.amount ELSE 0 END, 0) +
                COALESCE(CASE WHEN ccs.rsa_ccs = 'CCS' THEN ccs.amount ELSE 0 END, 0) +
                COALESCE(extand_warranties.amount, 0) +
                COALESCE(mcp.amount, 0) +
                COALESCE(fasttags.amount, 0) +
                COALESCE((IFNULL((SELECT SUM(accessories.price) FROM accessories WHERE FIND_IN_SET(accessories.id, obf.accessory_id)),0) + IFNULL(obf.other_accesssories_prise, 0)),0)
            )) as on_road_discount"),
            DB::raw("(SELECT GROUP_CONCAT(title SEPARATOR ', ')
                      FROM offer 
                      WHERE FIND_IN_SET(offer.id, obf.offer_kin)
                     ) as kin_offer_titles"),
            
            DB::raw("(SELECT GROUP_CONCAT(amount SEPARATOR ', ')
                      FROM offer 
                      WHERE FIND_IN_SET(offer.id, obf.offer_kin)
                     ) as kin_offer_amounts"),
            
            DB::raw("(SELECT GROUP_CONCAT(title SEPARATOR ', ')
                      FROM offer 
                      WHERE FIND_IN_SET(offer.id, obf.offer_ib)
                     ) as ib_offer_titles"),
            
            DB::raw("(SELECT GROUP_CONCAT(amount SEPARATOR ', ')
                      FROM offer 
                      WHERE FIND_IN_SET(offer.id, obf.offer_ib)
                     ) as ib_offer_amounts"),
            
            'obf.way_off as kec_discount',
            'obf.tl_discount',
            'obf.sm_discount',
            'obf.gm_discount',
            'obf.bh_discount',
            'obf.ceo_discount',
            'obf.md_discount',
            'obf.step1_remarks as remarks',
            'inventory.kin_invoice_no',
            'inventory.kin_invoice_date',
            'inventory.purchase_price',
            'inventory.manufacturing_year',
            DB::raw("DATEDIFF(orders.invoice_date, obf.booking_date) as tat_days"),
            DB::raw("
                CASE 
                    WHEN orders.invoice_date IS NULL THEN 'Pending Invoice'
            
                    WHEN DATEDIFF(orders.invoice_date, obf.booking_date) BETWEEN 0 AND 10 
                        THEN 'Within TAT (10 Days)'
            
                    WHEN DATEDIFF(orders.invoice_date, obf.booking_date) BETWEEN 11 AND 15 
                        THEN 'Delay (11-15 Days)'
            
                    WHEN DATEDIFF(orders.invoice_date, obf.booking_date) > 15 
                        THEN 'Delay (Above 15 Days)'
            
                    ELSE 'Invalid Date'
                END as tat_status
            "),
        )
        // === your original joins unchanged ===
        ->leftJoin('branches', 'obf.branch_id', '=', 'branches.id')
        ->leftJoin('users as us', 'us.id', 'obf.sales_person_id')
        ->leftJoin('Team_master', 'Team_master.id', 'us.team_name')
        ->leftJoin('district', 'district.id', 'obf.district_id')
        ->leftJoin('common_state', 'common_state.id', 'obf.state_id')
        ->leftJoin('customer_type_master', 'obf.customer_type_new', 'customer_type_master.id')
        ->leftJoin('orders', 'orders.obf_id', '=', 'obf.id')
        ->leftJoin('products', 'obf.product_id', '=', 'products.id')
        ->leftJoin('car_model', 'products.car_model', '=', 'car_model.id')
        ->leftJoin('car_varient', 'car_varient.id', '=', 'products.veriant')
        ->leftJoin('inventory', 'inventory.id', '=', 'obf.inventory_id')
        ->leftJoin('obf_finance', 'obf.id', '=', 'obf_finance.obf_id')
        ->leftJoin('finance', 'obf_finance.finance_name', '=', 'finance.id')
        ->leftJoin('make_deal', 'obf.id', '=', 'make_deal.obf_id')
        ->leftJoin('obf_insurance', 'obf.id', '=', 'obf_insurance.obf_id')
        ->leftJoin('insurance','obf_insurance.company_name', '=', 'insurance.id')
        ->leftJoin('rsa as rsa', 'obf.rsa_master_obf', '=', 'rsa.id')
        ->leftJoin('rsa as ccs', 'obf.ccs_master_obf', '=', 'ccs.id')
        ->leftJoin('extand_warranties', 'obf.extanded_warranty_id', '=', 'extand_warranties.id')
        ->leftJoin('mcp', 'obf.convinience_program', '=', 'mcp.id')
        ->leftJoin('fasttags', 'obf.fasttag_brand_name', '=', 'fasttags.id')
        ->leftjoin('pricelist_master', 'obf.pricelist_id', 'pricelist_master.id')
        ->leftJoin('fule_type', 'fule_type.id', '=', 'obf.fuel_type')
        ->leftJoin(DB::raw('(SELECT obf_id, 
                SUM(CASE WHEN receipt_type = "booking_receipt" AND status != "rejected" THEN amount ELSE 0 END) as booking_amount,
                SUM(CASE WHEN receipt_type = "downpayment_receipt" AND status != "rejected" THEN amount ELSE 0 END) as dp_amount,
                SUM(CASE WHEN receipt_type = "disbusment_receipt" AND status != "rejected" THEN amount ELSE 0 END) as finance_discount,
                SUM(CASE WHEN receipt_type = "access_sort_receipt" AND status != "rejected" THEN amount ELSE 0 END) as access_sort_amount,
                SUM(CASE WHEN receipt_type = "other_receipt" AND status != "rejected" THEN amount ELSE 0 END) as other_amount
            FROM cash_receipt GROUP BY obf_id) as cr'), 'cr.obf_id', '=', 'obf.id')
        ->leftJoin(DB::raw('(SELECT obf_id, company_name, variant, registration_number, total_exchange_amount
            FROM exchange_purchase WHERE status IN ("active", "sell")) as ex'), 'ex.obf_id', '=', 'obf.id')
        ->leftjoin('obf_rto','obf.id','obf_rto.obf_id')
        ->when($this->fromDate && $this->toDate, function ($q) {
            // $q->whereBetween('obf.booking_date', [
            //     $this->fromDate->toDateTimeString(),
            //     $this->toDate->toDateTimeString()
            // ]);
            if ($this->slug === 'all') {
                $q->where(function ($sub) {
                    $sub->whereBetween('obf.booking_date', [
                            $this->fromDate->toDateTimeString(),
                            $this->toDate->toDateTimeString()
                        ])
                        ->orWhereBetween('orders.invoice_date', [
                            $this->fromDate->toDateTimeString(),
                            $this->toDate->toDateTimeString()
                        ]);
                });
            }
            
            // BOOKING → only booking_date
            elseif ($this->slug === 'booking') {
                $q->whereBetween('obf.booking_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('orders.invoice_no')
        ->groupBy('obf.id')
        ->orderBy('obf.id', 'DESC')
        ->get();
        
        $data = $data->map(function ($row) {

            $offerOutput = [];
            $approvalOutput = [];
            $total = 0;

            /* Offer Discount (A) */

            if(!empty($row->kin_offer_titles)){
                $titles  = explode(',', $row->kin_offer_titles);
                $amounts = explode(',', $row->kin_offer_amounts);

                foreach($titles as $key => $title){
                    $amount = $amounts[$key] ?? 0;
                    $offerOutput[] = $title.' - '.$amount;
                    $total += (float)$amount;
                }
            }

            if(!empty($row->ib_offer_titles)){
                $titles  = explode(',', $row->ib_offer_titles);
                $amounts = explode(',', $row->ib_offer_amounts);

                foreach($titles as $key => $title){
                    $amount = $amounts[$key] ?? 0;
                    $offerOutput[] = $title.' - '.$amount;
                    $total += (float)$amount;
                }
            }

            $offer_discount = implode(",\n", $offerOutput);

            /* Designation Discount (B) */

            if(!empty($row->kec_discount)){
                $approvalOutput[] = 'kec - '.$row->kec_discount;
                $total += $row->kec_discount;
            }

            if(!empty($row->tl_discount)){
                $approvalOutput[] = 'tl - '.$row->tl_discount;
                $total += $row->tl_discount;
            }

            if(!empty($row->sm_discount)){
                $approvalOutput[] = 'sm - '.$row->sm_discount;
                $total += $row->sm_discount;
            }

            if(!empty($row->gm_discount)){
                $approvalOutput[] = 'gm - '.$row->gm_discount;
                $total += $row->gm_discount;
            }

            if(!empty($row->bh_discount)){
                $approvalOutput[] = 'bh - '.$row->bh_discount;
                $total += $row->bh_discount;
            }

            if(!empty($row->ceo_discount)){
                $approvalOutput[] = 'ceo - '.$row->ceo_discount;
                $total += $row->ceo_discount;
            }

            if(!empty($row->md_discount)){
                $approvalOutput[] = 'md - '.$row->md_discount;
                $total += $row->md_discount;
            }

            $approval_discount = implode(",\n", $approvalOutput);

            $row->offer_discount = $offer_discount;
            $row->approval_discount = $approval_discount;
            $row->total_discount = $total;

            return $row;
        });
    
        if ($data->isEmpty()) {
            throw new \Exception('No Data Found!');
        }
    
        return $data;
    }
    
    public function map($row): array
    {
        return [
            $row->location,
            $row->kec_name,
            $row->tl_name,
            $row->booking_month_year,
            $row->booking_date,
            $row->price_list_name,
            $row->customer_name,
            $row->company_name,
            $row->registration,
            $row->district_name,
            $row->state_name,
            $row->einvoicing_applicable,
            $row->gst,
            $row->ship_to_customer_name,
            $row->ship_to_address,
            $row->rto_type,
            $row->customer_type_master,
            $row->contact_number,
            $row->pan_number,
            $row->adhar_number,
            $row->aadhar_pan_link,
            $row->ibs_invoice_date,
            $row->ibs_invoice_no,
            $row->model,
            $row->varient,
            $row->fuel_type,
            $row->vin_number,
            $row->engine_number,
            $row->ex_showroom_price,
            $row->discount_with_gst,
            $row->after_dis_ex_showroom_price,
            $row->basic,
            $row->cgst,
            $row->sgst,
            $row->igst,
            $row->cess,
            $row->total_after_make_deal,
            $row->tcs,
            $row->rto,
            $row->vehicle_number,
            $row->rto_custom_handling_charges,
            $row->municipal_tax,
            $row->insurance,
            $row->insurance_type,
            $row->insurance_name,
            $row->rsa,
            $row->ccs,
            $row->ew,
            $row->mcp,
            $row->fastag,
            $row->accessory_amount,
            $row->total,
            $row->booking_amount,
            $row->dp_amount,
            $row->financer_name,
            $row->finance_type,
            $row->finance_discount,
            $row->exchange_amount,
            $row->exchange_name,
            $row->registration_number,
            $row->access_sort_amount,
            $row->other_amount,
            $row->total_amt_received,
            $row->refund_amount,
            $row->refund_amt_process,
            $row->refund_amt_date,
            $row->refund_diff,
            $row->date,
            $row->payment_ref_no,
            $row->on_road_price,
            $row->make_deal,
            $row->on_road_discount,
    
            // ✅ YOUR NEW COLUMNS
            $row->offer_discount,
            $row->approval_discount,
            $row->total_discount,
    
            $row->remarks,
            $row->kin_invoice_no,
            $row->kin_invoice_date,
            $row->purchase_price,
            $row->manufacturing_year,
            $row->tat_days,
            $row->tat_status
        ];
    }

    
    public function headings() :array{
            return ['Location','KEC Name','TL Name','Month','Booking Date','Pricelist (WEF)','Bill To : Customer Name','Bill To : Company Name','Bill To : Address: Registration','District','State','e-invoice Applicable','GST No.','Ship To : Customer Name','Address','RTO Type','Customer Type','Mobile No.','Pan No.','Aadhar No.','Aadhar and Pan Link Status','IBS Invoice Date','IBS Invoice No.','Model','Variant','Fuel Type','VIN Number','Engine No.','Ex Showroom Price','Discount with GST','After Disc Ex. Sr Price','Basic','CGST','SGST','IGST','CESS','Total','TCS','RTO','RTO Registration No.','RTO Custom Handling Charges','Municipal Tax','Insurance','Insurance Type','Insurance Company Name','RSA','CCS','EW','MCP','Fasttag','Accessories','Total','Booking Amount','Dowan payment','Financer Name','Fin. IN House/DSA/Self','Finance Disbusment','Exchange','Model / Variant','Registration No.','Excess Shrot','Other','Total Amount Received','Due Amount/Refund Amt','Refund Amount Process','Refund Amt. Date','Difference','Date','Payment Ref No.','On Road Price','Make Deal','On Road Discount','Offer Discount (A)','Designation Wise Discount (B)','Total Discount ( A + B = C )','Remarks','KIN Invocie no.','KIN Pur. Date','Purchase Basic','MFG Year','TAT Days','TAT Status'];
    }

    public function styles(Worksheet $sheet){
        $sheet->getStyle('A:ZZ')->getAlignment()->setWrapText(true);
        return [1 => ['font' => ['bold' => true]]];
    }
}