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

use App\Http\Controllers\Controller;
use App\Models\Equipment;
use App\Models\FuelLog;
use App\Models\StockItem;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

/** Live data for the Command Centre — Fuel Management module. */
class FuelController 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';
        $today = Carbon::today()->format('Y-m-d');

        $fuelQ = fn() => FuelLog::when($qry !== 'all', fn($q) => $q->whereHas('equipment', fn($w) => $w->where('current_quarry_id', $qry)));
        $l = $this->sumBetween($fuelQ(), 'log_date', 'qty_litres', $cf, $ct);
        $lP = $this->sumBetween($fuelQ(), 'log_date', 'qty_litres', $pf, $pt);
        $c = $this->sumBetween($fuelQ(), 'log_date', 'amount', $cf, $ct);
        $cP = $this->sumBetween($fuelQ(), 'log_date', 'amount', $pf, $pt);
        $n = (clone $fuelQ())->whereBetween('log_date', [$cf, $ct])->count();
        $nP = (clone $fuelQ())->whereBetween('log_date', [$pf, $pt])->count();
        $todayL = (float) (clone $fuelQ())->whereDate('log_date', $today)->sum('qty_litres');
        $rate = $l > 0 ? round($c / $l, 2) : 0;
        $rateP = $lP > 0 ? round($cP / $lP, 2) : 0;
        $equipN = (clone $fuelQ())->whereBetween('log_date', [$cf, $ct])->distinct('equipment_id')->count('equipment_id');

        $fuelItems = StockItem::where('is_active', true)->where('category', 'fuel-lubricant')->get();
        $dsl = (float) ($fuelItems->firstWhere('code', 'DSL')?->current_qty ?? 0);
        $ptr = (float) ($fuelItems->firstWhere('code', 'PTR')?->current_qty ?? 0);
        $lube = (float) $fuelItems->whereNotIn('code', ['DSL', 'PTR'])->sum('current_qty');
        $bayVal = (float) $fuelItems->sum(fn($i) => $i->current_qty * $i->avg_cost_rate);

        $t30 = Carbon::today()->subDays(29)->format('Y-m-d');
        $l30 = $this->sumBetween($fuelQ(), 'log_date', 'qty_litres', $t30, $today);
        $cover = $l30 > 0 ? round($dsl / ($l30 / 30), 1) : 0;
        $burn = $this->fleetBurn($qry, $cf, $ct);

        $cards = [
            $this->card('Diesel Issued (' . $r->get('period', 'This Month') . ')', round($l, 1), 'L', round($lP, 1), $this->spark('fuel_logs', 'log_date', 'qty_litres', $es, $grain), ['good' => 'down']),
            $this->card('Diesel Cost', round($c), 'GHS', round($cP), [], ['good' => 'down']),
            $this->card('Fuel Issues', $n, '#', $nP),
            $this->card('Issued Today', round($todayL, 1), 'L', round($todayL, 1)),
            $this->card('Avg Rate', $rate, 'GHS', $rateP, [], ['good' => 'down', 'info' => 'per litre']),
            $this->card('Diesel Stock (Fuel Bay)', round($dsl), 'L', round($dsl), [], ['st' => 'blue']),
            $this->card('Days of Cover', $cover, 'num', $cover, [], ['good' => 'up', 'info' => 'at 30-day burn rate']),
            $this->card('Petrol Stock', round($ptr), 'L', round($ptr), [], ['st' => 'blue']),
            $this->card('Lubricants Stock', round($lube), 'L', round($lube), [], ['st' => 'blue']),
            $this->card('Fuel Bay Value', round($bayVal), 'GHS', round($bayVal), [], ['st' => 'blue']),
            $this->card('Equipment Fuelled', $equipN, '#', $equipN, [], ['st' => 'blue']),
            $this->card('Fleet Burn Rate', $burn, 'num', $burn, [], ['good' => 'down', 'info' => 'litres per meter-hour']),
        ];

        return response()->json([
            'sub' => 'Diesel issues · burn rates · fuel bay stock · ' . $r->get('period', 'This Month'),
            'asAt' => Carbon::today()->format('d M Y'),
            'cards' => $cards,
            'sections' => [
                $this->secTrend($es),
                $this->secRate($qry),
                $this->secByEquip($qry, $cf, $ct),
                $this->secBurn($qry, $cf, $ct),
                $this->secLog($qry),
                $this->secStock(),
                $this->secEquip($qry, $cf, $ct),
            ],
        ]);
    }

    /** Fleet-wide litres per meter-hour across hour-metered equipment in window. */
    private function fleetBurn($qry, $f, $t): float
    {
        $rows = FuelLog::when($qry !== 'all', fn($q) => $q->whereHas('equipment', fn($w) => $w->where('current_quarry_id', $qry)))
            ->whereBetween('log_date', [$f, $t])->whereNotNull('meter_hours')->get(['equipment_id', 'qty_litres', 'meter_hours']);
        $byEq = $rows->groupBy('equipment_id');
        $l = 0;
        $h = 0;
        foreach ($byEq as $g) {
            $span = $g->max('meter_hours') - $g->min('meter_hours');
            if ($span > 0) {
                $l += $g->sum('qty_litres');
                $h += $span;
            }
        }
        return $h > 0 ? round($l / $h, 2) : 0;
    }

    private function secTrend($es): array
    {
        $data = $this->daily('fuel_logs', 'log_date', 'qty_litres', $es, 30);
        return ['k' => 'chart', 'id' => 'fu_trend', 'ti' => 'Diesel Issued — litres per day (30 days)', 'cls' => 'wide', 'spec' => [
            'type' => 'line', 'fmt' => 'L', 'zoom' => true, 'height' => 280,
            'labels' => $this->dailyLabels(30),
            'series' => [['name' => 'Litres', 'data' => $data, 'color' => '#f97316', 'area' => true]],
            'target' => ['value' => count($data) ? round(array_sum($data) / count($data), 1) : 0, 'label' => '30-day avg'],
        ]];
    }

    private function secRate($qry): array
    {
        $t = Carbon::today();
        $data = [];
        for ($i = 29; $i >= 0; $i--) {
            $d = $t->copy()->subDays($i)->format('Y-m-d');
            $row = FuelLog::when($qry !== 'all', fn($q) => $q->whereHas('equipment', fn($w) => $w->where('current_quarry_id', $qry)))
                ->whereDate('log_date', $d)->selectRaw('sum(qty_litres) l, sum(amount) a')->first();
            $data[] = $row->l > 0 ? round($row->a / $row->l, 2) : 0;
        }
        $nz = array_filter($data);
        return ['k' => 'chart', 'id' => 'fu_rate', 'ti' => 'Effective Rate — GHS per litre (30 days)', 'cls' => 'wide', 'spec' => [
            'type' => 'line', 'fmt' => 'num', 'zoom' => true, 'height' => 260,
            'labels' => $this->dailyLabels(30),
            'series' => [['name' => 'GHS/L', 'data' => $data, 'color' => '#3b82f6', 'area' => true]],
            'target' => ['value' => count($nz) ? round(array_sum($nz) / count($nz), 2) : 0, 'label' => 'avg'],
        ]];
    }

    private function secByEquip($qry, $cf, $ct): array
    {
        $rows = FuelLog::when($qry !== 'all', fn($q) => $q->whereHas('equipment', fn($w) => $w->where('current_quarry_id', $qry)))
            ->whereBetween('log_date', [$cf, $ct])
            ->select('equipment_id', DB::raw('sum(qty_litres) t'))->groupBy('equipment_id')->orderByDesc('t')->take(8)->get();
        $names = Equipment::whereIn('id', $rows->pluck('equipment_id'))->pluck('code', 'id');
        return ['k' => 'chart', 'id' => 'fu_byequip', 'ti' => 'Top Consumers — litres (period)', 'spec' => [
            'type' => 'hbar', 'fmt' => 'L',
            'labels' => $rows->map(fn($r) => $names[$r->equipment_id] ?? ('#' . $r->equipment_id))->all(),
            'series' => [['name' => 'Litres', 'data' => $rows->map(fn($r) => round((float) $r->t, 1))->all(), 'color' => '#f97316']],
        ]];
    }

    private function burnRows($qry, $cf, $ct): array
    {
        $rows = FuelLog::when($qry !== 'all', fn($q) => $q->whereHas('equipment', fn($w) => $w->where('current_quarry_id', $qry)))
            ->whereBetween('log_date', [$cf, $ct])->get();
        $out = [];
        foreach ($rows->groupBy('equipment_id') as $id => $g) {
            $h = $g->whereNotNull('meter_hours');
            $k = $g->whereNotNull('meter_km');
            if ($h->count() >= 2 && ($span = $h->max('meter_hours') - $h->min('meter_hours')) > 0) {
                $out[] = ['id' => $id, 'burn' => round($h->sum('qty_litres') / $span, 2), 'u' => 'L/hr'];
            } elseif ($k->count() >= 2 && ($span = $k->max('meter_km') - $k->min('meter_km')) > 0) {
                $out[] = ['id' => $id, 'burn' => round($k->sum('qty_litres') / $span, 2), 'u' => 'L/km'];
            }
        }
        return $out;
    }

    private function secBurn($qry, $cf, $ct): array
    {
        $rows = collect($this->burnRows($qry, $cf, $ct))->sortByDesc('burn')->take(8);
        $names = Equipment::whereIn('id', $rows->pluck('id'))->pluck('code', 'id');
        return ['k' => 'chart', 'id' => 'fu_burn', 'ti' => 'Burn Rate by Equipment — L per meter unit (period)', 'spec' => [
            'type' => 'hbar', 'fmt' => 'num',
            'labels' => $rows->map(fn($r) => ($names[$r['id']] ?? '#') . ' (' . $r['u'] . ')')->values()->all(),
            'series' => [['name' => 'Burn', 'data' => $rows->map(fn($r) => $r['burn'])->values()->all(), 'color' => '#ef4444']],
        ]];
    }

    private function secLog($qry): array
    {
        $rows = FuelLog::with(['equipment', 'warehouse'])
            ->when($qry !== 'all', fn($q) => $q->whereHas('equipment', fn($w) => $w->where('current_quarry_id', $qry)))
            ->orderByDesc('log_date')->orderByDesc('id')->take(60)->get()
            ->map(fn($f) => ['no' => $f->log_no, 'date' => substr((string) $f->log_date, 0, 10),
                'equip' => $f->equipment->code ?? '', 'wh' => $f->warehouse->code ?? '',
                'qty' => round((float) $f->qty_litres, 1), 'rate' => round((float) $f->rate_per_litre, 2),
                'amt' => round((float) $f->amount), 'meter' => $f->meter_hours ?? $f->meter_km ?? '',
                'by' => $f->issued_by ?? '', 'to' => $f->received_by ?? ''])->all();
        return ['k' => 'table', 'id' => 'fu_log', 'ti' => 'Fuel Issue Log — latest 60', 'cls' => 'wide', 'cfg' => [
            'id' => 'fu_log', 'page' => 12, 'search' => true,
            'cols' => [
                ['k' => 'no', 'l' => 'Issue'], ['k' => 'date', 'l' => 'Date'], ['k' => 'equip', 'l' => 'Equipment'],
                ['k' => 'wh', 'l' => 'Bay'], ['k' => 'qty', 'l' => 'Litres', 'y' => 'num'],
                ['k' => 'rate', 'l' => 'Rate', 'y' => 'num'], ['k' => 'amt', 'l' => 'Amount', 'y' => 'num'],
                ['k' => 'meter', 'l' => 'Meter'], ['k' => 'by', 'l' => 'Issued by'], ['k' => 'to', 'l' => 'Received by'],
            ], 'rows' => $rows, 'totals' => ['qty', 'amt'],
        ]];
    }

    private function secStock(): array
    {
        $rows = StockItem::with('unit')->where('is_active', true)->where('category', 'fuel-lubricant')->orderBy('code')->get()
            ->map(function ($i) {
                $st = $i->current_qty <= 0 ? 'red' : ($i->current_qty < $i->reorder_level ? 'red' : 'green');
                $stL = $i->current_qty <= 0 ? 'Stockout' : ($i->current_qty < $i->reorder_level ? 'Reorder' : 'OK');
                return ['code' => $i->code, 'name' => $i->name, 'unit' => $i->unit->symbol ?? '',
                    'onhand' => round((float) $i->current_qty, 1), 're' => round((float) $i->reorder_level, 1),
                    'cost' => round((float) $i->avg_cost_rate, 2), 'val' => round($i->current_qty * $i->avg_cost_rate),
                    'st' => $st, 'stL' => $stL];
            })->all();
        return ['k' => 'table', 'id' => 'fu_stock', 'ti' => 'Fuel Bay Stock — live balances', 'cls' => 'wide', 'cfg' => [
            'id' => 'fu_stock', 'page' => 10, 'search' => true,
            'cols' => [
                ['k' => 'code', 'l' => 'Code'], ['k' => 'name', 'l' => 'Fuel', 'wide' => 1], ['k' => 'unit', 'l' => 'UoM'],
                ['k' => 'onhand', 'l' => 'On hand', 'y' => 'num'], ['k' => 're', 'l' => 'Reorder', 'y' => 'num'],
                ['k' => 'cost', 'l' => 'Avg cost', 'y' => 'num'], ['k' => 'val', 'l' => 'Value', 'y' => 'num'],
                ['k' => 'st', 'l' => 'Status', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => ['val'],
        ]];
    }

    private function secEquip($qry, $cf, $ct): array
    {
        $burns = collect($this->burnRows($qry, $cf, $ct))->mapWithKeys(fn($r) => [$r['id'] => $r]);
        $med = $burns->count() ? $burns->median('burn') : 0;
        $ids = FuelLog::when($qry !== 'all', fn($q) => $q->whereHas('equipment', fn($w) => $w->where('current_quarry_id', $qry)))
            ->whereBetween('log_date', [$cf, $ct])->distinct('equipment_id')->pluck('equipment_id');
        $rows = Equipment::whereIn('id', $ids)->orderBy('code')->get()->map(function ($e) use ($cf, $ct, $burns, $med) {
            $agg = FuelLog::where('equipment_id', $e->id)->whereBetween('log_date', [$cf, $ct])
                ->selectRaw('count(*) n, sum(qty_litres) l, sum(amount) a')->first();
            $b = $burns[$e->id] ?? null;
            return ['equip' => $e->code, 'name' => $e->name, 'n' => (int) $agg->n,
                'litres' => round((float) $agg->l, 1), 'cost' => round((float) $agg->a),
                'burn' => $b ? $b['burn'] . ' ' . $b['u'] : '—',
                'st' => !$b ? 'grey' : ($b['burn'] > $med ? 'amber' : 'green'),
                'stL' => !$b ? 'No span' : ($b['burn'] > $med ? 'Above median' : 'Below median')];
        })->all();
        return ['k' => 'table', 'id' => 'fu_equip', 'ti' => 'Equipment Fuel Scorecard — consumption & burn (period)', 'cls' => 'wide', 'cfg' => [
            'id' => 'fu_equip', 'page' => 12, 'search' => true,
            'cols' => [
                ['k' => 'equip', 'l' => 'Equipment'], ['k' => 'name', 'l' => 'Name', 'wide' => 1],
                ['k' => 'n', 'l' => 'Fills', 'y' => 'num'], ['k' => 'litres', 'l' => 'Litres', 'y' => 'num'],
                ['k' => 'cost', 'l' => 'Cost', 'y' => 'num'], ['k' => 'burn', 'l' => 'Burn rate'],
                ['k' => 'st', 'l' => 'vs Median', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => ['litres', 'cost'],
        ]];
    }
}
