<?php

namespace App\Model;

use App\Model\Client\wallet;
use App\Model\Dids;
use Illuminate\Support\Facades\Log;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Config;
use Illuminate\Database\Eloquent\Model;
use App\Model\Client\SmsSetting;
use App\Model\Client\SmsProviders;

use Plivo\Exceptions\PlivoNotFoundException;
use Plivo\Exceptions\PlivoResponseException;
use Plivo\RestClient;
use Twilio\Rest\Client;


class Sms extends Model {

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    public $timestamps = false;
    protected $guarded = ['id'];
    protected $table = 'sms';

    /*
     * Fetch SMS list
     * @param integer $id
     * @return array
     */
    // public function smsDetails(Request $request): array
    // {
    //     $data_row = array();
    //     $clientId = $request->auth->parent_id;
    //     $id = $request->auth->id;
    //     $data['sms_email'] = $id;
    //     $sql = "SELECT * FROM did  WHERE sms_email = :sms_email";
    //     $record = DB::connection("mysql_$clientId")->select($sql, $data);
    //     $response = (array) $record;

    //     if (!empty($response)) {
    //         foreach ($response as $res) {

    //             $did = $res->cli;
    //             $str = "  WHERE did='" . $did . "'";
    //             $sql = "SELECT max(id) as id  FROM " . $this->table . $str . " group by number ";

    //             $sql1 = " Select * from sms where id IN (" . $sql . ") order by date DESC";
    //             $record = DB::connection("mysql_$clientId")->select($sql1, $data);

    //             if (!empty($record)) {
	// 				foreach($record as $k=>$k_val){
	// 					if($k_val->type=='outgoing')
	// 					$record[$k]->message = $record[$k]->message;
	// 				}
    //                 $data_row[] = $record;
    //             }
    //         }
    //     }
    //     $array_result = array_reduce($data_row, 'array_merge', array());

    //     return $this->array_sort($array_result, 'date', SORT_DESC);
    // }
    
public function smsDetails(Request $request): array
{
    $data_row = [];
    $clientId = $request->auth->parent_id;
    $id = $request->auth->id;

    // First query → fetch DID list
    $sqlDid = "SELECT * FROM did WHERE sms_email = :sms_email";
    $recordsDid = DB::connection("mysql_$clientId")->select($sqlDid, [
        'sms_email' => $id,
    ]);

    if (!empty($recordsDid)) {
        foreach ($recordsDid as $res) {
            $did = $res->cli;

            // Subquery → get max id per number for this DID
            $sqlMaxId = "SELECT MAX(id) as id 
                         FROM {$this->table} 
                         WHERE did = :did 
                         GROUP BY number";

            // Main query → fetch SMS by those IDs
            $sqlSms = "SELECT * 
                       FROM sms 
                       WHERE id IN ($sqlMaxId) 
                       ORDER BY date DESC";

            $recordsSms = DB::connection("mysql_$clientId")->select($sqlSms, [
                'did' => $did,
            ]);

            if (!empty($recordsSms)) {
                foreach ($recordsSms as $k => $k_val) {
                    // You had this check, keeping it here
                    if ($k_val->type === 'outgoing') {
                        $recordsSms[$k]->message = $recordsSms[$k]->message;
                    }
                }
                $data_row = array_merge($data_row, $recordsSms);
            }
        }
    }

    // Sort by date DESC (assuming array_sort is a helper you have)
    return $this->array_sort($data_row, 'date', SORT_DESC);
}


    function array_sort($array, $on, $order=SORT_ASC)
    {
        $new_array = array();
        $sortable_array = array();
        if(count($array) > 0)
        {
            foreach($array as $k => $v)
            {
                if (is_array($v))
                {
                    foreach ($v as $k2 => $v2)
                    {
                        if ($k2 == $on)
                        {
                            $sortable_array[$k] = $v2;
                        }
                    }
                }
                else
                {
                    $sortable_array[$k] = $v;
                }
            }
            switch ($order)
            {
                case SORT_ASC:
                asort($sortable_array);
                break;

                case SORT_DESC:
                arsort($sortable_array);
                break;
            }

            foreach ($sortable_array as $k => $v)
            {
                //$new_array[$k] = $array[$k];
                $new_array[] = $array[$k];

            }
        }
        return $new_array;
    }

