<?php

declare(strict_types=1);

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('dberp_inventory_balances', function (Blueprint $table) {
            $table->id();
            $table->foreignId('product_id')->constrained('dberp_products')->restrictOnDelete();
            $table->foreignId('warehouse_id')->constrained('dberp_warehouses')->restrictOnDelete();
            $table->decimal('realtime_quantity', 18, 4)->default(0)->comment('实时库存');
            $table->decimal('available_quantity', 18, 4)->default(0)->comment('可用库存');
            $table->decimal('occupied_quantity', 18, 4)->default(0)->comment('占用库存');
            $table->decimal('safety_stock', 18, 4)->default(0)->comment('安全库存');
            $table->dateTime('last_moved_at')->nullable()->comment('最近变动时间');
            $table->timestamps();

            $table->unique(['product_id', 'warehouse_id'], 'uq_dberp_inv_balance_prod_wh');
            $table->index(['warehouse_id', 'available_quantity'], 'idx_dberp_inv_balance_wh_available');
            $table->index(['product_id', 'available_quantity'], 'idx_dberp_inv_balance_prod_available');
            $table->index(['safety_stock', 'available_quantity'], 'idx_dberp_inv_balance_safe_available');
        });

        Schema::create('dberp_inventory_transactions', function (Blueprint $table) {
            $table->id();
            $table->foreignId('product_id')->constrained('dberp_products')->restrictOnDelete();
            $table->foreignId('warehouse_id')->constrained('dberp_warehouses')->restrictOnDelete();
            $table->string('direction', 16)->comment('变动方向');
            $table->decimal('change_quantity', 18, 4)->comment('变动数量');
            $table->decimal('before_quantity', 18, 4)->comment('变动前');
            $table->decimal('after_quantity', 18, 4)->comment('变动后');
            $table->string('business_type', 64)->comment('业务类型');
            $table->string('business_number', 64)->comment('业务单号');
            $table->string('source_type', 255)->nullable()->comment('来源对象类型');
            $table->unsignedBigInteger('source_id')->nullable()->comment('来源对象ID');
            $table->foreignId('operator_user_id')->nullable()->constrained('users')->nullOnDelete();
            $table->dateTime('business_time')->comment('业务时间');
            $table->text('remark')->nullable()->comment('备注');
            $table->string('idempotency_key', 191)->nullable()->comment('幂等键');
            $table->timestamps();

            $table->index(['warehouse_id', 'business_time'], 'idx_dberp_inv_tx_wh_time');
            $table->index(['product_id', 'business_time'], 'idx_dberp_inv_tx_prod_time');
            $table->index(['product_id', 'warehouse_id', 'business_time'], 'idx_dberp_inv_tx_prod_wh_time');
            $table->index(['business_type', 'business_time'], 'idx_dberp_inv_tx_type_time');
            $table->index(['business_number'], 'idx_dberp_inv_tx_biz_no');
            $table->index(['source_type', 'source_id'], 'idx_dberp_inv_tx_source');
            $table->unique('idempotency_key', 'uq_dberp_inventory_transactions_idempotency_key');
        });

        Schema::create('dberp_other_stock_in_orders', function (Blueprint $table) {
            $table->id();
            $table->string('code', 64)->comment('入库单号');
            $table->foreignId('warehouse_id')->constrained('dberp_warehouses')->restrictOnDelete();
            $table->string('reason', 255)->comment('入库原因');
            $table->string('status', 32)->default('draft')->comment('单据状态');
            $table->dateTime('business_time')->comment('业务时间');
            $table->dateTime('effective_at')->nullable()->comment('生效时间');
            $table->dateTime('voided_at')->nullable()->comment('作废时间');
            $table->text('remark')->nullable()->comment('备注');
            $table->timestamps();

            $table->unique('code', 'uq_dberp_other_in_code');
            $table->index(['warehouse_id', 'status', 'business_time'], 'idx_dberp_other_in_wh_status_time');
        });

        Schema::create('dberp_other_stock_in_order_items', function (Blueprint $table) {
            $table->id();
            $table->foreignId('order_id')->constrained('dberp_other_stock_in_orders')->cascadeOnDelete();
            $table->foreignId('product_id')->constrained('dberp_products')->restrictOnDelete();
            $table->decimal('quantity', 18, 4)->comment('入库数量');
            $table->unsignedInteger('sort_order')->default(0)->comment('越小越靠前');
            $table->text('remark')->nullable()->comment('备注');
            $table->timestamps();

            $table->index(['order_id', 'sort_order'], 'idx_dberp_other_in_item_order_sort');
            $table->index(['product_id'], 'idx_dberp_other_in_item_product');
        });

        Schema::create('dberp_other_stock_out_orders', function (Blueprint $table) {
            $table->id();
            $table->string('code', 64)->comment('出库单号');
            $table->foreignId('warehouse_id')->constrained('dberp_warehouses')->restrictOnDelete();
            $table->string('reason', 255)->comment('出库原因');
            $table->string('status', 32)->default('draft')->comment('单据状态');
            $table->dateTime('business_time')->comment('业务时间');
            $table->dateTime('effective_at')->nullable()->comment('生效时间');
            $table->dateTime('voided_at')->nullable()->comment('作废时间');
            $table->text('remark')->nullable()->comment('备注');
            $table->timestamps();

            $table->unique('code', 'uq_dberp_other_out_code');
            $table->index(['warehouse_id', 'status', 'business_time'], 'idx_dberp_other_out_wh_status_time');
        });

        Schema::create('dberp_other_stock_out_order_items', function (Blueprint $table) {
            $table->id();
            $table->foreignId('order_id')->constrained('dberp_other_stock_out_orders')->cascadeOnDelete();
            $table->foreignId('product_id')->constrained('dberp_products')->restrictOnDelete();
            $table->decimal('quantity', 18, 4)->comment('出库数量');
            $table->unsignedInteger('sort_order')->default(0)->comment('越小越靠前');
            $table->text('remark')->nullable()->comment('备注');
            $table->timestamps();

            $table->index(['order_id', 'sort_order'], 'idx_dberp_other_out_item_order_sort');
            $table->index(['product_id'], 'idx_dberp_other_out_item_product');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('dberp_other_stock_out_order_items');
        Schema::dropIfExists('dberp_other_stock_out_orders');
        Schema::dropIfExists('dberp_other_stock_in_order_items');
        Schema::dropIfExists('dberp_other_stock_in_orders');
        Schema::dropIfExists('dberp_inventory_transactions');
        Schema::dropIfExists('dberp_inventory_balances');
    }
};
