<?php

namespace App\Campaigns;

use Illuminate\Support\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Throwable;

class WhaleAdopsReader
{
    public function __construct(private readonly WhaleAdopsTables $tables) {}

    public function isAvailable(): bool
    {
        if (! (bool) config('campaign-performance.adops_enabled', true)) {
            return false;
        }

        return $this->tables->allAdopsTables() !== [];
    }

    /**
     * Distinct campaigns from every arb_adops_* table that has a campaign id.
     * Never reads the app `campaigns` table.
     *
     * @return list<array{
     *     platform: string,
     *     account_id: ?string,
     *     campaign_id: string,
     *     name: string,
     *     status: ?string,
     *     effective_status: ?string,
     *     daily_budget: ?float,
     *     learning_status: ?string,
     *     started_at: ?string
     * }>
     */
    public function campaigns(?string $platform = 'meta', ?string $campaignId = null, ?string $query = null, ?int $limit = null): array
    {
        $seen = [];

        foreach ($this->tables->allAdopsTables() as $table) {
            $this->collectCampaignIdsFromTable($table, $platform, $campaignId, $seen);
        }

        $this->overlayAdopsProfile($seen, 'campaigns');
        $this->overlayAdopsProfile($seen, 'ads');
        $this->overlayAdopsProfile($seen, 'adsets');
        $this->collectPerformanceCampaignIds($platform, $campaignId, $seen);
        $this->collectRoiCampaignIds($platform, $campaignId, $seen);

        $rows = array_values($seen);

        if (is_string($query) && $query !== '') {
            $needle = strtolower($query);
            $rows = array_values(array_filter(
                $rows,
                fn (array $row): bool => str_contains(strtolower($row['campaign_id']), $needle)
                    || str_contains(strtolower($row['name']), $needle),
            ));
        }

        if ($limit !== null) {
            $rows = array_slice($rows, 0, max(1, $limit));
        }

        return $rows;
    }

    /**
     * @param  array<string, array<string, mixed>>  $seen
     */
    private function collectCampaignIdsFromTable(string $table, ?string $platform, ?string $campaignId, array &$seen): void
    {
        $columns = $this->columns($table);
        $campaignCol = $this->pick($columns, ['campaign_id', 'external_campaign_id']);

        if ($campaignCol === null) {
            return;
        }

        $builder = DB::connection('whale')->table($table)->select($campaignCol);
        $platformCol = $this->pick($columns, ['platform']);
        $accountCol = $this->pick($columns, ['account_id', 'ad_account_id']);

        if ($platformCol !== null) {
            $builder->addSelect($platformCol);

            if (is_string($platform) && $platform !== '') {
                $builder->where($platformCol, strtolower($platform));
            }
        }

        if ($accountCol !== null) {
            $builder->addSelect($accountCol);
        }

        if (is_string($campaignId) && $campaignId !== '') {
            $builder->where($campaignCol, $campaignId);
        }

        try {
            foreach ($builder->distinct()->get() as $row) {
                $id = trim((string) $this->value($row, $campaignCol));

                if ($id === '' || isset($seen[$id])) {
                    continue;
                }

                $seen[$id] = [
                    'platform' => strtolower((string) ($platformCol !== null ? ($this->value($row, $platformCol) ?: $platform) : $platform)),
                    'account_id' => $accountCol !== null ? $this->nullableString($this->value($row, $accountCol)) : null,
                    'campaign_id' => $id,
                    'name' => 'Campaign '.$id,
                    'status' => null,
                    'effective_status' => null,
                    'daily_budget' => null,
                    'learning_status' => null,
                    'started_at' => null,
                ];
            }
        } catch (Throwable) {
            // Table is not a campaign-id grain; skip it.
        }
    }

