nshop/app/Console/Commands/MigrateAsteriaOrders.php

<?php

namespace App\Console\Commands;

use App\Services\Asteria\Magento1OrderReader;
use Illuminate\Console\Command;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Schema;
use Webkul\Core\Models\Channel;
use Webkul\Customer\Models\Customer;
use Webkul\Product\Models\Product;
use Webkul\Sales\Models\Order;
use Webkul\Sales\Models\OrderAddress;

/**
 * Migrates storefront orders from Asteria (Magento 1.x).
 *
 * Also imports Magento shipments/tracking. Does not create invoices/refunds,
 * does not decrement inventory, and does not fire checkout.order.save.after listeners.
 *
 * Usage
 * ─────
 * php artisan orders:migrate-asteria
 * php artisan orders:migrate-asteria --batch-size=200
 * php artisan orders:migrate-asteria --reset-progress
 * php artisan orders:migrate-asteria --dry-run
 */
class MigrateAsteriaOrders extends Command
{
    protected $signature = 'orders:migrate-asteria
        {--batch-size=500     : Number of Magento orders per batch}
        {--reset-progress     : Ignore saved progress and start from entity_id=0}
        {--dry-run            : Count records without writing}
        {--connection=asteria : Laravel DB connection for the Asteria database}';

    protected $description = 'Migrate Asteria (Magento 1.x) orders into Bagisto';

    private const PROGRESS_KEY = 'migrate_asteria_orders_last_id';

    private const BAGISTO_PRODUCT_TYPES = [
        'simple',
        'configurable',
        'virtual',
        'downloadable',
        'bundle',
        'grouped',
    ];

    private const INSERT_CHUNK = 200;

    /** @var array<string, bool>|null */
    private ?array $destinationOrderColumns = null;

    /** @var array{id: int|null, name: string|null}|null */
    private ?array $defaultInventorySource = null;

    public function handle(): int
    {
        $connection = (string) $this->option('connection');
        $batchSize = max(1, (int) $this->option('batch-size'));
        $resetProgress = (bool) $this->option('reset-progress');
        $dryRun = (bool) $this->option('dry-run');

        DB::disableQueryLog();

        try {
            DB::connection($connection)->getPdo();
        } catch (\Throwable $e) {
            $this->error("Cannot connect to Asteria DB (connection='{$connection}'): ".$e->getMessage());

            return self::FAILURE;
        }

        foreach ([
            'sales_flat_order',
            'sales_flat_order_item',
            'sales_flat_order_address',
            'sales_flat_order_payment',
        ] as $table) {
            if (! Schema::connection($connection)->hasTable($table)) {
                $this->error("Asteria table '{$table}' is missing on connection '{$connection}'.");

                return self::FAILURE;
            }
        }

        if (! Schema::hasColumn('orders', 'migrated_from_asteria_id')) {
            $this->error('orders.migrated_from_asteria_id is missing. Run php artisan migrate.');

            return self::FAILURE;
        }

        if (! Schema::hasColumn('orders', 'reward_points_used')) {
            $this->warn('orders.reward_points_used is missing. Run php artisan migrate to import order reward points.');
        }

        if (! Schema::hasColumn('orders', 'shipping_insurance_amount')) {
            $this->warn('orders.shipping_insurance_amount is missing. Run php artisan migrate to import lost-package insurance.');
        }

        if (! Schema::hasColumn('orders', 'remote_ip')) {
            $this->warn('orders.remote_ip is missing. Run php artisan migrate to import Magento remote_ip.');
        }

        if (! Schema::hasColumn('orders', 'giftcard_amount')) {
            $this->warn('orders.giftcard_amount is missing. Gift card amounts will not be imported.');
        }

        if (! Schema::hasColumn('orders', 'vip_plus_amount')) {
            $this->warn('orders.vip_plus_amount is missing. Plus fee amounts will not be imported.');
        }

        if (! Schema::hasColumn('orders', 'vip_discount_amount')) {
            $this->warn('orders.vip_discount_amount is missing. Magento member discounts will not be imported.');
        }

        if (! Schema::hasColumn('shipments', 'migrated_from_asteria_id')) {
            $this->warn('shipments.migrated_from_asteria_id is missing. Run php artisan migrate to import Magento shipments.');
        }

        $channel = core()->getDefaultChannel() ?? core()->getCurrentChannel();

        if (! $channel) {
            $this->error('Bagisto default channel was not found.');

            return self::FAILURE;
        }

        $reader = new Magento1OrderReader($connection);
        $lastId = $resetProgress ? 0 : (int) Cache::get(self::PROGRESS_KEY, 0);

        if ($resetProgress) {
            Cache::forget(self::PROGRESS_KEY);
        }

        if ($lastId > 0) {
            $this->line("Resuming from Asteria entity_id > {$lastId} (use --reset-progress to restart).");
        }

        $created = 0;
        $skipped = 0;
        $itemsImported = 0;
        $addressesImported = 0;
        $shipmentsImported = 0;
        $batchNumber = 0;

        $this->info($dryRun ? '[DRY RUN] Scanning Magento orders…' : 'Migrating Magento orders…');

        do {
            $started = microtime(true);
            $orders = $reader->fetchOrders($lastId, $batchSize);

            if ($orders->isEmpty()) {
                break;
            }

            $batchNumber++;
            $lastId = (int) $orders->max('entity_id');
            $orderIds = $orders->pluck('entity_id')->map(fn ($id) => (int) $id)->all();

            $items = $reader->fetchItems($orderIds)->groupBy(fn (array $row) => (int) $row['order_id']);
            $addresses = $reader->fetchAddresses($orderIds)->groupBy(fn (array $row) => (int) $row['parent_id']);
            $payments = $reader->fetchPayments($orderIds)->groupBy(fn (array $row) => (int) $row['parent_id']);
            $rewardPoints = $reader->fetchRewardPoints($orderIds)->keyBy(fn (array $row) => (int) $row['order_id']);

            if ($dryRun) {
                $shipments = $reader->fetchShipments($orderIds);
                $created += $orders->count();
                $itemsImported += $items->flatten(1)->count();
                $addressesImported += $addresses->flatten(1)->count();
                $shipmentsImported += $shipments->count();
                $this->line(sprintf(
                    '  Batch #%d: %d orders, %d items, %d addresses, %d shipments (last entity_id=%d) [skipped – dry-run] (%.1fs)',
                    $batchNumber,
                    $orders->count(),
                    $items->flatten(1)->count(),
                    $addresses->flatten(1)->count(),
                    $shipments->count(),
                    $lastId,
                    microtime(true) - $started
                ));

                continue;
            }

            [$customersByAsteriaId, $customersByEmail] = $this->loadCustomers($orders);
            $productIndex = $this->loadProductIndex($items->collapse(), $reader);

            $result = $this->persistBatch(
                $orders,
                $items,
                $addresses,
                $payments,
                $rewardPoints,
                $channel,
                $customersByAsteriaId,
                $customersByEmail,
                $productIndex
            );

            $shipments = $reader->fetchShipments($orderIds);
            $shipmentIds = $shipments->pluck('entity_id')->map(fn ($id) => (int) $id)->all();
            $shipmentItems = $reader->fetchShipmentItems($shipmentIds)->groupBy(fn (array $row) => (int) $row['parent_id']);
            $shipmentTracks = $reader->fetchShipmentTracks($shipmentIds)->groupBy(fn (array $row) => (int) $row['parent_id']);
            $extras = $this->syncOrderExtras($orders, $items, $payments, $shipments, $shipmentItems, $shipmentTracks);

            Cache::put(self::PROGRESS_KEY, $lastId, now()->addDays(30));

            $created += $result['created'];
            $skipped += $result['skipped'];
            $itemsImported += $result['items'];
            $addressesImported += $result['addresses'];
            $shipmentsImported += $extras['shipments'];

            $this->line(sprintf(
                '  Batch #%d: created=%d skipped=%d items=%d addresses=%d shipments=%d (last entity_id=%d) (%.1fs)',
                $batchNumber,
                $result['created'],
                $result['skipped'],
                $result['items'],
                $result['addresses'],
                $extras['shipments'],
                $lastId,
                microtime(true) - $started
            ));

            Log::info('MigrateAsteriaOrders: batch '.$batchNumber.', last_id='.$lastId);
        } while ($orders->count() === $batchSize);

        $this->newLine();
        $this->info("Done. Batches: {$batchNumber}, created: {$created}, skipped: {$skipped}, items: {$itemsImported}, addresses: {$addressesImported}, shipments: {$shipmentsImported}.");

        if (! $dryRun) {
            $linked = $this->backfillMissingProductIds($reader);

            if ($linked > 0) {
                $this->info("Backfilled product_id on {$linked} existing order items.");
            }
        }

        return self::SUCCESS;
    }

