<?php

namespace App\Services;

use App\Models\Account;
use App\Models\AccountType;
use App\Models\Activity;
use App\Models\CaseObject;
use App\Models\Client;
use App\Models\ClientCase;
use App\Models\Contract;
use App\Models\ContractType;
use App\Models\Decision;
use App\Models\Email;
use App\Models\Import;
use App\Models\ImportEntity;
use App\Models\ImportEvent;
use App\Models\ImportRow;
use App\Models\Payment;
use App\Models\Person\AddressType;
use App\Models\Person\Person;
use App\Models\Person\PersonAddress;
use App\Models\Person\PersonGroup;
use App\Models\Person\PersonPhone;
use App\Models\Person\PersonType;
use App\Models\Person\PhoneType;
use Exception;
use Illuminate\Contracts\Auth\Authenticatable;
use Illuminate\Database\QueryException;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Storage;

class ImportProcessor
{
    /**
     * Track contracts that already existed and were matched during history imports.
     *
     * @var array<int,bool>
     */
    private array $historyFoundContractIds = [];

    /**
     * Process an import and apply basic dedup checks.
     * Returns summary counts.
     */
    public function process(Import $import, ?Authenticatable $user = null): array
    {
        $started = now();
        $total = 0;
        $skipped = 0;
        $imported = 0;
        $invalid = 0;
        $fh = null;
        $this->historyFoundContractIds = [];

        // Only CSV/TSV supported in this pass
        if (! in_array($import->source_type, ['csv', 'txt'])) {
            ImportEvent::create([
                'import_id' => $import->id,
                'user_id' => $user?->getAuthIdentifier(),
                'event' => 'processing_skipped',
                'level' => 'warning',
                'message' => 'Only CSV/TXT supported in this pass.',
            ]);
            $import->update(['status' => 'completed', 'finished_at' => now()]);

            return ['ok' => true, 'status' => $import->status, 'counts' => compact('total', 'skipped', 'imported', 'invalid')];
        }

        // Get mappings for this import (with apply_mode)
        $mappings = DB::table('import_mappings')
            ->where('import_id', $import->id)
            ->get(['source_column', 'target_field', 'transform', 'apply_mode', 'options']);

        // Load dynamic entity config
        [$rootAliasMap, $fieldAliasMap, $validRoots, $supportsMultiple] = $this->loadImportEntityConfig();
        // Normalize aliases (plural/legacy roots, field names) before validation
        $mappings = $this->normalizeMappings($mappings, $rootAliasMap, $fieldAliasMap);
        // Validate mapping roots early to avoid silent failures due to typos
        $this->validateMappingRoots($mappings, $validRoots);

        $header = $import->meta['columns'] ?? null;
        // Template meta flags
        $tplMeta = optional($import->template)->meta ?? [];
        $paymentsImport = (bool) ($tplMeta['payments_import'] ?? false);
        $historyImport = (bool) ($tplMeta['history_import'] ?? false);
        $contractKeyMode = $tplMeta['contract_key_mode'] ?? null;
        // Prefer explicitly chosen delimiter, then template meta, else detected
        $delimiter = $import->meta['forced_delimiter']
            ?? optional($import->template)->meta['delimiter']
            ?? $import->meta['detected_delimiter']
            ?? ',';
        $hasHeader = (bool) ($import->meta['has_header'] ?? true);
        $path = Storage::disk($import->disk)->path($import->path);

        // Note: Do not auto-detect or infer mappings/fields beyond what the template mapping provides

        // Parse file and create import_rows with mapped_data
        $fh = @fopen($path, 'r');
        if (! $fh) {
            ImportEvent::create([
                'import_id' => $import->id,
                'user_id' => $user?->getAuthIdentifier(),
                'event' => 'processing_failed',
                'level' => 'error',
                'message' => 'Unable to open file for reading.',
            ]);
            $import->update(['status' => 'failed', 'failed_at' => now()]);

            return ['ok' => false, 'status' => $import->status];
        }
        try {
            DB::beginTransaction();

            ImportEvent::create([
                'import_id' => $import->id,
                'user_id' => $user?->getAuthIdentifier(),
                'event' => 'processing_started',
                'level' => 'info',
                'message' => 'Processing started.',
            ]);
            // Preflight recommendations for payments-import
            if ($paymentsImport) {
                $hasContractRef = $this->mappingIncludes($mappings, 'contract.reference');
                $hasPaymentAmount = $this->mappingIncludes($mappings, 'payment.amount');
                $hasPaymentDate = $this->mappingIncludes($mappings, 'payment.payment_date') || $this->mappingIncludes($mappings, 'payment.paid_at');
                $hasPaymentRef = $this->mappingIncludes($mappings, 'payment.reference');
                $hasPaymentNu = $this->mappingIncludes($mappings, 'payment.payment_nu');
                $missing = [];
                if (! $hasContractRef) {
                    $missing[] = 'contract.reference';
                }
                if (! $hasPaymentAmount) {
                    $missing[] = 'payment.amount';
                }
                if (! $hasPaymentDate) {
                    $missing[] = 'payment.payment_date';
                }
                if (! empty($missing)) {
                    ImportEvent::create([
                        'import_id' => $import->id,
                        'user_id' => $user?->getAuthIdentifier(),
                        'event' => 'payments_mapping_recommendation',
                        'level' => 'warning',
                        'message' => 'Payments import: recommended mappings missing.',
                        'context' => ['missing' => $missing],
                    ]);
                }
                if (! $hasPaymentRef && ! $hasPaymentNu) {
                    ImportEvent::create([
                        'import_id' => $import->id,
                        'user_id' => $user?->getAuthIdentifier(),
                        'event' => 'payments_idempotency_recommendation',
                        'level' => 'warning',
                        'message' => 'For idempotency, map payment.reference (preferred) or payment.payment_nu.',
                    ]);
                }
            }

            $rowNum = 0;
            if ($hasHeader) {
                $first = fgetcsv($fh, 0, $delimiter);
                $rowNum++;
                // Always use the actual header from the file for parsing
                $header = array_map(fn ($v) => $this->sanitizeHeaderName((string) $v), $first ?: []);
                // Heuristic: if header parsed as a single column but contains common delimiters, warn about mismatch
                if (count($header) === 1) {
                    $rawHeader = $first[0] ?? '';
                    if (is_string($rawHeader) && (str_contains($rawHeader, ';') || str_contains($rawHeader, "\t"))) {
                        ImportEvent::create([
                            'import_id' => $import->id,
                            'user_id' => $user?->getAuthIdentifier(),
                            'event' => 'delimiter_mismatch_suspected',
                            'level' => 'warning',
                            'message' => 'Header parsed as a single column. Suspected delimiter mismatch. Set a forced delimiter in the template or import settings.',
                            'context' => [
                                'current_delimiter' => $delimiter,
                                'raw_header' => $rawHeader,
                            ],
                        ]);
                    }
                }
                // Preflight: warn if any mapped source columns are not present in the header (exact match)
                $headerSet = [];
                // Regex validation removed per request; rely on basic length/placeholder checks only
                foreach ($mappings as $map) {
                    $src = (string) ($map->source_column ?? '');
                    if ($src !== '' && ! array_key_exists($src, $headerSet)) {
                        $missingSources[] = $src;
                    }
                }
                if (! empty($missingSources)) {
                    ImportEvent::create([
                        'import_id' => $import->id,
                        'user_id' => $user?->getAuthIdentifier(),
                        'event' => 'source_columns_missing_in_header',
                        'level' => 'warning',
                        'message' => 'Some mapped source columns are not present in the file header (exact match required).',
                        'context' => [
                            'missing' => $missingSources,
                            'header' => $header,
                        ],
                    ]);
                }
            }

            // If mapping contains contract.reference, we require each row to successfully resolve/create a contract
            $requireContract = $this->mappingIncludes($mappings, 'contract.reference');

            $isPg = DB::connection()->getDriverName() === 'pgsql';
            $failedRows = [];
            while (($row = fgetcsv($fh, 0, $delimiter)) !== false) {
                $rowNum++;
                $total++;

                if ($isPg) {
                    // Establish a savepoint so a failing row does not poison the whole transaction
                    try {
                        DB::statement('SAVEPOINT import_row_'.$rowNum);
                    } catch (\Throwable $se) {
                        Log::error('Import savepoint_failed', [
                            'import_id' => $import->id,
                            'row_number' => $rowNum,
                            'exception' => $this->exceptionContext($se),
                        ]);
                        ImportEvent::create([
                            'import_id' => $import->id,
                            'user_id' => $user?->getAuthIdentifier(),
                            'event' => 'savepoint_failed',
                            'level' => 'error',
                            'message' => 'Failed to create savepoint; transaction already aborted.',
                            'context' => [
                                'row_number' => $rowNum,
                                'exception' => $this->exceptionContext($se),
                            ],
                        ]);

                        throw $se; // abort import so root cause surfaces
                    }
                }

                // Scope variables per row so they aren't reused after exception
                $importRow = null;
                try {
                    $rawAssoc = $this->buildRowAssoc($row, $header);
                    // Skip entirely empty rows (all raw values blank/null after trimming) without creating an ImportRow
                    if ($this->rowIsEffectivelyEmpty($rawAssoc)) {
                        $skipped++;
                        if ($isPg) {
                            // No DB changes were made for this row; nothing to roll back explicitly.
                        }

                        continue; // proceed to next CSV row
                    }
                    [$recordType, $mapped] = $this->applyMappings($rawAssoc, $mappings, $supportsMultiple);

                    // Determine row-level reactivation intent: precedence row > import > template
                    $rowReactivate = false;
                    $rawReactivateVal = $rawAssoc['reactivate'] ?? null; // direct column named 'reactivate'
                    if (! is_null($rawReactivateVal)) {
                        $rowReactivate = filter_var($rawReactivateVal, FILTER_VALIDATE_BOOLEAN, FILTER_NULL_ON_FAILURE) ?? false;
                    }
                    $importReactivate = (bool) ($import->reactivate ?? false);
                    $templateReactivate = (bool) (optional($import->template)->reactivate ?? false);
                    $reactivateMode = $rowReactivate || $importReactivate || $templateReactivate;

                    // Do not auto-derive or fallback values; only use explicitly mapped fields

                    $rawSha1 = sha1(json_encode($rawAssoc));
                    $importRow = ImportRow::create([
                        'import_id' => $import->id,
                        'row_number' => $rowNum,
                        'record_type' => $recordType,
                        'raw_data' => $rawAssoc,
                        'mapped_data' => $mapped,
                        'status' => 'valid',
                        'raw_sha1' => $rawSha1,
                    ]);

                    // Contracts
                    $contractResult = null;
                    $reactivatedThisRow = false;
                    if (isset($mapped['contract'])) {
                        // In payments-import with contract_key_mode=reference, treat contract.reference as a keyref only
                        if ($paymentsImport && $contractKeyMode === 'reference') {
                            $ref = $mapped['contract']['reference'] ?? null;
                            if (is_string($ref)) {
                                $ref = preg_replace('/\s+/', '', trim($ref));
                            }
                            if ($ref) {
                                $q = Contract::query()->withTrashed()
                                    ->when($import->client_id, function ($q2, $clientId) {
                                        $q2->join('client_cases', 'contracts.client_case_id', '=', 'client_cases.id')
                                            ->where('client_cases.client_id', $clientId);
                                    })
                                    ->where('contracts.reference', $ref)
                                    ->select('contracts.*');
                                $found = $q->first();
                                if ($found instanceof \App\Models\Contract) {
                                    $contractResult = ['action' => 'resolved', 'contract' => $found];
                                    // Reactivation branch for resolved existing contract
                                    if ($found->active == 0 || $found->deleted_at) {
                                        $reactivationApplied = $this->attemptContractReactivation($found, $user);
                                        if ($reactivationApplied['reactivated']) {
                                            $reactivatedThisRow = true;
                                            $imported++;
                                            $importRow->update([
                                                'status' => 'imported',
                                                'entity_type' => Contract::class,
                                                'entity_id' => $found->id,
                                            ]);
                                            ImportEvent::create([
                                                'import_id' => $import->id,
                                                'user_id' => $user?->getAuthIdentifier(),
                                                'import_row_id' => $importRow->id,
                                                'event' => 'contract_reactivated',
                                                'level' => 'info',
                                                'message' => 'Contract reactivated via import.',
                                                'context' => ['contract_id' => $found->id],
                                            ]);
                                            // Do NOT continue; allow postContractActions + account processing below.
                                        }
                                    }
                                } else {
                                    $contractResult = null; // let requireContract logic flag invalid later
                                }
                            } else {
                                $contractResult = null;
                            }
                        } else {
                            $contractResult = $this->upsertContractChain($import, $mapped, $mappings, $historyImport);
                            // If contract was resolved/updated/inserted and reactivation requested but not needed (already active), we just continue normal flow.
                            if ($contractResult && isset($contractResult['contract']) && $contractResult['contract'] instanceof Contract) {
                                // Do not attempt reactivation on freshly inserted contracts
                                if (($contractResult['action'] ?? null) === 'inserted') {
                                    // Newly created contracts are already active; skip reactivation path
                                } else {
                                    $found = $contractResult['contract'];
                                    if ($found->active == 0 || $found->deleted_at) {
                                        $reactivationApplied = $this->attemptContractReactivation($found, $user);
                                        if ($reactivationApplied['reactivated']) {
                                            $reactivatedThisRow = true;
                                            $importRow->update([
                                                'status' => 'imported',
                                                'entity_type' => Contract::class,
                                                'entity_id' => $found->id,
                                            ]);
                                            ImportEvent::create([
                                                'import_id' => $import->id,
                                                'user_id' => $user?->getAuthIdentifier(),
                                                'import_row_id' => $importRow->id,
                                                'event' => 'contract_reactivated',
                                                'level' => 'info',
                                                'message' => 'Contract reactivated via import (post-upsert).',
                                                'context' => ['contract_id' => $found->id],
                                            ]);
                                            // Do not continue; allow post actions + account handling.
                                        }
                                    }
                                }
                            }
                        }
                        if ($contractResult['action'] === 'skipped_history') {
                            // History import: keep existing contract for downstream relations but do not update or attach segments/actions
                            $skipped++;
                            $importRow->update(['status' => 'skipped']);
                            ImportEvent::create([
                                'import_id' => $import->id,
                                'user_id' => $user?->getAuthIdentifier(),
                                'import_row_id' => $importRow->id,
                                'event' => 'row_skipped',
                                'level' => 'info',
                                'message' => $contractResult['message'] ?? 'Existing contract reused (history import).',
                            ]);
                        } elseif ($contractResult['action'] === 'skipped') {
                            // Even if no contract fields were updated, we may still need to apply template meta
                            // like attaching a segment or creating an activity. Do that if we have the contract.
                            if (isset($contractResult['contract']) && $contractResult['contract'] instanceof Contract) {
                                try {
                                    $this->postContractActions($import, $contractResult['contract']);
                                    ImportEvent::create([
                                        'import_id' => $import->id,
                                        'user_id' => $user?->getAuthIdentifier(),
                                        'import_row_id' => $importRow->id,
                                        'event' => 'post_contract_actions_applied',
                                        'level' => 'info',
                                        'message' => 'Applied template post-actions on existing contract.',
                                        'context' => ['contract_id' => $contractResult['contract']->id],
                                    ]);
                                } catch (\Throwable $e) {
                                    ImportEvent::create([
                                        'import_id' => $import->id,
                                        'user_id' => $user?->getAuthIdentifier(),
                                        'import_row_id' => $importRow->id,
                                        'event' => 'post_contract_action_failed',
                                        'level' => 'warning',
                                        'message' => $e->getMessage(),
                                    ]);
                                }
                            }
                            $skipped++;
                            $importRow->update(['status' => 'skipped']);
                            ImportEvent::create([
                                'import_id' => $import->id,
                                'user_id' => $user?->getAuthIdentifier(),
                                'import_row_id' => $importRow->id,
                                'event' => 'row_skipped',
                                'level' => 'info',
                                'message' => $contractResult['message'] ?? 'Skipped contract (no changes).',
                            ]);
                        } elseif (in_array($contractResult['action'], ['inserted', 'updated'])) {
                            $imported++;
                            $importRow->update([
                                'status' => 'imported',
                                'entity_type' => Contract::class,
                                'entity_id' => $contractResult['contract']->id,
                            ]);
                            $contractFieldsStr = '';
                            if (! empty($contractResult['applied_fields'] ?? [])) {
                                $contractFieldsStr = $this->formatAppliedFieldMessage('contract', $contractResult['applied_fields']);
                            }
                            ImportEvent::create([
                                'import_id' => $import->id,
                                'user_id' => $user?->getAuthIdentifier(),
                                'import_row_id' => $importRow->id,
                                'event' => 'row_imported',
                                'level' => 'info',
                                'message' => ucfirst($contractResult['action']).' contract'.($contractFieldsStr ? ' '.$contractFieldsStr : ''),
                                'context' => ['id' => $contractResult['contract']->id, 'fields' => $contractResult['applied_fields'] ?? []],
                            ]);

                            // Post-contract actions from template/import meta
                            if (! $reactivateMode || $reactivatedThisRow) { // run post actions also for reactivated contracts
                                try {
                                    $this->postContractActions($import, $contractResult['contract']);
                                } catch (\Throwable $e) {
                                    ImportEvent::create([
                                        'import_id' => $import->id,
                                        'user_id' => $user?->getAuthIdentifier(),
                                        'import_row_id' => $importRow->id,
                                        'event' => 'post_contract_action_failed',
                                        'level' => 'warning',
                                        'message' => $e->getMessage(),
                                    ]);
                                }
                            }
                        } else {
                            $invalid++;
                            $importRow->update(['status' => 'invalid', 'errors' => [$contractResult['message'] ?? 'Contract processing failed']]);
                        }
                    }

                    // Enforce hard requirement: if template mapped contract.reference but we didn't resolve/create a contract, mark row invalid and continue
                    if ($requireContract) {
                        $contractEnsured = false;
                        if ($contractResult && isset($contractResult['contract']) && $contractResult['contract'] instanceof Contract) {
                            $contractEnsured = true;
                        }
                        if (! $contractEnsured) {
                            $srcCol = $this->findSourceColumnFor($mappings, 'contract.reference');
                            $rawVal = $srcCol !== null ? ($rawAssoc[$srcCol] ?? null) : null;
                            $extra = $srcCol !== null ? ' Source column: "'.$srcCol.'" value: '.(is_null($rawVal) || $rawVal === '' ? '(empty)' : (is_scalar($rawVal) ? (string) $rawVal : json_encode($rawVal))) : '';
                            $msg = 'Row '.$rowNum.': Contract was required (contract.reference mapped) but not created/resolved. '.($contractResult['message'] ?? '').$extra;

                            // Avoid double-counting invalid if already set by contract processing
                            if ($importRow->status !== 'invalid') {
                                $invalid++;
                                $importRow->update(['status' => 'invalid', 'errors' => [$msg]]);
                            }
                            ImportEvent::create([
                                'import_id' => $import->id,
                                'user_id' => $user?->getAuthIdentifier(),
                                'import_row_id' => $importRow->id,
                                'event' => 'row_invalid',
                                'level' => 'error',
                                'message' => $msg,
                            ]);

                            // Skip further processing for this row
                            continue;
                        }
                    }

                    // Accounts
                    $accountResult = null;
                    if ($historyImport && isset($contractResult['contract']) && $contractResult['contract'] instanceof Contract && ! isset($mapped['account'])) {
                        $autoAcc = $this->ensureHistoryAccount($contractResult['contract'], $mapped);
                        if ($autoAcc) {
                            $accountResult = ['action' => 'inserted', 'account' => $autoAcc, 'contract' => $contractResult['contract'], 'contract_id' => $contractResult['contract']->id];
                        }
                    }
                    if (isset($mapped['account'])) {
                        // If a contract was just created or resolved above, pass its id to account mapping for this row
                        if ($contractResult && isset($contractResult['contract']) && $contractResult['contract'] instanceof Contract) {
                            $mapped['account']['contract_id'] = $contractResult['contract']->id;
                        }
                        $accountResult = $this->upsertAccount($import, $mapped, $mappings, $historyImport);
                        if ($accountResult['action'] === 'skipped') {
                            $skipped++;
                            $importRow->update(['status' => 'skipped']);
                            ImportEvent::create([
                                'import_id' => $import->id,
                                'user_id' => $user?->getAuthIdentifier(),
                                'import_row_id' => $importRow->id,
                                'event' => 'row_skipped',
                                'level' => 'info',
                                'message' => $accountResult['message'] ?? 'Skipped (no changes).',
                                'context' => $accountResult['context'] ?? null,
                            ]);
                        } elseif ($accountResult['action'] === 'inserted' || $accountResult['action'] === 'updated') {
                            $imported++;
                            $importRow->update([
                                'status' => 'imported',
                                'entity_type' => Account::class,
                                'entity_id' => $accountResult['account']->id,
                            ]);
                            $accountFieldsStr = '';
                            if (! empty($accountResult['applied_fields'] ?? [])) {
                                $accountFieldsStr = $this->formatAppliedFieldMessage('account', $accountResult['applied_fields']);
                            }
                            ImportEvent::create([
                                'import_id' => $import->id,
                                'user_id' => $user?->getAuthIdentifier(),
                                'import_row_id' => $importRow->id,
                                'event' => 'row_imported',
                                'level' => 'info',
                                'message' => ucfirst($accountResult['action']).' account'.($accountFieldsStr ? ' '.$accountFieldsStr : ''),
                                'context' => ['id' => $accountResult['account']->id, 'fields' => $accountResult['applied_fields'] ?? []],
                            ]);
                        } else {
                            $invalid++;
                            $importRow->update(['status' => 'invalid', 'errors' => ['Unhandled result']]);
                        }
                    }

                    // Case Objects: create or update case objects associated with contracts
                    // Support both 'case_object' and 'case_objects' keys (template may use plural)
                    if (isset($mapped['case_objects']) || isset($mapped['case_object'])) {
                        // Resolve contract_id from various sources
                        $contractIdForObject = null;

                        // Get the case object data (support both plural and singular)
                        $caseObjectData = $mapped['case_objects'] ?? $mapped['case_object'] ?? [];

                        // First, check if contract_id is directly provided in the mapping
                        if (! empty($caseObjectData['contract_id'])) {
                            $contractIdForObject = $caseObjectData['contract_id'];
                        }
                        // If contract was just created/resolved above, use its id
                        elseif ($contractResult && isset($contractResult['contract']) && $contractResult['contract'] instanceof Contract) {
                            $contractIdForObject = $contractResult['contract']->id;
                        }
                        // If account was processed and has a contract, use that contract
                        elseif ($accountResult && isset($accountResult['contract_id'])) {
                            $contractIdForObject = $accountResult['contract_id'];
                        }

                        if ($contractIdForObject) {
                            $caseObjectResult = $this->upsertCaseObject($import, $mapped, $mappings, $contractIdForObject);
                            if ($caseObjectResult['action'] === 'skipped') {
                                $skipped++;
                                $importRow->update(['status' => 'skipped']);
                                ImportEvent::create([
                                    'import_id' => $import->id,
                                    'user_id' => $user?->getAuthIdentifier(),
                                    'import_row_id' => $importRow->id,
                                    'event' => 'row_skipped',
                                    'level' => 'info',
                                    'message' => $caseObjectResult['message'] ?? 'Skipped (no changes).',
                                    'context' => $caseObjectResult['context'] ?? null,
                                ]);
                            } elseif ($caseObjectResult['action'] === 'inserted' || $caseObjectResult['action'] === 'updated') {
                                $imported++;
                                $importRow->update([
                                    'status' => 'imported',
                                    'entity_type' => CaseObject::class,
                                    'entity_id' => $caseObjectResult['case_object']->id,
                                ]);
                                $objectFieldsStr = '';
                                if (! empty($caseObjectResult['applied_fields'] ?? [])) {
                                    $objectFieldsStr = $this->formatAppliedFieldMessage('case_object', $caseObjectResult['applied_fields']);
                                }
                                ImportEvent::create([
                                    'import_id' => $import->id,
                                    'user_id' => $user?->getAuthIdentifier(),
                                    'import_row_id' => $importRow->id,
                                    'event' => 'row_imported',
                                    'level' => 'info',
                                    'message' => ucfirst($caseObjectResult['action']).' case object'.($objectFieldsStr ? ' '.$objectFieldsStr : ''),
                                    'context' => ['id' => $caseObjectResult['case_object']->id, 'fields' => $caseObjectResult['applied_fields'] ?? []],
                                ]);
                            } else {
                                $invalid++;
                                $importRow->update(['status' => 'invalid', 'errors' => ['Unhandled case object result']]);
                            }
                        } else {
                            $invalid++;
                            $importRow->update(['status' => 'invalid', 'errors' => ['Case object requires a valid contract_id']]);
                            ImportEvent::create([
                                'import_id' => $import->id,
                                'user_id' => $user?->getAuthIdentifier(),
                                'import_row_id' => $importRow->id,
                                'event' => 'row_invalid',
                                'level' => 'error',
                                'message' => 'Case object requires a valid contract_id (not resolved).',
                            ]);
                        }
                    }

                    // Activities: create or update activities linked to contracts/cases
                    if (isset($mapped['activity'])) {
                        $activityResult = $this->upsertActivity($import, $mapped, $mappings, $contractResult ?? null, $accountResult ?? null);
                        if ($activityResult['action'] === 'skipped') {
                            $skipped++;
                            $importRow->update(['status' => 'skipped']);
                            ImportEvent::create([
                                'import_id' => $import->id,
                                'user_id' => $user?->getAuthIdentifier(),
                                'import_row_id' => $importRow->id,
                                'event' => 'row_skipped',
                                'level' => 'info',
                                'message' => $activityResult['message'] ?? 'Skipped (no changes).',
                                'context' => $activityResult['context'] ?? null,
                            ]);
                        } elseif (in_array($activityResult['action'], ['inserted', 'updated'], true)) {
                            $imported++;
                            $importRow->update([
                                'status' => 'imported',
                                'entity_type' => Activity::class,
                                'entity_id' => $activityResult['activity']->id,
                            ]);
                            $activityFieldsStr = '';
                            if (! empty($activityResult['applied_fields'] ?? [])) {
                                $activityFieldsStr = $this->formatAppliedFieldMessage('activity', $activityResult['applied_fields']);
                            }
                            ImportEvent::create([
                                'import_id' => $import->id,
                                'user_id' => $user?->getAuthIdentifier(),
                                'import_row_id' => $importRow->id,
                                'event' => 'row_imported',
                                'level' => 'info',
                                'message' => ucfirst($activityResult['action']).' activity'.($activityFieldsStr ? ' '.$activityFieldsStr : ''),
                                'context' => ['id' => $activityResult['activity']->id, 'fields' => $activityResult['applied_fields'] ?? []],
                            ]);
                        } else {
                            $invalid++;
                            $importRow->update(['status' => 'invalid', 'errors' => [$activityResult['message'] ?? 'Activity processing failed']]);
                            ImportEvent::create([
                                'import_id' => $import->id,
                                'user_id' => $user?->getAuthIdentifier(),
                                'import_row_id' => $importRow->id,
                                'event' => 'row_invalid',
                                'level' => 'error',
                                'message' => $activityResult['message'] ?? 'Activity processing failed',
                            ]);
                        }
                    }

                    // Contacts: resolve person via Contract/Account chain, client_case.client_ref, contacts, or identifiers
                    $personIdForRow = null;
                    // Prefer person from contract created/updated above
                    if ($contractResult && isset($contractResult['contract']) && $contractResult['contract'] instanceof Contract) {
                        $ccId = $contractResult['contract']->client_case_id;
                        $personIdForRow = ClientCase::where('id', $ccId)->value('person_id');
                    }

                    // Payments: when present, require account resolution and create payment
                    if (isset($mapped['payment'])) {
                        // If no account yet, try to resolve via contract + first account
                        $accountIdForPayment = $accountResult['account']->id ?? null;

                        // If payments-import mode with contract_key_mode=reference and we have contract.reference mapped, resolve by reference only
                        $tplMeta = optional($import->template)->meta ?? [];
                        $paymentsImport = (bool) ($tplMeta['payments_import'] ?? false);
                        $contractKeyMode = $tplMeta['contract_key_mode'] ?? null;
                        if (! $accountIdForPayment && $paymentsImport && $contractKeyMode === 'reference') {
                            $contractRef = $mapped['contract']['reference'] ?? null;
                            if (! $contractId) {
                                $contract = \App\Models\Contract::query()
                                    ->when($import->client_id, function ($q, $clientId) {
                                        $q->join('client_cases', 'contracts.client_case_id', '=', 'client_cases.id')
                                            ->where('client_cases.client_id', $clientId);
                                    })
                                    ->where('contracts.reference', $contractRef)
                                    ->select('contracts.id')
                                    ->first();
                            } elseif ($hasContractRoot) {
                                // If mapping for contract.reference is keyref, do NOT create contract – lookup only
                                $refMode = $this->mappingMode($mappings, 'contract.reference');
                                if ($refMode === 'keyref') {
                                    ImportEvent::create([
                                        'import_id' => $import->id,
                                        'user_id' => null,
                                        'event' => 'row_skipped',
                                        'level' => 'warning',
                                        'message' => 'Contract reference '.$contractRef.' does not exist (keyref); row skipped.',
                                    ]);

                                    return [
                                        'action' => 'skipped',
                                        'message' => 'contract.reference keyref lookup failed: not found',
                                    ];
                                }
                                /* Lines 1242-1269 omitted */
                                $contractId = $createdContract->id;
                            }
                        }

                        if (! $accountIdForPayment) {
                            $invalid++;
                            $importRow->update(['status' => 'invalid', 'errors' => ['Payment requires an account. Could not resolve account for payment.']]);
                            ImportEvent::create([
                                'import_id' => $import->id,
                                'user_id' => $user?->getAuthIdentifier(),
                                'import_row_id' => $importRow->id,
                                'event' => 'row_invalid',
                                'level' => 'error',
                                'message' => 'Payment requires an account (not resolved).',
                            ]);

                            continue;
                        }

                        // Build payment payload
                        $p = $mapped['payment'];
                        // Normalize reference and payment number (if provided)
                        $refVal = isset($p['reference']) ? (is_string($p['reference']) ? trim($p['reference']) : $p['reference']) : null;
                        $nuVal = isset($p['payment_nu']) ? (is_string($p['payment_nu']) ? trim($p['payment_nu']) : $p['payment_nu']) : null;
                        $payload = [
                            'account_id' => $accountIdForPayment,
                            'reference' => $p['reference'] ?? null,
                            // Normalize payment date to ISO (Y-m-d) to avoid DB parse errors
                            'paid_at' => \App\Services\DateNormalizer::toDate((string) (($p['payment_date'] ?? ($p['paid_at'] ?? '')))),
                            'currency' => $p['currency'] ?? 'EUR',
                            'created_by' => $user?->getAuthIdentifier(),
                        ];
                        // Attach payment_nu into meta for idempotency if provided
                        $meta = [];
                        if (is_array($p['meta'] ?? null)) {
                            $meta = $p['meta'];
                        }
                        if (! empty($nuVal)) {
                            $meta['payment_nu'] = $nuVal;
                        }
                        if (! empty($meta)) {
                            $payload['meta'] = $meta;
                        }
                        // Amount: accept either amount (preferred) or legacy amount_cents; convert cents -> decimal
                        if (array_key_exists('amount', $p)) {
                            $payload['amount'] = is_string($p['amount']) ? (float) $this->normalizeDecimal($p['amount']) : (float) $p['amount'];
                        } elseif (array_key_exists('amount_cents', $p)) {
                            $payload['amount'] = ((int) $p['amount_cents']) / 100.0;
                        }

                        // Idempotency: skip creating if a payment with same (account_id, reference) already exists
                        if (! empty($refVal)) {
                            $exists = Payment::query()
                                ->where('account_id', $accountIdForPayment)
                                ->where('reference', $refVal)
                                ->exists();
                            if ($exists) {
                                $skipped++;
                                $importRow->update(['status' => 'skipped']);
                                ImportEvent::create([
                                    'import_id' => $import->id,
                                    'user_id' => $user?->getAuthIdentifier(),
                                    'import_row_id' => $importRow->id,
                                    'event' => 'payment_duplicate_skipped',
                                    'level' => 'info',
                                    'message' => 'Skipped duplicate payment (by reference).',
                                    'context' => [
                                        'account_id' => $accountIdForPayment,
                                        'reference' => $refVal,
                                    ],
                                ]);

                                continue;
                            }
                        }

                        $payment = new Payment;
                        $payment->fill($payload);
                        // Save the account balance before applying this payment
                        $accForBal = Account::find($accountIdForPayment);
                        if ($accForBal) {
                            $payment->balance_before = (float) ($accForBal->balance_amount ?? 0);
                        }
                        // If amount not in payload yet but provided, set it directly
                        if (! array_key_exists('amount', $payload) && isset($p['amount'])) {
                            $payment->amount = (float) $this->normalizeDecimal($p['amount']);
                        }
                        try {
                            $payment->save();
                        } catch (\Throwable $e) {
                            // Gracefully skip if unique index on (account_id, reference) is violated due to race conditions
                            if (str_contains($e->getMessage(), 'payments_account_id_reference_unique')) {
                                $skipped++;
                                $importRow->update(['status' => 'skipped']);
                                ImportEvent::create([
                                    'import_id' => $import->id,
                                    'user_id' => $user?->getAuthIdentifier(),
                                    'import_row_id' => $importRow->id,
                                    'event' => 'payment_duplicate_skipped_db',
                                    'level' => 'info',
                                    'message' => 'Skipped duplicate payment due to database unique constraint (account_id, reference).',
                                    'context' => [
                                        'account_id' => $accountIdForPayment,
                                        'reference' => $refVal,
                                    ],
                                ]);

                                continue;
                            }
                            throw $e;
                        }

                        // Option A: create a credit booking so account balance updates via booking events
                        try {
                            if (isset($payment->amount)) {
                                \App\Models\Booking::query()->create([
                                    'account_id' => $accountIdForPayment,
                                    'payment_id' => $payment->id,
                                    'amount_cents' => (int) round(((float) $payment->amount) * 100),
                                    'type' => 'credit',
                                    'description' => $payment->reference ? ('Plačilo '.$payment->reference) : 'Plačilo',
                                    'booked_at' => $payment->paid_at ?? now(),
                                ]);
                            }
                        } catch (\Throwable $e) {
                            ImportEvent::create([
                                'import_id' => $import->id,
                                'user_id' => $user?->getAuthIdentifier(),
                                'import_row_id' => $importRow->id,
                                'event' => 'booking_create_failed',
                                'level' => 'warning',
                                'message' => 'Failed to create booking for payment: '.$e->getMessage(),
                            ]);
                        }

                        // Optionally create an activity entry for this payment
                        try {
                            $settings = \App\Models\PaymentSetting::query()->first();
                            if ($settings && ($settings->create_activity_on_payment ?? false)) {
                                $amountCents = (int) round(((float) $payment->amount) * 100);
                                $note = $settings->activity_note_template ?? 'Prejeto plačilo';
                                $note = str_replace(['{amount}', '{currency}'], [number_format($amountCents / 100, 2, ',', '.'), $payment->currency ?? 'EUR'], $note);

                                // Append balance context (before/after) and mark cause as payment
                                // At this point, booking has been created so the account balance should reflect the new amount
                                $accountAfter = Account::find($accountIdForPayment);
                                $beforeStr = number_format((float) ($payment->balance_before ?? 0), 2, ',', '.').' '.($payment->currency ?? 'EUR');
                                $afterStr = number_format((float) ($accountAfter?->balance_amount ?? 0), 2, ',', '.').' '.($payment->currency ?? 'EUR');
                                $note .= " (Stanje pred: {$beforeStr}, Stanje po: {$afterStr}; Izvor: plačilo)";

                                // Resolve client_case_id via account->contract
                                $accountForActivity = $accForBal ?: Account::find($accountIdForPayment);
                                $accountForActivity?->loadMissing('contract');
                                $contractId = $accountForActivity?->contract_id;
                                $clientCaseId = $accountForActivity?->contract?->client_case_id;

                                if ($clientCaseId) {
                                    $activity = \App\Models\Activity::query()->create([
                                        'due_date' => null,
                                        'amount' => $amountCents / 100,
                                        'note' => $note,
                                        'action_id' => $settings->default_action_id,
                                        'decision_id' => $settings->default_decision_id,
                                        'client_case_id' => $clientCaseId,
                                        'contract_id' => $contractId,
                                    ]);
                                    $payment->update(['activity_id' => $activity->id]);
                                } else {
                                    ImportEvent::create([
                                        'import_id' => $import->id,
                                        'user_id' => $user?->getAuthIdentifier(),
                                        'import_row_id' => $importRow->id,
                                        'event' => 'payment_activity_skipped',
                                        'level' => 'info',
                                        'message' => 'Skipped creating activity for payment due to missing client_case_id on contract.',
                                    ]);
                                }
                            }
                        } catch (\Throwable $e) {
                            ImportEvent::create([
                                'import_id' => $import->id,
                                'user_id' => $user?->getAuthIdentifier(),
                                'import_row_id' => $importRow->id,
                                'event' => 'payment_activity_failed',
                                'level' => 'warning',
                                'message' => 'Failed to create activity for payment: '.$e->getMessage(),
                            ]);
                        }

                        $imported++;
                        $importRow->update([
                            'status' => 'imported',
                            'entity_type' => Payment::class,
                            'entity_id' => $payment->id,
                        ]);
                        $paymentFields = $this->collectPaymentAppliedFields($payload, $payment);
                        $paymentFieldsStr = $this->formatAppliedFieldMessage('payment', $paymentFields);
                        ImportEvent::create([
                            'import_id' => $import->id,
                            'user_id' => $user?->getAuthIdentifier(),
                            'import_row_id' => $importRow->id,
                            'event' => 'row_imported',
                            'level' => 'info',
                            'message' => 'Inserted payment'.($paymentFieldsStr ? ' '.$paymentFieldsStr : ''),
                            'context' => ['id' => $payment->id, 'fields' => $paymentFields],
                        ]);
                    }
                    // If we have a contract reference, resolve existing contract for this client and derive person
                    if (! $personIdForRow && $import->client_id && ! empty($mapped['contract']['reference'] ?? null)) {
                        $existingContract = Contract::query()
                            ->join('client_cases', 'contracts.client_case_id', '=', 'client_cases.id')
                            ->where('client_cases.client_id', $import->client_id)
                            ->where('contracts.reference', $mapped['contract']['reference'])
                            ->select('contracts.client_case_id')
                            ->first();
                        if ($existingContract) {
                            $personIdForRow = ClientCase::where('id', $existingContract->client_case_id)->value('person_id');
                        }
                    }
                    // If account processing created/resolved a contract, derive person via its client_case
                    if (! $personIdForRow && $accountResult) {
                        if (isset($accountResult['contract']) && $accountResult['contract'] instanceof Contract) {
                            $ccId = $accountResult['contract']->client_case_id;
                            $personIdForRow = ClientCase::where('id', $ccId)->value('person_id');
                        } elseif (isset($accountResult['contract_id'])) {
                            $ccId = Contract::where('id', $accountResult['contract_id'])->value('client_case_id');
                            if ($ccId) {
                                $personIdForRow = ClientCase::where('id', $ccId)->value('person_id');
                            }
                        }
                    }
                    // Resolve by client_case.client_ref for this client (prefer reusing existing person)
                    if (! $personIdForRow && $import->client_id && ! empty($mapped['client_case']['client_ref'] ?? null)) {
                        $cc = ClientCase::where('client_id', $import->client_id)
                            ->where('client_ref', $mapped['client_case']['client_ref'])
                            ->first();
                        if ($cc) {
                            $personIdForRow = $cc->person_id ?: null;
                        }
                    }

                    // Resolve by contact values next
                    if (! $personIdForRow) {
                        // consider first values from multi groups if present
                        $emailVal = trim((string) ($this->firstFromMulti($mapped, 'email', 'value') ?? ''));
                        $phoneNu = trim((string) ($this->firstFromMulti($mapped, 'phone', 'nu') ?? ''));
                        $addrLine = trim((string) ($this->firstFromMulti($mapped, 'address', 'address') ?? ''));

                        // Try to resolve by existing contacts first
                        if ($emailVal !== '') {
                            $personIdForRow = Email::where('value', $emailVal)->value('person_id');
                        }
                        if (! $personIdForRow && $phoneNu !== '') {
                            $personIdForRow = PersonPhone::where('nu', $phoneNu)->value('person_id');
                        }
                        if (! $personIdForRow && $addrLine !== '') {
                            $personIdForRow = PersonAddress::where('address', $addrLine)->value('person_id');
                        }

                        // If still no person but we have any contact value, auto-create a minimal person
                        // BUT if we can map to an existing client_case by client_ref, reuse that case and set person there (avoid separate person rows)
                        if (! $personIdForRow && ($emailVal !== '' || $phoneNu !== '' || $addrLine !== '')) {
                            if ($import->client_id && ! empty($mapped['client_case']['client_ref'] ?? null)) {
                                $cc = ClientCase::where('client_id', $import->client_id)
                                    ->where('client_ref', $mapped['client_case']['client_ref'])
                                    ->first();
                                if ($cc) {
                                    $pid = $cc->person_id ?: $this->createMinimalPersonId();
                                    if (! $cc->person_id) {
                                        $cc->person_id = $pid;
                                        $cc->save();
                                    }
                                    $personIdForRow = $pid;
                                }
                            }
                        }
                        if (! $personIdForRow && ($emailVal !== '' || $phoneNu !== '' || $addrLine !== '')) {
                            $personIdForRow = $this->createMinimalPersonId();
                            ImportEvent::create([
                                'import_id' => $import->id,
                                'user_id' => $user?->getAuthIdentifier(),
                                'import_row_id' => $importRow->id ?? null,
                                'event' => 'person_autocreated_for_contacts',
                                'level' => 'info',
                                'message' => 'Created minimal person to attach contact data (email/phone/address).',
                                'context' => [
                                    'email' => $emailVal ?: null,
                                    'phone' => $phoneNu ?: null,
                                    'address' => $addrLine ?: null,
                                    'person_id' => $personIdForRow,
                                ],
                            ]);
                        }
                    }
                    // Try identifiers from mapped person (no creation yet)
                    if (! $personIdForRow && ! empty($mapped['person'] ?? [])) {
                        $personIdForRow = $this->findPersonIdByIdentifiers($mapped['person']);
                    }
                    // Finally, if still unknown and person fields provided, create
                    if (! $personIdForRow && ! empty($mapped['person'] ?? [])) {
                        $personIdForRow = $this->findOrCreatePersonId($mapped['person']);
                    }

                    // At this point, personIdForRow is either resolved or remains null (no contacts/person data)

                    $contactChanged = false;
                    if ($personIdForRow) {
                        // Fan-out for multi-supported roots; backward compatible for single hashes
                        foreach (['email' => 'upsertEmail', 'address' => 'upsertAddress', 'phone' => 'upsertPhone'] as $root => $method) {
                            if (isset($mapped[$root]) && is_array($mapped[$root])) {
                                // If it's a grouped map (supports multiple), iterate groups; else treat as single data hash
                                $data = $mapped[$root];
                                $isGrouped = $this->isGroupedMulti($data);
                                if ($isGrouped) {
                                    // De-duplicate grouped items within the same row by their unique key per root
                                    $keyField = $root === 'email' ? 'value' : ($root === 'phone' ? 'nu' : 'address');
                                    $normalizer = function ($v) use ($root) {
                                        if ($v === null) {
                                            return null;
                                        }
                                        $s = trim((string) $v);
                                        if ($s === '') {
                                            return '';
                                        }
                                        if ($root === 'email') {
                                            return mb_strtolower($s);
                                        }
                                        if ($root === 'phone') {
                                            // Keep leading + and digits only for comparison
                                            $s = preg_replace('/[^0-9+]/', '', $s) ?? $s;
                                            // Collapse multiple + to single leading
                                            $s = ltrim($s, '+');

                                            return '+'.$s;
                                        }
                                        // address: normalize whitespace and lowercase for comparison
                                        $s = preg_replace('/\s+/', ' ', $s) ?? $s;

                                        return mb_strtolower(trim($s));
                                    };
                                    $data = $this->dedupeGroupedItems($data, $keyField, $normalizer);
                                    foreach ($data as $grp => $payload) {
                                        if (empty(array_filter($payload, fn ($v) => ! is_null($v) && trim((string) $v) !== ''))) {
                                            continue; // skip empty group
                                        }
                                        $r = $this->{$method}($personIdForRow, $payload, $mappings);
                                        if (in_array($r['action'] ?? 'skipped', ['inserted', 'updated'])) {
                                            $contactChanged = true;
                                        }
                                    }
                                } else {
                                    if (! empty($data)) {
                                        $r = $this->{$method}($personIdForRow, $data, $mappings);
                                        if (in_array($r['action'] ?? 'skipped', ['inserted', 'updated'])) {
                                            $contactChanged = true;
                                        }
                                    }
                                }
                            }
                        }
                    }

                    if (! isset($mapped['contract']) && ! isset($mapped['account'])) {
                        if ($contactChanged) {
                            $imported++;
                            $importRow->update([
                                'status' => 'imported',
                                'entity_type' => Person::class,
                                'entity_id' => $personIdForRow,
                            ]);
                            ImportEvent::create([
                                'import_id' => $import->id,
                                'user_id' => $user?->getAuthIdentifier(),
                                'import_row_id' => $importRow->id,
                                'event' => 'row_imported',
                                'level' => 'info',
                                'message' => 'Contacts upserted',
                                'context' => ['person_id' => $personIdForRow],
                            ]);
                        } else {
                            $skipped++;
                            $importRow->update(['status' => 'skipped']);
                        }
                    }
                } catch (\Throwable $e) {
                    $rollbackFailed = false;
                    $rollbackError = null;
                    if ($isPg) {
                        // Roll back only this row's work
                        try {
                            DB::statement('ROLLBACK TO SAVEPOINT import_row_'.$rowNum);
                        } catch (\Throwable $ignored) {
                            $rollbackFailed = true;
                            $rollbackError = $ignored;
                        }
                    }
                    if ($rollbackFailed) {
                        Log::error('Import row_rollback_failed', [
                            'import_id' => $import->id,
                            'row_number' => $rowNum,
                            'exception' => $this->exceptionContext($rollbackError ?? $e),
                        ]);
                        // Abort the whole import if we cannot rollback to the row savepoint (transaction is poisoned)
                        ImportEvent::create([
                            'import_id' => $import->id,
                            'user_id' => $user?->getAuthIdentifier(),
                            'event' => 'row_rollback_failed',
                            'level' => 'error',
                            'message' => 'Rollback to savepoint failed; aborting import.',
                            'context' => [
                                'row_number' => $rowNum,
                                'exception' => $this->exceptionContext($rollbackError ?? $e),
                            ],
                        ]);

                        throw $rollbackError ?? $e;
                    }
                    // Ensure importRow exists for logging if failure happened before its creation
                    if (! $importRow) {
                        try {
                            $msg = $this->safeErrorMessage($e->getMessage());
                            $rawPreviewSha1 = isset($rawAssoc) ? sha1(json_encode($rawAssoc)) : null;
                            $importRow = ImportRow::create([
                                'import_id' => $import->id,
                                'row_number' => $rowNum,
                                'record_type' => null,
                                'raw_data' => isset($rawAssoc) ? $rawAssoc : [],
                                'mapped_data' => [],
                                'status' => 'invalid',
                                'errors' => [$msg],
                                'raw_sha1' => $rawPreviewSha1,
                            ]);
                        } catch (\Throwable $inner) {
                            // Last resort: cannot persist row; log only event
                        }
                    } else {
                        // Mark existing row as invalid (avoid double increment if already invalid)
                        if ($importRow->status !== 'invalid') {
                            $importRow->update(['status' => 'invalid', 'errors' => [$this->safeErrorMessage($e->getMessage())]]);
                        }
                    }
                    $failedRows[] = $rowNum;
                    $invalid++;
                    Log::error('Import row_exception', [
                        'import_id' => $import->id,
                        'row_number' => $rowNum,
                        'exception' => $this->exceptionContext($e),
                        'raw_preview' => isset($rawAssoc) ? $this->buildRawDataPreview($rawAssoc) : [],
                    ]);
                    try {
                        ImportEvent::create([
                            'import_id' => $import->id,
                            'user_id' => $user?->getAuthIdentifier(),
                            'import_row_id' => $importRow?->id, // may be null if creation failed
                            'event' => 'row_exception',
                            'level' => 'error',
                            'message' => $this->safeErrorMessage($e->getMessage()),
                            'context' => [
                                'classification' => $this->classifyRowException($e),
                                'driver' => DB::connection()->getDriverName(),
                                'row_number' => $rowNum,
                                'raw_sha1' => isset($rawAssoc) ? sha1(json_encode($rawAssoc)) : null,
                                'raw_data_preview' => isset($rawAssoc) ? $this->buildRawDataPreview($rawAssoc) : [],
                            ],
                        ]);
                    } catch (\Throwable $evtErr) {
                        Log::error('Import row_exception_event_failed', [
                            'import_id' => $import->id,
                            'row_number' => $rowNum,
                            'exception' => $this->exceptionContext($evtErr),
                            'original_exception' => $this->exceptionContext($e),
                        ]);
                        // Swallow secondary failure to ensure loop continues
                    }

                    // Skip to next row without aborting whole import
                    continue;
                }
            }

            fclose($fh);

            if (! empty($failedRows)) {
                ImportEvent::create([
                    'import_id' => $import->id,
                    'user_id' => $user?->getAuthIdentifier(),
                    'event' => 'row_exceptions_summary',
                    'level' => 'warning',
                    'message' => 'Rows failed: '.(count($failedRows) > 30 ? (implode(',', array_slice($failedRows, 0, 30)).' (+'.(count($failedRows) - 30).' more)') : implode(',', $failedRows)),
                    'context' => [
                        'failed_count' => count($failedRows),
                        'rows' => count($failedRows) > 200 ? array_slice($failedRows, 0, 200) : $failedRows,
                    ],
                ]);
            }

            $meta = $import->meta ?? [];
            if ($historyImport) {
                if (! empty($this->historyFoundContractIds)) {
                    $found = Contract::query()
                        ->with(['clientCase.person'])
                        ->whereIn('id', array_keys($this->historyFoundContractIds))
                        ->get()
                        ->map(function (Contract $c) {
                            return [
                                'contract_uuid' => $c->uuid ?? null,
                                'reference' => $c->reference,
                                'case_uuid' => $c->clientCase?->uuid,
                                'full_name' => $c->clientCase?->person?->full_name,
                            ];
                        })
                        ->values()
                        ->all();
                    $meta['history_found_contracts'] = $found;
                } else {
                    $meta['history_found_contracts'] = [];
                }
            }

            $import->update([
                'status' => 'completed',
                'finished_at' => now(),
                'total_rows' => $total,
                'imported_rows' => $imported,
                'invalid_rows' => $invalid,
                'valid_rows' => $total - $invalid,
                'meta' => $meta,
            ]);

            DB::commit();

            return [
                'ok' => true,
                'status' => $import->status,
                'counts' => compact('total', 'skipped', 'imported', 'invalid'),
            ];
        } catch (\Throwable $e) {
            if (is_resource($fh)) {
                @fclose($fh);
            }
            DB::rollBack();
            // Mark failed and log after rollback (so no partial writes persist)
            $import->refresh();
            $import->update(['status' => 'failed', 'failed_at' => now()]);
            Log::error('Import processing_failed', [
                'import_id' => $import->id,
                'exception' => $this->exceptionContext($e),
            ]);
            ImportEvent::create([
                'import_id' => $import->id,
                'user_id' => $user?->getAuthIdentifier(),
                'event' => 'processing_failed',
                'level' => 'error',
                'message' => $this->safeErrorMessage($e->getMessage()),
                'context' => $this->exceptionContext($e),
            ]);

            return ['ok' => false, 'status' => 'failed', 'error' => $e->getMessage()];
        }
    }