    /**
     * Campaigns that exist only on the affiliate performance table.
     * An id already collected from arb_adops_* is left unchanged.
     *
     * @param  array<string, array<string, mixed>>  $seen
     */
    private function collectPerformanceCampaignIds(?string $platform, ?string $campaignId, array &$seen): void
    {
        if (! $this->whaleTableExists('arb_adops_campaign_performance')) {
            return;
        }

        $builder = DB::connection('whale')
            ->table('arb_adops_campaign_performance')
            ->select('campaign_id')
            ->distinct();

        if (is_string($campaignId) && $campaignId !== '') {
            $builder->where('campaign_id', $campaignId);
        }

        try {
            foreach ($builder->get() as $row) {
                $id = trim((string) ($row->campaign_id ?? ''));

                if ($id === '' || isset($seen[$id])) {
                    continue;
                }

                $seen[$id] = $this->blankCampaign($id, $platform);
            }
        } catch (Throwable) {
            // Performance table is optional for the campaign list.
        }
    }

    /**
     * ROI dashboard campaigns not already listed.
     * arb_adops_* and arb_adops_campaign_performance keep the row when the id exists in both.
     *
     * @param  array<string, array<string, mixed>>  $seen
     */
    private function collectRoiCampaignIds(?string $platform, ?string $campaignId, array &$seen): void
    {
        if (! $this->whaleTableExists('roi_dashboard_campaign_stats')) {
            return;
        }

        $builder = DB::connection('whale')
            ->table('roi_dashboard_campaign_stats')
            ->select('campaign_id', 'platform', 'campaign_name');

        if (is_string($platform) && $platform !== '') {
            $builder->whereRaw('LOWER(platform) = ?', [strtolower($platform)]);
        }

        if (is_string($campaignId) && $campaignId !== '') {
            $builder->where('campaign_id', $campaignId);
        }

        try {
            foreach ($builder->get() as $row) {
                $id = trim((string) ($row->campaign_id ?? ''));

                if ($id === '' || isset($seen[$id])) {
                    continue;
                }

                $name = trim((string) ($row->campaign_name ?? ''));
                $rowPlatform = strtolower(trim((string) ($row->platform ?? '')));
                $seen[$id] = $this->blankCampaign($id, $rowPlatform !== '' ? $rowPlatform : $platform);
                if ($name !== '') {
                    $seen[$id]['name'] = $name;
                }
            }
        } catch (Throwable) {
            // ROI table is optional for the campaign list.
        }
    }

    /**
     * @return array{
     *     platform: string,
     *     account_id: null,
     *     campaign_id: string,
     *     name: string,
     *     status: null,
     *     effective_status: null,
     *     daily_budget: null,
     *     learning_status: null,
     *     started_at: null
     * }
     */
    private function blankCampaign(string $id, ?string $platform): array
    {
        return [
            'platform' => strtolower((string) ($platform !== null && $platform !== '' ? $platform : 'meta')),
            'account_id' => null,
            'campaign_id' => $id,
            'name' => 'Campaign '.$id,
            'status' => null,
            'effective_status' => null,
            'daily_budget' => null,
            'learning_status' => null,
            'started_at' => null,
        ];
    }

    private function whaleTableExists(string $table): bool
    {
        try {
            return Schema::connection('whale')->hasTable($table);
        } catch (Throwable) {
            return false;
        }
    }

