nshop/app/Console/Commands/SyncCatalogCommand.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;
use Longyi\Core\Models\ProductOption;
use Longyi\Core\Models\ProductOptionValue;
use Longyi\Core\Models\ProductVariant;
use PhpOffice\PhpSpreadsheet\IOFactory;
use Webkul\Attribute\Models\Attribute;
use Webkul\Attribute\Models\AttributeFamily;
use Webkul\Attribute\Models\AttributeOption;
use Webkul\Attribute\Repositories\AttributeRepository;
use Webkul\Core\Models\Channel;
use Webkul\Product\Helpers\Indexers\ElasticSearch;
use Webkul\Product\Helpers\Indexers\Flat;
use Webkul\Product\Helpers\Indexers\Price;
use Webkul\Product\Models\Product;

/**
 * Import attributes and flexible_variant products from the Magento-style CSV
 * exports located in storage/sync/.
 *
 *  - bagisto_attributes.csv : descriptive Bagisto attributes (catalog attributes)
 *  - bagisto_products.csv   : configurable rows turned into flexible_variant products
 *                             (Longyi\Core product_options / product_variants tables)
 */
class SyncCatalogCommand extends Command
{
    protected $signature = 'catalog:sync
        {--attributes-only : Only sync attributes}
        {--products-only : Only sync products}
        {--limit= : Limit the number of products processed}
        {--attributes-file= : Absolute/relative path for attributes file (.csv/.xlsx/.xls)}
        {--products-file= : Absolute/relative path for products file (.csv/.xlsx/.xls)}
        {--no-index : Skip rebuilding indices at the end}';

    protected $description = 'Sync attributes and flexible_variant products from storage/sync CSV files';

    protected string $attributesCsv;

    protected string $productsCsv;

    protected int $familyId;

    /** General attribute group of the variant_product family. */
    protected int $generalGroupId = 15;

    protected string $channelCode = 'default';

    protected int $channelId;

    protected string $locale = 'en';

    /** @var array<string, \Webkul\Attribute\Models\Attribute> code => attribute */
    protected array $familyAttributes = [];

    /** @var int[] Product ids touched during this run (for targeted reindex). */
    protected array $touchedProductIds = [];

    public function __construct(protected AttributeRepository $attributeRepository)
    {
        parent::__construct();
    }

    public function handle(): int
    {
        $this->attributesCsv = $this->resolveInputFile(
            'bagisto_attributes',
            $this->option('attributes-file')
        );
        $this->productsCsv = $this->resolveInputFile(
            'bagisto_products',
            $this->option('products-file')
        );

        foreach ([
            'attributes' => $this->attributesCsv,
            'products'   => $this->productsCsv,
        ] as $name => $file) {
            if (! $file || ! is_file($file)) {
                $this->error("Input file not found for {$name}: ".($file ?: '[empty path]'));

                return self::FAILURE;
            }
        }

        $family = AttributeFamily::where('code', 'wigs')->first();

        if (! $family) {
            $this->error('Attribute family "variant_product" not found.');

            return self::FAILURE;
        }

        $this->familyId = $family->id;

        $channel = Channel::where('code', $this->channelCode)->first() ?? Channel::first();
        $this->channelId = $channel->id;
        $this->channelCode = $channel->code;

        if (! $this->option('products-only')) {
            $this->syncAttributes();
        }

        $this->loadFamilyAttributes($family);

        if (! $this->option('attributes-only')) {
            $this->syncProducts();

            if (! $this->option('no-index')) {
                $this->reindexProducts();
            }
        }

        $this->info('Catalog sync finished.');

        return self::SUCCESS;
    }

    /* =========================================================================
     | Attributes
     * ====================================================================== */

