<?php

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

/**
 * Replaces the fixed brand/model/color/size/weight/dimensions/material columns
 * with free-form rows in `product_attributes` — any product that already has a
 * value in one of these columns keeps it, just reshaped into a key/value pair,
 * before the columns themselves are dropped.
 */
return new class extends Migration
{
    private const LEGACY_COLUMNS = [
        'brand' => 'Brand',
        'model' => 'Model',
        'color' => 'Color',
        'size' => 'Size',
        'weight' => 'Weight',
        'dimensions' => 'Dimensions',
        'material' => 'Material',
    ];

    public function up(): void
    {
        $now = now();

        DB::table('products')
            ->select(array_merge(['id'], array_keys(self::LEGACY_COLUMNS)))
            ->orderBy('id')
            ->chunkById(500, function ($products) use ($now) {
                $rows = [];
                foreach ($products as $product) {
                    $order = 0;
                    foreach (self::LEGACY_COLUMNS as $column => $label) {
                        $value = $product->{$column} ?? null;
                        if ($value === null || $value === '') {
                            continue;
                        }
                        $rows[] = [
                            'product_id' => $product->id,
                            'key' => $label,
                            'value' => $value,
                            'sort_order' => $order++,
                            'created_at' => $now,
                            'updated_at' => $now,
                        ];
                    }
                }
                if ($rows !== []) {
                    DB::table('product_attributes')->insert($rows);
                }
            });

        Schema::table('products', function (Blueprint $table) {
            $table->dropColumn(array_keys(self::LEGACY_COLUMNS));
        });
    }

    public function down(): void
    {
        Schema::table('products', function (Blueprint $table) {
            foreach (array_keys(self::LEGACY_COLUMNS) as $column) {
                $length = in_array($column, ['dimensions', 'brand', 'model'], true) ? 100 : 50;
                $table->string($column, $length)->nullable();
            }
        });

        foreach (self::LEGACY_COLUMNS as $column => $label) {
            $rows = DB::table('product_attributes')->where('key', $label)->get(['id', 'product_id', 'value']);
            foreach ($rows as $row) {
                DB::table('products')->where('id', $row->product_id)->update([$column => $row->value]);
            }
            DB::table('product_attributes')->where('key', $label)->delete();
        }
    }
};
