<?php
/**
 * Created by PhpStorm.
 * User: sunilkhorwal
 * Date: 2020-10-26
 * Time: 15:40
 */
namespace App\Http\Controllers;


use App\ApprovedPincodeLists;
use App\ApproverData;
use App\ConsumerApplication;
use App\ConsumerApplicationFiles;
use App\CreditReport;
use App\Customers;
use App\Devices;
use App\Disbursement;
use App\EmiPayment;
use App\EPFUANLookup;
use App\GSTPANData;
use App\Http\Controllers\Admin\ConsumerApplicationController;
use App\Imports\TestImport;
use App\KYCDetails;
use App\Merchant;
use App\MerchantBank;
use App\MerchantGstFillingData;
use App\MerchantLocation;
use App\Moratorium;
use App\NegativeArea;
use App\NegativeProfile;
use App\OnfidoIntegration;
use App\PaymentTransaction;
use App\TelecomData;
use App\TelecomDetails;
use App\UserGeolocation;
use App\Equifax;
use Carbon\Carbon;
use Firebase\JWT\JWT;
use Hashids\Hashids;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Input;
use Illuminate\Support\Facades\Storage;
use Maatwebsite\Excel\Facades\Excel;
use phpDocumentor\Reflection\DocBlock\Tags\Reference\Url;

class AnalyticsController extends Controller {

    private $request;
    private $applications = [];
    private $internal = false;

    public function __construct(Request $request){
        if ($request->path() != 'analytics/v1/credit-check' && !$request->has('internal'))
            $this->request = json_decode($this->decryptData($request->get('data')), 1);
    }

    private function encryptData($response){
        $secret = "I'J~05SjU=S+nwc)UC`I@c^psP]ZN%Xnj#lEt>YC`U*1]Ag6nuXdu.YTy<:;,j";
//        $tokenId    = base64_encode('121211212');
//        $issuedAt   = time();
//        $notBefore  = $issuedAt - 10; // + 10 remove, add commented code - testing
//        $expire     = $issuedAt + (60 * 60 * 24 * 30); //60 or (60 * 4)
//        $serverName = $this->request->getHttpHost();
//
//        $data = [
//            'iat'  => $issuedAt,
//            'jti'  => $tokenId,
//            'iss'  => $serverName,
//            'nbf'  => $notBefore,
//            'exp'  => $expire,
//            'data' => $response
//        ];

        return JWT::encode($response, $secret, 'HS256');
    }
    private function decryptData($response){
        $secret = "I'J~05SjU=S+nwc)UC`I@c^psP]ZN%Xnj#lEt>YC`U*1]Ag6nuXdu.YTy<:;,j";

        $d = JWT::decode($response, $secret, array('HS256'));
        return json_encode($d);
    }

    public function isAuth(){

        if ($this->request['user']) {
            $this->request = new Request((array)$this->request['data']);
            return true;
        }

        return false;
    }

    public function getBureauData(){

        ini_set('max_execution_time', -1);
        set_time_limit(0);
        ini_set('memory_limit', '-1');

        if (!$this->internal && !$this->isAuth()){
            return response()->json(['status' => 'OK', 'message' => 'unauthorized access']);
        }

        $applications = CreditReport::whereNull('deleted_at');

        $page = $this->request['page'] == 1 ? 0 : ($this->request['page'] ? $this->request['page'] : 0);

        $skip = ($page - 1) * 10 < 0 ? 0 : ($page - 1) * 10;

        if ($this->internal && $this->applications){
            $applications = $applications->whereIn('application_id', $this->applications);
        }

        $applications = $applications->skip($skip)->limit(10)->get();

        $bureauData = [];
        foreach ($applications as $application) {
            try {
                $applicant_type = ($application->applicant_type == 1 || !$application->applicant_type) ? '' : '_'.$application->applicant_type;
                $filePath = $application->application_id. "/" . $application->application_id . "_response" . $applicant_type . ".json";
                //$filePath = storage_path().'/response.json'; // For testing on local
                if (false !== $file = Equifax::readFile($filePath)) {
                    $response = json_decode($file, true);
                    try {
                        unset($response['soapenv:Envelope']['soapenv:Body']['sch:InquiryResponse']['sch:InquiryResponseHeader']);
                        unset($response['soapenv:Envelope']['soapenv:Body']['sch:InquiryResponse']['sch:InquiryRequestInfo']);
                        unset($response['soapenv:Envelope']['soapenv:Body']['sch:InquiryResponse']['sch:ReportData']['sch:IDAndContactInfo']);
                    }catch (\Exception $e){

                    }

                    if ($this->internal){
                        $bureauData[$application->application_id.'-'.$application->applicant_type] = [
                            'id' => $application->application_id,
                            'applicant_type' => $application->applicant_type == 2 ? 'co-applicant' : 'applicant',
                            'bureau_type' => 'consumer',
                            'results' => $response,
                            'extracted_date' => $application->created_at
                        ];
                    }else{
                        $bureauData[] = [
                            'id' => $application->application_id,
                            'applicant_type' => $application->applicant_type == 2 ? 'co-applicant' : 'applicant',
                            'bureau_type' => 'consumer',
                            'results' => $response,
                            'extracted_date' => $application->created_at
                        ];
                    }

//                    DB::connection('markytics_rds')->table('')->insert($bureauData);
                }
            }catch (\Exception $e){

            }
        }

        if ($this->internal)
            return  $bureauData;

        $responsed = $this->encryptData([
            'status' => 'OK',
            'results' => $bureauData
        ]);


        return $bureauData ? response()->json($responsed, 200) : response()->json([
            'no_data_found' => true
        ], 200);
    }

