<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\Accessory;
use App\Models\Allocation; 
use App\Models\Approval;
use App\Models\Branch;
use App\Models\CarExchange; 
use App\Models\ExtandWarranty;
use App\Models\Fasttag;
use App\Models\Finance;
use App\Models\Insurance;
use App\Models\Lead;
use App\Models\Sublead;
use App\Models\OBF;
use App\Models\User;
use App\Models\Product;
use App\Models\Carmodel;
use App\Models\Interiorcolor;
use App\Models\Exteriorcolor;
use App\Models\Carvarient;
use App\Models\pricelist;
use App\Models\Mcp;
use App\Models\Tax;
use App\Models\OtherOne;
use App\Models\OtherTwo;
use App\Models\Inventory;
use App\Models\CashReceipt;
use App\Models\Texmaster;
use App\Models\Fuletype;
use App\Models\ObfInsurance;
use App\Models\CustomerTypeMaster;
use App\Models\RSA;
use App\Models\Setting;
use App\Models\rto_master;
use App\Models\offer;
use App\Models\MakeDeal;
use App\Models\CancellationReason;
use Spatie\Permission\Models\Role;
use Spatie\Permission\Models\Permission;
use Carbon\Carbon;
use Auth, DB, Mail, Validator, File, DataTables;
use Session; 
use PDF;
use App\Models\State;
use App\Models\District;
use App\Models\City;
use App\Models\Occupation;
use App\Models\DMS;
use TCPDF;

class ObfCompletedController extends Controller{
    /** construct **/
    public function __construct(){
        $this->middleware('permission:obf_completed-create', ['only' => ['create']]);
        $this->middleware('permission:obf_completed-edit', ['only' => ['edit']]);
        $this->middleware('permission:obf_completed-view', ['only' => ['view']]);
        $this->middleware('permission:obf_completed-delete', ['only' => ['delete']]);
    }