    private function buildRowAssoc(array $row, ?array $header): array
    {
        if (! $header) {
            // positional mapping: 0..N-1
            $assoc = [];
            foreach ($row as $i => $v) {
                $assoc[(string) $i] = $v;
            }

            return $assoc;
        }
        $assoc = [];
        foreach ($header as $i => $name) {
            $assoc[$name] = $row[$i] ?? null;
        }

        return $assoc;
    }

    protected function applyMappings(array $raw, $mappings, array $supportsMultiple): array
    {
        $recordType = null;
        $mapped = [];
        $appendValue = function ($existing, $label, $value) {
            // Skip empty new values
            if (is_null($value) || (is_string($value) && trim($value) === '')) {
                return $existing;
            }
            $stringVal = is_scalar($value) ? (string) $value : json_encode($value);
            $existingStr = is_null($existing) ? '' : (is_scalar($existing) ? (string) $existing : json_encode($existing));
            if ($existingStr === '') {
                return $label.': '.$stringVal;
            }

            return $existingStr.', '.$label.': '.$stringVal;
        };
        $fieldFirstLabel = [];
        $rootFirstLabel = [];
        foreach ($mappings as $map) {
            $src = $map->source_column;
            $target = $map->target_field;
            if (! $target) {
                continue;
            }
            $value = $raw[$src] ?? null;

            // Transform chain support: e.g. "trim|decimal" or "upper|alnum"
            $transform = (string) ($map->transform ?? '');
            if ($transform !== '') {
                $parts = explode('|', $transform);
                foreach ($parts as $t) {
                    $t = trim($t);
                    if ($t === 'trim') {
                        $value = is_string($value) ? trim($value) : $value;
                    } elseif ($t === 'upper') {
                        $value = is_string($value) ? strtoupper($value) : $value;
                    } elseif ($t === 'lower') {
                        $value = is_string($value) ? strtolower($value) : $value;
                    } elseif ($t === 'digits' || $t === 'numeric') {
                        $value = is_string($value) ? preg_replace('/[^0-9]/', '', $value) : $value;
                    } elseif ($t === 'decimal') {
                        $value = is_string($value) ? $this->normalizeDecimal($value) : $value;
                    } elseif ($t === 'alnum') {
                        $value = is_string($value) ? preg_replace('/[^A-Za-z0-9]/', '', $value) : $value;
                    } elseif ($t === 'ref') {
                        // Reference safe: keep letters+digits only, uppercase
                        $value = is_string($value) ? strtoupper(preg_replace('/[^A-Za-z0-9]/', '', $value)) : $value;
                    }
                }
            }

            // detect record type from first segment, e.g., "account.balance_amount"
            $parts = explode('.', $target);
            $rootWithBracket = $parts[0] ?? '';
            // Support bracket grouping like address[1].city
            $group = null;
            $root = $rootWithBracket;
            if (preg_match('/^(?P<base>[a-zA-Z_][a-zA-Z0-9_]*)(\[(?P<grp>[^\]]+)\])$/', $rootWithBracket, $m)) {
                $root = $m['base'];
                $group = $m['grp'];
            }
            if (! $recordType && isset($root)) {
                $recordType = $root;
            }

            // If this root supports multiple, determine group id: prefer mapping options.group, else bracket, else '1'
            $supportsMulti = $supportsMultiple[$root] ?? false;
            // Special handling for meta mappings: contract.meta.key (supports options.key and options.type)
            if ($root === 'contract' && isset($parts[1]) && str_starts_with($parts[1], 'meta')) {
                // Path could be meta.key or meta[key]
                $metaKey = null;
                $metaType = null;
                // support dot path contract.meta.someKey
                if (isset($parts[2])) {
                    $metaKey = $parts[2];
                } else {
                    // support contract.meta[someKey]
                    if (preg_match('/^meta\[(?P<k>[^\]]+)\]$/', $parts[1], $mm)) {
                        $metaKey = $mm['k'];
                    }
                }
                if ($metaKey === null || $metaKey === '') {
                    // fallback: read key from mapping options.key if present
                    $opts = $map->options ?? null;
                    if (is_string($opts)) {
                        $opts = json_decode($opts, true) ?: [];
                    }
                    if (is_array($opts) && ! empty($opts['key'])) {
                        $metaKey = (string) $opts['key'];
                    }
                    if (is_array($opts) && ! empty($opts['type'])) {
                        $metaType = is_string($opts['type']) ? strtolower($opts['type']) : null;
                    }
                } else {
                    // we still may have options.type
                    $opts = $map->options ?? null;
                    if (is_string($opts)) {
                        $opts = json_decode($opts, true) ?: [];
                    }
                    if (is_array($opts) && ! empty($opts['type'])) {
                        $metaType = is_string($opts['type']) ? strtolower($opts['type']) : null;
                    }
                }
                if ($metaKey !== null && $metaKey !== '') {
                    // group-aware bucket for meta entries
                    $groupOpt = $this->mappingOptionGroup($map);
                    $grp = ($groupOpt !== null && $groupOpt !== '') ? (string) $groupOpt : ($group ?? '1');
                    if (! isset($mapped['contract'])) {
                        $mapped['contract'] = [];
                    }
                    if (! isset($mapped['contract']['meta']) || ! is_array($mapped['contract']['meta'])) {
                        $mapped['contract']['meta'] = [];
                    }
                    if (! isset($mapped['contract']['meta'][$grp])) {
                        $mapped['contract']['meta'][$grp] = [];
                    }
                    // Optionally coerce the value based on provided type
                    $coerced = $value;
                    $metaType = in_array($metaType, ['string', 'number', 'date', 'boolean'], true) ? $metaType : null;
                    if ($metaType === 'number') {
                        if (is_string($coerced)) {
                            $norm = $this->normalizeDecimal($coerced);
                            $coerced = is_numeric($norm) ? (float) $norm : $coerced;
                        }
                    } elseif ($metaType === 'boolean') {
                        if (is_string($coerced)) {
                            $lc = strtolower(trim($coerced));
                            if (in_array($lc, ['1', 'true', 'yes', 'y'], true)) {
                                $coerced = true;
                            } elseif (in_array($lc, ['0', 'false', 'no', 'n'], true)) {
                                $coerced = false;
                            }
                        } else {
                            $coerced = (bool) $coerced;
                        }
                    } elseif ($metaType === 'date') {
                        $coerced = is_scalar($coerced) ? $this->normalizeDate((string) $coerced) : null;
                    } else {
                        // string or unspecified: cast scalars to string for consistency
                        if (is_scalar($coerced)) {
                            $coerced = (string) $coerced;
                        }
                    }
                    // Store as structure with title, value and optional type
                    $entry = [
                        'title' => is_string($src) ? $src : (string) $src,
                        'value' => $coerced,
                    ];
                    if ($metaType !== null) {
                        $entry['type'] = $metaType;
                    }
                    $mapped['contract']['meta'][$grp][$metaKey] = $entry;

                    continue;
                }
            }
            if ($supportsMulti) {
                $groupOpt = $this->mappingOptionGroup($map);
                $grp = ($groupOpt !== null && $groupOpt !== '') ? (string) $groupOpt : ($group ?? '1');
                // rebuild target path to exclude bracket part
                $field = $parts[1] ?? null;
                if ($field !== null) {
                    if (! isset($mapped[$root]) || ! is_array($mapped[$root])) {
                        $mapped[$root] = [];
                    }
                    if (! isset($mapped[$root][$grp]) || ! is_array($mapped[$root][$grp])) {
                        $mapped[$root][$grp] = [];
                    }
                    $mapped[$root][$grp][$field] = $value;
                }
            } else {
                // single item root: assign field or root as appropriate
                $field = $parts[1] ?? null;
                if ($field !== null) {
                    if (! isset($mapped[$root]) || ! is_array($mapped[$root])) {
                        $mapped[$root] = [];
                    }
                    $canConcat = $this->fieldAllowsConcatenation($root, $field);
                    if ($canConcat) {
                        $key = $root.'.'.$field;
                        if (! array_key_exists($field, $mapped[$root]) || is_null($mapped[$root][$field]) || $mapped[$root][$field] === '') {
                            $mapped[$root][$field] = $value;
                            $fieldFirstLabel[$key] = (string) $src;
                        } else {
                            $existing = $mapped[$root][$field];
                            $firstLabel = $fieldFirstLabel[$key] ?? null;
                            $existingStr = $firstLabel ? ($firstLabel.': '.(is_scalar($existing) ? (string) $existing : json_encode($existing))) : (is_scalar($existing) ? (string) $existing : json_encode($existing));
                            $mapped[$root][$field] = $appendValue($existingStr, (string) $src, $value);
                        }
                    } else {
                        // For typed fields (dates/numbers), keep the first non-empty value to avoid coercion errors
                        if (! array_key_exists($field, $mapped[$root]) || is_null($mapped[$root][$field]) || $mapped[$root][$field] === '') {
                            $mapped[$root][$field] = $value;
                        }
                    }
                } else {
                    if (! array_key_exists($root, $mapped) || is_null($mapped[$root]) || $mapped[$root] === '') {
                        $mapped[$root] = $value;
                        $rootFirstLabel[$root] = (string) $src;
                    } else {
                        $firstLabel = $rootFirstLabel[$root] ?? null;
                        $existing = $mapped[$root];
                        $existingStr = $firstLabel ? ($firstLabel.': '.(is_scalar($existing) ? (string) $existing : json_encode($existing))) : (is_scalar($existing) ? (string) $existing : json_encode($existing));
                        $mapped[$root] = $appendValue($existingStr, (string) $src, $value);
                    }
                }
            }
        }

        return [$recordType, $mapped];
    }

