= ? AND created_at <= ?
GROUP BY DATE(created_at) ORDER BY dt",
[$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59']
);
// ── Responses by day ──────────────────────────────────────────
$responseTrend = DB::all(
"SELECT DATE(created_at) dt, COUNT(*) cnt FROM survey_responses
WHERE created_at >= ? AND created_at <= ?
GROUP BY DATE(created_at) ORDER BY dt",
[$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59']
);
// ── Revenue by day ────────────────────────────────────────────
$revenueTrend = DB::all(
"SELECT DATE(created_at) dt, SUM(amount) total FROM transactions
WHERE type='credit' AND status='completed' AND created_at >= ? AND created_at <= ?
GROUP BY DATE(created_at) ORDER BY dt",
[$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59']
);
// ── Summary totals for the period ─────────────────────────────
$newUsers = DB::row("SELECT COUNT(*) c FROM users WHERE role='client' AND created_at >= ? AND created_at <= ?", [$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59'])['c'];
$newSurveys = DB::row("SELECT COUNT(*) c FROM surveys WHERE created_at >= ? AND created_at <= ?", [$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59'])['c'];
$newResp = DB::row("SELECT COUNT(*) c FROM survey_responses WHERE created_at >= ? AND created_at <= ?", [$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59'])['c'];
$revenue = DB::row("SELECT IFNULL(SUM(amount),0) c FROM transactions WHERE type='credit' AND status='completed' AND created_at >= ? AND created_at <= ?", [$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59'])['c'];
// ── Top surveys by responses ──────────────────────────────────
$topSurveys = DB::all(
"SELECT s.title, s.response_count, s.complete_count, u.name owner
FROM surveys s JOIN users u ON s.user_id=u.id
ORDER BY s.response_count DESC LIMIT 10"
);
// ── Top clients by survey count ───────────────────────────────
$topClients = DB::all(
"SELECT u.name, u.email, p.name plan_name,
COUNT(s.id) survey_count, SUM(s.response_count) total_responses
FROM users u
JOIN plans p ON u.plan_id=p.id
LEFT JOIN surveys s ON s.user_id=u.id
WHERE u.role='client'
GROUP BY u.id ORDER BY total_responses DESC LIMIT 10"
);
// ── Question type usage ───────────────────────────────────────
$qtypeUsage = DB::all(
"SELECT type, COUNT(*) cnt FROM survey_questions GROUP BY type ORDER BY cnt DESC LIMIT 15"
);
// ── Device breakdown ──────────────────────────────────────────
$deviceBreakdown = DB::all(
"SELECT device_type, COUNT(*) cnt FROM survey_responses
WHERE created_at >= ? AND created_at <= ? GROUP BY device_type",
[$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59']
);
$totalDevice = max(1, array_sum(array_column($deviceBreakdown, 'cnt')));
require_once __DIR__ . '/../includes/admin_header.php';
?>