nshop/app/Services/Asteria/Magento1ProductReader.php

<?php

namespace App\Services\Asteria;

use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

/**
 * Reads Magento 1 catalog products, EAV attributes, images, stock, categories,
 * and custom options from the Asteria connection (SELECT only).
 *
 * Output rows match the shape previously produced by Asteria's
 * shell/migrate_to_bagisto.php export, so Bagisto can persist them as
 * flexible_variant products.
 */
class Magento1ProductReader
{
    private Magento1Schema $schema;

    /** @var array<string, mixed> */
    private array $config;

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

    /** @var array<int, array<int, string>> */
    private array $optionLabelCache = [];

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

    public function __construct(private string $connection = 'asteria', ?array $config = null)
    {
        $this->schema = new Magento1Schema($connection);
        $this->config = array_merge(self::defaultConfig(), $config ?? (array) config('asteria.products', []));
    }

    /**
     * @return array<string, mixed>
     */
    public static function defaultConfig(): array
    {
        return [
            'media_base_url'         => 'https://img.asteriahair.com/media/catalog/product',
            'store_media_url'        => 'https://img.asteriahair.com/media/',
            'channel'                => '',
            'channels'               => [],
            'locale'                 => 'en',
            'locales'                => [],
            'attribute_family'       => 'wigs',
            'base_attributes'        => [
                'name', 'short_description', 'description',
                'price', 'special_price', 'cost', 'weight',
                'meta_title', 'meta_keyword', 'meta_description',
                'url_key', 'status',
            ],
            'include_eav_attributes' => [
                'feature', 'gmc_color', 'gmc_product_type',
                'hair_closure', 'hair_colorr', 'hair_density',
                'hair_wavy', 'human_hair_type',
                'lace_color', 'lace_sizes',
                'sale_num', 'texturee',
                'video', 'alone',
            ],
            'variant_option_titles'  => [],
            'skip_option_titles'     => [
                'Lace', 'Wig Type', 'Shipping Option', 'Construction', 'Free Gift',
                'Headband Color', 'Color', 'Customized Product', 'X-Mas Gift Random?',
                'Custom Item', 'Hair Type', 'Layering Option', '4C Hairline', 'Customize Item',
                'Free Headband', 'Hair Parting Styles', 'Limited Design',
                'Mannequin Head', 'Texture', 'Time', 'Trim the Ends (Optional)', 'U Part Open Size',
                'Wig Color', 'Wig Style',
            ],
            'max_variants_warn'      => 500,
            'enabled_status'         => 1,
        ];
    }

    /**
     * Attribute definitions for include_eav_attributes (Bagisto-shaped).
     *
     * @return Collection<int, array<string, mixed>>
     */
    public function fetchAttributeDefinitions(): Collection
    {
        $codes = array_values(array_filter(array_map(
            'strval',
            $this->config['include_eav_attributes'] ?? []
        )));

        if ($codes === [] || ! $this->schema->hasTable('eav_attribute')) {
            return collect();
        }

        $query = DB::connection($this->connection)
            ->table('eav_attribute as ea')
            ->select($this->attributeSelectColumns())
            ->whereIn('ea.attribute_code', $codes);

        $typeId = $this->entityTypeId('catalog_product');

        if ($typeId) {
            $query->where('ea.entity_type_id', $typeId);
        }

        if ($this->schema->hasTable('catalog_eav_attribute')) {
            $query->leftJoin('catalog_eav_attribute as cea', 'cea.attribute_id', '=', 'ea.attribute_id');
        }

        $rows = $query->orderBy('ea.attribute_code')->get();

        if ($rows->isEmpty()) {
            return collect();
        }

        $optionsByAttr = $this->loadAttributeOptions(
            $rows->pluck('attribute_id')->map(fn ($id) => (int) $id)->all()
        );

        return $rows->map(function ($row) use ($optionsByAttr) {
            $attributeId = (int) $row->attribute_id;

            return [
                'code'            => (string) $row->attribute_code,
                'name'            => (string) ($row->frontend_label ?: $row->attribute_code),
                'type'            => $this->magentoInputToBagistoType((string) ($row->frontend_input ?? 'text')),
                'magento_input'   => (string) ($row->frontend_input ?? ''),
                'is_required'     => (bool) (int) ($row->is_required ?? 0),
                'is_unique'       => false,
                'is_filterable'   => (bool) (int) ($row->is_filterable ?? 0),
                'is_searchable'   => (bool) (int) ($row->is_searchable ?? 0),
                'is_comparable'   => (bool) (int) ($row->is_comparable ?? 0),
                'is_configurable' => (bool) (int) ($row->is_configurable ?? 0),
                'position'        => (int) ($row->position ?? 0),
                'options'         => $optionsByAttr[$attributeId] ?? [],
            ];
        })->values();
    }

