<?php

namespace Tests\Concerns;

use App\Campaigns\WhaleAdopsTables;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

trait CreatesAdopsWhaleTables
{
    private bool $adopsWhaleReady = false;

    protected function createAdopsWhaleTables(): void
    {
        $schema = Schema::connection('whale');

        if (! $this->adopsWhaleReady) {
            foreach ([
                'arb_adops_campaigns',
                'arb_adops_ads',
                'arb_adops_adsets',
                'arb_adops_stats_campaign_daily',
                'arb_adops_stats_campaign_hourly',
                'arb_adops_sync_status',
            ] as $table) {
                $schema->dropIfExists($table);
            }

            $this->adopsWhaleReady = true;
        }

        if (! $schema->hasTable('arb_adops_campaigns')) {
            $schema->create('arb_adops_campaigns', function (Blueprint $table): void {
                $table->string('platform', 24);
                $table->string('account_id');
                $table->string('campaign_id');
                $table->string('name');
                $table->string('status')->nullable();
                $table->string('effective_status')->nullable();
                $table->decimal('daily_budget', 12, 2)->nullable();
                $table->string('learning_status')->nullable();
                $table->date('start_date')->nullable();
            });
        }

        if (! $schema->hasTable('arb_adops_ads')) {
            $schema->create('arb_adops_ads', function (Blueprint $table): void {
                $table->string('platform', 24);
                $table->string('account_id');
                $table->string('campaign_id');
                $table->string('ad_id');
                $table->string('name');
            });
        }

        if (! $schema->hasTable('arb_adops_adsets')) {
            $schema->create('arb_adops_adsets', function (Blueprint $table): void {
                $table->string('platform', 24);
                $table->string('account_id');
                $table->string('campaign_id');
                $table->string('adset_id');
                $table->string('name');
            });
        }

        if (! $schema->hasTable('arb_adops_stats_campaign_daily')) {
            $schema->create('arb_adops_stats_campaign_daily', function (Blueprint $table): void {
                $table->string('platform', 24);
                $table->string('account_id');
                $table->string('campaign_id');
                $table->date('stat_date');
                $table->decimal('spend', 12, 2)->default(0);
                $table->unsignedBigInteger('impressions')->default(0);
                $table->unsignedBigInteger('clicks')->default(0);
            });
        }

        if (! $schema->hasTable('arb_adops_stats_campaign_hourly')) {
            $schema->create('arb_adops_stats_campaign_hourly', function (Blueprint $table): void {
                $table->string('platform', 24);
                $table->string('account_id');
                $table->string('campaign_id');
                $table->date('stat_date');
                $table->unsignedTinyInteger('hour');
                $table->decimal('spend', 12, 2)->default(0);
                $table->unsignedBigInteger('impressions')->default(0);
                $table->unsignedBigInteger('clicks')->default(0);
            });
        }

        if (! $schema->hasTable('arb_adops_sync_status')) {
            $schema->create('arb_adops_sync_status', function (Blueprint $table): void {
                $table->string('platform', 24);
                $table->string('account_id');
                $table->string('dataset');
                $table->string('status')->nullable();
                $table->timestamp('last_success_at')->nullable();
                $table->string('data_through')->nullable();
                $table->text('error')->nullable();
            });
        }
    }

    protected function seedAdopsCampaign(
        string $id,
        string $name,
        float $dailySpend = 6.0,
        int $dailyClicks = 0,
        int $days = 7,
        int $hours = 7,
    ): void {
        $this->seedAdopsCampaignMeta($id, $name);

        for ($offset = 0; $offset < $days; $offset++) {
            $this->seedAdopsDaily($id, now()->subDays($days - 1 - $offset)->toDateString(), $dailySpend, $dailyClicks);
        }

        for ($offset = 0; $offset < $hours; $offset++) {
            $hour = now()->copy()->startOfHour()->subHours($hours - 1 - $offset);
            $this->seedAdopsHourly($id, $hour, $dailySpend, $dailyClicks);
        }

        DB::connection('whale')->table('arb_adops_sync_status')->insert([
            'platform' => 'meta',
            'account_id' => 'act_111',
            'dataset' => 'hourly',
            'status' => 'ok',
            'last_success_at' => now(),
            'data_through' => now()->toDateTimeString(),
            'error' => null,
        ]);
    }

