<?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('companies', 'short_name')) {
            $rows = DB::table('companies')
                ->select('id', 'short_name')
                ->whereNotNull('short_name')
                ->where('short_name', '!=', '')
                ->orderBy('id')
                ->get();

            $seen = [];
            foreach ($rows as $row) {
                $key = strtolower(trim((string) $row->short_name));
                if ($key === '') {
                    continue;
                }
                if (! isset($seen[$key])) {
                    $seen[$key] = true;
                    if ((string) $row->short_name !== $key) {
                        DB::table('companies')->where('id', $row->id)->update(['short_name' => $key]);
                    }

                    continue;
                }
                $unique = $key.'-'.$row->id;
                DB::table('companies')->where('id', $row->id)->update(['short_name' => $unique]);
                $seen[strtolower($unique)] = true;
            }
        }

        if (! $this->indexExists('companies', 'companies_short_name_unique')) {
            Schema::table('companies', function (Blueprint $table) {
                $table->unique('short_name', 'companies_short_name_unique');
            });
        }

        if ($this->indexExists('users', 'users_email_unique')) {
            Schema::table('users', function (Blueprint $table) {
                $table->dropUnique('users_email_unique');
            });
        }

        if (! $this->indexExists('users', 'users_company_id_email_unique')) {
            Schema::table('users', function (Blueprint $table) {
                $table->unique(['company_id', 'email'], 'users_company_id_email_unique');
            });
        }
    }

    public function down(): void
    {
        if ($this->indexExists('users', 'users_company_id_email_unique')) {
            Schema::table('users', function (Blueprint $table) {
                $table->dropUnique('users_company_id_email_unique');
            });
        }

        if (! $this->indexExists('users', 'users_email_unique')) {
            Schema::table('users', function (Blueprint $table) {
                $table->unique('email', 'users_email_unique');
            });
        }

        if ($this->indexExists('companies', 'companies_short_name_unique')) {
            Schema::table('companies', function (Blueprint $table) {
                $table->dropUnique('companies_short_name_unique');
            });
        }
    }

    private function indexExists(string $table, string $indexName): bool
    {
        $database = Schema::getConnection()->getDatabaseName();
        $result = DB::selectOne(
            'SELECT COUNT(*) AS aggregate FROM information_schema.statistics WHERE table_schema = ? AND table_name = ? AND index_name = ?',
            [$database, $table, $indexName]
        );

        return (int) ($result->aggregate ?? 0) > 0;
    }
};