nshop/app/Console/Commands/RepairOutOfStockProductStatus.php

<?php

namespace App\Console\Commands;

use App\Services\Asteria\Magento1Schema;
use Illuminate\Console\Command;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Webkul\Product\Helpers\Indexers\ElasticSearch;
use Webkul\Product\Models\Product;

/**
 * Disables Bagisto products that were migrated from Asteria while the old
 * site stock availability is Out of Stock (is_in_stock = 0).
 *
 * Does not re-enable products that are In Stock. Products with no Magento
 * stock row are left unchanged.
 *
 * Usage
 * ─────
 * php artisan products:repair-out-of-stock-status
 * php artisan products:repair-out-of-stock-status --dry-run
 * php artisan products:repair-out-of-stock-status --sku=PC001
 * php artisan products:repair-out-of-stock-status --no-index
 */
class RepairOutOfStockProductStatus extends Command
{
    protected $signature = 'products:repair-out-of-stock-status
        {--dry-run : Count products that would be disabled without writing}
        {--connection=asteria : Laravel DB connection for the Asteria database}
        {--sku= : Repair one Bagisto product SKU}
        {--no-index : Skip rebuilding the elastic index}';

    protected $description = 'Disable migrated products whose Asteria stock status is Out of Stock';

    private string $connectionName = 'asteria';

    private bool $hasStockId = false;

    private int $statusAttributeId = 0;

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

    private bool $hasUniqueId = false;

    private bool $hasJsonValue = false;

    private bool $hasProductFlat = false;

    private int $scanned = 0;

    private int $disabled = 0;

    private int $unchanged = 0;

    private int $inStock = 0;

    private int $noStockRow = 0;

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

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

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

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

