<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class SearchController extends Controller
{
    //

    public function index(Request $request){


        $searchTerm = '';
        $searchTerm =  $request->input('search_term');
        $letter_count = strlen($searchTerm);
        if($searchTerm === '' || $letter_count <= 2){
            $results = [];
            die(json_encode($results));
        }

        //GET COMPANY ID BASED ON API KEY FROM USER'S REQUEST
        $api_key = $request->header('X-API-KEY');
        $userID =  $request->header('USER-ID');

        $account_id = DB::table('accounts')
            ->where('api_key', $api_key)
            ->where('subscription_status', 'active')
            ->value('account_id');

        //FAIL QUERY IF API KEY ISN'T FOUND
        if (!$account_id) {
            $results = [];
            return response()->json([
                'error' => $api_key . 'Unauthorized Access',
            ], 404);
        }

        $company_id = $account_id;
        $position = strpos($searchTerm, '-');

//        if ($position !== false) {
        if (preg_match('/^[A-Z]{2}-[A-Z]{2}$/', $searchTerm)) {
            $from_state = substr($searchTerm, 0, $position);
            $to_state = substr($searchTerm, $position + 1);
//            echo "From state: $from_state, To state: $to_state";

            //orders found based on state to state search
            if($from_state == '' || $to_state == ''){
                $results = [];
            } else {
                $results = [
                    'orders' => DB::table('orders')
                        ->where(function ($query) use ($from_state, $to_state, $company_id) {
                            $query->where('origin_state', 'like', '%'.$from_state.'%')
                                ->where('dest_state', 'like', '%'.$to_state.'%')
                                ->where('company_id', $company_id);
                        })
                        ->get()
                        ->map(function ($row) {
                            $row->table_name = 'orders';
                            $row->id = $row->order_id;
                            $row->uuid = $row->order_uniq_id;
                            $row->title =  "From: " . $row->pickup_city . ', ' . $row->pickup_state . ', ' .$row->pickup_zip .  ' To: ' . $row->delivery_city . ', ' . $row->delivery_state . ', ' . $row->delivery_zip;
                            $row->sub_title = $row->shpr_name . ' ' . $row->shpr_email . ' ' . $row->shpr_mobile ;
                            $row->status =  $row->status;
                            $row->contact = $row->shpr_name;
                            $row->mobile = $row->shpr_number;
                            $row->email =  $row->shpr_email ;
                            $row->load =   $row->car_year  . ' ' . $row->car_make . ' ' .  $row->car_model;
                            $row->created = $row->created;
                            $row->source = $row->source;
                            return $row;
                        })
                ];
            }
        }
        else {

            $results = [
                'tasks' => DB::table('tasks')
                    ->where('todo', 'like', '%'.$searchTerm.'%')
                    ->get()
                    ->map(function ($row) {
                        $row->table_name = 'tasks';
                        $row->id = $row->task_id;
                        $row->object_id = $row->item_id;
                        $row->item_id = $row->item_id;
                        $row->task_type = $row->type;
                        $row->type = $row->type;
                        $row->title = strip_tags($row->todo);
                        $row->sub_title = "Due: " . $row->date;
                        return $row;
                    }),
                'company_accounts' => DB::table('company_accounts')
                    ->where('firstname', 'like', '%'.$searchTerm.'%')
                    ->orWhere('lastname', 'like', '%'.$searchTerm.'%')
                    ->orWhere('email', 'like', '%'.$searchTerm.'%')
                    ->orWhere('company_name', 'like', '%'.$searchTerm.'%')
                    ->orWhere('contact_1', 'like', '%'.$searchTerm.'%')
                    ->orWhere('contact_2', 'like', '%'.$searchTerm.'%')
                    ->orWhere('phone_1', 'like', '%'.$searchTerm.'%')
                    ->orWhere('phone_2', 'like', '%'.$searchTerm.'%')
                    ->orWhere('company_phone', 'like', '%'.$searchTerm.'%')
                    ->orWhere('address', 'like', '%'.$searchTerm.'%')
                    ->orWhere('address_2', 'like', '%'.$searchTerm.'%')
                    ->orWhere('city', 'like', '%'.$searchTerm.'%')
                    ->orWhere('state', 'like', '%'.$searchTerm.'%')
                    ->orWhere('zip', 'like', '%'.$searchTerm.'%')
                    ->orWhere('agent_name', 'like', '%'.$searchTerm.'%')
                    ->orWhere('agent_phone', 'like', '%'.$searchTerm.'%')
                    ->orWhere('policy_number', 'like', '%'.$searchTerm.'%')
                    ->orWhere('mc_number', 'like', '%'.$searchTerm.'%')
                    ->orWhere('cd_carrier_id', 'like', '%'.$searchTerm.'%')
                    ->get()
                    ->map(function ($row) {
                        $row->table_name = 'company_accounts';
                        $row->id = $row->comp_account_id;
                        $row->object_id = $row->comp_accnt_unique_id;
                        $row->type = $row->account_type;
                        $row->mobile = $row->phone_1;
                         if(isset($row->company_name)) {
                             $row->title = $row->company_name;
                             $row->contact = $row->company_name;
                         } else {
                             $row->title = $row->firstname . ' ' . $row->lastname;
                             $row->contact = $row->firstname . ' ' . $row->lastname;
                         }
                        $row->sub_title = $row->account_type;
                        return $row;
                    }),
                'notes' => DB::table('notes')
                    ->where('note', 'like', '%'.$searchTerm.'%')
                    ->get()
                    ->map(function ($row) {
                        $row->table_name = 'notes';
                        $row->id = $row->note_id;
                        $row->object_id = $row->item_id;
                        $row->item_id = $row->item_id;
                        $row->note_type = $row->note_type;
                        $row->type = $row->type;
                        $row->title = $row->note;
                        $row->sub_title = "Author:".  $row->added_by;
                        return $row;
                    }),
                'leads' => DB::table('leads')
                    ->where('move_date', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_name', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_number', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_scnd_number', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_company', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_mobile', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_fax', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_address', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_scnd_address', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_state', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_city', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_zip', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_email', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_year', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_make', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_model', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_type', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_color', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_plate_no', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_state', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_vin', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_lot_no', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_additional_info', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_condition', 'like', '%'.$searchTerm.'%')
                    ->orWhere('origin_city', 'like', '%'.$searchTerm.'%')
                    ->orWhere('origin_state', 'like', '%'.$searchTerm.'%')
                    ->orWhere('origin_zip', 'like', '%'.$searchTerm.'%')
                    ->orWhere('dest_city', 'like', '%'.$searchTerm.'%')
                    ->orWhere('dest_state', 'like', '%'.$searchTerm.'%')
                    ->orWhere('dest_zip', 'like', '%'.$searchTerm.'%')
                    ->get()
                    ->map(function ($row) {
                        $row->table_name = 'leads';
                        $row->id = $row->lead_id;
                        $row->object_id = $row->lead_uniq_id;
                        $row->title =  "From: " . $row->origin_city . ', ' . $row->origin_state . ', ' .$row->origin_zip .  ' To: ' . $row->dest_city . ', ' . $row->dest_state . ', ' . $row->dest_zip;
                        $row->sub_title = $row->shpr_name . ' ' . $row->shpr_email . ' ' . $row->shpr_number ;
                        $row->status =  "Quote";
                        $row->contact = $row->shpr_name;
                        $row->mobile = $row->shpr_number;
                        $row->email =  $row->shpr_email ;
                        $row->load =   $row->car_year  . ' ' . $row->car_make . ' ' .  $row->car_model;
                        $row->created = $row->received;
                        $row->source = $row->source;
                        return $row;
                    }),
//                'lead_cars' => DB::table('lead_cars')
//                    ->where('make', 'like', '%'.$searchTerm.'%')
//                    ->orWhere('model', 'like', '%'.$searchTerm.'%')
//                    ->orWhere('color', 'like', '%'.$searchTerm.'%')
//                    ->orWhere('plate_no', 'like', '%'.$searchTerm.'%')
//                    ->orWhere('state', 'like', '%'.$searchTerm.'%')
//                    ->orWhere('vin', 'like', '%'.$searchTerm.'%')
//                    ->orWhere('lot_no', 'like', '%'.$searchTerm.'%')
//                    ->get()
//                    ->map(function ($row) {
//                        $row->table_name = 'lead_cars';
//                        $row->id = $row->lead_car_id;
//                        $row->object_id = $row->lead_id;
//                        $row->title =  'car';
//                        $row->sub_title = 'sub';
//                        $row->status =  "Quote";
////                        $row->contact = $row->shpr_name;
////                        $row->mobile = $row->shpr_number;
////                        $row->email =  $row->shpr_email ;
//                        $row->load =   $row->year  . ' ' . $row->make . ' ' .  $row->model;
////                        $row->created = $row->quoted;
////                        $row->source = $row->source;
//                        return $row;
//                    }),
                'orders' => DB::table('orders')
                    ->where('order_uniq_id', 'like', '%'.$searchTerm.'%')
                    ->orWhere('first_avail', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_name', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_number', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_scnd_number', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_company', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_company_phone', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_mobile', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_fax', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_address', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_scnd_address', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_state', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_city', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_zip', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_country', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_email', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_year', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_make', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_model', 'like', '%'.$searchTerm.'%')
                    ->orWhere(function ($query) use ($searchTerm) {
                        $query->where('car_year', 'like', '%' . $searchTerm . '%')
                            ->orWhere('car_make', 'like', '%' . $searchTerm . '%')
                            ->orWhere('car_model', 'like', '%' . $searchTerm . '%')
                            ->orWhere(DB::raw("CONCAT(car_year, ' ', car_make, ' ', car_model)"), 'like', '%' . $searchTerm . '%');
                    })
                    ->orWhere('car_type', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_color', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_plate_no', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_state', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_vin', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_lot_no', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_additional_info', 'like', '%'.$searchTerm.'%')
                    ->orWhere('origin_city', 'like', '%'.$searchTerm.'%')
                    ->orWhere('origin_state', 'like', '%'.$searchTerm.'%')
                    ->orWhere('origin_zip', 'like', '%'.$searchTerm.'%')
                    ->orWhere('dest_city', 'like', '%'.$searchTerm.'%')
                    ->orWhere('dest_state', 'like', '%'.$searchTerm.'%')
                    ->orWhere('dest_zip', 'like', '%'.$searchTerm.'%')
                    ->orWhere('tariff', 'like', '%'.$searchTerm.'%')
                    ->orWhere('pickup_add1', 'like', '%'.$searchTerm.'%')
                    ->orWhere('pickup_add2', 'like', '%'.$searchTerm.'%')
                    ->orWhere('pickup_city', 'like', '%'.$searchTerm.'%')
                    ->orWhere('pickup_state', 'like', '%'.$searchTerm.'%')
                    ->orWhere('pickup_zip', 'like', '%'.$searchTerm.'%')
                    ->orWhere('pickup_country', 'like', '%'.$searchTerm.'%')
                    ->orWhere('pickup_cont_name', 'like', '%'.$searchTerm.'%')
                    ->orWhere('pickup_comp_name', 'like', '%'.$searchTerm.'%')
                    ->orWhere('pickup_buyer_number', 'like', '%'.$searchTerm.'%')
                    ->orWhere('pickup_phone_1', 'like', '%'.$searchTerm.'%')
                    ->orWhere('pickup_phone_2', 'like', '%'.$searchTerm.'%')
                    ->orWhere('pickup_phone_3', 'like', '%'.$searchTerm.'%')
                    ->orWhere('pickup_mobile', 'like', '%'.$searchTerm.'%')
                    ->orWhere('delivery_add1', 'like', '%'.$searchTerm.'%')
                    ->orWhere('delivery_add2', 'like', '%'.$searchTerm.'%')
                    ->orWhere('delivery_city', 'like', '%'.$searchTerm.'%')
                    ->orWhere('delivery_state', 'like', '%'.$searchTerm.'%')
                    ->orWhere('delivery_zip', 'like', '%'.$searchTerm.'%')
                    ->orWhere('delivery_country', 'like', '%'.$searchTerm.'%')
                    ->orWhere('delivery_cont_name', 'like', '%'.$searchTerm.'%')
                    ->orWhere('delivery_comp_name', 'like', '%'.$searchTerm.'%')
                    ->orWhere('delivery_phone_1', 'like', '%'.$searchTerm.'%')
                    ->orWhere('delivery_phone_2', 'like', '%'.$searchTerm.'%')
                    ->orWhere('delivery_phone_3', 'like', '%'.$searchTerm.'%')
                    ->orWhere('delivery_mobile', 'like', '%'.$searchTerm.'%')
                    ->orWhere('delivery_buyer_number', 'like', '%'.$searchTerm.'%')
                    ->orWhere('load_date', 'like', '%'.$searchTerm.'%')
                    ->orWhere('delivery_date', 'like', '%'.$searchTerm.'%')
                    ->orWhere('driver_firstname', 'like', '%'.$searchTerm.'%')
                    ->orWhere('driver_lastname', 'like', '%'.$searchTerm.'%')
                    ->orWhere('driver_phone', 'like', '%'.$searchTerm.'%')
                    ->orWhere('driver_email', 'like', '%'.$searchTerm.'%')
                    ->orWhere('dispatch_instructions', 'like', '%'.$searchTerm.'%')
                    ->get()
                    ->map(function ($row) use ($company_id) {
                        $row->table_name = 'orders';
                        $row->id = $row->order_id;
                        $row->uuid = $row->order_uniq_id;
                        $row->title =  $company_id  . "From: " . $row->pickup_city . ', ' . $row->pickup_state . ', ' .$row->pickup_zip .  ' To: ' . $row->delivery_city . ', ' . $row->delivery_state . ', ' . $row->delivery_zip;
                        $row->sub_title = $row->shpr_name . ' ' . $row->shpr_email . ' ' . $row->shpr_mobile ;
                        $row->status =  $row->status;
                        $row->contact = $row->shpr_name;
                        $row->mobile = $row->shpr_number;
                        $row->email =  $row->shpr_email ;
                        $row->load =   $row->car_year  . ' ' . $row->car_make . ' ' .  $row->car_model;
                        $row->created = $row->created;
                        $row->source = $row->source;
                        return $row;
                    }),
                'order_cars' => DB::table('order_cars')
                    ->leftJoin('orders', 'orders.order_id', '=', 'order_cars.order_id')
                    ->where(function ($query) use ($searchTerm) {
                        $query->where('order_cars.year', 'like', '%' . $searchTerm . '%')
                            ->orWhere('order_cars.make', 'like', '%' . $searchTerm . '%')
                            ->orWhere('order_cars.model', 'like', '%' . $searchTerm . '%')
                            ->orWhere(DB::raw("CONCAT(order_cars.year, ' ', order_cars.make, ' ', order_cars.model)"), 'like', '%' . $searchTerm . '%');
                    })
                    ->orWhere('order_cars.type', 'like', '%'.$searchTerm.'%')
                    ->orWhere('order_cars.color', 'like', '%'.$searchTerm.'%')
                    ->orWhere('order_cars.plate_no', 'like', '%'.$searchTerm.'%')
                    ->orWhere('order_cars.state', 'like', '%'.$searchTerm.'%')
                    ->orWhere('order_cars.vin', 'like', '%'.$searchTerm.'%')
                    ->orWhere('order_cars.lot_no', 'like', '%'.$searchTerm.'%')
                    ->select(
                        'order_cars.*',
                        'orders.order_uniq_id as order_uniq_id',
                        'orders.pickup_city as order_pickup_city',
                        'orders.pickup_state as order_pickup_state',
                        'orders.pickup_zip as order_pickup_zip',
                        'orders.delivery_city as order_delivery_city',
                        'orders.delivery_state as order_delivery_state',
                        'orders.delivery_zip as order_delivery_zip',
                        'orders.status as order_status',
                        'orders.shpr_name as order_shpr_name',
                        'orders.shpr_number as order_shpr_number',
                        'orders.shpr_email as order_shpr_email',
                        'orders.created as order_created',
                        'orders.source as order_source',
                    )
                    ->get()
                    ->map(function ($row) {
                        $row->table_name = 'orders';
                        $row->id = $row->order_id;
                        $row->object_id = $row->order_uniq_id;
                        $row->title =  "From: " . $row->order_pickup_city . ', ' . $row->order_pickup_state . ', ' .$row->order_pickup_zip .  ' To: ' . $row->order_delivery_city . ', ' . $row->order_delivery_state . ', ' . $row->order_delivery_zip;
                        $row->sub_title = $row->order_shpr_name . ' ' . $row->order_shpr_email . ' ' . $row->order_shpr_number ;
                        $row->status =  $row->order_status;
                        $row->contact = $row->order_shpr_name;
                        $row->mobile = $row->order_shpr_number;
                        $row->email =  $row->order_shpr_email ;
                        $row->load =   $row->year  . ' ' . $row->make . ' ' .  $row->model;
                        $row->created = $row->order_created;
                        $row->source = $row->order_source;
                        return $row;
                    }),
                'quotes' => DB::table('quotes')
                    ->where('shpr_name', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_number', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_scnd_number', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_company', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_company_phone', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_mobile', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_fax', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_address', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_scnd_address', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_state', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_city', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_zip', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_country', 'like', '%'.$searchTerm.'%')
                    ->orWhere('shpr_email', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_year', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_make', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_model', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_type', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_color', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_plate_no', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_state', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_vin', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_lot_no', 'like', '%'.$searchTerm.'%')
                    ->orWhere('car_additional_info', 'like', '%'.$searchTerm.'%')
                    ->orWhere('origin_city', 'like', '%'.$searchTerm.'%')
                    ->orWhere('origin_state', 'like', '%'.$searchTerm.'%')
                    ->orWhere('origin_zip', 'like', '%'.$searchTerm.'%')
                    ->orWhere('dest_city', 'like', '%'.$searchTerm.'%')
                    ->orWhere('dest_state', 'like', '%'.$searchTerm.'%')
                    ->orWhere('dest_zip', 'like', '%'.$searchTerm.'%')
                    ->orWhere('pickup_add1', 'like', '%'.$searchTerm.'%')
                    ->orWhere('pickup_add2', 'like', '%'.$searchTerm.'%')
                    ->orWhere('pickup_cont_name', 'like', '%'.$searchTerm.'%')
                    ->orWhere('pickup_phone_1', 'like', '%'.$searchTerm.'%')
                    ->orWhere('pickup_phone_2', 'like', '%'.$searchTerm.'%')
                    ->orWhere('delivery_add1', 'like', '%'.$searchTerm.'%')
                    ->orWhere('delivery_add2', 'like', '%'.$searchTerm.'%')
                    ->orWhere('delivery_cont_name', 'like', '%'.$searchTerm.'%')
                    ->orWhere('delivery_phone_1', 'like', '%'.$searchTerm.'%')
                    ->orWhere('delivery_phone_2', 'like', '%'.$searchTerm.'%')
                    ->orWhere('tariff', 'like', '%'.$searchTerm.'%')
                    ->get()
                    ->map(function ($row) {
                        $row->table_name = 'quotes';
                        $row->id = $row->quote_id;
                        $row->object_id = $row->quote_uniq_id;
                        $row->title =  "From: " . $row->origin_city . ', ' . $row->origin_state . ', ' .$row->origin_zip .  ' To: ' . $row->dest_city . ', ' . $row->dest_state . ', ' . $row->dest_zip;
                        $row->sub_title = $row->shpr_name . ' ' . $row->shpr_email . ' ' . $row->shpr_number ;
                        $row->status =  "Quote";
                        $row->contact = $row->shpr_name;
                        $row->mobile = $row->shpr_number;
                        $row->email =  $row->shpr_email ;
                        $row->load =   $row->car_year  . ' ' . $row->car_make . ' ' .  $row->car_model;
                        $row->created = $row->quoted;
                        $row->source = $row->source;
                        return $row;
                    }),
                'quote_cars' => DB::table('quote_cars')
                    ->leftJoin('quotes', 'quotes.quote_id', '=', 'quote_cars.quote_id')
                    ->where('make', 'like', '%'.$searchTerm.'%')
                    ->orWhere('model', 'like', '%'.$searchTerm.'%')
                    ->orWhere('color', 'like', '%'.$searchTerm.'%')
                    ->orWhere('plate_no', 'like', '%'.$searchTerm.'%')
                    ->orWhere('state', 'like', '%'.$searchTerm.'%')
                    ->orWhere('vin', 'like', '%'.$searchTerm.'%')
                    ->orWhere('lot_no', 'like', '%'.$searchTerm.'%')
                    ->select(
                        'quote_cars.*',
                        'quotes.quote_uniq_id as quote_uniq_id',
                        'quotes.origin_city as origin_city',
                        'quotes.origin_state as origin_state',
                        'quotes.origin_zip as origin_zip',
                        'quotes.dest_city as dest_city',
                        'quotes.dest_state as dest_state',
                        'quotes.dest_zip as dest_zip',
                        'quotes.shpr_name as shpr_name',
                        'quotes.shpr_number as shpr_number',
                        'quotes.shpr_email as shpr_email',
                        'quotes.quoted as created',
                        'quotes.source as source',
                    )
                    ->get()
                    ->map(function ($row) {
                        $row->table_name = 'quote_cars';
                        $row->id = $row->quote_car_id;
                        $row->object_id = $row->quote_id;
                        $row->title =  "From: " . $row->origin_city . ', ' . $row->origin_state . ', ' .$row->origin_zip .  ' To: ' . $row->dest_city . ', ' . $row->dest_state . ', ' . $row->dest_zip;
                        $row->sub_title = $row->shpr_name . ' ' . $row->shpr_email . ' ' . $row->shpr_number ;
                        $row->status =  "Quote";
                        $row->contact = $row->shpr_name;
                        $row->mobile = $row->shpr_number;
                        $row->email =  $row->shpr_email ;
                        $row->load =   $row->year  . ' ' . $row->make . ' ' .  $row->model;
                        $row->created = $row->created;
                        $row->source = $row->source;
                        return $row;
                    }),
            ];

        }



        return response()->json($results);

    }
}
