<?php

namespace App\Extensions\Installed\Importer\Services;

use App\Enums\{Organize, Type};
use App\Extensions\Installed\Importer\Models\{ImportJob, ImportMapping};
use App\Extensions\Installed\Importer\Services\DatabaseAdapters\{DatabaseAdapterRegistry, DatabaseImportAdapter};
use App\Models\{Announcements, Categories, Departments, Responses, Tickets, User};
use Carbon\Carbon;
use Illuminate\Support\Facades\{DB, Hash, Log};
use Illuminate\Support\Str;
use Spatie\Permission\Models\Role;

/**
 * Database Importer (adapter driven)
 *
 * Migrates data directly over a database connection using chunked processing.
 * Supported entities (in order):
 *  - Departments
 *  - Announcement Categories
 *  - Users (customers)
 *  - Operators (assigned employee role)
 *  - Tickets
 *  - Ticket Messages / Responses
 *  - Articles (Announcements)
 *
 * Features:
 *  - Chunked processing with resume support via ImportJob state
 *  - Progress tracking with ETA helper
 *  - Dry run simulation (no writes) for validation
 *  - ID mapping persisted via importer_mappings
 */
class DatabaseImporter
{
    protected ImportJob $job;
    protected DatabaseImportAdapter $adapter;
    protected int $chunkSize = 100;
    protected bool $dryRun = false;
    protected bool $isSimulation = false;

    /**
     * Ordered list of all available stages.
     */
    protected array $allStages = [
        'departments',
        'categories',
        'users',
        'operators',
        'tickets',
        'responses',
        'articles',
    ];

    /**
     * Active stages after filtering by options / table availability.
     */
    protected array $activeStages = [];

    /**
     * Runtime state (offsets, current stage, completed stages).
     */
    protected array $state = [];

    /**
     * Totals per stage.
     */
    protected array $stageTotals = [];

    /**
     * Cached mappings in memory.
     */
    protected array $mappingCache = [];

    /**
     * Cached source user records used for diagnostics.
     */
    protected array $sourceUserCache = [];

    /**
     * Cached fallback users created via ticket import.
     */
    protected array $fallbackUserCache = [];

    /**
     * Simulation counters.
     */
    protected int $simulationProcessed = 0;
    protected int $simulationFailed = 0;

    /**
     * Dry run report container.
     */
    protected array $dryRunReport = [
        'issues' => 0,
        'stage_errors' => [],
    ];

    /**
     * Dry run stage summaries.
     */
    protected array $simulationStageProgress = [];

    /**
     * Track ensured roles so we only create once.
     */
    protected array $ensuredRoles = [];

    /**
     * Create a new database import job.
     */
    public static function createJob(array $dbConfig, array $options = [], ?string $name = null): ImportJob
    {
        $defaultChunk = config('extensions.importer.chunk_size', 100) ?? 100;

        $system = $options['database_system'] ?? 'supportpal';
        $adapterClass = DatabaseAdapterRegistry::adapterClass($system);

        if (!$adapterClass) {
            throw new \InvalidArgumentException("Unknown database import adapter [{$system}].");
        }

        if ($adapterClass::isPreview()) {
            throw new \RuntimeException(sprintf('%s database import is not available yet.', $adapterClass::label()));
        }

        $adapterDefaults = $adapterClass::defaults();

        $jobOptions = array_merge([
            'import_departments' => true,
            'import_categories' => true,
            'import_users' => true,
            'import_operators' => true,
            'import_tickets' => true,
            'import_responses' => true,
            'import_articles' => true,
            'default_password' => 'changeme123',
            'force_password_reset' => true,
            'prefix' => '',
            'dry_run' => false,
        ], $adapterDefaults['options'] ?? [], $options);

        $jobOptions['database_system'] = $system;

        $systemLabel = $adapterClass::label();
        $chunkSize = $options['chunk_size']
            ?? ($adapterDefaults['chunk_size'] ?? null)
            ?? $defaultChunk;

        $sourceValue = $system === 'supportpal' ? 'supportpal' : 'database';

        return ImportJob::create([
            'name' => $name ?? sprintf('%s Import', $systemLabel),
            'source' => $sourceValue,
            'type' => 'database',
            'status' => 'pending',
            'total_rows' => 0,
            'processed_rows' => 0,
            'failed_rows' => 0,
            'current_offset' => 0,
            'chunk_size' => $chunkSize,
            'db_config' => $dbConfig,
            'options' => $jobOptions,
            'errors' => [],
        ]);
    }

    /**
     * Constructor.
     */
    public function __construct(ImportJob $job)
    {
        $this->job = $job;
        $this->bootAdapter();
    }

    /**
     * Execute the import (single chunk or full run depending on caller).
     *
     * @param bool $singleChunk Process only one chunk (UI polling mode)
     */
    public function process(bool $singleChunk = false): array
    {
        $this->isSimulation = false;
        $this->dryRun = (bool) ($this->job->options['dry_run'] ?? false);

        if ($this->job->status === 'pending') {
            $this->job->start();
        } elseif ($this->job->status === 'paused') {
            $this->job->resume();
        }

        try {
            $result = $this->executePipeline($singleChunk);

            return array_merge($result, [
                'status' => $this->job->fresh()->status,
                'errors' => $this->job->errors ?? [],
            ]);
        } catch (\Throwable $e) {
            Log::error($this->adapter::label() . ' import failed', [
                'job_id' => $this->job->id,
                'message' => $e->getMessage(),
            ]);

            if (!$this->isSimulation) {
                $this->job->fail($e->getMessage());
            }

            throw $e;
        }
    }

    /**
     * Perform a dry run simulation. No data is persisted.
     */
    public function simulate(): array
    {
        $this->isSimulation = true;
        $this->dryRun = true;
        $this->simulationFailed = 0;
        $this->simulationProcessed = 0;
        $this->dryRunReport = [
            'issues' => 0,
            'stage_errors' => [],
        ];
        $this->simulationStageProgress = [];

        $originalOptions = $this->job->options ?? [];
        $originalState = $this->job->only(['status', 'processed_rows', 'failed_rows', 'current_offset']);

        $result = $this->executePipeline(false);

        // Restore job counters/status to avoid altering persisted data.
        $this->job->fill($originalState)->save();
        $this->job->refresh();
        $options = $this->job->options ?? [];
        $options['state'] = $originalOptions['state'] ?? ($options['state'] ?? null);
        $options['dry_run_results'] = [
            'processed' => $this->simulationProcessed,
            'failed' => $this->simulationFailed,
            'issues' => $this->dryRunReport['issues'],
            'errors' => $this->dryRunReport['stage_errors'],
            'totals' => $this->stageTotals,
            'stage_progress' => array_values($this->simulationStageProgress),
        ];
        $this->job->update(['options' => $options]);

        return array_merge($result, [
            'status' => $this->job->status,
            'processed' => $this->simulationProcessed,
            'failed' => $this->simulationFailed,
            'issues' => $this->dryRunReport['issues'],
            'errors' => $this->dryRunReport['stage_errors'],
        ]);
    }

    /**
     * Initialise adapter based on job options.
     */
    protected function bootAdapter(): void
    {
        $options = $this->job->options ?? [];
        $system = $options['database_system'] ?? 'supportpal';

        try {
            $adapter = DatabaseAdapterRegistry::resolve($system);
            $adapter->boot($this->job->db_config ?? [], $options);
        } catch (\Throwable $e) {
            throw new \RuntimeException("Could not connect to source database: {$e->getMessage()}", 0, $e);
        }

        $this->adapter = $adapter;
    }