    protected function syncAttributes(): void
    {
        $rows = $this->readCsv($this->attributesCsv);

        $this->info('Syncing attributes ('.count($rows).' rows)...');

        $created = 0;
        $skipped = 0;

        foreach ($rows as $row) {
            $code = trim((string) ($row['code'] ?? ''));

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

            $existing = Attribute::where('code', $code)->first();

            if ($existing) {
                $this->ensureAttributeInFamily($existing->id);
                $skipped++;

                continue;
            }

            $type = trim((string) $row['type']);

            $data = [
                'code'              => $code,
                'en' => ['name' => $row['name'] !== '' ? $row['name'] : $code],
                'admin_name'        => $row['name'] !== '' ? $row['name'] : $code,
                'type'              => $type,
                'is_required'       => (int) ($row['is_required'] ?? 0),
                'is_filterable'     => (int) ($row['is_filterable'] ?? 0),
                'is_comparable'     => (int) ($row['is_comparable'] ?? 0),
                'is_configurable'   => (int) ($row['is_configurable'] ?? 0),
                'position'          => (int) ($row['position'] ?? 0),
                'is_user_defined'   => 1,
                'value_per_locale'  => in_array($type, ['text', 'textarea']) ? 1 : 0,
                'value_per_channel' => 0,
            ];

            if (in_array($type, ['select', 'multiselect', 'checkbox']) && trim((string) ($row['options_labels'] ?? '')) !== '') {
                $labels = array_map('trim', explode('|', $row['options_labels']));

                $options = [];

                foreach (array_values(array_filter($labels, fn ($l) => $l !== '')) as $i => $label) {
                    $options[] = [
                        'admin_name' => $label,
                        'sort_order' => $i,
                        'en'         => ['label' => $label],
                    ];
                }

                $data['options'] = $options;
            }

            $attribute = $this->attributeRepository->create($data);

            $this->ensureAttributeInFamily($attribute->id);

            $created++;
        }

        $this->info("Attributes: {$created} created, {$skipped} already existed.");
    }

    protected function ensureAttributeInFamily(int $attributeId): void
    {
        $exists = DB::table('attribute_group_mappings')
            ->where('attribute_id', $attributeId)
            ->where('attribute_group_id', $this->generalGroupId)
            ->exists();

        if ($exists) {
            return;
        }

        $position = (int) DB::table('attribute_group_mappings')
            ->where('attribute_group_id', $this->generalGroupId)
            ->max('position');

        DB::table('attribute_group_mappings')->insert([
            'attribute_id'       => $attributeId,
            'attribute_group_id' => $this->generalGroupId,
            'position'           => $position + 1,
        ]);
    }

    protected function loadFamilyAttributes(AttributeFamily $family): void
    {
        $this->familyAttributes = [];

        foreach ($family->custom_attributes()->get() as $attribute) {
            $this->familyAttributes[$attribute->code] = $attribute;
        }
    }

    /* =========================================================================
     | Products
     * ====================================================================== */

    protected function syncProducts(): void
    {
        $rows = $this->readCsv($this->productsCsv);

        $limit = $this->option('limit') ? (int) $this->option('limit') : null;

        if ($limit) {
            $rows = array_slice($rows, 0, $limit);
        }

        $this->info('Syncing '.count($rows).' products...');

        $bar = $this->output->createProgressBar(count($rows));
        $bar->start();

        foreach ($rows as $row) {
            try {
                $this->importProduct($row);
            } catch (\Throwable $e) {
                $this->newLine();
                $this->error('Product '.($row['sku'] ?? '?').' failed: '.$e->getMessage());
            }

            $bar->advance();
        }

        $bar->finish();
        $this->newLine();
    }

