nshop/app/Console/Commands/RepairMigratedVariantSkus.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Webkul\Product\Models\Product;

/**
 * Rewrites flexible-variant SKUs that were migrated with option sort_order suffixes.
 *
 * The new suffix is the option value SKU (`product_option_values.code`), in option position order.
 * Variant rows, images, and ids stay in place. Order, invoice, shipment, and
 * refund item SKUs that belong to those variants are rewritten to the same SKU.
 */
class RepairMigratedVariantSkus extends Command
{
    protected $signature = 'products:repair-variant-skus
        {--dry-run : Print the SKU changes without writing}
        {--sku= : Repair one parent product SKU}';

    protected $description = 'Rewrite migrated variant and order item SKUs from option value SKUs';

    private int $updated = 0;

    private int $unchanged = 0;

    private int $skipped = 0;

    private int $collisions = 0;

    private int $orderItems = 0;

    /** @var array<int, array{0: int, 1: string, 2: string}> */
    private array $examples = [];

    public function handle(): int
    {
        $dryRun = (bool) $this->option('dry-run');
        $sku = trim((string) $this->option('sku'));

        $query = Product::query()
            ->whereNotNull('migrated_from_asteria_id')
            ->where('type', 'flexible_variant')
            ->select('id', 'sku')
            ->orderBy('id');

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

        $query->chunkById(20, function (Collection $products) use ($dryRun) {
            $this->repairChunk($products, $dryRun);
        });

        if ($this->examples !== []) {
            $this->table(['variant_id', 'from', 'to'], $this->examples);
        }

        $this->info(sprintf(
            '%s updated=%d unchanged=%d skipped=%d collisions=%d order_skus=%d',
            $dryRun ? '[DRY RUN]' : 'Done.',
            $this->updated,
            $this->unchanged,
            $this->skipped,
            $this->collisions,
            $this->orderItems
        ));

        return $this->collisions > 0 ? self::FAILURE : self::SUCCESS;
    }