    /**
     * Decide whether multiple mapped source columns should be concatenated for a given target field.
     * For date/time and numeric-like fields we avoid concatenation to prevent invalid type coercion.
     */
    private function fieldAllowsConcatenation(?string $root, ?string $field): bool
    {
        if ($field === null) {
            return true;
        }
        $f = strtolower($field);
        // Date / datetime indicators
        if ($f === 'birthday' || str_contains($f, 'date') || str_ends_with($f, '_at')) {
            return false;
        }
        // Common numeric fields
        if (in_array($f, ['amount', 'amount_cents', 'quantity', 'balance_amount'], true)) {
            return false;
        }

        return true;
    }

    private function arraySetDot(array &$arr, string $path, $value): void
    {
        $keys = explode('.', $path);
        $ref = &$arr;
        foreach ($keys as $k) {
            if (! isset($ref[$k]) || ! is_array($ref[$k])) {
                $ref[$k] = [];
            }
            $ref = &$ref[$k];
        }
        $ref = $value;
    }

    private function upsertAccount(Import $import, array $mapped, $mappings, bool $historyImport = false): array
    {
        $clientId = $import->client_id; // may be null, used for contract lookup/creation
        $acc = $mapped['account'] ?? [];
        $contractId = $acc['contract_id'] ?? null;
        $reference = $acc['reference'] ?? null;
        // Determine if the template includes any contract mappings; if not, do not create contracts here
        $hasContractRoot = $this->mappingsContainRoot($mappings, 'contract');
        // Normalize references (remove spaces) for consistent matching
        if (! is_null($reference)) {
            $reference = preg_replace('/\s+/', '', trim((string) $reference));
            $acc['reference'] = $reference;
        }
        if (! empty($acc['contract_reference'] ?? null)) {
            $acc['contract_reference'] = preg_replace('/\s+/', '', trim((string) $acc['contract_reference']));
        }
        // If contract_id not provided, attempt to resolve by contract reference for the selected client
        if (! $contractId) {
            $contractRef = $acc['contract_reference'] ?? ($mapped['contract']['reference'] ?? null);
            if ($clientId && $contractRef) {
                // 1) Search existing contract by reference for that client (across its client cases)
                $existingContract = Contract::query()
                    ->join('client_cases', 'contracts.client_case_id', '=', 'client_cases.id')
                    ->where('client_cases.client_id', $clientId)
                    ->where('contracts.reference', $contractRef)
                    ->select('contracts.*')
                    ->first();
                if ($existingContract) {
                    $contractId = $existingContract->id;
                } elseif ($hasContractRoot) {
                    // Only create a new contract if the template explicitly includes contract mappings
                    // Resolve debtor via identifiers or provided person
                    $personId = $this->findPersonIdByIdentifiers($mapped['person'] ?? []);
                    if (! $personId) {
                        $personId = $this->findOrCreatePersonId($mapped['person'] ?? []);
                    }
                    if (! $personId) {
                        $personId = $this->createMinimalPersonId();
                    }
                    $clientCaseId = $this->findOrCreateClientCaseId($clientId, $personId, $mapped['client_case']['client_ref'] ?? null);
                    $contractFields = $mapped['contract'] ?? [];
                    $newContractData = [
                        'client_case_id' => $clientCaseId,
                        'reference' => $contractRef,
                    ];
                    foreach (['start_date', 'end_date', 'description', 'type_id'] as $k) {
                        if (array_key_exists($k, $contractFields) && ! is_null($contractFields[$k])) {
                            $val = $contractFields[$k];
                            if (in_array($k, ['start_date', 'end_date'], true)) {
                                $val = $this->normalizeDate(is_scalar($val) ? (string) $val : null);
                            }
                            $newContractData[$k] = $val;
                        }
                    }
                    $newContractData['start_date'] = $newContractData['start_date'] ?? now()->toDateString();
                    $newContractData['type_id'] = $newContractData['type_id'] ?? $this->getDefaultContractTypeId();
                    $createdContract = Contract::create($newContractData);
                    $contractId = $createdContract->id;
                } else {
                    // Do not create contracts implicitly when not mapped in the template
                    $contractId = null;
                }
                if ($contractId) {
                    $acc['contract_id'] = $contractId;
                    $mapped['account'] = $acc;
                }
            }
        }
        // Fallback: if account.reference is empty but contract.reference is present, use it
        if ((is_null($reference) || $reference === '') && ! empty($mapped['contract']['reference'] ?? null)) {
            $reference = preg_replace('/\s+/', '', trim((string) $mapped['contract']['reference']));
            if ($reference !== '') {
                $acc['reference'] = $reference;
                $mapped['account'] = $acc;
            }
        }
        // Do not default or infer account.reference from other fields; rely solely on mapped values
        if (! $contractId || ! $reference) {
            $issues = [];
            if (! $contractId) {
                $issues[] = 'contract_id unresolved';
            }
            if (! $reference) {
                $issues[] = 'account.reference empty';
            }
            $candidateContractRef = $acc['contract_reference'] ?? ($mapped['contract']['reference'] ?? null);

            return [
                'action' => 'skipped',
                'message' => 'Prerequisite missing: '.implode(' & ', $issues),
                'context' => [
                    'has_contract_root_mapped' => $hasContractRoot,
                    'candidate_contract_reference' => $candidateContractRef,
                    'account_reference_provided' => $reference,
                    'account_fields_present' => array_keys(array_filter($acc, fn ($v) => ! is_null($v) && $v !== '')),
                ],
            ];
        }

        $existing = Account::query()
            ->where('contract_id', $contractId)
            //->where('reference', $reference)
            ->where('active', 1)
            ->first();

        // Build applyable data based on apply_mode
        $applyInsert = [];
        $applyUpdate = [];
        $applyModeByField = [];
        foreach ($mappings as $map) {
            if (! $map->target_field) {
                continue;
            }
            $parts = explode('.', $map->target_field);
            if ($parts[0] !== 'account') {
                continue;
            }
            $field = $parts[1] ?? null;
            if (! $field) {
                continue;
            }
            $value = $acc[$field] ?? null;
            if (in_array($field, ['balance_amount', 'initial_amount'], true) && is_string($value)) {
                $value = $this->normalizeDecimal($value);
                // Ensure the normalized value is numeric, otherwise default to 0
                if ($value === '' || $value === '-' || ! is_numeric($value)) {
                    $value = 0;
                }
            }
            // Convert empty string to 0 for amount fields
            if (in_array($field, ['balance_amount', 'initial_amount'], true) && ($value === '' || $value === null)) {
                $value = 0;
            }
            $mode = $map->apply_mode ?? 'both';
            if ($mode === 'keyref') {
                // treat as insert-only field (lookup + create), never update
                $applyInsert[$field] = $value;

                continue;
            }
            $applyModeByField[$field] = $mode;
            if (in_array($mode, ['insert', 'both'])) {
                $applyInsert[$field] = $value;
            }
            if (in_array($mode, ['update', 'both'])) {
                // Do not allow updating initial_amount when mapping is update-only
                if ($field === 'initial_amount' && $mode === 'update') {
                    // skip: initial_amount can change only on insert, or when mapping mode is 'both'
                } else {
                    $applyUpdate[$field] = $value;
                }
            }
        }

        if ($historyImport && $existing) {
            return ['action' => 'skipped', 'account' => $existing, 'contract_id' => $contractId, 'message' => 'History import does not update accounts'];
        }

        if ($existing) {
            // Build non-null changes for account fields
            $changes = array_filter($applyUpdate, fn ($v) => ! is_null($v));
            // Track balance change - normalize in case DB has malformed data
            $rawBalance = $existing->balance_amount ?? 0;
            if (is_string($rawBalance) && $rawBalance !== '') {
                $rawBalance = $this->normalizeDecimal($rawBalance);
            }
            $oldBalance = is_numeric($rawBalance) ? (float) $rawBalance : 0;
            // Note: meta merging for contracts is handled in upsertContractChain, not here
            if (! empty($changes)) {
                $existing->fill($changes);
                $existing->save();
            }

            // If balance_amount changed and this wasn't caused by a payment (we are in account upsert), log an activity with before/after
            if (array_key_exists('balance_amount', $changes)) {
                $rawNewBalance = $existing->balance_amount ?? 0;
                if (is_string($rawNewBalance) && $rawNewBalance !== '') {
                    $rawNewBalance = $this->normalizeDecimal($rawNewBalance);
                }
                $newBalance = is_numeric($rawNewBalance) ? (float) $rawNewBalance : 0;
                if ($newBalance !== $oldBalance) {
                    try {
                        $contractId = $existing->contract_id;
                        $clientCaseId = Contract::where('id', $contractId)->value('client_case_id');
                        $currency = optional(\App\Models\PaymentSetting::query()->first())->default_currency ?? 'EUR';
                        $beforeStr = number_format($oldBalance, 2, ',', '.').' '.$currency;
                        $afterStr = number_format($newBalance, 2, ',', '.').' '.$currency;
                        $note = 'Sprememba stanja (Stanje pred: '.$beforeStr.', Stanje po: '.$afterStr.'; Izvor: sprememba)';
                        if ($clientCaseId) {
                            // Use action_id from import meta if available to satisfy NOT NULL constraint on activities.action_id
                            $metaActionId = (int) ($import->meta['action_id'] ?? 0);

                            if ($metaActionId > 0) {
                                Activity::create([
                                    'due_date' => null,
                                    'amount' => null,
                                    'note' => $note,
                                    'action_id' => $metaActionId,
                                    'decision_id' => $import->meta['decision_id'] ?? null,
                                    'client_case_id' => $clientCaseId,
                                    'contract_id' => $contractId,
                                ]);
                            } else {
                                // If no action id is provided, skip creating the activity to avoid NOT NULL violation
                            }
                        }
                    } catch (\Throwable $e) {
                        // Non-fatal: ignore activity creation failures
                    }
                }
            }

            // also include contract hints for downstream contact resolution
            return ['action' => 'updated', 'account' => $existing, 'contract_id' => $contractId, 'applied_fields' => $changes];
        } else {
            // On insert: if initial_amount is not provided but balance_amount is, allow defaulting
            // Only when the mapping for initial_amount is 'insert' or 'both', or unmapped (null).
            $initMode = $applyModeByField['initial_amount'] ?? null;
            if ((! array_key_exists('initial_amount', $applyInsert) || is_null($applyInsert['initial_amount'] ?? null))
                && array_key_exists('balance_amount', $applyInsert)
                && ($applyInsert['balance_amount'] !== null && $applyInsert['balance_amount'] !== '')
                && ($initMode === null || in_array($initMode, ['insert', 'both'], true))) {
                $applyInsert['initial_amount'] = $applyInsert['balance_amount'];
            }
            if ($historyImport) {
                // Force zero amounts for history imports regardless of mapped amounts
                $applyInsert['balance_amount'] = 0;
                $applyInsert['initial_amount'] = 0;
            }
            if (empty($applyInsert)) {
                return ['action' => 'skipped', 'message' => 'No fields marked for insert'];
            }
            $data = array_filter($applyInsert, fn ($v) => ! is_null($v));
            $data['contract_id'] = $contractId;
            $data['reference'] = $reference;
            // ensure required defaults
            $data['type_id'] = $data['type_id'] ?? $this->getDefaultAccountTypeId();
            if (! array_key_exists('active', $data)) {
                $data['active'] = 1;
            }
            if ($historyImport) {
                $data['balance_amount'] = 0;
                $data['initial_amount'] = 0;
            }
            $created = Account::create($data);

            return ['action' => 'inserted', 'account' => $created, 'contract_id' => $contractId, 'applied_fields' => $data];
        }
    }