    public function customerDemographic(){

        if (!$this->isAuth()){
            return response()->json(['status' => 'OK', 'message' => 'unauthorized access']);
        }

        $applications = ConsumerApplication::with('offer','application_element')->whereNull('is_deleted');

//        $app = DB::connection('markytics_rds')->table('consumer_application')->get();
//        $skip = count($app);
        $page = $this->request['page'] == 1 ? 0 : ($this->request['page'] ? $this->request['page'] : 0);
        $skip = ($page - 1) * 10 < 0 ? 0 : ($page - 1) * 10;

        $applications = $applications->skip($skip)->limit(10)->get();

        $response = [];
        foreach ($applications as $application) {

            try {
                $creditReport = CreditReport::where('application_id', $application->id)->where('applicant_type', 1)->first(['Score']);

                $response['consumer_application'][] = [
                    'id' => $application->id,
                    'merchant_id' => $application->merchant_id,
                    //'Name' => $application->name,
                    //'Mobile Number' => $application->mobile_number,
                    'Gender' => $application->gender,
                    'tenure' => $application->offer ? $application->offer->tenure : $application->tenure,
                    'Purpose' => $application->category ? $application->category->name : $application->category,
                    'aadhar_card' => $application->aadhaar_card,
                    'loan_amount' => $application->loan_amount,
                    'approved_amount' => $application->approved_amount,
                    'marital_status' => $application->marital_status,//
                    'monthly_income' => $application->monthly_income,
                    'credit_score' => $creditReport ? $creditReport->Score : '',
                    //'pan_card' => $application->pan_card,
                    'dob' => $application->dob,
                    'current_address_state' => $application->current_address_state,
                    'current_address_city' => $application->current_address_city,
                    'current_address_pincode' => $application->current_address_pincode,
                    'permanent_address_state' => $application->permanent_address_state,
                    'permanent_address_city' => $application->permanent_address_city,
                    'permanent_address_pincode' => $application->permanent_address_pincode,
                    'employee_status' => $application->employee_status,
                    'work_address_state' => $application->work_address_state,
                    'work_address_city' => $application->work_address_city,
                    'work_address_pincode' => $application->work_address_pincode,
                    //'co_applicant_name' => $application->co_applicant_name,
                    //'co_applicant_mobile_number' => $application->co_applicant_mobile_number,
                    'co_applicant_relationship' => $application->co_applicant_relationship,
                    'co_applicant_pan_card' => $application->co_applicant_pan_card,
                    'co_applicant_aadhaar_card' => $application->co_applicant_aadhaar_card,
                    'co_applicant_dob' => $application->co_applicant_dob,
                    'co_applicant_address_state' => $application->co_applicant_address_state,
                    'co_applicant_address_city' => $application->co_applicant_address_city,
                    'co_applicant_address_pincode' => $application->co_applicant_address_pincode,
                    'co_applicant_address_status' => $application->co_applicant_address_status,
                    'co_applicant_employee_status' => $application->co_applicant_employee_status,
                    'co_applicant_monthly_income' => $application->co_applicant_monthly_income,
                    'co_applicant_experience' => $application->co_applicant_experience,
                    'is_approved' => !in_array($application->state, array('form completed', 'incomplete form', 'rejected', 'aborted', 'in-process', 'waiting for documents', 'waiting for decision')) ? 1 : 0,
                    'is_rejected' => $application->state == 'rejected' ? 1 : 0,
                    'applicant_id' => $application->applicant_id,
                    'co_applicant_id' => $application->co_applicant_id,
                    'updated_from' => $application->updated_by,
                ];

                $response['consumer_application_element'][] = [
                    'id' => $application->id,
                    'industry' => $application->application_element ? $application->application_element->industry : '',
                    'profession_type' => $application->application_element ? $application->application_element->profession_type : '',
                    'co_applicant_industry' => $application->application_element ? $application->application_element->co_applicant_industry : '',
                    'co_applicant_profession_type' => $application->application_element ? $application->application_element->co_applicant_profession_type : '',
                    'negative_profile' => $application->application_element ? $application->application_element->negative_profile : '',
                    'gst_number' => $application->application_element ? $application->application_element->gst_number : '',
                    'current_address_lat_long' => $application->application_element ? $application->application_element->current_address_lat_long : '',
                    'permanent_address_lat_long' => $application->application_element ? $application->application_element->permanent_address_lat_long : '',
                    //'contact' => $application->application_element ? $application->application_element->contact : '',
                    'bank_id' => $application->application_element ? $application->application_element->bank_id : '',
                    'amount_over_due' => $application->application_element ? $application->application_element->amount_over_due : '',
                    'duration' => $application->application_element ? $application->application_element->duration : '',
                    'payment_transfer_date' => $application->application_element ? $application->application_element->payment_transfer_date : '',
                    'hold_amount' => $application->application_element ? $application->application_element->hold_amount : '',
                    'withhold_adjusted' => $application->application_element ? $application->application_element->withhold_adjusted : '',
                    'reason_for_loan' => $application->application_element ? $application->application_element->reason_for_loan : '',
                    //'nominee_full_name' => $application->application_element ? $application->application_element->nominee_full_name : '',
                    'nominee_dob' => $application->application_element ? $application->application_element->nominee_dob : '',
                    'nominee_gender' => $application->application_element ? $application->application_element->nominee_gender : '',
                    'nominee_relationship' => $application->application_element ? $application->application_element->nominee_relationship : '',
                    //'nominee_mobile_number' => $application->application_element ? $application->application_element->nominee_mobile_number : '',
                    'video_kyc' => $application->application_element ? $application->application_element->video_kyc : '',
                    'last_emi_date' => $application->application_element ? $application->application_element->last_emi_date : '',
                    'total_amount_due' => $application->application_element ? $application->application_element->total_amount_due : '',
                    'emi_pending' => $application->application_element ? $application->application_element->emi_pending : ''
                ];
            }catch (\Exception $e){

            }
        }

        DB::connection('markytics_rds')->table('consumer_application')->insert($response['consumer_application']);
        DB::connection('markytics_rds')->table('consumer_application_element')->insert($response['consumer_application_element']);

        $responsed = $this->encryptData([
            'status' => 'OK',
            'results' => $response
        ]);

        return response()->json($responsed, 200);
    }

