<?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_rules', function (Blueprint $table): void {
            $table->id();
            $table->unsignedBigInteger('product_id');
            $table->unsignedBigInteger('warehouse_id');
            $table->boolean('manages_batch')->default(false)->comment('启用批次管理');
            $table->boolean('manages_serial_number')->default(false)->comment('启用序列号管理');
            $table->boolean('manages_expiry')->default(false)->comment('启用效期管理');
            $table->decimal('safety_stock', 18, 4)->default(0)->comment('安全库存');
            $table->decimal('reorder_point', 18, 4)->default(0)->comment('订货点');
            $table->decimal('replenish_target_quantity', 18, 4)->default(0)->comment('补货目标量');
            $table->unsignedInteger('expiry_warning_days')->default(0)->comment('临期预警天数');
            $table->string('inventory_issue_policy', 32)->default('fifo')->comment('默认出库策略');
            $table->boolean('warning_enabled')->default(true)->comment('启用预警');
            $table->boolean('replenishment_enabled')->default(true)->comment('启用补货建议');
            $table->boolean('is_enabled')->default(true)->comment('启用状态');
            $table->unsignedInteger('sort_order')->default(0)->comment('越小越靠前');
            $table->string('remark', 255)->nullable()->comment('备注');
            $table->timestamps();

            $table->foreign('product_id', 'fk_dberp_inv_rule_prod')->references('id')->on('dberp_products')->restrictOnDelete();
            $table->foreign('warehouse_id', 'fk_dberp_inv_rule_wh')->references('id')->on('dberp_warehouses')->restrictOnDelete();
            $table->unique(['product_id', 'warehouse_id'], 'uq_dberp_inv_rule_prod_wh');
            $table->index(['warning_enabled', 'is_enabled'], 'idx_dberp_inv_rule_warn');
            $table->index(['replenishment_enabled', 'is_enabled'], 'idx_dberp_inv_rule_repl');
            $table->index(['sort_order', 'id'], 'idx_dberp_inv_rule_sort');
        });

        Schema::create('dberp_inventory_batches', function (Blueprint $table): void {
            $table->id();
            $table->unsignedBigInteger('product_id');
            $table->unsignedBigInteger('warehouse_id');
            $table->string('batch_no', 64)->comment('批次号');
            $table->string('source_system', 32)->default('dberp')->comment('来源系统');
            $table->string('source_document_type', 120)->nullable()->comment('来源单据类型');
            $table->unsignedBigInteger('source_document_id')->nullable()->comment('来源单据ID');
            $table->string('source_document_no', 64)->nullable()->comment('来源单据号');
            $table->string('external_reference_id', 128)->nullable()->comment('外部关联ID');
            $table->date('production_date')->nullable()->comment('生产日期');
            $table->date('expiry_date')->nullable()->comment('到期日期');
            $table->unsignedInteger('shelf_life_days')->default(0)->comment('保质期天数');
            $table->decimal('realtime_quantity', 18, 4)->default(0)->comment('当前数量');
            $table->decimal('available_quantity', 18, 4)->default(0)->comment('可用数量');
            $table->decimal('locked_quantity', 18, 4)->default(0)->comment('锁定数量');
            $table->string('status', 32)->default('active')->comment('批次状态');
            $table->boolean('is_enabled')->default(true)->comment('启用状态');
            $table->unsignedInteger('sort_order')->default(0)->comment('越小越靠前');
            $table->text('remark')->nullable()->comment('备注');
            $table->timestamps();

            $table->foreign('product_id', 'fk_dberp_inv_batch_prod')->references('id')->on('dberp_products')->restrictOnDelete();
            $table->foreign('warehouse_id', 'fk_dberp_inv_batch_wh')->references('id')->on('dberp_warehouses')->restrictOnDelete();
            $table->unique(['product_id', 'warehouse_id', 'batch_no'], 'uq_dberp_inv_batch_prod_wh_no');
            $table->index(['warehouse_id', 'status'], 'idx_dberp_inv_batch_wh_status');
            $table->index(['product_id', 'expiry_date'], 'idx_dberp_inv_batch_prod_exp');
            $table->index(['available_quantity', 'status'], 'idx_dberp_inv_batch_qty_status');
            $table->index(['sort_order', 'id'], 'idx_dberp_inv_batch_sort');
        });

        Schema::create('dberp_inventory_serial_numbers', function (Blueprint $table): void {
            $table->id();
            $table->unsignedBigInteger('product_id');
            $table->unsignedBigInteger('warehouse_id')->nullable();
            $table->unsignedBigInteger('batch_id')->nullable();
            $table->string('serial_no', 128)->comment('序列号');
            $table->string('source_system', 32)->default('dberp')->comment('来源系统');
            $table->string('source_document_type', 120)->nullable()->comment('来源单据类型');
            $table->unsignedBigInteger('source_document_id')->nullable()->comment('来源单据ID');
            $table->string('source_document_no', 64)->nullable()->comment('来源单据号');
            $table->string('external_reference_id', 128)->nullable()->comment('外部关联ID');
            $table->string('current_owner_type', 120)->nullable()->comment('当前归属类型');
            $table->unsignedBigInteger('current_owner_id')->nullable()->comment('当前归属ID');
            $table->timestamp('inbound_at')->nullable()->comment('入库时间');
            $table->timestamp('outbound_at')->nullable()->comment('出库时间');
            $table->string('status', 32)->default('in_stock')->comment('序列号状态');
            $table->boolean('is_enabled')->default(true)->comment('启用状态');
            $table->unsignedInteger('sort_order')->default(0)->comment('越小越靠前');
            $table->text('remark')->nullable()->comment('备注');
            $table->timestamps();

            $table->foreign('product_id', 'fk_dberp_inv_serial_prod')->references('id')->on('dberp_products')->restrictOnDelete();
            $table->foreign('warehouse_id', 'fk_dberp_inv_serial_wh')->references('id')->on('dberp_warehouses')->nullOnDelete();
            $table->foreign('batch_id', 'fk_dberp_inv_serial_batch')->references('id')->on('dberp_inventory_batches')->nullOnDelete();
            $table->unique('serial_no', 'uq_dberp_inv_serial_no');
            $table->index(['product_id', 'status'], 'idx_dberp_inv_serial_prod_st');
            $table->index(['warehouse_id', 'status'], 'idx_dberp_inv_serial_wh_st');
            $table->index(['current_owner_type', 'current_owner_id'], 'idx_dberp_inv_serial_owner');
            $table->index(['sort_order', 'id'], 'idx_dberp_inv_serial_sort');
        });

        Schema::create('dberp_inventory_replenishment_suggestions', function (Blueprint $table): void {
            $table->id();
            $table->unsignedBigInteger('product_id');
            $table->unsignedBigInteger('warehouse_id');
            $table->decimal('system_suggested_quantity', 18, 4)->default(0)->comment('系统建议数量');
            $table->decimal('adjusted_quantity', 18, 4)->default(0)->comment('调整后数量');
            $table->string('trigger_reason', 255)->comment('触发原因');
            $table->string('status', 32)->default('pending')->comment('建议状态');
            $table->timestamp('generated_at')->nullable()->comment('生成时间');
            $table->timestamp('processed_at')->nullable()->comment('处理时间');
            $table->json('metric_snapshot')->nullable()->comment('指标快照');
            $table->string('remark', 255)->nullable()->comment('备注');
            $table->timestamps();

            $table->foreign('product_id', 'fk_dberp_repl_prod')->references('id')->on('dberp_products')->restrictOnDelete();
            $table->foreign('warehouse_id', 'fk_dberp_repl_wh')->references('id')->on('dberp_warehouses')->restrictOnDelete();
            $table->index(['product_id', 'warehouse_id', 'status'], 'idx_dberp_repl_prod_wh_st');
            $table->index(['generated_at', 'status'], 'idx_dberp_repl_gen_st');
            $table->index(['status', 'processed_at'], 'idx_dberp_repl_proc_st');
        });

        Schema::create('dberp_inventory_batch_movements', function (Blueprint $table): void {
            $table->id();
            $table->unsignedBigInteger('batch_id')->nullable();
            $table->unsignedBigInteger('product_id');
            $table->unsignedBigInteger('warehouse_id');
            $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->dateTime('business_time')->comment('业务时间');
            $table->text('remark')->nullable()->comment('备注');
            $table->timestamps();

            $table->foreign('batch_id', 'fk_dberp_batch_move_batch')->references('id')->on('dberp_inventory_batches')->nullOnDelete();
            $table->foreign('product_id', 'fk_dberp_batch_move_prod')->references('id')->on('dberp_products')->restrictOnDelete();
            $table->foreign('warehouse_id', 'fk_dberp_batch_move_wh')->references('id')->on('dberp_warehouses')->restrictOnDelete();
            $table->index(['product_id', 'warehouse_id', 'business_time'], 'idx_dberp_batch_move_prod_wh');
            $table->index(['business_type', 'business_time'], 'idx_dberp_batch_move_type_time');
            $table->index(['source_type', 'source_id'], 'idx_dberp_batch_move_source');
        });

        Schema::create('dberp_inventory_serial_number_logs', function (Blueprint $table): void {
            $table->id();
            $table->unsignedBigInteger('serial_number_id');
            $table->unsignedBigInteger('product_id');
            $table->unsignedBigInteger('warehouse_id')->nullable();
            $table->unsignedBigInteger('batch_id')->nullable();
            $table->string('previous_status', 32)->nullable()->comment('变更前状态');
            $table->string('current_status', 32)->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->dateTime('business_time')->comment('业务时间');
            $table->json('context_snapshot')->nullable()->comment('上下文快照');
            $table->text('remark')->nullable()->comment('备注');
            $table->timestamps();

            $table->foreign('serial_number_id', 'fk_dberp_serial_log_sn')->references('id')->on('dberp_inventory_serial_numbers')->cascadeOnDelete();
            $table->foreign('product_id', 'fk_dberp_serial_log_prod')->references('id')->on('dberp_products')->restrictOnDelete();
            $table->foreign('warehouse_id', 'fk_dberp_serial_log_wh')->references('id')->on('dberp_warehouses')->nullOnDelete();
            $table->foreign('batch_id', 'fk_dberp_serial_log_batch')->references('id')->on('dberp_inventory_batches')->nullOnDelete();
            $table->index(['serial_number_id', 'business_time'], 'idx_dberp_serial_log_sn_time');
            $table->index(['product_id', 'warehouse_id', 'business_time'], 'idx_dberp_serial_log_prod_wh');
            $table->index(['source_type', 'source_id'], 'idx_dberp_serial_log_source');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('dberp_inventory_serial_number_logs');
        Schema::dropIfExists('dberp_inventory_batch_movements');
        Schema::dropIfExists('dberp_inventory_replenishment_suggestions');
        Schema::dropIfExists('dberp_inventory_serial_numbers');
        Schema::dropIfExists('dberp_inventory_batches');
        Schema::dropIfExists('dberp_inventory_rules');
    }
};
