<?php

namespace App\Imports;

use App\Models\EmoRecord;
use Illuminate\Support\Str;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class EmoImport implements ToModel, WithHeadingRow
{
    protected int $uploadId;
    protected int $seq;

    public function __construct(int $uploadId, int $startingSeq = 1)
    {
        $this->uploadId = $uploadId;
        $this->seq = $startingSeq;
    }

    public function model(array $row)
    {
        // helper to safely fetch headers (case-insensitive)
        $g = fn($k) => $row[$k] 
            ?? $row[Str::slug($k, '_')] 
            ?? $row[Str::lower($k)] 
            ?? null;

        $booking   = $this->toDate($g('Booking Date'));
        $delivered = $this->toDate($g('Delivered Date'));

        return new EmoRecord([
            'upload_id'         => $this->uploadId,
            'serial_no'         => (string) $g('Serial No'),
            'product_code'      => (string) $g('Product Code'),
            'emo_number'        => (string) $g('EMO Number'),
            'emo_amount'        => is_numeric($g('EMO Amount')) ? (float)$g('EMO Amount') : null,
            'emo_message'       => (string) ($g('EMO Message') ?? ''),
            'booking_date'      => $booking,
            'delivered_date'    => $delivered,
            'sender_name'       => (string) $g('Sender Name'),
            'sender_mobile'     => (string) $g('Sender Mobile Number'),
            'sender_pincode'    => (string) $g('Sender Pincode'),
            'sender_city'       => (string) $g('Sender City'),
            'sender_address1'   => (string) $g('Sender Address 1'),
            'sender_address2'   => (string) $g('Sender Address 2'),
            'sender_address3'   => (string) $g('Sender Address 3'),
            'receipt_no'        => 'EMO-' . str_pad($this->seq++, 5, '0', STR_PAD_LEFT),
        ]);
    }

    /**
     * Convert various date formats and Excel serials.
     */
    protected function toDate($value)
    {
        if ($value === null || $value === '') return null;

        if (is_numeric($value)) {
            try {
                return \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value);
            } catch (\Throwable $e) { return null; }
        }

        $formats = ['Y-m-d', 'd/m/Y', 'm/d/Y', 'd-m-Y', 'm-d-Y'];
        foreach ($formats as $fmt) {
            $dt = \DateTime::createFromFormat($fmt, (string)$value);
            if ($dt) return $dt;
        }

        $ts = strtotime((string)$value);
        return $ts ? (new \DateTime())->setTimestamp($ts) : null;
    }
}
