<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use Carbon\Carbon;
use App\ConsumerApplication;
use App\Logging\CloudWatchLoggerFactory;
use App\Http\Controllers\Admin\ConsumerApplicationController;
use App\Http\Controllers\ApiController;
use App\Http\Controllers\PostApprovalController;
use App\Http\Controllers\WebhookController;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Input;
use App\AutoDCDLog;
use App\LoanHypothecationModel;
use App\Jobs\AutoDCD;
use App\ConsumerApplicationElement;

class AutoDCDController extends Controller
{
    /**
     * Stores the failure reason from the last validate method that returned false.
     * @var string|null
     */
    private $lastFailureReason = null;

    /**
     * Stores the current job_providers ID being processed.
     * @var int|null
     */
    private $currentJobId = null;

    /**
     * Entry method to process the autoDCD job.
     * Evaluates validations and moves the application to Disbursement Checklist Done if all checks pass.
     *
     * @param object $job The database job record
     * @param object $payload Decoded payload containing application details
     * @return void
     */
    public function process($job, $payload)
    {
        $cwFactory = new CloudWatchLoggerFactory();
        try {
            $applicationId = $payload->application_id;
            $this->currentJobId = $job->id ?? null;

            $isValid = $this->checkValidations($applicationId, $payload);

            if ($isValid) {
                request()->merge(['state' => 'disbursement checklist done', 'source' => 'autoDCD']);
                (new ConsumerApplicationController())->updateStatus($applicationId);
            } else {
                $cwFactory->logger(json_encode([
                    'application_id' => $applicationId,
                    'message' => 'AutoDCD Validations Failed',
                    'values' => [
                        'reason' => 'Failed business conditions for AutoDCD.'
                    ]
                ]));
            }

        } catch (\Exception $e) {
            $cwFactory->logger(json_encode([
                'application_id' => $payload->application_id ?? null,
                'message' => 'AutoDCD Job exception',
                'line' => __LINE__,
                'values' => [
                    'job_id' => $job->id,
                    'error_message' => $e->getMessage()
                ]
            ]));
        }
    }

    /**
     * Orchestrates all validation checks. Short-circuits on the first failure.
     *
     * @param $applicationId
     * @param object $payload
     * @return bool
     */
    private function checkValidations($applicationId, $payload): bool
    {
        $application = ConsumerApplication::find($applicationId);
        $hasCoApplicant = isset($payload->has_co_applicant) ? $payload->has_co_applicant : (!empty($application->co_applicant_name));

        $validationResults = [];
        $allPassed = true;

        // Define all validation steps with optional dependency
        $validations = [
            'data_points'            => ['check' => function() use ($applicationId, $application) { return $this->validateDataPoints($applicationId, $application); }],
            'ebill_date'             => ['check' => function() use ($applicationId, $application) { return $this->validateEbillDate($applicationId, $application); }],
            'ebill_system_fail_doc'  => ['check' => function() use ($applicationId) { return $this->validateEbillSystemFailDoc($applicationId); }],
            'bank_validation'        => ['check' => function() use ($applicationId) { return $this->validateBankValidation($applicationId); }],
            'mandate_or_nach'        => ['check' => function() use ($applicationId) { return $this->validateMandateOrNach($applicationId); }, 'depends_on' => 'bank_validation'],
            'kyc_or_osv'             => ['check' => function() use ($applicationId, $hasCoApplicant) { return $this->validateKycOrOsv($applicationId, $hasCoApplicant); }],
            'advance_dp_receipt'     => ['check' => function() use ($applicationId) { return $this->validateAdvanceDpReceipt($applicationId); }],
            'pf_entry'               => ['check' => function() use ($applicationId) { return $this->validatePfEntry($applicationId); }],
            'required_documents'     => ['check' => function() use ($applicationId) { return $this->validateRequiredDocuments($applicationId); }],
            'reference_call'         => ['check' => function() use ($applicationId) { return $this->validateReferenceCall($applicationId); }],
            'mandatory_docs'         => ['check' => function() use ($applicationId) { return $this->validateMandatoryDocs($applicationId); }],
        ];

        foreach ($validations as $name => $config) {
            $this->lastFailureReason = null;

            // Skip if a dependency failed
            if (isset($config['depends_on'])) {
                $dep = $config['depends_on'];
                if (isset($validationResults[$dep]) && $validationResults[$dep]['status'] !== 'pass') {
                    $validationResults[$name] = [
                        'status'  => 'skip',
                        'message' => 'Skipped (' . str_replace('_', ' ', $dep) . ' failed)',
                    ];
                    continue;
                }
            }

            $passed = $config['check']();
            $validationResults[$name] = [
                'status'  => $passed ? 'pass' : 'fail',
                'message' => $passed
                    ? ucfirst(str_replace('_', ' ', $name)) . ' validated successfully'
                    : ($this->lastFailureReason ?? 'Validation failed'),
            ];

            if (!$passed) {
                $allPassed = false;
            }
        }

        // Persist the validation log
        AutoDCDLog::create([
            'application_id' => $applicationId,
            'result'         => $validationResults,
            'status'         => $allPassed ? 'success' : 'fail',
            'job_id'         => $this->currentJobId,
            'triggered_by'   => $payload->triggered_by ?? 'agreement_signing',
        ]);

        // Post a specific failure comment tagging the advisor
        if (!$allPassed) {
            $advisorTag = (new WebhookController())->getCommentTag(
                isset($application->created_by) ? [$application->created_by] : null
            );

            $failedReasons = [];
            foreach ($validationResults as $name => $result) {
                if ($result['status'] === 'fail') {
                    $failedReasons[] = $result['message'];
                }
            }
            $failedReasonsStr = implode(' | ', $failedReasons);

            $comment = trim($advisorTag . " Auto DCD failed due to: {$failedReasonsStr}. Please check reason on Checklist page.");
            (new ApiController())->updateComments(Input::merge([
                'application_id' => $applicationId,
                'id'             => 107,
                'comment'        => $comment,
                'show_merchant'  => 0,
                'show_consumer'  => 1,
                'confidential_notes' => 0,
            ]));
        } else {
            $advisorTag = (new WebhookController())->getCommentTag(
                isset($application->created_by) ? [$application->created_by] : null
            );
            $comment = trim($advisorTag . ' AutoDCD passed! Case moved to DCD by system.');
            (new ApiController())->updateComments(Input::merge([
                'application_id' => $applicationId,
                'id'             => 107,
                'comment'        => $comment,
                'show_merchant'  => 0,
                'show_consumer'  => 1,
                'confidential_notes' => 0,
            ]));
        }

        return $allPassed;
    }