    /**
     * @param  array<string, array<string, mixed>>  $seen
     */
    private function overlayAdopsProfile(array &$seen, string $dataset): void
    {
        $table = $this->tables->name($dataset);

        if ($table === null || $seen === []) {
            return;
        }

        $columns = $this->columns($table);
        $campaignCol = $this->pick($columns, ['campaign_id', 'external_campaign_id']);

        if ($campaignCol === null) {
            return;
        }

        $nameCol = $this->pick($columns, ['name', 'campaign_name']);
        $platformCol = $this->pick($columns, ['platform']);
        $accountCol = $this->pick($columns, ['account_id', 'ad_account_id']);
        $statusCol = $this->pick($columns, ['status']);
        $effectiveCol = $this->pick($columns, ['effective_status', 'serving_status', 'platform_status']);
        $budgetCol = $this->pick($columns, ['daily_budget', 'budget']);
        $learningCol = $this->pick($columns, ['learning_status', 'learning_stage']);
        $startedCol = $this->pick($columns, ['start_date', 'started_at', 'created_at_platform']);

        try {
            $rows = DB::connection('whale')->table($table)
                ->whereIn($campaignCol, array_keys($seen))
                ->get();
        } catch (Throwable) {
            return;
        }

        foreach ($rows as $row) {
            $id = trim((string) $this->value($row, $campaignCol));

            if ($id === '' || ! isset($seen[$id])) {
                continue;
            }

            $name = $nameCol !== null ? trim((string) $this->value($row, $nameCol)) : '';
            $placeholder = $seen[$id]['name'] === 'Campaign '.$id;

            if ($name !== '' && ($placeholder || $dataset === 'campaigns')) {
                $seen[$id]['name'] = $name;
            }

            if ($platformCol !== null) {
                $platform = strtolower(trim((string) $this->value($row, $platformCol)));
                if ($platform !== '') {
                    $seen[$id]['platform'] = $platform;
                }
            }

            if ($accountCol !== null && $seen[$id]['account_id'] === null) {
                $seen[$id]['account_id'] = $this->nullableString($this->value($row, $accountCol));
            }

            if ($statusCol !== null && $seen[$id]['status'] === null) {
                $seen[$id]['status'] = $this->nullableString($this->value($row, $statusCol));
            }

            if ($effectiveCol !== null && $seen[$id]['effective_status'] === null) {
                $seen[$id]['effective_status'] = $this->nullableString($this->value($row, $effectiveCol));
            }

            if ($budgetCol !== null && $seen[$id]['daily_budget'] === null) {
                $seen[$id]['daily_budget'] = $this->nullableFloat($this->value($row, $budgetCol));
            }

            if ($learningCol !== null && $seen[$id]['learning_status'] === null) {
                $seen[$id]['learning_status'] = $this->nullableString($this->value($row, $learningCol));
            }

            if ($startedCol !== null && $seen[$id]['started_at'] === null) {
                $seen[$id]['started_at'] = $this->nullableString($this->value($row, $startedCol));
            }
        }
    }

    /**
     * @return Collection<int, PerformanceDay>
     */
    public function dailyStats(string $platform, string $campaignId, string $from, string $to): Collection
    {
        $rows = $this->metricRows('campaign_daily', 'stats_daily', $platform, $campaignId, $from, $to, false);

        return $rows->map(function (array $row): PerformanceDay {
            $clicks = (int) $row['clicks'];
            $impressions = (int) $row['impressions'];
            $spend = (float) $row['spend'];

            return new PerformanceDay(
                stat_date: Carbon::parse($row['stat_date'])->startOfDay(),
                spend: $spend,
                impressions: $impressions,
                clicks: $clicks,
                cpc: $clicks > 0 ? round($spend / $clicks, 4) : null,
                ctr: $impressions > 0 ? round($clicks / $impressions, 4) : null,
            );
        })->values();
    }

    /**
     * @return Collection<int, PerformanceHour>
     */
    public function hourlyStats(string $platform, string $campaignId, Carbon $since): Collection
    {
        $from = $since->toDateString();
        $to = now()->toDateString();
        $rows = $this->metricRows('campaign_hourly', 'stats_hourly', $platform, $campaignId, $from, $to, true);

        return $rows
            ->map(function (array $row) use ($since): ?PerformanceHour {
                $hour = $this->statHour($row);

                if ($hour === null || $hour->lt($since)) {
                    return null;
                }

                $clicks = (int) $row['clicks'];
                $impressions = (int) $row['impressions'];
                $spend = (float) $row['spend'];

                return new PerformanceHour(
                    stat_hour: $hour,
                    spend: $spend,
                    impressions: $impressions,
                    clicks: $clicks,
                    cpc: $clicks > 0 ? round($spend / $clicks, 4) : null,
                    ctr: $impressions > 0 ? round($clicks / $impressions, 4) : null,
                );
            })
            ->filter()
            ->sortBy(fn (PerformanceHour $row): int => $row->stat_hour->timestamp)
            ->values();
    }

