<?php

namespace App\Services\Import;

use App\Models\LegacyImportMap;
use Illuminate\Console\Command;
use Illuminate\Database\Connection;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Str;
use RuntimeException;

class LegacyAmmarImportService
{
    private const SOURCE_SYSTEM = 'legacy_ammar';

    private ?Command $command = null;
    private ?Connection $legacyConnection = null;
    private array $options = [];
    private array $context = [];
    private array $summary = [
        'products_processed' => 0,
        'products_created' => 0,
        'products_updated' => 0,
        'customers_processed' => 0,
        'customers_created' => 0,
        'customers_updated' => 0,
        'addresses_created' => 0,
        'addresses_updated' => 0,
        'wallet_transactions_imported' => 0,
        'loyalty_transactions_imported' => 0,
        'categories_created' => 0,
        'brands_created' => 0,
        'units_created' => 0,
        'attributes_created' => 0,
        'images_copied' => 0,
        'missing_images' => 0,
    ];
    private ?Collection $legacyColors = null;

    public function import(Command $command, array $options): array
    {
        $this->command = $command;
        $this->options = $options;
        $this->context = $this->resolveTargetContext();

        $this->command->info('Legacy Ammar import started.');

        if ($this->options['products']) {
            $this->importProducts();
        }

        if ($this->options['customers']) {
            $this->importCustomers();
            $this->syncCustomerReferrals();
        }

        return $this->summary;
    }

    private function importProducts(): void
    {
        $this->ensureLegacyTable('products');

        $this->legacy()
            ->table('products')
            ->orderBy('id')
            ->chunkById($this->chunkSize(), function ($products) {
                foreach ($products as $legacyProduct) {
                    $this->importSingleProduct($legacyProduct);
                }
            });
    }

    private function importCustomers(): void
    {
        $this->ensureLegacyTable('users');

        $this->legacy()
            ->table('users')
            ->orderBy('id')
            ->chunkById($this->chunkSize(), function ($users) {
                foreach ($users as $legacyUser) {
                    $this->importSingleCustomer($legacyUser);
                }
            });
    }

    private function importSingleProduct(object $legacyProduct): void
    {
        $this->summary['products_processed']++;

        $existingItemId = $this->resolveExistingItemId($legacyProduct);
        $categoryMap = $this->ensureProductCategories($legacyProduct);
        $brandId = $this->ensureBrand($this->intValue($legacyProduct, 'brand_id'));
        $unitId = $this->ensureUnit($this->stringValue($legacyProduct, 'unit'));
        [$choiceOptions, $attributeIds] = $this->prepareChoiceOptions($this->jsonArray($legacyProduct->choice_options ?? null));
        $media = $this->prepareProductMedia($legacyProduct);
        $variations = $this->prepareVariations($this->jsonArray($legacyProduct->variation ?? null), $media['color_image_map']);
        $slug = $this->buildUniqueSlug($legacyProduct, $existingItemId, 'items');
        $name = $this->stringValue($legacyProduct, 'name', 'Imported Product');
        $description = $this->stringValue($legacyProduct, 'details');

        $itemPayload = [
            'name' => $name,
            'description' => $description,
            'image' => $media['main_image'],
            'category_id' => $categoryMap['deepest_category_id'],
            'category_ids' => json_encode($categoryMap['category_ids']),
            'variations' => json_encode($variations),
            'add_ons' => json_encode([]),
            'attributes' => json_encode($attributeIds),
            'choice_options' => json_encode($choiceOptions),
            'price' => $this->floatValue($legacyProduct, 'unit_price'),
            'tax' => $this->floatValue($legacyProduct, 'tax'),
            'tax_type' => $this->stringValue($legacyProduct, 'tax_type', 'percent'),
            'discount' => $this->floatValue($legacyProduct, 'discount'),
            'discount_type' => $this->stringValue($legacyProduct, 'discount_type', 'percent'),
            'available_time_starts' => '00:00:00',
            'available_time_ends' => '23:59:59',
            'veg' => 0,
            'status' => (int) $this->boolish($legacyProduct->status ?? 1),
            'store_id' => $this->context['store_id'],
            'module_id' => $this->context['module_id'],
            'stock' => 0,
            'unit_id' => $unitId,
            'images' => json_encode($media['gallery_images']),
            'food_variations' => json_encode([]),
            'slug' => $slug,
            'recommended' => 0,
            'organic' => 0,
            'maximum_cart_quantity' => $this->intValue($legacyProduct, 'minimum_order_qty') ?: null,
            'item_delivery_charge' => $this->floatValue($legacyProduct, 'shipping_cost'),
            'item_packaging_charge' => 0,
            'free_delivery_badge_status' => (int) $this->boolish($legacyProduct->free_shipping ?? 0),
            'is_approved' => (int) $this->boolish($legacyProduct->request_status ?? 1),
            'is_halal' => 0,
            'updated_at' => $this->dateValue($legacyProduct->updated_at ?? null),
            'created_at' => $this->dateValue($legacyProduct->created_at ?? null),
        ];

        if ($this->dryRun()) {
            $this->summary[$existingItemId ? 'products_updated' : 'products_created']++;
            return;
        }

        DB::transaction(function () use ($legacyProduct, $existingItemId, $itemPayload, $brandId, $name, $description) {
            if ($existingItemId) {
                DB::table('items')->where('id', $existingItemId)->update($itemPayload);
                $itemId = $existingItemId;
                $this->summary['products_updated']++;
            } else {
                $itemId = DB::table('items')->insertGetId($itemPayload);
                $this->summary['products_created']++;
            }

            DB::table('ecommerce_item_details')->updateOrInsert(
                ['item_id' => $itemId],
                ['brand_id' => $brandId, 'updated_at' => now(), 'created_at' => now()]
            );

            $this->syncStorageRecord('App\Models\Item', $itemId, 'image', $itemPayload['image']);
            $this->upsertTranslation('App\Models\Item', $itemId, 'name', $name);
            $this->upsertTranslation('App\Models\Item', $itemId, 'description', $description);
            $this->storeImportMap('products', (string) $legacyProduct->id, 'items', $itemId, [
                'slug' => $itemPayload['slug'],
                'source_code' => $this->stringValue($legacyProduct, 'code'),
            ]);
        });
    }

