<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     * Mengubah Activity ID dari auto increment ke UUID
     * Alasan: Mencegah deteksi backdate berdasarkan urutan ID
     * Jika banyak karyawan backdate bersamaan, ID auto increment akan terlihat mencolok
     */
    public function up(): void
    {
        // Step 1: Tambah kolom uuid baru
        Schema::table('activities', function (Blueprint $table) {
            $table->uuid('uuid')->nullable()->after('id');
        });

        // Step 2: Generate UUID untuk semua record yang ada
        DB::table('activities')->orderBy('id')->each(function ($activity) {
            DB::table('activities')
                ->where('id', $activity->id)
                ->update(['uuid' => (string) Str::uuid()]);
        });

        // Step 3: Make UUID not nullable
        DB::statement('ALTER TABLE activities MODIFY uuid CHAR(36) NOT NULL');

        // Step 4: Drop foreign key pada attendance_id (jika ada)
        $foreignKeys = DB::select("
            SELECT CONSTRAINT_NAME
            FROM information_schema.TABLE_CONSTRAINTS
            WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
            AND TABLE_SCHEMA = DATABASE()
            AND TABLE_NAME = 'activities'
        ");

        foreach ($foreignKeys as $fk) {
            DB::statement("ALTER TABLE activities DROP FOREIGN KEY {$fk->CONSTRAINT_NAME}");
        }

        // Step 5: Drop indexes (jika ada)
        try {
            DB::statement('ALTER TABLE activities DROP INDEX activities_user_id_date_index');
        } catch (\Exception $e) {
            // Index mungkin tidak ada, skip
        }

        try {
            DB::statement('ALTER TABLE activities DROP INDEX activities_attendance_id_index');
        } catch (\Exception $e) {
            // Index mungkin tidak ada, skip
        }

        // Step 6: Remove AUTO_INCREMENT from id column
        DB::statement('ALTER TABLE activities MODIFY id BIGINT UNSIGNED NOT NULL');

        // Step 7: Drop primary key
        DB::statement('ALTER TABLE activities DROP PRIMARY KEY');

        // Step 8: Drop old id column
        Schema::table('activities', function (Blueprint $table) {
            $table->dropColumn('id');
        });

        // Step 9: Rename uuid to id
        Schema::table('activities', function (Blueprint $table) {
            $table->renameColumn('uuid', 'id');
        });

        // Step 10: Add primary key on new UUID id
        DB::statement('ALTER TABLE activities ADD PRIMARY KEY (id)');

        // Step 11: Recreate indexes
        Schema::table('activities', function (Blueprint $table) {
            $table->index(['user_id', 'date'], 'activities_user_id_date_index');
        });

        // Step 11b: Add attendance_id index if not exists (may exist from previous migration)
        try {
            Schema::table('activities', function (Blueprint $table) {
                $table->index(['attendance_id'], 'activities_attendance_id_index');
            });
        } catch (\Exception $e) {
            // Index already exists, continue
        }

        // Step 12: Recreate foreign key untuk attendance_id (sekarang UUID)
        // Both activities.attendance_id and attendances.id are now char(36)
        Schema::table('activities', function (Blueprint $table) {
            $table->foreign('attendance_id')
                ->references('id')
                ->on('attendances')
                ->cascadeOnDelete();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        throw new \Exception('This migration cannot be automatically reversed. Please restore from backup if needed.');
    }
};
