<?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('inventory_stock_movements', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained('tenants')->cascadeOnDelete();
            $table->foreignId('warehouse_id')->constrained('inventory_warehouses')->restrictOnDelete();
            $table->foreignId('item_id')->constrained('inventory_items')->restrictOnDelete();
            $table->foreignId('warehouse_section_id')->nullable()->constrained('inventory_warehouse_sections')->nullOnDelete();
            $table->string('movement_type', 32);
            $table->decimal('quantity', 24, 8);
            $table->foreignId('unit_id')->nullable()->constrained('inventory_units')->nullOnDelete();
            $table->timestamp('occurred_at');
            $table->timestamp('recorded_at')->useCurrent();
            $table->foreignId('actor_user_id')->nullable()->constrained('users')->nullOnDelete();
            $table->uuid('correlation_id')->nullable();
            $table->string('idempotency_key', 191)->nullable();
            $table->string('idempotency_source', 64)->default('default');
            $table->string('reference_type', 80)->nullable();
            $table->unsignedBigInteger('reference_id')->nullable();
            $table->json('metadata')->nullable();
            $table->foreignId('reverses_movement_id')->nullable()->constrained('inventory_stock_movements')->nullOnDelete();
            $table->timestamp('created_at')->useCurrent();

            $table->index(['tenant_id', 'warehouse_id', 'item_id', 'recorded_at', 'id'], 'inv_stk_mov_tnt_wh_item_seq');
            $table->index(['tenant_id', 'idempotency_key', 'idempotency_source'], 'inv_stk_mov_idem_lookup');
        });

        Schema::create('inventory_stock_projections', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained('tenants')->cascadeOnDelete();
            $table->foreignId('warehouse_id')->constrained('inventory_warehouses')->cascadeOnDelete();
            $table->foreignId('item_id')->constrained('inventory_items')->cascadeOnDelete();
            $table->decimal('on_hand_quantity', 24, 8)->default(0);
            $table->decimal('reserved_quantity', 24, 8)->default(0);
            $table->decimal('available_quantity', 24, 8)->default(0);
            $table->unsignedInteger('row_version')->default(0);
            $table->unsignedBigInteger('last_movement_id')->nullable();
            $table->timestamp('last_rebuilt_at')->nullable();
            $table->timestamps();

            $table->unique(['tenant_id', 'warehouse_id', 'item_id'], 'inv_stk_proj_tnt_wh_item_uq');
            $table->index(['tenant_id', 'warehouse_id'], 'inv_stk_proj_tnt_wh');
        });

        Schema::create('inventory_stock_idempotency_keys', function (Blueprint $table) {
            $table->id();
            $table->foreignId('tenant_id')->constrained('tenants')->cascadeOnDelete();
            $table->string('idempotency_key', 191);
            $table->string('idempotency_source', 64)->default('default');
            $table->foreignId('first_movement_id')->constrained('inventory_stock_movements')->cascadeOnDelete();
            $table->json('result_payload');
            $table->timestamp('created_at')->useCurrent();

            $table->unique(['tenant_id', 'idempotency_key', 'idempotency_source'], 'inv_stk_idem_uq');
            $table->index(['tenant_id', 'created_at'], 'inv_stk_idem_tnt_created');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('inventory_stock_idempotency_keys');
        Schema::dropIfExists('inventory_stock_projections');
        Schema::dropIfExists('inventory_stock_movements');
    }
};