    /**
     * Enabled Magento products after $afterEntityId, hydrated for Bagisto persist.
     *
     * @return Collection<int, array<string, mixed>>
     */
    public function fetchProducts(int $afterEntityId, int $limit, ?string $sku = null): Collection
    {
        if (! $this->schema->hasTable('catalog_product_entity')) {
            return collect();
        }

        $staticColumns = $this->schema->existingColumns('catalog_product_entity', [
            'entity_id',
            'sku',
            'type_id',
            'attribute_set_id',
            'created_at',
            'updated_at',
        ]);

        if ($staticColumns === [] || ! in_array('entity_id', $staticColumns, true)) {
            return collect();
        }

        $query = DB::connection($this->connection)
            ->table('catalog_product_entity as pe')
            ->select(array_map(fn (string $column) => 'pe.'.$column, $staticColumns))
            ->where('pe.entity_id', '>', $afterEntityId)
            ->orderBy('pe.entity_id')
            ->limit($limit);

        if (is_string($sku) && $sku !== '') {
            $query->where('pe.sku', $sku);
        }

        $this->applyEnabledStatusFilter($query);

        $rows = $query->get();

        if ($rows->isEmpty()) {
            return collect();
        }

        $entityIds = $rows->pluck('entity_id')->map(fn ($id) => (int) $id)->all();
        $eav = $this->loadEavValues($entityIds);
        $images = $this->loadImages($entityIds);
        $categories = $this->loadCategories($entityIds);
        $stock = $this->loadStock($entityIds);
        $customOptions = $this->loadCustomOptions($entityIds);
        $variantTitles = array_flip($this->variantOptionTitles());

        return $rows->map(function ($row) use ($eav, $images, $categories, $stock, $customOptions, $variantTitles) {
            $id = (int) $row->entity_id;

            return $this->assembleProduct(
                (array) $row,
                $eav[$id] ?? [],
                $images[$id] ?? ['base' => '', 'additional' => []],
                $categories[$id] ?? [],
                $stock[$id] ?? ['qty' => 0, 'is_in_stock' => 0],
                $customOptions[$id] ?? [],
                $variantTitles
            );
        })->values();
    }

    /**
     * @param  \Illuminate\Database\Query\Builder  $query
     */
    private function applyEnabledStatusFilter($query): void
    {
        $status = $this->productAttributes()['status'] ?? null;

        if (! $status || ! $this->schema->hasTable('catalog_product_entity_int')) {
            return;
        }

        $query->join('catalog_product_entity_int as st', function ($join) use ($status) {
            $join->on('st.entity_id', '=', 'pe.entity_id')
                ->where('st.attribute_id', (int) $status->attribute_id)
                ->where('st.value', (int) ($this->config['enabled_status'] ?? 1));

            if (in_array('store_id', $this->schema->columnListing('catalog_product_entity_int'), true)) {
                $join->where('st.store_id', 0);
            }
        });
    }

