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

use App\Http\Controllers\Controller;
use App\Models\GoodsReceipt;
use App\Models\PurchaseInvoice;
use App\Models\PurchaseOrder;
use App\Models\Subcontract;
use App\Models\SubcontractorClaim;
use App\Models\Supplier;
use App\Models\SupplierPayment;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

/** Live data for the Command Centre — Suppliers & Subcontractors module (Group scope: vendors serve all sites). */
class SuppController extends Controller
{
    use Concerns\DashboardHelpers;

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

        $active = Supplier::where('is_active', true)->count();
        $creditors = (float) Supplier::where('current_balance', '>', 0)->sum('current_balance');
        $odQ = PurchaseInvoice::where('status', '!=', 'draft')->where('balance_due', '>', 0)->where('due_date', '<', $today . ' 00:00:00');
        $odN = (clone $odQ)->count();
        $odV = round(\App\Services\CurrencyService::sumGhs((clone $odQ), 'balance_due'));

        $pays = SupplierPayment::with('invoice')->whereBetween('payment_date', [$cf, $ct])->get();
        $avgPay = $pays->count() ? round($pays->avg(fn($p) => $p->invoice ? Carbon::parse($p->invoice->invoice_date)->diffInDays(Carbon::parse($p->payment_date), false) : 0), 1) : 0;

        $poN = PurchaseOrder::whereBetween('po_date', [$cf, $ct])->count();
        $poNP = PurchaseOrder::whereBetween('po_date', [$pf, $pt])->count();
        $poV = $this->sumBetween(PurchaseOrder::query(), 'po_date', 'grand_total', $cf, $ct);
        $poVP = $this->sumBetween(PurchaseOrder::query(), 'po_date', 'grand_total', $pf, $pt);
        $grn = GoodsReceipt::whereBetween('grn_date', [$cf, $ct])->count();
        $grnP = GoodsReceipt::whereBetween('grn_date', [$pf, $pt])->count();
        $pending = PurchaseInvoice::whereIn('status', ['draft', 'submitted', 'pending'])->count();

        $subN = Subcontract::where('status', 'active')->count();
        $subV = round((float) Subcontract::where('status', 'active')->sum('contract_amount'));
        $claim = $this->sumBetween(SubcontractorClaim::query(), 'claim_date', 'amount_certified', $cf, $ct);
        $claimP = $this->sumBetween(SubcontractorClaim::query(), 'claim_date', 'amount_certified', $pf, $pt);

        $cards = [
            $this->card('Active Suppliers', $active, '#', $active, [], ['st' => 'blue']),
            $this->card('Creditors Owing', round($creditors), 'GHS', round($creditors), [], ['good' => 'down']),
            $this->card('Overdue Bills', $odN, '#', $odN, [], ['good' => 'down', 'st' => $odN > 0 ? 'red' : 'green']),
            $this->card('Overdue Value', $odV, 'GHS', $odV, [], ['good' => 'down']),
            $this->card('Avg Days to Pay', $avgPay, 'num', $avgPay, [], ['good' => 'down', 'tgt' => 30]),
            $this->card('POs Raised', $poN, '#', $poNP),
            $this->card('PO Value', round($poV), 'GHS', round($poVP)),
            $this->card('GRNs Posted', $grn, '#', $grnP),
            $this->card('Bills Awaiting Approval', $pending, '#', $pending, [], ['good' => 'down', 'st' => $pending > 0 ? 'amber' : 'green']),
            $this->card('Active Subcontracts', $subN, '#', $subN, [], ['st' => 'blue']),
            $this->card('Subcontract Value', $subV, 'GHS', $subV, [], ['st' => 'blue']),
            $this->card('Claims Certified', round($claim), 'GHS', round($claimP)),
        ];

