<?php

use App\Domains\Catalog\Enums\InventoryPolicy;
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
    {
        $inventoryIds = [];

        DB::table('productos_variantes')->orderBy('id')->each(
            function (object $variant) use (&$inventoryIds): void {
                $inventoryIds[$variant->id] = DB::table('inventories')->insertGetId([
                    'sold_units' => $variant->cantidad_vendida,
                    'reserved_stock' => $variant->stock_reservado,
                    'real_stock' => $variant->stock_real,
                ]);
            }
        );

        DB::table('productos')->orderBy('id')->each(
            function (object $product): void {
                $variants = DB::table('productos_variantes')
                    ->where('producto_id', $product->id)
                    ->orderBy('id');
                $firstVariant = (clone $variants)->first();
                $inventoryId = null;

                if ($firstVariant === null) {
                    $inventoryId = DB::table('inventories')->insertGetId([
                        'sold_units' => 0,
                        'reserved_stock' => 0,
                        'real_stock' => 0,
                    ]);
                }

                DB::table('catalog_items')->insert([
                    'id' => $product->id,
                    'tenant_code' => $product->tenant_codigo,
                    'category_id' => $product->categoria_id,
                    'brand_id' => $product->brand_id,
                    'inventory_id' => $inventoryId,
                    'slug' => $product->slug,
                    'nombre' => $product->nombre,
                    'descripcion' => $product->descripcion,
                    'precio' => $product->precio,
                    'inventory_policy' => $firstVariant->inventory_policy
                        ?? InventoryPolicy::Tracked->value,
                    'has_tickets' => $firstVariant->has_tickets ?? false,
                    'minimum_use_date' => $firstVariant->minimum_use_date ?? null,
                    'maximum_use_date' => $firstVariant->maximum_use_date ?? null,
                ]);
            }
        );

        Schema::table('productos_variantes', function (Blueprint $table): void {
            $table->dropForeign(['producto_id']);
            $table->foreignId('inventory_id')->nullable()->after('producto_id');
        });

        foreach ($inventoryIds as $variantId => $inventoryId) {
            DB::table('productos_variantes')->where('id', $variantId)->update([
                'inventory_id' => $inventoryId,
            ]);
        }

        Schema::table('productos_variantes', function (Blueprint $table): void {
            $table->renameColumn('producto_id', 'catalog_item_id');
            $table->dropColumn([
                'inventory_policy',
                'stock_real',
                'stock_reservado',
                'cantidad_vendida',
                'is_placeholder',
                'has_tickets',
                'minimum_use_date',
                'maximum_use_date',
                'created_at',
                'updated_at',
            ]);
        });

        Schema::rename('productos_variantes', 'variantes');

        Schema::table('variantes', function (Blueprint $table): void {
            $table->unsignedBigInteger('inventory_id')->nullable(false)->change();
            $table->foreign('catalog_item_id')->references('id')->on('catalog_items')->cascadeOnDelete();
            $table->foreign('inventory_id')->references('id')->on('inventories')->restrictOnDelete();
            $table->unique('inventory_id');
        });

        Schema::table('products_attributes', function (Blueprint $table): void {
            $table->dropForeign(['product_id']);
            $table->renameColumn('product_id', 'catalog_item_id');
        });
        Schema::rename('products_attributes', 'item_attributes');
        Schema::table('item_attributes', function (Blueprint $table): void {
            $table->foreign('catalog_item_id')->references('id')->on('catalog_items')->cascadeOnDelete();
        });

        Schema::table('productos_variantes_values', function (Blueprint $table): void {
            $table->dropForeign('productos_variantes_definiciones_producto_variante_id_foreign');
            $table->dropForeign(['products_attribute_id']);
            $table->dropUnique('prod_var_values_variant_product_attr_unique');
            $table->renameColumn('producto_variante_id', 'variant_id');
            $table->renameColumn('products_attribute_id', 'item_attribute_id');
        });
        Schema::rename('productos_variantes_values', 'variant_values');
        Schema::table('variant_values', function (Blueprint $table): void {
            $table->foreign('variant_id')->references('id')->on('variantes')->cascadeOnDelete();
            $table->foreign('item_attribute_id')->references('id')->on('item_attributes')->cascadeOnDelete();
            $table->unique(['variant_id', 'item_attribute_id']);
        });

        Schema::table('productos_attachments', function (Blueprint $table): void {
            $table->dropForeign(['producto_id']);
            $table->dropUnique('productos_attachments_producto_id_attachment_id_unique');
            $table->renameColumn('producto_id', 'catalog_item_id');
        });
        Schema::rename('productos_attachments', 'catalog_items_attachments');
        Schema::table('catalog_items_attachments', function (Blueprint $table): void {
            $table->foreignId('variant_id')
                ->nullable()
                ->after('id')
                ->constrained('variantes')
                ->cascadeOnDelete();
            $table->unsignedInteger('orden')->default(0)->after('attachment_id');
            $table->foreign('catalog_item_id')->references('id')->on('catalog_items')->cascadeOnDelete();
        });

        $catalogAttachmentOrder = [];
        DB::table('catalog_items_attachments')
            ->orderBy('catalog_item_id')
            ->orderBy('id')
            ->each(function (object $catalogAttachment) use (&$catalogAttachmentOrder): void {
                $catalogItemId = $catalogAttachment->catalog_item_id;
                $order = $catalogAttachmentOrder[$catalogItemId] ?? 0;

                DB::table('catalog_items_attachments')
                    ->where('id', $catalogAttachment->id)
                    ->update(['orden' => $order]);

                $catalogAttachmentOrder[$catalogItemId] = $order + 1;
            });

        $variantAttachmentOrder = [];
        DB::table('variantes_attachments')->orderBy('id')->each(
            function (object $variantAttachment) use (&$variantAttachmentOrder): void {
                $variant = DB::table('variantes')
                    ->select('catalog_item_id')
                    ->where('id', $variantAttachment->variante_id)
                    ->first();

                if ($variant === null) {
                    return;
                }

                DB::table('catalog_items_attachments')->insert([
                    'variant_id' => $variantAttachment->variante_id,
                    'catalog_item_id' => $variant->catalog_item_id,
                    'attachment_id' => $variantAttachment->attachment_id,
                    'orden' => $variantAttachmentOrder[$variantAttachment->variante_id] ?? 0,
                    'created_at' => $variantAttachment->created_at,
                    'updated_at' => $variantAttachment->updated_at,
                ]);

                $variantAttachmentOrder[$variantAttachment->variante_id] =
                    ($variantAttachmentOrder[$variantAttachment->variante_id] ?? 0) + 1;
            }
        );

        Schema::drop('variantes_attachments');

        Schema::table('catalog_items_attachments', function (Blueprint $table): void {
            $table->dropColumn(['created_at', 'updated_at']);
            $table->unique(
                ['catalog_item_id', 'variant_id', 'attachment_id'],
                'catalog_item_variant_attachment_unique'
            );
        });

        Schema::drop('productos');
    }

    public function down(): void
    {
        throw new RuntimeException('This destructive catalog migration cannot be reversed.');
    }
};