    public function merchantDetails(){

        if (!$this->isAuth()){
            return response()->json(['status' => 'OK', 'message' => 'unauthorized access']);
        }


        if ($this->request['type']) {

            $page = $this->request['page'] == 1 ? 0 : ($this->request['page'] ? $this->request['page'] : 0);

            $skip = ($page - 1) * 10 < 0 ? 0 : ($page - 1) * 10;


            if ($this->request['type'] == 'merchant') {
//                $app=DB::connection('markytics_rds')->table('merchant')->get();
//                $skip=count($app);

                $merchants = Merchant::where('status', 'active')->where('is_guest_active', 1)->skip($skip)->limit(10)->get();

                $response = [];
                foreach ($merchants as $merchant) {
                    $response[] = [
                        'merchant_id' => $merchant->id,
                        //'merchant_name' => $merchant->merchant_name,
                        //'contact_number' => $merchant->contact_number,
                        'type_of_firm' => $merchant->type_of_firm,
                        'gender' => $merchant->gender,
                        'gst_number' => $merchant->gst_number,
                        'gst_is_defaulter' => $merchant->gst_is_defaulter,
                        'nature_of_business' => $merchant->nature_of_business,
                        'avg_ticket_size' => $merchant->avg_ticket_size,
                        'pincode' => $merchant->pincode,
                        'dob' => $merchant->dob,
                        'pan_card' => $merchant->pan_card,
                        'city' => $merchant->city,
                        'months_gst_filled' => $merchant->months_gst_filled,
                        'is_physical_verification' => $merchant->is_physical_verification,
                        'high_risk' => $merchant->high_risk,
                        'vulnerable_merchant' => $merchant->vulnerable_merchant,
                        'vulnerable_merchant_date' => $merchant->vulnerable_merchant_date,
                        'pre_approval' => $merchant->pre_approval,
                        'pending_adjustable_amount' => $merchant->pending_adjustable_amount,
                        'pending_collection_report' => $merchant->pending_collection_report,
                        'year_in_business' => $merchant->year_in_business,
                        'location_audit' => $merchant->location_audit,
                        'liability_percent' => $merchant->liability_percent,
                        'insurance_premium' => $merchant->insurance_premium,
                    ];
                }

                DB::connection('markytics_rds')->table('merchant')->insert($response);

                $responsed = $this->encryptData([
                    'status' => 'OK',
                    'results' => $response
                ]);

                return response()->json($responsed, 200);

            }else if ($this->request['type'] == 'merchant_location') {
//                $app=DB::connection('markytics_rds')->table('merchant_location')->get();
//                $skip=count($app);
                $merchantLocations = MerchantLocation::skip($skip)->limit(10)->get();

                $response = [];

                foreach ($merchantLocations as $merchantLocation) {
                    $response[] = [
                        'merchant_id' => $merchantLocation->merchant_id,
                        //'name' => $merchantLocation->name,
                        //'phone' => $merchantLocation->phone,
                        'location' => $merchantLocation->location,
                        'gst_number' => $merchantLocation->gst_number,
                        'is_processing_fee' => $merchantLocation->is_processing_fee,
                        'is_physical_verification' => $merchantLocation->is_physical_verification,
                        'state' => $merchantLocation->state
                    ];
                }

                DB::connection('markytics_rds')->table('merchant_location')->insert($response);

                $responsed = $this->encryptData([
                    'status' => 'OK',
                    'results' => $response
                ]);

                return response()->json($responsed, 200);

            }else if ($this->request['type'] == 'merchant_bank_details') {
//                $app=DB::connection('markytics_rds')->table('merchant_bank_details')->get();
//                $skip=count($app);

                $merchantBankDetails = MerchantBank::skip($skip)->limit(10)->get();
                $response = [];

                foreach ($merchantBankDetails as $merchantBankDetail) {
                    $response[] = [
                        'merchant_id' => $merchantBankDetail->merchant_id,
                        'bank_name' => $merchantBankDetail->bank_name,
                        'account_holder' => $merchantBankDetail->account_holder,
                        'branch_name' => $merchantBankDetail->branch_name,
                        'ifsc_code' => $merchantBankDetail->ifsc_code,
                        'karza_response' => $merchantBankDetail->karza_response,
                    ];
                }

                DB::connection('markytics_rds')->table('merchant_bank_details')->insert($response);

                $responsed = $this->encryptData([
                    'status' => 'OK',
                    'results' => $response
                ]);

                return response()->json($responsed, 200);

            }else if ($this->request['type'] == 'disbursements') {
//                $app=DB::connection('markytics_rds')->table('disbursements')->get();
//                $skip=count($app);

                $disbursements = Disbursement::skip($skip)->limit(10)->get();
                $response = [];

                foreach ($disbursements as $disbursement) {
                    $response[] = [
                        'application_id' => $disbursement->application_id,
                        'merchant_id' => $disbursement->merchant_id,
                        'disbursed' => $disbursement->disbursed,
                        'emi_amount_adjusted' => $disbursement->emi_amount_adjusted,
                        'total_amount_adjusted' => $disbursement->total_amount_adjusted,
                        'upfront_from_credit_fair' => $disbursement->upfront_from_credit_fair,
                        'is_cancellation_adjusted' => $disbursement->is_cancellation_adjusted,
                        'emi_adjusted' => $disbursement->emi_adjusted,
                        'adjusted_amount' => $disbursement->adjusted_amount,
                        'pending_adjustable_amount' => $disbursement->pending_adjustable_amount,
                        'withhold' => $disbursement->withhold
                    ];
                }

                DB::connection('markytics_rds')->table('disbursements')->insert($response);

                $responsed = $this->encryptData([
                    'status' => 'OK',
                    'results' => $response
                ]);

                return response()->json($responsed, 200);

            }else if ($this->request['type'] == 'emi_payments') {
//                $app=DB::connection('markytics_rds')->table('emi_payments')->get();
//                $skip=count($app);

                $emiPayments = EmiPayment::skip($skip)->limit(10)->get();

                $response = [];
                foreach ($emiPayments as $emiPayment) {
                    $response[] = [
                        'application_id' => $emiPayment->application_id,
                        'emi_amount' => $emiPayment->emi_amount,
                        'bounce_charges' => $emiPayment->bounce_charges,
                        'delay_penalty' => $emiPayment->delay_penalty,
                        'payment_mode' => $emiPayment->payment_mode,
                        'payment_date' => $emiPayment->payment_date,
                        'payment_status' => $emiPayment->payment_status,
//                        'bounce_charge_igst' => $emiPayment->bounce_charge_igst,
//                        'bounce_charge_cgst' => $emiPayment->bounce_charge_cgst,
//                        'bounce_charge_sgst' => $emiPayment->bounce_charge_sgst,
//                        'net_bounce_charge' => $emiPayment->net_bounce_charge,
//                        'penal_interest_igst' => $emiPayment->penal_interest_igst,
//                        'penal_interest_cgst' => $emiPayment->penal_interest_cgst,
//                        'penal_interest_sgst' => $emiPayment->penal_interest_sgst,
//                        'net_penal_interest' => $emiPayment->net_penal_interest,
//                        'final_bounce_charge' => $emiPayment->final_bounce_charge,
//                        'final_penal_interest' => $emiPayment->final_penal_interest,
                        'due_date' => $emiPayment->due_date,
                        'total_payable' => $emiPayment->total_payable,
                        'amount_due' => $emiPayment->amount_due,
                        'delay_days' => $emiPayment->delay_days,
                        'moratorium' => $emiPayment->moratorium,
                        'moratorium_amount' => $emiPayment->moratorium_amount,
                        'dpd_reason' => $emiPayment->dpd_reason
                    ];
                }

                DB::connection('markytics_rds')->table('emi_payments')->insert($response);

                $responsed = $this->encryptData([
                    'status' => 'OK',
                    'results' => $response
                ]);

                return response()->json($responsed, 200);

            }else if ($this->request['type'] == 'payment_transactions') {
//                $app=DB::connection('markytics_rds')->table('payment_transactions')->get();
//                $skip=count($app);
                $paymentTransactions = PaymentTransaction::skip($skip)->limit(10)->get();
                $response = [];

                foreach ($paymentTransactions as $paymentTransaction) {
                    $response[] = [
                        'application_id' => $paymentTransaction->application_id,
                        'emi_amount' => $paymentTransaction->emi_amount,
                        'amount' => $paymentTransaction->amount,
                        'mode' => $paymentTransaction->mode,
                        'payment_date' => $paymentTransaction->payment_date,
                        'wallet_name' => $paymentTransaction->wallet_name
                    ];
                }

                DB::connection('markytics_rds')->table('payment_transactions')->insert($response);

                $responsed = $this->encryptData([
                    'status' => 'OK',
                    'results' => $response
                ]);

                return response()->json($responsed, 200);

            }else if ($this->request['type'] == 'moratorium') {
//                $app=DB::connection('markytics_rds')->table('moratorium')->get();
//                $skip=count($app);

                $moratoriums = Moratorium::skip($skip)->limit(10)->get();
                $response = [];

                foreach ($moratoriums as $moratorium) {
                    $response[] = [
                        'emi_date' => $moratorium->emi_date,
                        'interest_rate' => $moratorium->interest_rate,
                        'status' => $moratorium->status,
                    ];
                }

                DB::connection('markytics_rds')->table('moratorium')->insert($response);

                $responsed = $this->encryptData([
                    'status' => 'OK',
                    'results' => $response
                ]);

                return response()->json($responsed, 200);
            }
        }

        return response()->json([
            'status' => 'OK',
            'results' => []
        ], 200);
    }

