<?php

namespace App\Campaigns;

use Illuminate\Database\Query\Builder;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use Throwable;

class WhaleAdopsQuery
{
    /**
     * @var list<string>
     */
    private const METRIC_COLUMNS = [
        'spend',
        'spend_usd',
        'impressions',
        'clicks',
        'link_clicks',
        'outbound_clicks',
        'reach',
        'conversions',
        'conversion_value',
        'conversion_value_usd',
        'orders',
        'net_revenue',
        'sum_cpc',
        'video_plays',
        'video_3s_views',
        'thruplays',
    ];

    /**
     * @var list<string>
     */
    private const GROUP_COLUMNS = [
        'platform',
        'account_id',
        'campaign_id',
        'adset_id',
        'ad_id',
        'country',
        'region',
        'stat_date',
        'hour',
        'dimension',
        'value_1',
        'value_2',
        'action_key',
        'action_name',
        'action_group',
        'entity_level',
        'entity_id',
        'status',
        'effective_status',
        'name',
        'objective',
        'currency',
        'date',
        'window_key',
        'metric',
        'issue_type',
        'change_type',
        'event_type',
        'actor',
        'affiliate',
        'advertiser_id',
        'creative_id',
        'type',
        'cta',
    ];

    /**
     * @var list<string>
     */
    private const HIDDEN_COLUMNS = [
        'id',
        'raw_payload',
        'raw',
        'debug_json',
        'advertiser_data',
        'content_hash',
        'dim_hash',
        'attribution_spec',
    ];

    /**
     * @var list<string>
     */
    private const LIST_COLUMNS = [
        'platform',
        'account_id',
        'campaign_id',
        'adset_id',
        'ad_id',
        'creative_id',
        'name',
        'status',
        'effective_status',
        'objective',
        'currency',
        'timezone',
        'country',
        'region',
        'countries',
        'daily_budget',
        'lifetime_budget',
        'learning_status',
        'optimization_goal',
        'bid_strategy',
        'final_url',
        'landing_url',
        'primary_text',
        'headlines',
        'cta',
        'placements',
        'devices',
        'age_min',
        'age_max',
        'dataset',
        'error',
        'last_success_at',
        'data_through',
        'issue_type',
        'reason',
        'entity_level',
        'entity_id',
        'entity_name',
        'changed_at',
        'actor',
        'change_type',
        'event_type',
        'affiliate',
        'orders',
        'net_revenue',
        'metric',
        'value',
    ];

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

    /**
     * @return list<array{
     *     key: string,
     *     about: string,
     *     grain: string,
     *     uses_dates: bool,
     *     group_by: list<string>,
     *     filters: list<string>,
     *     available: bool
     * }>
     */
    public function catalog(): array
    {
        $catalog = [];

        foreach ($this->specs() as $spec) {
            $catalog[] = [
                'key' => $spec['key'],
                'about' => $spec['about'],
                'grain' => $spec['grain'],
                'uses_dates' => $spec['uses_dates'],
                'group_by' => $spec['group_by'],
                'filters' => $spec['filters'],
                'available' => $this->tablesForSpec($spec, '2000-01-01', '2100-01-01') !== [],
            ];
        }

        return $catalog;
    }

