<?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_inquiries')) {
            Schema::create('dberp_purchase_inquiries', function (Blueprint $table): void {
                $table->id();
                $table->string('code', 64)->comment('询价单号');
                $table->foreignId('purchase_request_id')->constrained('dberp_purchase_requests')->restrictOnDelete();
                $table->string('purchase_request_code', 64)->comment('采购申请号');
                $table->date('inquiry_date')->comment('询价日期');
                $table->foreignId('handler_employee_id')->nullable()->constrained('dberp_employees')->nullOnDelete();
                $table->string('status', 32)->default('draft')->comment('询价单状态');
                $table->text('remark')->nullable()->comment('备注');
                $table->timestamps();

                $table->unique('code', 'uq_dberp_purchase_inquiry_code');
                $table->index(['purchase_request_id', 'status'], 'idx_dpi_preq_status');
                $table->index(['handler_employee_id', 'status'], 'idx_dpi_handler_status');
                $table->index(['status', 'inquiry_date'], 'idx_dpi_status_date');
            });
        }

        if (! Schema::hasTable('dberp_purchase_inquiry_items')) {
            Schema::create('dberp_purchase_inquiry_items', function (Blueprint $table): void {
                $table->id();
                $table->foreignId('inquiry_id')->constrained('dberp_purchase_inquiries')->cascadeOnDelete();
                $table->foreignId('purchase_request_item_id')->nullable()->constrained('dberp_purchase_request_items')->nullOnDelete();
                $table->foreignId('product_id')->constrained('dberp_products')->restrictOnDelete();
                $table->foreignId('warehouse_id')->nullable()->constrained('dberp_warehouses')->nullOnDelete();
                $table->decimal('inquiry_quantity', 18, 4)->default(0)->comment('询价数量');
                $table->unsignedInteger('sort_order')->default(0)->comment('越小越靠前');
                $table->text('remark')->nullable()->comment('备注');
                $table->timestamps();

                $table->index(['inquiry_id', 'sort_order'], 'idx_dpii_inquiry_sort');
                $table->index(['purchase_request_item_id'], 'idx_dpii_preq_item');
                $table->index(['product_id', 'warehouse_id'], 'idx_dpii_prod_wh');
            });
        }

        if (! Schema::hasTable('dberp_purchase_inquiry_quotes')) {
            Schema::create('dberp_purchase_inquiry_quotes', function (Blueprint $table): void {
                $table->id();
                $table->string('code', 64)->comment('供应商报价号');
                $table->foreignId('purchase_inquiry_id')->constrained('dberp_purchase_inquiries')->cascadeOnDelete();
                $table->foreignId('purchase_inquiry_item_id')->constrained('dberp_purchase_inquiry_items')->cascadeOnDelete();
                $table->foreignId('purchase_request_id')->constrained('dberp_purchase_requests')->cascadeOnDelete();
                $table->string('purchase_request_code', 64)->comment('采购申请号');
                $table->foreignId('purchase_request_item_id')->constrained('dberp_purchase_request_items')->cascadeOnDelete();
                $table->foreignId('product_id')->constrained('dberp_products')->restrictOnDelete();
                $table->string('product_name_snapshot', 160)->nullable()->comment('商品名称快照');
                $table->string('product_spec_snapshot', 160)->nullable()->comment('规格型号快照');
                $table->foreignId('supplier_id')->constrained('dberp_suppliers')->restrictOnDelete();
                $table->string('supplier_name_snapshot', 160)->nullable()->comment('供应商名称快照');
                $table->date('quote_date')->comment('报价日期');
                $table->date('valid_until')->nullable()->comment('报价有效期');
                $table->decimal('unit_price', 18, 4)->default(0)->comment('报价单价');
                $table->decimal('min_order_quantity', 18, 4)->default(0)->comment('最小起订量');
                $table->unsignedInteger('lead_time_days')->nullable()->comment('交期天数');
                $table->decimal('tax_rate', 8, 4)->default(0)->comment('税率');
                $table->boolean('is_tax_included')->default(false)->comment('是否含税');
                $table->boolean('is_recommended')->default(false)->comment('是否建议成交');
                $table->string('status', 32)->default('active')->comment('报价状态');
                $table->dateTime('invalidated_at')->nullable()->comment('作废时间');
                $table->text('quote_note')->nullable()->comment('报价说明');
                $table->text('remark')->nullable()->comment('备注');
                $table->timestamps();

                $table->unique('code', 'uq_dberp_purchase_inquiry_quote_code');
                $table->index(['purchase_inquiry_item_id', 'status', 'is_recommended'], 'idx_dpiq_inquiry_item_status_rec');
                $table->index(['purchase_inquiry_item_id', 'supplier_id', 'status'], 'idx_dpiq_item_supplier_status');
                $table->index(['purchase_request_item_id', 'status', 'is_recommended'], 'idx_dpiq_preq_item_status_rec');
                $table->index(['product_id', 'supplier_id', 'quote_date'], 'idx_dpiq_product_supplier_date');
                $table->index(['status', 'valid_until'], 'idx_dpiq_status_valid_until');
                $table->index(['purchase_inquiry_id', 'status'], 'idx_dpiq_inquiry_status');
            });
        }

        if (! Schema::hasTable('dberp_purchase_conversion_lines')) {
            Schema::create('dberp_purchase_conversion_lines', function (Blueprint $table): void {
                $table->id();
                $table->string('source_type', 64)->comment('来源单据类型');
                $table->unsignedBigInteger('source_header_id')->comment('来源单据头 ID');
                $table->unsignedBigInteger('source_line_id')->comment('来源单据行 ID');
                $table->string('target_type', 64)->comment('目标单据类型');
                $table->unsignedBigInteger('target_header_id')->comment('目标单据头 ID');
                $table->unsignedBigInteger('target_line_id')->comment('目标单据行 ID');
                $table->unsignedBigInteger('purchase_request_id')->nullable();
                $table->unsignedBigInteger('purchase_request_item_id')->nullable();
                $table->unsignedBigInteger('purchase_inquiry_id')->nullable();
                $table->unsignedBigInteger('purchase_inquiry_item_id')->nullable();
                $table->unsignedBigInteger('purchase_inquiry_quote_id')->nullable();
                $table->unsignedBigInteger('purchase_order_id')->nullable();
                $table->unsignedBigInteger('purchase_order_item_id')->nullable();
                $table->unsignedBigInteger('supplier_id')->nullable();
                $table->unsignedBigInteger('product_id')->nullable();
                $table->unsignedBigInteger('unit_id')->nullable()->comment('计量单位');
                $table->decimal('quantity', 18, 4)->default(0)->comment('转换数量');
                $table->json('source_snapshot')->nullable()->comment('来源快照');
                $table->dateTime('converted_at')->nullable()->comment('转换时间');
                $table->dateTime('reversed_at')->nullable()->comment('冲回时间');
                $table->string('reverse_reason', 255)->nullable()->comment('冲回原因');
                $table->unsignedBigInteger('created_by')->nullable();
                $table->unsignedBigInteger('updated_by')->nullable();
                $table->timestamps();

                $table->foreign('purchase_request_id', 'fk_dpcl_preq')->references('id')->on('dberp_purchase_requests')->nullOnDelete();
                $table->foreign('purchase_request_item_id', 'fk_dpcl_preq_item')->references('id')->on('dberp_purchase_request_items')->nullOnDelete();
                $table->foreign('purchase_inquiry_id', 'fk_dpcl_inquiry')->references('id')->on('dberp_purchase_inquiries')->nullOnDelete();
                $table->foreign('purchase_inquiry_item_id', 'fk_dpcl_inquiry_item')->references('id')->on('dberp_purchase_inquiry_items')->nullOnDelete();
                $table->foreign('purchase_inquiry_quote_id', 'fk_dpcl_inquiry_quote')->references('id')->on('dberp_purchase_inquiry_quotes')->nullOnDelete();
                $table->foreign('purchase_order_id', 'fk_dpcl_po')->references('id')->on('dberp_purchase_orders')->nullOnDelete();
                $table->foreign('purchase_order_item_id', 'fk_dpcl_poi')->references('id')->on('dberp_purchase_order_items')->nullOnDelete();
                $table->foreign('supplier_id', 'fk_dpcl_supplier')->references('id')->on('dberp_suppliers')->nullOnDelete();
                $table->foreign('product_id', 'fk_dpcl_product')->references('id')->on('dberp_products')->nullOnDelete();
                $table->foreign('unit_id', 'fk_dpcl_unit')->references('id')->on('dberp_units')->nullOnDelete();
                $table->foreign('created_by', 'fk_dpcl_created_by')->references('id')->on('users')->nullOnDelete();
                $table->foreign('updated_by', 'fk_dpcl_updated_by')->references('id')->on('users')->nullOnDelete();
                $table->index(['source_type', 'source_header_id', 'source_line_id'], 'idx_dpcl_source_lookup');
                $table->index(['target_type', 'target_header_id', 'target_line_id'], 'idx_dpcl_target_lookup');
                $table->index(['purchase_request_id', 'converted_at'], 'idx_dpcl_preq_converted');
                $table->index(['purchase_request_item_id'], 'idx_dpcl_preq_item');
                $table->index(['purchase_inquiry_id'], 'idx_dpcl_inquiry');
                $table->index(['purchase_inquiry_item_id'], 'idx_dpcl_inquiry_item');
                $table->index(['purchase_inquiry_quote_id'], 'idx_dpcl_inquiry_quote');
                $table->index(['purchase_order_id'], 'idx_dpcl_po');
                $table->index(['purchase_order_item_id'], 'idx_dpcl_poi');
                $table->index(['supplier_id', 'product_id'], 'idx_dpcl_supplier_product');
                $table->index(['reversed_at'], 'idx_dpcl_reversed_at');
            });
        }

        if (! Schema::hasTable('dberp_purchase_price_reference_snapshots')) {
            Schema::create('dberp_purchase_price_reference_snapshots', function (Blueprint $table): void {
                $table->id();
                $table->string('scope_key', 96)->comment('快照范围键');
                $table->string('scope_type', 32)->comment('快照范围类型');
                $table->foreignId('product_id')->constrained('dberp_products')->restrictOnDelete();
                $table->foreignId('supplier_id')->nullable()->constrained('dberp_suppliers')->nullOnDelete();
                $table->decimal('latest_transaction_unit_price', 18, 4)->nullable()->comment('最近成交单价');
                $table->date('latest_transaction_date')->nullable()->comment('最近成交日期');
                $table->date('latest_receipt_date')->nullable()->comment('最近到货日期');
                $table->unsignedInteger('latest_lead_time_days')->nullable()->comment('最近交期天数');
                $table->unsignedBigInteger('latest_purchase_order_id')->nullable();
                $table->unsignedBigInteger('latest_purchase_order_item_id')->nullable();
                $table->dateTime('refreshed_at')->nullable()->comment('刷新时间');
                $table->timestamps();

                $table->unique('scope_key', 'uq_dberp_purchase_price_ref_scope');
                $table->index(['scope_type', 'product_id', 'supplier_id'], 'idx_dppref_scope_product_supplier');
                $table->index(['refreshed_at'], 'idx_dppref_refreshed_at');
                $table->foreign('latest_purchase_order_id', 'fk_dppref_latest_po')->references('id')->on('dberp_purchase_orders')->nullOnDelete();
                $table->foreign('latest_purchase_order_item_id', 'fk_dppref_latest_poi')->references('id')->on('dberp_purchase_order_items')->nullOnDelete();
            });
        }

        Schema::table('dberp_purchase_order_items', function (Blueprint $table): void {
            if (! Schema::hasColumn('dberp_purchase_order_items', 'source_inquiry_quote_id')) {
                $table->foreignId('source_inquiry_quote_id')->nullable()->after('purchase_request_item_id')->constrained('dberp_purchase_inquiry_quotes')->nullOnDelete();
            }

            if (! Schema::hasColumn('dberp_purchase_order_items', 'price_hit_result')) {
                $table->string('price_hit_result', 32)->nullable()->after('line_amount')->comment('价格命中结果');
            }

            if (! Schema::hasColumn('dberp_purchase_order_items', 'price_hit_label')) {
                $table->string('price_hit_label', 64)->nullable()->after('price_hit_result')->comment('价格提示标签');
            }

            if (! Schema::hasColumn('dberp_purchase_order_items', 'price_hit_summary')) {
                $table->string('price_hit_summary', 255)->nullable()->after('price_hit_label')->comment('价格提示摘要');
            }

            if (! Schema::hasColumn('dberp_purchase_order_items', 'matched_reference_price')) {
                $table->decimal('matched_reference_price', 18, 4)->nullable()->after('price_hit_summary')->comment('命中参考价');
            }

            if (! Schema::hasColumn('dberp_purchase_order_items', 'recent_supplier_transaction_unit_price')) {
                $table->decimal('recent_supplier_transaction_unit_price', 18, 4)->nullable()->after('matched_reference_price')->comment('供应商最近成交价');
            }

            if (! Schema::hasColumn('dberp_purchase_order_items', 'recent_product_transaction_unit_price')) {
                $table->decimal('recent_product_transaction_unit_price', 18, 4)->nullable()->after('recent_supplier_transaction_unit_price')->comment('商品最近采购价');
            }

            if (! Schema::hasColumn('dberp_purchase_order_items', 'lowest_inquiry_unit_price')) {
                $table->decimal('lowest_inquiry_unit_price', 18, 4)->nullable()->after('recent_product_transaction_unit_price')->comment('最近最低有效报价');
            }

            if (! Schema::hasColumn('dberp_purchase_order_items', 'recent_supplier_transaction_date')) {
                $table->date('recent_supplier_transaction_date')->nullable()->after('lowest_inquiry_unit_price')->comment('供应商最近成交日期');
            }

            if (! Schema::hasColumn('dberp_purchase_order_items', 'recent_product_transaction_date')) {
                $table->date('recent_product_transaction_date')->nullable()->after('recent_supplier_transaction_date')->comment('商品最近采购日期');
            }

            if (! Schema::hasColumn('dberp_purchase_order_items', 'recent_supplier_lead_time_days')) {
                $table->unsignedInteger('recent_supplier_lead_time_days')->nullable()->after('recent_product_transaction_date')->comment('供应商最近交期天数');
            }

            if (! Schema::hasColumn('dberp_purchase_order_items', 'price_reference_snapshot')) {
                $table->json('price_reference_snapshot')->nullable()->after('recent_supplier_lead_time_days')->comment('价格参考快照');
            }
        });
    }

    public function down(): void
    {
        if (Schema::hasTable('dberp_purchase_order_items')) {
            Schema::table('dberp_purchase_order_items', function (Blueprint $table): void {
                if (Schema::hasColumn('dberp_purchase_order_items', 'source_inquiry_quote_id')) {
                    $table->dropConstrainedForeignId('source_inquiry_quote_id');
                }

                $dropColumns = array_values(array_filter([
                    Schema::hasColumn('dberp_purchase_order_items', 'price_hit_result') ? 'price_hit_result' : null,
                    Schema::hasColumn('dberp_purchase_order_items', 'price_hit_label') ? 'price_hit_label' : null,
                    Schema::hasColumn('dberp_purchase_order_items', 'price_hit_summary') ? 'price_hit_summary' : null,
                    Schema::hasColumn('dberp_purchase_order_items', 'matched_reference_price') ? 'matched_reference_price' : null,
                    Schema::hasColumn('dberp_purchase_order_items', 'recent_supplier_transaction_unit_price') ? 'recent_supplier_transaction_unit_price' : null,
                    Schema::hasColumn('dberp_purchase_order_items', 'recent_product_transaction_unit_price') ? 'recent_product_transaction_unit_price' : null,
                    Schema::hasColumn('dberp_purchase_order_items', 'lowest_inquiry_unit_price') ? 'lowest_inquiry_unit_price' : null,
                    Schema::hasColumn('dberp_purchase_order_items', 'recent_supplier_transaction_date') ? 'recent_supplier_transaction_date' : null,
                    Schema::hasColumn('dberp_purchase_order_items', 'recent_product_transaction_date') ? 'recent_product_transaction_date' : null,
                    Schema::hasColumn('dberp_purchase_order_items', 'recent_supplier_lead_time_days') ? 'recent_supplier_lead_time_days' : null,
                    Schema::hasColumn('dberp_purchase_order_items', 'price_reference_snapshot') ? 'price_reference_snapshot' : null,
                ]));

                if ($dropColumns !== []) {
                    $table->dropColumn($dropColumns);
                }
            });
        }

        Schema::dropIfExists('dberp_purchase_price_reference_snapshots');
        Schema::dropIfExists('dberp_purchase_conversion_lines');
        Schema::dropIfExists('dberp_purchase_inquiry_quotes');
        Schema::dropIfExists('dberp_purchase_inquiry_items');
        Schema::dropIfExists('dberp_purchase_inquiries');
    }
};