    public function smsDetailsByDid($request) {
        $data = array();
        $searchStr = array();
        if ($request->has('number') && is_numeric($request->input('number'))) {
            array_push($searchStr, 'number = :number');
            $data['number'] = $request->input('number');
            $data['number1'] = $request->input('number');
        }

        if ($request->has('did') && is_numeric($request->input('did'))) {
            array_push($searchStr, 'did = :did');
            $data['did'] = $request->input('did');
            $data['did1'] = $request->input('did');
        }


        $str = !empty($searchStr) ? "  WHERE " . implode(" AND ", $searchStr) : '';
		$query = "UPDATE " . $this->table . " set status=1 where  (did = :did and number= :number ) or (number = :did1 and did= :number1 )";
        $save_update = DB::connection('mysql_' . $request->auth->parent_id)->update($query, $data);

        $sql = "SELECT * FROM " . $this->table . " where  (did = :did and number= :number ) or (number = :did1 and did= :number1 )  order by id "; //." group by did";
        $record = DB::connection('mysql_' . $request->auth->parent_id)->select($sql, $data);
        $data = (array) $record;

		if($data){
			foreach($data as $key=>$val){
				//if($val->type=='outgoing')
				$data[$key]->message = $val->message;
			}
		}
		//echo '<pre>'.count($data); print_R($data); exit;
        return array(
            'success' => 'true',
            'message' => 'SMS detail.',
            'data' => $data
        );
    }

