<?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
    {
        // ── Ad Slots (positions defined once) ───────────────────────────────
        Schema::create('ad_slots', function (Blueprint $table) {
            $table->smallIncrements('id');
            $table->string('name', 100);
            $table->string('slug', 100)->unique();
            $table->enum('position', [
                'header',
                'homepage_hero',
                'homepage_mid',
                'sidebar_top',
                'sidebar_mid',
                'sidebar_bottom',
                'in_content',
                'between_posts',
                'footer',
                'mobile_sticky',
            ]);
            $table->string('description', 300)->nullable();
            $table->unsignedSmallInteger('width')->nullable();
            $table->unsignedSmallInteger('height')->nullable();
            $table->unsignedTinyInteger('max_ads')->default(1);
            $table->boolean('is_active')->default(true);
            $table->timestamps();
        });

        // ── Advertisements ───────────────────────────────────────────────────
        Schema::create('advertisements', function (Blueprint $table) {
            $table->increments('id');
            $table->unsignedSmallInteger('ad_slot_id');
            $table->string('title', 200);
            $table->enum('type', ['image', 'html_code', 'google_adsense'])->default('image');

            $table->string('image_url', 1000)->nullable();
            $table->string('link_url', 1000)->nullable();
            $table->text('ad_code')->nullable();                          // raw HTML/JS or AdSense snippet

            $table->string('advertiser_name', 150)->nullable();
            $table->string('advertiser_email', 150)->nullable();
            $table->decimal('price_per_day', 10, 2)->nullable();

            $table->date('start_date')->nullable();
            $table->date('end_date')->nullable();
            $table->boolean('is_active')->default(true);

            // Cached metrics
            $table->unsignedBigInteger('impressions_count')->default(0);
            $table->unsignedBigInteger('clicks_count')->default(0);
            $table->decimal('ctr', 5, 4)->default(0);                    // click-through rate

            $table->timestamps();

            $table->foreign('ad_slot_id')->references('id')->on('ad_slots')->cascadeOnDelete();
            $table->index(['ad_slot_id', 'is_active']);
            $table->index(['start_date', 'end_date']);
        });

        // ── Ad Impressions (granular tracking) ───────────────────────────────
        Schema::create('ad_impressions', function (Blueprint $table) {
            $table->id();
            $table->unsignedInteger('advertisement_id');
            $table->enum('type', ['impression', 'click']);
            $table->foreignId('post_id')->nullable()->constrained()->nullOnDelete();
            $table->string('session_id', 100)->nullable();
            $table->string('ip_hash', 64)->nullable();
            $table->char('country_code', 2)->nullable();
            $table->enum('device_type', ['desktop', 'mobile', 'tablet'])->nullable();
            $table->timestamp('created_at')->useCurrent();

            $table->foreign('advertisement_id')->references('id')->on('advertisements')->cascadeOnDelete();
            $table->index(['advertisement_id', 'type', 'created_at']);
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('ad_impressions');
        Schema::dropIfExists('advertisements');
        Schema::dropIfExists('ad_slots');
    }
};
