<?php

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

return new class extends Migration
{
    public function up(): void
    {
        if (Schema::hasTable('resume_reports')) {
            if (! Schema::hasColumn('resume_reports', 'is_current')) {
                Schema::table('resume_reports', function (Blueprint $table): void {
                    $table->boolean('is_current')->default(true)->index()->after('user_id');
                });
            }

            DB::table('resume_reports')->update(['is_current' => true]);

            if (! $this->indexExists('resume_reports', 'resume_reports_user_id_index')) {
                Schema::table('resume_reports', function (Blueprint $table): void {
                    $table->index('user_id', 'resume_reports_user_id_index');
                });
            }

            if ($this->indexExists('resume_reports', 'resume_reports_user_id_unique')) {
                Schema::table('resume_reports', function (Blueprint $table): void {
                    $table->dropUnique('resume_reports_user_id_unique');
                });
            }
        }

        if (! Schema::hasTable('job_ai_assistances')) {
            Schema::create('job_ai_assistances', function (Blueprint $table): void {
                $table->id();
                $table->foreignId('user_id')->constrained()->cascadeOnDelete();
                $table->foreignId('imported_job_id')->constrained()->cascadeOnDelete();
                $table->string('type', 60)->index();
                $table->string('status', 40)->default('completed')->index();
                $table->longText('content')->nullable();
                $table->json('payload')->nullable();
                $table->text('failure_reason')->nullable();
                $table->timestamp('generated_at')->nullable()->index();
                $table->timestamps();

                $table->unique(['user_id', 'imported_job_id', 'type']);
                $table->index(['imported_job_id', 'type']);
            });
        }

        if (! Schema::hasTable('job_notes')) {
            Schema::create('job_notes', function (Blueprint $table): void {
                $table->id();
                $table->foreignId('user_id')->constrained()->cascadeOnDelete();
                $table->foreignId('imported_job_id')->constrained()->cascadeOnDelete();
                $table->text('body')->nullable();
                $table->timestamps();

                $table->unique(['user_id', 'imported_job_id']);
            });
        }

        if (! Schema::hasTable('application_follow_up_reminders')) {
            Schema::create('application_follow_up_reminders', function (Blueprint $table): void {
                $table->id();
                $table->foreignId('user_id')->constrained()->cascadeOnDelete();
                $table->foreignId('application_id')->constrained()->cascadeOnDelete();
                $table->date('due_on')->index();
                $table->string('status', 40)->default('pending')->index();
                $table->timestamp('sent_at')->nullable()->index();
                $table->text('failure_reason')->nullable();
                $table->timestamps();

                $table->unique(['application_id', 'due_on']);
                $table->index(['user_id', 'due_on', 'status']);
            });
        }

        if (Schema::hasTable('platform_settings')) {
            foreach ([
                ['ai_features', 'enable_cover_letter_generator'],
                ['ai_features', 'enable_interview_preparation'],
                ['notifications', 'application_reminder_email_enabled'],
            ] as [$group, $key]) {
                DB::table('platform_settings')->updateOrInsert(
                    ['group' => $group, 'key' => $key],
                    [
                        'value' => 'true',
                        'type' => 'boolean',
                        'is_encrypted' => false,
                        'is_public' => false,
                        'description' => null,
                        'created_at' => now(),
                        'updated_at' => now(),
                    ]
                );
            }
        }
    }

    public function down(): void
    {
        Schema::dropIfExists('application_follow_up_reminders');
        Schema::dropIfExists('job_notes');
        Schema::dropIfExists('job_ai_assistances');

        if (Schema::hasTable('resume_reports') && Schema::hasColumn('resume_reports', 'is_current')) {
            Schema::table('resume_reports', function (Blueprint $table): void {
                $table->dropColumn('is_current');
            });
        }
    }

    private function indexExists(string $table, string $index): bool
    {
        if (DB::getDriverName() === 'sqlite') {
            return collect(DB::select("PRAGMA index_list('{$table}')"))
                ->contains(fn (object $row): bool => ($row->name ?? null) === $index);
        }

        return collect(DB::select("SHOW INDEX FROM `{$table}` WHERE Key_name = ?", [$index]))->isNotEmpty();
    }
};
