(int)db_value("SELECT COUNT(*) FROM members"),
'members_active' => (int)db_value("SELECT COUNT(*) FROM members WHERE status='active'"),
'members_pending' => (int)db_value("SELECT COUNT(*) FROM members WHERE status='pending'"),
'members_cancelled' => (int)db_value("SELECT COUNT(*) FROM members WHERE status='cancelled'"),
'invoices_unpaid' => (int)db_value("SELECT COUNT(*) FROM invoices WHERE status IN ('unpaid','overdue')"),
'invoices_overdue' => (int)db_value("SELECT COUNT(*) FROM invoices WHERE status='overdue'"),
'claims_pending' => (int)db_value("SELECT COUNT(*) FROM business_claims WHERE status='pending'"),
'subs_active' => (int)db_value("SELECT COUNT(*) FROM payment_tokens WHERE status='active'"),
];
// Journey summary — active members only
require_once __DIR__ . '/../includes/journey.php';
$active_member_ids = array_column(
db_all("SELECT id FROM members WHERE status='active'"),
'id'
);
$journey_summaries = journey_summaries_for_members($active_member_ids);
$j_done = 0; $j_in_prog = 0; $j_not_started = 0; $j_avg = 0;
foreach ($journey_summaries as $js) {
if ($js['percent'] === 100) $j_done++;
elseif ($js['percent'] > 0) $j_in_prog++;
else $j_not_started++;
$j_avg += $js['percent'];
}
$j_total = count($journey_summaries);
$j_avg = $j_total ? (int)round($j_avg / $j_total) : 0;
// Top "needs attention" members — anything not at 100%.
// Ordering priority:
// 0 = not started (highest priority — brand new, hasn't started)
// 1 = in progress (has active steps right now — may be stalled)
// 2 = partial (some done, rest pending, nothing currently in progress)
$journey_attention = db_all(
"SELECT m.id, m.business_name, m.tier,
(SELECT COUNT(*) FROM member_journey mj2 JOIN journey_steps js2 ON js2.id=mj2.step_id
WHERE mj2.member_id=m.id AND js2.active=1 AND mj2.status='done') AS done_count,
(SELECT COUNT(*) FROM journey_steps js3 WHERE js3.tier=m.tier AND js3.active=1) AS total_count,
(SELECT COUNT(*) FROM member_journey mj4 JOIN journey_steps js4 ON js4.id=mj4.step_id
WHERE mj4.member_id=m.id AND js4.active=1 AND mj4.status='in_progress') AS in_progress_count,
(SELECT MAX(mj3.updated_at) FROM member_journey mj3
WHERE mj3.member_id=m.id) AS last_activity,
m.created_at
FROM members m
WHERE m.status='active'
AND (SELECT COUNT(*) FROM journey_steps jst WHERE jst.tier=m.tier AND jst.active=1) > 0
-- Show if journey isn't complete: done_count < total_count
AND (
SELECT COUNT(*) FROM member_journey mjy JOIN journey_steps jsy ON jsy.id=mjy.step_id
WHERE mjy.member_id=m.id AND jsy.active=1 AND mjy.status='done'
) < (
SELECT COUNT(*) FROM journey_steps jst2 WHERE jst2.tier=m.tier AND jst2.active=1
)
ORDER BY
CASE
WHEN NOT EXISTS (
SELECT 1 FROM member_journey mjz
JOIN journey_steps jsz ON jsz.id=mjz.step_id
WHERE mjz.member_id=m.id AND jsz.active=1
AND mjz.status IN ('done','in_progress')
) THEN 0 -- Not started
WHEN EXISTS (
SELECT 1 FROM member_journey mja
WHERE mja.member_id=m.id AND mja.status='in_progress'
) THEN 1 -- In progress
ELSE 2 -- Partial (some done, rest pending)
END,
last_activity ASC,
m.created_at DESC
LIMIT 8"
);
$mrr = (int)db_value(
"SELECT COALESCE(SUM(i.amount_cents),0)
FROM invoices i
INNER JOIN (SELECT member_id, MAX(id) AS max_id FROM invoices
WHERE type='membership' AND status='paid' GROUP BY member_id) latest
ON latest.max_id=i.id
INNER JOIN payment_tokens pt ON pt.member_id=i.member_id
WHERE pt.status='active'"
);
$new_members = db_all(
"SELECT id, email, first_name, last_name, business_name, tier, status, created_at
FROM members
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
ORDER BY created_at DESC LIMIT 50"
);
$unpaid = db_all(
"SELECT DISTINCT m.id, m.email, m.business_name, m.first_name, m.last_name, m.tier,
(SELECT COUNT(*) FROM invoices WHERE member_id=m.id AND status IN ('unpaid','overdue')) AS invoice_count,
(SELECT COALESCE(SUM(amount_cents),0) FROM invoices WHERE member_id=m.id AND status IN ('unpaid','overdue')) AS amount_due
FROM members m
JOIN invoices i ON i.member_id=m.id
WHERE i.status IN ('unpaid','overdue') AND i.issued_at >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
ORDER BY amount_due DESC LIMIT 50"
);
$active_carts = db_all(
"SELECT o.id, o.status, o.total_cents, o.updated_at,
m.id AS member_id, m.email, m.business_name,
(SELECT COUNT(*) FROM order_items WHERE order_id=o.id) AS items
FROM orders o
JOIN members m ON m.id=o.member_id
WHERE o.status IN ('cart','pending_payment') AND o.total_cents>0
ORDER BY o.updated_at DESC LIMIT 50"
);
function cr(int $c): string { return 'R '.number_format($c/100, 2, '.', ' '); }
?>