<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('stock_batches', function (Blueprint $table) {
            $table->id();
            $table->ulid('uuid')->unique();
            $table->foreignId('branch_id')->constrained('warehouses')->cascadeOnUpdate()->restrictOnDelete();
            $table->foreignId('product_id')->constrained('products')->cascadeOnUpdate()->restrictOnDelete();
            // No FK on product_variant_id — matches established convention.
            $table->unsignedBigInteger('product_variant_id')->nullable();

            // Numbers — internal is system-generated (DocumentNumberService 'batch' kind).
            $table->string('internal_batch_number', 100)->unique();
            $table->string('supplier_batch_number', 100)->nullable();

            // Dates
            $table->date('manufacture_date')->nullable();
            $table->date('expiry_date')->nullable();

            // Quantities (maintained exclusively by BatchService, never written directly).
            $table->decimal('received_quantity', 12, 4);
            $table->decimal('available_quantity', 12, 4)->default(0);
            $table->decimal('reserved_quantity', 12, 4)->default(0);
            $table->decimal('damaged_quantity', 12, 4)->default(0);

            // Landed cost per base unit for this batch.
            $table->decimal('batch_cost', 12, 2)->nullable();

            // Status lookup (context = 'stock_batch'): active, expired, recalled, locked.
            $table->unsignedBigInteger('status_id')->nullable();

            $table->text('notes')->nullable();

            $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->index(['product_id', 'branch_id']);
            $table->index('expiry_date');
            $table->index('status_id');
            $table->index('product_variant_id');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('stock_batches');
    }
};
