<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Str;

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('hr_salary_structures', function (Blueprint $table): void {
            $table->string('code')->nullable()->after('id');
            $table->text('description')->nullable()->after('name');
        });

        $this->backfillCodes();

        Schema::table('hr_salary_structures', function (Blueprint $table): void {
            $table->unique(['tenant_id', 'code']);
        });

        Schema::table('hr_salary_structure_components', function (Blueprint $table): void {
            $table->string('code')->nullable()->after('structure_id');
            $table->unsignedSmallInteger('sort_order')->default(0)->after('is_taxable');
        });
    }

    public function down(): void
    {
        Schema::table('hr_salary_structure_components', function (Blueprint $table): void {
            $table->dropColumn(['code', 'sort_order']);
        });

        // The composite unique index is currently InnoDB's only supporting index for the
        // tenant_id foreign key, so a plain index must exist before it can be dropped.
        Schema::table('hr_salary_structures', function (Blueprint $table): void {
            $table->index('tenant_id');
        });

        Schema::table('hr_salary_structures', function (Blueprint $table): void {
            $table->dropUnique(['tenant_id', 'code']);
            $table->dropColumn(['code', 'description']);
        });
    }

    /**
     * Fill missing codes with a unique, per-tenant slug derived from the name.
     */
    private function backfillCodes(): void
    {
        $rows = DB::table('hr_salary_structures')
            ->whereNull('code')
            ->orderBy('id')
            ->get(['id', 'tenant_id', 'name']);

        $usedByTenant = [];

        foreach ($rows as $row) {
            $tenantId = (int) $row->tenant_id;
            $usedByTenant[$tenantId] ??= DB::table('hr_salary_structures')
                ->where('tenant_id', $tenantId)
                ->whereNotNull('code')
                ->pluck('code')
                ->flip()
                ->all();

            $base = Str::limit(Str::slug((string) $row->name), 48, '') ?: 'structure';
            $code = $base;
            $suffix = 1;

            while (isset($usedByTenant[$tenantId][$code])) {
                $code = $base.'-'.$suffix++;
            }

            $usedByTenant[$tenantId][$code] = true;

            DB::table('hr_salary_structures')->where('id', $row->id)->update(['code' => $code]);
        }
    }
};