    /**
     * @param  Collection<int, array<string, mixed>>  $orders
     * @param  Collection<int, Collection<int, array<string, mixed>>>  $items
     * @param  Collection<int, Collection<int, array<string, mixed>>>  $addresses
     * @param  Collection<int, Collection<int, array<string, mixed>>>  $payments
     * @param  Collection<int, array<string, mixed>>  $rewardPoints
     * @param  array<int, Customer>  $customersByAsteriaId
     * @param  array<string, Customer>  $customersByEmail
     * @param  array{by_sku: array<string, Product>, by_asteria_id: array<int, Product>, catalog_sku: array<int, string>}  $productIndex
     * @return array{created: int, skipped: int, items: int, addresses: int}
     */
    private function persistBatch(
        Collection $orders,
        Collection $items,
        Collection $addresses,
        Collection $payments,
        Collection $rewardPoints,
        Channel $channel,
        array $customersByAsteriaId,
        array $customersByEmail,
        array $productIndex
    ): array {
        $asteriaIds = $orders->pluck('entity_id')->map(fn ($id) => (int) $id)->filter()->unique()->values()->all();
        $incrementIds = $orders
            ->pluck('increment_id')
            ->map(fn ($id) => trim((string) $id))
            ->filter()
            ->unique()
            ->values()
            ->all();

        $existingAsteria = $asteriaIds === []
            ? []
            : DB::table('orders')
                ->whereIn('migrated_from_asteria_id', $asteriaIds)
                ->pluck('migrated_from_asteria_id')
                ->map(fn ($id) => (int) $id)
                ->flip()
                ->all();

        $existingIncrements = $incrementIds === []
            ? []
            : DB::table('orders')
                ->whereIn('increment_id', $incrementIds)
                ->pluck('increment_id')
                ->map(fn ($id) => (string) $id)
                ->flip()
                ->all();

        $now = now()->format('Y-m-d H:i:s');
        $orderInserts = [];
        $pending = [];
        $skipped = 0;
        $seenIncrements = [];

        foreach ($orders as $row) {
            $asteriaId = (int) $row['entity_id'];
            $incrementId = trim((string) ($row['increment_id'] ?? ''));

            if ($asteriaId < 1 || $incrementId === ''
                || isset($existingAsteria[$asteriaId])
                || isset($existingIncrements[$incrementId])
                || isset($seenIncrements[$incrementId])) {
                $skipped++;

                continue;
            }

            $seenIncrements[$incrementId] = true;
            $orderInserts[] = $this->buildOrderRow(
                $row,
                $channel,
                $customersByAsteriaId,
                $customersByEmail,
                $items->get($asteriaId, collect()),
                $rewardPoints->get($asteriaId),
                $now
            );
            $pending[] = $row;
        }

        $created = count($orderInserts);
        $importedItems = 0;
        $importedAddresses = 0;

        if ($orderInserts === []) {
            return ['created' => 0, 'skipped' => $skipped, 'items' => 0, 'addresses' => 0];
        }

        DB::transaction(function () use (
            $orderInserts,
            $pending,
            $items,
            $addresses,
            $payments,
            $productIndex,
            $customersByAsteriaId,
            $customersByEmail,
            &$importedItems,
            &$importedAddresses
        ) {
            $this->insertRows('orders', $orderInserts);

            $idMap = DB::table('orders')
                ->whereIn('migrated_from_asteria_id', array_column($orderInserts, 'migrated_from_asteria_id'))
                ->pluck('id', 'migrated_from_asteria_id')
                ->mapWithKeys(fn ($id, $asteriaId) => [(int) $asteriaId => (int) $id])
                ->all();

            $paymentInserts = [];
            $addressInserts = [];
            $parentItemInserts = [];
            $childItemRows = [];
            $now = now()->format('Y-m-d H:i:s');

            foreach ($pending as $row) {
                $asteriaId = (int) $row['entity_id'];
                $orderId = $idMap[$asteriaId] ?? null;

                if (! $orderId) {
                    continue;
                }

                $customer = $this->resolveCustomer($row, $customersByAsteriaId, $customersByEmail);
                $email = strtolower(trim((string) ($row['customer_email'] ?? '')));

                if ($email === '' && $customer) {
                    $email = strtolower((string) $customer->email);
                }

                $paymentInserts[] = $this->buildPaymentRow($orderId, $payments->get($asteriaId, collect())->first(), $now);

                foreach ($addresses->get($asteriaId, collect()) as $addressRow) {
                    $addressInserts[] = $this->buildAddressRow($orderId, $addressRow, $customer, $email, $row, $now);
                    $importedAddresses++;
                }

                $itemRows = $items->get($asteriaId, collect());
                $parents = $itemRows->filter(fn (array $item) => empty($item['parent_item_id']));
                $children = $itemRows->filter(fn (array $item) => ! empty($item['parent_item_id']));

                foreach ($parents as $itemRow) {
                    $parentItemInserts[] = $this->buildItemRow($orderId, $itemRow, null, $productIndex, $now);
                    $importedItems++;
                }

                foreach ($children as $itemRow) {
                    $childItemRows[] = ['order_id' => $orderId, 'row' => $itemRow];
                    $importedItems++;
                }
            }

            if ($paymentInserts !== []) {
                $this->insertRows('order_payment', $paymentInserts);
            }

            if ($addressInserts !== []) {
                $this->insertRows('addresses', $addressInserts);
            }

            if ($parentItemInserts !== []) {
                $this->insertRows('order_items', $parentItemInserts);
            }

            if ($childItemRows !== []) {
                $itemIdMap = $this->loadInsertedItemIds(array_values($idMap));

                $childInserts = [];

                foreach ($childItemRows as $child) {
                    $parentId = $itemIdMap[(int) $child['row']['parent_item_id']] ?? null;
                    $childInserts[] = $this->buildItemRow($child['order_id'], $child['row'], $parentId, $productIndex, $now);
                }

                $this->insertRows('order_items', $childInserts);
            }
        });

        return [
            'created'   => $created,
            'skipped'   => $skipped,
            'items'     => $importedItems,
            'addresses' => $importedAddresses,
        ];
    }

    /**
     * Backfill remote_ip / product options / PayPal details and import Magento
     * shipments for both newly created and previously skipped Asteria orders.
     *
     * @param  Collection<int, array<string, mixed>>  $orders
     * @param  Collection<int, Collection<int, array<string, mixed>>>  $items
     * @param  Collection<int, Collection<int, array<string, mixed>>>  $payments
     * @param  Collection<int, array<string, mixed>>  $shipments
     * @param  Collection<int, Collection<int, array<string, mixed>>>  $shipmentItems
     * @param  Collection<int, Collection<int, array<string, mixed>>>  $shipmentTracks
     * @return array{shipments: int}
     */
    private function syncOrderExtras(
        Collection $orders,
        Collection $items,
        Collection $payments,
        Collection $shipments,
        Collection $shipmentItems,
        Collection $shipmentTracks
    ): array {
        $asteriaIds = $orders->pluck('entity_id')->map(fn ($id) => (int) $id)->filter()->unique()->values()->all();

        if ($asteriaIds === []) {
            return ['shipments' => 0];
        }

        $orderColumns = ['id', 'migrated_from_asteria_id', 'customer_id', 'customer_type'];

        if ($this->hasDestinationColumn('remote_ip')) {
            $orderColumns[] = 'remote_ip';
        }

        if ($this->hasDestinationColumn('giftcard_amount')) {
            $orderColumns[] = 'giftcard_amount';
            $orderColumns[] = 'base_giftcard_amount';

            if ($this->hasDestinationColumn('giftcard_number')) {
                $orderColumns[] = 'giftcard_number';
            }
        }

        if ($this->hasDestinationColumn('vip_plus_amount')) {
            $orderColumns[] = 'vip_plus_amount';
            $orderColumns[] = 'base_vip_plus_amount';
        }

        if ($this->hasDestinationColumn('vip_discount_amount')) {
            $orderColumns[] = 'vip_discount_amount';
            $orderColumns[] = 'base_vip_discount_amount';
        }

        $bagistoOrders = DB::table('orders')
            ->whereIn('migrated_from_asteria_id', $asteriaIds)
            ->get($orderColumns);

        if ($bagistoOrders->isEmpty()) {
            return ['shipments' => 0];
        }

        $orderIdMap = $bagistoOrders
            ->mapWithKeys(fn ($order) => [(int) $order->migrated_from_asteria_id => (int) $order->id])
            ->all();

        $importedShipments = 0;

        DB::transaction(function () use (
            $orders,
            $items,
            $payments,
            $shipments,
            $shipmentItems,
            $shipmentTracks,
            $bagistoOrders,
            $orderIdMap,
            &$importedShipments
        ) {
            $this->syncRemoteIps($orders, $bagistoOrders);
            $this->syncGiftcardAndPlus($orders, $bagistoOrders);
            $this->syncPaypalPayments($payments, $orderIdMap);
            $this->syncItemProductOptions($items, $orderIdMap);
            $importedShipments = $this->persistShipments(
                $shipments,
                $shipmentItems,
                $shipmentTracks,
                $bagistoOrders,
                $orderIdMap
            );
        });

        return ['shipments' => $importedShipments];
    }

