<?php

namespace App\Imports;

use App\Logging\BlacksoilBreLogger;
use Carbon\Carbon;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use PhpOffice\PhpSpreadsheet\Shared\Date as ExcelDate;

class BlacksoilBreDecisionImport implements ToCollection, WithHeadingRow
{
    use Importable;

    public $rows;
    private $batchId;

    public function __construct($batchId = null)
    {
        $this->rows    = collect();
        $this->batchId = $batchId;
    }

    public function collection(Collection $collection)
    {
        $seen    = 0;
        $kept    = 0;
        $skipped = 0;

        foreach ($collection as $item) {
            $seen++;
            $row = $item->toArray();

            if (empty(array_filter($row, function ($v) { return $v !== null && $v !== ''; }))) {
                $skipped++;
                continue;
            }

            $clean = [];
            foreach ($row as $key => $value) {
                $clean[$key] = $this->sanitize($key, $value);
            }
            $this->rows->push($clean);
            $kept++;
        }

        BlacksoilBreLogger::event('import.sheet_parsed', [
            'batch_id'      => $this->batchId,
            'rows_seen'     => $seen,
            'rows_kept'     => $kept,
            'rows_skipped'  => $skipped,
        ]);
    }

    private function sanitize($key, $value)
    {
        if ($value === null) {
            return null;
        }

        $lowerKey  = strtolower((string) $key);
        $isDateKey = strpos($lowerKey, 'date') !== false || strpos($lowerKey, 'dob') !== false;

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

        $stringified = (string) $value;
        // Unicode-aware whitespace strip (handles U+00A0, U+200B, U+FEFF — which trim() misses)
        $stringified = preg_replace('/^[\s\x{00A0}\x{200B}\x{FEFF}]+|[\s\x{00A0}\x{200B}\x{FEFF}]+$/u', '', $stringified);
        $stringified = ltrim($stringified, "'");
        if ($stringified === '') {
            return null;
        }

        // Structured identifiers: strip every non-ASCII-alphanumeric so look-alike unicode
        // chars and stray invisible bytes can't break downstream regex validation.
        if ($lowerKey === 'pan_number' || preg_match('/_pan_number(_\d+)?$/', $lowerKey)) {
            return strtoupper(preg_replace('/[^A-Za-z0-9]/', '', $stringified));
        }
        if ($lowerKey === 'mobile_number' || preg_match('/_mobile_number(_\d+)?$/', $lowerKey)) {
            return preg_replace('/\D/', '', $stringified);
        }
        if ($lowerKey === 'aadhaar_number' || preg_match('/_aadhaar_number(_\d+)?$/', $lowerKey)) {
            return preg_replace('/\D/', '', $stringified);
        }

        if ($isDateKey) {
            // Excel serial — anything > 60 lands in 1900+, threshold 1000 is safely > Jan 1902
            if (is_numeric($stringified) && (float) $stringified > 1000) {
                try {
                    return Carbon::instance(ExcelDate::excelToDateTimeObject((float) $stringified))->format('Y-m-d');
                } catch (\Exception $e) {
                    BlacksoilBreLogger::warning('import.date_serial_failed', [
                        'batch_id' => $this->batchId,
                        'key'      => $key,
                        'value'    => $stringified,
                        'error'    => $e->getMessage(),
                    ]);
                    // fall through to text parse
                }
            }
            // Textual date — try Carbon::parse, return null if it can't be understood
            try {
                return Carbon::parse($stringified)->format('Y-m-d');
            } catch (\Exception $e) {
                BlacksoilBreLogger::warning('import.date_parse_failed', [
                    'batch_id' => $this->batchId,
                    'key'      => $key,
                    'value'    => $stringified,
                ]);
                return null;
            }
        }

        // Strip trailing degree symbol on lat/lon (U+00B0, multi-byte; trim() won't catch it)
        if (strpos($lowerKey, 'latitude') !== false || strpos($lowerKey, 'longitude') !== false) {
            $stringified = rtrim($stringified, " \t\n\r\0\x0B°");
            $stringified = preg_replace('/\xC2\xB0$/', '', $stringified);
        }

        return $stringified;
    }
}