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

use App\Http\Controllers\Controller;
use App\Models\Product;
use App\Models\Transporter;
use App\Models\User;
use App\Models\WeighbridgeTicket;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

/** Live data for the Command Centre — Weighbridge module (all sites: tickets are not quarry-linked). */
class WeighController extends Controller
{
    use Concerns\DashboardHelpers;

    public function show(Request $r)
    {
        $this->need('sales.view');
        [$cf, $ct, $pf, $pt, $grain] = $this->windows((string) $r->get('period', 'This Month'));
        $today = Carbon::today()->format('Y-m-d');
        $yest = Carbon::yesterday()->format('Y-m-d');

        $tickToday = WeighbridgeTicket::whereDate('ticket_date', $today)->count();
        $tickYest = WeighbridgeTicket::whereDate('ticket_date', $yest)->count();
        $netToday = (float) WeighbridgeTicket::whereDate('ticket_date', $today)->sum('net_weight');
        $netYest = (float) WeighbridgeTicket::whereDate('ticket_date', $yest)->sum('net_weight');
        $net = $this->sumBetween(WeighbridgeTicket::query(), 'ticket_date', 'net_weight', $cf, $ct);
        $netP = $this->sumBetween(WeighbridgeTicket::query(), 'ticket_date', 'net_weight', $pf, $pt);
        $ticks = WeighbridgeTicket::whereBetween('ticket_date', [$cf, $ct])->count();
        $ticksP = WeighbridgeTicket::whereBetween('ticket_date', [$pf, $pt])->count();
        $gross = $this->sumBetween(WeighbridgeTicket::query(), 'ticket_date', 'gross_weight', $cf, $ct);
        $grossP = $this->sumBetween(WeighbridgeTicket::query(), 'ticket_date', 'gross_weight', $pf, $pt);
        $tare = $this->sumBetween(WeighbridgeTicket::query(), 'ticket_date', 'tare_weight', $cf, $ct);
        $tareP = $this->sumBetween(WeighbridgeTicket::query(), 'ticket_date', 'tare_weight', $pf, $pt);
        $avgNet = $ticks > 0 ? round($net / $ticks, 1) : 0;
        $avgNetP = $ticksP > 0 ? round($netP / $ticksP, 1) : 0;
        $trucks = WeighbridgeTicket::whereBetween('ticket_date', [$cf, $ct])->distinct('vehicle_no')->count('vehicle_no');
        $trucksP = WeighbridgeTicket::whereBetween('ticket_date', [$pf, $pt])->distinct('vehicle_no')->count('vehicle_no');
        $turn = $this->avgTurnaround($cf, $ct);
        $turnP = $this->avgTurnaround($pf, $pt);
        $open = WeighbridgeTicket::whereNull('second_weight_time')->count();
        $transp = WeighbridgeTicket::whereBetween('ticket_date', [$cf, $ct])->whereNotNull('transporter_id')->distinct('transporter_id')->count('transporter_id');
        $custs = WeighbridgeTicket::whereBetween('ticket_date', [$cf, $ct])->whereNotNull('customer_id')->distinct('customer_id')->count('customer_id');

        $cards = [
            $this->card('Tickets Today', $tickToday, '#', $tickYest),
            $this->card('Net Weighed Today', round($netToday, 1), 't', round($netYest, 1)),
            $this->card('Net Weighed (' . $r->get('period', 'This Month') . ')', round($net, 1), 't', round($netP, 1), $this->spark('weighbridge_tickets', 'ticket_date', 'net_weight', '1=1', $grain)),
            $this->card('Tickets (' . $r->get('period', 'This Month') . ')', $ticks, '#', $ticksP),
            $this->card('Gross Weight', round($gross, 1), 't', round($grossP, 1)),
            $this->card('Tare Weight', round($tare, 1), 't', round($tareP, 1)),
            $this->card('Avg Net per Ticket', $avgNet, 'num', $avgNetP, [], ['info' => 'tonnes']),
            $this->card('Trucks Served', $trucks, '#', $trucksP, [], ['st' => 'blue']),
            $this->card('Avg Turnaround', $turn, 'num', $turnP, [], ['good' => 'down', 'info' => 'minutes, weigh-in to weigh-out']),
            $this->card('Open Trucks on Site', $open, '#', $open, [], ['good' => 'down', 'st' => $open > 0 ? 'amber' : 'green']),
            $this->card('Active Transporters', $transp, '#', $transp, [], ['st' => 'blue']),
            $this->card('Dispatch Customers', $custs, '#', $custs, [], ['st' => 'blue']),
        ];

        return response()->json([
            'sub' => 'Dispatches · turnaround · open trucks · all sites · ' . $r->get('period', 'This Month'),
            'asAt' => Carbon::today()->format('d M Y'),
            'cards' => $cards,
            'sections' => [
                $this->secTrend(),
                $this->secTurn(),
                $this->secByProduct($cf, $ct),
                $this->secByTransporter($cf, $ct),
                $this->secHour($cf, $ct),
                $this->secTickets(),
                $this->secOpen(),
            ],
        ]);
    }

