<?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_stocktake_differences', function (Blueprint $table) {
            $table->id();
            $table->foreignId('stocktake_order_id')->constrained('dberp_stocktake_orders')->cascadeOnDelete();
            $table->foreignId('stocktake_order_item_id')->unique('uq_dberp_stocktake_diff_item')->constrained('dberp_stocktake_order_items')->cascadeOnDelete();
            $table->foreignId('inventory_transaction_id')->nullable()->constrained('dberp_inventory_transactions')->nullOnDelete();
            $table->foreignId('product_id')->constrained('dberp_products')->restrictOnDelete();
            $table->foreignId('warehouse_id')->constrained('dberp_warehouses')->restrictOnDelete();
            $table->decimal('book_quantity', 18, 4)->default(0)->comment('账面数量');
            $table->decimal('actual_quantity', 18, 4)->default(0)->comment('实盘数量');
            $table->decimal('difference_quantity', 18, 4)->default(0)->comment('差异数量');
            $table->string('result_type', 32)->default('balanced')->comment('盘点结果');
            $table->string('status', 32)->default('pending')->comment('差异处理状态');
            $table->string('reason_code', 64)->nullable()->comment('差异原因分类');
            $table->text('reason_detail')->nullable()->comment('差异原因说明');
            $table->string('resolution_type', 32)->nullable()->comment('处理结果');
            $table->text('resolution_remark')->nullable()->comment('处理备注');
            $table->foreignId('handled_by')->nullable()->constrained('users')->nullOnDelete();
            $table->dateTime('handled_at')->nullable()->comment('处理时间');
            $table->unsignedInteger('sort_order')->default(0)->comment('越小越靠前');
            $table->timestamps();

            $table->index(['stocktake_order_id', 'status'], 'idx_dberp_std_order_status');
            $table->index(['product_id', 'warehouse_id', 'status'], 'idx_dberp_std_prod_wh_st');
            $table->index(['result_type', 'status'], 'idx_dberp_std_result_st');
        });

        Schema::create('dberp_inventory_exceptions', function (Blueprint $table) {
            $table->id();
            $table->string('exception_key', 160)->comment('异常唯一键');
            $table->string('exception_type', 64)->comment('异常类型');
            $table->string('severity', 32)->default('warning')->comment('异常级别');
            $table->string('title', 120)->comment('异常标题');
            $table->string('description', 255)->nullable()->comment('异常说明');
            $table->foreignId('product_id')->nullable()->constrained('dberp_products')->nullOnDelete();
            $table->foreignId('warehouse_id')->nullable()->constrained('dberp_warehouses')->nullOnDelete();
            $table->foreignId('batch_id')->nullable()->constrained('dberp_inventory_batches')->nullOnDelete();
            $table->foreignId('serial_number_id')->nullable()->constrained('dberp_inventory_serial_numbers')->nullOnDelete();
            $table->foreignId('stocktake_difference_id')->nullable()->constrained('dberp_stocktake_differences')->nullOnDelete();
            $table->foreignId('replenishment_suggestion_id')->nullable()->constrained('dberp_inventory_replenishment_suggestions')->nullOnDelete();
            $table->string('status', 32)->default('open')->comment('异常状态');
            $table->dateTime('occurred_at')->comment('发现时间');
            $table->dateTime('resolved_at')->nullable()->comment('关闭时间');
            $table->foreignId('handled_by')->nullable()->constrained('users')->nullOnDelete();
            $table->text('handling_remark')->nullable()->comment('处理备注');
            $table->json('context_snapshot')->nullable()->comment('异常上下文');
            $table->unsignedInteger('sort_order')->default(0)->comment('越小越靠前');
            $table->timestamps();

            $table->unique('exception_key', 'uq_dberp_inv_ex_key');
            $table->index(['exception_type', 'status'], 'idx_dberp_inv_ex_tp_st');
            $table->index(['severity', 'status'], 'idx_dberp_inv_ex_sv_st');
            $table->index(['product_id', 'warehouse_id', 'status'], 'idx_dberp_inv_ex_pw_st');
        });
    }

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