<?php

namespace App\Exports;

use DB;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStyles;
use Maatwebsite\Excel\Concerns\WithMapping;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;

class ExportBookingReport implements FromCollection, WithHeadings, WithStyles, ShouldAutoSize, WithMapping
{
    protected $ids;

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

    public function collection()
    {
        $data =  DB::table('obf')
            ->select(
                'obf.id',
                DB::raw("DATE_FORMAT(obf.booking_date, '%d-%m-%Y') as booking_date"),
                DB::raw("DATE_FORMAT(obf.booking_date, '%b-%y') as booking_month_year"),
                'branches.name as branch_name',
                'obf.customer_name',
                'obf.contact_number',
                'obf.gst',
                'obf.ship_to_customer_name',
                'Team_master.team_name',
                DB::raw("CONCAT(users.first_name,' ',users.last_name) AS sales_person_name"),
                'car_model.name as model',
                'car_varient.name as car_varient_name',
                'exteriorcolor.name as exterior_color',
                DB::raw('(SELECT vin_number FROM inventory WHERE inventory.status NOT IN ("inactive","deleted","sold") AND inventory.id = obf.inventory_id) as vin_number'),
                'pricelist_master.price_list_name',
                'pricelist_master.ex_showroom_price',
                'obf.on_road_price',
                DB::raw('cash_summary.booking_amount'),
                DB::raw('cash_summary.dp_amount'),
                'obf_finance.loan_amount',
                DB::raw('cash_summary.net_disbusment'),
                DB::raw('exchange.total_exchange_amount'),
                DB::raw('(COALESCE(cash_summary.booking_amount,0)+COALESCE(cash_summary.dp_amount,0)+COALESCE(cash_summary.net_disbusment,0)+COALESCE(exchange.total_exchange_amount,0)) as total_received'),
                DB::raw('(COALESCE(obf_finance.finance_discount,0)+COALESCE(obf.way_off,0)+COALESCE(obf.foc_accessories,0)+COALESCE(obf.offer_ib_amount,0)+COALESCE(obf.offer_kin_amount,0)) as total_discount'),
                DB::raw('(obf.on_road_price - (COALESCE(obf_finance.finance_discount,0)+COALESCE(obf.way_off,0)+COALESCE(obf.foc_accessories,0)+COALESCE(obf.offer_ib_amount,0)+COALESCE(obf.offer_kin_amount,0))) as receivable'),
                DB::raw('cash_summary.access_sort_amount'),
                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',
                DB::raw('(
                    obf.on_road_price -
                    (COALESCE(cash_summary.booking_amount,0)+COALESCE(cash_summary.dp_amount,0)+COALESCE(cash_summary.net_disbusment,0)+COALESCE(exchange.total_exchange_amount,0)+COALESCE(obf_finance.finance_discount,0)+COALESCE(obf.way_off,0)+COALESCE(obf.foc_accessories,0)+COALESCE(obf.offer_ib_amount,0)+COALESCE(obf.offer_kin_amount,0))
                ) as due_amount'),
                'obf.status as status',
                'obf.approval_status as sales_progress_status',
                'orders.status as order_status',
                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"),
                DB::raw("COALESCE(MAX(orders.invoice_date),'--') as invoice_date"),
                DB::raw('(SELECT MAX(status) FROM cash_receipt WHERE cash_receipt.obf_id = obf.id) as receipt_status'),
                'tex_master.gst as gst_master',
                'tex_master.compensation_cess as compensation_cess_master'
            )
            ->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('branches','branches.id','=','obf.branch_id')
            ->leftJoin('users','users.id','=','obf.sales_person_id')
            ->leftJoin('Team_master','Team_master.id','=','users.team_name')
            ->leftJoin('exteriorcolor','exteriorcolor.id','=','products.exterior_color')
            ->leftJoin('obf_finance','obf_finance.obf_id','=','obf.id')
            ->leftJoin('orders','orders.obf_id','=','obf.id')
            ->leftjoin('pricelist_master', 'pricelist_master.id', '=', 'obf.pricelist_id')
            ->leftJoinSub($this->cashSummary(), 'cash_summary', 'cash_summary.obf_id','=','obf.id')
            ->leftJoinSub($this->exchangeSummary(), 'exchange', 'exchange.obf_id','=','obf.id')
            ->leftJoin('tex_master','tex_master.id','car_varient.tex_master_id')
            // ✅ DORMANT
            ->when($this->slug === 'dormant', function ($q) {
                $date = Carbon::now()->subDays(45)->format('Y-m-d');
                $q->whereDate('obf.booking_date', '<=', $date)
                  ->whereNull('orders.invoice_no')
                  ->whereNull('orders.invoice_date');
            })
        
            // ✅ ACTIVE
            ->when($this->slug === 'active', function ($q) {
                $q->where(function ($sub) {
                    $sub->where('obf.status', '!=', 'refund_process')
                        ->orWhereNull('obf.status');
                });
            })
        
            // ✅ REFUND (FIXED ISSUE)
            ->when($this->slug === 'refund', function ($q) {
                $q->where('obf.status', '=', 'refund_process');
            })
            ->where('obf.status','!=','delete')
            ->groupBy('obf.id')
            ->orderByDesc('obf.id')
            ->get(); 
            
        if($data == null){
            throw new \ErrorException('No data found');
        }
        
        return $data;
    
    }

