<?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.
     * Changes attendance ID from auto-increment bigint to UUID
     *
     * Tables with foreign key to attendances.id:
     * - activities (already char(36) UUID)
     * - activity_records (bigint)
     * - overtimes (bigint)
     * - attendance_summaries (bigint)
     */
    public function up(): void
    {
        // Step 1: Add UUID column to attendances
        Schema::table('attendances', function (Blueprint $table) {
            $table->uuid('uuid')->nullable()->after('id');
        });

        // Step 2: Generate UUIDs for existing records
        DB::table('attendances')->orderBy('id')->each(function ($attendance) {
            DB::table('attendances')
                ->where('id', $attendance->id)
                ->update(['uuid' => (string) Str::uuid()]);
        });

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

        // Step 4: Handle activities table - add UUID column, update values, then swap columns
        // activities.attendance_id is originally bigint, needs to become char(36)
        Schema::table('activities', function (Blueprint $table) {
            $table->uuid('attendance_uuid')->nullable()->after('attendance_id');
        });

        // Update activities.attendance_uuid to match the new attendance UUIDs
        DB::statement('
            UPDATE activities act
            INNER JOIN attendances att ON act.attendance_id = att.id
            SET act.attendance_uuid = att.uuid
        ');

        // Step 5: Add UUID column to activity_records and update
        Schema::table('activity_records', function (Blueprint $table) {
            $table->uuid('attendance_uuid')->nullable()->after('attendance_id');
        });

        DB::statement('
            UPDATE activity_records ar
            INNER JOIN attendances att ON ar.attendance_id = att.id
            SET ar.attendance_uuid = att.uuid
        ');

        // Step 6: Add UUID column to overtimes and update
        Schema::table('overtimes', function (Blueprint $table) {
            $table->uuid('attendance_uuid')->nullable()->after('attendance_id');
        });

        DB::statement('
            UPDATE overtimes o
            INNER JOIN attendances att ON o.attendance_id = att.id
            SET o.attendance_uuid = att.uuid
        ');

        // Step 6b: Add UUID column to attendance_summaries and update
        Schema::table('attendance_summaries', function (Blueprint $table) {
            $table->uuid('attendance_uuid')->nullable()->after('attendance_id');
        });

        DB::statement('
            UPDATE attendance_summaries ats
            INNER JOIN attendances att ON ats.attendance_id = att.id
            SET ats.attendance_uuid = att.uuid
        ');

        // Step 7: Drop all foreign keys referencing attendances
        $this->dropForeignKeyIfExists('activity_records', 'audit_logs_attendance_id_foreign');
        $this->dropForeignKeyIfExists('overtimes', 'overtimes_attendance_id_foreign');
        $this->dropForeignKeyIfExists('attendance_summaries', 'attendance_summaries_attendance_id_foreign');

        // Drop any foreign key on activities (if exists)
        $activitiesFks = DB::select("
            SELECT CONSTRAINT_NAME
            FROM information_schema.TABLE_CONSTRAINTS
            WHERE TABLE_SCHEMA = DATABASE()
            AND TABLE_NAME = 'activities'
            AND CONSTRAINT_TYPE = 'FOREIGN KEY'
            AND CONSTRAINT_NAME LIKE '%attendance%'
        ");
        foreach ($activitiesFks as $fk) {
            DB::statement("ALTER TABLE activities DROP FOREIGN KEY {$fk->CONSTRAINT_NAME}");
        }

        // Step 8: Drop old attendance_id columns and rename uuid columns

        // activity_records
        Schema::table('activity_records', function (Blueprint $table) {
            $table->dropColumn('attendance_id');
        });
        Schema::table('activity_records', function (Blueprint $table) {
            $table->renameColumn('attendance_uuid', 'attendance_id');
        });

        // overtimes
        Schema::table('overtimes', function (Blueprint $table) {
            $table->dropColumn('attendance_id');
        });
        Schema::table('overtimes', function (Blueprint $table) {
            $table->renameColumn('attendance_uuid', 'attendance_id');
        });

        // attendance_summaries
        Schema::table('attendance_summaries', function (Blueprint $table) {
            $table->dropColumn('attendance_id');
        });
        Schema::table('attendance_summaries', function (Blueprint $table) {
            $table->renameColumn('attendance_uuid', 'attendance_id');
        });

        // activities - drop index first, then drop old column and rename uuid column
        try {
            DB::statement('ALTER TABLE activities DROP INDEX activities_attendance_id_index');
        } catch (\Exception $e) {
            // Index doesn't exist, continue
        }
        Schema::table('activities', function (Blueprint $table) {
            $table->dropColumn('attendance_id');
        });
        Schema::table('activities', function (Blueprint $table) {
            $table->renameColumn('attendance_uuid', 'attendance_id');
        });

        // Step 9: Now modify attendances - remove auto_increment from id
        DB::statement('ALTER TABLE attendances MODIFY id BIGINT UNSIGNED NOT NULL');

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

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

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

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

        // Step 14: Add indexes on related tables
        Schema::table('activities', function (Blueprint $table) {
            $table->index('attendance_id');
        });

        Schema::table('activity_records', function (Blueprint $table) {
            $table->index('attendance_id');
        });

        Schema::table('overtimes', function (Blueprint $table) {
            $table->index('attendance_id');
        });

        Schema::table('attendance_summaries', function (Blueprint $table) {
            $table->index('attendance_id');
        });
    }

    /**
     * Helper to drop foreign key if it exists
     */
    private function dropForeignKeyIfExists(string $table, string $constraintName): void
    {
        try {
            DB::statement("ALTER TABLE {$table} DROP FOREIGN KEY {$constraintName}");
        } catch (\Exception $e) {
            // Foreign key doesn't exist, continue
        }
    }

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