    private function avgTurnaround($f, $t): float
    {
        $v = WeighbridgeTicket::whereBetween('ticket_date', [$f, $t])
            ->whereNotNull('second_weight_time')
            ->selectRaw('avg(timestampdiff(minute, first_weight_time, second_weight_time)) a')->value('a');
        return round((float) $v, 0);
    }

    private function secTrend(): array
    {
        $data = $this->daily('weighbridge_tickets', 'ticket_date', 'net_weight', '1=1', 30);
        return ['k' => 'chart', 'id' => 'w_trend', 'ti' => 'Net Tonnes Weighed — per day (30 days)', 'cls' => 'wide', 'spec' => [
            'type' => 'line', 'fmt' => 't', 'zoom' => true, 'height' => 280,
            'labels' => $this->dailyLabels(30),
            'series' => [['name' => 'Net t', 'data' => $data, 'color' => '#f8c200', 'area' => true]],
            'target' => ['value' => count($data) ? round(array_sum($data) / count($data), 1) : 0, 'label' => '30-day avg'],
        ]];
    }

    private function secTurn(): array
    {
        $t = Carbon::today();
        $data = [];
        for ($i = 29; $i >= 0; $i--) {
            $d = $t->copy()->subDays($i)->format('Y-m-d');
            $data[] = $this->avgTurnaround($d, $d);
        }
        return ['k' => 'chart', 'id' => 'w_turn', 'ti' => 'Avg Turnaround — minutes per day (30 days)', 'cls' => 'wide', 'spec' => [
            'type' => 'line', 'fmt' => 'num', 'zoom' => true, 'height' => 260,
            'labels' => $this->dailyLabels(30),
            'series' => [['name' => 'Minutes', 'data' => $data, 'color' => '#3b82f6', 'area' => true]],
            'target' => ['value' => 60, 'label' => 'Target 60'],
        ]];
    }

    private function secByProduct($cf, $ct): array
    {
        $rows = WeighbridgeTicket::whereBetween('ticket_date', [$cf, $ct])
            ->select('product_id', DB::raw('sum(net_weight) t'))->groupBy('product_id')->orderByDesc('t')->get();
        $names = Product::whereIn('id', $rows->pluck('product_id'))->pluck('name', 'id');
        $pal = $this->palette();
        $items = $rows->values()->map(fn($r, $i) => ['label' => mb_strimwidth($names[$r->product_id] ?? ('#' . $r->product_id), 0, 30, '…'), 'value' => round((float) $r->t, 1), 'color' => $pal[$i % count($pal)]])->all();
        return ['k' => 'chart', 'id' => 'w_byprod', 'ti' => 'Dispatches by Product — net tonnes (period)', 'spec' => [
            'type' => 'doughnut', 'fmt' => 't', 'center' => 'Mix', 'items' => $items,
        ]];
    }

    private function secByTransporter($cf, $ct): array
    {
        $rows = WeighbridgeTicket::whereBetween('ticket_date', [$cf, $ct])->whereNotNull('transporter_id')
            ->select('transporter_id', DB::raw('sum(net_weight) t'))->groupBy('transporter_id')->orderByDesc('t')->take(8)->get();
        $names = Transporter::whereIn('id', $rows->pluck('transporter_id'))->pluck('name', 'id');
        return ['k' => 'chart', 'id' => 'w_bytrans', 'ti' => 'Top Transporters — net tonnes (period)', 'spec' => [
            'type' => 'hbar', 'fmt' => 't',
            'labels' => $rows->map(fn($r) => mb_strimwidth($names[$r->transporter_id] ?? ('#' . $r->transporter_id), 0, 24, '…'))->all(),
            'series' => [['name' => 'Net t', 'data' => $rows->map(fn($r) => round((float) $r->t, 1))->all(), 'color' => '#22c55e']],
        ]];
    }

    private function secHour($cf, $ct): array
    {
        $rows = WeighbridgeTicket::whereBetween('ticket_date', [$cf, $ct])->whereNotNull('first_weight_time')
            ->selectRaw('hour(first_weight_time) div 2 b, count(*) n')->groupBy('b')->orderBy('b')->pluck('n', 'b');
        $labels = [];
        $data = [];
        for ($b = 0; $b < 12; $b++) {
            $labels[] = sprintf('%02d–%02d', $b * 2, $b * 2 + 2);
            $data[] = (int) ($rows[$b] ?? 0);
        }
        return ['k' => 'chart', 'id' => 'w_hour', 'ti' => 'Weigh-ins by Time of Day — tickets (period)', 'spec' => [
            'type' => 'bar', 'fmt' => 'num',
            'labels' => $labels,
            'series' => [['name' => 'Tickets', 'data' => $data, 'color' => '#a855f7']],
        ]];
    }