    /**
     * @param  array<string, mixed>  $entity
     * @param  array<string, mixed>  $eav
     * @param  array{base: string, additional: array<int, string>}  $images
     * @param  array<int, string>  $categories
     * @param  array{qty: mixed, is_in_stock: mixed}  $stock
     * @param  array<string, array<string, mixed>>  $customOptions
     * @param  array<string, int>  $variantTitles
     * @return array<string, mixed>
     */
    private function assembleProduct(
        array $entity,
        array $eav,
        array $images,
        array $categories,
        array $stock,
        array $customOptions,
        array $variantTitles
    ): array {
        $sku = trim((string) ($entity['sku'] ?? ''));
        $name = (string) ($eav['name'] ?? $sku);
        $urlKey = trim((string) ($eav['url_key'] ?? ''));

        if ($urlKey === '') {
            $urlKey = $this->generateUrlKey($name, $sku);
        }

        $variantOptions = [];

        foreach ($customOptions as $title => $option) {
            if (! isset($variantTitles[$title])) {
                continue;
            }

            if (! in_array($option['type'], ['drop_down', 'radio', 'checkbox', 'multiple'], true)) {
                continue;
            }

            $variantOptions[$title] = $option;
        }

        $basePrice = (float) ($eav['price'] ?? 0);
        $superAttributes = [];

        foreach (array_keys($variantOptions) as $title) {
            $superAttributes[] = $this->sanitizeAttributeCode($title);
        }

        $options = [];

        foreach ($variantOptions as $title => $option) {
            $options[] = $this->optionPayload($title, $option);
        }

        $combinations = $variantOptions === [] ? [] : $this->cartesianProduct($variantOptions);
        $variants = $this->buildVariants($sku, $stock, $variantOptions, $basePrice, $combinations);

        $row = [
            'entity_id'             => (int) $entity['entity_id'],
            'sku'                   => $sku,
            'type_id'               => (string) ($entity['type_id'] ?? 'simple'),
            'type'                  => 'flexible_variant',
            'name'                  => $name,
            'url_key'               => $urlKey,
            'short_description'     => $this->htmlToText((string) ($eav['short_description'] ?? '')),
            'description'           => $this->replaceMediaDirectives((string) ($eav['description'] ?? '')),
            'price'                 => $eav['price'] ?? '',
            'special_price'         => $eav['special_price'] ?? '',
            'cost'                  => $eav['cost'] ?? '',
            'weight'                => $eav['weight'] ?? '',
            'status'                => 1,
            'featured'              => 0,
            'new'                   => 0,
            'guest_checkout'        => 1,
            'visible_individually'  => 1,
            'manage_stock'          => 1,
            'qty'                   => $stock['qty'] ?? 0,
            'in_stock'              => $stock['is_in_stock'] ?? 0,
            'base_image'            => $images['base'] ?? '',
            'additional_images'     => implode('|', $images['additional'] ?? []),
            'categories'            => $categories,
            'super_attributes'      => implode(',', $superAttributes),
            'options'               => $options,
            'variants'              => $variants,
            'meta_title'            => $eav['meta_title'] ?? '',
            'meta_keywords'         => $eav['meta_keyword'] ?? '',
            'meta_description'      => $eav['meta_description'] ?? '',
            'created_at'            => $entity['created_at'] ?? null,
        ];

        foreach ($this->config['include_eav_attributes'] ?? [] as $code) {
            if (array_key_exists($code, $row)) {
                continue;
            }

            $row[$code] = $eav[$code] ?? '';
        }

        return $row;
    }

    /**
     * @param  array<int, int>  $entityIds
     * @return array<int, array<string, mixed>>
     */
    private function loadEavValues(array $entityIds): array
    {
        $attributes = $this->productAttributes();

        if ($entityIds === [] || $attributes === []) {
            return [];
        }

        $byType = [];

        foreach ($attributes as $code => $attribute) {
            $type = $attribute->backend_type ?? 'varchar';

            if ($type === 'static' || $type === '') {
                continue;
            }

            $byType[$type][(int) $attribute->attribute_id] = $code;
        }

        $values = [];

        foreach ($byType as $type => $idToCode) {
            $table = 'catalog_product_entity_'.$type;

            if (! $this->schema->hasTable($table)) {
                continue;
            }

            $columns = ['entity_id', 'attribute_id', 'value'];

            if (in_array('store_id', $this->schema->columnListing($table), true)) {
                $columns[] = 'store_id';
            }

            $rows = DB::connection($this->connection)
                ->table($table)
                ->select($columns)
                ->whereIn('entity_id', $entityIds)
                ->whereIn('attribute_id', array_keys($idToCode))
                ->get();

            foreach ($rows as $row) {
                $code = $idToCode[(int) $row->attribute_id] ?? null;

                if ($code === null || $row->value === null || $row->value === '') {
                    continue;
                }

                $entityId = (int) $row->entity_id;
                $storeId = (int) ($row->store_id ?? 0);

                if (! isset($values[$entityId][$code]) || $storeId === 0) {
                    $values[$entityId][$code] = $row->value;
                }
            }
        }

        foreach ($attributes as $code => $info) {
            $input = (string) ($info->frontend_input ?? '');

            if (! in_array($input, ['select', 'multiselect'], true)) {
                continue;
            }

            foreach ($values as $entityId => $row) {
                if (! isset($row[$code])) {
                    continue;
                }

                $optionIds = array_filter(explode(',', (string) $row[$code]), 'is_numeric');
                $labels = $this->optionLabels((int) $info->attribute_id, $optionIds);
                $values[$entityId][$code] = implode(',', $labels);
            }
        }

        return $values;
    }

