<?php

namespace App\Services\Reports;

use App\Models\Client;
use App\Models\Ga4TrafficSnapshot;
use App\Models\GoogleCampaignSnapshot;
use App\Models\GoogleKeywordSnapshot;
use App\Models\GoogleSearchTermSnapshot;
use App\Models\MetaAdSnapshot;
use App\Models\MetaAdsetSnapshot;
use App\Models\MetaCampaignSnapshot;
use App\Models\SearchConsolePageSnapshot;
use App\Models\SearchConsoleQuerySnapshot;
use Carbon\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

class ReportDatasetBuilder
{
    /**
     * @param array<int, string> $channels
     */
    public function build(Client $client, Carbon $dateFrom, Carbon $dateTo, array $channels): array
    {
        $channels = $this->normalizeChannels($channels);

        return [
            'client' => $client,
            'period' => [
                'from' => $dateFrom->toDateString(),
                'to' => $dateTo->toDateString(),
                'days' => $dateFrom->diffInDays($dateTo) + 1,
            ],
            'channels' => $channels,
            'google_ads' => in_array('google_ads', $channels, true) ? $this->googleAds($client, $dateFrom, $dateTo) : null,
            'meta_ads' => in_array('meta_ads', $channels, true) ? $this->metaAds($client, $dateFrom, $dateTo) : null,
            'search_console' => in_array('search_console', $channels, true) ? $this->searchConsole($client, $dateFrom, $dateTo) : null,
            'ga4' => in_array('ga4', $channels, true) ? $this->ga4($client, $dateFrom, $dateTo) : null,
            'generated_at' => now()->toDateTimeString(),
        ];
    }

    /**
     * @param array<int, string> $channels
     * @return array<int, string>
     */
    private function normalizeChannels(array $channels): array
    {
        $allowed = ['google_ads', 'meta_ads', 'search_console', 'ga4'];
        $selected = array_values(array_intersect($allowed, $channels));

        return $selected ?: $allowed;
    }

    private function googleAds(Client $client, Carbon $from, Carbon $to): array
    {
        $base = GoogleCampaignSnapshot::query()
            ->where('client_id', $client->id)
            ->whereBetween('date', [$from->toDateString(), $to->toDateString()]);

        $totals = (clone $base)
            ->selectRaw('COALESCE(SUM(impressions),0) as impressions')
            ->selectRaw('COALESCE(SUM(clicks),0) as clicks')
            ->selectRaw('COALESCE(SUM(cost),0) as cost')
            ->selectRaw('COALESCE(SUM(conversions),0) as conversions')
            ->first();

        $daily = (clone $base)
            ->selectRaw('date, COALESCE(SUM(impressions),0) as impressions, COALESCE(SUM(clicks),0) as clicks, COALESCE(SUM(cost),0) as cost, COALESCE(SUM(conversions),0) as conversions')
            ->groupBy('date')
            ->orderBy('date')
            ->get();

        $campaigns = (clone $base)
            ->select('campaign_id', 'campaign_name')
            ->selectRaw('COALESCE(SUM(impressions),0) as impressions, COALESCE(SUM(clicks),0) as clicks, COALESCE(SUM(cost),0) as cost, COALESCE(SUM(conversions),0) as conversions')
            ->groupBy('campaign_id', 'campaign_name')
            ->orderByDesc(DB::raw('SUM(cost)'))
            ->limit(20)
            ->get();

        $keywords = GoogleKeywordSnapshot::query()
            ->where('client_id', $client->id)
            ->whereBetween('date', [$from->toDateString(), $to->toDateString()])
            ->select('keyword_text', 'match_type', 'campaign_name')
            ->selectRaw('COALESCE(SUM(impressions),0) as impressions, COALESCE(SUM(clicks),0) as clicks, COALESCE(SUM(cost),0) as cost, COALESCE(SUM(conversions),0) as conversions')
            ->groupBy('keyword_text', 'match_type', 'campaign_name')
            ->orderByDesc(DB::raw('SUM(cost)'))
            ->limit(20)
            ->get();

        $searchTerms = GoogleSearchTermSnapshot::query()
            ->where('client_id', $client->id)
            ->whereBetween('date', [$from->toDateString(), $to->toDateString()])
            ->select('search_term', 'campaign_name')
            ->selectRaw('COALESCE(SUM(impressions),0) as impressions, COALESCE(SUM(clicks),0) as clicks, COALESCE(SUM(cost),0) as cost, COALESCE(SUM(conversions),0) as conversions')
            ->groupBy('search_term', 'campaign_name')
            ->orderByDesc(DB::raw('SUM(cost)'))
            ->limit(20)
            ->get();

        return [
            'totals' => $this->adTotals($totals),
            'daily' => $this->normalizeRows($daily),
            'campaigns' => $this->normalizeRows($campaigns),
            'keywords' => $this->normalizeRows($keywords),
            'search_terms' => $this->normalizeRows($searchTerms),
        ];
    }

