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

use App\Http\Controllers\Controller;
use App\Models\Customer;
use App\Models\CustomerPayment;
use App\Models\Quotation;
use App\Models\SalesInvoice;
use App\Models\SalesOrder;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

/** Live data for the Command Centre — Customers & CRM module (Group scope: relationships span quarries). */
class CrmController extends Controller
{
    use Concerns\DashboardHelpers;

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

        $active = Customer::where('is_active', true)->count();
        $new = Customer::whereBetween('created_at', [$cf . ' 00:00:00', $ct . ' 23:59:59'])->count();
        $newP = Customer::whereBetween('created_at', [$pf . ' 00:00:00', $pt . ' 23:59:59'])->count();
        $debtors = (float) Customer::where('current_balance', '>', 0)->sum('current_balance');
        $overLim = Customer::whereColumn('current_balance', '>', 'credit_limit')->count();
        $actDebt = Customer::where('current_balance', '>', 0)->count();

        $pays = CustomerPayment::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;

        $qSent = Quotation::whereBetween('quotation_date', [$cf, $ct])->count();
        $qSentP = Quotation::whereBetween('quotation_date', [$pf, $pt])->count();
        $qVal = $this->sumBetween(Quotation::query(), 'quotation_date', 'grand_total', $cf, $ct);
        $qValP = $this->sumBetween(Quotation::query(), 'quotation_date', 'grand_total', $pf, $pt);
        $qTot = Quotation::count();
        $conv = $qTot > 0 ? round(Quotation::where('status', 'converted')->count() / $qTot * 100, 1) : 0;
        $pipe = \App\Services\CurrencyService::sumGhs(Quotation::whereNotIn('status', ['converted', 'cancelled', 'rejected']), 'grand_total');

        $ordN = SalesOrder::whereBetween('order_date', [$cf, $ct])->count();
        $ordV = $this->sumBetween(SalesOrder::query(), 'order_date', 'grand_total', $cf, $ct);
        $aov = $ordN > 0 ? round($ordV / $ordN) : 0;
        $ordCust = SalesOrder::distinct('customer_id')->count('customer_id');
        $repeatCust = SalesOrder::select('customer_id', DB::raw('count(*) n'))->groupBy('customer_id')->having('n', '>', 1)->count();
        $repeat = $ordCust > 0 ? round($repeatCust / $ordCust * 100, 1) : 0;

        $cards = [
            $this->card('Active Customers', $active, '#', $active, [], ['st' => 'blue']),
            $this->card('New Customers', $new, '#', $newP),
            $this->card('Debtors Owing', round($debtors), 'GHS', round($debtors), [], ['good' => 'down']),
            $this->card('Over Credit Limit', $overLim, '#', $overLim, [], ['good' => 'down', 'st' => $overLim > 0 ? 'red' : 'green']),
            $this->card('Avg Days to Pay', $avgPay, 'num', $avgPay, [], ['good' => 'down', 'tgt' => 30]),
            $this->card('Quotations Sent', $qSent, '#', $qSentP),
            $this->card('Quoted Value', round($qVal), 'GHS', round($qValP)),
            $this->card('Quote Conversion', $conv, '%', $conv, [], ['tgt' => 60]),
            $this->card('Open Pipeline', round($pipe), 'GHS', round($pipe), [], ['st' => 'blue']),
            $this->card('Active Debtors', $actDebt, '#', $actDebt, [], ['st' => 'blue']),
            $this->card('Avg Order Value', $aov, 'GHS', $aov),
            $this->card('Repeat Customer Rate', $repeat, '%', $repeat, [], ['good' => 'up']),
        ];