    private function repairChunk(Collection $products, bool $dryRun): void
    {
        $productIds = $products->pluck('id')->map(fn ($id) => (int) $id)->all();
        $parentSku = $products->mapWithKeys(fn ($product) => [(int) $product->id => trim((string) $product->sku)]);

        $optionCounts = DB::table('product_product_options')
            ->whereIn('product_id', $productIds)
            ->select('product_id', DB::raw('COUNT(*) as aggregate'))
            ->groupBy('product_id')
            ->pluck('aggregate', 'product_id');

        $rows = DB::table('product_variants as v')
            ->join('product_variant_option_values as pvov', 'pvov.product_variant_id', '=', 'v.id')
            ->join('product_option_values as pov', 'pov.id', '=', 'pvov.product_option_value_id')
            ->join('product_product_options as ppo', function ($join) {
                $join->on('ppo.product_option_id', '=', 'pov.product_option_id')
                    ->on('ppo.product_id', '=', 'v.product_id');
            })
            ->whereIn('v.product_id', $productIds)
            ->whereNull('v.deleted_at')
            ->orderBy('v.id')
            ->orderBy('ppo.position')
            ->orderBy('pov.id')
            ->join('product_options as po', 'po.id', '=', 'pov.product_option_id')
            ->get([
                'v.id as variant_id',
                'v.product_id',
                'v.sku',
                'pov.product_option_id',
                'po.label as option_label',
                'pov.label as value_label',
                'pov.code as value_sku',
            ]);

        $grouped = [];

        foreach ($rows as $row) {
            $variantId = (int) $row->variant_id;
            $grouped[$variantId]['product_id'] = (int) $row->product_id;
            $grouped[$variantId]['sku'] = (string) $row->sku;
            $grouped[$variantId]['parts'][] = $row;
        }

        $liveVariantIds = DB::table('product_variants')
            ->whereIn('product_id', $productIds)
            ->whereNull('deleted_at')
            ->pluck('id');

        foreach ($liveVariantIds as $variantId) {
            if (! isset($grouped[(int) $variantId])) {
                $this->skipped++;
            }
        }

        /** @var array<int, string> $planned */
        $planned = [];
        /** @var array<int, string> $previous */
        $previous = [];
        /** @var array<int, array{product_id: int, sku: string, pairs: array<int, array{0: string, 1: string}>}> $targets */
        $targets = [];

        foreach ($grouped as $variantId => $info) {
            $expected = (int) ($optionCounts[$info['product_id']] ?? 0);
            $seenOptions = [];
            $labels = [];
            $invalid = false;

            $pairs = [];

            foreach ($info['parts'] as $part) {
                $optionId = (int) $part->product_option_id;

                if (isset($seenOptions[$optionId])) {
                    $invalid = true;
                    break;
                }

                $seenOptions[$optionId] = true;
                $optionLabel = trim((string) $part->option_label);
                $valueLabel = trim((string) $part->value_label);
                $valueSku = trim((string) ($part->value_sku ?? ''));

                if ($valueSku === '') {
                    $valueSku = $valueLabel;
                }

                if ($optionLabel === '' || $valueLabel === '' || $valueSku === '') {
                    $invalid = true;
                    break;
                }

                $labels[] = $valueSku;
                $pairs[] = [$optionLabel, $valueLabel];
            }

            if ($invalid || $expected === 0 || count($labels) !== $expected) {
                $this->skipped++;
                continue;
            }

            $newSku = $parentSku[$info['product_id']].'-'.implode('-', $labels);

            if (strlen($newSku) > 255) {
                $this->skipped++;
                $this->warn("Variant {$variantId} SKU exceeds 255 characters.");
                continue;
            }

            $targets[$variantId] = [
                'product_id' => $info['product_id'],
                'sku'        => $newSku,
                'pairs'      => $pairs,
            ];

            if ($newSku === $info['sku']) {
                $this->unchanged++;
                continue;
            }

            $planned[$variantId] = $newSku;
            $previous[$variantId] = $info['sku'];
        }

        $this->dropCollisions($planned, $previous);

        foreach ($targets as $variantId => $target) {
            $current = (string) ($grouped[$variantId]['sku'] ?? '');

            if ($target['sku'] !== $current && ! isset($planned[$variantId])) {
                unset($targets[$variantId]);
            }
        }

        if ($dryRun) {
            foreach ($planned as $variantId => $newSku) {
                $this->rememberExample($variantId, $previous[$variantId], $newSku);
                $this->updated++;
            }

            $this->rewriteOrderSkus($targets, $productIds, true);

            return;
        }

        if ($planned !== []) {
            $this->writeSkus($planned, $previous);
            $this->updated += count($planned);
        }

        $this->rewriteOrderSkus($targets, $productIds, false);
    }

    /**
     * @param  array<int, string>  $planned
     * @param  array<int, string>  $previous
     */
    private function dropCollisions(array &$planned, array &$previous): void
    {
        if ($planned === []) {
            return;
        }

        $owners = DB::table('product_variants')
            ->whereIn('sku', array_values($planned))
            ->pluck('id', 'sku');

        $blocked = [];
        $seen = [];

        foreach ($planned as $variantId => $newSku) {
            if (isset($seen[$newSku])) {
                $blocked[$variantId] = true;
                $blocked[$seen[$newSku]] = true;
                continue;
            }

            $seen[$newSku] = $variantId;
            $ownerId = isset($owners[$newSku]) ? (int) $owners[$newSku] : null;

            if ($ownerId === null || $ownerId === (int) $variantId) {
                continue;
            }

            $ownerNext = $planned[$ownerId] ?? null;

            if ($ownerNext === null || $ownerNext === $newSku) {
                $blocked[$variantId] = true;
            }
        }

        foreach (array_keys($blocked) as $variantId) {
            $this->warn("SKU collision, left variant {$variantId} as {$previous[$variantId]}.");
            unset($planned[$variantId], $previous[$variantId]);
            $this->collisions++;
        }
    }

