<?php

declare(strict_types=1);

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        if (Schema::getConnection()->getDriverName() === 'sqlite') {
            DB::statement('DROP VIEW IF EXISTS dberp_receipt_payment_queries');
            DB::statement('DROP VIEW IF EXISTS dberp_document_queries');
        }

        Schema::create('dberp_customer_price_rules', function (Blueprint $table): void {
            $table->id();
            $table->string('rule_name', 160)->comment('规则名称');
            $table->string('rule_type', 32)->comment('规则类型');
            $table->foreignId('customer_id')->nullable()->constrained('dberp_customers')->nullOnDelete();
            $table->string('price_level_code', 64)->nullable()->comment('客户价格等级');
            $table->foreignId('product_id')->constrained('dberp_products')->restrictOnDelete();
            $table->string('external_product_id', 128)->nullable()->comment('外部商品 ID');
            $table->string('external_sku', 128)->nullable()->comment('外部 SKU');
            $table->string('product_spec_snapshot', 160)->nullable()->comment('规格快照');
            $table->decimal('unit_price', 18, 4)->default(0)->comment('规则单价');
            $table->unsignedInteger('sort_order')->default(0)->comment('越小越靠前');
            $table->date('valid_from')->comment('生效日期');
            $table->date('valid_until')->nullable()->comment('失效日期');
            $table->string('status', 32)->default('active')->comment('规则状态');
            $table->string('source_system', 32)->nullable()->comment('来源系统');
            $table->string('channel_code', 64)->nullable()->comment('渠道标识');
            $table->string('external_reference_id', 128)->nullable()->comment('外部关联 ID');
            $table->text('remark')->nullable()->comment('备注');
            $table->timestamps();

            $table->index(['rule_type', 'status', 'valid_from'], 'idx_dcpr_type_status_from');
            $table->index(['customer_id', 'product_id', 'status'], 'idx_dcpr_customer_product');
            $table->index(['price_level_code', 'product_id', 'status'], 'idx_dcpr_level_product');
            $table->index(['product_id', 'external_sku'], 'idx_dcpr_product_sku');
            $table->index(['sort_order', 'id'], 'idx_dcpr_sort_id');
        });

        Schema::create('dberp_sales_quotes', function (Blueprint $table): void {
            $table->id();
            $table->string('code', 64)->comment('报价单号');
            $table->foreignId('customer_id')->constrained('dberp_customers')->restrictOnDelete();
            $table->foreignId('handler_employee_id')->nullable()->constrained('dberp_employees')->nullOnDelete();
            $table->date('quote_date')->comment('报价日期');
            $table->date('valid_from')->nullable()->comment('生效日期');
            $table->date('valid_until')->nullable()->comment('失效日期');
            $table->string('status', 32)->default('draft')->comment('报价状态');
            $table->unsignedInteger('suggested_payment_term_days')->nullable()->comment('建议账期天数');
            $table->decimal('total_quantity', 18, 4)->default(0)->comment('总数量');
            $table->decimal('total_amount', 18, 4)->default(0)->comment('总金额');
            $table->decimal('discount_amount', 18, 4)->default(0)->comment('优惠金额');
            $table->decimal('final_amount', 18, 4)->default(0)->comment('成交参考金额');
            $table->foreignId('converted_sales_order_id')->nullable()->constrained('dberp_sales_orders')->nullOnDelete();
            $table->string('converted_sales_order_no', 64)->nullable()->comment('转出销售单号');
            $table->dateTime('converted_at')->nullable()->comment('转单时间');
            $table->string('source_system', 32)->nullable()->comment('来源系统');
            $table->string('external_reference_id', 128)->nullable()->comment('外部关联 ID');
            $table->text('remark')->nullable()->comment('备注');
            $table->timestamps();

            $table->unique('code', 'uq_dberp_sales_quote_code');
            $table->index(['customer_id', 'status', 'quote_date'], 'idx_dsq_customer_status_date');
            $table->index(['handler_employee_id', 'status'], 'idx_dsq_handler_status');
            $table->index(['status', 'valid_until'], 'idx_dsq_status_valid_until');
            $table->index(['source_system', 'external_reference_id'], 'idx_dsq_source_external');
        });

        Schema::create('dberp_sales_quote_items', function (Blueprint $table): void {
            $table->id();
            $table->foreignId('quote_id')->constrained('dberp_sales_quotes')->cascadeOnDelete();
            $table->foreignId('product_id')->constrained('dberp_products')->restrictOnDelete();
            $table->string('product_code_snapshot', 64)->nullable()->comment('商品编码快照');
            $table->string('product_name_snapshot', 160)->nullable()->comment('商品名称快照');
            $table->string('product_spec_snapshot', 160)->nullable()->comment('规格快照');
            $table->string('external_product_id', 128)->nullable()->comment('外部商品 ID');
            $table->string('external_sku', 128)->nullable()->comment('外部 SKU');
            $table->decimal('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('discount_amount', 18, 4)->default(0)->comment('优惠金额');
            $table->decimal('final_amount', 18, 4)->default(0)->comment('小计');
            $table->string('price_source_type', 32)->nullable()->comment('价格来源');
            $table->string('price_source_label', 64)->nullable()->comment('价格来源说明');
            $table->foreignId('price_rule_id')->nullable()->constrained('dberp_customer_price_rules')->nullOnDelete();
            $table->decimal('matched_unit_price', 18, 4)->nullable()->comment('命中规则价');
            $table->decimal('reference_unit_price', 18, 4)->nullable()->comment('默认销售价参考');
            $table->decimal('recent_transaction_unit_price', 18, 4)->nullable()->comment('最近成交价参考');
            $table->date('recent_transaction_date')->nullable()->comment('最近成交日期');
            $table->unsignedInteger('sort_order')->default(0)->comment('越小越靠前');
            $table->text('remark')->nullable()->comment('备注');
            $table->timestamps();

            $table->index(['quote_id', 'sort_order'], 'idx_dsqi_quote_sort');
            $table->index(['price_rule_id'], 'idx_dsqi_price_rule');
            $table->index(['product_id', 'external_sku'], 'idx_dsqi_product_sku');
        });

        Schema::table('dberp_customers', function (Blueprint $table): void {
            $table->string('price_level_code', 64)->nullable()->after('settlement_method')->comment('客户价格等级');
            $table->unsignedInteger('default_payment_term_days')->nullable()->after('price_level_code')->comment('默认账期天数');
            $table->decimal('credit_limit_amount', 18, 4)->nullable()->after('default_payment_term_days')->comment('信用额度');
            $table->string('credit_control_note', 255)->nullable()->after('credit_limit_amount')->comment('信用控制备注');

            $table->index(['price_level_code'], 'idx_dberp_customer_price_level');
        });

        Schema::table('dberp_sales_orders', function (Blueprint $table): void {
            $table->foreignId('quote_id')->nullable()->after('customer_id')->constrained('dberp_sales_quotes')->nullOnDelete();
            $table->string('quote_code', 64)->nullable()->after('quote_id')->comment('来源报价号');
            $table->string('price_source_type', 32)->nullable()->after('receivable_status')->comment('价格来源');
            $table->foreignId('price_rule_id')->nullable()->after('price_source_type')->constrained('dberp_customer_price_rules')->nullOnDelete();
            $table->unsignedInteger('payment_term_days')->nullable()->after('price_rule_id')->comment('本单账期天数');
            $table->date('due_date')->nullable()->after('payment_term_days')->comment('本单到期日');
            $table->string('credit_check_result', 32)->nullable()->after('due_date')->comment('信用检查结果');
            $table->string('credit_override_reason', 255)->nullable()->after('credit_check_result')->comment('放行原因');
            $table->dateTime('credit_checked_at')->nullable()->after('credit_override_reason')->comment('信用检查时间');

            $table->index(['quote_id', 'status'], 'idx_dso_quote_status');
            $table->index(['due_date', 'status'], 'idx_dso_due_status');
        });
    }

    public function down(): void
    {
        Schema::table('dberp_sales_orders', function (Blueprint $table): void {
            $table->dropIndex('idx_dso_quote_status');
            $table->dropIndex('idx_dso_due_status');
            $table->dropConstrainedForeignId('quote_id');
            $table->dropConstrainedForeignId('price_rule_id');
            $table->dropColumn([
                'quote_code',
                'price_source_type',
                'payment_term_days',
                'due_date',
                'credit_check_result',
                'credit_override_reason',
                'credit_checked_at',
            ]);
        });

        Schema::table('dberp_customers', function (Blueprint $table): void {
            $table->dropIndex('idx_dberp_customer_price_level');
            $table->dropColumn([
                'price_level_code',
                'default_payment_term_days',
                'credit_limit_amount',
                'credit_control_note',
            ]);
        });

        Schema::dropIfExists('dberp_sales_quote_items');
        Schema::dropIfExists('dberp_sales_quotes');
        Schema::dropIfExists('dberp_customer_price_rules');
    }
};
