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

use App\Http\Controllers\Controller;
use App\Models\Approval;
use App\Models\ClientIpc;
use App\Models\ExpenseClaim;
use App\Models\PurchaseOrder;
use App\Models\StaffAdvance;
use App\Models\SubcontractorClaim;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

/** Live data for the Command Centre — Approvals & Workflow module (Group scope: single approval queue). */
class WflowController extends Controller
{
    use Concerns\DashboardHelpers;

    /** document_type → [model, no-col, amount-col|null]. */
    private const SOURCES = [
        'expense_claim' => [ExpenseClaim::class, 'claim_no', 'total_amount'],
        'staff_advance' => [StaffAdvance::class, 'advance_no', 'amount'],
        'purchase_order' => [PurchaseOrder::class, 'po_no', 'grand_total'],
        'client_ipc' => [ClientIpc::class, 'ipc_no', 'net_certified'],
        'subcontractor_claim' => [SubcontractorClaim::class, 'claim_no', 'net_payable'],
    ];

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

        $pendQ = Approval::where('status', 'pending');
        $pend = (clone $pendQ)->count();
        $pendVal = round((clone $pendQ)->get()->sum(fn($a) => $this->amount($a)));

        $dec = fn($from, $to, $st) => Approval::where('status', $st)->whereBetween('decided_at', [$from, $to])->count();
        $appr = $dec($cf, $ct, 'approved');
        $apprP = $dec($pf, $pt, 'approved');
        $rej = $dec($cf, $ct, 'rejected');
        $rejP = $dec($pf, $pt, 'rejected');
        $rate = ($appr + $rej) > 0 ? round($appr / ($appr + $rej) * 100, 1) : 100;

        $oldest = Approval::where('status', 'pending')->min('created_at');
        $oldAge = $oldest ? (int) Carbon::parse($oldest)->diffInDays($today) : 0;

        $prA = Approval::where('status', 'approved')->where('document_type', 'purchase_requisition')->whereBetween('decided_at', [$cf, $ct])->count();
        $prAP = Approval::where('status', 'approved')->where('document_type', 'purchase_requisition')->whereBetween('decided_at', [$pf, $pt])->count();
        $poA = Approval::where('status', 'approved')->where('document_type', 'purchase_order')->whereBetween('decided_at', [$cf, $ct])->count();
        $poAP = Approval::where('status', 'approved')->where('document_type', 'purchase_order')->whereBetween('decided_at', [$pf, $pt])->count();
        $hrP = Approval::where('status', 'pending')->whereIn('document_type', ['leave_application', 'staff_advance', 'expense_claim'])->count();
        $certA = Approval::where('status', 'approved')->whereIn('document_type', ['client_ipc', 'subcontractor_claim'])->whereBetween('decided_at', [$cf, $ct])->count();
        $certAP = Approval::where('status', 'approved')->whereIn('document_type', ['client_ipc', 'subcontractor_claim'])->whereBetween('decided_at', [$pf, $pt])->count();
        $flows = Approval::where('status', 'pending')->distinct('document_type')->count('document_type');

        $cards = [
            $this->card('Pending Now', $pend, '#', $pend, [], ['good' => 'down', 'st' => $pend > 0 ? 'amber' : 'green']),
            $this->card('Pending Value', $pendVal, 'GHS', $pendVal, [], ['st' => 'blue']),
            $this->card('Approved', $appr, '#', $apprP),
            $this->card('Rejected', $rej, '#', $rejP, [], ['good' => 'down', 'st' => $rej > 0 ? 'red' : 'green']),
            $this->card('Approval Rate', $rate, '%', $rate, [], ['tgt' => 90]),
            $this->card('Oldest Pending', $oldAge, 'num', $oldAge, [], ['good' => 'down', 'info' => 'days']),
            $this->card('PRs Approved', $prA, '#', $prAP),
            $this->card('POs Approved', $poA, '#', $poAP),
            $this->card('HR Requests Pending', $hrP, '#', $hrP, [], ['good' => 'down']),
            $this->card('Certs Approved', $certA, '#', $certAP),
            $this->card('Total Decisions', Approval::whereIn('status', ['approved', 'rejected'])->count(), '#', Approval::whereIn('status', ['approved', 'rejected'])->count(), [], ['st' => 'blue']),
            $this->card('Flows With Pending', $flows, '#', $flows, [], ['st' => 'blue']),
        ];

