<?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_stocktake_serial_number_differences') && ! $this->tableHasForeignKeys('dberp_stocktake_serial_number_differences')) {
            Schema::dropIfExists('dberp_stocktake_serial_number_differences');
        }

        if (! Schema::hasTable('dberp_stocktake_serial_number_differences')) {
            Schema::create('dberp_stocktake_serial_number_differences', function (Blueprint $table): void {
                $table->id();
                $table->unsignedBigInteger('stocktake_order_id')->comment('盘点单');
                $table->unsignedBigInteger('stocktake_order_item_id')->comment('盘点明细');
                $table->unsignedBigInteger('product_id')->comment('物料');
                $table->unsignedBigInteger('product_sku_id')->nullable()->comment('SKU');
                $table->unsignedBigInteger('warehouse_id')->comment('仓库');
                $table->string('serial_no', 128)->comment('序列号');
                $table->string('difference_type', 64)->comment('序列号差异类型');
                $table->string('system_status', 32)->nullable()->comment('系统序列号状态快照');
                $table->unsignedBigInteger('system_warehouse_id')->nullable()->comment('系统仓库快照');
                $table->boolean('system_is_enabled')->nullable()->comment('系统启用状态快照');
                $table->boolean('counted_in_actual')->default(true)->comment('是否出现在实盘序列号中');
                $table->string('status', 32)->default('pending')->comment('差异处理状态');
                $table->unsignedInteger('sort_order')->default(0)->comment('越小越靠前');
                $table->timestamps();

                $table->foreign('stocktake_order_id', 'fk_dberp_stk_sn_diff_ord')
                    ->references('id')->on('dberp_stocktake_orders')->cascadeOnDelete();
                $table->foreign('stocktake_order_item_id', 'fk_dberp_stk_sn_diff_item')
                    ->references('id')->on('dberp_stocktake_order_items')->cascadeOnDelete();
                $table->foreign('product_id', 'fk_dberp_stk_sn_diff_prod')
                    ->references('id')->on('dberp_products')->restrictOnDelete();
                $table->foreign('product_sku_id', 'fk_dberp_stk_sn_diff_sku')
                    ->references('id')->on('dberp_product_skus')->nullOnDelete();
                $table->foreign('warehouse_id', 'fk_dberp_stk_sn_diff_wh')
                    ->references('id')->on('dberp_warehouses')->restrictOnDelete();
                $table->foreign('system_warehouse_id', 'fk_dberp_stk_sn_diff_sys_wh')
                    ->references('id')->on('dberp_warehouses')->nullOnDelete();

                $table->unique(
                    ['stocktake_order_item_id', 'serial_no', 'difference_type'],
                    'uq_dberp_stk_sn_diff_item_serial_type',
                );
                $table->index(['stocktake_order_id', 'status'], 'idx_dberp_stk_sn_diff_order_status');
                $table->index(['warehouse_id', 'serial_no'], 'idx_dberp_stk_sn_diff_wh_serial');
            });
        }
    }

    public function down(): void
    {
        Schema::dropIfExists('dberp_stocktake_serial_number_differences');
    }

    private function tableHasForeignKeys(string $table): bool
    {
        $connection = Schema::getConnection();

        if ($connection->getDriverName() === 'sqlite') {
            return true;
        }

        $database = $connection->getDatabaseName();

        $result = $connection->selectOne(
            'select count(*) as c from information_schema.table_constraints where table_schema = ? and table_name = ? and constraint_type = ?',
            [$database, $table, 'FOREIGN KEY'],
        );

        return isset($result->c) && (int) $result->c > 0;
    }
};
