<?php
/**
 * DashboardQueries — one home for every chart's data source across the
 * seven role dashboards.
 *
 * Kept as a single file (rather than one model per role) so a query used
 * by two dashboards — e.g. AR aging shows up on both Executive and
 * Finance Manager — only lives in one place. Each public method returns
 * a shape the ApexCharts wrapper (`PacificChart`) can consume directly:
 *   line/area/bar : ['categories' => [...], 'series' => [[...], ...]]
 *   donut/pie     : ['labels' => [...], 'series' => [n, n, n]]
 *   radialBar     : ['value' => float, 'label' => string]
 *   heatmap       : ['categories' => [...], 'series' => [{name, data:[{x,y},...]}]]
 *   rangeBar      : ['series' => [{data: [{x, y:[from,to]}]}]]
 */
class DashboardQueries extends Model
{
    private int $tenantId;

    public function __construct(?int $tenantId = null)
    {
        $this->tenantId = $tenantId ?? (defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1);
    }

    /* ══════════════════════════════════════════════════════════════════
       Executive
       ══════════════════════════════════════════════════════════════════ */

    public function revenueTrend12(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT DATE_FORMAT(je.entry_date, '%Y-%m') AS ym,
                    COALESCE(SUM(jl.credit - jl.debit), 0) AS rev
               FROM journal_lines jl
               JOIN journal_entries je    ON je.c0d = jl.journal_entry_id
                                          AND je.status = 'POSTED' AND je.deleted = 0
                                          AND je.entry_date >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 11 MONTH)
               JOIN chart_of_accounts coa ON coa.c0d = jl.account_id
              WHERE coa.account_type = 'REVENUE'
           GROUP BY ym", []
        ) ?: [];

        $byYm = [];
        foreach ($rs as $r) $byYm[$r['ym']] = (float)$r['rev'];

        $categories = $series = [];
        $today = new DateTimeImmutable('first day of this month');
        for ($i = 11; $i >= 0; $i--) {
            $t = $today->modify("-$i months");
            $ym = $t->format('Y-m');
            $categories[] = $t->format('M \'y');
            $series[]     = round($byYm[$ym] ?? 0, 2);
        }
        return ['categories' => $categories, 'series' => [['name' => 'Revenue', 'data' => $series]]];
    }

    public function cashCoverRatio(): array
    {
        $db = new DB();
        $cash = (float)($db->mySelect(
            "SELECT COALESCE(SUM(jl.debit - jl.credit), 0) AS bal
               FROM journal_lines jl
               JOIN journal_entries je    ON je.c0d = jl.journal_entry_id
                                          AND je.status = 'POSTED' AND je.deleted = 0
                                          AND je.entry_date <= CURDATE()
               JOIN chart_of_accounts coa ON coa.c0d = jl.account_id
              WHERE coa.is_bank = 1 OR coa.account_code IN ('3003','3006','3009')", []
        )[0]['bal'] ?? 0);
        $ap = (float)($db->mySelect(
            "SELECT COALESCE(SUM(total_amount - amount_paid), 0) AS ap
               FROM supplier_bills
              WHERE deleted = 0 AND status IN ('posted','paid')
                AND (total_amount - amount_paid) > 0.005 AND tenant_id = :t",
            [':t' => $this->tenantId]
        )[0]['ap'] ?? 0);

        $pct = $ap <= 0 ? 100 : min(300, (int)round(($cash / $ap) * 100));
        return ['value' => $pct, 'label' => 'Cash ÷ AP'];
    }

    public function arAgingDonut(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT
                SUM(CASE WHEN DATEDIFF(CURDATE(), COALESCE(due_date, invoice_date)) <= 0  THEN (total_amount - amount_paid) ELSE 0 END) AS current_,
                SUM(CASE WHEN DATEDIFF(CURDATE(), COALESCE(due_date, invoice_date)) BETWEEN 1 AND 30 THEN (total_amount - amount_paid) ELSE 0 END) AS d1_30,
                SUM(CASE WHEN DATEDIFF(CURDATE(), COALESCE(due_date, invoice_date)) BETWEEN 31 AND 60 THEN (total_amount - amount_paid) ELSE 0 END) AS d31_60,
                SUM(CASE WHEN DATEDIFF(CURDATE(), COALESCE(due_date, invoice_date)) BETWEEN 61 AND 90 THEN (total_amount - amount_paid) ELSE 0 END) AS d61_90,
                SUM(CASE WHEN DATEDIFF(CURDATE(), COALESCE(due_date, invoice_date)) > 90 THEN (total_amount - amount_paid) ELSE 0 END) AS d90_plus
               FROM customer_invoices
              WHERE deleted = 0 AND tenant_id = :t
                AND status IN ('posted','partially_paid')
                AND (total_amount - amount_paid) > 0.005",
            [':t' => $this->tenantId]
        );
        $r = $rs[0] ?? [];
        return [
            'labels' => ['Current', '1–30 d', '31–60 d', '61–90 d', 'Over 90 d'],
            'series' => [
                round((float)($r['current_']  ?? 0), 2),
                round((float)($r['d1_30']     ?? 0), 2),
                round((float)($r['d31_60']    ?? 0), 2),
                round((float)($r['d61_90']    ?? 0), 2),
                round((float)($r['d90_plus']  ?? 0), 2),
            ],
        ];
    }

    /** Revenue vs Expenses per month for the last 6 months. */
    public function revenueVsExpenses6(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT DATE_FORMAT(je.entry_date, '%Y-%m') AS ym, coa.account_type AS at,
                    COALESCE(SUM(CASE WHEN coa.account_type='REVENUE' THEN (jl.credit - jl.debit) ELSE (jl.debit - jl.credit) END), 0) AS amt
               FROM journal_lines jl
               JOIN journal_entries je    ON je.c0d = jl.journal_entry_id
                                          AND je.status = 'POSTED' AND je.deleted = 0
                                          AND je.entry_date >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 5 MONTH)
               JOIN chart_of_accounts coa ON coa.c0d = jl.account_id
              WHERE coa.account_type IN ('REVENUE','EXPENSE')
           GROUP BY ym, coa.account_type", []
        ) ?: [];

        $byYm = [];
        foreach ($rs as $r) $byYm[$r['ym']][$r['at']] = (float)$r['amt'];

        $today = new DateTimeImmutable('first day of this month');
        $categories = $rev = $exp = [];
        for ($i = 5; $i >= 0; $i--) {
            $t  = $today->modify("-$i months");
            $ym = $t->format('Y-m');
            $categories[] = $t->format('M');
            $rev[] = round($byYm[$ym]['REVENUE'] ?? 0, 2);
            $exp[] = round($byYm[$ym]['EXPENSE'] ?? 0, 2);
        }
        return ['categories' => $categories,
                'series' => [
                    ['name' => 'Revenue',  'data' => $rev],
                    ['name' => 'Expenses', 'data' => $exp],
                ]];
    }

    /* ══════════════════════════════════════════════════════════════════
       Finance Manager / Accountant
       ══════════════════════════════════════════════════════════════════ */

    /** Supplier-bill lifecycle counts, ordered draft → paid. */
    public function billLifecycleFunnel(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT status, COUNT(*) AS n
               FROM supplier_bills
              WHERE deleted = 0 AND tenant_id = :t
           GROUP BY status",
            [':t' => $this->tenantId]
        ) ?: [];

        $order = ['draft' => 0, 'posted' => 0, 'paid' => 0];
        foreach ($rs as $r) if (isset($order[$r['status']])) $order[$r['status']] = (int)$r['n'];
        return [
            'categories' => ['Draft', 'Posted', 'Paid'],
            'series' => [['name' => 'Bills', 'data' => array_values($order)]],
        ];
    }

    /** Daily cash balance for the last 60 days. */
    public function dailyCash60(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT je.entry_date AS d, COALESCE(SUM(jl.debit - jl.credit), 0) AS delta
               FROM journal_lines jl
               JOIN journal_entries je    ON je.c0d = jl.journal_entry_id
                                          AND je.status = 'POSTED' AND je.deleted = 0
                                          AND je.entry_date >= DATE_SUB(CURDATE(), INTERVAL 60 DAY)
               JOIN chart_of_accounts coa ON coa.c0d = jl.account_id
              WHERE coa.is_bank = 1 OR coa.account_code IN ('3003','3006','3009')
           GROUP BY je.entry_date
           ORDER BY je.entry_date", []
        ) ?: [];

        // Opening balance at (today − 60d)
        $opening = (float)($db->mySelect(
            "SELECT COALESCE(SUM(jl.debit - jl.credit), 0) AS bal
               FROM journal_lines jl
               JOIN journal_entries je    ON je.c0d = jl.journal_entry_id
                                          AND je.status = 'POSTED' AND je.deleted = 0
                                          AND je.entry_date < DATE_SUB(CURDATE(), INTERVAL 60 DAY)
               JOIN chart_of_accounts coa ON coa.c0d = jl.account_id
              WHERE coa.is_bank = 1 OR coa.account_code IN ('3003','3006','3009')", []
        )[0]['bal'] ?? 0);

        $byDate = [];
        foreach ($rs as $r) $byDate[$r['d']] = (float)$r['delta'];

        $today = new DateTimeImmutable('today');
        $categories = $series = [];
        $running = $opening;
        for ($i = 59; $i >= 0; $i--) {
            $d = $today->modify("-$i days");
            $ymd = $d->format('Y-m-d');
            $running += ($byDate[$ymd] ?? 0);
            $categories[] = $d->format('d M');
            $series[]     = round($running, 2);
        }
        return ['categories' => $categories, 'series' => [['name' => 'Cash', 'data' => $series]]];
    }

    /** Receipts vs supplier payments — monthly, last 6 months. */
    public function receiptsVsPayments6(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT DATE_FORMAT(receipt_date, '%Y-%m') AS ym, 'RCPT' AS kind, SUM(amount) AS amt
               FROM customer_receipts
              WHERE deleted = 0 AND status = 'recorded' AND tenant_id = :t
                AND receipt_date >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 5 MONTH)
           GROUP BY ym
             UNION ALL
             SELECT DATE_FORMAT(payment_date, '%Y-%m') AS ym, 'PAY' AS kind, SUM(amount) AS amt
               FROM supplier_payments
              WHERE deleted = 0 AND status = 'recorded' AND tenant_id = :t
                AND payment_date >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 5 MONTH)
           GROUP BY ym",
            [':t' => $this->tenantId]
        ) ?: [];

        $rcpt = $pay = [];
        foreach ($rs as $r) {
            if ($r['kind'] === 'RCPT') $rcpt[$r['ym']] = (float)$r['amt'];
            else                       $pay[$r['ym']]  = (float)$r['amt'];
        }
        $today = new DateTimeImmutable('first day of this month');
        $categories = $rr = $pp = [];
        for ($i = 5; $i >= 0; $i--) {
            $t = $today->modify("-$i months");
            $ym = $t->format('Y-m');
            $categories[] = $t->format('M');
            $rr[] = round($rcpt[$ym] ?? 0, 2);
            $pp[] = round($pay[$ym]  ?? 0, 2);
        }
        return ['categories' => $categories,
                'series' => [
                    ['name' => 'Receipts', 'data' => $rr],
                    ['name' => 'Payments', 'data' => $pp],
                ]];
    }

    /** Top-6 expense accounts YTD. */
    public function expenseMixDonut(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT coa.account_code, coa.account_name,
                    COALESCE(SUM(jl.debit - jl.credit), 0) AS amt
               FROM journal_lines jl
               JOIN journal_entries je    ON je.c0d = jl.journal_entry_id
                                          AND je.status = 'POSTED' AND je.deleted = 0
                                          AND je.entry_date >= DATE_FORMAT(CURDATE(), '%Y-01-01')
               JOIN chart_of_accounts coa ON coa.c0d = jl.account_id
              WHERE coa.account_type = 'EXPENSE'
           GROUP BY coa.c0d
           ORDER BY amt DESC
              LIMIT 6", []
        ) ?: [];
        return [
            'labels' => array_map(fn($r) => (string)$r['account_name'], $rs),
            'series' => array_map(fn($r) => round((float)$r['amt'], 2), $rs),
        ];
    }

    /* ══════════════════════════════════════════════════════════════════
       Fleet Manager
       ══════════════════════════════════════════════════════════════════ */

    /** % trucks currently on a trip (on_trip status ÷ available fleet). */
    public function fleetUtilisation(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT
                (SELECT COUNT(*) FROM trucks WHERE deleted = 0 AND tenant_id = :t AND status IN ('available','on_trip')) AS active,
                (SELECT COUNT(*) FROM trucks WHERE deleted = 0 AND tenant_id = :t AND status = 'on_trip') AS moving",
            [':t' => $this->tenantId]
        );
        $active = (int)($rs[0]['active'] ?? 0);
        $moving = (int)($rs[0]['moving'] ?? 0);
        $pct = $active > 0 ? (int)round(($moving / $active) * 100) : 0;
        return ['value' => $pct, 'label' => 'On-trip %'];
    }

    /** Km driven per truck over the last 30 days from GPS. */
    public function truckKm30(): array
    {
        // Compute per-truck total via haversine-friendly summing; approximate
        // using position deltas within the window. We fall back to trips.actual_km
        // if positions are sparse — many small fleets ping less than every 5 min.
        $db = new DB();
        $rows = $db->mySelect(
            "SELECT tk.c0d AS truck_id, tk.reg_no,
                    COALESCE(SUM(t.actual_km), 0) AS km
               FROM trucks tk
          LEFT JOIN trips  t ON t.truck_id = tk.c0d AND t.deleted = 0
                            AND t.status = 'closed'
                            AND t.actual_end >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
              WHERE tk.deleted = 0 AND tk.tenant_id = :t
           GROUP BY tk.c0d
           ORDER BY km DESC
              LIMIT 8",
            [':t' => $this->tenantId]
        ) ?: [];

        return [
            'categories' => array_map(fn($r) => (string)$r['reg_no'], $rows),
            'series' => [['name' => 'Km', 'data' => array_map(fn($r) => (int)$r['km'], $rows)]],
        ];
    }

    /** Doc-expiry composition: current / soon / expired across truck + driver docs + licence. */
    public function docExpiryComposition(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT
                (SELECT COUNT(*) FROM truck_docs   WHERE deleted = 0 AND tenant_id = :t
                    AND (expires_on IS NULL OR expires_on > DATE_ADD(CURDATE(), INTERVAL 30 DAY))) AS td_ok,
                (SELECT COUNT(*) FROM truck_docs   WHERE deleted = 0 AND tenant_id = :t
                    AND expires_on >= CURDATE() AND expires_on <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)) AS td_soon,
                (SELECT COUNT(*) FROM truck_docs   WHERE deleted = 0 AND tenant_id = :t
                    AND expires_on IS NOT NULL AND expires_on < CURDATE()) AS td_exp,
                (SELECT COUNT(*) FROM driver_docs  WHERE deleted = 0
                    AND (expires_on IS NULL OR expires_on > DATE_ADD(CURDATE(), INTERVAL 30 DAY))) AS dd_ok,
                (SELECT COUNT(*) FROM driver_docs  WHERE deleted = 0
                    AND expires_on >= CURDATE() AND expires_on <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)) AS dd_soon,
                (SELECT COUNT(*) FROM driver_docs  WHERE deleted = 0
                    AND expires_on IS NOT NULL AND expires_on < CURDATE()) AS dd_exp",
            [':t' => $this->tenantId]
        );
        $r = $rs[0] ?? [];
        $ok   = (int)($r['td_ok']   ?? 0) + (int)($r['dd_ok']   ?? 0);
        $soon = (int)($r['td_soon'] ?? 0) + (int)($r['dd_soon'] ?? 0);
        $exp  = (int)($r['td_exp']  ?? 0) + (int)($r['dd_exp']  ?? 0);
        return ['labels' => ['Current', 'Soon (≤ 30d)', 'Expired'], 'series' => [$ok, $soon, $exp]];
    }

    /** Trip departures heatmap · weekday × hour, last 60 days. */
    public function tripDepartureHeatmap(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT WEEKDAY(actual_start) AS dow, HOUR(actual_start) AS hr, COUNT(*) AS n
               FROM trips
              WHERE deleted = 0 AND tenant_id = :t
                AND actual_start >= DATE_SUB(CURDATE(), INTERVAL 60 DAY)
           GROUP BY dow, hr",
            [':t' => $this->tenantId]
        ) ?: [];

        $counts = [];
        foreach ($rs as $r) $counts[(int)$r['dow']][(int)$r['hr']] = (int)$r['n'];

        // Series = one row per day-of-week, points = 24 hours.
        $days = ['Mon','Tue','Wed','Thu','Fri','Sat','Sun'];
        $series = [];
        foreach ($days as $i => $dowName) {
            $data = [];
            for ($h = 0; $h < 24; $h++) {
                $data[] = ['x' => sprintf('%02d', $h), 'y' => $counts[$i][$h] ?? 0];
            }
            $series[] = ['name' => $dowName, 'data' => $data];
        }
        return ['series' => $series];
    }

    /* ══════════════════════════════════════════════════════════════════
       Ops Manager / Dispatcher
       ══════════════════════════════════════════════════════════════════ */

    /** Trip pipeline funnel: request → dispatched → in_transit → delivered. */
    public function tripPipelineFunnel(): array
    {
        $db = new DB();
        $tripsByStatus = [];
        $rs = $db->mySelect(
            "SELECT status, COUNT(*) AS n FROM trips
              WHERE deleted = 0 AND tenant_id = :t
           GROUP BY status",
            [':t' => $this->tenantId]
        ) ?: [];
        foreach ($rs as $r) $tripsByStatus[$r['status']] = (int)$r['n'];

        $requests = (int)($db->mySelect(
            "SELECT COUNT(*) AS n FROM trip_requests
              WHERE deleted = 0 AND tenant_id = :t
                AND status IN ('draft','pending_review','approved')",
            [':t' => $this->tenantId]
        )[0]['n'] ?? 0);

        return [
            'categories' => ['Open req', 'Dispatched', 'In transit', 'Delivered', 'Closed'],
            'series' => [['name' => 'Trips', 'data' => [
                $requests,
                $tripsByStatus['dispatched'] ?? 0,
                $tripsByStatus['in_transit'] ?? 0,
                $tripsByStatus['delivered']  ?? 0,
                $tripsByStatus['closed']     ?? 0,
            ]]],
        ];
    }

    public function onTimeRate30(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT COUNT(*) AS total,
                    SUM(CASE WHEN actual_end <= planned_delivery THEN 1 ELSE 0 END) AS ontime
               FROM trips
              WHERE deleted = 0 AND tenant_id = :t
                AND status = 'closed' AND actual_end IS NOT NULL
                AND planned_delivery IS NOT NULL
                AND actual_end >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)",
            [':t' => $this->tenantId]
        );
        $total  = (int)($rs[0]['total']  ?? 0);
        $ontime = (int)($rs[0]['ontime'] ?? 0);
        $pct = $total > 0 ? (int)round(($ontime / $total) * 100) : 0;
        return ['value' => $pct, 'label' => 'On-time %'];
    }

    public function topCustomersOpenTrips(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT c.c0d, TRIM(CONCAT(c.fname, ' ', c.lname)) AS name, COUNT(*) AS n
               FROM trips t
               JOIN customers c ON c.c0d = t.customer_id
              WHERE t.deleted = 0 AND t.tenant_id = :t
                AND t.status IN ('dispatched','in_transit','delivered')
           GROUP BY c.c0d
           ORDER BY n DESC
              LIMIT 5",
            [':t' => $this->tenantId]
        ) ?: [];
        return [
            'categories' => array_map(fn($r) => (string)$r['name'], $rs),
            'series' => [['name' => 'Open trips', 'data' => array_map(fn($r) => (int)$r['n'], $rs)]],
        ];
    }

    /** Today's dispatched-or-in-transit trip Gantt. */
    public function todayTripGantt(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT t.c0d, t.trip_no, tk.reg_no,
                    COALESCE(t.actual_start, t.planned_pickup)  AS start_ts,
                    COALESCE(t.planned_delivery, DATE_ADD(t.planned_pickup, INTERVAL 8 HOUR)) AS end_ts,
                    t.status
               FROM trips t
          LEFT JOIN trucks tk ON tk.c0d = t.truck_id
              WHERE t.deleted = 0 AND t.tenant_id = :t
                AND t.status IN ('dispatched','in_transit','delivered')
                AND (
                     (t.actual_start IS NOT NULL AND t.actual_start >= DATE_SUB(CURDATE(), INTERVAL 1 DAY))
                  OR (t.planned_pickup >= DATE_SUB(CURDATE(), INTERVAL 1 DAY))
                )
           ORDER BY start_ts
              LIMIT 12",
            [':t' => $this->tenantId]
        ) ?: [];

        $palette = ['dispatched' => '#2626F5', 'in_transit' => '#F59E0B', 'delivered' => '#0E9F6E'];
        $data = [];
        foreach ($rs as $t) {
            $data[] = [
                'x' => ($t['reg_no'] ?: '—') . ' · ' . $t['trip_no'],
                'y' => [strtotime($t['start_ts']) * 1000, strtotime($t['end_ts']) * 1000],
                'fillColor' => $palette[$t['status']] ?? '#6B7484',
            ];
        }
        return ['series' => [['data' => $data]]];
    }

    /* ══════════════════════════════════════════════════════════════════
       HR Officer
       ══════════════════════════════════════════════════════════════════ */

    public function netPayrollMonthly(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT DATE_FORMAT(pp.period_start, '%Y-%m') AS ym,
                    COALESCE(SUM(pr.net_pay), 0) AS net
               FROM payroll_records pr
               JOIN payroll_periods pp ON pp.c0d = pr.period_id
              WHERE pp.status IN ('approved','paid') AND pp.deleted = 0 AND pp.tenant_id = :t
                AND pp.period_start >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 5 MONTH)
           GROUP BY ym",
            [':t' => $this->tenantId]
        ) ?: [];

        $byYm = [];
        foreach ($rs as $r) $byYm[$r['ym']] = (float)$r['net'];

        $today = new DateTimeImmutable('first day of this month');
        $categories = $series = [];
        for ($i = 5; $i >= 0; $i--) {
            $t = $today->modify("-$i months");
            $categories[] = $t->format('M');
            $series[]     = round($byYm[$t->format('Y-m')] ?? 0, 2);
        }
        return ['categories' => $categories, 'series' => [['name' => 'Net pay', 'data' => $series]]];
    }

    public function statutoryDonut(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT
                COALESCE(SUM(pr.paye),     0) AS paye,
                COALESCE(SUM(pr.nssf_ee) + SUM(pr.nssf_er), 0) AS nssf,
                COALESCE(SUM(pr.sdl),      0) AS sdl,
                COALESCE(SUM(pr.wcf),      0) AS wcf
               FROM payroll_records pr
               JOIN payroll_periods pp ON pp.c0d = pr.period_id
              WHERE pp.status IN ('approved','paid') AND pp.deleted = 0 AND pp.tenant_id = :t
                AND pp.period_start >= DATE_FORMAT(CURDATE(), '%Y-01-01')",
            [':t' => $this->tenantId]
        );
        $r = $rs[0] ?? [];
        return [
            'labels' => ['PAYE', 'NSSF (ee+er)', 'SDL', 'WCF'],
            'series' => [
                round((float)($r['paye'] ?? 0), 2),
                round((float)($r['nssf'] ?? 0), 2),
                round((float)($r['sdl']  ?? 0), 2),
                round((float)($r['wcf']  ?? 0), 2),
            ],
        ];
    }

    public function headcountTrend6(): array
    {
        // Approximate: count drivers whose hire_date ≤ end-of-each-month and
        // who are not soft-deleted. Doesn't model terminations (would need
        // a driver_events table) — good enough for a trend line.
        $db = new DB();
        $today = new DateTimeImmutable('first day of this month');
        $categories = $series = [];
        for ($i = 5; $i >= 0; $i--) {
            $t = $today->modify("-$i months")->modify('last day of this month');
            $r = $db->mySelect(
                "SELECT COUNT(*) AS n FROM drivers
                  WHERE deleted = 0 AND tenant_id = :t
                    AND (hire_date IS NULL OR hire_date <= :d)",
                [':t' => $this->tenantId, ':d' => $t->format('Y-m-d')]
            );
            $categories[] = $t->format('M');
            $series[]     = (int)($r[0]['n'] ?? 0);
        }
        return ['categories' => $categories, 'series' => [['name' => 'Drivers', 'data' => $series]]];
    }

    public function docExpirySeverityBar(): array
    {
        $c = (new DashboardQueries($this->tenantId))->docExpiryComposition();
        // Reuse the same query but flip into a horizontal bar with severity order
        return [
            'categories' => ['Expired', 'Soon (≤ 30d)', 'Current'],
            'series' => [['name' => 'Count', 'data' => array_reverse($c['series'])]],
        ];
    }

    /* ══════════════════════════════════════════════════════════════════
       Fuel Officer
       ══════════════════════════════════════════════════════════════════ */

    public function weeklyFuelSpend(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT YEARWEEK(entry_date, 1) AS yw, COALESCE(SUM(gross_amount), 0) AS spend
               FROM fuel_entries
              WHERE deleted = 0 AND tenant_id = :t
                AND entry_date >= DATE_SUB(CURDATE(), INTERVAL 12 WEEK)
           GROUP BY yw
           ORDER BY yw",
            [':t' => $this->tenantId]
        ) ?: [];

        $byYw = [];
        foreach ($rs as $r) $byYw[(int)$r['yw']] = (float)$r['spend'];

        // Build 12 weekly buckets ending this week
        $today = new DateTimeImmutable('today');
        $categories = $series = [];
        for ($i = 11; $i >= 0; $i--) {
            $ts = $today->modify("-$i weeks");
            $yw = (int)$ts->format('YW');
            $categories[] = 'W' . $ts->format('W');
            $series[]     = round($byYw[$yw] ?? 0, 2);
        }
        return ['categories' => $categories, 'series' => [['name' => 'Fuel spend', 'data' => $series]]];
    }

    public function topLitres30(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT tk.reg_no, COALESCE(SUM(fe.liters), 0) AS l
               FROM fuel_entries fe
               JOIN trucks tk ON tk.c0d = fe.truck_id
              WHERE fe.deleted = 0 AND fe.tenant_id = :t
                AND fe.entry_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
           GROUP BY tk.c0d
           ORDER BY l DESC
              LIMIT 5",
            [':t' => $this->tenantId]
        ) ?: [];
        return [
            'categories' => array_map(fn($r) => (string)$r['reg_no'], $rs),
            'series' => [['name' => 'Litres', 'data' => array_map(fn($r) => round((float)$r['l'], 1), $rs)]],
        ];
    }

    public function fuelPaymentMethodDonut(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT payment_method AS pm, COALESCE(SUM(gross_amount), 0) AS amt
               FROM fuel_entries
              WHERE deleted = 0 AND tenant_id = :t
                AND entry_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
           GROUP BY pm",
            [':t' => $this->tenantId]
        ) ?: [];
        return [
            'labels' => array_map(fn($r) => ucfirst(str_replace('_', ' ', (string)$r['pm'])), $rs),
            'series' => array_map(fn($r) => round((float)$r['amt'], 2), $rs),
        ];
    }

    /* ══════════════════════════════════════════════════════════════════
       Procurement
       ══════════════════════════════════════════════════════════════════ */

    public function topSuppliersSpendYtd(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT v.vendor_name, COALESCE(SUM(sb.total_amount), 0) AS spend
               FROM supplier_bills sb
               JOIN vendors v ON v.c0d = sb.vendor_id
              WHERE sb.deleted = 0 AND sb.tenant_id = :t
                AND sb.status IN ('posted','paid')
                AND sb.bill_date >= DATE_FORMAT(CURDATE(), '%Y-01-01')
           GROUP BY v.c0d
           ORDER BY spend DESC
              LIMIT 10",
            [':t' => $this->tenantId]
        ) ?: [];
        return [
            'categories' => array_map(fn($r) => (string)$r['vendor_name'], $rs),
            'series' => [['name' => 'Spend', 'data' => array_map(fn($r) => round((float)$r['spend'], 2), $rs)]],
        ];
    }

    public function apAgingDonut(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT
                SUM(CASE WHEN DATEDIFF(CURDATE(), COALESCE(due_date, bill_date)) <= 0  THEN (total_amount - amount_paid) ELSE 0 END) AS current_,
                SUM(CASE WHEN DATEDIFF(CURDATE(), COALESCE(due_date, bill_date)) BETWEEN 1 AND 30 THEN (total_amount - amount_paid) ELSE 0 END) AS d1_30,
                SUM(CASE WHEN DATEDIFF(CURDATE(), COALESCE(due_date, bill_date)) BETWEEN 31 AND 60 THEN (total_amount - amount_paid) ELSE 0 END) AS d31_60,
                SUM(CASE WHEN DATEDIFF(CURDATE(), COALESCE(due_date, bill_date)) BETWEEN 61 AND 90 THEN (total_amount - amount_paid) ELSE 0 END) AS d61_90,
                SUM(CASE WHEN DATEDIFF(CURDATE(), COALESCE(due_date, bill_date)) > 90 THEN (total_amount - amount_paid) ELSE 0 END) AS d90_plus
               FROM supplier_bills
              WHERE deleted = 0 AND tenant_id = :t
                AND status IN ('posted','paid')
                AND (total_amount - amount_paid) > 0.005",
            [':t' => $this->tenantId]
        );
        $r = $rs[0] ?? [];
        return [
            'labels' => ['Current', '1–30 d', '31–60 d', '61–90 d', 'Over 90 d'],
            'series' => [
                round((float)($r['current_'] ?? 0), 2),
                round((float)($r['d1_30']    ?? 0), 2),
                round((float)($r['d31_60']   ?? 0), 2),
                round((float)($r['d61_90']   ?? 0), 2),
                round((float)($r['d90_plus'] ?? 0), 2),
            ],
        ];
    }

    public function billVsPaymentVolume6(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT DATE_FORMAT(bill_date, '%Y-%m') AS ym, 'BILL' AS kind, COUNT(*) AS n
               FROM supplier_bills
              WHERE deleted = 0 AND status IN ('posted','paid') AND tenant_id = :t
                AND bill_date >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 5 MONTH)
           GROUP BY ym
             UNION ALL
             SELECT DATE_FORMAT(payment_date, '%Y-%m') AS ym, 'PAY' AS kind, COUNT(*) AS n
               FROM supplier_payments
              WHERE deleted = 0 AND status = 'recorded' AND tenant_id = :t
                AND payment_date >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 5 MONTH)
           GROUP BY ym",
            [':t' => $this->tenantId]
        ) ?: [];

        $bills = $pays = [];
        foreach ($rs as $r) {
            if ($r['kind'] === 'BILL') $bills[$r['ym']] = (int)$r['n'];
            else                       $pays[$r['ym']]  = (int)$r['n'];
        }
        $today = new DateTimeImmutable('first day of this month');
        $categories = $bb = $pp = [];
        for ($i = 5; $i >= 0; $i--) {
            $t = $today->modify("-$i months");
            $ym = $t->format('Y-m');
            $categories[] = $t->format('M');
            $bb[] = $bills[$ym] ?? 0;
            $pp[] = $pays[$ym]  ?? 0;
        }
        return ['categories' => $categories,
                'series' => [
                    ['name' => 'Bills', 'data' => $bb],
                    ['name' => 'Payments', 'data' => $pp],
                ]];
    }

    /* ══════════════════════════════════════════════════════════════════
       KPI tiles — state right now (numbers) per role.
       Returned as an ordered array of tiles:
         ['label', 'value_html', 'sub', 'href' (optional), 'accent' (optional)]
       ══════════════════════════════════════════════════════════════════ */
    public function kpiTiles(string $flavour): array
    {
        $flavour = strtolower($flavour);
        switch ($flavour) {
            case 'exec':         return $this->kpiExec();
            case 'finance':      return $this->kpiFinance();
            case 'fleet':        return $this->kpiFleet();
            case 'ops':          return $this->kpiOps();
            case 'hr':           return $this->kpiHr();
            case 'fuel':         return $this->kpiFuel();
            case 'procurement':  return $this->kpiProcurement();
        }
        return [];
    }

    private function kpiExec(): array
    {
        $db = new DB();
        $s = (new PacificExecDashboard())->financialSummary();
        // Revenue MTD
        $mtd = (float)($db->mySelect(
            "SELECT COALESCE(SUM(jl.credit - jl.debit), 0) AS rev
               FROM journal_lines jl
               JOIN journal_entries    je  ON je.c0d = jl.journal_entry_id AND je.status='POSTED' AND je.deleted=0
                                          AND je.entry_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
               JOIN chart_of_accounts  coa ON coa.c0d = jl.account_id
              WHERE coa.account_type = 'REVENUE'", []
        )[0]['rev'] ?? 0);
        $closedM = (int)($db->mySelect(
            "SELECT COUNT(*) AS n FROM trips
              WHERE deleted=0 AND tenant_id=:t AND status='closed'
                AND actual_end >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)",
            [':t' => $this->tenantId]
        )[0]['n'] ?? 0);

        return [
            ['label' => 'Cash on hand',   'value' => 'TZS ' . number_format($s['cash'], 0), 'sub' => 'bank + cash + MoMo', 'accent' => 'primary'],
            ['label' => 'Receivables',    'value' => 'TZS ' . number_format($s['ar'],   0), 'sub' => 'customers owe us',    'accent' => 'success', 'href' => 'AgedReceivables'],
            ['label' => 'Payables',       'value' => 'TZS ' . number_format($s['ap'],   0), 'sub' => 'we owe suppliers',    'accent' => 'danger',  'href' => 'AgedPayables'],
            ['label' => 'Net position',   'value' => 'TZS ' . number_format($s['net'],  0), 'sub' => 'cash + AR − AP',      'accent' => 'primary'],
            ['label' => 'Revenue MTD',    'value' => 'TZS ' . number_format($mtd,       0), 'sub' => date('F'),             'accent' => 'success', 'href' => 'FinancialReports/pnl'],
            ['label' => 'Trips closed 30d','value'=> (string)$closedM,                       'sub' => 'delivery volume',     'accent' => 'info',    'href' => 'Trips'],
        ];
    }

    private function kpiFinance(): array
    {
        $db = new DB();
        $s   = (new PacificExecDashboard())->financialSummary();
        $unposted = (int)($db->mySelect(
            "SELECT COUNT(*) AS n FROM journal_entries WHERE status='DRAFT' AND deleted=0", []
        )[0]['n'] ?? 0);
        $unmatched = (int)($db->mySelect(
            "SELECT COUNT(*) AS n FROM bank_statement_lines
              WHERE matched_journal_line_id = 0 AND deleted = 0", []
        )[0]['n'] ?? 0);
        $expWait = (int)($db->mySelect(
            "SELECT COUNT(*) AS n FROM expenses
              WHERE deleted=0 AND tenant_id=:t
                AND status IN ('draft','submitted','reviewed','approved')",
            [':t' => $this->tenantId]
        )[0]['n'] ?? 0);
        return [
            ['label' => 'Cash on hand',       'value' => 'TZS ' . number_format($s['cash'], 0), 'accent' => 'primary'],
            ['label' => 'Receivables',        'value' => 'TZS ' . number_format($s['ar'],   0), 'accent' => 'success', 'href' => 'AgedReceivables'],
            ['label' => 'Payables',           'value' => 'TZS ' . number_format($s['ap'],   0), 'accent' => 'danger',  'href' => 'AgedPayables'],
            ['label' => 'Unposted JEs',       'value' => (string)$unposted,                      'sub' => 'draft entries',         'accent' => $unposted > 0 ? 'warning' : 'muted',   'href' => 'Journals'],
            ['label' => 'Unmatched bank',     'value' => (string)$unmatched,                     'sub' => 'reconciliation gap',    'accent' => $unmatched > 0 ? 'warning' : 'muted', 'href' => 'BankRecon'],
            ['label' => 'Expenses awaiting',  'value' => (string)$expWait,                       'sub' => 'review or approve',     'accent' => $expWait > 0 ? 'warning' : 'muted',   'href' => 'Expenses'],
        ];
    }

    private function kpiFleet(): array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT
                (SELECT COUNT(*) FROM trucks WHERE deleted=0 AND tenant_id=:t AND status IN ('available','on_trip')) AS trucks_active,
                (SELECT COUNT(*) FROM trucks WHERE deleted=0 AND tenant_id=:t AND status='maintenance') AS trucks_maint,
                (SELECT COUNT(*) FROM drivers WHERE deleted=0 AND tenant_id=:t) AS drivers,
                (SELECT COUNT(*) FROM driver_truck_assignments WHERE deleted=0 AND tenant_id=:t AND assigned_to IS NULL) AS assignments,
                (SELECT COUNT(*) FROM work_orders WHERE deleted=0 AND tenant_id=:t AND status IN ('open','in_progress')) AS open_wo,
                (SELECT COUNT(*) FROM truck_docs WHERE deleted=0 AND tenant_id=:t AND expires_on IS NOT NULL AND expires_on <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)) AS docs_soon",
            [':t' => $this->tenantId]
        );
        $x = $r[0] ?? [];
        return [
            ['label' => 'Trucks active',    'value' => (string)($x['trucks_active'] ?? 0), 'sub' => 'available + on trip', 'accent' => 'primary', 'href' => 'Trucks'],
            ['label' => 'In maintenance',   'value' => (string)($x['trucks_maint']  ?? 0), 'sub' => 'off-road',            'accent' => (int)($x['trucks_maint'] ?? 0) > 0 ? 'warning' : 'muted'],
            ['label' => 'Drivers',          'value' => (string)($x['drivers']       ?? 0), 'sub' => 'total roster',        'accent' => 'primary', 'href' => 'Drivers'],
            ['label' => 'Active assignments','value'=> (string)($x['assignments']   ?? 0), 'sub' => 'currently on truck',  'accent' => 'info',    'href' => 'DriverAssignments'],
            ['label' => 'Open work orders', 'value' => (string)($x['open_wo']       ?? 0), 'sub' => 'open + in-progress',  'accent' => (int)($x['open_wo'] ?? 0) > 0 ? 'warning' : 'muted', 'href' => 'WorkOrders'],
            ['label' => 'Docs expiring 30d','value' => (string)($x['docs_soon']     ?? 0), 'sub' => 'renew now',           'accent' => (int)($x['docs_soon'] ?? 0) > 0 ? 'danger' : 'success', 'href' => 'DocExpiries'],
        ];
    }

    private function kpiOps(): array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT
                (SELECT COUNT(*) FROM trip_requests WHERE deleted=0 AND tenant_id=:t AND status='pending_review') AS pending,
                (SELECT COUNT(*) FROM trip_requests WHERE deleted=0 AND tenant_id=:t AND status='approved')       AS approved,
                (SELECT COUNT(*) FROM trips WHERE deleted=0 AND tenant_id=:t AND status='in_transit')             AS in_transit,
                (SELECT COUNT(*) FROM trips WHERE deleted=0 AND tenant_id=:t AND status='delivered')              AS delivered,
                (SELECT COUNT(*) FROM trucks  WHERE deleted=0 AND tenant_id=:t AND status='available')            AS trucks_free,
                (SELECT COUNT(*) FROM drivers WHERE deleted=0 AND tenant_id=:t AND status='available')            AS drivers_free",
            [':t' => $this->tenantId]
        );
        $x = $r[0] ?? [];
        return [
            ['label' => 'Pending review',    'value' => (string)($x['pending']     ?? 0), 'sub' => 'trip requests',    'accent' => (int)($x['pending']    ?? 0) > 0 ? 'warning' : 'muted', 'href' => 'TripRequests'],
            ['label' => 'Approved',          'value' => (string)($x['approved']    ?? 0), 'sub' => 'awaiting dispatch','accent' => 'primary',                                                'href' => 'TripRequests'],
            ['label' => 'In transit',        'value' => (string)($x['in_transit']  ?? 0), 'sub' => 'trucks on the road','accent' => 'info',                                                  'href' => 'Trips'],
            ['label' => 'Delivered',         'value' => (string)($x['delivered']   ?? 0), 'sub' => 'awaiting close',   'accent' => (int)($x['delivered']  ?? 0) > 0 ? 'warning' : 'muted', 'href' => 'Trips'],
            ['label' => 'Trucks available',  'value' => (string)($x['trucks_free'] ?? 0), 'sub' => 'ready to dispatch','accent' => 'success',                                              'href' => 'Trucks'],
            ['label' => 'Drivers available', 'value' => (string)($x['drivers_free']?? 0), 'sub' => 'ready to dispatch','accent' => 'success',                                              'href' => 'Drivers'],
        ];
    }

    private function kpiHr(): array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT
                (SELECT COUNT(*) FROM drivers WHERE deleted=0 AND tenant_id=:t) AS drivers,
                (SELECT COUNT(*) FROM drivers WHERE deleted=0 AND tenant_id=:t AND status='on_leave')   AS on_leave,
                (SELECT COUNT(*) FROM driver_docs WHERE deleted=0 AND expires_on IS NOT NULL AND expires_on <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)) AS docs_soon,
                (SELECT COUNT(*) FROM drivers WHERE deleted=0 AND tenant_id=:t AND licence_expires IS NOT NULL AND licence_expires < CURDATE()) AS licences_exp,
                (SELECT status FROM payroll_periods WHERE deleted=0 AND tenant_id=:t ORDER BY period_start DESC LIMIT 1) AS last_period_status,
                (SELECT period_no FROM payroll_periods WHERE deleted=0 AND tenant_id=:t ORDER BY period_start DESC LIMIT 1) AS last_period_no,
                (SELECT COUNT(*) FROM drivers WHERE deleted=0 AND tenant_id=:t AND status='terminated') AS terminated_ct",
            [':t' => $this->tenantId]
        );
        $x = $r[0] ?? [];
        $lastStatus = (string)($x['last_period_status'] ?? '—');
        return [
            ['label' => 'Drivers on roster', 'value' => (string)($x['drivers']       ?? 0), 'sub' => 'active + on_trip + on_leave', 'accent' => 'primary', 'href' => 'Drivers'],
            ['label' => 'On leave',          'value' => (string)($x['on_leave']      ?? 0), 'sub' => 'today',                       'accent' => (int)($x['on_leave'] ?? 0) > 0 ? 'info' : 'muted'],
            ['label' => 'Docs expiring 30d', 'value' => (string)($x['docs_soon']     ?? 0), 'sub' => 'driver docs',                 'accent' => (int)($x['docs_soon'] ?? 0) > 0 ? 'warning' : 'success', 'href' => 'DocExpiries'],
            ['label' => 'Licences expired',  'value' => (string)($x['licences_exp']  ?? 0), 'sub' => 'urgent renew',                'accent' => (int)($x['licences_exp'] ?? 0) > 0 ? 'danger' : 'success'],
            ['label' => 'Payroll status',    'value' => ucfirst($lastStatus),                'sub' => (string)($x['last_period_no'] ?? '—'), 'accent' => $lastStatus === 'paid' ? 'success' : 'warning', 'href' => 'Payroll'],
            ['label' => 'Terminated (all)',  'value' => (string)($x['terminated_ct']    ?? 0), 'sub' => 'off-boarded',              'accent' => 'muted'],
        ];
    }

    private function kpiFuel(): array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT
                COALESCE(SUM(CASE WHEN entry_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01') THEN gross_amount ELSE 0 END), 0) AS mtd_amt,
                COALESCE(SUM(CASE WHEN entry_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01') THEN liters       ELSE 0 END), 0) AS mtd_l,
                COALESCE(SUM(CASE WHEN entry_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN liters     ELSE 0 END), 0) AS l_30
               FROM fuel_entries WHERE deleted=0 AND tenant_id=:t",
            [':t' => $this->tenantId]
        );
        $x = $r[0] ?? [];
        // Rough km approximation: sum trips.actual_km in same 30d
        $km30 = (float)($db->mySelect(
            "SELECT COALESCE(SUM(actual_km), 0) AS km FROM trips
              WHERE deleted=0 AND tenant_id=:t AND status='closed'
                AND actual_end >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)",
            [':t' => $this->tenantId]
        )[0]['km'] ?? 0);
        $costPerKm = $km30 > 0 ? ((float)$x['mtd_amt'] / $km30) : 0;
        // Fuel card balance (COA 3009)
        $card = (float)($db->mySelect(
            "SELECT COALESCE(SUM(jl.debit - jl.credit), 0) AS bal
               FROM journal_lines jl
               JOIN journal_entries    je  ON je.c0d = jl.journal_entry_id AND je.status='POSTED' AND je.deleted=0
               JOIN chart_of_accounts  coa ON coa.c0d = jl.account_id
              WHERE coa.account_code = '3009'", []
        )[0]['bal'] ?? 0);

        return [
            ['label' => 'Fuel MTD (TZS)',    'value' => 'TZS ' . number_format((float)$x['mtd_amt'], 0), 'sub' => date('F') . ' spend',   'accent' => 'primary'],
            ['label' => 'Litres MTD',        'value' => number_format((float)$x['mtd_l'], 0) . ' L',    'sub' => 'monthly consumption',  'accent' => 'info'],
            ['label' => 'Litres last 30d',   'value' => number_format((float)$x['l_30'], 0) . ' L',     'sub' => 'rolling',              'accent' => 'muted'],
            ['label' => 'Cost per km',       'value' => $km30 > 0 ? 'TZS ' . number_format($costPerKm, 0) : '—', 'sub' => 'MTD spend ÷ 30d km', 'accent' => 'primary'],
            ['label' => 'Fuel card balance', 'value' => 'TZS ' . number_format($card, 0), 'sub' => 'COA 3009',                        'accent' => $card < 0 ? 'danger' : 'success', 'href' => 'BankRecon'],
            ['label' => 'Fuel entries 30d',  'value' => (string)($db->mySelect(
                "SELECT COUNT(*) AS n FROM fuel_entries
                  WHERE deleted=0 AND tenant_id=:t AND entry_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)",
                [':t' => $this->tenantId]
            )[0]['n'] ?? 0),                                                            'sub' => 'fill-ups logged',  'accent' => 'muted', 'href' => 'FuelEntries'],
        ];
    }

    private function kpiProcurement(): array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT
                COALESCE(SUM(CASE WHEN status IN ('posted') THEN (total_amount - amount_paid) ELSE 0 END), 0) AS ap_total,
                COUNT(CASE WHEN status IN ('posted') AND DATEDIFF(CURDATE(), COALESCE(due_date, bill_date)) > 0 AND (total_amount - amount_paid) > 0.005 THEN 1 END) AS overdue,
                COALESCE(SUM(CASE WHEN status IN ('posted') AND DATEDIFF(CURDATE(), COALESCE(due_date, bill_date)) > 0 THEN (total_amount - amount_paid) ELSE 0 END), 0) AS overdue_amt,
                COUNT(CASE WHEN status = 'draft' THEN 1 END) AS drafts,
                COUNT(CASE WHEN status IN ('posted') AND (total_amount - amount_paid) > 0.005 THEN 1 END) AS open_count
               FROM supplier_bills WHERE deleted=0 AND tenant_id=:t",
            [':t' => $this->tenantId]
        );
        $x = $r[0] ?? [];
        $suppliers = (int)($db->mySelect(
            "SELECT COUNT(*) AS n FROM vendors WHERE deleted=0 AND status='ACTIVE'", []
        )[0]['n'] ?? 0);
        return [
            ['label' => 'Payables total',     'value' => 'TZS ' . number_format((float)($x['ap_total'] ?? 0), 0), 'sub' => 'open bills',        'accent' => 'primary',                             'href' => 'AgedPayables'],
            ['label' => 'Overdue',            'value' => 'TZS ' . number_format((float)($x['overdue_amt'] ?? 0), 0), 'sub' => (int)($x['overdue'] ?? 0) . ' bill(s)', 'accent' => (int)($x['overdue'] ?? 0) > 0 ? 'danger' : 'success', 'href' => 'AgedPayables'],
            ['label' => 'Drafts to post',     'value' => (string)($x['drafts'] ?? 0), 'sub' => 'awaiting review', 'accent' => (int)($x['drafts'] ?? 0) > 0 ? 'warning' : 'muted', 'href' => 'SupplierBills'],
            ['label' => 'Open bills',         'value' => (string)($x['open_count'] ?? 0), 'sub' => 'posted, unpaid', 'accent' => 'info', 'href' => 'SupplierBills'],
            ['label' => 'Active suppliers',   'value' => (string)$suppliers, 'sub' => 'in the master', 'accent' => 'primary', 'href' => 'Suppliers'],
            ['label' => 'YTD spend',          'value' => 'TZS ' . number_format((float)($db->mySelect(
                "SELECT COALESCE(SUM(total_amount), 0) AS n FROM supplier_bills
                  WHERE deleted=0 AND tenant_id=:t AND status IN ('posted','paid')
                    AND bill_date >= DATE_FORMAT(CURDATE(), '%Y-01-01')",
                [':t' => $this->tenantId]
            )[0]['n'] ?? 0), 0), 'sub' => date('Y'), 'accent' => 'muted'],
        ];
    }

    /* ══════════════════════════════════════════════════════════════════
       Role-tuned activity feeds
       ══════════════════════════════════════════════════════════════════ */

    public function recentTrips(int $limit = 6): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT t.c0d, t.trip_no, t.status,
                    TRIM(CONCAT(c.fname, ' ', c.lname)) AS customer_name,
                    tk.reg_no AS truck_reg,
                    d.full_name AS driver_name,
                    COALESCE(t.actual_start, t.planned_pickup) AS ts,
                    t.origin_address, t.destination_address
               FROM trips t
          LEFT JOIN customers c ON c.c0d = t.customer_id
          LEFT JOIN trucks   tk ON tk.c0d = t.truck_id
          LEFT JOIN drivers  d  ON d.c0d = t.driver_id
              WHERE t.deleted = 0 AND t.tenant_id = :t
           ORDER BY COALESCE(t.actual_start, t.planned_pickup) DESC
              LIMIT $limit",
            [':t' => $this->tenantId]
        ) ?: [];
    }

    public function recentSupplierBills(int $limit = 6): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT sb.c0d, sb.bill_no, sb.bill_date, sb.total_amount, sb.amount_paid, sb.status,
                    v.vendor_name
               FROM supplier_bills sb
          LEFT JOIN vendors v ON v.c0d = sb.vendor_id
              WHERE sb.deleted=0 AND sb.tenant_id=:t
           ORDER BY sb.bill_date DESC, sb.c0d DESC
              LIMIT $limit",
            [':t' => $this->tenantId]
        ) ?: [];
    }

    public function recentFuelEntries(int $limit = 6): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT fe.c0d, fe.entry_no, fe.entry_date, fe.liters, fe.gross_amount, fe.payment_method,
                    tk.reg_no AS truck_reg, d.full_name AS driver_name
               FROM fuel_entries fe
          LEFT JOIN trucks  tk ON tk.c0d = fe.truck_id
          LEFT JOIN drivers d  ON d.c0d = fe.driver_id
              WHERE fe.deleted=0 AND fe.tenant_id=:t
           ORDER BY fe.entry_date DESC, fe.c0d DESC
              LIMIT $limit",
            [':t' => $this->tenantId]
        ) ?: [];
    }

    public function recentAssignmentsHr(int $limit = 6): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT da.c0d, da.assigned_from, da.assigned_to, da.role_type,
                    d.full_name AS driver_name, tk.reg_no AS truck_reg
               FROM driver_truck_assignments da
          LEFT JOIN drivers d  ON d.c0d = da.driver_id
          LEFT JOIN trucks  tk ON tk.c0d = da.truck_id
              WHERE da.deleted=0 AND da.tenant_id=:t
           ORDER BY da.assigned_from DESC
              LIMIT $limit",
            [':t' => $this->tenantId]
        ) ?: [];
    }

    public function requestsNeedingAttention(int $limit = 6): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT tr.c0d, tr.request_no, tr.status,
                    TRIM(CONCAT(c.fname, ' ', c.lname)) AS customer_name,
                    tr.origin_address AS origin_city, tr.destination_address AS destination_city, tr.pickup_date AS planned_pickup
               FROM trip_requests tr
          LEFT JOIN customers c ON c.c0d = tr.customer_id
              WHERE tr.deleted=0 AND tr.tenant_id=:t
                AND tr.status IN ('pending_review','approved')
           ORDER BY tr.pickup_date ASC
              LIMIT $limit",
            [':t' => $this->tenantId]
        ) ?: [];
    }

    public function complianceUpcoming(int $limit = 6): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM (
                SELECT 'truck_doc' AS kind, td.c0d, dt.display_name AS doc_type,
                       t.reg_no AS about, td.expires_on,
                       DATEDIFF(td.expires_on, CURDATE()) AS days
                  FROM truck_docs td
             LEFT JOIN truck_doc_types dt ON dt.c0d = td.doc_type_id
             LEFT JOIN trucks t ON t.c0d = td.truck_id
                 WHERE td.deleted=0 AND td.tenant_id=:t
                   AND td.expires_on IS NOT NULL
                UNION ALL
                SELECT 'driver_doc' AS kind, dd.c0d, ddt.display_name AS doc_type,
                       d.full_name AS about, dd.expires_on,
                       DATEDIFF(dd.expires_on, CURDATE()) AS days
                  FROM driver_docs dd
             LEFT JOIN driver_doc_types ddt ON ddt.c0d = dd.doc_type_id
             LEFT JOIN drivers d ON d.c0d = dd.driver_id
                 WHERE dd.deleted=0
                   AND dd.expires_on IS NOT NULL
             ) x
             ORDER BY days ASC
             LIMIT $limit",
            [':t' => $this->tenantId]
        ) ?: [];
    }

    public function avgDaysToPayTrend(): array
    {
        // Avg (payment_date - bill_date) per month, weighted by allocation
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT DATE_FORMAT(sp.payment_date, '%Y-%m') AS ym,
                    AVG(DATEDIFF(sp.payment_date, sb.bill_date)) AS days
               FROM supplier_payment_allocations spa
               JOIN supplier_bills    sb ON sb.c0d = spa.bill_id
               JOIN supplier_payments sp ON sp.c0d = spa.payment_id
              WHERE sp.status = 'recorded' AND sp.deleted = 0 AND sp.tenant_id = :t
                AND sp.payment_date >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 5 MONTH)
           GROUP BY ym",
            [':t' => $this->tenantId]
        ) ?: [];
        $byYm = [];
        foreach ($rs as $r) $byYm[$r['ym']] = round((float)$r['days'], 1);

        $today = new DateTimeImmutable('first day of this month');
        $categories = $series = [];
        for ($i = 5; $i >= 0; $i--) {
            $t = $today->modify("-$i months");
            $ym = $t->format('Y-m');
            $categories[] = $t->format('M');
            $series[]     = $byYm[$ym] ?? 0;
        }
        return ['categories' => $categories, 'series' => [['name' => 'Avg days', 'data' => $series]]];
    }
}