    /**
     * Reindex only the products touched during this run.
     *
     * Done per-product to keep price-index inserts well under MySQL's
     * 65,535 placeholder limit (a single flexible_variant product can have
     * hundreds of variants × customer groups). The core Inventory indexer
     * only covers simple/virtual products, so it is intentionally skipped for
     * flexible_variant.
     */
    protected function reindexProducts(): void
    {
        $ids = array_values(array_unique($this->touchedProductIds));

        if (empty($ids)) {
            return;
        }

        $this->info('Reindexing '.count($ids).' products (price/flat'.($this->elasticEnabled() ? '/elastic' : '').')...');

        /** @var \Webkul\Product\Helpers\Indexers\Price $priceIndexer */
        $priceIndexer = app(Price::class);
        /** @var \Webkul\Product\Helpers\Indexers\Flat $flatIndexer */
        $flatIndexer = app(Flat::class);
        $elasticIndexer = $this->elasticEnabled() ? app(ElasticSearch::class) : null;

        $relations = [
            'attribute_family',
            'attribute_values',
            'channels',
            'price_indices',
            'customer_group_prices',
            'catalog_rule_prices',
            'flexibleVariants',
            'flexibleVariants.price_indices',
            'flexibleVariants.customer_group_prices',
        ];

        $bar = $this->output->createProgressBar(count($ids));
        $bar->start();

        foreach (array_chunk($ids, 20) as $chunk) {
            $products = Product::with($relations)->whereIn('id', $chunk)->get();

            foreach ($products as $product) {
                try {
                    $priceIndexer->reindexRow($product);
                    $flatIndexer->reindexRow($product);
                    $elasticIndexer?->reindexRow($product);
                } catch (\Throwable $e) {
                    $this->newLine();
                    $this->error('Reindex failed for product '.$product->sku.': '.$e->getMessage());
                }

                $bar->advance();
            }
        }

        $bar->finish();
        $this->newLine();
    }

    protected function elasticEnabled(): bool
    {
        return core()->getConfigData('catalog.products.search.engine') === 'elastic';
    }

    protected function importProduct(array $row): void
    {
        $sku = trim((string) $row['sku']);

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

        $product = Product::firstOrNew(['sku' => $sku]);
        $product->type = 'flexible_variant';
        $product->attribute_family_id = $this->familyId;
        $product->save();

        $this->touchedProductIds[] = $product->id;

        DB::table('product_channels')->updateOrInsert([
            'product_id' => $product->id,
            'channel_id' => $this->channelId,
        ]);

        $this->saveAttributeValues($product, $row);
        $this->saveInventory($product, $row);

        $this->saveImages($product, $row);

        DB::transaction(function () use ($product, $row) {
            $this->saveOptionsAndVariants($product, $row);
        });
    }

    protected function saveAttributeValues(Product $product, array $row): void
    {
        $row = $this->applyDefaultProductAttributeValues($row);

        $values = [];

        foreach ($this->familyAttributes as $code => $attribute) {
            if (! array_key_exists($code, $row)) {
                continue;
            }

            $rawValue = $row[$code];

            if ($rawValue === null || $rawValue === '') {
                continue;
            }

            $columnValue = $this->castAttributeValue($attribute, $rawValue);

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

            $valueRow = array_fill_keys(array_values($attribute->attributeTypeFields), null);

            $valueRow['json_value'] = null;
            $valueRow[$attribute->column_name] = $columnValue;
            $valueRow['attribute_id'] = $attribute->id;
            $valueRow['product_id'] = $product->id;
            $valueRow['channel'] = $attribute->value_per_channel ? $this->channelCode : null;
            $valueRow['locale'] = $attribute->value_per_locale ? $this->locale : null;
            $valueRow['unique_id'] = implode('|', array_filter([
                $valueRow['channel'],
                $valueRow['locale'],
                $valueRow['product_id'],
                $valueRow['attribute_id'],
            ]));

            $values[$valueRow['unique_id']] = $valueRow;
        }

        if (empty($values)) {
            return;
        }

        DB::table('product_attribute_values')->upsert(
            array_values($values),
            ['unique_id'],
            ['text_value', 'boolean_value', 'integer_value', 'float_value', 'datetime_value', 'date_value', 'json_value']
        );
    }

