<?php

namespace App\Campaigns;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Schema;
use Throwable;

class CampaignPerformanceWhaleEnrichment
{
    /**
     * @param  array<string, mixed>  $snapshot
     * @return array<string, mixed>
     */
    public function enrichSnapshot(array $snapshot, string $externalCampaignId): array
    {
        $campaignId = trim($externalCampaignId);

        if (! $this->isEnabled()) {
            $snapshot['affiliate'] = ['matched' => false, 'skipped' => 'disabled'];

            return $snapshot;
        }

        if ($campaignId === '') {
            $snapshot['affiliate'] = ['matched' => false, 'skipped' => 'empty_campaign_id'];

            return $snapshot;
        }

        try {
            $windows = [
                '3d' => $this->rollup($campaignId, 3),
                '7d' => $this->rollup($campaignId, 7),
            ];
            $matched = ($windows['3d']['days_with_rows'] ?? 0) > 0
                || ($windows['7d']['days_with_rows'] ?? 0) > 0;

            if (! $matched) {
                $snapshot['affiliate'] = [
                    'matched' => false,
                    'campaign_id' => $campaignId,
                ];

                return $snapshot;
            }

            $snapshot['affiliate'] = [
                'matched' => true,
                'campaign_id' => $campaignId,
                'data_through' => $this->dataThrough($campaignId),
                '3d' => $windows['3d'],
                '7d' => $windows['7d'],
            ];
        } catch (Throwable $exception) {
            Log::warning('Campaign performance whale enrichment failed', [
                'external_campaign_id' => $campaignId,
                'error' => $exception->getMessage(),
            ]);
            $snapshot['affiliate'] = ['matched' => false, 'skipped' => 'error'];
        }

        return $snapshot;
    }

    /**
     * @return list<array{
     *     date: string,
     *     clicks: int,
     *     sum_cpc: float,
     *     orders: float,
     *     net_revenue: float
     * }>
     */
    public function dailyRows(string $externalCampaignId, int $limit = 14): array
    {
        $campaignId = trim($externalCampaignId);

        if (! $this->isEnabled() || $campaignId === '' || ! Schema::connection('whale')->hasTable('arb_adops_campaign_performance')) {
            return [];
        }

        $rows = DB::connection('whale')
            ->table('arb_adops_campaign_performance')
            ->where('campaign_id', $campaignId)
            ->orderByDesc('date')
            ->orderByDesc('id')
            ->limit(max(1, $limit))
            ->get();

        return $rows->map(fn (object $row): array => [
            'date' => (string) $row->date,
            'clicks' => (int) $row->clicks,
            'sum_cpc' => round((float) $row->sum_cpc, 4),
            'orders' => round((float) $row->orders, 4),
            'net_revenue' => round((float) $row->net_revenue, 4),
        ])->all();
    }

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

