<?php
declare(strict_types=1);

require __DIR__ . '/../partials/auth.php';

$pageTitle = 'Reports';
$pageSubtitle = 'Student learning progress and XP reports';
$activeTeacherNav = 'reports';

require_once __DIR__ . '/../../config/database.php';
$pdo = getDbConnection();

function h(string $value): string {
    return htmlspecialchars($value, ENT_QUOTES, 'UTF-8');
}

function reportTeacherScope(PDO $pdo): array {
    $role=(string)($_SESSION['user_role']??'teacher');
    if($role==='admin') return ['role'=>'admin','ready'=>true,'province_id'=>0,'school_id'=>0,'province'=>'','school'=>''];
    $q=$pdo->prepare("SELECT tp.province_id,tp.school_id,tp.province,tp.school_name,p.name province_master,s.name school_master FROM teacher_profiles tp LEFT JOIN provinces p ON p.id=tp.province_id LEFT JOIN schools s ON s.id=tp.school_id WHERE tp.user_id=? LIMIT 1");
    $q->execute([(int)($_SESSION['user_id']??0)]); $r=$q->fetch(PDO::FETCH_ASSOC)?:[];
    $pid=(int)($r['province_id']??0); $sid=(int)($r['school_id']??0);
    $pn=trim((string)($r['province_master']??$r['province']??'')); $sn=trim((string)($r['school_master']??$r['school_name']??''));
    return ['role'=>'teacher','ready'=>(($pid>0&&$sid>0)||($pn!==''&&$sn!=='')),'province_id'=>$pid,'school_id'=>$sid,'province'=>$pn,'school'=>$sn];
}
function reportStudentScopeSql(array $s,string $a='s'): string {
    if(($s['role']??'')!=='teacher') return ''; if(empty($s['ready'])) return ' AND 1=0';
    return ((int)$s['province_id']>0&&(int)$s['school_id']>0) ? " AND {$a}.province_id=? AND {$a}.school_id=?" : " AND TRIM({$a}.province)=? AND TRIM({$a}.school_name)=?";
}
function reportStudentScopeParams(array $s): array {
    if(($s['role']??'')!=='teacher'||empty($s['ready'])) return [];
    return ((int)$s['province_id']>0&&(int)$s['school_id']>0) ? [(int)$s['province_id'],(int)$s['school_id']] : [(string)$s['province'],(string)$s['school']];
}

function tableExists(PDO $pdo, string $table): bool {
    $stmt = $pdo->prepare(
        "SELECT COUNT(*)
         FROM information_schema.TABLES
         WHERE TABLE_SCHEMA = DATABASE()
           AND TABLE_NAME = ?"
    );
    $stmt->execute([$table]);
    return (int)$stmt->fetchColumn() > 0;
}

function tableColumns(PDO $pdo, string $table): array {
    if (!tableExists($pdo, $table)) return [];
    $rows = $pdo->query("SHOW COLUMNS FROM `" . str_replace('`', '``', $table) . "`")
        ->fetchAll(PDO::FETCH_ASSOC);
    $out = [];
    foreach ($rows as $row) {
        $name = (string)($row['Field'] ?? '');
        if ($name !== '') $out[$name] = true;
    }
    return $out;
}

function academicYearLabelColumn(PDO $pdo): ?string {
    $cols = tableColumns($pdo, 'academic_years');
    foreach (['name','year_name','label','title','school_year'] as $candidate) {
        if (isset($cols[$candidate])) return $candidate;
    }
    return null;
}

$requiredTables = ['students', 'student_content_progress', 'student_activity_attempts', 'xp_transactions', 'academic_years', 'student_class_history'];
foreach ($requiredTables as $table) {
    if (!tableExists($pdo, $table)) {
        http_response_code(500);
        exit('Missing required table: ' . h($table));
    }
}

$reportScope=reportTeacherScope($pdo);

$academicYearCols = tableColumns($pdo, 'academic_years');
$yearLabelColumn = academicYearLabelColumn($pdo);
if ($yearLabelColumn === null || !isset($academicYearCols['id'])) {
    http_response_code(500);
    exit('Bảng academic_years chưa có cấu trúc phù hợp.');
}

