<?php

namespace App\Console\Commands;

use App\CronJobStat;
use App\Tools\CustomSmppClient;
use App\Tools\EmailAlert;
use App\Tools\GsmEncoder;
use App\Tools\ServiceTools;
use App\Tools\SMPP;
use App\Tools\SmppAddress;
use App\Tools\SocketTransport;
use App\Tools\SmsTools;
use App\User;
use App\EmailJob;
use Bogardo\Mailgun\Facades\Mailgun;
use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;

class SpeedyPersoBulkEmail extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'bulkemail:p_start {type}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Send Personalised Bulk Email!';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        //update & check cron job status for sending bulk sms
        $this->update_cron_job_status_running('BULK_EMAIL_PERSO', 'RUNNING');

        //init vars
        $cdate = Carbon::now();
        $cron_status_running = true;

        //get arguments
        $type = $this->argument('type');

        try {
        while (Carbon::now()->diffInSeconds($cdate)<=54) {
            try {
                $this->check_for_stop_flag();
                $job = DB::table('email_jobs')
                    ->where(function ($query){
                        $query->where('status', 'pending')->orWhere('status', 'stopped');
                    })
                    ->where('type', $type)
                    ->where('sched_datetime', '<=', Carbon::now()->toDateTimeString())
                    ->select('id')
                    ->orderBy('id')->first();

                if (empty($job)) {
                    break;
                }

                $job = EmailJob::findOrFail($job->id);
                $job->status = 'started';
                $job->progress = empty($job->progress) ? 0 : $job->progress;
                $job->task_started_date = Carbon::now()->toDateTimeString();
                if(empty($job->save())) {
                    EmailAlert::insertError('BULK_EMAIL_PERSO', 'Database Error: Unable to update `email_jobs` table (status->started)');
                    exit(1);
                }

                $this->info(Carbon::now()->toDateTimeString() . '| Job(s) found...');

                $path = $job->file_path;
                $this->check_for_stop_flag();
                $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();
                            $rejected_arr = [];
                            $headers = [];

                            //computing
                            $user = User::findOrFail($job->user_id);
                            $credit = $user->credit;

                            //smsc credential
                            $smsc_usr = 'guiuser';
                            $row_number = (int)(empty($job->row_number)?0:$job->row_number);

                            $price = $credit->email_price;
                            $cost = $price;
                            $num_email = 0;
                            // no of recepients
                            $tot_cost = 0;

                            $curr_bal = $credit->new_bal;
                            $credit_used = $credit->credit_used;

                            $job_id = $job->id;
                            $user_id = $job->user_id;
                            $username = $job->username;
                            $email = '';
                            $network = 'Unknown';
                            $sender = $job->sender;
                            $subject = $job->subject;
                            $tmp_msg = $message = $job->message;
                            $submit_date = $job->sched_datetime;
                            $status = 'PENDING';
                            $originated = $smsc_usr;
                            $created_at = Carbon::now()->toDateTimeString();
                            $email_price = null;

                            $usr_new_bal = (double) $user->credit->new_bal;
                            $usr_credit_used = (double) $user->credit->credit_used;

                            DB::beginTransaction();

                            //  Loop through each row of the worksheet in turn
                            $rowData = $sheet->rangeToArray('A' . 1 . ':' . $highestColumn . 1, NULL, TRUE, FALSE);
                            $headers = $rowData[0];
                            //save msg sent to msisdn, to prevent dupplicate
                            $processed_email = [];
                            for ($row = 2; $row <= $highestRow; $row++) {
                                if($row <= $row_number) continue;
                                $id = uniqid('NE', true);
                                $email = '';
                                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();
                                        //update user credit
                                        $credo = $user->credit;
                                        $credo->new_bal = $usr_new_bal;
                                        $credo->credit_used = $usr_credit_used;
                                        $credo->save();
                                        //commit transaction
                                        DB::commit();
                                        $this->info('Service Stopped By User!');
                                        $this->update_cron_job_status_done('BULK_EMAIL_PERSO', 'DONE');
                                        exit(1);
                                    }
                                    //commit every 10% of progress
                                    $progress = (double)(100 * ($row-1))/$highestRow;
                                    if($row>2 && $progress%10===0) {
                                        $job->progress = $progress;
                                        $job->row_number = $row;
                                        $job->save();
                                        //update user credit
                                        $credo = $user->credit;
                                        $credo->new_bal = $usr_new_bal;
                                        $credo->credit_used = $usr_credit_used;
                                        $credo->save();
                                        //commit transaction
                                        DB::commit();
                                        DB::beginTransaction();
                                    }
                                    $tmp_msg = $message;
                                    //  Read a row of data into an array
                                    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
                                        NULL, TRUE, FALSE);
                                    $rowData = $rowData[0];
                                    $email = $rowData[0];
                                    $email = trim($email);

                                    if (filter_var($email, FILTER_VALIDATE_EMAIL)) {

                                        //check for duplicate
//                                        if (in_array($msisdn, $processed_msisdn)) {
//                                            $status = 'SKIPPED';
//
//                                            DB::insert('INSERT INTO `logs`
//                                (id, job_id, user_id, username, msisdn, network, sender, message,
//                                sms_count, submit_date, status, created_by, originated, created_at, network_id, sms_price)
//                                  VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', [$id, $job_id, $user_id, $username,
//                                                $msisdn, $network, $sender, $tmp_msg, $sms_count, $submit_date,
//                                                $status, $username, $originated, $created_at, $network_id, $sms_price]);
//                                            continue;
//                                        }

                                        $tmp_msg = $this->update_msg($headers, $rowData, $tmp_msg);

                                        //Send SMS
                                        $data = Mailgun::raw($tmp_msg, function ($msg) use ($subject, $email, $sender) {
                                            $msg->to($email)->subject($subject)->from($sender);
                                            $msg->trackOpens(true);
                                        });
//                                    $this->info(var_export($data, true));

                                        $msgId = str_replace(['<','>'], ['',''], $data->id);
                                        $resp_code = (int)$data->status;
                                        $response = $data->message;
                                        $email_price = $credit->email_price;

                                        if (!empty($msgId)) {
                                            $usr_new_bal -= (double)$email_price;
                                            $usr_credit_used += (double)$email_price;
                                        }
                                        $status = !empty($msgId) ? 'SENT' : 'FAILED';

                                        DB::insert('INSERT INTO `email_logs`
                                (id, job_id, user_id, username, email, subject, sender, message, response, resp_code,
                                submit_date, status, created_by, originated, created_at, email_price, msgid)
                                  VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', [$id, $job_id, $user_id, $username,
                                            $email, $subject, $sender, $message, $response, $resp_code, $submit_date,
                                            $status, $username, $originated, $created_at, $email_price, $msgId]);

                                        $num_email++;
                                        array_push($processed_email, $email);
                                    } else {
                                        array_push($rejected_arr, $email);
                                        $status = 'FAILED';

                                        DB::insert('INSERT INTO `email_logs`
                                (id, job_id, user_id, username, email, subject, sender, message,
                                submit_date, status, created_by, originated, created_at, email_price)
                                  VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)', [$id, $job_id, $user_id, $username,
                                            $email, $subject, $sender, $message, $submit_date,
                                            $status, $username, $originated, $created_at, $email_price]);
                                    }
                                } catch (\PDOException $pdoex){
                                    EmailAlert::insertError('SendPersoBulkSms', 'User:' . $username . ', Sender:' . $sender . ', Email:' . $email . ', Message:' . $message . ' | ErrorCode:' . $pdoex->getCode() . ', ErrorMsg:' . $pdoex->getMessage(), $pdoex->getLine());
                                    $this->info('Database Error: '.$pdoex->getMessage().'. The Cron Process has been terminated!');
                                    $this->update_cron_job_status_done('BULK_EMAIL_PERSO', 'DONE');
                                    exit(3);
                                }
                                catch (\ErrorException $eex){
                                    EmailAlert::insertError('SendPersoBulkSms', 'User:' . $username . ', Sender:' . $sender . ', Email:' . $email . ', Message:' . $message . ' | ErrorCode:' . $eex->getCode() . ', ErrorMsg:' . $eex->getMessage(), $eex->getLine());
                                    //update job status
                                    $job->progress = (double)(100 * $row) / $highestRow;
                                    $job->row_number = $row;
                                    $job->status = 'stopped';
                                    $job->save();
                                    //update user credit
                                    $credo = $user->credit;
                                    $credo->new_bal = $usr_new_bal;
                                    $credo->credit_used = $usr_credit_used;
                                    $credo->save();
                                    //commit transaction
                                    DB::commit();
                                    $this->update_cron_job_status_done('BULK_EMAIL_PERSO', 'DONE');
                                    $this->info('Error: '.$eex->getMessage().'. The Cron Process has been terminated!');
                                    exit(3);
                                }
                                catch (\RuntimeException $re) {
                                    if((int)$re->getCode()!=69)
                                        EmailAlert::insertError('SendPersoBulkSms', 'User:' . $username . ', Sender:' . $sender . ', Email:' . $email . ', Message:' . $message . ' | ErrorCode:' . $re->getCode() . ', ErrorMsg:' . $re->getMessage(), $re->getLine());
                                } catch (\Exception $e) {
                                    if((int)$e->getCode()!=69) {
                                        $this->error($e->getMessage());
                                        EmailAlert::insertError('SendPersoBulkSms', 'User:' . $username . ', Sender:' . $sender . ', Email:' . $email . ', Message:' . $message . ' | ErrorCode:' . $e->getCode() . ', ErrorMsg:' . $e->getMessage(), $e->getLine());
                                    }
                                }
                                $row_number++;
                            }

                            $job->progress = 100;
                            $job->row_number = $highestRow;
                            $job->save();
                            //update user credit
                            $credo = $user->credit;
                            $credo->new_bal = $usr_new_bal;
                            $credo->credit_used = $usr_credit_used;
                            $credo->save();
                            //commit transaction
                            DB::commit();

                            $no_rej_msisdn = count($rejected_arr);

                            $logged = $num_email - $no_rej_msisdn;
                            $this->info($logged . ' email(s) sent for this campaign');
                        });
                    } catch (\RuntimeException $re) {
                        EmailAlert::insertError('SendPersoBulkSms', $re->getMessage(), $re->getLine());
                    } catch (\Exception $e) {
                        $this->error($e->getMessage());
                        EmailAlert::insertError('SendPersoBulkSms', $e->getMessage(), $e->getLine());
                    }

                    $this->info('Task Complete for this Job!');
                }

                //mark job as complete
                $job->status = 'done';
                $job->task_completed_date = Carbon::now()->toDateTimeString();
                if(empty($job->save())) {
                    EmailAlert::insertError('BULK_EMAIL_PERSO', 'Database Error: Unable to update `email_jobs` table (status->done)');
                    exit(1);
                }
            } catch (\RuntimeException $re) {
                EmailAlert::insertError('SendPersoBulkSms', $re->getMessage(), $re->getLine());
            } catch (\Exception $e) {
                EmailAlert::insertError('SendPersoBulkSms', $e->getMessage(), $e->getLine());
            }
        }
        } catch(\Throwable $t) {
            if($cron_status_running === true) {
                //update status of the running cron job
                $this->update_cron_job_status_done('BULK_EMAIL_PERSO', 'DONE');
            }
            throw $t;
        }

        //update status of the running cron job
        $this->update_cron_job_status_done('BULK_EMAIL_PERSO', 'DONE');
    }

    public function update_msg($headers, $rowData, $msg) {
        $retValue = $msg;
        for($i=0; $i< count($headers); $i++) {
            $header = $headers[$i];
            $value = isset($rowData[$i]) && !empty($rowData[$i]) ? $rowData[$i] : '';
            $retValue = str_ireplace('['.$header.']', $value, $retValue);
        }
        return $retValue;
    }

    private function update_cron_job_status_done($name, $status) {
        $affected = DB::update(
            'update `cron_job_stats` set `child_process`=`child_process`-1 where `name` = ?',
            [$name]
        );
        if((int)$affected !== 1) {
            EmailAlert::insertError($name, 'Database Error: Unable to update `cron_job_stats` table ('.$name.': status->'.$status.')');
            exit(1);
        }
        DB::update(
            'update `cron_job_stats` set `status`=? where `name` = ? AND `child_process`=?',
            [$status, $name, 0]
        );
    }

    private function update_cron_job_status_running($name, $status) {
        $now = Carbon::now()->toDateTimeString();
        $affected = DB::update(
            'update `cron_job_stats` set `last_run` = ?, `status`=?, `child_process`=`child_process`+1 where `name` = ?',
            [$now, $status, $name]
        );
        if((int)$affected !== 1) {
            EmailAlert::insertError($name, 'Database Error: Unable to update `cron_job_stats` table ('.$name.': status->'.$status.')');
            exit(1);
        }
    }

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