<?php

namespace App\Http\Controllers\Blacksoil;

use App\CampaignsJobProvider;
use App\Http\Controllers\Controller;
use App\Imports\BlacksoilBreDecisionImport;
use App\Logging\BlacksoilBreLogger;
use App\Slack;
use Carbon\Carbon;
use GuzzleHttp\Client;
use GuzzleHttp\Exception\RequestException;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Validator;
use Maatwebsite\Excel\Facades\Excel;
use Ramsey\Uuid\Uuid;
use Session;
use Storage;

class BlacksoilController extends Controller
{
    public const VENDOR = 'blacksoil';

    private $request;
    private $DECISION_API_URL;
    private $POLICY_API_URL;
    private $POLICY_API_KEY;
    private $POLICY_API_SECRET;

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

    private function setup(){
        $this->DECISION_API_URL = _env('DECISION_API_URL');
        $this->POLICY_API_URL = _env('POLICY_API_URL');
        $this->POLICY_API_KEY = _env('POLICY_API_KEY');
        $this->POLICY_API_SECRET = _env('POLICY_API_SECRET');
    }

    public function processDecision(){

        $jobs = CampaignsJobProvider::whereIn('from', ['co_lending_loan_decision'])
            ->orderBy('id', 'desc')
            ->limit(20)
            ->get(['id', 'payload', 'is_available', 'created_at']);

        return view('admin.co-lending.process-decision',  compact('jobs'));
    }

    public function importLoanDecision()
    {
        try {
            if ($this->request->has('file') && $this->request->files->get('file') != null) {
                if (filesize($this->request->files->get('file')) > 18874368) {
                    Session::flash('error', 'Uploaded file is too large');
                    return redirect()->back();
                }
            }

            $date = Carbon::now()->toDateTimeString();
            $name = str_replace(' ', '_', $date) . '_' . time() . '_co_lending_loan_decision.' . $this->request->file('file')->getClientOriginalExtension();

            $file_path = $this->saveFileToS3($this->request->file('file'), $name, 'co_lending_loan_decision_upload');

            $data = $this->request->all();
            $data['file_path'] = $file_path;
            $data['file_name'] = $name;

            $jobs = new CampaignsJobProvider();
            $jobs->from = 'co_lending_loan_decision';
            $jobs->payload = json_encode($data);
            $jobs->available_at = $date;
            $jobs->application_id = 0;
            $jobs->created_at = $date;
            $jobs->is_available = 0;
            $jobs->save();

            Session::flash('success', 'Loan Decision file uploaded successfully. It will be processed shortly.');
            return redirect()->back();
        } catch (\Exception $e) {
            Session::flash('error', 'Something went wrong: ' . $e->getMessage() . ' (line ' . $e->getLine() . ')');
            return redirect()->back();
        }
    }

    public function startLoanDecisionFileProcess()
    {
        $s3Path   = $this->request->get('file_path');
        $fileName = $this->request->get('file_name');

        try {
            if (!$s3Path || !Storage::disk('s3')->exists($s3Path)) {
                throw new \Exception('S3 rps file not found: ' . $s3Path);
            }

            // Download file from S3 to a temporary local path
            $fileContents = Storage::disk('s3')->get($s3Path);
            $localPath = storage_path('app/temp_' . $fileName);
            file_put_contents($localPath, $fileContents);

            // Call evaluate, passing the local file path
            $response = $this->evaluate($localPath);

            // Clean up temporary file
            @unlink($localPath);

            if ($response->getStatusCode() !== 200) {
                throw new \Exception('BRE evaluation failed: ' . $response->getContent());
            }

            return $response;

        } catch (\Exception $e) {
            (new Slack(new Request([
                'title'  => 'Co Lending rps file could not be processed',
                'fields' => [
                    'message' => $e->getMessage(),
                    'line'    => $e->getLine(),
                    'file'    => $e->getFile(),
                ],
                'group'  => Slack::$STAGE_TESTING,
            ])))->send();

            return response()->json([
                'success' => false,
                'message' => $e->getMessage(),
            ], 500);
        }
    }

    private function saveFileToS3($file, $fileName, $folder)
    {
        $s3 = Storage::disk('s3');
        $file_path = '/' . $folder . '/' . $fileName;
        $s3->put($file_path, $file);
        $files = $s3->listContents($file_path);
        return $files[0]['path'];
    }