    /*
     * Validates that the electricity bill date (from utility_validation) is within 2 months.
     * Skipped if no Karza bill record exists for the application.
     * On failure, adds an advisor-tagged comment and logs to CloudWatch.
     */
    private function validateEbillDate($applicationId, $application): bool
    {
        $cwFactory = new CloudWatchLoggerFactory();

        $utilityRecord = DB::selectOne(
            "SELECT data FROM utility_validation WHERE consumer_application_id = ? ORDER BY created_at DESC LIMIT 1",
            [$applicationId]
        );

        if (empty($utilityRecord) || empty($utilityRecord->data)) {
            return true; // No Karza bill fetched — skip date check
        }

        $billData = json_decode($utilityRecord->data, true);
        $billDate = $billData['result']['bill_date'] ?? null;

        if (empty($billDate)) {
            return true; // bill_date absent in data — skip
        }

        try {
            $parsedBillDate = Carbon::createFromFormat('d-m-Y', $billDate);
        } catch (\Exception $e) {
            $cwFactory->logger(json_encode([
                'application_id' => $applicationId,
                'message' => 'AutoDCD validateEbillDate: could not parse bill_date',
                'values' => ['bill_date' => $billDate, 'error' => $e->getMessage()]
            ]));
            return true; // Unparseable date — skip rather than block
        }

        if ($parsedBillDate->lt(Carbon::now()->subMonths(2))) {
            $advisorTag = (new WebhookController())->getCommentTag(
                isset($application->created_by) ? [$application->created_by] : null
            );
            $comment = trim($advisorTag . ' Auto DCD failed, Do the updated E-bill validation or upload latest validation document.');
            (new ApiController())->updateComments(Input::merge([
                'application_id' => $applicationId,
                'id'             => 107,
                'comment'        => $comment,
                'show_merchant'  => 0,
                'show_consumer'  => 1,
                'confidential_notes' => 0,
            ]));

            $cwFactory->logger(json_encode([
                'application_id' => $applicationId,
                'message' => 'AutoDCD Validation Failed',
                'values' => [
                    'reason' => 'E-bill date is older than 2 months',
                    'bill_date' => $billDate
                ]
            ]));
            $this->lastFailureReason = 'E-bill date is older than 2 months';
            return false;
        }

        return true;
    }

