<?php

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

return new class extends Migration
{
    public function up(): void
    {
        // 1. Add jenis_sekolah to buku table
        if (!Schema::hasColumn('buku', 'jenis_sekolah')) {
            Schema::table('buku', function (Blueprint $table) {
                $table->string('jenis_sekolah', 20)->nullable()->after('darjah');
            });
        }

        // 2. Add jenis_sekolah to maklumat_buku_darjah
        if (!Schema::hasColumn('maklumat_buku_darjah', 'jenis_sekolah')) {
            Schema::table('maklumat_buku_darjah', function (Blueprint $table) {
                $table->string('jenis_sekolah', 20)->nullable()->after('kod_sekolah');
            });
        }

        // Drop foreign key first (it depends on the unique index), then swap unique index
        $hasOldKey = collect(DB::select("SHOW INDEX FROM maklumat_buku_darjah WHERE Key_name = 'mbd_user_tahun_darjah_kodbuku_unique'"))->isNotEmpty();
        if ($hasOldKey) {
            Schema::table('maklumat_buku_darjah', function (Blueprint $table) {
                $table->dropForeign('maklumat_buku_darjah_user_id_foreign');
            });

            Schema::table('maklumat_buku_darjah', function (Blueprint $table) {
                $table->dropUnique('mbd_user_tahun_darjah_kodbuku_unique');
                $table->unique(
                    ['user_id', 'tahun', 'darjah', 'kod_buku', 'jenis_sekolah'],
                    'mbd_user_tahun_darjah_kodbuku_jenis_unique'
                );
                $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            });
        }

        // 3. Add jenis_sekolah to pengesahan + update unique constraint
        if (!Schema::hasColumn('pengesahan', 'jenis_sekolah')) {
            Schema::table('pengesahan', function (Blueprint $table) {
                $table->string('jenis_sekolah', 20)->nullable()->after('kod_sekolah');
            });
        }

        $hasOldPengesahanKey = collect(DB::select("SHOW INDEX FROM pengesahan WHERE Key_name = 'pengesahan_user_id_tahun_unique'"))->isNotEmpty();
        if ($hasOldPengesahanKey) {
            Schema::table('pengesahan', function (Blueprint $table) {
                $table->dropForeign('pengesahan_user_id_foreign');
            });
            Schema::table('pengesahan', function (Blueprint $table) {
                $table->dropUnique('pengesahan_user_id_tahun_unique');
                $table->unique(
                    ['user_id', 'tahun', 'jenis_sekolah'],
                    'pengesahan_user_tahun_jenis_unique'
                );
                $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            });
        }

        // 4. Convert existing users.jenis_sekolah from plain string to JSON array
        $map = ['123' => 'SRA', '124' => 'SRAI', '125' => 'KAFAI'];

        DB::table('users')
            ->whereNotNull('jenis_sekolah')
            ->where('jenis_sekolah', '!=', '')
            ->orderBy('id')
            ->chunk(100, function ($users) use ($map) {
                foreach ($users as $user) {
                    $val = $user->jenis_sekolah;
                    // Skip if already JSON array
                    if (str_starts_with($val, '[')) {
                        continue;
                    }
                    $nama = $map[$val] ?? strtoupper($val);
                    DB::table('users')->where('id', $user->id)->update([
                        'jenis_sekolah' => json_encode([$nama]),
                    ]);
                }
            });
    }

    public function down(): void
    {
        // Revert users.jenis_sekolah from JSON to plain string
        DB::table('users')
            ->whereNotNull('jenis_sekolah')
            ->where('jenis_sekolah', '!=', '')
            ->orderBy('id')
            ->chunk(100, function ($users) {
                foreach ($users as $user) {
                    $val = $user->jenis_sekolah;
                    if (str_starts_with($val, '[')) {
                        $arr = json_decode($val, true);
                        DB::table('users')->where('id', $user->id)->update([
                            'jenis_sekolah' => $arr[0] ?? null,
                        ]);
                    }
                }
            });

        Schema::table('pengesahan', function (Blueprint $table) {
            $table->dropForeign('pengesahan_user_id_foreign');
            $table->dropUnique('pengesahan_user_tahun_jenis_unique');
            $table->unique(['user_id', 'tahun'], 'pengesahan_user_id_tahun_unique');
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->dropColumn('jenis_sekolah');
        });

        Schema::table('maklumat_buku_darjah', function (Blueprint $table) {
            $table->dropForeign('maklumat_buku_darjah_user_id_foreign');
            $table->dropUnique('mbd_user_tahun_darjah_kodbuku_jenis_unique');
            $table->unique(
                ['user_id', 'tahun', 'darjah', 'kod_buku'],
                'mbd_user_tahun_darjah_kodbuku_unique'
            );
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->dropColumn('jenis_sekolah');
        });

        Schema::table('buku', function (Blueprint $table) {
            $table->dropColumn('jenis_sekolah');
        });
    }
};
