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

use App\Http\Controllers\Controller;
use App\Models\BlastLog;
use App\Models\DrillLog;
use App\Models\ExplosivesRegister;
use App\Models\Quarry;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

/** Live data for the Command Centre — Drilling & Blasting module. */
class DrillController extends Controller
{
    use Concerns\DashboardHelpers;

    public function show(Request $r)
    {
        $this->need('production.view');
        $qry = $r->get('qry', 'all');
        $qScope = fn($q) => $qry !== 'all' ? $q->where('quarry_id', $qry) : $q;
        [$cf, $ct, $pf, $pt, $grain] = $this->windows((string) $r->get('period', 'This Month'));
        $qw = $qry !== 'all' ? ' and quarry_id=' . (int) $qry : '';

        $fired = fn() => $qScope(BlastLog::where('status', 'fired'));
        $n = (clone $fired())->whereBetween('blast_date', [$cf, $ct])->count();
        $nP = (clone $fired())->whereBetween('blast_date', [$pf, $pt])->count();
        $ton = $this->sumBetween($fired(), 'blast_date', 'tonnage_broken', $cf, $ct);
        $tonP = $this->sumBetween($fired(), 'blast_date', 'tonnage_broken', $pf, $pt);
        $vol = $this->sumBetween($fired(), 'blast_date', 'volume_broken_m3', $cf, $ct);
        $volP = $this->sumBetween($fired(), 'blast_date', 'volume_broken_m3', $pf, $pt);
        $holes = $this->sumBetween($fired(), 'blast_date', 'holes_drilled', $cf, $ct);
        $holesP = $this->sumBetween($fired(), 'blast_date', 'holes_drilled', $pf, $pt);
        $depth = $this->sumBetween($fired(), 'blast_date', 'total_depth_m', $cf, $ct);
        $depthP = $this->sumBetween($fired(), 'blast_date', 'total_depth_m', $pf, $pt);
        $det = $this->sumBetween($fired(), 'blast_date', 'detonators_used', $cf, $ct);
        $detP = $this->sumBetween($fired(), 'blast_date', 'detonators_used', $pf, $pt);

        $issued = $this->sumBetween($qScope(ExplosivesRegister::query()), 'register_date', 'issued_qty', $cf, $ct);
        $issuedP = $this->sumBetween($qScope(ExplosivesRegister::query()), 'register_date', 'issued_qty', $pf, $pt);
        $recvd = $this->sumBetween($qScope(ExplosivesRegister::query()), 'register_date', 'received_qty', $cf, $ct);
        $recvdP = $this->sumBetween($qScope(ExplosivesRegister::query()), 'register_date', 'received_qty', $pf, $pt);

        $pfNow = $ton > 0 ? round($issued / $ton, 2) : 0;
        $pfPrev = $tonP > 0 ? round($issuedP / $tonP, 2) : 0;
        $avgTon = $n > 0 ? round($ton / $n) : 0;
        $avgTonP = $nP > 0 ? round($tonP / $nP) : 0;
        $pending = $qScope(BlastLog::where('status', 'approved'))->count();
        $stock = $this->stockOnHand($qry);
        $meters = $this->sumBetween($qScope(DrillLog::query()), 'drill_date', 'total_meters', $cf, $ct);
        $metersP = $this->sumBetween($qScope(DrillLog::query()), 'drill_date', 'total_meters', $pf, $pt);
        $dholes = $this->sumBetween($qScope(DrillLog::query()), 'drill_date', 'holes_drilled', $cf, $ct);
        $dholesP = $this->sumBetween($qScope(DrillLog::query()), 'drill_date', 'holes_drilled', $pf, $pt);

        $cards = [
            $this->card('Blasts Fired', $n, '#', $nP),
            $this->card('Tonnage Broken', round($ton, 1), 't', round($tonP, 1), $this->spark('blast_logs', 'blast_date', 'tonnage_broken', "status='fired'$qw", $grain)),
            $this->card('Volume Broken', round($vol, 1), 'num', round($volP, 1), [], ['info' => 'm³']),
            $this->card('Holes Drilled', round($holes), '#', round($holesP)),
            $this->card('Total Depth Drilled', round($depth, 1), 'num', round($depthP, 1), [], ['info' => 'metres']),
            $this->card('Explosives Issued', round($issued, 1), 'num', round($issuedP, 1), $this->spark('explosives_registers', 'register_date', 'issued_qty', "1=1$qw", $grain), ['good' => 'down', 'info' => 'kg']),
            $this->card('Detonators Used', round($det), '#', round($detP), [], ['good' => 'down']),
            $this->card('Powder Factor', $pfNow, 'num', $pfPrev, [], ['good' => 'down', 'info' => 'kg per tonne']),
            $this->card('Explosives Stock on Hand', round($stock, 1), 'num', round($stock, 1), [], ['st' => 'blue', 'info' => 'kg across magazines']),
            $this->card('Explosives Received', round($recvd, 1), 'num', round($recvdP, 1), [], ['info' => 'kg in period']),
            $this->card('Avg Tonnage per Blast', $avgTon, 't', $avgTonP),
            $this->card('Blasts Awaiting Firing', $pending, '#', $pending, [], ['good' => 'down', 'st' => $pending === 0 ? 'green' : 'amber']),
            $this->card('Meters Drilled', round($meters, 1), 'num', round($metersP, 1), $this->spark('drill_logs', 'drill_date', 'total_meters', "1=1$qw", $grain), ['info' => 'metres']),
            $this->card('Drill Holes', round($dholes), '#', round($dholesP)),
        ];

        return response()->json([
            'sub' => 'Blast design · firing · powder factor · magazine control · ' . $r->get('period', 'This Month'),
            'asAt' => Carbon::today()->format('d M Y'),
            'cards' => $cards,
            'sections' => [
                $this->secTrend($qry),
                $this->secPowder($qry),
                $this->secByType($qry, $cf, $ct),
                $this->secStock($qry),
                $this->secDrill($qry),
                $this->secBlasts($qry),
                $this->secRegister($qry),
            ],
        ]);
    }