    private function importSingleCustomer(object $legacyUser): void
    {
        $phone = $this->normalizeString($legacyUser->phone ?? null);
        $email = $this->normalizeEmail($legacyUser->email ?? null);

        if (!$phone && !$email) {
            return;
        }

        $this->summary['customers_processed']++;
        $existingUserId = $this->resolveExistingUserId($legacyUser);
        $image = $this->copyLegacyFile('profile', $this->stringValue($legacyUser, 'image'));
        $refCode = $this->stringValue($legacyUser, 'referral_code');

        $userPayload = [
            'f_name' => $this->stringValue($legacyUser, 'f_name') ?: $this->fallbackFirstName($legacyUser),
            'l_name' => $this->stringValue($legacyUser, 'l_name'),
            'phone' => $phone,
            'email' => $email,
            'image' => $image,
            'is_phone_verified' => (int) $this->boolish($legacyUser->is_phone_verified ?? 0),
            'email_verified_at' => $this->dateValue($legacyUser->email_verified_at ?? null),
            'password' => $this->stringValue($legacyUser, 'password'),
            'remember_token' => $this->stringValue($legacyUser, 'remember_token'),
            'created_at' => $this->dateValue($legacyUser->created_at ?? null),
            'updated_at' => $this->dateValue($legacyUser->updated_at ?? null),
            'cm_firebase_token' => $this->stringValue($legacyUser, 'cm_firebase_token'),
            'status' => (int) $this->boolish($legacyUser->is_active ?? 1),
            'order_count' => 0,
            'login_medium' => $this->stringValue($legacyUser, 'login_medium'),
            'social_id' => $this->stringValue($legacyUser, 'social_id'),
            'zone_id' => $this->context['zone_id'],
            'wallet_balance' => $this->floatValue($legacyUser, 'wallet_balance'),
            'loyalty_point' => $this->floatValue($legacyUser, 'loyalty_point'),
            'ref_code' => $refCode ?: $this->generateUniqueRefCode($existingUserId),
            'current_language_key' => 'en',
            'ref_by' => null,
            'temp_token' => $this->stringValue($legacyUser, 'temporary_token'),
            'module_ids' => json_encode([$this->context['module_id']]),
            'is_email_verified' => (int) $this->boolish($legacyUser->is_email_verified ?? 0),
            'is_from_pos' => 0,
        ];

        if ($this->dryRun()) {
            $this->summary[$existingUserId ? 'customers_updated' : 'customers_created']++;
            return;
        }

        DB::transaction(function () use ($legacyUser, $existingUserId, $userPayload) {
            if ($existingUserId) {
                DB::table('users')->where('id', $existingUserId)->update($userPayload);
                $targetUserId = $existingUserId;
                $this->summary['customers_updated']++;
            } else {
                $targetUserId = DB::table('users')->insertGetId($userPayload);
                $this->summary['customers_created']++;
            }

            $this->syncStorageRecord('App\Models\User', $targetUserId, 'image', $userPayload['image'], 'profile');
            $this->storeImportMap('users', (string) $legacyUser->id, 'users', $targetUserId, [
                'phone' => $userPayload['phone'],
                'email' => $userPayload['email'],
            ]);

            $this->importCustomerAddresses($legacyUser, $targetUserId);
            $this->importWalletTransactions($legacyUser, $targetUserId);
            $this->importLoyaltyTransactions($legacyUser, $targetUserId);
        });
    }