    protected function applyDefaultProductAttributeValues(array $row): array
    {
        foreach ([
            'visible_individually' => 1,
            'manage_stock' => 1,
        ] as $code => $defaultValue) {
            if (
                ! array_key_exists($code, $row)
                || $row[$code] === null
                || (is_string($row[$code]) && trim($row[$code]) === '')
            ) {
                $row[$code] = $defaultValue;
            }
        }

        return $row;
    }

    protected function saveInventory(Product $product, array $row): void
    {
        $qty = $row['qty'] ?? 9999;

        if ($qty === null || (is_string($qty) && trim($qty) === '')) {
            $qty = 9999;
        }

        DB::table('product_inventories')->updateOrInsert(
            [
                'product_id' => $product->id,
                'inventory_source_id' => 1,
                'vendor_id' => 0,
            ],
            ['qty' => (int) $qty]
        );
    }

    /**
     * @return mixed
     */
    protected function castAttributeValue(Attribute $attribute, $value)
    {
        switch ($attribute->type) {
            case 'boolean':
                return (int) (bool) (is_numeric($value) ? (int) $value : $value);

            case 'price':
                return (float) $value;

            case 'select':
                $option = AttributeOption::where('attribute_id', $attribute->id)
                    ->where('admin_name', $value)
                    ->first();

                return $option?->id;

            case 'multiselect':
                $ids = [];

                foreach (array_map('trim', explode(',', (string) $value)) as $label) {
                    if ($label === '') {
                        continue;
                    }

                    $option = AttributeOption::where('attribute_id', $attribute->id)
                        ->where('admin_name', $label)
                        ->first();

                    if ($option) {
                        $ids[] = $option->id;
                    }
                }

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

            default:
                return $value;
        }
    }

    protected function saveImages(Product $product, array $row): void
    {
        $urls = [];

        if (! empty($row['base_image'])) {
            $urls[] = trim($row['base_image']);
        }

        if (! empty($row['additional_images'])) {
            foreach (explode('|', $row['additional_images']) as $url) {
                $url = trim($url);

                if ($url !== '') {
                    $urls[] = $url;
                }
            }
        }

        $urls = array_values(array_unique($urls));

        if (empty($urls)) {
            return;
        }

        // Rebuild image set for idempotent re-runs.
        DB::table('product_images')->where('product_id', $product->id)->delete();

        $records = [];

        foreach ($urls as $position => $url) {
            $records[] = [
                'type'          => 'images',
                'path'          => $url,
                'product_id'    => $product->id,
                'position'      => $position + 1,
                'is_base_image' => $position === 0 ? 1 : 0,
            ];
        }

        DB::table('product_images')->insert($records);
    }

    /* =========================================================================
     | Flexible variant options & variants
     * ====================================================================== */

