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

use App\Http\Controllers\Controller;
use App\Models\Account;
use App\Models\BankAccount;
use App\Models\Journal;
use App\Models\JournalLine;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

/** Live data for the Command Centre — Finance & Accounting module (Group scope: journals are not quarry-allocated). */
class FinanceController extends Controller
{
    use Concerns\DashboardHelpers;

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

        $rev = $this->flow('income', $cf, $ct);
        $revP = $this->flow('income', $pf, $pt);
        $exp = $this->flow('expense', $cf, $ct);
        $expP = $this->flow('expense', $pf, $pt);
        $profit = $rev - $exp;
        $profitP = $revP - $expP;
        $margin = $rev > 0 ? round($profit / $rev * 100, 1) : 0;
        $marginP = $revP > 0 ? round($profitP / $revP * 100, 1) : 0;

        $assets = (float) Account::where('account_type', 'asset')->sum('current_balance');
        $liab = (float) Account::where('account_type', 'liability')->sum('current_balance');
        $equity = (float) Account::where('account_type', 'equity')->sum('current_balance');
        $cash = (float) BankAccount::where('is_active', true)->sum('current_balance');
        $ar = (float) Account::where('code', '1100')->value('current_balance');
        $ap = (float) Account::where('code', '2000')->value('current_balance');
        $journals = Journal::where('status', 'posted')->whereBetween('journal_date', [$cf, $ct])->count();
        $journalsP = Journal::where('status', 'posted')->whereBetween('journal_date', [$pf, $pt])->count();
        $unbal = Journal::whereRaw('abs(total_debit - total_credit) > 0.005')->count();

        $cards = [
            $this->card('Revenue (' . $r->get('period', 'This Month') . ')', round($rev), 'GHS', round($revP), $this->sparkFlow('income', $grain)),
            $this->card('Expenses (' . $r->get('period', 'This Month') . ')', round($exp), 'GHS', round($expP), $this->sparkFlow('expense', $grain), ['good' => 'down']),
            $this->card('Net Profit', round($profit), 'GHS', round($profitP), [], ['st' => $profit >= 0 ? 'green' : 'red']),
            $this->card('Net Margin', $margin, '%', $marginP, [], ['tgt' => 15]),
            $this->card('Total Assets', round($assets), 'GHS', round($assets), [], ['st' => 'blue']),
            $this->card('Total Liabilities', round($liab), 'GHS', round($liab), [], ['good' => 'down']),
            $this->card('Equity', round($equity), 'GHS', round($equity), [], ['st' => 'blue']),
            $this->card('Cash & Bank', round($cash), 'GHS', round($cash), [], ['st' => 'blue']),
            $this->card('Receivables (GL)', round($ar), 'GHS', round($ar), [], ['good' => 'down']),
            $this->card('Payables (GL)', round($ap), 'GHS', round($ap), [], ['good' => 'down']),
            $this->card('Journals Posted', $journals, '#', $journalsP),
            $this->card('Unbalanced Journals', $unbal, '#', $unbal, [], ['good' => 'down', 'st' => $unbal > 0 ? 'red' : 'green']),
        ];