    public function sendSms(Request $request) {
        Log::info('reached backend sms data',[$request->all()]);
        try {

            $clientId = $request->auth->parent_id;
            $intUserId = $request->auth->id;
            $data = array();
            $searchStr = array();

            $data_array['to'] = $request->to;
            $data_array['from'] =  $request->from;
            $data_array['text'] = $request->message;
            $data_array['mms_url'] = $request->mms_url;

            $get_provider = Dids::on("mysql_$clientId")->where("cli",$request->from)->get()->first();

            $voip_provider = $get_provider->voip_provider;

            if($voip_provider == 'didforsale')
            {
                $sms_setting = SmsProviders::on("mysql_$clientId")->where("status",'1')->where('provider',$voip_provider)->get()->first();
                
                $auth_id = $sms_setting->auth_id;
                $api_key = $sms_setting->api_key;

                $didforsale_sms_url = "https://api.didforsale.com/didforsaleapi/index.php/api/V4/SMS/SingleSend"; 

                $json_data_to_send = json_encode($data_array);
            
                $ch = curl_init();
                curl_setopt($ch, CURLOPT_URL, $didforsale_sms_url);
                curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
                curl_setopt($ch, CURLOPT_POSTFIELDS, $json_data_to_send);
                curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
                curl_setopt($ch, CURLOPT_HTTPHEADER, array("Content-Type: application/json", "Authorization: Basic " . base64_encode("$auth_id:$api_key")));
                $result = curl_exec($ch);
                $res = json_decode($result);
Log::info('result reached',['res'=>$res]);
                 if ($res->status == 'true') {

                //Billing part
                $isFree = $intCharge = $currencyCode = $clientPackageId = NULL;

                $user = new User();
                $user->id = $intUserId;
                $user->parent_id = $clientId;
                $package = $user->getAssignedUserPackage(true);

                if(empty($package)){
                    //No charge for Admin
                    $isFree = 1;
                    $intCharge = 0;
                } else {
                    //Calculate SMS charges
                    if($package->free_sms > 0){
                        $isFree = 1;
                        $intCharge = 0;

                        //Deduct free balance
                        DB::connection('mysql_'.$clientId)->table('user_packages')->where('id',$package->user_package_id)->decrement('free_sms',1);

                    } else {
                        $intCharge = $package->rate_per_sms;
                        $isFree = 0;

                        //Deduct amount from client_xxx.wallet
                        wallet::debitCharge($intCharge, $clientId, $package->currency_code);
                    }
                    $currencyCode = $package->currency_code;
                    $clientPackageId = $package->id;
                }

                $smsObj = new Sms;
                $smsObj->setConnection('mysql_' . $clientId);
                $smsObj->number     = $request->to;
                $smsObj->did        = $request->from;
                $smsObj->message    = $request->message;
                $smsObj->operator   = $voip_provider;
                $smsObj->type       = 'outgoing';
                $smsObj->date       = $request->date;
                $smsObj->extension  = $request->auth->id;
                if(!empty($clientPackageId))
                $smsObj->currency_code = $clientPackageId;
                else
                $smsObj->currency_code = 'USD';
                $smsObj->client_package_id = $currencyCode;
                $smsObj->user_id = $intUserId;
                $smsObj->charge = $intCharge;
                $smsObj->isFree = $isFree;

                if($smsObj->save()){
                    return array(
                            'success' => 'true',
                            'message' => $res->message
                        );
                }
            }
            else if ($res->code == '101') {
                return array(
                    'success' => 'false',
                    'message' => $res->message
                );
            } else if ($res->code == '113') {
                return array(
                    'success' => 'false',
                    'message' => $res->message
                );
            }

            }
else
           if ($voip_provider == 'plivo') {
    $sms_setting = SmsProviders::on("mysql_$clientId")
        ->where("status", '1')
        ->where('provider', $voip_provider)
        ->first();

    $auth_id = $sms_setting->auth_id;
    $api_key = $sms_setting->api_key;

    $client = new RestClient($auth_id, $api_key);

    try {
        // ✅ Check if number is SMS enabled
        $number = $data_array['from']; // sender DID
        $response = $client->numbers->get($number);

        Log::info('Number check response', ['response' => $response]);

        if (isset($response->properties) && isset($response->properties['number'])) {
            $numberDetails = $response->properties;

            // some Plivo accounts return "sms_enabled" instead of "smsEnabled"
            $isSmsEnabled = $numberDetails['smsEnabled'] ?? ($numberDetails['sms_enabled'] ?? false);

            if (!$isSmsEnabled) {
                return response()->json([
                    'success' => false,
                    'message' => "❌ Please enable SMS on number {$number} before sending."
                ]);
            }
        } else {
            return response()->json([
                'success' => false,
                'message' => "❌ Number {$number} not found in your Plivo account."
            ]);
        }

        // ✅ Prepare message payload
        if ($request->has('mms_url')) {
            $result = $client->messages->create([
                "src"        => $data_array['from'],
                "dst"        => $data_array['to'],
                "text"       => $data_array['text'],
                "type"       => "mms",
                "media_urls" => [$data_array['mms_url']]
            ]);
        } else {
            $result = $client->messages->create([
                "src"  => $data_array['from'],
                "dst"  => $data_array['to'],
                "text" => $data_array['text']
            ]);
        }
    if ((is_object($result) && isset($result->statusCode) && $result->statusCode == 202) 
        || (is_array($result) && isset($result['message_uuid']))) 
    {
        // Billing
        $isFree = $intCharge = $currencyCode = $clientPackageId = null;

        $user = new User();
        $user->id = $intUserId;
        $user->parent_id = $clientId;
        $package = $user->getAssignedUserPackage(true);

        if (empty($package)) {
            $isFree = 1;
            $intCharge = 0;
        } else {
            if ($package->free_sms > 0) {
                $isFree = 1;
                $intCharge = 0;

                DB::connection('mysql_' . $clientId)
                    ->table('user_packages')
                    ->where('id', $package->user_package_id)
                    ->decrement('free_sms', 1);
            } else {
                $intCharge = $package->rate_per_sms;
                $isFree = 0;

                Wallet::debitCharge($intCharge, $clientId, $package->currency_code);
            }
            $currencyCode = $package->currency_code;
            $clientPackageId = $package->id;
        }

        $smsObj = new Sms;
        $smsObj->setConnection('mysql_' . $clientId);
        $smsObj->number     = $request->to;
        $smsObj->did        = $request->from;
        $smsObj->message    = $request->message;
        $smsObj->operator   = $voip_provider;
        $smsObj->type       = 'outgoing';
        $smsObj->date       = $request->date;
        $smsObj->extension  = $request->auth->id;
        $smsObj->currency_code = $currencyCode ?? 'USD';
        $smsObj->client_package_id = $clientPackageId;
        $smsObj->user_id    = $intUserId;
        $smsObj->charge     = $intCharge;
        $smsObj->isFree     = $isFree;
         $smsObj->save();
}

        return response()->json([
            'success' => true,
            'message' => '✅ SMS sent successfully!',
            'data'    => $result
        ]);

    } catch (PlivoResponseException $e) {
        // Plivo-specific error (like "not found")
        Log::error('Plivo API error: ' . $e->getMessage());

        return response()->json([
            'success' => false,
            'message' => "❌ Plivo error: " . $e->getMessage()
        ], 400);

    } catch (\Exception $e) {
        // Any other error
        Log::error('General Plivo error: ' . $e->getMessage());

        return response()->json([
            'success' => false,
            'message' => "❌ Something went wrong while sending SMS. Try again later."
        ], 500);
    }


    // ✅ Prepare message payload
    // if ($request->has('mms_url')) {
    //     $result = $client->messages->create([
    //         "src"        => $data_array['from'],
    //         "dst"        => $data_array['to'],
    //         "text"       => $data_array['text'],
    //         "type"       => "mms",
    //         "media_urls" => [$data_array['mms_url']]
    //     ]);
    // } else {
    //     $result = $client->messages->create([
    //         "src"  => $data_array['from'],
    //         "dst"  => $data_array['to'],
    //         "text" => $data_array['text']
    //     ]);
    // }

    // Log::info('Plivo send result', ['result' => $result]);

    // ✅ Success check (depends on SDK version)
    

        // if ($smsObj->save()) {
        //     return [
        //         'success' => true,
        //         'message' => "SMS has been sent successfully on ({$request->to})"
        //     ];
        // }
    //}
}

            else
            if($voip_provider == 'telnyx')
            {

                if (app()->environment() == "local") 
                {

                   
                    $response_id = true;

                }

                else
                {
                $sms_setting = SmsProviders::on("mysql_$clientId")->where("status",'1')->where('provider',$voip_provider)->get()->first();
                //$auth_id = $sms_setting->auth_id;
                $api_key = $sms_setting->api_key;


                //$telnyxApiKey = 'KEY018C5C0A3935A6E6A770627FDE9749FB_Wd0H3Ktw0S3uJ3xbIXhr51';
                //$api_key = $telnyxApiKey;

                $telnyxApiEndpoint = 'https://api.telnyx.com/v2/messages';

                if($request->has('mms_url'))
                {
                    $data = array('from' => '+'.$request->from, 'to' => '+'.$request->to, 'subject' => 'Picture' , 'text' => $request->message, 'media_urls' => [$request->mms_url]);
                }
                else
                {
                $data = array('from' => '+'.$request->from, 'to' => '+'.$request->to, 'text' => $request->message);

                }


                $ch = curl_init();
                curl_setopt($ch, CURLOPT_URL, $telnyxApiEndpoint);
                curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
                curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'POST');
                curl_setopt($ch, CURLOPT_HTTPHEADER, [
                    'Content-Type: application/json',
                    'Authorization: Bearer '.$api_key,
                ]);
                curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($data));

                $response = curl_exec($ch);

                curl_close($ch);
                $json_decode = json_decode($response);
                $response_id = $json_decode->data->id;

            }


            if(!empty($response_id))
            {
                //Billing part
                $isFree = $intCharge = $currencyCode = $clientPackageId = NULL;

                $user = new User();
                $user->id = $intUserId;
                $user->parent_id = $clientId;
                $package = $user->getAssignedUserPackage(true);

                if(empty($package)){
                    //No charge for Admin
                    $isFree = 1;
                    $intCharge = 0;
                } else {
                    //Calculate SMS charges
                    if($package->free_sms > 0){
                        $isFree = 1;
                        $intCharge = 0;

                        //Deduct free balance
                        DB::connection('mysql_'.$clientId)->table('user_packages')->where('id',$package->user_package_id)->decrement('free_sms',1);

                    } else {
                        $intCharge = $package->rate_per_sms;
                        $isFree = 0;

                        //Deduct amount from client_xxx.wallet
                        wallet::debitCharge($intCharge, $clientId, $package->currency_code);
                    }
                    $currencyCode = $package->currency_code;
                    $clientPackageId = $package->id;
                }


                $smsObj = new Sms;
                $smsObj->setConnection('mysql_' . $clientId);
                $smsObj->number     = $request->to;
                $smsObj->did        = $request->from;
                $smsObj->message    = $request->message;
                $smsObj->operator   = $voip_provider;
                $smsObj->type       = 'outgoing';
                if($request->has('mms_url'))
                {
                    $smsObj->sms_type       = 1;
                    $smsObj->mms_url       = $request->mms_url;

                }

                $smsObj->date       = $request->date;
                $smsObj->extension  = $request->auth->id;
                if(!empty($clientPackageId))
                $smsObj->currency_code = $clientPackageId;
                else
                $smsObj->currency_code = 'USD';
                $smsObj->client_package_id = $currencyCode;
                $smsObj->user_id = $intUserId;
                $smsObj->charge = $intCharge;
                $smsObj->isFree = $isFree;

                if($smsObj->save()){
                    return array(
                            'success' => 'true',
                            'message' => "SMS has been sent successfully on (".$request->to.")"
                        );
                    }
                }

            }