    public function evaluate($localFilePath = null)
    {
        $batchId = Uuid::uuid4()->toString();
        $uploaded = $this->request ? $this->request->file('file') : null;

        BlacksoilBreLogger::event('evaluate.received', [
            'batch_id'  => $batchId,
            'has_file'  => $uploaded !== null || $localFilePath !== null,
            'filename'  => $uploaded ? $uploaded->getClientOriginalName() : ($localFilePath ? basename($localFilePath) : null),
            'size'      => $uploaded ? $uploaded->getSize() : ($localFilePath ? filesize($localFilePath) : null),
            'mime'      => $uploaded ? $uploaded->getMimeType() : null,
            'ip'        => $this->request ? $this->request->ip() : 'cli',
        ]);

        if (!$localFilePath) {
            $validator = Validator::make($this->request->all(), [
                'file' => 'required|file|mimes:xls,xlsx,csv|max:20480',
            ]);

            if ($validator->fails()) {
                BlacksoilBreLogger::warning('evaluate.file_validation_failed', [
                    'batch_id' => $batchId,
                    'errors'   => $validator->errors()->toArray(),
                ]);
                return response()->json([
                    'status'  => 'error',
                    'message' => 'Invalid file upload',
                    'errors'  => $validator->errors()->toArray(),
                ], 422);
            }
        }

        try {
            $import = new BlacksoilBreDecisionImport($batchId);
            Excel::import($import, $localFilePath ? $localFilePath : $uploaded);
            $rows = $import->rows;
        } catch (\Exception $e) {
            BlacksoilBreLogger::error('evaluate.excel_parse_failed', [
                'batch_id' => $batchId,
                'error'    => $e->getMessage(),
            ]);
            return response()->json([
                'status'  => 'error',
                'message' => 'Failed to read excel file',
                'error'   => $e->getMessage(),
            ], 422);
        }

        BlacksoilBreLogger::event('evaluate.excel_parsed', [
            'batch_id'  => $batchId,
            'row_count' => $rows->count(),
        ]);

        if ($rows->isEmpty()) {
            BlacksoilBreLogger::warning('evaluate.empty_file', ['batch_id' => $batchId]);
            return response()->json([
                'status'  => 'error',
                'message' => 'No rows found in uploaded file',
            ], 422);
        }

        // Fetch the rejection policy once for this batch — it's flat-merged with every row's
        // decision payload before being sent to the BRE API. Abort the whole upload if it fails,
        // because a stale/missing policy would silently change every BRE decision.
        $policyStartedAt = microtime(true);
        BlacksoilBreLogger::event('policy.request', [
            'batch_id' => $batchId,
            'url'      => $this->POLICY_API_URL,
        ]);

        list($policyOk, $policyHttpStatus, $policyResponse, $policyError) = $this->fetchRejectionPolicy();

        BlacksoilBreLogger::event('policy.response', [
            'batch_id'    => $batchId,
            'ok'          => $policyOk,
            'http_status' => $policyHttpStatus,
            'duration_ms' => (int) ((microtime(true) - $policyStartedAt) * 1000),
            'response'    => $policyResponse,
            'error'       => $policyError,
        ]);

        if (!$policyOk) {
            BlacksoilBreLogger::error('evaluate.aborted_policy_unavailable', [
                'batch_id'    => $batchId,
                'http_status' => $policyHttpStatus,
                'error'       => $policyError,
            ]);
            return response()->json([
                'status'      => 'error',
                'message'     => 'Failed to fetch rejection policy — BRE evaluation aborted',
                'policy_url'  => $this->POLICY_API_URL,
                'http_status' => $policyHttpStatus,
                'error'       => $policyError,
            ], 502);
        }

        // The contract is: response.grade.rules.{policyName} → object with a `rule` array.
        // The row payload is injected as `payload` next to `rule` for every policy. If the
        // shape isn't there, downstream injection would silently produce wrong BRE calls.
        if (!is_array($policyResponse)
            || !isset($policyResponse['grade']['rules'])
            || !is_array($policyResponse['grade']['rules'])
            || empty($policyResponse['grade']['rules'])
        ) {
            BlacksoilBreLogger::error('evaluate.aborted_policy_malformed', [
                'batch_id'      => $batchId,
                'expected_path' => 'grade.rules.{policyName}',
                'response_keys' => is_array($policyResponse) ? array_keys($policyResponse) : null,
                'response'      => $policyResponse,
            ]);
            return response()->json([
                'status'  => 'error',
                'message' => 'Policy API response is missing expected structure (grade.rules) — BRE evaluation aborted',
            ], 502);
        }

        $policyNames = array_keys($policyResponse['grade']['rules']);
        BlacksoilBreLogger::event('policy.parsed', [
            'batch_id'     => $batchId,
            'policy_count' => count($policyNames),
            'policy_names' => $policyNames,
        ]);

        $summary   = [
            'total'              => 0,
            'succeeded'          => 0,
            'failed'             => 0,
            'validation_failed'  => 0,
            'skipped_duplicate'  => 0,
        ];
        $results   = [];
        $rowNumber = 0;

        foreach ($rows as $row) {
            $rowNumber++;
            $summary['total']++;
            $applicationId = $row['application_id'] ?? null;

            BlacksoilBreLogger::event('row.start', [
                'batch_id'       => $batchId,
                'row'            => $rowNumber,
                'application_id' => $applicationId,
            ]);

            $rowValidator = Validator::make($row, $this->rowRules());

            if ($rowValidator->fails()) {
                $summary['validation_failed']++;
                $summary['failed']++;
                BlacksoilBreLogger::warning('row.validation_failed', [
                    'batch_id'       => $batchId,
                    'row'            => $rowNumber,
                    'application_id' => $applicationId,
                    'errors'         => $rowValidator->errors()->toArray(),
                    'pan_number'     => $row['pan_number'] ?? null,
                    'pan_bytes'      => isset($row['pan_number']) ? bin2hex((string) $row['pan_number']) : null,
                    'mobile_number'  => $row['mobile_number'] ?? null,
                ]);
                $results[] = [
                    'row'            => $rowNumber,
                    'application_id' => $applicationId,
                    'status'         => 'validation_failed',
                    'message'        => 'Row failed validation',
                    'errors'         => $rowValidator->errors()->toArray(),
                ];
                continue;
            }

            list($demographic, $decision) = $this->categorize($row);
            $decision = $this->castDecisionTypes($decision);

            // ── Idempotency: look up an existing lead before inserting anything ────────
            $existingLead = $this->findExistingLead($applicationId);

            if ($existingLead) {
                $vendorLoanId = $existingLead->id;

                $existingBre = $this->findCompletedBre($vendorLoanId);
                if ($existingBre) {
                    // Case 1: lead exists AND BRE already executed → skip everything
                    $summary['skipped_duplicate']++;
                    BlacksoilBreLogger::event('row.bre_already_executed', [
                        'batch_id'        => $batchId,
                        'row'             => $rowNumber,
                        'application_id'  => $applicationId,
                        'vendor_loan_id'  => $vendorLoanId,
                        'bre_decision_id' => $existingBre->id,
                        'decision'        => $existingBre->decision,
                    ]);
                    $results[] = [
                        'row'             => $rowNumber,
                        'application_id'  => $applicationId,
                        'vendor_loan_id'  => $vendorLoanId,
                        'bre_decision_id' => $existingBre->id,
                        'status'          => 'bre_already_executed',
                        'message'         => 'Lead and BRE decision already exist for this application — skipped to avoid duplicate processing.',
                        'decision'        => $existingBre->decision,
                        'previous_status' => $existingBre->status,
                    ];
                    continue;
                }

                // Case 2: lead exists, BRE missing/failed → reuse loan id, refresh decision snapshot, run BRE
                try {
                    $this->saveDecisionData($vendorLoanId, $applicationId, $batchId, $decision);
                } catch (\Exception $e) {
                    BlacksoilBreLogger::error('row.db_failed', [
                        'batch_id'       => $batchId,
                        'row'            => $rowNumber,
                        'application_id' => $applicationId,
                        'vendor_loan_id' => $vendorLoanId,
                        'stage'          => 'refresh_decision_data',
                        'error'          => $e->getMessage(),
                    ]);
                    $summary['failed']++;
                    $results[] = [
                        'row'            => $rowNumber,
                        'application_id' => $applicationId,
                        'vendor_loan_id' => $vendorLoanId,
                        'status'         => 'db_failed',
                        'error'          => $e->getMessage(),
                    ];
                    continue;
                }

                BlacksoilBreLogger::event('row.existing_lead_running_bre', [
                    'batch_id'       => $batchId,
                    'row'            => $rowNumber,
                    'application_id' => $applicationId,
                    'vendor_loan_id' => $vendorLoanId,
                ]);
                $rowStatusOnSuccess = 'existing_lead_bre_executed';
            } else {
                // Case 3: brand-new lead — create everything
                try {
                    $vendorLoanId = $this->createLead($demographic, $row, $batchId, $rowNumber);
                    BlacksoilBreLogger::event('row.lead_created', [
                        'batch_id'       => $batchId,
                        'row'            => $rowNumber,
                        'application_id' => $applicationId,
                        'vendor_loan_id' => $vendorLoanId,
                    ]);

                    $coCount = $this->saveCoApplicants($vendorLoanId, $applicationId, $row);
                    BlacksoilBreLogger::event('row.co_applicants_saved', [
                        'batch_id'       => $batchId,
                        'row'            => $rowNumber,
                        'vendor_loan_id' => $vendorLoanId,
                        'count'          => $coCount,
                    ]);

                    $this->saveDecisionData($vendorLoanId, $applicationId, $batchId, $decision);
                    BlacksoilBreLogger::event('row.decision_data_saved', [
                        'batch_id'       => $batchId,
                        'row'            => $rowNumber,
                        'vendor_loan_id' => $vendorLoanId,
                        'decision_keys'  => count($decision),
                    ]);
                } catch (\Exception $e) {
                    BlacksoilBreLogger::error('row.db_failed', [
                        'batch_id'       => $batchId,
                        'row'            => $rowNumber,
                        'application_id' => $applicationId,
                        'stage'          => 'create_lead',
                        'error'          => $e->getMessage(),
                    ]);
                    $summary['failed']++;
                    $results[] = [
                        'row'            => $rowNumber,
                        'application_id' => $applicationId,
                        'status'         => 'db_failed',
                        'error'          => $e->getMessage(),
                    ];
                    continue;
                }
                $rowStatusOnSuccess = 'created';
            }

            // Inject the row's decision data as `payload` next to `rule` for every policy under
            // grade.rules. PHP arrays are deep value-copied on assignment, so the local copy
            // doesn't mutate the cached policyResponse across rows.
            $brePayload = $policyResponse;
            foreach ($brePayload['grade']['rules'] as &$policy) {
                if (is_array($policy)) {
                    $policy['payload'] = $decision;
                }
            }
            unset($policy);

            $startedAt = microtime(true);
            BlacksoilBreLogger::event('row.api_request', [
                'batch_id'         => $batchId,
                'row'              => $rowNumber,
                'vendor_loan_id'   => $vendorLoanId,
                'url'              => $this->DECISION_API_URL,
                'policies_payload' => $policyNames,
                'decision_keys'    => array_keys($decision),
                'payload'          => $brePayload,
            ]);

            list($apiOk, $httpStatus, $apiResponse, $errorMsg) = $this->callDecisionApi($brePayload);

            BlacksoilBreLogger::event('row.api_response', [
                'batch_id'       => $batchId,
                'row'            => $rowNumber,
                'vendor_loan_id' => $vendorLoanId,
                'http_status'    => $httpStatus,
                'ok'             => $apiOk,
                'duration_ms'    => (int) ((microtime(true) - $startedAt) * 1000),
                'response'       => $apiResponse,
                'error'          => $errorMsg,
            ]);

            $status         = $apiOk ? 'success' : 'api_failed';
            $decisionResult = $apiOk && is_array($apiResponse) ? ($apiResponse['decision'] ?? null) : null;
            $now            = Carbon::now()->toDateTimeString();

            $breId = DB::table('vendor_bre_decision')->insertGetId([
                'vendor'          => self::VENDOR,
                'vendor_loan_id'  => $vendorLoanId,
                'application_id'  => $applicationId,
                'row_number'      => $rowNumber,
                'upload_batch_id' => $batchId,
                'payload'         => json_encode($brePayload),
                'api_response'    => $apiResponse !== null ? json_encode($apiResponse) : null,
                'http_status'     => $httpStatus,
                'decision'        => $decisionResult,
                'status'          => $status,
                'error'           => $errorMsg,
                'created_at'      => $now,
                'updated_at'      => $now,
            ]);

            BlacksoilBreLogger::event('row.bre_decision_saved', [
                'batch_id'        => $batchId,
                'row'             => $rowNumber,
                'vendor_loan_id'  => $vendorLoanId,
                'bre_decision_id' => $breId,
                'status'          => $status,
                'decision'        => $decisionResult,
            ]);

            $rowStatus = $apiOk ? $rowStatusOnSuccess : 'api_failed';
            $message   = $apiOk
                ? ($rowStatusOnSuccess === 'created'
                    ? 'Lead created and BRE executed successfully.'
                    : 'Lead already existed; BRE executed successfully.')
                : 'Lead processed but BRE API call failed — can be re-run by uploading this row again.';

            if ($apiOk) {
                $summary['succeeded']++;
            } else {
                $summary['failed']++;
            }

            $results[] = [
                'row'             => $rowNumber,
                'application_id'  => $applicationId,
                'vendor_loan_id'  => $vendorLoanId,
                'bre_decision_id' => $breId,
                'status'          => $rowStatus,
                'message'         => $message,
                'http_status'     => $httpStatus,
                'decision'        => $decisionResult,
                'response'        => $apiResponse,
                'error'           => $errorMsg,
            ];
        }

        BlacksoilBreLogger::event('evaluate.completed', [
            'batch_id' => $batchId,
            'summary'  => $summary,
        ]);

        return response()->json([
            'status'          => 'OK',
            'upload_batch_id' => $batchId,
            'summary'         => $summary,
            'rows'            => $results,
        ]);
    }

