<?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,
'partner_id'        => $row['partner_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,
            '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 viewDecisionResults(Request $request)
    {
        $query = DB::table('vendor_loans_data')
            ->leftJoin('vendor_bre_decision', 'vendor_loans_data.id', '=', 'vendor_bre_decision.vendor_loan_id')
            ->select('vendor_loans_data.*', 'vendor_bre_decision.status as bre_status', 'vendor_bre_decision.id as bre_id', 'vendor_bre_decision.payload as bre_payload')
            ->orderBy('vendor_loans_data.id', 'desc');
        
        $decisions = $query->paginate(20);

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

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

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

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