    /**
     * @param  array<string, mixed>  $input
     * @return array<string, mixed>
     */
    public function run(array $input): array
    {
        if (! $this->reader->isAvailable()) {
            return ['ok' => false, 'error' => 'Whale adops tables are not available.'];
        }

        $dataset = strtolower(trim((string) ($input['dataset'] ?? '')));
        $spec = $this->specs()[$dataset] ?? null;

        if ($spec === null) {
            return [
                'ok' => false,
                'error' => 'Unknown dataset "'.$dataset.'". Call list_adops_catalog and use one of those keys.',
            ];
        }

        $from = $this->dateOrDefault($input['from'] ?? null, now()->subDays(6)->toDateString());
        $to = $this->dateOrDefault($input['to'] ?? null, now()->toDateString());

        if ($from === null || $to === null) {
            return ['ok' => false, 'error' => 'from and to must be YYYY-MM-DD dates.'];
        }

        if ($from > $to) {
            return ['ok' => false, 'error' => 'from must be on or before to.'];
        }

        $tables = $this->tablesForSpec($spec, $from, $to);

        if ($tables === []) {
            return [
                'ok' => false,
                'error' => 'Dataset "'.$dataset.'" has no Whale tables in this date range.',
                'hint' => $spec['about'],
            ];
        }

        $requestedGroups = $this->parseGroupBy((string) ($input['group_by'] ?? ''));
        $limit = (int) ($input['limit'] ?? 25);
        $limit = $limit > 0 ? min($limit, 100) : 25;

        $filters = [
            'platform' => $this->nullableString($input['platform'] ?? 'meta') ?? 'meta',
            'account_id' => $this->nullableString($input['account_id'] ?? null),
            'campaign_id' => $this->nullableString($input['campaign_id'] ?? null),
            'adset_id' => $this->nullableString($input['adset_id'] ?? null),
            'ad_id' => $this->nullableString($input['ad_id'] ?? null),
            'country' => $this->nullableString($input['country'] ?? null),
            'region' => $this->nullableString($input['region'] ?? null),
            'dimension' => $this->nullableString($input['dimension'] ?? null),
            'value_1' => $this->nullableString($input['value_1'] ?? null),
            'search' => $this->nullableString($input['search'] ?? null),
        ];

        $columns = $this->tables->columns($tables[0]);
        $groupBy = [];

        foreach ($requestedGroups as $column) {
            if (! in_array($column, self::GROUP_COLUMNS, true) || ! in_array($column, $columns, true)) {
                $allowed = array_values(array_intersect(self::GROUP_COLUMNS, $columns));

                return [
                    'ok' => false,
                    'error' => 'Cannot group by "'.$column.'" on '.$dataset.'.',
                    'allowed_group_by' => $allowed,
                ];
            }

            $groupBy[] = $column;
        }

        $metrics = array_values(array_intersect(self::METRIC_COLUMNS, $columns));
        $aggregate = $metrics !== [];
        $merged = [];

        foreach ($tables as $table) {
            $tableColumns = $this->tables->columns($table);

            try {
                $rows = $aggregate
                    ? $this->aggregateTable($table, $tableColumns, $groupBy, $metrics, $filters, $spec['uses_dates'] ? $from : null, $spec['uses_dates'] ? $to : null)
                    : $this->listTable($table, $tableColumns, $filters, $spec['uses_dates'] ? $from : null, $spec['uses_dates'] ? $to : null, $limit);
            } catch (Throwable) {
                continue;
            }

            foreach ($rows as $row) {
                if ($aggregate) {
                    $this->mergeAggregate($merged, $row, $groupBy, $metrics);

                    continue;
                }

                $merged[] = $row;
            }
        }

        $rows = array_values($merged);

        if ($aggregate) {
            foreach ($rows as $index => $row) {
                $rows[$index] = $this->withRates($row);
            }

            usort($rows, function (array $left, array $right): int {
                $spend = ($right['spend'] ?? 0) <=> ($left['spend'] ?? 0);

                if ($spend !== 0) {
                    return $spend;
                }

                return ($right['clicks'] ?? 0) <=> ($left['clicks'] ?? 0);
            });
        }

        $rows = array_slice($rows, 0, $limit);
        $rows = $this->enrich($rows);

        return [
            'ok' => true,
            'dataset' => $dataset,
            'from' => $spec['uses_dates'] ? $from : null,
            'to' => $spec['uses_dates'] ? $to : null,
            'filters' => array_filter($filters, fn (mixed $value): bool => $value !== null && $value !== ''),
            'group_by' => $groupBy,
            'row_count' => count($rows),
            'rows' => $rows,
            'note' => 'Quote only these numbers. Affiliate CPC/orders live in campaign_performance and conversin_* datasets.',
        ];
    }

