<?php

use App\Support\SystemReferenceGenerator;
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
    {
        Schema::table('departments', function (Blueprint $table) {
            $table->string('code', 64)->nullable()->after('name');
            $table->unique(['tenant_id', 'code'], 'departments_tenant_code_uq');
        });

        Schema::table('positions', function (Blueprint $table) {
            $table->string('code', 64)->nullable()->after('title');
            $table->unique(['tenant_id', 'code'], 'positions_tenant_code_uq');
        });

        Schema::table('goods_receipts', function (Blueprint $table) {
            $table->string('receipt_number', 64)->nullable()->after('purchase_order_id');
            $table->unique(['tenant_id', 'receipt_number'], 'gr_tenant_receipt_num_uq');
        });

        Schema::table('inventory_stock_transfers', function (Blueprint $table) {
            $table->string('transfer_number', 64)->nullable()->after('status');
            $table->unique(['tenant_id', 'transfer_number'], 'inv_xfer_tenant_num_uq');
        });

        Schema::table('sales_orders', function (Blueprint $table) {
            $table->string('order_number', 64)->nullable()->after('status');
            $table->unique(['tenant_id', 'order_number'], 'sales_orders_tenant_num_uq');
        });

        $this->backfill('departments', 'code', SystemReferenceGenerator::DEPARTMENT);
        $this->backfill('positions', 'code', SystemReferenceGenerator::POSITION);
        $this->backfill('goods_receipts', 'receipt_number', SystemReferenceGenerator::GOODS_RECEIPT);
        $this->backfill('inventory_stock_transfers', 'transfer_number', SystemReferenceGenerator::TRANSFER);
        $this->backfill('sales_orders', 'order_number', SystemReferenceGenerator::SALES_ORDER);
        $this->backfill('purchase_orders', 'po_number', SystemReferenceGenerator::PURCHASE_ORDER);
    }

    public function down(): void
    {
        Schema::table('sales_orders', function (Blueprint $table) {
            $table->dropUnique('sales_orders_tenant_num_uq');
            $table->dropColumn('order_number');
        });

        Schema::table('inventory_stock_transfers', function (Blueprint $table) {
            $table->dropUnique('inv_xfer_tenant_num_uq');
            $table->dropColumn('transfer_number');
        });

        Schema::table('goods_receipts', function (Blueprint $table) {
            $table->dropUnique('gr_tenant_receipt_num_uq');
            $table->dropColumn('receipt_number');
        });

        Schema::table('positions', function (Blueprint $table) {
            $table->dropUnique('positions_tenant_code_uq');
            $table->dropColumn('code');
        });

        Schema::table('departments', function (Blueprint $table) {
            $table->dropUnique('departments_tenant_code_uq');
            $table->dropColumn('code');
        });
    }

    private function backfill(string $table, string $column, string $prefix): void
    {
        DB::table($table)
            ->orderBy('id')
            ->select(['id', $column])
            ->lazyById()
            ->each(function (object $row) use ($table, $column, $prefix): void {
                $current = $row->{$column} ?? null;
                if ($current !== null && $current !== '') {
                    return;
                }

                DB::table($table)
                    ->where('id', $row->id)
                    ->update([$column => SystemReferenceGenerator::format($prefix, (int) $row->id)]);
            });
    }
};