    private function ensureHistoryAccount(Contract $contract, array $mapped): ?Account
    {
        $existing = Account::query()
            ->where('contract_id', $contract->id)
            ->where('active', 1)
            ->first();
        if ($existing) {
            return $existing;
        }

        $reference = $mapped['account']['reference'] ?? $mapped['contract']['reference'] ?? null;
        if (is_string($reference)) {
            $reference = preg_replace('/\s+/', '', trim($reference));
        }
        if (! $reference || $reference === '') {
            $reference = 'HIST-'.$contract->id;
        }

        return Account::create([
            'contract_id' => $contract->id,
            'reference' => $reference,
            'type_id' => $this->getDefaultAccountTypeId(),
            'active' => 1,
            'balance_amount' => 0,
            'initial_amount' => 0,
        ]);
    }

    private function upsertCaseObject(Import $import, array $mapped, $mappings, int $contractId): array
    {
        // Support both 'case_object' and 'case_objects' keys (template may use plural)
        $obj = $mapped['case_objects'] ?? $mapped['case_object'] ?? [];
        $reference = $obj['reference'] ?? null;
        $name = $obj['name'] ?? null;

        // Normalize reference (remove spaces) for consistent matching
        if (! is_null($reference)) {
            $reference = preg_replace('/\s+/', '', trim((string) $reference));
        }

        // At least name or reference must be provided
        if (! $reference && (! $name || trim($name) === '')) {
            return [
                'action' => 'skipped',
                'message' => 'Case object requires at least a reference or name',
                'context' => ['missing' => 'reference and name'],
            ];
        }

        $existing = null;

        // First, try to find by contract_id and reference (if reference provided)
        if ($reference) {
            $existing = CaseObject::query()
                ->where('contract_id', $contractId)
                ->where('reference', $reference)
                ->first();
        }

        // If not found by reference and name is provided, check for duplicate by name
        // This prevents creating duplicate case objects with same name for a contract
        if (! $existing && ! is_null($name) && trim($name) !== '') {
            $normalizedName = trim($name);
            $duplicateByName = CaseObject::query()
                ->where('contract_id', $contractId)
                ->where('name', $normalizedName)
                ->first();

            if ($duplicateByName) {
                // Found existing by name - use it as the existing record
                $existing = $duplicateByName;
            }
        }

        // Build applyable data based on apply_mode
        $applyInsert = [];
        $applyUpdate = [];
        $applyModeByField = [];
        foreach ($mappings as $map) {
            $target = (string) ($map->target_field ?? '');
            // Support both 'case_object.' and 'case_objects.' (template may use plural)
            if (! str_starts_with($target, 'case_object.') && ! str_starts_with($target, 'case_objects.')) {
                continue;
            }
            // Extract field name - handle both singular and plural prefix
            $field = str_starts_with($target, 'case_objects.')
                ? substr($target, strlen('case_objects.'))
                : substr($target, strlen('case_object.'));
            $applyModeByField[$field] = (string) ($map->apply_mode ?? 'both');
        }

        foreach ($obj as $field => $value) {
            $applyMode = $applyModeByField[$field] ?? 'both';
            if (is_null($value) || (is_string($value) && trim($value) === '')) {
                continue;
            }
            if (in_array($applyMode, ['both', 'insert'], true)) {
                $applyInsert[$field] = $value;
            }
            if (in_array($applyMode, ['both', 'update'], true)) {
                $applyUpdate[$field] = $value;
            }
        }

        if ($existing) {
            // Build non-null changes for case object fields
            $changes = array_filter($applyUpdate, fn ($v) => ! is_null($v));
            if (! empty($changes)) {
                $existing->fill($changes);
                $existing->save();

                return ['action' => 'updated', 'case_object' => $existing, 'applied_fields' => $changes];
            } else {
                return ['action' => 'skipped', 'case_object' => $existing, 'message' => 'No changes needed'];
            }
        } else {
            // Create new case object
            $data = array_merge([
                'contract_id' => $contractId,
                'reference' => $reference,
            ], $applyInsert);

            // Remove any null values
            $data = array_filter($data, fn ($v) => ! is_null($v));

            $created = CaseObject::create($data);

            return ['action' => 'inserted', 'case_object' => $created, 'applied_fields' => $data];
        }
    }