    /**
     * @return array<string, array{
     *     key: string,
     *     about: string,
     *     grain: string,
     *     uses_dates: bool,
     *     group_by: list<string>,
     *     filters: list<string>
     * }>
     */
    private function specs(): array
    {
        $specs = [
            [
                'key' => 'accounts',
                'about' => 'Meta ad accounts: name, currency, timezone, status, spend cap.',
                'grain' => 'one row per ad account',
                'uses_dates' => false,
                'group_by' => ['status', 'currency', 'timezone'],
                'filters' => ['platform', 'account_id', 'search'],
            ],
            [
                'key' => 'campaigns',
                'about' => 'Campaign structure: name, objective, status, budget, learning.',
                'grain' => 'one row per campaign',
                'uses_dates' => false,
                'group_by' => ['account_id', 'status', 'objective'],
                'filters' => ['platform', 'account_id', 'campaign_id', 'search'],
            ],
            [
                'key' => 'adsets',
                'about' => 'Ad sets: budget, bid, optimization, learning status.',
                'grain' => 'one row per ad set',
                'uses_dates' => false,
                'group_by' => ['account_id', 'campaign_id', 'status', 'learning_status'],
                'filters' => ['platform', 'account_id', 'campaign_id', 'adset_id', 'search'],
            ],
            [
                'key' => 'ads',
                'about' => 'Ads: name, status, creative id, landing URL.',
                'grain' => 'one row per ad',
                'uses_dates' => false,
                'group_by' => ['account_id', 'campaign_id', 'adset_id', 'status'],
                'filters' => ['platform', 'account_id', 'campaign_id', 'adset_id', 'ad_id', 'search'],
            ],
            [
                'key' => 'creatives',
                'about' => 'Creative copy: headlines, primary text, CTA, landing URL.',
                'grain' => 'one row per creative',
                'uses_dates' => false,
                'group_by' => ['account_id', 'type', 'cta'],
                'filters' => ['platform', 'account_id', 'search'],
            ],
            [
                'key' => 'targeting',
                'about' => 'Targeting snapshot: countries, age, placements, devices, audiences.',
                'grain' => 'one row per entity targeting snapshot',
                'uses_dates' => false,
                'group_by' => ['account_id', 'campaign_id', 'entity_level'],
                'filters' => ['platform', 'account_id', 'campaign_id'],
            ],
            [
                'key' => 'stats_campaign_daily',
                'about' => 'Campaign-level daily spend, impressions, clicks, reach.',
                'grain' => 'campaign × day',
                'uses_dates' => true,
                'group_by' => ['account_id', 'campaign_id', 'stat_date'],
                'filters' => ['platform', 'account_id', 'campaign_id'],
            ],
            [
                'key' => 'stats_campaign_hourly',
                'about' => 'Campaign-level hourly spend, impressions, clicks.',
                'grain' => 'campaign × day × hour',
                'uses_dates' => true,
                'group_by' => ['account_id', 'campaign_id', 'stat_date', 'hour'],
                'filters' => ['platform', 'account_id', 'campaign_id'],
            ],
            [
                'key' => 'stats_daily',
                'about' => 'Ad-level daily stats including video metrics when present.',
                'grain' => 'ad × day',
                'uses_dates' => true,
                'group_by' => ['account_id', 'campaign_id', 'adset_id', 'ad_id', 'stat_date'],
                'filters' => ['platform', 'account_id', 'campaign_id', 'adset_id', 'ad_id'],
            ],
            [
                'key' => 'stats_hourly',
                'about' => 'Ad-set hourly spend, impressions, clicks.',
                'grain' => 'ad set × day × hour',
                'uses_dates' => true,
                'group_by' => ['account_id', 'campaign_id', 'adset_id', 'stat_date', 'hour'],
                'filters' => ['platform', 'account_id', 'campaign_id', 'adset_id'],
            ],
            [
                'key' => 'stats_geo_daily',
                'about' => 'Daily spend/clicks by country and region. Use this for geo questions.',
                'grain' => 'campaign × country × region × day',
                'uses_dates' => true,
                'group_by' => ['account_id', 'campaign_id', 'country', 'region', 'stat_date'],
                'filters' => ['platform', 'account_id', 'campaign_id', 'country', 'region'],
            ],
            [
                'key' => 'stats_breakdown_daily',
                'about' => 'Daily breakdowns. dimension is age_gender, device, placement, or region. value_1/value_2 hold the slice.',
                'grain' => 'ad set × dimension × day',
                'uses_dates' => true,
                'group_by' => ['account_id', 'campaign_id', 'adset_id', 'dimension', 'value_1', 'value_2', 'stat_date'],
                'filters' => ['platform', 'account_id', 'campaign_id', 'adset_id', 'dimension', 'value_1'],
            ],
            [
                'key' => 'conversions_daily',
                'about' => 'Platform conversion actions (link_click, etc.) with cost per action.',
                'grain' => 'ad set × action × day',
                'uses_dates' => true,
                'group_by' => ['account_id', 'campaign_id', 'adset_id', 'action_name', 'action_group', 'stat_date'],
                'filters' => ['platform', 'account_id', 'campaign_id', 'adset_id', 'search'],
            ],
            [
                'key' => 'quality_daily',
                'about' => 'Quality rankings per ad or campaign (quality_ranking, etc.).',
                'grain' => 'entity × metric × day',
                'uses_dates' => true,
                'group_by' => ['account_id', 'campaign_id', 'entity_level', 'metric', 'stat_date'],
                'filters' => ['platform', 'account_id', 'campaign_id'],
            ],
            [
                'key' => 'campaign_performance',
                'about' => 'Affiliate rollup: clicks, sum_cpc, orders, net_revenue by campaign day.',
                'grain' => 'campaign × day',
                'uses_dates' => true,
                'group_by' => ['campaign_id', 'date'],
                'filters' => ['campaign_id'],
            ],
            [
                'key' => 'conversin_aggregate',
                'about' => 'Affiliate orders and net revenue aggregated by campaign day (table name is spelled conversin).',
                'grain' => 'campaign × day',
                'uses_dates' => true,
                'group_by' => ['campaign_id', 'advertiser_id', 'date'],
                'filters' => ['campaign_id'],
            ],
            [
                'key' => 'conversin_mapping',
                'about' => 'Affiliate click-to-order mapping with subid, cpc, rpc, affiliate.',
                'grain' => 'one matched click/order row',
                'uses_dates' => true,
                'group_by' => ['campaign_id', 'affiliate', 'advertiser_id'],
                'filters' => ['campaign_id', 'search'],
            ],
            [
                'key' => 'reach_windows',
                'about' => 'Reach and frequency for 1d/7d windows.',
                'grain' => 'entity × window × as-of date',
                'uses_dates' => true,
                'group_by' => ['account_id', 'campaign_id', 'entity_level', 'window_key'],
                'filters' => ['platform', 'account_id', 'campaign_id'],
            ],
            [
                'key' => 'change_events',
                'about' => 'Platform change history: who changed what, when.',
                'grain' => 'one change event',
                'uses_dates' => true,
                'group_by' => ['account_id', 'actor', 'entity_level', 'change_type', 'event_type'],
                'filters' => ['platform', 'account_id', 'campaign_id', 'search'],
            ],
            [
                'key' => 'delivery_issues',
                'about' => 'Delivery issues currently or previously seen on entities.',
                'grain' => 'one issue row',
                'uses_dates' => false,
                'group_by' => ['account_id', 'entity_level', 'issue_type'],
                'filters' => ['platform', 'account_id', 'campaign_id'],
            ],
            [
                'key' => 'sync_status',
                'about' => 'Whale ingest health per account and dataset.',
                'grain' => 'platform × account × dataset',
                'uses_dates' => false,
                'group_by' => ['account_id', 'dataset', 'status'],
                'filters' => ['platform', 'account_id', 'search'],
            ],
        ];

        $keyed = [];

        foreach ($specs as $spec) {
            $keyed[$spec['key']] = $spec;
        }

        return $keyed;
    }