    /**
     * Execute pipeline (shared by import and simulation).
     */
    protected function executePipeline(bool $singleChunk = false): array
    {
        $this->prepareJob();

        if (empty($this->activeStages)) {
            if (!$this->isSimulation) {
                $this->job->complete('Nothing to import. All selected entities were empty.');
            }

            return [
                'processed' => $this->job->processed_rows,
                'failed' => $this->job->failed_rows,
                'total' => $this->job->total_rows,
                'stage' => null,
                'eta' => null,
            ];
        }

        do {
            if (!$this->isSimulation && $this->job->fresh()->status !== 'processing') {
                break;
            }

            $stageResult = $this->processCurrentStageChunk();

            if ($singleChunk) {
                break;
            }

            if (!$stageResult['hasMore'] && !$this->hasRemainingWork()) {
                break;
            }
        } while ($this->hasRemainingWork());

        if (!$this->isSimulation && !$this->hasRemainingWork() && $this->job->status === 'processing') {
            $this->job->complete($this->buildSummary());
        }

        return [
            'processed' => $this->isSimulation ? $this->simulationProcessed : $this->job->processed_rows,
            'failed' => $this->isSimulation ? $this->simulationFailed : $this->job->failed_rows,
            'total' => $this->job->total_rows,
            'stage' => $this->state['current_stage'] ?? null,
            'eta' => $this->isSimulation ? null : $this->job->etaForHumans(),
        ];
    }

    /**
     * Prepare job state, totals, chunk size, and operator cache.
     */
    protected function prepareJob(): void
    {
        $this->chunkSize = $this->job->chunk_size ?? 100;
        if ($this->chunkSize <= 0) {
            $this->chunkSize = 100;
        }

        $this->mappingCache = [];
        $this->sourceUserCache = [];

        $adapterStages = $this->adapter->stages();
        if (!empty($adapterStages)) {
            $this->allStages = array_values($adapterStages);
        }

        $this->activeStages = $this->enabledStages();

        $options = $this->job->options ?? [];

        if (!isset($options['state'])) {
            $options['state'] = [
                'current_stage' => $this->activeStages[0] ?? null,
                'offsets' => array_fill_keys($this->allStages, 0),
                'completed' => [],
            ];
        }

        $this->state = $options['state'];

        $this->stageTotals = $options['totals'] ?? [];
        if (empty($this->stageTotals)) {
            $this->stageTotals = $this->calculateTotals();
            $options['totals'] = $this->stageTotals;
        }

        $totalRows = array_sum(array_intersect_key($this->stageTotals, array_flip($this->activeStages)));
        $this->job->total_rows = $totalRows;

        if (!$this->isSimulation) {
            $this->job->update([
                'total_rows' => $totalRows,
                'options' => $options,
            ]);
        } else {
            $this->job->setAttribute('options', $options);
        }

        $this->ensureCurrentStage();
    }

    /**
     * Determine enabled stages.
     */
    protected function enabledStages(): array
    {
        $options = $this->job->options ?? [];

        $stageMap = [
            'departments' => 'import_departments',
            'categories' => 'import_categories',
            'users' => 'import_users',
            'operators' => 'import_operators',
            'tickets' => 'import_tickets',
            'responses' => 'import_responses',
            'articles' => 'import_articles',
        ];

        $supported = array_flip($this->adapter->stages());
        $stages = [];
        foreach ($this->allStages as $stage) {
            if (!isset($supported[$stage])) {
                continue;
            }
            $optionKey = $stageMap[$stage] ?? null;
            $enabled = $optionKey ? ($options[$optionKey] ?? true) : true;
            if ($enabled) {
                $stages[] = $stage;
            }
        }

        return $stages;
    }

    /**
     * Calculate total counts per stage using adapter lookup.
     */
    protected function calculateTotals(): array
    {
        $totals = array_fill_keys($this->allStages, 0);

        foreach ($this->activeStages as $stage) {
            $totals[$stage] = $this->adapter->count($stage);
        }

        return $totals;
    }

    /**
     * Ensure current_stage points to the next pending stage.
     */
    protected function ensureCurrentStage(): void
    {
        foreach ($this->activeStages as $stage) {
            $offset = $this->state['offsets'][$stage] ?? 0;
            $total = $this->stageTotals[$stage] ?? 0;

            if ($total === 0) {
                $this->recordSimulationChunk($stage, [
                    'processed' => 0,
                    'failed' => 0,
                    'hasMore' => false,
                ], true);
                $this->markStageComplete($stage, false);
                continue;
            }

            if ($offset < $total) {
                $this->state['current_stage'] = $stage;
                return;
            }

            $this->markStageComplete($stage, false);
        }

        $this->state['current_stage'] = null;
    }

    /**
     * Does remaining work exist?
     */
    protected function hasRemainingWork(): bool
    {
        foreach ($this->activeStages as $stage) {
            $total = $this->stageTotals[$stage] ?? 0;
            $offset = $this->state['offsets'][$stage] ?? 0;

            if ($total > 0 && $offset < $total) {
                return true;
            }
        }

        return false;
    }

    /**
     * Process the current stage chunk.
     */
    protected function processCurrentStageChunk(): array
    {
        $stage = $this->state['current_stage'] ?? null;

        if (!$stage) {
            return [
                'processed' => 0,
                'failed' => 0,
                'hasMore' => false,
                'next_offset' => 0,
            ];
        }

        $result = match ($stage) {
            'departments' => $this->processDepartmentsChunk(),
            'categories' => $this->processCategoriesChunk(),
            'users' => $this->processUsersChunk(),
            'operators' => $this->processOperatorsChunk(),
            'tickets' => $this->processTicketsChunk(),
            'responses' => $this->processResponsesChunk(),
            'articles' => $this->processArticlesChunk(),
            default => ['processed' => 0, 'failed' => 0, 'hasMore' => false, 'next_offset' => 0],
        };

        $this->recordSimulationChunk($stage, $result);

        if (!$result['hasMore']) {
            $this->markStageComplete($stage, true);
        }

        $this->persistState();

        return $result;
    }

    /**
     * Mark a stage as complete and set next stage.
     */
    protected function markStageComplete(string $stage, bool $recalculateNext): void
    {
        $completed = $this->state['completed'] ?? [];
        if (!in_array($stage, $completed, true)) {
            $completed[] = $stage;
        }
        $this->state['completed'] = $completed;

        if ($this->isSimulation && !isset($this->simulationStageProgress[$stage])) {
            $this->recordSimulationChunk($stage, [
                'processed' => 0,
                'failed' => 0,
                'hasMore' => false,
            ], true);
        }

        if (!$recalculateNext) {
            return;
        }

        foreach ($this->activeStages as $candidate) {
            if (!in_array($candidate, $completed, true)) {
                $this->state['current_stage'] = $candidate;
                return;
            }
        }

        $this->state['current_stage'] = null;
    }

    /**
     * Persist state to job options (skip during simulation).
     */
    protected function persistState(): void
    {
        if ($this->isSimulation) {
            return;
        }

        $options = $this->job->options ?? [];
        $options['state'] = $this->state;
        $this->job->update(['options' => $options]);
    }

    /**
     * Track per-stage progress during simulation.
     */
    protected function recordSimulationChunk(string $stage, array $result, bool $skipped = false): void
    {
        if (!$this->isSimulation) {
            return;
        }

        $total = $this->stageTotals[$stage] ?? 0;

        if (!isset($this->simulationStageProgress[$stage])) {
            $this->simulationStageProgress[$stage] = [
                'stage' => $stage,
                'processed' => 0,
                'failed' => 0,
                'total' => $total,
                'completed' => false,
                'skipped' => $skipped || $total === 0,
            ];
        }

        if (!$skipped) {
            $this->simulationStageProgress[$stage]['processed'] += $result['processed'] ?? 0;
            $this->simulationStageProgress[$stage]['failed'] += $result['failed'] ?? 0;
        }

        if ($skipped || !($result['hasMore'] ?? true)) {
            $this->simulationStageProgress[$stage]['completed'] = true;
        }
    }

    /**
     * Increment processed/failed counters (simulation aware).
     */
    protected function advanceCounters(int $successes, int $failures = 0): void
    {
        if ($this->isSimulation) {
            $this->simulationProcessed += $successes;
            $this->simulationFailed += $failures;
            return;
        }

        if ($successes > 0) {
            $this->job->increment('processed_rows', $successes);
            $this->job->increment('current_offset', $successes);
        }

        if ($failures > 0) {
            $this->job->increment('failed_rows', $failures);
        }

        $this->job->touch();
    }