    private function rowRules()
    {
        return [
            'application_id'           => 'required',
            'customer_name'            => 'required|string',
            'pan_number'               => 'nullable|string|regex:/^[A-Z]{5}[0-9]{4}[A-Z]{1}$/i',
            'mobile_number'            => 'nullable|digits:10',
            'date_of_birth'            => 'nullable|date',
            'household_monthly_income' => 'nullable|numeric|min:0',
            'loan_amount'              => 'nullable|numeric|min:0',
            'tenure'                   => 'nullable|numeric|min:0',
            'bureau_score'             => 'nullable|numeric',
        ];
    }

    /**
     * Split a sanitized row into demographic (Set 1 - lead) and decision (Set 2 - BRE input) buckets.
     * application_id is included in both so the decision payload is self-contained for the API call.
     */
    private function categorize(array $row)
    {
        $demographicKeys = $this->demographicKeys();
        $decisionKeys    = $this->decisionKeys();
        $calculatedDecisionKeys = $this->calculatedDecisionKeys();

        $demographic = [];
        $decision    = [];

        foreach ($row as $key => $value) {
            if (in_array($key, $demographicKeys, true)) {
                $demographic[$key] = strtolower($value);
            }
            if (in_array($key, $decisionKeys, true)) {
                $decision[$key] = strtolower($value);
            }
        }

        foreach($calculatedDecisionKeys as $calculatedDecisionKey){
            if($normalizeInput = $this->normalizeInput($calculatedDecisionKey, $value, $decision)){
                $decision[$calculatedDecisionKey] = $normalizeInput;
            }
        }

        if (isset($row['application_id'])) {
            $demographic['application_id'] = $row['application_id'];
            $decision['application_id']    = $row['application_id'];
        }

        return [$demographic, $decision];
    }

    private function normalizeInput($key, $value, $decision){
        if($key == 'borrower_maturity_age'){
            return $decision['age'] + ($decision['tenure'] / 12);
        }
        return $value;
    }

    private function createLead(array $demographic, array $row, $batchId, $rowNumber)
    {
        $now = Carbon::now()->toDateTimeString();

        return DB::table('vendor_loans_data')->insertGetId([
            'vendor'            => self::VENDOR,
            'application_id'    => $row['application_id'] ?? null,
            'upload_batch_id'   => $batchId,
            'row_number'        => $rowNumber,
            'customer_name'     => $demographic['customer_name'] ?? null,
            'customer_category' => $demographic['customer_category'] ?? null,
            'mobile_number'     => $demographic['mobile_number'] ?? null,
            'pan_number'        => $demographic['pan_number'] ?? null,
            'aadhaar_number'    => $demographic['aadhaar_number'] ?? null,
            'date_of_birth'     => $this->isoDate($demographic['date_of_birth'] ?? null),
            'gender'            => $demographic['gender'] ?? null,
            'marital_status'    => $demographic['marital_status'] ?? null,
            'current_address'   => $demographic['current_address'] ?? null,
            'current_city'      => $demographic['current_city'] ?? null,
            'current_state'     => $demographic['current_state'] ?? null,
            'current_pincode'   => $demographic['current_pincode'] ?? null,
            'permanent_address' => $demographic['permanent_address'] ?? null,
            'permanent_city'    => $demographic['permanent_city'] ?? null,
            'permanent_state'   => $demographic['permanent_state'] ?? null,
            'permanent_pincode' => $demographic['permanent_pincode'] ?? null,
            'branch_code'       => $demographic['branch_code'] ?? null,
            'name_of_business'  => $demographic['name_of_business'] ?? null,
            'status'            => 'In-Progress',
            'demographics'      => json_encode($demographic),
            'created_at'        => $now,
            'updated_at'        => $now,
        ]);
    }

    /**
     * Persist up to 2 co-applicant demographic rows for a lead.
     * Skips an index when every field for that co-applicant is empty.
     */
    private function saveCoApplicants($vendorLoanId, $applicationId, array $row)
    {
        $now      = Carbon::now()->toDateTimeString();
        $bases    = $this->coApplicantDemographicBases();
        $inserted = 0;

        foreach ([1, 2] as $index) {
            $fields = [];
            foreach ($bases as $base) {
                $sourceKey = $base . '_' . $index;
                $targetKey = preg_replace('/^co_applicant_/', '', $base);
                $fields[$targetKey] = $row[$sourceKey] ?? null;
            }

            if (empty(array_filter($fields, function ($v) { return $v !== null && $v !== ''; }))) {
                continue;
            }

            DB::table('vendor_loans_co_applicants_data')->insert([
                'vendor_loan_id'            => $vendorLoanId,
                'application_id'            => $applicationId,
                'co_applicant_index'        => $index,
                'name'                      => $fields['name'] ?? null,
                'customer_category'         => $fields['customer_category'] ?? null,
                'mobile_number'             => $fields['mobile_number'] ?? null,
                'email'                     => $fields['email'] ?? null,
                'date_of_birth'             => $this->isoDate($fields['date_of_birth'] ?? null),
                'age'                       => $fields['age'] ?? null,
                'gender'                    => $fields['gender'] ?? null,
                'occupation'                => $fields['occupation'] ?? null,
                'marital_status'            => $fields['marital_status'] ?? null,
                'nationality'               => $fields['nationality'] ?? null,
                'relationship'              => $fields['relationship'] ?? null,
                'pan_number'                => $fields['pan_number'] ?? null,
                'aadhaar_number'            => $fields['aadhaar_number'] ?? null,
                'voter_id_number'           => $fields['voter_id_number'] ?? null,
                'dl_number'                 => $fields['dl_number'] ?? null,
                'udyam_registration_number' => $fields['udyam_registration_number'] ?? null,
                'current_address'           => $fields['current_address'] ?? null,
                'current_city'              => $fields['current_city'] ?? null,
                'current_state'             => $fields['current_state'] ?? null,
                'current_pincode'           => $fields['current_pincode'] ?? null,
                'permanent_address'         => $fields['permanent_address'] ?? null,
                'permanent_city'            => $fields['permanent_city'] ?? null,
                'permanent_state'           => $fields['permanent_state'] ?? null,
                'permanent_country'         => $fields['permanent_country'] ?? null,
                'permanent_pincode'         => $fields['permanent_pincode'] ?? null,
                'demographics'              => json_encode($fields),
                'created_at'                => $now,
                'updated_at'                => $now,
            ]);
            $inserted++;
        }

        return $inserted;
    }

    private function saveDecisionData($vendorLoanId, $applicationId, $batchId, array $decision)
    {
        $now = Carbon::now()->toDateTimeString();

        return DB::table('vendor_loans_decision_data')->insertGetId([
            'vendor_loan_id'  => $vendorLoanId,
            'application_id'  => $applicationId,
            'upload_batch_id' => $batchId,
            'decision_data'   => json_encode($decision),
            'created_at'      => $now,
            'updated_at'      => $now,
        ]);
    }

    /**
     * Lookup an existing lead by (vendor, application_id), ignoring soft-deleted rows.
     * Returns the row or null.
     */
    private function findExistingLead($applicationId)
    {
        if ($applicationId === null || $applicationId === '') {
            return null;
        }

        return DB::table('vendor_loans_data')
            ->where('vendor', self::VENDOR)
            ->where('application_id', $applicationId)
            ->whereNull('deleted_at')
            ->orderBy('id', 'desc')
            ->first();
    }

    /**
     * A BRE run is considered "completed" only when it returned a successful API response.
     * Failed/pending rows do NOT count — they can be retried.
     */
    private function findCompletedBre($vendorLoanId)
    {
        return DB::table('vendor_bre_decision')
            ->where('vendor_loan_id', $vendorLoanId)
            ->where('status', 'success')
            ->whereNull('deleted_at')
            ->orderBy('id', 'desc')
            ->first();
    }

    /**
     * Final guard before a value hits a MySQL `date` column.
     * The Import sanitizer normalizes dates to Y-m-d; this catches anything that slipped through.
     */
    private function isoDate($value)
    {
        if ($value === null || $value === '') {
            return null;
        }
        if (preg_match('/^\d{4}-\d{2}-\d{2}$/', (string) $value)) {
            return $value;
        }
        try {
            return Carbon::parse($value)->format('Y-m-d');
        } catch (\Exception $e) {
            return null;
        }
    }