    private function upsertActivity(Import $import, array $mapped, $mappings, ?array $contractResult, ?array $accountResult): array
    {
        $activity = $mapped['activity'] ?? [];

        // Default contract/client_case from freshly created or updated contract when present
        if ($contractResult && isset($contractResult['contract']) && $contractResult['contract'] instanceof Contract) {
            $activity['contract_id'] = $activity['contract_id'] ?? $contractResult['contract']->id;
            $activity['client_case_id'] = $activity['client_case_id'] ?? $contractResult['contract']->client_case_id;
        }

        $contractId = $activity['contract_id'] ?? null;
        if (! $contractId && $contractResult && isset($contractResult['contract']) && $contractResult['contract'] instanceof Contract) {
            $contractId = $contractResult['contract']->id;
        } elseif (! $contractId && $accountResult && isset($accountResult['contract_id'])) {
            $contractId = $accountResult['contract_id'];
        } elseif (! $contractId && $accountResult && isset($accountResult['contract']) && $accountResult['contract'] instanceof Contract) {
            $contractId = $accountResult['contract']->id;
        }
        if (! $contractId && $import->client_id && ! empty($mapped['contract']['reference'] ?? null)) {
            $ref = preg_replace('/\s+/', '', trim((string) $mapped['contract']['reference']));
            if ($ref !== '') {
                $contractId = Contract::query()
                    ->join('client_cases', 'contracts.client_case_id', '=', 'client_cases.id')
                    ->where('client_cases.client_id', $import->client_id)
                    ->where('contracts.reference', $ref)
                    ->value('contracts.id');
            }
        }

        $clientCaseId = $activity['client_case_id'] ?? null;
        if (! $clientCaseId && $contractId) {
            $clientCaseId = Contract::where('id', $contractId)->value('client_case_id');
        }
        if (! $clientCaseId && $contractResult && isset($contractResult['contract']) && $contractResult['contract'] instanceof Contract) {
            $clientCaseId = $contractResult['contract']->client_case_id;
        }
        if (! $clientCaseId && $accountResult && isset($accountResult['contract']) && $accountResult['contract'] instanceof Contract) {
            $clientCaseId = $accountResult['contract']->client_case_id;
        }
        if (! $clientCaseId && $accountResult && isset($accountResult['contract_id'])) {
            $clientCaseId = Contract::where('id', $accountResult['contract_id'])->value('client_case_id');
        }
        if (! $clientCaseId && $import->client_id && ! empty($mapped['client_case']['client_ref'] ?? null)) {
            $clientCaseId = ClientCase::where('client_id', $import->client_id)
                ->where('client_ref', $mapped['client_case']['client_ref'])
                ->value('id');
        }

        if (! $clientCaseId) {
            return [
                'action' => 'invalid',
                'message' => 'Activity requires a client_case_id or resolvable contract.',
                'context' => [
                    'contract_id' => $contractId,
                    'contract_reference' => $mapped['contract']['reference'] ?? null,
                ],
            ];
        }

        // Collect apply_mode settings for activity fields
        $applyModeByField = [];
        foreach ($mappings as $map) {
            $target = (string) ($map->target_field ?? '');
            if (! str_starts_with($target, 'activity.')) {
                continue;
            }
            $field = substr($target, strlen('activity.'));
            $applyModeByField[$field] = (string) ($map->apply_mode ?? 'both');
        }

        $applyInsert = [];
        $applyUpdate = [];
        foreach ($activity as $field => $value) {
            $applyMode = $applyModeByField[$field] ?? 'both';
            $normalized = $value;
            if ($field === 'due_date') {
                $normalized = is_scalar($value) ? $this->normalizeDate((string) $value) : null;
            } elseif ($field === 'created_at') {
                $normalized = is_scalar($value) ? $this->normalizeDateTime((string) $value) : null;
            } elseif ($field === 'amount') {
                if (is_string($value)) {
                    $normalized = $this->normalizeDecimal($value);
                }
                $normalized = is_null($normalized) ? null : (float) $normalized;
            } elseif (in_array($field, ['action_id', 'decision_id', 'user_id'], true)) {
                $normalized = is_null($value) ? null : (int) $value;
            } elseif (is_string($normalized)) {
                // Clean invalid UTF-8 sequences from string fields
                $normalized = mb_convert_encoding($normalized, 'UTF-8', 'UTF-8');
                $normalized = trim($normalized);
            }
            if (in_array($applyMode, ['both', 'insert'], true)) {
                $applyInsert[$field] = $normalized;
            }
            if (in_array($applyMode, ['both', 'update'], true)) {
                $applyUpdate[$field] = $normalized;
            }
        }

        $settings = \App\Models\PaymentSetting::query()->first();
        $tplMeta = optional($import->template)->meta ?? [];
        $defaultActionId = $applyInsert['action_id']
            ?? $applyUpdate['action_id']
            ?? ($import->meta['activity_action_id'] ?? null)
            ?? ($tplMeta['activity_action_id'] ?? null)
            ?? ($import->meta['action_id'] ?? null)
            ?? ($tplMeta['action_id'] ?? null)
            ?? ($settings?->default_action_id ?? null);
        $defaultDecisionId = $applyInsert['decision_id']
            ?? $applyUpdate['decision_id']
            ?? ($import->meta['activity_decision_id'] ?? null)
            ?? ($tplMeta['activity_decision_id'] ?? null)
            ?? ($import->meta['decision_id'] ?? null)
            ?? ($tplMeta['decision_id'] ?? null);

        if (! $defaultActionId) {
            return [
                'action' => 'invalid',
                'message' => 'Activity requires action_id (provide via mapping or import meta).',
            ];
        }

        $applyInsert['action_id'] = $applyInsert['action_id'] ?? $defaultActionId;
        $applyUpdate['action_id'] = $applyUpdate['action_id'] ?? $defaultActionId;
        if ($defaultDecisionId) {
            $applyInsert['decision_id'] = $applyInsert['decision_id'] ?? $defaultDecisionId;
            $applyUpdate['decision_id'] = $applyUpdate['decision_id'] ?? $defaultDecisionId;
        }

        // Default created_at for inserted activities (from mapped field or template/import meta)
        if (empty($applyInsert['created_at'])) {
            $defaultCreated = $this->normalizeDateTime($applyInsert['created_at'] ?? $import->meta['activity_created_at'] ?? $tplMeta['activity_created_at'] ?? null);
            if ($defaultCreated) {
                $applyInsert['created_at'] = $defaultCreated;
                if (empty($applyInsert['updated_at'])) {
                    $applyInsert['updated_at'] = $defaultCreated;
                }
            }
        }

        $applyInsert['client_case_id'] = $clientCaseId;
        $applyUpdate['client_case_id'] = $applyUpdate['client_case_id'] ?? $clientCaseId;
        if ($contractId) {
            $applyInsert['contract_id'] = $contractId;
            $applyUpdate['contract_id'] = $applyUpdate['contract_id'] ?? $contractId;
        }

        // Idempotency: if note + due_date + contract match, treat as existing
        $existing = null;
        $noteKey = $applyInsert['note'] ?? null;
        $dueKey = $applyInsert['due_date'] ?? null;
        if ($contractId && $noteKey && $dueKey) {
            $existing = Activity::query()
                ->where('contract_id', $contractId)
                ->where('note', $noteKey)
                ->whereDate('due_date', $dueKey)
                ->first();
        } elseif ($clientCaseId && $noteKey && $dueKey) {
            $existing = Activity::query()
                ->where('client_case_id', $clientCaseId)
                ->where('note', $noteKey)
                ->whereDate('due_date', $dueKey)
                ->first();
        }

        if ($existing) {
            $changes = array_filter($applyUpdate, fn ($v) => ! is_null($v));
            if (! empty($changes)) {
                $existing->fill($changes);
                $existing->save();

                return ['action' => 'updated', 'activity' => $existing, 'applied_fields' => $changes];
            }

            return ['action' => 'skipped', 'activity' => $existing, 'message' => 'No changes needed'];
        }

        $data = array_filter($applyInsert, fn ($v) => ! is_null($v));
        $activityModel = new Activity;
        $activityModel->forceFill($data);
        if (array_key_exists('created_at', $data)) {
            // Preserve provided timestamps by disabling automatic timestamps for this save
            $activityModel->timestamps = false;
            $activityModel->save();
            $activityModel->timestamps = true;
        } else {
            $activityModel->save();
        }

        return ['action' => 'inserted', 'activity' => $activityModel, 'applied_fields' => $data];
    }

