<?php

namespace App\Services\Quality;

use App\Models\PlatformConnection;
use Carbon\Carbon;
use Carbon\CarbonPeriod;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Throwable;

class DataQualityService
{
    public function analyze(?int $clientId, ?string $platform, ?int $connectionId, string $from, string $to, bool $save = false): array
    {
        $fromDate = Carbon::parse($from)->startOfDay();
        $toDate = Carbon::parse($to)->startOfDay();
        $expectedDates = $this->dateList($fromDate, $toDate);

        $connections = PlatformConnection::with('client')
            ->when($clientId, fn ($query) => $query->where('client_id', $clientId))
            ->when($platform, fn ($query) => $query->where('platform', $platform))
            ->when($connectionId, fn ($query) => $query->whereKey($connectionId))
            ->orderBy('platform')
            ->orderBy('connection_name')
            ->get();

        $items = $connections->map(fn (PlatformConnection $connection) => $this->analyzeConnection($connection, $fromDate, $toDate, $expectedDates, $save))->values()->all();

        return [
            'from' => $fromDate->toDateString(),
            'to' => $toDate->toDateString(),
            'expected_days' => count($expectedDates),
            'summary' => $this->summary($items),
            'items' => $items,
        ];
    }

    public function analyzeConnection(PlatformConnection $connection, Carbon|string $from, Carbon|string $to, ?array $expectedDates = null, bool $save = false): array
    {
        $fromDate = $from instanceof Carbon ? $from->copy()->startOfDay() : Carbon::parse($from)->startOfDay();
        $toDate = $to instanceof Carbon ? $to->copy()->startOfDay() : Carbon::parse($to)->startOfDay();
        $expectedDates ??= $this->dateList($fromDate, $toDate);
        $config = $this->platformConfig($connection->platform);

        $tableChecks = [];
        $messages = [];
        $totalRows = 0;
        $totalMissingDays = 0;
        $missingTableCount = 0;
        $requiredEmptyCount = 0;
        $latestDate = null;

        foreach ($config['tables'] ?? [] as $tableConfig) {
            $check = $this->checkTable($connection, $tableConfig, $fromDate, $toDate, $expectedDates);
            $tableChecks[] = $check;
            $totalRows += $check['row_count'];
            $totalMissingDays += count($check['missing_dates']);

            if (! $check['table_exists']) {
                $missingTableCount++;
                $messages[] = $check['label'].' tablosu bulunamadı.';
            }

            if (($tableConfig['required'] ?? false) && $check['table_exists'] && $check['row_count'] === 0) {
                $requiredEmptyCount++;
                $messages[] = $check['label'].' için seçilen aralıkta veri yok.';
            }

            if ($check['latest_date'] && (! $latestDate || $check['latest_date'] > $latestDate)) {
                $latestDate = $check['latest_date'];
            }
        }

        if ($connection->last_error) {
            $messages[] = 'Bağlantıda son hata kaydı var: '.$connection->last_error;
        }

        if (! $connection->last_sync_at) {
            $messages[] = 'Bu bağlantı henüz hiç senkronize edilmemiş.';
        } elseif ($connection->last_sync_at->lt($toDate->copy()->endOfDay())) {
            $messages[] = 'Son senkron tarihi istenen bitiş tarihinden eski olabilir.';
        }

        if ($totalRows === 0 && empty($messages)) {
            $messages[] = 'Seçilen aralıkta snapshot satırı bulunamadı.';
        }

        $score = $this->score($totalRows, $missingTableCount, $requiredEmptyCount, $totalMissingDays, (bool) $connection->last_error, $connection->last_sync_at, $toDate, count($expectedDates));
        $status = $this->statusFromScore($score, $totalRows, $missingTableCount, $requiredEmptyCount, (bool) $connection->last_error);

        $result = [
            'connection_id' => $connection->id,
            'client_id' => $connection->client_id,
            'client_name' => $connection->client?->company_name,
            'platform' => $connection->platform,
            'platform_label' => $config['label'] ?? strtoupper(str_replace('_', ' ', $connection->platform)),
            'connection_name' => $connection->connection_name,
            'account_id' => $connection->account_id,
            'connection_status' => $connection->status,
            'last_sync_at' => $connection->last_sync_at?->format('Y-m-d H:i:s'),
            'last_error' => $connection->last_error,
            'score' => $score,
            'status' => $status,
            'status_label' => $this->statusLabel($status),
            'latest_snapshot_date' => $latestDate,
            'total_rows' => $totalRows,
            'missing_table_count' => $missingTableCount,
            'required_empty_count' => $requiredEmptyCount,
            'missing_days_total' => $totalMissingDays,
            'messages' => array_values(array_unique($messages)),
            'table_checks' => $tableChecks,
            'checked_at' => now()->format('Y-m-d H:i:s'),
        ];

        if ($save) {
            $meta = $connection->meta ?? [];
            $meta['last_quality_check'] = $result;
            $connection->forceFill(['meta' => $meta])->save();
        }

        return $result;
    }