    protected function saveOptionsAndVariants(Product $product, array $row): void
    {
        $superCodes = array_values(array_filter(array_map('trim', explode(',', (string) ($row['super_attributes'] ?? '')))));

        if (empty($superCodes)) {
            return;
        }

        $optionsMeta = json_decode((string) ($row['options'] ?? '[]'), true) ?: [];
        $variants = json_decode((string) ($row['variants'] ?? '[]'), true) ?: [];

        // Clean previous flexible variant data for idempotent re-runs.
        $this->clearVariantData($product);

        /**
         * For each super-attribute (dimension) create a product option and its
         * values. labelToValueId[dimensionIndex][label] => product_option_value_id
         *
         * @var array<int, array<string, int>> $labelToValueId
         */
        $labelToValueId = [];

        $syncOptions = [];

        foreach ($superCodes as $index => $code) {
            $meta = $optionsMeta[$index] ?? [];

            $optionLabel = $meta['title'] ?? Str::title(str_replace('_', ' ', $code));

            /**
             * product_options.code is globally unique, so options are SHARED
             * across products. Reuse an existing option for this dimension or
             * create it once.
             */
            $option = ProductOption::firstOrCreate(
                ['code' => $code],
                ['label' => $optionLabel, 'type' => 'select', 'position' => $index]
            );

            $syncOptions[$option->id] = ['position' => $index, 'is_required' => true];

            // Existing values for this (shared) option, keyed by label.
            $existing = ProductOptionValue::where('product_option_id', $option->id)
                ->get()
                ->keyBy('label');

            foreach ($existing as $label => $value) {
                $labelToValueId[$index][$label] = $value->id;
            }

            // Collect distinct labels for this dimension: prefer the options JSON
            // value list (gives sku/order), then add any labels seen in variants.
            $orderedLabels = [];
            $valueSku = [];

            foreach (($meta['values'] ?? []) as $valueMeta) {
                $label = (string) ($valueMeta['title'] ?? '');

                if ($label === '' || array_key_exists($label, $valueSku)) {
                    continue;
                }

                $orderedLabels[] = $label;
                $valueSku[$label] = (string) ($valueMeta['sku'] ?? '');
            }

            foreach ($variants as $variant) {
                $label = $variant[(string) $index] ?? null;

                if ($label !== null && ! array_key_exists($label, $valueSku)) {
                    $orderedLabels[] = $label;
                    $valueSku[$label] = '';
                }
            }

            $nextPosition = (int) (ProductOptionValue::where('product_option_id', $option->id)->max('position'));

            foreach ($orderedLabels as $label) {
                if (isset($labelToValueId[$index][$label])) {
                    continue;
                }

                $valueCode = $valueSku[$label] !== '' ? $valueSku[$label] : Str::slug($label);

                if ($valueCode === '') {
                    $valueCode = 'value-'.($nextPosition + 1);
                }

                $value = ProductOptionValue::create([
                    'product_option_id' => $option->id,
                    'label'             => $label,
                    'code'              => $valueCode,
                    'position'          => ++$nextPosition,
                ]);

                $labelToValueId[$index][$label] = $value->id;
            }
        }

        $product->options()->sync($syncOptions);

        $this->saveVariants($product, $row, $superCodes, $variants, $labelToValueId);
    }

    protected function saveVariants(Product $product, array $row, array $superCodes, array $variants, array $labelToValueId): void
    {
        if (empty($variants)) {
            return;
        }

        $parentName = (string) ($row['name'] ?? '');

        $variantRecords = [];
        $now = now();

        foreach ($variants as $sortOrder => $variant) {
            $sku = trim((string) ($variant['sku'] ?? ''));

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

            $variantRecords[] = [
                'product_id' => $product->id,
                'sku'        => $sku,
                'name'       => $parentName !== '' ? $parentName : null,
                'price'      => (float) ($variant['price'] ?? 0),
                'quantity'   => (int) ($variant['qty'] ?? 0),
                'status'     => (int) ($variant['in_stock'] ?? 1) ? 1 : 1,
                'sort_order' => $sortOrder,
                'created_at' => $now,
                'updated_at' => $now,
            ];
        }

        if (empty($variantRecords)) {
            return;
        }

        foreach (array_chunk($variantRecords, 500) as $chunk) {
            ProductVariant::insert($chunk);
        }

        // Map sku => variant id for the pivot inserts.
        $variantIdBySku = ProductVariant::where('product_id', $product->id)
            ->pluck('id', 'sku')
            ->toArray();

        $pivotRecords = [];

        foreach ($variants as $variant) {
            $sku = trim((string) ($variant['sku'] ?? ''));
            $variantId = $variantIdBySku[$sku] ?? null;

            if (! $variantId) {
                continue;
            }

            foreach ($superCodes as $index => $code) {
                $label = $variant[(string) $index] ?? null;

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

                $valueId = $labelToValueId[$index][$label] ?? null;

                if (! $valueId) {
                    continue;
                }

                $pivotRecords[] = [
                    'product_variant_id'      => $variantId,
                    'product_option_value_id' => $valueId,
                    'created_at'              => $now,
                    'updated_at'              => $now,
                ];
            }
        }

        foreach (array_chunk($pivotRecords, 1000) as $chunk) {
            DB::table('product_variant_option_values')->insert($chunk);
        }
    }

