<?php

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
    {
        if (Schema::hasColumn('users', 'username')) {
            $this->normalizeExistingUsernames();
        }

        Schema::table('users', function (Blueprint $table) {
            if (Schema::hasColumn('users', 'username')) {
                $table->string('username', 20)->nullable()->change();
            }

            if (! Schema::hasColumn('users', 'username_changed_at')) {
                $table->timestamp('username_changed_at')->nullable()->after('username');
            }
        });

        $sm = Schema::getConnection()->getSchemaBuilder();
        $indexes = $sm->getIndexes('users');
        $hasUsernameUnique = collect($indexes)->contains(function ($index): bool {
            $columns = array_map('strtolower', (array) ($index['columns'] ?? []));
            return (bool) ($index['unique'] ?? false) && $columns === ['username'];
        });

        if (! $hasUsernameUnique && Schema::hasColumn('users', 'username')) {
            Schema::table('users', function (Blueprint $table) {
                $table->unique('username', 'users_username_unique');
            });
        }
    }

    public function down(): void
    {
        Schema::table('users', function (Blueprint $table) {
            if (Schema::hasColumn('users', 'username_changed_at')) {
                $table->dropColumn('username_changed_at');
            }
            if (Schema::hasColumn('users', 'username')) {
                $table->string('username', 80)->nullable()->change();
            }
        });
    }

    private function normalizeExistingUsernames(): void
    {
        $rows = DB::table('users')
            ->select('id', 'username')
            ->whereNotNull('username')
            ->orderBy('id')
            ->get();

        if ($rows->isEmpty()) {
            return;
        }

        $resolved = [];
        $used = [];

        foreach ($rows as $row) {
            $raw = strtolower(trim((string) $row->username));
            $base = preg_replace('/[^a-z0-9_-]+/', '_', $raw) ?? '';
            $base = trim($base, '_-');
            if ($base === '') {
                $base = 'user' . (int) $row->id;
            }

            $base = substr($base, 0, 20);
            if ($base === '') {
                $base = 'user';
            }

            $candidate = $base;
            $suffix = 1;

            while (isset($used[$candidate])) {
                $suffixValue = (string) $suffix;
                $prefixLen = max(1, 20 - strlen($suffixValue));
                $candidate = substr($base, 0, $prefixLen) . $suffixValue;
                $suffix++;
            }

            $used[$candidate] = true;
            $resolved[(int) $row->id] = $candidate;
        }

        foreach ($rows as $row) {
            DB::table('users')
                ->where('id', (int) $row->id)
                ->update(['username' => 'tmpu' . (int) $row->id]);
        }

        foreach ($rows as $row) {
            $final = $resolved[(int) $row->id] ?? null;
            if ($final === null) {
                continue;
            }

            DB::table('users')
                ->where('id', (int) $row->id)
                ->update(['username' => $final]);
        }
    }
};
