<?php

namespace App\Http\Controllers\Admin\Advisor;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class AdvisorFilterDateController
{
    public function average(Request $request)
    {
        $year = $request->input('year');
        $month = $request->input('month');
        $day = $request->input('day');

        // Obtener todos los IdAdvisors y NameAdvisors de la tabla advisor
        $advisors = DB::table('advisor')->select('IdAdvisor', 'NameAdvisor')->get();

        // Calcular los promedios y la cantidad de ratings para cada advisor en el período especificado
        $data = $this->calculateAveragesAndCounts($year, $month, $day, $advisors);

        // Si no se encontraron registros, devolver un array vacío
        if (empty($data)) {
            return response()->json(['data' => []]);
        }

        return response()->json(['data' => $data]);
    }

    public function calculateAveragesAndCounts($year, $month = null, $day = null, $advisors)
    {
        $query = DB::table('rating')
            ->whereYear('DateRating', $year);

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

        // Si $advisors está vacío, retornar un array vacío
        if (empty($advisors)) {
            return [];
        }

        // Obtener promedios de calificaciones por advisor
        $averages = $query->whereIn('IdAdvisor', $advisors->pluck('IdAdvisor'))
            ->groupBy('IdAdvisor')
            ->select('IdAdvisor', DB::raw('COALESCE(avg(ValueRating), 0) as averageRating'))
            ->get();

        // Obtener cantidad de ratings por advisor
        $counts = $query->whereIn('IdAdvisor', $advisors->pluck('IdAdvisor'))
            ->groupBy('IdAdvisor')
            ->select('IdAdvisor', DB::raw('count(*) as ratingCount'))
            ->get();

        // Combinar promedios y cantidades en un solo arreglo por advisor
        $data = [];
        foreach ($advisors as $advisor) {
            $average = $averages->where('IdAdvisor', $advisor->IdAdvisor)->first();
            $count = $counts->where('IdAdvisor', $advisor->IdAdvisor)->first();

            $data[] = [
                'IdAdvisor' => $advisor->IdAdvisor,
                'NameAdvisor' => $advisor->NameAdvisor,
                'averageRating' => $average ? $average->averageRating : 0,
                'ratingCount' => $count ? $count->ratingCount : 0,
            ];
        }

        return $data;
    }
}