    protected function seedAdopsCampaignMeta(string $id, string $name): void
    {
        $this->createAdopsWhaleTables();

        DB::connection('whale')->table('arb_adops_campaigns')->where('campaign_id', $id)->delete();
        DB::connection('whale')->table('arb_adops_campaigns')->insert([
            'platform' => 'meta',
            'account_id' => 'act_111',
            'campaign_id' => $id,
            'name' => $name,
            'status' => 'ACTIVE',
            'effective_status' => 'ACTIVE',
            'daily_budget' => 20,
            'learning_status' => null,
            'start_date' => now()->subDays(7)->toDateString(),
        ]);
    }

    protected function seedAdopsDaily(
        string $id,
        string $date,
        float $spend,
        int $clicks,
        int $impressions = 100,
    ): void {
        $this->createAdopsWhaleTables();

        DB::connection('whale')->table('arb_adops_stats_campaign_daily')->insert([
            'platform' => 'meta',
            'account_id' => 'act_111',
            'campaign_id' => $id,
            'stat_date' => $date,
            'spend' => $spend,
            'impressions' => $impressions,
            'clicks' => $clicks,
        ]);
    }

    protected function seedAdopsHourly(
        string $id,
        Carbon|string $hour,
        float $spend,
        int $clicks,
        int $impressions = 20,
    ): void {
        $this->createAdopsWhaleTables();
        $parsed = $hour instanceof Carbon ? $hour : Carbon::parse($hour);

        DB::connection('whale')->table('arb_adops_stats_campaign_hourly')->insert([
            'platform' => 'meta',
            'account_id' => 'act_111',
            'campaign_id' => $id,
            'stat_date' => $parsed->toDateString(),
            'hour' => (int) $parsed->format('G'),
            'spend' => $spend,
            'impressions' => $impressions,
            'clicks' => $clicks,
        ]);
    }

    protected function seedAdopsAd(string $campaignId, string $name, string $adId = 'ad_1'): void
    {
        $this->createAdopsWhaleTables();

        DB::connection('whale')->table('arb_adops_ads')->insert([
            'platform' => 'meta',
            'account_id' => 'act_111',
            'campaign_id' => $campaignId,
            'ad_id' => $adId,
            'name' => $name,
        ]);
    }

    protected function seedAdopsAccount(string $accountId, string $name, string $status = 'ACTIVE'): void
    {
        $schema = Schema::connection('whale');

        if (! $schema->hasTable('arb_adops_accounts')) {
            $schema->create('arb_adops_accounts', function (Blueprint $table): void {
                $table->string('platform', 24);
                $table->string('account_id');
                $table->string('name');
                $table->string('currency')->nullable();
                $table->string('timezone')->nullable();
                $table->string('status')->nullable();
            });
        }

        DB::connection('whale')->table('arb_adops_accounts')->insert([
            'platform' => 'meta',
            'account_id' => $accountId,
            'name' => $name,
            'currency' => 'USD',
            'timezone' => 'UTC',
            'status' => $status,
        ]);
    }

    protected function seedAdopsGeoDaily(
        string $campaignId,
        string $country,
        string $date,
        float $spend,
        int $clicks,
        int $impressions = 100,
        string $region = '',
        string $accountId = 'act_111',
    ): void {
        $table = 'arb_adops_stats_geo_daily_'.Carbon::parse($date)->format('Ym');
        $schema = Schema::connection('whale');

        if (! $schema->hasTable($table)) {
            $schema->create($table, function (Blueprint $table): void {
                $table->string('platform', 24);
                $table->string('account_id');
                $table->string('campaign_id');
                $table->date('stat_date');
                $table->string('country');
                $table->string('region')->nullable();
                $table->decimal('spend', 12, 2)->default(0);
                $table->unsignedBigInteger('impressions')->default(0);
                $table->unsignedBigInteger('clicks')->default(0);
            });
        }

        DB::connection('whale')->table($table)->insert([
            'platform' => 'meta',
            'account_id' => $accountId,
            'campaign_id' => $campaignId,
            'stat_date' => $date,
            'country' => $country,
            'region' => $region,
            'spend' => $spend,
            'impressions' => $impressions,
            'clicks' => $clicks,
        ]);
    }

    protected function wipeAdopsWhaleRows(): void
    {
        $this->createAdopsWhaleTables();

        foreach (app(WhaleAdopsTables::class)->allAdopsTables() as $table) {
            DB::connection('whale')->table($table)->delete();
        }
    }
}