<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    private array $targetColumns = [
        'students' => ['student_id', 'teacher_id'],
        'teachers' => ['teacher_id', 'user_id'],
        'users' => ['user_id'],
    ];

    /**
     * Run the migrations.
     */
    public function up(): void
    {
        if (DB::getDriverName() !== 'sqlite') {
            $this->updateColumnsCollation('utf8mb4_unicode_ci');
        }
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        if (DB::getDriverName() !== 'sqlite') {
            $this->updateColumnsCollation('utf8mb4_general_ci');
        }
    }

    private function updateColumnsCollation(string $collation): void
    {
        $database = DB::getDatabaseName();

        $columns = DB::table('information_schema.columns')
            ->select('TABLE_NAME', 'COLUMN_NAME', 'COLUMN_TYPE', 'IS_NULLABLE', 'COLUMN_DEFAULT', 'EXTRA')
            ->where('TABLE_SCHEMA', $database)
            ->where(function ($query) {
                foreach ($this->targetColumns as $table => $cols) {
                    $query->orWhere(function ($q) use ($table, $cols) {
                        $q->where('TABLE_NAME', $table)->whereIn('COLUMN_NAME', $cols);
                    });
                }
            })
            ->get();

        foreach ($columns as $column) {
            $nullClause = $column->IS_NULLABLE === 'YES' ? 'NULL' : 'NOT NULL';

            if ($column->COLUMN_DEFAULT === null) {
                $defaultClause = '';
            } elseif ($column->COLUMN_DEFAULT === 'CURRENT_TIMESTAMP') {
                $defaultClause = "DEFAULT CURRENT_TIMESTAMP";
            } else {
                $escaped = addslashes($column->COLUMN_DEFAULT);
                $defaultClause = "DEFAULT '{$escaped}'";
            }

            $extraClause = $column->EXTRA ? strtoupper($column->EXTRA) : '';

            $sql = sprintf(
                'ALTER TABLE `%s` MODIFY `%s` %s CHARACTER SET utf8mb4 COLLATE %s %s %s %s',
                $column->TABLE_NAME,
                $column->COLUMN_NAME,
                $column->COLUMN_TYPE,
                $collation,
                $nullClause,
                $defaultClause,
                $extraClause
            );

            DB::statement(trim($sql));
        }
    }
};