        return response()->json([
            'sub' => 'Approval queue · decision history · flow health · Group · ' . $r->get('period', 'This Month'),
            'asAt' => Carbon::today()->format('d M Y'),
            'cards' => $cards,
            'sections' => [
                $this->secDec(),
                $this->secType(),
                $this->secStat(),
                $this->secDecider(),
                $this->secPend($today),
                $this->secHist(),
                $this->secSla(),
            ],
        ]);
    }

    private function amount($a): float
    {
        $src = self::SOURCES[$a->document_type] ?? null;
        if (!$src) return 0;
        [$model, $no, $amt] = $src;
        if (!$amt) return 0;
        $row = $model::where($no, $a->document_no)->first();
        return $row ? (float) $row->{$amt} : 0;
    }

    private function pretty($t): string
    {
        return ucwords(str_replace('_', ' ', (string) $t));
    }

    private function secDec(): array
    {
        $t = Carbon::today();
        $labels = [];
        $appr = [];
        $rej = [];
        for ($i = 11; $i >= 0; $i--) {
            $d = $t->copy()->subMonths($i);
            $f = $d->copy()->startOfMonth()->format('Y-m-d');
            $e = $d->copy()->endOfMonth()->format('Y-m-d');
            $labels[] = $d->format('M y');
            $appr[] = Approval::where('status', 'approved')->whereBetween('decided_at', [$f, $e])->count();
            $rej[] = Approval::where('status', 'rejected')->whereBetween('decided_at', [$f, $e])->count();
        }
        return ['k' => 'chart', 'id' => 'wf_dec', 'ti' => 'Decisions — approved vs rejected per month (12 months)', 'cls' => 'wide', 'spec' => [
            'type' => 'bar', 'fmt' => 'num',
            'labels' => $labels,
            'series' => [
                ['name' => 'Approved', 'data' => $appr, 'color' => '#22c55e'],
                ['name' => 'Rejected', 'data' => $rej, 'color' => '#ef4444'],
            ],
        ]];
    }

    private function secType(): array
    {
        $rows = Approval::select('document_type', DB::raw('count(*) n'))->groupBy('document_type')->orderByDesc('n')->get();
        return ['k' => 'chart', 'id' => 'wf_type', 'ti' => 'Requests by Flow — count (all time)', 'spec' => [
            'type' => 'hbar', 'fmt' => 'num',
            'labels' => $rows->map(fn($r) => mb_strimwidth($this->pretty($r->document_type), 0, 24, '…'))->all(),
            'series' => [['name' => 'Requests', 'data' => $rows->map(fn($r) => (int) $r->n)->all(), 'color' => '#8b5cf6']],
        ]];
    }

    private function secStat(): array
    {
        $rows = Approval::select('status', DB::raw('count(*) n'))->groupBy('status')->get();
        $col = ['approved' => '#22c55e', 'pending' => '#f59e0b', 'rejected' => '#ef4444'];
        $items = $rows->map(fn($r) => ['label' => ucfirst((string) $r->status), 'value' => (int) $r->n, 'color' => $col[$r->status] ?? '#3b82f6'])->all();
        return ['k' => 'chart', 'id' => 'wf_stat', 'ti' => 'Queue by Status — count (live)', 'spec' => [
            'type' => 'doughnut', 'fmt' => 'num', 'center' => 'Queue', 'items' => $items,
        ]];
    }

    private function secDecider(): array
    {
        $rows = Approval::whereIn('status', ['approved', 'rejected'])->whereNotNull('decided_by')
            ->select('decided_by', DB::raw('count(*) n'))->groupBy('decided_by')->orderByDesc('n')->take(8)->get();
        $names = \App\Models\User::whereIn('id', $rows->pluck('decided_by'))->pluck('name', 'id');
        return ['k' => 'chart', 'id' => 'wf_decider', 'ti' => 'Decisions by Approver — count (all time)', 'spec' => [
            'type' => 'hbar', 'fmt' => 'num',
            'labels' => $rows->map(fn($r) => mb_strimwidth($names[$r->decided_by] ?? ('#' . $r->decided_by), 0, 24, '…'))->all(),
            'series' => [['name' => 'Decisions', 'data' => $rows->map(fn($r) => (int) $r->n)->all(), 'color' => '#06b6d4']],
        ]];
    }

    private function secPend($today): array
    {
        $rows = Approval::with('requester')->where('status', 'pending')->orderBy('created_at')->get()
            ->map(function ($a) use ($today) {
                $age = (int) Carbon::parse($a->created_at)->diffInDays($today);
                $amt = $this->amount($a);
                return ['no' => $a->document_no, 'type' => $this->pretty($a->document_type),
                    'req' => $a->requester->name ?? '', 'lvl' => 'L' . $a->level,
                    'since' => substr((string) $a->created_at, 0, 10), 'age' => $age . 'd',
                    'amt' => $amt > 0 ? round($amt) : '—', 'aid' => $a->id, '_decide' => '',
                    'st' => $age > 5 ? 'red' : ($age > 2 ? 'amber' : 'green'),
                    'stL' => $age > 5 ? 'Breach' : ($age > 2 ? 'Ageing' : 'On track')];
            })->all();
        return ['k' => 'table', 'id' => 'wf_pend', 'ti' => 'Pending Queue — oldest first with SLA flags', 'cls' => 'wide', 'cfg' => [
            'id' => 'wf_pend', 'page' => 12, 'search' => true,
            'cols' => [
                ['k' => 'no', 'l' => 'Document'], ['k' => 'type', 'l' => 'Flow', 'wide' => 1], ['k' => 'req', 'l' => 'Requested by'],
                ['k' => 'lvl', 'l' => 'Level'], ['k' => 'since', 'l' => 'Since'], ['k' => 'age', 'l' => 'Age'],
                ['k' => 'amt', 'l' => 'Amount', 'y' => 'num'], ['k' => 'st', 'l' => 'SLA', 'y' => 'badge'],
                ['k' => '_decide', 'l' => 'Decide', 'y' => 'decide'],
            ], 'rows' => $rows,
        ]];
    }

    private function secHist(): array
    {
        $rows = Approval::with(['requester', 'decider'])->whereIn('status', ['approved', 'rejected'])
            ->orderByDesc('decided_at')->take(30)->get()
            ->map(fn($a) => ['when' => $a->decided_at ? substr((string) $a->decided_at, 0, 10) : '',
                'no' => $a->document_no, 'type' => $this->pretty($a->document_type),
                'req' => $a->requester->name ?? '', 'dec' => $a->decider->name ?? '', 'lvl' => 'L' . $a->level,
                'rem' => mb_strimwidth((string) $a->remarks, 0, 50, '…'),
                'st' => $a->status === 'approved' ? 'green' : 'red', 'stL' => ucfirst((string) $a->status)])->all();
        return ['k' => 'table', 'id' => 'wf_hist', 'ti' => 'Decision History — latest 30 decided requests', 'cls' => 'wide', 'cfg' => [
            'id' => 'wf_hist', 'page' => 10, 'search' => true,
            'cols' => [
                ['k' => 'when', 'l' => 'Decided'], ['k' => 'no', 'l' => 'Document'], ['k' => 'type', 'l' => 'Flow', 'wide' => 1],
                ['k' => 'req', 'l' => 'Requested by'], ['k' => 'dec', 'l' => 'Decided by'], ['k' => 'lvl', 'l' => 'Level'],
                ['k' => 'rem', 'l' => 'Remarks'], ['k' => 'st', 'l' => 'Decision', 'y' => 'badge'],
            ], 'rows' => $rows,
        ]];
    }

    private function secSla(): array
    {
        $rows = Approval::select('document_type', DB::raw('count(*) t'),
                DB::raw("sum(status='approved') a"), DB::raw("sum(status='pending') p"), DB::raw("sum(status='rejected') r"))
            ->groupBy('document_type')->get()
            ->map(function ($r) {
                $rate = ($r->a + $r->r) > 0 ? round($r->a / ($r->a + $r->r) * 100, 1) : 100;
                return ['type' => $this->pretty($r->document_type), 'tot' => (int) $r->t, 'appr' => (int) $r->a,
                    'pend' => (int) $r->p, 'rej' => (int) $r->r, 'rate' => $rate . '%',
                    'st' => $r->p > 0 ? 'amber' : 'green', 'stL' => $r->p > 0 ? $r->p . ' waiting' : 'Clear'];
            })->all();
        return ['k' => 'table', 'id' => 'wf_sla', 'ti' => 'Flow Health — throughput & approval rate per flow', 'cls' => 'wide', 'cfg' => [
            'id' => 'wf_sla', 'page' => 10, 'search' => true,
            'cols' => [
                ['k' => 'type', 'l' => 'Flow', 'wide' => 1], ['k' => 'tot', 'l' => 'Total', 'y' => 'num'],
                ['k' => 'appr', 'l' => 'Approved', 'y' => 'num'], ['k' => 'pend', 'l' => 'Pending', 'y' => 'num'],
                ['k' => 'rej', 'l' => 'Rejected', 'y' => 'num'], ['k' => 'rate', 'l' => 'Appr. rate'],
                ['k' => 'st', 'l' => 'Status', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => ['tot', 'appr', 'pend', 'rej'],
        ]];
    }
}
