<?php

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

return new class extends Migration
{
    public function up(): void
    {
        if (! Schema::hasTable('email_delivery_logs')) {
            Schema::create('email_delivery_logs', function (Blueprint $table): void {
                $table->id();
                $table->foreignId('user_id')->nullable()->constrained()->nullOnDelete();
                $table->string('type', 80)->default('transactional')->index();
                $table->string('provider', 40)->default('laravel')->index();
                $table->string('provider_message_id')->nullable()->index();
                $table->string('brevo_message_id')->nullable()->index();
                $table->string('recipient_email')->index();
                $table->string('recipient_name')->nullable();
                $table->string('subject');
                $table->string('status', 40)->default('pending')->index();
                $table->boolean('resendable')->default(false)->index();
                $table->json('headers')->nullable();
                $table->json('metadata')->nullable();
                $table->json('event_counts')->nullable();
                $table->timestamp('sent_at')->nullable()->index();
                $table->timestamp('delivered_at')->nullable()->index();
                $table->timestamp('opened_at')->nullable()->index();
                $table->timestamp('clicked_at')->nullable()->index();
                $table->timestamp('unsubscribed_at')->nullable()->index();
                $table->timestamp('bounced_at')->nullable()->index();
                $table->timestamp('failed_at')->nullable()->index();
                $table->text('failure_reason')->nullable();
                $table->timestamps();

                $table->index(['type', 'status']);
                $table->index(['recipient_email', 'created_at']);
            });
        }

        if (! Schema::hasTable('user_engagement_events')) {
            Schema::create('user_engagement_events', function (Blueprint $table): void {
                $table->id();
                $table->foreignId('user_id')->nullable()->constrained()->nullOnDelete();
                $table->string('event_type', 100)->index();
                $table->nullableMorphs('subject');
                $table->string('description')->nullable();
                $table->json('metadata')->nullable();
                $table->timestamp('occurred_at')->index();
                $table->timestamps();

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

        if (! Schema::hasTable('job_source_sync_logs')) {
            Schema::create('job_source_sync_logs', function (Blueprint $table): void {
                $table->id();
                $table->foreignId('job_source_id')->constrained()->cascadeOnDelete();
                $table->string('status', 40)->index();
                $table->unsignedInteger('jobs_imported')->default(0);
                $table->unsignedInteger('jobs_expired')->default(0);
                $table->unsignedInteger('duration_ms')->nullable();
                $table->text('message')->nullable();
                $table->timestamp('started_at')->nullable()->index();
                $table->timestamp('finished_at')->nullable()->index();
                $table->timestamps();

                $table->index(['job_source_id', 'finished_at']);
            });
        }

        if (Schema::hasTable('job_alert_email_logs')) {
            Schema::table('job_alert_email_logs', function (Blueprint $table): void {
                if (! Schema::hasColumn('job_alert_email_logs', 'email_delivery_log_id')) {
                    $table->foreignId('email_delivery_log_id')->nullable()->after('user_id')->constrained('email_delivery_logs')->nullOnDelete();
                }

                if (! Schema::hasColumn('job_alert_email_logs', 'provider_message_id')) {
                    $table->string('provider_message_id')->nullable()->after('status')->index();
                }

                if (! Schema::hasColumn('job_alert_email_logs', 'brevo_message_id')) {
                    $table->string('brevo_message_id')->nullable()->after('provider_message_id')->index();
                }

                if (! Schema::hasColumn('job_alert_email_logs', 'delivered_at')) {
                    $table->timestamp('delivered_at')->nullable()->after('sent_at')->index();
                }

                if (! Schema::hasColumn('job_alert_email_logs', 'opened_at')) {
                    $table->timestamp('opened_at')->nullable()->after('delivered_at')->index();
                }

                if (! Schema::hasColumn('job_alert_email_logs', 'clicked_at')) {
                    $table->timestamp('clicked_at')->nullable()->after('opened_at')->index();
                }

                if (! Schema::hasColumn('job_alert_email_logs', 'unsubscribed_at')) {
                    $table->timestamp('unsubscribed_at')->nullable()->after('clicked_at')->index();
                }

                if (! Schema::hasColumn('job_alert_email_logs', 'bounced_at')) {
                    $table->timestamp('bounced_at')->nullable()->after('unsubscribed_at')->index();
                }

                if (! Schema::hasColumn('job_alert_email_logs', 'event_counts')) {
                    $table->json('event_counts')->nullable()->after('bounced_at');
                }

                if (! Schema::hasColumn('job_alert_email_logs', 'last_event_at')) {
                    $table->timestamp('last_event_at')->nullable()->after('event_counts')->index();
                }
            });
        }
    }

    public function down(): void
    {
        if (Schema::hasTable('job_alert_email_logs')) {
            Schema::table('job_alert_email_logs', function (Blueprint $table): void {
                foreach ([
                    'email_delivery_log_id',
                    'provider_message_id',
                    'brevo_message_id',
                    'delivered_at',
                    'opened_at',
                    'clicked_at',
                    'unsubscribed_at',
                    'bounced_at',
                    'event_counts',
                    'last_event_at',
                ] as $column) {
                    if (Schema::hasColumn('job_alert_email_logs', $column)) {
                        $table->dropColumn($column);
                    }
                }
            });
        }

        Schema::dropIfExists('job_source_sync_logs');
        Schema::dropIfExists('user_engagement_events');
        Schema::dropIfExists('email_delivery_logs');
    }
};
