<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class AdAnalytics extends Model
{
    protected $table = 'ad_analytics';

    protected $fillable = [
        'ad_id',
        'date',
        'impressions',
        'views',
        'clicks',
        'ctr',
        'unique_viewers',
        'avg_time_spent',
        'bounce_rate',
    ];

    protected $casts = [
        'date' => 'date',
        'ctr' => 'decimal:2',
        'bounce_rate' => 'decimal:2',
    ];

    /**
     * Get the ad that owns this analytics
     */
    public function ad(): BelongsTo
    {
        return $this->belongsTo(Ad::class);
    }

    /**
     * Scope to filter by date range
     */
    public function scopeDateRange($query, $start, $end)
    {
        return $query->whereBetween('date', [$start, $end]);
    }

    /**
     * Scope to filter by specific ad
     */
    public function scopeForAd($query, int $adId)
    {
        return $query->where('ad_id', $adId);
    }

    /**
     * Calculate and update CTR based on impressions and clicks
     */
    public function calculateCTR(): float
    {
        if ($this->impressions > 0) {
            $this->ctr = ($this->clicks / $this->impressions) * 100;
            $this->save();
        }
        
        return $this->ctr;
    }

    /**
     * Get aggregated stats for an ad over a date range
     */
    public static function getAggregatedStats(int $adId, $startDate, $endDate): array
    {
        $stats = self::forAd($adId)
            ->dateRange($startDate, $endDate)
            ->selectRaw('
                SUM(impressions) as total_impressions,
                SUM(views) as total_views,
                SUM(clicks) as total_clicks,
                AVG(ctr) as avg_ctr,
                AVG(bounce_rate) as avg_bounce_rate,
                SUM(unique_viewers) as total_unique_viewers,
                AVG(avg_time_spent) as avg_time_spent
            ')
            ->first();

        return $stats ? $stats->toArray() : [];
    }
}
