<?php
namespace App\Http\Controllers\Api\V1;

use App\Http\Controllers\Controller;
use App\Models\DeliveryNote;
use App\Models\DeliveryNoteLine;
use App\Models\Equipment;
use App\Models\Transporter;
use App\Models\TripLog;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

/** Live data for the Command Centre — Fleet & Transport module. */
class FleetController extends Controller
{
    use Concerns\DashboardHelpers;

    public function show(Request $r)
    {
        $this->need('fleet.view');
        $qry = $r->get('qry', 'all');
        [$cf, $ct, $pf, $pt, $grain] = $this->windows((string) $r->get('period', 'This Month'));
        $es = $qry !== 'all' ? 'equipment_id in (select id from equipment where current_quarry_id=' . (int) $qry . ')' : '1=1';

        $tripQ = fn() => TripLog::when($qry !== 'all', fn($q) => $q->whereHas('equipment', fn($w) => $w->where('current_quarry_id', $qry)));
        $logs = (clone $tripQ())->whereBetween('trip_date', [$cf, $ct])->count();
        $logsP = (clone $tripQ())->whereBetween('trip_date', [$pf, $pt])->count();
        $trips = $this->sumBetween($tripQ(), 'trip_date', 'trips_count', $cf, $ct);
        $tripsP = $this->sumBetween($tripQ(), 'trip_date', 'trips_count', $pf, $pt);
        $ton = $this->sumBetween($tripQ(), 'trip_date', 'tonnage', $cf, $ct);
        $tonP = $this->sumBetween($tripQ(), 'trip_date', 'tonnage', $pf, $pt);
        $km = (float) (clone $tripQ())->whereBetween('trip_date', [$cf, $ct])->selectRaw('sum(end_km-start_km) v')->value('v');
        $kmP = (float) (clone $tripQ())->whereBetween('trip_date', [$pf, $pt])->selectRaw('sum(end_km-start_km) v')->value('v');
        $cost = $this->sumBetween($tripQ(), 'trip_date', 'transport_cost', $cf, $ct);
        $costP = $this->sumBetween($tripQ(), 'trip_date', 'transport_cost', $pf, $pt);

        $costT = $ton > 0 ? round($cost / $ton, 2) : 0;
        $costTP = $tonP > 0 ? round($costP / $tonP, 2) : 0;
        $costKm = $km > 0 ? round($cost / $km, 2) : 0;
        $costKmP = $kmP > 0 ? round($costP / $kmP, 2) : 0;
        $avgT = $trips > 0 ? round($ton / $trips, 1) : 0;
        $avgTP = $tripsP > 0 ? round($tonP / $tripsP, 1) : 0;
        $trucks = (clone $tripQ())->whereBetween('trip_date', [$cf, $ct])->distinct('equipment_id')->count('equipment_id');
        $drivers = (clone $tripQ())->whereBetween('trip_date', [$cf, $ct])->distinct('driver_name')->count('driver_name');

        $hired = DeliveryNote::when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))
            ->whereBetween('dn_date', [$cf, $ct])->whereNotNull('transporter_id')->count();
        $hiredP = DeliveryNote::when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))
            ->whereBetween('dn_date', [$pf, $pt])->whereNotNull('transporter_id')->count();
        $dnTon = (float) DeliveryNoteLine::whereHas('deliveryNote', function ($q) use ($qry, $cf, $ct) {
            $q->whereBetween('dn_date', [$cf, $ct]);
            if ($qry !== 'all') $q->where('quarry_id', $qry);
        })->sum('qty_tonnes');
        $ownShare = $dnTon > 0 ? round(min($ton / $dnTon, 1) * 100, 1) : 0;

        $cards = [
            $this->card('Trip Logs', $logs, '#', $logsP),
            $this->card('Trips Run', round($trips), '#', round($tripsP)),
            $this->card('Tonnage Hauled (' . $r->get('period', 'This Month') . ')', round($ton, 1), 't', round($tonP, 1), $this->spark('trip_logs', 'trip_date', 'tonnage', $es, $grain)),
            $this->card('Km Covered', round($km), 'num', round($kmP), [], ['info' => 'kilometres']),
            $this->card('Transport Cost', round($cost), 'GHS', round($costP), $this->spark('trip_logs', 'trip_date', 'transport_cost', $es, $grain), ['good' => 'down']),
            $this->card('Cost per Tonne', $costT, 'GHS', $costTP, [], ['good' => 'down']),
            $this->card('Cost per Km', $costKm, 'GHS', $costKmP, [], ['good' => 'down']),
            $this->card('Avg Tonnes per Trip', $avgT, 'num', $avgTP, [], ['good' => 'up']),
            $this->card('Active Trucks', $trucks, '#', $trucks, [], ['st' => 'blue']),
            $this->card('Active Drivers', $drivers, '#', $drivers, [], ['st' => 'blue']),
            $this->card('Hired Haulier Jobs', $hired, '#', $hiredP),
            $this->card('Own-Fleet Share', $ownShare, '%', $ownShare, [], ['tgt' => 70]),
        ];

        return response()->json([
            'sub' => 'Own fleet trips · haulage cost · hired hauliers · ' . $r->get('period', 'This Month'),
            'asAt' => Carbon::today()->format('d M Y'),
            'cards' => $cards,
            'sections' => [
                $this->secTrend($es),
                $this->secCost($es),
                $this->secByTruck($qry, $cf, $ct),
                $this->secByDest($qry, $cf, $ct),
                $this->secTrips($qry),
                $this->secTrucks($qry, $cf, $ct),
                $this->secHired($qry),
                $this->secTransp($qry, $cf, $ct),
            ],
        ]);
    }

    private function secTrend($es): array
    {
        $data = $this->daily('trip_logs', 'trip_date', 'tonnage', $es, 30);
        return ['k' => 'chart', 'id' => 'f_trend', 'ti' => 'Tonnage Hauled — per day (30 days)', 'cls' => 'wide', 'spec' => [
            'type' => 'line', 'fmt' => 't', 'zoom' => true, 'height' => 280,
            'labels' => $this->dailyLabels(30),
            'series' => [['name' => 'Tonnes', 'data' => $data, 'color' => '#22c55e', 'area' => true]],
            'target' => ['value' => count($data) ? round(array_sum($data) / count($data), 1) : 0, 'label' => '30-day avg'],
        ]];
    }

    private function secCost($es): array
    {
        return ['k' => 'chart', 'id' => 'f_cost', 'ti' => 'Transport Cost — GHS per day (30 days)', 'cls' => 'wide', 'spec' => [
            'type' => 'line', 'fmt' => 'money', 'zoom' => true, 'height' => 260,
            'labels' => $this->dailyLabels(30),
            'series' => [['name' => 'Cost', 'data' => $this->daily('trip_logs', 'trip_date', 'transport_cost', $es, 30), 'color' => '#f97316', 'area' => true]],
        ]];
    }

    private function secByTruck($qry, $cf, $ct): array
    {
        $rows = TripLog::when($qry !== 'all', fn($q) => $q->whereHas('equipment', fn($w) => $w->where('current_quarry_id', $qry)))
            ->whereBetween('trip_date', [$cf, $ct])
            ->select('equipment_id', DB::raw('sum(tonnage) t'))->groupBy('equipment_id')->orderByDesc('t')->take(8)->get();
        $names = Equipment::whereIn('id', $rows->pluck('equipment_id'))->pluck('code', 'id');
        return ['k' => 'chart', 'id' => 'f_bytruck', 'ti' => 'Tonnage by Truck — tonnes (period)', 'spec' => [
            'type' => 'hbar', 'fmt' => 't',
            'labels' => $rows->map(fn($r) => $names[$r->equipment_id] ?? ('#' . $r->equipment_id))->all(),
            'series' => [['name' => 'Tonnes', 'data' => $rows->map(fn($r) => round((float) $r->t, 1))->all(), 'color' => '#3b82f6']],
        ]];
    }

    private function secByDest($qry, $cf, $ct): array
    {
        $rows = TripLog::when($qry !== 'all', fn($q) => $q->whereHas('equipment', fn($w) => $w->where('current_quarry_id', $qry)))
            ->whereBetween('trip_date', [$cf, $ct])
            ->select('to_location', DB::raw('sum(tonnage) t'))->groupBy('to_location')->orderByDesc('t')->get();
        $pal = $this->palette();
        $items = $rows->values()->map(fn($r, $i) => ['label' => mb_strimwidth($r->to_location ?? '—', 0, 30, '…'), 'value' => round((float) $r->t, 1), 'color' => $pal[$i % count($pal)]])->all();
        return ['k' => 'chart', 'id' => 'f_bydest', 'ti' => 'Tonnage by Destination — tonnes (period)', 'spec' => [
            'type' => 'doughnut', 'fmt' => 't', 'center' => 'Dest.', 'items' => $items,
        ]];
    }

    private function secTrips($qry): array
    {
        $rows = TripLog::with(['equipment', 'customer', 'product'])
            ->when($qry !== 'all', fn($q) => $q->whereHas('equipment', fn($w) => $w->where('current_quarry_id', $qry)))
            ->orderByDesc('trip_date')->orderByDesc('id')->take(60)->get()
            ->map(fn($t) => ['no' => $t->trip_no, 'date' => substr((string) $t->trip_date, 0, 10),
                'truck' => $t->equipment->code ?? '', 'driver' => $t->driver_name ?? '',
                'from' => mb_strimwidth($t->from_location ?? '', 0, 24, '…'), 'to' => mb_strimwidth($t->to_location ?? '', 0, 24, '…'),
                'cust' => $t->customer->name ?? '', 'prod' => $t->product->name ?? '',
                'trips' => (int) $t->trips_count, 'ton' => round((float) $t->tonnage, 1),
                'km' => round((float) $t->end_km - (float) $t->start_km), 'cost' => round((float) $t->transport_cost)])->all();
        return ['k' => 'table', 'id' => 'f_trips', 'ti' => 'Trip Log — latest 60', 'cls' => 'wide', 'cfg' => [
            'id' => 'f_trips', 'page' => 12, 'search' => true,
            'cols' => [
                ['k' => 'no', 'l' => 'Trip'], ['k' => 'date', 'l' => 'Date'], ['k' => 'truck', 'l' => 'Truck'],
                ['k' => 'driver', 'l' => 'Driver'], ['k' => 'from', 'l' => 'From'], ['k' => 'to', 'l' => 'To'],
                ['k' => 'cust', 'l' => 'Customer', 'wide' => 1], ['k' => 'prod', 'l' => 'Product'],
                ['k' => 'trips', 'l' => 'Trips', 'y' => 'num'], ['k' => 'ton', 'l' => 'Tonnes', 'y' => 'num'],
                ['k' => 'km', 'l' => 'Km', 'y' => 'num'], ['k' => 'cost', 'l' => 'Cost', 'y' => 'num'],
            ], 'rows' => $rows, 'totals' => ['trips', 'ton', 'km', 'cost'],
        ]];
    }

    private function secTrucks($qry, $cf, $ct): array
    {
        $ids = TripLog::when($qry !== 'all', fn($q) => $q->whereHas('equipment', fn($w) => $w->where('current_quarry_id', $qry)))
            ->whereBetween('trip_date', [$cf, $ct])->distinct('equipment_id')->pluck('equipment_id');
        $rows = Equipment::whereIn('id', $ids)->orderBy('code')->get()->map(function ($e) use ($cf, $ct) {
            $agg = TripLog::where('equipment_id', $e->id)->whereBetween('trip_date', [$cf, $ct])
                ->selectRaw('sum(trips_count) tr, sum(tonnage) tn, sum(end_km-start_km) km, sum(transport_cost) c')->first();
            $ton = (float) $agg->tn;
            return ['truck' => $e->code, 'name' => $e->name, 'trips' => (int) $agg->tr,
                'ton' => round($ton, 1), 'km' => round((float) $agg->km), 'cost' => round((float) $agg->c),
                'ct' => $ton > 0 ? round($agg->c / $ton, 2) : 0];
        })->all();
        return ['k' => 'table', 'id' => 'f_trucks', 'ti' => 'Truck Scorecard — trips, cost efficiency (period)', 'cls' => 'wide', 'cfg' => [
            'id' => 'f_trucks', 'page' => 10, 'search' => true,
            'cols' => [
                ['k' => 'truck', 'l' => 'Truck'], ['k' => 'name', 'l' => 'Vehicle', 'wide' => 1],
                ['k' => 'trips', 'l' => 'Trips', 'y' => 'num'], ['k' => 'ton', 'l' => 'Tonnes', 'y' => 'num'],
                ['k' => 'km', 'l' => 'Km', 'y' => 'num'], ['k' => 'cost', 'l' => 'Cost', 'y' => 'num'],
                ['k' => 'ct', 'l' => 'Cost/t', 'y' => 'num'],
            ], 'rows' => $rows, 'totals' => ['trips', 'ton', 'km', 'cost'],
        ]];
    }

    private function secHired($qry): array
    {
        $rows = DeliveryNote::with(['transporter', 'customer', 'ticket'])
            ->when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))->whereNotNull('transporter_id')
            ->orderByDesc('dn_date')->take(30)->get()
            ->map(function ($d) {
                $ton = (float) DeliveryNoteLine::where('delivery_note_id', $d->id)->sum('qty_tonnes');
                return ['no' => $d->dn_no, 'date' => substr((string) $d->dn_date, 0, 10),
                    'transp' => $d->transporter->name ?? '', 'vehicle' => $d->vehicle_no ?? '',
                    'driver' => $d->driver_name ?? '', 'cust' => $d->customer->name ?? '',
                    'ton' => round($ton, 1), 'ticket' => $d->ticket->ticket_no ?? ''];
            })->all();
        return ['k' => 'table', 'id' => 'f_hired', 'ti' => 'Hired Haulier Jobs — latest 30', 'cls' => 'wide', 'cfg' => [
            'id' => 'f_hired', 'page' => 10, 'search' => true,
            'cols' => [
                ['k' => 'no', 'l' => 'DN'], ['k' => 'date', 'l' => 'Date'], ['k' => 'transp', 'l' => 'Haulier', 'wide' => 1],
                ['k' => 'vehicle', 'l' => 'Vehicle'], ['k' => 'driver', 'l' => 'Driver'], ['k' => 'cust', 'l' => 'Customer'],
                ['k' => 'ton', 'l' => 'Tonnes', 'y' => 'num'], ['k' => 'ticket', 'l' => 'WB Ticket'],
            ], 'rows' => $rows, 'totals' => ['ton'],
        ]];
    }

    private function secTransp($qry, $cf, $ct): array
    {
        $rows = Transporter::where('is_active', true)->orderBy('name')->get()->map(function ($t) use ($qry, $cf, $ct) {
            $jobs = DeliveryNote::when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))
                ->where('transporter_id', $t->id)->whereBetween('dn_date', [$cf, $ct])->count();
            $ton = (float) DeliveryNoteLine::whereHas('deliveryNote', function ($q) use ($qry, $t, $cf, $ct) {
                $q->where('transporter_id', $t->id)->whereBetween('dn_date', [$cf, $ct]);
                if ($qry !== 'all') $q->where('quarry_id', $qry);
            })->sum('qty_tonnes');
            return ['name' => $t->name, 'contact' => $t->contact_person ?? '', 'phone' => $t->phone ?? '',
                'vehicle' => $t->vehicle_no ?? '', 'rate' => round((float) $t->rate_per_tonne, 2),
                'jobs' => $jobs, 'ton' => round($ton, 1)];
        })->all();
        return ['k' => 'table', 'id' => 'f_transp', 'ti' => 'Haulier Register — rates & period jobs', 'cls' => 'wide', 'cfg' => [
            'id' => 'f_transp', 'page' => 10, 'search' => true,
            'cols' => [
                ['k' => 'name', 'l' => 'Haulier', 'wide' => 1], ['k' => 'contact', 'l' => 'Contact'],
                ['k' => 'phone', 'l' => 'Phone'], ['k' => 'vehicle', 'l' => 'Vehicle'], ['k' => 'rate', 'l' => 'Rate/t', 'y' => 'num'],
                ['k' => 'jobs', 'l' => 'Jobs', 'y' => 'num'], ['k' => 'ton', 'l' => 'Tonnes', 'y' => 'num'],
            ], 'rows' => $rows, 'totals' => ['jobs', 'ton'],
        ]];
    }
}