    private function checkTable(PlatformConnection $connection, array $tableConfig, Carbon $fromDate, Carbon $toDate, array $expectedDates): array
    {
        $table = $tableConfig['table'];
        $label = $tableConfig['label'];
        $metricColumns = $tableConfig['metric_columns'] ?? [];
        $primaryMetric = $tableConfig['primary_metric'] ?? ($metricColumns[0] ?? null);

        if (! Schema::hasTable($table)) {
            return [
                'table' => $table,
                'label' => $label,
                'table_exists' => false,
                'required' => (bool) ($tableConfig['required'] ?? false),
                'row_count' => 0,
                'date_count' => 0,
                'first_date' => null,
                'latest_date' => null,
                'missing_dates' => $expectedDates,
                'zero_metric_dates' => [],
                'totals' => [],
                'duplicate_groups' => 0,
                'status' => 'danger',
            ];
        }

        $base = DB::table($table)
            ->where('platform_connection_id', $connection->id)
            ->whereBetween('date', [$fromDate->toDateString(), $toDate->toDateString()]);

        $rowCount = (clone $base)->count();
        $dates = (clone $base)->select('date')->distinct()->orderBy('date')->pluck('date')->map(fn ($date) => (string) $date)->all();
        $missingDates = array_values(array_diff($expectedDates, $dates));

        $totals = [];
        foreach ($metricColumns as $column) {
            if (Schema::hasColumn($table, $column)) {
                $totals[$column] = (float) (clone $base)->sum($column);
            }
        }

        $zeroMetricDates = [];
        if ($primaryMetric && Schema::hasColumn($table, $primaryMetric)) {
            $zeroMetricDates = (clone $base)
                ->select('date', DB::raw('SUM(`'.$primaryMetric.'`) as metric_total'))
                ->groupBy('date')
                ->havingRaw('SUM(`'.$primaryMetric.'`) = 0')
                ->pluck('date')
                ->map(fn ($date) => (string) $date)
                ->all();
        }

        $duplicateGroups = $this->duplicateGroups($table, $connection->id, $fromDate, $toDate);

        $status = 'ok';
        if ($rowCount === 0 && ($tableConfig['required'] ?? false)) {
            $status = 'danger';
        } elseif (! empty($missingDates) || $duplicateGroups > 0) {
            $status = 'warning';
        }

        return [
            'table' => $table,
            'label' => $label,
            'table_exists' => true,
            'required' => (bool) ($tableConfig['required'] ?? false),
            'row_count' => $rowCount,
            'date_count' => count($dates),
            'first_date' => $dates[0] ?? null,
            'latest_date' => ! empty($dates) ? end($dates) : null,
            'missing_dates' => $missingDates,
            'zero_metric_dates' => $zeroMetricDates,
            'totals' => $totals,
            'duplicate_groups' => $duplicateGroups,
            'status' => $status,
        ];
    }

    private function duplicateGroups(string $table, int $connectionId, Carbon $fromDate, Carbon $toDate): int
    {
        try {
            if (Schema::hasColumn($table, 'row_hash')) {
                return DB::query()
                    ->fromSub(
                        DB::table($table)
                            ->select('platform_connection_id', 'date', 'row_hash', DB::raw('COUNT(*) as duplicate_count'))
                            ->where('platform_connection_id', $connectionId)
                            ->whereBetween('date', [$fromDate->toDateString(), $toDate->toDateString()])
                            ->groupBy('platform_connection_id', 'date', 'row_hash')
                            ->havingRaw('COUNT(*) > 1'),
                        'duplicates'
                    )
                    ->count();
            }
        } catch (Throwable) {
            return 0;
        }

        return 0;
    }

    private function score(int $totalRows, int $missingTableCount, int $requiredEmptyCount, int $missingDaysTotal, bool $hasLastError, $lastSyncAt, Carbon $toDate, int $expectedDays): int
    {
        $score = 100;

        if ($totalRows === 0) {
            $score -= 45;
        }
        $score -= min(30, $missingTableCount * 15);
        $score -= min(30, $requiredEmptyCount * 15);
        $score -= min(25, $missingDaysTotal * 3);
        if ($hasLastError) {
            $score -= 25;
        }
        if (! $lastSyncAt) {
            $score -= 15;
        } elseif ($lastSyncAt->lt($toDate->copy()->endOfDay())) {
            $score -= 8;
        }

        return max(0, min(100, $score));
    }