    /** Latest closing balance per quarry/magazine/type summed. */
    private function stockOnHand($qry): float
    {
        $ids = ExplosivesRegister::when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))
            ->select(DB::raw('max(id) id'))->groupBy('quarry_id', 'magazine', 'explosive_type')->pluck('id');
        return (float) ExplosivesRegister::whereIn('id', $ids)->sum('closing_balance');
    }

    private function secTrend($qry): array
    {
        $pal = $this->palette();
        $series = [];
        $qs = Quarry::where('is_active', true)->when($qry !== 'all', fn($q) => $q->where('id', $qry))->orderBy('name')->get()->values();
        foreach ($qs as $i => $q) {
            $series[] = ['name' => $q->name, 'data' => $this->daily('blast_logs', 'blast_date', 'tonnage_broken', "status='fired' and quarry_id=" . (int) $q->id, 30), 'color' => $pal[$i % count($pal)], 'area' => $i === 0];
        }
        return ['k' => 'chart', 'id' => 'd_tonnage', 'ti' => 'Tonnage Broken by Quarry — tonnes per day (30 days)', 'cls' => 'wide', 'spec' => [
            'type' => 'line', 'fmt' => 't', 'zoom' => true, 'height' => 280,
            'labels' => $this->dailyLabels(30), 'series' => $series,
        ]];
    }

    private function secPowder($qry): array
    {
        $blasts = BlastLog::when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))
            ->orderByDesc('blast_date')->take(12)->get()->reverse()->values();
        $labels = [];
        $data = [];
        foreach ($blasts as $b) {
            $labels[] = $b->blast_no;
            $kg = ExplosivesRegister::where('blast_log_id', $b->id)->sum('issued_qty');
            if (!$kg) $kg = (float) $b->explosive_used_kg;
            $data[] = $b->tonnage_broken > 0 ? round($kg / $b->tonnage_broken, 2) : 0;
        }
        return ['k' => 'chart', 'id' => 'd_pf', 'ti' => 'Powder Factor per Blast — kg per tonne (last 12)', 'spec' => [
            'type' => 'line', 'fmt' => 'num',
            'labels' => $labels,
            'series' => [['name' => 'kg/t', 'data' => $data, 'color' => '#f97316', 'area' => true]],
        ]];
    }

    private function secByType($qry, $cf, $ct): array
    {
        $rows = ExplosivesRegister::when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))
            ->whereBetween('register_date', [$cf, $ct])
            ->select('explosive_type', DB::raw('sum(issued_qty) t'))->groupBy('explosive_type')->orderByDesc('t')->get();
        $pal = $this->palette();
        $items = $rows->values()->map(fn($r, $i) => ['label' => $r->explosive_type, 'value' => round((float) $r->t, 1), 'color' => $pal[$i % count($pal)]])->all();
        return ['k' => 'chart', 'id' => 'd_bytype', 'ti' => 'Explosives Issued by Type — kg (period)', 'spec' => [
            'type' => 'doughnut', 'fmt' => 'num', 'center' => 'kg', 'items' => $items,
        ]];
    }

    private function secStock($qry): array
    {
        $ids = ExplosivesRegister::when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))
            ->select(DB::raw('max(id) id'))->groupBy('quarry_id', 'magazine', 'explosive_type')->pluck('id');
        $rows = ExplosivesRegister::with('quarry')->whereIn('id', $ids)->orderByDesc('closing_balance')->get();
        $pal = $this->palette();
        return ['k' => 'chart', 'id' => 'd_stock', 'ti' => 'Magazine Stock on Hand — kg by store & type', 'spec' => [
            'type' => 'hbar', 'fmt' => 'num',
            'labels' => $rows->map(fn($r) => mb_strimwidth(($r->quarry->name ?? '') . ' · ' . $r->magazine . ' · ' . $r->explosive_type, 0, 42, '…'))->all(),
            'series' => [['name' => 'kg', 'data' => $rows->map(fn($r) => round((float) $r->closing_balance, 1))->all(), 'color' => '#a855f7']],
        ]];
    }

    private function secDrill($qry): array
    {
        $rows = DrillLog::with(['quarry', 'bench', 'rig'])->when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))
            ->orderByDesc('drill_date')->take(60)->get()
            ->map(fn($d) => [
                'no' => $d->drill_no, 'date' => $d->drill_date, 'quarry' => $d->quarry->name ?? '',
                'bench' => $d->bench->code ?? '', 'rig' => $d->rig->code ?? '', 'pattern' => ucfirst((string) $d->pattern),
                'holes' => (int) $d->holes_drilled, 'depth' => round((float) $d->depth_per_hole_m, 1),
                'meters' => round((float) $d->total_meters, 1), 'op' => $d->operator_name,
                'st' => $d->status === 'completed' ? 'green' : 'blue', 'stL' => ucfirst((string) $d->status),
                'openUrl' => '/app/drill-logs/' . $d->id,
            ])->all();
        return ['k' => 'table', 'id' => 'd_drill', 'ti' => 'Drilling Register — latest 60 (click a row for detail)', 'cls' => 'wide', 'cfg' => [
            'id' => 'd_drill', 'page' => 12, 'search' => true,
            'cols' => [
                ['k' => 'no', 'l' => 'Drill'], ['k' => 'date', 'l' => 'Date'], ['k' => 'quarry', 'l' => 'Quarry'],
                ['k' => 'bench', 'l' => 'Bench'], ['k' => 'rig', 'l' => 'Rig'], ['k' => 'pattern', 'l' => 'Pattern'],
                ['k' => 'holes', 'l' => 'Holes', 'y' => 'num'], ['k' => 'depth', 'l' => 'm/hole', 'y' => 'num'],
                ['k' => 'meters', 'l' => 'Meters', 'y' => 'num'], ['k' => 'op', 'l' => 'Operator'],
                ['k' => 'st', 'l' => 'Status', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => ['holes', 'meters'],
        ]];
    }

    private function secBlasts($qry): array
    {
        $rows = BlastLog::with(['quarry', 'bench'])->when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))
            ->orderByDesc('blast_date')->take(60)->get()
            ->map(fn($b) => [
                'no' => $b->blast_no, 'date' => $b->blast_date, 'quarry' => $b->quarry->name ?? '',
                'bench' => $b->bench->code ?? '', 'holes' => (int) $b->holes_drilled,
                'depth' => round((float) $b->total_depth_m, 1), 'expl' => round((float) $b->explosive_used_kg, 1),
                'det' => (int) $b->detonators_used, 'vol' => round((float) $b->volume_broken_m3, 1),
                'tonnes' => round((float) $b->tonnage_broken, 1), 'blaster' => $b->blaster_name,
                'st' => $b->status === 'fired' ? 'green' : 'blue', 'stL' => ucfirst((string) $b->status),
            ])->all();
        return ['k' => 'table', 'id' => 'd_blasts', 'ti' => 'Blast Register — latest 60 (click a row for detail)', 'cls' => 'wide', 'cfg' => [
            'id' => 'd_blasts', 'page' => 12, 'search' => true,
            'cols' => [
                ['k' => 'no', 'l' => 'Blast'], ['k' => 'date', 'l' => 'Date'], ['k' => 'quarry', 'l' => 'Quarry'],
                ['k' => 'bench', 'l' => 'Bench'], ['k' => 'holes', 'l' => 'Holes', 'y' => 'num'],
                ['k' => 'depth', 'l' => 'Depth (m)', 'y' => 'num'], ['k' => 'expl', 'l' => 'Expl (kg)', 'y' => 'num'],
                ['k' => 'det', 'l' => 'Deton.', 'y' => 'num'], ['k' => 'vol', 'l' => 'Vol (m³)', 'y' => 'num'],
                ['k' => 'tonnes', 'l' => 'Tonnes', 'y' => 'num'], ['k' => 'blaster', 'l' => 'Blaster'],
                ['k' => 'st', 'l' => 'Status', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => ['holes', 'depth', 'expl', 'det', 'vol', 'tonnes'],
        ]];
    }

    private function secRegister($qry): array
    {
        $rows = ExplosivesRegister::with(['quarry', 'blast'])->when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))
            ->orderByDesc('register_date')->take(60)->get()
            ->map(fn($e) => [
                'date' => $e->register_date, 'quarry' => $e->quarry->name ?? '', 'mag' => $e->magazine,
                'type' => $e->explosive_type, 'open' => round((float) $e->opening_balance, 1),
                'recv' => round((float) $e->received_qty, 1), 'issued' => round((float) $e->issued_qty, 1),
                'close' => round((float) $e->closing_balance, 1), 'to' => $e->issued_to,
                'blast' => $e->blast->blast_no ?? '', 'permit' => $e->permit_no,
            ])->all();
        return ['k' => 'table', 'id' => 'd_reg', 'ti' => 'Explosives Magazine Register — chronology with running balances', 'cls' => 'wide', 'cfg' => [
            'id' => 'd_reg', 'page' => 12, 'search' => true,
            'cols' => [
                ['k' => 'date', 'l' => 'Date'], ['k' => 'quarry', 'l' => 'Quarry'], ['k' => 'mag', 'l' => 'Magazine'],
                ['k' => 'type', 'l' => 'Type'], ['k' => 'open', 'l' => 'Opening', 'y' => 'num'],
                ['k' => 'recv', 'l' => 'Received', 'y' => 'num'], ['k' => 'issued', 'l' => 'Issued', 'y' => 'num'],
                ['k' => 'close', 'l' => 'Closing', 'y' => 'num'], ['k' => 'to', 'l' => 'Issued to'],
                ['k' => 'blast', 'l' => 'Blast'], ['k' => 'permit', 'l' => 'Permit'],
            ], 'rows' => $rows, 'totals' => ['recv', 'issued'],
        ]];
    }
}