    /**
     * GET the active Rejection Policy from the policy service. The response is later flat-merged
     * with the decision payload before it is sent to the BRE API.
     * Returns [ok, http_status, decodedBody|rawBody, errorMessage|null].
     */
    private function fetchRejectionPolicy()
    {
        try {
            $client = new Client([
                'timeout'         => 15,
                'connect_timeout' => 5,
                'http_errors'     => false,
            ]);

            $response   = $client->get($this->POLICY_API_URL, [
                'headers' => [
                    'X-API-Key'    => $this->POLICY_API_KEY,
                    'X-API-Secret' => $this->POLICY_API_SECRET,
                    'Accept'       => 'application/json',
                ],
            ]);
            $httpStatus = $response->getStatusCode();
            $body       = (string) $response->getBody();
            $decoded    = json_decode($body, true);

            if ($httpStatus >= 200 && $httpStatus < 300) {
                if ($decoded === null) {
                    return [false, $httpStatus, $body, 'Policy response is not valid JSON'];
                }
                return [true, $httpStatus, $decoded, null];
            }

            return [false, $httpStatus, $decoded !== null ? $decoded : $body, 'Non-2xx response from policy API'];
        } catch (RequestException $e) {
            BlacksoilBreLogger::error('policy.transport_failed', [
                'url'   => $this->POLICY_API_URL,
                'error' => $e->getMessage(),
            ]);
            return [false, null, null, 'Policy API request failed: ' . $e->getMessage()];
        } catch (\Exception $e) {
            BlacksoilBreLogger::error('policy.exception', [
                'url'   => $this->POLICY_API_URL,
                'error' => $e->getMessage(),
            ]);
            return [false, null, null, 'Policy API error: ' . $e->getMessage()];
        }
    }

    private function callDecisionApi(array $payload)
    {
        try {
            $client = new Client([
                'timeout'         => 30,
                'connect_timeout' => 10,
                'http_errors'     => false,
            ]);

            $response   = $client->post($this->DECISION_API_URL, [
                'json'    => $payload,
                'headers' => ['Accept' => 'application/json', 'Content-Type' => 'application/json'],
            ]);
            $httpStatus = $response->getStatusCode();
            $body       = (string) $response->getBody();
            $decoded    = json_decode($body, true);

            if ($httpStatus >= 200 && $httpStatus < 300) {
                return [true, $httpStatus, $decoded !== null ? $decoded : $body, null];
            }

            return [false, $httpStatus, $decoded !== null ? $decoded : $body, 'Non-2xx response from decision API'];
        } catch (RequestException $e) {
            BlacksoilBreLogger::error('api.transport_failed', [
                'url'   => $this->DECISION_API_URL,
                'error' => $e->getMessage(),
            ]);
            return [false, null, null, 'Decision API request failed: ' . $e->getMessage()];
        } catch (\Exception $e) {
            BlacksoilBreLogger::error('api.exception', [
                'url'   => $this->DECISION_API_URL,
                'error' => $e->getMessage(),
            ]);
            return [false, null, null, 'Decision API error: ' . $e->getMessage()];
        }
    }

    /**
     * Set 1 — Customer / Lead Demographics. Snake-case slugs derived from the agreed Excel headers.
     */
    private function demographicKeys()
    {
        return [
            'application_id',
            'customer_name', 'customer_category', 'date_of_birth', 'age', 'gender', 'marital_status',
            'current_address', 'current_city', 'current_state', 'current_country', 'current_pincode',
            'permanent_address', 'permanent_city', 'permanent_state', 'permanent_country', 'permanent_pincode',
            'mobile_number', 'pan_number', 'aadhaar_number', 'voter_id_number', 'dl_number', 'udyam_registration_number',
            'no_of_members_in_household', 'no_of_earning_members', 'no_of_dependents', 'income_source',
            'branch_code', 'residence_area', 'residence_latitude', 'residence_longitude',
            'residence_ownership', 'property_owned_by', 'residence_to_branch_distance_km',
            'branch_latitude', 'branch_longitude',
            'name_of_business', 'business_ownership', 'incorporation_date', 'number_of_employees',
            'reference_name_1', 'reference_mobile_number_1', 'reference_name_2', 'reference_mobile_number_2',
            'beneficiary_name', 'beneficiary_account_no', 'account_type', 'ifsc', 'branch_name',
        ];
    }

    /**
     * Co-applicant demographic field bases (without the _1/_2 suffix).
     * The controller iterates over indexes [1, 2] and pulls "{base}_{index}" from the row.
     */
    private function coApplicantDemographicBases()
    {
        return [
            'co_applicant_name', 'co_applicant_customer_category', 'co_applicant_mobile_number',
            'co_applicant_email', 'co_applicant_date_of_birth', 'co_applicant_age', 'co_applicant_gender',
            'co_applicant_occupation', 'co_applicant_marital_status', 'co_applicant_nationality',
            'co_applicant_relationship', 'co_applicant_pan_number', 'co_applicant_aadhaar_number',
            'co_applicant_voter_id_number', 'co_applicant_dl_number', 'co_applicant_udyam_registration_number',
            'co_applicant_current_address', 'co_applicant_current_city', 'co_applicant_current_state',
            'co_applicant_current_pincode', 'co_applicant_permanent_address', 'co_applicant_permanent_city',
            'co_applicant_permanent_state', 'co_applicant_permanent_country', 'co_applicant_permanent_pincode',
        ];
    }

    /**
     * Type dictionary for decision-payload keys. Keys are the BASE slug (without `_1`/`_2`
     * co-applicant suffix, without `co_applicant_` prefix); resolveDecisionType() falls back
     * through those normalizations. Anything not listed defaults to `string`.
     *
     * Supported types: 'int', 'float', 'bool', 'string'.
     * Add or change entries here to alter what the BRE API receives.
     */
    private function decisionTypes()
    {
        return [
            // ── Textual / identifier ─────────────────────────────────────────
            'application_id'                  => 'string',
            'current_node'                    => 'string',
            'execute_next_node'                => 'string',
            'name_of_bureau'                  => 'string',
            'repayment_frequency'             => 'string',
            'loan_purpose'                    => 'string',
            'loan_utilization'                => 'string',
            'financial_status'                => 'string',
            'applicant_ntc'                   => 'string', // "Yes" / "No"
            'ntc'                             => 'string',
            'bureau_pull_date'                => 'string', // ISO date — leave as string
            'gender'                          => 'string',  

            // ── Money (rupees, integer) ──────────────────────────────────────
            'household_monthly_income'        => 'int',
            'household_annual_income'         => 'int',
            'household_expenses'              => 'int',
            'existing_emi_obligations'        => 'int',
            'business_income'                 => 'int',
            'agriculture_income'              => 'int',
            'dairy_income'                    => 'int',
            'eligible_emi'                    => 'int',
            'eligible_loan_amount'            => 'int',
            'loan_amount'                     => 'int',
            'overdue_amount'                  => 'int',
            'bank_statement_6m'               => 'int',
            'asset_fridge_value'              => 'int',
            'asset_air_conditioner_value'     => 'int',
            'asset_washing_machine_value'     => 'int',
            'asset_tv_value'                  => 'int',
            'asset_smart_phone_value'         => 'int',

            // ── Percentages / rates (decimal) ─────────────────────────────────
            'foir'                            => 'float',
            'foir_percent'                    => 'float',
            'interest_rate'                   => 'float',
            'apr'                             => 'float',

            // ── Counts / scores / tenure (integer) ───────────────────────────
            'tenure'                          => 'int',
            'bureau_score'                    => 'int',
            'dpd_0_6_months'                  => 'int',
            'dpd_6_12_months'                 => 'int',
            'bureau_sub_count'                => 'int',
            'bureau_dbt_count'                => 'int',
            'bureau_loss_count'               => 'int',
            'bureau_write_off_count'          => 'int',
            'bureau_settlement_count'         => 'int',
            'bureau_suit_filled_count'        => 'int',
            'bureau_suit_filed_count'         => 'int',
            'bureau_willful_default_count'    => 'int',
            'restructured_count'              => 'int',
            'enquiries_last_3_months'         => 'int',
            'active_credit_lines_count'       => 'int',
            'cheque_bounce_count_6m'          => 'int',
            'residence_to_branch_distance_km' => 'int',

            // ── Years / durations (integer) ──────────────────────────────────
            'age'                             => 'int',
            'residence_vintage_years'         => 'int',
            'business_vintage_years'          => 'int',
            'years_of_stay_in_current_address'=> 'int',

            'borrower_maturity_age'           => 'int',
        ];
    }

    /**
     * Apply the type dictionary to every value in a decision payload.
     * Null/empty values pass through unchanged. If a string value cannot be safely cast
     * to a numeric type (e.g. "TRUE" arriving in a count field), it is left as-is rather
     * than silently turning into 0 — easier to spot upstream upload errors.
     */
    private function castDecisionTypes(array $decision)
    {
        $types  = $this->decisionTypes();
        $casted = [];
        foreach ($decision as $key => $value) {
            if ($value === null || $value === '') {
                $casted[$key] = $value;
                continue;
            }
            $type = $this->resolveDecisionType($key, $types);
            $casted[$key] = $this->castValue($value, $type);
        }
        return $casted;
    }

    private function resolveDecisionType($key, array $types)
    {
        if (isset($types[$key])) {
            return $types[$key];
        }
        // Strip trailing _1, _2 (co-applicant suffix) — e.g. co_applicant_bureau_score_1
        $stripped = preg_replace('/_\d+$/', '', $key);
        if (isset($types[$stripped])) {
            return $types[$stripped];
        }
        // Strip co_applicant_ prefix — e.g. co_applicant_bureau_score → bureau_score
        $base = preg_replace('/^co_applicant_/', '', $stripped);
        if (isset($types[$base])) {
            return $types[$base];
        }
        return 'string';
    }