    private function statusFromScore(int $score, int $totalRows, int $missingTableCount, int $requiredEmptyCount, bool $hasLastError): string
    {
        if ($missingTableCount > 0 || $requiredEmptyCount > 0 || $totalRows === 0 || $hasLastError || $score < 60) {
            return 'danger';
        }
        if ($score < 85) {
            return 'warning';
        }

        return 'ok';
    }

    private function statusLabel(string $status): string
    {
        return match ($status) {
            'ok' => 'Raporlamaya Hazır',
            'warning' => 'Kontrol Gerekli',
            'danger' => 'Sorun Var',
            default => 'Bilinmiyor',
        };
    }

    private function summary(array $items): array
    {
        return [
            'connections' => count($items),
            'ok' => collect($items)->where('status', 'ok')->count(),
            'warning' => collect($items)->where('status', 'warning')->count(),
            'danger' => collect($items)->where('status', 'danger')->count(),
            'rows' => collect($items)->sum('total_rows'),
            'missing_days' => collect($items)->sum('missing_days_total'),
            'average_score' => count($items) ? round(collect($items)->avg('score'), 1) : 0,
        ];
    }

    private function dateList(Carbon $fromDate, Carbon $toDate): array
    {
        if ($toDate->lt($fromDate)) {
            return [];
        }

        return collect(CarbonPeriod::create($fromDate, $toDate))
            ->map(fn (Carbon $date) => $date->toDateString())
            ->values()
            ->all();
    }

    private function platformConfig(string $platform): array
    {
        $configs = [
            'google_ads' => [
                'label' => 'Google Ads',
                'tables' => [
                    ['label' => 'Google Kampanyalar', 'table' => 'google_campaign_snapshots', 'metric_columns' => ['impressions', 'clicks', 'cost', 'conversions'], 'primary_metric' => 'cost', 'required' => true],
                    ['label' => 'Google Anahtar Kelimeler', 'table' => 'google_keyword_snapshots', 'metric_columns' => ['impressions', 'clicks', 'cost', 'conversions'], 'primary_metric' => 'cost', 'required' => false],
                    ['label' => 'Google Arama Terimleri', 'table' => 'google_search_term_snapshots', 'metric_columns' => ['impressions', 'clicks', 'cost', 'conversions'], 'primary_metric' => 'cost', 'required' => false],
                ],
            ],
            'meta_ads' => [
                'label' => 'Meta Ads',
                'tables' => [
                    ['label' => 'Meta Kampanyalar', 'table' => 'meta_campaign_snapshots', 'metric_columns' => ['impressions', 'reach', 'clicks', 'spend', 'conversions', 'leads', 'purchases'], 'primary_metric' => 'spend', 'required' => true],
                    ['label' => 'Meta Reklam Setleri', 'table' => 'meta_adset_snapshots', 'metric_columns' => ['impressions', 'reach', 'clicks', 'spend', 'conversions', 'leads', 'purchases'], 'primary_metric' => 'spend', 'required' => false],
                    ['label' => 'Meta Reklamlar', 'table' => 'meta_ad_snapshots', 'metric_columns' => ['impressions', 'reach', 'clicks', 'spend', 'conversions', 'leads', 'purchases'], 'primary_metric' => 'spend', 'required' => false],
                ],
            ],
            'search_console' => [
                'label' => 'Search Console',
                'tables' => [
                    ['label' => 'GSC Sorgular', 'table' => 'search_console_query_snapshots', 'metric_columns' => ['clicks', 'impressions', 'ctr', 'position'], 'primary_metric' => 'clicks', 'required' => true],
                    ['label' => 'GSC Sayfalar', 'table' => 'search_console_page_snapshots', 'metric_columns' => ['clicks', 'impressions', 'ctr', 'position'], 'primary_metric' => 'clicks', 'required' => false],
                ],
            ],
            'ga4' => [
                'label' => 'GA4',
                'tables' => [
                    ['label' => 'GA4 Trafik', 'table' => 'ga4_traffic_snapshots', 'metric_columns' => ['sessions', 'total_users', 'screen_page_views', 'conversions', 'total_revenue', 'event_count'], 'primary_metric' => 'sessions', 'required' => true],
                ],
            ],
        ];

        return $configs[$platform] ?? ['label' => strtoupper(str_replace('_', ' ', $platform)), 'tables' => []];
    }
}