    /**
     * When Karza ebill validation failed (ebill_name_verify IS NULL on the latest relevant file),
     * validates that at least one fallback document has been uploaded.
     * Accepted file types (contains match): electricity_bill_validation_report,
     * utilitybillvalidation, Ebill Payment proof.
     */
    private function validateEbillSystemFailDoc($applicationId): bool
    {
        $cwFactory = new CloudWatchLoggerFactory();

        $ebillFile = DB::selectOne(
            "SELECT id, ebill_name_verify
             FROM consumer_application_files
             WHERE consumer_application_id = ?
             AND file_type IN ('electricity_bill_validation_report', 'electricity_bill')
             ORDER BY created_at DESC LIMIT 1",
            [$applicationId]
        );

        if (empty($ebillFile)) {
            return true; // No file present — skip
        }

        if (!is_null($ebillFile->ebill_name_verify)) {
            // Karza ran — verify all match percentages are >= 60%
            $nameVerify = json_decode($ebillFile->ebill_name_verify, true);
            $allAboveThreshold = true;
            if (is_array($nameVerify)) {
                foreach ($nameVerify as $percentage) {
                    if ($percentage < 60) {
                        $allAboveThreshold = false;
                        break;
                    }
                }
            }
            if ($allAboveThreshold) {
                return true; // Karza validation succeeded with sufficient match
            }
            // Fall through: Karza ran but name match below 60% — treat as failure
        }

        // Karza validation failed — require at least one fallback document
        $fallbackDoc = DB::selectOne(
            "SELECT id FROM consumer_application_files
             WHERE consumer_application_id = ?
             AND (
                 LOWER(file_type) LIKE '%electricity_bill_validation_report%'
                 OR LOWER(file_type) LIKE '%utilitybillvalidation%'
                 OR LOWER(file_type) LIKE '%ebill payment proof%'
                 OR LOWER(file_type) LIKE '%manual e-bill validation%'
             )
             ORDER BY created_at DESC LIMIT 1",
            [$applicationId]
        );

        if (empty($fallbackDoc)) {
            $cwFactory->logger(json_encode([
                'application_id' => $applicationId,
                'message' => 'AutoDCD Validation Failed',
                'values' => [
                    'reason' => 'E-bill Karza validation failed and no fallback document uploaded (electricity_bill_validation_report / utilitybillvalidation / Ebill Payment proof / Manual E-bill Validation)'
                ]
            ]));
            $this->lastFailureReason = 'E-bill Karza validation failed and no fallback document uploaded';
            return false;
        }

        return true;
    }

    /**
     * Validates that the penny drop (bank validation) was successful via Karza.
     * bankTxnStatus must be true and bankResponse must indicate a successful transaction.
     * Compulsory — if this fails, the mandate check is skipped entirely.
     */
    private function validateBankValidation($applicationId): bool
    {
        $cwFactory = new CloudWatchLoggerFactory();

        $bankRecord = DB::selectOne(
            "SELECT karza_response FROM bank WHERE application_id = ? AND applicant_type = 1 ORDER BY created_at DESC LIMIT 1",
            [$applicationId]
        );

        $pennydropSuccess = false;

        if (!empty($bankRecord) && !empty($bankRecord->karza_response) && $bankRecord->karza_response !== 'false') {
            $karza = json_decode($bankRecord->karza_response);
            if (isset($karza->result->bankTxnStatus) && $karza->result->bankTxnStatus === true &&
                isset($karza->result->bankResponse) &&
                in_array($karza->result->bankResponse, ['Transaction Successful', 'SUCCESSFUL TRANSACTION'])
            ) {
                $pennydropSuccess = true;
            }
        }

        if (!$pennydropSuccess) {
            $cwFactory->logger(json_encode([
                'application_id' => $applicationId,
                'message' => 'AutoDCD Validation Failed',
                'values' => [
                    'reason' => 'Bank validation (penny drop) failed or not done',
                    'karza_response' => $bankRecord->karza_response ?? null
                ]
            ]));
            $this->lastFailureReason = 'Bank validation (penny drop) failed or not done';
            return false;
        }

        return true;
    }

