<?php namespace App\Auditor;

use App\ApplicationEMICalculation;
use App\Auditor\Helper\LoanManagementAuditService;
use App\ConsumerApplication;
use App\EmiPayment;
use App\LoanManagement;
use App\PaymentTransaction;
use App\Slack;
use App\StatusLogs;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use function Composer\Autoload\includeFile;

/**
 * Created by PhpStorm.
 * User: sunil khorwal
 * Date: 6/6/16
 * Time: 1:52 PM
 */
class AuditService implements LoanManagementAuditService
{

    private $DISBURSED_AND_NACH_REGISTERED = ['disbursed', 'nach registered'];
    private $APPROVED = ['approved'];
    private $request;

    public function __construct(Request  $request){
        $this->request = $request;
    }

    public function doubleDueDate()
    {
        $payments = EmiPayment::whereIn('application_id',$this->getApplications($this->DISBURSED_AND_NACH_REGISTERED, $this->request->get('options')->date ?? null))->get(['id', 'application_id', 'payment_status', 'emi_date']);
        $data = [];
        $duplicateRows = [];
        $duplicateApplications = [];
        foreach($payments as $payment){
            if(!in_array($payment->emi_date, $data[$payment->application_id] ?? [])) {
                $data[$payment->application_id][] = $payment->emi_date;
            }else if($payment->payment_status == 'pending'){
                $duplicateRows[] = $payment->id;
                $duplicateApplications[] = $payment->application_id;
            }
        }

        if($duplicateRows){
            EmiPayment::whereIn('id', $duplicateRows)->delete();
            (new Slack(new Request(['title'=>'Double due date Mismatch','fields'=>['Application IDs'=> json_encode(array_unique($duplicateApplications))], 'group' => Slack::$STAGE_TESTING])))->send();
        }

        return $this->sendResponse('OK', $duplicateRows ? 'Success' : 'No records found', [
            'duplicate_rows' => $duplicateRows,
            'duplicate_applications' => array_unique($duplicateApplications)
        ]);
    }

    private function getApplications($status, $after = null){
        if (is_null($after))
            $after = $this->todayDate();

        $statusLogs = StatusLogs::where('created_at', '>=', $after)->whereIn('status', $status)->get(['application_id']);
        $response = [];
        foreach ($statusLogs as $log){
            $response[] = $log->application_id;
        }

        return $response;
    }

    private function todayDate(): string
    {
        return Carbon::now()->startOfMonth()->toDateTimeString();
    }

    private function sendResponse($status = 'OK', $mesage = 'Success', $output = array()): array
    {
        return [
          'status' => $status,
          'message' => $mesage,
          'output' => $output
        ];
    }

    public function emiDateAnomaliesInEMIPayments()
    {
        $applications = ApplicationEMICalculation::whereIn('application_id', $this->getApplications($this->DISBURSED_AND_NACH_REGISTERED, $this->request->get('options')->date ?? null))->get(['application_id', 'emi_date']);
        foreach($applications as $application){
            $applicationIds[] = $application->application_id;
            $applicationEMIDates[$application->application_id] = date('Y-m-d', strtotime($application->emi_date));
        }

        if(isset($applicationIds)) {
            $query = 'SELECT e.application_id, e.emi_date
                FROM emi_payments e
                         JOIN (
                    SELECT application_id, MIN(emi_date) AS min_emi_date
                    FROM emi_payments
                    WHERE application_id IN (' . implode(',', $applicationIds) . ')
                    GROUP BY id
                ) AS earliest ON e.application_id = earliest.application_id AND e.emi_date = earliest.min_emi_date';

            $data = DB::select($query);

            $processed = [];
            $errorProcessed = [];
            foreach ($data as $item){
                if (!in_array($item->application_id, $processed) && $item->emi_date != $applicationEMIDates[$item->application_id]){
                    $isPayment = EmiPayment::where('application_id', $item->application_id)->where('payment_status', '!=', 'pending')->first(['id']);
                    if(!isset($isPayment->id)){
                        EmiPayment::where('application_id', $item->application_id)->where('payment_status', 'pending')->delete();
                        (new LoanManagement())->paymentCalculate($item->application_id);
                    }
                    (new Slack(new Request(['title'=>'EMI Payment - EMI Date Mismatch','fields'=>['Application ID'=> $item->application_id, 'EMI Date'=> $applicationEMIDates[$item->application_id],'EMI Payment start date'=> $item->emi_date], 'group' => Slack::$STAGE_TESTING])))->send();
                    $errorProcessed[] = $item->application_id;
                }
                $processed[] = $item->application_id;
            }
        }

        return $this->sendResponse('OK', $errorProcessed ? 'Success' : 'No records processed', [
            'anomalies_processed' => $errorProcessed
        ]);
    }