            else
            if($voip_provider == 'twilio')
            {

                // if (app()->environment() == "local") 
                // {

                   
                //     $response_id = true;

                // }

                // else
                // {


                $sms_setting = SmsProviders::on("mysql_$clientId")->where("status",'1')->where('provider',$voip_provider)->get()->first();
                //$auth_id = $sms_setting->auth_id;
                $api_key = $sms_setting->api_key;

                $auth_id = $sms_setting->auth_id;

                $auth_token = $api_key;
                $account_sid = $auth_id;
          $twilioNumber = $data_array['from']; // your sender DID
$client = new \Twilio\Rest\Client($account_sid, $auth_token);

$incomingPhoneNumber = $client->incomingPhoneNumbers
    ->read(["phoneNumber" => $twilioNumber], 1);

Log::info('Number check response', ['incomingPhoneNumber' => $incomingPhoneNumber]);

if (!empty($incomingPhoneNumber)) {
    $capabilities = $incomingPhoneNumber[0]->capabilities;

    // SMS enabled check
    if ($capabilities->sms !== true) {
        return response()->json([
            'success' => false,
            'message' => "❌ Please enable SMS on number {$twilioNumber} before sending."
        ]);
    }

    // SMS is enabled, continue sending
} else {
    return response()->json([
        'success' => false,
        'message' => "❌ Number {$twilioNumber} not found in your Twilio account."
    ]);
}

                if($request->has('mms_url'))
                {
                    $data = array('from' => '+'.$request->from, 'to' => $request->to, 'body' => $request->message, 'mediaUrl' => [$request->mms_url]);
                }
                else
                {
                    $data = array('from' => '+'.$request->from, 'to' => $request->to, 'body' => $request->message);
                }

               // return $data;


$twilio_number = '+'.$request->from;
$to_number = $request->to;
$response_twilio = $client->messages->create(
    $to_number,$data
);


$response_id = $response_twilio->sid;

            //}

           /// return $request->mms_url.'-'.$response_twilio->sid;


            if(!empty($response_id))
            {
                //Billing part
                $isFree = $intCharge = $currencyCode = $clientPackageId = NULL;

                $user = new User();
                $user->id = $intUserId;
                $user->parent_id = $clientId;
                $package = $user->getAssignedUserPackage(true);

                if(empty($package)){
                    //No charge for Admin
                    $isFree = 1;
                    $intCharge = 0;
                } else {
                    //Calculate SMS charges
                    if($package->free_sms > 0){
                        $isFree = 1;
                        $intCharge = 0;

                        //Deduct free balance
                        DB::connection('mysql_'.$clientId)->table('user_packages')->where('id',$package->user_package_id)->decrement('free_sms',1);

                    } else {
                        $intCharge = $package->rate_per_sms;
                        $isFree = 0;

                        //Deduct amount from client_xxx.wallet
                        wallet::debitCharge($intCharge, $clientId, $package->currency_code);
                    }
                    $currencyCode = $package->currency_code;
                    $clientPackageId = $package->id;
                }


                $smsObj = new Sms;
                $smsObj->setConnection('mysql_' . $clientId);
                $smsObj->number     = $request->to;
                $smsObj->did        = $request->from;
                $smsObj->message    = $request->message;
                $smsObj->operator   = $voip_provider;
                $smsObj->type       = 'outgoing';
                if($request->has('mms_url'))
                {
                    $smsObj->sms_type       = 1;
                    $smsObj->mms_url       = $request->mms_url;

                }

                $smsObj->date       = $request->date;
                $smsObj->extension  = $request->auth->id;
                if(!empty($$clientPackageId))
                $smsObj->currency_code = $clientPackageId;
                else
                $smsObj->currency_code = 'USD';
                $smsObj->client_package_id = $currencyCode;
                $smsObj->user_id = $intUserId;
                $smsObj->charge = $intCharge;
                $smsObj->isFree = $isFree;

                if($smsObj->save()){
                    return array(
                            'success' => 'true',
                            'message' => "SMS has been sent successfully on (".$request->to.")"
                        );
                    }
                }

            }


