<?php

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

/**
 * Removes the product variant layer entirely. Pricing is now a property of the
 * product alone: products.sale_price / products.purchase_price are the single
 * source of truth, editable on the product form and refreshed by GRN confirm.
 *
 * `product_variant_id` never carried a DB-level FK on any child table, so there
 * is nothing to detach — the columns simply go. The two composite uniques that
 * included it are reverted to their pre-variant shape, with a defensive merge
 * first so the migration is safe even if variant rows exist.
 *
 * Irreversible by design: down() restores the columns and table but cannot
 * recover which variant a historical row pointed at.
 */
return new class extends Migration
{
    /**
     * Tables carrying a nullable `product_variant_id` plus an index on it.
     *
     * @var array<int, string>
     */
    private array $indexedTables = [
        'stock_movements', 'grn_items', 'stock_adjustments', 'stock_transfers',
        'invoice_items', 'return_items', 'damages', 'stock_batches', 'product_serials',
    ];

    /**
     * Tables carrying the column with no index on it.
     *
     * @var array<int, string>
     */
    private array $bareTables = [
        'purchase_order_items', 'bundle_items',
    ];

    public function up(): void
    {
        $this->collapseBranchStock();
        $this->collapseProductUnits();

        foreach ($this->indexedTables as $table) {
            if (! Schema::hasColumn($table, 'product_variant_id')) {
                continue;
            }

            Schema::table($table, function (Blueprint $t) {
                $t->dropIndex(['product_variant_id']);
            });

            Schema::table($table, function (Blueprint $t) {
                $t->dropColumn('product_variant_id');
            });
        }

        foreach ($this->bareTables as $table) {
            if (Schema::hasColumn($table, 'product_variant_id')) {
                Schema::table($table, function (Blueprint $t) {
                    $t->dropColumn('product_variant_id');
                });
            }
        }

        Schema::dropIfExists('product_variants');

        if (Schema::hasColumn('products', 'has_variants')) {
            Schema::table('products', function (Blueprint $table) {
                $table->dropColumn('has_variants');
            });
        }
    }

    /**
     * One stock row per (branch, product). Variant rows are summed into the
     * lowest-id row for that pair before the unique key is narrowed, otherwise
     * a product with N variants would collide on index creation.
     */
    private function collapseBranchStock(): void
    {
        if (! Schema::hasColumn('branch_stock', 'product_variant_id')) {
            return;
        }

        $groups = DB::table('branch_stock')
            ->selectRaw('branch_id, product_id, MIN(id) as keep_id, SUM(quantity) as total')
            ->groupBy('branch_id', 'product_id')
            ->havingRaw('COUNT(*) > 1')
            ->get();

        foreach ($groups as $group) {
            DB::table('branch_stock')->where('id', $group->keep_id)
                ->update(['quantity' => $group->total]);

            DB::table('branch_stock')
                ->where('branch_id', $group->branch_id)
                ->where('product_id', $group->product_id)
                ->where('id', '!=', $group->keep_id)
                ->delete();
        }

        // Restore the narrow unique before dropping the wide one: branch_id's FK
        // requires an index with branch_id leftmost at all times.
        Schema::table('branch_stock', function (Blueprint $table) {
            $table->unique(['branch_id', 'product_id']);
        });

        Schema::table('branch_stock', function (Blueprint $table) {
            $table->dropUnique('branch_stock_branch_product_variant_unique');
            $table->dropIndex(['product_variant_id']);
        });

        Schema::table('branch_stock', function (Blueprint $table) {
            $table->dropColumn('product_variant_id');
        });
    }

    /**
     * One unit row per (product, unit). Per-variant duplicates are discarded —
     * the lowest-id row wins, since conversion factors cannot be summed.
     */
    private function collapseProductUnits(): void
    {
        if (! Schema::hasColumn('product_units', 'product_variant_id')) {
            return;
        }

        $groups = DB::table('product_units')
            ->selectRaw('product_id, unit_id, MIN(id) as keep_id')
            ->groupBy('product_id', 'unit_id')
            ->havingRaw('COUNT(*) > 1')
            ->get();

        foreach ($groups as $group) {
            DB::table('product_units')
                ->where('product_id', $group->product_id)
                ->where('unit_id', $group->unit_id)
                ->where('id', '!=', $group->keep_id)
                ->delete();
        }

        // Restore the narrow unique before dropping the wide one: product_id's FK
        // requires an index with product_id leftmost at all times.
        Schema::table('product_units', function (Blueprint $table) {
            $table->unique(['product_id', 'unit_id'], 'product_units_product_unit_unique');
        });

        Schema::table('product_units', function (Blueprint $table) {
            $table->dropUnique('product_units_product_variant_unit_unique');
            $table->dropIndex(['product_variant_id']);
        });

        Schema::table('product_units', function (Blueprint $table) {
            $table->dropColumn('product_variant_id');
        });
    }

    public function down(): void
    {
        Schema::table('products', function (Blueprint $table) {
            $table->boolean('has_variants')->default(false)->after('base_unit_id');
        });

        Schema::create('product_variants', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('tenant_id')->nullable()->index();
            $table->ulid('uuid')->unique();
            $table->foreignId('product_id')->constrained('products')->cascadeOnDelete();
            $table->string('sku', 60);
            $table->string('barcode', 50)->nullable();
            $table->string('variant_label', 120);
            $table->string('color', 50)->nullable();
            $table->string('size', 50)->nullable();
            $table->decimal('sale_price', 12, 2)->nullable();
            $table->decimal('purchase_price', 12, 2)->nullable();
            $table->string('image_url', 500)->nullable();
            $table->boolean('is_active')->default(true);
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->foreignId('updated_by')->nullable()->constrained('users')->nullOnDelete();
            $table->foreignId('deleted_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();
            $table->softDeletes();

            $table->unique(['tenant_id', 'sku']);
            $table->unique(['tenant_id', 'barcode']);
            $table->index('product_id');
        });

        foreach (array_merge($this->indexedTables, ['branch_stock', 'product_units']) as $table) {
            Schema::table($table, function (Blueprint $t) {
                $t->unsignedBigInteger('product_variant_id')->nullable()->after('product_id');
            });

            Schema::table($table, function (Blueprint $t) {
                $t->index('product_variant_id');
            });
        }

        foreach ($this->bareTables as $table) {
            Schema::table($table, function (Blueprint $t) {
                $t->unsignedBigInteger('product_variant_id')->nullable()->after('product_id');
            });
        }

        Schema::table('branch_stock', function (Blueprint $table) {
            $table->unique(['branch_id', 'product_id', 'product_variant_id'], 'branch_stock_branch_product_variant_unique');
        });

        Schema::table('branch_stock', function (Blueprint $table) {
            $table->dropUnique(['branch_id', 'product_id']);
        });

        Schema::table('product_units', function (Blueprint $table) {
            $table->unique(['product_id', 'product_variant_id', 'unit_id'], 'product_units_product_variant_unit_unique');
            $table->dropUnique('product_units_product_unit_unique');
        });
    }
};