    /**
     * @return array<string, array{spend: float, clicks: int, impressions: int}>
     */
    public function sevenDayTotals(string $platform, string $since): array
    {
        $to = now()->toDateString();
        $tables = array_values(array_unique(array_merge(
            $this->tables->tablesFor('stats_daily', $since, $to),
            $this->tables->tablesFor('campaign_daily', $since, $to),
        )));

        $totals = [];

        foreach ($tables as $name) {
            $columns = $this->columns($name);
            $campaignCol = $this->pick($columns, ['campaign_id', 'external_campaign_id']);
            $dateCol = $this->pick($columns, ['stat_date', 'date']);
            $spendCol = $this->pick($columns, ['spend', 'cost']);
            $clicksCol = $this->pick($columns, ['clicks']);
            $impressionsCol = $this->pick($columns, ['impressions', 'impr']);

            if ($campaignCol === null || $dateCol === null || $spendCol === null) {
                continue;
            }

            $builder = DB::connection('whale')->table($name)
                ->where($dateCol, '>=', $since)
                ->selectRaw($campaignCol.' as campaign_id')
                ->selectRaw('SUM('.$spendCol.') as spend')
                ->selectRaw($clicksCol !== null ? 'SUM('.$clicksCol.') as clicks' : '0 as clicks')
                ->selectRaw($impressionsCol !== null ? 'SUM('.$impressionsCol.') as impressions' : '0 as impressions')
                ->groupBy($campaignCol);

            $platformCol = $this->pick($columns, ['platform']);

            if ($platformCol !== null) {
                $builder->where($platformCol, $platform);
            }

            try {
                foreach ($builder->get() as $row) {
                    $id = trim((string) $row->campaign_id);

                    if ($id === '') {
                        continue;
                    }

                    $current = $totals[$id] ?? ['spend' => 0.0, 'clicks' => 0, 'impressions' => 0];
                    $totals[$id] = [
                        'spend' => $current['spend'] + (float) $row->spend,
                        'clicks' => $current['clicks'] + (int) $row->clicks,
                        'impressions' => $current['impressions'] + (int) $row->impressions,
                    ];
                }
            } catch (Throwable) {
                continue;
            }
        }

        foreach ($totals as $id => $row) {
            $totals[$id]['spend'] = round($row['spend'], 2);
        }

        return $totals;
    }

    /**
     * @return array{status: ?string, last_success_at: ?string, data_through: ?string, error: ?string}|null
     */
    public function syncStatus(string $platform, ?string $accountId, string $dataset): ?array
    {
        $table = $this->tables->name('sync_status');

        if ($table === null) {
            return null;
        }

        $columns = $this->columns($table);
        $platformCol = $this->pick($columns, ['platform']);
        $datasetCol = $this->pick($columns, ['dataset']);

        if ($platformCol === null || $datasetCol === null) {
            return null;
        }

        $builder = DB::connection('whale')->table($table)
            ->where($platformCol, $platform)
            ->where($datasetCol, $dataset);

        $accountCol = $this->pick($columns, ['account_id', 'ad_account_id']);

        if ($accountCol !== null && is_string($accountId) && $accountId !== '') {
            $builder->where($accountCol, $accountId);
        }

        try {
            $row = $builder->orderByDesc($this->pick($columns, ['last_success_at', 'updated_at']) ?? $datasetCol)->first();
        } catch (Throwable) {
            return null;
        }

        if ($row === null) {
            return null;
        }

        $statusCol = $this->pick($columns, ['status']);
        $successCol = $this->pick($columns, ['last_success_at']);
        $throughCol = $this->pick($columns, ['data_through']);
        $errorCol = $this->pick($columns, ['error', 'last_error']);

        return [
            'status' => $statusCol !== null ? $this->nullableString($this->value($row, $statusCol)) : null,
            'last_success_at' => $successCol !== null ? $this->nullableString($this->value($row, $successCol)) : null,
            'data_through' => $throughCol !== null ? $this->nullableString($this->value($row, $throughCol)) : null,
            'error' => $errorCol !== null ? $this->nullableString($this->value($row, $errorCol)) : null,
        ];
    }

