<?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
    {
        $this->dropFarmForeignKeyIfExists();

        Schema::table('users', function (Blueprint $table) {
            $table->unsignedInteger('farm_id')->nullable()->change();
        });

        DB::table('users')->where('farm_id', 0)->update(['farm_id' => null]);
        DB::table('users')
            ->whereNotNull('farm_id')
            ->whereNotIn('farm_id', DB::table('farms')->select('id'))
            ->update(['farm_id' => null]);

        $this->addFarmForeignKeyIfMissing();
    }

    public function down(): void
    {
        $this->dropFarmForeignKeyIfExists();

        $fallbackFarmId = DB::table('farms')->value('id');

        if (!$fallbackFarmId) {
            throw new RuntimeException('Cannot rollback nullable user farm_id because no fallback farm exists.');
        }

        DB::table('users')->whereNull('farm_id')->update(['farm_id' => $fallbackFarmId]);

        Schema::table('users', function (Blueprint $table) {
            $table->unsignedInteger('farm_id')->nullable(false)->change();
        });

        $this->addFarmForeignKeyIfMissing();
    }

    private function dropFarmForeignKeyIfExists(): void
    {
        if (!$this->farmForeignKeyExists()) {
            return;
        }

        DB::statement('ALTER TABLE users DROP FOREIGN KEY users_farm_id_foreign');
    }

    private function addFarmForeignKeyIfMissing(): void
    {
        if ($this->farmForeignKeyExists()) {
            return;
        }

        Schema::table('users', function (Blueprint $table) {
            $table->foreign('farm_id')->references('id')->on('farms');
        });
    }

    private function farmForeignKeyExists(): bool
    {
        return (bool) DB::table('information_schema.KEY_COLUMN_USAGE')
            ->whereRaw('TABLE_SCHEMA = DATABASE()')
            ->where('TABLE_NAME', 'users')
            ->where('COLUMN_NAME', 'farm_id')
            ->where('CONSTRAINT_NAME', 'users_farm_id_foreign')
            ->where('REFERENCED_TABLE_NAME', 'farms')
            ->exists();
    }
};