    /**
     * Append an error to the job (or simulation report).
     */
    protected function addError(string $type, $id, string $message, array $data): void
    {
        if ($this->isSimulation) {
            $this->dryRunReport['issues']++;

            if (count($this->dryRunReport['stage_errors']) < 25) {
                $this->dryRunReport['stage_errors'][] = [
                    'type' => $type,
                    'source_id' => $id,
                    'error' => $message,
                ];
            }

            return;
        }

        $errors = $this->job->errors ?? [];
        $errors[] = [
            'type' => $type,
            'source_id' => $id,
            'error' => $message,
            'data' => $data,
        ];

        $this->job->update(['errors' => $errors]);
        Log::warning($this->adapter::label() . " import error ({$type} #{$id}): {$message}");
    }

    /**
     * Remember mapping (simulation aware).
     */
    protected function rememberMapping(string $entityType, $sourceId, ?int $targetId, array $meta = []): void
    {
        $sourceKey = (string) $sourceId;

        if ($this->dryRun || $this->isSimulation) {
            if ($targetId === null) {
                $targetId = -abs((int) crc32($entityType . $sourceKey));
            }

            $this->mappingCache[$entityType][$sourceKey] = $targetId;
            return;
        }

        $this->mappingCache[$entityType][$sourceKey] = $targetId;
        ImportMapping::remember(
            $this->job->id,
            $entityType,
            $sourceKey,
            $targetId,
            $meta
        );
    }

    /**
     * Resolve mapped ID from cache/database.
     */
    protected function getMappedId(string $entityType, $sourceId): ?int
    {
        $sourceKey = (string) $sourceId;

        if (isset($this->mappingCache[$entityType][$sourceKey])) {
            return $this->mappingCache[$entityType][$sourceKey];
        }

        $targetId = ImportMapping::resolveId($this->job->id, $entityType, $sourceKey);
        if ($targetId !== null) {
            $this->mappingCache[$entityType][$sourceKey] = $targetId;
        }

        return $targetId;
    }

    /**
     * Process departments chunk.
     */
    protected function processDepartmentsChunk(): array
    {
        $stage = 'departments';
        $offset = $this->state['offsets'][$stage] ?? 0;
        $total = $this->stageTotals[$stage] ?? 0;

        if ($total === 0) {
            return [
                'processed' => 0,
                'failed' => 0,
                'hasMore' => false,
                'next_offset' => $offset,
            ];
        }

        $rows = $this->adapter->fetch('departments', $offset, $this->chunkSize);

        if (empty($rows)) {
            $this->state['offsets'][$stage] = $offset;
            return [
                'processed' => 0,
                'failed' => 0,
                'hasMore' => false,
                'next_offset' => $offset,
            ];
        }

        $successes = 0;
        $failures = 0;

        $processRow = function (array $row) use (&$successes, &$failures) {
            $sourceId = $row['id'] ?? null;
            if ($sourceId === null) {
                $failures++;
                $this->addError('department', 'unknown', $this->adapter::label() . ' department id missing.', $row);
                return;
            }

            $existing = $this->getMappedId('department', $sourceId);
            if ($existing !== null && !$this->isSimulation && !$this->dryRun) {
                $successes++;
                return;
            }

            if ($this->dryRun || $this->isSimulation) {
                $this->rememberMapping('department', $sourceId, null);
                $successes++;
                return;
            }

            try {
                $department = Departments::create([
                    'department_name' => $row['name'] ?? "Department {$sourceId}",
                    'department_description' => $row['description'] ?? null,
                    'slug' => $this->uniqueDepartmentSlug($row['name'] ?? "department-{$sourceId}"),
                    'allows_high_priority' => (bool) ($row['priority_enabled'] ?? true),
                    'cc_enabled' => (bool) ($row['cc_enabled'] ?? false),
                    'is_public' => (bool) ($row['public'] ?? true),
                    'is_disabled' => (bool) ($row['disabled'] ?? false),
                    'department_email' => $row['email'] ?? null,
                    'soft_deleted' => 0,
                ]);

                $this->rememberMapping('department', $sourceId, $department->id);
                $successes++;
            } catch (\Throwable $e) {
                $failures++;
                $this->addError('department', $sourceId, $e->getMessage(), $row);
            }
        };

        if ($this->dryRun || $this->isSimulation) {
            foreach ($rows as $row) {
                $processRow($row);
            }
        } else {
            DB::transaction(function () use ($rows, $processRow) {
                foreach ($rows as $row) {
                    $processRow($row);
                }
            });
        }

        $offset += count($rows);
        $this->state['offsets'][$stage] = $offset;

        $this->advanceCounters($successes, $failures);

        return [
            'processed' => $successes,
            'failed' => $failures,
            'hasMore' => $offset < $total,
            'next_offset' => $offset,
        ];
    }

    /**
     * Process categories chunk (announcement categories).
     */
    protected function processCategoriesChunk(): array
    {
        $stage = 'categories';
        $offset = $this->state['offsets'][$stage] ?? 0;
        $total = $this->stageTotals[$stage] ?? 0;

        if ($total === 0) {
            return [
                'processed' => 0,
                'failed' => 0,
                'hasMore' => false,
                'next_offset' => $offset,
            ];
        }

        $rows = $this->adapter->fetch('categories', $offset, $this->chunkSize);

        if (empty($rows)) {
            $this->state['offsets'][$stage] = $offset;
            return [
                'processed' => 0,
                'failed' => 0,
                'hasMore' => false,
                'next_offset' => $offset,
            ];
        }

        $successes = 0;
        $failures = 0;

        $processRow = function (array $row) use (&$successes, &$failures) {
            $sourceId = $row['id'] ?? null;
            if ($sourceId === null) {
                $failures++;
                $this->addError('category', 'unknown', $this->adapter::label() . ' category id missing.', $row);
                return;
            }

            $existing = $this->getMappedId('category', $sourceId);
            if ($existing !== null && !$this->dryRun && !$this->isSimulation) {
                $successes++;
                return;
            }

            if ($this->dryRun || $this->isSimulation) {
                $this->rememberMapping('category', $sourceId, null);
                $successes++;
                return;
            }

            try {
                $name = $row['name'] ?? "Category {$sourceId}";
                $category = Categories::create([
                    'category_name' => $name,
                    'uri' => $this->uniqueCategoryUri($name, $sourceId),
                    'type' => Type::Announcements->value,
                    'display' => (int) ($row['status'] ?? 1),
                ]);

                $this->rememberMapping('category', $sourceId, $category->id);
                $successes++;
            } catch (\Throwable $e) {
                $failures++;
                $this->addError('category', $sourceId, $e->getMessage(), $row);
            }
        };

        if ($this->dryRun || $this->isSimulation) {
            foreach ($rows as $row) {
                $processRow($row);
            }
        } else {
            DB::transaction(function () use ($rows, $processRow) {
                foreach ($rows as $row) {
                    $processRow($row);
                }
            });
        }

        $offset += count($rows);
        $this->state['offsets'][$stage] = $offset;

        $this->advanceCounters($successes, $failures);

        return [
            'processed' => $successes,
            'failed' => $failures,
            'hasMore' => $offset < $total,
            'next_offset' => $offset,
        ];
    }

