<?php /** @noinspection SqlNoDataSourceInspection */

namespace App\Console\Commands;

use App\Exceptions\ErrorReporting;
use App\Jobs\SendLowBalanceAlertToClient;
use App\Jobs\SendLowBalanceAlertToSupport;
use App\Jobs\SendSimpleBulkSms;
use App\Jobs\SendSms;
use App\Tools\ServiceTools;
use App\Tools\Sms;
use App\Tools\SmsTools;
use App\UserJob;
use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;

class SpeedyBulkSms extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'bulksms:start';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Send Bulk SMS asynchronously';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
        $this->log_path = storage_path('logs' . DIRECTORY_SEPARATOR . 'speedy_sms_bulk.log');
    }


    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $this->check_for_stop_flag();
        $black_list = ['clicksys', 'MISSION', 'GMLS'];
        $this->info(Carbon::now().' | Checking for bulk blast.....');
        DB::table('user_jobs')
            ->where(function ($query){
                $query->where('status', 'pending')->orWhere('status', 'stopped');
            })
            ->whereIn('type', ['bulk', 'group'])
            ->where('sched_datetime', '<=', Carbon::now()->toDateTimeString())
            ->select(['id', 'username'])
            ->orderBy('id')->chunk(100, function ($jo) use (&$black_list) {
                $this->info('Fetched ' . count($jo) . ' pending job(s)');
                foreach ($jo as $j) {
                    $tempUC = strtoupper($j->username);
                    $this->info('Processing bulksms from ' . $j->username .'.....');
                    if(in_array($j->username, $black_list) || in_array($tempUC, $black_list)) {
                        DB::table('user_jobs')
                            ->where('id', $j->id)
                            ->update([
                                'status' => 'skipped'
                            ]);
                        continue;
                    }
                    $this->check_for_stop_flag();
                    $this->info('Executing Simple Bulk SMS!');
                    try {
                        DB::table('user_jobs')
                            ->where('id', $j->id)
                            ->update([
                                'status' => 'starting',
                                'task_started_date' => Carbon::now()->toDateTimeString()
                            ]);
//                        SendSimpleBulkSms::dispatch($j->id)->onConnection('redis')->onQueue('bulksms');


                        $user_j = UserJob::find($j->id);
                        $job = &$user_j;
                        if(empty($job)) {
                            $this->error_log('Job Not Found! ' .$j->id);
                            return;
                        }
                        //check for esme users
                        $tmp = DB::select('select r.name as role_name from users u left join roles r on u.role_id=r.id where u.id=?',[$job->user_id]);
                        if(empty($tmp) || empty($tmp[0]) || $tmp[0]->role_name=='esme') {
                            $this->error_log('Only normal users are allowed to send blast!');
                            return;
                        }

                        if($job->status !== 'starting') {
                            $this->error('Job '.$job->name.' has already started!');
                            return;
                        }
                        $job->status = 'started';
                        $job->task_started_date = Carbon::now()->toDateTimeString();
                        $job->task_completed_date = null;
                        $job->save();
                        $path = $job->file_path;
                        $this->info(Carbon::now()->toDateTimeString() . '| Reading uploaded file.....');
                        if (!empty($path) && file_exists($path)) {
                            //load record from file to redis
                            try {
                                Excel::load($path, function ($reader) use ($job) {
                                    $objExcel = $reader->getExcel();
                                    $sheet = $objExcel->getSheet(0);
                                    $highestRow = $sheet->getHighestRow();
                                    $highestColumn = $sheet->getHighestColumn();

                                    //computing
                                    $nummsisdn = 0;
                                    $job_id = $job->id;
                                    $row_number = (int)(empty($job->row_number)?0:$job->row_number);
                                    $flash = $job->flash;
                                    $sender = $job->sender;
                                    $message = preg_replace(array('/\r\n/'), array("\n"), trim($job->message));

                                    //  Loop through each row of the worksheet in turn
                                    //save msg sent to msisdn, to prevent dupplicate
                                    $processed_msisdn = [];
                                    $total_amount = 0.0;

                                    $this->info(Carbon::now()->toDateTimeString() . '| Sending Bulk SMS Now...');

                                    $q1 = "SELECT new_bal, username FROM credits WHERE user_id = '{$job->user_id}'";
                                    $result11 = DB::select($q1);
                                    $curr_bal = $result11[0]->new_bal;

                                    for ($row = 1; $row <= $highestRow; $row++) {
                                        if($row <= $row_number) continue;
                                        try {
                                            //check stop service flag
                                            if (!ServiceTools::can_run_service()) {
                                                //update job status
                                                $job->progress = (double)(100 * $row) / $highestRow;
                                                $job->row_number = $row;
                                                $job->status = 'stopped';
                                                $job->save();
                                                $this->info('Service Stopped By User!');
                                                exit(1);
                                            }
                                            //commit every 10% of progress
                                            $progress = (double)(100 * $row) / $highestRow;
                                            if ($row > 1 && $progress % 10 === 0) {
                                                $job->progress = $progress;
                                                $job->row_number = $row;
                                                $job->save();
                                            }
                                            //  Read a row of data into an array
                                            $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
                                            $number = $rowData[0][0];
                                            $msisdn = SmsTools::msisdn_prep(preg_replace('/\D/', '', $number));
                                            $msisdn = trim($msisdn);
                                            $msisdn = SmsTools::format_phone_number($msisdn);

                                            if (is_numeric($msisdn) && !empty($msisdn)) {
                                                //check for duplicate
                                                if ($job->remove_duplicate && in_array($msisdn, $processed_msisdn)) {
                                                    continue;
                                                }

                                                //prepare sms to be sent
                                                $sms = new Sms();
                                                $sms->job_id = $job_id;
                                                $sms->user_id = $job->user_id;
                                                $sms->sender = $sender;
                                                $sms->msisdn = $msisdn;
                                                $sms->message = $message;
                                                $sms->flash = $flash;
                                                $sms->log = $this->log_path;

                                                $cost = $this->get_cost($msisdn, $message, $job->user_id);
                                                $total_amount += $cost;


                                                if($curr_bal < $total_amount) {
                                                    $job->status = 'low_balance';
                                                    $job->progress = $progress;
                                                    $job->row_number = $row -1;
                                                    $job->save();
                                                    $this->info('Client has low balance!');

                                                    SendLowBalanceAlertToClient::dispatch($job->user, $job)->onConnection('redis')->onQueue('notification');
                                                    SendLowBalanceAlertToSupport::dispatch($job->user, $job)->onConnection('redis')->onQueue('notification');

                                                    exit(0);
                                                }

                                                //TODO: check that user has more credit to send that sms otherwise set job_status to low_balance and abort blast

//                                                SendSms::dispatch($sms)->onConnection('redis')->onQueue('bulksms');
                                                $this->doJob($msisdn, $sender, $message,$job->user_id,$job->id,$flash);

                                                $nummsisdn++;

                                                array_push($processed_msisdn, $msisdn);
                                            } else {
                                                continue;
                                            }

                                        } catch (\PDOException $pdoex){
                                            $this->info('Database Error: '.$pdoex->getMessage().'. The Cron Process has been terminated!');
                                            exit(3);
                                        } catch (\Throwable $e) {
                                            $this->error('Application Error: '.$e->getMessage());
                                        }
                                        $row_number++;
                                        //sleep for 25 milliseconds to prevent Lock wait timeout exceeded error on database
                                        usleep(25000);
                                    }

                                    $job->progress = 100;
                                    $job->row_number = $highestRow;
                                    $job->save();

                                    $this->info($nummsisdn . ' sms sent for this campaign');
                                });
                            } catch (\Exception $e)
                            {
                                $this->error($e->getMessage());
                                ErrorReporting::report($e);
                            }
                        } else {
                            $this->error_log('File Not Found');
                        }

                        //mark job as complete
                        $job->status = 'done';
                        $job->task_completed_date = Carbon::now()->toDateTimeString();
                        $job->save();
                        $this->info('Task Complete!');
                        sleep(3);
                    } catch (\Exception $e) {
                        $this->error_log($e->getMessage());
//                        ErrorReporting::report($e);
                    }
                }
                $this->info('All the job(s) have been started!');
                sleep(1);
            });
    }


    private function doJob( $numb, $source, $message,$user_id,$job_id,$flash)
    {
        $this->info('doing the job');
        try {
            if ($source == 'STANBICGH' || $source == 'STANBIC') {
                $source = 'Stanbic';
            }
            if ($numb == '233123456789') {
                exit;
            }

            if (substr($numb, 0, 3) == '024' | substr($numb, 0, 3) == '054' |
                substr($numb, 0, 3) == '055' | substr($numb, 0, 3) == '026' |
                substr($numb, 0, 3) == '056' | substr($numb, 0, 3) == '020' |
                substr($numb, 0, 3) == '050' | substr($numb, 0, 3) == '027' |
                substr($numb, 0, 3) == '057' | substr($numb, 0, 3) == '023') {
                $remove = substr($numb, 1);
                $numb = '233' . $remove;
            }

            $sender = $source;
            $msgs = $message;
            $submitdt = date('Y-m-d H:i:s');
            $destination = explode(',', $numb);
            $no_chars = strlen($msgs);
            $no_pages = $this->no_pages($no_chars);
            $sms_count = $no_pages;
            $cost = 0.0;
            $resellerCost = 0.0;
            $msisdn = preg_replace('/\D/', '', $numb);


            if (is_numeric($msisdn)) {
                if (substr($msisdn, 0, 1) == '1') {
                    $code = substr($msisdn, 0, 1);
                    $ccLength = strlen($code);
                } elseif (substr($msisdn, 0, 1) == '9') {
                    $code = substr($msisdn, 0, 1);
                    $ccLength = strlen($code);
                } elseif (substr($msisdn, 0, 2) == '44') {
                    $code = substr($msisdn, 0, 2);
                    $ccLength = strlen($code);
                } elseif (substr($msisdn, 0, 2) == '27') {
                    $code = substr($msisdn, 0, 2);
                    $ccLength = strlen($code);
                } else {
                    $code = substr($msisdn, 0, 3);
                    $ccLength = strlen($code);
                }
                $standard_len = $ccLength + $this->checkCode($code);
                $new_len = strlen($msisdn);
                if ($standard_len === $new_len) {
                    $userNetwork = $this->get_user_sms_network($user_id, $msisdn);
                    $cost += ($no_pages * $userNetwork['sms_price']);
                    if (!empty($resellerId)) {
                        $resellerNetwork = $this->get_user_sms_network($resellerId, $msisdn);
                        $resellerCost += ($no_pages * $resellerNetwork['sms_price']);
                    }
                }
            }
            $resellerProfit = 0.0;
            if (!empty($resellerId) && !empty($resellerCost)) {
                $resellerProfit = $cost - $resellerCost;
            }


            $sql = "SELECT id, created_by FROM users WHERE id='{$user_id}' LIMIT 1";
            $result = DB::select($sql);
            $user_array = $result[0];
            $created_by = $user_array->created_by;
            $resellerId = 0;
            if ($created_by != 'npontu') {
                $resSub = DB::select("SELECT id FROM users WHERE username='$created_by' LIMIT 1");
                if (!empty($resSub) && count($resSub) > 0) {
                    $resel_array = $resSub[0];
                    $resellerId = $resel_array->id;
                }
            }

            // no of recepients
            $tot_cost = $cost;
            $q1 = "SELECT new_bal, username FROM credits WHERE user_id = '{$user_id}'";
            $result = DB::select($q1);
            $detail = $result[0];

            $curr_bal = $detail->new_bal;
            $user_sess = $detail->username;
            $myIds = $user_id;


            if ($curr_bal >= $tot_cost) {
                $count = 0;
                $number = $destination[$count];
                $msisdn = preg_replace('/\D/', '', $number);
                $id = uniqid('NS', true);

                if (is_numeric($msisdn)) {

                    if (substr($numb, 0, 1) == '1' || substr($numb, 0, 1) == '9') {
                        $code = substr($numb, 0, 1);
                        $ccLength = strlen($code);
                    } elseif (substr($numb, 0, 2) == '44' || substr($numb, 0, 2) == '96'
                        || substr($numb, 0, 2) == '64' || substr($numb, 0, 2) == '27') {
                        $code = substr($numb, 0, 2);
                        $ccLength = strlen($code);
                    } else {
                        $code = substr($numb, 0, 3);
                        $ccLength = strlen($code);
                    }

                    $network = $this->which_network($msisdn);

                    $standard_len = $ccLength + $this->checkCode($code);

                    $new_len = strlen($msisdn);

                    if ($user_sess == 'stanbicgh' || $user_sess == 'Sammy') {
                        $userNetwork = $this->get_user_sms_network($user_id, $msisdn);
                        $resp = $this->sendsms($id, $job_id, $user_sess, $msisdn, $sender, $sms_count, $userNetwork, $msgs, $submitdt, $created_by, $myIds,$flash);
                        $this->info($user_sess . ' | ' . $numb . ' | ' . $source . ' | ' . $id . ' | ' . $resp);

                    } else {
                        if ($standard_len !== $new_len && $code !== '64') {
                            $sql = "INSERT INTO logs(id, job_id,user_id, username, msisdn, sender, sms_count, network, message,submit_date, status,created_by,response,originated,created_at)
                    VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                            DB::insert($sql, [$id, $job_id, $myIds, $user_sess, $msisdn, $sender, $sms_count, $network, $msgs, $submitdt, 'FAILED', $created_by, '', 'guiuser', date('Y-m-d H:i:s')]);
                            $this->error($user_sess . ' | ' . $numb. ' | ' . $source . ' | Invalid Destination');
                            return;
                        } else {
                            $userNetwork = $this->get_user_sms_network($user_id, $msisdn);
                            $resp = $this->sendsms($id, $job_id, $user_sess, $msisdn, $sender, $sms_count, $userNetwork, $msgs, $submitdt, $created_by, $myIds,$flash);
                            $this->info($user_sess . ' | ' . $numb . ' | ' . $source . ' | ' . $id . ' | ' . $resp);
                        }
                    }
                }

                DB::update("UPDATE credits SET credit_used = credit_used + '$tot_cost', new_bal = new_bal - '$tot_cost' WHERE username = ?", [$user_sess]);
                //update reseller profit
                if (!empty($resellerId) && $resellerProfit > 0) {
                    DB::update("UPDATE credits SET credit_used = credit_used - '$resellerProfit', new_bal = new_bal + '$resellerProfit' WHERE user_id = '$resellerId'");
                }
            }
        } catch (\Throwable $t) {
            $this->error($t->getMessage());
            ErrorReporting::report($t);
        }
    }


    private function check_for_stop_flag()
    {
        //check stop service flag
        if(!ServiceTools::can_run_service()) {
            $this->info('Service Stopped By User!');
            exit(1);
        }
    }

    private function get_cost($numb, $msgs, $user_id) {
        $no_chars = strlen($msgs);
        $no_pages = $this->no_pages($no_chars);
        $cost = 0.0;
        $msisdn = preg_replace('/\D/', '', $numb);

        if (is_numeric($msisdn)) {
            if (substr($msisdn, 0, 1) == '1') {
                $code = substr($msisdn, 0, 1);
                $ccLength = strlen($code);
            } elseif (substr($msisdn, 0, 1) == '9') {
                $code = substr($msisdn, 0, 1);
                $ccLength = strlen($code);
            } elseif (substr($msisdn, 0, 2) == '44') {
                $code = substr($msisdn, 0, 2);
                $ccLength = strlen($code);
            } elseif (substr($msisdn, 0, 2) == '27') {
                $code = substr($msisdn, 0, 2);
                $ccLength = strlen($code);
            } else {
                $code = substr($msisdn, 0, 3);
                $ccLength = strlen($code);
            }
            $standard_len = $ccLength + $this->checkCode($code);
            $new_len = strlen($msisdn);
            if ($standard_len === $new_len) {
                $userNetwork = $this->get_user_sms_network($user_id, $msisdn);
                $cost += ($no_pages * $userNetwork['sms_price']);
            }
        }

        return $cost;
    }

    private function get_user_sms_network($userId, $msisdn) {
        $sms_network = [];
        $query = "SELECT * FROM networks";
        $res = DB::select($query);
        if (!empty($res) && count($res)>0) {
            foreach ($res as $network) {
                $prefixes = explode(',', $network->prefix);
                foreach ($prefixes as $prefix) {
                    $prefix = trim($prefix);
                    if (strpos($msisdn, $prefix) === 0) {
                        $network_id = $network->id;
                        $country_id = $network->country_id;
                        $query = "SELECT * from user_networks where user_id='$userId' and network_id='$network_id' and country_id='$country_id' LIMIT 0,1";
                        $res2 = DB::select($query);
                        if (!empty($res2) && count($res2)>0) {
                            $usr_price = $res2[0];
                            $sms_network = ['network_id' => $network_id, 'network' => $network->nice_name, 'sms_price' => $usr_price->sms_price];
                        } else {
                            $sms_network = ['network_id' => $network_id, 'network' => $network->nice_name, 'sms_price' => $network->sms_price];
                        }
                    }
                }

            }
        }
        if (!empty($sms_network))
            return $sms_network;
        $query = "select price from credits where user_id='$userId' limit 0,1";
        $res = DB::select($query);
        if (!empty($res) && count($res)>0) {
            $record = $res[0];
            return ['network_id' => null, 'network' => null, 'sms_price' => $record->price];
        }
        return ['network_id' => null, 'network' => null, 'sms_price' => null];
    }

    private function no_pages($no_chars) {

        if ($no_chars <= 160) {
            $no_pages = 1;
        }
        if ($no_chars > 160 && $no_chars <= 306) {
            $no_pages = 2;
        }
        if ($no_chars > 306 && $no_chars <= 459) {
            $no_pages = 3;
        }
        if ($no_chars > 459 && $no_chars <= 621) {
            $no_pages = 4;
        }
        if ($no_chars > 621) {
            $no_pages = 5;
        }

        return $no_pages;
    }

    private function checkCode($code) {
        if ($code == '246') {
            $msisdnLeng = 6;
            return $msisdnLeng;
        } elseif ($code == '238' || $code == '241' || $code == '239' || $code == '220') {
            $msisdnLeng = 7;
            return $msisdnLeng;
        } elseif (
               $code == '64' || $code == '229' || $code == '257' || $code == '237' || $code == '236'
            || $code == '235' || $code == '225' || $code == '243' || $code == '231' || $code == '223'
            || $code == '227' || $code == '228') {
            $msisdnLeng = 8;
            return $msisdnLeng;
        } elseif ($code == '242' || $code == '240'
            || $code == '254' || $code == '265' || $code == '250' || $code == '27' || $code == '221'
            || $code == '232' || $code == '211' || $code == '255' || $code == '256' || $code == '260'
            || $code == '233' || $code == '972') {
            $msisdnLeng = 9;
            return $msisdnLeng;
        } elseif ($code == '1' || $code == '234' || $code == '218' || $code == '263' || $code == '44' || $code == '96') {
            return $msisdnLeng = 10;
        } elseif ($code == '9') {
            return $msisdnLeng = 11;
        } else {
            $msisdnLeng = 10;
            return $msisdnLeng;
        }

    }


    private function log($text) {
        if(env('APP_ENV')=='local') {
            Log::info('INFO '.date('d-m-Y H:i:s').' || '.$text);
        } else
            file_put_contents($this->log_path,'INFO '.date('d-m-Y H:i:s').' || '.$text.PHP_EOL, FILE_APPEND);
    }

    private function warn_log($text) {
        if(env('APP_ENV')=='local') {
            Log::info('WARN '.date('d-m-Y H:i:s').' || '.$text);
        } else
            file_put_contents($this->log_path,'WARN '.date('d-m-Y H:i:s').' || '.$text.PHP_EOL, FILE_APPEND);
//            \swoole_async_writefile($this->log_path, 'WARN '.date('d-m-Y H:i:s').' || '. $text.PHP_EOL, null, FILE_APPEND);
    }

    private function error_log($text) {
        if(env('APP_ENV')=='local') {
            Log::error('ERROR '.date('d-m-Y H:i:s').' || '.$text);
        } else
            file_put_contents($this->log_path,'ERROR '.date('d-m-Y H:i:s').' || '.$text.PHP_EOL, FILE_APPEND);

    }


    private function sendsms($id, $job_id, $user_sess, $msisdn, $sender, $sms_count, $userNetwork, $msgs, $submitdt, $created_by, $myid,$flash) {
        $network = $userNetwork['network'];
        $created_at = date('Y-m-d H:i:s');
        date_default_timezone_set('GMT');
        $submitdt = date('Y-m-d H:i:s');
        $msgs = urlencode($msgs);

        // remove carriage returns
        // ==========RUN DIRECT URL TOO===========

        $msgs = str_replace("%5Cr", '%20', $msgs);
        // remove new lines
        $msgs = str_replace("%5Cn", '%0A', $msgs);
        // remove carriage returns
        $msgs = str_replace("%5C", '%20', $msgs);
        // remove backslash returns

        $dlr = "https://bulk.deywuro.com/api/dlr-receiver-route?source=%P&destination=%p&SVC=%n&msgID=%F&msg=%a&msglen=%L&timestamp=%t&status=%d&smsc=%i&smsid=%I&dlrv=%d&MDS=%D&DLRS=%A&osms=%b&mid=" . $id;
        $from = urlencode($sender);
        $dlrEncode = urlencode($dlr);

        if($flash == 'flash')
            $url = "http://esme.npontutechnologies.com:13014/cgi-bin/sendsms?username=guiuser&password=O14ns0&to=" . $msisdn . "&from=" . $from . "&text=" . $msgs . "&dlr-mask=31&mclass=0&dlr-url=" . $dlrEncode;
//            $url = "http://esme.npontutechnologies.com:13014/cgi-bin/sendsms?username=guiuser&password=O14ns0&to=" . $msisdn . "&from=" . $from . "&text=" . $msgs . "&dlr-mask=31&mclass=0";
        else
            $url = "http://esme.npontutechnologies.com:13014/cgi-bin/sendsms?username=guiuser&password=O14ns0&to=" . $msisdn . "&from=" . $from . "&text=" . $msgs . "&dlr-mask=31&dlr-url=" . $dlrEncode;
//            $url = "http://esme.npontutechnologies.com:13014/cgi-bin/sendsms?username=guiuser&password=O14ns0&to=" . $msisdn . "&from=" . $from . "&text=" . $msgs . "&dlr-mask=31";


        file_put_contents(storage_path('logs/kannel.log'), date('Y m d, H:i:s').' | Before Sending Request | '.$url. PHP_EOL. PHP_EOL, FILE_APPEND);

        try {
            $urloutput = file_get_contents($url);
        } catch (\Throwable $t) {
            file_put_contents(storage_path('logs/kannel.log'), date('Y m d, H:i:s').' | ERROR | '.$t->getMessage(). PHP_EOL. PHP_EOL, FILE_APPEND);
            $urloutput = $t->getMessage();
        }

        file_put_contents(storage_path('logs/kannel.log'), date('Y m d, H:i:s').' | After Sending Request | URL= '.$url.' | RESP= '.$urloutput. PHP_EOL. PHP_EOL, FILE_APPEND);

        $msgsd = urldecode($msgs);
        $sql = "INSERT INTO logs(id, job_id,user_id, username, msisdn, sender, sms_count, network, message,submit_date, status,created_by,response,originated,created_at)
                    VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        $returnInsert = DB::insert($sql,[$id, $job_id,$myid, $user_sess, $msisdn, $sender,$sms_count,$network,$msgsd,$submitdt,'PENDING',$created_by,$urloutput,'guiuser',$created_at]);
        $this->info('INSERT='.$returnInsert.'|SQL='.$sql);
        return $urloutput;
    }


}