    /**
     * Remove this product's variants and its option associations so the command
     * can be safely re-run. Shared product_options / product_option_values are
     * intentionally left intact (they are global and reused by other products).
     */
    protected function clearVariantData(Product $product): void
    {
        $variantIds = ProductVariant::withTrashed()
            ->where('product_id', $product->id)
            ->pluck('id')
            ->toArray();

        if (! empty($variantIds)) {
            DB::table('product_variant_option_values')
                ->whereIn('product_variant_id', $variantIds)
                ->delete();

            DB::table('product_variant_images')
                ->whereIn('product_variant_id', $variantIds)
                ->delete();

            ProductVariant::withTrashed()->whereIn('id', $variantIds)->forceDelete();
        }

        $product->options()->detach();
    }

    /* =========================================================================
     | File helper
     * ====================================================================== */

    /**
     * Resolve input file path from:
     * 1) explicit option path
     * 2) storage/sync/<base>.csv
     * 3) storage/sync/<base>.xlsx
     * 4) storage/sync/<base>.xls
     */
    protected function resolveInputFile(string $baseName, ?string $customPath = null): ?string
    {
        if (is_string($customPath) && trim($customPath) !== '') {
            $path = trim($customPath);

            if (! Str::startsWith($path, ['/'])) {
                $path = base_path($path);
            }

            return $path;
        }

        foreach (['csv', 'xlsx', 'xls'] as $ext) {
            $path = storage_path("sync/{$baseName}.{$ext}");

            if (is_file($path)) {
                return $path;
            }
        }

        return null;
    }

    /**
     * Read CSV/XLSX/XLS into associative rows keyed by header.
     *
     * @return array<int, array<string, string|null>>
     */
    protected function readCsv(string $path): array
    {
        $extension = strtolower((string) pathinfo($path, PATHINFO_EXTENSION));

        if (in_array($extension, ['xlsx', 'xls'], true)) {
            return $this->readExcel($path);
        }

        return $this->readCsvFile($path);
    }

    /**
     * @return array<int, array<string, string|null>>
     */
    protected function readCsvFile(string $path): array
    {
        $rows = [];

        $handle = fopen($path, 'r');

        if ($handle === false) {
            return $rows;
        }

        $header = fgetcsv($handle);

        if ($header === false) {
            fclose($handle);

            return $rows;
        }

        $header = array_map(fn ($h) => trim((string) $h), $header);

        while (($data = fgetcsv($handle)) !== false) {
            if ($data === [null] || $data === false) {
                continue;
            }

            $row = [];

            foreach ($header as $i => $key) {
                if ($key === '') {
                    continue;
                }

                $row[$key] = $data[$i] ?? null;
            }

            // Skip fully empty lines.
            if (count(array_filter($row, fn ($v) => $v !== null && $v !== '')) === 0) {
                continue;
            }

            $rows[] = $row;
        }

        fclose($handle);

        return $rows;
    }

    /**
     * @return array<int, array<string, string|null>>
     */
    protected function readExcel(string $path): array
    {
        $rows = [];

        $spreadsheet = IOFactory::load($path);
        $sheetRows = $spreadsheet
            ->getActiveSheet()
            ->toArray(null, false, false, false);

        if (empty($sheetRows)) {
            return $rows;
        }

        $header = array_map(
            fn ($h) => trim((string) $h),
            array_shift($sheetRows) ?: []
        );

        foreach ($sheetRows as $data) {
            if (! is_array($data)) {
                continue;
            }

            $row = [];

            foreach ($header as $i => $key) {
                if ($key === '') {
                    continue;
                }

                $value = $data[$i] ?? null;
                $row[$key] = $value === null ? null : (string) $value;
            }

            if (count(array_filter($row, fn ($v) => $v !== null && $v !== '')) === 0) {
                continue;
            }

            $rows[] = $row;
        }

        return $rows;
    }
}