    /**
     * Validates that either an E-Mandate document is uploaded (file_type contains
     * 'e mandate' or 'emandate') OR a P-NACH form (file_type contains 'nach') AND
     * a BSV document (file_type contains 'bsv') are both uploaded.
     * Logs a specific reason to CloudWatch depending on which path was attempted.
     */
    private function validateMandateOrNach($applicationId): bool
    {
        $cwFactory = new CloudWatchLoggerFactory();

        // Check E-Mandate: file with 'e mandate' or 'emandate' in file_type
        $eMandateFile = DB::selectOne(
            "SELECT id FROM consumer_application_files
             WHERE consumer_application_id = ?
             AND deleted_at IS NULL
             AND (LOWER(file_type) LIKE '%e mandate%' OR LOWER(file_type) LIKE '%emandate%')
             ORDER BY created_at DESC LIMIT 1",
            [$applicationId]
        );

        if (!empty($eMandateFile)) {
            return true; // E-Mandate document present
        }

        // Check P-NACH: a 'nach' file must exist
        $nachFile = DB::selectOne(
            "SELECT id FROM consumer_application_files
             WHERE consumer_application_id = ?
             AND deleted_at IS NULL
             AND LOWER(file_type) LIKE '%nach%'
             ORDER BY created_at DESC LIMIT 1",
            [$applicationId]
        );

        if (!empty($nachFile)) {
            // P-NACH uploaded — also require BSV document
            $bsvFile = DB::selectOne(
                "SELECT id FROM consumer_application_files
                 WHERE consumer_application_id = ?
                 AND deleted_at IS NULL
                 AND LOWER(file_type) LIKE '%bsv%'
                 ORDER BY created_at DESC LIMIT 1",
                [$applicationId]
            );

            if (!empty($bsvFile)) {
                return true; // P-NACH + BSV both present
            }

            $cwFactory->logger(json_encode([
                'application_id' => $applicationId,
                'message' => 'AutoDCD Validation Failed',
                'values' => [
                    'reason' => 'P-NACH uploaded but BSV document not uploaded'
                ]
            ]));
            $this->lastFailureReason = 'P-NACH uploaded but BSV document not uploaded';
            return false;
        }

        $cwFactory->logger(json_encode([
            'application_id' => $applicationId,
            'message' => 'AutoDCD Validation Failed',
            'values' => [
                'reason' => 'Neither E-Mandate document nor P-NACH uploaded'
            ]
        ]));
        $this->lastFailureReason = 'Neither E-Mandate document nor P-NACH uploaded';
        return false;
    }

    /**
     * Validates that each applicant (and co-applicant if present) has either:
     * - Two-way VKYC approved (Karza or Digio), OR
     * - All OSV documents (PAN + Aadhaar front + Aadhaar back) uploaded and approved (doc_verify = 1).
     */
    private function validateKycOrOsv($applicationId, $hasCoApplicant): bool
    {
        $cwFactory = new CloudWatchLoggerFactory();

        $kycResponse = json_decode(
            (new PostApprovalController())->checkKYCStatus($applicationId)->getContent(),
            true
        );

        if (($kycResponse['applicant']['kyc_status'] ?? null) !== 'approved') {
            $cwFactory->logger(json_encode([
                'application_id' => $applicationId,
                'message' => 'AutoDCD Validation Failed',
                'values' => [
                    'reason' => 'KYC is missing / Self Attested Documents Of OSV is missing for Applicant'
                ]
            ]));
            $this->lastFailureReason = 'KYC is missing / Self Attested Documents Of OSV is missing for Applicant';
            return false;
        }

        if ($hasCoApplicant && ($kycResponse['co_applicant']['kyc_status'] ?? null) !== 'approved') {
            $cwFactory->logger(json_encode([
                'application_id' => $applicationId,
                'message' => 'AutoDCD Validation Failed',
                'values' => [
                    'reason' => 'KYC is missing / Self Attested Documents Of OSV is missing for Co-Applicant'
                ]
            ]));
            $this->lastFailureReason = 'KYC is missing / Self Attested Documents Of OSV is missing for Co-Applicant';
            return false;
        }

        return true;
    }