    /**
     * @param  array{key: string, uses_dates: bool}  $spec
     * @return list<string>
     */
    private function tablesForSpec(array $spec, string $from, string $to): array
    {
        return $this->tables->tablesFor($spec['key'], $from, $to);
    }

    /**
     * @param  list<string>  $columns
     * @param  list<string>  $groupBy
     * @param  list<string>  $metrics
     * @param  array<string, ?string>  $filters
     * @return list<array<string, mixed>>
     */
    private function aggregateTable(
        string $table,
        array $columns,
        array $groupBy,
        array $metrics,
        array $filters,
        ?string $from,
        ?string $to,
    ): array {
        $builder = DB::connection('whale')->table($table);
        $this->applyFilters($builder, $columns, $filters, $from, $to);

        $presentMetrics = array_values(array_intersect($metrics, $columns));

        if ($presentMetrics === []) {
            return [];
        }

        if ($groupBy !== []) {
            $builder->select($groupBy);
        }

        foreach ($presentMetrics as $metric) {
            $builder->selectRaw('SUM('.$this->wrap($metric).') as '.$metric);
        }

        if ($groupBy !== []) {
            $builder->groupBy($groupBy);
        }

        return $builder->get()->map(function (object $row) use ($groupBy, $presentMetrics): array {
            $out = [];

            foreach ($groupBy as $column) {
                $out[$column] = $row->{$column} ?? null;
            }

            foreach ($presentMetrics as $metric) {
                $out[$metric] = $this->numeric($row->{$metric} ?? 0, $metric);
            }

            return $out;
        })->all();
    }

