<?php

namespace App\Http\Controllers\Api\V1\Admin;

use App\Http\Controllers\Api\V1\ApiController;
use App\Models\CollectionLog;
use App\Models\CollectionTeam;
use App\Models\Tenant;
use App\Models\Trip;
use App\Models\TripTimelineEvent;
use App\Tenancy\Tenancy;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;

class AdminTeamReportController extends ApiController
{
    /**
     * GET /api/v1/admin/reports/teams/leaderboard
     *
     * Returns a ranked list of all collection teams with aggregate KPIs
     * for the given date range. Super-admins can pass tenant_id to scope
     * to a specific LGU, or omit it to see all LGUs combined.
     */
    public function leaderboard(Request $request): JsonResponse
    {
        $data = $request->validate([
            'from'      => ['nullable', 'date'],
            'to'        => ['nullable', 'date'],
            'tenant_id' => ['nullable', 'integer', 'exists:tenants,id'],
            'per_page'  => ['nullable', 'integer', 'min:1', 'max:200'],
        ]);

        $from    = Carbon::parse($data['from'] ?? now()->subDays(30))->startOfDay();
        $to      = Carbon::parse($data['to'] ?? now())->endOfDay();
        $perPage = (int) ($data['per_page'] ?? 50);

        $tenant = $this->resolveTargetTenant($request);

        // For the leaderboard we run a single DB query per team to avoid N+1.
        // We use subquery aggregates to compute everything in one pass.
        $rows = Tenancy::withoutScope(function () use ($from, $to, $tenant, $perPage) {
            $query = CollectionTeam::withoutTrashed()
                ->with(['driver', 'scanner', 'truck'])
                ->when($tenant, fn ($q) => $q->where('collection_teams.tenant_id', $tenant->id));

            // Eager-load trip IDs, then compute aggregates in PHP to avoid
            // complex cross-table subqueries that differ by DB engine.
            return $query->orderBy('name')->paginate($perPage);
        });

        $teamIds   = $rows->pluck('id');
        $fromStr   = $from->toDateTimeString();
        $toStr     = $to->toDateTimeString();

        // Batch-load all aggregate data using a single query per metric.
        $tripStats = Tenancy::withoutScope(function () use ($teamIds, $from, $to) {
            return DB::table('trips')
                ->select(
                    'team_id',
                    DB::raw('COUNT(*) as total_trips'),
                    DB::raw("SUM(CASE WHEN status IN ('completed','handed_off') THEN 1 ELSE 0 END) as completed_trips"),
                    DB::raw("SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_trips"),
                    DB::raw('SUM(COALESCE(total_load_kg, 0)) as total_weight_kg'),
                    DB::raw('MAX(scheduled_date) as last_trip_date')
                )
                ->whereIn('team_id', $teamIds)
                ->whereBetween('scheduled_date', [$from->toDateString(), $to->toDateString()])
                ->whereNull('deleted_at')
                ->groupBy('team_id')
                ->get()
                ->keyBy('team_id');
        });

        $scanStats = Tenancy::withoutScope(function () use ($teamIds, $fromStr, $toStr) {
            return DB::table('collection_logs')
                ->select('team_id', DB::raw('COUNT(id) as total_scans'))
                ->whereIn('team_id', $teamIds)
                ->whereBetween('scanned_at', [$fromStr, $toStr])
                ->groupBy('team_id')
                ->get()
                ->keyBy('team_id');
        });

        $eventStats = Tenancy::withoutScope(function () use ($teamIds, $fromStr, $toStr) {
            return DB::table('trip_timeline_events')
                ->join('trips', 'trips.id', '=', 'trip_timeline_events.trip_id')
                ->select('trips.team_id', DB::raw('COUNT(trip_timeline_events.id) as event_count'))
                ->whereIn('trips.team_id', $teamIds)
                ->whereBetween('trip_timeline_events.event_at', [$fromStr, $toStr])
                ->whereNull('trips.deleted_at')
                ->groupBy('trips.team_id')
                ->get()
                ->keyBy('team_id');
        });

        // Map teams to leaderboard rows.
        $leaderboard = $rows->map(function (CollectionTeam $team) use ($tripStats, $scanStats, $eventStats) {
            $ts = $tripStats->get($team->id);
            $ss = $scanStats->get($team->id);
            $es = $eventStats->get($team->id);

            $totalTrips     = (int) ($ts->total_trips ?? 0);
            $completedTrips = (int) ($ts->completed_trips ?? 0);
            $totalWeight    = (int) ($ts->total_weight_kg ?? 0);
            $totalScans     = (int) ($ss->total_scans ?? 0);

            return [
                'team_uuid'              => $team->uuid,
                'team_name'              => $team->name,
                'driver_name'            => $team->driver?->full_name,
                'scanner_name'           => $team->scanner?->full_name,
                'truck_plate'            => $team->truck?->plate_number,
                'status'                 => $team->status,
                'total_trips'            => $totalTrips,
                'completed_trips'        => $completedTrips,
                'cancelled_trips'        => (int) ($ts->cancelled_trips ?? 0),
                'completion_rate_percent'=> $totalTrips > 0
                    ? round(($completedTrips / $totalTrips) * 100, 1)
                    : 0,
                'total_scans'            => $totalScans,
                'total_weight_kg'        => $totalWeight,
                'avg_load_per_trip_kg'   => $totalTrips > 0
                    ? (int) round($totalWeight / $totalTrips)
                    : 0,
                'event_count'            => (int) ($es->event_count ?? 0),
                'last_trip_date'         => $ts?->last_trip_date,
            ];
        })->sortByDesc('total_scans')->values();

        return $this->ok($leaderboard, null, [
            'page'      => $rows->currentPage(),
            'per_page'  => $rows->perPage(),
            'total'     => $rows->total(),
            'last_page' => $rows->lastPage(),
            'from'      => $from->toDateString(),
            'to'        => $to->toDateString(),
        ]);
    }

