<?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_orders')) {
            Schema::create('dberp_purchase_orders', function (Blueprint $table) {
                $table->id();
                $table->string('code', 64)->comment('采购订单号');
                $table->foreignId('supplier_id')->constrained('dberp_suppliers')->restrictOnDelete();
                $table->string('supplier_contact_person_snapshot', 120)->nullable()->comment('供应商联系人快照');
                $table->string('supplier_mobile_snapshot', 32)->nullable()->comment('供应商手机快照');
                $table->string('supplier_phone_snapshot', 32)->nullable()->comment('供应商电话快照');
                $table->string('supplier_country_code_snapshot', 32)->nullable()->comment('供应商国家快照');
                $table->string('supplier_region_name_snapshot', 255)->nullable()->comment('供应商地区路径快照');
                $table->string('supplier_address_snapshot', 255)->nullable()->comment('供应商详细地址快照');
                $table->foreignId('handler_employee_id')->nullable()->constrained('dberp_employees')->nullOnDelete();
                $table->string('status', 32)->default('draft')->comment('订单状态');
                $table->dateTime('business_time')->comment('业务日期');
                $table->date('expected_arrival_date')->nullable()->comment('预计到货日期');
                $table->date('last_arrival_date')->nullable()->comment('最后到货日期');
                $table->decimal('total_quantity', 18, 4)->default(0)->comment('订单总数量');
                $table->decimal('total_arrived_quantity', 18, 4)->default(0)->comment('累计到货数量');
                $table->decimal('total_rejected_quantity', 18, 4)->default(0)->comment('累计拒收数量');
                $table->decimal('remaining_arrival_quantity', 18, 4)->default(0)->comment('剩余未到数量');
                $table->string('arrival_progress_status', 32)->nullable()->comment('到货进度状态');
                $table->decimal('total_amount', 18, 4)->default(0)->comment('订单总金额');
                $table->decimal('payable_amount', 18, 4)->default(0)->comment('应付金额');
                $table->decimal('paid_amount', 18, 4)->default(0)->comment('已付金额');
                $table->decimal('unpaid_amount', 18, 4)->default(0)->comment('未付金额');
                $table->string('payable_status', 32)->default('unpaid')->comment('应付状态');
                $table->dateTime('confirmed_at')->nullable()->comment('确认时间');
                $table->dateTime('closed_at')->nullable()->comment('关闭时间');
                $table->dateTime('voided_at')->nullable()->comment('作废时间');
                $table->text('remark')->nullable()->comment('备注');
                $table->string('source_system', 32)->nullable()->comment('来源系统');
                $table->string('external_reference_id', 128)->nullable()->comment('外部关联 ID');
                $table->timestamps();

                $table->unique('code', 'uq_dberp_purchase_order_code');
                $table->index(['expected_arrival_date', 'status'], 'idx_dberp_po_expected_status');
                $table->index(['remaining_arrival_quantity', 'status'], 'idx_dberp_po_remaining_status');
                $table->index(['source_system'], 'idx_dberp_po_source_system');
                $table->index(['external_reference_id'], 'idx_dberp_po_external_ref');
                $table->index(['supplier_id', 'status', 'business_time'], 'idx_dberp_po_supplier_status_time');
                $table->index(['supplier_id', 'payable_status', 'business_time'], 'idx_dpo_payable_status_time');
                $table->index(['handler_employee_id', 'status'], 'idx_dberp_po_handler_status');
            });
        }

        if (! Schema::hasTable('dberp_purchase_order_items')) {
            Schema::create('dberp_purchase_order_items', function (Blueprint $table) {
                $table->id();
                $table->foreignId('order_id')->constrained('dberp_purchase_orders')->cascadeOnDelete();
                $table->foreignId('product_id')->constrained('dberp_products')->restrictOnDelete();
                $table->decimal('quantity', 18, 4)->comment('订购数量');
                $table->decimal('unit_price', 18, 4)->default(0)->comment('采购单价');
                $table->decimal('line_amount', 18, 4)->default(0)->comment('行金额');
                $table->decimal('arrived_quantity', 18, 4)->default(0)->comment('累计到货数量');
                $table->decimal('rejected_quantity', 18, 4)->default(0)->comment('累计拒收数量');
                $table->decimal('remaining_arrival_quantity', 18, 4)->default(0)->comment('剩余未到数量');
                $table->boolean('is_completed')->default(false)->comment('是否完成');
                $table->date('expected_arrival_date')->nullable()->comment('预计到货日期');
                $table->date('last_arrival_date')->nullable()->comment('最后到货日期');
                $table->string('execution_status', 32)->nullable()->comment('明细执行状态');
                $table->decimal('received_quantity', 18, 4)->default(0)->comment('累计入库数量');
                $table->decimal('returned_quantity', 18, 4)->default(0)->comment('累计退货数量');
                $table->unsignedInteger('sort_order')->default(0)->comment('越小越靠前');
                $table->text('remark')->nullable()->comment('备注');
                $table->string('external_product_id', 128)->nullable();
                $table->string('external_sku', 128)->nullable();
                $table->timestamps();

                $table->index(['order_id', 'sort_order'], 'idx_dberp_po_item_order_sort');
                $table->index(['execution_status', 'is_completed'], 'idx_dberp_po_item_exec_complete');
                $table->index(['expected_arrival_date', 'execution_status'], 'idx_dberp_po_item_expected_exec');
                $table->index(['external_product_id'], 'idx_dberp_poi_external_prod');
                $table->index(['external_sku'], 'idx_dberp_poi_external_sku');
                $table->index(['product_id'], 'idx_dberp_po_item_product');
            });
        }

        if (! Schema::hasTable('dberp_purchase_receipt_orders')) {
            Schema::create('dberp_purchase_receipt_orders', function (Blueprint $table) {
                $table->id();
                $table->string('code', 64)->comment('采购入库单号');
                $table->foreignId('purchase_order_id');
                $table->foreign('purchase_order_id', 'fk_dberp_pr_order_po')->references('id')->on('dberp_purchase_orders')->restrictOnDelete();
                $table->foreignId('supplier_id');
                $table->foreign('supplier_id', 'fk_dberp_pr_order_supplier')->references('id')->on('dberp_suppliers')->restrictOnDelete();
                $table->string('supplier_contact_person_snapshot', 120)->nullable()->comment('供应商联系人快照');
                $table->string('supplier_mobile_snapshot', 32)->nullable()->comment('供应商手机快照');
                $table->string('supplier_phone_snapshot', 32)->nullable()->comment('供应商电话快照');
                $table->string('supplier_country_code_snapshot', 32)->nullable()->comment('供应商国家快照');
                $table->string('supplier_region_name_snapshot', 255)->nullable()->comment('供应商地区路径快照');
                $table->string('supplier_address_snapshot', 255)->nullable()->comment('供应商详细地址快照');
                $table->foreignId('warehouse_id');
                $table->foreign('warehouse_id', 'fk_dberp_pr_order_wh')->references('id')->on('dberp_warehouses')->restrictOnDelete();
                $table->string('warehouse_contact_phone_snapshot', 32)->nullable()->comment('仓库联系电话快照');
                $table->string('warehouse_country_code_snapshot', 32)->nullable()->comment('仓库国家快照');
                $table->string('warehouse_region_name_snapshot', 255)->nullable()->comment('仓库地区路径快照');
                $table->string('warehouse_address_snapshot', 255)->nullable()->comment('仓库详细地址快照');
                $table->foreignId('handler_employee_id')->nullable();
                $table->foreign('handler_employee_id', 'fk_dberp_pr_order_handler')->references('id')->on('dberp_employees')->nullOnDelete();
                $table->string('status', 32)->default('draft')->comment('单据状态');
                $table->dateTime('business_time')->comment('业务日期');
                $table->date('arrival_date')->nullable()->comment('到货日期');
                $table->decimal('total_arrival_quantity', 18, 4)->default(0)->comment('本次到货数量');
                $table->decimal('total_received_quantity', 18, 4)->default(0)->comment('累计入库数量');
                $table->decimal('total_amount', 18, 4)->default(0)->comment('单据金额');
                $table->string('delivery_note', 255)->nullable()->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_purchase_receipt_code');
                $table->index(['arrival_date', 'status'], 'idx_dberp_pr_arrival_status');
                $table->index(['purchase_order_id', 'status', 'business_time'], 'idx_dberp_pr_order_status_time');
                $table->index(['supplier_id', 'status', 'business_time'], 'idx_dberp_pr_supplier_status_time');
                $table->index(['warehouse_id', 'status'], 'idx_dberp_pr_wh_status');
            });
        }

        if (! Schema::hasTable('dberp_purchase_receipt_order_items')) {
            Schema::create('dberp_purchase_receipt_order_items', function (Blueprint $table) {
                $table->id();
                $table->foreignId('order_id');
                $table->foreign('order_id', 'fk_dberp_pr_item_order')->references('id')->on('dberp_purchase_receipt_orders')->cascadeOnDelete();
                $table->foreignId('purchase_order_item_id');
                $table->foreign('purchase_order_item_id', 'fk_dberp_pr_item_po')->references('id')->on('dberp_purchase_order_items')->restrictOnDelete();
                $table->foreignId('product_id');
                $table->foreign('product_id', 'fk_dberp_pr_item_product')->references('id')->on('dberp_products')->restrictOnDelete();
                $table->decimal('arrival_quantity', 18, 4)->default(0)->comment('到货数量');
                $table->decimal('received_quantity', 18, 4)->default(0)->comment('入库数量');
                $table->decimal('unit_price', 18, 4)->default(0)->comment('采购单价');
                $table->decimal('line_amount', 18, 4)->default(0)->comment('行金额');
                $table->decimal('returned_quantity', 18, 4)->default(0)->comment('累计退货数量');
                $table->unsignedInteger('sort_order')->default(0)->comment('越小越靠前');
                $table->text('remark')->nullable()->comment('备注');
                $table->timestamps();

                $table->index(['order_id', 'sort_order'], 'idx_dberp_pr_item_order_sort');
                $table->index(['purchase_order_item_id'], 'idx_dberp_pr_item_po_item');
                $table->index(['product_id'], 'idx_dberp_pr_item_product');
            });
        }

        if (! Schema::hasTable('dberp_purchase_return_orders')) {
            Schema::create('dberp_purchase_return_orders', function (Blueprint $table) {
                $table->id();
                $table->string('code', 64)->comment('采购退货单号');
                $table->foreignId('purchase_order_id')->nullable();
                $table->foreign('purchase_order_id', 'fk_dberp_preturn_po')->references('id')->on('dberp_purchase_orders')->nullOnDelete();
                $table->foreignId('purchase_receipt_order_id')->nullable();
                $table->foreign('purchase_receipt_order_id', 'fk_dberp_preturn_pr')->references('id')->on('dberp_purchase_receipt_orders')->nullOnDelete();
                $table->foreignId('supplier_id');
                $table->foreign('supplier_id', 'fk_dberp_preturn_supplier')->references('id')->on('dberp_suppliers')->restrictOnDelete();
                $table->foreignId('warehouse_id');
                $table->foreign('warehouse_id', 'fk_dberp_preturn_wh')->references('id')->on('dberp_warehouses')->restrictOnDelete();
                $table->foreignId('handler_employee_id')->nullable();
                $table->foreign('handler_employee_id', 'fk_dberp_preturn_handler')->references('id')->on('dberp_employees')->nullOnDelete();
                $table->string('status', 32)->default('draft')->comment('单据状态');
                $table->dateTime('business_time')->comment('业务日期');
                $table->decimal('total_return_quantity', 18, 4)->default(0)->comment('退货总数量');
                $table->decimal('total_amount', 18, 4)->default(0)->comment('退货总金额');
                $table->string('reason', 255)->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_purchase_return_code');
                $table->index(['purchase_order_id', 'status', 'business_time'], 'idx_dberp_preturn_order_status_time');
                $table->index(['purchase_receipt_order_id', 'status'], 'idx_dberp_preturn_receipt_status');
                $table->index(['supplier_id', 'status', 'business_time'], 'idx_dberp_preturn_supplier_status_time');
            });
        }

        if (! Schema::hasTable('dberp_purchase_return_order_items')) {
            Schema::create('dberp_purchase_return_order_items', function (Blueprint $table) {
                $table->id();
                $table->foreignId('order_id');
                $table->foreign('order_id', 'fk_dberp_preturn_item_order')->references('id')->on('dberp_purchase_return_orders')->cascadeOnDelete();
                $table->foreignId('purchase_order_item_id');
                $table->foreign('purchase_order_item_id', 'fk_dberp_preturn_item_po')->references('id')->on('dberp_purchase_order_items')->restrictOnDelete();
                $table->foreignId('purchase_receipt_item_id')->nullable();
                $table->foreign('purchase_receipt_item_id', 'fk_dberp_preturn_item_pr')->references('id')->on('dberp_purchase_receipt_order_items')->nullOnDelete();
                $table->foreignId('product_id');
                $table->foreign('product_id', 'fk_dberp_preturn_item_product')->references('id')->on('dberp_products')->restrictOnDelete();
                $table->decimal('return_quantity', 18, 4)->default(0)->comment('退货数量');
                $table->decimal('unit_price', 18, 4)->default(0)->comment('退货单价');
                $table->decimal('return_amount', 18, 4)->default(0)->comment('退货金额');
                $table->unsignedInteger('sort_order')->default(0)->comment('越小越靠前');
                $table->text('remark')->nullable()->comment('备注');
                $table->timestamps();

                $table->index(['order_id', 'sort_order'], 'idx_dberp_preturn_item_order_sort');
                $table->index(['purchase_order_item_id'], 'idx_dberp_preturn_item_po_item');
                $table->index(['purchase_receipt_item_id'], 'idx_dberp_preturn_item_pr_item');
                $table->index(['product_id'], 'idx_dberp_preturn_item_product');
            });
        }
    }

    public function down(): void
    {
        Schema::dropIfExists('dberp_purchase_return_order_items');
        Schema::dropIfExists('dberp_purchase_return_orders');
        Schema::dropIfExists('dberp_purchase_receipt_order_items');
        Schema::dropIfExists('dberp_purchase_receipt_orders');
        Schema::dropIfExists('dberp_purchase_order_items');
        Schema::dropIfExists('dberp_purchase_orders');
    }
};