    /**
     * Process customers chunk.
     */
    protected function processUsersChunk(): array
    {
        $stage = 'users';
        $offset = $this->state['offsets'][$stage] ?? 0;
        $total = $this->stageTotals[$stage] ?? 0;

        if ($total === 0) {
            return [
                'processed' => 0,
                'failed' => 0,
                'hasMore' => false,
                'next_offset' => $offset,
            ];
        }

        $rows = $this->adapter->fetch('users', $offset, $this->chunkSize);

        if (empty($rows)) {
            $this->state['offsets'][$stage] = $offset;
            return [
                'processed' => 0,
                'failed' => 0,
                'hasMore' => false,
                'next_offset' => $offset,
            ];
        }

        $successes = 0;
        $failures = 0;

        $processRow = function (array $row) use (&$successes, &$failures) {
            $sourceId = $row['id'] ?? null;
            if ($sourceId === null) {
                $failures++;
                $this->addError('user', 'unknown', $this->adapter::label() . ' user id missing.', $row);
                return;
            }

            $name = $this->resolveUserName($row);
            $email = strtolower(trim($row['email'] ?? ''));
            if (empty($email)) {
                if ($this->isBotUser($name)) {
                    $email = $this->generatePlaceholderEmail($sourceId, $name);
                } else {
                    $failures++;
                    $this->addError('user', $sourceId, 'User email missing — cannot import.', $row);
                    return;
                }
            }

            $existing = $this->getMappedId('user', $sourceId);
            if ($existing !== null && !$this->dryRun && !$this->isSimulation) {
                $successes++;
                return;
            }

            if ($this->dryRun || $this->isSimulation) {
                $this->rememberMapping('user', $sourceId, null);
                $successes++;
                return;
            }

            try {
                $user = User::where('email', $email)->first();

                if (!$user) {
                    $user = User::create([
                        'name' => $this->resolveUserName($row),
                        'email' => $email,
                        'password' => Hash::make($this->job->options['default_password'] ?? 'changeme123'),
                        'company' => $row['organisation_name'] ?? ($row['company'] ?? null),
                        'biography' => $row['biography'] ?? '',
                        'account_manager' => (string) ($row['account_manager'] ?? '0'),
                        'force_password_reset' => $this->job->options['force_password_reset'] ?? true,
                        'email_verified_at' => !empty($row['confirmed']) ? now() : null,
                    ]);
                }

                $this->rememberMapping('user', $sourceId, $user->id);
                $this->ensureRole('customer');
                if (!$user->hasRole('customer')) {
                    $user->assignRole('customer');
                }

                $successes++;
            } catch (\Throwable $e) {
                $failures++;
                $this->addError('user', $sourceId, $e->getMessage(), $row);
            }
        };

        if ($this->dryRun || $this->isSimulation) {
            foreach ($rows as $row) {
                $processRow($row);
            }
        } else {
            DB::transaction(function () use ($rows, $processRow) {
                foreach ($rows as $row) {
                    $processRow($row);
                }
            });
        }

        $offset += count($rows);
        $this->state['offsets'][$stage] = $offset;

        $this->advanceCounters($successes, $failures);

        return [
            'processed' => $successes,
            'failed' => $failures,
            'hasMore' => $offset < $total,
            'next_offset' => $offset,
        ];
    }

    /**
     * Process operators chunk (assign employee role).
     */
    protected function processOperatorsChunk(): array
    {
        $stage = 'operators';
        $offset = $this->state['offsets'][$stage] ?? 0;
        $total = $this->stageTotals[$stage] ?? 0;

        if ($total === 0) {
            return [
                'processed' => 0,
                'failed' => 0,
                'hasMore' => false,
                'next_offset' => $offset,
            ];
        }

        $rows = $this->adapter->fetch('operators', $offset, $this->chunkSize);

        if (empty($rows)) {
            $this->state['offsets'][$stage] = $offset;
            return [
                'processed' => 0,
                'failed' => 0,
                'hasMore' => false,
                'next_offset' => $offset,
            ];
        }

        $successes = 0;
        $failures = 0;

        $processRow = function (array $row) use (&$successes, &$failures) {
            $operatorId = $row['id'] ?? null;
            $userId = $row['user_id'] ?? null;

            if ($operatorId === null || $userId === null) {
                $failures++;
                $this->addError('operator', $operatorId ?? $userId ?? 'unknown', 'Operator record missing identifiers.', $row);
                return;
            }

            $existingUser = $this->getMappedId('user', $userId);
            if ($existingUser !== null && !$this->dryRun && !$this->isSimulation) {
                $this->rememberMapping('operator', $operatorId, $existingUser);

                $this->ensureRole('employee');
                $existingModel = User::find($existingUser);
                if ($existingModel && !$existingModel->hasAnyRole(['employee', 'admin', 'superadmin'])) {
                    $existingModel->assignRole('employee');
                }

                $successes++;
                return;
            }

            if ($this->dryRun || $this->isSimulation) {
                $this->rememberMapping('user', $userId, null);
                $this->rememberMapping('operator', $operatorId, null);
                $successes++;
                return;
            }

            try {
                $email = strtolower(trim($row['email'] ?? $row['user_email'] ?? ''));

                if (empty($email)) {
                    $failures++;
                    $this->addError('operator', $operatorId, 'Operator email missing — cannot create employee.', $row);
                    return;
                }

                $user = User::where('email', $email)->first();

                if (!$user) {
                    $user = User::create([
                        'name' => $this->resolveOperatorName($row),
                        'email' => $email,
                        'password' => Hash::make($this->job->options['default_password'] ?? 'changeme123'),
                        'biography' => $row['biography'] ?? '',
                        'account_manager' => (string) ($row['account_manager'] ?? '0'),
                        'force_password_reset' => $this->job->options['force_password_reset'] ?? true,
                        'email_verified_at' => now(),
                    ]);
                }

                $this->ensureRole('employee');
                if (!$user->hasAnyRole(['employee', 'admin', 'superadmin'])) {
                    $user->assignRole('employee');
                }

                $this->rememberMapping('user', $userId, $user->id);
                $this->rememberMapping('operator', $operatorId, $user->id);

                $successes++;
            } catch (\Throwable $e) {
                $failures++;
                $this->addError('operator', $operatorId, $e->getMessage(), $row);
            }
        };

        if ($this->dryRun || $this->isSimulation) {
            foreach ($rows as $row) {
                $processRow($row);
            }
        } else {
            DB::transaction(function () use ($rows, $processRow) {
                foreach ($rows as $row) {
                    $processRow($row);
                }
            });
        }

        $offset += count($rows);
        $this->state['offsets'][$stage] = $offset;

        $this->advanceCounters($successes, $failures);

        return [
            'processed' => $successes,
            'failed' => $failures,
            'hasMore' => $offset < $total,
            'next_offset' => $offset,
        ];
    }