$yearSelect = "SELECT id, `{$yearLabelColumn}` AS year_label"
    . (isset($academicYearCols['start_date']) ? ", start_date" : ", NULL AS start_date")
    . (isset($academicYearCols['end_date']) ? ", end_date" : ", NULL AS end_date")
    . " FROM academic_years"
    . (isset($academicYearCols['start_date']) ? " ORDER BY start_date DESC, id DESC" : " ORDER BY id DESC");

$academicYearRows = $pdo->query($yearSelect)->fetchAll(PDO::FETCH_ASSOC);
$academicYearId = max(0, (int)($_GET['academic_year_id'] ?? 0));

if ($academicYearId <= 0 && $academicYearRows !== []) {
    if (isset($academicYearCols['is_active'])) {
        $active = $pdo->query("SELECT id FROM academic_years WHERE is_active = 1 ORDER BY id DESC LIMIT 1")->fetchColumn();
        if ($active !== false) $academicYearId = (int)$active;
    } elseif (isset($academicYearCols['status'])) {
        $active = $pdo->query("SELECT id FROM academic_years WHERE status IN ('active','current') ORDER BY id DESC LIMIT 1")->fetchColumn();
        if ($active !== false) $academicYearId = (int)$active;
    }
    if ($academicYearId <= 0) $academicYearId = (int)($academicYearRows[0]['id'] ?? 0);
}

$selectedYear = null;
foreach ($academicYearRows as $yr) {
    if ((int)$yr['id'] === $academicYearId) {
        $selectedYear = $yr;
        break;
    }
}
if (!$selectedYear && $academicYearRows !== []) {
    $selectedYear = $academicYearRows[0];
    $academicYearId = (int)$selectedYear['id'];
}

$yearLabel = (string)($selectedYear['year_label'] ?? '');
$yearStart = trim((string)($selectedYear['start_date'] ?? ''));
$yearEnd = trim((string)($selectedYear['end_date'] ?? ''));

/* DB cũ không có start_date/end_date: suy ra từ nhãn 2026-2027. */
if (($yearStart === '' || $yearEnd === '') && preg_match('/(\d{4})\D+(\d{4})/', $yearLabel, $m)) {
    $yearStart = $yearStart !== '' ? $yearStart : ($m[1] . '-08-01');
    $yearEnd = $yearEnd !== '' ? $yearEnd : ($m[2] . '-07-31');
}
if ($yearStart === '' || $yearEnd === '') {
    http_response_code(500);
    exit('Năm học chưa có ngày bắt đầu/kết thúc hợp lệ.');
}
$yearEndExclusive = date('Y-m-d', strtotime($yearEnd . ' +1 day'));

$province=trim((string)($_GET['province']??''));
$school=trim((string)($_GET['school']??''));
if($reportScope['role']==='teacher'){ $province=(string)$reportScope['province']; $school=(string)$reportScope['school']; }
$gradeId = max(0, (int)($_GET['grade_id'] ?? 0)); // 1..5
$className = trim((string)($_GET['class_name'] ?? ''));
$search = trim((string)($_GET['q'] ?? ''));
$export = trim((string)($_GET['export'] ?? ''));

/*
 * Student_class_history giữ lớp cũ.
 * Nếu học sinh đang ở đúng năm được chọn thì ưu tiên students hiện tại;
 * nếu xem năm cũ thì lấy snapshot student_class_history.
 */
$resolvedGrade = "CASE WHEN s.academic_year_id = {$academicYearId} THEN s.grade ELSE h.grade END";
$resolvedClass = "(CASE WHEN s.academic_year_id = {$academicYearId} THEN CONVERT(s.class_name USING utf8mb4) ELSE CONVERT(h.class_name USING utf8mb4) END) COLLATE utf8mb4_unicode_ci";
$resolvedProvinceId = "CASE WHEN s.academic_year_id = {$academicYearId} THEN COALESCE(s.province_id,0) ELSE COALESCE(h.province_id,0) END";
$resolvedSchoolId = "CASE WHEN s.academic_year_id = {$academicYearId} THEN COALESCE(s.school_id,0) ELSE COALESCE(h.school_id,0) END";
/* Tên tỉnh/trường luôn lấy từ bảng master theo ID hiện tại.
 * Snapshot history chỉ giữ ID/lớp/khối; text cũ chỉ dùng fallback khi record cũ không có ID. */
