<?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\SmppAddress;
use App\Tools\SocketTransport;
use App\Tools\SMPP;
use App\Tools\SmsTools;
use App\User;
use App\EmailJob;
use Bogardo\Mailgun\Facades\Mailgun;
use Carbon\Carbon;
use Doctrine\DBAL\Driver\PDOException;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;

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

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Send 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', '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', 'Database Error: Unable to update `email_jobs` table (status->started)');
                        exit(1);
                    }

                    $this->info(Carbon::now()->toDateTimeString() . '| Email Job(s) found!');
                    //connect to smpp client

                    $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 = [];

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

                                //smsc credential
                                $smsc_usr = 'guiuser';

                                $num_emails = 0;
                                // no of recepients

                                //prepare statement for logs
                                $job_id = $job->id;
                                $user_id = $job->user_id;
                                $row_number = (int)(empty($job->row_number)?0:$job->row_number);
                                $username = $job->username;
                                $email = '';
                                $sender = $job->sender;
                                $message = $job->message;
                                $subject = $job->subject;
                                $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
                                //save msg sent to msisdn, to prevent dupplicate
                                $processed_email = [];
                                $this->info(Carbon::now()->toDateTimeString() . '| Sending Bulk Email...');
                                for ($row = 1; $row <= $highestRow; $row++) {
                                    if($row <= $row_number) continue;
                                    $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', 'DONE');
                                            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();
                                            //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();
                                        }
                                        //  Read a row of data into an array
                                        $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
                                            NULL, TRUE, FALSE);
                                        $email = $rowData[0][0];
                                        $email = trim($email);

                                        $id = uniqid('NE', true);

                                        if (filter_var($email, FILTER_VALIDATE_EMAIL)) {
                                            //check for duplicate
                                            if (in_array($email, $processed_email)) {
                                                $status = 'SKIPPED';

                                                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]);
                                                continue;
                                            }

                                            if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
                                                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]);

                                            } else {
                                                //Send SMS
                                                $data = Mailgun::raw($message, 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_emails++;
                                            }
                                            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('SendBulkEmail', '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', 'DONE');
                                        exit(3);
                                    }
                                    catch (\ErrorException $eex){
                                        EmailAlert::insertError('SendBulkEmail', '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', 'DONE');
                                        $this->info(' Error: '.$eex->getMessage().'. The Cron Process has been terminated!');
                                        exit(3);
                                    }
                                    catch (\RuntimeException $re) {
                                        if ((int)$re->getCode() != 69)
                                            EmailAlert::insertError('SendBulkEmail', 'User:' . $username . ', Sender:' . $sender . ', Email:' . $email . ', Message:' . $message . ' | ErrorCode:' . $re->getCode() . ', ErrorMsg:' . $re->getMessage(), $re->getLine());
                                    } catch (\Throwable $e) {
                                        if ((int)$e->getCode() != 69) {
                                            $this->error($e->getMessage());
                                            EmailAlert::insertError('SendBulkEmail', 'User:' . $username . ', Sender:' . $sender . ', Email:' . $email . ', Message:' . $message . ' | ErrorCode:' . $e->getCode() . ', ErrorMsg:' . $e->getMessage(), $e->getLine());
                                        }
                                    }
                                    $row_number++;
                                }

                                // Close connection
                                $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_emails - $no_rej_msisdn;
                                $this->info($logged . ' email(s) sent for this campaign');
                            });
                        } catch (\RuntimeException $re) {
                            EmailAlert::insertError('SendBulkEmail', $re->getMessage(), $re->getLine());
                        } catch (\Exception $e) {
                            $this->error($e->getMessage());
                            EmailAlert::insertError('SendBulkEmail', $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', 'Database Error: Unable to update `email_jobs` table (status->done)');
                        exit(1);
                    }

                } catch (\RuntimeException $re) {
                    EmailAlert::insertError('SendBulkEmail', $re->getMessage(), $re->getLine());
                } catch (\Exception $e) {
                    EmailAlert::insertError('SendBulkEmail', $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', 'DONE');
            }
            throw $t;
        }

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

    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);
        }
    }

    function gen_uuid() {
        return sprintf( '%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
            // 32 bits for "time_low"
            mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff ),

            // 16 bits for "time_mid"
            mt_rand( 0, 0xffff ),

            // 16 bits for "time_hi_and_version",
            // four most significant bits holds version number 4
            mt_rand( 0, 0x0fff ) | 0x4000,

            // 16 bits, 8 bits for "clk_seq_hi_res",
            // 8 bits for "clk_seq_low",
            // two most significant bits holds zero and one for variant DCE1.1
            mt_rand( 0, 0x3fff ) | 0x8000,

            // 48 bits for "node"
            mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff )
        );
    }


    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', 'DONE');
            exit(1);
        }
    }
}