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

use App\Http\Controllers\Controller;
use App\Models\BankAccount;
use App\Models\BankTransaction;
use App\Models\ExpenseClaim;
use App\Models\PurchaseInvoice;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

/** Live data for the Command Centre — Cash, Bank & Treasury module (Group scope: accounts are not quarry-linked). */
class TreasuryController extends Controller
{
    use Concerns\DashboardHelpers;

    private ?array $flowCache = null;

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

        $accts = BankAccount::where('is_active', true)->get();
        $cash = (float) $accts->sum(fn($a) => ((float) $a->current_balance) * \App\Services\CurrencyService::rate($a->currency ?? 'GHS'));
        $byName = fn($n) => round((float) $accts->firstWhere('bank_name', $n)?->current_balance, 2);

        $in = $this->sumBetween(BankTransaction::where('txn_type', 'deposit'), 'txn_date', 'amount', $cf, $ct);
        $inP = $this->sumBetween(BankTransaction::where('txn_type', 'deposit'), 'txn_date', 'amount', $pf, $pt);
        $out = $this->sumBetween(BankTransaction::whereIn('txn_type', ['withdrawal', 'bank-charges']), 'txn_date', 'amount', $cf, $ct);
        $outP = $this->sumBetween(BankTransaction::whereIn('txn_type', ['withdrawal', 'bank-charges']), 'txn_date', 'amount', $pf, $pt);

        $unrec = BankTransaction::where('reconciled', false)->count();
        $unrecVal = \App\Services\CurrencyService::sumGhs(BankTransaction::where('reconciled', false), 'amount');
        $claimSub = ExpenseClaim::where('status', 'submitted')->count();
        $claimAppr = ExpenseClaim::where('status', 'approved')->count();
        $due14 = \App\Services\CurrencyService::sumGhs(PurchaseInvoice::where('status', '!=', 'draft')->where('balance_due', '>', 0)
            ->whereDate('due_date', '<=', Carbon::today()->addDays(14)), 'balance_due');

        $cards = [
            $this->card('Cash & Bank', round($cash), 'GHS', round($cash), [], ['st' => 'blue']),
            $this->card('GCB Current Account', $byName('GCB Bank'), 'GHS', $byName('GCB Bank'), [], ['st' => 'blue']),
            $this->card('MTN MoMo Wallet', $byName('MTN MoMo'), 'GHS', $byName('MTN MoMo'), [], ['st' => 'blue']),
            $this->card('Petty Cash', $byName('Petty Cash'), 'GHS', $byName('Petty Cash'), [], ['st' => 'blue']),
            $this->card('Cash In (' . $r->get('period', 'This Month') . ')', round($in), 'GHS', round($inP), $this->spark('bank_transactions', 'txn_date', 'amount', "txn_type='deposit'", $grain)),
            $this->card('Cash Out (' . $r->get('period', 'This Month') . ')', round($out), 'GHS', round($outP), $this->spark('bank_transactions', 'txn_date', 'amount', "txn_type in ('withdrawal','bank-charges')", $grain), ['good' => 'down']),
            $this->card('Net Cash Flow', round($in - $out), 'GHS', round($inP - $outP), [], ['st' => ($in - $out) >= 0 ? 'green' : 'red']),
            $this->card('Unreconciled Txns', $unrec, '#', $unrec, [], ['good' => 'down', 'st' => $unrec > 0 ? 'red' : 'green']),
            $this->card('Unreconciled Value', round($unrecVal), 'GHS', round($unrecVal), [], ['good' => 'down']),
            $this->card('Claims Awaiting Approval', $claimSub, '#', $claimSub, [], ['st' => $claimSub > 0 ? 'amber' : 'green']),
            $this->card('Claims Approved, Unpaid', $claimAppr, '#', $claimAppr, [], ['st' => $claimAppr > 0 ? 'amber' : 'green']),
            $this->card('Payables Due ≤ 14d', round($due14), 'GHS', round($due14), [], ['good' => 'down']),
        ];