$resolvedProvince = "COALESCE(CONVERT(pm.name USING utf8mb4), (CASE WHEN s.academic_year_id = {$academicYearId} THEN CONVERT(s.province USING utf8mb4) ELSE CONVERT(h.province USING utf8mb4) END)) COLLATE utf8mb4_unicode_ci";
$resolvedSchool = "COALESCE(CONVERT(sm.name USING utf8mb4), (CASE WHEN s.academic_year_id = {$academicYearId} THEN CONVERT(s.school_name USING utf8mb4) ELSE CONVERT(h.school_name USING utf8mb4) END)) COLLATE utf8mb4_unicode_ci";

$where=["s.status = 'active'", "(s.academic_year_id = ? OR h.student_id IS NOT NULL)"];
$params=[$academicYearId];

if($reportScope['role']==='teacher'){
    if(empty($reportScope['ready'])){
        $where[]='1=0';
    } elseif((int)$reportScope['province_id']>0 && (int)$reportScope['school_id']>0) {
        $where[]="{$resolvedProvinceId} = ? AND {$resolvedSchoolId} = ?";
        $params[]=(int)$reportScope['province_id'];
        $params[]=(int)$reportScope['school_id'];
    } else {
        $where[]="TRIM({$resolvedProvince}) = ? AND TRIM({$resolvedSchool}) = ?";
        $params[]=(string)$reportScope['province'];
        $params[]=(string)$reportScope['school'];
    }
}

if ($province !== '' && $reportScope['role'] !== 'teacher') {
    $where[] = "{$resolvedProvince} = ?";
    $params[] = $province;
}
if ($school !== '' && $reportScope['role'] !== 'teacher') {
    $where[] = "{$resolvedSchool} = ?";
    $params[] = $school;
}
if ($gradeId > 0) {
    $where[] = "{$resolvedGrade} = ?";
    $params[] = $gradeId;
}
if ($className !== '') {
    $where[] = "{$resolvedClass} = ?";
    $params[] = $className;
}
if ($search !== '') {
    $where[] = "(s.full_name LIKE ? OR s.phone LIKE ? OR {$resolvedSchool} LIKE ?)";
    $like = '%' . $search . '%';
    array_push($params, $like, $like, $like);
}
$whereSql = implode(' AND ', $where);

/*
 * Progress / Best Score / Attempts are scoped to the selected academic year
 * from student_activity_attempts.
 * XP is read only from xp_transactions so Analytics uses the same XP ledger
 * as the student pages and Ranking.
 */
$sql = "
SELECT
    s.id,
    s.full_name,
    {$resolvedClass} AS class_name,
    {$resolvedProvince} AS province,
    {$resolvedSchool} AS school_name,
    {$resolvedGrade} AS grade_number,
    CONCAT('Khối ', {$resolvedGrade}) AS grade_name,
    s.last_login_at,

    COALESCE(p.started_activities, 0) AS started_activities,
    COALESCE(p.completed_activities, 0) AS completed_activities,
    COALESCE(p.avg_best_score, 0) AS avg_best_score,
    COALESCE(p.total_attempts, 0) AS total_attempts,
    p.last_learning_at,

    COALESCE(xy.completion_xp, 0) AS completion_xp,
    COALESCE(xy.practice_xp, 0) AS practice_xp,
    COALESCE(xy.other_xp, 0) AS other_xp,
    COALESCE(xy.year_xp, 0) AS year_xp,
    COALESCE(xc.cumulative_xp, 0) AS cumulative_xp,
    COALESCE(xc.cumulative_xp, 0) AS total_xp

FROM students s

LEFT JOIN student_class_history h
  ON h.student_id = s.id
 AND h.academic_year_id = {$academicYearId}

LEFT JOIN provinces pm
  ON pm.id = {$resolvedProvinceId}
LEFT JOIN schools sm
  ON sm.id = {$resolvedSchoolId}

LEFT JOIN (
    SELECT
        pa.student_id,
        COUNT(*) AS started_activities,
        SUM(CASE WHEN pa.passed_once = 1 THEN 1 ELSE 0 END) AS completed_activities,
        ROUND(AVG(pa.best_score), 0) AS avg_best_score,
        SUM(pa.attempts) AS total_attempts,
        MAX(pa.last_attempt_at) AS last_learning_at
    FROM (
        SELECT
            student_id,
            activity_id,
            MAX(score) AS best_score,
            MAX(CASE WHEN passed = 1 THEN 1 ELSE 0 END) AS passed_once,
            COUNT(*) AS attempts,
            MAX(attempted_at) AS last_attempt_at
        FROM student_activity_attempts
        WHERE academic_year_id = {$academicYearId}
        GROUP BY student_id, activity_id
    ) pa
    GROUP BY pa.student_id
) p ON p.student_id = s.id