    private function secTickets(): array
    {
        $ops = User::pluck('name', 'id');
        $rows = WeighbridgeTicket::with(['customer', 'transporter', 'product'])
            ->orderByDesc('ticket_date')->orderByDesc('id')->take(60)->get()
            ->map(function ($t) use ($ops) {
                $turn = ($t->first_weight_time && $t->second_weight_time)
                    ? Carbon::parse($t->first_weight_time)->diffInMinutes(Carbon::parse($t->second_weight_time)) : null;
                return ['no' => $t->ticket_no, 'date' => substr((string) $t->ticket_date, 0, 10),
                    'vehicle' => $t->vehicle_no, 'customer' => $t->customer->name ?? '', 'transporter' => $t->transporter->name ?? '',
                    'product' => $t->product->name ?? '', 'gross' => round((float) $t->gross_weight, 2),
                    'tare' => round((float) $t->tare_weight, 2), 'net' => round((float) $t->net_weight, 2),
                    'in' => $t->first_weight_time ? substr((string) $t->first_weight_time, 11, 5) : '',
                    'out' => $t->second_weight_time ? substr((string) $t->second_weight_time, 11, 5) : '',
                    'turn' => $turn ?? '—', 'op' => $ops[$t->operator_id] ?? '',
                    'st' => $t->status === 'closed' ? 'green' : 'amber', 'stL' => ucfirst((string) $t->status)];
            })->all();
        return ['k' => 'table', 'id' => 'w_tickets', 'ti' => 'Weighbridge Register — latest 60', 'cls' => 'wide', 'cfg' => [
            'id' => 'w_tickets', 'page' => 12, 'search' => true,
            'cols' => [
                ['k' => 'no', 'l' => 'Ticket'], ['k' => 'date', 'l' => 'Date'], ['k' => 'vehicle', 'l' => 'Vehicle'],
                ['k' => 'customer', 'l' => 'Customer', 'wide' => 1], ['k' => 'transporter', 'l' => 'Transporter'],
                ['k' => 'product', 'l' => 'Product'], ['k' => 'gross', 'l' => 'Gross (t)', 'y' => 'num'],
                ['k' => 'tare', 'l' => 'Tare (t)', 'y' => 'num'], ['k' => 'net', 'l' => 'Net (t)', 'y' => 'num'],
                ['k' => 'in', 'l' => 'In'], ['k' => 'out', 'l' => 'Out'], ['k' => 'turn', 'l' => 'Turn (min)'],
                ['k' => 'op', 'l' => 'Operator'], ['k' => 'st', 'l' => 'Status', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => ['gross', 'tare', 'net'],
        ]];
    }

    private function secOpen(): array
    {
        $rows = WeighbridgeTicket::with(['customer', 'product'])
            ->whereNull('second_weight_time')->orderBy('first_weight_time')->get()
            ->map(fn($t) => ['no' => $t->ticket_no, 'date' => substr((string) $t->ticket_date, 0, 10),
                'vehicle' => $t->vehicle_no, 'driver' => $t->driver_name,
                'customer' => $t->customer->name ?? '', 'product' => $t->product->name ?? '',
                'gross' => round((float) $t->gross_weight, 2),
                'in' => $t->first_weight_time ? substr((string) $t->first_weight_time, 11, 5) : '',
                'age' => $t->first_weight_time ? round(Carbon::parse($t->first_weight_time)->diffInMinutes(now()) / 60, 1) . 'h' : '—',
                'st' => 'amber', 'stL' => 'On site'])->all();
        return ['k' => 'table', 'id' => 'w_open', 'ti' => 'Open Trucks on Site — weighed in, not out', 'cls' => 'wide', 'cfg' => [
            'id' => 'w_open', 'page' => 10, 'search' => true,
            'cols' => [
                ['k' => 'no', 'l' => 'Ticket'], ['k' => 'date', 'l' => 'Date'], ['k' => 'vehicle', 'l' => 'Vehicle'],
                ['k' => 'driver', 'l' => 'Driver'], ['k' => 'customer', 'l' => 'Customer', 'wide' => 1],
                ['k' => 'product', 'l' => 'Product'], ['k' => 'gross', 'l' => 'Gross (t)', 'y' => 'num'],
                ['k' => 'in', 'l' => 'In'], ['k' => 'age', 'l' => 'On site'], ['k' => 'st', 'l' => 'Status', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => [],
        ]];
    }
}