    /**
     * @return array{status: ?string, last_success_at: ?string, data_through: ?string, error: ?string}|null
     */
    public function latestIngest(string $platform, ?string $accountId, string $dataset): ?array
    {
        return $this->syncStatus($platform, $accountId, $dataset)
            ?? $this->syncStatus($platform, $accountId, $dataset.'_backfill')
            ?? $this->syncStatus($platform, $accountId, 'structure');
    }

    /**
     * @param  array{status: ?string, last_success_at: ?string, data_through: ?string, error: ?string}|null  $sync
     */
    public function ingestFailed(?array $sync): bool
    {
        if ($sync === null) {
            return false;
        }

        return in_array(strtolower((string) ($sync['status'] ?? '')), ['failed', 'error'], true);
    }

    public function ingestError(string $platform, ?string $accountId, string $dataset): ?string
    {
        $sync = $this->latestIngest($platform, $accountId, $dataset);

        if (! $this->ingestFailed($sync)) {
            return null;
        }

        return $sync['error'] ?? 'Whale ingest reported a failure.';
    }

    /**
     * @return Collection<int, array{stat_date: string, spend: float, impressions: int, clicks: int, hour: ?int, stat_hour: ?string}>
     */
    private function metricRows(
        string $rollupDataset,
        string $detailDataset,
        string $platform,
        string $campaignId,
        string $from,
        string $to,
        bool $hourly,
    ): Collection {
        $tables = $this->tables->tablesFor($detailDataset, $from, $to);

        if ($tables === []) {
            $tables = $this->tables->tablesFor($rollupDataset, $from, $to);
        }

        $rows = collect();

        foreach ($tables as $table) {
            foreach ($this->readMetricTable($table, $platform, $campaignId, $from, $to, $hourly) as $row) {
                $key = $hourly
                    ? $row['stat_date'].'|'.($row['hour'] ?? $row['stat_hour'] ?? '')
                    : $row['stat_date'];

                if (! $rows->has($key)) {
                    $rows->put($key, $row);

                    continue;
                }

                $existing = $rows->get($key);
                $rows->put($key, [
                    ...$existing,
                    'spend' => $existing['spend'] + $row['spend'],
                    'impressions' => $existing['impressions'] + $row['impressions'],
                    'clicks' => $existing['clicks'] + $row['clicks'],
                ]);
            }
        }

        return $rows->values();
    }