    /**
     * GET /api/v1/admin/reports/teams/{team:uuid}/profile
     *
     * Returns the full analytics profile for a single team:
     * KPIs, daily scan trend, weekly scan trend, trip history, and full event log.
     */
    public function teamProfile(Request $request, CollectionTeam $team): JsonResponse
    {
        $data = $request->validate([
            'from'       => ['nullable', 'date'],
            'to'         => ['nullable', 'date'],
            'trip_page'  => ['nullable', 'integer', 'min:1'],
            'tenant_id'  => ['nullable', 'integer', 'exists:tenants,id'],
        ]);

        $from      = Carbon::parse($data['from'] ?? now()->subDays(30))->startOfDay();
        $to        = Carbon::parse($data['to'] ?? now())->endOfDay();
        $tripPage  = (int) ($data['trip_page'] ?? 1);
        $fromDate  = $from->toDateString();
        $toDate    = $to->toDateString();
        $fromDT    = $from->toDateTimeString();
        $toDT      = $to->toDateTimeString();

        // Re-load team fully (route model binding already resolved it, but load relations)
        $team->load(['driver', 'scanner', 'truck', 'area']);

        // Get all trip IDs for this team in range.
        $tripIds = Tenancy::withoutScope(function () use ($team, $fromDate, $toDate) {
            return Trip::withoutGlobalScopes()
                ->where('team_id', $team->id)
                ->whereBetween('scheduled_date', [$fromDate, $toDate])
                ->pluck('id');
        });

        // --- KPIs ---
        $kpis = $this->buildKpis($team->id, $tripIds, $fromDate, $toDate, $fromDT, $toDT);

        // --- Daily scans (group by DATE) ---
        $dailyScans = Tenancy::withoutScope(function () use ($team, $fromDT, $toDT) {
            return DB::table('collection_logs')
                ->selectRaw('DATE(scanned_at) as date, COUNT(*) as scans')
                ->where('team_id', $team->id)
                ->whereBetween('scanned_at', [$fromDT, $toDT])
                ->groupByRaw('DATE(scanned_at)')
                ->orderBy('date')
                ->get()
                ->map(fn ($r) => ['date' => $r->date, 'scans' => (int) $r->scans]);
        });

        // --- Weekly scans (group by YEARWEEK) ---
        $weeklyScans = Tenancy::withoutScope(function () use ($team, $fromDT, $toDT) {
            return DB::table('collection_logs')
                ->selectRaw("DATE(DATE_SUB(scanned_at, INTERVAL WEEKDAY(scanned_at) DAY)) as week_start, COUNT(*) as scans")
                ->where('team_id', $team->id)
                ->whereBetween('scanned_at', [$fromDT, $toDT])
                ->groupByRaw("DATE(DATE_SUB(scanned_at, INTERVAL WEEKDAY(scanned_at) DAY))")
                ->orderBy('week_start')
                ->get()
                ->map(fn ($r) => ['week_start' => $r->week_start, 'scans' => (int) $r->scans]);
        });

        // --- Trip history (paginated, 10/page) ---
        $tripsData = Tenancy::withoutScope(function () use ($team, $fromDate, $toDate, $tripPage, $tripIds) {
            $perPage = 10;
            $trips   = Trip::withoutGlobalScopes()
                ->withCount('collectionLogs')
                ->where('team_id', $team->id)
                ->whereBetween('scheduled_date', [$fromDate, $toDate])
                ->orderByDesc('scheduled_date')
                ->paginate($perPage, ['*'], 'trip_page', $tripPage);

            return [
                'data' => $trips->map(fn (Trip $t) => [
                    'trip_number'       => $t->trip_number,
                    'scheduled_date'    => $t->scheduled_date?->toDateString(),
                    'status'            => $t->status,
                    'scans_count'       => $t->collection_logs_count,
                    'total_load_kg'     => (int) ($t->total_load_kg ?? 0),
                    'duration_minutes'  => ($t->actual_start_time && $t->actual_end_time)
                        ? (int) $t->actual_start_time->diffInMinutes($t->actual_end_time)
                        : null,
                    'actual_start_time' => $t->actual_start_time?->toIso8601String(),
                    'actual_end_time'   => $t->actual_end_time?->toIso8601String(),
                ]),
                'meta' => [
                    'page'      => $trips->currentPage(),
                    'per_page'  => $trips->perPage(),
                    'total'     => $trips->total(),
                    'last_page' => $trips->lastPage(),
                ],
            ];
        });

        // --- Event log (all event types, last 200) ---
        $events = Tenancy::withoutScope(function () use ($tripIds, $fromDT, $toDT) {
            return DB::table('trip_timeline_events')
                ->join('trips', 'trips.id', '=', 'trip_timeline_events.trip_id')
                ->select(
                    'trip_timeline_events.event_type',
                    'trip_timeline_events.event_at',
                    'trip_timeline_events.notes',
                    'trip_timeline_events.metadata',
                    'trips.trip_number'
                )
                ->whereIn('trip_timeline_events.trip_id', $tripIds)
                ->whereBetween('trip_timeline_events.event_at', [$fromDT, $toDT])
                ->orderByDesc('trip_timeline_events.event_at')
                ->limit(200)
                ->get()
                ->map(fn ($e) => [
                    'event_type'  => $e->event_type,
                    'event_at'    => $e->event_at,
                    'trip_number' => $e->trip_number,
                    'notes'       => $e->notes,
                    'metadata'    => $e->metadata ? json_decode($e->metadata, true) : null,
                ]);
        });

        return $this->ok([
            'team' => [
                'uuid'    => $team->uuid,
                'name'    => $team->name,
                'status'  => $team->status,
                'area'    => $team->area?->name,
                'driver'  => $team->driver ? [
                    'name'  => $team->driver->full_name,
                    'email' => $team->driver->email,
                ] : null,
                'scanner' => $team->scanner ? [
                    'name'  => $team->scanner->full_name,
                    'email' => $team->scanner->email,
                ] : null,
                'truck'   => $team->truck ? [
                    'plate'       => $team->truck->plate_number,
                    'model'       => $team->truck->model,
                    'capacity_kg' => $team->truck->capacity_kg,
                ] : null,
            ],
            'kpis'         => $kpis,
            'daily_scans'  => $dailyScans,
            'weekly_scans' => $weeklyScans,
            'trips'        => $tripsData,
            'events'       => $events,
        ]);
    }