    private function normalizeDateTime(?string $raw): ?string
    {
        if ($raw === null || trim($raw) === '') {
            return null;
        }
        try {
            return Carbon::parse($raw)->format('Y-m-d H:i:s');
        } catch (\Throwable $e) {
            return null;
        }
    }

    private function mappingsContainRoot($mappings, string $root): bool
    {
        foreach ($mappings as $map) {
            $target = (string) ($map->target_field ?? '');
            if ($target !== '' && str_starts_with($target, $root.'.')) {
                return true;
            }
        }

        return false;
    }

    private function findPersonIdByIdentifiers(array $p): ?int
    {
        $tax = $p['tax_number'] ?? null;
        if ($tax) {
            $found = Person::where('tax_number', $tax)->first();
            if ($found) {
                return $found->id;
            }
        }
        $ssn = $p['social_security_number'] ?? null;
        if ($ssn) {
            $found = Person::where('social_security_number', $ssn)->first();
            if ($found) {
                return $found->id;
            }
        }

        return null;
    }

    private function upsertContractChain(Import $import, array $mapped, $mappings, bool $historyImport = false): array
    {
        $contractData = $mapped['contract'] ?? [];
        $reference = $contractData['reference'] ?? null;
        if (! is_null($reference)) {
            $reference = preg_replace('/\s+/', '', trim((string) $reference));
            $contractData['reference'] = $reference;
        }
        if (! $reference) {
            return ['action' => 'invalid', 'message' => 'Missing contract.reference'];
        }

        // Temporary debug output
        $debugInfo = [
            'row' => $rowIndex ?? 'unknown',
            'reference' => $reference,
            'has_person' => isset($mapped['person']),
            'person_tax' => $mapped['person']['tax_number'] ?? null,
            'client_id' => $import->client_id,
        ];

        \Log::info('ImportProcessor: upsertContractChain START', $debugInfo);

        // Determine mapping mode for contract.reference (e.g., keyref)
        $refMode = $this->mappingMode($mappings, 'contract.reference');

        // Determine client_case_id: prefer provided, else derive via person/client
        $clientCaseId = $contractData['client_case_id'] ?? null;
        $clientId = $import->client_id; // may be null

        // Try to find existing contract EARLY by (client_id, reference) across all cases to prevent duplicates
        $existing = null;
        if ($clientId) {
            $existing = Contract::query()->withTrashed()
                ->join('client_cases', 'contracts.client_case_id', '=', 'client_cases.id')
                ->where('client_cases.client_id', $clientId)
                ->where('contracts.reference', $reference)
                ->select('contracts.*')
                ->first();

            // Debug logging to track contract lookup
            if ($existing) {
                \Log::info('ImportProcessor: Found existing contract', [
                    'client_id' => $clientId,
                    'reference' => $reference,
                    'contract_id' => $existing->id,
                    'client_case_id' => $existing->client_case_id,
                ]);
            } else {
                \Log::info('ImportProcessor: No existing contract found', [
                    'client_id' => $clientId,
                    'reference' => $reference,
                ]);
            }
        }

        // If not found by client+reference and a specific client_case_id is provided, try that too
        if (! $existing && $clientCaseId) {
            $existing = Contract::query()->withTrashed()
                ->where('client_case_id', $clientCaseId)
                ->where('reference', $reference)
                ->first();
        }

        // If contract.reference is keyref and contract not found, do not create any entities
        if (! $existing && $refMode === 'keyref') {
            ImportEvent::create([
                'import_id' => $import->id,
                'user_id' => null,
                'event' => 'row_skipped',
                'level' => 'warning',
                'message' => 'Contract reference '.$reference.' does not exist (keyref); row skipped.',
            ]);

            return ['action' => 'skipped', 'message' => 'contract.reference keyref lookup failed: not found'];
        }

        // If we still need to insert, we must resolve clientCaseId, but avoid creating new person/case unless necessary
        if (! $existing && ! $clientCaseId) {
            $clientRef = $mapped['client_case']['client_ref'] ?? null;
            // First, if we have a client and client_ref, try to reuse existing case to avoid creating extra persons
            if ($clientId && $clientRef) {
                $cc = ClientCase::where('client_id', $clientId)->where('client_ref', $clientRef)->first();
                if ($cc) {
                    // Reuse this case
                    $clientCaseId = $cc->id;
                    // If case has no person yet and we have mapped person identifiers/data, set it once
                    if (! $cc->person_id) {
                        $pid = null;
                        if (! empty($mapped['person'] ?? [])) {
                            $pid = $this->findPersonIdByIdentifiers($mapped['person']);
                            if (! $pid) {
                                $pid = $this->findOrCreatePersonId($mapped['person']);
                            }
                        }
                        if (! $pid) {
                            $pid = $this->createMinimalPersonId();
                        }
                        $cc->person_id = $pid;
                        $cc->save();
                    }
                }
            }

            if (! $clientCaseId) {
                // Resolve by identifiers or provided person; do not use Client->person
                $personId = null;
                if (! empty($mapped['person'] ?? [])) {
                    $personId = $this->findPersonIdByIdentifiers($mapped['person']);
                    if (! $personId) {
                        $personId = $this->findOrCreatePersonId($mapped['person']);
                    }
                }
                // As a last resort, create a minimal person for this client
                if ($clientId && ! $personId) {
                    $personId = $this->createMinimalPersonId();
                }

                if ($clientId && $personId) {
                    $clientCaseId = $this->findOrCreateClientCaseId($clientId, $personId, $clientRef);
                } elseif ($personId) {
                    // require an import client to attach case/contract
                    return ['action' => 'invalid', 'message' => 'Import must be linked to a client to create a case'];
                } else {
                    return ['action' => 'invalid', 'message' => 'Unable to resolve client_case (need import client)'];
                }
            }
        }

        // Build applyable data based on apply_mode
        $applyInsert = [];
        $applyUpdate = [];
        foreach ($mappings as $map) {
            if (! $map->target_field) {
                continue;
            }
            $parts = explode('.', $map->target_field);
            if (($parts[0] ?? null) !== 'contract') {
                continue;
            }
            $field = $parts[1] ?? null;
            if (! $field) {
                continue;
            }
            $value = $contractData[$field] ?? null;
            if ($field === 'reference' && ! is_null($value)) {
                $value = preg_replace('/\s+/', '', trim((string) $value));
            }
            $mode = $map->apply_mode ?? 'both';
            // keyref: used as lookup and applied on insert, but not on update
            if ($mode === 'keyref') {
                $applyInsert[$field] = $value;

                continue;
            }
            if (in_array($mode, ['insert', 'both'], true)) {
                $applyInsert[$field] = $value;
            }
            if (in_array($mode, ['update', 'both'], true)) {
                $applyUpdate[$field] = $value;
            }
        }

        if ($existing) {
            if ($historyImport) {
                $this->historyFoundContractIds[$existing->id] = true;

                return ['action' => 'skipped_history', 'contract' => $existing, 'message' => 'Existing contract left unchanged (history import)'];
            }
            
            // Check if contract is soft-deleted and needs reactivation
            $isTrashed = $existing->trashed();
            
            // 1) Prepare contract field changes (non-null)
            $changes = array_filter($applyUpdate, fn ($v) => ! is_null($v));
            
            // 2) Handle reactivation defaults when contract is soft-deleted
            if ($isTrashed || $existing->active == 0) {
                // Check if start_date is in the mappings
                $hasStartDateMapping = $this->mappingIncludes($mappings, 'contract.start_date');
                if (!$hasStartDateMapping) {
                    // Default to current date when not in mappings
                    $changes['start_date'] = now()->toDateString();
                }
                
                // Check if end_date is in the mappings
                $hasEndDateMapping = $this->mappingIncludes($mappings, 'contract.end_date');
                if (!$hasEndDateMapping) {
                    // Default to null when not in mappings
                    $changes['end_date'] = null;
                }
            }

            // 3) Prepare meta changes if provided via mapping
            $metaUpdated = false;
            $metaAppliedKeys = [];
            if (! empty($contractData['meta'] ?? null) && is_array($contractData['meta'])) {
                // Flatten incoming grouped meta to key => {title, value}
                $incomingMeta = [];
                foreach ($contractData['meta'] as $grp => $entries) {
                    if (! is_array($entries)) {
                        continue;
                    }
                    foreach ($entries as $k => $v) {
                        // v is expected as [title, value]
                        $incomingMeta[$k] = $v;
                    }
                }
                if (! empty($incomingMeta)) {
                    $currentMeta = is_array($existing->meta ?? null) ? $existing->meta : (json_decode((string) $existing->meta, true) ?: []);
                    foreach ($incomingMeta as $k => $entry) {
                        $newVal = is_array($entry) && array_key_exists('value', $entry) ? $entry['value'] : $entry;
                        $newTitle = is_array($entry) && array_key_exists('title', $entry) ? $entry['title'] : null;
                        $newType = is_array($entry) && array_key_exists('type', $entry) ? $entry['type'] : null;
                        $curEntry = $currentMeta[$k] ?? null;
                        $curVal = is_array($curEntry) && array_key_exists('value', $curEntry) ? $curEntry['value'] : $curEntry;
                        $curTitle = is_array($curEntry) && array_key_exists('title', $curEntry) ? $curEntry['title'] : null;
                        $curType = is_array($curEntry) && array_key_exists('type', $curEntry) ? $curEntry['type'] : null;
                        // Update when value differs, or title differs, or type differs
                        $shouldUpdate = ($newVal !== $curVal) || ($newTitle !== null && $newTitle !== $curTitle) || ($newType !== null && $newType !== $curType);
                        if ($shouldUpdate) {
                            if (is_array($entry)) {
                                $currentMeta[$k] = $entry;
                            } else {
                                $currentMeta[$k] = ['title' => (string) $k, 'value' => $newVal];
                            }
                            $metaUpdated = true;
                            $metaAppliedKeys[] = $k;
                        }
                    }
                    if ($metaUpdated) {
                        $existing->meta = $currentMeta;
                    }
                }
            }

            if (empty($changes) && ! $metaUpdated && ! $isTrashed) {
                // Nothing to change
                return ['action' => 'skipped', 'message' => 'No contract fields or meta changes', 'contract' => $existing];
            }

            if (! empty($changes)) {
                $existing->fill($changes);
            }
            
            // Restore soft-deleted contract if it was trashed
            if ($isTrashed) {
                $existing->restore();
            }
            
            $existing->save();

            // Build applied fields info, include meta keys if any
            $applied = $changes;
            if ($metaUpdated && ! empty($metaAppliedKeys)) {
                foreach ($metaAppliedKeys as $k) {
                    $applied['meta:'.$k] = 'updated';
                }
            }
            
            $actionType = $isTrashed ? 'reactivated' : 'updated';

            return ['action' => $actionType, 'contract' => $existing, 'applied_fields' => $applied];
        } else {
            if (empty($applyInsert)) {
                return ['action' => 'skipped', 'message' => 'No contract fields marked for insert'];
            }
            $data = array_filter($applyInsert, fn ($v) => ! is_null($v));
            if (array_key_exists('start_date', $data)) {
                $norm = $this->normalizeDate(is_scalar($data['start_date']) ? (string) $data['start_date'] : null);
                if ($norm === null || $norm === '') {
                    unset($data['start_date']); // let default fill below
                } else {
                    $data['start_date'] = $norm;
                }
            }
            if (array_key_exists('end_date', $data)) {
                $normEnd = $this->normalizeDate(is_scalar($data['end_date']) ? (string) $data['end_date'] : null);
                if ($normEnd === null || $normEnd === '') {
                    unset($data['end_date']); // treat blank as null (omit)
                } else {
                    $data['end_date'] = $normEnd;
                }
            }
            $data['client_case_id'] = $clientCaseId;
            $data['reference'] = $reference;
            // ensure required defaults
            $data['start_date'] = $data['start_date'] ?? now()->toDateString();
            $data['type_id'] = $data['type_id'] ?? $this->getDefaultContractTypeId();
            // Merge meta for create if provided
            if (! empty($contractData['meta'] ?? null) && is_array($contractData['meta'])) {
                $incomingMeta = [];
                foreach ($contractData['meta'] as $grp => $entries) {
                    if (! is_array($entries)) {
                        continue;
                    }
                    foreach ($entries as $k => $v) {
                        $incomingMeta[$k] = $v;
                    }
                }
                if (! empty($incomingMeta)) {
                    $data['meta'] = $incomingMeta;
                }
            }
            $created = Contract::create($data);

            return ['action' => 'inserted', 'contract' => $created, 'applied_fields' => $data];
        }
    }

    private function sanitizeHeaderName(string $v): string
    {
        // Strip UTF-8 BOM and trim whitespace/control characters
        $v = preg_replace('/^\xEF\xBB\xBF/', '', $v) ?? $v;

        return trim($v);
    }

    /**
     * Normalize a raw date string coming from import sources to Y-m-d or null.
     * Accepts common European formats like d.m.Y / d.m.y / d/m/Y / d/m/y and ISO.
     * Falls back to strtotime parsing; returns null on failure instead of throwing.
     */
    private function normalizeDate(?string $raw): ?string
    {
        // Delegate to shared normalizer for consistency across the app
        return \App\Services\DateNormalizer::toDate($raw);
    }