    private function castValue($value, $type)
    {
        switch ($type) {
            case 'int':
                return is_numeric($value) ? (int) $value : $value;
            case 'float':
                return is_numeric($value) ? (float) $value : $value;
            case 'bool':
                $v = strtolower(trim((string) $value));
                if (in_array($v, ['true', 'yes', '1'], true))  return true;
                if (in_array($v, ['false', 'no', '0'], true)) return false;
                return $value;
            case 'string':
            default:
                return (string) $value;
        }
    }

    /**
     * Set 2 — BRE Decision inputs. Only this payload is sent to the decision API.
     */
    private function decisionKeys()
    {
        return [
            'application_id', 'current_node', 'execute_next_node',
            'applicant_ntc',
            'age',
            'gender',
            'marital_status',
            'residence_area',
            'residence_ownership',
            'residence_to_branch_distance_km',
            'household_monthly_income', 'household_annual_income', 'household_expenses',
            'existing_emi_obligations', 'business_income', 'agriculture_income', 'dairy_income',
            'foir', 'foir_percent',
            'eligible_emi', 'eligible_loan_amount', 'loan_utilization',
            'name_of_bureau', 'bureau_score', 'bureau_pull_date', 'overdue_amount',
            'dpd_0_6_months', 'dpd_6_12_months',
            'bureau_sub_count', 'bureau_dbt_count', 'bureau_loss_count', 'bureau_write_off_count',
            'bureau_settlement_count', 'bureau_suit_filled_count', 'bureau_willful_default_count',
            'restructured_count', 'enquiries_last_3_months', 'active_credit_lines_count',
            'bank_statement_6m', 'cheque_bounce_count_6m',
            'loan_amount', 'tenure', 'interest_rate', 'apr', 'repayment_frequency', 'loan_purpose',
            'residence_vintage_years', 'business_vintage_years',
            'asset_fridge_value', 'asset_air_conditioner_value', 'asset_washing_machine_value',
            'asset_tv_value', 'asset_smart_phone_value',
            'co_applicant_relationship_1',
            'co_applicant_relationship_2',
            'co_applicant_relationship_3',
            'co_applicant_age_1',
            'co_applicant_years_of_stay_in_current_address_1', 'co_applicant_ntc_1', 'co_applicant_financial_status_1',
            'co_applicant_name_of_bureau_1', 'co_applicant_bureau_score_1', 'co_applicant_bureau_pull_date_1',
            'co_applicant_overdue_amount_1', 'co_applicant_dpd_0_6_months_1', 'co_applicant_dpd_6_12_months_1',
            'co_applicant_bureau_sub_count_1', 'co_applicant_bureau_dbt_count_1', 'co_applicant_bureau_loss_count_1',
            'co_applicant_bureau_write_off_count_1', 'co_applicant_bureau_settlement_count_1',
            'co_applicant_bureau_suit_filed_count_1', 'co_applicant_bureau_willful_default_count_1',
            'co_applicant_restructured_count_1', 'co_applicant_enquiries_last_3_months_1',
            'co_applicant_active_credit_lines_count_1',
            'co_applicant_age_2',
            'co_applicant_years_of_stay_in_current_address_2', 'co_applicant_ntc_2', 'co_applicant_financial_status_2',
            'co_applicant_name_of_bureau_2', 'co_applicant_bureau_score_2', 'co_applicant_bureau_pull_date_2',
            'co_applicant_overdue_amount_2', 'co_applicant_dpd_0_6_months_2', 'co_applicant_dpd_6_12_months_2',
            'co_applicant_bureau_sub_count_2', 'co_applicant_bureau_dbt_count_2', 'co_applicant_bureau_loss_count_2',
            'co_applicant_bureau_write_off_count_2', 'co_applicant_bureau_settlement_count_2',
            'co_applicant_bureau_suit_filed_count_2', 'co_applicant_bureau_willful_default_count_2',
            'co_applicant_restructured_count_2', 'co_applicant_enquiries_last_3_months_2',
            'co_applicant_active_credit_lines_count_2',
        ];
    }

    private function calculatedDecisionKeys(){
        return ['borrower_maturity_age'];
    }

    public function calculateDisbursementAmount($loanAmount, $partnerId)
    {
        $loanAmount = floatval($loanAmount);
        if ($partnerId == 1) {
            return $loanAmount * 0.9;
        } elseif ($partnerId == 2) {
            return $loanAmount * 0.8;
        }
        return $loanAmount;
    }

    public function viewReadyDisbursementLeads(Request $request)
    {
        return $this->viewDecisionResults($request);
    }

