<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Schema;

class EnsureAdmissionIdentifiersAreStrings extends Migration
{
    public function up()
    {
        if (Schema::hasTable('users')) {
            Schema::table('users', function (Blueprint $table) {
                if (!Schema::hasColumn('users', 'admission_number')) {
                    $table->string('admission_number', 50)->nullable()->after('email');
                }
            });

            $this->changeColumnToString('users', 'admission_number');
            $this->normalizeColumn('users', 'admission_number');
            $this->addUniqueIndex('users', ['admission_number'], 'users_admission_number_unique');
        }

        foreach ([
            ['sm_students', 'admission_no'],
            ['sm_student_promotions', 'admission_number'],
            ['exam_merit_positions', 'admission_no'],
            ['all_exam_wise_positions', 'admission_no'],
        ] as [$table, $column]) {
            $this->changeColumnToString($table, $column);
            $this->normalizeColumn($table, $column);
        }
    }

    public function down()
    {
        // Admission identifiers must remain strings to preserve alphanumeric values.
    }

    private function changeColumnToString($tableName, $columnName)
    {
        if (!Schema::hasTable($tableName) || !Schema::hasColumn($tableName, $columnName)) {
            return;
        }

        try {
            Schema::table($tableName, function (Blueprint $table) use ($columnName) {
                $table->string($columnName, 50)->nullable()->change();
            });
        } catch (\Throwable $e) {
            Log::warning('Unable to convert admission identifier to string.', [
                'table' => $tableName,
                'column' => $columnName,
                'error' => $e->getMessage(),
            ]);
        }
    }

    private function normalizeColumn($tableName, $columnName)
    {
        if (!Schema::hasTable($tableName) || !Schema::hasColumn($tableName, $columnName)) {
            return;
        }

        try {
            DB::table($tableName)
                ->whereNotNull($columnName)
                ->update([$columnName => DB::raw("UPPER(TRIM(`{$columnName}`))")]);
        } catch (\Throwable $e) {
            Log::warning('Unable to normalize admission identifier values.', [
                'table' => $tableName,
                'column' => $columnName,
                'error' => $e->getMessage(),
            ]);
        }
    }

    private function addUniqueIndex($tableName, array $columns, $indexName)
    {
        if (!Schema::hasTable($tableName) || $this->indexExists($tableName, $indexName)) {
            return;
        }

        try {
            Schema::table($tableName, function (Blueprint $table) use ($columns, $indexName) {
                $table->unique($columns, $indexName);
            });
        } catch (\Throwable $e) {
            Log::warning('Unable to add unique admission identifier index.', [
                'table' => $tableName,
                'index' => $indexName,
                'error' => $e->getMessage(),
            ]);
        }
    }

    private function indexExists($tableName, $indexName)
    {
        try {
            return count(DB::select("SHOW INDEX FROM `{$tableName}` WHERE Key_name = ?", [$indexName])) > 0;
        } catch (\Throwable $e) {
            return false;
        }
    }
}
