<?php

namespace App\Console\Commands;

use App\Models\DataGadai;
use App\Models\JobLog;
use App\Models\Level;
use App\Models\CronjobCheck;
use Illuminate\Console\Command;
use  App\Models\Customer;
use  App\Models\Notification;
use Carbon\Carbon;
use Google_Client;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use App\Models\PromoPoint;
use App\Models\VoucherPromoPoint;

class CronJobLog extends Command
{

    protected $signature = 'job:notifikasi';

    protected $description = 'Save job ke table job log untuk reminder data gadai';

    public function __construct()
    {
        parent::__construct();
    }

    public function handle()
    {
        try {
            $this->info(Carbon::now()->format('Y-m-d H:i:s').' => Start Processing Notification');
            $tanggal_sekarang = Carbon::now()->format('Y-m-d');
            $job_check = DB::table('cronjob_check')->where('keterangan', 'INSERT NOTIFIKASI')->first();
			//$promo_point = PromoPoint::where('tgl_selesai_penukaran_point', '<', $tanggal_sekarang)->where('status', 'Aktif')->get();
            if ($job_check->status == 0) {
                DB::table('cronjob_check')->where('keterangan', 'INSERT NOTIFIKASI')->update([
                    'status' => 1,
                    'start_at' => Carbon::now()
                ]);

                $qryJob = Customer::crossJoin('notification')
                    ->leftJoin('job_log as c', function ($join) {
                        $join->on('customer.customer_id', '=', 'c.customer_id')
                            ->on('notification.id', '=', 'c.notification_id');
                    })
                    ->leftJoin('job_log_success as d', function ($join) {
                        $join->on('customer.customer_id', '=', 'd.customer_id')
                            ->on('notification.id', '=', 'd.notification_id');
                    })
                    ->join('data_gadai as e', 'customer.customer_id', '=', 'e.customer_id')
                    ->whereNotIn('e.status', ['Close', 'Cancel'])
                    ->whereIn('notification.type_id', [7, 4])
                    ->where('e.tgl_lelang','>=',$tanggal_sekarang)
                    ->select(
                        'customer.customer_id',
                        'notification.id as notification_id',
                        'notification.type_id as notif_type',
                        'e.tgl_jatuh_tempo',
                        'e.tgl_lelang',
                        DB::raw('MAX(c.id) as job_id'),
                        DB::raw('MAX(d.id) as job_success_id'),
                        DB::raw('MAX(notification.reminder) as reminder'),
                        DB::raw('MAX(c.tgl_proses) as tgl_proses'),
                        DB::raw('MAX(d.tgl_proses) as tgl_proses_success'),
                        'e.nomor_transaksi',
                        'e.status',
                        'notification.schedule'
                    )
                    ->groupBy(
                        'customer.customer_id',
                        'notification.id',
                        'notification.type_id',
                        'e.nomor_transaksi',
                        'e.status',
                        'e.tgl_jatuh_tempo',
                        'e.tgl_lelang'
                    )
                    ->havingRaw('MAX(c.id) IS NULL OR MAX(d.id) IS NULL')
                    ->orderBy('customer.customer_id')
                    ->orderBy('e.nomor_transaksi')
                    ->orderBy('notification.id')
                    ->get();
                foreach ($qryJob as $result){
                    if ($result->notif_type == 4){
                        $reminder = Carbon::parse($result->schedule)->format('H:i:s');
                        $reminderJob = Carbon::parse($result->tgl_jatuh_tempo)->subDays($result->reminder)->format('Y-m-d');

                        $now = Carbon::now()->format('Y-m-d');
                        if ($result->job_id == null && $result->job_success_id == null){
                            if ($reminderJob >= $now){
                                $jobCreate = JobLog::create([
                                    'notification_id' => $result->notification_id,
                                    'customer_id' => $result->customer_id,
                                    'gadai_number' => $result->nomor_transaksi,
                                    'created_who' => 'cronjob',
                                    'status' => 0,
                                    'count_sending' => 0,
                                    'tgl_proses' => $reminderJob.' '.$reminder,
                                    'tipe_proses' => 'Reminder'
                                ]);
                                $this->info('Job Reminder Jatuh Tempo berhasil dibuat, ID: '. $jobCreate->id.' nomor_transaksi: '.$result->nomor_transaksi.' Customer ID: '.$result->customer_id .' Notification ID: '.$result->notification_id);
                                Log::info('Job Reminder Jatuh Tempo berhasil dibuat, ID: '. $jobCreate->id.' nomor_transaksi: '.$result->nomor_transaksi.' Customer ID: '.$result->customer_id .' Notification ID: '.$result->notification_id);
                            }
                        } else {
                            if ($result->job_id != null){
                                if ($result->job_status == 0 && $result->tgl_proses == $reminderJob . ' ' . $reminder) {
                                    JobLog::where('id', $result->job_id)->update([
                                        'status' => 0,
                                        'tgl_proses' => $reminderJob . ' ' . $reminder,
                                    ]);
                                }
                            }
                            if ($result->job_success_id != null && $result->job_id == null){
                                $timePast = Carbon::parse($result->tgl_proses_success)->format('Y-m-d H:i:s');
                                $timeNew = Carbon::createFromFormat('Y-m-d H:i:s', $reminderJob. ' ' . $reminder);
                                if ($timePast < $timeNew){
                                    $jobCreate = JobLog::create([
                                        'notification_id' => $result->notification_id,
                                        'customer_id' => $result->customer_id,
                                        'gadai_number' => $result->nomor_transaksi,
                                        'created_who' => 'cronjob',
                                        'status' => 0,
                                        'count_sending' => 0,
                                        'tgl_proses' => $reminderJob.' '.$reminder,
                                        'tipe_proses' => 'Reminder'
                                    ]);
                                    $this->info('Job Reminder Jatuh Tempo berhasil dibuat, ID: '. $jobCreate->id.' nomor_transaksi: '.$result->nomor_transaksi.' Customer ID: '.$result->customer_id .' Notification ID: '.$result->notification_id);
                                    Log::info('Job Reminder Jatuh Tempo berhasil dibuat, ID: '. $jobCreate->id.' nomor_transaksi: '.$result->nomor_transaksi.' Customer ID: '.$result->customer_id .' Notification ID: '.$result->notification_id);
                                }
                            }
                        }
                    } else if ($result->notif_type == 7){

                        $reminder = Carbon::parse($result->schedule)->format('H:i:s');
                        $reminderJob = Carbon::parse($result->tgl_lelang)->subDays($result->reminder)->format('Y-m-d');

                        $now = Carbon::now()->format('Y-m-d');
                        if ($result->job_id == null && $result->job_success_id == null){
                            if ($reminderJob >= $now){
                                $jobCreate = JobLog::create([
                                    'notification_id' => $result->notification_id,
                                    'customer_id' => $result->customer_id,
                                    'gadai_number' => $result->nomor_transaksi,
                                    'created_who' => 'cronjob',
                                    'status' => 0,
                                    'count_sending' => 0,
                                    'tgl_proses' => $reminderJob.' '.$reminder,
                                    'tipe_proses' => 'Reminder'
                                ]);
                                $this->info('Job Reminder Lelang berhasil dibuat, ID: '. $jobCreate->id.' nomor_transaksi: '.$result->nomor_transaksi.' Customer ID: '.$result->customer_id .' Notification ID: '.$result->notification_id);
                                Log::info('Job Reminder Lelang berhasil dibuat, ID: '. $jobCreate->id.' nomor_transaksi: '.$result->nomor_transaksi.' Customer ID: '.$result->customer_id .' Notification ID: '.$result->notification_id);
                            }
                        } else {
                            if ($result->job_id != null){
                                if ($result->job_status == 0 && $result->tgl_proses == $reminderJob . ' ' . $reminder) {
                                    JobLog::where('id', $result->job_id)->update([
                                        'status' => 0,
                                        'tgl_proses' => $reminderJob . ' ' . $reminder,
                                    ]);
                                }
                            }
                            if ($result->job_success_id != null && $result->job_id == null){
                                $timePast = Carbon::parse($result->tgl_proses_success)->format('Y-m-d H:i:s');
                                $timeNew = Carbon::createFromFormat('Y-m-d H:i:s', $reminderJob. ' ' . $reminder);
                                if ($timePast < $timeNew){
                                    $jobCreate = JobLog::create([
                                        'notification_id' => $result->notification_id,
                                        'customer_id' => $result->customer_id,
                                        'gadai_number' => $result->nomor_transaksi,
                                        'created_who' => 'cronjob',
                                        'status' => 0,
                                        'count_sending' => 0,
                                        'tgl_proses' => $reminderJob.' '.$reminder,
                                        'tipe_proses' => 'Reminder'
                                    ]);
                                    $this->info('Job Reminder Lelang berhasil dibuat, ID: '. $jobCreate->id.' nomor_transaksi: '.$result->nomor_transaksi.' Customer ID: '.$result->customer_id .' Notification ID: '.$result->notification_id);
                                    Log::info('Job Reminder Lelang berhasil dibuat, ID: '. $jobCreate->id.' nomor_transaksi: '.$result->nomor_transaksi.' Customer ID: '.$result->customer_id .' Notification ID: '.$result->notification_id);
                                }
                            }
                        }

                    }
                }                
            }
        $this->info(Carbon::now()->format('Y-m-d H:i:s').' => Start Processing Notification');
        }catch (Exception $e){
            Log::error($e->getMessage());
            Log::error($e->getTraceAsString());

            $this->info($e->getTraceAsString());
        } finally{
            $job_check = DB::table('cronjob_check')->where('keterangan', 'INSERT NOTIFIKASI')->first();

            DB::table('cronjob_check')->where('keterangan', 'INSERT NOTIFIKASI')->update([
                'status' => 0,
                'start_at' => null
            ]);
        }
    }
}