<?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
    {
        $this->extendInventoryTransactions();
        $this->extendPurchaseOrderItems();
        $this->extendSalesOrderItems();
        $this->extendPurchaseReceiptItems();
        $this->extendSalesOutboundItems();
        $this->extendPurchaseReturnItems();
        $this->extendSalesReturnItems();
        $this->extendOtherStockItems();
        $this->extendTransferItems();
        $this->extendStocktakeItems();
        $this->backfillPurchaseReceiptBaseColumns();
        $this->backfillOtherTransferOutboundBases();
        $this->backfillReturnOrderItemBases();
        $this->backfillReceiptBaseReturnedFromReturns();
        $this->backfillPurchaseOrderBaseQuantities();
        $this->backfillSalesOrderBaseQuantities();
        $this->backfillStocktakeBaseActual();
        $this->backfillPurchaseOrderBaseReceived();
        $this->backfillSalesOrderBaseShipped();
        $this->backfillOutboundBaseReturned();
    }

    public function down(): void
    {
        $this->dropColumnIfExists('dberp_stocktake_order_items', 'base_actual_quantity');
        $this->dropColumnIfExists('dberp_transfer_order_items', 'base_quantity');
        $this->dropColumnIfExists('dberp_other_stock_out_order_items', 'base_quantity');
        $this->dropColumnIfExists('dberp_other_stock_in_order_items', 'base_quantity');
        $this->dropColumnIfExists('dberp_sales_return_order_items', 'base_return_quantity');
        $this->dropColumnIfExists('dberp_purchase_return_order_items', 'base_return_quantity');
        $this->dropColumnIfExists('dberp_sales_outbound_order_items', 'base_returned_quantity');
        $this->dropColumnIfExists('dberp_sales_outbound_order_items', 'base_ship_quantity');
        $this->dropColumnIfExists('dberp_purchase_receipt_order_items', 'base_returned_quantity');
        $this->dropColumnIfExists('dberp_purchase_receipt_order_items', 'base_arrival_quantity');
        $this->dropColumnIfExists('dberp_purchase_receipt_order_items', 'base_received_quantity');
        $this->dropColumnIfExists('dberp_sales_order_items', 'base_shipped_quantity');
        $this->dropColumnIfExists('dberp_sales_order_items', 'base_quantity');
        $this->dropColumnIfExists('dberp_purchase_order_items', 'base_received_quantity');
        $this->dropColumnIfExists('dberp_purchase_order_items', 'base_quantity');
        $this->dropInventoryTransactionTraceColumns();
    }

    private function extendInventoryTransactions(): void
    {
        if (! Schema::hasTable('dberp_inventory_transactions')) {
            return;
        }

        Schema::table('dberp_inventory_transactions', function (Blueprint $table): void {
            if (! Schema::hasColumn('dberp_inventory_transactions', 'document_input_quantity')) {
                $table->decimal('document_input_quantity', 18, 4)->nullable()->after('unit_name');
            }

            if (! Schema::hasColumn('dberp_inventory_transactions', 'document_input_unit_id')) {
                $table->unsignedBigInteger('document_input_unit_id')->nullable()->after('document_input_quantity');
            }

            if (! Schema::hasColumn('dberp_inventory_transactions', 'document_input_unit_name')) {
                $table->string('document_input_unit_name', 64)->nullable()->after('document_input_unit_id');
            }

            if (! Schema::hasColumn('dberp_inventory_transactions', 'posting_base_unit_id')) {
                $table->unsignedBigInteger('posting_base_unit_id')->nullable()->after('document_input_unit_name');
            }

            if (! Schema::hasColumn('dberp_inventory_transactions', 'posting_auxiliary_unit_id')) {
                $table->unsignedBigInteger('posting_auxiliary_unit_id')->nullable()->after('posting_base_unit_id');
            }

            if (! Schema::hasColumn('dberp_inventory_transactions', 'posting_is_auxiliary_unit')) {
                $table->boolean('posting_is_auxiliary_unit')->default(false)->after('posting_auxiliary_unit_id');
            }

            if (! Schema::hasColumn('dberp_inventory_transactions', 'posting_auxiliary_unit_quantity')) {
                $table->decimal('posting_auxiliary_unit_quantity', 18, 6)->nullable()->after('posting_is_auxiliary_unit');
            }

            if (! Schema::hasColumn('dberp_inventory_transactions', 'posting_base_unit_quantity')) {
                $table->decimal('posting_base_unit_quantity', 18, 6)->nullable()->after('posting_auxiliary_unit_quantity');
            }
        });
    }

    private function dropInventoryTransactionTraceColumns(): void
    {
        if (! Schema::hasTable('dberp_inventory_transactions')) {
            return;
        }

        $cols = [
            'document_input_quantity',
            'document_input_unit_id',
            'document_input_unit_name',
            'posting_base_unit_id',
            'posting_auxiliary_unit_id',
            'posting_is_auxiliary_unit',
            'posting_auxiliary_unit_quantity',
            'posting_base_unit_quantity',
        ];

        foreach ($cols as $col) {
            if (Schema::hasColumn('dberp_inventory_transactions', $col)) {
                Schema::table('dberp_inventory_transactions', function (Blueprint $table) use ($col): void {
                    $table->dropColumn($col);
                });
            }
        }
    }

    private function extendPurchaseOrderItems(): void
    {
        if (! Schema::hasTable('dberp_purchase_order_items')) {
            return;
        }

        Schema::table('dberp_purchase_order_items', function (Blueprint $table): void {
            if (! Schema::hasColumn('dberp_purchase_order_items', 'base_quantity')) {
                $table->decimal('base_quantity', 18, 4)->default(0)->after('quantity');
            }

            if (! Schema::hasColumn('dberp_purchase_order_items', 'base_received_quantity')) {
                $table->decimal('base_received_quantity', 18, 4)->default(0)->after('received_quantity');
            }
        });
    }

    private function extendSalesOrderItems(): void
    {
        if (! Schema::hasTable('dberp_sales_order_items')) {
            return;
        }

        Schema::table('dberp_sales_order_items', function (Blueprint $table): void {
            if (! Schema::hasColumn('dberp_sales_order_items', 'base_quantity')) {
                $table->decimal('base_quantity', 18, 4)->default(0)->after('quantity');
            }

            if (! Schema::hasColumn('dberp_sales_order_items', 'base_shipped_quantity')) {
                $table->decimal('base_shipped_quantity', 18, 4)->default(0)->after('shipped_quantity');
            }
        });
    }

    private function extendPurchaseReceiptItems(): void
    {
        if (! Schema::hasTable('dberp_purchase_receipt_order_items')) {
            return;
        }

        Schema::table('dberp_purchase_receipt_order_items', function (Blueprint $table): void {
            if (! Schema::hasColumn('dberp_purchase_receipt_order_items', 'base_received_quantity')) {
                $table->decimal('base_received_quantity', 18, 4)->default(0)->after('received_quantity');
            }

            if (! Schema::hasColumn('dberp_purchase_receipt_order_items', 'base_arrival_quantity')) {
                $table->decimal('base_arrival_quantity', 18, 4)->default(0)->after('arrival_quantity');
            }

            if (! Schema::hasColumn('dberp_purchase_receipt_order_items', 'base_returned_quantity')) {
                $table->decimal('base_returned_quantity', 18, 4)->default(0)->after('returned_quantity');
            }
        });
    }

    private function extendSalesOutboundItems(): void
    {
        if (! Schema::hasTable('dberp_sales_outbound_order_items')) {
            return;
        }

        Schema::table('dberp_sales_outbound_order_items', function (Blueprint $table): void {
            if (! Schema::hasColumn('dberp_sales_outbound_order_items', 'base_ship_quantity')) {
                $table->decimal('base_ship_quantity', 18, 4)->default(0)->after('ship_quantity');
            }

            if (! Schema::hasColumn('dberp_sales_outbound_order_items', 'base_returned_quantity')) {
                $table->decimal('base_returned_quantity', 18, 4)->default(0)->after('returned_quantity');
            }
        });
    }

    private function extendPurchaseReturnItems(): void
    {
        if (! Schema::hasTable('dberp_purchase_return_order_items')) {
            return;
        }

        Schema::table('dberp_purchase_return_order_items', function (Blueprint $table): void {
            if (! Schema::hasColumn('dberp_purchase_return_order_items', 'base_return_quantity')) {
                $table->decimal('base_return_quantity', 18, 4)->default(0)->after('return_quantity');
            }
        });
    }

    private function extendSalesReturnItems(): void
    {
        if (! Schema::hasTable('dberp_sales_return_order_items')) {
            return;
        }

        Schema::table('dberp_sales_return_order_items', function (Blueprint $table): void {
            if (! Schema::hasColumn('dberp_sales_return_order_items', 'base_return_quantity')) {
                $table->decimal('base_return_quantity', 18, 4)->default(0)->after('return_quantity');
            }
        });
    }

    private function extendOtherStockItems(): void
    {
        foreach (['dberp_other_stock_in_order_items', 'dberp_other_stock_out_order_items'] as $tableName) {
            if (! Schema::hasTable($tableName)) {
                continue;
            }

            Schema::table($tableName, function (Blueprint $table) use ($tableName): void {
                if (! Schema::hasColumn($tableName, 'base_quantity')) {
                    $table->decimal('base_quantity', 18, 4)->default(0)->after('quantity');
                }
            });
        }
    }

    private function extendTransferItems(): void
    {
        if (! Schema::hasTable('dberp_transfer_order_items')) {
            return;
        }

        Schema::table('dberp_transfer_order_items', function (Blueprint $table): void {
            if (! Schema::hasColumn('dberp_transfer_order_items', 'base_quantity')) {
                $table->decimal('base_quantity', 18, 4)->default(0)->after('quantity');
            }
        });
    }

    private function extendStocktakeItems(): void
    {
        if (! Schema::hasTable('dberp_stocktake_order_items')) {
            return;
        }

        Schema::table('dberp_stocktake_order_items', function (Blueprint $table): void {
            if (! Schema::hasColumn('dberp_stocktake_order_items', 'base_actual_quantity')) {
                $table->decimal('base_actual_quantity', 18, 4)->default(0)->after('actual_quantity');
            }
        });
    }

    private function backfillPurchaseOrderBaseQuantities(): void
    {
        if (! Schema::hasTable('dberp_purchase_order_items')) {
            return;
        }

        $rows = DB::table('dberp_purchase_order_items')->orderBy('id')->get();
        foreach ($rows as $row) {
            $base = $this->migrationLineBaseQuantityFromRow($row);
            DB::table('dberp_purchase_order_items')->where('id', $row->id)->update(['base_quantity' => $base]);
        }
    }

    private function backfillSalesOrderBaseQuantities(): void
    {
        if (! Schema::hasTable('dberp_sales_order_items')) {
            return;
        }

        $rows = DB::table('dberp_sales_order_items')->orderBy('id')->get();
        foreach ($rows as $row) {
            $base = $this->migrationLineBaseQuantityFromRow($row);
            DB::table('dberp_sales_order_items')->where('id', $row->id)->update(['base_quantity' => $base]);
        }
    }

    private function backfillStocktakeBaseActual(): void
    {
        if (! Schema::hasTable('dberp_stocktake_order_items')) {
            return;
        }

        $rows = DB::table('dberp_stocktake_order_items')->orderBy('id')->get();
        foreach ($rows as $row) {
            $base = $this->migrationLineBaseQuantityFromRow($row, 'actual_quantity');
            DB::table('dberp_stocktake_order_items')->where('id', $row->id)->update(['base_actual_quantity' => $base]);
        }
    }

    private function migrationLineBaseQuantityFromRow(object $row, string $quantityColumn = 'quantity'): string
    {
        $qty = (string) ($row->{$quantityColumn} ?? '0');
        if (! is_numeric($qty) || bccomp($qty, '0', 6) !== 1) {
            return '0.0000';
        }

        $isAux = (bool) ($row->is_auxiliary_unit ?? false);
        if (! $isAux) {
            return number_format((float) $qty, 4, '.', '');
        }

        $aux = isset($row->auxiliary_unit_quantity) ? (string) $row->auxiliary_unit_quantity : '';
        $base = isset($row->base_unit_quantity) ? (string) $row->base_unit_quantity : '';
        if ($aux === '' || $base === '' || ! is_numeric($aux) || ! is_numeric($base)) {
            return number_format((float) $qty, 4, '.', '');
        }

        if (bccomp($aux, '0', 6) !== 1 || bccomp($base, '0', 6) !== 1) {
            return number_format((float) $qty, 4, '.', '');
        }

        $ratio = bcdiv($base, $aux, 12);
        $converted = bcmul($qty, $ratio, 12);

        return number_format((float) bcdiv($converted, '1', 4), 4, '.', '');
    }

    private function backfillOtherTransferOutboundBases(): void
    {
        foreach (['dberp_other_stock_in_order_items', 'dberp_other_stock_out_order_items', 'dberp_transfer_order_items'] as $table) {
            if (! Schema::hasTable($table)) {
                continue;
            }

            DB::statement('UPDATE '.$table.' SET base_quantity = quantity WHERE base_quantity = 0 OR base_quantity IS NULL');
        }

        if (Schema::hasTable('dberp_sales_outbound_order_items')) {
            DB::statement('UPDATE dberp_sales_outbound_order_items SET base_ship_quantity = ship_quantity WHERE base_ship_quantity = 0 OR base_ship_quantity IS NULL');
        }
    }

    private function backfillReturnOrderItemBases(): void
    {
        foreach (['dberp_purchase_return_order_items', 'dberp_sales_return_order_items'] as $tableName) {
            if (! Schema::hasTable($tableName)) {
                continue;
            }

            $rows = DB::table($tableName)->orderBy('id')->get();
            foreach ($rows as $row) {
                $base = $this->migrationLineBaseQuantityFromRow($row, 'return_quantity');
                DB::table($tableName)->where('id', $row->id)->update(['base_return_quantity' => $base]);
            }
        }
    }

    private function backfillReceiptBaseReturnedFromReturns(): void
    {
        if (! Schema::hasTable('dberp_purchase_receipt_order_items') || ! Schema::hasTable('dberp_purchase_return_order_items')) {
            return;
        }

        $receiptItemIds = DB::table('dberp_purchase_receipt_order_items')->pluck('id');
        foreach ($receiptItemIds as $rid) {
            $sum = DB::table('dberp_purchase_return_order_items as ri')
                ->join('dberp_purchase_return_orders as ro', 'ro.id', '=', 'ri.order_id')
                ->where('ri.purchase_receipt_item_id', $rid)
                ->where('ro.status', 'effective')
                ->sum(DB::raw('COALESCE(ri.base_return_quantity, ri.return_quantity, 0)'));

            DB::table('dberp_purchase_receipt_order_items')->where('id', $rid)->update([
                'base_returned_quantity' => $sum ?: 0,
            ]);
        }
    }

    private function backfillPurchaseReceiptBaseColumns(): void
    {
        if (! Schema::hasTable('dberp_purchase_receipt_order_items')) {
            return;
        }

        DB::table('dberp_purchase_receipt_order_items')->orderBy('id')->chunkById(200, function ($rows): void {
            foreach ($rows as $row) {
                DB::table('dberp_purchase_receipt_order_items')->where('id', $row->id)->update([
                    'base_received_quantity' => $row->received_quantity ?? 0,
                    'base_arrival_quantity' => $row->arrival_quantity ?? 0,
                    'base_returned_quantity' => $row->returned_quantity ?? 0,
                ]);
            }
        });
    }

    private function backfillPurchaseOrderBaseReceived(): void
    {
        if (! Schema::hasTable('dberp_purchase_order_items') || ! Schema::hasTable('dberp_purchase_receipt_order_items')) {
            return;
        }

        $poItemIds = DB::table('dberp_purchase_order_items')->pluck('id');
        foreach ($poItemIds as $poItemId) {
            $sum = DB::table('dberp_purchase_receipt_order_items as i')
                ->join('dberp_purchase_receipt_orders as o', 'o.id', '=', 'i.order_id')
                ->where('i.purchase_order_item_id', $poItemId)
                ->where('o.status', 'effective')
                ->sum(DB::raw('COALESCE(i.base_received_quantity, i.received_quantity, 0)'));

            DB::table('dberp_purchase_order_items')->where('id', $poItemId)->update([
                'base_received_quantity' => $sum ?: 0,
            ]);
        }
    }

    private function backfillSalesOrderBaseShipped(): void
    {
        if (! Schema::hasTable('dberp_sales_order_items') || ! Schema::hasTable('dberp_sales_outbound_order_items')) {
            return;
        }

        $soItemIds = DB::table('dberp_sales_order_items')->pluck('id');
        foreach ($soItemIds as $soItemId) {
            $sum = DB::table('dberp_sales_outbound_order_items as i')
                ->join('dberp_sales_outbound_orders as o', 'o.id', '=', 'i.order_id')
                ->where('i.sales_order_item_id', $soItemId)
                ->where('o.status', 'effective')
                ->sum(DB::raw('COALESCE(i.base_ship_quantity, i.ship_quantity, 0)'));

            DB::table('dberp_sales_order_items')->where('id', $soItemId)->update([
                'base_shipped_quantity' => $sum ?: 0,
            ]);
        }
    }

    private function backfillOutboundBaseReturned(): void
    {
        if (! Schema::hasTable('dberp_sales_outbound_order_items') || ! Schema::hasTable('dberp_sales_return_order_items')) {
            return;
        }

        $outItemIds = DB::table('dberp_sales_outbound_order_items')->pluck('id');
        foreach ($outItemIds as $outItemId) {
            $sum = DB::table('dberp_sales_return_order_items as ri')
                ->join('dberp_sales_return_orders as ro', 'ro.id', '=', 'ri.order_id')
                ->where('ri.sales_outbound_item_id', $outItemId)
                ->where('ro.status', 'effective')
                ->sum(DB::raw('COALESCE(ri.base_return_quantity, ri.return_quantity, 0)'));

            DB::table('dberp_sales_outbound_order_items')->where('id', $outItemId)->update([
                'base_returned_quantity' => $sum ?: 0,
            ]);
        }
    }

    private function dropColumnIfExists(string $table, string $column): void
    {
        if (Schema::hasTable($table) && Schema::hasColumn($table, $column)) {
            Schema::table($table, function (Blueprint $blueprint) use ($column): void {
                $blueprint->dropColumn($column);
            });
        }
    }
};