    /**
     * @param  Collection<int, array<string, mixed>>  $orders
     * @param  Collection<int, object>  $bagistoOrders
     */
    private function syncRemoteIps(Collection $orders, $bagistoOrders): void
    {
        if (! $this->hasDestinationColumn('remote_ip')) {
            return;
        }

        $remoteIps = $orders
            ->mapWithKeys(fn (array $row) => [(int) $row['entity_id'] => $this->nullableString($row['remote_ip'] ?? null)])
            ->all();

        foreach ($bagistoOrders as $order) {
            $remoteIp = $remoteIps[(int) $order->migrated_from_asteria_id] ?? null;

            if ($remoteIp === null || (string) $order->remote_ip === $remoteIp) {
                continue;
            }

            DB::table('orders')->where('id', $order->id)->update(['remote_ip' => $remoteIp]);
        }
    }

    /**
     * @param  Collection<int, array<string, mixed>>  $orders
     * @param  Collection<int, object>  $bagistoOrders
     */
    private function syncGiftcardAndPlus(Collection $orders, $bagistoOrders): void
    {
        if (! $this->hasDestinationColumn('giftcard_amount')
            && ! $this->hasDestinationColumn('vip_plus_amount')
            && ! $this->hasDestinationColumn('vip_discount_amount')) {
            return;
        }

        $byAsteriaId = $orders->keyBy(fn (array $row) => (int) $row['entity_id']);

        foreach ($bagistoOrders as $order) {
            $row = $byAsteriaId->get((int) $order->migrated_from_asteria_id);

            if (! is_array($row)) {
                continue;
            }

            $payload = $this->mapGiftcardAndPlus($row);

            if ($payload === []) {
                continue;
            }

            $changed = false;

            foreach ($payload as $column => $value) {
                $current = $order->{$column} ?? null;

                if (is_numeric($value)) {
                    if ($this->money($current) !== $this->money($value)) {
                        $changed = true;
                        break;
                    }

                    continue;
                }

                if ((string) ($current ?? '') !== (string) ($value ?? '')) {
                    $changed = true;
                    break;
                }
            }

            if (! $changed) {
                continue;
            }

            DB::table('orders')->where('id', $order->id)->update($payload);
        }
    }

    /**
     * @param  Collection<int, Collection<int, array<string, mixed>>>  $payments
     * @param  array<int, int>  $orderIdMap
     */
    private function syncPaypalPayments(Collection $payments, array $orderIdMap): void
    {
        $bagistoOrderIds = array_values($orderIdMap);

        if ($bagistoOrderIds === [] || $payments->isEmpty()) {
            return;
        }

        $existing = DB::table('order_payment')
            ->whereIn('order_id', $bagistoOrderIds)
            ->get(['id', 'order_id', 'additional'])
            ->keyBy(fn ($row) => (int) $row->order_id);

        foreach ($orderIdMap as $asteriaOrderId => $bagistoOrderId) {
            $paymentRow = $payments->get($asteriaOrderId, collect())->first();

            if (! is_array($paymentRow) || ! $this->isPaypalMethod($paymentRow['method'] ?? null)) {
                continue;
            }

            $current = $existing->get($bagistoOrderId);

            if (! $current) {
                continue;
            }

            $additional = $this->appendPaypalAdditional(
                $this->decodeAdditional($current->additional),
                $paymentRow
            );

            $transactionId = $this->nullableString($paymentRow['last_trans_id'] ?? null);

            if ($transactionId !== null) {
                $additional['last_trans_id'] = $transactionId;
            }

            if ($additional == $this->decodeAdditional($current->additional)) {
                continue;
            }

            DB::table('order_payment')->where('id', $current->id)->update([
                'additional' => $this->encodeAdditional($additional),
            ]);
        }
    }

    /**
     * @param  Collection<int, Collection<int, array<string, mixed>>>  $items
     * @param  array<int, int>  $orderIdMap
     */
    private function syncItemProductOptions(Collection $items, array $orderIdMap): void
    {
        $bagistoOrderIds = array_values($orderIdMap);

        if ($bagistoOrderIds === []) {
            return;
        }

        $existing = [];

        foreach (DB::table('order_items')->whereIn('order_id', $bagistoOrderIds)->get(['id', 'additional']) as $item) {
            $additional = $this->decodeAdditional($item->additional);
            $asteriaItemId = (int) ($additional['asteria_item_id'] ?? 0);

            if ($asteriaItemId > 0) {
                $existing[$asteriaItemId] = [
                    'id'         => (int) $item->id,
                    'additional' => $additional,
                ];
            }
        }

        foreach ($items->flatten(1) as $row) {
            if (! is_array($row)) {
                continue;
            }

            $asteriaItemId = (int) ($row['item_id'] ?? 0);
            $current = $existing[$asteriaItemId] ?? null;

            if (! $current) {
                continue;
            }

            $merged = $this->mergeItemAdditional($current['additional'], $row);

            if ($merged === $current['additional']) {
                continue;
            }

            DB::table('order_items')->where('id', $current['id'])->update([
                'additional' => $this->encodeAdditional($merged),
            ]);
        }
    }

