<?php
/*
namespace App\Http\Controllers;

use App\Models\InstallRequest;
use App\Models\User;
use Illuminate\Auth\Access\AuthorizationException;
use Illuminate\Http\Request;
use Illuminate\Foundation\Auth\Access\AuthorizesRequests;
use Illuminate\Support\Facades\Gate;
use Inertia\Inertia;
use Illuminate\Support\Facades\DB;
use Inertia\Response;

class InstallerStatsController extends Controller
{
    use AuthorizesRequests;
*/
    /**
     * @throws AuthorizationException
     *//*
    public function index(Request $request): Response
    {

        $this->authorize('viewAny', User::class);

        $installers = User::where('role', 'installer')
            ->withCount([
                'installRequests as completed_count' => fn($q) => $q->where('status', 'done'),
                'installRequests as rejected_count' => fn($q) => $q->where('status', 'rejected'),
            ])
            ->withAvg('ratingsReceived', 'rating')
            ->get();

        // Среднее время выполнения
        $times = InstallRequest::selectRaw('assigned_to, AVG(TIMESTAMPDIFF(HOUR, created_at, completed_at)) as avg_hours')
            ->whereNotNull('completed_at')
            ->groupBy('assigned_to')
            ->pluck('avg_hours', 'assigned_to');

        foreach ($installers as $installer) {
            $installer->avg_hours = round($times[$installer->id] ?? 0, 1);
        }

        return Inertia::render('installers/Stats', [
            'installers' => $installers
        ]);
    }
*/
    /**
     * @throws AuthorizationException
     */
    /*
    public function show(Request $request, User $user): Response
    {
        $this->authorize('view', $user);

        $requests = InstallRequest::where('assigned_to', $user->id)
            ->select('status', 'created_at', 'scheduled_date', 'completed_at')
            ->latest()
            ->get();

        $completed = $requests->where('status', 'done');
        $avgHours = round($completed->avg(fn($r) => $r->created_at->diffInHours($r->completed_at)), 1);

        return Inertia::render('installers/Show', [
            'user' => $user,
            'requests' => $requests,
            'avg_hours' => $avgHours,
        ]);
    }
*/
    /**
     * @throws AuthorizationException
     */
    /*
    public function analytics(Request $request): Response
    {
        Gate::authorize('analytics');

        $start = $request->input('start_date') ?? now()->startOfMonth()->toDateString();
        $end = $request->input('end_date') ?? now()->endOfMonth()->toDateString();

        $query = InstallRequest::whereBetween('created_at', [$start, $end]);

        // Аналитика по регионам (region есть)
        $byRegion = (clone $query)
            ->select('region', DB::raw('count(*) as total'))
            ->groupBy('region')
            ->get();

        // Аналитика по клиентам с join users
        $byClient = DB::table('install_requests')
            ->join('users', 'install_requests.created_by', '=', 'users.id')
            ->whereBetween('install_requests.created_at', [$start, $end])
            ->select('users.name as client_name', DB::raw('count(*) as total'))
            ->groupBy('users.name')
            ->orderByDesc('total')
            ->limit(10)
            ->get();

        // Аналитика по типу заявки — если у тебя есть колонка 'request_type'
        // Иначе убрать или добавить эту колонку в миграцию

        return Inertia::render('analytics/Overview', [
            'byRegion' => $byRegion,
            'byClient' => $byClient,
            'filters' => ['start_date' => $start, 'end_date' => $end],
        ]);
    }

}*/
namespace App\Http\Controllers;

use App\Models\InstallRequest;
use App\Models\User;
use Illuminate\Auth\Access\AuthorizationException;
use Illuminate\Foundation\Auth\Access\AuthorizesRequests;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Gate;
use Illuminate\Support\Facades\Cache;
use Inertia\Inertia;
use Inertia\Response;

class InstallerStatsController extends Controller
{
    use AuthorizesRequests;

    /**
     * @throws AuthorizationException
     */
    public function index(Request $request): Response
    {
        $this->authorize('viewAny', User::class);

        // 🔹 Основная статистика
        $installers = User::query()
            ->where('role', 'installer')
            ->select(['id', 'name'])
            ->withCount([
                'installRequests as completed_count' => fn($q) => $q->where('status', 'done'),

                'installRequests as rejected_count' => fn($q) => $q->where('status', 'cancelled'),
            ])
            ->withAvg('ratingsReceived', 'rating')
            ->get();

        // 🔹 Среднее время выполнения (SQL, а не PHP)
        $avgTimes = InstallRequest::query()
            ->selectRaw('assigned_to, ROUND(AVG(TIMESTAMPDIFF(HOUR, created_at, completed_at)), 1) as avg_hours')
            ->whereNotNull('completed_at')
            ->where('status', 'done')
            ->groupBy('assigned_to')
            ->pluck('avg_hours', 'assigned_to');

        foreach ($installers as $installer) {
            $installer->avg_hours = (float)($avgTimes[$installer->id] ?? 0);
        }

        return Inertia::render('installers/Stats', [
            'installers' => $installers
        ]);
    }

    /**
     * @throws AuthorizationException
     */
    public function show(Request $request, User $user): Response
    {
        $this->authorize('view', $user);

        // 🔹 Пагинация вместо get()
        $requests = InstallRequest::query()
            ->where('assigned_to', $user->id)
            ->select([
                'id',
                'status',
                'created_at',
                'scheduled_date',
                'completed_at'
            ])
            ->latest()
            ->paginate(20)
            ->withQueryString();

        // 🔹 Среднее время через SQL
        $avgHours = InstallRequest::query()
            ->where('assigned_to', $user->id)
            ->where('status', 'done')
            ->whereNotNull('completed_at')
            ->selectRaw('ROUND(AVG(TIMESTAMPDIFF(HOUR, created_at, completed_at)), 1) as avg')
            ->value('avg');

        return Inertia::render('installers/Show', [
            'user' => $user->only(['id', 'name']),
            'requests' => $requests,
            'avg_hours' => (float)($avgHours ?? 0),
        ]);
    }

    /**
     * @throws AuthorizationException
     */
    public function analytics(Request $request): Response
    {
        Gate::authorize('analytics');

        $start = $request->input('start_date') ?? now()->startOfMonth()->toDateString();
        $end = $request->input('end_date') ?? now()->endOfMonth()->toDateString();

        $cacheKey = "analytics:{$start}:{$end}";

        $data = Cache::remember($cacheKey, now()->addMinutes(10), function () use ($start, $end) {

            $baseQuery = InstallRequest::query()
                ->whereBetween('created_at', [$start, $end]);

            // 🔹 По регионам
            $byRegion = (clone $baseQuery)
                ->select('region', DB::raw('COUNT(*) as total'))
                ->groupBy('region')
                ->orderByDesc('total')
                ->get();

            // 🔹 По клиентам (правильный groupBy)
            $byClient = InstallRequest::query()
                ->join('users', 'install_requests.created_by', '=', 'users.id')
                ->whereBetween('install_requests.created_at', [$start, $end])
                ->select(
                    'users.id',
                    'users.name',
                    DB::raw('COUNT(*) as total')
                )
                ->groupBy('users.id', 'users.name')
                ->orderByDesc('total')
                ->limit(10)
                ->get();

            return [
                'byRegion' => $byRegion,
                'byClient' => $byClient,
            ];
        });

        return Inertia::render('analytics/Overview', [
            'byRegion' => $data['byRegion'],
            'byClient' => $data['byClient'],
            'filters' => [
                'start_date' => $start,
                'end_date' => $end
            ],
        ]);
    }
}