    private function metaAds(Client $client, Carbon $from, Carbon $to): array
    {
        $base = MetaCampaignSnapshot::query()
            ->where('client_id', $client->id)
            ->whereBetween('date', [$from->toDateString(), $to->toDateString()]);

        $totals = (clone $base)
            ->selectRaw('COALESCE(SUM(impressions),0) as impressions')
            ->selectRaw('COALESCE(SUM(reach),0) as reach')
            ->selectRaw('COALESCE(SUM(clicks),0) as clicks')
            ->selectRaw('COALESCE(SUM(inline_link_clicks),0) as inline_link_clicks')
            ->selectRaw('COALESCE(SUM(spend),0) as spend')
            ->selectRaw('COALESCE(SUM(conversions),0) as conversions')
            ->selectRaw('COALESCE(SUM(leads),0) as leads')
            ->selectRaw('COALESCE(SUM(purchases),0) as purchases')
            ->selectRaw('COALESCE(SUM(purchase_value),0) as purchase_value')
            ->first();

        $daily = (clone $base)
            ->selectRaw('date, COALESCE(SUM(impressions),0) as impressions, COALESCE(SUM(clicks),0) as clicks, COALESCE(SUM(spend),0) as spend, COALESCE(SUM(leads),0) as leads, COALESCE(SUM(conversions),0) as conversions')
            ->groupBy('date')
            ->orderBy('date')
            ->get();

        $campaigns = (clone $base)
            ->select('campaign_id', 'campaign_name', 'objective')
            ->selectRaw('COALESCE(SUM(impressions),0) as impressions, COALESCE(SUM(reach),0) as reach, COALESCE(SUM(clicks),0) as clicks, COALESCE(SUM(spend),0) as spend, COALESCE(SUM(leads),0) as leads, COALESCE(SUM(conversions),0) as conversions, COALESCE(SUM(purchases),0) as purchases')
            ->groupBy('campaign_id', 'campaign_name', 'objective')
            ->orderByDesc(DB::raw('SUM(spend)'))
            ->limit(20)
            ->get();

        $adsets = MetaAdsetSnapshot::query()
            ->where('client_id', $client->id)
            ->whereBetween('date', [$from->toDateString(), $to->toDateString()])
            ->select('campaign_name', 'adset_id', 'adset_name')
            ->selectRaw('COALESCE(SUM(impressions),0) as impressions, COALESCE(SUM(reach),0) as reach, COALESCE(SUM(clicks),0) as clicks, COALESCE(SUM(spend),0) as spend, COALESCE(SUM(leads),0) as leads, COALESCE(SUM(conversions),0) as conversions')
            ->groupBy('campaign_name', 'adset_id', 'adset_name')
            ->orderByDesc(DB::raw('SUM(spend)'))
            ->limit(20)
            ->get();

        $ads = MetaAdSnapshot::query()
            ->where('client_id', $client->id)
            ->whereBetween('date', [$from->toDateString(), $to->toDateString()])
            ->select('campaign_name', 'adset_name', 'ad_id', 'ad_name')
            ->selectRaw('COALESCE(SUM(impressions),0) as impressions, COALESCE(SUM(reach),0) as reach, COALESCE(SUM(clicks),0) as clicks, COALESCE(SUM(spend),0) as spend, COALESCE(SUM(leads),0) as leads, COALESCE(SUM(conversions),0) as conversions')
            ->groupBy('campaign_name', 'adset_name', 'ad_id', 'ad_name')
            ->orderByDesc(DB::raw('SUM(spend)'))
            ->limit(20)
            ->get();

        return [
            'totals' => $this->metaTotals($totals),
            'daily' => $this->normalizeRows($daily),
            'campaigns' => $this->normalizeRows($campaigns),
            'adsets' => $this->normalizeRows($adsets),
            'ads' => $this->normalizeRows($ads),
        ];
    }

