<?php
require_once '../includes/config.php';
requireAdmin();

$type = $_GET['type'] ?? 'csv';
$month = $_GET['month'] ?? date('Y-m');

// Gather data
$studyHours = $pdo->query("SELECT DATE(created_at) as d, SUM(duration_minutes)/60 as hrs FROM study_sessions GROUP BY DATE(created_at) ORDER BY d DESC LIMIT 30")->fetchAll();
$topUsers   = $pdo->query("SELECT u.name, u.email, COUNT(ss.id) as sessions, COALESCE(SUM(ss.duration_minutes),0) as total_mins FROM users u LEFT JOIN study_sessions ss ON u.id=ss.user_id WHERE u.is_verified=1 GROUP BY u.id ORDER BY total_mins DESC LIMIT 20")->fetchAll();
$totalUsers = $pdo->query("SELECT COUNT(*) FROM users WHERE is_verified=1")->fetchColumn();
$totalSessions = $pdo->query("SELECT COUNT(*) FROM study_sessions")->fetchColumn();
$totalHours = round($pdo->query("SELECT SUM(duration_minutes)/60 FROM study_sessions")->fetchColumn(), 1);

header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename="studytrack_report_'.date('Y-m-d').'.csv"');
$out = fopen('php://output','w');

fputcsv($out,['StudyTrack Monthly Report — Generated: '.date('Y-m-d H:i')]);
fputcsv($out,[]);
fputcsv($out,['Summary']);
fputcsv($out,['Total Verified Users', $totalUsers]);
fputcsv($out,['Total Study Sessions', $totalSessions]);
fputcsv($out,['Total Study Hours (Platform)', $totalHours]);
fputcsv($out,[]);
fputcsv($out,['Top 20 Students by Study Time']);
fputcsv($out,['Name','Email','Sessions','Total Hours']);
foreach($topUsers as $u) fputcsv($out,[$u['name'],$u['email'],$u['sessions'],round($u['total_mins']/60,1)]);
fputcsv($out,[]);
fputcsv($out,['Daily Study Hours (Last 30 Days)']);
fputcsv($out,['Date','Hours']);
foreach($studyHours as $r) fputcsv($out,[$r['d'],round($r['hrs'],2)]);

fclose($out);
exit;