    /**
     * @param  Collection<int, array<string, mixed>>  $shipments
     * @param  Collection<int, Collection<int, array<string, mixed>>>  $shipmentItems
     * @param  Collection<int, Collection<int, array<string, mixed>>>  $shipmentTracks
     * @param  Collection<int, object>  $bagistoOrders
     * @param  array<int, int>  $orderIdMap
     */
    private function persistShipments(
        Collection $shipments,
        Collection $shipmentItems,
        Collection $shipmentTracks,
        $bagistoOrders,
        array $orderIdMap
    ): int {
        if ($shipments->isEmpty() || ! Schema::hasColumn('shipments', 'migrated_from_asteria_id')) {
            return 0;
        }

        $asteriaShipmentIds = $shipments
            ->pluck('entity_id')
            ->map(fn ($id) => (int) $id)
            ->filter()
            ->unique()
            ->values()
            ->all();

        $existing = $asteriaShipmentIds === []
            ? []
            : DB::table('shipments')
                ->whereIn('migrated_from_asteria_id', $asteriaShipmentIds)
                ->pluck('migrated_from_asteria_id')
                ->map(fn ($id) => (int) $id)
                ->flip()
                ->all();

        $ordersByAsteriaId = $bagistoOrders->keyBy(fn ($order) => (int) $order->migrated_from_asteria_id);
        $shippingAddressIds = DB::table('addresses')
            ->whereIn('order_id', array_values($orderIdMap))
            ->where('address_type', OrderAddress::ADDRESS_TYPE_SHIPPING)
            ->pluck('id', 'order_id');
        $itemIdMap = $this->loadInsertedItemIds(array_values($orderIdMap));
        $orderItems = $this->loadOrderItemsById(array_values($itemIdMap));
        $inventory = $this->defaultInventorySource();
        $hasInclTax = Schema::hasColumn('shipment_items', 'price_incl_tax');
        $now = now()->format('Y-m-d H:i:s');
        $inserts = [];
        $pending = [];

        foreach ($shipments as $row) {
            $asteriaId = (int) ($row['entity_id'] ?? 0);
            $magentoOrderId = (int) ($row['order_id'] ?? 0);
            $orderId = $orderIdMap[$magentoOrderId] ?? null;
            $order = $ordersByAsteriaId->get($magentoOrderId);

            if ($asteriaId < 1 || ! $orderId || ! $order || isset($existing[$asteriaId])) {
                continue;
            }

            $tracks = $shipmentTracks->get($asteriaId, collect());
            [$carrierCode, $carrierTitle, $trackNumber] = $this->mapShipmentTracks($tracks);

            $inserts[] = [
                'migrated_from_asteria_id' => $asteriaId,
                'status'                   => null,
                'total_qty'                => $this->qty($row['total_qty'] ?? 0),
                'total_weight'             => $this->money($row['total_weight'] ?? 0),
                'carrier_code'             => $carrierCode,
                'carrier_title'            => $carrierTitle,
                'track_number'             => $trackNumber,
                'email_sent'               => ((int) ($row['email_sent'] ?? 0)) === 1 ? 1 : 0,
                'customer_id'              => $order->customer_id,
                'customer_type'            => $order->customer_type,
                'order_id'                 => $orderId,
                'order_address_id'         => $shippingAddressIds[$orderId] ?? null,
                'inventory_source_id'      => $inventory['id'],
                'inventory_source_name'    => $inventory['name'],
                'created_at'               => ! empty($row['created_at']) ? $row['created_at'] : $now,
                'updated_at'               => $now,
            ];
            $pending[] = $row;
        }

        if ($inserts === []) {
            return 0;
        }

        $this->insertRows('shipments', $inserts);

        $idMap = DB::table('shipments')
            ->whereIn('migrated_from_asteria_id', array_column($inserts, 'migrated_from_asteria_id'))
            ->pluck('id', 'migrated_from_asteria_id')
            ->mapWithKeys(fn ($id, $asteriaId) => [(int) $asteriaId => (int) $id])
            ->all();

        $itemInserts = [];

        foreach ($pending as $row) {
            $asteriaId = (int) $row['entity_id'];
            $shipmentId = $idMap[$asteriaId] ?? null;

            if (! $shipmentId) {
                continue;
            }

            foreach ($shipmentItems->get($asteriaId, collect()) as $itemRow) {
                $orderItemId = isset($itemRow['order_item_id']) ? (int) $itemRow['order_item_id'] : 0;
                $bagistoItemId = $orderItemId > 0 ? ($itemIdMap[$orderItemId] ?? null) : null;
                $orderItem = $bagistoItemId ? ($orderItems[$bagistoItemId] ?? null) : null;
                $price = array_key_exists('price', $itemRow) && $itemRow['price'] !== null
                    ? $this->money($itemRow['price'])
                    : $this->money($orderItem->price ?? 0);
                $qty = $this->qty($itemRow['qty'] ?? 0);
                $total = array_key_exists('row_total', $itemRow) && $itemRow['row_total'] !== null
                    ? $this->money($itemRow['row_total'])
                    : $price * $qty;
                $basePrice = $this->money($orderItem->base_price ?? $price);
                $payload = [
                    'name'         => $this->nullableString($itemRow['name'] ?? null)
                        ?? $this->nullableString($orderItem->name ?? null)
                        ?? '-',
                    'description'  => $this->nullableString($itemRow['description'] ?? null),
                    'sku'          => $this->nullableString($itemRow['sku'] ?? null)
                        ?? $this->nullableString($orderItem->sku ?? null),
                    'qty'          => $qty,
                    'weight'       => $this->money($itemRow['weight'] ?? $orderItem->weight ?? 0),
                    'price'        => $price,
                    'base_price'   => $basePrice,
                    'total'        => $total,
                    'base_total'   => $basePrice * $qty,
                    'product_id'   => $orderItem->product_id ?? null,
                    'product_type' => $orderItem->product_type ?? null,
                    'order_item_id'=> $bagistoItemId,
                    'shipment_id'  => $shipmentId,
                    'additional'   => $this->encodeAdditional([
                        'asteria_shipment_item_id' => (int) ($itemRow['entity_id'] ?? 0),
                    ]),
                    'created_at'   => $now,
                    'updated_at'   => $now,
                ];

                if ($hasInclTax) {
                    $payload['price_incl_tax'] = $this->money($orderItem->price_incl_tax ?? $price);
                    $payload['base_price_incl_tax'] = $this->money($orderItem->base_price_incl_tax ?? $basePrice);
                }

                $itemInserts[] = $payload;
            }
        }

        if ($itemInserts !== []) {
            $this->insertRows('shipment_items', $itemInserts);
        }

        return count($inserts);
    }

    /**
     * @param  array<int, int>  $ids
     * @return array<int, object>
     */
    private function loadOrderItemsById(array $ids): array
    {
        if ($ids === []) {
            return [];
        }

        $columns = ['id', 'name', 'sku', 'weight', 'price', 'base_price', 'product_id', 'product_type'];

        if (Schema::hasColumn('order_items', 'price_incl_tax')) {
            $columns[] = 'price_incl_tax';
            $columns[] = 'base_price_incl_tax';
        }

        return DB::table('order_items')
            ->whereIn('id', $ids)
            ->get($columns)
            ->keyBy(fn ($item) => (int) $item->id)
            ->all();
    }

    /**
     * @param  Collection<int, array<string, mixed>>  $tracks
     * @return array{0: ?string, 1: ?string, 2: ?string}
     */
    private function mapShipmentTracks($tracks): array
    {
        $numbers = [];
        $carrierCode = null;
        $carrierTitle = null;

        foreach ($tracks as $track) {
            $number = $this->nullableString($track['track_number'] ?? null);

            if ($number !== null) {
                $numbers[] = $number;
            }

            if ($carrierCode === null) {
                $carrierCode = $this->nullableString($track['carrier_code'] ?? null);
                $carrierTitle = $this->nullableString($track['title'] ?? null);
            }
        }

        $numbers = array_values(array_unique($numbers));

        return [
            $carrierCode,
            $carrierTitle,
            $numbers === [] ? null : implode(', ', $numbers),
        ];
    }

    /**
     * @return array{id: int|null, name: string|null}
     */
    private function defaultInventorySource(): array
    {
        if ($this->defaultInventorySource !== null) {
            return $this->defaultInventorySource;
        }

        $row = Schema::hasTable('inventory_sources')
            ? DB::table('inventory_sources')->orderBy('id')->first(['id', 'name'])
            : null;

        return $this->defaultInventorySource = [
            'id'   => $row ? (int) $row->id : null,
            'name' => $row ? $this->nullableString($row->name ?? null) : null,
        ];
    }

