= ?'; $dateParams[] = $dateFrom . ' 00:00:00'; }
if ($dateTo) { $dateWhere .= ' AND r.created_at <= ?'; $dateParams[] = $dateTo . ' 23:59:59'; }
$responses = DB::all(
"SELECT r.*, COUNT(a.id) as answer_count
FROM survey_responses r
LEFT JOIN survey_answers a ON r.id = a.response_id
WHERE r.survey_id=? $dateWhere
GROUP BY r.id
ORDER BY r.created_at DESC
LIMIT $per_page OFFSET $offset",
$dateParams
);
$totalFiltered = DB::row("SELECT COUNT(*) c FROM survey_responses r WHERE r.survey_id=? $dateWhere", $dateParams)['c'];
$totalPages = ceil($totalFiltered / $per_page);
// Question-level aggregates (for chart data)
$questionStats = [];
foreach ($pages as $page) {
foreach ($page['questions'] as $q) {
$qid = $q['id'];
$answered = DB::row("SELECT COUNT(*) c FROM survey_answers WHERE question_id=? AND response_id IN (SELECT id FROM survey_responses WHERE survey_id=?)", [$qid, $surveyId])['c'];
$stats = ['question' => $q, 'answered' => $answered];
if (in_array($q['type'], ['single_choice','dropdown','yes_no'])) {
$vals = DB::all("SELECT answer_text, COUNT(*) cnt FROM survey_answers WHERE question_id=? AND answer_text IS NOT NULL GROUP BY answer_text ORDER BY cnt DESC", [$qid]);
$stats['distribution'] = $vals;
} elseif (in_array($q['type'], ['rating_scale','nps','star_rating','slider'])) {
$row = DB::row("SELECT AVG(answer_value) avg_val, MIN(answer_value) min_val, MAX(answer_value) max_val FROM survey_answers WHERE question_id=? AND answer_value IS NOT NULL", [$qid]);
$stats['avg'] = round($row['avg_val'] ?? 0, 2);
$stats['min'] = $row['min_val'] ?? 0;
$stats['max'] = $row['max_val'] ?? 0;
$dist = DB::all("SELECT answer_value val, COUNT(*) cnt FROM survey_answers WHERE question_id=? AND answer_value IS NOT NULL GROUP BY answer_value ORDER BY val", [$qid]);
$stats['distribution'] = $dist;
} elseif ($q['type'] === 'multi_choice') {
$vals = DB::all("SELECT answer_json FROM survey_answers WHERE question_id=? AND answer_json IS NOT NULL", [$qid]);
$counts = [];
foreach ($vals as $v) {
$arr = json_decode($v['answer_json'], true) ?? [];
foreach ($arr as $item) { $counts[$item] = ($counts[$item] ?? 0) + 1; }
}
arsort($counts);
$stats['distribution'] = array_map(fn($k,$v) => ['answer_text'=>$k,'cnt'=>$v], array_keys($counts), array_values($counts));
}
$questionStats[$qid] = $stats;
}
}
// Daily response trend (last 30 days)
$trend = DB::all(
"SELECT DATE(created_at) as dt, COUNT(*) cnt FROM survey_responses WHERE survey_id=? AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY DATE(created_at) ORDER BY dt",
[$surveyId]
);
// Device breakdown
$deviceStats = DB::all("SELECT device_type, COUNT(*) cnt FROM survey_responses WHERE survey_id=? GROUP BY device_type", [$surveyId]);
define('PAGE_TITLE', 'Results: ' . $survey['title']);
require_once __DIR__ . '/../includes/header.php';
?>