<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Carbon\Carbon;

class DnsZoneStatistic extends Model
{
    use HasFactory;

    protected $fillable = [
        'domain',
        'nameserver_id',
        'statistic_date',
        'period_type',
        'raw_statistics',
        'total_queries',
        'cache_hits',
        'cache_misses',
        'servfail_responses',
        'nxdomain_responses',
        'noerror_responses',
        'cache_hit_ratio',
        'top_query_types',
        'top_client_ips',
        'hourly_distribution',
        'hourly_queries', // ✨ NOU
        'top_subdomains', // ✨ NOU
        'geographic_distribution', // ✨ NOU
        'comparison_stats', // ✨ NOU
        'protocol_distribution', // ✨ NOU
        'response_time_avg', // ✨ NOU: latency mediu în microsecunde
        'dnssec_stats', // ✨ NOU: statistici DNSSEC
        'security_stats', // ✨ NOU: statistici security/rate limiting
        'collected_at'
    ];

    protected $casts = [
        'statistic_date' => 'date',
        'raw_statistics' => 'array',
        'top_query_types' => 'array',
        'top_client_ips' => 'array',
        'hourly_distribution' => 'array',
        'hourly_queries' => 'array', // ✨ NOU
        'top_subdomains' => 'array', // ✨ NOU
        'geographic_distribution' => 'array', // ✨ NOU
        'comparison_stats' => 'array', // ✨ NOU
        'protocol_distribution' => 'array', // ✨ NOU
        'dnssec_stats' => 'array', // ✨ NOU
        'security_stats' => 'array', // ✨ NOU
        'collected_at' => 'datetime',
        'cache_hit_ratio' => 'float'
    ];

    // Relații
    public function nameserver()
    {
        return $this->belongsTo(Nameserver::class);
    }

    public function dnsZone()
    {
        return $this->belongsTo(DnsZone::class, 'domain', 'domain');
    }

    // Scope-uri pentru statistici
    public function scopeForDomain($query, $domain)
    {
        return $query->where('domain', $domain);
    }

    public function scopeForPeriod($query, $periodType)
    {
        return $query->where('period_type', $periodType);
    }

    public function scopeForDateRange($query, $startDate, $endDate)
    {
        return $query->whereBetween('statistic_date', [$startDate, $endDate]);
    }

    public function scopeLatestFirst($query)
    {
        return $query->orderBy('statistic_date', 'desc');
    }

    // Metode helper pentru agregarea statisticilor
    public static function getAggregatedStatsForDomain($domain, $periodType = 'daily', $days = 30)
    {
        $startDate = Carbon::now()->subDays($days);
        $endDate = Carbon::now();

        return self::forDomain($domain)
            ->forPeriod($periodType)
            ->forDateRange($startDate, $endDate)
            ->with('nameserver')
            ->latestFirst()
            ->get()
            ->groupBy('statistic_date')
            ->map(function ($stats) {
                // Formatează data pentru afișare (doar ziua, fără ora)
                $formattedDate = Carbon::parse($stats->first()->statistic_date)->format('Y-m-d');

                return [
                    'date' => $formattedDate,
                    'total_queries' => $stats->sum('total_queries'),
                    'cache_hits' => $stats->sum('cache_hits'),
                    'cache_misses' => $stats->sum('cache_misses'),
                    'servfail_responses' => $stats->sum('servfail_responses'),
                    'nxdomain_responses' => $stats->sum('nxdomain_responses'),
                    'noerror_responses' => $stats->sum('noerror_responses'),
                    'cache_hit_ratio' => $stats->avg('cache_hit_ratio'),
                    'nameservers' => $stats->pluck('nameserver.name')->unique(),
                    'nameserver_count' => $stats->count()
                ];
            });
    }

    public static function getTrendDataForDomain($domain, $periodType = 'daily', $limit = 30)
    {
        $query = self::forDomain($domain)
            ->forPeriod($periodType)
            ->latestFirst()
            ->limit($limit);

        $stats = $query->get()->groupBy('statistic_date');

        $trendData = [];
        foreach ($stats as $date => $dailyStats) {
            // Formatează data pentru afișare (doar ziua, fără ora)
            $formattedDate = Carbon::parse($date)->format('Y-m-d');

            $trendData[] = [
                'date' => $formattedDate,
                'queries' => $dailyStats->sum('total_queries'),
                'cache_hit_ratio' => round($dailyStats->avg('cache_hit_ratio'), 2),
                'nameservers' => $dailyStats->count()
            ];
        }

        return array_reverse($trendData); // Returnează în ordine cronologică
    }

    public static function getTopQueryTypes($domain, $days = 7)
    {
        $startDate = Carbon::now()->subDays($days);

        $stats = self::forDomain($domain)
            ->forDateRange($startDate, Carbon::now())
            ->whereNotNull('top_query_types')
            ->get();

        $aggregatedTypes = [];
        foreach ($stats as $stat) {
            if ($stat->top_query_types) {
                foreach ($stat->top_query_types as $type => $count) {
                    $aggregatedTypes[$type] = ($aggregatedTypes[$type] ?? 0) + $count;
                }
            }
        }

        arsort($aggregatedTypes);
        return array_slice($aggregatedTypes, 0, 10, true);
    }

    public static function getTopClientIPs($domain, $days = 7)
    {
        $startDate = Carbon::now()->subDays($days);

        $stats = self::forDomain($domain)
            ->forDateRange($startDate, Carbon::now())
            ->whereNotNull('top_client_ips')
            ->get();

        $aggregatedIPs = [];
        foreach ($stats as $stat) {
            if ($stat->top_client_ips) {
                foreach ($stat->top_client_ips as $ip => $count) {
                    $aggregatedIPs[$ip] = ($aggregatedIPs[$ip] ?? 0) + $count;
                }
            }
        }

        arsort($aggregatedIPs);
        return array_slice($aggregatedIPs, 0, 20, true);
    }
}