    private function importCustomerAddresses(object $legacyUser, int $targetUserId): void
    {
        $legacyAddresses = collect();

        if ($this->hasLegacyTable('shipping_addresses')) {
            $legacyAddresses = $this->legacy()
                ->table('shipping_addresses')
                ->where('customer_id', $legacyUser->id)
                ->orderBy('id')
                ->get();
        }

        if ($legacyAddresses->isEmpty()) {
            $fallbackAddress = $this->buildFallbackLegacyAddress($legacyUser);
            if ($fallbackAddress) {
                $legacyAddresses = collect([$fallbackAddress]);
            }
        }

        foreach ($legacyAddresses as $legacyAddress) {
            $sourceId = $this->stringValue($legacyAddress, 'id') ?: 'user-' . $legacyUser->id . '-fallback';
            $existingAddressId = $this->mappedTargetId('shipping_addresses', $sourceId);
            $payload = [
                'address_type' => $this->stringValue($legacyAddress, 'address_type', 'home'),
                'contact_person_number' => Str::limit(
                    $this->stringValue($legacyAddress, 'phone') ?: $this->stringValue($legacyUser, 'phone'),
                    20,
                    ''
                ),
                'address' => $this->stringValue($legacyAddress, 'address') ?: $this->stringValue($legacyUser, 'street_address'),
                'country' => $this->stringValue($legacyAddress, 'country', 'Bangladesh'),
                'district' => $this->stringValue($legacyAddress, 'district') ?: $this->stringValue($legacyAddress, 'state') ?: $this->stringValue($legacyUser, 'city'),
                'thana' => $this->stringValue($legacyAddress, 'area') ?: $this->stringValue($legacyAddress, 'city'),
                'post_office' => null,
                'postal_code' => $this->stringValue($legacyAddress, 'zip') ?: $this->stringValue($legacyUser, 'zip'),
                'address_source' => 'legacy_ammar_import',
                'latitude' => $this->stringValue($legacyAddress, 'latitude'),
                'longitude' => $this->stringValue($legacyAddress, 'longitude'),
                'user_id' => $targetUserId,
                'contact_person_name' => $this->stringValue($legacyAddress, 'contact_person_name') ?: trim(($this->stringValue($legacyUser, 'f_name') . ' ' . $this->stringValue($legacyUser, 'l_name'))),
                'zone_id' => $this->context['zone_id'],
                'floor' => null,
                'road' => null,
                'house' => $this->stringValue($legacyUser, 'house_no'),
                'updated_at' => now(),
                'created_at' => now(),
            ];

            if ($existingAddressId) {
                DB::table('customer_addresses')->where('id', $existingAddressId)->update($payload);
                $targetAddressId = $existingAddressId;
                $this->summary['addresses_updated']++;
            } else {
                $targetAddressId = DB::table('customer_addresses')->insertGetId($payload);
                $this->summary['addresses_created']++;
            }

            $this->storeImportMap('shipping_addresses', $sourceId, 'customer_addresses', $targetAddressId, [
                'user_id' => $targetUserId,
            ]);
        }
    }

    private function importWalletTransactions(object $legacyUser, int $targetUserId): void
    {
        if (!$this->hasLegacyTable('wallet_transactions')) {
            return;
        }

        $rows = $this->legacy()
            ->table('wallet_transactions')
            ->where('user_id', $legacyUser->id)
            ->orderBy('id')
            ->get();

        foreach ($rows as $legacyTransaction) {
            $sourceId = (string) $legacyTransaction->id;
            $existingId = $this->mappedTargetId('wallet_transactions', $sourceId);
            $payload = [
                'user_id' => $targetUserId,
                'transaction_id' => $this->stringValue($legacyTransaction, 'transaction_id') ?: (string) Str::uuid(),
                'credit' => $this->floatValue($legacyTransaction, 'credit'),
                'debit' => $this->floatValue($legacyTransaction, 'debit'),
                'admin_bonus' => $this->floatValue($legacyTransaction, 'admin_bonus'),
                'balance' => $this->floatValue($legacyTransaction, 'balance'),
                'transaction_type' => $this->stringValue($legacyTransaction, 'transaction_type'),
                'reference' => $this->stringValue($legacyTransaction, 'reference'),
                'created_at' => $this->dateValue($legacyTransaction->created_at ?? null),
                'updated_at' => $this->dateValue($legacyTransaction->updated_at ?? null),
            ];

            if ($existingId) {
                DB::table('wallet_transactions')->where('id', $existingId)->update($payload);
            } else {
                $existingByTransactionId = DB::table('wallet_transactions')
                    ->where('transaction_id', $payload['transaction_id'])
                    ->where('user_id', $targetUserId)
                    ->value('id');

                $transactionId = $existingByTransactionId
                    ? tap((int) $existingByTransactionId, fn ($id) => DB::table('wallet_transactions')->where('id', $id)->update($payload))
                    : DB::table('wallet_transactions')->insertGetId($payload);

                $this->storeImportMap('wallet_transactions', $sourceId, 'wallet_transactions', $transactionId);
                $this->summary['wallet_transactions_imported']++;
            }
        }
    }