    /**
     * @param  array<string, mixed>  $row
     * @param  array<int, Customer>  $customersByAsteriaId
     * @param  array<string, Customer>  $customersByEmail
     * @param  Collection<int, array<string, mixed>>  $itemRows
     * @param  array<string, mixed>|null  $rewardRow
     * @return array<string, mixed>
     */
    private function buildOrderRow(
        array $row,
        Channel $channel,
        array $customersByAsteriaId,
        array $customersByEmail,
        $itemRows,
        ?array $rewardRow,
        string $now
    ): array {
        $customer = $this->resolveCustomer($row, $customersByAsteriaId, $customersByEmail);
        $email = strtolower(trim((string) ($row['customer_email'] ?? '')));

        if ($email === '' && $customer) {
            $email = strtolower((string) $customer->email);
        }

        $subTotal = $this->money($row['subtotal'] ?? 0);
        $baseSubTotal = $this->money($row['base_subtotal'] ?? $subTotal);
        $taxAmount = $this->money($row['tax_amount'] ?? 0);
        $baseTaxAmount = $this->money($row['base_tax_amount'] ?? $taxAmount);
        $shippingAmount = $this->money($row['shipping_amount'] ?? 0);
        $baseShippingAmount = $this->money($row['base_shipping_amount'] ?? $shippingAmount);
        $shippingTaxAmount = $this->money($row['shipping_tax_amount'] ?? 0);
        $baseShippingTaxAmount = $this->money($row['base_shipping_tax_amount'] ?? $shippingTaxAmount);
        $discountAmount = $this->money($row['discount_amount'] ?? 0);
        $baseDiscountAmount = $this->money($row['base_discount_amount'] ?? $discountAmount);
        $grandTotal = $this->money($row['grand_total'] ?? 0);
        $baseGrandTotal = $this->money($row['base_grand_total'] ?? $grandTotal);

        $subTotalInclTax = array_key_exists('subtotal_incl_tax', $row)
            ? $this->money($row['subtotal_incl_tax'])
            : $subTotal + $taxAmount;
        $baseSubTotalInclTax = array_key_exists('base_subtotal_incl_tax', $row)
            ? $this->money($row['base_subtotal_incl_tax'])
            : $baseSubTotal + $baseTaxAmount;
        $shippingInclTax = array_key_exists('shipping_incl_tax', $row)
            ? $this->money($row['shipping_incl_tax'])
            : $shippingAmount + $shippingTaxAmount;
        $baseShippingInclTax = array_key_exists('base_shipping_incl_tax', $row)
            ? $this->money($row['base_shipping_incl_tax'])
            : $baseShippingAmount + $baseShippingTaxAmount;

        $insert = [
            'migrated_from_asteria_id'      => (int) $row['entity_id'],
            'increment_id'                  => trim((string) $row['increment_id']),
            'status'                        => $this->mapStatus($row),
            'channel_name'                  => $channel->name,
            'is_guest'                      => $customer ? 0 : 1,
            'customer_email'                => $email !== '' ? $email : null,
            'customer_first_name'           => $this->requiredName($row['customer_firstname'] ?? null, $customer?->first_name ?? $email),
            'customer_last_name'            => trim((string) ($row['customer_lastname'] ?? '')) ?: ($customer?->last_name ?? '-'),
            'customer_id'                   => $customer?->id,
            'customer_type'                 => $customer ? Customer::class : null,
            'channel_id'                    => $channel->id,
            'channel_type'                  => get_class($channel),
            'cart_id'                       => null,
            'shipping_method'               => $this->nullableString($row['shipping_method'] ?? null),
            'shipping_title'                => $this->nullableString($row['shipping_description'] ?? null),
            'shipping_description'          => $this->nullableString($row['shipping_description'] ?? null),
            'coupon_code'                   => $this->nullableString($row['coupon_code'] ?? null),
            'is_gift'                       => 0,
            'total_item_count'              => $this->qty($row['total_item_count'] ?? $itemRows->count()),
            'total_qty_ordered'             => $this->qty($row['total_qty_ordered'] ?? $itemRows->sum(fn (array $item) => (float) ($item['qty_ordered'] ?? 0))),
            'base_currency_code'            => $this->nullableString($row['base_currency_code'] ?? null) ?? 'USD',
            'channel_currency_code'         => $this->nullableString($row['store_currency_code'] ?? null)
                ?? $this->nullableString($row['order_currency_code'] ?? null)
                ?? 'USD',
            'order_currency_code'           => $this->nullableString($row['order_currency_code'] ?? null) ?? 'USD',
            'grand_total'                   => $grandTotal,
            'base_grand_total'              => $baseGrandTotal,
            'grand_total_invoiced'          => $this->money($row['total_invoiced'] ?? 0),
            'base_grand_total_invoiced'     => $this->money($row['base_total_invoiced'] ?? 0),
            'grand_total_refunded'          => $this->money($row['total_refunded'] ?? 0),
            'base_grand_total_refunded'     => $this->money($row['base_total_refunded'] ?? 0),
            'sub_total'                     => $subTotal,
            'base_sub_total'                => $baseSubTotal,
            'sub_total_incl_tax'            => $subTotalInclTax,
            'base_sub_total_incl_tax'       => $baseSubTotalInclTax,
            'sub_total_invoiced'            => $this->money($row['subtotal_invoiced'] ?? 0),
            'base_sub_total_invoiced'       => $this->money($row['base_subtotal_invoiced'] ?? 0),
            'sub_total_refunded'            => $this->money($row['subtotal_refunded'] ?? 0),
            'base_sub_total_refunded'       => $this->money($row['base_subtotal_refunded'] ?? 0),
            'discount_amount'               => $discountAmount,
            'base_discount_amount'          => $baseDiscountAmount,
            'discount_invoiced'             => $this->money($row['discount_invoiced'] ?? 0),
            'base_discount_invoiced'        => $this->money($row['base_discount_invoiced'] ?? 0),
            'discount_refunded'             => $this->money($row['discount_refunded'] ?? 0),
            'base_discount_refunded'        => $this->money($row['base_discount_refunded'] ?? 0),
            'tax_amount'                    => $taxAmount,
            'base_tax_amount'               => $baseTaxAmount,
            'tax_amount_invoiced'           => $this->money($row['tax_invoiced'] ?? 0),
            'base_tax_amount_invoiced'      => $this->money($row['base_tax_invoiced'] ?? 0),
            'tax_amount_refunded'           => $this->money($row['tax_refunded'] ?? 0),
            'base_tax_amount_refunded'      => $this->money($row['base_tax_refunded'] ?? 0),
            'shipping_amount'               => $shippingAmount,
            'base_shipping_amount'          => $baseShippingAmount,
            'shipping_amount_incl_tax'      => $shippingInclTax,
            'base_shipping_amount_incl_tax' => $baseShippingInclTax,
            'shipping_invoiced'             => $this->money($row['shipping_invoiced'] ?? 0),
            'base_shipping_invoiced'        => $this->money($row['base_shipping_invoiced'] ?? 0),
            'shipping_refunded'             => $this->money($row['shipping_refunded'] ?? 0),
            'base_shipping_refunded'        => $this->money($row['base_shipping_refunded'] ?? 0),
            'shipping_tax_amount'           => $shippingTaxAmount,
            'base_shipping_tax_amount'      => $baseShippingTaxAmount,
            'created_at'                    => ! empty($row['created_at']) ? $row['created_at'] : $now,
            'updated_at'                    => $now,
        ];

        if ($this->hasDestinationColumn('remote_ip')) {
            $insert['remote_ip'] = $this->nullableString($row['remote_ip'] ?? null);
        }

        if ($this->hasDestinationColumn('shipping_discount_amount')) {
            $insert['shipping_discount_amount'] = $this->money($row['shipping_discount_amount'] ?? 0);
            $insert['base_shipping_discount_amount'] = $this->money(
                $row['base_shipping_discount_amount'] ?? $insert['shipping_discount_amount']
            );
        }

        return $this->appendRewardAndInsurance(
            array_merge($insert, $this->mapGiftcardAndPlus($row)),
            $row,
            $rewardRow
        );
    }

    /**
     * Magento stores giftcard_amount and member_amount as negative discounts; Bagisto stores positive amounts.
     * Magento member_free_amount is the Plus membership fee (14.99 / 17.90).
     *
     * @param  array<string, mixed>  $row
     * @return array<string, mixed>
     */
    private function mapGiftcardAndPlus(array $row): array
    {
        $payload = [];

        if ($this->hasDestinationColumn('giftcard_amount')) {
            $amount = $this->absMoney($row['giftcard_amount'] ?? 0);
            $base = array_key_exists('base_giftcard_amount', $row)
                ? $this->absMoney($row['base_giftcard_amount'])
                : $amount;

            $payload['giftcard_amount'] = $amount;
            $payload['base_giftcard_amount'] = $base > 0 ? $base : $amount;

            if ($this->hasDestinationColumn('giftcard_number')) {
                $giftcardId = (int) ($row['giftcard_id'] ?? 0);
                $payload['giftcard_number'] = $giftcardId > 0 ? (string) $giftcardId : null;
            }
        }

        if ($this->hasDestinationColumn('vip_plus_amount')) {
            $plus = $this->absMoney($row['member_free_amount'] ?? 0);
            $basePlus = array_key_exists('base_member_free_amount', $row)
                ? $this->absMoney($row['base_member_free_amount'])
                : $plus;

            $payload['vip_plus_amount'] = $plus;
            $payload['base_vip_plus_amount'] = $basePlus > 0 ? $basePlus : $plus;
        }

        if ($this->hasDestinationColumn('vip_discount_amount')) {
            $discount = $this->absMoney($row['member_amount'] ?? 0);
            $baseDiscount = array_key_exists('base_member_amount', $row)
                ? $this->absMoney($row['base_member_amount'])
                : $discount;

            $payload['vip_discount_amount'] = $discount;
            $payload['base_vip_discount_amount'] = $baseDiscount > 0 ? $baseDiscount : $discount;
        }

        return $payload;
    }

    /**
     * @param  array<string, mixed>  $insert
     * @param  array<string, mixed>  $row
     * @param  array<string, mixed>|null  $rewardRow
     * @return array<string, mixed>
     */
    private function appendRewardAndInsurance(array $insert, array $row, ?array $rewardRow): array
    {
        $rewardRow ??= [];

        if ($this->hasDestinationColumn('reward_points_used')) {
            $usedFromOrder = (int) ($row['mw_rewardpoint'] ?? 0);
            $usedFromHistory = (int) ($rewardRow['reward_point'] ?? 0);
            $amountFromOrder = $this->absMoney($row['mw_rewardpoint_discount'] ?? 0);
            $amountFromHistory = $this->absMoney($rewardRow['money'] ?? 0);

            $insert['reward_points_used'] = max($usedFromOrder, $usedFromHistory);
            $insert['reward_points_amount'] = $amountFromOrder > 0 ? $amountFromOrder : $amountFromHistory;
            $insert['base_reward_points_amount'] = $insert['reward_points_amount'];
            $insert['reward_points_earned'] = (int) ($rewardRow['earn_rewardpoint'] ?? 0);
        }

        if ($this->hasDestinationColumn('shipping_insurance_amount')) {
            $insurance = $this->absMoney($row['amcheckoutfees_amount'] ?? 0);
            $baseInsurance = array_key_exists('base_amcheckoutfees_amount', $row)
                ? $this->absMoney($row['base_amcheckoutfees_amount'])
                : $insurance;

            $insert['shipping_insurance_amount'] = $insurance;
            $insert['base_shipping_insurance_amount'] = $baseInsurance > 0 ? $baseInsurance : $insurance;
        }

        return $insert;
    }

