<?php

namespace App;

use Carbon\Carbon;
use Illuminate\Database\Eloquent\Model;

class StatusLogs extends Model
{

    protected $table = 'status_logs';

    public function getKycVerified($id){
        return $this->where('status_logs.application_id',$id)
            ->where('status', 'ready for disbursement')
            ->leftJoin('users as u','u.id','=','status_logs.user_id')
            ->orderBy('status_logs.created_at','asc')->first(['status_logs.id','u.name', 'u.id as user_id', 'status_logs.created_at as verified_at']);
    }

    public function getLogs($id){
        return $this->where('application_id',$id)
                ->leftJoin('users as u','u.id','=','status_logs.user_id')
                ->orderBy('created_at','asc')->get(['status_logs.id','status_logs.created_at','u.name','status']);
    }

    public function user(){
        return $this->hasOne('App\User','id','user_id');
    }

    public function application(){
        return $this->hasOne('App\ConsumerApplication','id','application_id')->whereNull('is_deleted');
    }

    public function activeApplication(){
        return $this->hasOne('App\ConsumerApplication','id','application_id')->whereNotIn('state', ['closed', 'cancelled', 'cancellation in-process'])->whereNull('is_deleted');
    }

    public function application_element(){
        return $this->hasOne('App\ConsumerApplicationElement','application_id','application_id');
    }