    /** index */
    public function index(Request $request){
        $permission_get = '';
            $data_v = auth()->user()->roles->pluck('data_permission');
            if (!empty($data_v)) {
                $permission_get = $data_v[0];
            }
            
            // Pre-fetch dropdown data (these do not need optimization):
            $customer_name = OBF::select('id', 'customer_name')->get();
            $kec_name = User::select(DB::raw("CONCAT(first_name,' ',last_name) AS kec_name"), 'id')->get();
            $product = Carmodel::select('name', 'id')->where('status', 'active')->get();
            $price = OBF::select('on_road_price', 'id')->get();
            $varient = Carvarient::select('name', 'id')->where('status', 'active')->get();
            $branch = Branch::select('name', 'id')->where('status', 'active')->get();
            $date = OBF::select('booking_date', 'id')->get();
            $cash_status = CashReceipt::select('cash_receipt.status')->join('obf', 'obf.id', '=', 'cash_receipt.obf_id')->get();
            $cash_total = CashReceipt::select('amount', 'receipt_type', 'obf.id as obf_id')
                ->join('obf', 'obf.id', '=', 'cash_receipt.obf_id')
                ->whereIn('receipt_type', ['booking_receipt', 'downpayment_receipt'])
                ->get();
            $cancel_reason = CancellationReason::select('name', 'id')->where('status', 'active')->get();
            
            // Filters
            $cust_name = $request->client;
            $kec = $request->kec;
            $products = $request->product;
            $road_price = $request->road_price;
            $variants = $request->varient;
            $locations = $request->location;
            $startdates = $request->start_date;
            $enddates = $request->end_date;
            
            if ($request->ajax()) {
                $cashSummary = 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'),
                        DB::raw('SUM(CASE WHEN status!="rejected" THEN amount ELSE 0 END) AS received_amount')
                    )
                    ->groupBy('obf_id');
                
                // Aggregate exchange purchase
                $exchangeSummary = DB::table('exchange_purchase')
                    ->select('obf_id', DB::raw('total_exchange_amount'))
                    ->whereIn('status', ['active','sell']);
                
                $data = OBF::select(
                    'obf.id', 
                    'obf.customer_name', 
                    'obf.ship_to_customer_name',
                    'obf.booking_date',
                    'car_model.name AS product',
                    'obf.status',
                    'obf.ew_status',
                    'obf.mcp_status',
                    'obf.rsa_status',
                    'obf.yodepartment_status',
                    'obf.rto_status',
                    'obf.insurance_status',
                    'obf.accesories_status',
                    'obf.fasttag_status',  
                    'obf.municipal_tax_status', 
                    'obf.on_road_price',
                    'car_varient.name AS car_varient_name',
                    'branches.name as branch_name',
                    'cash_receipt.status as receipt_status',
                    'orders.status as order_status',
                    'orders.invoice_no as order_invoice_no',
                    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 vin_number FROM inventory WHERE inventory.status != "inactive" AND inventory.status != "deleted" AND inventory.status != "sold" AND inventory.id = obf.inventory_id) as vin_number'),
                    DB::raw("CONCAT(users.first_name,' ',users.last_name) AS sales_person_name"),
                    DB::raw('cash_summary.received_amount'),
                    'obf.gst',
                    'exteriorcolor.name as exterior_color',
                    'obf.address',
                    'obf.registration',
                    'obf_finance.loan_amount',
                    'obf_finance.finance_discount',
                    DB::raw('cash_summary.net_disbusment'),
                    'obf.way_off',
                    'obf.foc_accessories',
                    'obf.offer_ib_amount',
                    'obf.offer_kin_amount',
                    'obf.approval_status',
                    DB::raw('cash_summary.booking_amount'),
                    DB::raw('cash_summary.dp_amount'),
                    DB::raw('cash_summary.access_sort_amount'),
                    'Team_master.team_name',
                    DB::raw('(exchange.total_exchange_amount) as 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 final_total"),
                    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 net_total"),
                    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)) + CASE WHEN ((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)) < obf.on_road_price) THEN -COALESCE(cash_summary.access_sort_amount, 0) ELSE COALESCE(cash_summary.access_sort_amount, 0) END) as due_amount"),
                    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_finance_discount')
                )
                ->leftjoin('products', 'obf.product_id', 'products.id')
                ->leftjoin('car_varient', 'car_varient.id', 'products.veriant')
                ->leftjoin('users','users.id','=','obf.sales_person_id')
                ->leftjoin('car_model', 'car_model.id', 'products.car_model')
                ->leftjoin('branches','branches.id','=','obf.branch_id')
                ->leftjoin('cash_receipt','cash_receipt.obf_id','=','obf.id') // required for cash_receipt.status only
                ->leftjoin('orders','orders.obf_id','=','obf.id')
                ->leftJoin('inventory','inventory.name','=','products.id')
                ->leftJoin('exteriorcolor','exteriorcolor.id','=','products.exterior_color')
                ->leftJoin('interiorcolor','interiorcolor.id','=','products.interior_color')
                ->leftJoin('obf_finance','obf_finance.obf_id','=','obf.id')
                ->leftjoin('Team_master','Team_master.id','users.team_name')
                ->leftJoinSub($cashSummary, 'cash_summary', 'cash_summary.obf_id', '=', 'obf.id')
                ->leftJoinSub($exchangeSummary, 'exchange', 'exchange.obf_id', '=', 'obf.id')
                ->where('orders.status','delivered');
            
                // Role-specific filters
                if (auth()->user()->roles && isset(auth()->user()->roles[0]['id'])) {
                    $roleId = auth()->user()->roles[0]['id'];
            
                    if (in_array($roleId, [33, 36, 37, 32])) {
                        $columns = [
                            33 => 'obf.extanded_warranty_id',
                            36 => 'obf.convinience_program',
                            37 => 'obf.rsa_master_obf',
                            32 => 'obf.accessory_id'
                        ];
                        $data = $data->groupBy('orders.obf_id')->whereIn('orders.status', ['all_payment_done', 'invoice_create'])->whereNotNull($columns[$roleId]);
                    } elseif ($roleId == 22) {
                        $data = $data->groupBy('obf.id');
                    } elseif ($roleId == 34) {
                        $data = $data->groupBy('obf.id')->where('obf.trade_car_value', '=', 'yes');
                    } else {
                        $data = $data->groupBy('obf.id');
                    }
                }
            
                // Permissions and branch restrictions
                if ($permission_get === 'ONLY_MY_DATA') {
                    $data = $data->whereRaw('FIND_IN_SET(obf.created_by, "' . auth()->id() . '")');
                }
                if (!empty(auth()->user()->branch)) {
                    $data = $data->whereRaw('FIND_IN_SET(obf.branch_id, "' . auth()->user()->branch . '")');
                }
                if ($permission_get === 'MY_TEAM_DATA' && auth()->user()->team_name !== null) {
                    $teamIds = User::where('team_name', auth()->user()->team_name)->pluck('id');
                    $data = $data->whereIn('obf.created_by', $teamIds);
                }
            
                $data->orderByDesc('obf.id');

                return Datatables::of($data)
                    ->addIndexColumn()
    
                    ->addColumn('action', function ($data) {
    
                        $return = '<div class="btn-group">';
                        
                        if (auth()->user()->can('docket-view')) {
                            $return .= '<a href="'.route('docket.preview', ['id' => base64_encode($data->id)]).'" class="btn btn-default btn-xs">
                                            <i class="fa-regular fa-file-pdf"></i>
                                        </a> &nbsp;';
                        }
    
                        return $return;
                    })
    
                    ->editColumn('booking_date', function($data) {
                        return (!empty($data->booking_date))
                            ? \Carbon\Carbon::parse($data->booking_date)->format('d-m-Y H:i:s')
                            : '--';
                    })
    
                    ->editColumn('final_total',function($data){
    
                        $book_amt = $data->booking_amount + $data->dp_amount + $data->net_disbusment + $data->total_exchange_amount;
    
                        return $book_amt;
    
                    })
    
                    ->editColumn('net_total',function($data){
                        
                        $finance_dis = ($data->finance_discount ?? 0) + ($data->way_off ?? 0) + ($data->foc_accessories ?? 0) + ($data->offer_ib_amount ?? 0) + ($data->offer_kin_amount ?? 0);
                        
                        $net_amt = $data->on_road_price - $finance_dis;
    
                        return $net_amt;
    
                    })
                    
                    ->editColumn('finance_discount',function($data){
    
                        $finance_dis = ($data->finance_discount ?? 0) + ($data->way_off ?? 0) + ($data->foc_accessories ?? 0) + ($data->offer_ib_amount ?? 0) + ($data->offer_kin_amount ?? 0);
                        
                        return $finance_dis;
    
                    })
                    
                    ->editColumn('access_sort_amount',function($data){
                        return (isset($data->access_sort_amount) && !empty($data->access_sort_amount)) ? $data->access_sort_amount : '0';
                    })
    
                    ->editColumn('due_amount',function($data){
                        $finance_dis = ($data->finance_discount ?? 0) + ($data->way_off ?? 0) + ($data->foc_accessories ?? 0) + ($data->offer_ib_amount ?? 0) + ($data->offer_kin_amount ?? 0);
                        
                        $book_amt = $data->booking_amount + $data->dp_amount + $data->net_disbusment + $data->total_exchange_amount;
                        
                        $total_amt = $book_amt + $finance_dis;
                        $on_road_amount = $data->on_road_price;
                        $due_amount = $on_road_amount - $total_amt;
                        if ($total_amt < $on_road_amount) {
                            $due_amount -= ($data->access_sort_amount ?? 0);
                        } else {
                            $due_amount += ($data->access_sort_amount ?? 0);
                        }
                        return $due_amount;
    
                    })
    
                    ->editColumn('status', function ($data) {
    
                        if($data->receipt_status == 'accepted' && $data->status == 'account_accepted' && $data->order_status == 'waiting'){
                    
                            return '<span class="badge badge-pill badge-success">Order'. '-'.$data->order_status .'</span>';
                    
                        }elseif($data->status == 'account_accepted' && $data->order_status == 'finance_apporve'){
                    
                            return '<span class="badge badge-pill badge-success">Finance Apporved</span>';
                    
                        }elseif($data->status == 'account_accepted' && $data->order_status == 'disbursement'){
                    
                            return '<span class="badge badge-pill badge-success">Disbursement</span>';
                    
                        }elseif($data->status == 'account_accepted' && $data->order_status == 'all_payment_done'){
                    
                            return '<span class="badge badge-pill badge-success">All Payment Done</span>';
                    
                        }elseif($data->order_status == 'pdi_approve'){
                    
                            return '<span class="badge badge-pill badge-success">PDI Approve</span>';
                    
                        }elseif($data->order_status == 'pdi_reject'){
                    
                            return '<span class="badge badge-pill badge-danger">PDI Reject</span>';
                    
                        }elseif($data->order_status == 'ready_to_deliver'){
                    
                            return '<span class="badge badge-pill badge-success">Ready to Deliver</span>';
                    
                        }elseif($data->order_status == 'delivered'){
                    
                            return '<span class="badge badge-pill badge-success">Delivered</span>';
                    
                        }elseif($data->order_status == 'invoice_create'){
                    
                            return '<span class="badge badge-pill badge-success">Invoice Created</span>';
                    
                        }elseif($data->order_status == 'invoice_cancel'){
                    
                            return '<span class="badge badge-pill badge-danger">Invoice Cancelled</span>';
                    
                        }elseif($data->receipt_status == 'accepted' && $data->status == 'account_accepted'){
                    
                            return '<span class="badge badge-pill badge-success">Receipt Created</span>';
                    
                        }elseif ($data->status == 'accepted') {
                    
                            return '<span class="badge badge-pill badge-success">Active</span>';
                    
                        }elseif ($data->status == 'pending') {
                    
                            return '-';
                    
                        }elseif ($data->status == 'deleted') {
                    
                            return '<span class="badge badge-pill badge-danger">Deleted</span>';
                    
                        }elseif ($data->status == 'account_rejected') {
                    
                            return '<span class="badge badge-pill badge-danger">Account Rejected</span>';
                    
                        }elseif ($data->status == 'rejected') {
                    
                            return '<span class="badge badge-pill badge-danger">Rejected</span>';
                    
                        }elseif ($data->status == 'account_accepted') {
                    
                            return '<span class="badge badge-pill badge-success">Account Accepted</span>';
                    
                        }elseif($data->status == 'completed'){
                    
                            return '<span class="badge badge-pill badge-success">Completed</span>'; 
                    
                        }elseif ($data->status == 'cancel') {
                    
                            return '<span class="badge badge-pill badge-danger">Cancel</span>';
                    
                        }elseif ($data->status == 'deal_generated') {
                    
                            return '<span class="badge badge-pill badge-success">Deal Generated</span>';
                    
                        }elseif ($data->status == 'delete') {
                    
                            return '<span class="badge badge-pill badge-danger">Deleted</span>';
                    
                        }else{
                    
                            return '-';
                    
                        }                            
                    
                    })
                    
                    ->editColumn('approval_status', function ($data) {
    
                        if ($data->approval_status == 'pending') {
    
                            return '<span class="badge badge-pill badge-warning">Pending From TL</span>';
    
                        }elseif ($data->approval_status == 'obf_tl_rejected') {
    
                            return '<span class="badge badge-pill badge-danger">OBF Rejected By TL</span>';
    
                        }elseif ($data->approval_status == 'obf_sm_rejected') {
    
                            return '<span class="badge badge-pill badge-danger">OBF Rejected By SM</span>';
    
                        }elseif ($data->approval_status == 'obf_gm_rejected') {
    
                            return '<span class="badge badge-pill badge-danger">OBF Rejected By GM</span>';
    
                        }elseif ($data->approval_status == 'obf_accountant_rejected') {
    
                            return '<span class="badge badge-pill badge-danger">OBF Rejected By Accountant</span>';
    
                        }elseif ($data->approval_status == 'obf_tl_accepted') {
    
                            return '<span class="badge badge-pill badge-success">Obf Accepted By TL</span>';
    
                        }elseif ($data->approval_status == 'obf_sm_accepted') {
    
                            return '<span class="badge badge-pill badge-success">Obf Accepted By SM</span>';
    
                        }elseif ($data->approval_status == 'obf_gm_accepted') {
    
                            return '<span class="badge badge-pill badge-success">Obf Accepted By GM</span>';
    
                        }elseif ($data->approval_status == 'obf_accountant_accepted') {
    
                            return '<span class="badge badge-pill badge-success">Obf Accepted By Accountant</span>';
    
                        }elseif ($data->approval_status == 'delete') {
    
                            return '<span class="badge badge-pill badge-danger">Deleted</span>';
    
                        }else{
    
                            return '-';
    
                        }                            
    
                    })
                    
                    ->editColumn('ew_status', function ($data) {
                        return ucwords(str_replace('_', ' ', $data->ew_status));
                    })
                    
                    ->editColumn('mcp_status', function ($data) {
                        return ucwords(str_replace('_', ' ', $data->mcp_status));
                    })
                    
                    ->editColumn('rsa_status', function ($data) {
                        return ucwords(str_replace('_', ' ', $data->rsa_status));
                    })
                    
                    ->editColumn('accesories_status', function ($data) {
                        return ucwords(str_replace('_', ' ', $data->accesories_status));
                    })
                    
                    ->editColumn('rto_status', function ($data) {
                        return ucwords(str_replace('_', ' ', $data->rto_status));
                    })
                    
                    ->editColumn('insurance_status', function ($data) {
                        return ucwords(str_replace('_', ' ', $data->insurance_status));
                    })
                    
                    ->rawColumns(['action' ,'status','approval_status'])
    
                    ->filter(function ($query){
                            if ($dateRange = request()->get('booking_date', false)) {
                            $dates = explode(' to ', $dateRange);
                            if (count($dates) === 2) {
                                try {
                                    $start = \Carbon\Carbon::createFromFormat('d-m-Y', trim($dates[0]))->startOfDay()->format('Y-m-d');
                                    $end = \Carbon\Carbon::createFromFormat('d-m-Y', trim($dates[1]))->endOfDay()->format('Y-m-d');
                                    $query->whereBetween('obf.booking_date', [$start, $end]);
                                } catch (\Exception $e) {
                                    \Log::error('Invalid date range format for booking_date filter', [
                                        'input' => $dateRange,
                                        'error' => $e->getMessage()
                                    ]);
                                }
                            }
                        }
    
                        if (request()->get('branch_name',false)) {
                            $query->where('branches.name', "like", "%".request()->get("branch_name")."%");
                        }
                        if (request()->get('customer_name',false)) {
                            $query->where('obf.customer_name', "like", "%".request()->get("customer_name")."%");
                        }
                        if (request()->get('gst',false)) {
                            $query->where('obf.gst', "like", "%".request()->get("gst")."%");
                        }
                        if (request()->get('ship_to_customer_name',false)) {
                            $query->where('obf.ship_to_customer_name', "like", "%".request()->get("ship_to_customer_name")."%");
                        }
                        if (request()->get('team_name',false)) {
                            $query->where('Team_master.team_name', "like", "%".request()->get("team_name")."%");
                        }
                        if (request()->get('sales_person_name',false)) {
                            $salesPersonName = request()->get('sales_person_name');
                            $query->whereRaw("CONCAT(users.first_name, ' ', users.last_name) LIKE ?", ["%{$salesPersonName}%"]);
                        }
                        if (request()->get('product',false)) {
                            $query->where('car_model.name', "like", "%".request()->get("product")."%");
                        }
                        if (request()->get('car_varient_name',false)) {
                            $query->where('car_varient.name', "like", "%".request()->get("car_varient_name")."%");
                        }
                        if (request()->get('exterior_color',false)) {
                            $query->where('exteriorcolor.name', "like", "%".request()->get("exterior_color")."%");
                        }
                        if (request()->get('vin_number',false)) {
                            $query->where('inventory.vin_number', "like", "%".request()->get("vin_number")."%");
                        }
                        if (request()->get('on_road_price',false)) {
                            $query->where('obf.on_road_price', "like", "%".request()->get("on_road_price")."%");
                        }
                        if (request()->get('booking_amount', false)) {
                            $bookingAmount = request()->get('booking_amount');
                            $query->having('booking_amount', 'LIKE', "%{$bookingAmount}%");
                        }
                        if (request()->get('dp_amount', false)) {
                            $dpAmount = request()->get("dp_amount");
                            $query->having('dp_amount', 'LIKE', "%{$dpAmount}%");
                        }
                        if (request()->get('loan_amount',false)) {
                            $query->where('obf_finance.loan_amount', "like", "%".request()->get("loan_amount")."%");
                        }
                        if (request()->get('net_disbusment',false)) {
                            $query->where('obf_finance.net_disbusment', "like", "%".request()->get("net_disbusment")."%");
                        }
                        if (request()->get('total_exchange_amount',false)) {
                            $query->where('exchange_purchase.total_exchange_amount', "like", "%".request()->get("total_exchange_amount")."%");
                        }
                        if ($finalTotal = request()->get('final_total')) {
                            $query->havingRaw("final_total LIKE ?", ["%{$finalTotal}%"]);
                        }
                        if ($discount = request()->get('finance_discount')) {
                            $query->havingRaw('CAST(total_finance_discount AS CHAR) LIKE ?', ["%{$discount}%"]);
                        }
    
                        if ($netTotal = request()->get('net_total')) {
                            $query->havingRaw("net_total LIKE ?", ["%{$netTotal}%"]);
                        }
                        if ($accessSort = request()->get('access_sort_amount')) {
                            $query->havingRaw("access_sort_amount LIKE ?", ["%{$accessSort}%"]);
                        }
                        if ($dueAmount = request()->get('due_amount')) {
                            $query->havingRaw("due_amount LIKE ?", ["%{$dueAmount}%"]);
                        }
                        if (request()->get('status',false)) {
                            $query->where('obf.status', "like", "%".request()->get("status")."%");
                        }
                        if (request()->get('approval_status',false)) {
                            $query->where('obf.approval_status', "like", "%".request()->get("approval_status")."%");
                        }
                        if (request()->get('invoice_no',false)) {
                            $query->where('orders.invoice_no', "like", "%".request()->get("invoice_no")."%");
                        }
                        if (request()->get('invoice_date',false)) {
                            $query->where('orders.invoice_date', "like", "%".request()->get("invoice_date")."%");
                        }
                    })   
    
                    ->make(true);
                }

            

                $role = auth()->user()->roles;
    
                $roleName = "";
    
                if ($role) {
    
                    $roleName = $role[0]->name;
    
                }

        return view('obf_completed.index')->with(['customer_name'=>$customer_name,'kec_name'=>$kec_name,'product'=>$product,'price'=>$price,'varient'=>$varient,'branch'=>$branch,'date'=>$date,'roleName'=>$roleName,'cancel_reason'=>$cancel_reason,'cash_total'=>$cash_total]);
    }
    /** index */
    
    

    /** change-status */
    public function change_status(Request $request){
        if (!$request->ajax()) { exit('No direct script access allowed'); }
        $id = base64_decode($request->id);
        $data = OBF::where(['id' => $id])->first();
        
        if (!empty($data)) {
            $update = OBF::where(['id' => $id])->update(['status' => $request->status, 'updated_at' => date('Y-m-d H:i:s'), 'updated_by' => auth()->user()->id]);
            
            $crud = [
                'module_nm' => 'obf',
                'module_id' => $id,
                'status' => $request->status,
                'remark' => '',
                'created_at' => date('Y-m-d H:i:s'),
                'created_by' => auth()->user()->id,
            ];
            \DB::table('logs')->insert($crud);

            if ($update) {
                return response()->json(['code' => 200]);
            } else {
                return response()->json(['code' => 201]);
            }
        } else {
            return response()->json(['code' => 201]);
        }
    }
    /** change-status */
    

    
    /** Docket PDF View */
    public function preview(Request $request, $id)
    {
        $id = base64_decode($request->id);   
        
        
        /* MAKE DEAL DATA*/
        $make_deal_data = MakeDeal::select([
            'make_deal.deal_amt',
            'make_deal.purchase_gst_tax_deal',
            'make_deal.gst_tax_deal',
            'make_deal.cess_tax_deal',
            'make_deal.tcs_tax_deal',
            'make_deal.municipal_tax_deal',
            'make_deal.municipal_custom_handling_charges_deal',
            'make_deal.rto_deal',
            'make_deal.rto_cutom_handling_charges_deal',
            'make_deal.insurance_deal',
            'make_deal.final_deal_amt',
            'make_deal.final_deal_amt',
            'make_deal.remark',
            'obf.customer_name',
            'obf.tcs_tax_id',
            'obf.municipal_tax_id',
            'obf.municipal_custom_handling_charges',
            'obf.registration_tax_id',
            'obf.rto_custom_handling_charges',
            'obf.insurance_id',
            'obf.other_accesssories_prise',
            'obf.final_on_road_price_after_way_off',
            'obf.accessory_id',
            'obf.pricelist_id',
            'Team_master.team_name',
            'branches.name AS branch_name',
            'car_model.name AS car_model_name',
            'car_varient.name AS car_varient_name',
            'inventory.vin_number',
            'inventory.purchase_price',
            'inventory.aging_days',
            'inventory.remark as inventory_remark',
            'fasttags.amount AS fasttag_amount_new',
            'extand_warranties.amount AS extand_warranties_amount',
            'rsa.rsa_ccs as rsa_type',
            'rsa.amount as rsa_amount',
            'ccs.rsa_ccs as ccs_type',
            'ccs.amount as ccs_amount',
            'mcp.amount as mcp_amount',
            DB::raw("DATE_FORMAT(obf.booking_date, '%d-%m-%Y') as booking_date"),
            DB::raw("CONCAT(users.first_name,' ',users.last_name) AS sales_person_name")
        ])
        ->leftjoin('obf', 'make_deal.obf_id', 'obf.id')
        ->leftjoin('branches' ,'obf.branch_id' ,'branches.id')
        ->leftjoin('products', 'obf.product_id', 'products.id')
        ->leftjoin('car_model', 'car_model.id', 'products.car_model')
        ->leftjoin('car_varient', 'car_varient.id', 'products.veriant')
        ->leftjoin('inventory' ,'obf.inventory_id' ,'inventory.id')
        ->leftjoin('fasttags' ,'obf.fasttag_brand_name' ,'fasttags.id')
        ->leftjoin('extand_warranties' ,'obf.extanded_warranty_id' ,'extand_warranties.id')
        ->leftjoin('rsa','obf.rsa_master_obf','rsa.id')
        ->leftjoin('rsa as ccs','obf.ccs_master_obf','ccs.id')
        ->leftjoin('mcp','obf.convinience_program','mcp.id')
        ->leftjoin('users' ,'obf.sales_person_id' ,'users.id')
        ->leftjoin('Team_master','Team_master.id','users.team_name')
        ->where('make_deal.obf_id', $id)
        ->first();
        
        $price_list_name = DB::table('pricelist_master')->select('basic','price_list_name','gst_percentage','gst_amount','cess_percentage','cess_amount')->where('id',  $make_deal_data->pricelist_id)->first();
        
        $accessory_sum = 0;
        if(!empty($make_deal_data->accessory_id) && $make_deal_data->accessory_id != NULL && $make_deal_data->accessory_id != ''){
            $accessory_id_arr = explode(' ,', $make_deal_data->accessory_id);
            if(!empty($accessory_id_arr)){
                $accessory_sum = Accessory::whereIn('accessories.id', $accessory_id_arr)->sum('price');           
            }
        }
        
        
        
        /* OBF PRINT SUMMARY DATA */
        $data = OBF::select('obf.id',
            'obf.sales_person_id',
            DB::raw("CONCAT(users.first_name,' ',users.last_name) AS sales_person_name"),
            'obf.temporary_id',
            'obf.booking_date',
            'obf.customer_name',
            'obf.customer_type',
            'obf.customer_type_new',
            'obf.booking_date',
            'branches.name AS branch_name',
            'branches.id AS branch_id',
            'obf.company_name', 
            'obf.einvoicing_applicable',
            'obf.gst',
            'obf.address',
            'obf.registration',
            'obf.email',
            'obf.pan_number',
            'obf.adhar_number',
            'obf.licance_number',
            'obf.contact_number',
            'obf.dob',
            'obf.nominee_name',
            'obf.nominee_reletion',
            'obf.nominee_age',
            'obf.occupation',
            'products.name AS product_name',
            'obf.product_id',
            'products.veriant',
            'products.is_applicable_for_mcp',
            'obf.ex_showroom_price',
            'obf.fasttag_brand_name AS fasttag_brand_name',
            'registration_tax.percentage AS registration_tax',
            'insurance.name AS insurance',
            'obf.insurance_id',
            'municipal_tax.percentage AS municipal_tax',
            'tcs_tax.percentage AS tcs_tax',
            'accessories.name AS accessory_name',
            'obf.accessory_id',
            'obf.extanded_warranty_id',
            'extand_warranties.years AS extand_warranties_years',
            'extand_warranties.amount AS extand_warranties_amount',
            'fasttags.brand_name AS brand_name',
            'fasttags.amount AS fasttag_amount_new',
            'fasttags.id AS fasttag_id',
            'obf.trade_car_name',
            'obf.trade_car_value', 
            'obf.on_road_price',
            'obf.on_road_price_word',
            'obf.on_road_price_word',
            'obf.finance_id',
            'finance.name AS finance_name',
            'finance_branch.name AS finance_branch_name',
            'lead.name AS lead_name',
            'obf.lead_id',
            'obf.mode_of_payment',
            'obf.status',
            'obf.created_by',
            'obf.updated_by',
            'obf.created_at',
            'obf.updated_at',
            'allocation.tentetive_delivery_date',
            DB::raw('(SELECT SUM(amount) FROM cash_receipt WHERE cash_receipt.obf_id = obf.id AND cash_receipt.receipt_type = "booking_receipt" AND cash_receipt.status != "rejected") as booking_amount'),
            DB::raw('(SELECT SUM(amount) FROM cash_receipt WHERE cash_receipt.obf_id = obf.id AND cash_receipt.receipt_type = "downpayment_receipt" AND cash_receipt.status != "rejected") as dp_amount'),
            DB::raw('(SELECT total_exchange_amount FROM exchange_purchase WHERE exchange_purchase.obf_id = obf.id AND exchange_purchase.status = "active" LIMIT 1) as total_exchange_amount'),
            DB::raw('(SELECT exchange_bonus FROM exchange_purchase WHERE exchange_purchase.obf_id = obf.id AND exchange_purchase.status = "active" LIMIT 1) as exchange_bonus'),
            'obf_rto.rto_type',
            'obf_rto.registration_date',
            'obf_rto.application_number',
            'obf_rto.temporary_number',
            'obf_rto.final_amount',
            'obf_rto.vehicle_number',
            'obf_rto.status as rto_status',
            'obf_fasttag.fasttag_number',
            'obf_fasttag.fasttag_brand',
            'obf_fasttag.date_of_issue',
            'obf_fasttag.fasttag_amount',
            'obf_fasttag.opening_stock',
            'obf_fasttag.status as fasttag_status',
            'obf.pricelist_id',
            'obf.registration_tax_id',
            'obf.municipal_tax_id',
            'obf.municipal_tax_applicable',
            'obf.tcs_tax_id',
            'obf.nominee_dob',
            'convinience_program',
            'obf.step1_remarks',
            'obf.step2_remarks',
            'obf.step3_remarks',
            'obf.step4_remarks',
            'obf.step5_remarks',
            'obf.other_documents',
            'obf.pan_image',
            'obf.adhar_image',
            'obf.licance_image',
            'obf.other_details_1',
            'obf.other_details_2',
            'obf.priclist_detail',
            'obf.inventory_id',
            'obf.aadhar_pan_link',
            'obf_finance.repo_rate',
            DB::raw('(SELECT SUM(amount) FROM cash_receipt WHERE cash_receipt.obf_id = obf.id AND cash_receipt.receipt_type = "disbusment_receipt" AND cash_receipt.status != "rejected") as net_disbusment'),
            'obf_finance.loan_amount',
            'obf_finance.processing_fee',
            'obf_finance.finance_discount',
            'obf_finance.payout',
            'obf_finance.pay_booster',
            'obf_finance.finance_required',
            'obf.sub_lead_id',
            'obf.rsa_master_obf',
            'car_model.name AS car_model_name',
            'car_varient.name AS car_varient_name',
            'interiorcolor.name AS interiorcolor_name',
            'exteriorcolor.name AS exteriorcolor_name',
            'obf_insurance.company_name AS insurance_company_name',
            'finance.name AS finance_name',
            'inventory.key_number',
            'inventory.engine_number',
            'inventory.chassis_number',
            'inventory.vin_number',
            'inventory.manufacturing_year',
            'obf_insurance.company_name AS insurance_company_name',
            'obf_insurance.ncb_amount',
            'obf.offer_ib_amount',
            'obf.offer_kin_amount',
            'obf.offer_amount',
            'obf.nominee_dob',
            'obf.other_accesssories_prise',
            'obf.offer_type',
            'obf.offer_kin',
            'obf.offer_ib',
            'obf.offer_amount',
            'obf.deal_price',
            'obf.way_off',
            'obf.foc_accessories',
            'obf.final_on_road_price_after_way_off',
            'obf_finance.disbursement',
            'obf_finance.finance_type',
            'obf_finance.payout_amount',
            'obf_finance.lumsum_amount',
            'obf_finance.total_payout_amount',
            'obf.number_plate',
            'customer_type_master.name as customer_type_nm',
            'Team_master.team_name',
            'rsa.name as rsa_name',
            'rsa.amount as rsa_amount',
            'ccs.name as ccs_name',
            'ccs.amount as ccs_amount',
            'obf.anniversary_date',
            'obf.rto_custom_handling_charges',
            'obf.municipal_tax_type',
            'obf.municipal_custom_handling_charges',
        )
        ->leftjoin('obf_rto' ,'obf.id' ,'obf_rto.obf_id')
        ->leftjoin('obf_fasttag' ,'obf.id' ,'obf_fasttag.obf_id')
        ->leftjoin('obf_finance' ,'obf.id' ,'obf_finance.obf_id')
        ->leftjoin('users' ,'obf.sales_person_id' ,'users.id')
        ->leftjoin('branches' ,'obf.branch_id' ,'branches.id')
        ->leftjoin('products', 'obf.product_id', 'products.id')
        ->leftjoin('car_model', 'car_model.id', 'products.car_model')
        ->leftjoin('car_varient', 'car_varient.id', 'products.veriant')
        ->leftjoin('interiorcolor', 'interiorcolor.id', 'products.interior_color')
        ->leftjoin('exteriorcolor', 'exteriorcolor.id', 'products.exterior_color')
        ->leftjoin('taxes AS registration_tax' ,'obf.registration_tax_id' ,'registration_tax.id')
        ->leftjoin('taxes AS municipal_tax' ,'obf.municipal_tax_id' ,'municipal_tax.id')
        ->leftjoin('taxes AS tcs_tax' ,'obf.tcs_tax_id' ,'tcs_tax.id')
        ->leftjoin('insurance' ,'obf.insurance_id' ,'insurance.id')
        ->leftjoin('allocation' ,'obf.id' ,'allocation.obf_id')
        ->leftjoin('accessories' ,'obf.accessory_id' ,'accessories.id')
        ->leftjoin('extand_warranties' ,'obf.extanded_warranty_id' ,'extand_warranties.id')
        ->leftjoin('fasttags' ,'obf.fasttag_brand_name' ,'fasttags.id')
        ->leftjoin('finance' ,'obf_finance.finance_name' ,'finance.id')
        ->leftjoin('branches AS finance_branch' ,'obf.finance_branch_id' ,'finance_branch.id')
        ->leftjoin('lead' ,'obf.lead_id' ,'lead.id')
        ->leftjoin('obf_insurance' ,'obf.id' ,'obf_insurance.obf_id')
        ->leftjoin('inventory' ,'obf.inventory_id' ,'inventory.id')
        ->leftjoin('customer_type_master','obf.customer_type_new','customer_type_master.id')
        ->leftjoin('exchange_purchase','exchange_purchase.obf_id','=','obf.id')
        ->leftjoin('cash_receipt','cash_receipt.obf_id','=','obf.id')
        ->leftjoin('Team_master','Team_master.id','users.team_name')
        ->leftjoin('rsa','obf.rsa_master_obf','rsa.id')
        ->leftjoin('rsa as ccs','obf.ccs_master_obf','ccs.id')
        ->where(['obf.id' => $id])
        ->first();
        
        $obf = OBF::find($id);
        
        $accessory_ids_string = $obf->accessory_id; 
        $accessory_ids_array = explode(',', $accessory_ids_string);

        $img = DB::table('global_configuration')->select('*')->where('status','active')->first();
        $img1 = $img->invoice_logo1 ?? '';
        $img2 = $img->invoice_logo2 ?? '';

        $leads = OBF::select('lead.id' ,'lead.name')->leftjoin('lead' ,'lead.id','obf.lead_id')->where('obf.id' , $id)->first();
        $sub_leads = OBF::select('sub_lead.id' ,'sub_lead.name')->leftjoin('sub_lead' ,'sub_lead.id','obf.sub_lead_id')->where('obf.id' , $id)->first();

        $fule = Obf::select('fule_type.title as fule_name')
        ->leftJoin('products','products.id','=','obf.product_id')
        ->leftJoin('car_varient','car_varient.id','=','products.veriant')
        ->leftJoin('fule_type','fule_type.id','=','car_varient.fule_type')
        ->where('obf.id', '=', $id)  
        ->first();

        $Inventory_data = [];
        if($data->inventory_id != NULL){
            $Inventory_data = Inventory::where('id', '=', $data->inventory_id)->first();
        } else {
            $product_id = Product::where('id',$data->product_id)->first();
            $Inventory_data = Inventory::where('name', '=', $product_id->id)->first();
        }

        $ObfInsurance = ObfInsurance::where(['obf_id'=> $id,'is_delete' => 0])->orderBy('id', 'desc')->first();

        $summary_accessory_sum = 0;
        if(!empty($data->accessory_id) && $data->accessory_id != NULL && $data->accessory_id != ''){
            $accessory_id_arr = explode(' ,', $data->accessory_id);
            if(!empty($accessory_id_arr)){
                $summary_accessory_sum = Accessory::whereIn('id', $accessory_id_arr)->sum('price');           
            }
        }
        
        
        
        /* OBF DOCUMENTS DATA */
        $obf_document_image = OBF::select(
            'pan_image',
            'adhar_image',
            'aadhar_pan_image',
            'licance_image',
            'passport_document',
            'gst_certificate',
        )->where('id',$id)->first();
        
        
        /* EW DOCUMENT DATA */
        $ew_document = DB::table('ew_process_dms')->select('dms_ew_certificate')->where('obf_id',$id)->first();
        
        /* MCP DOCUMENT DATA */
        $mcp_document = DB::table('mcp_process_dms')->select('dms_mcp_certificate')->where('obf_id',$id)->first();
        
        /* RSA DOCUMENT DATA */
        $rsa_document = DB::table('rsa_process_dms')->select('dms_rsa_certificate')->whereIn('status',['rsa_completed','rsa_in_progress'])->where('obf_id',$id)->first();
        
        /* CCS DOCUMENT DATA */
        $ccs_document = DB::table('rsa_process_dms')->select('dms_rsa_certificate')->whereIn('status',['ccs_completed','ccs_in_progress'])->where('obf_id',$id)->first();
        
        /* FASTAG DOCUMENT DATA */
        $fastag_document = DB::table('obf_fasttag')->select('fasttag_image','dms_certificate')->where('obf_id',$id)->first();
        
        /* MUNICIPAL DOCUMENT DATA */
        $municipal_document = DB::table('obf_municiple_tax')->select('municiple_image')->where('obf_id',$id)->first();
        
        /* ACCESSORY DOCUMENT DATA */
        $accessory_document = DB::table('accesories_process_dms')->select('dms_accesories_certificate')->where('obf_id',$id)->first();
        
        /* RTO DOCUMENT DATA */
        $rto_document = DB::table('obf_rto')->select('rto_image','dms_rto_certificate')->where('obf_id',$id)->first();
        
        /* INSURANCE DOCUMENT DATA */
        $insurance_document = DB::table('obf_insurance')->select('insurance_image','handicap_image','old_rc_book_image','policy_image','ncb_certificate_image')->where('obf_id',$id)->first();

        return view('obf_completed.docket', compact(
            'make_deal_data',
            'price_list_name',
            'accessory_sum',
            'data',
            'Inventory_data',
            'ObfInsurance',
            'fule',
            'summary_accessory_sum',
            'obf_document_image',
            'ew_document',
            'mcp_document',
            'rsa_document',
            'ccs_document',
            'fastag_document',
            'municipal_document',
            'accessory_document',
            'rto_document',
            'insurance_document',
        ));
    }
}