    private function searchConsole(Client $client, Carbon $from, Carbon $to): array
    {
        $queryBase = SearchConsoleQuerySnapshot::query()
            ->where('client_id', $client->id)
            ->whereBetween('date', [$from->toDateString(), $to->toDateString()]);

        $totals = (clone $queryBase)
            ->selectRaw('COALESCE(SUM(clicks),0) as clicks')
            ->selectRaw('COALESCE(SUM(impressions),0) as impressions')
            ->selectRaw('COALESCE(SUM(ctr * impressions) / NULLIF(SUM(impressions),0),0) as ctr')
            ->selectRaw('COALESCE(SUM(position * impressions) / NULLIF(SUM(impressions),0),0) as position')
            ->first();

        $queries = (clone $queryBase)
            ->select('query_text')
            ->selectRaw('COALESCE(SUM(clicks),0) as clicks, COALESCE(SUM(impressions),0) as impressions, COALESCE(SUM(ctr * impressions) / NULLIF(SUM(impressions),0),0) as ctr, COALESCE(SUM(position * impressions) / NULLIF(SUM(impressions),0),0) as position')
            ->groupBy('query_text')
            ->orderByDesc(DB::raw('SUM(clicks)'))
            ->limit(30)
            ->get();

        $pages = SearchConsolePageSnapshot::query()
            ->where('client_id', $client->id)
            ->whereBetween('date', [$from->toDateString(), $to->toDateString()])
            ->select('page_url')
            ->selectRaw('COALESCE(SUM(clicks),0) as clicks, COALESCE(SUM(impressions),0) as impressions, COALESCE(SUM(ctr * impressions) / NULLIF(SUM(impressions),0),0) as ctr, COALESCE(SUM(position * impressions) / NULLIF(SUM(impressions),0),0) as position')
            ->groupBy('page_url')
            ->orderByDesc(DB::raw('SUM(clicks)'))
            ->limit(30)
            ->get();

        $daily = (clone $queryBase)
            ->selectRaw('date, COALESCE(SUM(clicks),0) as clicks, COALESCE(SUM(impressions),0) as impressions, COALESCE(SUM(position * impressions) / NULLIF(SUM(impressions),0),0) as position')
            ->groupBy('date')
            ->orderBy('date')
            ->get();

        return [
            'totals' => $this->gscTotals($totals),
            'daily' => $this->normalizeRows($daily),
            'queries' => $this->normalizeRows($queries),
            'pages' => $this->normalizeRows($pages),
        ];
    }