    public function getProcessLogs($id){

        $processingFee = SMSLogs::where('_id', $id)->where('id_type','application_id')
            ->where(function ($query) {
                $query->where('message','like','%and+Processing+Fee+Invoice+@%')->orWhere('message','like','%Pay+processing+fees%');
            })->first(['created_at']);
        $esign = SMSLogs::where('_id', $id)->where('id_type','application_id')->where('message','like','%sign+your+loan+agreement+online+here%')->first(['created_at']);
        $advanceSending = SMSLogs::where('_id', $id)->where('id_type','application_id')->where('message','like','%Pay+Advance+Amount%')->first(['created_at']);
        $enach = SMSLogs::where('_id', $id)->where('id_type','application_id')->where('message','like','%NACH+process+to+register+your+bank+account+for%')->first(['created_at']);
        $insurance = SMSLogs::where('_id', $id)->where('id_type','application_id')->where('message','like','%Pay+Insurance+Premium%')->first(['created_at']);
        $digitalSign = DigitalSignDetails::where('application_id', $id)->get(['created_at','applicant','updated_at','click_time']);
        $KYCDetails = KYCDetails::where('application_id', $id)->whereNotNull('response')->get(['created_at','applicant_type','updated_at']);
        //$onfidoIntegration = OnfidoIntegration::where('application_id', $id)->whereNotNull('webhook_response')->get(['created_at','applicant_type']);
        $paymentTransaction = PaymentTransaction::where('application_id', $id)->whereNotIn('mode',['cash','Adjusted In disbursement'])->where('payment_for','processing_fee')->first(['created_at']);
        $paymentTransactionAdvance = PaymentTransaction::where('application_id', $id)->whereNotIn('mode',['cash','Adjusted In disbursement'])->where('payment_for','advance_amount')->first(['created_at']);
        $resignTAT = TATLogs::where('_id', $id)->where('id_type','application_id')->where('action', 'Agreement Resign')->get(['created_at']);
        $resignNach = TATLogs::where('_id', $id)->where('id_type','application_id')->where('action', 'e-mandate completed')->orderBy('created_at','desc')->get(['created_at']);
        $creditReport = CreditReport::where('application_id', $id)->get(['created_at','applicant_type']);
        //$kycDetails = KYCDetails::where('application_id', $id)->whereNotNull('response')->orderBy('created_at','desc')->first(['created_at', 'updated_at']);
        $onfidoIntegration = OnfidoIntegration::where('application_id', $id)->whereNotNull('webhook_response')->orderBy('created_at','desc')->get(['created_at', 'updated_at','applicant_type']);

        $mandates = Mandate::where('consumer_application_id', $id)->get(['id']);
        $mandatesIds = [];
        foreach ($mandates as $mandate){
            $mandatesIds[] = $mandate->id;
        }
        if($mandatesIds) {
            $mandateStatusLog = MandateStatusLog::whereIn('mandate_id', $mandatesIds)->where('status', 'active')->orderBy('created_at', 'desc')->first(['created_at']);
        }

        $data = [
                'Time of sending agreement' => isset($esign->created_at) ? Carbon::parse($esign->created_at)->toDateTimeString() : '',
                'Time of E-Sign App' => isset($digitalSign[0]) && $digitalSign[0]->applicant == 1 ? Carbon::parse($digitalSign[0]->click_time)->toDateTimeString() : (isset($digitalSign[1]) && $digitalSign[1]->applicant == 1 ? Carbon::parse($digitalSign[1]->click_time)->toDateTimeString() : ''),
                'time of E-Sign Co-app' => isset($digitalSign[0]) && $digitalSign[0]->applicant == 2 ? Carbon::parse($digitalSign[0]->click_time)->toDateTimeString() : (isset($digitalSign[1]) && $digitalSign[1]->applicant == 2 ? Carbon::parse($digitalSign[1]->click_time)->toDateTimeString() : ''),
                'Time of E-Sign App End' => isset($digitalSign[0]) && $digitalSign[0]->applicant == 1 ? Carbon::parse($digitalSign[0]->updated_at)->toDateTimeString() : (isset($digitalSign[1]) && $digitalSign[1]->applicant == 1 ? Carbon::parse($digitalSign[1]->updated_at)->toDateTimeString() : ''),
                'time of E-Sign Co-app End' => isset($digitalSign[0]) && $digitalSign[0]->applicant == 2 ? Carbon::parse($digitalSign[0]->updated_at)->toDateTimeString() : (isset($digitalSign[1]) && $digitalSign[1]->applicant == 2 ? Carbon::parse($digitalSign[1]->updated_at)->toDateTimeString() : ''),
                //'Time of RE-Sign' => isset($resignTAT->created_at) ? $resignTAT->created_at : '',
                'time of Digio App' => isset($KYCDetails[0]) && $KYCDetails[0]->applicant_type == 1 ? Carbon::parse($KYCDetails[0]->created_at)->toDateTimeString() : (isset($KYCDetails[1]) && $KYCDetails[1]->applicant_type == 1 ? Carbon::parse($KYCDetails[1]->created_at)->toDateTimeString() : ''),
                'time of Digio Co-app' => isset($KYCDetails[0]) && $KYCDetails[0]->applicant_type == 2 ? Carbon::parse($KYCDetails[0]->created_at)->toDateTimeString() : (isset($KYCDetails[1]) && $KYCDetails[1]->applicant_type == 2 ? Carbon::parse($KYCDetails[1]->created_at)->toDateTimeString() : ''),
                'time of Digio App End' => isset($KYCDetails[0]) && $KYCDetails[0]->applicant_type == 1 ? Carbon::parse($KYCDetails[0]->updated_at)->toDateTimeString() : (isset($KYCDetails[1]) && $KYCDetails[1]->applicant_type == 1 ? Carbon::parse($KYCDetails[1]->updated_at)->toDateTimeString() : ''),
                'time of Digio Co-app End' => isset($KYCDetails[0]) && $KYCDetails[0]->applicant_type == 2 ? Carbon::parse($KYCDetails[0]->updated_at)->toDateTimeString() : (isset($KYCDetails[1]) && $KYCDetails[1]->applicant_type == 2 ? Carbon::parse($KYCDetails[1]->updated_at)->toDateTimeString() : ''),
                'Time of Onfido app' => isset($onfidoIntegration[0]) && $onfidoIntegration[0]->applicant_type == 1 ? Carbon::parse($onfidoIntegration[0]->created_at)->toDateTimeString() : (isset($onfidoIntegration[1]) && $onfidoIntegration[1]->applicant_type == 1 ? Carbon::parse($onfidoIntegration[1]->created_at)->toDateTimeString() : ''),
                'Time of Onfido Co-app' => isset($onfidoIntegration[0]) && $onfidoIntegration[0]->applicant_type == 2 ? Carbon::parse($onfidoIntegration[0]->created_at)->toDateTimeString() : (isset($onfidoIntegration[1]) && $onfidoIntegration[1]->applicant_type == 2 ? Carbon::parse($onfidoIntegration[1]->created_at)->toDateTimeString() : ''),
                'Time of Onfido App End' => isset($onfidoIntegration[0]) && $onfidoIntegration[0]->applicant_type == 1 ? Carbon::parse($onfidoIntegration[0]->updated_at)->toDateTimeString() : (isset($onfidoIntegration[1]) && $onfidoIntegration[1]->applicant_type == 1 ? Carbon::parse($onfidoIntegration[1]->updated_at)->toDateTimeString() : ''),
                'Time of Onfido Co-app End' => isset($onfidoIntegration[0]) && $onfidoIntegration[0]->applicant_type == 2 ? Carbon::parse($onfidoIntegration[0]->updated_at)->toDateTimeString() : (isset($onfidoIntegration[1]) && $onfidoIntegration[1]->applicant_type == 2 ? Carbon::parse($onfidoIntegration[1]->updated_at)->toDateTimeString() : ''),
                'Time of sending processing fees' => isset($processingFee->created_at) ? Carbon::parse($processingFee->created_at)->toDateTimeString() : '',
                'Time of sending advance amount link' => isset($advanceSending->created_at) ? Carbon::parse($advanceSending->created_at)->toDateTimeString() : '',
                'Time of paying processing fees online' => isset($paymentTransaction->created_at) ? Carbon::parse($paymentTransaction->created_at)->toDateTimeString() : '',
                'Time of paying advance amount online' => isset($paymentTransactionAdvance->created_at) ? Carbon::parse($paymentTransactionAdvance->created_at)->toDateTimeString() : '',
                'Time of sending E-Mandate link' => isset($enach->created_at) ? Carbon::parse($enach->created_at)->toDateTimeString() : '',
                'Time of completing E-Mandate' => isset($mandateStatusLog->created_at) ? Carbon::parse($mandateStatusLog->created_at)->toDateTimeString() : '',
                'Time of E-Mandate Failure' => '',
                'Time of insurance Link' => isset($insurance->created_at) ? Carbon::parse($insurance->created_at)->toDateTimeString() : '',
        ];

        foreach ($resignNach as $nach){
            $data = array_merge($data, ['Time of completing E-Mandate' => isset($nach->created_at) ? Carbon::parse($nach->created_at)->toDateTimeString() : '']);
            break;
        }

        foreach ($resignTAT as $tat){
            $data = array_merge($data, ['Time of RE-Sign' => isset($tat->created_at) ? Carbon::parse($tat->created_at)->toDateTimeString() : '']);
        }

        foreach ($creditReport as $credit){
            $type = $credit->applicant_type == 1 ? 'Applicant' : 'Co-Applicant';
            $data = array_merge($data, ['Time of Bureau Pull ('.$type.')' => isset($credit->created_at) ? Carbon::parse($credit->created_at)->toDateTimeString() : '']);
        }

        return $data;
    }

}