    /**
     * @param  array<int, int>  $entityIds
     * @return array<int, array{base: string, additional: array<int, string>}>
     */
    private function loadImages(array $entityIds): array
    {
        if ($entityIds === [] || ! $this->schema->hasTable('catalog_product_entity_media_gallery')) {
            return [];
        }

        $query = DB::connection($this->connection)
            ->table('catalog_product_entity_media_gallery as mg')
            ->select('mg.entity_id', 'mg.value');

        if ($this->schema->hasTable('catalog_product_entity_media_gallery_value')) {
            $query->leftJoin('catalog_product_entity_media_gallery_value as mgv', function ($join) {
                $join->on('mgv.value_id', '=', 'mg.value_id');

                if (in_array('store_id', $this->schema->columnListing('catalog_product_entity_media_gallery_value'), true)) {
                    $join->where('mgv.store_id', 0);
                }
            });

            $query->addSelect('mgv.position', 'mgv.disabled');
            $query->orderBy('mg.entity_id')->orderByRaw('COALESCE(mgv.position, 999) asc');
        } else {
            $query->orderBy('mg.entity_id');
        }

        $rows = $query->whereIn('mg.entity_id', $entityIds)->get();
        $baseUrl = rtrim((string) ($this->config['media_base_url'] ?? ''), '/');
        $grouped = [];

        foreach ($rows as $row) {
            if ((int) ($row->disabled ?? 0) === 1) {
                continue;
            }

            $path = (string) ($row->value ?? '');

            if ($path === '' || $path === 'no_selection') {
                continue;
            }

            $grouped[(int) $row->entity_id][] = $this->absoluteMediaUrl($baseUrl, $path);
        }

        $result = [];

        foreach ($grouped as $entityId => $urls) {
            $urls = array_values(array_unique($urls));
            $result[$entityId] = [
                'base'        => $urls[0] ?? '',
                'additional'  => array_slice($urls, 1),
            ];
        }

        return $result;
    }

    /**
     * @param  array<int, int>  $entityIds
     * @return array<int, array<int, string>>
     */
    private function loadCategories(array $entityIds): array
    {
        if (
            $entityIds === []
            || ! $this->schema->hasTable('catalog_category_product')
            || ! $this->schema->hasTable('catalog_category_entity')
            || ! $this->schema->hasTable('catalog_category_entity_varchar')
        ) {
            return [];
        }

        $nameAttr = $this->attributeByCode('catalog_category', 'name');

        if (! $nameAttr) {
            return [];
        }

        $query = DB::connection($this->connection)
            ->table('catalog_category_product as ccp')
            ->join('catalog_category_entity as cc', 'cc.entity_id', '=', 'ccp.category_id')
            ->join('catalog_category_entity_varchar as ccv', function ($join) use ($nameAttr) {
                $join->on('ccv.entity_id', '=', 'cc.entity_id')
                    ->where('ccv.attribute_id', (int) $nameAttr->attribute_id);

                if (in_array('store_id', $this->schema->columnListing('catalog_category_entity_varchar'), true)) {
                    $join->where('ccv.store_id', 0);
                }
            })
            ->whereIn('ccp.product_id', $entityIds)
            ->select('ccp.product_id', 'ccv.value as category_name', 'cc.path');

        if (in_array('path', $this->schema->columnListing('catalog_category_entity'), true)) {
            $query->orderByRaw('LENGTH(cc.path) desc');
        }

        $result = [];

        foreach ($query->get() as $row) {
            $name = trim((string) ($row->category_name ?? ''));

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

            $productId = (int) $row->product_id;

            if (! isset($result[$productId]) || ! in_array($name, $result[$productId], true)) {
                $result[$productId][] = $name;
            }
        }

        return $result;
    }