    public function customerKYCData(){

        if (!$this->isAuth()){
            return response()->json(['status' => 'OK', 'message' => 'unauthorized access']);
        }

        if ($this->request['type']) {

            $page = $this->request['page'] == 1 ? 0 : ($this->request['page'] ? $this->request['page'] : 0);

            $skip = ($page - 1) * 10 < 0 ? 0 : ($page - 1) * 10;


            if ($this->request['type'] == 'onfido') {
//                $app=DB::connection('markytics_rds')->table('onfido')->get();
//                $skip=count($app);

                $onfido = OnfidoIntegration::skip($skip)->limit(10)->get();

                $response = [];
                foreach ($onfido as $onfidoItem) {
                    $response[] = [
                        'application_id' => $onfidoItem->application_id,
                        'applicant_type' => $onfidoItem->applicant_type,
                        'applicant_token_id' => $onfidoItem->applicant_token_id,
                        'check_response' => $onfidoItem->check_response,
                        'list_live_photo' => $onfidoItem->list_live_photo,
                        'list_live_video' => $onfidoItem->list_live_video
                    ];
                }

                DB::connection('markytics_rds')->table('onfido')->insert($response);

                $responsed = $this->encryptData([
                    'status' => 'OK',
                    'results' => $response
                ]);

                return response()->json($responsed, 200);

            }else if ($this->request['type'] == 'kyc_details') {
//                $app=DB::connection('markytics_rds')->table('kyc_details')->get();
//                $skip=count($app);
                $kycDetails = KYCDetails::skip($skip)->limit(10)->get();

                $response = [];
                foreach ($kycDetails as $kycDetail) {
                    $res = json_decode($kycDetail->response, true);
                    $response[] = [
                        'application_id' => $kycDetail->application_id,
                        'id_proof_type' => isset($res[0]['id_proof_type']) ? $res[0]['id_proof_type'] : null,
                        'gender' => isset($res[0]['gender']) ? $res[0]['gender'] : null,
                        //'name' => $kycDetail->name,
                        'age' => isset($res[0]['age']) ? $res[0]['age'] : null,
                        'dob' => isset($res[0]['dob']) ? $res[0]['dob'] : null,
                    ];
                }

                DB::connection('markytics_rds')->table('kyc_details')->insert($response);

                $responsed = $this->encryptData([
                    'status' => 'OK',
                    'results' => $response
                ]);

                return response()->json($responsed, 200);

            }else if ($this->request['type'] == 'credit_report') {
//                $app=DB::connection('markytics_rds')->table('credit_report')->get();
//                $skip=count($app);

                $creditReport = CreditReport::skip($skip)->limit(10)->get();

                $response = [];
                foreach ($creditReport as $creditReportItem) {
                    $response[] = [
                        'application_id' => $creditReportItem->application_id,
                        'Score' => $creditReportItem->Score,
                        'NoOfActiveAccounts' => $creditReportItem->NoOfActiveAccounts,
                        'TotalPastDue' => $creditReportItem->TotalPastDue,
                        'Past12Months' => $creditReportItem->Past12Months,
                        'Past24Months' => $creditReportItem->Past24Months,
                        //'VoterID' => $creditReportItem->VoterID,
                        'DateOfBirth' => $creditReportItem->DateOfBirth,
                        //'PANIdsList' => $creditReportItem->PANIdsList,
                        //'NationalIdsList' => $creditReportItem->NationalIdsList,
                        //'panValidation' => $creditReportItem->panValidation,
                        'applicant_type' => $creditReportItem->applicant_type,
                        'foirIncludingCFEMI' => $creditReportItem->foirIncludingCFEMI,
                        'foirExcludingCFEMI' => $creditReportItem->foirNAIncludingCFEMI,
                    ];
                }

                DB::connection('markytics_rds')->table('credit_report')->insert($response);

                $responsed = $this->encryptData([
                    'status' => 'OK',
                    'results' => $response
                ]);

                return response()->json($responsed, 200);
            }
        }

        return response()->json([
            'status' => 'OK',
            'results' => []
        ], 200);

    }