    /**
     * Process tickets chunk.
     */
    protected function processTicketsChunk(): array
    {
        $stage = 'tickets';
        $offset = $this->state['offsets'][$stage] ?? 0;
        $total = $this->stageTotals[$stage] ?? 0;

        if ($total === 0) {
            return [
                'processed' => 0,
                'failed' => 0,
                'hasMore' => false,
                'next_offset' => $offset,
            ];
        }

        $rows = $this->adapter->fetch('tickets', $offset, $this->chunkSize);

        if (empty($rows)) {
            $this->state['offsets'][$stage] = $offset;
            return [
                'processed' => 0,
                'failed' => 0,
                'hasMore' => false,
                'next_offset' => $offset,
            ];
        }

        $successes = 0;
        $failures = 0;

        $processRow = function (array $row) use (&$successes, &$failures) {
            $ticketId = $row['id'] ?? null;

            if ($ticketId === null) {
                $failures++;
                $this->addError('ticket', 'unknown', $this->adapter::label() . ' ticket id missing.', $row);
                return;
            }

            $existing = $this->getMappedId('ticket', $ticketId);
            if ($existing !== null && !$this->dryRun && !$this->isSimulation) {
                $successes++;
                return;
            }

            $userId = $row['user_id'] ?? null;
            $mappedUserId = $userId ? $this->getMappedId('user', $userId) : null;
            $isOperatorRequester = $userId ? $this->isOperatorUser((int) $userId) : false;
            $fallbackNote = null;

            if ($mappedUserId === null) {
                $fallback = $this->importTicketFallbackRequester($row, $isOperatorRequester, $userId);
                if ($fallback !== null) {
                    $mappedUserId = $fallback['user_id'];
                    $fallbackNote = $fallback['note'] ?? null;
                }
            }

            if ($mappedUserId === null) {
                $message = $this->describeMissingUserMapping((int) ($userId ?? 0), $row['number'] ?? null);

                if ($this->dryRun || $this->isSimulation) {
                    $this->addError('ticket', $ticketId, $message, $row);
                    $failures++;
                    return;
                }

                $mappedUserId = $this->importUserOnDemand((int) $userId);
                if ($mappedUserId === null) {
                    $failures++;
                    $this->addError('ticket', $ticketId, $message, $row);
                    return;
                }
            }

            $departmentId = $row['department_id'] ?? null;
            $mappedDepartmentId = $departmentId ? $this->getMappedId('department', $departmentId) : null;

            if ($mappedDepartmentId === null) {
                $departmentsFallback = Departments::first();
                if (!$departmentsFallback && !$this->dryRun && !$this->isSimulation) {
                    $failures++;
                    $this->addError('ticket', $ticketId, 'No target department available.', $row);
                    return;
                }

                $mappedDepartmentId = $departmentsFallback?->id;
            }

            $assignedId = $row['assigned_to'] ?? $row['assigned'] ?? null;
            $mappedAssigned = null;
            if ($assignedId !== null) {
                $mappedAssigned = $this->getMappedId('operator', $assignedId)
                    ?? $this->getMappedId('user', $assignedId);
            }

            if ($this->dryRun || $this->isSimulation) {
                $this->rememberMapping('ticket', $ticketId, null);
                $successes++;
                return;
            }

            try {
                $ticket = new Tickets([
                    'user_id' => $mappedUserId,
                    'subject' => $row['subject'] ?? "Ticket {$ticketId}",
                    'message' => $this->extractTicketMessage($row),
                    'status' => $this->mapStatus($row['status_id'] ?? null),
                    'priority' => $this->mapPriority($row['priority_id'] ?? null),
                    'department_id' => $mappedDepartmentId,
                    'cc' => $this->formatTicketCc($row['cc_list'] ?? ($row['cc_emails'] ?? null)),
                    'assigned' => $mappedAssigned ?? 0,
                    'public_hash' => $this->generatePublicHash(),
                    'organize' => Organize::Ticket->value,
                    'date_closed' => $this->resolveTicketClosedAt($row),
                    'updated_by_client_at' => $this->normalizeTimestamp($row['last_reply_at'] ?? null),
                    'first_employee_reply' => $this->normalizeTimestamp($row['first_staff_reply_at'] ?? null),
                ]);

                $createdAt = $this->normalizeTimestamp($row['created_at'] ?? null) ?? now();
                $updatedAt = $this->normalizeTimestamp($row['updated_at'] ?? null) ?? $createdAt;

                $ticket->created_at = $createdAt;
                $ticket->updated_at = $updatedAt;
                $ticket->save();

                if (!$this->dryRun && !$this->isSimulation) {
                    $this->importTicketHistoryNotes($ticket, $row['assignment_history'] ?? []);
                    if ($fallbackNote) {
                        $this->createTicketNote($ticket, $fallbackNote, $row);
                    }
                }

                $this->rememberMapping('ticket', $ticketId, $ticket->id);
                $successes++;
            } catch (\Throwable $e) {
                $failures++;
                $this->addError('ticket', $ticketId, $e->getMessage(), $row);
            }
        };

        if ($this->dryRun || $this->isSimulation) {
            foreach ($rows as $row) {
                $processRow($row);
            }
        } else {
            DB::transaction(function () use ($rows, $processRow) {
                foreach ($rows as $row) {
                    $processRow($row);
                }
            });
        }

        $offset += count($rows);
        $this->state['offsets'][$stage] = $offset;

        $this->advanceCounters($successes, $failures);

        return [
            'processed' => $successes,
            'failed' => $failures,
            'hasMore' => $offset < $total,
            'next_offset' => $offset,
        ];
    }

    /**
     * Process responses chunk.
     */
    protected function processResponsesChunk(): array
    {
        $stage = 'responses';
        $offset = $this->state['offsets'][$stage] ?? 0;
        $total = $this->stageTotals[$stage] ?? 0;

        if ($total === 0) {
            return [
                'processed' => 0,
                'failed' => 0,
                'hasMore' => false,
                'next_offset' => $offset,
            ];
        }

        $rows = $this->adapter->fetch('responses', $offset, $this->chunkSize);

        if (empty($rows)) {
            $this->state['offsets'][$stage] = $offset;
            return [
                'processed' => 0,
                'failed' => 0,
                'hasMore' => false,
                'next_offset' => $offset,
            ];
        }

        $successes = 0;
        $failures = 0;

        $processRow = function (array $row) use (&$successes, &$failures) {
            $messageId = $row['id'] ?? null;
            $ticketId = $row['ticket_id'] ?? null;

            if ($ticketId === null || $messageId === null) {
                $failures++;
                $this->addError('response', $messageId ?? 'unknown', 'Missing ticket/message identifiers.', $row);
                return;
            }

            $mappedTicketId = $this->getMappedId('ticket', $ticketId);
            if ($mappedTicketId === null) {
                $failures++;
                $this->addError('response', $messageId, "Ticket #{$ticketId} not imported — response skipped.", $row);
                return;
            }

            $userId = $row['user_id'] ?? null;
            $isNote = (int) ($row['type'] ?? 0) === 1;
            $mappedUserId = $userId ? $this->getMappedId('user', $userId) : null;

            if ($mappedUserId === null) {
                if ($this->dryRun || $this->isSimulation) {
                    $this->addError('response', $messageId, 'User mapping missing — response would fail.', $row);
                    $failures++;
                    return;
                }

                $mappedUserId = $this->importUserOnDemand((int) $userId);
                if ($mappedUserId === null) {
                    $failures++;
                    $this->addError('response', $messageId, 'Unable to resolve responder.', $row);
                    return;
                }
            }

            $content = $row['purified_text'] ?? $row['text'] ?? $row['message'] ?? '';
            $isStaff = $this->isOperatorUser($userId);

            if (!$isNote) {
                $operatorOpenedTicket = $this->shouldSkipOperatorOpeningResponse(
                    $mappedTicketId,
                    $userId !== null ? (int) $userId : null,
                    $mappedUserId,
                    $content,
                    $row
                );
                if ($operatorOpenedTicket) {
                    $successes++;
                    return;
                }
            }

            if ($this->dryRun || $this->isSimulation) {
                $successes++;
                return;
            }

            try {
                $response = new Responses([
                    'ticket_number' => $mappedTicketId,
                    'user_id' => $mappedUserId,
                    'content' => $content,
                    'employee_response' => $isStaff,
                    'is_note' => $isNote,
                    'organize' => Organize::Ticket->value,
                    'ip_address' => $row['ip_address'] ?? null,
                ]);

                $createdAt = $this->normalizeTimestamp($row['created_at'] ?? null) ?? now();
                $updatedAt = $this->normalizeTimestamp($row['updated_at'] ?? null) ?? $createdAt;

                $response->created_at = $createdAt;
                $response->updated_at = $updatedAt;
                $response->save();

                // Update original ticket message if empty and this is the first public message
                if (!$isNote && trim($content) !== '') {
                    $ticket = Tickets::find($mappedTicketId);
                    if ($ticket && trim((string) $ticket->message) === '') {
                        $ticket->update(['message' => $content]);
                    }
                }

                $successes++;
            } catch (\Throwable $e) {
                $failures++;
                $this->addError('response', $messageId, $e->getMessage(), $row);
            }
        };

        if ($this->dryRun || $this->isSimulation) {
            foreach ($rows as $row) {
                $processRow($row);
            }
        } else {
            DB::transaction(function () use ($rows, $processRow) {
                foreach ($rows as $row) {
                    $processRow($row);
                }
            });
        }

        $offset += count($rows);
        $this->state['offsets'][$stage] = $offset;

        $this->advanceCounters($successes, $failures);

        return [
            'processed' => $successes,
            'failed' => $failures,
            'hasMore' => $offset < $total,
            'next_offset' => $offset,
        ];
    }

