parfum/app/Console/Commands/Migrations/MigrateBottlesFromOldData.php

<?php

namespace App\Console\Commands\Migrations;

use App\Models\Bottle;
use App\Models\Outlet;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;

class MigrateBottlesFromOldData extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'migrate:bottles {--force : Force migration without confirmation} {--recreate : Delete existing bottles and recreate}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Migrate bottles data from old database to new structure';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        if ($this->option('recreate')) {
            if (! $this->confirm('Are you sure you want to DELETE ALL EXISTING BOTTLES and recreate them? This action cannot be undone.')) {
                $this->info('Migration cancelled.');

                return;
            }

            $this->warn('Deleting all existing bottles...');
            Bottle::query()->delete(); // This will cascade delete outlet relations
            $this->info('All bottles deleted.');
        } elseif (! $this->option('force') && ! $this->confirm('Are you sure you want to migrate bottles data? This will add new bottles to your database.')) {
            $this->info('Migration cancelled.');

            return;
        }

        $this->info('Starting bottles migration...');

        try {
            $oldBottles = DB::connection('old_database')->table('bottles')->whereNull('deleted_at')->get();

            $this->info("Found {$oldBottles->count()} bottles to migrate.");
            $bar = $this->output->createProgressBar($oldBottles->count());

            $migrated = 0;
            $skipped = 0;

            foreach ($oldBottles as $oldBottle) {
                $existingBottle = Bottle::where('name', $oldBottle->name)->first();

                if (! $this->option('recreate') && $existingBottle) {
                    $this->warn("Bottle '{$oldBottle->name}' already exists, skipping...");
                    $skipped++;
                    $bar->advance();

                    continue;
                }

                // Extract size from name if possible (e.g., "Botol 50ml" -> 50)
                $size = $this->extractSizeFromName($oldBottle->name);

                $bottle = Bottle::create([
                    'name' => $oldBottle->name,
                    'slug' => Str::slug($oldBottle->name),
                    'size' => $size,
                    'cost_price' => $oldBottle->buying_price,
                    'sale_price' => $oldBottle->selling_price,
                    'description' => null,
                    'views' => 0,
                    'created_at' => $oldBottle->created_at,
                    'updated_at' => $oldBottle->updated_at,
                ]);

                // Create outlet relations from bottle_stocks
                $this->createBottleOutletRelations($bottle->id, $oldBottle->id);

                $migrated++;
                $bar->advance();
            }

            $bar->finish();
            $this->newLine(2);

            $this->info('✅ Bottles migration completed!');
            $this->info("📊 Migrated: {$migrated} bottles");
            $this->info("⏭️  Skipped: {$skipped} bottles (already exist)");
        } catch (\Exception $e) {
            $this->error("❌ Migration failed: {$e->getMessage()}");

            return 1;
        }

        return 0;
    }

    private function extractSizeFromName(string $name): int
    {
        // Try to extract size from name (e.g., "Botol 50ml" -> 50)
        preg_match('/(\d+)/', $name, $matches);

        return $matches ? (int) $matches[0] : 100; // Default to 100ml if not found
    }

    private function createBottleOutletRelations(int $newBottleId, int $oldBottleId): void
    {
        try {
            // Get bottle stocks from old database
            $bottleStocks = DB::connection('old_database')
                ->table('bottle_stocks')
                ->where('bottle_id', $oldBottleId)
                ->get();

            foreach ($bottleStocks as $stock) {
                // Map old store_id to new outlet_id
                $outletMapping = $this->getOutletMapping();
                $outletId = $outletMapping[$stock->store_id] ?? null;

                if ($outletId) {
                    // Create many-to-many relation with stock amount
                    DB::table('bottle_outlet')->insert([
                        'bottle_id' => $newBottleId,
                        'outlet_id' => $outletId,
                        'stock' => $stock->amount,
                        'created_at' => now(),
                        'updated_at' => now(),
                    ]);
                }
            }
        } catch (\Exception $e) {
            $this->warn("Could not create outlet relations for bottle ID {$newBottleId}: {$e->getMessage()}");
        }
    }

    private function getOutletMapping(): array
    {
        // Map old store_id to new outlet_id
        // This assumes outlets have been migrated first
        $outlets = Outlet::select('id')->orderBy('id')->get();

        $mapping = [];
        foreach ($outlets as $index => $outlet) {
            $mapping[$index + 1] = $outlet->id; // Old store_id starts from 1
        }

        return $mapping;
    }
}