<?php
// ==========================================
// report.php (All-in-one: UI + AJAX backend)
// ==========================================
session_name('super_admin_session');
if (!is_dir(dirname(__DIR__) . '/session_data/super')) { mkdir(dirname(__DIR__) . '/session_data/super', 0700, true); } session_save_path(dirname(__DIR__) . '/session_data/super');
session_set_cookie_params(['path' => '/', 'httponly' => true, 'samesite' => 'Strict']);
session_start();
include('includes/dbconnection.php'); // expects $con = new mysqli(...)

// ======== CONFIG ========
$DEFAULT_PROMOTION_THRESHOLD = 50; // %
$DEFAULT_WEAK_THRESHOLD      = 40; // %

if (!isset($_SESSION['sid'])) {
    // header('location:logout.php'); exit();
    // For testing without auth, comment the above line.
}

// ========= HELPERS =========
function json_out($data) {
    header('Content-Type: application/json');
    echo json_encode($data);
    exit;
}

function fetch_all($stmt) {
    $res = $stmt->get_result();
    $rows = [];
    while ($row = $res->fetch_assoc()) $rows[] = $row;
    return $rows;
}

// Build WHERE parts safely based on filters
function build_filters(&$params, &$types, $session_id, $term_id, $class, $section, $subject) {
    $wheres = ["r.session_id = ?", "r.term_id = ?"];
    $params[] = $session_id; $types .= "i";
    $params[] = $term_id;    $types .= "i";

    if ($class && $class !== 'all')  { $wheres[] = "r.class = ?";   $params[] = $class;   $types .= "s"; }
    if ($section && $section !== 'all'){ $wheres[] = "r.section = ?"; $params[] = $section; $types .= "s"; }
    if ($subject && $subject !== 'all'){ $wheres[] = "r.subject = ?"; $params[] = $subject; $types .= "s"; }

    return "WHERE " . implode(" AND ", $wheres);
}

// Aggregate: per student/class/section average % (for session+term and optional filters)
function get_student_averages($con, $session_id, $term_id, $class='all', $section='all', $subject='all') {
    $params = []; $types = "";
    $where = build_filters($params, $types, $session_id, $term_id, $class, $section, $subject);
    $sql = "
        SELECT r.student_id, s.studentName, r.class, r.section,
               ROUND(AVG(r.total_score),2) AS avg_percentage,
               COUNT(*) AS subjects_count
        FROM results r
        JOIN students s ON s.id = r.student_id
        $where
        GROUP BY r.student_id, r.class, r.section
    ";
    $stmt = $con->prepare($sql);
    if ($types) $stmt->bind_param($types, ...$params);
    $stmt->execute();
    return fetch_all($stmt);
}

