nshop/app/Console/Commands/ResetCustomersCommand.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/**
 * Wipe Bagisto storefront customers so `customers:migrate-asteria` can start clean.
 *
 * Keeps customer_groups, admins, products, and orders. Historical orders / reviews /
 * Q&A keep their snapshots; `customer_id` is set to NULL. Customer addresses,
 * newsletters, wishlists, compare items, reward/growth balances, and login tokens
 * are removed.
 *
 * Usage
 * ─────
 * php artisan customers:reset --dry-run
 * php artisan customers:reset --force
 */
class ResetCustomersCommand extends Command
{
    protected $signature = 'customers:reset
        {--dry-run : Count rows only, do not write}
        {--force : Skip the confirmation prompt}';

    protected $description = 'Truncate existing storefront customers so a fresh customer migration can run';

    private const PROGRESS_KEY = 'migrate_asteria_customers_last_id';

    /**
     * Customer-owned tables truncated in child-first order.
     *
     * @var list<string>
     */
    private const CUSTOMER_TABLES = [
        'customer_notes',
        'customer_social_accounts',
        'customer_password_resets',
        'gdpr_data_request',
        'wishlist_items',
        'wishlist',
        'compare_items',
        'cart_rule_customers',
        'cart_rule_coupon_usage',
        'mw_reward_point_customer_sign',
        'mw_reward_point_history',
        'mw_reward_point_customer',
        'mw_growth_value_history',
        'mw_growth_value_customer',
        'member_log',
        'downloadable_link_purchased',
        'subscribers_list',
        'customers',
    ];

    /**
     * Historical / catalog rows that keep their snapshot but must not block deleting customers.
     *
     * @var list<array{0: string, 1: string}>
     */
    private const DETACH_CUSTOMER_ID = [
        ['orders', 'customer_id'],
        ['shipments', 'customer_id'],
        ['product_reviews', 'customer_id'],
        ['product_questions', 'customer_id'],
        ['product_question_answers', 'customer_id'],
        ['product_question_answer_votes', 'customer_id'],
        ['cart_rule_coupons', 'customer_id'],
        ['gift_cards', 'customer_id'],
        ['gift_card_usage_logs', 'customer_id'],
        ['addresses', 'customer_id'],
    ];

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

        $this->warn('This clears storefront customers so they can be re-imported.');
        $this->line('Kept: customer groups, admins, products, orders (email snapshot stays; customer_id set null).');
        $this->line('Removed: customers, customer addresses, newsletters, wishlists, compare, reward/growth balances, tokens.');
        $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("Customer rows that would be truncated: {$total}");

        $addressCount = $this->countCustomerAddresses();
        if ($addressCount > 0) {
            $this->comment("addresses (address_type=customer): {$addressCount}");
        }

        $cartCount = $this->countCustomerCarts();
        if ($cartCount > 0) {
            $this->comment("cart (logged-in customers): {$cartCount}");
        }

        $tokenCount = $this->countCustomerTokens();
        if ($tokenCount > 0) {
            $this->comment("personal_access_tokens (customers): {$tokenCount}");
        }

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

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

            return self::SUCCESS;
        }

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

            return self::SUCCESS;
        }

        $driver = DB::getDriverName();

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

            $this->deleteCustomerAddresses();
            $this->deleteCustomerCarts();
            $this->deleteCustomerTokens();
            $this->deleteCustomerVisits();
            $this->detachHistoricalCustomerIds();

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

        Cache::forget(self::PROGRESS_KEY);

        $this->newLine();
        $this->info('Customer reset finished. Next:');
        $this->line('  php artisan customers:migrate-asteria --reset-progress');
        $this->line('  php artisan orders:migrate-asteria --reset-progress   # if orders should re-link to new customer ids');
        $this->line('  php artisan reviews:migrate-asteria --reset-progress --sync');

        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_CUSTOMER_ID as [$table, $column]) {
            if (! Schema::hasTable($table) || ! Schema::hasColumn($table, $column)) {
                continue;
            }

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

            if ($table === 'gift_cards') {
                $query->where($column, '!=', 0);
            }

            if ($table === 'addresses' && Schema::hasColumn($table, 'address_type')) {
                $query->where('address_type', '!=', 'customer');
            }

            $count = (int) $query->count();

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

        return $counts;
    }

    private function countCustomerAddresses(): int
    {
        if (! Schema::hasTable('addresses') || ! Schema::hasColumn('addresses', 'address_type')) {
            return 0;
        }

        return (int) DB::table('addresses')->where('address_type', 'customer')->count();
    }

    private function countCustomerCarts(): int
    {
        if (! Schema::hasTable('cart') || ! Schema::hasColumn('cart', 'customer_id')) {
            return 0;
        }

        return (int) DB::table('cart')->whereNotNull('customer_id')->count();
    }

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

        return (int) $this->customerTokenQuery()->count();
    }

    private function deleteCustomerAddresses(): void
    {
        if (! Schema::hasTable('addresses') || ! Schema::hasColumn('addresses', 'address_type')) {
            return;
        }

        DB::table('addresses')->where('address_type', 'customer')->delete();
    }

    private function deleteCustomerCarts(): void
    {
        if (! Schema::hasTable('cart') || ! Schema::hasColumn('cart', 'customer_id')) {
            return;
        }

        $cartIds = DB::table('cart')->whereNotNull('customer_id')->pluck('id');

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

        $itemIds = Schema::hasTable('cart_items')
            ? DB::table('cart_items')->whereIn('cart_id', $cartIds)->pluck('id')
            : collect();

        if ($itemIds->isNotEmpty() && Schema::hasTable('cart_item_inventories')) {
            DB::table('cart_item_inventories')->whereIn('cart_item_id', $itemIds)->delete();
        }

        if ($itemIds->isNotEmpty()) {
            DB::table('cart_items')->whereIn('id', $itemIds)->delete();
        }

        if (Schema::hasTable('cart_payment')) {
            DB::table('cart_payment')->whereIn('cart_id', $cartIds)->delete();
        }

        if (Schema::hasTable('guest_cart_tokens')) {
            DB::table('guest_cart_tokens')->whereIn('cart_id', $cartIds)->delete();
        }

        $addressIds = collect();

        if (Schema::hasTable('addresses') && Schema::hasColumn('addresses', 'cart_id')) {
            $addressIds = DB::table('addresses')->whereIn('cart_id', $cartIds)->pluck('id');
        }

        if ($addressIds->isNotEmpty() && Schema::hasTable('cart_shipping_rates') && Schema::hasColumn('cart_shipping_rates', 'cart_address_id')) {
            DB::table('cart_shipping_rates')->whereIn('cart_address_id', $addressIds)->delete();
        }

        if ($addressIds->isNotEmpty()) {
            DB::table('addresses')->whereIn('id', $addressIds)->delete();
        }

        DB::table('cart')->whereIn('id', $cartIds)->delete();
    }

    private function deleteCustomerTokens(): void
    {
        if (! Schema::hasTable('personal_access_tokens')) {
            return;
        }

        $this->customerTokenQuery()->delete();
    }

    private function customerTokenQuery()
    {
        return DB::table('personal_access_tokens')
            ->where('tokenable_type', 'like', '%Customer%');
    }

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

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

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

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

            if ($table === 'gift_cards') {
                $query->where($column, '!=', 0);
            }

            $query->update([$column => null]);
        }
    }

    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');
        }
    }
}