        return response()->json([
            'sub' => 'Customer master · pipeline · debtors & aging · Group · ' . $r->get('period', 'This Month'),
            'asAt' => Carbon::today()->format('d M Y'),
            'cards' => $cards,
            'sections' => [
                $this->secSales(),
                $this->secTop($cf, $ct),
                $this->secAging(),
                $this->secTypes(),
                $this->secCust(),
                $this->secQuote(),
                $this->secAgeDetail(),
            ],
        ]);
    }

    private function secSales(): array
    {
        $t = Carbon::today();
        $labels = [];
        $ord = [];
        $col = [];
        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');
            $ord[] = round(\App\Services\CurrencyService::sumGhs(SalesOrder::whereBetween('order_date', [$f, $e]), 'grand_total'));
            $col[] = round(\App\Services\CurrencyService::sumGhs(CustomerPayment::whereBetween('payment_date', [$f, $e]), 'amount'));
        }
        return ['k' => 'chart', 'id' => 'cr_sales', 'ti' => 'Orders vs Collections — GHS per month (12 months)', 'cls' => 'wide', 'spec' => [
            'type' => 'bar', 'fmt' => 'money',
            'labels' => $labels,
            'series' => [
                ['name' => 'Orders', 'data' => $ord, 'color' => '#3b82f6'],
                ['name' => 'Collected', 'data' => $col, 'color' => '#22c55e'],
            ],
        ]];
    }

    private function secTop($cf, $ct): array
    {
        $rows = SalesOrder::whereBetween('order_date', [$cf, $ct])
            ->select('customer_id', DB::raw('sum(grand_total * exchange_rate) t'))->groupBy('customer_id')->orderByDesc('t')->take(8)->get();
        $names = Customer::whereIn('id', $rows->pluck('customer_id'))->pluck('name', 'id');
        return ['k' => 'chart', 'id' => 'cr_top', 'ti' => 'Top Customers — order value (period)', 'spec' => [
            'type' => 'hbar', 'fmt' => 'money',
            'labels' => $rows->map(fn($r) => mb_strimwidth($names[$r->customer_id] ?? ('#' . $r->customer_id), 0, 24, '…'))->all(),
            'series' => [['name' => 'GHS', 'data' => $rows->map(fn($r) => round((float) $r->t))->all(), 'color' => '#3b82f6']],
        ]];
    }

    private function agingBuckets(): array
    {
        $today = Carbon::today();
        $b = ['Current' => 0, '1–30d' => 0, '31–60d' => 0, '61d+' => 0];
        $rows = SalesInvoice::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 $b;
    }

    private function secAging(): array
    {
        $b = $this->agingBuckets();
        return ['k' => 'chart', 'id' => 'cr_aging', 'ti' => 'Debtor 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' => '#f8c200']],
        ]];
    }

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

    private function secCust(): array
    {
        $rows = Customer::where('is_active', true)->orderBy('code')->get()->map(function ($c) {
            $bal = (float) $c->current_balance;
            $lim = (float) $c->credit_limit;
            $ordN = SalesOrder::where('customer_id', $c->id)->count();
            $ltv = \App\Services\CurrencyService::sumGhs(SalesOrder::where('customer_id', $c->id), 'grand_total');
            $last = SalesOrder::where('customer_id', $c->id)->max('order_date');
            return ['code' => $c->code, 'name' => $c->name, 'type' => ucfirst((string) $c->customer_type),
                'contact' => $c->contact_person ?? '', 'phone' => $c->phone ?? '', 'terms' => ($c->payment_terms_days ?? 0) . 'd',
                'limit' => round($lim), 'bal' => round($bal),
                'pct' => $lim > 0 ? round($bal / $lim * 100, 1) . '%' : '—',
                'orders' => $ordN, 'ltv' => round($ltv), 'last' => $last ? substr($last, 0, 10) : '—',
                'st' => $bal > $lim ? 'red' : ($bal > 0 ? 'amber' : 'green'),
                'stL' => $bal > $lim ? 'Over limit' : ($bal > 0 ? 'Owes' : 'Clear'),
                'open' => 'Open \u2192', 'openUrl' => '/app/customers/' . $c->id];
        })->all();
        return ['k' => 'table', 'id' => 'cr_cust', 'ti' => 'Customer Master — balances, limits & lifetime value', 'cls' => 'wide', 'cfg' => [
            'id' => 'cr_cust', 'page' => 10, 'search' => true,
            'cols' => [
                ['k' => 'code', 'l' => 'Code'], ['k' => 'name', 'l' => 'Customer', 'wide' => 1], ['k' => 'type', 'l' => 'Type'],
                ['k' => 'contact', 'l' => 'Contact'], ['k' => 'phone', 'l' => 'Phone'], ['k' => 'terms', 'l' => 'Terms'],
                ['k' => 'limit', 'l' => 'Limit', 'y' => 'num'], ['k' => 'bal', 'l' => 'Balance', 'y' => 'num'],
                ['k' => 'pct', 'l' => '% used'], ['k' => 'orders', 'l' => 'Orders', 'y' => 'num'],
                ['k' => 'ltv', 'l' => 'Lifetime', 'y' => 'num'], ['k' => 'last', 'l' => 'Last order'],
                ['k' => 'st', 'l' => 'Status', 'y' => 'badge'], ['k' => 'open', 'l' => 'Open', 'y' => 'link'],
            ], 'rows' => $rows, 'totals' => ['bal', 'ltv'],
        ]];
    }

    private function secQuote(): array
    {
        $rows = Quotation::with('customer')->orderByDesc('quotation_date')->take(30)->get()
            ->map(fn($q) => ['no' => $q->quotation_no, 'date' => substr((string) $q->quotation_date, 0, 10),
                'valid' => $q->valid_until ? substr((string) $q->valid_until, 0, 10) : '',
                'cust' => $q->customer->name ?? '', 'subj' => mb_strimwidth((string) $q->subject, 0, 50, '…'),
                'total' => round((float) $q->grand_total),
                'st' => $q->status === 'converted' ? 'green' : (in_array($q->status, ['cancelled', 'rejected', 'expired']) ? 'red' : 'amber'),
                'stL' => ucfirst((string) $q->status),
                'open' => 'Open \u2192', 'openUrl' => '/quotations/' . $q->id])->all();
        return ['k' => 'table', 'id' => 'cr_quote', 'ti' => 'Quotations — latest 30', 'cls' => 'wide', 'cfg' => [
            'id' => 'cr_quote', 'page' => 10, 'search' => true,
            'cols' => [
                ['k' => 'no', 'l' => 'Quote'], ['k' => 'date', 'l' => 'Date'], ['k' => 'valid', 'l' => 'Valid until'],
                ['k' => 'cust', 'l' => 'Customer', 'wide' => 1], ['k' => 'subj', 'l' => 'Subject'],
                ['k' => 'total', 'l' => 'Total', 'y' => 'num'], ['k' => 'st', 'l' => 'Status', 'y' => 'badge'],
                ['k' => 'open', 'l' => 'Open', 'y' => 'link'],
            ], 'rows' => $rows, 'totals' => ['total'],
        ]];
    }

    private function secAgeDetail(): array
    {
        $today = Carbon::today();
        $rows = SalesInvoice::with('customer')->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($today, false) : 0;
                return ['cust' => $v->customer->name ?? '', 'inv' => $v->invoice_no,
                    'date' => substr((string) $v->invoice_date, 0, 10),
                    'due' => $v->due_date ? substr((string) $v->due_date, 0, 10) : '',
                    'od' => $od <= 0 ? 'Current' : $od . 'd', 'bal' => round((float) $v->balance_due),
                    'st' => $od <= 0 ? 'green' : ($od <= 30 ? 'amber' : 'red'),
                    'stL' => $od <= 0 ? 'Current' : ($od <= 30 ? 'Overdue' : 'Serious'),
                    'open' => 'Open \u2192', 'openUrl' => '/sales-invoices/' . $v->id];
            })->all();
        return ['k' => 'table', 'id' => 'cr_age', 'ti' => 'Debtor Aging Detail — open invoices by due date', 'cls' => 'wide', 'cfg' => [
            'id' => 'cr_age', 'page' => 12, 'search' => true,
            'cols' => [
                ['k' => 'cust', 'l' => 'Customer', 'wide' => 1], ['k' => 'inv', 'l' => 'Invoice'],
                ['k' => 'date', 'l' => 'Date'], ['k' => 'due', 'l' => 'Due'], ['k' => 'od', 'l' => 'Overdue'],
                ['k' => 'bal', 'l' => 'Balance', 'y' => 'num'], ['k' => 'st', 'l' => 'Status', 'y' => 'badge'],
                ['k' => 'open', 'l' => 'Open', 'y' => 'link'],
            ], 'rows' => $rows, 'totals' => ['bal'],
        ]];
    }
}