        return response()->json([
            'sub' => 'P&L · balance sheet · trial balance · journals · Group · ' . $r->get('period', 'This Month'),
            'asAt' => Carbon::today()->format('d M Y'),
            'cards' => $cards,
            'sections' => [
                $this->secPnlTrend(),
                $this->secRevMix($cf, $ct),
                $this->secExpTop($cf, $ct),
                $this->secPnlStmt($cf, $ct, $pf, $pt, $rev, $exp),
                $this->secBalanceSheet($assets, $liab, $equity),
                $this->secTrialBalance(),
                $this->secJournals(),
            ],
        ]);
    }

    /** Period flow for an account type from posted lines: income = Cr-Dr, expense = Dr-Cr. */
    private function flow($type, $f, $t): float
    {
        $expr = $type === 'income' ? 'sum(credit - debit)' : 'sum(debit - credit)';
        return (float) JournalLine::join('journals as j', 'j.id', '=', 'journal_lines.journal_id')
            ->join('accounts as a', 'a.id', '=', 'journal_lines.account_id')
            ->where('j.status', 'posted')->where('a.account_type', $type)
            ->whereBetween('j.journal_date', [$f, $t])->selectRaw("$expr v")->value('v');
    }

    private function sparkFlow($type, $grain): array
    {
        $t = Carbon::today();
        $out = [];
        for ($i = 11; $i >= 0; $i--) {
            if ($grain === 'month') {
                $d = $t->copy()->subMonths($i);
                $f = $d->copy()->startOfMonth()->format('Y-m-d');
                $e = $d->copy()->endOfMonth()->format('Y-m-d');
            } elseif ($grain === 'week') {
                $e = $t->copy()->subWeeks($i)->format('Y-m-d');
                $f = $t->copy()->subWeeks($i)->subDays(6)->format('Y-m-d');
            } else {
                $f = $e = $t->copy()->subDays($i)->format('Y-m-d');
            }
            $out[] = round($this->flow($type, $f, $e));
        }
        return $out;
    }

    private function secPnlTrend(): array
    {
        $t = Carbon::today();
        $rev = [];
        $exp = [];
        for ($i = 29; $i >= 0; $i--) {
            $d = $t->copy()->subDays($i)->format('Y-m-d');
            $rev[] = round($this->flow('income', $d, $d));
            $exp[] = round($this->flow('expense', $d, $d));
        }
        return ['k' => 'chart', 'id' => 'fl_trend', 'ti' => 'Revenue vs Expenses — GHS per day (30 days)', 'cls' => 'wide', 'spec' => [
            'type' => 'line', 'fmt' => 'money', 'zoom' => true, 'height' => 280,
            'labels' => $this->dailyLabels(30),
            'series' => [
                ['name' => 'Revenue', 'data' => $rev, 'color' => '#22c55e', 'area' => true],
                ['name' => 'Expenses', 'data' => $exp, 'color' => '#ef4444'],
            ],
        ]];
    }

    private function secRevMix($cf, $ct): array
    {
        $rows = JournalLine::join('journals as j', 'j.id', '=', 'journal_lines.journal_id')
            ->join('accounts as a', 'a.id', '=', 'journal_lines.account_id')
            ->where('j.status', 'posted')->where('a.account_type', 'income')
            ->whereBetween('j.journal_date', [$cf, $ct])
            ->select('a.name', DB::raw('sum(credit - debit) t'))->groupBy('a.name')->orderByDesc('t')->get();
        $pal = $this->palette();
        $items = $rows->values()->map(fn($r, $i) => ['label' => mb_strimwidth($r->name, 0, 30, '…'), 'value' => round((float) $r->t), 'color' => $pal[$i % count($pal)]])->all();
        return ['k' => 'chart', 'id' => 'fl_revmix', 'ti' => 'Revenue Mix — GHS (period)', 'spec' => [
            'type' => 'doughnut', 'fmt' => 'money', 'center' => 'Revenue', 'items' => $items,
        ]];
    }

    private function secExpTop($cf, $ct): array
    {
        $rows = JournalLine::join('journals as j', 'j.id', '=', 'journal_lines.journal_id')
            ->join('accounts as a', 'a.id', '=', 'journal_lines.account_id')
            ->where('j.status', 'posted')->where('a.account_type', 'expense')
            ->whereBetween('j.journal_date', [$cf, $ct])
            ->select('a.name', DB::raw('sum(debit - credit) t'))->groupBy('a.name')->orderByDesc('t')->take(8)->get();
        return ['k' => 'chart', 'id' => 'fl_exptop', 'ti' => 'Top Expense Lines — GHS (period)', 'spec' => [
            'type' => 'hbar', 'fmt' => 'money',
            'labels' => $rows->map(fn($r) => mb_strimwidth($r->name, 0, 24, '…'))->all(),
            'series' => [['name' => 'GHS', 'data' => $rows->map(fn($r) => round((float) $r->t))->all(), 'color' => '#ef4444']],
        ]];
    }

    private function secPnlStmt($cf, $ct, $pf, $pt, $rev, $exp): array
    {
        $rows = [];
        foreach (['income' => 'REVENUE', 'expense' => 'EXPENSES'] as $type => $head) {
            $rows[] = ['acc' => $head, 'code' => '', 'per' => '', 'prev' => '', 'chg' => '', 'st' => 'blue', 'stL' => '—'];
            $accts = Account::where('account_type', $type)->orderBy('code')->get();
            foreach ($accts as $a) {
                $p = $this->acctFlow($a->id, $type, $cf, $ct);
                $q = $this->acctFlow($a->id, $type, $pf, $pt);
                if (abs($p) < 0.005 && abs($q) < 0.005) continue;
                $rows[] = ['acc' => $a->name, 'code' => $a->code, 'per' => round($p), 'prev' => round($q),
                    'chg' => $q != 0 ? round(($p - $q) / abs($q) * 100, 1) . '%' : '—',
                    'st' => 'grey', 'stL' => ucfirst($type)];
            }
        }
        $rows[] = ['acc' => 'NET PROFIT', 'code' => '', 'per' => round($rev - $exp), 'prev' => '', 'chg' => '',
            'st' => ($rev - $exp) >= 0 ? 'green' : 'red', 'stL' => 'Net'];
        return ['k' => 'table', 'id' => 'fl_pnl', 'ti' => 'Profit & Loss — by ledger account (period vs previous)', 'cls' => 'wide', 'cfg' => [
            'id' => 'fl_pnl', 'page' => 20, 'search' => true,
            'cols' => [
                ['k' => 'code', 'l' => 'Code'], ['k' => 'acc', 'l' => 'Account', 'wide' => 1],
                ['k' => 'per', 'l' => 'Period', 'y' => 'num'], ['k' => 'prev', 'l' => 'Previous', 'y' => 'num'],
                ['k' => 'chg', 'l' => 'Change'], ['k' => 'st', 'l' => 'Type', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => [],
        ]];
    }

    private function acctFlow($id, $type, $f, $t): float
    {
        $expr = $type === 'income' ? 'sum(credit - debit)' : 'sum(debit - credit)';
        return (float) JournalLine::join('journals as j', 'j.id', '=', 'journal_lines.journal_id')
            ->where('j.status', 'posted')->where('account_id', $id)
            ->whereBetween('j.journal_date', [$f, $t])->selectRaw("$expr v")->value('v');
    }

    private function secBalanceSheet($assets, $liab, $equity): array
    {
        $rows = [];
        foreach (['asset' => 'ASSETS', 'liability' => 'LIABILITIES', 'equity' => 'EQUITY'] as $type => $head) {
            $rows[] = ['acc' => $head, 'code' => '', 'bal' => '', 'st' => 'blue', 'stL' => '—'];
            $sub = 0;
            foreach (Account::where('account_type', $type)->orderBy('code')->get() as $a) {
                $b = (float) $a->current_balance;
                $sub += $b;
                $rows[] = ['acc' => $a->name, 'code' => $a->code, 'bal' => round($b), 'st' => 'grey', 'stL' => ucfirst($type)];
            }
            $rows[] = ['acc' => 'TOTAL ' . $head, 'code' => '', 'bal' => round($sub), 'st' => 'green', 'stL' => 'Total'];
        }
        $inc = (float) Account::where('account_type', 'income')->sum('current_balance');
        $cappedExp = (float) Account::where('account_type', 'expense')->sum('current_balance');
        $check = round($assets - $liab - $equity - ($inc - $cappedExp));
        $rows[] = ['acc' => 'BALANCE CHECK (A = L + E + P&L)', 'code' => '', 'bal' => $check,
            'st' => abs($check) < 1 ? 'green' : 'red', 'stL' => abs($check) < 1 ? 'Balanced' : 'Off'];
        return ['k' => 'table', 'id' => 'fl_bs', 'ti' => 'Balance Sheet — live ledger balances', 'cls' => 'wide', 'cfg' => [
            'id' => 'fl_bs', 'page' => 25, 'search' => true,
            'cols' => [
                ['k' => 'code', 'l' => 'Code'], ['k' => 'acc', 'l' => 'Account', 'wide' => 1],
                ['k' => 'bal', 'l' => 'Balance', 'y' => 'num'], ['k' => 'st', 'l' => 'Type', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => [],
        ]];
    }

    private function secTrialBalance(): array
    {
        $td = 0;
        $tc = 0;
        $rows = Account::orderBy('code')->get()->map(function ($a) use (&$td, &$tc) {
            $b = (float) $a->current_balance;
            $debitNormal = in_array($a->account_type, ['asset', 'expense']);
            $dr = $debitNormal ? max($b, 0) : max(-$b, 0);
            $cr = $debitNormal ? max(-$b, 0) : max($b, 0);
            $td += $dr;
            $tc += $cr;
            return ['code' => $a->code, 'acc' => $a->name, 'type' => ucfirst($a->account_type),
                'dr' => round($dr, 2), 'cr' => round($cr, 2)];
        })->all();
        $rows[] = ['code' => '', 'acc' => 'TOTAL', 'type' => '', 'dr' => round($td, 2), 'cr' => round($tc, 2)];
        return ['k' => 'table', 'id' => 'fl_tb', 'ti' => 'Trial Balance — all ledger accounts (Dr must equal Cr)', 'cls' => 'wide', 'cfg' => [
            'id' => 'fl_tb', 'page' => 25, 'search' => true,
            'cols' => [
                ['k' => 'code', 'l' => 'Code'], ['k' => 'acc', 'l' => 'Account', 'wide' => 1], ['k' => 'type', 'l' => 'Type'],
                ['k' => 'dr', 'l' => 'Debit', 'y' => 'num'], ['k' => 'cr', 'l' => 'Credit', 'y' => 'num'],
            ], 'rows' => $rows, 'totals' => ['dr', 'cr'],
        ]];
    }

    private function secJournals(): array
    {
        $rows = Journal::orderByDesc('journal_date')->orderByDesc('id')->take(60)->get()
            ->map(fn($j) => ['no' => $j->journal_no, 'date' => substr((string) $j->journal_date, 0, 10),
                'ref' => $j->reference ?? '', 'narr' => mb_strimwidth((string) $j->narration, 0, 70, '…'),
                'src' => $j->source_module ?? 'manual',
                'dr' => round((float) $j->total_debit, 2), 'cr' => round((float) $j->total_credit, 2),
                'by' => $j->posted_by ?? '',
                'st' => $j->status === 'posted' ? 'green' : 'amber', 'stL' => ucfirst((string) $j->status)])->all();
        return ['k' => 'table', 'id' => 'fl_j', 'ti' => 'Journals — latest 60', 'cls' => 'wide', 'cfg' => [
            'id' => 'fl_j', 'page' => 12, 'search' => true,
            'cols' => [
                ['k' => 'no', 'l' => 'Journal'], ['k' => 'date', 'l' => 'Date'], ['k' => 'ref', 'l' => 'Reference'],
                ['k' => 'narr', 'l' => 'Narration', 'wide' => 1], ['k' => 'src', 'l' => 'Source'],
                ['k' => 'dr', 'l' => 'Debit', 'y' => 'num'], ['k' => 'cr', 'l' => 'Credit', 'y' => 'num'],
                ['k' => 'by', 'l' => 'Posted by'], ['k' => 'st', 'l' => 'Status', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => ['dr', 'cr'],
        ]];
    }
}