    private function hasDestinationColumn(string $column): bool
    {
        $this->destinationOrderColumns ??= [];

        if (! array_key_exists($column, $this->destinationOrderColumns)) {
            $this->destinationOrderColumns[$column] = Schema::hasColumn('orders', $column);
        }

        return $this->destinationOrderColumns[$column];
    }

    private function absMoney(mixed $value): float
    {
        return abs($this->money($value));
    }

    /**
     * @param  array<string, mixed>|null  $paymentRow
     * @return array<string, mixed>
     */
    private function buildPaymentRow(int $orderId, ?array $paymentRow, string $now): array
    {
        $magentoMethod = trim((string) ($paymentRow['method'] ?? ''));

        $additional = [
            'magento_method' => $magentoMethod !== '' ? $magentoMethod : null,
        ];

        if ($paymentRow) {
            $additional['asteria_payment_id'] = (int) ($paymentRow['entity_id'] ?? 0);

            foreach (['last_trans_id', 'cc_type', 'cc_last4'] as $key) {
                $value = $this->nullableString($paymentRow[$key] ?? null);

                if ($value !== null) {
                    $additional[$key] = $value;
                }
            }

            $additional = $this->appendPaypalAdditional($additional, $paymentRow);
        }

        return [
            'order_id'     => $orderId,
            'method'       => $this->mapPaymentMethod($magentoMethod),
            'method_title' => $magentoMethod !== '' ? $magentoMethod : null,
            'additional'   => $this->encodeAdditional($additional),
            'created_at'   => $now,
            'updated_at'   => $now,
        ];
    }

    /**
     * @param  array<string, mixed>  $additional
     * @param  array<string, mixed>  $paymentRow
     * @return array<string, mixed>
     */
    private function appendPaypalAdditional(array $additional, array $paymentRow): array
    {
        if (! $this->isPaypalMethod($paymentRow['method'] ?? null)) {
            return $additional;
        }

        $info = $this->unserializeMagento($paymentRow['additional_information'] ?? null) ?? [];

        $fields = [
            'paypal_payer_id'                 => ['Payer ID', $info['paypal_payer_id'] ?? $info['paypal_express_checkout_payer_id'] ?? null],
            'paypal_payer_email'              => ['Payer Email', $info['paypal_payer_email'] ?? null],
            'paypal_payer_status'             => ['Payer Status', $info['paypal_payer_status'] ?? $paymentRow['account_status'] ?? null],
            'paypal_address_status'           => ['Payer Address Status', $info['paypal_address_status'] ?? $paymentRow['address_status'] ?? null],
            'paypal_protection_eligibility'   => ['Merchant Protection Eligibility', $info['paypal_protection_eligibility'] ?? $paymentRow['protection_eligibility'] ?? null],
            'paypal_correlation_id'           => ['Last Correlation ID', $info['paypal_correlation_id'] ?? null],
        ];

        $paypal = [];

        foreach ($fields as $key => [$label, $raw]) {
            $value = $this->nullableString($raw);

            if ($value === null) {
                continue;
            }

            $additional[$key] = $value;
            $paypal[$label] = $value;
        }

        $transactionId = $this->nullableString($paymentRow['last_trans_id'] ?? $info['paypal_transaction_id'] ?? null);

        if ($transactionId !== null) {
            $paypal['Last Transaction ID'] = $transactionId;
        }

        foreach (['paypal_payment_status', 'paypal_pending_reason', 'paypal_express_checkout_token'] as $key) {
            $value = $this->nullableString($info[$key] ?? null);

            if ($value !== null) {
                $additional[$key] = $value;
            }
        }

        if ($paypal !== []) {
            $additional['paypal'] = $paypal;
        }

        return $additional;
    }

    private function isPaypalMethod(mixed $method): bool
    {
        $method = strtolower(trim((string) $method));

        return $method !== '' && (str_starts_with($method, 'paypal') || str_starts_with($method, 'paypaluk_'));
    }

    /**
     * @param  array<string, mixed>  $row
     * @param  array<string, mixed>  $orderRow
     * @return array<string, mixed>
     */
    private function buildAddressRow(int $orderId, array $row, ?object $customer, string $email, array $orderRow, string $now): array
    {
        $type = strtolower(trim((string) ($row['address_type'] ?? '')));
        $addressType = $type === 'shipping'
            ? OrderAddress::ADDRESS_TYPE_SHIPPING
            : OrderAddress::ADDRESS_TYPE_BILLING;

        $firstName = $this->requiredName(
            $row['firstname'] ?? null,
            $this->requiredName($orderRow['customer_firstname'] ?? null, $customer?->first_name ?? $email)
        );
        $lastName = trim((string) ($row['lastname'] ?? ''))
            ?: (trim((string) ($orderRow['customer_lastname'] ?? '')) ?: ($customer?->last_name ?? '-'));

        return [
            'order_id'     => $orderId,
            'customer_id'  => $customer?->id,
            'address_type' => $addressType,
            'first_name'   => $firstName,
            'last_name'    => $lastName,
            'company_name' => $this->nullableString($row['company'] ?? null),
            'address'      => $this->mapStreet($row['street'] ?? null) ?: '-',
            'city'         => trim((string) ($row['city'] ?? '')) ?: '-',
            'state'        => $this->nullableString($row['region'] ?? null),
            'country'      => $this->nullableString($row['country_id'] ?? null),
            'postcode'     => $this->nullableString($row['postcode'] ?? null),
            'email'        => $this->nullableString($row['email'] ?? null) ?? ($email !== '' ? $email : null),
            'phone'        => $this->nullableString($row['telephone'] ?? null),
            'additional'   => json_encode(['asteria_address_id' => (int) ($row['entity_id'] ?? 0)]),
            'created_at'   => $now,
            'updated_at'   => $now,
        ];
    }

    /**
     * @param  array<string, mixed>  $row
     * @param  array{by_sku: array<string, Product>, by_asteria_id: array<int, Product>, catalog_sku: array<int, string>}  $productIndex
     * @return array<string, mixed>
     */
    private function buildItemRow(int $orderId, array $row, ?int $parentId, array $productIndex, string $now): array
    {
        $sku = trim((string) ($row['sku'] ?? ''));
        $product = $this->resolveProduct($row, $productIndex);
        $type = $this->mapProductType($row['product_type'] ?? null, $product);

        $price = $this->money($row['price'] ?? 0);
        $basePrice = $this->money($row['base_price'] ?? $price);
        $total = $this->money($row['row_total'] ?? 0);
        $baseTotal = $this->money($row['base_row_total'] ?? $total);
        $taxAmount = $this->money($row['tax_amount'] ?? 0);
        $baseTaxAmount = $this->money($row['base_tax_amount'] ?? $taxAmount);

        $additional = $this->buildItemAdditional($row);

        return [
            'order_id'             => $orderId,
            'parent_id'            => $parentId,
            'sku'                  => $sku !== '' ? $sku : null,
            'type'                 => $type,
            'name'                 => $this->nullableString($row['name'] ?? null) ?? '-',
            'weight'               => $this->money($row['weight'] ?? 0),
            'total_weight'         => $this->money($row['row_weight'] ?? $row['weight'] ?? 0),
            'qty_ordered'          => $this->qty($row['qty_ordered'] ?? 0),
            'qty_shipped'          => $this->qty($row['qty_shipped'] ?? 0),
            'qty_invoiced'         => $this->qty($row['qty_invoiced'] ?? 0),
            'qty_canceled'         => $this->qty($row['qty_canceled'] ?? 0),
            'qty_refunded'         => $this->qty($row['qty_refunded'] ?? 0),
            'price'                => $price,
            'base_price'           => $basePrice,
            'price_incl_tax'       => array_key_exists('price_incl_tax', $row) ? $this->money($row['price_incl_tax']) : $price,
            'base_price_incl_tax'  => array_key_exists('base_price_incl_tax', $row) ? $this->money($row['base_price_incl_tax']) : $basePrice,
            'total'                => $total,
            'base_total'           => $baseTotal,
            'total_incl_tax'       => array_key_exists('row_total_incl_tax', $row) ? $this->money($row['row_total_incl_tax']) : $total + $taxAmount,
            'base_total_incl_tax'  => array_key_exists('base_row_total_incl_tax', $row) ? $this->money($row['base_row_total_incl_tax']) : $baseTotal + $baseTaxAmount,
            'tax_percent'          => $this->money($row['tax_percent'] ?? 0),
            'tax_amount'           => $taxAmount,
            'base_tax_amount'      => $baseTaxAmount,
            'discount_percent'     => $this->money($row['discount_percent'] ?? 0),
            'discount_amount'      => $this->money($row['discount_amount'] ?? 0),
            'base_discount_amount' => $this->money($row['base_discount_amount'] ?? 0),
            'product_id'           => $product?->id,
            'product_type'         => $product ? get_class($product) : null,
            'additional'           => $this->encodeAdditional($additional),
            'created_at'           => $now,
            'updated_at'           => $now,
        ];
    }