        return response()->json([
            'sub' => 'Vendor master · bills & aging · subcontracts · Group · ' . $r->get('period', 'This Month'),
            'asAt' => Carbon::today()->format('d M Y'),
            'cards' => $cards,
            'sections' => [
                $this->secFlow(),
                $this->secTop($cf, $ct),
                $this->secAging(),
                $this->secTypes(),
                $this->secSup(),
                $this->secBills($today),
                $this->secSub(),
                $this->secClaim(),
            ],
        ]);
    }

    private function secFlow(): array
    {
        $t = Carbon::today();
        $labels = [];
        $billed = [];
        $paid = [];
        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');
            $billed[] = round(\App\Services\CurrencyService::sumGhs(PurchaseInvoice::whereBetween('invoice_date', [$f, $e])->where('status', '!=', 'draft'), 'grand_total'));
            $paid[] = round(\App\Services\CurrencyService::sumGhs(SupplierPayment::whereBetween('payment_date', [$f, $e]), 'amount'));
        }
        return ['k' => 'chart', 'id' => 'su_flow', 'ti' => 'Billed vs Paid — GHS per month (12 months)', 'cls' => 'wide', 'spec' => [
            'type' => 'bar', 'fmt' => 'money',
            'labels' => $labels,
            'series' => [
                ['name' => 'Billed', 'data' => $billed, 'color' => '#f59e0b'],
                ['name' => 'Paid', 'data' => $paid, 'color' => '#22c55e'],
            ],
        ]];
    }

    private function secTop($cf, $ct): array
    {
        $rows = PurchaseInvoice::whereBetween('invoice_date', [$cf, $ct])->where('status', '!=', 'draft')
            ->select('supplier_id', DB::raw('sum(grand_total * exchange_rate) t'))->groupBy('supplier_id')->orderByDesc('t')->take(8)->get();
        $names = Supplier::whereIn('id', $rows->pluck('supplier_id'))->pluck('name', 'id');
        return ['k' => 'chart', 'id' => 'su_top', 'ti' => 'Top Suppliers — billed value (period)', 'spec' => [
            'type' => 'hbar', 'fmt' => 'money',
            'labels' => $rows->map(fn($r) => mb_strimwidth($names[$r->supplier_id] ?? ('#' . $r->supplier_id), 0, 24, '…'))->all(),
            'series' => [['name' => 'GHS', 'data' => $rows->map(fn($r) => round((float) $r->t))->all(), 'color' => '#f59e0b']],
        ]];
    }

    private function secAging(): array
    {
        $today = Carbon::today();
        $b = ['Current' => 0, '1–30d' => 0, '31–60d' => 0, '61d+' => 0];
        $rows = PurchaseInvoice::where('status', '!=', 'draft')->where('balance_due', '>', 0)->get(['due_date', 'balance_due']);
        foreach ($rows as $v) {
            $bal = (float) $v->balance_due;
            $od = $v->due_date ? Carbon::parse($v->due_date)->diffInDays($today, false) : 0;
            if ($od <= 0) $b['Current'] += $bal;
            elseif ($od <= 30) $b['1–30d'] += $bal;
            elseif ($od <= 60) $b['31–60d'] += $bal;
            else $b['61d+'] += $bal;
        }
        return ['k' => 'chart', 'id' => 'su_aging', 'ti' => 'Creditor Aging — GHS by overdue band (live)', 'spec' => [
            'type' => 'hbar', 'fmt' => 'money',
            'labels' => array_keys($b),
            'series' => [['name' => 'GHS', 'data' => array_values(array_map(fn($v) => round($v), $b)), 'color' => '#ef4444']],
        ]];
    }

    private function secTypes(): array
    {
        $rows = Supplier::where('is_active', true)->select('supplier_type', DB::raw('count(*) n'))->groupBy('supplier_type')->get();
        $pal = $this->palette();
        $items = $rows->values()->map(fn($r, $i) => ['label' => ucfirst((string) $r->supplier_type), 'value' => (int) $r->n, 'color' => $pal[$i % count($pal)]])->all();
        return ['k' => 'chart', 'id' => 'su_types', 'ti' => 'Suppliers by Category — count (live)', 'spec' => [
            'type' => 'doughnut', 'fmt' => 'num', 'center' => 'Base', 'items' => $items,
        ]];
    }

    private function secSup(): array
    {
        $rows = Supplier::where('is_active', true)->orderBy('code')->get()->map(function ($s) {
            $bal = (float) $s->current_balance;
            $poN = PurchaseOrder::where('supplier_id', $s->id)->count();
            $ltv = \App\Services\CurrencyService::sumGhs(PurchaseInvoice::where('supplier_id', $s->id)->where('status', '!=', 'draft'), 'grand_total');
            $last = PurchaseOrder::where('supplier_id', $s->id)->max('po_date');
            return ['code' => $s->code, 'name' => $s->name, 'type' => ucfirst((string) $s->supplier_type),
                'contact' => $s->contact_person ?? '', 'phone' => $s->phone ?? '', 'terms' => ($s->payment_terms_days ?? 0) . 'd',
                'bal' => round($bal), 'pos' => $poN, 'ltv' => round($ltv), 'last' => $last ? substr($last, 0, 10) : '—',
                'st' => $bal > 0 ? 'amber' : 'green', 'stL' => $bal > 0 ? 'Owed' : 'Clear'];
        })->all();
        return ['k' => 'table', 'id' => 'su_sup', 'ti' => 'Supplier Master — balances, terms & lifetime spend', 'cls' => 'wide', 'cfg' => [
            'id' => 'su_sup', 'page' => 10, 'search' => true,
            'cols' => [
                ['k' => 'code', 'l' => 'Code'], ['k' => 'name', 'l' => 'Supplier', 'wide' => 1], ['k' => 'type', 'l' => 'Category'],
                ['k' => 'contact', 'l' => 'Contact'], ['k' => 'phone', 'l' => 'Phone'], ['k' => 'terms', 'l' => 'Terms'],
                ['k' => 'bal', 'l' => 'Balance', 'y' => 'num'], ['k' => 'pos', 'l' => 'POs', 'y' => 'num'],
                ['k' => 'ltv', 'l' => 'Lifetime', 'y' => 'num'], ['k' => 'last', 'l' => 'Last PO'],
                ['k' => 'st', 'l' => 'Status', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => ['bal', 'ltv'],
        ]];
    }

    private function secBills($today): array
    {
        $rows = PurchaseInvoice::with('supplier')->where('status', '!=', 'draft')->where('balance_due', '>', 0)
            ->orderBy('due_date')->get()
            ->map(function ($v) use ($today) {
                $od = $v->due_date ? (int) Carbon::parse($v->due_date)->diffInDays(Carbon::parse($today), false) : 0;
                return ['pin' => $v->pin_no, 'date' => substr((string) $v->invoice_date, 0, 10),
                    'due' => $v->due_date ? substr((string) $v->due_date, 0, 10) : '',
                    'sup' => $v->supplier->name ?? '', 'ref' => $v->supplier_invoice_no ?? '',
                    'total' => round((float) $v->grand_total), 'paid' => round((float) $v->amount_paid),
                    'bal' => round((float) $v->balance_due),
                    'od' => $od <= 0 ? 'Current' : $od . 'd',
                    'st' => $od <= 0 ? 'amber' : ($od <= 30 ? 'red' : 'red'),
                    'stL' => $od <= 0 ? 'Due' : 'Overdue',
                    'open' => 'Open \u2192', 'openUrl' => '/purchase-invoices/' . $v->id];
            })->all();
        return ['k' => 'table', 'id' => 'su_bills', 'ti' => 'Open Bills — unpaid supplier invoices by due date', 'cls' => 'wide', 'cfg' => [
            'id' => 'su_bills', 'page' => 12, 'search' => true,
            'cols' => [
                ['k' => 'pin', 'l' => 'Bill'], ['k' => 'date', 'l' => 'Date'], ['k' => 'due', 'l' => 'Due'],
                ['k' => 'sup', 'l' => 'Supplier', 'wide' => 1], ['k' => 'ref', 'l' => 'Supplier ref'],
                ['k' => 'total', 'l' => 'Total', 'y' => 'num'], ['k' => 'paid', 'l' => 'Paid', 'y' => 'num'],
                ['k' => 'bal', 'l' => 'Balance', 'y' => 'num'], ['k' => 'od', 'l' => 'Overdue'],
                ['k' => 'st', 'l' => 'Status', 'y' => 'badge'], ['k' => 'open', 'l' => 'Open', 'y' => 'link'],
            ], 'rows' => $rows, 'totals' => ['total', 'paid', 'bal'],
        ]];
    }

    private function secSub(): array
    {
        $rows = Subcontract::with('project')->orderBy('subcontract_no')->get()->map(function ($s) {
            $claimed = (float) $s->claims()->sum('amount_claimed');
            $cert = (float) $s->claims()->sum('amount_certified');
            $ret = (float) $s->claims()->sum('retention_deducted');
            return ['no' => $s->subcontract_no, 'name' => $s->subcontractor_name,
                'proj' => $s->project->project_no ?? '', 'scope' => mb_strimwidth((string) $s->scope_of_work, 0, 46, '…'),
                'amt' => round((float) $s->contract_amount), 'claimed' => round($claimed), 'cert' => round($cert), 'ret' => round($ret),
                'linked' => $s->supplier_id ? 'Yes' : 'No',
                'st' => $s->status === 'active' ? 'green' : ($s->status === 'completed' ? 'blue' : 'amber'),
                'stL' => ucfirst((string) $s->status)];
        })->all();
        return ['k' => 'table', 'id' => 'su_sub', 'ti' => 'Subcontracts — value, claims to date & retention', 'cls' => 'wide', 'cfg' => [
            'id' => 'su_sub', 'page' => 10, 'search' => true,
            'cols' => [
                ['k' => 'no', 'l' => 'Subcontract'], ['k' => 'name', 'l' => 'Contractor', 'wide' => 1],
                ['k' => 'proj', 'l' => 'Project'], ['k' => 'scope', 'l' => 'Scope'],
                ['k' => 'amt', 'l' => 'Contract', 'y' => 'num'], ['k' => 'claimed', 'l' => 'Claimed', 'y' => 'num'],
                ['k' => 'cert', 'l' => 'Certified', 'y' => 'num'], ['k' => 'ret', 'l' => 'Retention', 'y' => 'num'],
                ['k' => 'linked', 'l' => 'Vendor link'], ['k' => 'st', 'l' => 'Status', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => ['amt', 'claimed', 'cert', 'ret'],
        ]];
    }

    private function secClaim(): array
    {
        $rows = SubcontractorClaim::with('subcontract')->orderByDesc('claim_date')->take(30)->get()
            ->map(fn($c) => ['no' => $c->claim_no, 'date' => substr((string) $c->claim_date, 0, 10),
                'sub' => $c->subcontract->subcontract_no ?? '', 'claimed' => round((float) $c->amount_claimed),
                'cert' => round((float) $c->amount_certified), 'ret' => round((float) $c->retention_deducted),
                'wht' => round((float) $c->tax_withheld), 'net' => round((float) $c->net_payable),
                'st' => $c->status === 'paid' ? 'green' : ($c->status === 'certified' ? 'blue' : 'amber'),
                'stL' => ucfirst((string) $c->status)])->all();
        return ['k' => 'table', 'id' => 'su_claim', 'ti' => 'Subcontractor Claims — latest 30', 'cls' => 'wide', 'cfg' => [
            'id' => 'su_claim', 'page' => 10, 'search' => true,
            'cols' => [
                ['k' => 'no', 'l' => 'Claim'], ['k' => 'date', 'l' => 'Date'], ['k' => 'sub', 'l' => 'Subcontract', 'wide' => 1],
                ['k' => 'claimed', 'l' => 'Claimed', 'y' => 'num'], ['k' => 'cert', 'l' => 'Certified', 'y' => 'num'],
                ['k' => 'ret', 'l' => 'Retention', 'y' => 'num'], ['k' => 'wht', 'l' => 'WHT', 'y' => 'num'],
                ['k' => 'net', 'l' => 'Net', 'y' => 'num'], ['k' => 'st', 'l' => 'Status', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => ['claimed', 'cert', 'ret', 'wht', 'net'],
        ]];
    }
}
