<?php

namespace App\Services\Alerts;

use App\Models\ApiRequestLog;
use App\Models\EmailLog;
use App\Models\Ga4TrafficSnapshot;
use App\Models\GoogleCampaignSnapshot;
use App\Models\GoogleKeywordSnapshot;
use App\Models\MetaCampaignSnapshot;
use App\Models\SearchConsoleQuerySnapshot;
use Carbon\CarbonInterface;
use Illuminate\Support\Facades\DB;

class AlertMetricCalculator
{
    public function calculate(int $clientId, string $platform, string $metric, CarbonInterface $start, CarbonInterface $end): float
    {
        return match ($platform) {
            'google_ads' => $this->googleAds($clientId, $metric, $start, $end),
            'meta_ads' => $this->metaAds($clientId, $metric, $start, $end),
            'search_console' => $this->searchConsole($clientId, $metric, $start, $end),
            'ga4' => $this->ga4($clientId, $metric, $start, $end),
            'system' => $this->system($clientId, $metric, $start, $end),
            default => 0.0,
        };
    }

    private function googleAds(int $clientId, string $metric, CarbonInterface $start, CarbonInterface $end): float
    {
        $base = GoogleCampaignSnapshot::query()
            ->where('client_id', $clientId)
            ->whereBetween('date', [$start->toDateString(), $end->toDateString()]);

        $totals = (clone $base)->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();

        $cost = (float) ($totals->cost ?? 0);
        $clicks = (float) ($totals->clicks ?? 0);
        $impressions = (float) ($totals->impressions ?? 0);
        $conversions = (float) ($totals->conversions ?? 0);

        return match ($metric) {
            'cost' => $cost,
            'clicks' => $clicks,
            'impressions' => $impressions,
            'conversions' => $conversions,
            'ctr' => $impressions > 0 ? ($clicks / $impressions) * 100 : 0,
            'average_cpc' => $clicks > 0 ? $cost / $clicks : 0,
            'conversion_rate' => $clicks > 0 ? ($conversions / $clicks) * 100 : 0,
            'cost_per_conversion' => $conversions > 0 ? $cost / $conversions : 0,
            'wasted_cost' => (float) GoogleKeywordSnapshot::query()
                ->where('client_id', $clientId)
                ->whereBetween('date', [$start->toDateString(), $end->toDateString()])
                ->where('conversions', '<=', 0)
                ->sum('cost'),
            default => 0,
        };
    }

    private function metaAds(int $clientId, string $metric, CarbonInterface $start, CarbonInterface $end): float
    {
        $base = MetaCampaignSnapshot::query()
            ->where('client_id', $clientId)
            ->whereBetween('date', [$start->toDateString(), $end->toDateString()]);

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

        $spend = (float) ($totals->spend ?? 0);
        $clicks = (float) ($totals->clicks ?? 0);
        $impressions = (float) ($totals->impressions ?? 0);
        $reach = (float) ($totals->reach ?? 0);
        $leads = (float) ($totals->leads ?? 0);
        $purchases = (float) ($totals->purchases ?? 0);
        $conversions = (float) ($totals->conversions ?? 0);

        return match ($metric) {
            'spend' => $spend,
            'clicks' => $clicks,
            'impressions' => $impressions,
            'reach' => $reach,
            'leads' => $leads,
            'purchases' => $purchases,
            'conversions' => $conversions,
            'ctr' => $impressions > 0 ? ($clicks / $impressions) * 100 : 0,
            'cpc' => $clicks > 0 ? $spend / $clicks : 0,
            'cpm' => $impressions > 0 ? ($spend / $impressions) * 1000 : 0,
            'cost_per_lead' => $leads > 0 ? $spend / $leads : 0,
            'cost_per_purchase' => $purchases > 0 ? $spend / $purchases : 0,
            'wasted_spend' => (float) MetaCampaignSnapshot::query()
                ->where('client_id', $clientId)
                ->whereBetween('date', [$start->toDateString(), $end->toDateString()])
                ->where('conversions', '<=', 0)
                ->sum('spend'),
            default => 0,
        };
    }

    private function searchConsole(int $clientId, string $metric, CarbonInterface $start, CarbonInterface $end): float
    {
        $base = SearchConsoleQuerySnapshot::query()
            ->where('client_id', $clientId)
            ->whereBetween('date', [$start->toDateString(), $end->toDateString()]);

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

        $clicks = (float) ($totals->clicks ?? 0);
        $impressions = (float) ($totals->impressions ?? 0);

        return match ($metric) {
            'clicks' => $clicks,
            'impressions' => $impressions,
            'ctr' => $impressions > 0 ? ($clicks / $impressions) * 100 : 0,
            'position' => (float) ($totals->position ?? 0),
            default => 0,
        };
    }

    private function ga4(int $clientId, string $metric, CarbonInterface $start, CarbonInterface $end): float
    {
        $base = Ga4TrafficSnapshot::query()
            ->where('client_id', $clientId)
            ->whereBetween('date', [$start->toDateString(), $end->toDateString()]);

        $totals = (clone $base)->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, COALESCE(SUM(event_count),0) as event_count')->first();

        $sessions = (float) ($totals->sessions ?? 0);
        $conversions = (float) ($totals->conversions ?? 0);

        return match ($metric) {
            'sessions' => $sessions,
            'total_users' => (float) ($totals->total_users ?? 0),
            'screen_page_views' => (float) ($totals->screen_page_views ?? 0),
            'conversions' => $conversions,
            'total_revenue' => (float) ($totals->total_revenue ?? 0),
            'event_count' => (float) ($totals->event_count ?? 0),
            'conversion_rate' => $sessions > 0 ? ($conversions / $sessions) * 100 : 0,
            default => 0,
        };
    }

    private function system(int $clientId, string $metric, CarbonInterface $start, CarbonInterface $end): float
    {
        return match ($metric) {
            'api_errors' => (float) ApiRequestLog::query()
                ->where('client_id', $clientId)
                ->whereNotNull('error_message')
                ->whereBetween(DB::raw('DATE(requested_at)'), [$start->toDateString(), $end->toDateString()])
                ->count(),
            'failed_emails' => (float) EmailLog::query()
                ->where('client_id', $clientId)
                ->where('status', 'failed')
                ->whereBetween(DB::raw('DATE(created_at)'), [$start->toDateString(), $end->toDateString()])
                ->count(),
            default => 0,
        };
    }
}