    /**
     * @param  array<int, string>  $planned
     * @param  array<int, string>  $previous
     */
    private function writeSkus(array $planned, array $previous): void
    {
        $now = now()->toDateTimeString();

        DB::transaction(function () use ($planned, $previous, $now) {
            foreach (array_chunk(array_keys($planned), 200) as $ids) {
                DB::table('product_variants')
                    ->whereIn('id', $ids)
                    ->update([
                        'sku'        => DB::raw("CONCAT('__sku_repair_', id)"),
                        'updated_at' => $now,
                    ]);
            }

            foreach (array_chunk($planned, 200, true) as $chunk) {
                $case = 'CASE id';
                $bindings = [];
                $ids = array_keys($chunk);

                foreach ($chunk as $variantId => $newSku) {
                    $case .= ' WHEN ? THEN ?';
                    $bindings[] = $variantId;
                    $bindings[] = $newSku;
                    $this->rememberExample($variantId, $previous[$variantId], $newSku);
                }

                $case .= ' END';
                $placeholders = implode(',', array_fill(0, count($chunk), '?'));

                DB::update(
                    "UPDATE product_variants SET sku = {$case}, updated_at = ? WHERE id IN ({$placeholders})",
                    array_merge($bindings, [$now], $ids)
                );
            }
        });
    }

    private function rememberExample(int $variantId, string $from, string $to): void
    {
        if (count($this->examples) >= 15) {
            return;
        }

        $this->examples[] = [$variantId, $from, $to];
    }

    /**
     * Point order documents at the variant SKU. Match variant_id first, then the
     * option labels stored on the order item.
     *
     * @param  array<int, array{product_id: int, sku: string, pairs: array<int, array{0: string, 1: string}>}>  $targets
     * @param  array<int, int>  $productIds
     */
    private function rewriteOrderSkus(array $targets, array $productIds, bool $dryRun): void
    {
        if ($targets === [] || $productIds === [] || ! Schema::hasTable('order_items')) {
            return;
        }

        $byVariant = [];
        $byProductAndKey = [];
        $optionNames = [];

        foreach ($targets as $variantId => $target) {
            $byVariant[$variantId] = $target['sku'];
            $key = $this->labelKey($target['pairs']);
            $byProductAndKey[$target['product_id']][$key][] = $variantId;

            foreach ($target['pairs'] as [$optionLabel]) {
                $optionNames[$target['product_id']][$this->normalize($optionLabel)] = true;
            }
        }

        DB::table('order_items')
            ->whereIn('product_id', $productIds)
            ->orderBy('id')
            ->select('id', 'product_id', 'sku', 'additional')
            ->chunkById(500, function (Collection $items) use ($byVariant, $byProductAndKey, $optionNames, $dryRun) {
                $skuUpdates = [];
                $additionalUpdates = [];

                foreach ($items as $item) {
                    $productId = (int) $item->product_id;
                    $additional = $this->decodeAdditional($item->additional);
                    $newSku = $this->resolveOrderSku($productId, $additional, $byVariant, $byProductAndKey, $optionNames);

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

                    if ((string) $item->sku !== $newSku) {
                        $skuUpdates[(int) $item->id] = $newSku;
                    }

                    if ($this->additionalVariantSku($additional, $newSku)) {
                        $additionalUpdates[(int) $item->id] = $additional;
                    }
                }

                $this->orderItems += count($skuUpdates);
                $now = now()->toDateTimeString();
                $this->syncRelatedItemSkus($skuUpdates, $dryRun, $now);

                if ($dryRun || ($skuUpdates === [] && $additionalUpdates === [])) {
                    return;
                }

                $this->writeColumnSkus('order_items', $skuUpdates, $now);
                $this->writeAdditional($additionalUpdates, $now);
            });
    }

    /**
     * @param  array<string, mixed>  $additional
     * @param  array<int, string>  $byVariant
     * @param  array<int, array<string, array<int, int>>>  $byProductAndKey
     * @param  array<int, array<string, true>>  $optionNames
     */
    private function resolveOrderSku(
        int $productId,
        array $additional,
        array $byVariant,
        array $byProductAndKey,
        array $optionNames
    ): ?string {
        $variantId = (int) ($additional['variant_id'] ?? 0);

        if ($variantId > 0 && isset($byVariant[$variantId])) {
            return $byVariant[$variantId];
        }

        $pairs = $this->orderLabelPairs($additional, $optionNames[$productId] ?? []);

        if ($pairs === []) {
            return null;
        }

        $ids = $byProductAndKey[$productId][$this->labelKey($pairs)] ?? [];

        if (count($ids) !== 1) {
            return null;
        }

        return $byVariant[$ids[0]] ?? null;
    }

