<?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_import_row_products', function (Blueprint $table) {
            $table->id();
            $table->foreignId('row_id')->constrained('recon_import_rows')->cascadeOnDelete();
            $table->foreignId('product_id')->constrained('products');
            $table->unsignedInteger('qty');
            $table->unsignedBigInteger('amount');
            $table->timestamps();
            $table->index(['product_id', 'row_id']);
        });

        // Backfill: each already-mapped row becomes exactly one item row,
        // qty/amount unsplit (matches today's behaviour exactly).
        $now = now();
        DB::table('recon_import_rows')
            ->whereNotNull('product_id')
            ->select('id', 'product_id', 'qty', 'amount')
            ->orderBy('id')
            ->chunkById(500, function ($rows) use ($now) {
                DB::table('recon_import_row_products')->insert(
                    $rows->map(fn ($r) => [
                        'row_id' => $r->id,
                        'product_id' => $r->product_id,
                        'qty' => $r->qty,
                        'amount' => $r->amount,
                        'created_at' => $now,
                        'updated_at' => $now,
                    ])->all()
                );
            });

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

    public function down(): void
    {
        Schema::table('recon_import_rows', function (Blueprint $table) {
            $table->foreignId('product_id')->nullable()->after('excluded')->constrained('products')->nullOnDelete();
            $table->index(['product_id', 'sale_date']);
        });

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

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