    public function otherAvailableData(){

        if (!$this->isAuth()){
            return response()->json(['status' => 'OK', 'message' => 'unauthorized access']);
        }

        if ($this->request['type']) {

            $page = $this->request['page'] == 1 ? 0 : ($this->request['page'] ? $this->request['page'] : 0);

            $skip = ($page - 1) * 10 < 0 ? 0 : ($page - 1) * 10;


            if ($this->request['type'] == 'employment_verification') {
//                $app=DB::connection('markytics_rds')->table('employment_verification')->get();
//                $skip=count($app);

                $EPFUANLookup = EPFUANLookup::skip($skip)->limit(10)->get();

                $response = [];
                foreach ($EPFUANLookup as $EPFUANLookupItem) {
                    $response[] = [
                        'application_id' => $EPFUANLookupItem->application_id,
                        'entityID' => $EPFUANLookupItem->entity_id,
                        //'totalMembers' => $EPFUANLookupItem->,
                        'employerName' => $EPFUANLookupItem->employerName,
                        'uan_no' => $EPFUANLookupItem->uan_no,
                        'data' => $EPFUANLookupItem->data
                        //'wageMonth' => $EPFUANLookupItem->,
                        //'contact' => $EPFUANLookupItem->mobile,
                        //'organizationName' => $EPFUANLookupItem->,
                        //'isEmployed' => $EPFUANLookupItem->
                    ];
                }

                DB::connection('markytics_rds')->table('employment_verification')->insert($response);

                $responsed = $this->encryptData([
                    'status' => 'OK',
                    'results' => $response
                ]);

                return response()->json($responsed, 200);

            }else if ($this->request['type'] == 'device') {
//                $app=DB::connection('markytics_rds')->table('device')->get();
//                $skip=count($app);

                $devices = Devices::skip($skip)->limit(10)->get();

                $response = [];
                foreach ($devices as $device) {
                    $response[] = [
                        'model' => $device->model,
                        'brand' => $device->brand,
                        'product' => $device->product,
                        'device' => $device->device,
                        'fingerprint' => $device->fingerprint
                    ];
                }

                DB::connection('markytics_rds')->table('device')->insert($response);

                $responsed = $this->encryptData([
                    'status' => 'OK',
                    'results' => $response
                ]);

                return response()->json($responsed, 200);

            }else if ($this->request['type'] == 'gps_location_tracking') {
//                $app=DB::connection('markytics_rds')->table('gps_location_tracking')->get();
//                $skip=count($app);
                $userGeolocation = UserGeolocation::skip($skip)->limit(10)->get();

                $response = [];
                foreach ($userGeolocation as $userGeolocationItem) {
                    $response[] = [
                        'id' => $userGeolocationItem->id,
                        'user_id' => $userGeolocationItem->user_id,
                        'latitude' => $userGeolocationItem->latitude,
                        'longitude' => $userGeolocationItem->longitude,
                        'created_at' => $userGeolocationItem->created_at,
                        'updated_at' => $userGeolocationItem->updated_at,
                    ];
                }

                DB::connection('markytics_rds')->table('gps_location_tracking')->insert($response);

                $responsed = $this->encryptData([
                    'status' => 'OK',
                    'results' => $response
                ]);

                return response()->json($responsed, 200);

            }else if ($this->request['type'] == 'negative_profile') {
//                $app=DB::connection('markytics_rds')->table('negative_profile')->get();
//                $skip=count($app);

                $negativeProfile = NegativeProfile::skip($skip)->limit(10)->get();

                $response = [];
                foreach ($negativeProfile as $negativeProfileItem) {
                    $response[] = [
                        'name' => $negativeProfileItem->name,
                        'is_hard_reject' => $negativeProfileItem->is_hard_reject
                    ];
                }

                DB::connection('markytics_rds')->table('negative_profile')->insert($response);

                $responsed = $this->encryptData([
                    'status' => 'OK',
                    'results' => $response
                ]);

                return response()->json($responsed, 200);

            }else if ($this->request['type'] == 'negative_area') {
//                $app=DB::connection('markytics_rds')->table('negative_area')->get();
//                $skip=count($app);

                $negativeArea = NegativeArea::skip($skip)->limit(10)->get();

                $response = [];
                foreach ($negativeArea as $negativeAreaItem) {
                    $response[] = [
                        'state' => $negativeAreaItem->state,
                        'city' => $negativeAreaItem->city,
                        'area' => $negativeAreaItem->area,
                        'location' => $negativeAreaItem->location,
                        'pincode' => $negativeAreaItem->pincode,
                        'comment' => $negativeAreaItem->comment,
                        'level' => $negativeAreaItem->level
                    ];
                }

                DB::connection('markytics_rds')->table('negative_area')->insert($response);

                $responsed = $this->encryptData([
                    'status' => 'OK',
                    'results' => $response
                ]);

                return response()->json($responsed, 200);

            }else if ($this->request['type'] == 'gst_filing') {
//                $app=DB::connection('markytics_rds')->table('gst_filing')->get();
//                $skip=count($app);

                $merchantGstFillingData = MerchantGstFillingData::skip($skip)->where('type','merchant_id')->limit(10)->get();

                $response = [];
                foreach ($merchantGstFillingData as $merchantGstFillingDataItem) {
                    $response[] = [
                        'merchant_id' => $merchantGstFillingDataItem->merchant_id,
                        'filing_frequency' => $merchantGstFillingDataItem->filing_frequency,
                        'financial_year' => $merchantGstFillingDataItem->financial_year,
                        'status' => $merchantGstFillingDataItem->status,
                        'is_delay' => $merchantGstFillingDataItem->is_delay,
                        'valid' => $merchantGstFillingDataItem->valid,
                    ];
                }

                DB::connection('markytics_rds')->table('gst_filing')->insert($response);

                $responsed = $this->encryptData([
                    'status' => 'OK',
                    'results' => $response
                ]);

                return response()->json($responsed, 200);

            }else if ($this->request['type'] == 'approved_pincodes') {
//                $app=DB::connection('markytics_rds')->table('approved_pincodes')->get();
//                $skip=count($app);

                $approvedPincodes = ApprovedPincodeLists::skip($skip)->limit(10)->get();

                $response = [];
                foreach ($approvedPincodes as $approvedPincodesItem) {
                    $response[] = [
                        'pincodes' => $approvedPincodesItem->pin_codes,
                    ];
                }

                DB::connection('markytics_rds')->table('approved_pincodes')->insert($response);

                $responsed = $this->encryptData([
                    'status' => 'OK',
                    'results' => $response
                ]);

                return response()->json($responsed, 200);

            } else if ($this->request['type'] == 'pincode_approvals') {
//                $app=DB::connection('markytics_rds')->table('pincode_approvals')->get();
//                $skip=count($app);

                $approverData = ApproverData::skip($skip)->limit(10)->get();

                $response = [];
                foreach ($approverData as $approverDataItem) {
                    $response[] = [
                        'id' => $approverDataItem->id,
                        'application_id' => $approverDataItem->application_id,
                        'reason' => $approverDataItem->reason,
                    ];
                }

                DB::connection('markytics_rds')->table('pincode_approvals')->insert($response);

                $responsed = $this->encryptData([
                    'status' => 'OK',
                    'results' => $response
                ]);

                return response()->json($responsed, 200);

            } else if ($this->request->get('type') == 'gst_pan_validation') {
//                $app=DB::connection('markytics_rds')->table('gst_pan_validation')->get();
//                $skip=count($app);

                $gst_pan_data = GSTPANData::skip($skip)->limit(10)->get();

                $response = [];
                foreach ($gst_pan_data as $gst_pan_item) {
                    $response[] = [
                        'application_id' => $gst_pan_item->application_id,
                        'data' => $gst_pan_item->data,
                        'type' => $gst_pan_item->type,
                        'pan' => $gst_pan_item->pan,
                    ];
                }

                DB::connection('markytics_rds')->table('gst_pan_validation')->insert($response);

                $responsed = $this->encryptData([
                    'status' => 'OK',
                    'results' => $response
                ]);

                return response()->json($responsed, 200);
            } else if ($this->request->get('type') == 'telecom_data') {
//                $app=DB::connection('markytics_rds')->table('telecom_data')->get();
//                $skip=count($app);

                $telecom_data = TelecomData::skip($skip)->limit(10)->get();

                $response = [];
                foreach ($telecom_data as $telecom_item) {
                    $response[] = [
                        'mobile_number' => $telecom_item->mobile_number,
                        'telecom_response' => $telecom_item->telecom_response,
                    ];
                }

                DB::connection('markytics_rds')->table('telecom_data')->insert($response);

                $responsed = $this->encryptData([
                    'status' => 'OK',
                    'results' => $response
                ]);

                return response()->json($responsed, 200);
            }
        }

        return response()->json([
            'status' => 'OK',
            'results' => []
        ], 200);
    }