    /**
     * @param  array<string, mixed>  $additional
     * @param  array<string, true>  $optionNames
     * @return array<int, array{0: string, 1: string}>
     */
    private function orderLabelPairs(array $additional, array $optionNames): array
    {
        $attributes = $additional['attributes'] ?? null;

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

        $pairs = [];
        $list = array_is_list($attributes);

        foreach ($attributes as $row) {
            if (! is_array($row)) {
                continue;
            }

            if ($list) {
                $option = trim((string) ($row['attribute_name'] ?? $row['label'] ?? ''));
                $value = trim((string) ($row['option_label'] ?? $row['value_label'] ?? ''));
            } else {
                $option = trim((string) ($row['attribute_name'] ?? $row['option_label'] ?? ''));
                $value = trim((string) ($row['value_label'] ?? ''));
            }

            if ($option === '' || $value === '' || ! isset($optionNames[$this->normalize($option)])) {
                continue;
            }

            $pairs[] = [$option, $value];
        }

        return $pairs;
    }

    /**
     * @param  array<string, mixed>  $additional
     */
    private function additionalVariantSku(array &$additional, string $newSku): bool
    {
        $hasVariant = ! empty($additional['variant_id']) || array_key_exists('variant_sku', $additional);

        if (! $hasVariant || (string) ($additional['variant_sku'] ?? '') === $newSku) {
            return false;
        }

        $additional['variant_sku'] = $newSku;

        return true;
    }

    /**
     * @param  array<int, array{0: string, 1: string}>  $pairs
     */
    private function labelKey(array $pairs): string
    {
        $parts = [];

        foreach ($pairs as [$option, $value]) {
            $parts[] = $this->normalize($option)."\n".$value;
        }

        sort($parts);

        return implode("\n---\n", $parts);
    }

    private function normalize(string $value): string
    {
        return mb_strtolower(trim($value));
    }

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

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

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

        return [];
    }

    /**
     * @param  array<int, string>  $skuUpdates
     */
    private function syncRelatedItemSkus(array $skuUpdates, bool $dryRun, string $now): void
    {
        if ($skuUpdates === []) {
            return;
        }

        foreach (['invoice_items', 'shipment_items', 'refund_items'] as $table) {
            if (! Schema::hasTable($table) || ! Schema::hasColumn($table, 'sku') || ! Schema::hasColumn($table, 'order_item_id')) {
                continue;
            }

            $rows = DB::table($table)
                ->whereIn('order_item_id', array_keys($skuUpdates))
                ->get(['id', 'order_item_id', 'sku']);

            $updates = [];

            foreach ($rows as $row) {
                $newSku = $skuUpdates[(int) $row->order_item_id] ?? null;

                if ($newSku === null || (string) $row->sku === $newSku) {
                    continue;
                }

                $updates[(int) $row->id] = $newSku;
            }

            $this->orderItems += count($updates);

            if (! $dryRun && $updates !== []) {
                $this->writeColumnSkus($table, $updates, $now);
            }
        }
    }

    /**
     * @param  array<int, string>  $updates
     */
    private function writeColumnSkus(string $table, array $updates, string $now): void
    {
        foreach (array_chunk($updates, 200, true) as $chunk) {
            $case = 'CASE id';
            $bindings = [];

            foreach ($chunk as $id => $sku) {
                $case .= ' WHEN ? THEN ?';
                $bindings[] = $id;
                $bindings[] = $sku;
            }

            $case .= ' END';
            $placeholders = implode(',', array_fill(0, count($chunk), '?'));

            DB::update(
                "UPDATE {$table} SET sku = {$case}, updated_at = ? WHERE id IN ({$placeholders})",
                array_merge($bindings, [$now], array_keys($chunk))
            );
        }
    }

    /**
     * @param  array<int, array<string, mixed>>  $updates
     */
    private function writeAdditional(array $updates, string $now): void
    {
        foreach ($updates as $id => $additional) {
            $encoded = json_encode($additional, JSON_UNESCAPED_UNICODE);

            if ($encoded === false) {
                continue;
            }

            DB::table('order_items')->where('id', $id)->update([
                'additional' => $encoded,
                'updated_at' => $now,
            ]);
        }
    }
}