    private function importLoyaltyTransactions(object $legacyUser, int $targetUserId): void
    {
        if (!$this->hasLegacyTable('loyalty_point_transactions')) {
            return;
        }

        $rows = $this->legacy()
            ->table('loyalty_point_transactions')
            ->where('user_id', $legacyUser->id)
            ->orderBy('id')
            ->get();

        foreach ($rows as $legacyTransaction) {
            $sourceId = (string) $legacyTransaction->id;
            $existingId = $this->mappedTargetId('loyalty_point_transactions', $sourceId);
            $payload = [
                'user_id' => $targetUserId,
                'transaction_id' => $this->stringValue($legacyTransaction, 'transaction_id') ?: (string) Str::uuid(),
                'credit' => $this->floatValue($legacyTransaction, 'credit'),
                'debit' => $this->floatValue($legacyTransaction, 'debit'),
                'balance' => $this->floatValue($legacyTransaction, 'balance'),
                'reference' => $this->stringValue($legacyTransaction, 'reference'),
                'transaction_type' => $this->stringValue($legacyTransaction, 'transaction_type'),
                'created_at' => $this->dateValue($legacyTransaction->created_at ?? null),
                'updated_at' => $this->dateValue($legacyTransaction->updated_at ?? null),
            ];

            if ($existingId) {
                DB::table('loyalty_point_transactions')->where('id', $existingId)->update($payload);
            } else {
                $existingByTransactionId = DB::table('loyalty_point_transactions')
                    ->where('transaction_id', $payload['transaction_id'])
                    ->where('user_id', $targetUserId)
                    ->value('id');

                $transactionId = $existingByTransactionId
                    ? tap((int) $existingByTransactionId, fn ($id) => DB::table('loyalty_point_transactions')->where('id', $id)->update($payload))
                    : DB::table('loyalty_point_transactions')->insertGetId($payload);

                $this->storeImportMap('loyalty_point_transactions', $sourceId, 'loyalty_point_transactions', $transactionId);
                $this->summary['loyalty_transactions_imported']++;
            }
        }
    }

    private function syncCustomerReferrals(): void
    {
        if (!$this->hasLegacyTable('users') || $this->dryRun()) {
            return;
        }

        $this->legacy()
            ->table('users')
            ->select('id', 'referred_by')
            ->whereNotNull('referred_by')
            ->orderBy('id')
            ->chunkById($this->chunkSize(), function ($users) {
                foreach ($users as $legacyUser) {
                    $targetUserId = $this->mappedTargetId('users', (string) $legacyUser->id);
                    $targetReferrerId = $this->mappedTargetId('users', (string) $legacyUser->referred_by);

                    if ($targetUserId && $targetReferrerId) {
                        DB::table('users')->where('id', $targetUserId)->update(['ref_by' => $targetReferrerId]);
                    }
                }
            });
    }

    private function ensureProductCategories(object $legacyProduct): array
    {
        $rootId = $this->ensureCategory($this->intValue($legacyProduct, 'category_id'));
        $subId = $this->ensureCategory($this->intValue($legacyProduct, 'sub_category_id'), $rootId);
        $subSubId = $this->ensureCategory($this->intValue($legacyProduct, 'sub_sub_category_id'), $subId);

        $categoryIds = [];
        foreach ([$rootId, $subId, $subSubId] as $index => $id) {
            if ($id) {
                $categoryIds[] = ['id' => $id, 'position' => $index + 1];
            }
        }

        return [
            'category_ids' => $categoryIds,
            'deepest_category_id' => $subSubId ?: ($subId ?: $rootId),
        ];
    }

    private function ensureCategory(?int $legacyCategoryId, ?int $forcedParentId = null): ?int
    {
        if (!$legacyCategoryId || !$this->hasLegacyTable('categories')) {
            return null;
        }

        $mappedId = $this->mappedTargetId('categories', (string) $legacyCategoryId);
        if ($mappedId) {
            return $mappedId;
        }

        $legacyCategory = $this->legacy()->table('categories')->where('id', $legacyCategoryId)->first();
        if (!$legacyCategory) {
            return null;
        }

        $parentId = $forcedParentId;
        if (!$parentId && $this->intValue($legacyCategory, 'parent_id')) {
            $parentId = $this->ensureCategory($this->intValue($legacyCategory, 'parent_id'));
        }

        $existingId = DB::table('categories')
            ->where('module_id', $this->context['module_id'])
            ->where('parent_id', $parentId ?? 0)
            ->whereRaw('LOWER(name) = ?', [Str::lower($this->stringValue($legacyCategory, 'name', 'Category'))])
            ->value('id');

        $position = $parentId
            ? ((int) DB::table('categories')->where('id', $parentId)->value('position') + 1)
            : 0;
        $image = $this->copyLegacyFile('category', $this->stringValue($legacyCategory, 'image'));
        $payload = [
            'name' => $this->stringValue($legacyCategory, 'name', 'Category'),
            'image' => $image ?: 'def.png',
            'parent_id' => $parentId ?? 0,
            'position' => $position,
            'status' => (int) $this->boolish($legacyCategory->status ?? 1),
            'priority' => 0,
            'module_id' => $this->context['module_id'],
            'slug' => $this->buildUniqueSlug($legacyCategory, $existingId, 'categories'),
            'featured' => 0,
            'updated_at' => now(),
            'created_at' => now(),
        ];

        if ($existingId) {
            DB::table('categories')->where('id', $existingId)->update($payload);
            $targetId = (int) $existingId;
        } else {
            $targetId = DB::table('categories')->insertGetId($payload);
            $this->summary['categories_created']++;
        }

        $this->syncStorageRecord('App\Models\Category', $targetId, 'image', $payload['image'], 'category');
        $this->upsertTranslation('App\Models\Category', $targetId, 'name', $payload['name']);
        $this->storeImportMap('categories', (string) $legacyCategoryId, 'categories', $targetId);

        return $targetId;
    }