    public function getCustomerFinBitBankFile(){

        if (!$this->isAuth()){
            return response()->json(['status' => 'OK', 'message' => 'unauthorized access']);
        }

        $applications = ConsumerApplicationFiles::where('file_type', 'like', '%_Excel_File_%')->orWhere('file_type', 'like', '%fraud_report_%');

        $page = $this->request['page'] == 1 ? 0 : ($this->request['page'] ? $this->request['page'] : 0);

        $skip = ($page - 1) * 10 < 0 ? 0 : ($page - 1) * 10;
        $applications = $applications->skip($skip)->limit(10)->get(['consumer_application_id as id','file_type']);

        $response = [];
        $hashids         = new Hashids('Creditfair by CS', 20);
        foreach ($applications as $application) {

            try {

                $link = asset('documents/' . $hashids->encode($application->id) . '/' . base64_encode($application->file_type));

                $response[] = [
                    'id' => $application->id,
                    'file' => $application->file_type,
                    'link' => $link
                ];
            }catch (\Exception $e){

            }
        }

        $responsed = $this->encryptData([
            'status' => 'OK',
            'results' => $response
        ]);

        return response()->json($responsed, 200);
    }

    public function getFinbitDetails($id){

        $files = ConsumerApplicationFiles::where('consumer_application_id', $id)->where('file_type', 'like','%_Excel_File_%')->get(['id', 'consumer_application_id', 'file_path']);

        $response = [];
        foreach ($files as $file) {

            $collection = Excel::toCollection(new TestImport(new Request(['from' => __FUNCTION__])), $file->file_path, 's3');
            //$collection = Excel::toCollection(new TestImport(new Request(['from' => __FUNCTION__])), storage_path().'/SBI_Excel_File_1615896864_58.xls');

            $date = '';
            foreach ($collection[0] as $item) {

                if ($item[0]) {
                    if ($item[0] == 'Statement Upload') {
                        $dateString = explode('to', $item[1]);
                        $date = Carbon::parse(trim($dateString[0]))->toDateString();
                    }

                    if (isset($inner) || $item[0] == 'Particulars') {
                        $inner = true;
                        if ($item[0] == 'Particulars')
                            continue;
                        foreach ($item as $_item) {
                            if (!is_string($_item)) {
                                $response[$file->id][$item[0]][] = ['date' => $date, 'amount' => $_item];
                                $date = Carbon::parse($date)->addMonth(1)->toDateString();
                            }
                        }
                    } else {
                        if (!isset($response[$file->id]['id'])){
                            $response[$file->id]['id'] = $id;
                        }
                        $response[$file->id][$item[0]] = $item[1];
                    }
                }
            }
            unset($inner);
        }

        return $response;
    }

