<?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('job_alert_preferences')) {
            Schema::create('job_alert_preferences', function (Blueprint $table): void {
                $table->id();
                $table->foreignId('user_id')->unique()->constrained()->cascadeOnDelete();
                $table->boolean('enabled')->default(true)->index();
                $table->string('frequency', 40)->default('daily')->index();
                $table->unsignedTinyInteger('minimum_match_score')->default(45);
                $table->json('categories')->nullable();
                $table->json('keywords')->nullable();
                $table->timestamp('last_sent_at')->nullable()->index();
                $table->timestamp('unsubscribed_at')->nullable()->index();
                $table->timestamps();
            });
        }

        if (! Schema::hasTable('job_alert_email_logs')) {
            Schema::create('job_alert_email_logs', function (Blueprint $table): void {
                $table->id();
                $table->foreignId('user_id')->constrained()->cascadeOnDelete();
                $table->string('status', 40)->index();
                $table->unsignedSmallInteger('job_count')->default(0);
                $table->json('imported_job_ids')->nullable();
                $table->timestamp('sent_at')->nullable()->index();
                $table->text('failure_reason')->nullable();
                $table->timestamps();

                $table->index(['user_id', 'sent_at']);
            });
        }

        if (! Schema::hasTable('job_interests')) {
            Schema::create('job_interests', function (Blueprint $table): void {
                $table->id();
                $table->foreignId('user_id')->constrained()->cascadeOnDelete();
                $table->foreignId('imported_job_id')->constrained()->cascadeOnDelete();
                $table->string('source', 40)->default('viewed')->index();
                $table->unsignedInteger('view_count')->default(1);
                $table->timestamp('first_seen_at')->nullable();
                $table->timestamp('last_seen_at')->nullable()->index();
                $table->timestamps();

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

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

    public function down(): void
    {
        Schema::dropIfExists('job_interests');
        Schema::dropIfExists('job_alert_email_logs');
        Schema::dropIfExists('job_alert_preferences');
    }
};