            //}
        } 
catch (Exception $e) {
    // Log full details
    Log::error('SMS sending failed', [
        'exception_class' => get_class($e),
        'message'         => $e->getMessage(),
        'trace'           => $e->getTraceAsString(),
        'previous'        => $e->getPrevious() ? $e->getPrevious()->getMessage() : null,
    ]);

    // Default user message
    $userMessage = $e->getMessage();

    // Check for Plivo "not found" error
    if (stripos($e->getMessage(), 'not found') !== false || 
        ($e->getPrevious() && stripos($e->getPrevious()->getMessage(), 'not found') !== false)) {
        $userMessage = "❌ Number not found in your Plivo account.";
    }

    return response()->json([
        'success' => false,
        'message' => $userMessage
    ], 404);
}

 catch (InvalidArgumentException $e) {
    Log::info('Invalid argument in SMS sending: ' . $e->getMessage(), [
        'trace' => $e->getTraceAsString()
    ]);
   $userMessage = $e->getMessage();

    // Check for Plivo "not found" error
    if (stripos($e->getMessage(), 'not found') !== false || 
        ($e->getPrevious() && stripos($e->getPrevious()->getMessage(), 'not found') !== false)) {
        $userMessage = "❌ Number not found in your Plivo account.";
    }

    return response()->json([
        'success' => false,
        'message' => $userMessage
    ], 400);
}

    }

    function nexmoAPI($from, $to, $message) {
        $url = 'https://rest.nexmo.com/sms/json?' . http_build_query([
                    'api_key' => 'f8540474',
                    'api_secret' => 'oCZAngCfCdjM9NNO',
                    'to' => $to,
                    'from' => $from,
                    'text' => $message
        ]);

        $ch = curl_init($url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        $response = curl_exec($ch);
    }

    /*
    public function saveSms($to, $from, $message, $operator, $type, $parent_id, $request) {
        if (!empty($to)) {
            $data['extension'] = $request;
            $data['number'] = trim($to);
            $data['did'] = trim($from);
            $data['message'] = $message;
            $data['operator'] = $operator;
            $data['type'] = $type;
            $query = "INSERT INTO " . $this->table . " (extension, number, did, message, operator, type) VALUE (:extension, :number, :did, :message, :operator, :type)";
            $add = DB::connection('mysql_' . $parent_id)->insert($query, $data);
            if ($add == 1) {

                return array(
                    'success' => 'true',
                    'message' => 'Sms Send successfully.'
                );
            } else {
                return array(
                    'success' => 'false',
                    'message' => 'Sms are not Send successfully.'
                );
            }
        }
    }
    */

    function getSmsCountDetails(Request $request) {
        $data = [
            'incoming' => 0,
            'outgoing' => 0
        ];

        $sql = "SELECT count(1) as rowCount, type FROM " . $this->table . "  group by type ";
        $record = DB::connection('mysql_' . $request->auth->parent_id)->select($sql);
        $response = (array) $record;
        foreach ($response as $res) {

            $data[$res->type] = $res->rowCount;
        }
        return array(
            'success' => 'true',
            'message' => 'SMS count',
            'data' => $data
        );
    }


     function getUnreadSmsOpenAI($request) {
        try {
            $data = array();
            $parent_id = $request->auth->parent_id;


            if ($request->has('id') && $request->input('id')) {
                $data['sms_email'] = $request->input('id');
                $sql = "SELECT * FROM did  WHERE sms_email = :sms_email";
                $record = DB::connection('mysql_' . $request->auth->parent_id)->select($sql, $data);
                $response = (array) $record;
            }

            $did = array();
            if (!empty($response)) {

                foreach ($response as $res) {

                    // echo "<pre>";print_r($res);die;

                    $did[] = $res->cli;
                }
            }

            if(empty($did)){
                return array(
                    'success' => 'false',
                    'message' => 'No Sms Count',
                    'data' => $data
                );
            }

            $did_all = implode(',', $did);
            if (is_numeric($parent_id)) {
                $data = array();
                $sql = "SELECT count(1) as rowCount,type,number as num FROM " . $this->table . " where status='0' and type='incoming' and did IN (" . $did_all . ") group by number";
                $record = DB::connection('mysql_' . $request->auth->parent_id)->select($sql);
                $response = (array) $record;
                foreach ($response as $key => $res) {
                    if($res->num == '14168362235')
                    {
                    $data[$res->num]['countRow'] = $res->rowCount;
                    $data[$res->num]['number'] = $res->num;
                        
                    }


                }
                if (!empty($data)) {
                    return array(
                        'success' => 'true',
                        'message' => 'SMS count',
                        'data' => $data
                    );
                } else {

                    return array(
                        'success' => 'false',
                        'message' => 'No Sms Count',
                        'data' => $data
                    );
                }
            }
        } catch (Exception $e) {
            Log::log($e->getMessage());
        } catch (InvalidArgumentException $e) {
            Log::log($e->getMessage());
        }
    }

    function getUnreadSms($request) {
        try {
            $data = array();
            $parent_id = $request->auth->parent_id;


            if ($request->has('id') && $request->input('id')) {
                $data['sms_email'] = $request->input('id');
                $sql = "SELECT * FROM did  WHERE sms_email = :sms_email";
                $record = DB::connection('mysql_' . $request->auth->parent_id)->select($sql, $data);
                $response = (array) $record;
            }

            $did = array();
            if (!empty($response)) {

                foreach ($response as $res) {

                    // echo "<pre>";print_r($res);die;

                    $did[] = $res->cli;
                }
            }

            if(empty($did)){
                return array(
                    'success' => 'false',
                    'message' => 'No Sms Count',
                    'data' => $data
                );
            }

            $did_all = implode(',', $did);
            if (is_numeric($parent_id)) {
                $data = array();
                $sql = "SELECT count(1) as rowCount,type FROM " . $this->table . " where status='0' and type='incoming' and did IN (" . $did_all . ")";
                $record = DB::connection('mysql_' . $request->auth->parent_id)->select($sql);
                $response = (array) $record;
                foreach ($response as $res) {
                    $data['countRow'] = $res->rowCount;
                }
                if (!empty($data)) {
                    return array(
                        'success' => 'true',
                        'message' => 'SMS count',
                        'data' => $data
                    );
                } else {

                    return array(
                        'success' => 'false',
                        'message' => 'No Sms Count',
                        'data' => $data
                    );
                }
            }
        } catch (Exception $e) {
            Log::log($e->getMessage());
        } catch (InvalidArgumentException $e) {
            Log::log($e->getMessage());
        }
    }

    public function smsDetailsByDidRecent($request) {
        $data = array();
        $searchStr = array();
        if ($request->has('number') && is_numeric($request->input('number'))) {
            array_push($searchStr, 'number = :number');
            $data['number'] = $request->input('number');
            $data['number1'] = $request->input('number');
        }

        if ($request->has('did') && is_numeric($request->input('did'))) {
            array_push($searchStr, 'did = :did');
            $data['did'] = $request->input('did');
            $data['did1'] = $request->input('did');
        }

        $str = !empty($searchStr) ? "  WHERE " . implode(" AND ", $searchStr) : '';

        $query = "UPDATE " . $this->table . " set status=1 where  (did = :did and number= :number ) or (number = :did1 and did= :number1 )";
        $save_update = DB::connection('mysql_' . $request->auth->parent_id)->update($query, $data);
        $data['last_id'] = $request->input('last_id');
       $sql = "SELECT * FROM " . $this->table . " where id > :last_id and ((did = :did and number= :number ) or (number = :did1 and did= :number1 ) )  order by id "; //." group by did";
        //$sql = "SELECT * FROM ".$this->table;
        $record = DB::connection('mysql_' . $request->auth->parent_id)->select($sql, $data);
        $data = (array) $record;

        return array(
            'success' => 'true',
            'message' => 'SMS detail.',
            'data' => $data
        );
    }

   function smsDidList(Request $request) {
    $clientId = $request->auth->parent_id;
    $id = $request->auth->id;

    $response = Dids::on('mysql_' . $clientId)
        ->where([["sms_email", '=', $id], ["sms", '=', 1]])
        ->get(['cli', 'voip_provider']);  // fetch both columns

    return $response; // returns array of objects [{cli: ..., voip_provider: ...}, ...]
}


    function smsDidListCRM(Request $request){
        $clientId = $request->auth->parent_id;
        $id = $request->auth->id;
        $response = Dids::on('mysql_' . $clientId)->where([["sms_email",'=',$id],["sms",'=',1]])->get('cli')->all();

        return array(
            'success' => 'true',
            'message' => 'SMS Numbers.',
            'data' => $response
        );
    }

}