<?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
    {
        if (! Schema::hasTable('user_profiles')) {
            return;
        }

        Schema::table('user_profiles', function (Blueprint $table) {
            // Add avatar metadata to support the avatar pipeline
            if (! Schema::hasColumn('user_profiles', 'avatar_hash')) {
                $table->string('avatar_hash', 64)->nullable()->after('avatar');
            }
            if (! Schema::hasColumn('user_profiles', 'avatar_mime')) {
                $table->string('avatar_mime', 80)->nullable()->after('avatar_hash');
            }
            if (! Schema::hasColumn('user_profiles', 'avatar_updated_at')) {
                $table->timestamp('avatar_updated_at')->nullable()->after('avatar_mime');
            }
        });

        // Ensure `user_id` is indexed/unique and add a FK to `users(id)` when possible
        try {
            // Add unique index on user_id if it doesn't exist
            DB::statement('ALTER TABLE `user_profiles` ADD UNIQUE INDEX `user_profiles_user_id_unique` (`user_id`)');
        } catch (\Throwable $e) {
            // ignore if index exists or operation unsupported
        }

        try {
            // Add foreign key constraint to users.id if not present
            DB::statement('ALTER TABLE `user_profiles` ADD CONSTRAINT `user_profiles_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE');
        } catch (\Throwable $e) {
            // ignore if users table missing or constraint already present
        }
    }

    public function down(): void
    {
        if (! Schema::hasTable('user_profiles')) {
            return;
        }

        Schema::table('user_profiles', function (Blueprint $table) {
            if (Schema::hasColumn('user_profiles', 'avatar_updated_at')) {
                $table->dropColumn('avatar_updated_at');
            }
            if (Schema::hasColumn('user_profiles', 'avatar_mime')) {
                $table->dropColumn('avatar_mime');
            }
            if (Schema::hasColumn('user_profiles', 'avatar_hash')) {
                $table->dropColumn('avatar_hash');
            }
        });

        try {
            DB::statement('ALTER TABLE `user_profiles` DROP FOREIGN KEY `user_profiles_user_id_foreign`');
        } catch (\Throwable $e) {
            // ignore
        }

        try {
            DB::statement('ALTER TABLE `user_profiles` DROP INDEX `user_profiles_user_id_unique`');
        } catch (\Throwable $e) {
            // ignore
        }
    }
};