    /**
     * Compute all KPI metrics for a team within the given date range.
     */
    private function buildKpis(int $teamId, $tripIds, string $fromDate, string $toDate, string $fromDT, string $toDT): array
    {
        $tripStats = Tenancy::withoutScope(function () use ($teamId, $fromDate, $toDate) {
            return DB::table('trips')
                ->select(
                    DB::raw('COUNT(*) as total_trips'),
                    DB::raw("SUM(CASE WHEN status IN ('completed','handed_off') THEN 1 ELSE 0 END) as completed_trips"),
                    DB::raw("SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_trips"),
                    DB::raw('SUM(COALESCE(total_load_kg,0)) as total_weight_kg'),
                    // On-time: actual_start_time <= scheduled_start_time + 30 minutes
                    DB::raw("SUM(CASE
                        WHEN actual_start_time IS NOT NULL AND scheduled_start_time IS NOT NULL
                             AND actual_start_time <= DATE_ADD(
                                 CONCAT(scheduled_date, ' ', scheduled_start_time),
                                 INTERVAL 30 MINUTE
                             )
                        THEN 1 ELSE 0 END) as on_time_trips")
                )
                ->where('team_id', $teamId)
                ->whereBetween('scheduled_date', [$fromDate, $toDate])
                ->whereNull('deleted_at')
                ->first();
        });

        $scanStats = Tenancy::withoutScope(function () use ($teamId, $fromDT, $toDT) {
            return DB::table('collection_logs')
                ->selectRaw('COUNT(*) as total_scans, SUM(COALESCE(weight_kg,0)) as total_scan_weight')
                ->where('team_id', $teamId)
                ->whereBetween('scanned_at', [$fromDT, $toDT])
                ->first();
        });

        $eventStats = Tenancy::withoutScope(function () use ($tripIds, $fromDT, $toDT) {
            return DB::table('trip_timeline_events')
                ->selectRaw("
                    COUNT(*) as total_events,
                    SUM(CASE WHEN event_type IN ('incident_reported','breakdown') THEN 1 ELSE 0 END) as incident_count,
                    SUM(CASE WHEN event_type = 'stop_skipped' THEN 1 ELSE 0 END) as stops_skipped_count,
                    SUM(CASE WHEN event_type = 'detour_to_dumpsite' THEN 1 ELSE 0 END) as detours_count,
                    SUM(CASE WHEN event_type = 'breakdown' THEN 1 ELSE 0 END) as breakdowns_count,
                    SUM(CASE WHEN event_type = 'truck_full_warning' THEN 1 ELSE 0 END) as truck_full_count
                ")
                ->whereIn('trip_id', $tripIds)
                ->whereBetween('event_at', [$fromDT, $toDT])
                ->first();
        });

        $totalTrips     = (int) ($tripStats->total_trips ?? 0);
        $completedTrips = (int) ($tripStats->completed_trips ?? 0);
        $onTimeTrips    = (int) ($tripStats->on_time_trips ?? 0);
        $totalWeight    = (int) ($tripStats->total_weight_kg ?? 0);
        $totalScans     = (int) ($scanStats->total_scans ?? 0);

        return [
            'total_trips'              => $totalTrips,
            'completed_trips'          => $completedTrips,
            'cancelled_trips'          => (int) ($tripStats->cancelled_trips ?? 0),
            'completion_rate_percent'  => $totalTrips > 0 ? round(($completedTrips / $totalTrips) * 100, 1) : 0,
            'on_time_trips'            => $onTimeTrips,
            'on_time_rate_percent'     => $completedTrips > 0 ? round(($onTimeTrips / $completedTrips) * 100, 1) : 0,
            'total_scans'              => $totalScans,
            'total_weight_kg'          => $totalWeight,
            'avg_load_per_trip_kg'     => $totalTrips > 0 ? (int) round($totalWeight / $totalTrips) : 0,
            'incident_count'           => (int) ($eventStats->incident_count ?? 0),
            'stops_skipped_count'      => (int) ($eventStats->stops_skipped_count ?? 0),
            'detours_count'            => (int) ($eventStats->detours_count ?? 0),
            'breakdowns_count'         => (int) ($eventStats->breakdowns_count ?? 0),
            'truck_full_count'         => (int) ($eventStats->truck_full_count ?? 0),
            'total_events'             => (int) ($eventStats->total_events ?? 0),
        ];
    }

    private function resolveTargetTenant(Request $request): ?Tenant
    {
        if (optional($request->user())->isSuperAdmin() && $request->filled('tenant_id')) {
            return Tenant::find($request->input('tenant_id'));
        }

        return Tenancy::current();
    }
}