<?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
    {
        if (! Schema::hasTable('dberp_purchase_requests')) {
            Schema::create('dberp_purchase_requests', function (Blueprint $table): void {
                $table->id();
                $table->string('code', 64)->comment('采购申请号');
                $table->dateTime('request_time')->comment('申请时间');
                $table->string('source_type', 32)->default('manual')->comment('来源类型');
                $table->unsignedBigInteger('source_id')->nullable()->comment('来源对象 ID');
                $table->string('source_reference_no', 64)->nullable()->comment('来源编号');
                $table->json('source_snapshot')->nullable()->comment('来源快照');
                $table->string('department_name', 120)->nullable()->comment('申请部门');
                $table->unsignedBigInteger('applicant_employee_id')->nullable()->comment('申请人');
                $table->unsignedBigInteger('suggested_supplier_id')->nullable()->comment('建议供应商');
                $table->string('urgency_level', 32)->default('normal')->comment('紧急程度');
                $table->date('expected_arrival_date')->nullable()->comment('期望到货日期');
                $table->string('request_reason', 255)->nullable()->comment('申请原因');
                $table->string('status', 32)->default('draft')->comment('申请状态');
                $table->decimal('total_quantity', 18, 4)->default(0)->comment('申请总数量');
                $table->decimal('converted_quantity', 18, 4)->default(0)->comment('累计已转采购数量');
                $table->decimal('remaining_quantity', 18, 4)->default(0)->comment('剩余可转采购数量');
                $table->dateTime('submitted_at')->nullable()->comment('提交时间');
                $table->dateTime('last_converted_at')->nullable()->comment('最近转采购时间');
                $table->dateTime('closed_at')->nullable()->comment('关闭时间');
                $table->dateTime('voided_at')->nullable()->comment('作废时间');
                $table->text('remark')->nullable()->comment('备注');
                $table->timestamps();

                $table->unique('code', 'uq_dberp_purchase_request_code');
                $table->index(['status', 'request_time'], 'idx_dberp_preq_status_time');
                $table->index(['applicant_employee_id', 'status'], 'idx_dberp_preq_applicant_st');
                $table->index(['source_type', 'source_id'], 'idx_dberp_preq_source');
                $table->index(['suggested_supplier_id', 'status'], 'idx_dberp_preq_supplier_st');
                $table->foreign('applicant_employee_id', 'fk_dberp_preq_applicant')->references('id')->on('dberp_employees')->nullOnDelete();
                $table->foreign('suggested_supplier_id', 'fk_dberp_preq_supplier')->references('id')->on('dberp_suppliers')->nullOnDelete();
            });
        }

        if (! Schema::hasTable('dberp_purchase_request_items')) {
            Schema::create('dberp_purchase_request_items', function (Blueprint $table): void {
                $table->id();
                $table->unsignedBigInteger('request_id');
                $table->unsignedBigInteger('product_id');
                $table->unsignedBigInteger('warehouse_id')->nullable();
                $table->string('source_type', 32)->default('manual')->comment('来源类型');
                $table->unsignedBigInteger('source_id')->nullable()->comment('来源对象 ID');
                $table->string('source_reference_no', 64)->nullable()->comment('来源编号');
                $table->json('source_snapshot')->nullable()->comment('来源快照');
                $table->decimal('request_quantity', 18, 4)->default(0)->comment('申请数量');
                $table->decimal('converted_quantity', 18, 4)->default(0)->comment('累计已转采购数量');
                $table->decimal('remaining_quantity', 18, 4)->default(0)->comment('剩余可转采购数量');
                $table->unsignedInteger('sort_order')->default(0)->comment('越小越靠前');
                $table->text('remark')->nullable()->comment('备注');
                $table->timestamps();

                $table->foreign('request_id', 'fk_dberp_preq_item_req')->references('id')->on('dberp_purchase_requests')->cascadeOnDelete();
                $table->foreign('product_id', 'fk_dberp_preq_item_prod')->references('id')->on('dberp_products')->restrictOnDelete();
                $table->foreign('warehouse_id', 'fk_dberp_preq_item_wh')->references('id')->on('dberp_warehouses')->nullOnDelete();
                $table->index(['request_id', 'sort_order'], 'idx_dberp_preq_item_req_sort');
                $table->index(['product_id', 'warehouse_id'], 'idx_dberp_preq_item_prod_wh');
                $table->index(['source_type', 'source_id'], 'idx_dberp_preq_item_source');
                $table->index(['remaining_quantity'], 'idx_dberp_preq_item_remaining');
            });
        }

        Schema::table('dberp_purchase_orders', function (Blueprint $table): void {
            if (! Schema::hasColumn('dberp_purchase_orders', 'purchase_request_id')) {
                $table->unsignedBigInteger('purchase_request_id')->nullable()->comment('来源采购申请');
                $table->foreign('purchase_request_id', 'fk_dberp_po_preq')->references('id')->on('dberp_purchase_requests')->nullOnDelete();
            }

            if (! Schema::hasColumn('dberp_purchase_orders', 'purchase_request_code')) {
                $table->string('purchase_request_code', 64)->nullable()->comment('来源采购申请号');
                $table->index(['purchase_request_code'], 'idx_dberp_po_preq_code');
            }
        });

        Schema::table('dberp_purchase_order_items', function (Blueprint $table): void {
            if (! Schema::hasColumn('dberp_purchase_order_items', 'purchase_request_item_id')) {
                $table->unsignedBigInteger('purchase_request_item_id')->nullable()->comment('来源采购申请明细');
                $table->foreign('purchase_request_item_id', 'fk_dberp_poi_preq_item')->references('id')->on('dberp_purchase_request_items')->nullOnDelete();
                $table->index(['purchase_request_item_id'], 'idx_dberp_poi_preq_item');
            }
        });
    }

    public function down(): void
    {
        Schema::table('dberp_purchase_order_items', function (Blueprint $table): void {
            if (Schema::hasColumn('dberp_purchase_order_items', 'purchase_request_item_id')) {
                $table->dropForeign('fk_dberp_poi_preq_item');
                $table->dropIndex('idx_dberp_poi_preq_item');
                $table->dropColumn('purchase_request_item_id');
            }
        });

        Schema::table('dberp_purchase_orders', function (Blueprint $table): void {
            if (Schema::hasColumn('dberp_purchase_orders', 'purchase_request_id')) {
                $table->dropForeign('fk_dberp_po_preq');
                $table->dropColumn('purchase_request_id');
            }

            if (Schema::hasColumn('dberp_purchase_orders', 'purchase_request_code')) {
                $table->dropIndex('idx_dberp_po_preq_code');
                $table->dropColumn('purchase_request_code');
            }
        });

        Schema::dropIfExists('dberp_purchase_request_items');
        Schema::dropIfExists('dberp_purchase_requests');
    }
};