    /**
     * @param  array<int, int>  $entityIds
     * @return array<int, array{qty: mixed, is_in_stock: mixed}>
     */
    private function loadStock(array $entityIds): array
    {
        if ($entityIds === [] || ! $this->schema->hasTable('cataloginventory_stock_item')) {
            return [];
        }

        $columns = $this->schema->existingColumns('cataloginventory_stock_item', [
            'product_id',
            'qty',
            'is_in_stock',
            'stock_id',
        ]);

        if ($columns === [] || ! in_array('product_id', $columns, true)) {
            return [];
        }

        $query = DB::connection($this->connection)
            ->table('cataloginventory_stock_item')
            ->select($columns)
            ->whereIn('product_id', $entityIds);

        if (in_array('stock_id', $columns, true)) {
            $query->where('stock_id', 1);
        }

        $result = [];

        foreach ($query->get() as $row) {
            $result[(int) $row->product_id] = [
                'qty'         => $row->qty ?? 0,
                'is_in_stock' => $row->is_in_stock ?? 0,
            ];
        }

        return $result;
    }

    /**
     * @param  array<int, int>  $entityIds
     * @return array<int, array<string, array<string, mixed>>>
     */
    private function loadCustomOptions(array $entityIds): array
    {
        if (
            $entityIds === []
            || ! $this->schema->hasTable('catalog_product_option')
            || ! $this->schema->hasTable('catalog_product_option_title')
        ) {
            return [];
        }

        $query = DB::connection($this->connection)
            ->table('catalog_product_option as o')
            ->leftJoin('catalog_product_option_title as ot', function ($join) {
                $join->on('ot.option_id', '=', 'o.option_id');

                if (in_array('store_id', $this->schema->columnListing('catalog_product_option_title'), true)) {
                    $join->where('ot.store_id', 0);
                }
            })
            ->whereIn('o.product_id', $entityIds)
            ->select(
                'o.product_id',
                'o.option_id',
                'o.type',
                'o.sort_order as option_sort_order',
                'o.is_require',
                'ot.title as option_title'
            )
            ->orderBy('o.product_id')
            ->orderBy('o.sort_order');

        if ($this->schema->hasTable('catalog_product_option_type_value')) {
            $query->leftJoin('catalog_product_option_type_value as otv', 'otv.option_id', '=', 'o.option_id')
                ->addSelect('otv.option_type_id', 'otv.sku as value_sku', 'otv.sort_order as value_sort_order')
                ->orderBy('otv.sort_order');

            if ($this->schema->hasTable('catalog_product_option_type_price')) {
                $query->leftJoin('catalog_product_option_type_price as otp', function ($join) {
                    $join->on('otp.option_type_id', '=', 'otv.option_type_id');

                    if (in_array('store_id', $this->schema->columnListing('catalog_product_option_type_price'), true)) {
                        $join->where('otp.store_id', 0);
                    }
                })->addSelect('otp.price', 'otp.price_type');
            }

            if ($this->schema->hasTable('catalog_product_option_type_title')) {
                $query->leftJoin('catalog_product_option_type_title as ott', function ($join) {
                    $join->on('ott.option_type_id', '=', 'otv.option_type_id');

                    if (in_array('store_id', $this->schema->columnListing('catalog_product_option_type_title'), true)) {
                        $join->where('ott.store_id', 0);
                    }
                })->addSelect('ott.title as value_title');
            }
        }

        $options = [];

        foreach ($query->get() as $row) {
            $title = (string) ($row->option_title ?? '');

            if ($title === '') {
                continue;
            }

            $productId = (int) $row->product_id;

            if (! isset($options[$productId][$title])) {
                $options[$productId][$title] = [
                    'option_id'  => (int) $row->option_id,
                    'type'       => (string) ($row->type ?? ''),
                    'is_require' => (int) ($row->is_require ?? 0),
                    'sort_order' => (int) ($row->option_sort_order ?? 0),
                    'values'     => [],
                ];
            }

            if (! empty($row->option_type_id)) {
                $sort = (int) ($row->value_sort_order ?? 0);
                $options[$productId][$title]['values'][$sort] = [
                    'id'         => (int) $row->option_type_id,
                    'title'      => (string) ($row->value_title ?? ''),
                    'sku'        => (string) ($row->value_sku ?? ''),
                    'price'      => (float) ($row->price ?? 0),
                    'price_type' => (string) ($row->price_type ?? 'fixed'),
                    'sort_order' => $sort,
                ];
            }
        }

        foreach ($options as &$productOptions) {
            foreach ($productOptions as &$option) {
                ksort($option['values']);
            }
        }
        unset($productOptions, $option);

        return $options;
    }

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

