<?php

namespace App\Console\Commands;

use App\CronJobStat;
use App\Log;
use App\Tools\CustomSmppClient;
use App\Tools\EmailAlert;
use App\Tools\ServiceTools;
use App\Tools\SMPP;
use App\Tools\SmppAddress;
use App\Tools\SmppException;
use App\Tools\SocketTransport;
use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

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

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Query SMS to Update Status';

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

    private function check_for_stop_flag() {
        //stop execution
        return;
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $cron = CronJobStat::where('name', 'DLR_QUERY')->firstOrFail();
        $cron->update([
            'last_run'=>Carbon::now(),
            'status' => 'RUNNING',
            'child_process'=> 1
        ]);

        //setup tables
        $this->info(Carbon::now()->toDateTimeString() . '| Setup temporary tables ...');

        //dropping table
        Schema::connection('kannel')->table('deywuro1', function (Blueprint $table){
            $table->dropIfExists();
        });
        Schema::connection('kannel')->table('deywuro2', function (Blueprint $table){
            $table->dropIfExists();
        });
        $this->check_for_stop_flag();

        //creating table
        Schema::connection('kannel')->create('deywuro1', function (Blueprint $table) {
            $table->string('msgid')->unique();
        });
        Schema::connection('kannel')->create('deywuro2', function (Blueprint $table) {
            $table->string('msgid')->unique();
            $table->string('msisdn',20);
            $table->dateTime('delivery_date');
            $table->string('status');
        });

        DB::delete('delete from tmpdlr');
        $this->check_for_stop_flag();

        $this->info(Carbon::now()->toDateTimeString() . '| Copying msgId to kannel ...');

        DB::table('sms_log')
            ->where('status', 'ACK/')
            ->whereNotNull('msgid')
            ->where('originated', 'mtnuser')
            ->where('submit_date', '<=', Carbon::now()->addMinutes(-10)->toDateTimeString())
            ->where('submit_date', '>=', Carbon::now()->addDays(-2)->toDateTimeString())
            ->select('msgid')
            ->orderBy('id')->chunk(10000, function ($jo) {
                try {
                    $this->info(Carbon::now()->toDateTimeString() . '| clearing tables.....');
                    DB::connection('kannel')->delete('delete from `deywuro1`');
                    DB::connection('kannel')->delete('delete from `deywuro2`');
                    DB::delete('delete from tmpdlr');

                    DB::connection('kannel')->beginTransaction();
                    foreach ($jo as $j) {
                        DB::connection('kannel')->insert('insert into `deywuro1` (`msgid`) values(?)', [$j->msgid]);
                    }
                    $this->check_for_stop_flag();
                    DB::connection('kannel')->commit();

                    $this->info(Carbon::now()->toDateTimeString() . '| updating delivery status of copied records ...');

                    DB::connection('kannel')->insert('insert into `deywuro2` (`msgid`, `msisdn`, `delivery_date`, `status`)
                      select d.msgid, sl.msisdn, sl.delivery_time, sl.status 
                      from sms_log sl INNER JOIN deywuro1 d ON sl.msg_id=d.msgid');

                    $this->check_for_stop_flag();

                    $this->info(Carbon::now()->toDateTimeString() . '| copying updated records from kannel to deywuro db...');

                    DB::beginTransaction();
                    $jo = DB::connection('kannel')->table('deywuro2')->get();
                    foreach ($jo as $record) {
                        if(str_contains($record->status, 'DELIVRD'))
                            $status = 'DELIVRD';
                        else if(str_contains($record->status, 'UNDELIV'))
                            $status = 'UNDELIV';
                        else if(str_contains($record->status, 'REJECTD'))
                            $status = 'REJECTD';
                        else if(str_contains($record->status, 'EXPIRED'))
                            $status = 'EXPIRED';
                        else
                            $status = '';

                        if(!empty($status)) {
                            DB::insert('insert into `tmpdlr` (`msgid`, `msisdn`, `delivery_date`, `status`) values (?,?,?,?)',[
                                $record->msgid, $record->msisdn, $record->delivery_date, $status
                            ]);
                        }
                    }
                    $this->check_for_stop_flag();
                    DB::commit();

                    $this->info(Carbon::now()->toDateTimeString() . '| updating delivery status in deywuro sms_log table...');

                    DB::update('UPDATE `sms_log`
INNER JOIN `tmpdlr` ON (`sms_log`.`msgid` = `tmpdlr`.`msgid`)
SET `sms_log`.`status` = `tmpdlr`.`status`, `sms_log`.`delivery_date` = `tmpdlr`.`delivery_date` ');

                    $this->info(Carbon::now()->toDateTimeString(). '| '. DB::table('tmpdlr')->count() . ' record(s) updated!');

                } catch (\RuntimeException $e) {
                    $this->error($e->getMessage());
                    EmailAlert::insertError('SmsQuery', $e->getMessage(), $e->getLine());
                } catch (\Throwable $e) {
                    $this->error($e->getMessage());
                    EmailAlert::insertError('SmsQuery', $e->getMessage(), $e->getLine());
                }
            });


        //droping table
        Schema::connection('kannel')->table('deywuro1', function (Blueprint $table){
            $table->drop();
        });
        Schema::connection('kannel')->table('deywuro2', function (Blueprint $table){
            $table->drop();
        });

        $cron = CronJobStat::where('name', 'DLR_QUERY')->firstOrFail();
        $cron->update([
            'last_run'=>Carbon::now(),
            'status' => 'DONE',
            'child_process'=> 0
        ]);
    }
}