<?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
{
    /**
     * Ubah relasi ad↔kategori dari single FK (kategori_id) ke many-to-many pivot.
     * Backfill relasi existing ke pivot, lalu drop kolom + index lama.
     */
    public function up(): void
    {
        // Pivot many-to-many (mirror menu_groups: FK ganda, composite PK, no timestamps).
        Schema::create('ad_kategori', function (Blueprint $table) {
            $table->foreignId('ad_id')->constrained('ads')->cascadeOnDelete();
            $table->foreignId('kategori_id')->constrained('kategoris')->cascadeOnDelete();
            $table->primary(['ad_id', 'kategori_id']);
        });

        // Backfill: relasi existing (kategori_id) → pivot.
        DB::table('ad_kategori')->insertUsing(
            ['ad_id', 'kategori_id'],
            DB::table('ads')->whereNotNull('kategori_id')->select('id', 'kategori_id')
        );

        // Drop index + FK + kolom kategori_id. Pakai array kolom utk dropForeign
        // agar kompatibel cross-driver (SQLite tidak support drop by constraint name).
        Schema::table('ads', function (Blueprint $table) {
            $table->dropIndex('ads_rotatable_index');
            $table->dropIndex('ads_status_kategori_id_klik_index');
            $table->dropForeign(['kategori_id']);
            $table->dropColumn('kategori_id');
        });
    }

    public function down(): void
    {
        // Recreate kategori_id nullable + FK.
        Schema::table('ads', function (Blueprint $table) {
            $table->foreignId('kategori_id')->nullable()->after('user_id')->constrained('kategoris')->restrictOnDelete();
            $table->index(['status', 'kategori_id', 'is_paused'], 'ads_rotatable_index');
            $table->index(['status', 'kategori_id', 'klik'], 'ads_status_kategori_id_klik_index');
        });

        // Backfill from pivot (ambil kategori pertama per ad).
        DB::statement('UPDATE ads SET kategori_id = (SELECT kategori_id FROM ad_kategori WHERE ad_kategori.ad_id = ads.id ORDER BY kategori_id LIMIT 1)');

        Schema::dropIfExists('ad_kategori');
    }
};