    /**
     * Process articles chunk (announcements).
     */
    protected function processArticlesChunk(): array
    {
        $stage = 'articles';
        $offset = $this->state['offsets'][$stage] ?? 0;
        $total = $this->stageTotals[$stage] ?? 0;

        if ($total === 0) {
            return [
                'processed' => 0,
                'failed' => 0,
                'hasMore' => false,
                'next_offset' => $offset,
            ];
        }

        $rows = $this->adapter->fetch('articles', $offset, $this->chunkSize);

        if (empty($rows)) {
            $this->state['offsets'][$stage] = $offset;
            return [
                'processed' => 0,
                'failed' => 0,
                'hasMore' => false,
                'next_offset' => $offset,
            ];
        }

        $successes = 0;
        $failures = 0;

        $processRow = function (array $row) use (&$successes, &$failures) {
            $articleId = $row['id'] ?? null;
            if ($articleId === null) {
                $failures++;
                $this->addError('article', 'unknown', $this->adapter::label() . ' article id missing.', $row);
                return;
            }

            $categoryId = $row['category_id'] ?? null;
            $mappedCategoryId = null;

            if ($categoryId !== null && $categoryId !== '') {
                $mappedCategoryId = $this->getMappedId('category', $categoryId);
            }

            if ($mappedCategoryId === null) {
                $failures++;
                $this->addError('article', $articleId, 'Article category not imported.', $row);
                return;
            }

            if ($this->dryRun || $this->isSimulation) {
                $successes++;
                return;
            }

            try {
                $article = new Announcements([
                    'parent_category' => $mappedCategoryId,
                    'title' => $row['title'] ?? $row['question'] ?? "Article {$articleId}",
                    'slug' => $this->uniqueAnnouncementSlug($row['slug'] ?? $row['title'] ?? "article-{$articleId}"),
                    'image' => $row['image'] ?? '',
                    'content' => $this->extractArticleContent($row),
                    'tags' => $row['tags'] ?? '',
                    'views' => $row['views'] ?? 0,
                    'likes' => $row['likes'] ?? 0,
                    'draft' => (int) ($row['status'] ?? 1) === 1 ? 0 : 1,
                    'private' => (int) ($row['visibility'] ?? 0),
                    'author_id' => 1,
                ]);

                $createdAt = $this->normalizeTimestamp($row['created_at'] ?? null) ?? now();
                $updatedAt = $this->normalizeTimestamp($row['updated_at'] ?? null) ?? $createdAt;

                $article->created_at = $createdAt;
                $article->updated_at = $updatedAt;
                $article->save();

                $successes++;
            } catch (\Throwable $e) {
                $failures++;
                $this->addError('article', $articleId, $e->getMessage(), $row);
            }
        };

        if ($this->dryRun || $this->isSimulation) {
            foreach ($rows as $row) {
                $processRow($row);
            }
        } else {
            DB::transaction(function () use ($rows, $processRow) {
                foreach ($rows as $row) {
                    $processRow($row);
                }
            });
        }

        $offset += count($rows);
        $this->state['offsets'][$stage] = $offset;

        $this->advanceCounters($successes, $failures);

        return [
            'processed' => $successes,
            'failed' => $failures,
            'hasMore' => $offset < $total,
            'next_offset' => $offset,
        ];
    }

    /**
     * Build completion summary.
     */
    protected function buildSummary(): string
    {
        $imported = $this->job->processed_rows;
        $failed = $this->job->failed_rows;

        $systemLabel = $this->adapter::label();

        return sprintf(
            '%s import finished. Imported %d records across %d stages. %d failed.',
            $systemLabel,
            $imported,
            count($this->activeStages),
            $failed
        );
    }

    /**
     * Determine if source user is an operator.
     */
    protected function isOperatorUser(?int $userId): bool
    {
        if ($userId === null) {
            return false;
        }

        return $this->adapter->isOperatorUser((int) $userId);
    }

    /**
     * Generate unique slug for departments.
     */
    protected function uniqueDepartmentSlug(string $name): string
    {
        $base = Str::slug($name) ?: Str::slug(Str::random(6));
        $slug = $base;
        $counter = 1;

        while (Departments::where('slug', $slug)->exists()) {
            $slug = "{$base}-{$counter}";
            $counter++;
        }

        return $slug;
    }

    /**
     * Generate unique URI for categories.
     */
    protected function uniqueCategoryUri(string $name, int $sourceId): string
    {
        $base = Str::slug($name) ?: "category-{$sourceId}";
        $uri = $base;
        $counter = 1;

        while (Categories::where('uri', $uri)->exists()) {
            $uri = "{$base}-{$counter}";
            $counter++;
        }

        return $uri;
    }

    /**
     * Generate unique slug for announcements.
     */
    protected function uniqueAnnouncementSlug(string $value): string
    {
        $base = Str::slug($value) ?: Str::slug(Str::random(8));
        $slug = $base;
        $counter = 1;

        while (Announcements::where('slug', $slug)->exists()) {
            $slug = "{$base}-{$counter}";
            $counter++;
        }

        return $slug;
    }

    /**
     * Resolve human name for a source user.
     */
    protected function resolveUserName(array $row): string
    {
        $parts = array_filter([
            $row['firstname'] ?? null,
            $row['lastname'] ?? null,
        ]);

        if (!empty($parts)) {
            return trim(implode(' ', $parts));
        }

        if (!empty($row['name'])) {
            return $row['name'];
        }

        if (!empty($row['organisation_firstname']) || !empty($row['organisation_lastname'])) {
            return trim(
                ($row['organisation_firstname'] ?? '') . ' ' . ($row['organisation_lastname'] ?? '')
            );
        }

        $email = $row['email'] ?? 'customer';
        return ucfirst(strtok($email, '@'));
    }

    /**
     * Resolve operator name.
     */
    protected function resolveOperatorName(array $row): string
    {
        $parts = array_filter([
            $row['firstname'] ?? null,
            $row['lastname'] ?? null,
            $row['operator_firstname'] ?? null,
            $row['operator_lastname'] ?? null,
        ]);

        if (!empty($parts)) {
            return trim(implode(' ', $parts));
        }

        $email = $row['email'] ?? $row['user_email'] ?? 'operator';
        return ucfirst(strtok($email, '@'));
    }

    /**
     * Ensure spatie role exists.
     */
    protected function ensureRole(string $role): void
    {
        if (isset($this->ensuredRoles[$role])) {
            return;
        }

        Role::findOrCreate($role);
        $this->ensuredRoles[$role] = true;
    }

    /**
     * Map adapter provided status to Ticaga status value.
     */
    protected function mapStatus(?int $statusId): string
    {
        return $this->adapter->mapStatus($statusId);
    }

    /**
     * Map adapter provided priority to Ticaga priority value.
     */
    protected function mapPriority(?int $priorityId): string
    {
        return $this->adapter->mapPriority($priorityId);
    }

    /**
     * Generate public hash for tickets.
     */
    protected function generatePublicHash(): string
    {
        return hash_hmac('sha256', Str::uuid()->toString(), config('app.key'));
    }

    /**
     * Sanitize timestamps coming from source systems.
     */
    protected function normalizeTimestamp(mixed $value): ?string
    {
        if ($value === null) {
            return null;
        }

        $value = trim((string) $value);
        if ($value === '') {
            return null;
        }

        $placeholders = [
            '0000-00-00',
            '0000-00-00 00:00:00',
            '0000-00-00 00:00:00.000000',
        ];

        if (in_array($value, $placeholders, true) || str_starts_with($value, '-0001')) {
            return null;
        }

        try {
            return Carbon::parse($value)->format('Y-m-d H:i:s');
        } catch (\Throwable $e) {
            return null;
        }
    }

    /**
     * Determine initial ticket message content from source row.
     */
    protected function extractTicketMessage(array $row): string
    {
        $candidates = [
            'message',
            'content',
            'body',
            'detail',
            'description',
            'initial_message',
            'text',
        ];

        foreach ($candidates as $key) {
            if (!empty($row[$key])) {
                return trim((string) $row[$key]);
            }
        }

        return '';
    }

    /**
     * Determine announcement content from source row.
     */
    protected function extractArticleContent(array $row): string
    {
        $candidates = [
            'answer',
            'content_html',
            'content',
            'article',
            'body',
            'description',
            'text',
            'summary',
            'content_text',
        ];

        foreach ($candidates as $key) {
            if (!empty($row[$key])) {
                return trim((string) $row[$key]);
            }
        }

        return '';
    }