    /**
     * @param  array<string, mixed>  $row
     * @return array<string, mixed>
     */
    private function buildItemAdditional(array $row): array
    {
        return $this->mergeItemAdditional([], $row);
    }

    /**
     * @param  array<string, mixed>  $additional
     * @param  array<string, mixed>  $row
     * @return array<string, mixed>
     */
    private function mergeItemAdditional(array $additional, array $row): array
    {
        $asteriaItemId = (int) ($row['item_id'] ?? 0);

        if ($asteriaItemId > 0) {
            $additional['asteria_item_id'] = $asteriaItemId;
        }

        $asteriaProductId = (int) ($row['product_id'] ?? 0);

        if ($asteriaProductId > 0) {
            $additional['asteria_product_id'] = $asteriaProductId;
        }

        $productOptions = $this->unserializeMagento($row['product_options'] ?? null);

        if ($productOptions !== null && empty($additional['product_options'])) {
            $additional['product_options'] = $productOptions;
        }

        $attributes = $this->magentoOptionsToAttributes($productOptions ?? []);

        if ($attributes !== [] && empty($additional['attributes'])) {
            $additional['attributes'] = $attributes;
        }

        return $additional;
    }

    /**
     * @param  array<string, mixed>  $productOptions
     * @return array<int, array{attribute_name: string, option_label: string, attribute_type: string}>
     */
    private function magentoOptionsToAttributes(array $productOptions): array
    {
        $attributes = [];
        $seen = [];

        foreach (['options', 'attributes_info', 'additional_options'] as $group) {
            $rows = $productOptions[$group] ?? [];

            if (! is_array($rows)) {
                continue;
            }

            foreach ($rows as $option) {
                if (! is_array($option)) {
                    continue;
                }

                $name = trim((string) ($option['label'] ?? ''));
                $label = $this->magentoOptionLabel($option);
                $type = trim((string) ($option['option_type'] ?? ($group === 'attributes_info' ? 'select' : 'text')));

                if ($name === '' && $label === '') {
                    continue;
                }

                $key = strtolower($name.'|'.$label);

                if (isset($seen[$key])) {
                    continue;
                }

                $seen[$key] = true;
                $attributes[] = [
                    'attribute_name' => $name !== '' ? $name : $label,
                    'option_label'   => $label !== '' ? $label : $name,
                    'attribute_type' => $type !== '' ? $type : 'text',
                ];
            }
        }

        return $attributes;
    }