    public function duplicateEMIInEMICalculation(){

        $applicationIds = $this->getApplications($this->DISBURSED_AND_NACH_REGISTERED, $this->request->get('options')->date ?? null);

//        $query = 'SELECT application_id, COUNT(*) as dup_count
//                FROM emi_calculation WHERE application_id IN (' . implode(',', $applicationIds) . ')
//                GROUP BY application_id
//                HAVING COUNT(*) > 1';

        $query = 'select application_id from emi_calculation where application_id  =
                (SELECT application_id as dup_count
                FROM emi_calculation WHERE application_id IN (' . implode(',', $applicationIds) . ')
                GROUP BY application_id
                HAVING COUNT(*) > 1)  order by id desc limit 1';

        $data = DB::select($query);


        $queryData = [];
        foreach ($data as $item) {
            $queryData[] = 'SELECT x.id, x.application_id
                        FROM emi_calculation x
                                 JOIN (
                            SELECT application_id, MIN(id) AS min_id
                            FROM emi_calculation
                            WHERE application_id = '.$item->application_id.'
                            GROUP BY application_id
                            HAVING COUNT(*) > 1
                        ) AS min_ids ON x.application_id = min_ids.application_id AND x.id != min_ids.min_id
                        WHERE x.application_id = '.$item->application_id.';';
            }

        $processed = [];
        $idsToBeProcessed = [];
        foreach ($queryData as $item){
            $data = DB::select($item);
            foreach($data as $_item) {
                $processed[] = $_item->application_id;
                $idsToBeProcessed[] = $_item->id;
            }
        }

        if($idsToBeProcessed && $processed) {
            (new Slack(new Request(['title' => 'EMI Payment - EMI Date Mismatch', 'fields' => ['Application ID' => implode(',', $processed)], 'group' => Slack::$STAGE_TESTING])))->send();
            ApplicationEMICalculation::whereIn('id', $idsToBeProcessed)->delete();
        }
        return $this->sendResponse('OK', $processed ? 'Success' : 'No records processed', [
            'anomalies_processed' => $processed
        ]);
    }

    public function doubleProcessingFeeEntry()
    {
        $applicationIds = $this->getApplications($this->APPROVED, $this->request->get('options')->date ?? null);

        $query = 'select application_id from payment_transactions  WHERE application_id IN (' . implode(',', $applicationIds) . ') and payment_for = "processing_fee" and deleted_at is null GROUP BY application_id HAVING COUNT(*) > 1';

        $data = DB::select($query);

        $queryData = [];
        foreach ($data as $item) {

            $queryData[] = 'SELECT id
                FROM payment_transactions
                WHERE payment_for = "processing_fee"
                  AND deleted_at IS NULL
                  AND (application_id, created_at) IN (
                    SELECT application_id, min(created_at)
                    FROM payment_transactions
                    WHERE payment_for = "processing_fee"
                      AND deleted_at IS NULL
                      and application_id = ' . $item->application_id . '
                ) and application_id = ' . $item->application_id . ';';
        }


        $processed = [];
        $idsToBeProcessed = [];
        foreach ($queryData as $item) {
            $data = DB::select($item);
            foreach ($data as $_item) {
                $processed[] = $_item->application_id;
                $idsToBeProcessed[] = $_item->id;
            }
        }

        if ($idsToBeProcessed && $processed) {
            (new Slack(new Request(['title' => 'Double Processing Fee', 'fields' => ['Application ID' => implode(',', $processed)], 'group' => Slack::$STAGE_TESTING])))->send();
            PaymentTransaction::whereIn('id', $idsToBeProcessed)->delete();
        }
        return $this->sendResponse('OK', $processed ? 'Success' : 'No records processed', [
            'anomalies_processed' => $processed
        ]);
    }
}