    /**
     * Normalize ticket CC list into a comma-separated string.
     */
    protected function formatTicketCc(mixed $value): ?string
    {
        if ($value === null) {
            return null;
        }

        $emails = [];
        if (is_string($value)) {
            $emails = preg_split('/[,\r\n]+/', $value) ?: [];
        } elseif (is_array($value)) {
            $emails = $value;
        }

        $normalized = array_values(array_filter(array_unique(array_map(
            static fn ($email) => strtolower(trim((string) $email)),
            $emails
        )), static fn ($email) => filter_var($email, FILTER_VALIDATE_EMAIL)));

        return empty($normalized) ? null : implode(',', $normalized);
    }

    /**
     * Import or resolve a fallback requester for tickets opened by operators.
     *
     * @return array{user_id:int,note:?string}|null
     */
    protected function importTicketFallbackRequester(array $row, bool $requesterWasOperator, $originalUserId): ?array
    {
        $email = $this->extractTicketRequesterEmail($row);
        if (!$email) {
            return null;
        }

        $emailKey = strtolower($email);

        if ($this->dryRun || $this->isSimulation) {
            return [
                'user_id' => 0,
                'note' => null,
            ];
        }

        $user = User::where('email', $emailKey)->first();

        if (!$user && isset($this->fallbackUserCache[$emailKey])) {
            $cached = User::find($this->fallbackUserCache[$emailKey]);
            if ($cached) {
                $user = $cached;
            }
        }

        if (!$user) {
            $name = $this->extractTicketRequesterName($row, $emailKey);
            $user = User::create([
                'name' => $name,
                'email' => $emailKey,
                'password' => Hash::make($this->job->options['default_password'] ?? 'changeme123'),
                'company' => $row['organisation_name'] ?? ($row['company'] ?? null),
                'biography' => '',
                'account_manager' => '0',
                'force_password_reset' => $this->job->options['force_password_reset'] ?? true,
            ]);

            $this->ensureRole('customer');
            if (!$user->hasRole('customer')) {
                $user->assignRole('customer');
            }
        }

        $this->fallbackUserCache[$emailKey] = $user->id;

        $note = $this->buildTicketOnBehalfNote($row, $requesterWasOperator, $originalUserId, $user, $emailKey);

        return [
            'user_id' => $user->id,
            'note' => $note,
        ];
    }

    /**
     * Create an internal note on the imported ticket.
     */
    protected function createTicketNote(Tickets $ticket, string $content, array $row): void
    {
        $content = trim($content);
        if ($content === '') {
            return;
        }

        $note = new Responses([
            'ticket_number' => $ticket->id,
            'user_id' => 0,
            'content' => $content,
            'organize' => Organize::Ticket->value,
            'is_note' => true,
            'employee_response' => true,
            'ip_address' => null,
        ]);

        $timestamp = $this->normalizeTimestamp($row['created_at'] ?? null);
        if ($timestamp) {
            $note->created_at = $timestamp;
            $note->updated_at = $timestamp;
        }

        $note->save();
    }

    /**
     * Build note content for tickets opened by an operator on behalf of a customer.
     */
    protected function buildTicketCreatedOnBehalfNote(array $row, int $operatorId, ?User $customer): string
    {
        $operator = $this->getSourceUser($operatorId);
        $operatorName = $this->formatSourceUserName($operator) ?? "Operator #{$operatorId}";

        $customerEmail = $customer?->email;
        $customerName = $customer?->name;
        if (!$customerName) {
            $customerName = $this->extractTicketRequesterName($row, $customerEmail);
        }

        $customerName = $customerName ?: 'the customer';

        $message = "[Imported Log] {$operatorName} (SupportPal ID {$operatorId}) opened this ticket on behalf of {$customerName}.";

        if ($customer && $customerEmail) {
            $displayName = $customer->name ?: 'customer';
            $message .= sprintf(' Imported requester mapped to %s <%s>.', $displayName, $customerEmail);
        }

        return rtrim($message, '.') . '.';
    }

    /**
     * Determine if the first public response should be skipped because the ticket was opened by an operator.
     */
    protected function shouldSkipOperatorOpeningResponse(int $mappedTicketId, ?int $sourceUserId, ?int $mappedUserId, string $content, array $row): bool
    {
        if ($sourceUserId === null || !$this->isOperatorUser($sourceUserId)) {
            return false;
        }

        $ticket = Tickets::find($mappedTicketId);
        if (!$ticket) {
            return false;
        }

        $hasPublicResponses = Responses::where('ticket_number', $mappedTicketId)
            ->where('is_note', false)
            ->exists();

        if ($hasPublicResponses) {
            return false;
        }

        if ($mappedUserId !== null && (int) $ticket->user_id === (int) $mappedUserId) {
            return false;
        }

        if (!$this->dryRun && !$this->isSimulation) {
            if (trim($content) !== '' && trim((string) $ticket->message) === '') {
                $ticket->update(['message' => $content]);
            }

            if (!$this->ticketHasOnBehalfNote($ticket->id)) {
                $customerModel = User::find($ticket->user_id);
                $note = $this->buildTicketCreatedOnBehalfNote($row, $sourceUserId, $customerModel);
                if ($note) {
                    $this->createTicketNote($ticket, $note, $row);
                }
            }
        }

        return true;
    }

    /**
     * Check whether we have already added the on-behalf note.
     */
    protected function ticketHasOnBehalfNote(int $ticketId): bool
    {
        return Responses::where('ticket_number', $ticketId)
            ->where('is_note', true)
            ->where('user_id', 0)
            ->where('content', 'like', '[Imported Log]%opened this ticket on behalf%')
            ->exists();
    }