        if (! empty($this->config['variant_option_titles'])) {
            return $this->variantOptionTitles = array_values(array_map(
                'strval',
                $this->config['variant_option_titles']
            ));
        }

        $skip = array_flip(array_map('strval', $this->config['skip_option_titles'] ?? []));

        if (
            ! $this->schema->hasTable('catalog_product_option')
            || ! $this->schema->hasTable('catalog_product_option_title')
        ) {
            return $this->variantOptionTitles = [];
        }

        $query = DB::connection($this->connection)
            ->table('catalog_product_option as o')
            ->join('catalog_product_option_title as ot', function ($join) {
                $join->on('ot.option_id', '=', 'o.option_id');

                if (in_array('store_id', $this->schema->columnListing('catalog_product_option_title'), true)) {
                    $join->where('ot.store_id', 0);
                }
            })
            ->whereIn('o.type', ['drop_down', 'radio'])
            ->distinct()
            ->orderBy('ot.title')
            ->select('ot.title');

        $titles = [];

        foreach ($query->pluck('title') as $title) {
            $title = (string) $title;

            if ($title === '' || isset($skip[$title])) {
                continue;
            }

            $titles[] = $title;
        }

        return $this->variantOptionTitles = $titles;
    }

    /**
     * @param  array<string, array<string, mixed>>  $options
     * @return array<int, array<string, array<string, mixed>>>
     */
    private function cartesianProduct(array $options): array
    {
        $sets = [];
        $titles = [];

        foreach ($options as $title => $option) {
            if (empty($option['values'])) {
                continue;
            }

            $titles[] = $title;
            $sets[] = array_values($option['values']);
        }

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

        $result = [[]];

        foreach ($sets as $idx => $valueList) {
            $title = $titles[$idx];
            $next = [];

            foreach ($result as $existing) {
                foreach ($valueList as $value) {
                    $next[] = array_merge($existing, [$title => $value]);
                }
            }

            $result = $next;
        }

        return $result;
    }

    /**
     * @param  array{qty: mixed, is_in_stock: mixed}  $stock
     * @param  array<string, array<string, mixed>>  $variantOptions
     * @param  array<int, array<string, array<string, mixed>>>  $combinations
     * @return array<int, array<string, mixed>>
     */
    private function buildVariants(
        string $parentSku,
        array $stock,
        array $variantOptions,
        float $basePrice,
        array $combinations
    ): array {
        if ($combinations === []) {
            return [];
        }

        $variants = [];

        foreach ($combinations as $combo) {
            $skuParts = [];
            $price = $basePrice;
            $variant = [
                'sku'      => '',
                'price'    => '',
                'qty'      => (float) ($stock['qty'] ?? 0),
                'in_stock' => (int) ($stock['is_in_stock'] ?? 0),
            ];

            foreach ($combo as $optTitle => $valueInfo) {
                $skuParts[] = (string) ($valueInfo['sort_order'] ?? '');
                $price += (float) ($valueInfo['price'] ?? 0);
                $label = (string) ($valueInfo['title'] ?? '');
                $variant[$optTitle] = $label;
                $variant[$this->sanitizeAttributeCode($optTitle)] = $label;
            }

            $variant['sku'] = $parentSku.'-'.implode('-', $skuParts);
            $variant['price'] = number_format($price, 2, '.', '');
            $variants[] = $variant;
        }

        return $variants;
    }

    /**
     * @param  array<string, mixed>  $option
     * @return array<string, mixed>
     */
    private function optionPayload(string $title, array $option): array
    {
        $values = [];

        foreach ($option['values'] as $value) {
            $values[] = [
                'title'      => $value['title'],
                'price'      => $value['price'],
                'price_type' => $value['price_type'],
                'sku'        => $value['sku'],
                'sort_order' => (int) $value['sort_order'],
            ];
        }

        return [
            'title'      => $title,
            'type'       => $option['type'],
            'is_require' => (bool) $option['is_require'],
            'sort_order' => (int) $option['sort_order'],
            'values'     => $values,
        ];
    }

    /**
     * @return array<string, object>
     */
    private function productAttributes(): array
    {
        if ($this->productAttributeMap !== null) {
            return $this->productAttributeMap;
        }

        $codes = array_values(array_unique(array_merge(
            $this->config['base_attributes'] ?? [],
            $this->config['include_eav_attributes'] ?? [],
            ['status', 'url_key']
        )));

        return $this->productAttributeMap = $this->attributeMap('catalog_product', $codes);
    }

    /**
     * @param  array<int, string>  $codes
     * @return array<string, object>
     */
    private function attributeMap(string $entityTypeCode, array $codes): array
    {
        if ($codes === [] || ! $this->schema->hasTable('eav_attribute')) {
            return [];
        }

        $select = $this->schema->existingColumns('eav_attribute', [
            'attribute_id',
            'attribute_code',
            'backend_type',
            'frontend_input',
            'frontend_label',
            'is_required',
        ]);

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

        $query = DB::connection($this->connection)
            ->table('eav_attribute')
            ->select($select)
            ->whereIn('attribute_code', $codes);

        $typeId = $this->entityTypeId($entityTypeCode);

        if ($typeId) {
            $query->where('entity_type_id', $typeId);
        }

        $map = [];

        foreach ($query->get() as $attribute) {
            $input = (string) ($attribute->frontend_input ?? '');

            if (in_array($input, ['media_image', 'gallery'], true)) {
                continue;
            }

            $map[$attribute->attribute_code] = $attribute;
        }

        return $map;
    }

    private function attributeByCode(string $entityTypeCode, string $code): ?object
    {
        return $this->attributeMap($entityTypeCode, [$code])[$code] ?? null;
    }

    private function entityTypeId(string $code): ?int
    {
        if (! $this->schema->hasTable('eav_entity_type')) {
            return null;
        }

        $id = DB::connection($this->connection)
            ->table('eav_entity_type')
            ->where('entity_type_code', $code)
            ->value('entity_type_id');

        return $id ? (int) $id : null;
    }

    /**
     * @return array<int, string|\Illuminate\Database\Query\Expression>
     */
    private function attributeSelectColumns(): array
    {
        $columns = [
            'ea.attribute_id',
            'ea.attribute_code',
            'ea.backend_type',
        ];

        $listing = array_fill_keys(array_map('strtolower', $this->schema->columnListing('eav_attribute')), true);

        foreach (['frontend_label', 'frontend_input', 'is_required', 'is_user_defined'] as $column) {
            if (isset($listing[$column])) {
                $columns[] = 'ea.'.$column;
            }
        }

        if ($this->schema->hasTable('catalog_eav_attribute')) {
            $cea = array_fill_keys(array_map('strtolower', $this->schema->columnListing('catalog_eav_attribute')), true);

            foreach (['is_searchable', 'is_filterable', 'is_comparable', 'is_configurable', 'position'] as $column) {
                if (isset($cea[$column])) {
                    $columns[] = DB::raw('IFNULL(cea.'.$column.', 0) as '.$column);
                }
            }
        }

        return $columns;
    }

    /**
     * @param  array<int, int>  $attributeIds
     * @return array<int, array<int, array<string, mixed>>>
     */
    private function loadAttributeOptions(array $attributeIds): array
    {
        if (
            $attributeIds === []
            || ! $this->schema->hasTable('eav_attribute_option')
            || ! $this->schema->hasTable('eav_attribute_option_value')
        ) {
            return [];
        }

        $rows = DB::connection($this->connection)
            ->table('eav_attribute_option as eao')
            ->leftJoin('eav_attribute_option_value as eaov', function ($join) {
                $join->on('eaov.option_id', '=', 'eao.option_id');

                if (in_array('store_id', $this->schema->columnListing('eav_attribute_option_value'), true)) {
                    $join->where('eaov.store_id', 0);
                }
            })
            ->whereIn('eao.attribute_id', $attributeIds)
            ->orderBy('eao.attribute_id')
            ->orderBy('eao.sort_order')
            ->get(['eao.attribute_id', 'eao.option_id', 'eao.sort_order', 'eaov.value as label']);

        $grouped = [];

        foreach ($rows as $row) {
            $grouped[(int) $row->attribute_id][] = [
                'id'         => (int) $row->option_id,
                'label'      => (string) ($row->label ?? ''),
                'sort_order' => (int) ($row->sort_order ?? 0),
            ];
        }

        return $grouped;
    }

    /**
     * @param  array<int, string>  $optionIds
     * @return array<int, string>
     */
    private function optionLabels(int $attributeId, array $optionIds): array
    {
        $this->warmOptionLabels($attributeId);

        $labels = [];

        foreach ($optionIds as $optionId) {
            $id = (int) $optionId;

            if (isset($this->optionLabelCache[$attributeId][$id])) {
                $labels[] = $this->optionLabelCache[$attributeId][$id];
            }
        }

        return $labels;
    }

    private function warmOptionLabels(int $attributeId): void
    {
        if (isset($this->optionLabelCache[$attributeId])) {
            return;
        }

        $this->optionLabelCache[$attributeId] = [];

        foreach ($this->loadAttributeOptions([$attributeId])[$attributeId] ?? [] as $option) {
            $this->optionLabelCache[$attributeId][(int) $option['id']] = (string) $option['label'];
        }
    }

    private function magentoInputToBagistoType(string $frontendInput): string
    {
        return match ($frontendInput) {
            'textarea'    => 'textarea',
            'select'      => 'select',
            'multiselect' => 'multiselect',
            'boolean'     => 'boolean',
            'price'       => 'price',
            'date'        => 'date',
            'media_image', 'gallery' => 'image',
            default       => 'text',
        };
    }

    public function sanitizeAttributeCode(string $title): string
    {
        $code = strtolower($title);
        $code = (string) preg_replace('/[^a-z0-9]+/', '_', $code);

        return trim($code, '_');
    }

    private function generateUrlKey(string $name, string $sku): string
    {
        $key = strtolower($name);
        $key = (string) preg_replace('/[^a-z0-9\s-]/', '', $key);
        $key = (string) preg_replace('/[\s]+/', '-', trim($key));
        $key = (string) preg_replace('/-+/', '-', $key);
        $key = trim($key, '-');

        if ($key === '') {
            $key = strtolower((string) preg_replace('/[^a-zA-Z0-9]/', '-', $sku));
        }

        return $key;
    }

    private function htmlToText(string $html): string
    {
        if ($html === '') {
            return '';
        }

        $text = html_entity_decode(strip_tags($html), ENT_QUOTES | ENT_HTML5, 'UTF-8');

        return trim((string) preg_replace('/\s+/', ' ', $text));
    }

    private function replaceMediaDirectives(string $html): string
    {
        $mediaBaseUrl = (string) ($this->config['store_media_url'] ?? '');

        if ($html === '' || $mediaBaseUrl === '') {
            return $html;
        }

        $mediaBaseUrl = rtrim($mediaBaseUrl, '/').'/';

        return (string) preg_replace_callback(
            '/\{\{\s*media\s+url\s*=\s*["\']([^"\']+)["\']\s*\}\}/i',
            fn (array $match) => $mediaBaseUrl.ltrim($match[1], '/'),
            $html
        );
    }

    private function absoluteMediaUrl(string $baseUrl, string $path): string
    {
        if (str_starts_with($path, 'http://') || str_starts_with($path, 'https://')) {
            return $path;
        }

        if ($baseUrl === '') {
            return $path;
        }

        return $baseUrl.'/'.ltrim($path, '/');
    }
}