    /**
     * Returns true if VKYC is approved for the given applicant_type via Karza or Digio.
     */
    private function isVkycApproved($applicationId, int $applicantType): bool
    {
        // Karza VKYC
        $karzaRecords = DB::select(
            "SELECT data FROM karza_video_webhooks WHERE application_id = ? AND applicant_type = ? ORDER BY created_at DESC",
            [$applicationId, $applicantType]
        );
        foreach ($karzaRecords as $record) {
            if (!empty($record->data)) {
                $data = json_decode($record->data, true);
                if (isset($data['approvedByAuditor']) && $data['approvedByAuditor'] === 'ACCEPTED') {
                    return true;
                }
            }
        }

        // Digio VKYC
        $digioApproved = DB::selectOne(
            "SELECT id FROM digio_vkyc
             WHERE application_id = ? AND applicant_type = ? AND deleted_at IS NULL
             AND kyc_journey = 'complete' AND kyc_status = 'approved'
             LIMIT 1",
            [$applicationId, $applicantType]
        );

        return !empty($digioApproved);
    }

    /**
     * Validates that an Advance/DP receipt is present when DP is applicable.
     * Skipped (Zero DP) when merchant_collected_advance_amount != 1 or upfront_from_customer is 0.
     * Passes if either an advance_amount_receipt file or an advance_amount payment transaction exists.
     */
    private function validateAdvanceDpReceipt($applicationId): bool
    {
        $cwFactory = new CloudWatchLoggerFactory();

        $invoice = DB::selectOne(
            "SELECT merchant_collected_advance_amount FROM pro_forma_invoice WHERE application_id = ? LIMIT 1",
            [$applicationId]
        );

        if (empty($invoice) || $invoice->merchant_collected_advance_amount != 1) {
            return true; // Zero DP — skip
        }

        $emi = DB::selectOne(
            "SELECT upfront_from_customer FROM emi_calculation WHERE application_id = ? LIMIT 1",
            [$applicationId]
        );

        if (empty($emi) || empty($emi->upfront_from_customer)) {
            return true; // Zero DP — skip
        }

        $receiptFile = DB::selectOne(
            "SELECT id FROM consumer_application_files
             WHERE consumer_application_id = ? AND file_type = 'advance_amount_receipt'
             AND deleted_at IS NULL LIMIT 1",
            [$applicationId]
        );

        if (!empty($receiptFile)) {
            return true;
        }

        $paymentTransaction = DB::selectOne(
            "SELECT id FROM payment_transactions
             WHERE application_id = ? AND payment_for = 'advance_amount' AND deleted_at IS NULL LIMIT 1",
            [$applicationId]
        );

        if (!empty($paymentTransaction)) {
            return true;
        }

        $cwFactory->logger(json_encode([
            'application_id' => $applicationId,
            'message' => 'AutoDCD Validation Failed',
            'values' => [
                'reason' => 'Advance Payment Confirmation / Down Payment document is missing'
            ]
        ]));
        $this->lastFailureReason = 'Advance Payment Confirmation / Down Payment document is missing';
        return false;
    }

    /**
     * Validates that a PF (processing fee) payment entry exists and covers the full amount
     * when CF collects PF (processing_fee_by_merchant != 1).
     * Skipped entirely when proFormaInvoice is absent or processing_fee_by_merchant == 1.
     */
    private function validatePfEntry($applicationId): bool
    {
        $cwFactory = new CloudWatchLoggerFactory();

        $invoice = DB::selectOne(
            "SELECT processing_fee_by_merchant FROM pro_forma_invoice WHERE application_id = ? LIMIT 1",
            [$applicationId]
        );

        if (empty($invoice)) {
            return true; // No PFI row — skip
        }

        if ($invoice->processing_fee_by_merchant == 1) {
            return true; // Merchant collects PF — skip
        }

        $emi = DB::selectOne(
            "SELECT processing_fee FROM emi_calculation WHERE application_id = ? LIMIT 1",
            [$applicationId]
        );

        $requiredFee = isset($emi->processing_fee) ? (float) $emi->processing_fee : 0;

        $totalPaid = DB::selectOne(
            "SELECT COALESCE(SUM(amount), 0) AS total FROM payment_transactions
             WHERE application_id = ? AND payment_for = 'processing_fee' AND deleted_at IS NULL",
            [$applicationId]
        );

        $paidAmount = isset($totalPaid->total) ? (float) $totalPaid->total : 0;

        if ($paidAmount < $requiredFee) {
            $cwFactory->logger(json_encode([
                'application_id' => $applicationId,
                'message' => 'AutoDCD Validation Failed',
                'values' => [
                    'reason' => 'PF entry is pending',
                    'required' => $requiredFee,
                    'paid' => $paidAmount
                ]
            ]));
            $this->lastFailureReason = 'PF entry is pending';
            return false;
        }

        return true;
    }

