nshop/app/Console/Commands/ResetCatalogCommand.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\Storage;
use Throwable;
use Webkul\Core\Facades\ElasticSearch;
use Webkul\Core\Models\Channel;
use Webkul\Product\Helpers\Product as ProductIndexName;

/**
 * Wipe Bagisto catalog rows so `products:migrate-asteria` (or catalog:sync) can start clean.
 *
 * Keeps attributes, categories, customers, and orders. Historical sales lines keep their
 * SKU snapshots; `product_id` is set to NULL. Reviews / Q&A / carts / wishlists that
 * point at products are removed — re-run `reviews:migrate-asteria --reset-progress`
 * after the product import if those comments should come back.
 *
 * Usage
 * ─────
 * php artisan catalog:reset --dry-run
 * php artisan catalog:reset --force
 * php artisan catalog:reset --force --purge-files
 */
class ResetCatalogCommand extends Command
{
    protected $signature = 'catalog:reset
        {--dry-run : Count rows only, do not write}
        {--force : Skip the confirmation prompt}
        {--purge-files : Also delete storage/app/public/product image directories}';

    protected $description = 'Truncate existing product catalog data so a fresh product migration can run';

    /**
     * Catalog tables truncated in child-first order (safe even if FK checks stay on).
     *
     * @var list<string>
     */
    private const CATALOG_TABLES = [
        // Longyi flexible variants
        'product_variant_option_values',
        'product_variant_images',
        'product_variants',
        'product_product_options',
        'product_option_values',
        'product_options',

        // Q&A / reviews
        'product_question_answer_votes',
        'product_question_answers',
        'product_questions',
        'product_review_images',
        'product_reviews',

        // Booking
        'booking_product_event_ticket_translations',
        'booking_product_event_tickets',
        'booking_product_appointment_slots',
        'booking_product_default_slots',
        'booking_product_rental_slots',
        'booking_product_table_slots',
        'booking_products',

        // Bundle / grouped / customizable / downloadable
        'product_bundle_option_products',
        'product_bundle_option_translations',
        'product_bundle_options',
        'product_grouped_products',
        'product_customizable_option_prices',
        'product_customizable_option_translations',
        'product_customizable_options',
        'product_downloadable_link_translations',
        'product_downloadable_links',
        'product_downloadable_sample_translations',
        'product_downloadable_samples',

        // Catalog rule product indices (not the rules themselves)
        'catalog_rule_product_prices',
        'catalog_rule_products',

        // Runtime references
        'cart_item_inventories',
        'cart_items',
        'wishlist_items',
        'wishlist',
        'compare_items',

        // Associations / media / inventory / indices
        'product_cross_sells',
        'product_up_sells',
        'product_relations',
        'product_super_attributes',
        'product_categories',
        'product_channels',
        'product_attribute_values',
        'product_images',
        'product_videos',
        'product_inventories',
        'product_ordered_inventories',
        'product_inventory_indices',
        'product_salable_inventories',
        'product_price_indices',
        'product_customer_group_prices',
        'product_flat',

        'products',
    ];

    /**
     * Historical rows that keep their snapshot but must not block deleting products.
     *
     * @var list<array{0: string, 1: string}>
     */
    private const DETACH_PRODUCT_ID = [
        ['order_items', 'product_id'],
        ['invoice_items', 'product_id'],
        ['shipment_items', 'product_id'],
        ['refund_items', 'product_id'],
        ['bookings', 'product_id'],
    ];

    public function handle(): int
    {
        $dryRun = (bool) $this->option('dry-run');
        $tables = $this->existingTables(self::CATALOG_TABLES);

        $this->warn('This clears catalog products so they can be re-imported.');
        $this->line('Kept: attributes, categories, customers, orders (SKU snapshot stays; product_id set null).');
        $this->line('Removed: products, variants, inventories, reviews, Q&A, cart items, wishlists, compare items.');
        $this->newLine();

        $rows = $this->countRows($tables);
        $this->table(['table', 'rows'], collect($rows)->map(fn ($count, $table) => [$table, $count])->values()->all());

        $total = array_sum($rows);
        $this->info("Catalog rows that would be truncated: {$total}");

        $detachCounts = $this->countDetachRows();
        if ($detachCounts !== []) {
            $this->newLine();
            $this->comment('Historical rows whose product_id will be set to NULL:');
            $this->table(['table', 'rows with product_id'], collect($detachCounts)->map(fn ($count, $table) => [$table, $count])->values()->all());
        }

        $rewriteCount = $this->countProductUrlRewrites();
        if ($rewriteCount > 0) {
            $this->comment("url_rewrites (entity_type=product): {$rewriteCount}");
        }

        if ($dryRun) {
            $this->info('Dry run — nothing written.');

            return self::SUCCESS;
        }

        if (! $this->option('force') && ! $this->confirm('Truncate catalog tables now?', false)) {
            $this->info('Aborted.');

            return self::SUCCESS;
        }

        $driver = DB::getDriverName();

        try {
            $this->disableForeignKeyChecks($driver);

            $this->detachHistoricalProductIds();
            $this->deleteProductUrlRewrites();
            $this->deleteProductVisits();

            foreach ($tables as $table) {
                $this->emptyTable($driver, $table);
            }
        } finally {
            $this->enableForeignKeyChecks($driver);
        }

        if ($this->option('purge-files')) {
            Storage::deleteDirectory('product');
            $this->info('Deleted storage directory: product/');
        }

        $this->wipeElasticsearchIndices();

        $this->newLine();
        $this->info('Catalog reset finished. Next:');
        $this->line('  php artisan products:migrate-asteria');
        $this->line('  php artisan reviews:migrate-asteria --reset-progress --sync   # if reviews should be re-imported');

        return self::SUCCESS;
    }