    private function ga4(Client $client, Carbon $from, Carbon $to): array
    {
        $base = Ga4TrafficSnapshot::query()
            ->where('client_id', $client->id)
            ->whereBetween('date', [$from->toDateString(), $to->toDateString()]);

        $totals = (clone $base)
            ->selectRaw('COALESCE(SUM(sessions),0) as sessions')
            ->selectRaw('COALESCE(SUM(total_users),0) as total_users')
            ->selectRaw('COALESCE(SUM(screen_page_views),0) as screen_page_views')
            ->selectRaw('COALESCE(SUM(conversions),0) as conversions')
            ->selectRaw('COALESCE(SUM(total_revenue),0) as total_revenue')
            ->selectRaw('COALESCE(SUM(event_count),0) as event_count')
            ->first();

        $channels = (clone $base)
            ->select('dimension_type', 'dimension_value')
            ->selectRaw('COALESCE(SUM(sessions),0) as sessions, COALESCE(SUM(total_users),0) as total_users, COALESCE(SUM(screen_page_views),0) as screen_page_views, COALESCE(SUM(conversions),0) as conversions, COALESCE(SUM(total_revenue),0) as total_revenue')
            ->groupBy('dimension_type', 'dimension_value')
            ->orderByDesc(DB::raw('SUM(sessions)'))
            ->limit(30)
            ->get();

        $daily = (clone $base)
            ->selectRaw('date, COALESCE(SUM(sessions),0) as sessions, COALESCE(SUM(total_users),0) as total_users, COALESCE(SUM(screen_page_views),0) as screen_page_views, COALESCE(SUM(conversions),0) as conversions')
            ->groupBy('date')
            ->orderBy('date')
            ->get();

        return [
            'totals' => $this->ga4Totals($totals),
            'daily' => $this->normalizeRows($daily),
            'channels' => $this->normalizeRows($channels),
        ];
    }

    private function adTotals(?object $row): array
    {
        $impressions = (int) ($row->impressions ?? 0);
        $clicks = (int) ($row->clicks ?? 0);
        $cost = (float) ($row->cost ?? 0);
        $conversions = (float) ($row->conversions ?? 0);

        return [
            'impressions' => $impressions,
            'clicks' => $clicks,
            'cost' => $cost,
            'conversions' => $conversions,
            'ctr' => $impressions > 0 ? ($clicks / $impressions) * 100 : 0,
            'average_cpc' => $clicks > 0 ? $cost / $clicks : 0,
            'cost_per_conversion' => $conversions > 0 ? $cost / $conversions : 0,
        ];
    }

    private function metaTotals(?object $row): array
    {
        $base = $this->adTotals((object) [
            'impressions' => $row->impressions ?? 0,
            'clicks' => $row->clicks ?? 0,
            'cost' => $row->spend ?? 0,
            'conversions' => $row->conversions ?? 0,
        ]);

        $base['reach'] = (int) ($row->reach ?? 0);
        $base['inline_link_clicks'] = (int) ($row->inline_link_clicks ?? 0);
        $base['spend'] = $base['cost'];
        $base['leads'] = (float) ($row->leads ?? 0);
        $base['purchases'] = (float) ($row->purchases ?? 0);
        $base['purchase_value'] = (float) ($row->purchase_value ?? 0);
        $base['cost_per_lead'] = $base['leads'] > 0 ? $base['spend'] / $base['leads'] : 0;

        return $base;
    }

    private function gscTotals(?object $row): array
    {
        return [
            'clicks' => (int) ($row->clicks ?? 0),
            'impressions' => (int) ($row->impressions ?? 0),
            'ctr' => ((float) ($row->ctr ?? 0)) * 100,
            'position' => (float) ($row->position ?? 0),
        ];
    }

    private function ga4Totals(?object $row): array
    {
        return [
            'sessions' => (int) ($row->sessions ?? 0),
            'total_users' => (int) ($row->total_users ?? 0),
            'screen_page_views' => (int) ($row->screen_page_views ?? 0),
            'conversions' => (float) ($row->conversions ?? 0),
            'total_revenue' => (float) ($row->total_revenue ?? 0),
            'event_count' => (int) ($row->event_count ?? 0),
        ];
    }

    private function normalizeRows(Collection $rows): array
    {
        return $rows->map(fn ($row) => collect($row->toArray())->map(function ($value) {
            if ($value instanceof \DateTimeInterface) {
                return $value->format('Y-m-d');
            }

            return is_numeric($value) ? $value + 0 : $value;
        })->all())->values()->all();
    }
}