    public function viewDecisionResults(Request $request)
    {
        $subQuery = "(SELECT vendor_loan_id, MAX(id) as max_bre_id FROM vendor_bre_decision WHERE deleted_at IS NULL GROUP BY vendor_loan_id)";

        $query = DB::table('vendor_loans_data')
            ->leftJoin(DB::raw("{$subQuery} as latest_bre"), 'vendor_loans_data.id', '=', 'latest_bre.vendor_loan_id')
            ->leftJoin('vendor_bre_decision', 'latest_bre.max_bre_id', '=', 'vendor_bre_decision.id')
            ->leftJoin('partner_data', 'vendor_loans_data.partner_id', '=', 'partner_data.id')
            ->select(
                'vendor_loans_data.*',
                'partner_data.name as partner_name',
                'vendor_bre_decision.status as bre_status',
                'vendor_bre_decision.id as bre_id',
                'vendor_bre_decision.payload as bre_payload'
            )
            ->whereNull('vendor_loans_data.deleted_at')
            ->orderBy('vendor_loans_data.id', 'desc');

        if ($request->filled('search_customer')) {
            $query->where('vendor_loans_data.customer_name', 'like', '%' . $request->get('search_customer') . '%');
        }

        if ($request->filled('search_app_id')) {
            $query->where('vendor_loans_data.application_id', 'like', '%' . $request->get('search_app_id') . '%');
        }

        if ($request->is('*/ready-disbursement') || $request->get('ready_only', 0)) {
            if (\Schema::hasColumn('vendor_loans_data', 'status')) {
                $query->where('vendor_loans_data.status', 'Ready for disbursement');
            } else {
                $query->join('vendor_status_logs', function ($join) {
                    $join->on('vendor_loans_data.id', '=', 'vendor_status_logs.vendor_loan_id')
                        ->whereRaw('vendor_status_logs.id = (SELECT MAX(id) FROM vendor_status_logs WHERE vendor_loan_id = vendor_loans_data.id AND deleted_at IS NULL)');
                })
                ->where('vendor_status_logs.status', 'Ready for disbursement')
                ->whereNull('vendor_status_logs.deleted_at');
            }
        }

        $decisions = $query->paginate(20);

        $summaryCounts = DB::table('vendor_loans_data')
            ->whereNull('deleted_at')
            ->selectRaw("
                COUNT(id) as total_leads,
                COUNT(DISTINCT partner_id) as total_partners
            ")
            ->first();

        $totalDecisions = DB::table('vendor_bre_decision')
            ->whereNull('deleted_at')
            ->count();

        $readyCount = 0;
        if (\Schema::hasColumn('vendor_loans_data', 'status')) {
            $readyCount = DB::table('vendor_loans_data')
                ->whereNull('deleted_at')
                ->where('status', 'Ready for disbursement')
                ->count();
        } else {
            $readyCount = DB::table('vendor_loans_data')
                ->join('vendor_status_logs', function ($join) {
                    $join->on('vendor_loans_data.id', '=', 'vendor_status_logs.vendor_loan_id')
                        ->whereRaw('vendor_status_logs.id = (SELECT MAX(id) FROM vendor_status_logs WHERE vendor_loan_id = vendor_loans_data.id AND deleted_at IS NULL)');
                })
                ->where('vendor_status_logs.status', 'Ready for disbursement')
                ->whereNull('vendor_loans_data.deleted_at')
                ->whereNull('vendor_status_logs.deleted_at')
                ->count();
        }

        $summaryData = [
            'total_leads'            => $summaryCounts->total_leads ?? 0,
            'total_partners'         => $summaryCounts->total_partners ?? 0,
            'total_decisions'        => $totalDecisions ?? 0,
            'ready_disbursement'     => $readyCount,
        ];

        return view('admin.co-lending.decision-results', compact('decisions', 'summaryData'));
    }

    public function viewDecisionDetail($id)
    {
        $decision = DB::table('vendor_bre_decision')
            ->leftJoin('vendor_loans_data', 'vendor_bre_decision.vendor_loan_id', '=', 'vendor_loans_data.id')
            ->leftJoin('partner_data', 'vendor_loans_data.partner_id', '=', 'partner_data.id')
            ->select('vendor_bre_decision.*', 'vendor_loans_data.customer_name', 'vendor_loans_data.gender', 'partner_data.name as partner_name')
            ->where('vendor_bre_decision.id', $id)
            ->first();

        if (!$decision) {
            abort(404, 'Decision not found');
        }

        return view('admin.co-lending.decision-detail', compact('decision'));
    }

    public function viewLeadChecklist($id)
    {
        $lead = DB::table('vendor_loans_data')
            ->leftJoin('partner_data', 'vendor_loans_data.partner_id', '=', 'partner_data.id')
            ->select('vendor_loans_data.*', 'partner_data.name as partner_name')
            ->where('vendor_loans_data.id', $id)
            ->whereNull('vendor_loans_data.deleted_at')
            ->first();

        if (!$lead) {
            abort(404, 'Lead not found');
        }

        $breDecision = DB::table('vendor_bre_decision')
            ->where('vendor_loan_id', $id)
            ->whereNull('deleted_at')
            ->orderBy('id', 'desc')
            ->first();

        $latestStatus = DB::table('vendor_status_logs')
            ->where('vendor_loan_id', $id)
            ->whereNull('deleted_at')
            ->orderBy('id', 'desc')
            ->first();

        $currentStatus = ($latestStatus && !empty($latestStatus->status)) ? trim($latestStatus->status) : 'In-Progress';
        $allowedOptions = $this->getAllowedNextStatuses($currentStatus);

        $statusLogs = DB::table('vendor_status_logs')
            ->leftJoin('users', 'vendor_status_logs.created_by', '=', 'users.id')
            ->select('vendor_status_logs.*', 'users.name as user_name')
            ->where('vendor_status_logs.vendor_loan_id', $id)
            ->whereNull('vendor_status_logs.deleted_at')
            ->orderBy('vendor_status_logs.id', 'desc')
            ->get();

        $comments = DB::table('vendor_loans_comments')
            ->leftJoin('users', 'vendor_loans_comments.created_by', '=', 'users.id')
            ->select('vendor_loans_comments.*', 'users.name as user_name')
            ->where('vendor_loans_comments.vendor_loan_id', $id)
            ->whereNull('vendor_loans_comments.deleted_at')
            ->orderBy('vendor_loans_comments.id', 'desc')
            ->get();

        $disbursementDetails = null;
        if (strtolower(trim($currentStatus)) === 'disbursed') {
            $disbursementDetails = DB::table('vendor_disbursement_details')
                ->leftJoin('users', 'vendor_disbursement_details.created_by', '=', 'users.id')
                ->select('vendor_disbursement_details.*', 'users.name as user_name')
                ->where('vendor_disbursement_details.vendor_loan_id', $id)
                ->whereNull('vendor_disbursement_details.deleted_at')
                ->orderBy('vendor_disbursement_details.id', 'desc')
                ->first();
        }

        $documents = collect();
        if (!empty($lead->application_id)) {
            $documents = DB::table('blacksoil_documents')
                ->leftJoin('users', 'blacksoil_documents.created_by', '=', 'users.id')
                ->select('blacksoil_documents.*', 'users.name as user_name')
                ->where('blacksoil_documents.loan_id', $lead->application_id)
                ->orderBy('blacksoil_documents.id', 'desc')
                ->get();
        }

        // Resolve Main Category ID from vendor_loans_data.customer_category
        $mainCategoryId = null;
        if (!empty($lead->customer_category)) {
            $catRecord = DB::table('master_config_da_document_categories')
                ->where('id', $lead->customer_category)
                ->orWhere('name', $lead->customer_category)
                ->first();
            if ($catRecord) {
                $mainCategoryId = $catRecord->id;
            }
        }

        // Fetch configured document requirements for partner & main category
        $preSanctionConfigs = collect();
        $postSanctionConfigs = collect();

        if (!empty($lead->partner_id)) {
            $configQuery = \App\DaPartnerDocumentConfig::with(['documentMaster.docCategory', 'subCategory'])
                ->where('partner_id', $lead->partner_id);

            if ($mainCategoryId) {
                $configQuery->where('category_id', $mainCategoryId);
            }

            $partnerConfigs = $configQuery->get();

            $preSanctionConfigs = $partnerConfigs->where('stage', 'pre_sanction')->values();
            $postSanctionConfigs = $partnerConfigs->where('stage', 'post_sanction')->values();
        }

        // Match uploaded documents to requirements and collect unmatched ones
        $matchedDocumentIds = [];

        $findUploadedDoc = function($docMaster) use ($documents, &$matchedDocumentIds) {
            if (!$docMaster) return null;
            $latestDoc = null;

            foreach ($documents as $doc) {
                $uploadedName = strtolower(trim((string)($doc->file_name ?? '')));
                $uploadedCode = strtolower(trim((string)($doc->document_code ?? '')));
                $masterName   = strtolower(trim((string)($docMaster->name ?? '')));
                $masterCode   = strtolower(trim((string)($docMaster->code ?? '')));

                if (($uploadedCode !== '' && $uploadedCode === $masterCode) ||
                    ($uploadedName !== '' && ($uploadedName === $masterName || $uploadedName === $masterCode))) {
                    $matchedDocumentIds[] = $doc->id;
                    if (!$latestDoc) {
                        $latestDoc = $doc;
                    }
                }
            }
            return $latestDoc;
        };

        // Fetch document audit logs for this lead
        $documentLogs = DB::table('blacksoil_document_logs')
            ->leftJoin('users', 'blacksoil_document_logs.created_by', '=', 'users.id')
            ->select('blacksoil_document_logs.*', 'users.name as user_name')
            ->where('blacksoil_document_logs.vendor_loan_id', $id)
            ->orderBy('blacksoil_document_logs.id', 'desc')
            ->get();

        $findDocLogs = function($docMaster, $uploadedDoc) use ($documentLogs) {
            $masterCode = strtolower(trim((string)($docMaster->code ?? '')));
            $masterName = strtolower(trim((string)($docMaster->name ?? '')));
            $uploadedId = $uploadedDoc->id ?? null;

            return $documentLogs->filter(function($log) use ($masterCode, $masterName, $uploadedId) {
                if ($uploadedId && !empty($log->blacksoil_document_id) && $log->blacksoil_document_id == $uploadedId) {
                    return true;
                }
                $logCode = strtolower(trim((string)($log->document_code ?? '')));
                $logName = strtolower(trim((string)($log->document_name ?? '')));
                return ($masterCode !== '' && $logCode === $masterCode) ||
                       ($masterName !== '' && ($logName === $masterName || $logName === $masterCode));
            })->values();
        };

        $getLatestDocStatus = function($uploadedDoc, $docLogs) {
            if (!$uploadedDoc) return null;

            $docLog = $docLogs->first(function($log) use ($uploadedDoc) {
                if (!empty($log->blacksoil_document_id) && $log->blacksoil_document_id == $uploadedDoc->id) {
                    return true;
                }
                if (!empty($uploadedDoc->created_at) && !empty($log->created_at)) {
                    $uploadedTime = strtotime($uploadedDoc->created_at);
                    $logTime      = strtotime($log->created_at);
                    if ($logTime >= $uploadedTime) {
                        return true;
                    }
                }
                return false;
            });

            return $docLog ? strtolower(trim((string)$docLog->action)) : null;
        };

        foreach ($preSanctionConfigs as $cfg) {
            $cfg->uploadedDocument = $findUploadedDoc($cfg->documentMaster);
            $cfg->docLogs          = $findDocLogs($cfg->documentMaster, $cfg->uploadedDocument);
            $cfg->latestStatus     = $getLatestDocStatus($cfg->uploadedDocument, $cfg->docLogs);
        }
        foreach ($postSanctionConfigs as $cfg) {
            $cfg->uploadedDocument = $findUploadedDoc($cfg->documentMaster);
            $cfg->docLogs          = $findDocLogs($cfg->documentMaster, $cfg->uploadedDocument);
            $cfg->latestStatus     = $getLatestDocStatus($cfg->uploadedDocument, $cfg->docLogs);
        }

        $otherDocuments = $documents->filter(function($doc) use ($matchedDocumentIds) {
            return !in_array($doc->id, $matchedDocumentIds);
        })->values();

        $masterDocuments = \App\DaDocumentMaster::with('docCategory')->where('status', 1)->orderBy('name', 'asc')->get();

        return view('admin.co-lending.lead-checklist', compact(
            'lead',
            'breDecision',
            'latestStatus',
            'currentStatus',
            'allowedOptions',
            'statusLogs',
            'comments',
            'disbursementDetails',
            'documents',
            'preSanctionConfigs',
            'postSanctionConfigs',
            'otherDocuments',
            'masterDocuments',
            'documentLogs'
        ));
    }

    public function saveLeadStatusComment(Request $request)
    {
        try {
            $vendorLoanId     = $request->get('vendor_loan_id');
            $status           = $request->get('status');
            $remark           = trim($request->get('remark', ''));
            $visibleToPartner = $request->has('visible_to_partner') ? (int) $request->get('visible_to_partner') : 1;
            $userId           = \Auth::id() ?? Session::get('user_id');
            $now              = Carbon::now();

            if (!$vendorLoanId) {
                return response()->json(['success' => false, 'message' => 'vendor_loan_id is required.'], 400);
            }

            // Wrap all operations in a database transaction to ensure data integrity
            return DB::transaction(function () use ($request, $vendorLoanId, $status, $remark, $visibleToPartner, $userId, $now) {
                if (!empty($status)) {
                    // Optimization: Query vendor_loans_data directly by primary key if status column exists, otherwise fallback to logs
                    if (\Schema::hasColumn('vendor_loans_data', 'status')) {
                        $loan = DB::table('vendor_loans_data')->where('id', $vendorLoanId)->whereNull('deleted_at')->first();
                        $currentStatus = ($loan && !empty($loan->status)) ? trim($loan->status) : 'In-Progress';
                    } else {
                        $latestStatus = DB::table('vendor_status_logs')
                            ->where('vendor_loan_id', $vendorLoanId)
                            ->whereNull('deleted_at')
                            ->orderBy('id', 'desc')
                            ->first();
                        $currentStatus = ($latestStatus && !empty($latestStatus->status)) ? trim($latestStatus->status) : 'In-Progress';
                    }

                    $allowedNext = $this->getAllowedNextStatuses($currentStatus);

                    $isAllowed = false;
                    foreach ($allowedNext as $opt) {
                        if (strtolower(trim($opt)) === strtolower(trim($status))) {
                            $isAllowed = true;
                            break;
                        }
                    }

                    if (!$isAllowed) {
                        return response()->json([
                            'success' => false,
                            'message' => "Transition to '{$status}' is not allowed from '{$currentStatus}'.",
                        ], 400);
                    }

                    $targetStatusClean = strtolower(trim($status));
                    if ($targetStatusClean === 'approved') {
                        if (!$this->validateDocumentsApproved($vendorLoanId, 'pre_sanction')) {
                            return response()->json([
                                'success' => false,
                                'message' => "Cannot change status to 'Approved'. All Pre-Sanction documents must be uploaded and approved first.",
                            ], 400);
                        }
                    }

                    if ($targetStatusClean === 'disbursement checklist done') {
                        if (!$this->validateDocumentsApproved($vendorLoanId, 'post_sanction')) {
                            return response()->json([
                                'success' => false,
                                'message' => "Cannot change status to 'Disbursement Checklist Done'. All Post-Sanction documents must be uploaded and approved first.",
                            ], 400);
                        }
                    }

                    if (strtolower(trim($status)) === 'disbursed') {
                        $utrNumber = trim($request->get('utr_number', ''));
                        $disbursementDate = trim($request->get('disbursement_date', ''));

                        // Retrieve the loan record to calculate the official disbursement amount
                        $loanRecord = DB::table('vendor_loans_data')->where('id', $vendorLoanId)->first();
                        if (!$loanRecord) {
                            return response()->json(['success' => false, 'message' => 'Loan record not found.'], 404);
                        }

                        $calculatedAmountVal = $this->calculateDisbursementAmount($loanRecord->loan_amount, $loanRecord->partner_id);
                        $amount = number_format($calculatedAmountVal, 2, '.', '');

                        if (empty($utrNumber)) {
                            return response()->json(['success' => false, 'message' => 'UTR number is required.'], 400);
                        }
                        if (empty($disbursementDate)) {
                            return response()->json(['success' => false, 'message' => 'Disbursement date is required.'], 400);
                        }

                        DB::table('vendor_disbursement_details')->insert([
                            'vendor_loan_id'    => $vendorLoanId,
                            'utr_number'        => $utrNumber,
                            'disbursement_date' => $disbursementDate,
                            'amount'            => $amount,
                            'created_by'        => $userId,
                            'created_at'        => $now,
                            'updated_at'        => $now,
                        ]);
                    }

                    $statusLogData = [
                        'vendor_loan_id' => $vendorLoanId,
                        'status'         => $status,
                        'created_by'     => $userId,
                        'created_at'     => $now,
                        'updated_at'     => $now,
                    ];
                    if (\Schema::hasColumn('vendor_status_logs', 'remark')) {
                        $statusLogData['remark'] = $remark;
                    }
                    if (\Schema::hasColumn('vendor_status_logs', 'visible_to_partner')) {
                        $statusLogData['visible_to_partner'] = $visibleToPartner;
                    }

                    DB::table('vendor_status_logs')->insert($statusLogData);

                    DB::table('vendor_loans_data')
                        ->where('id', $vendorLoanId)
                        ->update([
                            'status'     => $status,
                            'updated_at' => $now,
                        ]);
                }

                if (!empty($remark)) {
                    DB::table('vendor_loans_comments')->insert([
                        'vendor_loan_id' => $vendorLoanId,
                        'remark'         => $remark,
                        'created_by'     => $userId,
                        'created_at'     => $now,
                        'updated_at'     => $now,
                    ]);
                }

                return response()->json([
                    'success' => true,
                    'message' => 'Status & remark updated successfully.',
                ]);
            });
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to save: ' . $e->getMessage(),
            ], 500);
        }
    }

    public function uploadLeadDocument(Request $request)
    {
        try {
            $loanId       = $request->get('loan_id');
            $documentName = trim((string)$request->get('document_name', ''));
            $documentCode = trim((string)$request->get('document_code', ''));

            if (!$loanId || !$request->hasFile('file')) {
                return response()->json([
                    'success' => false,
                    'message' => 'Loan ID and file are required.'
                ], 400);
            }

            $file         = $request->file('file');
            $originalName = $file->getClientOriginalName();
            $storedName   = time() . '_' . str_replace(' ', '_', $originalName);
            $filePath     = $file->storeAs('blacksoil_documents', $storedName, 'public');

            $userId = \Auth::id() ?? Session::get('user_id');
            $now    = Carbon::now();

            DB::table('blacksoil_documents')->insert([
                'loan_id'       => $loanId,
                'file_name'     => !empty($documentName) ? $documentName : $originalName,
                'document_code' => !empty($documentCode) ? $documentCode : null,
                'file_path'      => '/storage/' . $filePath,
                'created_by'    => $userId,
                'created_at'    => $now,
                'updated_at'    => $now,
            ]);

            return response()->json([
                'success' => true,
                'message' => 'Document uploaded successfully.'
            ]);
        } catch (\Exception $e) {
            \Log::error("Document upload error: " . $e->getMessage());
            return response()->json([
                'success' => false,
                'message' => 'Error uploading document: ' . $e->getMessage()
            ], 500);
        }
    }

    public function reviewLeadDocument(Request $request)
    {
        try {
            $vendorLoanId = $request->get('vendor_loan_id');
            $action       = strtolower(trim((string)$request->get('action', '')));
            $remark       = trim((string)$request->get('remark', ''));
            $docName      = trim((string)$request->get('document_name', ''));
            $userId       = \Auth::id() ?? Session::get('user_id');
            $now          = Carbon::now();

            if (!$vendorLoanId) {
                return response()->json(['success' => false, 'message' => 'vendor_loan_id is required.'], 400);
            }

            if (!in_array($action, ['approve', 'reject'])) {
                return response()->json(['success' => false, 'message' => 'Invalid action.'], 400);
            }

            $docId   = $request->get('document_id');
            $docCode = $request->get('document_code');

            if (!empty($docId) && (empty($docCode) || empty($docName))) {
                $docObj = DB::table('blacksoil_documents')->where('id', $docId)->first();
                if ($docObj) {
                    if (empty($docCode) && !empty($docObj->document_code)) {
                        $docCode = $docObj->document_code;
                    }
                    if (empty($docName) && !empty($docObj->file_name)) {
                        $docName = $docObj->file_name;
                    }
                }
            }

            $loan = DB::table('vendor_loans_data')->where('id', $vendorLoanId)->whereNull('deleted_at')->first();
            if (!$loan) {
                return response()->json(['success' => false, 'message' => 'Loan record not found.'], 404);
            }

            $latestStatusLog = DB::table('vendor_status_logs')
                ->where('vendor_loan_id', $vendorLoanId)
                ->whereNull('deleted_at')
                ->orderBy('id', 'desc')
                ->first();

            $currentStatus = ($latestStatusLog && !empty($latestStatusLog->status))
                ? trim($latestStatusLog->status)
                : (($loan && !empty($loan->status)) ? trim($loan->status) : 'In-Progress');
            $currStatusLower = strtolower($currentStatus);

            $docStage = null;
            if (!empty($docCode) || !empty($docName)) {
                $docMaster = \App\DaDocumentMaster::where(function ($q) use ($docCode, $docName) {
                    if (!empty($docCode)) {
                        $q->where('code', $docCode);
                    }
                    if (!empty($docName)) {
                        $q->orWhere('name', $docName);
                    }
                })->first();
                if ($docMaster && !empty($loan->partner_id)) {
                    $partnerConfig = \App\DaPartnerDocumentConfig::where('partner_id', $loan->partner_id)
                        ->where('document_master_id', $docMaster->id)
                        ->first();
                    if ($partnerConfig) {
                        $docStage = strtolower(trim((string)$partnerConfig->stage));
                    }
                }
            }

            $preSanctionLockStatuses  = ['approved', 'disbursement checklist done', 'ready for disbursement', 'disbursed'];
            $postSanctionLockStatuses = ['disbursement checklist done', 'ready for disbursement', 'disbursed'];

            if ($docStage === 'pre_sanction' && in_array($currStatusLower, $preSanctionLockStatuses)) {
                return response()->json(['success' => false, 'message' => 'Cannot modify Pre-Sanction documents as the lead is already Approved.'], 400);
            }
            if ($docStage === 'post_sanction' && in_array($currStatusLower, $postSanctionLockStatuses)) {
                return response()->json(['success' => false, 'message' => 'Cannot modify Post-Sanction documents as status is Disbursement Checklist Done.'], 400);
            }

            $userObj  = $userId ? DB::table('users')->where('id', $userId)->first() : null;
            $userName = $userObj ? $userObj->name : 'User';

            // Record audit entry in blacksoil_document_logs
            DB::table('blacksoil_document_logs')->insert([
                'vendor_loan_id'        => $vendorLoanId,
                'blacksoil_document_id' => $docId,
                'document_name'         => $docName,
                'document_code'         => $docCode,
                'action'                => ($action === 'approve' ? 'approved' : 'rejected'),
                'remark'                => ($action === 'reject' ? $remark : null),
                'created_by'            => $userId,
                'created_at'            => $now,
                'updated_at'            => $now,
            ]);

            $logPayload = [
                'action'        => ($action === 'approve' ? 'approved' : 'rejected'),
                'user_name'     => $userName,
                'document_name' => $docName,
                'remark'        => ($action === 'reject' ? $remark : null),
                'created_at'    => $now->format('Y-m-d H:i:s'),
                'document_id'   => $docId,
            ];

            if ($action === 'reject') {
                if (empty($remark)) {
                    return response()->json(['success' => false, 'message' => 'Remark is mandatory when rejecting a document.'], 400);
                }

                $loan = DB::table('vendor_loans_data')->where('id', $vendorLoanId)->whereNull('deleted_at')->first();
                if (!$loan) {
                    return response()->json(['success' => false, 'message' => 'Loan record not found.'], 404);
                }

                $latestStatusLog = DB::table('vendor_status_logs')
                    ->where('vendor_loan_id', $vendorLoanId)
                    ->whereNull('deleted_at')
                    ->orderBy('id', 'desc')
                    ->first();

                $currentStatus = ($latestStatusLog && !empty($latestStatusLog->status))
                    ? trim($latestStatusLog->status)
                    : (($loan && !empty($loan->status)) ? trim($loan->status) : 'In-Progress');

                $formattedRemark = (!empty($docName) ? "Rejected document ({$docName}): " : "Rejected document: ") . $remark;

                $targetRejectionStatus = ($docStage === 'post_sanction') ? 'Disbursement Query' : 'Waiting for Documents';
                $targetStatusLower     = strtolower($targetRejectionStatus);

                $statusUpdated = false;
                if (strtolower($currentStatus) !== $targetStatusLower) {
                    $statusLogData = [
                        'vendor_loan_id' => $vendorLoanId,
                        'status'         => $targetRejectionStatus,
                        'created_by'     => $userId,
                        'created_at'     => $now,
                        'updated_at'     => $now,
                    ];
                    if (\Schema::hasColumn('vendor_status_logs', 'remark')) {
                        $statusLogData['remark'] = $formattedRemark;
                    }
                    if (\Schema::hasColumn('vendor_status_logs', 'visible_to_partner')) {
                        $statusLogData['visible_to_partner'] = 1;
                    }
                    DB::table('vendor_status_logs')->insert($statusLogData);

                    DB::table('vendor_loans_data')
                        ->where('id', $vendorLoanId)
                        ->update([
                            'status'     => $targetRejectionStatus,
                            'updated_at' => $now,
                        ]);
                    $statusUpdated = true;
                } else {
                    DB::table('vendor_loans_comments')->insert([
                        'vendor_loan_id' => $vendorLoanId,
                        'remark'         => $formattedRemark,
                        'created_by'     => $userId,
                        'created_at'     => $now,
                        'updated_at'     => $now,
                    ]);
                }

                return response()->json([
                    'success'        => true,
                    'message'        => 'Document rejected successfully.',
                    'action'         => 'rejected',
                    'document_id'    => $docId,
                    'document_code'  => $docCode,
                    'log'            => $logPayload,
                    'status_updated' => $statusUpdated
                ]);
            }

            if ($action === 'approve') {
                $formattedRemark = !empty($docName) ? "Approved document: {$docName}" : "Approved document";

                DB::table('vendor_loans_comments')->insert([
                    'vendor_loan_id' => $vendorLoanId,
                    'remark'         => $formattedRemark,
                    'created_by'     => $userId,
                    'created_at'     => $now,
                    'updated_at'     => $now,
                ]);

                return response()->json([
                    'success'       => true,
                    'message'       => 'Document approved successfully.',
                    'action'        => 'approved',
                    'document_id'   => $docId,
                    'document_code' => $docCode,
                    'log'           => $logPayload
                ]);
            }
        } catch (\Exception $e) {
            \Log::error("Document review error: " . $e->getMessage());
            return response()->json([
                'success' => false,
                'message' => 'Error processing document review: ' . $e->getMessage()
            ], 500);
        }
    }

    private function validateDocumentsApproved($vendorLoanId, $stage)
    {
        $loan = DB::table('vendor_loans_data')->where('id', $vendorLoanId)->first();
        if (!$loan) return false;

        $mainCategoryId = null;
        if (!empty($loan->customer_category)) {
            $catRecord = DB::table('master_config_da_document_categories')
                ->where('id', $loan->customer_category)
                ->orWhere('name', $loan->customer_category)
                ->first();
            if ($catRecord) {
                $mainCategoryId = $catRecord->id;
            }
        }

        if (empty($loan->partner_id)) {
            return true;
        }

        $configQuery = \App\DaPartnerDocumentConfig::with(['documentMaster.docCategory', 'subCategory'])
            ->where('partner_id', $loan->partner_id)
            ->where('stage', $stage);

        if ($mainCategoryId) {
            $configQuery->where('category_id', $mainCategoryId);
        }

        $configs = $configQuery->get();
        if ($configs->isEmpty()) {
            return true;
        }

        $documents = collect();
        if (!empty($loan->application_id)) {
            $documents = DB::table('blacksoil_documents')
                ->where('loan_id', $loan->application_id)
                ->orderBy('id', 'desc')
                ->get();
        }

        $documentLogs = DB::table('blacksoil_document_logs')
            ->where('vendor_loan_id', $vendorLoanId)
            ->orderBy('id', 'desc')
            ->get();

        foreach ($configs as $cfg) {
            $docMaster = $cfg->documentMaster;
            if (!$docMaster) continue;

            $masterCode = strtolower(trim((string)($docMaster->code ?? '')));
            $masterName = strtolower(trim((string)($docMaster->name ?? '')));

            $uploadedDoc = $documents->first(function($doc) use ($masterCode, $masterName) {
                $uploadedName = strtolower(trim((string)($doc->file_name ?? '')));
                $uploadedCode = strtolower(trim((string)($doc->document_code ?? '')));
                return ($uploadedCode !== '' && $uploadedCode === $masterCode) ||
                       ($uploadedName !== '' && ($uploadedName === $masterName || $uploadedName === $masterCode));
            });

            if (!$uploadedDoc) {
                return false;
            }

            $uploadedId = $uploadedDoc->id;
            $latestLog = $documentLogs->first(function($log) use ($masterCode, $masterName, $uploadedId) {
                if (!empty($log->blacksoil_document_id) && $log->blacksoil_document_id == $uploadedId) {
                    return true;
                }
                $logCode = strtolower(trim((string)($log->document_code ?? '')));
                $logName = strtolower(trim((string)($log->document_name ?? '')));
                return ($masterCode !== '' && $logCode === $masterCode) ||
                       ($masterName !== '' && ($logName === $masterName || $logName === $masterCode));
            });

            if (!$latestLog || strtolower(trim((string)$latestLog->action)) !== 'approved') {
                return false;
            }
        }

        return true;
    }

    private function getAllowedNextStatuses($currentStatus)
    {
        $currentStatus = trim($currentStatus);
        if (empty($currentStatus)) {
            $currentStatus = 'In-Progress';
        }

        $map = [
            'In-Progress'            => ['Waiting for Documents', 'Pre Approval', 'Rejected'],
            'Pre Approval'            => ['Waiting for Documents', 'Approved'],
            'Waiting for Decision'   => ['Waiting for Documents', 'Approved'],
            'Waiting for Documents'  => ['Waiting for Decision', 'Approved'],
            'Approved'               => ['Disbursement Checklist Done'],
            'Disbursement Checklist Done' => ['Ready for disbursement'],
            'Disbursement Query'     => ['Disbursement Checklist Done'],
            'Ready for disbursement' => ['Disbursed'],
            'Disbursed'              => [],
            'Rejected'               => [],
        ];

        foreach ($map as $statusKey => $options) {
            if (strtolower($statusKey) === strtolower($currentStatus)) {
                return $options;
            }
        }

        return ['Waiting for Decision', 'Waiting for Documents', 'Approved', 'Rejected'];
    }

    public function colendingBREAPI($vendorLoanId) {
        return true;
    }
}