<?php

namespace App\Services\Portal;

use App\Models\AlertLog;
use App\Models\Client;
use App\Models\Ga4TrafficSnapshot;
use App\Models\GeneratedReport;
use App\Models\GoogleCampaignSnapshot;
use App\Models\MetaCampaignSnapshot;
use App\Models\SearchConsoleQuerySnapshot;
use Carbon\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

class PortalMetricsService
{
    public function overview(Client $client, ?string $from = null, ?string $to = null): array
    {
        $toDate = $to ? Carbon::parse($to) : now();
        $fromDate = $from ? Carbon::parse($from) : $toDate->copy()->subDays(29);

        $google = $this->googleSummary($client, $fromDate, $toDate);
        $meta = $this->metaSummary($client, $fromDate, $toDate);
        $gsc = $this->gscSummary($client, $fromDate, $toDate);
        $ga4 = $this->ga4Summary($client, $fromDate, $toDate);

        return [
            'date_from' => $fromDate->toDateString(),
            'date_to' => $toDate->toDateString(),
            'currency' => $client->currency_code ?: 'TRY',
            'totals' => [
                'spend' => $google['cost'] + $meta['spend'],
                'clicks' => $google['clicks'] + $meta['clicks'],
                'impressions' => $google['impressions'] + $meta['impressions'],
                'conversions' => $google['conversions'] + $meta['conversions'] + $ga4['conversions'],
                'organic_clicks' => $gsc['clicks'],
                'sessions' => $ga4['sessions'],
                'open_alerts' => AlertLog::where('client_id', $client->id)->whereNull('resolved_at')->count(),
                'reports' => GeneratedReport::where('client_id', $client->id)->count(),
            ],
            'channels' => [
                'google_ads' => $google,
                'meta_ads' => $meta,
                'search_console' => $gsc,
                'ga4' => $ga4,
            ],
            'top_google_campaigns' => $this->topGoogleCampaigns($client, $fromDate, $toDate),
            'top_meta_campaigns' => $this->topMetaCampaigns($client, $fromDate, $toDate),
        ];
    }

    private function googleSummary(Client $client, Carbon $from, Carbon $to): array
    {
        $row = GoogleCampaignSnapshot::where('client_id', $client->id)
            ->whereBetween('date', [$from->toDateString(), $to->toDateString()])
            ->selectRaw('COALESCE(SUM(cost),0) as cost, COALESCE(SUM(clicks),0) as clicks, COALESCE(SUM(impressions),0) as impressions, COALESCE(SUM(conversions),0) as conversions')
            ->first();

        return [
            'label' => 'Google Ads',
            'cost' => (float) ($row->cost ?? 0),
            'clicks' => (int) ($row->clicks ?? 0),
            'impressions' => (int) ($row->impressions ?? 0),
            'conversions' => (float) ($row->conversions ?? 0),
            'ctr' => $this->rate((int) ($row->clicks ?? 0), (int) ($row->impressions ?? 0)),
            'cpc' => $this->rate((float) ($row->cost ?? 0), (int) ($row->clicks ?? 0)),
        ];
    }

    private function metaSummary(Client $client, Carbon $from, Carbon $to): array
    {
        $row = MetaCampaignSnapshot::where('client_id', $client->id)
            ->whereBetween('date', [$from->toDateString(), $to->toDateString()])
            ->selectRaw('COALESCE(SUM(spend),0) as spend, COALESCE(SUM(clicks),0) as clicks, COALESCE(SUM(impressions),0) as impressions, COALESCE(SUM(conversions),0) as conversions, COALESCE(SUM(leads),0) as leads, COALESCE(SUM(purchases),0) as purchases')
            ->first();

        return [
            'label' => 'Meta Ads',
            'spend' => (float) ($row->spend ?? 0),
            'clicks' => (int) ($row->clicks ?? 0),
            'impressions' => (int) ($row->impressions ?? 0),
            'conversions' => (float) ($row->conversions ?? 0),
            'leads' => (float) ($row->leads ?? 0),
            'purchases' => (float) ($row->purchases ?? 0),
            'ctr' => $this->rate((int) ($row->clicks ?? 0), (int) ($row->impressions ?? 0)),
            'cpc' => $this->rate((float) ($row->spend ?? 0), (int) ($row->clicks ?? 0)),
        ];
    }

    private function gscSummary(Client $client, Carbon $from, Carbon $to): array
    {
        $row = SearchConsoleQuerySnapshot::where('client_id', $client->id)
            ->whereBetween('date', [$from->toDateString(), $to->toDateString()])
            ->selectRaw('COALESCE(SUM(clicks),0) as clicks, COALESCE(SUM(impressions),0) as impressions, COALESCE(AVG(position),0) as position')
            ->first();

        return [
            'label' => 'Search Console',
            'clicks' => (int) ($row->clicks ?? 0),
            'impressions' => (int) ($row->impressions ?? 0),
            'ctr' => $this->rate((int) ($row->clicks ?? 0), (int) ($row->impressions ?? 0)),
            'position' => (float) ($row->position ?? 0),
        ];
    }

    private function ga4Summary(Client $client, Carbon $from, Carbon $to): array
    {
        $row = Ga4TrafficSnapshot::where('client_id', $client->id)
            ->whereBetween('date', [$from->toDateString(), $to->toDateString()])
            ->selectRaw('COALESCE(SUM(sessions),0) as sessions, COALESCE(SUM(total_users),0) as users, COALESCE(SUM(conversions),0) as conversions, COALESCE(SUM(total_revenue),0) as revenue')
            ->first();

        return [
            'label' => 'GA4',
            'sessions' => (int) ($row->sessions ?? 0),
            'users' => (int) ($row->users ?? 0),
            'conversions' => (float) ($row->conversions ?? 0),
            'revenue' => (float) ($row->revenue ?? 0),
        ];
    }

    private function topGoogleCampaigns(Client $client, Carbon $from, Carbon $to): Collection
    {
        return GoogleCampaignSnapshot::where('client_id', $client->id)
            ->whereBetween('date', [$from->toDateString(), $to->toDateString()])
            ->select('campaign_name', DB::raw('SUM(cost) as cost'), DB::raw('SUM(clicks) as clicks'), DB::raw('SUM(conversions) as conversions'))
            ->groupBy('campaign_name')
            ->orderByDesc('cost')
            ->limit(5)
            ->get();
    }

    private function topMetaCampaigns(Client $client, Carbon $from, Carbon $to): Collection
    {
        return MetaCampaignSnapshot::where('client_id', $client->id)
            ->whereBetween('date', [$from->toDateString(), $to->toDateString()])
            ->select('campaign_name', DB::raw('SUM(spend) as spend'), DB::raw('SUM(clicks) as clicks'), DB::raw('SUM(conversions) as conversions'))
            ->groupBy('campaign_name')
            ->orderByDesc('spend')
            ->limit(5)
            ->get();
    }

    private function rate(float|int $numerator, float|int $denominator): float
    {
        if ((float) $denominator === 0.0) {
            return 0.0;
        }

        return round(((float) $numerator / (float) $denominator) * 100, 4);
    }
}
