<?php

namespace App\Models;

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

class DnsStatistic extends Model
{
    use HasFactory;

    protected $fillable = [
        'dns_zone_id',
        'date',
        'query_count',
        'query_types',
        'response_codes',
        'top_clients',
    ];

    protected function casts(): array
    {
        return [
            'date' => 'date',
            'query_count' => 'integer',
            'query_types' => 'array',
            'response_codes' => 'array',
            'top_clients' => 'array',
        ];
    }

    /**
     * Relația cu zona DNS
     */
    public function dnsZone()
    {
        return $this->belongsTo(DnsZone::class);
    }

    /**
     * Scope pentru statistici lunare
     */
    public function scopeForMonth($query, $year, $month)
    {
        return $query->whereYear('date', $year)
                    ->whereMonth('date', $month);
    }

    /**
     * Scope pentru statistici zilnice
     */
    public function scopeForDate($query, $date)
    {
        return $query->whereDate('date', $date);
    }

    /**
     * Obține statistici agregate pentru o perioadă
     */
    public static function getAggregatedStats($dnsZoneId, $startDate, $endDate)
    {
        return self::where('dns_zone_id', $dnsZoneId)
            ->whereBetween('date', [$startDate, $endDate])
            ->selectRaw('
                SUM(query_count) as total_queries,
                COUNT(*) as days_count,
                AVG(query_count) as avg_queries_per_day,
                MAX(query_count) as max_queries_per_day
            ')
            ->first();
    }
}