        return response()->json([
            'sub' => 'Cash position · flows · bank reconciliation · claims · Group · ' . $r->get('period', 'This Month'),
            'asAt' => Carbon::today()->format('d M Y'),
            'cards' => $cards,
            'sections' => [
                $this->secFlow(),
                $this->secBalance($cash),
                $this->secByAcct(),
                $this->secOutSrc($cf, $ct),
                $this->secAccts(),
                $this->secTxns(),
                $this->secUnrec(),
                $this->secClaims(),
            ],
        ]);
    }

    private function dayFlows(): array
    {
        if ($this->flowCache === null) {
            $this->flowCache = [
                $this->daily('bank_transactions', 'txn_date', 'amount', "txn_type='deposit'", 30),
                $this->daily('bank_transactions', 'txn_date', 'amount', "txn_type in ('withdrawal','bank-charges')", 30),
            ];
        }
        return $this->flowCache;
    }

    private function secFlow(): array
    {
        [$in, $out] = $this->dayFlows();
        return ['k' => 'chart', 'id' => 't_flow', 'ti' => 'Cash In vs Cash Out — GHS per day (30 days)', 'cls' => 'wide', 'spec' => [
            'type' => 'line', 'fmt' => 'money', 'zoom' => true, 'height' => 280,
            'labels' => $this->dailyLabels(30),
            'series' => [
                ['name' => 'In', 'data' => $in, 'color' => '#22c55e', 'area' => true],
                ['name' => 'Out', 'data' => $out, 'color' => '#ef4444'],
            ],
        ]];
    }

    private function secBalance($cash): array
    {
        [$in, $out] = $this->dayFlows();
        $bal = $cash - (array_sum($in) - array_sum($out));
        $data = [];
        for ($i = 0; $i < 30; $i++) {
            $bal += $in[$i] - $out[$i];
            $data[] = round(max($bal, 0));
        }
        return ['k' => 'chart', 'id' => 't_bal', 'ti' => 'Closing Cash Balance — GHS per day (30 days)', 'cls' => 'wide', 'spec' => [
            'type' => 'line', 'fmt' => 'money', 'zoom' => true, 'height' => 260,
            'labels' => $this->dailyLabels(30),
            'series' => [['name' => 'Balance', 'data' => $data, 'color' => '#f8c200', 'area' => true]],
            'target' => ['value' => count($data) ? round(array_sum($data) / count($data)) : 0, 'label' => '30-day avg'],
        ]];
    }

    private function secByAcct(): array
    {
        $rows = BankAccount::where('is_active', true)->orderBy('bank_name')->get();
        return ['k' => 'chart', 'id' => 't_byacct', 'ti' => 'Cash by Account — GHS (live)', 'spec' => [
            'type' => 'hbar', 'fmt' => 'money',
            'labels' => $rows->map(fn($b) => mb_strimwidth($b->bank_name, 0, 24, '…'))->all(),
            'series' => [['name' => 'GHS', 'data' => $rows->map(fn($b) => round((float) $b->current_balance))->all(), 'color' => '#3b82f6']],
        ]];
    }

    private function secOutSrc($cf, $ct): array
    {
        $rows = BankTransaction::whereBetween('txn_date', [$cf, $ct])->whereIn('txn_type', ['withdrawal', 'bank-charges'])
            ->select('related_module', DB::raw('sum(amount * exchange_rate) t'))->groupBy('related_module')->orderByDesc('t')->get();
        $pal = $this->palette();
        $items = $rows->values()->map(fn($r, $i) => ['label' => ucwords(str_replace(['-', '_'], ' ', (string) $r->related_module)), 'value' => round((float) $r->t), 'color' => $pal[$i % count($pal)]])->all();
        return ['k' => 'chart', 'id' => 't_outsrc', 'ti' => 'Cash Out by Source — GHS (period)', 'spec' => [
            'type' => 'doughnut', 'fmt' => 'money', 'center' => 'Out', 'items' => $items,
        ]];
    }

    private function secAccts(): array
    {
        $rows = BankAccount::with('glAccount')->where('is_active', true)->orderBy('bank_name')->get()
            ->map(function ($b) {
                $gl = $b->glAccount ? (float) $b->glAccount->current_balance : null;
                $diff = $gl === null ? null : round((float) $b->current_balance - $gl, 2);
                return ['bank' => $b->bank_name, 'no' => $b->account_no ?? '', 'name' => $b->account_name,
                    'type' => ucwords(str_replace('-', ' ', (string) $b->account_type)), 'ccy' => $b->currency ?? 'GHS',
                    'open' => round((float) $b->opening_balance), 'bal' => round((float) $b->current_balance),
                    'gl' => $b->glAccount->code ?? '—', 'glbal' => $gl === null ? '—' : round($gl),
                    'diff' => $diff === null ? '—' : $diff,
                    'st' => ($diff === null || abs($diff) > 0.01) ? 'red' : 'green', 'stL' => ($diff === null || abs($diff) > 0.01) ? 'Differs' : 'Tied'];
            })->all();
        return ['k' => 'table', 'id' => 't_accts', 'ti' => 'Bank Accounts — balances vs GL (live tie-out)', 'cls' => 'wide', 'cfg' => [
            'id' => 't_accts', 'page' => 10, 'search' => true,
            'cols' => [
                ['k' => 'bank', 'l' => 'Bank'], ['k' => 'no', 'l' => 'Account No'], ['k' => 'name', 'l' => 'Name', 'wide' => 1],
                ['k' => 'type', 'l' => 'Type'], ['k' => 'ccy', 'l' => 'Ccy'], ['k' => 'open', 'l' => 'Opening', 'y' => 'num'],
                ['k' => 'bal', 'l' => 'Balance', 'y' => 'num'], ['k' => 'gl', 'l' => 'GL'], ['k' => 'glbal', 'l' => 'GL bal', 'y' => 'num'],
                ['k' => 'diff', 'l' => 'Diff', 'y' => 'num'], ['k' => 'st', 'l' => 'Tie-out', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => ['bal', 'glbal'],
        ]];
    }

    private function secTxns(): array
    {
        $rows = BankTransaction::with('bankAccount')->orderByDesc('txn_date')->orderByDesc('id')->take(60)->get()
            ->map(fn($t) => ['no' => $t->txn_no, 'date' => substr((string) $t->txn_date, 0, 10),
                'acct' => $t->bankAccount->bank_name ?? '',
                'type' => ucwords(str_replace('-', ' ', (string) $t->txn_type)),
                'amt' => round((float) $t->amount, 2), 'ref' => $t->reference ?? '',
                'src' => ucwords(str_replace('_', ' ', (string) $t->related_module)),
                'narr' => mb_strimwidth((string) $t->narration, 0, 50, '…'),
                'st' => $t->reconciled ? 'green' : 'amber', 'stL' => $t->reconciled ? 'Reconciled' : 'Open'])->all();
        return ['k' => 'table', 'id' => 't_txns', 'ti' => 'Bank Transactions — latest 60', 'cls' => 'wide', 'cfg' => [
            'id' => 't_txns', 'page' => 12, 'search' => true,
            'cols' => [
                ['k' => 'no', 'l' => 'Txn'], ['k' => 'date', 'l' => 'Date'], ['k' => 'acct', 'l' => 'Account'],
                ['k' => 'type', 'l' => 'Type'], ['k' => 'amt', 'l' => 'Amount', 'y' => 'num'], ['k' => 'ref', 'l' => 'Reference'],
                ['k' => 'src', 'l' => 'Source'], ['k' => 'narr', 'l' => 'Narration', 'wide' => 1],
                ['k' => 'st', 'l' => 'Recon', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => [],
        ]];
    }

    private function secUnrec(): array
    {
        $today = Carbon::today();
        $rows = BankTransaction::with('bankAccount')->where('reconciled', false)
            ->orderBy('txn_date')->take(60)->get()
            ->map(fn($t) => ['no' => $t->txn_no, 'date' => substr((string) $t->txn_date, 0, 10),
                'acct' => $t->bankAccount->bank_name ?? '',
                'type' => ucwords(str_replace('-', ' ', (string) $t->txn_type)),
                'amt' => round((float) $t->amount, 2), 'ref' => $t->reference ?? '',
                'age' => Carbon::parse($t->txn_date)->diffInDays($today) . 'd',
                'st' => 'red', 'stL' => 'Open'])->all();
        return ['k' => 'table', 'id' => 't_unrec', 'ti' => 'Unreconciled Items — bank recon backlog', 'cls' => 'wide', 'cfg' => [
            'id' => 't_unrec', 'page' => 12, 'search' => true,
            'cols' => [
                ['k' => 'no', 'l' => 'Txn'], ['k' => 'date', 'l' => 'Date'], ['k' => 'acct', 'l' => 'Account'],
                ['k' => 'type', 'l' => 'Type'], ['k' => 'amt', 'l' => 'Amount', 'y' => 'num'], ['k' => 'ref', 'l' => 'Reference', 'wide' => 1],
                ['k' => 'age', 'l' => 'Age'], ['k' => 'st', 'l' => 'Status', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => ['amt'],
        ]];
    }

    private function secClaims(): array
    {
        $rows = ExpenseClaim::with('employee')->orderByDesc('claim_date')->take(30)->get()
            ->map(fn($c) => ['no' => $c->claim_no, 'date' => substr((string) $c->claim_date, 0, 10),
                'emp' => $c->employee->full_name ?? '', 'purpose' => mb_strimwidth((string) $c->purpose, 0, 60, '…'),
                'amt' => round((float) $c->total_amount),
                'st' => $c->status === 'paid' ? 'green' : ($c->status === 'submitted' ? 'amber' : 'blue'),
                'stL' => ucfirst((string) $c->status)])->all();
        return ['k' => 'table', 'id' => 't_claims', 'ti' => 'Expense Claims — latest 30', 'cls' => 'wide', 'cfg' => [
            'id' => 't_claims', 'page' => 10, 'search' => true,
            'cols' => [
                ['k' => 'no', 'l' => 'Claim'], ['k' => 'date', 'l' => 'Date'], ['k' => 'emp', 'l' => 'Employee', 'wide' => 1],
                ['k' => 'purpose', 'l' => 'Purpose'], ['k' => 'amt', 'l' => 'Amount', 'y' => 'num'],
                ['k' => 'st', 'l' => 'Status', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => ['amt'],
        ]];
    }
}
