<?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
    {
        Schema::create('google_campaign_snapshots', function (Blueprint $table) {
            $table->id();
            $table->foreignId('client_id')->constrained()->cascadeOnDelete();
            $table->foreignId('platform_connection_id')->constrained('platform_connections')->cascadeOnDelete();
            $table->date('date');
            $table->string('campaign_id', 80);
            $table->string('campaign_name')->nullable();
            $table->string('campaign_status', 80)->nullable();
            $table->unsignedBigInteger('impressions')->default(0);
            $table->unsignedBigInteger('clicks')->default(0);
            $table->decimal('cost', 18, 6)->default(0);
            $table->decimal('conversions', 18, 4)->default(0);
            $table->decimal('ctr', 12, 6)->default(0);
            $table->decimal('average_cpc', 18, 6)->default(0);
            $table->longText('raw_json')->nullable();
            $table->timestamps();
            $table->unique(['platform_connection_id', 'date', 'campaign_id'], 'g_campaign_unique');
            $table->index(['client_id', 'date']);
        });

        Schema::create('google_keyword_snapshots', function (Blueprint $table) {
            $table->id();
            $table->foreignId('client_id')->constrained()->cascadeOnDelete();
            $table->foreignId('platform_connection_id')->constrained('platform_connections')->cascadeOnDelete();
            $table->date('date');
            $table->string('campaign_id', 80)->nullable();
            $table->string('campaign_name')->nullable();
            $table->string('ad_group_id', 80)->nullable();
            $table->string('ad_group_name')->nullable();
            $table->string('criterion_id', 80)->nullable();
            $table->string('keyword_text', 1024)->nullable();
            $table->string('match_type', 80)->nullable();
            $table->unsignedBigInteger('impressions')->default(0);
            $table->unsignedBigInteger('clicks')->default(0);
            $table->decimal('cost', 18, 6)->default(0);
            $table->decimal('conversions', 18, 4)->default(0);
            $table->decimal('ctr', 12, 6)->default(0);
            $table->decimal('average_cpc', 18, 6)->default(0);
            $table->longText('raw_json')->nullable();
            $table->timestamps();
            $table->string('row_hash', 64);
            $table->unique(['platform_connection_id', 'date', 'row_hash'], 'g_keyword_unique');
            $table->index(['client_id', 'date']);
        });

        Schema::create('google_search_term_snapshots', function (Blueprint $table) {
            $table->id();
            $table->foreignId('client_id')->constrained()->cascadeOnDelete();
            $table->foreignId('platform_connection_id')->constrained('platform_connections')->cascadeOnDelete();
            $table->date('date');
            $table->string('campaign_id', 80)->nullable();
            $table->string('campaign_name')->nullable();
            $table->string('ad_group_id', 80)->nullable();
            $table->string('ad_group_name')->nullable();
            $table->string('search_term', 1024)->nullable();
            $table->string('match_type', 80)->nullable();
            $table->unsignedBigInteger('impressions')->default(0);
            $table->unsignedBigInteger('clicks')->default(0);
            $table->decimal('cost', 18, 6)->default(0);
            $table->decimal('conversions', 18, 4)->default(0);
            $table->decimal('ctr', 12, 6)->default(0);
            $table->decimal('average_cpc', 18, 6)->default(0);
            $table->longText('raw_json')->nullable();
            $table->timestamps();
            $table->string('row_hash', 64);
            $table->unique(['platform_connection_id', 'date', 'row_hash'], 'g_search_term_unique');
            $table->index(['client_id', 'date']);
        });

        Schema::create('meta_campaign_snapshots', function (Blueprint $table) {
            $table->id();
            $table->foreignId('client_id')->constrained()->cascadeOnDelete();
            $table->foreignId('platform_connection_id')->constrained('platform_connections')->cascadeOnDelete();
            $table->date('date');
            $table->string('campaign_id', 80);
            $table->string('campaign_name')->nullable();
            $table->string('objective', 120)->nullable();
            $table->decimal('spend', 18, 6)->default(0);
            $table->unsignedBigInteger('impressions')->default(0);
            $table->unsignedBigInteger('reach')->default(0);
            $table->unsignedBigInteger('clicks')->default(0);
            $table->unsignedBigInteger('inline_link_clicks')->default(0);
            $table->decimal('cpc', 18, 6)->default(0);
            $table->decimal('cpm', 18, 6)->default(0);
            $table->decimal('ctr', 12, 6)->default(0);
            $table->decimal('conversions', 18, 4)->default(0);
            $table->decimal('leads', 18, 4)->default(0);
            $table->decimal('purchases', 18, 4)->default(0);
            $table->decimal('purchase_value', 18, 6)->default(0);
            $table->longText('raw_json')->nullable();
            $table->timestamps();
            $table->unique(['platform_connection_id', 'date', 'campaign_id'], 'm_campaign_unique');
            $table->index(['client_id', 'date']);
        });

        Schema::create('meta_adset_snapshots', function (Blueprint $table) {
            $table->id();
            $table->foreignId('client_id')->constrained()->cascadeOnDelete();
            $table->foreignId('platform_connection_id')->constrained('platform_connections')->cascadeOnDelete();
            $table->date('date');
            $table->string('campaign_id', 80)->nullable();
            $table->string('campaign_name')->nullable();
            $table->string('adset_id', 80);
            $table->string('adset_name')->nullable();
            $table->decimal('spend', 18, 6)->default(0);
            $table->unsignedBigInteger('impressions')->default(0);
            $table->unsignedBigInteger('reach')->default(0);
            $table->unsignedBigInteger('clicks')->default(0);
            $table->unsignedBigInteger('inline_link_clicks')->default(0);
            $table->decimal('cpc', 18, 6)->default(0);
            $table->decimal('cpm', 18, 6)->default(0);
            $table->decimal('ctr', 12, 6)->default(0);
            $table->decimal('conversions', 18, 4)->default(0);
            $table->decimal('leads', 18, 4)->default(0);
            $table->decimal('purchases', 18, 4)->default(0);
            $table->decimal('purchase_value', 18, 6)->default(0);
            $table->longText('raw_json')->nullable();
            $table->timestamps();
            $table->unique(['platform_connection_id', 'date', 'adset_id'], 'm_adset_unique');
            $table->index(['client_id', 'date']);
        });

        Schema::create('meta_ad_snapshots', function (Blueprint $table) {
            $table->id();
            $table->foreignId('client_id')->constrained()->cascadeOnDelete();
            $table->foreignId('platform_connection_id')->constrained('platform_connections')->cascadeOnDelete();
            $table->date('date');
            $table->string('campaign_id', 80)->nullable();
            $table->string('campaign_name')->nullable();
            $table->string('adset_id', 80)->nullable();
            $table->string('adset_name')->nullable();
            $table->string('ad_id', 80);
            $table->string('ad_name')->nullable();
            $table->decimal('spend', 18, 6)->default(0);
            $table->unsignedBigInteger('impressions')->default(0);
            $table->unsignedBigInteger('reach')->default(0);
            $table->unsignedBigInteger('clicks')->default(0);
            $table->unsignedBigInteger('inline_link_clicks')->default(0);
            $table->decimal('cpc', 18, 6)->default(0);
            $table->decimal('cpm', 18, 6)->default(0);
            $table->decimal('ctr', 12, 6)->default(0);
            $table->decimal('conversions', 18, 4)->default(0);
            $table->decimal('leads', 18, 4)->default(0);
            $table->decimal('purchases', 18, 4)->default(0);
            $table->decimal('purchase_value', 18, 6)->default(0);
            $table->longText('raw_json')->nullable();
            $table->timestamps();
            $table->unique(['platform_connection_id', 'date', 'ad_id'], 'm_ad_unique');
            $table->index(['client_id', 'date']);
        });

        Schema::create('search_console_query_snapshots', function (Blueprint $table) {
            $table->id();
            $table->foreignId('client_id')->constrained()->cascadeOnDelete();
            $table->foreignId('platform_connection_id')->constrained('platform_connections')->cascadeOnDelete();
            $table->date('date');
            $table->string('query_text', 1024)->nullable();
            $table->unsignedBigInteger('clicks')->default(0);
            $table->unsignedBigInteger('impressions')->default(0);
            $table->decimal('ctr', 12, 6)->default(0);
            $table->decimal('position', 12, 4)->default(0);
            $table->longText('raw_json')->nullable();
            $table->timestamps();
            $table->string('row_hash', 64);
            $table->unique(['platform_connection_id', 'date', 'row_hash'], 'gsc_query_unique');
            $table->index(['client_id', 'date']);
        });

        Schema::create('search_console_page_snapshots', function (Blueprint $table) {
            $table->id();
            $table->foreignId('client_id')->constrained()->cascadeOnDelete();
            $table->foreignId('platform_connection_id')->constrained('platform_connections')->cascadeOnDelete();
            $table->date('date');
            $table->text('page_url')->nullable();
            $table->unsignedBigInteger('clicks')->default(0);
            $table->unsignedBigInteger('impressions')->default(0);
            $table->decimal('ctr', 12, 6)->default(0);
            $table->decimal('position', 12, 4)->default(0);
            $table->longText('raw_json')->nullable();
            $table->timestamps();
            $table->string('row_hash', 64);
            $table->unique(['platform_connection_id', 'date', 'row_hash'], 'gsc_page_unique');
            $table->index(['client_id', 'date']);
        });

        Schema::create('ga4_traffic_snapshots', function (Blueprint $table) {
            $table->id();
            $table->foreignId('client_id')->constrained()->cascadeOnDelete();
            $table->foreignId('platform_connection_id')->constrained('platform_connections')->cascadeOnDelete();
            $table->date('date');
            $table->string('dimension_type', 120)->default('channel_group');
            $table->string('dimension_value')->nullable();
            $table->unsignedBigInteger('sessions')->default(0);
            $table->unsignedBigInteger('total_users')->default(0);
            $table->unsignedBigInteger('screen_page_views')->default(0);
            $table->decimal('conversions', 18, 4)->default(0);
            $table->decimal('total_revenue', 18, 6)->default(0);
            $table->unsignedBigInteger('event_count')->default(0);
            $table->longText('raw_json')->nullable();
            $table->timestamps();
            $table->unique(['platform_connection_id', 'date', 'dimension_type', 'dimension_value'], 'ga4_traffic_unique');
            $table->index(['client_id', 'date']);
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('ga4_traffic_snapshots');
        Schema::dropIfExists('search_console_page_snapshots');
        Schema::dropIfExists('search_console_query_snapshots');
        Schema::dropIfExists('meta_ad_snapshots');
        Schema::dropIfExists('meta_adset_snapshots');
        Schema::dropIfExists('meta_campaign_snapshots');
        Schema::dropIfExists('google_search_term_snapshots');
        Schema::dropIfExists('google_keyword_snapshots');
        Schema::dropIfExists('google_campaign_snapshots');
    }
};
