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

use App\Http\Controllers\Controller;
use App\Models\Product;
use App\Models\Stockpile;
use App\Models\StockpileMovement;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

/** Live data for the Command Centre — Stockpile Management module. */
class StockpileController extends Controller
{
    use Concerns\DashboardHelpers;

    public function show(Request $r)
    {
        $this->need('inventory.view');
        $qry = $r->get('qry', 'all');
        [$cf, $ct, $pf, $pt, $grain] = $this->windows((string) $r->get('period', 'This Month'));
        $pileIds = $this->pileIds($qry);

        $inScope = fn() => StockpileMovement::when($qry !== 'all', fn($q) => $q->whereIn('stockpile_id', $pileIds));
        $in = $this->sumBetween($inScope(), 'movement_date', 'qty_in', $cf, $ct);
        $inP = $this->sumBetween($inScope(), 'movement_date', 'qty_in', $pf, $pt);
        $out = $this->sumBetween($inScope(), 'movement_date', 'qty_out', $cf, $ct);
        $outP = $this->sumBetween($inScope(), 'movement_date', 'qty_out', $pf, $pt);
        $moves = (clone $inScope())->whereBetween('movement_date', [$cf, $ct])->count();
        $movesP = (clone $inScope())->whereBetween('movement_date', [$pf, $pt])->count();

        $stock = (float) Stockpile::when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))->sum('current_qty');
        $cap = (float) Stockpile::when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))->sum('capacity_tonnes');
        $opening = $stock - ($in - $out);
        $fill = $cap > 0 ? round($stock / $cap * 100, 1) : 0;
        $net = $in - $out;
        $netP = $inP - $outP;

        $t30 = Carbon::today()->subDays(29)->format('Y-m-d');
        $out30 = $this->sumBetween($inScope(), 'movement_date', 'qty_out', $t30, Carbon::today()->format('Y-m-d'));
        $cover = $out30 > 0 ? round($stock / ($out30 / 30), 1) : 0;

        $piles = Stockpile::when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))->get();
        $low = $piles->filter(fn($p) => $p->capacity_tonnes > 0 && $p->current_qty / $p->capacity_tonnes < 0.2)->count();
        $full = $piles->filter(fn($p) => $p->capacity_tonnes > 0 && $p->current_qty / $p->capacity_tonnes > 0.95)->count();
        $prods = $piles->pluck('product_id')->unique()->count();

        $cards = [
            $this->card('Stock on Hand', round($stock, 1), 't', round($opening, 1), [], ['info' => 'live pile balances']),
            $this->card('Rated Bay Capacity', round($cap, 1), 't', round($cap, 1), [], ['st' => 'blue']),
            $this->card('Overall Fill', $fill, '%', $fill, [], ['info' => 'stock vs rated capacity']),
            $this->card('Stock In (' . $r->get('period', 'This Month') . ')', round($in, 1), 't', round($inP, 1), $this->spark('stockpile_movements', 'movement_date', 'qty_in', $this->lw($qry), $grain)),
            $this->card('Stock Out (' . $r->get('period', 'This Month') . ')', round($out, 1), 't', round($outP, 1), $this->spark('stockpile_movements', 'movement_date', 'qty_out', $this->lw($qry), $grain)),
            $this->card('Net Change', round($net, 1), 't', round($netP, 1)),
            $this->card('Days of Cover', $cover, 'num', $cover, [], ['good' => 'up', 'info' => 'at 30-day avg dispatch rate']),
            $this->card('Piles Below 20%', $low, '#', $low, [], ['good' => 'down', 'st' => $low > 0 ? 'amber' : 'green']),
            $this->card('Piles Above 95%', $full, '#', $full, [], ['good' => 'down', 'st' => $full > 0 ? 'amber' : 'green']),
            $this->card('Active Piles', $piles->count(), '#', $piles->count(), [], ['st' => 'blue']),
            $this->card('Products Stocked', $prods, '#', $prods, [], ['st' => 'blue']),
            $this->card('Movements (' . $r->get('period', 'This Month') . ')', $moves, '#', $movesP),
        ];

        return response()->json([
            'sub' => 'Pile balances · inflows · dispatches · cover · ' . $r->get('period', 'This Month'),
            'asAt' => Carbon::today()->format('d M Y'),
            'cards' => $cards,
            'sections' => [
                $this->secFlows($qry),
                $this->secLevel($qry, $stock),
                $this->secFill($qry),
                $this->secByProduct($qry),
                $this->secPiles($qry),
                $this->secMoves($qry),
            ],
        ]);
    }

    private function pileIds($qry): array
    {
        return Stockpile::when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))->pluck('id')->all();
    }

    private function lw($qry): string
    {
        $ids = $this->pileIds($qry);
        return $qry !== 'all' ? 'stockpile_id in (' . implode(',', $ids ?: [0]) . ')' : '1=1';
    }

    private function secFlows($qry): array
    {
        $lw = $this->lw($qry);
        return ['k' => 'chart', 'id' => 's_flows', 'ti' => 'Stock In vs Stock Out — tonnes per day (30 days)', 'cls' => 'wide', 'spec' => [
            'type' => 'line', 'fmt' => 't', 'zoom' => true, 'height' => 280,
            'labels' => $this->dailyLabels(30),
            'series' => [
                ['name' => 'In', 'data' => $this->daily('stockpile_movements', 'movement_date', 'qty_in', $lw, 30), 'color' => '#22c55e', 'area' => true],
                ['name' => 'Out', 'data' => $this->daily('stockpile_movements', 'movement_date', 'qty_out', $lw, 30), 'color' => '#f97316'],
            ],
        ]];
    }

    private function secLevel($qry, $stock): array
    {
        $lw = $this->lw($qry);
        $in = $this->daily('stockpile_movements', 'movement_date', 'qty_in', $lw, 30);
        $out = $this->daily('stockpile_movements', 'movement_date', 'qty_out', $lw, 30);
        $net30 = array_sum($in) - array_sum($out);
        $bal = $stock - $net30;
        $data = [];
        for ($i = 0; $i < 30; $i++) {
            $bal += $in[$i] - $out[$i];
            $data[] = round(max($bal, 0), 1);
        }
        return ['k' => 'chart', 'id' => 's_level', 'ti' => 'Total Stock on Hand — closing tonnes per day (30 days)', 'cls' => 'wide', 'spec' => [
            'type' => 'line', 'fmt' => 't', 'zoom' => true, 'height' => 260,
            'labels' => $this->dailyLabels(30),
            'series' => [['name' => 'On hand', 'data' => $data, 'color' => '#f8c200', 'area' => true]],
            'target' => ['value' => count($data) ? round(array_sum($data) / count($data), 1) : 0, 'label' => '30-day avg'],
        ]];
    }

    private function secFill($qry): array
    {
        $piles = Stockpile::when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))->orderBy('code')->get();
        $labels = [];
        $vals = [];
        foreach ($piles as $p) {
            $labels[] = $p->code;
            $vals[] = $p->capacity_tonnes > 0 ? round($p->current_qty / $p->capacity_tonnes * 100, 1) : 0;
        }
        return ['k' => 'chart', 'id' => 's_fill', 'ti' => 'Fill Level by Pile — % of rated capacity', 'spec' => [
            'type' => 'hbar', 'fmt' => 'pct', 'min' => 0, 'max' => 100,
            'labels' => $labels,
            'series' => [['name' => '%', 'data' => $vals, 'color' => '#3b82f6']],
        ]];
    }

    private function secByProduct($qry): array
    {
        $rows = Stockpile::when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))
            ->select('product_id', DB::raw('sum(current_qty) 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' => 's_byprod', 'ti' => 'Stock on Hand by Product — tonnes (live)', 'spec' => [
            'type' => 'doughnut', 'fmt' => 't', 'center' => 'On hand', 'items' => $items,
        ]];
    }

    private function secPiles($qry): array
    {
        $rows = Stockpile::with(['quarry', 'product'])
            ->when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))->orderBy('code')->get()
            ->map(function ($p) {
                $fill = $p->capacity_tonnes > 0 ? round($p->current_qty / $p->capacity_tonnes * 100, 1) : 0;
                $st = $fill < 20 ? 'red' : ($fill > 95 ? 'amber' : 'green');
                $stL = $fill < 20 ? 'Low' : ($fill > 95 ? 'Full' : 'OK');
                return ['code' => $p->code, 'loc' => $p->location, 'quarry' => $p->quarry->name ?? '',
                    'product' => $p->product->name ?? '', 'cap' => round((float) $p->capacity_tonnes, 1),
                    'onhand' => round((float) $p->current_qty, 1), 'fill' => $fill . '%',
                    'st' => $st, 'stL' => $stL];
            })->all();
        return ['k' => 'table', 'id' => 's_piles', 'ti' => 'Stockpiles — live balances & fill', 'cls' => 'wide', 'cfg' => [
            'id' => 's_piles', 'page' => 12, 'search' => true,
            'cols' => [
                ['k' => 'code', 'l' => 'Pile'], ['k' => 'loc', 'l' => 'Location'], ['k' => 'quarry', 'l' => 'Quarry'],
                ['k' => 'product', 'l' => 'Product', 'wide' => 1], ['k' => 'cap', 'l' => 'Capacity (t)', 'y' => 'num'],
                ['k' => 'onhand', 'l' => 'On hand (t)', 'y' => 'num'], ['k' => 'fill', 'l' => 'Fill'],
                ['k' => 'st', 'l' => 'Status', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => ['cap', 'onhand'],
        ]];
    }

    private function secMoves($qry): array
    {
        $pileIds = $this->pileIds($qry);
        $rows = StockpileMovement::with('stockpile.product')
            ->when($qry !== 'all', fn($q) => $q->whereIn('stockpile_id', $pileIds))
            ->orderByDesc('movement_date')->orderByDesc('id')->take(60)->get()
            ->map(fn($m) => ['date' => substr((string) $m->movement_date, 0, 10),
                'pile' => $m->stockpile->code ?? '', 'product' => $m->stockpile->product->name ?? '',
                'type' => ucwords(str_replace('-', ' ', (string) $m->movement_type)),
                'st' => str_ends_with((string) $m->movement_type, '-in') ? 'green' : 'amber',
                'stL' => ucwords(str_replace('-', ' ', (string) $m->movement_type)),
                'ref' => trim(($m->ref_type ?? '') . ' ' . ($m->ref_id ?? '')),
                'in' => round((float) $m->qty_in, 1), 'out' => round((float) $m->qty_out, 1),
                'bal' => round((float) $m->balance_after, 1), 'rem' => $m->remarks ?? ''])->all();
        return ['k' => 'table', 'id' => 's_moves', 'ti' => 'Stock Movements — latest 60', 'cls' => 'wide', 'cfg' => [
            'id' => 's_moves', 'page' => 12, 'search' => true,
            'cols' => [
                ['k' => 'date', 'l' => 'Date'], ['k' => 'pile', 'l' => 'Pile'], ['k' => 'product', 'l' => 'Product', 'wide' => 1],
                ['k' => 'st', 'l' => 'Type', 'y' => 'badge'], ['k' => 'ref', 'l' => 'Reference'],
                ['k' => 'in', 'l' => 'In (t)', 'y' => 'num'], ['k' => 'out', 'l' => 'Out (t)', 'y' => 'num'],
                ['k' => 'bal', 'l' => 'Balance (t)', 'y' => 'num'], ['k' => 'rem', 'l' => 'Remarks'],
            ], 'rows' => $rows, 'totals' => ['in', 'out'],
        ]];
    }
}