    private function ensureBrand(?int $legacyBrandId): ?int
    {
        if (!$legacyBrandId || !$this->hasLegacyTable('brands')) {
            return null;
        }

        $mappedId = $this->mappedTargetId('brands', (string) $legacyBrandId);
        if ($mappedId) {
            return $mappedId;
        }

        $legacyBrand = $this->legacy()->table('brands')->where('id', $legacyBrandId)->first();
        if (!$legacyBrand) {
            return null;
        }

        $existingId = DB::table('brands')
            ->whereRaw('LOWER(name) = ?', [Str::lower($this->stringValue($legacyBrand, 'name', 'Brand'))])
            ->value('id');

        $payload = [
            'name' => $this->stringValue($legacyBrand, 'name', 'Brand'),
            'slug' => $this->buildUniqueSlug($legacyBrand, $existingId, 'brands'),
            'image' => $this->copyLegacyFile('brand', $this->stringValue($legacyBrand, 'image')),
            'status' => (int) $this->boolish($legacyBrand->status ?? 1),
            'module_id' => $this->context['module_id'],
            'updated_at' => now(),
            'created_at' => now(),
        ];

        if ($existingId) {
            DB::table('brands')->where('id', $existingId)->update($payload);
            $targetId = (int) $existingId;
        } else {
            $targetId = DB::table('brands')->insertGetId($payload);
            $this->summary['brands_created']++;
        }

        $this->syncStorageRecord('App\Models\Brand', $targetId, 'image', $payload['image'], 'brand');
        $this->upsertTranslation('App\Models\Brand', $targetId, 'name', $payload['name']);
        $this->storeImportMap('brands', (string) $legacyBrandId, 'brands', $targetId);

        return $targetId;
    }

    private function ensureUnit(?string $legacyUnit): ?int
    {
        $legacyUnit = trim((string) $legacyUnit);
        if ($legacyUnit === '') {
            return null;
        }

        $existingId = DB::table('units')
            ->whereRaw('LOWER(unit) = ?', [Str::lower($legacyUnit)])
            ->value('id');

        if ($existingId) {
            return (int) $existingId;
        }

        if ($this->dryRun()) {
            $this->summary['units_created']++;
            return null;
        }

        $unitId = DB::table('units')->insertGetId([
            'unit' => $legacyUnit,
            'created_at' => now(),
            'updated_at' => now(),
        ]);

        $this->summary['units_created']++;

        return $unitId;
    }

    private function ensureAttribute(string $name): ?int
    {
        $name = trim($name);
        if ($name === '') {
            return null;
        }

        $existingId = DB::table('attributes')
            ->whereRaw('LOWER(name) = ?', [Str::lower($name)])
            ->value('id');

        if ($existingId) {
            return (int) $existingId;
        }

        if ($this->dryRun()) {
            $this->summary['attributes_created']++;
            return null;
        }

        $attributeId = DB::table('attributes')->insertGetId([
            'name' => $name,
            'created_at' => now(),
            'updated_at' => now(),
        ]);

        $this->summary['attributes_created']++;

        return $attributeId;
    }

    private function prepareChoiceOptions(array $choiceOptions): array
    {
        $prepared = [];
        $attributeIds = [];

        foreach ($choiceOptions as $choiceOption) {
            if (!is_array($choiceOption)) {
                continue;
            }

            $title = trim((string) ($choiceOption['title'] ?? $choiceOption['name'] ?? ''));
            if ($title === '') {
                continue;
            }

            $attributeId = $this->ensureAttribute($title);
            if ($attributeId) {
                $attributeIds[] = $attributeId;
            }

            $options = collect($choiceOption['options'] ?? [])
                ->map(fn ($value) => trim((string) $value))
                ->filter()
                ->values()
                ->all();

            $prepared[] = [
                'name' => 'choice_' . ($attributeId ?: Str::slug($title, '_')),
                'title' => $title,
                'options' => $options,
            ];
        }

        return [$prepared, array_values(array_unique(array_filter($attributeIds)))];
    }

