<?php

namespace App\Http\Controllers\Admin;

use App\CampaignsJobProvider;
use App\CoLendingLoan;
use App\DaSystemData;
use Illuminate\Support\Facades\Auth;
use App\Logging\CloudWatchLoggerFactory;
use App\Http\Controllers\Controller;
use App\Imports\CoLendingRPSImport;
use App\Imports\DaSystemDataImport;
use App\Slack;
use Carbon\Carbon;
use GuzzleHttp\Client;
use GuzzleHttp\Exception\RequestException;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Session;
use Illuminate\Support\Facades\Storage;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Support\Facades\DB;

class CoLendingController extends Controller
{

    // ─── DA Loan Data ────────────────────────────────────────────────────────

    public static function registry(){
        return [
            [
                'name'    => 'Partners',
                'summary' => 'Manage Co-Lending system partner configurations and details.',
                'url'     => null,
            ],
            [
                'name'    => 'Import Loans',
                'summary' => 'Upload and process Co-Lending loan data files in bulk.',
                'url'     => '/admin/co-lending',
            ],
            [
                'name'    => 'View Loans',
                'summary' => 'Browse and search all Co-Lending system loan records.',
                'url'     => '/admin/co-lending/loans',
            ],
        ];
    }

    public function index()
    {
        $jobs = CampaignsJobProvider::whereIn('from', ['co_lending_system_rps_upload', 'co_lending_system_payment_upload'])
            ->orderBy('id', 'desc')
            ->limit(20)
            ->get(['id', 'payload', 'is_available', 'created_at']);

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

    public function loans(Request $request)
    {
        $query = CoLendingLoan::whereNull('deleted_at');

        if ($request->filled('search_loan')) {
            $query->where('loan_id', 'like', '%' . $request->get('search_loan') . '%');
        }

        if ($request->filled('search_product')) {
            $query->where('product_id', 'like', '%' . $request->get('search_product') . '%');
        }

        if ($request->filled('search_date')) {
            $query->whereDate('disbursement_date', $request->get('search_date'));
        }

        $loans = $query->orderBy('id', 'desc')
            ->paginate(25, [
                'id', 'loan_id', 'loan_amount', 'interest_rate', 'tenure',
                'disbursement_date', 'first_emi_date', 'product_id',
            ]);

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

    public function loanDetail($id)
    {
        $loan = CoLendingLoan::whereNull('deleted_at')->findOrFail($id);
        return view('admin.co-lending.loan-detail-v2', compact('loan'));
    }


    public function rpsImport(Request $request)
    {
        try {
            if ($request->has('file') && $request->files->get('file') != null) {
                if (filesize($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_rps.' . $request->file('file')->getClientOriginalExtension();

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

            $data = $request->all();
            $data['file_path'] = $file_path;
            $data['file_name'] = $name;
            $data['user_id'] = Auth::user()->id ?? 107;

            $jobs = new CampaignsJobProvider();
            $jobs->from = 'co_lending_system_rps_upload';
            $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', 'RPS 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();
        }
    }

    /**
     * Parse the uploaded RPS Excel from S3 and save each row into co_lending_loans.
     * Does NOT call the external API — use processCoLendingRPSApi() for that.
     *
     * Expected Excel headers:
     *   loan_id, loan_amount, interest_rate, tenure, disbursement_date, first_emi_date, product_id
     */
    public function startCoLendingRPSFileProcess(Request $request)
    {
        $s3Path   = $request->get('file_path');
        $fileName = $request->get('file_name');
        $user_id = $request->get('user_id');

        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);
            $tempPath     = sys_get_temp_dir() . '/' . $fileName;
            file_put_contents($tempPath, $fileContents);

            // Parse the Excel and insert rows via Maatwebsite Excel
            $import = new CoLendingRPSImport($fileName, $s3Path);
            (new \App\Logging\CloudWatchLoggerFactory())->logger(json_encode([
                'process' => 'Excel Import',
                'status' => 'Info',
                'message' => 'Starting Excel import for RPS file: ' . $fileName
            ]));
            Excel::import($import, $tempPath);
            (new \App\Logging\CloudWatchLoggerFactory())->logger(json_encode([
                'process' => 'Excel Import',
                'status' => 'Info',
                'message' => 'Finished Excel import. Total rows inserted: ' . $import->inserted
            ]));

            // Clean up temp file
            @unlink($tempPath);

            // Collect ALL loan IDs from the file and send a single bulk API call
            $loanIds = DB::table('co_lending_loans as cll')
                ->leftJoin('co_lending_additional as cla', 'cll.loan_id', '=', 'cla.loan_id')
                ->whereIn('cll.loan_id', $import->getLoanIds())
                ->whereNull('cll.deleted_at')
                ->get([
                    'cll.loan_id',
                    'cll.loan_amount',
                    'cll.interest_rate',
                    'cll.tenure',
                    'cll.disbursement_date',
                    'cll.product_id',
                    'cla.bounce_type'
                ]);


            (new \App\Logging\CloudWatchLoggerFactory())->logger(json_encode([
                'process' => 'Prepare payload',
                'status' => 'Info',
                'message' => 'Fetched ' . $loanIds->count() . ' loans from DB to send to Python API.'
            ]));

            if ($loanIds->isNotEmpty()) {
                $this->callCoLendingRPSApiBulk($loanIds->toArray(), $user_id);
            } else {
                (new \App\Logging\CloudWatchLoggerFactory())->logger(json_encode([
                    'process' => 'Prepare payload',
                    'status' => 'Warning',
                    'message' => 'No valid loans found to send to Python API.'
                ]));
            }

            return response()->json([
                'success' => true,
                'message' => "RPS file processed successfully. {$import->inserted} records saved.",
            ]);

        } catch (\Exception $e) {
            
            $cwFactory = new CloudWatchLoggerFactory();
            $_data = [
                'process' => 'Co Lending rps file could not be processed',
                'status' => 'Exception',
                'file_name' => $e->getFile(),
                'line' => $e->getLine(),
                'message' => $e->getMessage()
            ];
            $cwFactory->logger(json_encode($_data));

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


    public function paymentImport(Request $request)
    {
        try {
            if ($request->has('file') && $request->files->get('file') != null) {
                if (filesize($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_payment.' . $request->file('file')->getClientOriginalExtension();

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

            $data = $request->all();
            $data['file_path'] = $file_path;
            $data['file_name'] = $name;
            $data['user_id'] = Auth::user()->id ?? 107;

            $jobs = new CampaignsJobProvider();
            $jobs->from = 'co_lending_system_payment_upload';
            $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', 'Payment 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 startCoLendingPaymentFileProcess(Request $request)
    {
        $s3Path    = $request->get('file_path');
        $fileName  = $request->get('file_name');
        $user_id  = $request->get('user_id');

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

            $fileContents = Storage::disk('s3')->get($s3Path);

            $client = new Client();
            $response = $client->request('POST', _env('PY_SCRIPT_SERVER_HOST').'/api/co_lending_regular_allocation_bulk', [
                'timeout'   => 120,
                'multipart' => [
                    [
                        'name'     => 'file',
                        'contents' => $fileContents,
                        'filename' => $fileName,
                    ],
                    [
                        'name'     => 'user_id',
                        'contents' => $user_id,
                    ],
                ],
            ]);

            if ($response->getStatusCode() < 200 || $response->getStatusCode() >= 300) {
                throw new \Exception('API returned status ' . $response->getStatusCode());
            }

            Storage::disk('s3')->delete($s3Path);
        } catch (RequestException $e) {
            $body = $e->hasResponse() ? (string) $e->getResponse()->getBody() : $e->getMessage();
            (new Slack(new Request([
                'title'  => 'Co Lending payment file API call failed',
                'fields' => ['message' => $body, 'file' => $fileName],
                'group'  => Slack::$STAGE_TESTING,
            ])))->send();
        } catch (\Exception $e) {
            (new Slack(new Request([
                'title'  => 'Co Lendign payment file could not be processed',
                'fields' => [
                    'message' => $e->getMessage(),
                    'line'    => $e->getLine(),
                    'file'    => $e->getFile(),
                ],
                'group'  => Slack::$STAGE_TESTING,
            ])))->send();
        }
    }


    /**
     * Load RPS schedule for a single loan from the co_lending_emi_payments table.
     * Called by the loan-detail blade when the user views RPS — no external API call.
     */
    public function processCoLendingRPSApi(Request $request)
    {
        $loanId = $request->get('loan_id');

        try {
            if (!$loanId) {
                throw new \Exception('loan_id is required');
            }

            $rows = DB::table('co_lending_emi_payments')
                ->where('loan_id', $loanId)
                ->whereNull('deleted_at')
                ->orderBy('rps_type')
                ->orderBy('schedule_percent', 'desc')
                ->orderBy('installment_number')
                ->get();

            // Build schedules dynamically keyed as "{rps_type}_{schedule_percent}"
            // e.g. orig_100, orig_80, orig_20, hurdle_80, orig_70, orig_30 — whatever is in the DB
            $schedules = [];

            foreach ($rows as $row) {
                $scheduleKey = $row->rps_type . '_' . $row->schedule_percent;

                $schedules[$scheduleKey][] = [
                    'rps_type'           => $row->rps_type,
                    'schedule_percent'   => $row->schedule_percent,
                    'installment_number' => $row->installment_number,
                    'due_date'           => $row->due_date,
                    'days_diff'          => $row->days_diff,
                    'bpi_due'            => $row->bpi_due,
                    'emi_amount_due'     => $row->emi_amount_due,
                    'interest_due'       => $row->interest_due,
                    'principal_due'      => $row->principal_due,
                    'closing_pos'        => $row->closing_pos,
                    'payment_status'     => $row->payment_status,
                ];
            }

            return response()->json([
                'success'  => true,
                'message'  => 'RPS loaded from database.',
                'response' => [
                    'data' => [[
                        'loan_id'   => $loanId,
                        'schedules' => $schedules,
                    ]],
                ],
            ]);

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

    /**
     * Send a single bulk POST to the external Python RPS API with all loan data.
     * Called internally by startCoLendingRPSFileProcess only — never from the blade.
     *
     * @param array $loans  Array of loan attribute arrays
     */
    private function callCoLendingRPSApiBulk(array $loans, $user_id)
    {
        try {
            $payload = array_map(function ($loan) {
                $loan = (array) $loan;
                return [
                    'loan_id'           => $loan['loan_id'],
                    'loan_amount'       => $loan['loan_amount'],
                    'interest_rate'     => $loan['interest_rate'],
                    'tenure'            => $loan['tenure'],
                    'disbursement_date' => $loan['disbursement_date'],
                    'bounce_type'       => $loan['bounce_type'],
                    'product_id'        => $loan['product_id'],
                ];
            }, $loans);
            
            (new \App\Logging\CloudWatchLoggerFactory())->logger(json_encode([
                'process' => 'Send RPS Bulk API',
                'status' => 'Info',
                'message' => 'Sending payload to /api/co_lending_rps_bulk with ' . count($payload) . ' loans'
            ]));

            $client   = new Client();
            $response = $client->request('POST', _env('PY_SCRIPT_SERVER_HOST') . '/api/co_lending_rps_bulk', [
                'timeout'   => 300,
                'multipart' => [
                    [
                        'name'     => 'file',
                        'contents' => '',
                    ],
                    [
                        'name'     => 'json_data',
                        'contents' => json_encode($payload),
                    ],
                    [
                        'name'     => 'user_id',
                        'contents' => $user_id,
                    ],
                ],
            ]);

            if ($response->getStatusCode() < 200 || $response->getStatusCode() >= 300) {
                throw new \Exception('Bulk API returned status ' . $response->getStatusCode());
            }
            (new \App\Logging\CloudWatchLoggerFactory())->logger(json_encode([
                'process' => 'RPS Bulk API Response',
                'status' => 'Success',
                'http_status' => $response->getStatusCode(),
                'body' => (string) $response->getBody()
            ]));

        } catch (RequestException $e) {
            (new \App\Logging\CloudWatchLoggerFactory())->logger(json_encode([
                'process' => 'RPS Bulk API RequestException',
                'status' => 'Error',
                'message' => $e->getMessage()
            ]));
            $body = $e->hasResponse() ? (string) $e->getResponse()->getBody() : $e->getMessage();
            (new Slack(new Request([
                'title'  => 'Co Lending Bulk RPS API call failed',
                'fields' => [
                    'message'    => $body,
                    'loan_count' => count($loans),
                ],
                'group'  => Slack::$STAGE_TESTING,
            ])))->send();

        } catch (\Exception $e) {
            (new \App\Logging\CloudWatchLoggerFactory())->logger(json_encode([
                'process' => 'RPS Bulk API Exception',
                'status' => 'Error',
                'message' => $e->getMessage(),
                'line' => $e->getLine()
            ]));
            (new Slack(new Request([
                'title'  => 'Co Lending Bulk RPS API error',
                'fields' => [
                    'message'    => $e->getMessage(),
                    'line'       => $e->getLine(),
                    'file'       => $e->getFile(),
                    'loan_count' => count($loans),
                ],
                'group'  => Slack::$STAGE_TESTING,
            ])))->send();
        }
    }

    // ─── Shared ──────────────────────────────────────────────────────────────

    /**
     * Call Python API to synchronize bounce and penal metrics
     */
    public function refreshMetricsApi(Request $request)
    {
        $loanId = $request->get('loan_id');
        try {
            if (!$loanId) {
                throw new \Exception('loan_id is required');
            }
            $client = new Client();
            $response = $client->request('POST', _env('PY_SCRIPT_SERVER_HOST').'/api/co_lending_refresh_metrics', [
                'timeout' => 60,
                'json' => [
                    'loan_ids' => [(string)$loanId]
                ]
            ]);

            if ($response->getStatusCode() < 200 || $response->getStatusCode() >= 300) {
                throw new \Exception('API returned status ' . $response->getStatusCode());
            }

            return response()->json([
                'success' => true,
                'message' => 'Metrics refreshed successfully'
            ]);

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

    /**
     * Load RPS schedule for a single loan using the new raw MySQL query with all metrics
     */
    public function processCoLendingRPSApiV2(Request $request)
    {
        $loanId = $request->get('loan_id');

        try {
            if (!$loanId) {
                throw new \Exception('loan_id is required');
            }

            $sql = file_get_contents(base_path('database/queries/co_lending_loan_schedule_query.sql'));
            
            // Execute the raw query with parameter binding
            $rows = DB::select($sql, [$loanId, $loanId]);

            $metricsSql = file_get_contents(base_path('database/queries/co_lending_loan_metrics_query.sql'));
            $metricsRows = DB::select($metricsSql, [$loanId, $loanId]);
            $metricsByRps = [];
            foreach ($metricsRows as $mrow) {
                $metricsByRps[$mrow->rps_type] = $mrow;
            }

            $schedules = [];

            foreach ($rows as $row) {
                // Ensure rps_type is string
                $rpsType = $row->rps_type;
                if (!isset($schedules[$rpsType])) {
                    $schedules[$rpsType] = [
                        'metrics' => isset($metricsByRps[$rpsType]) ? $metricsByRps[$rpsType] : null,
                        'rows' => []
                    ];
                }

                $schedules[$rpsType]['rows'][] = [
                    'rps_type'           => $row->rps_type,
                    'installment_number' => $row->installment_number,
                    'emi_date'           => $row->emi_date,
                    'opening_pos'        => $row->opening_pos,
                    'closing_pos'        => $row->closing_pos,
                    'emi_amount_due'     => $row->emi_amount_due,
                    'interest_due'       => $row->interest_due,
                    'principal_due'      => $row->principal_due,
                    'bounce_charges'     => $row->bounce_charges,
                    'delay_penalty'      => $row->delay_penalty,
                    'charges_waived'     => $row->charges_waived,
                    'interest_received'  => $row->interest_received,
                    'principal_received' => $row->principal_received,
                    'bounce_received'    => $row->bounce_received,
                    'penal_received'     => $row->penal_received,
                    'total_amount_paid'  => $row->total_amount_paid,
                    'amount_due'         => $row->amount_due,
                    'payment_details'    => $row->payment_details,
                ];
            }

            return response()->json([
                'success'  => true,
                'message'  => 'RPS loaded from database.',
                'response' => [
                    'data' => [[
                        'loan_id'   => $loanId,
                        'schedules' => $schedules,
                    ]],
                ],
            ]);

        } catch (\Exception $e) {
            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'];
    }
}