<?php

declare(strict_types=1);

use App\Models\DberpProduct;
use App\Models\DberpProductSku;
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->upgradeInventoryBalances();
        $this->upgradeInventoryTransactions();
        $this->upgradePurchaseOrderItems();
        $this->upgradePurchaseReceiptOrderItems();
        $this->upgradeSalesOrderItems();
        $this->upgradeSalesOutboundOrderItems();
    }

    public function down(): void
    {
        if (Schema::hasTable('dberp_sales_outbound_order_items') && Schema::hasColumn('dberp_sales_outbound_order_items', 'product_sku_id')) {
            Schema::table('dberp_sales_outbound_order_items', function (Blueprint $table): void {
                $table->dropForeign(['product_sku_id']);
                $table->dropIndex('idx_dberp_sout_item_sku');
                $table->dropColumn([
                    'product_sku_id',
                    'product_code',
                    'product_name',
                    'sku_code',
                    'spec_text',
                    'unit_id',
                    'unit_name',
                ]);
            });
        }

        if (Schema::hasTable('dberp_sales_order_items') && Schema::hasColumn('dberp_sales_order_items', 'product_sku_id')) {
            Schema::table('dberp_sales_order_items', function (Blueprint $table): void {
                $table->dropForeign(['product_sku_id']);
                $table->dropIndex('idx_dberp_so_item_sku');
                $table->dropColumn([
                    'product_sku_id',
                    'product_code',
                    'product_name',
                    'sku_code',
                    'spec_text',
                    'unit_id',
                    'unit_name',
                ]);
            });
        }

        if (Schema::hasTable('dberp_purchase_receipt_order_items') && Schema::hasColumn('dberp_purchase_receipt_order_items', 'product_sku_id')) {
            Schema::table('dberp_purchase_receipt_order_items', function (Blueprint $table): void {
                $table->dropForeign(['product_sku_id']);
                $table->dropIndex('idx_dberp_pr_item_sku');
                $table->dropColumn([
                    'product_sku_id',
                    'product_code',
                    'product_name',
                    'sku_code',
                    'spec_text',
                    'unit_id',
                    'unit_name',
                ]);
            });
        }

        if (Schema::hasTable('dberp_purchase_order_items') && Schema::hasColumn('dberp_purchase_order_items', 'product_sku_id')) {
            Schema::table('dberp_purchase_order_items', function (Blueprint $table): void {
                $table->dropForeign(['product_sku_id']);
                $table->dropIndex('idx_dberp_po_item_sku');
                $table->dropColumn([
                    'product_sku_id',
                    'product_code',
                    'product_name',
                    'sku_code',
                    'spec_text',
                    'unit_id',
                    'unit_name',
                ]);
            });
        }

        if (Schema::hasTable('dberp_inventory_transactions') && Schema::hasColumn('dberp_inventory_transactions', 'product_sku_id')) {
            Schema::table('dberp_inventory_transactions', function (Blueprint $table): void {
                $table->dropForeign(['product_sku_id']);
                $table->dropIndex('idx_dberp_inv_tx_sku_time');
                $table->dropColumn([
                    'product_sku_id',
                    'product_code',
                    'product_name',
                    'sku_code',
                    'spec_text',
                    'unit_id',
                    'unit_name',
                ]);
            });
        }

        if (Schema::hasTable('dberp_inventory_balances') && Schema::hasColumn('dberp_inventory_balances', 'product_sku_id')) {
            Schema::table('dberp_inventory_balances', function (Blueprint $table): void {
                $table->dropForeign(['product_sku_id']);
                $table->dropUnique('uq_dberp_inv_balance_sku_wh');
                $table->dropColumn('product_sku_id');
            });

            Schema::table('dberp_inventory_balances', function (Blueprint $table): void {
                $table->unique(['product_id', 'warehouse_id'], 'uq_dberp_inv_balance_prod_wh');
            });
        }
    }

    private function upgradeInventoryBalances(): void
    {
        if (! Schema::hasTable('dberp_inventory_balances') || Schema::hasColumn('dberp_inventory_balances', 'product_sku_id')) {
            return;
        }

        Schema::table('dberp_inventory_balances', function (Blueprint $table): void {
            $table->unsignedBigInteger('product_sku_id')->nullable()->after('product_id');
        });

        foreach (DB::table('dberp_inventory_balances')->orderBy('id')->cursor() as $row) {
            $skuId = $this->resolveDefaultSkuIdForProduct((int) $row->product_id);
            DB::table('dberp_inventory_balances')->where('id', $row->id)->update(['product_sku_id' => $skuId]);
        }

        Schema::table('dberp_inventory_balances', function (Blueprint $table): void {
            $table->dropUnique('uq_dberp_inv_balance_prod_wh');
        });

        Schema::table('dberp_inventory_balances', function (Blueprint $table): void {
            $table->unique(['product_sku_id', 'warehouse_id'], 'uq_dberp_inv_balance_sku_wh');
        });

        Schema::table('dberp_inventory_balances', function (Blueprint $table): void {
            $table->foreign('product_sku_id')->references('id')->on('dberp_product_skus')->restrictOnDelete();
        });
    }

    private function upgradeInventoryTransactions(): void
    {
        if (! Schema::hasTable('dberp_inventory_transactions') || Schema::hasColumn('dberp_inventory_transactions', 'product_sku_id')) {
            return;
        }

        Schema::table('dberp_inventory_transactions', function (Blueprint $table): void {
            $table->unsignedBigInteger('product_sku_id')->nullable()->after('product_id');
            $table->string('product_code', 64)->nullable()->after('product_sku_id');
            $table->string('product_name', 255)->nullable()->after('product_code');
            $table->string('sku_code', 128)->nullable()->after('product_name');
            $table->text('spec_text')->nullable()->after('sku_code');
            $table->unsignedBigInteger('unit_id')->nullable()->after('spec_text');
            $table->string('unit_name', 64)->nullable()->after('unit_id');
            $table->index(['product_sku_id', 'business_time'], 'idx_dberp_inv_tx_sku_time');
        });

        foreach (DB::table('dberp_inventory_transactions')->orderBy('id')->cursor() as $row) {
            $skuId = DB::table('dberp_inventory_balances')
                ->where('product_id', $row->product_id)
                ->where('warehouse_id', $row->warehouse_id)
                ->value('product_sku_id');

            if ($skuId === null) {
                $skuId = $this->resolveDefaultSkuIdForProduct((int) $row->product_id);
            }

            DB::table('dberp_inventory_transactions')->where('id', $row->id)->update(['product_sku_id' => $skuId]);
        }

        Schema::table('dberp_inventory_transactions', function (Blueprint $table): void {
            $table->foreign('product_sku_id')->references('id')->on('dberp_product_skus')->restrictOnDelete();
        });
    }

    private function documentSnapshotColumns(Blueprint $table): void
    {
        $table->unsignedBigInteger('product_sku_id')->nullable()->after('product_id');
        $table->string('product_code', 64)->nullable()->after('product_sku_id');
        $table->string('product_name', 255)->nullable()->after('product_code');
        $table->string('sku_code', 128)->nullable()->after('product_name');
        $table->text('spec_text')->nullable()->after('sku_code');
        $table->unsignedBigInteger('unit_id')->nullable()->after('spec_text');
        $table->string('unit_name', 64)->nullable()->after('unit_id');
    }

    private function upgradePurchaseOrderItems(): void
    {
        if (! Schema::hasTable('dberp_purchase_order_items') || Schema::hasColumn('dberp_purchase_order_items', 'product_sku_id')) {
            return;
        }

        Schema::table('dberp_purchase_order_items', function (Blueprint $table): void {
            $this->documentSnapshotColumns($table);
            $table->index(['product_sku_id'], 'idx_dberp_po_item_sku');
        });

        foreach (DB::table('dberp_purchase_order_items')->orderBy('id')->cursor() as $row) {
            $this->backfillDocumentItemRow('dberp_purchase_order_items', (int) $row->id, (int) $row->product_id);
        }

        Schema::table('dberp_purchase_order_items', function (Blueprint $table): void {
            $table->foreign('product_sku_id')->references('id')->on('dberp_product_skus')->restrictOnDelete();
        });
    }

    private function upgradePurchaseReceiptOrderItems(): void
    {
        if (! Schema::hasTable('dberp_purchase_receipt_order_items') || Schema::hasColumn('dberp_purchase_receipt_order_items', 'product_sku_id')) {
            return;
        }

        Schema::table('dberp_purchase_receipt_order_items', function (Blueprint $table): void {
            $this->documentSnapshotColumns($table);
            $table->index(['product_sku_id'], 'idx_dberp_pr_item_sku');
        });

        foreach (DB::table('dberp_purchase_receipt_order_items')->orderBy('id')->cursor() as $row) {
            $poItemId = (int) ($row->purchase_order_item_id ?? 0);
            if ($poItemId > 0) {
                $po = DB::table('dberp_purchase_order_items')->where('id', $poItemId)->first();
                if ($po !== null && $po->product_sku_id !== null) {
                    $snap = $this->snapshotFromSkuId((int) $po->product_sku_id, (int) $row->product_id);
                    DB::table('dberp_purchase_receipt_order_items')->where('id', $row->id)->update($snap);

                    continue;
                }
            }

            $this->backfillDocumentItemRow('dberp_purchase_receipt_order_items', (int) $row->id, (int) $row->product_id);
        }

        Schema::table('dberp_purchase_receipt_order_items', function (Blueprint $table): void {
            $table->foreign('product_sku_id')->references('id')->on('dberp_product_skus')->restrictOnDelete();
        });
    }

    private function upgradeSalesOrderItems(): void
    {
        if (! Schema::hasTable('dberp_sales_order_items') || Schema::hasColumn('dberp_sales_order_items', 'product_sku_id')) {
            return;
        }

        Schema::table('dberp_sales_order_items', function (Blueprint $table): void {
            $this->documentSnapshotColumns($table);
            $table->index(['product_sku_id'], 'idx_dberp_so_item_sku');
        });

        foreach (DB::table('dberp_sales_order_items')->orderBy('id')->cursor() as $row) {
            $this->backfillDocumentItemRow('dberp_sales_order_items', (int) $row->id, (int) $row->product_id);
        }

        Schema::table('dberp_sales_order_items', function (Blueprint $table): void {
            $table->foreign('product_sku_id')->references('id')->on('dberp_product_skus')->restrictOnDelete();
        });
    }

    private function upgradeSalesOutboundOrderItems(): void
    {
        if (! Schema::hasTable('dberp_sales_outbound_order_items') || Schema::hasColumn('dberp_sales_outbound_order_items', 'product_sku_id')) {
            return;
        }

        Schema::table('dberp_sales_outbound_order_items', function (Blueprint $table): void {
            $this->documentSnapshotColumns($table);
            $table->index(['product_sku_id'], 'idx_dberp_sout_item_sku');
        });

        foreach (DB::table('dberp_sales_outbound_order_items')->orderBy('id')->cursor() as $row) {
            $soItemId = (int) ($row->sales_order_item_id ?? 0);
            if ($soItemId > 0) {
                $so = DB::table('dberp_sales_order_items')->where('id', $soItemId)->first();
                if ($so !== null && $so->product_sku_id !== null) {
                    $snap = $this->snapshotFromSkuId((int) $so->product_sku_id, (int) $row->product_id);
                    DB::table('dberp_sales_outbound_order_items')->where('id', $row->id)->update($snap);

                    continue;
                }
            }

            $this->backfillDocumentItemRow('dberp_sales_outbound_order_items', (int) $row->id, (int) $row->product_id);
        }

        Schema::table('dberp_sales_outbound_order_items', function (Blueprint $table): void {
            $table->foreign('product_sku_id')->references('id')->on('dberp_product_skus')->restrictOnDelete();
        });
    }

    /**
     * @return array<string, mixed>
     */
    private function snapshotFromSkuId(int $skuId, int $expectedProductId): array
    {
        $sku = DberpProductSku::query()->with(['product.unit'])->find($skuId);
        if (! $sku instanceof DberpProductSku || (int) $sku->product_id !== $expectedProductId) {
            return $this->snapshotPayloadForProduct($expectedProductId);
        }

        $unit = $sku->resolveEffectiveUnit();

        return [
            'product_sku_id' => $sku->getKey(),
            'product_code' => (string) $sku->product->code,
            'product_name' => (string) $sku->product->name,
            'sku_code' => (string) $sku->sku_code,
            'spec_text' => $sku->spec_text !== null && $sku->spec_text !== '' ? (string) $sku->spec_text : null,
            'unit_id' => $unit !== null ? $unit->getKey() : null,
            'unit_name' => $unit !== null ? (string) $unit->name : null,
        ];
    }

    /**
     * @return array<string, mixed>
     */
    private function snapshotPayloadForProduct(int $productId): array
    {
        $skuId = $this->resolveDefaultSkuIdForProduct($productId);

        return $this->snapshotFromSkuId($skuId, $productId);
    }

    private function backfillDocumentItemRow(string $table, int $id, int $productId): void
    {
        DB::table($table)->where('id', $id)->update($this->snapshotPayloadForProduct($productId));
    }

    private function resolveDefaultSkuIdForProduct(int $productId): int
    {
        $skuId = DB::table('dberp_product_skus')
            ->where('product_id', $productId)
            ->orderByDesc('is_default')
            ->orderBy('sort_order')
            ->orderBy('id')
            ->value('id');

        if ($skuId !== null) {
            return (int) $skuId;
        }

        $product = DberpProduct::query()->find($productId);
        if ($product === null) {
            throw new RuntimeException("Missing product id {$productId} while migrating SKU backfill.");
        }

        return (int) DberpProductSku::createDefaultForProduct($product)->getKey();
    }
};