    /**
     * @param  list<string>  $columns
     * @param  array<string, ?string>  $filters
     * @return list<array<string, mixed>>
     */
    private function listTable(
        string $table,
        array $columns,
        array $filters,
        ?string $from,
        ?string $to,
        int $limit,
    ): array {
        $select = array_values(array_filter(
            self::LIST_COLUMNS,
            fn (string $column): bool => in_array($column, $columns, true) && ! in_array($column, self::HIDDEN_COLUMNS, true),
        ));

        if ($select === []) {
            $select = array_values(array_filter(
                $columns,
                fn (string $column): bool => ! in_array($column, self::HIDDEN_COLUMNS, true),
            ));
            $select = array_slice($select, 0, 20);
        }

        if ($select === []) {
            return [];
        }

        $builder = DB::connection('whale')->table($table)->select($select);
        $this->applyFilters($builder, $columns, $filters, $from, $to);

        $order = $this->pick($columns, ['name', 'changed_at', 'last_success_at', 'campaign_id', 'account_id']);

        if ($order !== null) {
            $builder->orderBy($order);
        }

        return $builder->limit($limit)->get()->map(function (object $row) use ($select): array {
            $out = [];

            foreach ($select as $column) {
                $out[$column] = $row->{$column} ?? null;
            }

            return $out;
        })->all();
    }

    /**
     * @param  list<string>  $columns
     * @param  array<string, ?string>  $filters
     */
    private function applyFilters(Builder $builder, array $columns, array $filters, ?string $from, ?string $to): void
    {
        $this->whereEquals($builder, $columns, ['platform'], $filters['platform']);
        $this->whereEquals($builder, $columns, ['account_id', 'ad_account_id'], $filters['account_id']);
        $this->whereEquals($builder, $columns, ['campaign_id', 'external_campaign_id'], $filters['campaign_id']);
        $this->whereEquals($builder, $columns, ['adset_id'], $filters['adset_id']);
        $this->whereEquals($builder, $columns, ['ad_id'], $filters['ad_id']);
        $this->whereEquals($builder, $columns, ['country'], $filters['country']);
        $this->whereEquals($builder, $columns, ['region'], $filters['region']);
        $this->whereEquals($builder, $columns, ['dimension'], $filters['dimension']);
        $this->whereEquals($builder, $columns, ['value_1'], $filters['value_1']);

        $search = $filters['search'] ?? null;
        $nameCol = $this->pick($columns, ['name', 'campaign_name', 'entity_name', 'primary_text', 'action_name', 'dataset', 'affiliate']);

        if (is_string($search) && $search !== '' && $nameCol !== null) {
            $builder->where($nameCol, 'like', '%'.$search.'%');
        }

        if ($from === null || $to === null) {
            return;
        }

        $dateCol = $this->pick($columns, ['stat_date', 'date', 'as_of_date', 'changed_at', 'captured_at']);

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

        if (in_array($dateCol, ['changed_at', 'captured_at'], true)) {
            $builder->where($dateCol, '>=', $from.' 00:00:00')
                ->where($dateCol, '<=', $to.' 23:59:59');

            return;
        }

        $builder->whereBetween($dateCol, [$from, $to]);
    }

    /**
     * @param  list<string>  $columns
     * @param  list<string>  $candidates
     */
    private function whereEquals(Builder $builder, array $columns, array $candidates, ?string $value): void
    {
        if ($value === null || $value === '') {
            return;
        }

        $column = $this->pick($columns, $candidates);

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

        $builder->where($column, $value);
    }

    /**
     * @param  array<string, array<string, mixed>>  $merged
     * @param  array<string, mixed>  $row
     * @param  list<string>  $groupBy
     * @param  list<string>  $metrics
     */
    private function mergeAggregate(array &$merged, array $row, array $groupBy, array $metrics): void
    {
        $key = $groupBy === []
            ? '_total'
            : implode('|', array_map(fn (string $column): string => (string) ($row[$column] ?? ''), $groupBy));

        if (! isset($merged[$key])) {
            $merged[$key] = $row;

            return;
        }

        foreach ($metrics as $metric) {
            if (! array_key_exists($metric, $row)) {
                continue;
            }

            $merged[$key][$metric] = $this->numeric(
                ($merged[$key][$metric] ?? 0) + $row[$metric],
                $metric,
            );
        }
    }

