<?php
namespace App\Http\Controllers\Admin\Advisor;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class AdvisorFilterYearController
{
    public function monthlyAverages(Request $request)
    {
        $year = $request->input('year');
        $idadvisor = $request->input('IdAdvisor');
        if (is_null($idadvisor)) {
            return response()->json(['error' => 'El parámetro IdAdvisor es requerido']);
        }
        
        $monthlyAverages = [];
        for ($month = 1; $month <= 12; $month++) {
            $average = $this->calculateAverage($year, $month, null, $idadvisor);
            $count = $this->countRatings($year, $month, null, $idadvisor);
            $monthName = date('F', mktime(0, 0, 0, $month, 1));
            $monthlyAverages[$monthName] = [
                'average' => $average,
                'count' => $count
            ];
        }
        return response()->json(['monthlyAverages' => $monthlyAverages]);
    }
    
    private function calculateAverage($year, $month = null, $day = null, $idadvisor)
    {
        $query = DB::table('rating')
            ->whereYear('DateRating', $year)
            ->where('IdAdvisor', $idadvisor);

        if (!is_null($month)) {
            $query->whereMonth('DateRating', $month);
        }

        if (!is_null($day)) {
            $query->whereDay('DateRating', $day);
        }
        
        return $query->avg('ValueRating') ?? 0;
    }
    
    private function countRatings($year, $month = null, $day = null, $idadvisor)
    {
        $query = DB::table('rating')
            ->whereYear('DateRating', $year)
            ->where('IdAdvisor', $idadvisor);

        if (!is_null($month)) {
            $query->whereMonth('DateRating', $month);
        }

        if (!is_null($day)) {
            $query->whereDay('DateRating', $day);
        }
        
        return $query->count();
    }
}