    private function prepareProductMedia(object $legacyProduct): array
    {
        $galleryImages = [];
        foreach ($this->jsonArray($legacyProduct->images ?? null) as $image) {
            $imageName = is_array($image) ? ($image['img'] ?? $image['image'] ?? null) : $image;
            $copied = $this->copyLegacyFile('product', (string) $imageName);
            if ($copied) {
                $galleryImages[] = ['img' => $copied, 'storage' => 'public'];
            }
        }

        $mainImage = $this->copyLegacyFile('product', $this->stringValue($legacyProduct, 'thumbnail'));
        if (!$mainImage && !empty($galleryImages)) {
            $mainImage = $galleryImages[0]['img'];
        }

        $colorMap = [];
        foreach ($this->jsonArray($legacyProduct->color_image ?? null) as $entry) {
            if (!is_array($entry)) {
                continue;
            }

            $colorKey = $entry['name'] ?? $entry['color_name'] ?? $entry['color'] ?? $entry['id'] ?? null;
            if (is_numeric($colorKey)) {
                $colorKey = $this->legacyColors()->get((int) $colorKey);
            }

            $colorKey = Str::lower(trim((string) $colorKey));
            if ($colorKey === '') {
                continue;
            }

            $imageName = $entry['image'] ?? $entry['image_name'] ?? $entry['value'] ?? null;
            $copied = $this->copyLegacyFile('product', (string) $imageName);
            if ($copied) {
                $colorMap[$colorKey] = $copied;
            }
        }

        return [
            'main_image' => $mainImage,
            'gallery_images' => $galleryImages,
            'color_image_map' => $colorMap,
        ];
    }

    private function prepareVariations(array $legacyVariations, array $colorImageMap): array
    {
        $prepared = [];

        foreach ($legacyVariations as $variation) {
            if (!is_array($variation)) {
                continue;
            }

            $type = trim((string) ($variation['type'] ?? $variation['name'] ?? $variation['code'] ?? ''));
            if ($type === '') {
                continue;
            }

            $price = (float) ($variation['price'] ?? $variation['optionPrice'] ?? 0);
            $image = $this->copyLegacyFile('product', (string) ($variation['image'] ?? ''));
            if (!$image) {
                $image = $this->matchVariationColorImage($type, $colorImageMap);
            }

            $prepared[] = [
                'type' => str_replace(' ', '', $type),
                'price' => $price,
                'stock' => 0,
                'image' => $image,
                'storage' => 'public',
            ];
        }

        return $prepared;
    }

    private function resolveExistingItemId(object $legacyProduct): ?int
    {
        $mappedId = $this->mappedTargetId('products', (string) $legacyProduct->id);
        if ($mappedId) {
            return $mappedId;
        }

        $slug = $this->stringValue($legacyProduct, 'slug');
        if ($slug !== '') {
            $existingId = DB::table('items')
                ->where('module_id', $this->context['module_id'])
                ->where('store_id', $this->context['store_id'])
                ->where('slug', $slug)
                ->value('id');

            if ($existingId) {
                return (int) $existingId;
            }
        }

        $name = $this->stringValue($legacyProduct, 'name');
        if ($name !== '') {
            $existingId = DB::table('items')
                ->where('module_id', $this->context['module_id'])
                ->where('store_id', $this->context['store_id'])
                ->whereRaw('LOWER(name) = ?', [Str::lower($name)])
                ->value('id');

            if ($existingId) {
                return (int) $existingId;
            }
        }

        return null;
    }

    private function resolveExistingUserId(object $legacyUser): ?int
    {
        $mappedId = $this->mappedTargetId('users', (string) $legacyUser->id);
        if ($mappedId) {
            return $mappedId;
        }

        $phone = $this->normalizeString($legacyUser->phone ?? null);
        if ($phone) {
            $existingId = DB::table('users')->where('phone', $phone)->value('id');
            if ($existingId) {
                return (int) $existingId;
            }
        }

        $email = $this->normalizeEmail($legacyUser->email ?? null);
        if ($email) {
            $existingId = DB::table('users')->whereRaw('LOWER(email) = ?', [Str::lower($email)])->value('id');
            if ($existingId) {
                return (int) $existingId;
            }
        }

        return null;
    }

    private function resolveTargetContext(): array
    {
        $moduleSlug = trim((string) ($this->options['module_slug'] ?? 'ammar'));
        $module = DB::table('modules')->where('slug', $moduleSlug)->first();
        if (!$module) {
            throw new RuntimeException("Target module '{$moduleSlug}' not found.");
        }

        $storeId = $this->options['store_id'] ?: DB::table('stores')->where('module_id', $module->id)->orderBy('id')->value('id');
        if (!$storeId) {
            throw new RuntimeException('Target store not found. Use --store-id to specify one.');
        }

        $zoneId = $this->options['zone_id']
            ?: DB::table('stores')->where('id', $storeId)->value('zone_id')
            ?: ((Schema::hasTable('zones') ? DB::table('zones')->orderBy('id')->value('id') : null));

        if ($this->options['customers'] && !$zoneId) {
            throw new RuntimeException('A target zone is required for customer import. Use --zone-id.');
        }

        $legacyStoragePath = $this->options['legacy_storage_path'] ?: env('LEGACY_AMMAR_STORAGE_PUBLIC_PATH');

        return [
            'module_id' => (int) $module->id,
            'module_slug' => $moduleSlug,
            'store_id' => (int) $storeId,
            'zone_id' => $zoneId ? (int) $zoneId : null,
            'legacy_storage_path' => $legacyStoragePath ? rtrim($legacyStoragePath, DIRECTORY_SEPARATOR) : null,
        ];
    }

