<?php

namespace App\Imports;

use App\DaSystemData;
use Illuminate\Support\Facades\Auth;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\ToCollection;
use PhpOffice\PhpSpreadsheet\Shared\Date as ExcelDate;

class DaSystemDataImport implements ToCollection
{
    use Importable;

    private $request;
    private $headerSkipped = false;

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

    public function collection(Collection $collection)
    {
        $data = $collection->toArray();

        if (!$this->headerSkipped) {
            unset($data[0]);
            $this->headerSkipped = true;
        }

        $payload = [];
        $date = Carbon::now()->toDateTimeString();
        $filePath = $this->request->get('file_path');

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

            $payload[] = [
                'company_id'                      => $this->str($item[0]),
                'cust_id'                         => $this->str($item[1]),
                'loan_no'                         => $this->str($item[2]),
                'pool'                            => $this->str($item[3]),
                'member_name'                     => $this->str($item[4]),
                'date_of_birth'                   => $this->date($item[5]),
                'age'                             => $this->str($item[6]),
                'mobile_number'                   => $this->str($item[7]),
                'nominee_name'                    => $this->str($item[8]),
                'nominee_dob'                     => $this->date($item[9]),
                'nominee_age'                     => $this->str($item[10]),
                'nominee_relation'                => $this->str($item[11]),
                'spouse_name'                     => $this->str($item[12]),
                'father_name'                     => $this->str($item[13]),
                'caste'                           => $this->str($item[14]),
                'religion'                        => $this->str($item[15]),
                'disbursement_date'               => $this->date($item[16]),
                'rural_urban'                     => $this->str($item[17]),
                'purpose_id'                      => $this->str($item[18]),
                'sub_purpose'                     => $this->str($item[19]),
                'status'                          => $this->str($item[20]),
                'closure_remark'                  => $this->str($item[21]),
                'aadhaar_id'                      => $this->str($item[22]),
                'loan_created_on'                 => $this->date($item[23]),
                'address'                         => $this->str($item[24]),
                'village_name'                    => $this->str($item[25]),
                'district'                        => $this->str($item[26]),
                'state'                           => $this->str($item[27]),
                'pincode'                         => $this->str($item[28]),
                'funder_id'                       => $this->str($item[29]),
                'fund_allocation_date'            => $this->date($item[30]),
                'lender_id'                       => $this->str($item[31]),
                'branch_name'                     => $this->str($item[32]),
                'branch_state'                    => $this->str($item[33]),
                'region'                          => $this->str($item[34]),
                'lo_name'                         => $this->str($item[35]),
                'bank_account'                    => $this->str($item[36]),
                'bank_ifsc_code'                  => $this->str($item[37]),
                'disburse_by_fo'                  => $this->str($item[38]),
                'empcode'                         => $this->str($item[39]),
                'principal_arrear'                => $this->str($item[40]),
                'interest_arrear'                 => $this->str($item[41]),
                'total_arrear'                    => $this->str($item[42]),
                'prin_collected'                  => $this->str($item[43]),
                'int_collected'                   => $this->str($item[44]),
                'first_due_amount'                => $this->str($item[45]),
                'first_demand_date'               => $this->date($item[46]),
                'is_qualify'                      => $this->str($item[47]),
                'last_maturity_date'              => $this->date($item[48]),
                'remaining_maturity'              => $this->str($item[49]),
                'last_coll_date'                  => $this->date($item[50]),
                'last_coll_amount'                => $this->str($item[51]),
                'od_days'                         => $this->str($item[52]),
                'od_bucket'                       => $this->str($item[53]),
                'product_category_id'             => $this->str($item[54]),
                'product_id'                      => $this->str($item[55]),
                'principal_total'                 => $this->str($item[56]),
                'int_rate'                        => $this->str($item[57]),
                'processing_fee'                  => $this->str($item[58]),
                'tenure'                          => $this->str($item[59]),
                'total_interest'                  => $this->str($item[60]),
                'total_instal'                    => $this->str($item[61]),
                'instal_os'                       => $this->str($item[62]),
                'insurance_id'                    => $this->str($item[63]),
                'fo_id'                           => $this->str($item[64]),
                'payment_ref_number'              => $this->str($item[65]),
                'cycle'                           => $this->str($item[66]),
                'repayment_frequency'             => $this->str($item[67]),
                'next_demand_date'                => $this->date($item[68]),
                'loan_closure_date'               => $this->date($item[69]),
                'insurance_charges'               => $this->str($item[70]),
                'prod_classification'             => $this->str($item[71]),
                'repayment_mode'                  => $this->str($item[72]),
                'repayment_bank'                  => $this->str($item[73]),
                'insurance_maturity_date'         => $this->date($item[74]),
                'outstanding_principal'           => $this->str($item[75]),
                'outstanding_interest'            => $this->str($item[76]),
                'netoff_amount'                   => $this->str($item[77]),
                'document_charges'                => $this->str($item[78]),
                'stampduty_charges'               => $this->str($item[79]),
                'legal_fee'                       => $this->str($item[80]),
                'application_fee'                 => $this->str($item[81]),
                'pre_emi_amt'                     => $this->str($item[82]),
                'fee_pay_mode'                    => $this->str($item[83]),
                'npa_date'                        => $this->date($item[84]),
                'excess_amount'                   => $this->str($item[85]),
                'waiveoff_amount'                 => $this->str($item[86]),
                'od_interest_collected_till_date' => $this->str($item[87]),
                'loan_charges'                    => $this->str($item[88]),
                'write_off_amount'                => $this->str($item[89]),
                'city'                            => $this->str($item[90]),
                'customer_type'                   => $this->str($item[91]),
                'w_off_settlement'                => $this->str($item[92]),
                'disbursement_amount'             => $this->str($item[93]),
                'disb_bank_name'                  => $this->str($item[94]),
                'advance_emi'                     => $this->str($item[95]),
                'mode_of_repayment'               => $this->str($item[96]),
                'inst_amt_rs'                     => $this->str($item[97]),
                'over_due_inst'                   => $this->str($item[98]),
                'over_due_amount_rs'              => $this->str($item[99]),
                'foir_percent'                    => $this->str($item[100]),
                'credit_score'                    => $this->str($item[101]),
                'aadhar_number'                   => $this->str($item[102]),
                'pan_card_number'                 => $this->str($item[103]),
                'peak_dpd'                        => $this->str($item[104]),
                'dpd_string'                      => $this->str($item[105]),
                'monthly_income'                  => $this->str($item[106]),
                'business_vintage'                => $this->str($item[107]),
                'residence_vintage'               => $this->str($item[108]),
                'business_premises'               => $this->str($item[109]),
                'co_borrower_name'                => $this->str($item[110]),
                'co_borrower_contact_no'          => $this->str($item[111]),
                'gender'                          => $this->str($item[112]),
                'partner_id'                      => $this->str($item[113]),
                'uploaded_file'                   => $filePath,
                'created_by'                      => Auth::user()->id ?? null,
                'created_at'                      => $date,
                'updated_at'                      => $date,
            ];
        }

        if ($payload) {
            foreach (array_chunk($payload, 500) as $chunk) {
                DaSystemData::insert($chunk);
            }
        }
    }

    // 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 readable date string.
    // Handles: Excel numeric serials, PHP DateTime objects, and apostrophe-prefixed strings.
    private function date($value)
    {
        if ($value === null) return null;

        // PhpSpreadsheet may return a DateTime object for date-formatted cells
        if ($value instanceof \DateTimeInterface) {
            return Carbon::instance($value)->format('d-m-Y');
        }

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

        // Excel date serial: any number in a plausible date range (>30000 ≈ after 1982)
        if (is_numeric($cleaned) && (float) $cleaned > 30000) {
            try {
                return Carbon::instance(ExcelDate::excelToDateTimeObject((float) $cleaned))->format('d-m-Y');
            } catch (\Exception $e) {
                // fall through to return as-is
            }
        }

        return $cleaned;
    }
}