    /**
     * Pre-checks the existence of mandatory documents before triggering AutoDCD job.
     */
    public static function precheckDocuments($applicationId): bool
    {
        $uploadedFiles = DB::table('consumer_application_files')
            ->where('consumer_application_id', $applicationId)
            ->whereNull('deleted_at')
            ->pluck('file_type')
            ->map(function ($type) {
                return strtolower($type);
            })
            ->toArray();

        $fileRules = [
            'Solar Quotation / Proforma' => function($files) { return in_array('solar_quotation', $files) || in_array('proforma', $files); },
            'Technical Feasibility Report (TFR)' => function($files) { return in_array('tfr', $files); }
        ];

        $missingDocs = [];
        foreach ($fileRules as $docName => $rule) {
            if (!$rule($uploadedFiles)) {
                $missingDocs[] = $docName;
            }
        }

        if (!empty($missingDocs)) {
            $application = ConsumerApplication::find($applicationId);
            $advisorTag = (new WebhookController())->getCommentTag(
                isset($application->created_by) ? [$application->created_by] : null
            );
            $missingDocsString = implode(' and ', $missingDocs);
            $comment = trim($advisorTag . " Auto DCD Pending: {$missingDocsString} missing. Please upload to proceed.");
            
            (new ApiController())->updateComments(Input::merge([
                'application_id' => $applicationId,
                'id'             => 107,
                'comment'        => $comment,
                'show_merchant'  => 0,
                'show_consumer'  => 1,
                'confidential_notes' => 0,
            ]));
            
            return false;
        }

        return true;
    }

    /**
     * Helper to safely try triggering AutoDCD job from external document endpoints.
     */
    public static function tryTriggerAutoDCD($applicationId)
    {
        // 1. Check if it already ran
        $hasLog = AutoDCDLog::where('application_id', $applicationId)->exists();
        if ($hasLog) {
            return false;
        }

        $application = ConsumerApplication::find($applicationId);
        if (!$application) {
            return false;
        }

        // 3. Agreement signing check
        $loanHypothecationModel = LoanHypothecationModel::where('application_id', $applicationId)
            ->orderBy('id', 'desc')
            ->first();

        if (!$loanHypothecationModel) {
            return false;
        }

        $allSigned = false;
        if (($loanHypothecationModel->applicant_signing && empty($application->co_applicant_name)) ||
            (!empty($application->co_applicant_name) && $loanHypothecationModel->co_applicant_signing && $loanHypothecationModel->applicant_signing)) {
            $allSigned = true;
        }

        if (!$allSigned) {
            return false;
        }

        // 4. Precheck Documents
        if (self::precheckDocuments($applicationId)) {
            return AutoDCD::precheckAndCreateJob($applicationId);
        }

        return false;
    }

    /**
     * Validates element requirements.
     */
    private function validateRequiredDocuments($applicationId): bool{
        $failures = [];

        // 1. Fetch all active files
        $files = DB::table('consumer_application_files')
            ->where('consumer_application_id', $applicationId)
            ->whereNull('deleted_at')
            ->pluck('file_type')
            ->map(function ($type) {
                return strtolower(trim($type));
            })
            ->toArray();

        if (!in_array('sanction_letter', $files)) {
            $failures[] = 'Sanction letter missing';
        }

        $merchantConfExists = in_array('pro_forma_invoice', $files);
        if (!$merchantConfExists) {
            foreach ($files as $file) {
                if (strpos($file, 'merchant') !== false && strpos($file, 'confirmation') !== false) {
                    $merchantConfExists = true;
                    break;
                }
            }
        }
        if (!$merchantConfExists) {
            $failures[] = 'Merchant Email Confirmation / Signed DA missing';
        }

        // 2. Handle the single non-file validation (Product Name)
        $element = DB::table('consumer_application_element')
            ->where('application_id', $applicationId)
            ->select('product_name')
            ->first();

        if (empty($element) || empty($element->product_name)) {
            $failures[] = 'Product Name missing';
        }

        // 2. If any checks failed, log all of them together in one entry and return false.
        if (!empty($failures)) {
            $cwFactory = new CloudWatchLoggerFactory();
            $cwFactory->logger(json_encode([
                'application_id' => $applicationId,
                'message'        => 'AutoDCD Validation Failed',
                'values'         => [
                    'reason'       => 'Required elements missing',
                    'missing_docs' => $failures,
                ]
            ]));
            $this->lastFailureReason = implode(', ', $failures);
            return false;
        }

        return true;
    }