    private function findSourceColumnFor($mappings, string $targetField): ?string
    {
        foreach ($mappings as $map) {
            if ((string) ($map->target_field ?? '') === $targetField) {
                $src = (string) ($map->source_column ?? '');

                return $src !== '' ? $src : null;
            }
        }

        return null;
    }

    // Removed auto-detection helpers by request: no pattern scanning or fallback derivation

    private function normalizeDecimal(string $raw): string
    {
        // Keep digits, comma, dot, and minus to detect separators
        $s = preg_replace('/[^0-9,\.-]/', '', $raw) ?? '';
        $s = trim($s);
        if ($s === '') {
            return $s;
        }
        $lastComma = strrpos($s, ',');
        $lastDot = strrpos($s, '.');
        // Determine decimal separator by last occurrence
        $decimalSep = null;
        if ($lastComma !== false || $lastDot !== false) {
            if ($lastComma === false) {
                $decimalSep = '.';
            } elseif ($lastDot === false) {
                $decimalSep = ',';
            } else {
                $decimalSep = $lastComma > $lastDot ? ',' : '.';
            }
        }
        // Remove all thousand separators (the other one) and unify decimal to '.'
        if ($decimalSep === ',') {
            // remove all dots
            $s = str_replace('.', '', $s);
            // replace last comma with dot
            $pos = strrpos($s, ',');
            if ($pos !== false) {
                $s[$pos] = '.';
            }
            // remove any remaining commas (unlikely)
            $s = str_replace(',', '', $s);
        } elseif ($decimalSep === '.') {
            // remove all commas
            $s = str_replace(',', '', $s);
            // dot already decimal
        } else {
            // no decimal separator: remove commas/dots entirely
            $s = str_replace([',', '.'], '', $s);
        }

        // Collapse multiple minus signs, keep leading only
        $s = ltrim($s, '+');
        $neg = false;
        if (str_starts_with($s, '-')) {
            $neg = true;
            $s = ltrim($s, '-');
        }
        // Remove any stray minus signs
        $s = str_replace('-', '', $s);
        if ($neg) {
            $s = '-'.$s;
        }

        return $s;
    }

    /**
     * Classify a row-level exception into a coarse category for diagnostics.
     * duplicate|constraint|integrity|validation|db|unknown
     */
    private function classifyRowException(\Throwable $e): string
    {
        $msg = strtolower($e->getMessage());
        if (str_contains($msg, 'duplicate') || str_contains($msg, 'unique') || str_contains($msg, 'already exists')) {
            return 'duplicate';
        }
        if (str_contains($msg, 'foreign key') || str_contains($msg, 'not-null') || str_contains($msg, 'violates') || str_contains($msg, 'constraint')) {
            return 'constraint';
        }
        if (str_contains($msg, 'integrity')) {
            return 'integrity';
        }
        if (str_contains($msg, 'missing') || str_contains($msg, 'required')) {
            return 'validation';
        }
        if (str_contains($msg, 'sqlstate') || str_contains($msg, 'syntax error') || str_contains($msg, 'invalid input')) {
            return 'db';
        }

        return 'unknown';
    }

    /**
     * Ensure error message is valid UTF-8 and safely truncated.
     */
    private function safeErrorMessage(string $msg): string
    {
        // Convert to UTF-8, dropping invalid sequences
        if (! mb_detect_encoding($msg, 'UTF-8', true)) {
            $msg = mb_convert_encoding($msg, 'UTF-8', 'UTF-8');
        }
        // Fallback strip invalid bytes
        $msg = @iconv('UTF-8', 'UTF-8//IGNORE', $msg) ?: $msg;
        // Use mb_strlen and mb_substr for UTF-8 safety
        if (mb_strlen($msg) > 500) {
            $msg = mb_substr($msg, 0, 497).'...';
        }

        return $msg;
    }

    /**
     * Extract structured exception details for logging.
     */
    private function exceptionContext(\Throwable $e): array
    {
        $ctx = [
            'exception' => get_class($e),
            'message' => $this->safeErrorMessage($e->getMessage()),
            'code' => $e->getCode(),
            'file' => $e->getFile().':'.$e->getLine(),
        ];

        if (method_exists($e, 'getPrevious') && $e->getPrevious()) {
            $prev = $e->getPrevious();
            $ctx['previous'] = [
                'exception' => get_class($prev),
                'message' => $this->safeErrorMessage($prev->getMessage()),
                'code' => $prev->getCode(),
                'file' => $prev->getFile().':'.$prev->getLine(),
            ];
        }

        if ($e instanceof QueryException) {
            $ctx['sql'] = $e->getSql();
            $ctx['bindings'] = $e->getBindings();
            $info = $e->errorInfo ?? null;
            if (is_array($info)) {
                $ctx['sqlstate'] = $info[0] ?? null;
                $ctx['driver_error_code'] = $info[1] ?? null;
                $ctx['driver_error_message'] = $info[2] ?? null;
            }
        } elseif (property_exists($e, 'errorInfo')) {
            $info = $e->errorInfo;
            if (is_array($info)) {
                $ctx['sqlstate'] = $info[0] ?? null;
                $ctx['driver_error_code'] = $info[1] ?? null;
                $ctx['driver_error_message'] = $info[2] ?? null;
            }
        }

        return $ctx;
    }

    /**
     * Build a trimmed raw data preview (first 8 columns, truncated values) for logging.
     */
    private function buildRawDataPreview(array $raw): array
    {
        $out = [];
        $i = 0;
        foreach ($raw as $k => $v) {
            if ($i >= 8) {
                break;
            }
            $val = is_scalar($v) || is_null($v) ? (string) $v : json_encode($v);
            if (mb_strlen($val) > 80) {
                $val = mb_substr($val, 0, 77).'...';
            }
            $out[$k] = $val;
            $i++;
        }

        return $out;
    }

    /**
     * Build a concise human-readable field=value list for logging.
     * Example: [account] reference=ACC123 balance_amount=100.00
     */
    private function formatAppliedFieldMessage(string $root, array $fields): string
    {
        if (empty($fields)) {
            return '';
        }
        $parts = [];
        foreach ($fields as $k => $v) {
            if (is_scalar($v) || is_null($v)) {
                $disp = is_null($v) ? 'NULL' : (string) $v;
            } elseif (is_array($v)) {
                $disp = json_encode($v);
            } else {
                $disp = method_exists($v, '__toString') ? (string) $v : gettype($v);
            }
            // Truncate very long values for log safety (use mb_substr for UTF-8 safety)
            if (mb_strlen($disp) > 60) {
                $disp = mb_substr($disp, 0, 57).'...';
            }
            $parts[] = $k.'='.$disp;
        }

        return '['.$root.'] '.implode(' ', $parts);
    }

    /**
     * Collect persisted payment fields (sanitized) for event logging.
     */
    private function collectPaymentAppliedFields(array $payload, \App\Models\Payment $payment): array
    {
        $fields = [];
        foreach (['account_id', 'reference', 'amount', 'paid_at', 'currency'] as $f) {
            if (array_key_exists($f, $payload)) {
                $fields[$f] = $payload[$f];
            } elseif (isset($payment->$f)) {
                $fields[$f] = $payment->$f;
            }
        }
        if (isset($payload['meta'])) {
            $fields['meta'] = $payload['meta'];
        }

        return $fields;
    }

    /**
     * Determine if a raw CSV row is "effectively" empty: all scalar values are null or blank after trimming.
     * Non-scalar values (arrays/objects) will cause the row to be treated as non-empty.
     */
    private function rowIsEffectivelyEmpty(array $rawAssoc): bool
    {
        if (empty($rawAssoc)) {
            return true; // no columns at all
        }
        foreach ($rawAssoc as $v) {
            if (is_array($v) || is_object($v)) {
                return false; // treat structured data as content
            }
            if (! is_null($v)) {
                $s = trim((string) $v);
                if ($s !== '') {
                    return false;
                }
            }
        }

        return true;
    }

    /**
     * Ensure mapping roots are recognized; fail fast if unknown roots found.
     */
    private function validateMappingRoots($mappings, array $validRoots): void
    {
        foreach ($mappings as $map) {
            $target = (string) ($map->target_field ?? '');
            if ($target === '') {
                continue;
            }
            $root = explode('.', $target)[0];
            if (! in_array($root, $validRoots, true)) {
                // Common typos guidance
                $hint = '';
                if (str_starts_with($root, 'contract')) {
                    $hint = ' Did you mean "contract"?';
                }
                throw new \InvalidArgumentException('Unknown mapping root "'.$root.'" in target_field "'.$target.'".'.$hint);
            }
        }
    }

    private function mappingIncludes($mappings, string $targetField): bool
    {
        foreach ($mappings as $map) {
            if ((string) ($map->target_field ?? '') === $targetField) {
                return true;
            }
        }

        return false;
    }

    /**
     * Normalize mapping target_field to canonical forms.
     * Examples:
     *  - contracts.reference   => contract.reference
     *  - accounts.balance_amount => account.balance_amount
     *  - person_phones.nu      => phone.nu
     *  - person_addresses.address => address.address
     *  - emails.email|emails.value => email.value
     */
    private function normalizeMappings($mappings, array $rootAliasMap, array $fieldAliasMap)
    {
        $normalized = [];
        foreach ($mappings as $map) {
            $clone = clone $map;
            $clone->target_field = $this->normalizeTargetField((string) ($map->target_field ?? ''), $rootAliasMap, $fieldAliasMap);
            $normalized[] = $clone;
        }

        return collect($normalized);
    }

    private function normalizeTargetField(string $target, array $rootAliasMap, array $fieldAliasMap): string
    {
        if ($target === '') {
            return $target;
        }
        $parts = explode('.', $target);
        $rootWithBracket = $parts[0] ?? '';
        // Extract optional bracket group from root (e.g., address[1]) but preserve it after aliasing
        $bracket = null;
        if (preg_match('/^(?P<base>[a-zA-Z_][a-zA-Z0-9_]*)(\[(?P<grp>[^\]]+)\])$/', $rootWithBracket, $m)) {
            $root = $m['base'];
            $bracket = $m['grp'];
        } else {
            $root = $rootWithBracket;
        }
        $field = $parts[1] ?? null;

        // Root aliases (plural to canonical) from DB
        $root = $rootAliasMap[$root] ?? $root;

        // Field aliases per root from DB
        $aliases = $fieldAliasMap[$root] ?? [];
        if ($field === null && isset($aliases['__default'])) {
            $field = $aliases['__default'];
        } elseif (isset($aliases[$field])) {
            $field = $aliases[$field];
        }

        // Rebuild
        if ($field !== null) {
            $rootOut = $bracket !== null ? ($root.'['.$bracket.']') : $root;

            return $rootOut.'.'.$field;
        }

        return $bracket !== null ? ($root.'['.$bracket.']') : $root;
    }

    /**
     * Get apply mode for a specific mapping target field, normalized via normalizeMappings beforehand.
     * Returns lowercased mode string like 'insert', 'update', 'both', 'keyref', or null if not present.
     */
    private function mappingMode($mappings, string $targetField): ?string
    {
        foreach ($mappings as $map) {
            $target = (string) ($map->target_field ?? '');
            if ($target === $targetField) {
                $mode = $map->apply_mode ?? null;

                return is_string($mode) ? strtolower($mode) : null;
            }
        }

        return null;
    }

    protected function loadImportEntityConfig(): array
    {
        $entities = ImportEntity::all();
        $rootAliasMap = [];
        $fieldAliasMap = [];
        $validRoots = [];
        $supportsMultiple = [];
        foreach ($entities as $ent) {
            $canonical = $ent->canonical_root;
            $validRoots[] = $canonical;
            foreach ((array) ($ent->aliases ?? []) as $alias) {
                $rootAliasMap[$alias] = $canonical;
            }
            // Also ensure canonical maps to itself
            $rootAliasMap[$canonical] = $canonical;
            $aliases = (array) ($ent->field_aliases ?? []);
            // Allow default field per entity via '__default'
            if (is_array($ent->fields) && count($ent->fields)) {
                $aliases['__default'] = $aliases['__default'] ?? null;
            }
            $fieldAliasMap[$canonical] = $aliases;
            $supportsMultiple[$canonical] = (bool) ($ent->supports_multiple ?? false);
        }
        // sensible defaults when DB empty
        if (empty($validRoots)) {
            $validRoots = ['person', 'contract', 'account', 'address', 'phone', 'email', 'client_case'];
            $supportsMultiple = [
                'address' => true,
                'phone' => true,
                'email' => true,
            ];
        }

        return [$rootAliasMap, $fieldAliasMap, $validRoots, $supportsMultiple];
    }

    /**
     * Get mapping options.group if provided.
     */
    private function mappingOptionGroup(object $map): ?string
    {
        $raw = $map->options ?? null;
        if (is_string($raw)) {
            try {
                $json = json_decode($raw, true, 512, JSON_THROW_ON_ERROR);

                return isset($json['group']) ? (string) $json['group'] : null;
            } catch (\Throwable $e) {
                return null;
            }
        }
        if (is_array($raw)) {
            return isset($raw['group']) ? (string) $raw['group'] : null;
        }

        return null;
    }

    /**
     * Determine if a mapped root is a grouped multi structure.
     */
    private function isGroupedMulti(mixed $data): bool
    {
        if (! is_array($data)) {
            return false;
        }
        // Consider grouped if first element is itself an array
        foreach ($data as $k => $v) {
            return is_array($v);
        }

        return false;
    }

    /**
     * Read first value from a multi-group or single map for a given root/field.
     */
    private function firstFromMulti(array $mapped, string $root, string $field): mixed
    {
        if (! isset($mapped[$root])) {
            return null;
        }
        $data = $mapped[$root];
        if ($this->isGroupedMulti($data)) {
            foreach ($data as $grp => $payload) {
                if (isset($payload[$field]) && $payload[$field] !== null && trim((string) $payload[$field]) !== '') {
                    return $payload[$field];
                }
            }

            return null;
        }

        return $data[$field] ?? null;
    }

    /**
     * Remove duplicates from grouped items by comparing a key field across groups after normalization.
     * Keeps the first occurrence and drops later duplicates. Empty/blank keys are kept only once.
     *
     * @param  array<string,array>  $grouped  e.g. ['1' => ['value' => 'a'], '2' => ['value' => 'a']]
     * @param  string  $keyField  e.g. 'value' for email, 'nu' for phone, 'address' for address
     * @param  callable|null  $normalizer  function(string|null): string|null normalizes comparison key
     * @return array<string,array>
     */
    protected function dedupeGroupedItems(array $grouped, string $keyField, ?callable $normalizer = null): array
    {
        $seen = [];
        $out = [];
        foreach ($grouped as $grp => $payload) {
            $raw = $payload[$keyField] ?? null;
            $key = $normalizer ? $normalizer($raw) : (is_null($raw) ? null : trim((string) $raw));
            $key = $key === '' ? '' : $key; // ensure empty string stays empty
            $finger = is_null($key) ? '__NULL__' : (string) $key;
            if (array_key_exists($finger, $seen)) {
                // duplicate => skip
                continue;
            }
            $seen[$finger] = true;
            $out[$grp] = $payload;
        }

        return $out;
    }

    private function findOrCreatePersonId(array $p): ?int
    {
        // Basic dedup: by tax_number, ssn, else full_name
        $query = Person::query();
        if (! empty($p['tax_number'] ?? null)) {
            $found = $query->where('tax_number', $p['tax_number'])->first();
            if ($found) {
                return $found->id;
            }
        }
        if (! empty($p['social_security_number'] ?? null)) {
            $found = Person::where('social_security_number', $p['social_security_number'])->first();
            if ($found) {
                return $found->id;
            }
        }
        // Do NOT use full_name as an identifier
        // Create person if any fields present; ensure required foreign keys
        if (! empty($p)) {
            $data = [];
            foreach (['first_name', 'last_name', 'full_name', 'tax_number', 'social_security_number', 'birthday', 'gender', 'description', 'group_id', 'type_id', 'employer'] as $k) {
                if (array_key_exists($k, $p)) {
                    $data[$k] = $p[$k];
                }
            }
            // derive full_name if missing
            if (empty($data['full_name'])) {
                $fn = trim((string) ($data['first_name'] ?? ''));
                $ln = trim((string) ($data['last_name'] ?? ''));
                if ($fn || $ln) {
                    $data['full_name'] = trim($fn.' '.$ln);
                }
            }

            // normalise birthday date
            if (!empty($data['birthday'])) {
                try {
                    $data['birthday'] = date('Y-m-d', strtotime($data['birthday']));
                } catch (Exception $e) {
                    Log::warning('ImportProcessor::findOrCreatePersonId ' . $e->getMessage());
                } 
                
            }
            // ensure required group/type ids
            $data['group_id'] = $data['group_id'] ?? $this->getDefaultPersonGroupId();
            $data['type_id'] = $data['type_id'] ?? $this->getDefaultPersonTypeId();
            $created = Person::create($data);

            return $created->id;
        }

        return null;
    }

