<?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
    {
        Schema::table('spotlights', function (Blueprint $table) {
            // Add foreign keys
            $table->foreignId('redemption_id')->nullable()->after('offer_id')->constrained()->nullOnDelete();
            $table->enum('flag_status', ['none', 'flagged', 'under_review', 'cleared'])->default('none')->after('status');
            $table->text('flag_reason')->nullable()->after('flag_status');
            $table->foreignId('flagged_by')->nullable()->after('flag_reason')->constrained('users')->nullOnDelete();
            $table->timestamp('flagged_at')->nullable()->after('flagged_by');
            $table->foreignId('reviewed_by')->nullable()->after('flagged_at')->constrained('users')->nullOnDelete();
            $table->timestamp('reviewed_at')->nullable()->after('reviewed_by');

            $table->index(['flag_status', 'status']);
        });

        // Add CHECK constraint WITHOUT 'flagged_by'
        DB::statement('ALTER TABLE spotlights ADD CONSTRAINT check_spotlight_flags CHECK (
            (flag_status = \'none\' AND flag_reason IS NULL AND flagged_at IS NULL) OR
            (flag_status != \'none\')
        )');
    }

    public function down(): void
    {
        Schema::table('spotlights', function (Blueprint $table) {
            // Drop CHECK constraint
            DB::statement('ALTER TABLE spotlights DROP CHECK check_spotlight_flags');

            // Drop foreign keys
            $table->dropForeign(['redemption_id']);
            $table->dropForeign(['flagged_by']);
            $table->dropForeign(['reviewed_by']);

            // Drop columns
            $table->dropColumn([
                'redemption_id',
                'flag_status',
                'flag_reason',
                'flagged_by',
                'flagged_at',
                'reviewed_by',
                'reviewed_at',
            ]);
        });
    }
};