    private function legacy(): Connection
    {
        if ($this->legacyConnection) {
            return $this->legacyConnection;
        }

        $config = [
            'driver' => 'mysql',
            'host' => env('LEGACY_AMMAR_DB_HOST'),
            'port' => env('LEGACY_AMMAR_DB_PORT', 3306),
            'database' => env('LEGACY_AMMAR_DB_DATABASE'),
            'username' => env('LEGACY_AMMAR_DB_USERNAME'),
            'password' => env('LEGACY_AMMAR_DB_PASSWORD'),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => false,
            'engine' => null,
        ];

        foreach (['host', 'database', 'username'] as $requiredKey) {
            if (empty($config[$requiredKey])) {
                throw new RuntimeException("Legacy database config missing: LEGACY_AMMAR_DB_" . strtoupper($requiredKey));
            }
        }

        config(['database.connections.legacy_ammar_import' => $config]);
        DB::purge('legacy_ammar_import');
        $this->legacyConnection = DB::connection('legacy_ammar_import');

        return $this->legacyConnection;
    }

    private function ensureLegacyTable(string $table): void
    {
        if (!$this->hasLegacyTable($table)) {
            throw new RuntimeException("Legacy table '{$table}' not found on import connection.");
        }
    }

    private function hasLegacyTable(string $table): bool
    {
        $this->legacy();
        return Schema::connection('legacy_ammar_import')->hasTable($table);
    }

    private function mappedTargetId(string $sourceTable, string $sourceId): ?int
    {
        $map = LegacyImportMap::query()
            ->where('source_system', self::SOURCE_SYSTEM)
            ->where('source_table', $sourceTable)
            ->where('source_id', $sourceId)
            ->first();

        return $map?->target_id;
    }

    private function storeImportMap(string $sourceTable, string $sourceId, string $targetTable, int $targetId, array $meta = []): void
    {
        LegacyImportMap::query()->updateOrCreate(
            [
                'source_system' => self::SOURCE_SYSTEM,
                'source_table' => $sourceTable,
                'source_id' => $sourceId,
                'target_table' => $targetTable,
            ],
            [
                'target_id' => $targetId,
                'meta' => $meta ?: null,
            ]
        );
    }

    private function syncStorageRecord(string $dataType, int $dataId, string $key, ?string $value, string $storageValue = 'public'): void
    {
        if (!$value) {
            return;
        }

        DB::table('storages')->updateOrInsert(
            [
                'data_type' => $dataType,
                'data_id' => (string) $dataId,
                'key' => $key,
            ],
            [
                'value' => $storageValue,
                'created_at' => now(),
                'updated_at' => now(),
            ]
        );
    }

    private function upsertTranslation(string $type, int $id, string $key, ?string $value, string $locale = 'en'): void
    {
        if ($value === null || trim($value) === '') {
            return;
        }

        DB::table('translations')->updateOrInsert(
            [
                'translationable_type' => $type,
                'translationable_id' => $id,
                'locale' => $locale,
                'key' => $key,
            ],
            [
                'value' => $value,
                'created_at' => now(),
                'updated_at' => now(),
            ]
        );
    }

    private function copyLegacyFile(string $directory, ?string $filename): ?string
    {
        $filename = trim((string) $filename);
        if ($filename === '') {
            return null;
        }

        $basename = basename(str_replace('\\', '/', $filename));
        if ($this->options['skip_images']) {
            return $basename;
        }

        $legacyStoragePath = $this->context['legacy_storage_path'];
        if (!$legacyStoragePath) {
            return $basename;
        }

        $sourceCandidates = [];
        if (str_contains($filename, '/')) {
            $sourceCandidates[] = $legacyStoragePath . DIRECTORY_SEPARATOR . ltrim(str_replace('/', DIRECTORY_SEPARATOR, $filename), DIRECTORY_SEPARATOR);
        }

        $sourceCandidates[] = $legacyStoragePath . DIRECTORY_SEPARATOR . trim($directory, '/\\') . DIRECTORY_SEPARATOR . $basename;
        $sourceCandidates[] = $legacyStoragePath . DIRECTORY_SEPARATOR . $basename;

        $sourcePath = null;
        foreach ($sourceCandidates as $candidate) {
            if (File::exists($candidate)) {
                $sourcePath = $candidate;
                break;
            }
        }

        if (!$sourcePath) {
            $this->summary['missing_images']++;
            return $basename;
        }

        $extension = pathinfo($basename, PATHINFO_EXTENSION) ?: 'png';
        $targetFilename = date('ymd') . Str::lower(Str::random(12)) . '.' . Str::lower($extension);
        $targetDirectory = storage_path('app/public/' . trim($directory, '/\\'));
        $targetPath = $targetDirectory . DIRECTORY_SEPARATOR . $targetFilename;

        if (!File::isDirectory($targetDirectory)) {
            File::makeDirectory($targetDirectory, 0775, true);
        }

        File::copy($sourcePath, $targetPath);
        $this->summary['images_copied']++;

        return $targetFilename;
    }