            return self::FAILURE;
        }

        $schema = new Magento1Schema($this->connectionName);

        if (! $schema->hasTable('cataloginventory_stock_item')) {
            $this->error("Asteria table 'cataloginventory_stock_item' is missing on connection '{$this->connectionName}'.");

            return self::FAILURE;
        }

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

        if (! in_array('product_id', $columns, true) || ! in_array('is_in_stock', $columns, true)) {
            $this->error('cataloginventory_stock_item is missing product_id or is_in_stock.');

            return self::FAILURE;
        }

        $this->hasStockId = in_array('stock_id', $columns, true);

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

            return self::FAILURE;
        }

        $statusAttributeId = DB::table('attributes')->where('code', 'status')->value('id');

        if (! $statusAttributeId) {
            $this->error('Bagisto status attribute is missing.');

            return self::FAILURE;
        }

        $this->statusAttributeId = (int) $statusAttributeId;
        $this->channelCodes = DB::table('channels')->orderBy('id')->pluck('code')
            ->map(fn ($code) => trim((string) $code))
            ->filter()
            ->unique()
            ->values()
            ->all();

        if ($this->channelCodes === []) {
            $this->channelCodes = ['default'];
        }

        $this->hasUniqueId = Schema::hasColumn('product_attribute_values', 'unique_id');
        $this->hasJsonValue = Schema::hasColumn('product_attribute_values', 'json_value');
        $this->hasProductFlat = Schema::hasTable('product_flat') && Schema::hasColumn('product_flat', 'status');

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

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

            if (! (clone $query)->exists()) {
                $exists = Product::query()->where('sku', $sku)->exists();
                $this->error($exists
                    ? "Product {$sku} was not migrated from Asteria."
                    : "Product {$sku} was not found.");

                return self::FAILURE;
            }
        }

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

        if ($this->examples !== []) {
            $this->table(['sku', 'product_id'], $this->examples);
        }

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

        $this->info(sprintf(
            '%s scanned=%d disabled=%d unchanged=%d in_stock=%d no_stock_row=%d',
            $dryRun ? '[DRY RUN]' : 'Done.',
            $this->scanned,
            $this->disabled,
            $this->unchanged,
            $this->inStock,
            $this->noStockRow
        ));

        return self::SUCCESS;
    }

    private function repairChunk(Collection $products, bool $dryRun): void
    {
        $asteriaIds = $products
            ->pluck('migrated_from_asteria_id')
            ->map(fn ($id) => (int) $id)
            ->unique()
            ->values()
            ->all();

        $stock = $this->loadStock($asteriaIds);
        $outOfStock = $products->filter(function ($product) use ($stock) {
            $asteriaId = (int) $product->migrated_from_asteria_id;

            return array_key_exists($asteriaId, $stock) && $stock[$asteriaId] === false;
        })->values();

        foreach ($products as $product) {
            $this->scanned++;
            $asteriaId = (int) $product->migrated_from_asteria_id;

            if (! array_key_exists($asteriaId, $stock)) {
                $this->noStockRow++;
            } elseif ($stock[$asteriaId] === true) {
                $this->inStock++;
            }
        }

        if ($outOfStock->isEmpty()) {
            return;
        }

        $productIds = $outOfStock->pluck('id')->map(fn ($id) => (int) $id)->all();
        $statusRows = DB::table('product_attribute_values')
            ->where('attribute_id', $this->statusAttributeId)
            ->whereIn('product_id', $productIds)
            ->get(['id', 'product_id', 'channel', 'boolean_value']);

        $rowsByProduct = [];

        foreach ($statusRows as $row) {
            $rowsByProduct[(int) $row->product_id][] = $row;
        }

        $flatNeedsUpdate = [];

        if ($this->hasProductFlat) {
            $flatNeedsUpdate = DB::table('product_flat')
                ->whereIn('product_id', $productIds)
                ->where(function ($query) {
                    $query->where('status', '!=', 0)->orWhereNull('status');
                })
                ->pluck('product_id')
                ->mapWithKeys(fn ($id) => [(int) $id => true])
                ->all();
        }

        $changedIds = [];

        foreach ($outOfStock as $product) {
            $productId = (int) $product->id;
            $rows = $rowsByProduct[$productId] ?? [];
            $presentChannels = [];
            $statusOff = $rows !== [];

            foreach ($rows as $row) {
                if ((int) $row->boolean_value !== 0) {
                    $statusOff = false;
                }

                $channel = trim((string) ($row->channel ?? ''));

                if ($channel !== '') {
                    $presentChannels[$channel] = true;
                }
            }

            $missingChannel = false;

            foreach ($this->channelCodes as $channel) {
                if (! isset($presentChannels[$channel])) {
                    $missingChannel = true;
                    break;
                }
            }

            if ($statusOff && ! $missingChannel && ! isset($flatNeedsUpdate[$productId])) {
                $this->unchanged++;

                continue;
            }

            $this->disabled++;
            $changedIds[] = $productId;

            if (count($this->examples) < 20) {
                $this->examples[] = [(string) $product->sku, $productId];
            }
        }

        if ($changedIds === [] || $dryRun) {
            return;
        }

        $this->touchedProductIds = array_merge($this->touchedProductIds, $changedIds);

        DB::table('product_attribute_values')
            ->where('attribute_id', $this->statusAttributeId)
            ->whereIn('product_id', $changedIds)
            ->where(function ($query) {
                $query->where('boolean_value', '!=', 0)->orWhereNull('boolean_value');
            })
            ->update(['boolean_value' => 0]);

        $inserts = [];

        foreach ($changedIds as $productId) {
            $presentChannels = [];

            foreach ($rowsByProduct[$productId] ?? [] as $row) {
                $channel = trim((string) ($row->channel ?? ''));

                if ($channel !== '') {
                    $presentChannels[$channel] = true;
                }
            }

            foreach ($this->channelCodes as $channel) {
                if (isset($presentChannels[$channel])) {
                    continue;
                }

                $inserts[] = $this->statusValueRow($productId, $channel);
            }
        }

        foreach (array_chunk($inserts, 500) as $chunk) {
            DB::table('product_attribute_values')->insert($chunk);
        }

        if ($this->hasProductFlat) {
            $flatUpdate = ['status' => 0];

            if (Schema::hasColumn('product_flat', 'updated_at')) {
                $flatUpdate['updated_at'] = now();
            }

            DB::table('product_flat')
                ->whereIn('product_id', $changedIds)
                ->update($flatUpdate);
        }
    }

    /**
     * @param  array<int, int>  $asteriaIds
     * @return array<int, bool> asteria id => true when In Stock
     */
    private function loadStock(array $asteriaIds): array
    {
        if ($asteriaIds === []) {
            return [];
        }

        $query = DB::connection($this->connectionName)
            ->table('cataloginventory_stock_item')
            ->whereIn('product_id', $asteriaIds);

        if ($this->hasStockId) {
            $query->where('stock_id', 1);
        }

        $stock = [];

        foreach ($query->get(['product_id', 'is_in_stock']) as $row) {
            $stock[(int) $row->product_id] = (int) $row->is_in_stock === 1;
        }

        return $stock;
    }

    /**
     * @return array<string, mixed>
     */
    private function statusValueRow(int $productId, string $channel): array
    {
        $row = [
            'locale'        => null,
            'channel'       => $channel,
            'text_value'    => null,
            'boolean_value' => 0,
            'integer_value' => null,
            'float_value'   => null,
            'datetime_value'=> null,
            'date_value'    => null,
            'product_id'    => $productId,
            'attribute_id'  => $this->statusAttributeId,
        ];

        if ($this->hasJsonValue) {
            $row['json_value'] = null;
        }

        if ($this->hasUniqueId) {
            $row['unique_id'] = $channel.'|'.$productId.'|'.$this->statusAttributeId;
        }

        return $row;
    }

    private function reindexProducts(): void
    {
        $ids = array_values(array_unique($this->touchedProductIds));

        if ($ids === [] || ! $this->elasticEnabled()) {
            return;
        }

        $this->info('Reindexing '.count($ids).' products…');

        $elasticIndexer = app(ElasticSearch::class);

        foreach (array_chunk($ids, 20) as $chunk) {
            $products = Product::query()->with([
                'attribute_family',
                'attribute_values',
                'channels',
                'categories',
            ])->whereIn('id', $chunk)->get();

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

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