    /**
     * @param  list<string>  $tables
     * @return list<string>
     */
    private function existingTables(array $tables): array
    {
        return array_values(array_filter($tables, fn (string $table) => Schema::hasTable($table)));
    }

    /**
     * @param  list<string>  $tables
     * @return array<string, int>
     */
    private function countRows(array $tables): array
    {
        $counts = [];

        foreach ($tables as $table) {
            $counts[$table] = (int) DB::table($table)->count();
        }

        return $counts;
    }

    /**
     * @return array<string, int>
     */
    private function countDetachRows(): array
    {
        $counts = [];

        foreach (self::DETACH_PRODUCT_ID as [$table, $column]) {
            if (! Schema::hasTable($table) || ! Schema::hasColumn($table, $column)) {
                continue;
            }

            $count = (int) DB::table($table)->whereNotNull($column)->count();

            if ($count > 0) {
                $counts[$table] = $count;
            }
        }

        return $counts;
    }

    private function countProductUrlRewrites(): int
    {
        if (! Schema::hasTable('url_rewrites')) {
            return 0;
        }

        return (int) DB::table('url_rewrites')->where('entity_type', 'product')->count();
    }

    private function detachHistoricalProductIds(): void
    {
        foreach (self::DETACH_PRODUCT_ID as [$table, $column]) {
            if (! Schema::hasTable($table) || ! Schema::hasColumn($table, $column)) {
                continue;
            }

            DB::table($table)->whereNotNull($column)->update([$column => null]);
        }
    }

    private function deleteProductUrlRewrites(): void
    {
        if (! Schema::hasTable('url_rewrites')) {
            return;
        }

        DB::table('url_rewrites')->where('entity_type', 'product')->delete();
    }

    private function deleteProductVisits(): void
    {
        if (! Schema::hasTable('visits') || ! Schema::hasColumn('visits', 'visitable_type')) {
            return;
        }

        DB::table('visits')->where('visitable_type', 'like', '%Product%')->delete();
    }

    private function emptyTable(string $driver, string $table): void
    {
        if ($driver === 'mysql') {
            DB::statement('TRUNCATE TABLE '.$this->quoteTable($table));

            return;
        }

        DB::table($table)->delete();

        if ($driver === 'sqlite') {
            DB::table('sqlite_sequence')->where('name', DB::getTablePrefix().$table)->delete();
        }
    }

    private function quoteTable(string $table): string
    {
        $name = DB::getTablePrefix().$table;

        return '`'.str_replace('`', '``', $name).'`';
    }

    private function disableForeignKeyChecks(string $driver): void
    {
        if ($driver === 'mysql') {
            DB::statement('SET FOREIGN_KEY_CHECKS=0');
        } elseif ($driver === 'sqlite') {
            DB::statement('PRAGMA foreign_keys = OFF');
        }
    }

    private function enableForeignKeyChecks(string $driver): void
    {
        if ($driver === 'mysql') {
            DB::statement('SET FOREIGN_KEY_CHECKS=1');
        } elseif ($driver === 'sqlite') {
            DB::statement('PRAGMA foreign_keys = ON');
        }
    }

    private function wipeElasticsearchIndices(): void
    {
        if (core()->getConfigData('catalog.products.search.engine') !== 'elastic') {
            return;
        }

        try {
            $channels = Channel::with('locales')->get();

            foreach ($channels as $channel) {
                foreach ($channel->locales as $locale) {
                    $index = ProductIndexName::formatElasticSearchIndexName($channel->code, $locale->code);

                    try {
                        ElasticSearch::indices()->delete(['index' => $index]);
                        $this->comment("Deleted Elasticsearch index: {$index}");
                    } catch (Throwable $e) {
                        $this->comment("Elasticsearch index {$index} skipped: ".$e->getMessage());
                    }
                }
            }
        } catch (Throwable $e) {
            $this->warn('Elasticsearch wipe skipped: '.$e->getMessage());
        }
    }
}