    public function getCreditApi(){

        $ids = explode(',',Input::get('id'));

        $applications = ConsumerApplication::with('devices','epf_uan_lookup','creditReport','foir', 'gst_pan_data','application_element')->whereIn('id', $ids)->get(['id','user_id','gender','name','tenure','purpose','loan_amount','approved_amount','marital_status','employee_status', 'co_applicant_employee_status','monthly_income','updated_by','dob']);

        $this->request['page'] = 0;
        $this->applications = $ids;
        $this->internal = true;
        $bureauFile = $this->getBureauData();


        $bureau = [];
        $bank = [];
        $consumer_application = [];
        foreach ($applications as $application) {

            $finbitDetails = $this->getFinbitDetails($application->id);


            if ($application->creditReport) {

                foreach ($application->creditReport as $creditReport) {


                    $extractedDateData = $bureauFile[$creditReport->application_id . '-' . $creditReport->applicant_type]['extracted_date'];
                    $bureauData = $bureauFile[$creditReport->application_id . '-' . $creditReport->applicant_type]['results']['soapenv:Envelope']['soapenv:Body']['sch:InquiryResponse']['sch:ReportData'];
                    $scoreData = $bureauData['sch:Score'];
                    $accountSummaryData = $bureauData['sch:AccountSummary'];
                    $recentActivitiesData = $bureauData['sch:RecentActivities'];
                    $otherKeyIndData = $bureauData['sch:OtherKeyInd'];
                    $enquirySummaryData = $bureauData['sch:EnquirySummary'];
                    $accountDetailsData = $bureauData['sch:AccountDetails']['sch:Account'];

                    $accounts = [];
                    foreach ($accountDetailsData as $account) {

                        $history = [];
                        foreach ($account['sch:History48Months']['sch:Month'] as $month) {
                            $history[] = $month;
                        }

                        $accounts[] = [
                            'seq' => $account['@seq'],
                            'AccountType' => $account['sch:AccountType'],
                            'OwnershipType' => $account['sch:OwnershipType'],
                            'history' => isset($history) ? $history : [],
                        ];
                    }

                    $bureau[] = [
                        'id' => $creditReport->application_id,
                        'applicant_type' => $creditReport->applicant_type,
                        'extracted_date' => date('d-m-Y', strtotime($extractedDateData)),
                        'extracted_time' => date('G:i:s', strtotime($extractedDateData)),
                        'credit_score' => $scoreData['sch:Value'],
                        'average_open_balance' => $accountSummaryData['sch:AverageOpenBalance'],
                        'most_severe_status_within_24_months' => $accountSummaryData['sch:MostSevereStatusWithIn24Months'],
                        'recent_account' => $accountSummaryData['sch:RecentAccount'],
                        'no_of_accounts' => $accountSummaryData['sch:NoOfAccounts'],
                        'no_of_active_accounts' => $accountSummaryData['sch:NoOfActiveAccounts'],
                        'no_of_past_due_accounts' => $accountSummaryData['sch:NoOfPastDueAccounts'],
                        'no_of_write_offs' => $accountSummaryData['sch:NoOfWriteOffs'],
                        'no_of_zero_balance_accounts' => $creditReport->zeroBalanceAccount,
                        'total_past_due' => $accountSummaryData['sch:TotalPastDue'],
                        'account_deliquent' => $recentActivitiesData['sch:AccountsDeliquent'],
                        'account_opened' => $recentActivitiesData['sch:AccountsOpened'],
                        'total_inquiries' => $recentActivitiesData['sch:TotalInquiries'],
                        'account_updated' => $recentActivitiesData['sch:AccountsUpdated'],
                        'age_of_oldest_trade' => $otherKeyIndData['sch:AgeOfOldestTrade'],
                        'number_of_open_trades' => $otherKeyIndData['sch:NumberOfOpenTrades'],
                        'past_12_months' => $enquirySummaryData['sch:Past12Months'],
                        'past_24_months' => $enquirySummaryData['sch:Past24Months'],
                        'past_30_days' => $enquirySummaryData['sch:Past30Days'],
                        'purpose' => $enquirySummaryData['sch:Purpose'],
                        'accounts' => $accounts
                    ];
                }
            }

            $gst_pan_data = [];

            if (isset($application->gst_pan_data)) {
                foreach ($application->gst_pan_data as $gst_pan_item) {
                    $gst_pan_data[] = [
                        'application_id' => $gst_pan_item->application_id,
                        'data' => $gst_pan_item->data,
                        'type' => $gst_pan_item->type,
                        'pan' => $gst_pan_item->pan,
                    ];
                }
            }

            $epf_data = [];

            if (isset($application->epf_uan_lookup)) {
                foreach ($application->epf_uan_lookup as $epf_uan_lookup) {
                    $epf_data[] = [
                        'application_id' => $epf_uan_lookup->application_id,
                        'entityID' => $epf_uan_lookup->entity_id,
                        'employerName' => $epf_uan_lookup->employerName,
                        'uan_no' => $epf_uan_lookup->uan_no,
                        'data' => $epf_uan_lookup->data
                    ];
                }
            }

            $devices = [];

            if (isset($application->devices)) {
                foreach ($application->devices as $device) {
                    $devices[] = [
                        'model' => $device->model,
                        'brand' => $device->brand,
                        'product' => $device->product,
                        'device' => $device->device,
                        'fingerprint' => $device->fingerprint
                    ];
                }
            }

            $installed_app = false;
            $sms_data = false;
            $negative_location = false;
            $consumerApplicationController = new ConsumerApplicationController();
            $userAppData = $consumerApplicationController->getUserFormattedData($application->user_id);
            if($userAppData){
                $installed_app = $userAppData['risky'] ? true : false;
                $sms_data = $userAppData['charges'] || $userAppData['salary'] ? true : false;
            }


            $NegativeAreaResponse = $consumerApplicationController->getNegativeAreaList([
                'current_pincode' => $application->current_pincode,
                'installation_pincode' => $application->application_element->installation_address_pincode,
            ]);
            if ($NegativeAreaResponse && isset($NegativeAreaResponse->level) && $NegativeAreaResponse->level == 1) {
                $negative_location = true;
            }

            $consumer_application[] = [
                'id' => $application->id,
                'Gender' => $application->gender,
                'tenure' => $application->tenure,
                'Purpose' => $application->purpose,
                'loan_amount' => $application->loan_amount,
                'approved_amount' => $application->approved_amount,
                'marital_status' => $application->martial_status,
                'employment_status' => $application->employee_status,
                'dob' => $application->dob,
                'employee_status' => $application->employee_status,
                'co_applicant_employee_status' => $application->co_applicant_employee_status,
                'monthly_income' => $application->monthly_income,
                'updated_from' => $application->updated_by,
                'foir_including_cf_emi' => $application->credit_report ? $application->credit_report->foirIncludingCFEMI : '',
                'foir_excluding_cf_emi' => $application->credit_report ? $application->credit_report->foirExcludingCFEMI : '',
                'gst_pan_validation' => $gst_pan_data,
                'epf_data' => $epf_data,
                'sms_data' => $sms_data,
                'contact_list' => $this->getContactListCount($application->user_id),
                'installed_app' => $installed_app,
                'device_data' => $devices,
                'is_negative_location' => $negative_location,
            ];


            $bank[] = $finbitDetails;
        }

        return $response = [
            'bureau' => $bureau,
            'consumer_application' => $consumer_application,
            'bank' => $bank
            ];
    }

    public function getContactListCount($user_id){

        try {
            $s3 = Storage::disk('s3');

            $userInfo = $s3->listContents("info/" . $user_id . '/');

            foreach ($userInfo as $userInfoItem) {
                $date = $userInfoItem['basename'];
                $userFile = $s3->listContents("info/" . $user_id . '/' . $date . '/');

                foreach ($userFile as $userFileItem) {
                    $file = $s3->get("info/" . $user_id . '/' . $date . '/' . $userFileItem['basename']);
                    $decrypted_file = wall_decrypt($file);
                    $report_data = json_decode($decrypted_file, true);
                    if (isset($report_data['data'])) {
                        $userData = json_decode($report_data['data'], true);
                        if (isset($userData) && isset($userData['contact'])) {
                            $contactCount = count($userData['contact']);
                            $applicant = ConsumerApplication::where('user_id', $user_id)->get(['applicant_id']);
                            foreach ($applicant as $applicantData) {
                                if (!empty($applicantData->applicant_id)) {
                                    return $contactCount;
                                }
                            }
                        }
                    }
                }
            }

            return 0;
        }catch (\Exception $exception){
            return  0;
        }
    }

}