/* XP phát sinh riêng trong năm học được chọn. */
LEFT JOIN (
    SELECT
        student_id,
        SUM(CASE WHEN xp_source = 'completion' THEN xp_change ELSE 0 END) AS completion_xp,
        SUM(CASE WHEN xp_source IN ('practice','new_best') THEN xp_change ELSE 0 END) AS practice_xp,
        SUM(CASE WHEN xp_source IS NULL OR xp_source NOT IN ('completion','practice','new_best') THEN xp_change ELSE 0 END) AS other_xp,
        SUM(xp_change) AS year_xp
    FROM xp_transactions
    WHERE academic_year_id = {$academicYearId}
    GROUP BY student_id
) xy ON xy.student_id = s.id

/* XP cộng dồn từ trước đến hết năm học đang xem. */
LEFT JOIN (
    SELECT
        student_id,
        SUM(xp_change) AS cumulative_xp
    FROM xp_transactions
    WHERE created_at < ?
    GROUP BY student_id
) xc ON xc.student_id = s.id

WHERE {$whereSql}
ORDER BY cumulative_xp DESC, completed_activities DESC, s.full_name ASC
";

$stmt = $pdo->prepare($sql);
$stmt->execute(array_merge([$yearEndExclusive], $params));
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);

$summary = [
    'students' => count($students),
    'learning' => 0,
    'completed' => 0,
    'year_xp' => 0,
    'xp' => 0,
];

foreach ($students as $row) {
    if ((int)$row['started_activities'] > 0) {
        $summary['learning']++;
    }
    $summary['completed'] += (int)$row['completed_activities'];
    $summary['year_xp'] += (int)$row['year_xp'];
    $summary['xp'] += (int)$row['cumulative_xp'];
}

/*
 * Export the currently filtered Analytics rows.
 * UTF-8 BOM keeps Vietnamese text readable when opened in Microsoft Excel.
 * XP values come from the same $students query above (xp_transactions).
 */
if ($export === 'excel') {
    $filename = 'EnglishSpeak-Analytics-' . date('Y-m-d-His') . '.csv';

    header('Content-Type: text/csv; charset=UTF-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Cache-Control: no-store, no-cache, must-revalidate');

    $out = fopen('php://output', 'wb');
    fwrite($out, "\xEF\xBB\xBF");

    fputcsv($out, ['EnglishSpeak - Reports']);
    fputcsv($out, ['Academic year', $yearLabel]);
    fputcsv($out, ['Students', $summary['students']]);
    fputcsv($out, ['Students learning', $summary['learning']]);
    fputcsv($out, ['Activities completed', $summary['completed']]);
    fputcsv($out, ['XP in academic year', $summary['year_xp']]);
    fputcsv($out, ['Cumulative XP', $summary['xp']]);
    fputcsv($out, []);

    fputcsv($out, [
        'Student',
        'Province',
        'School',
        'Grade',
        'Class',
        'Completed activities',
        'Started activities',
        'Progress',
        'Best score',
        'Attempts',
        'Completion XP',
        'Practice XP',
        'Other XP',
        'XP in academic year',
        'Cumulative XP',
        'Last learning',
    ]);

    foreach ($students as $student) {
        $started = (int)$student['started_activities'];
        $completed = (int)$student['completed_activities'];
        $pct = $started > 0 ? (int)round(($completed / $started) * 100) : 0;
        $lastLearning = (string)($student['last_learning_at'] ?? '');

        fputcsv($out, [
            (string)$student['full_name'],
            (string)($student['province'] ?? ''),
            (string)($student['school_name'] ?? ''),
            (string)($student['grade_name'] ?? ''),
            (string)($student['class_name'] ?? ''),
            $completed,
            $started,
            $pct . '%',
            (int)$student['avg_best_score'] . '%',
            (int)$student['total_attempts'],
            (int)$student['completion_xp'],
            (int)$student['practice_xp'],
            (int)$student['other_xp'],
            (int)$student['year_xp'],
            (int)$student['cumulative_xp'],
            $lastLearning !== '' ? date('d/m/Y H:i', strtotime($lastLearning)) : '',
        ]);
    }

    fclose($out);
    exit;
}