    /**
     * @param  array<string, mixed>  $option
     */
    private function magentoOptionLabel(array $option): string
    {
        foreach (['print_value', 'value'] as $key) {
            if (! array_key_exists($key, $option)) {
                continue;
            }

            $value = $option[$key];

            if (is_array($value)) {
                $value = $value['title']
                    ?? $value['url']
                    ?? implode(', ', array_filter($value, 'is_scalar'));
            }

            $value = trim((string) $value);

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

        return '';
    }

    private function unserializeMagento(mixed $value): ?array
    {
        if (is_array($value)) {
            return $value;
        }

        $raw = trim((string) $value);

        if ($raw === '') {
            return null;
        }

        $first = $raw[0];

        if ($first === '{' || $first === '[') {
            $json = json_decode($raw, true);

            return is_array($json) ? $json : null;
        }

        $decoded = @unserialize($raw, ['allowed_classes' => false]);

        return is_array($decoded) ? $decoded : null;
    }

    /**
     * @return array<string, mixed>
     */
    private function decodeAdditional(mixed $value): array
    {
        if (is_array($value)) {
            return $value;
        }

        if (is_string($value) && $value !== '') {
            $decoded = json_decode($value, true);

            return is_array($decoded) ? $decoded : [];
        }

        return [];
    }

    /**
     * @param  array<string, mixed>  $additional
     */
    private function encodeAdditional(array $additional): string
    {
        $encoded = json_encode($additional, JSON_UNESCAPED_UNICODE);

        if ($encoded !== false) {
            return $encoded;
        }

        unset($additional['product_options']);
        $encoded = json_encode($additional, JSON_UNESCAPED_UNICODE);

        return $encoded !== false ? $encoded : '{}';
    }

    /**
     * @param  array<int, int>  $orderIds
     * @return array<int, int>
     */
    private function loadInsertedItemIds(array $orderIds): array
    {
        if ($orderIds === []) {
            return [];
        }

        $map = [];

        foreach (DB::table('order_items')->whereIn('order_id', $orderIds)->get(['id', 'additional']) as $item) {
            $additional = $item->additional;

            if (is_string($additional) && $additional !== '') {
                $additional = json_decode($additional, true);
            }

            if (is_array($additional) && isset($additional['asteria_item_id'])) {
                $map[(int) $additional['asteria_item_id']] = (int) $item->id;
            }
        }

        return $map;
    }

    /**
     * @param  array<int, array<string, mixed>>  $rows
     */
    private function insertRows(string $table, array $rows): void
    {
        foreach (array_chunk($rows, self::INSERT_CHUNK) as $chunk) {
            DB::table($table)->insert($chunk);
        }
    }

    /**
     * @param  Collection<int, array<string, mixed>>  $orders
     * @return array{0: array<int, object>, 1: array<string, object>}
     */
    private function loadCustomers($orders): array
    {
        $asteriaIds = $orders
            ->pluck('customer_id')
            ->filter(fn ($id) => (int) $id > 0)
            ->map(fn ($id) => (int) $id)
            ->unique()
            ->values()
            ->all();

        $emails = $orders
            ->pluck('customer_email')
            ->map(fn ($email) => strtolower(trim((string) $email)))
            ->filter()
            ->unique()
            ->values()
            ->all();

        $byAsteriaId = [];
        $byEmail = [];

        if ($asteriaIds !== []) {
            foreach (
                DB::table('customers')
                    ->select('id', 'email', 'first_name', 'last_name', 'migrated_from_asteria_id')
                    ->whereIn('migrated_from_asteria_id', $asteriaIds)
                    ->get() as $customer
            ) {
                $byAsteriaId[(int) $customer->migrated_from_asteria_id] = $customer;
            }
        }

        if ($emails !== []) {
            foreach (
                DB::table('customers')
                    ->select('id', 'email', 'first_name', 'last_name', 'migrated_from_asteria_id')
                    ->whereIn(DB::raw('LOWER(email)'), $emails)
                    ->get() as $customer
            ) {
                $byEmail[strtolower((string) $customer->email)] = $customer;
            }
        }

        return [$byAsteriaId, $byEmail];
    }

    /**
     * @param  Collection<int, array<string, mixed>>  $itemRows
     * @return array{by_sku: array<string, Product>, by_asteria_id: array<int, Product>, catalog_sku: array<int, string>}
     */
    private function loadProductIndex($itemRows, Magento1OrderReader $reader): array
    {
        $itemRows = collect($itemRows);

        $skus = $itemRows
            ->pluck('sku')
            ->map(fn ($sku) => trim((string) $sku))
            ->filter()
            ->unique()
            ->values()
            ->all();

        $asteriaIds = $itemRows
            ->pluck('product_id')
            ->map(fn ($id) => (int) $id)
            ->filter(fn ($id) => $id > 0)
            ->unique()
            ->values()
            ->all();

        $catalogSkuById = $reader->fetchCatalogSkus($asteriaIds);

        foreach ($catalogSkuById as $catalogSku) {
            $skus[] = $catalogSku;
        }

        $skus = array_values(array_unique(array_filter($skus)));

        $bySku = [];
        $byAsteriaId = [];
        $hasAsteriaColumn = Schema::hasColumn('products', 'migrated_from_asteria_id');
        $select = ['id', 'sku', 'type', 'attribute_family_id'];

        if ($hasAsteriaColumn) {
            $select[] = 'migrated_from_asteria_id';
        }

        $index = function (object $row) use (&$bySku, &$byAsteriaId): void {
            $product = new Product;
            $product->exists = true;
            $product->setRawAttributes((array) $row, true);

            $attributes = $product->getAttributes();
            $sku = trim((string) ($attributes['sku'] ?? ''));

            if ($sku !== '') {
                $bySku[$sku] = $product;
                $bySku[strtolower($sku)] = $bySku[strtolower($sku)] ?? $product;
            }

            $asteriaId = (int) ($attributes['migrated_from_asteria_id'] ?? 0);

            if ($asteriaId > 0) {
                $byAsteriaId[$asteriaId] = $product;
            }
        };

        foreach (array_chunk($skus, 500) as $chunk) {
            foreach (DB::table('products')->whereIn('sku', $chunk)->get($select) as $row) {
                $index($row);
            }
        }

        if ($hasAsteriaColumn && $asteriaIds !== []) {
            foreach (array_chunk($asteriaIds, 500) as $chunk) {
                foreach (DB::table('products')->whereIn('migrated_from_asteria_id', $chunk)->get($select) as $row) {
                    $index($row);
                }
            }
        }

        return [
            'by_sku'        => $bySku,
            'by_asteria_id' => $byAsteriaId,
            'catalog_sku'   => $catalogSkuById,
        ];
    }

    /**
     * @param  array<string, mixed>  $itemRow
     * @param  array{by_sku: array<string, Product>, by_asteria_id: array<int, Product>, catalog_sku: array<int, string>}  $productIndex
     */
    private function resolveProduct(array $itemRow, array $productIndex): ?Product
    {
        $asteriaProductId = (int) ($itemRow['product_id'] ?? 0);

        if ($asteriaProductId > 0 && isset($productIndex['by_asteria_id'][$asteriaProductId])) {
            return $productIndex['by_asteria_id'][$asteriaProductId];
        }

        $catalogSku = $asteriaProductId > 0
            ? trim((string) ($productIndex['catalog_sku'][$asteriaProductId] ?? ''))
            : '';

        if ($catalogSku !== '') {
            $hit = $productIndex['by_sku'][$catalogSku]
                ?? $productIndex['by_sku'][strtolower($catalogSku)]
                ?? null;

            if ($hit) {
                return $hit;
            }
        }

        $sku = trim((string) ($itemRow['sku'] ?? ''));

        if ($sku === '') {
            return null;
        }

        return $productIndex['by_sku'][$sku]
            ?? $productIndex['by_sku'][strtolower($sku)]
            ?? null;
    }

    private function backfillMissingProductIds(Magento1OrderReader $reader): int
    {
        $updated = 0;

        DB::table('order_items as oi')
            ->join('orders as o', 'o.id', '=', 'oi.order_id')
            ->whereNotNull('o.migrated_from_asteria_id')
            ->where(function ($query) {
                $query->whereNull('oi.product_id')->orWhere('oi.product_id', 0);
            })
            ->select('oi.id', 'oi.additional')
            ->orderBy('oi.id')
            ->chunkById(500, function ($rows) use ($reader, &$updated) {
                $asteriaItemIds = [];

                foreach ($rows as $row) {
                    $additional = $row->additional;

                    if (is_string($additional) && $additional !== '') {
                        $additional = json_decode($additional, true);
                    }

                    $asteriaItemId = is_array($additional) ? (int) ($additional['asteria_item_id'] ?? 0) : 0;

                    if ($asteriaItemId > 0) {
                        $asteriaItemIds[(int) $row->id] = $asteriaItemId;
                    }
                }

                if ($asteriaItemIds === []) {
                    return;
                }

                $magentoItems = $reader->fetchItemsByItemIds(array_values($asteriaItemIds))
                    ->keyBy(fn (array $item) => (int) $item['item_id']);
                $productIndex = $this->loadProductIndex($magentoItems->values(), $reader);

                foreach ($asteriaItemIds as $orderItemId => $asteriaItemId) {
                    $magentoItem = $magentoItems->get($asteriaItemId);

                    if (! is_array($magentoItem)) {
                        continue;
                    }

                    $product = $this->resolveProduct($magentoItem, $productIndex);

                    if (! $product) {
                        continue;
                    }

                    $payload = [
                        'product_id'   => $product->id,
                        'product_type' => get_class($product),
                        'type'         => $this->mapProductType($magentoItem['product_type'] ?? null, $product),
                    ];

                    $asteriaProductId = (int) ($magentoItem['product_id'] ?? 0);

                    if ($asteriaProductId > 0) {
                        $additional = $rows->firstWhere('id', $orderItemId)?->additional ?? null;

                        if (is_string($additional) && $additional !== '') {
                            $additional = json_decode($additional, true) ?: [];
                        }

                        if (! is_array($additional)) {
                            $additional = [];
                        }

                        $additional['asteria_product_id'] = $asteriaProductId;
                        $payload['additional'] = json_encode($additional);
                    }

                    DB::table('order_items')->where('id', $orderItemId)->update($payload);
                    $updated++;
                }
            }, 'oi.id', 'id');

        return $updated;
    }

    /**
     * @param  array<string, mixed>  $row
     * @param  array<int, Customer>  $customersByAsteriaId
     * @param  array<string, Customer>  $customersByEmail
     */
    private function resolveCustomer(array $row, array $customersByAsteriaId, array $customersByEmail): ?object
    {
        $asteriaCustomerId = (int) ($row['customer_id'] ?? 0);

        if ($asteriaCustomerId > 0 && isset($customersByAsteriaId[$asteriaCustomerId])) {
            return $customersByAsteriaId[$asteriaCustomerId];
        }

        $email = strtolower(trim((string) ($row['customer_email'] ?? '')));

        if ($email !== '' && isset($customersByEmail[$email])) {
            return $customersByEmail[$email];
        }

        return null;
    }

    /**
     * @param  array<string, mixed>  $row
     */
    private function mapStatus(array $row): string
    {
        $status = strtolower(trim((string) ($row['status'] ?? '')));
        $state = strtolower(trim((string) ($row['state'] ?? '')));
        $value = $status !== '' ? $status : $state;

        return match ($value) {
            'complete' => Order::STATUS_COMPLETED,
            'canceled', 'cancelled' => Order::STATUS_CANCELED,
            'pending_payment', 'payment_review', 'pending_paypal' => Order::STATUS_PENDING_PAYMENT,
            'holded' => Order::STATUS_PENDING,
            'fraud' => Order::STATUS_FRAUD,
            'closed' => Order::STATUS_CLOSED,
            'processing' => Order::STATUS_PROCESSING,
            default => Order::STATUS_PENDING,
        };
    }

    private function mapPaymentMethod(string $method): string
    {
        $method = strtolower(trim($method));

        if ($method === '') {
            return 'unknown';
        }

        if (in_array($method, ['paypal_express', 'paypal_standard'], true) || str_starts_with($method, 'paypaluk_')) {
            return 'paypal_standard';
        }

        if ($method === 'checkmo') {
            return 'moneytransfer';
        }

        if ($method === 'cashondelivery') {
            return 'cashondelivery';
        }

        if (str_starts_with($method, 'klarna')) {
            return 'klarna';
        }

        if (str_starts_with($method, 'afterpay') || str_starts_with($method, 'clearpay')) {
            return 'afterpay';
        }

        return $method;
    }

    private function mapProductType(mixed $magentoType, ?Product $product): string
    {
        $bagistoType = is_object($product) ? trim((string) ($product->getAttributes()['type'] ?? '')) : '';

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

        $type = strtolower(trim((string) $magentoType));

        if (in_array($type, self::BAGISTO_PRODUCT_TYPES, true)) {
            return $type;
        }

        return 'simple';
    }

    private function mapStreet(mixed $value): string
    {
        $value = trim((string) $value);

        if ($value === '') {
            return '';
        }

        $lines = preg_split("/\r\n|\n|\r/", $value) ?: [];

        return implode(', ', array_filter(array_map('trim', $lines)));
    }

    private function requiredName(mixed $value, string $fallback): string
    {
        $value = trim((string) $value);

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

        $fallback = trim($fallback);

        if ($fallback !== '') {
            $local = strstr($fallback, '@', true);

            return $local !== false && $local !== '' ? $local : $fallback;
        }

        return 'Customer';
    }

    private function nullableString(mixed $value): ?string
    {
        $value = trim((string) $value);

        return $value === '' ? null : $value;
    }

    private function money(mixed $value): float
    {
        return is_numeric($value) ? (float) $value : 0.0;
    }

    private function qty(mixed $value): int
    {
        return (int) round((float) $value);
    }
}