    private function buildFallbackLegacyAddress(object $legacyUser): ?array
    {
        if (!$this->stringValue($legacyUser, 'street_address') && !$this->stringValue($legacyUser, 'city')) {
            return null;
        }

        return [
            'id' => null,
            'address_type' => 'home',
            'address' => $this->stringValue($legacyUser, 'street_address'),
            'country' => $this->stringValue($legacyUser, 'country', 'Bangladesh'),
            'city' => $this->stringValue($legacyUser, 'city'),
            'state' => $this->stringValue($legacyUser, 'city'),
            'zip' => $this->stringValue($legacyUser, 'zip'),
            'phone' => $this->stringValue($legacyUser, 'phone'),
            'contact_person_name' => trim(($this->stringValue($legacyUser, 'f_name') . ' ' . $this->stringValue($legacyUser, 'l_name'))),
        ];
    }

    private function buildUniqueSlug(object $sourceRecord, ?int $existingId, string $targetTable): string
    {
        $base = $this->stringValue($sourceRecord, 'slug')
            ?: Str::slug($this->stringValue($sourceRecord, 'name', $targetTable . '-' . $this->stringValue($sourceRecord, 'id')));
        $base = $base ?: Str::slug($targetTable . '-' . $this->stringValue($sourceRecord, 'id'));

        $slug = $base;
        $suffix = 1;

        while (
            DB::table($targetTable)
                ->where('slug', $slug)
                ->when($existingId, fn ($query) => $query->where('id', '!=', $existingId))
                ->exists()
        ) {
            $slug = $base . '-' . $suffix;
            $suffix++;
        }

        return $slug;
    }

    private function generateUniqueRefCode(?int $exceptUserId = null): string
    {
        do {
            $code = strtoupper(Str::random(10));
            $exists = DB::table('users')
                ->where('ref_code', $code)
                ->when($exceptUserId, fn ($query) => $query->where('id', '!=', $exceptUserId))
                ->exists();
        } while ($exists);

        return $code;
    }

    private function legacyColors(): Collection
    {
        if ($this->legacyColors !== null) {
            return $this->legacyColors;
        }

        if (!$this->hasLegacyTable('colors')) {
            $this->legacyColors = collect();
            return $this->legacyColors;
        }

        $this->legacyColors = $this->legacy()
            ->table('colors')
            ->pluck('name', 'id')
            ->map(fn ($name) => Str::lower(trim((string) $name)));

        return $this->legacyColors;
    }

    private function matchVariationColorImage(string $variationType, array $colorImageMap): ?string
    {
        $variationType = Str::lower($variationType);
        foreach ($colorImageMap as $colorName => $image) {
            if (str_contains($variationType, $colorName)) {
                return $image;
            }
        }

        return null;
    }

    private function jsonArray($value): array
    {
        if (is_array($value)) {
            return $value;
        }

        if (!is_string($value) || trim($value) === '') {
            return [];
        }

        $decoded = json_decode($value, true);
        return is_array($decoded) ? $decoded : [];
    }

    private function stringValue(object|array $row, string $key, string $default = ''): string
    {
        $value = data_get($row, $key, $default);
        return is_scalar($value) ? trim((string) $value) : $default;
    }

    private function intValue(object|array $row, string $key, ?int $default = null): ?int
    {
        $value = data_get($row, $key, $default);
        if ($value === null || $value === '') {
            return $default;
        }

        return (int) $value;
    }

    private function floatValue(object|array $row, string $key, float $default = 0): float
    {
        $value = data_get($row, $key, $default);
        return is_numeric($value) ? (float) $value : $default;
    }

    private function dateValue($value)
    {
        return $value ?: now();
    }

    private function normalizeString(?string $value): ?string
    {
        $value = trim((string) $value);
        return $value !== '' ? $value : null;
    }

    private function normalizeEmail(?string $value): ?string
    {
        $value = trim(Str::lower((string) $value));
        return $value !== '' ? $value : null;
    }

    private function boolish($value): bool
    {
        return filter_var($value, FILTER_VALIDATE_BOOLEAN, FILTER_NULL_ON_FAILURE) ?? ((int) $value === 1);
    }

    private function fallbackFirstName(object $legacyUser): string
    {
        $name = trim($this->stringValue($legacyUser, 'name'));
        if ($name !== '') {
            return Str::before($name, ' ');
        }

        return 'Customer';
    }

    private function chunkSize(): int
    {
        return max(1, (int) ($this->options['chunk'] ?? 100));
    }

    private function dryRun(): bool
    {
        return (bool) ($this->options['dry_run'] ?? false);
    }
}
