<?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).
 *
 * Does not create invoices/shipments/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;

    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.');
        }

        $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;
        $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) {
                $created += $orders->count();
                $itemsImported += $items->flatten(1)->count();
                $addressesImported += $addresses->flatten(1)->count();
                $this->line(sprintf(
                    '  Batch #%d: %d orders, %d items, %d addresses (last entity_id=%d) [skipped – dry-run] (%.1fs)',
                    $batchNumber,
                    $orders->count(),
                    $items->flatten(1)->count(),
                    $addresses->flatten(1)->count(),
                    $lastId,
                    microtime(true) - $started
                ));

                continue;
            }

            [$customersByAsteriaId, $customersByEmail] = $this->loadCustomers($orders);
            $productsBySku = $this->loadProducts($items);

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

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

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

            $this->line(sprintf(
                '  Batch #%d: created=%d skipped=%d items=%d addresses=%d (last entity_id=%d) (%.1fs)',
                $batchNumber,
                $result['created'],
                $result['skipped'],
                $result['items'],
                $result['addresses'],
                $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}.");

        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<string, Product>  $productsBySku
     * @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 $productsBySku
    ): 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,
            $productsBySku,
            $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, $productsBySku, $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, $productsBySku, $now);
                }

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

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

    /**
     * @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,
        ];

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

    /**
     * @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 ??= [
            'reward_points_used'         => Schema::hasColumn('orders', 'reward_points_used'),
            'shipping_insurance_amount'  => Schema::hasColumn('orders', 'shipping_insurance_amount'),
        ];

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

    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;
                }
            }
        }

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

    /**
     * @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<string, Product>  $productsBySku
     * @return array<string, mixed>
     */
    private function buildItemRow(int $orderId, array $row, ?int $parentId, array $productsBySku, string $now): array
    {
        $sku = trim((string) ($row['sku'] ?? ''));
        $product = $sku !== '' ? ($productsBySku[$sku] ?? null) : null;
        $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);

        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'           => json_encode(['asteria_item_id' => (int) ($row['item_id'] ?? 0)]),
            'created_at'           => $now,
            'updated_at'           => $now,
        ];
    }

    /**
     * @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, Collection<int, array<string, mixed>>>  $items
     * @return array<string, Product>
     */
    private function loadProducts($items): array
    {
        $skus = $items
            ->flatten(1)
            ->pluck('sku')
            ->map(fn ($sku) => trim((string) $sku))
            ->filter()
            ->unique()
            ->values()
            ->all();

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

        return Product::query()
            ->whereIn('sku', $skus)
            ->get(['id', 'sku', 'type'])
            ->keyBy('sku')
            ->all();
    }

    /**
     * @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
    {
        if ($product && in_array($product->type, self::BAGISTO_PRODUCT_TYPES, true)) {
            return $product->type;
        }

        $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);
    }
}