    /**
     * @return list<array{stat_date: string, spend: float, impressions: int, clicks: int, hour: ?int, stat_hour: ?string}>
     */
    private function readMetricTable(
        string $table,
        string $platform,
        string $campaignId,
        string $from,
        string $to,
        bool $hourly,
    ): array {
        $columns = $this->columns($table);
        $campaignCol = $this->pick($columns, ['campaign_id', 'external_campaign_id']);
        $dateCol = $this->pick($columns, ['stat_date', 'date']);
        $spendCol = $this->pick($columns, ['spend', 'cost']);

        if ($campaignCol === null || $dateCol === null || $spendCol === null) {
            return [];
        }

        $builder = DB::connection('whale')->table($table)
            ->where($campaignCol, $campaignId)
            ->whereBetween($dateCol, [$from, $to]);

        $platformCol = $this->pick($columns, ['platform']);

        if ($platformCol !== null) {
            $builder->where($platformCol, $platform);
        }

        $hourCol = $this->pick($columns, ['hour', 'hour_of_day']);
        $statHourCol = $this->pick($columns, ['stat_hour']);
        $clicksCol = $this->pick($columns, ['clicks']);
        $impressionsCol = $this->pick($columns, ['impressions', 'impr']);

        if ($hourly && $hourCol !== null) {
            $builder->selectRaw($dateCol.' as stat_date')
                ->selectRaw($hourCol.' as hour')
                ->selectRaw('SUM('.$spendCol.') as spend')
                ->selectRaw($clicksCol !== null ? 'SUM('.$clicksCol.') as clicks' : '0 as clicks')
                ->selectRaw($impressionsCol !== null ? 'SUM('.$impressionsCol.') as impressions' : '0 as impressions')
                ->groupBy($dateCol, $hourCol);
        } elseif ($hourly && $statHourCol !== null) {
            $builder->selectRaw($statHourCol.' as stat_hour')
                ->selectRaw('SUM('.$spendCol.') as spend')
                ->selectRaw($clicksCol !== null ? 'SUM('.$clicksCol.') as clicks' : '0 as clicks')
                ->selectRaw($impressionsCol !== null ? 'SUM('.$impressionsCol.') as impressions' : '0 as impressions')
                ->groupBy($statHourCol);
        } else {
            $builder->selectRaw($dateCol.' as stat_date')
                ->selectRaw('SUM('.$spendCol.') as spend')
                ->selectRaw($clicksCol !== null ? 'SUM('.$clicksCol.') as clicks' : '0 as clicks')
                ->selectRaw($impressionsCol !== null ? 'SUM('.$impressionsCol.') as impressions' : '0 as impressions')
                ->groupBy($dateCol);
        }

        try {
            return $builder->get()->map(function (object $row): array {
                $statHour = isset($row->stat_hour) ? $this->nullableString($row->stat_hour) : null;
                $date = isset($row->stat_date) ? (string) $row->stat_date : ($statHour !== null ? substr($statHour, 0, 10) : '');

                return [
                    'stat_date' => $date,
                    'spend' => (float) ($row->spend ?? 0),
                    'impressions' => (int) ($row->impressions ?? 0),
                    'clicks' => (int) ($row->clicks ?? 0),
                    'hour' => isset($row->hour) ? (int) $row->hour : null,
                    'stat_hour' => $statHour,
                ];
            })->all();
        } catch (Throwable) {
            return [];
        }
    }

    /**
     * @param  array{stat_date: string, hour: ?int, stat_hour: ?string}  $row
     */
    private function statHour(array $row): ?Carbon
    {
        if (is_string($row['stat_hour']) && $row['stat_hour'] !== '') {
            return Carbon::parse($row['stat_hour'])->startOfHour();
        }

        if ($row['stat_date'] === '' || $row['hour'] === null) {
            return null;
        }

        return Carbon::parse($row['stat_date'])->startOfDay()->setHour($row['hour']);
    }

    /**
     * @return list<string>
     */
    private function columns(string $table): array
    {
        return $this->tables->columns($table);
    }

    /**
     * @param  list<string>  $columns
     * @param  list<string>  $candidates
     */
    private function pick(array $columns, array $candidates): ?string
    {
        foreach ($candidates as $candidate) {
            if (in_array($candidate, $columns, true)) {
                return $candidate;
            }
        }

        return null;
    }

    private function value(object $row, string $column): mixed
    {
        return $row->{$column} ?? null;
    }

    private function nullableString(mixed $value): ?string
    {
        if ($value === null) {
            return null;
        }

        $string = trim((string) $value);

        return $string === '' ? null : $string;
    }

    private function nullableFloat(mixed $value): ?float
    {
        if ($value === null || $value === '') {
            return null;
        }

        return (float) $value;
    }
}