<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('recon_product_mapping_components', function (Blueprint $table) {
            $table->id();
            $table->foreignId('mapping_id')->constrained('recon_product_mappings')->cascadeOnDelete();
            $table->foreignId('product_id')->constrained('products');
            $table->decimal('qty_multiplier', 8, 2)->default(1);
            $table->timestamps();
            $table->unique(['mapping_id', 'product_id']);
        });

        // Backfill: every existing mapping's scalar product_id becomes one
        // component row with qty_multiplier = 1, before the column is dropped.
        $now = now();
        $components = DB::table('recon_product_mappings')
            ->select('id as mapping_id', 'product_id')
            ->get()
            ->map(fn ($m) => [
                'mapping_id' => $m->mapping_id,
                'product_id' => $m->product_id,
                'qty_multiplier' => 1,
                'created_at' => $now,
                'updated_at' => $now,
            ])->all();

        if (!empty($components)) {
            DB::table('recon_product_mapping_components')->insert($components);
        }

        Schema::table('recon_product_mappings', function (Blueprint $table) {
            $table->dropForeign(['product_id']);
            $table->dropColumn('product_id');
        });
    }

    public function down(): void
    {
        // Nullable (not NOT NULL like the original) since doctrine/dbal isn't
        // installed, so a later ->change() call isn't available.
        Schema::table('recon_product_mappings', function (Blueprint $table) {
            $table->foreignId('product_id')->nullable()->after('external_label')->constrained('products');
        });

        DB::table('recon_product_mapping_components')
            ->select('mapping_id', DB::raw('MIN(product_id) as product_id'))
            ->groupBy('mapping_id')
            ->get()
            ->each(fn ($row) => DB::table('recon_product_mappings')
                ->where('id', $row->mapping_id)
                ->update(['product_id' => $row->product_id]));

        Schema::dropIfExists('recon_product_mapping_components');
    }
};
