nshop/app/Console/Commands/ResetOrdersCommand.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 sales orders so `orders:migrate-asteria` can start clean.
 *
 * Keeps customers, products, carts, and customer groups. Invoices, shipments,
 * refunds, payments, and order addresses are removed. Gift-card / reward /
 * booking rows keep their snapshots; `order_id` is set to NULL.
 *
 * Usage
 * ─────
 * php artisan orders:reset --dry-run
 * php artisan orders:reset --force
 */
class ResetOrdersCommand extends Command
{
    protected $signature = 'orders:reset
        {--dry-run : Count rows only, do not write}
        {--force : Skip the confirmation prompt}';

    protected $description = 'Truncate existing orders so a fresh order migration can run';

    private const PROGRESS_KEY = 'migrate_asteria_orders_last_id';

    /**
     * Sales tables truncated in child-first order.
     *
     * @var list<string>
     */
    private const ORDER_TABLES = [
        'refund_items',
        'refunds',
        'invoice_items',
        'invoices',
        'shipment_items',
        'shipments',
        'order_comments',
        'order_transactions',
        'order_payment',
        'downloadable_product_download_links',
        'downloadable_link_purchased',
        'order_items',
        'notifications',
        'product_ordered_inventories',
        'orders',
    ];

    /**
     * Rows that keep their snapshot but must not block deleting orders.
     *
     * @var list<array{0: string, 1: string}>
     */
    private const DETACH_ORDER_ID = [
        ['bookings', 'order_id'],
        ['bookings', 'order_item_id'],
        ['gift_card_usage_logs', 'order_id'],
        ['mw_growth_value_history', 'order_id'],
        ['mw_reward_point_history', 'history_order_id'],
        ['member_log', 'order_id'],
        ['payment_attempts', 'order_id'],
    ];

    /** @var list<string> */
    private const ORDER_ADDRESS_TYPES = [
        'order_billing',
        'order_shipping',
        'invoice_billing',
        'invoice_shipping',
    ];

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

        $this->warn('This clears sales orders so they can be re-imported.');
        $this->line('Kept: customers, products, carts, customer groups.');
        $this->line('Removed: orders, items, payments, invoices, shipments, refunds, order addresses.');
        $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("Order rows that would be truncated: {$total}");

        $addressCount = $this->countOrderAddresses();
        if ($addressCount > 0) {
            $this->comment("addresses (order/invoice): {$addressCount}");
        }

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

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

            return self::SUCCESS;
        }

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

            return self::SUCCESS;
        }

        $driver = DB::getDriverName();

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

            $this->deleteOrderAddresses();
            $this->detachHistoricalOrderIds();

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

        Cache::forget(self::PROGRESS_KEY);

        $this->newLine();
        $this->info('Order reset finished. Next:');
        $this->line('  php artisan orders:migrate-asteria --reset-progress');

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

            $count = (int) DB::table($table)->whereNotNull($column)->where($column, '!=', 0)->count();

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

        return $counts;
    }

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

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

    private function deleteOrderAddresses(): void
    {
        if (! Schema::hasTable('addresses')) {
            return;
        }

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

    private function orderAddressQuery()
    {
        $query = DB::table('addresses');

        return $query->where(function ($builder) {
            if (Schema::hasColumn('addresses', 'order_id')) {
                $builder->orWhereNotNull('order_id');
            }

            if (Schema::hasColumn('addresses', 'address_type')) {
                $builder->orWhereIn('address_type', self::ORDER_ADDRESS_TYPES);
            }
        });
    }

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

            DB::table($table)->whereNotNull($column)->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');
        }
    }
}