<?php

namespace App\Imports;

use App\CoLendingLoan;
use Carbon\Carbon;
use Illuminate\Support\Collection;
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: first_emi_date
     *   6: product_id
     */
    public function collection(Collection $collection)
    {
        $data = $collection->toArray();

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

        $payload        = [];
        $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]),
                'first_emi_date'    => $this->date($item[5]),
                'product_id'        => $this->str($item[6]),
                'file_name'         => $this->fileName,
                'file_path'         => $this->filePath,
                'created_at'        => $now,
                'updated_at'        => $now,
            ];

            if ($loanId) {
                $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)) {
                CoLendingLoan::insert(array_values($newRows));
                $this->inserted += count($newRows);
            }
        }

        // 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;
    }
}