// Aggregate: best in subject per class (ties allowed)
function get_subject_bests($con, $session_id, $term_id, $class='all', $section='all', $subject='all') {
    // Step 1: Find max per (class, subject) within filters
    $params = []; $types = "";
    $where = build_filters($params, $types, $session_id, $term_id, $class, $section, $subject);

    $sqlMax = "
        SELECT r.class, r.subject, MAX(r.total_score) AS max_score
        FROM results r
        $where
        GROUP BY r.class, r.subject
    ";
    $stmtMax = $con->prepare($sqlMax);
    if ($types) $stmtMax->bind_param($types, ...$params);
    $stmtMax->execute();
    $maxRows = fetch_all($stmtMax);

    if (!$maxRows) return [];

    // Build a lookup (class,subject) => max_score
    $maxLookup = [];
    foreach ($maxRows as $m) {
        $maxLookup[$m['class'].'|||'.$m['subject']] = (float)$m['max_score'];
    }

    // Step 2: Fetch all candidates matching those max scores (to allow ties)
    $stmtAll = $con->prepare("
        SELECT r.class, r.subject, r.student_id, s.studentName, r.total_score
        FROM results r
        JOIN students s ON s.id = r.student_id
        $where
    ");
    if ($types) $stmtAll->bind_param($types, ...$params);
    $stmtAll->execute();
    $rows = fetch_all($stmtAll);

    $winners = []; // [class][subject] = [ {student_id, studentName, total_score}, ... ]
    foreach ($rows as $r) {
        $key = $r['class'].'|||'.$r['subject'];
        if (!isset($maxLookup[$key])) continue;
        if ((float)$r['total_score'] == $maxLookup[$key]) {
            $winners[$r['class']][$r['subject']][] = [
                'student_id'   => $r['student_id'],
                'studentName'  => $r['studentName'],
                'score'        => (float)$r['total_score']
            ];
        }
    }
    return $winners;
}

// Distinct lists for selects
function get_sessions($con) {
    $res = $con->query("SELECT id, session_name FROM school_sessions ORDER BY id DESC");
    $out = [];
    while ($row = $res->fetch_assoc()) $out[] = $row;
    return $out;
}
function get_terms_by_session($con, $session_id) {
    $stmt = $con->prepare("SELECT id, term_name FROM school_terms WHERE session_id=? ORDER BY id");
    $stmt->bind_param("i", $session_id);
    $stmt->execute();
    return fetch_all($stmt);
}
function get_distinct_classes($con) {
    $res = $con->query("SELECT DISTINCT class FROM results ORDER BY class");
    $out = [];
    while ($r = $res->fetch_assoc()) if (!empty($r['class'])) $out[] = $r['class'];
    return $out;
}
function get_distinct_sections($con) {
    $res = $con->query("SELECT DISTINCT section FROM results ORDER BY section");
    $out = [];
    while ($r = $res->fetch_assoc()) if (!empty($r['section'])) $out[] = $r['section'];
    return $out;
}
function get_distinct_subjects($con) {
    $res = $con->query("SELECT DISTINCT subject FROM results ORDER BY subject");
    $out = [];
    while ($r = $res->fetch_assoc()) if (!empty($r['subject'])) $out[] = $r['subject'];
    return $out;
}

// Compute ranks inside PHP (no window funcs). Returns array with 'position' added.
function add_positions($arr, $groupByKeys) {
    // Group then sort desc by avg_percentage, assign dense ranks
    $grouped = [];
    foreach ($arr as $row) {
        $key = implode('|||', array_map(fn($k)=>$row[$k]??'', $groupByKeys));
        $grouped[$key][] = $row;
    }
    $withPos = [];
    foreach ($grouped as $gkey => $rows) {
        usort($rows, function($a,$b){
            if ($b['avg_percentage'] == $a['avg_percentage']) return 0;
            return ($b['avg_percentage'] < $a['avg_percentage']) ? -1 : 1;
        });
        $rank = 0; $prevScore = null; $seen = 0;
        foreach ($rows as $r) {
            $seen++;
            if ($prevScore === null || $r['avg_percentage'] != $prevScore) {
                $rank = $seen;
                $prevScore = $r['avg_percentage'];
            }
            $r['position'] = $rank;
            $withPos[] = $r;
        }
    }
    return $withPos;
}

// ============ AJAX HANDLER (same file) ============
if (isset($_GET['ajax'])) {
    $action     = $_GET['action'] ?? 'init';
    $session_id = (int)($_GET['session_id'] ?? 0);
    $term_id    = (int)($_GET['term_id'] ?? 0);
    $class      = $_GET['class']   ?? 'all';
    $section    = $_GET['section'] ?? 'all';
    $subject    = $_GET['subject'] ?? 'all';
    $weak_th    = isset($_GET['weak_threshold']) ? (float)$_GET['weak_threshold'] : $DEFAULT_WEAK_THRESHOLD;
    $prom_th    = isset($_GET['promotion_threshold']) ? (float)$_GET['promotion_threshold'] : $DEFAULT_PROMOTION_THRESHOLD;

    if ($action === 'init') {
        $sessions = get_sessions($con);
        // Choose default session/term if not provided
        $defaultSessionId = $sessions ? (int)$sessions[0]['id'] : 0;
        $terms   = $defaultSessionId ? get_terms_by_session($con, $defaultSessionId) : [];
        $classes = get_distinct_classes($con);
        $sections= get_distinct_sections($con);
        $subjects= get_distinct_subjects($con);
        json_out([
            'status'   => 'ok',
            'sessions' => $sessions,
            'terms'    => $terms,
            'classes'  => $classes,
            'sections' => $sections,
            'subjects' => $subjects,
            'defaults' => [
                'session_id' => $defaultSessionId,
                'term_id'    => $terms ? (int)$terms[0]['id'] : 0,
                'promotion_threshold' => $prom_th,
                'weak_threshold'      => $weak_th
            ]
        ]);
    }

    if ($action === 'terms_for_session') {
        $terms = $session_id ? get_terms_by_session($con, $session_id) : [];
        json_out(['status'=>'ok','terms'=>$terms]);
    }

    if ($action === 'generate') {
        // 1) Core averages for the scope
        $avgs = get_student_averages($con, $session_id, $term_id, $class, $section, 'all'); // overall scope

        // Overall Top 10 across scope
        $overall = $avgs;
        usort($overall, fn($a,$b)=> ($b['avg_percentage']<=>$a['avg_percentage']));
        $overallTop10 = array_slice($overall, 0, 10);

        // Top 3 per Class
        $withClassPos = add_positions($avgs, ['class']);
        $groupTop3Class = [];
        foreach ($withClassPos as $row) {
            $cls = $row['class'];
            if (!isset($groupTop3Class[$cls])) $groupTop3Class[$cls] = [];
            if (count($groupTop3Class[$cls]) < 3) $groupTop3Class[$cls][] = $row;
        }

        // Top 3 per Class & Section
        $withSectionPos = add_positions($avgs, ['class','section']);
        $groupTop3ClassSection = [];
        foreach ($withSectionPos as $row) {
            $key = $row['class'].' - '.$row['section'];
            if (!isset($groupTop3ClassSection[$key])) $groupTop3ClassSection[$key] = [];
            if (count($groupTop3ClassSection[$key]) < 3) $groupTop3ClassSection[$key][] = $row;
        }

        // Weak students (< weak_th)
        $weakList = array_values(array_filter($avgs, fn($r)=> (float)$r['avg_percentage'] < $weak_th));
        usort($weakList, fn($a,$b)=> ($a['avg_percentage']<=>$b['avg_percentage']));

        // Promotion list
        $promotion = [];
        foreach ($withClassPos as $r) {
            $status = ((float)$r['avg_percentage'] >= $prom_th) ? 'Promote' : 'Repeat';
            $promotion[] = [
                'student_id'    => $r['student_id'],
                'studentName'   => $r['studentName'],
                'class'         => $r['class'],
                'section'       => $r['section'],
                'avg_percentage'=> $r['avg_percentage'],
                'position'      => $r['position'],
                'status'        => $status
            ];
        }
        usort($promotion, function($a,$b){
            return [$a['class'],$a['position']] <=> [$b['class'],$b['position']];
        });

        // Subject bests (per class). If a specific subject is selected, only that; else all subjects in scope.
        $subjectBests = get_subject_bests($con, $session_id, $term_id, $class, $section, $subject);

        json_out([
            'status'                 => 'ok',
            'overallTop10'           => $overallTop10,
            'top3ByClass'            => $groupTop3Class,
            'top3ByClassSection'     => $groupTop3ClassSection,
            'weakList'               => $weakList,
            'promotion'              => $promotion,
            'subjectBests'           => $subjectBests
        ]);
    }

    json_out(['status'=>'error','message'=>'Unknown action']);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8" />
    <title>Student Performance Report System</title>
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <!-- jsPDF -->
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf/2.5.1/jspdf.umd.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf-autotable/3.5.29/jspdf.plugin.autotable.min.js"></script>
    <style>
        :root { --primary:#4a6fdc; --secondary:#6c757d; --success:#28a745; --danger:#dc3545; --warning:#ffc107; --info:#17a2b8; --light:#f8f9fa; --dark:#343a40; }
        * { box-sizing:border-box; margin:0; padding:0; font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; }
        body { background:#f5f7fb; color:#333; line-height:1.6; }
        .container { width:95%; max-width:1400px; margin:0 auto; padding:20px; }
        header { background:linear-gradient(135deg, var(--primary), #2c4eca); color:#fff; padding:20px; border-radius:10px; margin-bottom:25px; box-shadow:0 4px 12px rgba(0,0,0,.1); }
        h1,h2,h3{ margin-bottom:15px; }
        .filters{ background:#fff; padding:20px; border-radius:10px; margin-bottom:25px; box-shadow:0 4px 12px rgba(0,0,0,.05); }
        .filter-row{ display:flex; flex-wrap:wrap; gap:20px; margin-bottom:15px; }
        .filter-group{ flex:1; min-width:200px; }
        label{ display:block; margin-bottom:8px; font-weight:600; color:var(--dark); }
        select,input{ width:100%; padding:12px; border:1px solid #ddd; border-radius:6px; font-size:16px; }
        button{ background:var(--primary); color:#fff; border:none; padding:12px 20px; border-radius:6px; cursor:pointer; font-size:16px; font-weight:600; transition:background .3s; }
        button:hover{ background:#3a5fc8; }
        .btn-download{ background:var(--success); }
        .btn-download:hover{ background:#218838; }
        .report-section{ background:#fff; padding:20px; border-radius:10px; margin-bottom:25px; box-shadow:0 4px 12px rgba(0,0,0,.05); }
        .section-header{ display:flex; justify-content:space-between; align-items:center; margin-bottom:20px; padding-bottom:10px; border-bottom:2px solid #eee; }
        table{ width:100%; border-collapse:collapse; margin-bottom:20px; }
        th,td{ padding:12px 15px; text-align:left; border-bottom:1px solid #ddd; }
        th{ background:#f8f9fa; font-weight:600; }
        tr:hover{ background:#f5f7fb; }
        .highlight{ background:#e8f4ff; }
        .top-performer{ color:var(--success); font-weight:600; }
        .weak-performer{ color:var(--danger); font-weight:600; }
        .subject-best{ background:#fff8e1; }
        .tabs{ display:flex; margin-bottom:20px; border-bottom:1px solid #ddd; }
        .tab{ padding:12px 20px; cursor:pointer; background:#f8f9fa; border:1px solid #ddd; border-bottom:none; border-radius:5px 5px 0 0; margin-right:5px; }
        .tab.active{ background:#fff; border-bottom:1px solid #fff; margin-bottom:-1px; }
        .tab-content{ display:none; }
        .tab-content.active{ display:block; }
        .stats-grid{ display:grid; grid-template-columns:repeat(auto-fill, minmax(250px, 1fr)); gap:20px; margin-bottom:30px; }
        .stat-card{ background:#fff; padding:20px; border-radius:10px; box-shadow:0 4px 12px rgba(0,0,0,.05); text-align:center; }
        .stat-value{ font-size:24px; font-weight:700; margin:10px 0; }
        .performance-badge{ display:inline-block; padding:5px 10px; border-radius:20px; font-size:14px; font-weight:600; }
        .badge-high{ background:#e8f5e9; color:var(--success); }
        .badge-medium{ background:#fff8e1; color:var(--warning); }
        .badge-low{ background:#ffebee; color:var(--danger); }
        @media (max-width:768px){ .filter-row{ flex-direction:column; } .stats-grid{ grid-template-columns:1fr; } }
        .row-actions { display:flex; gap:10px; align-items:center; }
    </style>
</head>
<body>
<div class="container">
    <header>
        <h1>Student Performance Report System</h1>
        <p>Generate comprehensive reports on student performance, rankings, and subject excellence</p>
    </header>

    <div class="filters">
        <h2>Report Filters</h2>
        <div class="filter-row">
            <div class="filter-group">
                <label for="session">Academic Session</label>
                <select id="session"></select>
            </div>
            <div class="filter-group">
                <label for="term">Term</label>
                <select id="term"></select>
            </div>
            <div class="filter-group">
                <label for="class">Class</label>
                <select id="class">
                    <option value="all">All Classes</option>
                </select>
            </div>
        </div>
        <div class="filter-row">
            <div class="filter-group">
                <label for="section">Section</label>
                <select id="section">
                    <option value="all">All Sections</option>
                </select>
            </div>
            <div class="filter-group">
                <label for="subject">Subject (for subject awards/reports)</label>
                <select id="subject">
                    <option value="all">All Subjects</option>
                </select>
            </div>
            <div class="filter-group">
                <label for="weak_threshold">Weak Threshold (%)</label>
                <input type="number" id="weak_threshold" value="<?php echo $DEFAULT_WEAK_THRESHOLD; ?>" min="0" max="100" />
            </div>
            <div class="filter-group">
                <label for="promotion_threshold">Promotion Threshold (%)</label>
                <input type="number" id="promotion_threshold" value="<?php echo $DEFAULT_PROMOTION_THRESHOLD; ?>" min="0" max="100" />
            </div>
            <div class="filter-group" style="display:flex; align-items:flex-end;">
                <div class="row-actions">
                    <button id="generate-btn">Generate Report</button>
                    <button id="download-all" class="btn-download">Download Current Tab</button>
                </div>
            </div>
        </div>
    </div>

    <div class="tabs">
        <div class="tab active" data-tab="overview">Overview</div>
        <div class="tab" data-tab="class-performance">Class Performance</div>
        <div class="tab" data-tab="subject-performance">Subject Performance</div>
        <div class="tab" data-tab="weak-students">Weak Students</div>
        <div class="tab" data-tab="promotion">Promotion List</div>
    </div>

    <!-- OVERVIEW -->
    <div class="tab-content active" id="overview">
        <div class="report-section">
            <div class="section-header">
                <h2>Overall Performance Summary</h2>
                <button class="btn-download" onclick="downloadPDF('overall-table','Overall Performance Summary')">Download Overview</button>
            </div>
            <div class="stats-grid" id="stats-grid">
                <!-- Filled dynamically -->
            </div>
            <h3>Top 10 Students Overall</h3>
            <table id="overall-table">
                <thead>
                    <tr>
                        <th>Rank</th>
                        <th>Student ID</th>
                        <th>Name</th>
                        <th>Class</th>
                        <th>Section</th>
                        <th>Average (%)</th>
                        <th>Performance</th>
                    </tr>
                </thead>
                <tbody></tbody>
            </table>

            <h3>Top 3 Students by Class</h3>
            <div id="top3-by-class"></div>

            <h3>Top 3 Students by Class & Section</h3>
            <div id="top3-by-class-section"></div>
        </div>
    </div>

    <!-- CLASS PERFORMANCE -->
    <div class="tab-content" id="class-performance">
        <div class="report-section">
            <div class="section-header">
                <h2>Class Performance Ranking</h2>
                <button class="btn-download" onclick="downloadPDF('class-performance-table','Class Performance Ranking')">Download Class Report</button>
            </div>
            <table id="class-performance-table">
                <thead>
                    <tr>
                        <th>Rank</th>
                        <th>Student ID</th>
                        <th>Name</th>
                        <th>Class</th>
                        <th>Section</th>
                        <th>Average (%)</th>
                        <th>Position (Class)</th>
                    </tr>
                </thead>
                <tbody></tbody>
            </table>
        </div>
    </div>

    <!-- SUBJECT PERFORMANCE -->
    <div class="tab-content" id="subject-performance">
        <div class="report-section">
            <div class="section-header">
                <h2>Subject Performance / Awards</h2>
                <button class="btn-download" onclick="downloadPDF('subject-performance-table','Subject Awards')">Download Subject Report</button>
            </div>
            <table id="subject-performance-table">
                <thead>
                    <tr>
                        <th>Class</th>
                        <th>Subject</th>
                        <th>Student ID</th>
                        <th>Name</th>
                        <th>Score (%)</th>
                        <th>Award</th>
                    </tr>
                </thead>
                <tbody></tbody>
            </table>
        </div>
    </div>

    <!-- WEAK STUDENTS -->
    <div class="tab-content" id="weak-students">
        <div class="report-section">
            <div class="section-header">
                <h2>Students Needing Improvement</h2>
                <button class="btn-download" onclick="downloadPDF('weak-students-table','Weak Students Report')">Download Weak Students Report</button>
            </div>
            <table id="weak-students-table">
                <thead>
                    <tr>
                        <th>Student ID</th>
                        <th>Name</th>
                        <th>Class</th>
                        <th>Section</th>
                        <th>Average (%)</th>
                        <th>Intervention Needed</th>
                    </tr>
                </thead>
                <tbody></tbody>
            </table>
        </div>
    </div>

    <!-- PROMOTION -->
    <div class="tab-content" id="promotion">
        <div class="report-section">
            <div class="section-header">
                <h2>Promotion List</h2>
                <button class="btn-download" onclick="downloadPDF('promotion-table','Promotion List')">Download Promotion List</button>
            </div>
            <table id="promotion-table">
                <thead>
                    <tr>
                        <th>Class</th>
                        <th>Section</th>
                        <th>Student ID</th>
                        <th>Name</th>
                        <th>Average (%)</th>
                        <th>Position</th>
                        <th>Status</th>
                    </tr>
                </thead>
                <tbody></tbody>
            </table>
        </div>
    </div>

</div>

<script>
const qs = (sel, root=document) => root.querySelector(sel);
const qsa = (sel, root=document) => Array.from(root.querySelectorAll(sel));

function perfBadge(pct){
    pct = parseFloat(pct||0);
    if (pct >= 70) return `<span class="performance-badge badge-high">Excellent</span>`;
    if (pct >= 50) return `<span class="performance-badge badge-medium">Average</span>`;
    return `<span class="performance-badge badge-low">Weak</span>`;
}

// Tabs
qsa('.tab').forEach(tab=>{
    tab.addEventListener('click', ()=>{
        qsa('.tab').forEach(t=>t.classList.remove('active'));
        qsa('.tab-content').forEach(c=>c.classList.remove('active'));
        tab.classList.add('active');
        qs('#'+tab.dataset.tab).classList.add('active');
    });
});

// INIT: load sessions, terms, class, section, subject
async function initUI(){
    const res = await fetch('report.php?ajax=1&action=init');
    const data = await res.json();
    if (data.status !== 'ok') return;

    const ses = qs('#session');
    ses.innerHTML = '';
    data.sessions.forEach(s=>{
        const opt = document.createElement('option');
        opt.value = s.id; opt.textContent = s.session_name;
        ses.appendChild(opt);
    });
    if (data.defaults.session_id) ses.value = data.defaults.session_id;

    await loadTermsForSession(ses.value, data.defaults.term_id);

    // classes
    const clsSel = qs('#class');
    data.classes.forEach(c=>{
        const opt = document.createElement('option');
        opt.value = c; opt.textContent = c;
        clsSel.appendChild(opt);
    });
    // sections
    const secSel = qs('#section');
    data.sections.forEach(s=>{
        const opt = document.createElement('option');
        opt.value = s; opt.textContent = s;
        secSel.appendChild(opt);
    });
    // subjects
    const subjSel = qs('#subject');
    data.subjects.forEach(s=>{
        const opt = document.createElement('option');
        opt.value = s; opt.textContent = s;
        subjSel.appendChild(opt);
    });

    // listeners
    ses.addEventListener('change', async ()=>{
        await loadTermsForSession(ses.value);
    });
}
async function loadTermsForSession(sessionId, defaultTermId=null){
    const res = await fetch(`report.php?ajax=1&action=terms_for_session&session_id=${encodeURIComponent(sessionId)}`);
    const data = await res.json();
    const termSel = qs('#term');
    termSel.innerHTML = '';
    (data.terms||[]).forEach(t=>{
        const opt = document.createElement('option');
        opt.value = t.id; opt.textContent = t.term_name;
        termSel.appendChild(opt);
    });
    if (defaultTermId) termSel.value = defaultTermId;
}

// Generate reports
qs('#generate-btn').addEventListener('click', generateReports);
async function generateReports(){
    const session_id = qs('#session').value;
    const term_id    = qs('#term').value;
    const klass      = qs('#class').value;
    const section    = qs('#section').value;
    const subject    = qs('#subject').value;
    const weak_th    = qs('#weak_threshold').value || 40;
    const prom_th    = qs('#promotion_threshold').value || 50;

    const url = new URL('report.php', window.location.href);
    url.searchParams.set('ajax','1');
    url.searchParams.set('action','generate');
    url.searchParams.set('session_id', session_id);
    url.searchParams.set('term_id', term_id);
    url.searchParams.set('class', klass);
    url.searchParams.set('section', section);
    url.searchParams.set('subject', subject);
    url.searchParams.set('weak_threshold', weak_th);
    url.searchParams.set('promotion_threshold', prom_th);

    const res = await fetch(url.toString());
    const data = await res.json();
    if (data.status !== 'ok') return;

    renderOverview(data);
    renderClassPerformance(data);
    renderSubjectPerformance(data);
    renderWeakStudents(data);
    renderPromotion(data, prom_th);
}

function renderOverview(data){
    // Stats
    const stats = qs('#stats-grid');
    const totalStudents = new Set(data.promotion.map(r=>r.student_id)).size;
    const overallAvg = (()=> {
        if (!data.promotion.length) return 0;
        const s = data.promotion.reduce((a,b)=>a+parseFloat(b.avg_percentage), 0);
        return (s/data.promotion.length).toFixed(1);
    })();
    const topClassEntry = (()=> {
        if (!data.promotion.length) return {key:'-', avg:0};
        // avg per class-section
        const byCS = {};
        data.promotion.forEach(p=>{
            const key = `${p.class} - ${p.section}`;
            byCS[key] = byCS[key] || {sum:0, n:0};
            byCS[key].sum += parseFloat(p.avg_percentage);
            byCS[key].n   += 1;
        });
        let bestKey='-', bestAvg=0;
        for (const k in byCS){
            const avg = byCS[k].sum/byCS[k].n;
            if (avg>bestAvg){ bestAvg=avg; bestKey=k; }
        }
        return {key:bestKey, avg:bestAvg.toFixed(1)};
    })();
    const bestSubjectName = (()=> {
        // rough: use subjectBests counts as a proxy
        const counts = {};
        for (const cls in data.subjectBests){
            for (const subj in data.subjectBests[cls]){
                counts[subj] = (counts[subj]||0) + data.subjectBests[cls][subj].length;
            }
        }
        let best='-', c=0;
        for (const s in counts){ if (counts[s]>c){ c=counts[s]; best=s; } }
        return best;
    })();

    stats.innerHTML = `
        <div class="stat-card">
            <h3>Total Students</h3>
            <div class="stat-value">${totalStudents}</div>
            <p>Across current scope</p>
        </div>
        <div class="stat-card">
            <h3>Overall Average</h3>
            <div class="stat-value">${overallAvg}%</div>
            <p>School performance (scope)</p>
        </div>
        <div class="stat-card">
            <h3>Top Performing Class-Section</h3>
            <div class="stat-value">${topClassEntry.key}</div>
            <p>Average: ${topClassEntry.avg}%</p>
        </div>
        <div class="stat-card">
            <h3>Best Subject</h3>
            <div class="stat-value">${bestSubjectName}</div>
            <p>Within current scope</p>
        </div>
    `;

    // Overall Top 10 table
    const tb = qs('#overall-table tbody');
    tb.innerHTML = '';
    data.overallTop10.forEach((st, idx)=>{
        const tr = document.createElement('tr');
        if (idx < 3) tr.classList.add('highlight');
        tr.innerHTML = `
            <td>${idx+1}</td>
            <td>${st.student_id}</td>
            <td>${st.studentName ?? ''}</td>
            <td>${st.class ?? ''}</td>
            <td>${st.section ?? ''}</td>
            <td class="${idx<3?'top-performer':''}">${st.avg_percentage}%</td>
            <td>${perfBadge(st.avg_percentage)}</td>
        `;
        tb.appendChild(tr);
    });

    // Top 3 by Class
    const wrapClass = qs('#top3-by-class');
    wrapClass.innerHTML = '';
    Object.keys(data.top3ByClass).sort().forEach(cls=>{
        const table = document.createElement('table');
        table.innerHTML = `
            <thead><tr><th>Position</th><th>Student ID</th><th>Name</th><th>Average (%)</th></tr></thead>
            <tbody></tbody>
        `;
        const tbody = table.querySelector('tbody');
        data.top3ByClass[cls].forEach(r=>{
            const tr = document.createElement('tr');
            tr.classList.add('highlight');
            tr.innerHTML = `
                <td>${r.position}</td>
                <td>${r.student_id}</td>
                <td>${r.studentName ?? ''}</td>
                <td class="top-performer">${r.avg_percentage}%</td>
            `;
            tbody.appendChild(tr);
        });
        const h = document.createElement('h4'); h.textContent = cls;
        wrapClass.appendChild(h);
        wrapClass.appendChild(table);
    });

    // Top 3 by Class & Section
    const wrapCS = qs('#top3-by-class-section');
    wrapCS.innerHTML = '';
    Object.keys(data.top3ByClassSection).sort().forEach(key=>{
        const table = document.createElement('table');
        table.innerHTML = `
            <thead><tr><th>Position</th><th>Student ID</th><th>Name</th><th>Class</th><th>Section</th><th>Average (%)</th></tr></thead>
            <tbody></tbody>
        `;
        const tbody = table.querySelector('tbody');
        data.top3ByClassSection[key].forEach(r=>{
            const tr = document.createElement('tr');
            tr.classList.add('highlight');
            tr.innerHTML = `
                <td>${r.position}</td>
                <td>${r.student_id}</td>
                <td>${r.studentName ?? ''}</td>
                <td>${r.class ?? ''}</td>
                <td>${r.section ?? ''}</td>
                <td class="top-performer">${r.avg_percentage}%</td>
            `;
            tbody.appendChild(tr);
        });
        const h = document.createElement('h4'); h.textContent = key;
        wrapCS.appendChild(h);
        wrapCS.appendChild(table);
    });
}

function renderClassPerformance(data){
    // Flatten promotion table ordered by class then position
    const tb = qs('#class-performance-table tbody');
    tb.innerHTML = '';
    data.promotion.forEach((r, idx)=>{
        const tr = document.createElement('tr');
        if (r.position<=3) tr.classList.add('highlight');
        tr.innerHTML = `
            <td>${r.position}</td>
            <td>${r.student_id}</td>
            <td>${r.studentName ?? ''}</td>
            <td>${r.class ?? ''}</td>
            <td>${r.section ?? ''}</td>
            <td>${parseFloat(r.avg_percentage).toFixed(2)}%</td>
            <td>${r.position}${ordinalSuffix(r.position)}</td>
        `;
        tb.appendChild(tr);
    });
}

function renderSubjectPerformance(data){
    const tb = qs('#subject-performance-table tbody');
    tb.innerHTML = '';
    const chosenSubject = qs('#subject').value;
    const label = (s)=> s;

    // For each class -> each subject -> winners
    const classes = Object.keys(data.subjectBests).sort();
    classes.forEach(cls=>{
        const subjects = Object.keys(data.subjectBests[cls]).sort();
        subjects.forEach(subj=>{
            data.subjectBests[cls][subj].forEach(w=>{
                const tr = document.createElement('tr');
                tr.classList.add('subject-best');
                tr.innerHTML = `
                    <td>${cls}</td>
                    <td>${subj}</td>
                    <td>${w.student_id}</td>
                    <td>${w.studentName ?? ''}</td>
                    <td class="top-performer">${parseFloat(w.score).toFixed(2)}%</td>
                    <td>Best in ${subj}</td>
                `;
                tb.appendChild(tr);
            });
        });
    });
}

function renderWeakStudents(data){
    const tb = qs('#weak-students-table tbody');
    tb.innerHTML = '';
    const interventions = (pct)=>{
        const v = parseFloat(pct||0);
        if (v < 40) return 'Extra classes, Parent meeting';
        if (v < 50) return 'Subject tutoring, Counseling';
        return 'Monitor';
    };
    data.weakList.forEach(r=>{
        const tr = document.createElement('tr');
        tr.innerHTML = `
            <td>${r.student_id}</td>
            <td>${r.studentName ?? ''}</td>
            <td>${r.class ?? ''}</td>
            <td>${r.section ?? ''}</td>
            <td class="weak-performer">${parseFloat(r.avg_percentage).toFixed(2)}%</td>
            <td>${interventions(r.avg_percentage)}</td>
        `;
        tb.appendChild(tr);
    });
}

function renderPromotion(data, prom_th){
    const tb = qs('#promotion-table tbody');
    tb.innerHTML = '';
    data.promotion.forEach(r=>{
        const tr = document.createElement('tr');
        tr.innerHTML = `
            <td>${r.class ?? ''}</td>
            <td>${r.section ?? ''}</td>
            <td>${r.student_id}</td>
            <td>${r.studentName ?? ''}</td>
            <td>${parseFloat(r.avg_percentage).toFixed(2)}%</td>
            <td>${r.position}${ordinalSuffix(r.position)}</td>
            <td>${r.status}</td>
        `;
        tb.appendChild(tr);
    });
}

function ordinalSuffix(n){
    n = parseInt(n||0,10);
    const j = n % 10, k = n % 100;
    if (j==1 && k!=11) return "st";
    if (j==2 && k!=12) return "nd";
    if (j==3 && k!=13) return "rd";
    return "th";
}

// PDF export
function downloadPDF(tableId, title){
    const { jsPDF } = window.jspdf;
    const doc = new jsPDF();
    doc.setFontSize(16);
    doc.text(title || 'Student Performance Report', 14, 18);

    const meta = `Session: ${qs('#session').selectedOptions[0]?.textContent || qs('#session').value}  |  Term: ${qs('#term').selectedOptions[0]?.textContent || qs('#term').value}
Class: ${qs('#class').value}  |  Section: ${qs('#section').value}  |  Subject: ${qs('#subject').value}`;
    doc.setFontSize(10);
    const lines = doc.splitTextToSize(meta, 180);
    doc.text(lines, 14, 26);

    const table = document.getElementById(tableId);
    if (!table) { alert('Nothing to export'); return; }

    doc.autoTable({ html: '#'+tableId, startY: 36, styles: { fontSize: 9 }});
    doc.save(`${title?.toLowerCase().replace(/\s+/g,'-') || 'report'}.pdf`);
}

// “Download Current Tab” button
qs('#download-all').addEventListener('click', ()=>{
    const active = document.querySelector('.tab-content.active');
    if (!active) return;
    const map = {
        'overview':'overall-table',
        'class-performance':'class-performance-table',
        'subject-performance':'subject-performance-table',
        'weak-students':'weak-students-table',
        'promotion':'promotion-table'
    };
    const id = map[active.id];
    const title = active.querySelector('.section-header h2')?.textContent || 'Report';
    downloadPDF(id, title);
});

// Boot
initUI().then(generateReports);
</script>
</body>
</html>
