<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('stock_balances', function (Blueprint $table) {
            $table->id();
            $table->foreignId('item_id')->constrained('items')->restrictOnDelete();
            $table->foreignId('warehouse_id')->constrained('warehouses')->restrictOnDelete();
            $table->foreignId('bin_id')->constrained('warehouse_bins')->restrictOnDelete();
            $table->string('lot_no')->nullable();
            $table->decimal('on_hand_qty', 18, 6)->default(0);
            $table->decimal('reserved_qty', 18, 6)->default(0);
            $table->decimal('unit_cost_avg', 18, 4)->default(0);
            $table->timestamps();

            // Unique constraint as per requirements
            $table->unique(['item_id', 'warehouse_id', 'bin_id', 'lot_no'], 'stock_balances_unique');

            // Indexes for performance
            $table->index(['item_id', 'warehouse_id']);
            $table->index(['bin_id', 'lot_no']);
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('stock_balances');
    }
};