    /**
     * DRY Helper for logging AutoDCD Validation Failures
     */
    private function logAutoDcdFailure($applicationId, $reason)
    {
        $cwFactory = new CloudWatchLoggerFactory();

        $this->lastFailureReason = $reason;
        
        $cwFactory->logger(json_encode([
            'application_id' => $applicationId,
            'message' => 'AutoDCD Validation Failed',
            'values' => ['reason' => $reason]
        ]));
    }

    /**
     * Validates that reference contacts are verified.
     * - For non-SSE merchants, passes if the corresponding reference file exists, OR
     * - Completed call_log exists for each reference contact number present in consumer_application_element.
     * - For SSE merchants, reference files are ignored and completed call logs are required.
     */
    private function validateReferenceCall($applicationId): bool
    {
        $cwFactory = new CloudWatchLoggerFactory();

        $element = DB::selectOne(
            "SELECT cae.reference_contact_1, cae.reference_contact_2, ca.merchant_id
             FROM consumer_application_element cae
             LEFT JOIN consumer_application ca ON ca.id = cae.application_id
             WHERE cae.application_id = ? LIMIT 1",
            [$applicationId]
        );

        if (empty($element)) {
            $element = DB::selectOne(
                "SELECT ca.merchant_id
             FROM consumer_application ca
             WHERE ca.id = ? LIMIT 1",
                [$applicationId]
            );
        }

        $contact1 = $element->reference_contact_1 ?? null;
        $contact2 = $element->reference_contact_2 ?? null;
        $isSseMerchant = !empty(env('SSE_MERCHANT_ID'))
            && isset($element->merchant_id)
            && (string)$element->merchant_id === (string)env('SSE_MERCHANT_ID');

        if (empty($contact1) && empty($contact2)) {
            $cwFactory->logger(json_encode([
                'application_id' => $applicationId,
                'message' => 'AutoDCD Validation Failed',
                'values' => ['reason' => 'Reference number missing']
            ]));
            $this->lastFailureReason = 'Reference number missing';
            return false;
        }

        // For non-SSE merchants, at least one corresponding reference file must exist.
        if (!$isSseMerchant) {
            $hasRef1File = false;
            $hasRef2File = false;

            if (!empty($contact1)) {
                $ref1File = DB::selectOne(
                    "SELECT id FROM consumer_application_files
                     WHERE consumer_application_id = ? AND file_type = 'reference_1' AND deleted_at IS NULL LIMIT 1",
                    [$applicationId]
                );
                if (!empty($ref1File)) {
                    $hasRef1File = true;
                }
            }

            if (!empty($contact2)) {
                $ref2File = DB::selectOne(
                    "SELECT id FROM consumer_application_files
                     WHERE consumer_application_id = ? AND file_type = 'reference_2' AND deleted_at IS NULL LIMIT 1",
                    [$applicationId]
                );
                if (!empty($ref2File)) {
                    $hasRef2File = true;
                }
            }

            if (!$hasRef1File && !$hasRef2File) {
                $cwFactory->logger(json_encode([
                    'application_id' => $applicationId,
                    'message' => 'AutoDCD Validation Failed',
                    'values' => ['reason' => 'At least one reference document is missing']
                ]));
                $this->lastFailureReason = 'At least one reference document is missing';
                return false;
            }
        }

        return true;
    }