    /**
     * @param  array<string, mixed>  $row
     * @return array<string, mixed>
     */
    private function withRates(array $row): array
    {
        $spend = (float) ($row['spend'] ?? 0);
        $clicks = (int) ($row['clicks'] ?? 0);
        $impressions = (int) ($row['impressions'] ?? 0);

        if (array_key_exists('spend', $row) || array_key_exists('clicks', $row)) {
            $row['cpc'] = $clicks > 0 ? round($spend / $clicks, 4) : null;
        }

        if (array_key_exists('clicks', $row) || array_key_exists('impressions', $row)) {
            $row['ctr'] = $impressions > 0 ? round($clicks / $impressions, 4) : null;
        }

        return $row;
    }

    /**
     * @param  list<array<string, mixed>>  $rows
     * @return list<array<string, mixed>>
     */
    private function enrich(array $rows): array
    {
        $campaignIds = $this->idsFrom($rows, 'campaign_id');
        $accountIds = $this->idsFrom($rows, 'account_id');
        $adIds = $this->idsFrom($rows, 'ad_id');

        $campaignNames = $this->names('campaigns', 'campaign_id', $campaignIds);
        $accountNames = $this->names('accounts', 'account_id', $accountIds);
        $adNames = $this->names('ads', 'ad_id', $adIds);

        foreach ($rows as $index => $row) {
            $campaignId = isset($row['campaign_id']) ? trim((string) $row['campaign_id']) : '';
            $accountId = isset($row['account_id']) ? trim((string) $row['account_id']) : '';
            $adId = isset($row['ad_id']) ? trim((string) $row['ad_id']) : '';

            if ($campaignId !== '' && ! isset($row['campaign_name']) && isset($campaignNames[$campaignId])) {
                $rows[$index]['campaign_name'] = $campaignNames[$campaignId];
            }

            if ($accountId !== '' && ! isset($row['account_name']) && isset($accountNames[$accountId])) {
                $rows[$index]['account_name'] = $accountNames[$accountId];
            }

            if ($adId !== '' && ! isset($row['ad_name']) && isset($adNames[$adId])) {
                $rows[$index]['ad_name'] = $adNames[$adId];
            }
        }

        return $rows;
    }

    /**
     * @param  list<array<string, mixed>>  $rows
     * @return list<string>
     */
    private function idsFrom(array $rows, string $column): array
    {
        $ids = [];

        foreach ($rows as $row) {
            if (! isset($row[$column])) {
                continue;
            }

            $id = trim((string) $row[$column]);

            if ($id !== '') {
                $ids[] = $id;
            }
        }

        return array_values(array_unique($ids));
    }

    /**
     * @param  list<string>  $ids
     * @return array<string, string>
     */
    private function names(string $dataset, string $idColumn, array $ids): array
    {
        if ($ids === []) {
            return [];
        }

        $table = $this->tables->name($dataset);

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

        $columns = $this->tables->columns($table);
        $idCol = $this->pick($columns, [$idColumn]);
        $nameCol = $this->pick($columns, ['name']);

        if ($idCol === null || $nameCol === null) {
            return [];
        }

        try {
            $map = [];

            foreach (DB::connection('whale')->table($table)->whereIn($idCol, $ids)->get([$idCol, $nameCol]) as $row) {
                $id = trim((string) ($row->{$idCol} ?? ''));
                $name = trim((string) ($row->{$nameCol} ?? ''));

                if ($id !== '' && $name !== '') {
                    $map[$id] = $name;
                }
            }

            return $map;
        } catch (Throwable) {
            return [];
        }
    }

    /**
     * @return list<string>
     */
    private function parseGroupBy(string $value): array
    {
        if (trim($value) === '') {
            return [];
        }

        $parts = preg_split('/\s*,\s*/', strtolower($value)) ?: [];

        return array_values(array_filter($parts, fn (string $part): bool => $part !== ''));
    }

    private function dateOrDefault(mixed $value, string $default): ?string
    {
        if ($value === null || $value === '') {
            return $default;
        }

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

        try {
            return Carbon::parse($raw)->toDateString();
        } catch (Throwable) {
            return null;
        }
    }

    /**
     * @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 wrap(string $column): string
    {
        return DB::connection('whale')->getQueryGrammar()->wrap($column);
    }

    private function numeric(mixed $value, string $metric): int|float
    {
        if (in_array($metric, ['impressions', 'clicks', 'link_clicks', 'outbound_clicks', 'reach', 'video_plays', 'video_3s_views', 'thruplays'], true)) {
            return (int) $value;
        }

        return round((float) $value, 4);
    }

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

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

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