    public function map($row): array
    {
        $financeStatus = '-';
    
        if ($row->receipt_status == 'accepted' && $row->status == 'account_accepted' && $row->order_status == 'waiting') {
            $financeStatus = 'Order - ' . $row->order_status;
        } elseif ($row->status == 'account_accepted' && $row->order_status == 'finance_apporve') {
            $financeStatus = 'Finance Approved';
        } elseif ($row->status == 'account_accepted' && $row->order_status == 'disbursement') {
            $financeStatus = 'Disbursement';
        } elseif ($row->status == 'account_accepted' && $row->order_status == 'all_payment_done') {
            $financeStatus = 'All Payment Done';
        } elseif ($row->order_status == 'pdi_approve') {
            $financeStatus = 'PDI Approve';
        } elseif ($row->order_status == 'pdi_reject') {
            $financeStatus = 'PDI Reject';
        } elseif ($row->order_status == 'ready_to_deliver') {
            $financeStatus = 'Ready to Deliver';
        } elseif ($row->order_status == 'delivered') {
            $financeStatus = 'Delivered';
        } elseif ($row->order_status == 'invoice_create') {
            $financeStatus = 'Invoice Created';
        } elseif ($row->order_status == 'invoice_cancel') {
            $financeStatus = 'Invoice Cancelled';
        } elseif ($row->receipt_status == 'accepted' && $row->status == 'account_accepted') {
            $financeStatus = 'Receipt Created';
        } elseif ($row->status == 'accepted') {
            $financeStatus = 'Active';
        } elseif ($row->status == 'deleted') {
            $financeStatus = 'Deleted';
        } elseif ($row->status == 'account_rejected') {
            $financeStatus = 'Account Rejected';
        } elseif ($row->status == 'rejected') {
            $financeStatus = 'Rejected';
        } elseif ($row->status == 'account_accepted') {
            $financeStatus = 'Account Accepted';
        } elseif ($row->status == 'completed') {
            $financeStatus = 'Completed';
        } elseif ($row->status == 'cancel') {
            $financeStatus = 'Cancel';
        } elseif ($row->status == 'deal_generated') {
            $financeStatus = 'Deal Generated';
        } elseif ($row->status == 'refund_process') {
            $financeStatus = 'Refund Process';
        }
    
        // ðŸ”¹ Sales Progress
        $sales_progress_status = '-';
        if (!empty($row->sales_progress_status)) {
            $sales_progress_status = ucwords(str_replace('_', ' ', $row->sales_progress_status));
        }
    
        // ðŸ”¹ Offer Discount (A)
        $offerOutput = [];
        $approvalOutput = [];
        $total = 0;
    
        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(",\r\n", $offerOutput);
    
        // ðŸ”¹ Approval 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(",\r\n", $approvalOutput);
    
        // âœ… FINAL RETURN (FIXED ALIGNMENT ONLY â€” NO CODE REMOVED)
        return [
            $row->id,
            $row->booking_date,
            $row->booking_month_year,
            $row->branch_name,
            $row->customer_name,
            $row->contact_number,
            $row->gst,
            $row->ship_to_customer_name,
            $row->team_name,
            $row->sales_person_name,
            $row->model,
            $row->car_varient_name,
            $row->exterior_color,
            $row->vin_number,
            $row->price_list_name,
            $row->ex_showroom_price,
            $row->on_road_price,
    
            $row->booking_amount,
            $row->dp_amount,
            $row->loan_amount,
            $row->net_disbusment,
            $row->total_exchange_amount,
            $row->total_received,
    
            $row->total_discount,   // âœ… G (DB)
    
            $row->receivable,       // âœ… H
    
            $row->access_sort_amount,
    
            $offer_discount,        // âœ… X
            $approval_discount,     // âœ… Y
            $total,                 // âœ… Z
    
            $row->due_amount,
    
            $financeStatus,
            $sales_progress_status,
    
            $row->invoice_no,
            $row->invoice_date,
            $row->gst_master,
            $row->compensation_cess_master,
        ];
    }

    private function cashSummary()
    {
        return DB::table('cash_receipt')
            ->select(
                'obf_id',
                DB::raw('SUM(CASE WHEN receipt_type="booking_receipt" AND status!="rejected" THEN amount ELSE 0 END) AS booking_amount'),
                DB::raw('SUM(CASE WHEN receipt_type="downpayment_receipt" AND status!="rejected" THEN amount ELSE 0 END) AS dp_amount'),
                DB::raw('SUM(CASE WHEN receipt_type="disbusment_receipt" AND status!="rejected" THEN amount ELSE 0 END) AS net_disbusment'),
                DB::raw('SUM(CASE WHEN receipt_type="access_sort_receipt" AND status!="rejected" THEN amount ELSE 0 END) AS access_sort_amount')
            )
            ->groupBy('obf_id');
    }

    private function exchangeSummary()
    {
        return DB::table('exchange_purchase')
            ->select('obf_id', DB::raw('total_exchange_amount'))
            ->whereIn('status',['active','sell']);
    }

    public function headings(): array
    {
        return [
            'ID','Date','Month','Location','Customer Name (Bill To)','Customer Contact No.','GST No. (Bill To)', 
            'Customer Name (Ship To)','Team Leader','KEC Name','Model',
            'Variant','Exterior Color','VIN No.','Pricelist Name','Ex-Showroom Price (Pricelist)','On Road Price (A)',
            'Booking Amount (B(+))','DP Amount Received (C(+))',
            'Finance Amount','Net Disbusment Amount (D(+))',
            'Exchange Amount (E(+))','Total received amount (B+C+D+E = F)',
            'Total Discount (G(-))','Total Amount Receivable (A-G = H)',
            'Access / Sort Amount','Offer Discount (X)','Designation Wise Discount (Y)','Total Discount ( X + Y = Z )','Due Amount (H-F=J)',
            'Finance Progress Status','Sales Progress Status',
            'Invoice No.','Invoice Date','GST','Compensation Cess'
        ];
    }

    public function styles(Worksheet $sheet)
    {
        return [1 => ['font' => ['bold' => true]]];
    }
}
