<?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
{
    /**
     * 单据明细：计量单位、主/辅单位快照（Step 3）；库存过账类明细在业务层限制仅主单位。
     */
    public function up(): void
    {
        $extendExistingUnit = [
            'dberp_purchase_order_items',
            'dberp_purchase_receipt_order_items',
            'dberp_sales_order_items',
            'dberp_sales_outbound_order_items',
        ];

        $addFullUnit = [
            'dberp_purchase_return_order_items',
            'dberp_sales_return_order_items',
            'dberp_other_stock_in_order_items',
            'dberp_other_stock_out_order_items',
            'dberp_transfer_order_items',
            'dberp_stocktake_order_items',
            'dberp_purchase_request_items',
            'dberp_purchase_inquiry_items',
            'dberp_sales_quote_items',
        ];

        foreach ($extendExistingUnit as $tableName) {
            if (! Schema::hasTable($tableName) || Schema::hasColumn($tableName, 'base_unit_id')) {
                continue;
            }

            Schema::table($tableName, function (Blueprint $blueprint): void {
                $blueprint->unsignedBigInteger('base_unit_id')->nullable()->after('unit_name');
                $blueprint->unsignedBigInteger('auxiliary_unit_id')->nullable()->after('base_unit_id');
                $blueprint->boolean('is_auxiliary_unit')->default(false)->after('auxiliary_unit_id');
                $blueprint->decimal('auxiliary_unit_quantity', 18, 6)->nullable()->after('is_auxiliary_unit');
                $blueprint->decimal('base_unit_quantity', 18, 6)->nullable()->after('auxiliary_unit_quantity');
            });
        }

        foreach ($addFullUnit as $tableName) {
            if (! Schema::hasTable($tableName) || Schema::hasColumn($tableName, 'unit_id')) {
                continue;
            }

            Schema::table($tableName, function (Blueprint $blueprint): void {
                $blueprint->unsignedBigInteger('unit_id')->nullable()->after('product_id');
                $blueprint->string('unit_name', 64)->nullable()->after('unit_id');
                $blueprint->unsignedBigInteger('base_unit_id')->nullable()->after('unit_name');
                $blueprint->unsignedBigInteger('auxiliary_unit_id')->nullable()->after('base_unit_id');
                $blueprint->boolean('is_auxiliary_unit')->default(false)->after('auxiliary_unit_id');
                $blueprint->decimal('auxiliary_unit_quantity', 18, 6)->nullable()->after('is_auxiliary_unit');
                $blueprint->decimal('base_unit_quantity', 18, 6)->nullable()->after('auxiliary_unit_quantity');
            });
        }

        foreach (array_merge($extendExistingUnit, $addFullUnit) as $tableName) {
            if (! Schema::hasTable($tableName) || ! Schema::hasColumn($tableName, 'base_unit_id')) {
                continue;
            }

            $this->backfillTable($tableName, in_array($tableName, $extendExistingUnit, true));
        }

        $fkMap = [
            'dberp_purchase_order_items' => 'poi',
            'dberp_purchase_receipt_order_items' => 'pri',
            'dberp_sales_order_items' => 'soi',
            'dberp_sales_outbound_order_items' => 'souti',
            'dberp_purchase_return_order_items' => 'proi',
            'dberp_sales_return_order_items' => 'sri',
            'dberp_other_stock_in_order_items' => 'osii',
            'dberp_other_stock_out_order_items' => 'osoi',
            'dberp_transfer_order_items' => 'toi',
            'dberp_stocktake_order_items' => 'sti',
            'dberp_purchase_request_items' => 'preqi',
            'dberp_purchase_inquiry_items' => 'dpii',
            'dberp_sales_quote_items' => 'sqti',
        ];

        foreach ($fkMap as $tableName => $short) {
            if (! Schema::hasTable($tableName) || ! Schema::hasColumn($tableName, 'base_unit_id')) {
                continue;
            }

            Schema::table($tableName, function (Blueprint $blueprint) use ($short, $tableName): void {
                if (Schema::hasColumn($tableName, 'unit_id')) {
                    $blueprint->foreign('unit_id', 'fk_'.$short.'_unit')->references('id')->on('dberp_units')->nullOnDelete();
                }

                $blueprint->foreign('base_unit_id', 'fk_'.$short.'_base_unit')->references('id')->on('dberp_units')->restrictOnDelete();
                $blueprint->foreign('auxiliary_unit_id', 'fk_'.$short.'_aux_unit')->references('id')->on('dberp_units')->nullOnDelete();
            });
        }
    }

    public function down(): void
    {
        $fkMap = [
            'dberp_purchase_order_items' => 'poi',
            'dberp_purchase_receipt_order_items' => 'pri',
            'dberp_sales_order_items' => 'soi',
            'dberp_sales_outbound_order_items' => 'souti',
            'dberp_purchase_return_order_items' => 'proi',
            'dberp_sales_return_order_items' => 'sri',
            'dberp_other_stock_in_order_items' => 'osii',
            'dberp_other_stock_out_order_items' => 'osoi',
            'dberp_transfer_order_items' => 'toi',
            'dberp_stocktake_order_items' => 'sti',
            'dberp_purchase_request_items' => 'preqi',
            'dberp_purchase_inquiry_items' => 'dpii',
            'dberp_sales_quote_items' => 'sqti',
        ];

        $hadUnitBefore = [
            'dberp_purchase_order_items',
            'dberp_purchase_receipt_order_items',
            'dberp_sales_order_items',
            'dberp_sales_outbound_order_items',
        ];

        foreach ($fkMap as $tableName => $short) {
            if (! Schema::hasTable($tableName) || ! Schema::hasColumn($tableName, 'base_unit_id')) {
                continue;
            }

            Schema::table($tableName, function (Blueprint $blueprint) use ($short, $tableName): void {
                if (Schema::hasColumn($tableName, 'unit_id')) {
                    $blueprint->dropForeign('fk_'.$short.'_unit');
                }

                $blueprint->dropForeign('fk_'.$short.'_base_unit');
                $blueprint->dropForeign('fk_'.$short.'_aux_unit');
            });

            $keepUnitColumns = in_array($tableName, $hadUnitBefore, true);

            Schema::table($tableName, function (Blueprint $blueprint) use ($keepUnitColumns): void {
                $blueprint->dropColumn([
                    'base_unit_id',
                    'auxiliary_unit_id',
                    'is_auxiliary_unit',
                    'auxiliary_unit_quantity',
                    'base_unit_quantity',
                ]);

                if (! $keepUnitColumns) {
                    $blueprint->dropColumn(['unit_id', 'unit_name']);
                }
            });
        }
    }

    private function backfillTable(string $tableName, bool $hadUnitColumn): void
    {
        $rows = DB::table($tableName)->orderBy('id')->get();
        foreach ($rows as $row) {
            $productId = (int) ($row->product_id ?? 0);
            if ($productId <= 0) {
                continue;
            }

            $product = DB::table('dberp_products')->where('id', $productId)->first();
            if ($product === null) {
                continue;
            }

            $baseId = (int) ($product->unit_id ?? 0);
            $enabled = (bool) ($product->is_auxiliary_unit_enabled ?? false);
            $auxId = $enabled ? (int) ($product->auxiliary_unit_id ?? 0) : 0;
            $auxQty = $enabled ? $product->auxiliary_unit_quantity : null;
            $baseQty = $enabled ? $product->base_unit_quantity : null;

            $allowedAux = $enabled && $auxId > 0 && $auxId !== $baseId;

            $selectedUnitId = $hadUnitColumn ? (int) ($row->unit_id ?? 0) : 0;
            if ($selectedUnitId <= 0 || ($selectedUnitId !== $baseId && ! ($allowedAux && $selectedUnitId === $auxId))) {
                $selectedUnitId = $baseId > 0 ? $baseId : $selectedUnitId;
            }

            $unitName = $selectedUnitId > 0
                ? DB::table('dberp_units')->where('id', $selectedUnitId)->value('name')
                : null;

            $payload = [
                'base_unit_id' => $baseId > 0 ? $baseId : null,
                'auxiliary_unit_id' => $allowedAux ? $auxId : null,
                'auxiliary_unit_quantity' => $allowedAux ? $auxQty : null,
                'base_unit_quantity' => $allowedAux ? $baseQty : null,
                'is_auxiliary_unit' => $allowedAux && $selectedUnitId === $auxId,
            ];

            if (! $hadUnitColumn) {
                $payload['unit_id'] = $selectedUnitId > 0 ? $selectedUnitId : ($baseId > 0 ? $baseId : null);
                $payload['unit_name'] = $unitName ?? ($baseId > 0 ? DB::table('dberp_units')->where('id', $baseId)->value('name') : null);
            } else {
                $payload['unit_id'] = $selectedUnitId > 0 ? $selectedUnitId : null;
                $payload['unit_name'] = $unitName;
            }

            DB::table($tableName)->where('id', $row->id)->update($payload);
        }
    }
};