    /**
     * Validates that all docs listed in consumer_application_element.mandatory_document_list
     * are present in consumer_application_files.
     * Tries exact file_type match first, then underscore-to-space conversion.
     * Skipped entirely if mandatory_document_list is empty or not set.
     */
    private function validateMandatoryDocs($applicationId): bool
    {
        $cwFactory = new CloudWatchLoggerFactory();

        $element = DB::selectOne(
            "SELECT mandatory_document_list FROM consumer_application_element WHERE application_id = ? LIMIT 1",
            [$applicationId]
        );

        if (empty($element) || empty($element->mandatory_document_list)) {
            return true;
        }

        $requiredDocs = array_filter(array_map('trim', explode(',', $element->mandatory_document_list)));

        if (empty($requiredDocs)) {
            return true;
        }

        $uploadedFiles = DB::select(
            "SELECT file_type FROM consumer_application_files
             WHERE consumer_application_id = ? AND deleted_at IS NULL",
            [$applicationId]
        );
        $uploadedTypes = array_map(function ($f) {
            return $f->file_type;
        }, $uploadedFiles);

        $missingDocs = [];
        foreach ($requiredDocs as $doc) {
            $spaceVariant = str_replace('_', ' ', $doc);
            $found = in_array($doc, $uploadedTypes) || in_array($spaceVariant, $uploadedTypes);

            // Special-case: 'emandate' also accepts 'e mandate' as file_type
            if (!$found && strtolower($doc) === 'emandate') {
                $found = in_array('e mandate', $uploadedTypes) || in_array('emandate', $uploadedTypes);
            }

            if (!$found) {
                $missingDocs[] = $doc;
            }
        }

        if (!empty($missingDocs)) {
            $cwFactory->logger(json_encode([
                'application_id' => $applicationId,
                'message' => 'AutoDCD Validation Failed',
                'values' => [
                    'reason' => 'Mandatory is pending',
                    'missing_docs' => $missingDocs
                ]
            ]));
            $this->lastFailureReason = 'Mandatory is pending - missing: ' . implode(', ', $missingDocs);
            return false;
        }

        return true;
    }

    /**
     * Validates that all required data points are filled.
     *
     * @param $applicationId
     * @param $application
     * @return bool
     */
    private function validateDataPoints($applicationId, $application)
    {
        $element = ConsumerApplicationElement::where('application_id', $applicationId)->first();

        $missingFields = [];

        if (empty($application->marital_status)) $missingFields[] = 'Marital Status';
        if (empty($application->email)) $missingFields[] = 'Email Address';
        if (empty($application->gender)) $missingFields[] = 'Gender';
        if (empty($application->permanent_address_pincode)) $missingFields[] = 'Permanent Address';

        if (!$element) {
            $missingFields = array_merge($missingFields, [
                'Type of Customer', 'Father\'s Name', 'Religion', 'Solar Financing Sub-category', 
                'Inverter to be Installed', 'Panel to be Installed', 'Model Description', 
                'Service Provider', 'Subsidy Amount'
            ]);
        } else {
            if (empty($element->type_of_customer_application)) $missingFields[] = 'Type of Customer';
            if (empty($element->father_name)) $missingFields[] = 'Father\'s Name';
            if (empty($element->applicant_religion)) $missingFields[] = 'Religion';
            if (empty($element->type_of_customer_sub_category)) $missingFields[] = 'Solar Financing Sub-category';
            if (empty($element->inverter)) $missingFields[] = 'Inverter to be Installed';
            if (empty($element->panel)) $missingFields[] = 'Panel to be Installed';
            if (empty($element->inverter_model_description)) $missingFields[] = 'Model Description';
            if (empty($element->discom_service_provider)) $missingFields[] = 'Service Provider';
            if (is_null($element->inverter_subsidy_amount) || trim((string)$element->inverter_subsidy_amount) === '') $missingFields[] = 'Subsidy Amount';
        }

        if (!empty($missingFields)) {
            $this->lastFailureReason = 'Missing data points: ' . implode(', ', $missingFields);
            return false;
        }

        return true;
    }

        /**
     * API endpoint: Returns the failed validation entries from the latest AutoDCD log.
     * Called via AJAX from the checklist page.
     *
     * @param $applicationId
     * @return \Illuminate\Http\JsonResponse
     */
    public function getFailedValidations($applicationId)
    {
        $log = AutoDCDLog::where('application_id', $applicationId)
            ->orderBy('created_at', 'desc')
            ->first();

        if (!$log) {
            return response()->json(['show' => false]);
        }

        if ($log->status === 'success') {
            return response()->json([
                'show'   => true,
                'status' => 'success',
            ]);
        }

        if ($log->status !== 'fail') {
            return response()->json(['show' => false]);
        }

        $result = is_array($log->result) ? $log->result : json_decode($log->result, true);
        $failures = [];

        if (is_array($result)) {
            foreach ($result as $name => $entry) {
                if (isset($entry['status']) && $entry['status'] === 'fail') {
                    $failures[] = [
                        'name'    => ucwords(str_replace('_', ' ', $name)),
                        'message' => $entry['message'] ?? 'Validation failed',
                    ];
                }
            }
        }

        return response()->json([
            'show'     => true,
            'status'   => 'fail',
            'failures' => $failures,
        ]);
    }


}