    /**
     * Build note content describing tickets created on behalf of customers when a fallback requester is generated.
     */
    protected function buildTicketOnBehalfNote(array $row, bool $requesterWasOperator, $originalUserId, User $requester, string $email): ?string
    {
        if (!$requesterWasOperator && !$originalUserId) {
            return null;
        }

        $parts = [];

        if ($requesterWasOperator && $originalUserId) {
            $operator = $this->getSourceUser((int) $originalUserId);
            $operatorName = $this->formatSourceUserName($operator);
            if ($operatorName) {
                $parts[] = "Created on behalf by operator {$operatorName} (SupportPal ID {$originalUserId})";
            } else {
                $parts[] = "Created on behalf by operator (SupportPal ID {$originalUserId})";
            }
        } else {
            $parts[] = 'Requester missing in source system; fallback customer created';
        }

        $parts[] = sprintf('Requester imported as %s <%s>', $requester->name ?? 'customer', $email);

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

    /**
     * Extract requester email from a ticket row.
     */
    protected function extractTicketRequesterEmail(array $row): ?string
    {
        $candidates = [
            'requester_email',
            'user_email',
            'email',
            'contact_email',
            'reply_email',
            'to_email',
            'customer_email',
        ];

        foreach ($candidates as $key) {
            if (!empty($row[$key]) && filter_var($row[$key], FILTER_VALIDATE_EMAIL)) {
                return strtolower(trim((string) $row[$key]));
            }
        }

        return null;
    }

    /**
     * Extract requester name from a ticket row.
     */
    protected function extractTicketRequesterName(array $row, ?string $email = null): string
    {
        $nameFields = [
            'requester_name',
            'user_name',
            'name',
            'full_name',
        ];

        foreach ($nameFields as $field) {
            if (!empty($row[$field])) {
                return trim((string) $row[$field]);
            }
        }

        $firstNames = [
            'requester_firstname',
            'requester_first_name',
            'firstname',
            'first_name',
        ];
        $lastNames = [
            'requester_lastname',
            'requester_last_name',
            'lastname',
            'last_name',
        ];

        $first = null;
        foreach ($firstNames as $field) {
            if (!empty($row[$field])) {
                $first = trim((string) $row[$field]);
                break;
            }
        }

        $last = null;
        foreach ($lastNames as $field) {
            if (!empty($row[$field])) {
                $last = trim((string) $row[$field]);
                break;
            }
        }

        $parts = array_filter([$first, $last]);
        if (!empty($parts)) {
            return implode(' ', $parts);
        }

        if ($email) {
            return ucfirst(strtok($email, '@'));
        }

        return 'Customer';
    }

    /**
     * Format a source user name for messaging.
     */
    protected function formatSourceUserName(?array $user): ?string
    {
        if (!$user) {
            return null;
        }

        $name = trim(
            (($user['firstname'] ?? '') . ' ' . ($user['lastname'] ?? ''))
        );

        if ($name !== '') {
            return $name;
        }

        if (!empty($user['name'])) {
            return trim((string) $user['name']);
        }

        if (!empty($user['email'])) {
            return trim((string) $user['email']);
        }

        return null;
    }

    /**
     * Determine if the source user looks like a bot/system account.
     */
    protected function isBotUser(string $name): bool
    {
        $normalized = strtolower($name);

        return str_contains($normalized, 'bot')
            || str_contains($normalized, 'system');
    }

    /**
     * Generate a placeholder email for bot/system users.
     */
    protected function generatePlaceholderEmail(int $sourceId, string $name): string
    {
        $slug = Str::slug($name) ?: 'bot';

        return "{$slug}-{$sourceId}@placeholder.local";
    }

    /**
     * Import ticket history entries as internal notes owned by the system (user_id = 0).
     *
     * @param array<int,array<string,mixed>> $history
     */
    protected function importTicketHistoryNotes(Tickets $ticket, array $history): void
    {
        if (empty($history)) {
            return;
        }

        foreach ($history as $entry) {
            $content = $this->formatTicketHistoryMessage($entry);
            if ($content === null) {
                continue;
            }

            $note = new Responses([
                'ticket_number' => $ticket->id,
                'user_id' => 0,
                'content' => $content,
                'organize' => Organize::Ticket->value,
                'is_note' => true,
                'employee_response' => true,
                'ip_address' => null,
            ]);

            $timestamp = $this->normalizeTimestamp($entry['timestamp'] ?? null);
            if ($timestamp) {
                $note->created_at = $timestamp;
                $note->updated_at = $timestamp;
            }

            $note->save();
        }
    }

    /**
     * Format a ticket history row into a note body.
     */
    protected function formatTicketHistoryMessage(array $entry): ?string
    {
        $segments = [];

        $action = trim((string) ($entry['action'] ?? ''));
        if ($action !== '') {
            $segments[] = $action;
        }

        $message = trim((string) ($entry['message'] ?? ''));
        if ($message !== '' && !in_array($message, $segments, true)) {
            $segments[] = $message;
        }

        if (empty($segments)) {
            return null;
        }

        $combined = strtolower(implode(' ', $segments));
        if (!str_contains($combined, 'assign') && !str_contains($combined, 'watch')) {
            return null;
        }

        $author = trim((string) ($entry['author'] ?? ''));
        $prefix = '[Imported Log]';
        if ($author !== '') {
            $prefix .= " {$author}";
        }

        return $prefix . ': ' . implode(' — ', $segments);
    }

    /**
     * Resolve ticket closure timestamp.
     */
    protected function resolveTicketClosedAt(array $row): ?string
    {
        return $this->normalizeTimestamp(
            $row['resolved_at']
            ?? $row['closed_at']
            ?? null
        );
    }

    /**
     * Import user on demand if not already imported.
     */
    protected function importUserOnDemand(int $supportPalUserId): ?int
    {
        if ($supportPalUserId <= 0) {
            return null;
        }

        $existing = $this->getMappedId('user', $supportPalUserId);
        if ($existing !== null) {
            return $existing;
        }

        $row = $this->adapter->findUser($supportPalUserId);
        if (!$row) {
            return null;
        }

        $isOperator = $this->isOperatorUser($supportPalUserId);

        if ($this->dryRun || $this->isSimulation) {
            $this->rememberMapping('user', $supportPalUserId, null);
            if ($isOperator) {
                $this->rememberMapping('operator', $supportPalUserId, null);
            }

            return $this->getMappedId('user', $supportPalUserId);
        }

        try {
            $email = strtolower(trim($row['email'] ?? ''));
            if (empty($email)) {
                return null;
            }

            $user = User::where('email', $email)->first();
            if (!$user) {
                $user = User::create([
                    'name' => $this->resolveUserName($row),
                    'email' => $email,
                    'password' => Hash::make($this->job->options['default_password'] ?? 'changeme123'),
                    'biography' => $row['biography'] ?? '',
                    'account_manager' => (string) ($row['account_manager'] ?? '0'),
                    'force_password_reset' => $this->job->options['force_password_reset'] ?? true,
                    'email_verified_at' => !empty($row['confirmed']) ? now() : null,
                ]);
            }

            $this->rememberMapping('user', $supportPalUserId, $user->id);

            if ($isOperator) {
                $this->ensureRole('employee');
                if (!$user->hasAnyRole(['employee', 'admin', 'superadmin'])) {
                    $user->assignRole('employee');
                }
                $this->rememberMapping('operator', $supportPalUserId, $user->id);
            } else {
                $this->ensureRole('customer');
                if (!$user->hasRole('customer')) {
                    $user->assignRole('customer');
                }
            }

            return $user->id;
        } catch (\Throwable $e) {
            $this->addError('user', $supportPalUserId, "On-demand import failed: {$e->getMessage()}", $row);
            return null;
        }
    }

    /**
     * Resolve article author mapping.
     */
    protected function resolveArticleAuthor(array $row): int
    {
        $authorId = $row['author_id'] ?? $row['user_id'] ?? null;
        if ($authorId === null) {
            return 0;
        }

        $mapped = $this->getMappedId('user', $authorId)
            ?? $this->getMappedId('operator', $authorId);

        return $mapped ?? 0;
    }

    /**
     * Provide a detailed message when a ticket requester cannot be mapped.
     */
    protected function describeMissingUserMapping(?int $supportPalUserId, ?string $ticketNumber = null): string
    {
        $systemLabel = $this->adapter::label();

        if (!$supportPalUserId) {
            return "Ticket requester is missing in {$systemLabel}. Update the ticket to reference a valid user.";
        }

        $user = $this->getSourceUser($supportPalUserId);
        $ticketRef = $ticketNumber ? "#{$ticketNumber}" : "ID {$supportPalUserId}";

        if (!$user) {
            return sprintf(
                '%s user #%d referenced by ticket %s could not be found. Verify the requester exists before importing.',
                $systemLabel,
                $supportPalUserId,
                $ticketRef
            );
        }

        $name = trim(($user['firstname'] ?? '') . ' ' . ($user['lastname'] ?? ''));
        $email = trim($user['email'] ?? '');
        $label = $name !== '' ? $name : "User #{$supportPalUserId}";

        if ($email === '') {
            return sprintf(
                '%s (%s ID %d) has no email address. Add an email before importing ticket %s.',
                $label,
                $systemLabel,
                $supportPalUserId,
                $ticketRef
            );
        }

        if (!($this->job->options['import_users'] ?? true)) {
            return sprintf(
                '%s (%s ID %d, %s) cannot be mapped because user imports are disabled. Enable user imports and retry.',
                $label,
                $systemLabel,
                $supportPalUserId,
                $email
            );
        }

        if ($this->isOperatorUser($supportPalUserId) && !($this->job->options['import_operators'] ?? true)) {
            return sprintf(
                '%s (%s ID %d, %s) is an operator but operator imports are disabled. Enable operator imports to include this ticket.',
                $label,
                $systemLabel,
                $supportPalUserId,
                $email
            );
        }

        return sprintf(
            '%s (%s ID %d, %s) was not imported. Ensure the user stage completes successfully before importing ticket %s.',
            $label,
            $systemLabel,
            $supportPalUserId,
            $email,
            $ticketRef
        );
    }

    /**
     * Retrieve and cache a source user row for diagnostics.
     */
    protected function getSourceUser(int $supportPalUserId): ?array
    {
        if (isset($this->sourceUserCache[$supportPalUserId])) {
            return $this->sourceUserCache[$supportPalUserId];
        }

        $user = $this->adapter->findUser($supportPalUserId);
        $this->sourceUserCache[$supportPalUserId] = $user;

        return $user;
    }
}