        try {
            return Schema::connection('whale')->hasTable('arb_adops_campaign_performance')
                || Schema::connection('whale')->hasTable('roi_dashboard_campaign_stats');
        } catch (Throwable) {
            return false;
        }
    }

    /**
     * Affiliate rollup ending `lagDays` before today (advertiser reporting delay).
     *
     * @return array{
     *     from: string,
     *     to: string,
     *     clicks: int,
     *     sum_cpc: float,
     *     orders: float,
     *     net_revenue: float,
     *     days_with_rows: int
     * }
     */
    public function rollupLagged(string $campaignId, int $days, int $lagDays = 2): array
    {
        $to = now()->copy()->startOfDay()->subDays(max(0, $lagDays));
        $from = $to->copy()->subDays(max(0, $days - 1));

        return $this->rollupBetween($campaignId, $from->toDateString(), $to->toDateString());
    }

    /**
     * ROI stats for a campaign that has no arb_adops_campaign_performance rows in the window.
     * Spend and arb_sales_amount are the verdict inputs. estimated_revenue is kept aside.
     *
     * @return array{
     *     from: string,
     *     to: string,
     *     clicks: int,
     *     sum_cpc: float,
     *     orders: float,
     *     net_revenue: float,
     *     days_with_rows: int,
     *     spend: float,
     *     revenue_source: string,
     *     estimated_revenue: float,
     *     conversions: float,
     *     subscriptions: float,
     *     installs: float,
     *     bookings: float,
     *     leads: float,
     *     arb_orders: float
     * }
     */
    public function rollupRoiLagged(string $campaignId, string $platform, int $days, int $lagDays = 2): array
    {
        $to = now()->copy()->startOfDay()->subDays(max(0, $lagDays));
        $from = $to->copy()->subDays(max(0, $days - 1));

        return $this->rollupRoiBetween($campaignId, $platform, $from->toDateString(), $to->toDateString());
    }

    /**
     * @return array{
     *     from: string,
     *     to: string,
     *     clicks: int,
     *     sum_cpc: float,
     *     orders: float,
     *     net_revenue: float,
     *     days_with_rows: int
     * }
     */
    private function rollup(string $campaignId, int $days): array
    {
        $to = now()->copy()->startOfDay();
        $from = $to->copy()->subDays($days - 1);

        return $this->rollupBetween($campaignId, $from->toDateString(), $to->toDateString());
    }

    /**
     * @return array{
     *     from: string,
     *     to: string,
     *     clicks: int,
     *     sum_cpc: float,
     *     orders: float,
     *     net_revenue: float,
     *     days_with_rows: int
     * }
     */
    private function rollupBetween(string $campaignId, string $from, string $to): array
    {
        if (! Schema::connection('whale')->hasTable('arb_adops_campaign_performance')) {
            return $this->emptyRollup($from, $to);
        }

        $summary = DB::connection('whale')
            ->table('arb_adops_campaign_performance')
            ->where('campaign_id', $campaignId)
            ->whereBetween('date', [$from, $to])
            ->selectRaw('COUNT(*) as days_with_rows')
            ->selectRaw('COALESCE(SUM(clicks), 0) as clicks')
            ->selectRaw('COALESCE(SUM(sum_cpc), 0) as sum_cpc')
            ->selectRaw('COALESCE(SUM(orders), 0) as orders')
            ->selectRaw('COALESCE(SUM(net_revenue), 0) as net_revenue')
            ->first();

        return [
            'from' => $from,
            'to' => $to,
            'clicks' => (int) ($summary->days_with_rows ? $summary->clicks : 0),
            'sum_cpc' => round((float) ($summary->sum_cpc ?? 0), 4),
            'orders' => round((float) ($summary->orders ?? 0), 4),
            'net_revenue' => round((float) ($summary->net_revenue ?? 0), 4),
            'days_with_rows' => (int) ($summary->days_with_rows ?? 0),
        ];
    }

    private function dataThrough(string $campaignId): ?string
    {
        $value = DB::connection('whale')
            ->table('arb_adops_campaign_performance')
            ->where('campaign_id', $campaignId)
            ->max('date');

        return is_string($value) && $value !== '' ? $value : null;
    }

    /**
     * @return array{
     *     from: string,
     *     to: string,
     *     clicks: int,
     *     sum_cpc: float,
     *     orders: float,
     *     net_revenue: float,
     *     days_with_rows: int,
     *     spend: float,
     *     revenue_source: string,
     *     estimated_revenue: float,
     *     conversions: float,
     *     subscriptions: float,
     *     installs: float,
     *     bookings: float,
     *     leads: float,
     *     arb_orders: float
     * }
     */
    private function rollupRoiBetween(string $campaignId, string $platform, string $from, string $to): array
    {
        $empty = [
            ...$this->emptyRollup($from, $to),
            'spend' => 0.0,
            'revenue_source' => 'roi_dashboard',
            'estimated_revenue' => 0.0,
            'conversions' => 0.0,
            'subscriptions' => 0.0,
            'installs' => 0.0,
            'bookings' => 0.0,
            'leads' => 0.0,
            'arb_orders' => 0.0,
        ];

        if ($platform === '' || ! Schema::connection('whale')->hasTable('roi_dashboard_campaign_stats')) {
            return $empty;
        }

        $rows = DB::connection('whale')
            ->table('roi_dashboard_campaign_stats')
            ->where('campaign_id', $campaignId)
            ->whereRaw('LOWER(platform) = ?', [strtolower($platform)])
            ->whereBetween('date', [$from, $to])
            ->get(['date', 'spent as spend', 'conversions', 'subscriptions', 'installs', 'bookings', 'leads', 'estimated_revenue', 'arb_orders', 'arb_sales_amount']);

        if ($rows->isEmpty()) {
            return $empty;
        }

        $dates = [];
        $spend = 0.0;
        $sales = 0.0;
        $orders = 0.0;
        $estimated = 0.0;
        $conversions = 0.0;
        $subscriptions = 0.0;
        $installs = 0.0;
        $bookings = 0.0;
        $leads = 0.0;

        foreach ($rows as $row) {
            $dates[(string) $row->date] = true;
            $spend += (float) $row->spend;
            $sales += (float) $row->arb_sales_amount;
            $orders += (float) $row->arb_orders;
            $estimated += (float) $row->estimated_revenue;
            $conversions += (float) $row->conversions;
            $subscriptions += (float) $row->subscriptions;
            $installs += (float) $row->installs;
            $bookings += (float) $row->bookings;
            $leads += (float) $row->leads;
        }

        return [
            'from' => $from,
            'to' => $to,
            'clicks' => 0,
            'sum_cpc' => 0.0,
            'orders' => round($orders, 4),
            'net_revenue' => round($sales, 4),
            'days_with_rows' => count($dates),
            'spend' => round($spend, 2),
            'revenue_source' => 'roi_dashboard',
            'estimated_revenue' => round($estimated, 4),
            'conversions' => round($conversions, 4),
            'subscriptions' => round($subscriptions, 4),
            'installs' => round($installs, 4),
            'bookings' => round($bookings, 4),
            'leads' => round($leads, 4),
            'arb_orders' => round($orders, 4),
        ];
    }

    /**
     * @return array{
     *     from: string,
     *     to: string,
     *     clicks: int,
     *     sum_cpc: float,
     *     orders: float,
     *     net_revenue: float,
     *     days_with_rows: int
     * }
     */
    private function emptyRollup(string $from, string $to): array
    {
        return [
            'from' => $from,
            'to' => $to,
            'clicks' => 0,
            'sum_cpc' => 0.0,
            'orders' => 0.0,
            'net_revenue' => 0.0,
            'days_with_rows' => 0,
        ];
    }
}