<?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
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        if (!Schema::hasTable('departments')) {
            return;
        }

        $rows = DB::table('departments')
            ->select('id', 'dealer_id', 'name', 'code')
            ->orderBy('dealer_id')
            ->orderBy('id')
            ->get();

        $seenByDealer = [];

        foreach ($rows as $row) {
            $dealerId = (int) $row->dealer_id;
            $code = $this->normalizeCode($row->code, $row->name, (int) $row->id);

            $seenByDealer[$dealerId] ??= [];
            if (in_array($code, $seenByDealer[$dealerId], true)) {
                $code = $code.'_'.(int) $row->id;
            }

            $seenByDealer[$dealerId][] = $code;

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

        if ($this->isMysql()) {
            DB::statement('ALTER TABLE departments MODIFY code VARCHAR(255) NOT NULL');
            DB::statement('ALTER TABLE departments MODIFY dealer_id BIGINT UNSIGNED NOT NULL');
        }

        if (!$this->indexExists('departments', 'departments_dealer_id_code_unique')) {
            Schema::table('departments', function (Blueprint $table) {
                $table->unique(['dealer_id', 'code']);
            });
        }

        if (!$this->foreignKeyExists('departments', 'departments_dealer_id_foreign') && Schema::hasTable('dealers')) {
            Schema::table('departments', function (Blueprint $table) {
                $table->foreign('dealer_id')
                    ->references('id')
                    ->on('dealers')
                    ->onDelete('cascade');
            });
        }

        if (
            Schema::hasTable('users') &&
            Schema::hasColumn('users', 'department_id') &&
            Schema::hasTable('departments') &&
            !$this->foreignKeyExists('users', 'users_department_id_foreign')
        ) {
            Schema::table('users', function (Blueprint $table) {
                $table->foreign('department_id')
                    ->references('id')
                    ->on('departments')
                    ->nullOnDelete();
            });
        }
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // No destructive rollback for hardening migration.
    }

    private function normalizeCode(?string $code, ?string $name, int $id): string
    {
        $base = trim((string) $code);
        if ($base === '') {
            $base = trim((string) $name);
        }

        $base = strtolower($base);
        $base = str_replace(['-', ' '], '_', $base);
        $base = preg_replace('/[^a-z0-9_]/', '', $base) ?? '';
        $base = trim($base, '_');

        if ($base === '') {
            return 'dept_'.$id;
        }

        return $base;
    }

    private function isMysql(): bool
    {
        return DB::getDriverName() === 'mysql';
    }

    private function indexExists(string $table, string $indexName): bool
    {
        if (!$this->isMysql()) {
            return false;
        }

        return DB::table('information_schema.statistics')
            ->where('table_schema', DB::getDatabaseName())
            ->where('table_name', $table)
            ->where('index_name', $indexName)
            ->exists();
    }

    private function foreignKeyExists(string $table, string $constraintName): bool
    {
        if (!$this->isMysql()) {
            return false;
        }

        return DB::table('information_schema.table_constraints')
            ->where('constraint_schema', DB::getDatabaseName())
            ->where('table_name', $table)
            ->where('constraint_name', $constraintName)
            ->where('constraint_type', 'FOREIGN KEY')
            ->exists();
    }
};
