<?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_receivable_records', function (Blueprint $table): void {
            $table->id();
            $table->string('code', 64)->comment('应收记录号');
            $table->foreignId('customer_id');
            $table->foreign('customer_id', 'fk_dberp_recv_record_customer')->references('id')->on('dberp_customers')->restrictOnDelete();
            $table->string('source_type', 64)->comment('来源类型');
            $table->unsignedBigInteger('source_id')->nullable()->comment('来源主键');
            $table->string('source_document_no', 64)->nullable()->comment('来源单号');
            $table->string('source_document_status', 32)->nullable()->comment('来源状态');
            $table->dateTime('business_date')->comment('业务日期');
            $table->date('due_date')->nullable()->comment('到期日期');
            $table->foreignId('handler_employee_id')->nullable();
            $table->foreign('handler_employee_id', 'fk_dberp_recv_record_handler')->references('id')->on('dberp_employees')->nullOnDelete();
            $table->string('source_system', 32)->nullable()->comment('来源系统');
            $table->string('external_document_no', 128)->nullable()->comment('外部单号');
            $table->string('external_reference_id', 128)->nullable()->comment('外部关联 ID');
            $table->decimal('original_amount', 18, 4)->default(0)->comment('原始金额');
            $table->decimal('adjusted_amount', 18, 4)->default(0)->comment('净额');
            $table->decimal('written_off_amount', 18, 4)->default(0)->comment('已核销金额');
            $table->decimal('remaining_amount', 18, 4)->default(0)->comment('未核销金额');
            $table->string('record_status', 32)->default('formed')->comment('记录状态');
            $table->dateTime('formed_at')->nullable()->comment('形成时间');
            $table->dateTime('last_write_off_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_recv_record_code');
            $table->unique(['source_type', 'source_id'], 'uq_dberp_recv_record_source');
            $table->index(['customer_id', 'record_status', 'business_date'], 'idx_dberp_recv_record_customer_status');
            $table->index(['customer_id', 'remaining_amount'], 'idx_dberp_recv_record_customer_remaining');
            $table->index(['due_date', 'record_status'], 'idx_dberp_recv_record_due_status');
            $table->index(['source_system', 'external_document_no'], 'idx_dberp_recv_record_external');
        });

        Schema::create('dberp_payable_records', function (Blueprint $table): void {
            $table->id();
            $table->string('code', 64)->comment('应付记录号');
            $table->foreignId('supplier_id');
            $table->foreign('supplier_id', 'fk_dberp_pay_record_supplier')->references('id')->on('dberp_suppliers')->restrictOnDelete();
            $table->string('source_type', 64)->comment('来源类型');
            $table->unsignedBigInteger('source_id')->nullable()->comment('来源主键');
            $table->string('source_document_no', 64)->nullable()->comment('来源单号');
            $table->string('source_document_status', 32)->nullable()->comment('来源状态');
            $table->dateTime('business_date')->comment('业务日期');
            $table->date('due_date')->nullable()->comment('到期日期');
            $table->foreignId('handler_employee_id')->nullable();
            $table->foreign('handler_employee_id', 'fk_dberp_pay_record_handler')->references('id')->on('dberp_employees')->nullOnDelete();
            $table->string('source_system', 32)->nullable()->comment('来源系统');
            $table->string('external_document_no', 128)->nullable()->comment('外部单号');
            $table->string('external_reference_id', 128)->nullable()->comment('外部关联 ID');
            $table->decimal('original_amount', 18, 4)->default(0)->comment('原始金额');
            $table->decimal('adjusted_amount', 18, 4)->default(0)->comment('净额');
            $table->decimal('written_off_amount', 18, 4)->default(0)->comment('已核销金额');
            $table->decimal('remaining_amount', 18, 4)->default(0)->comment('未核销金额');
            $table->string('record_status', 32)->default('formed')->comment('记录状态');
            $table->dateTime('formed_at')->nullable()->comment('形成时间');
            $table->dateTime('last_write_off_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_pay_record_code');
            $table->unique(['source_type', 'source_id'], 'uq_dberp_pay_record_source');
            $table->index(['supplier_id', 'record_status', 'business_date'], 'idx_dberp_pay_record_supplier_status');
            $table->index(['supplier_id', 'remaining_amount'], 'idx_dberp_pay_record_supplier_remaining');
            $table->index(['due_date', 'record_status'], 'idx_dberp_pay_record_due_status');
            $table->index(['source_system', 'external_document_no'], 'idx_dberp_pay_record_external');
        });

        Schema::create('dberp_settlement_write_offs', function (Blueprint $table): void {
            $table->id();
            $table->string('code', 64)->comment('核销单号');
            $table->string('settlement_side', 16)->comment('核销方向');
            $table->string('counterparty_type', 16)->comment('往来对象类型');
            $table->unsignedBigInteger('counterparty_id')->comment('往来对象主键');
            $table->decimal('total_amount', 18, 4)->default(0)->comment('核销总额');
            $table->decimal('reversed_amount', 18, 4)->default(0)->comment('已反核销金额');
            $table->decimal('effective_amount', 18, 4)->default(0)->comment('有效金额');
            $table->string('write_off_status', 32)->default('confirmed')->comment('核销状态');
            $table->dateTime('business_date')->comment('业务时间');
            $table->foreignId('handler_employee_id')->nullable();
            $table->foreign('handler_employee_id', 'fk_dberp_writeoff_handler')->references('id')->on('dberp_employees')->nullOnDelete();
            $table->string('source_system', 32)->nullable()->comment('来源系统');
            $table->string('external_document_no', 128)->nullable()->comment('外部单号');
            $table->string('external_reference_id', 128)->nullable()->comment('外部关联 ID');
            $table->dateTime('confirmed_at')->nullable()->comment('确认时间');
            $table->dateTime('reversed_at')->nullable()->comment('反核销时间');
            $table->dateTime('voided_at')->nullable()->comment('作废时间');
            $table->text('remark')->nullable()->comment('备注');
            $table->timestamps();

            $table->unique('code', 'uq_dberp_writeoff_code');
            $table->index(['settlement_side', 'counterparty_type', 'counterparty_id', 'business_date'], 'idx_dberp_writeoff_side_counterparty');
            $table->index(['write_off_status', 'business_date'], 'idx_dberp_writeoff_status_time');
        });

        Schema::create('dberp_settlement_write_off_lines', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('write_off_id');
            $table->foreign('write_off_id', 'fk_dberp_writeoff_line_head')->references('id')->on('dberp_settlement_write_offs')->cascadeOnDelete();
            $table->unsignedInteger('line_no')->default(1)->comment('行号');
            $table->string('settlement_side', 16)->comment('核销方向');
            $table->foreignId('receipt_order_id')->nullable();
            $table->foreign('receipt_order_id', 'fk_dberp_writeoff_line_receipt')->references('id')->on('dberp_receipt_orders')->restrictOnDelete();
            $table->foreignId('payment_order_id')->nullable();
            $table->foreign('payment_order_id', 'fk_dberp_writeoff_line_payment')->references('id')->on('dberp_payment_orders')->restrictOnDelete();
            $table->foreignId('receivable_record_id')->nullable();
            $table->foreign('receivable_record_id', 'fk_dberp_writeoff_line_recv_record')->references('id')->on('dberp_receivable_records')->restrictOnDelete();
            $table->foreignId('payable_record_id')->nullable();
            $table->foreign('payable_record_id', 'fk_dberp_writeoff_line_pay_record')->references('id')->on('dberp_payable_records')->restrictOnDelete();
            $table->decimal('write_off_amount', 18, 4)->default(0)->comment('核销金额');
            $table->decimal('reversed_amount', 18, 4)->default(0)->comment('已反核销金额');
            $table->decimal('effective_amount', 18, 4)->default(0)->comment('有效金额');
            $table->string('line_status', 32)->default('confirmed')->comment('行状态');
            $table->dateTime('business_date')->comment('业务时间');
            $table->dateTime('reversed_at')->nullable()->comment('反核销时间');
            $table->text('remark')->nullable()->comment('备注');
            $table->timestamps();

            $table->unique(['write_off_id', 'line_no'], 'uq_dberp_writeoff_line_no');
            $table->index(['receipt_order_id', 'line_status'], 'idx_dberp_writeoff_line_receipt');
            $table->index(['payment_order_id', 'line_status'], 'idx_dberp_writeoff_line_payment');
            $table->index(['receivable_record_id', 'line_status'], 'idx_dberp_writeoff_line_recv_record');
            $table->index(['payable_record_id', 'line_status'], 'idx_dberp_writeoff_line_pay_record');
        });

        Schema::create('dberp_advance_accounts', function (Blueprint $table): void {
            $table->id();
            $table->string('counterparty_type', 16)->comment('往来对象类型');
            $table->unsignedBigInteger('counterparty_id')->comment('往来对象主键');
            $table->string('advance_direction', 16)->comment('余额方向');
            $table->string('currency_code', 8)->default('CNY')->comment('币种');
            $table->decimal('total_amount', 18, 4)->default(0)->comment('累计金额');
            $table->decimal('used_amount', 18, 4)->default(0)->comment('已使用金额');
            $table->decimal('occupied_amount', 18, 4)->default(0)->comment('已占用金额');
            $table->decimal('available_amount', 18, 4)->default(0)->comment('可用金额');
            $table->string('account_status', 32)->default('active')->comment('账户状态');
            $table->dateTime('last_business_time')->nullable()->comment('最近业务时间');
            $table->string('source_system', 32)->nullable()->comment('来源系统');
            $table->string('external_reference_id', 128)->nullable()->comment('外部关联 ID');
            $table->timestamps();

            $table->unique(['counterparty_type', 'counterparty_id', 'advance_direction', 'currency_code'], 'uq_dberp_advance_account_counterparty');
            $table->index(['account_status', 'last_business_time'], 'idx_dberp_advance_account_status');
        });

        Schema::create('dberp_advance_account_flows', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('account_id');
            $table->foreign('account_id', 'fk_dberp_advance_flow_account')->references('id')->on('dberp_advance_accounts')->cascadeOnDelete();
            $table->string('flow_type', 32)->comment('流水类型');
            $table->foreignId('receipt_order_id')->nullable();
            $table->foreign('receipt_order_id', 'fk_dberp_advance_flow_receipt')->references('id')->on('dberp_receipt_orders')->restrictOnDelete();
            $table->foreignId('payment_order_id')->nullable();
            $table->foreign('payment_order_id', 'fk_dberp_advance_flow_payment')->references('id')->on('dberp_payment_orders')->restrictOnDelete();
            $table->foreignId('write_off_line_id')->nullable();
            $table->foreign('write_off_line_id', 'fk_dberp_advance_flow_writeoff_line')->references('id')->on('dberp_settlement_write_off_lines')->restrictOnDelete();
            $table->decimal('change_amount', 18, 4)->default(0)->comment('变动金额');
            $table->decimal('balance_after', 18, 4)->default(0)->comment('变动后余额');
            $table->dateTime('business_date')->comment('业务时间');
            $table->text('remark')->nullable()->comment('备注');
            $table->timestamps();

            $table->index(['account_id', 'business_date'], 'idx_dberp_advance_flow_account_time');
            $table->index(['flow_type', 'business_date'], 'idx_dberp_advance_flow_type_time');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('dberp_advance_account_flows');
        Schema::dropIfExists('dberp_advance_accounts');
        Schema::dropIfExists('dberp_settlement_write_off_lines');
        Schema::dropIfExists('dberp_settlement_write_offs');
        Schema::dropIfExists('dberp_payable_records');
        Schema::dropIfExists('dberp_receivable_records');
    }
};