/*
 * PDF export without adding a server-side PDF library:
 * render a clean A4 report and open the browser print dialog.
 * Choose "Save as PDF" in the print dialog.
 * It uses the same filtered $students/$summary data as Analytics.
 */
if ($export === 'pdf') {
    $filterParts = ['Academic year: ' . $yearLabel];
    if ($province !== '') $filterParts[] = 'Province: ' . $province;
    if ($school !== '') $filterParts[] = 'School: ' . $school;
    if ($gradeId > 0) {
        foreach ($students as $s) {
            if (!empty($s['grade_name'])) {
                $filterParts[] = 'Grade: ' . (string)$s['grade_name'];
                break;
            }
        }
    }
    if ($className !== '') $filterParts[] = 'Class: ' . $className;
    if ($search !== '') $filterParts[] = 'Search: ' . $search;

    $filterText = $filterParts ? implode(' • ', $filterParts) : 'All students';
    ?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>EnglishSpeak - Learning Analytics</title>
<style>
@page{size:A4 landscape;margin:10mm}
*{box-sizing:border-box}
body{font-family:Arial,"DejaVu Sans",sans-serif;color:#111;margin:0;font-size:10px}
h1{font-size:20px;margin:0 0 4px}
.meta{color:#555;margin-bottom:12px}
.stats{display:grid;grid-template-columns:repeat(5,1fr);gap:8px;margin:12px 0}
.stat{border:1px solid #bbb;border-radius:7px;padding:8px}
.stat span{display:block;color:#666;font-size:9px}
.stat strong{display:block;font-size:17px;margin-top:3px}
table{width:100%;border-collapse:collapse}
th,td{border:1px solid #bbb;padding:5px 6px;text-align:left;vertical-align:top}
th{background:#f1f1f1;font-size:8px;text-transform:uppercase}
.num{text-align:right;white-space:nowrap}
.small{font-size:8px;color:#555}
.no-print{margin-bottom:10px}
@media print{.no-print{display:none}body{-webkit-print-color-adjust:exact;print-color-adjust:exact}}
</style>
</head>
<body>
<div class="no-print">
    <button type="button" onclick="window.print()">Save / Print PDF</button>
    <button type="button" onclick="history.back()">Back</button>
</div>

<h1>EnglishSpeak - Learning Analytics</h1>
<div class="meta">
    <?= h($filterText) ?> • Generated <?= h(date('d/m/Y H:i')) ?>
</div>

<div class="stats">
    <div class="stat"><span>Students</span><strong><?= number_format($summary['students']) ?></strong></div>
    <div class="stat"><span>Students learning</span><strong><?= number_format($summary['learning']) ?></strong></div>
    <div class="stat"><span>Activities completed</span><strong><?= number_format($summary['completed']) ?></strong></div>
    <div class="stat"><span>XP in year</span><strong><?= number_format($summary['year_xp']) ?></strong></div>
<div class="stat"><span>Cumulative XP</span><strong><?= number_format($summary['xp']) ?></strong></div>
</div>

<table>
<thead>
<tr>
    <th>Student</th>
    <th>Province / School</th>
    <th>Grade</th>
    <th>Class</th>
    <th class="num">Progress</th>
    <th class="num">Best</th>
    <th class="num">Attempts</th>
    <th class="num">Completion XP</th>
    <th class="num">Practice XP</th>
    <th class="num">XP in year</th>
    <th class="num">Cumulative XP</th>
    <th>Last learning</th>
</tr>
</thead>
<tbody>
<?php foreach ($students as $student): ?>
<?php
    $started = (int)$student['started_activities'];
    $completed = (int)$student['completed_activities'];
    $pct = $started > 0 ? (int)round(($completed / $started) * 100) : 0;
    $lastLearning = (string)($student['last_learning_at'] ?? '');
?>
<tr>
    <td><strong><?= h((string)$student['full_name']) ?></strong></td>
    <td>
        <?= h((string)($student['province'] ?: '—')) ?>
        <?php if (!empty($student['school_name'])): ?>
            <div class="small"><?= h((string)$student['school_name']) ?></div>
        <?php endif; ?>
    </td>
    <td><?= h((string)($student['grade_name'] ?: '—')) ?></td>
    <td><?= h((string)($student['class_name'] ?: '—')) ?></td>
    <td class="num"><?= $completed ?>/<?= $started ?> (<?= $pct ?>%)</td>
    <td class="num"><?= (int)$student['avg_best_score'] ?>%</td>
    <td class="num"><?= number_format((int)$student['total_attempts']) ?></td>
    <td class="num">+<?= number_format((int)$student['completion_xp']) ?></td>
    <td class="num">+<?= number_format((int)$student['practice_xp']) ?></td>
    <td class="num"><strong><?= number_format((int)$student['year_xp']) ?> XP</strong></td>
    <td class="num"><strong><?= number_format((int)$student['cumulative_xp']) ?> XP</strong></td>
    <td><?= $lastLearning !== '' ? h(date('d/m/Y H:i', strtotime($lastLearning))) : '—' ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>

<script>
window.addEventListener('load', function () {
    window.setTimeout(function () { window.print(); }, 250);
});
</script>
</body>
</html>
<?php
    exit;
}

/* Filter options are also scoped to the selected academic year. */
$optionBase = "
    FROM students s
    LEFT JOIN student_class_history h
      ON h.student_id = s.id
     AND h.academic_year_id = {$academicYearId}
    LEFT JOIN provinces pm
      ON pm.id = {$resolvedProvinceId}
    LEFT JOIN schools sm
      ON sm.id = {$resolvedSchoolId}
    WHERE s.status = 'active'
      AND (s.academic_year_id = {$academicYearId} OR h.student_id IS NOT NULL)
";

$provinceRows = $pdo->query("
    SELECT DISTINCT {$resolvedProvince} AS province
    {$optionBase}
      AND {$resolvedProvince} IS NOT NULL
      AND TRIM({$resolvedProvince}) <> ''
    ORDER BY province
")->fetchAll(PDO::FETCH_COLUMN);

$schoolRows = $pdo->query("
    SELECT DISTINCT {$resolvedSchool} AS school_name
    {$optionBase}
      AND {$resolvedSchool} IS NOT NULL
      AND TRIM({$resolvedSchool}) <> ''
    ORDER BY school_name
")->fetchAll(PDO::FETCH_COLUMN);

if($reportScope['role']==='teacher'){
    $provinceRows=$province!==''?[$province]:[];
    $schoolRows=$school!==''?[$school]:[];
}

$gradeRows = $pdo->query("
    SELECT DISTINCT {$resolvedGrade} AS grade_number
    {$optionBase}
      AND {$resolvedGrade} BETWEEN 1 AND 5
    ORDER BY grade_number
")->fetchAll(PDO::FETCH_ASSOC);

$classRows = $pdo->query("
    SELECT DISTINCT {$resolvedClass} AS class_name
    {$optionBase}
      AND {$resolvedClass} IS NOT NULL
      AND TRIM({$resolvedClass}) <> ''
    ORDER BY class_name
")->fetchAll(PDO::FETCH_COLUMN);

ob_start();
?>

<style>
.analytics-page{display:grid;gap:18px}
.analytics-head{display:flex;justify-content:space-between;align-items:flex-end;gap:16px}
.analytics-head h2{margin:0;font-size:28px}
.analytics-head p{margin:5px 0 0;color:var(--muted)}
.analytics-filters{display:grid;grid-template-columns:minmax(180px,1.3fr) repeat(5,minmax(125px,1fr)) auto auto;gap:10px;align-items:center;width:100%}
.analytics-filters input,.analytics-filters select{
    min-height:40px;border:1px solid var(--border);border-radius:10px;
    padding:0 12px;background:var(--surface);color:inherit
}
.analytics-filters input{min-width:0}
.analytics-stats{display:grid;grid-template-columns:repeat(5,minmax(0,1fr));gap:12px}
.analytics-stat{background:var(--surface);border:1px solid var(--border);border-radius:14px;padding:16px}
.analytics-stat span{display:block;color:var(--muted);font-size:12px;margin-bottom:6px}
.analytics-stat strong{font-size:26px}
.analytics-card{background:var(--surface);border:1px solid var(--border);border-radius:16px;overflow:hidden}
.analytics-card-head{padding:16px 18px;border-bottom:1px solid var(--border);display:flex;justify-content:space-between;gap:12px}
.analytics-card-head h3{margin:0}
.analytics-table-wrap{overflow:auto}
.analytics-table{width:100%;border-collapse:collapse;min-width:1140px}
.analytics-table th,.analytics-table td{padding:13px 14px;border-bottom:1px solid var(--border);text-align:left;vertical-align:middle}
.analytics-table th{font-size:11px;text-transform:uppercase;letter-spacing:.04em;color:var(--muted);background:#fafbfc}
.analytics-table tbody tr:last-child td{border-bottom:0}
.student-name{display:grid;gap:3px}
.student-name strong{font-size:14px}
.student-name small{color:var(--muted)}
.num{text-align:right!important;font-variant-numeric:tabular-nums}
.xp-total{font-weight:800}
.progress-cell{min-width:145px}
.progress-line{display:flex;justify-content:space-between;font-size:12px;margin-bottom:5px}
.progress-track-mini{height:7px;background:#edf0f3;border-radius:999px;overflow:hidden}
.progress-track-mini span{display:block;height:100%;background:#5b7cfa;border-radius:999px}
.empty-report{padding:48px 20px;text-align:center;color:var(--muted)}
.report-note{font-size:12px;color:var(--muted)}
@media(max-width:1200px){
    .analytics-filters{grid-template-columns:repeat(3,minmax(0,1fr))}
}
@media(max-width:900px){
    .analytics-stats{grid-template-columns:repeat(2,minmax(0,1fr))}
    .analytics-head{align-items:stretch;flex-direction:column}
    .analytics-filters{grid-template-columns:repeat(2,minmax(0,1fr))}
}
@media(max-width:560px){
    .analytics-stats{grid-template-columns:1fr 1fr}
    .analytics-filters{grid-template-columns:1fr}
    .analytics-filters input{width:100%}
}
</style>

<div class="analytics-page">
    <div class="analytics-head">
        <div>
            <div style="margin-top:10px;">
                <a class="button button-secondary" href="daily.php?academic_year_id=<?= (int)$academicYearId ?>">Daily Learning Report</a>
            </div>
        </div>

        <form method="get" class="analytics-filters">
            <input
                type="search"
                name="q"
                value="<?= h($search) ?>"
                placeholder="Search student..."
            >

            <select name="academic_year_id" onchange="this.form.submit()">
                <?php foreach ($academicYearRows as $yearOption): ?>
                    <option value="<?= (int)$yearOption['id'] ?>" <?= $academicYearId === (int)$yearOption['id'] ? 'selected' : '' ?>>
                        <?= h((string)$yearOption['year_label']) ?>
                    </option>
                <?php endforeach; ?>
            </select>

            <select name="province" onchange="this.form.submit()">
                <option value="">All provinces</option>
                <?php foreach ($provinceRows as $provinceOption): ?>
                    <option value="<?= h((string)$provinceOption) ?>" <?= $province === (string)$provinceOption ? 'selected' : '' ?>>
                        <?= h((string)$provinceOption) ?>
                    </option>
                <?php endforeach; ?>
            </select>

            <select name="school" onchange="this.form.submit()">
                <option value="">All schools</option>
                <?php foreach ($schoolRows as $schoolOption): ?>
                    <option value="<?= h((string)$schoolOption) ?>" <?= $school === (string)$schoolOption ? 'selected' : '' ?>>
                        <?= h((string)$schoolOption) ?>
                    </option>
                <?php endforeach; ?>
            </select>

            <select name="grade_id" onchange="this.form.submit()">
                <option value="">All grades</option>
                <?php foreach ($gradeRows as $gradeOption): ?>
                    <?php $gradeNumber = (int)($gradeOption['grade_number'] ?? 0); ?>
                    <option value="<?= $gradeNumber ?>" <?= $gradeId === $gradeNumber ? 'selected' : '' ?>>
                        Khối <?= $gradeNumber ?>
                    </option>
                <?php endforeach; ?>
            </select>

            <select name="class_name" onchange="this.form.submit()">
                <option value="">All classes</option>
                <?php foreach ($classRows as $classOption): ?>
                    <option value="<?= h((string)$classOption) ?>" <?= $className === (string)$classOption ? 'selected' : '' ?>>
                        <?= h((string)$classOption) ?>
                    </option>
                <?php endforeach; ?>
            </select>

            <button class="button button-primary" type="submit">Search</button>
            <button class="button button-secondary" type="submit" name="export" value="excel">Export Excel</button>
            <button class="button button-secondary" type="submit" name="export" value="pdf">Export PDF</button>

            <?php if ($province !== '' || $school !== '' || $gradeId > 0 || $className !== '' || $search !== ''): ?>
                <a class="button button-secondary" href="./">Clear</a>
            <?php endif; ?>
        </form>
    </div>

    <div class="analytics-stats">
        <div class="analytics-stat">
            <span>Students</span>
            <strong><?= number_format($summary['students']) ?></strong>
        </div>
        <div class="analytics-stat">
            <span>Students learning</span>
            <strong><?= number_format($summary['learning']) ?></strong>
        </div>
        <div class="analytics-stat">
            <span>Activities completed</span>
            <strong><?= number_format($summary['completed']) ?></strong>
        </div>
        <div class="analytics-stat">
            <span>XP trong năm <?= h($yearLabel) ?></span>
            <strong><?= number_format($summary['year_xp']) ?></strong>
        </div>
        <div class="analytics-stat">
            <span>XP cộng dồn</span>
            <strong><?= number_format($summary['xp']) ?></strong>
        </div>
    </div>

    <section class="analytics-card">
        <div class="analytics-card-head">
            <div>
                <h3>Student progress</h3>
                <div class="report-note">Năm học <?= h($yearLabel) ?> • XP source: xp_transactions</div>
            </div>
            <strong><?= count($students) ?> student<?= count($students) === 1 ? '' : 's' ?></strong>
        </div>

        <?php if (!$students): ?>
            <div class="empty-report">No student data found.</div>
        <?php else: ?>
            <div class="analytics-table-wrap">
                <table class="analytics-table">
                    <thead>
                        <tr>
                            <th>Student</th>
                            <th>Grade</th>
                            <th>Class</th>
                            <th>Progress</th>
                            <th class="num">Best score</th>
                            <th class="num">Attempts</th>
                            <th class="num">Completion XP</th>
                            <th class="num">Practice XP</th>
                            <th class="num">XP trong năm</th>
                            <th class="num">XP cộng dồn</th>
                            <th>Last learning</th>
                        </tr>
                    </thead>
                    <tbody>
                    <?php foreach ($students as $student): ?>
                        <?php
                            $started = (int)$student['started_activities'];
                            $completed = (int)$student['completed_activities'];
                            $pct = $started > 0 ? (int)round(($completed / $started) * 100) : 0;
                            $lastLearning = (string)($student['last_learning_at'] ?? '');
                        ?>
                        <tr>
                            <td>
                                <div class="student-name">
                                    <strong><a href="student.php?student_id=<?= (int)$student['id'] ?>" style="color:inherit;text-decoration:none;"><?= h((string)$student['full_name']) ?></a></strong>
                                    <small><?= h((string)($student['school_name'] ?: $student['province'] ?: '—')) ?></small>
                                </div>
                            </td>
                            <td><?= h((string)($student['grade_name'] ?: '—')) ?></td>
                            <td><?= h((string)($student['class_name'] ?: '—')) ?></td>
                            <td class="progress-cell">
                                <div class="progress-line">
                                    <span><?= $completed ?>/<?= $started ?> activities</span>
                                    <strong><?= $pct ?>%</strong>
                                </div>
                                <div class="progress-track-mini"><span style="width:<?= max(0,min(100,$pct)) ?>%"></span></div>
                            </td>
                            <td class="num"><?= (int)$student['avg_best_score'] ?>%</td>
                            <td class="num"><?= number_format((int)$student['total_attempts']) ?></td>
                            <td class="num">+<?= number_format((int)$student['completion_xp']) ?></td>
                            <td class="num">+<?= number_format((int)$student['practice_xp']) ?></td>
                            <td class="num xp-total"><?= number_format((int)$student['year_xp']) ?> XP</td>
                            <td class="num xp-total"><?= number_format((int)$student['cumulative_xp']) ?> XP</td>
                            <td><?= $lastLearning !== '' ? h(date('d/m/Y H:i', strtotime($lastLearning))) : '—' ?></td>
                        </tr>
                    <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        <?php endif; ?>
    </section>
</div>

<?php
$content = ob_get_clean();
require __DIR__ . '/../layouts/app.php';
