<?php

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

class UpdateUsersTableForTeacherIdUniqueness extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        // Ensure the users table uses the InnoDB storage engine
        DB::statement('ALTER TABLE users ENGINE = InnoDB');

        // Add the generated column and unique index
        Schema::table('users', function (Blueprint $table) {
            // First, add the generated column without the unique index
            $table->integer('teacher_id_unique')->virtualAs('CASE WHEN teacher_id = 0 THEN NULL ELSE teacher_id END')->nullable();

            // Next, add the unique index
            $table->unique('teacher_id_unique', 'idx_teacher_id_unique');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('users', function (Blueprint $table) {
            // Drop the unique index first
            $table->dropUnique('idx_teacher_id_unique');

            // Then drop the generated column
            $table->dropColumn('teacher_id_unique');
        });

        // Optionally, you can revert the storage engine to MyISAM or any other if needed
        // DB::statement('ALTER TABLE users ENGINE = MyISAM');
    }
}
