<?php

namespace App\Imports;

use App\CoLendingLoan;
use Carbon\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\ToCollection;
use PhpOffice\PhpSpreadsheet\Shared\Date as ExcelDate;

class CoLendingRPSImport implements ToCollection
{
    use Importable;

    private $fileName;
    private $filePath;
    public  $inserted  = 0;
    private $loanIds   = [];

    public function __construct(string $fileName, string $filePath)
    {
        $this->fileName = $fileName;
        $this->filePath = $filePath;
    }

    /**
     * Expected Excel columns (0-indexed):
     *   0: loan_id
     *   1: loan_amount
     *   2: interest_rate
     *   3: tenure
     *   4: disbursement_date
     *   5: bounce_type
     *   6: product_id
     */
    public function collection(Collection $collection)
    {
        $data = $collection->toArray();

        // Skip header row
        unset($data[0]);

        $payload        = [];
        $additionalRows = [];
        $processedIds   = [];
        $now            = Carbon::now()->toDateTimeString();

        foreach ($data as $item) {
            if (empty(array_filter($item))) {
                continue;
            }

            $loanId = $this->str($item[0]);

            $payload[] = [
                'loan_id'           => $loanId,
                'loan_amount'       => $this->str($item[1]),
                'interest_rate'     => $this->str($item[2]),
                'tenure'            => $this->str($item[3]),
                'disbursement_date' => $this->date($item[4]),
                'product_id'        => $this->str($item[6]),
                'file_name'         => $this->fileName,
                'file_path'         => $this->filePath,
                'created_at'        => $now,
                'updated_at'        => $now,
            ];

            if ($loanId) {
                $additionalRows[] = [
                    'loan_id'     => $loanId,
                    'bounce_type' => strtolower($this->str($item[5])),
                ];
                $processedIds[] = $loanId;
            }
        }

        if (!empty($payload)) {
            // Collect all loan_ids in this batch and find which already exist
            $incomingIds  = array_column($payload, 'loan_id');
            $existingIds  = CoLendingLoan::whereIn('loan_id', $incomingIds)
                ->pluck('loan_id')
                ->toArray();

            // Only keep rows whose loan_id is not already in the table
            $newRows = array_filter($payload, function ($row) use ($existingIds) {
                return !in_array($row['loan_id'], $existingIds);
            });

            if (!empty($newRows)) {
                try {
                    CoLendingLoan::insert(array_values($newRows));
                    $this->inserted += count($newRows);
                    (new \App\Logging\CloudWatchLoggerFactory())->logger(json_encode([
                        'process' => 'Insert into co_lending_loans',
                        'status' => 'Info',
                        'message' => 'Successfully inserted ' . count($newRows) . ' into co_lending_loans'
                    ]));
                } catch (\Exception $e) {
                    (new \App\Logging\CloudWatchLoggerFactory())->logger(json_encode([
                        'process' => 'Insert into co_lending_loans',
                        'status' => 'Exception',
                        'message' => $e->getMessage()
                    ]));
                    throw $e;
                }
            }
        }

        foreach ($additionalRows as $row) {
            try {
                $exists = DB::table('co_lending_additional')->where('loan_id', $row['loan_id'])->first();
                if ($exists) {
                    DB::table('co_lending_additional')
                        ->where('loan_id', $row['loan_id'])
                        ->update([
                            'bounce_type' => $row['bounce_type'], 
                            'updated_at' => $now,
                            'deleted_at' => null
                        ]);
                } else {
                    DB::table('co_lending_additional')->insert([
                        'loan_id' => $row['loan_id'],
                        'bounce_type' => $row['bounce_type'],
                        'created_at' => $now,
                        'updated_at' => $now
                    ]);
                }
            } catch (\Exception $e) {
                (new \App\Logging\CloudWatchLoggerFactory())->logger(json_encode([
                    'process' => 'Insert/Update co_lending_additional',
                    'status' => 'Exception',
                    'loan_id' => $row['loan_id'],
                    'message' => $e->getMessage()
                ]));
                throw $e;
            }
        }

        (new \App\Logging\CloudWatchLoggerFactory())->logger(json_encode([
            'process' => 'Insert/Update co_lending_additional',
            'status' => 'Info',
            'message' => 'Successfully processed ' . count($additionalRows) . ' rows in co_lending_additional'
        ]));

        // Track all loan IDs seen in this file (including duplicates that were skipped)
        $this->loanIds = array_unique(array_merge($this->loanIds, $processedIds));
    }

    /**
     * Return all loan IDs parsed from the uploaded file.
     */
    public function getLoanIds(): array
    {
        return $this->loanIds;
    }

    // Strip leading apostrophe (Excel text-prefix escape) and trim whitespace
    private function str($value)
    {
        if ($value === null) return null;
        $s = ltrim(trim((string) $value), "'");
        return $s === '' ? null : $s;
    }

    // Convert value to a Y-m-d date string.
    // Handles: Excel numeric serials, PHP DateTime objects, and apostrophe-prefixed strings.
    private function date($value)
    {
        if ($value === null) return null;

        if ($value instanceof \DateTimeInterface) {
            return Carbon::instance($value)->format('Y-m-d');
        }

        $cleaned = ltrim(trim((string) $value), "'");
        if ($cleaned === '') return null;

        if (is_numeric($cleaned) && (float) $cleaned > 30000) {
            try {
                return Carbon::instance(ExcelDate::excelToDateTimeObject((float) $cleaned))->format('Y-m-d');
            } catch (\Exception $e) {
                // fall through
            }
        }

        return $cleaned;
    }
}