    private function createMinimalPersonId(): int
    {
        return Person::create([
            'group_id' => $this->getDefaultPersonGroupId(),
            'type_id' => $this->getDefaultPersonTypeId(),
            // names and identifiers can be null; 'nu' will be auto-generated (unique 6-char)
        ])->id;
    }

    private function getDefaultPersonGroupId(): int
    {
        return (int) (PersonGroup::min('id') ?? 1);
    }

    private function getDefaultPersonTypeId(): int
    {
        return (int) (PersonType::min('id') ?? 1);
    }

    private function getDefaultContractTypeId(): int
    {
        return (int) (ContractType::min('id') ?? 1);
    }

    private function getDefaultAccountTypeId(): int
    {
        return (int) (AccountType::min('id') ?? 1);
    }

    private function getDefaultAddressTypeId(): int
    {
        return (int) (AddressType::min('id') ?? 1);
    }

    private function getDefaultPhoneTypeId(): int
    {
        return (int) (PhoneType::min('id') ?? 1);
    }

    // Removed getExistingPersonIdForClient: resolution should come from Contract -> ClientCase -> Person or identifiers

    private function findOrCreateClientId(int $personId): int
    {
        $client = Client::where('person_id', $personId)->first();
        if ($client) {
            return $client->id;
        }

        return Client::create(['person_id' => $personId])->id;
    }

    private function findOrCreateClientCaseId(int $clientId, int $personId, ?string $clientRef = null): int
    {
        // Prefer existing by client_ref if provided
        if ($clientRef) {
            $cc = ClientCase::where('client_id', $clientId)
                ->where('client_ref', $clientRef)
                ->first();
            if ($cc) {
                // Ensure person_id is set (if missing) when matching by client_ref
                if (! $cc->person_id) {
                    $cc->person_id = $personId;
                    $cc->save();
                }

                return $cc->id;
            }

            // client_ref was provided but not found, create new case with this client_ref
            return ClientCase::create(['client_id' => $clientId, 'person_id' => $personId, 'client_ref' => $clientRef])->id;
        }

        // No client_ref provided: reuse existing case by (client_id, person_id) if available
        $cc = ClientCase::where('client_id', $clientId)->where('person_id', $personId)->first();
        if ($cc) {
            return $cc->id;
        }

        // Create new case without client_ref
        return ClientCase::create(['client_id' => $clientId, 'person_id' => $personId, 'client_ref' => null])->id;
    }

    private function upsertEmail(int $personId, array $emailData, $mappings): array
    {
        $value = trim((string) ($emailData['value'] ?? ''));
        if ($value === '') {
            return ['action' => 'skipped', 'message' => 'No email value'];
        }
        $normalizedEmail = filter_var($value, FILTER_VALIDATE_EMAIL);
        if (! $normalizedEmail) {
            return ['action' => 'skipped', 'message' => 'Invalid email format'];
        }
        $value = $normalizedEmail;
        $emailData['value'] = $normalizedEmail;
        $existing = Email::where('person_id', $personId)->where('value', $value)->first();
        $applyInsert = [];
        $applyUpdate = [];
        foreach ($mappings as $map) {
            if (! $map->target_field) {
                continue;
            }
            $parts = explode('.', $map->target_field);
            if ($parts[0] !== 'email') {
                continue;
            }
            $field = $parts[1] ?? null;
            if (! $field) {
                continue;
            }
            $val = $emailData[$field] ?? null;
            $mode = $map->apply_mode ?? 'both';
            if (in_array($mode, ['insert', 'both'])) {
                $applyInsert[$field] = $val;
            }
            if (in_array($mode, ['update', 'both'])) {
                $applyUpdate[$field] = $val;
            }
        }
        if ($existing) {
            $changes = array_filter($applyUpdate, fn ($v) => ! is_null($v));
            if (empty($changes)) {
                return ['action' => 'skipped', 'message' => 'No email updates'];
            }
            $existing->fill($changes);
            $existing->save();

            return ['action' => 'updated', 'email' => $existing];
        } else {
            if (empty($applyInsert)) {
                return ['action' => 'skipped', 'message' => 'No email fields for insert'];
            }
            $data = array_filter($applyInsert, fn ($v) => ! is_null($v));
            $data['person_id'] = $personId;
            if (! array_key_exists('is_active', $data)) {
                $data['is_active'] = true;
            }
            $created = Email::create($data);

            return ['action' => 'inserted', 'email' => $created];
        }
    }

    private function upsertAddress(int $personId, array $addrData, $mappings): array
    {
        $addressLine = trim((string) ($addrData['address'] ?? ''));
        // Normalize whitespace: collapse multiples and tighten around separators
        $addressLine = preg_replace('/\s+/', ' ', $addressLine);
        $addressLine = preg_replace('/\s*([,;\/])\s*/', '$1 ', $addressLine);
        $addressLine = trim($addressLine);
        // Skip common placeholders or missing values
        if ($addressLine === '' || $addressLine === '0' || strcasecmp($addressLine, '#N/A') === 0 || preg_match('/^(#?n\/?a|na|null|none)$/i', $addressLine)) {
            return ['action' => 'skipped', 'message' => 'No address value'];
        }
        if (mb_strlen($addressLine) < 3) {
            return ['action' => 'skipped', 'message' => 'Invalid address value'];
        }
        // If identical address already exists anywhere, skip to avoid constraint violation
        /*$existingAny = PersonAddress::where('address', $addressLine)->first();
        if ($existingAny) {
            return ['action' => 'skipped', 'message' => 'Address already exists in database'];
        }*/
        // Default country SLO if not provided
        if (! isset($addrData['country']) || $addrData['country'] === null || $addrData['country'] === '') {
            $addrData['country'] = 'SLO';
        }

        if (!empty($addrData['city']) && empty($addrData['post_code'])) {
            if (preg_match('/^\d{3,}\s+/',trim($addrData['city']))) {
                $cleanStrCity = str($addrData['city'])->squish()->value();
                $splitCity = preg_split('/\s/', $cleanStrCity, 2);
                if (count($splitCity) >= 2) {
                    $addrData['post_code'] = $splitCity[0];
                    $addrData['city'] = $splitCity[1];
                }
            }
        }
        // Compare addresses with all spaces removed to handle whitespace variations
        /*$addressLineNoSpaces = preg_replace('/\s+/', '', $addressLine);
        
        
        $existing = PersonAddress::where('person_id', $personId)
            ->whereRaw("REPLACE(address, ' ', '') = ?", [$addressLineNoSpaces])
            ->first();*/

        // Build search query combining address, post_code and city
        $searchParts = [$addrData['address']];
        if (!empty($addrData['post_code'])) {
            $searchParts[] = $addrData['post_code'];
        }
        if (!empty($addrData['city'])) {
            $searchParts[] = $addrData['city'];
        }

        $searchQuery = implode(' ', $searchParts);
        // Use fulltext search (GIN index optimized)
        $existing = PersonAddress::query()->where('person_id', $personId)
            ->whereRaw("search_vector @@ plainto_tsquery('simple', ?)", [$searchQuery])
            ->first();
        
        $applyInsert = [];
        $applyUpdate = [];
        foreach ($mappings as $map) {
            if (! $map->target_field) {
                continue;
            }
            $parts = explode('.', $map->target_field);
            if ($parts[0] !== 'address') {
                continue;
            }
            $field = $parts[1] ?? null;
            if (! $field) {
                continue;
            }
            // Allow alias 'postal_code' in CSV mappings but persist as 'post_code' in DB
            $targetField = $field === 'postal_code' ? 'post_code' : $field;
            $val = $addrData[$field] ?? $addrData[$targetField] ?? null;
            $mode = $map->apply_mode ?? 'both';
            if (in_array($mode, ['insert', 'both'])) {
                $applyInsert[$targetField] = $val;
            }
            if (in_array($mode, ['update', 'both'])) {
                $applyUpdate[$targetField] = $val;
            }
        }
        if ($existing) {
            $changes = array_filter($applyUpdate, fn ($v) => ! is_null($v));
            if (empty($changes)) {
                return ['action' => 'skipped', 'message' => 'No address updates'];
            }
            $existing->fill($changes);
            $existing->save();

            return ['action' => 'updated', 'address' => $existing];
        } else {
            if (empty($applyInsert)) {
                return ['action' => 'skipped', 'message' => 'No address fields for insert'];
            }
            $data = array_filter($applyInsert, fn ($v) => ! is_null($v));
            $data['person_id'] = $personId;
            $data['country'] = $data['country'] ?? 'SLO';
            $data['type_id'] = $data['type_id'] ?? $this->getDefaultAddressTypeId();
            
            if (!empty($addrData['post_code']) && $addrData['post_code'] !== '0' && !isset($applyUpdate['post_code'])) {
                $data['post_code'] = $addrData['post_code'];
            }                        
             
            try {
                $created = PersonAddress::create($data);

                return ['action' => 'inserted', 'address' => $created];
            } catch (QueryException $e) {
                // If unique constraint violation, skip instead of aborting
                Log::warning('Address constraint violation during import', [
                    'person_id' => $personId,
                    'address' => $addressLine,
                    'error' => $e->getMessage(),
                ]);
                if ($e->getCode() === '23505' || str_contains($e->getMessage(), 'unique') || str_contains($e->getMessage(), 'duplicate')) {
                    return ['action' => 'skipped', 'message' => 'Address already exists (constraint violation)'];
                }

                throw $e;
            }
        }
    }

    private function upsertPhone(int $personId, array $phoneData, $mappings): array
    {
        $nu = trim((string) ($phoneData['nu'] ?? ''));
        // Strip all non-numeric characters from phone number
        $nu = preg_replace('/[^0-9]/', '', $nu);
        if ($nu === '') {
            return ['action' => 'skipped', 'message' => 'No phone value'];
        }
        if (! preg_match('/^[0-9]{6,20}$/', $nu)) {
            return ['action' => 'skipped', 'message' => 'Invalid phone value'];
        }
        $phoneData['nu'] = $nu;

        // Find existing phone by normalized number (strip non-numeric from DB values too)
        $existing = PersonPhone::where('person_id', $personId)
            ->whereRaw("REGEXP_REPLACE(nu, '[^0-9]', '', 'g') = ?", [$nu])
            ->first();
        $applyInsert = [];
        $applyUpdate = [];
        foreach ($mappings as $map) {
            if (! $map->target_field) {
                continue;
            }
            $parts = explode('.', $map->target_field);
            if ($parts[0] !== 'phone') {
                continue;
            }
            $field = $parts[1] ?? null;
            if (! $field) {
                continue;
            }
            $val = $phoneData[$field] ?? null;
            $mode = $map->apply_mode ?? 'both';
            if (in_array($mode, ['insert', 'both'])) {
                $applyInsert[$field] = $val;
            }
            if (in_array($mode, ['update', 'both'])) {
                $applyUpdate[$field] = $val;
            }
        }
        if ($existing) {
            $changes = array_filter($applyUpdate, fn ($v) => ! is_null($v));
            if (empty($changes)) {
                return ['action' => 'skipped', 'message' => 'No phone updates'];
            }
            $existing->fill($changes);
            $existing->save();

            return ['action' => 'updated', 'phone' => $existing];
        } else {
            if (empty($applyInsert)) {
                return ['action' => 'skipped', 'message' => 'No phone fields for insert'];
            }
            $data = array_filter($applyInsert, fn ($v) => ! is_null($v));
            $data['person_id'] = $personId;
            $data['type_id'] = $data['type_id'] ?? $this->getDefaultPhoneTypeId();
            // Override nu field with normalized value (digits only)
            $data['nu'] = $nu;
            // Set default phone_type to mobile enum if not provided
            if (! array_key_exists('phone_type', $data) || $data['phone_type'] === null) {
                $data['phone_type'] = \App\Enums\PersonPhoneType::Mobile;
            }
            $created = PersonPhone::create($data);

            return ['action' => 'inserted', 'phone' => $created];
        }
    }

    /**
     * After a contract is inserted/updated, attach default segment and create an activity
     * using decision_id from import/template meta. Activity note includes template name.
     */
    private function postContractActions(Import $import, Contract $contract): void
    {
        $meta = $import->meta ?? [];
        $segmentId = (int) ($meta['segment_id'] ?? 0);
        $decisionId = (int) ($meta['decision_id'] ?? 0);
        $templateName = (string) ($meta['template_name'] ?? optional($import->template)->name ?? '');
        $actionId = (int) ($meta['action_id'] ?? 0);

        // Attach segment to contract as the main (active) segment if provided
        if ($segmentId > 0) {
            // Ensure the segment exists on the client case and is active
            $ccSeg = \DB::table('client_case_segment')
                ->where('client_case_id', $contract->client_case_id)
                ->where('segment_id', $segmentId)
                ->first();
            if (! $ccSeg) {
                \DB::table('client_case_segment')->insert([
                    'client_case_id' => $contract->client_case_id,
                    'segment_id' => $segmentId,
                    'active' => true,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
            } elseif (! $ccSeg->active) {
                \DB::table('client_case_segment')
                    ->where('id', $ccSeg->id)
                    ->update(['active' => true, 'updated_at' => now()]);
            }

            // Deactivate all other segments for this contract to make this the main one
            \DB::table('contract_segment')
                ->where('contract_id', $contract->id)
                ->where('segment_id', '!=', $segmentId)
                ->update(['active' => false, 'updated_at' => now()]);

            // Upsert the selected segment as active for this contract
            $pivot = \DB::table('contract_segment')
                ->where('contract_id', $contract->id)
                ->where('segment_id', $segmentId)
                ->first();
            if ($pivot) {
                if (! $pivot->active) {
                    \DB::table('contract_segment')
                        ->where('id', $pivot->id)
                        ->update(['active' => true, 'updated_at' => now()]);
                }
            } else {
                \DB::table('contract_segment')->insert([
                    'contract_id' => $contract->id,
                    'segment_id' => $segmentId,
                    'active' => true,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
            }
        }

        // Create activity if decision provided
        if ($decisionId > 0) {
            Activity::create([
                'decision_id' => $decisionId,
                'action_id' => $actionId > 0 ? $actionId : null,
                'contract_id' => $contract->id,
                'client_case_id' => $contract->client_case_id,
                'note' => trim('Imported via template'.($templateName ? ': '.$templateName : '')),
            ]);
        }
    }

    /**
     * Attempt to reactivate a single archived contract via the latest enabled reactivate ArchiveSetting.
     * Returns array{reactivated: bool}.
     */
    protected function attemptContractReactivation(Contract $contract, ?Authenticatable $user = null): array
    {
        try {
            // Skip if already active
            if ($contract->active && ! $contract->deleted_at) {
                return ['reactivated' => false];
            }
            $setting = \App\Models\ArchiveSetting::query()
                ->where('enabled', true)
                ->where('reactivate', true)
                ->orderByDesc('id')
                ->first();
            if (! $setting) {
                return ['reactivated' => false];
            }
            $context = [
                'contract_id' => $contract->id,
                'client_case_id' => $contract->client_case_id,
            ];
            if ($contract->account) {
                $context['account_id'] = $contract->account->id;
            }
            $executor = app(\App\Services\Archiving\ArchiveExecutor::class);
            $executor->executeSetting($setting, $context, $user?->getAuthIdentifier());
            // Ensure contract flagged active (safety)
            $contract->forceFill(['active' => 1, 'deleted_at' => null])->save();

            // Activity from archive setting (if action/decision present) handled inside executor path or we can optionally create here
            if ($setting->action_id || $setting->decision_id) {
                try {
                    Activity::create([
                        'due_date' => null,
                        'amount' => null,
                        'note' => 'Ponovna aktivacija pogodba '.$contract->reference,
                        'action_id' => $setting->action_id,
                        'decision_id' => $setting->decision_id,
                        'client_case_id' => $contract->client_case_id,
                        'contract_id' => $contract->id,
                        'user_id' => $user?->getAuthIdentifier(),
                    ]);
                } catch (\Throwable $e) {
                    // Non-fatal
                }
            }

            return ['reactivated' => true];
        } catch (\Throwable $e) {
            return ['reactivated' => false];
        }
    }
}
