<?php
session_start();
include 'db.php';

if (!isset($_SESSION['admin_id'])) {
    header("Location: admin_login.php");
    exit();
}

mysqli_set_charset($conn, "utf8mb4");

function esc($s) {
    return htmlspecialchars($s, ENT_QUOTES, 'UTF-8');
}
function getq($name, $default = '') {
    return isset($_GET[$name]) ? trim($_GET[$name]) : $default;
}

// إعدادات النجاح والتفوّق
$PASS_MARK = 50;
$EXCELLENT = 85;

/* ======================
   مصادر الفلاتر
   ====================== */
$stages = [];
$rs = mysqli_query(
    $conn,
    "SELECT DISTINCT stage FROM students
     ORDER BY FIELD(stage,'أول متوسط','ثاني متوسط','ثالث متوسط','رابع علمي','خامس علمي','سادس علمي'), stage"
);
while ($r = mysqli_fetch_assoc($rs)) {
    $stages[] = $r['stage'];
}

$teachers = [];
$rs = mysqli_query($conn, "SELECT id, name FROM teachers ORDER BY name");
while ($r = mysqli_fetch_assoc($rs)) {
    $teachers[] = $r;
}

$subjects_all = [];
$rs = mysqli_query($conn, "SELECT DISTINCT subject FROM grades WHERE subject <> '' ORDER BY subject");
while ($r = mysqli_fetch_assoc($rs)) {
    $subjects_all[] = $r['subject'];
}

/* ✅ جديد: جلب الشعب لاستخدامها كفلتر */
$classes = [];
$rs = mysqli_query($conn, "SELECT DISTINCT class FROM students WHERE class IS NOT NULL AND class <> '' ORDER BY class");
while ($r = mysqli_fetch_assoc($rs)) {
    $classes[] = $r['class'];
}

/* ======================
   قراءة الفلاتر
   ====================== */
$filter_stage   = getq('stage', '');
$filter_subject = getq('subject', '');
$filter_teacher = getq('teacher_id', '');
$filter_class   = getq('class', ''); // ✅ فلتر الشعبة

/* ======================
   دالة تركيب WHERE
   ====================== */
function build_where($stage, $subject, $teacher_id, $class = '') {
    $conn = $GLOBALS['conn'];
    $w = " WHERE 1 ";
    if ($stage !== '') {
        $s = mysqli_real_escape_string($conn, $stage);
        $w .= " AND REPLACE(s.stage,'الأ','ا') LIKE REPLACE('%$s%','الأ','ا') ";
    }
    if ($subject !== '') {
        $sub = mysqli_real_escape_string($conn, $subject);
        $w   .= " AND TRIM(LOWER(REPLACE(g.subject,'ال',''))) = TRIM(LOWER(REPLACE('$sub','ال',''))) ";
    }
    if ($teacher_id !== '' && ctype_digit($teacher_id) && (int)$teacher_id > 0) {
        $tid = (int)$teacher_id;
        $w  .= " AND g.teacher_id = $tid ";
    }
    // ✅ تصفية على الشعبة (إن وُجدت)
    if ($class !== '') {
        $c = mysqli_real_escape_string($conn, $class);
        $w .= " AND s.class = '$c' ";
    }
    return $w;
}

/* ======================
   تحويل اسم المقياس إلى عمود درجات
   ====================== */
function metric_to_column($m) {
    switch ($m) {
        case 'suc_m1':      return 'month1';
        case 'suc_m2':      return 'month2';
        case 'suc_m3':      return 'month3';
        case 'suc_daily1':  return 'daily1';
        case 'suc_sem1':    return 'semester1';
        case 'suc_midterm': return 'midterm';
        case 'suc_m4':      return 'month4';
        case 'suc_m5':      return 'month5';
        case 'suc_m6':      return 'month6';
        case 'suc_daily2':  return 'daily2';
        case 'suc_sem2':    return 'semester2';
        case 'suc_annual':  return 'annual_average';
        case 'success_rate':return 'final_result';
        default:            return 'month1';
    }
}

/* ======================
   AJAX
   ====================== */
if (isset($_GET['ajax']) && $_GET['ajax'] == '1') {

    $metric_req      = isset($_GET['metric']) ? trim($_GET['metric']) : 'suc_m1';
    $col_for_missing = metric_to_column($metric_req);

    // ✅ نضيف فلتر الشعبة هنا
    $w = build_where($filter_stage, $filter_subject, $filter_teacher, $filter_class);

    // إجمالي الطلبة
    $total_students = 0;
    $q = mysqli_query($conn, "
        SELECT COUNT(DISTINCT s.id) c
        FROM grades g
        JOIN students s ON s.id = g.student_id
        $w
    ");
    if ($q && $row = mysqli_fetch_assoc($q)) {
        $total_students = (int)$row['c'];
    }

    // إجمالي المواد
    $total_subjects = 0;
    $q = mysqli_query($conn, "
        SELECT COUNT(DISTINCT TRIM(LOWER(g.subject))) c
        FROM grades g
        JOIN students s ON s.id = g.student_id
        $w
    ");
    if ($q && $row = mysqli_fetch_assoc($q)) {
        $total_subjects = (int)$row['c'];
    }

    // إجمالي المدرّسين
    $total_teachers = 0;
    $q = mysqli_query($conn, "
        SELECT COUNT(DISTINCT g.teacher_id) c
        FROM grades g
        JOIN students s ON s.id = g.student_id
        $w
    ");
    if ($q && $row = mysqli_fetch_assoc($q)) {
        $total_teachers = (int)$row['c'];
    }

    // نجاح / رسوب / تفوّق على النهائي
    $pass_cnt = $fail_cnt = $excellent_cnt = 0;
    $q = mysqli_query($conn, "
        SELECT
            SUM(CASE WHEN g.final_result >= $PASS_MARK THEN 1 ELSE 0 END) AS pass_cnt,
            SUM(CASE WHEN g.final_result <  $PASS_MARK THEN 1 ELSE 0 END) AS fail_cnt,
            SUM(CASE WHEN g.final_result >= $EXCELLENT THEN 1 ELSE 0 END) AS excellent_cnt
        FROM grades g
        JOIN students s ON s.id = g.student_id
        $w
    ");
    if ($q && $row = mysqli_fetch_assoc($q)) {
        $pass_cnt      = (int)$row['pass_cnt'];
        $fail_cnt      = (int)$row['fail_cnt'];
        $excellent_cnt = (int)$row['excellent_cnt'];
    }

    /* ====== إحصائيات حسب المادة ====== */
    $subjects_stats = [];
    $q = mysqli_query($conn, "
        SELECT
            g.subject,
            COALESCE(t.name,'غير محدد') AS teacher_name,
            s.stage,
            GROUP_CONCAT(DISTINCT s.class ORDER BY s.class SEPARATOR ', ') AS classes_text,
            COUNT(*) AS total_students,

            ROUND(
                SUM(CASE WHEN g.month1 >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.month1 > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_m1,

            ROUND(
                SUM(CASE WHEN g.month2 >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.month2 > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_m2,

            ROUND(
                SUM(CASE WHEN g.month3 >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.month3 > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_m3,

            ROUND(
                SUM(CASE WHEN g.daily1 >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.daily1 > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_daily1,

            ROUND(
                SUM(CASE WHEN g.semester1 >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.semester1 > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_sem1,

            ROUND(
                SUM(CASE WHEN g.midterm >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.midterm > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_midterm,

            ROUND(
                SUM(CASE WHEN g.month4 >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.month4 > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_m4,

            ROUND(
                SUM(CASE WHEN g.month5 >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.month5 > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_m5,

            ROUND(
                SUM(CASE WHEN g.month6 >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.month6 > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_m6,

            ROUND(
                SUM(CASE WHEN g.daily2 >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.daily2 > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_daily2,

            ROUND(
                SUM(CASE WHEN g.semester2 >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.semester2 > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_sem2,

            ROUND(
                SUM(CASE WHEN g.annual_average >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.annual_average > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_annual,

            ROUND(
                SUM(CASE WHEN g.final_result >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.final_result > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS success_rate

        FROM grades g
        JOIN students s ON s.id = g.student_id
        LEFT JOIN teachers t ON t.id = g.teacher_id
        $w
        GROUP BY g.subject, teacher_name, s.stage
        ORDER BY g.subject, s.stage
    ");
    while ($row = mysqli_fetch_assoc($q)) {
        $subjects_stats[] = $row;
    }

    /* ====== إحصائيات حسب المدرس ====== */
    $teachers_stats = [];
    $q = mysqli_query($conn, "
        SELECT
            COALESCE(t.name,'غير محدد') AS teacher_name,
            COUNT(*) AS total_students,
            ROUND(AVG(g.final_result),2)     AS avg_final,
            ROUND(AVG(g.annual_average),2)   AS avg_annual,
            ROUND(AVG(g.semester1),2)        AS avg_sem1,
            ROUND(AVG(g.midterm),2)          AS avg_midterm,
            ROUND(AVG(g.semester2),2)        AS avg_sem2,

            ROUND(
                SUM(CASE WHEN g.month1 >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.month1 > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_m1,

            ROUND(
                SUM(CASE WHEN g.month2 >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.month2 > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_m2,

            ROUND(
                SUM(CASE WHEN g.month3 >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.month3 > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_m3,

            ROUND(
                SUM(CASE WHEN g.daily1 >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.daily1 > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_daily1,

            ROUND(
                SUM(CASE WHEN g.semester1 >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.semester1 > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_sem1,

            ROUND(
                SUM(CASE WHEN g.midterm >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.midterm > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_midterm,

            ROUND(
                SUM(CASE WHEN g.month4 >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.month4 > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_m4,

            ROUND(
                SUM(CASE WHEN g.month5 >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.month5 > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_m5,

            ROUND(
                SUM(CASE WHEN g.month6 >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.month6 > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_m6,

            ROUND(
                SUM(CASE WHEN g.daily2 >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.daily2 > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_daily2,

            ROUND(
                SUM(CASE WHEN g.semester2 >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.semester2 > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_sem2,

            ROUND(
                SUM(CASE WHEN g.annual_average >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.annual_average > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_annual,

            ROUND(
                SUM(CASE WHEN g.final_result >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.final_result > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS success_rate

        FROM grades g
        JOIN students s ON s.id = g.student_id
        LEFT JOIN teachers t ON t.id = g.teacher_id
        $w
        GROUP BY teacher_name
        ORDER BY avg_final DESC
    ");
    while ($row = mysqli_fetch_assoc($q)) {
        $teachers_stats[] = $row;
    }

    /* ====== توزيع النجاح/الرسوب حسب المرحلة ====== */
    $stages_distribution = [];
    $chart_stage_labels  = [];
    $chart_stage_pass    = [];
    $chart_stage_fail    = [];
    $q = mysqli_query($conn, "
        SELECT
            s.stage,
            SUM(CASE WHEN g.final_result >= $PASS_MARK THEN 1 ELSE 0 END) AS pass_cnt,
            SUM(CASE WHEN g.final_result <  $PASS_MARK THEN 1 ELSE 0 END) AS fail_cnt
        FROM grades g
        JOIN students s ON s.id = g.student_id
        $w
        GROUP BY s.stage
        ORDER BY FIELD(s.stage,'أول متوسط','ثاني متوسط','ثالث متوسط','رابع علمي','خامس علمي','سادس علمي'), s.stage
    ");
    while ($row = mysqli_fetch_assoc($q)) {
        $stages_distribution[] = $row;
        $chart_stage_labels[]  = $row['stage'];
        $chart_stage_pass[]    = (int)$row['pass_cnt'];
        $chart_stage_fail[]    = (int)$row['fail_cnt'];
    }

    /* ====== الأوائل العام (Top 10 على مستوى الدرجات الخام) ====== */
    $top_students = [];
    $q = mysqli_query($conn, "
        SELECT
            s.name,
            s.stage,
            s.class,
            g.subject,
            g.final_result,
            COALESCE(t.name,'غير محدد') AS teacher_name
        FROM grades g
        JOIN students s ON s.id = g.student_id
        LEFT JOIN teachers t ON t.id = g.teacher_id
        $w
        ORDER BY g.final_result DESC
        LIMIT 10
    ");
    while ($row = mysqli_fetch_assoc($q)) {
        $top_students[] = $row;
    }

    /* ====== أعلى الطلبة حسب المرحلة (متوسط درجات كل المواد) ====== */
    $top_by_stage = [];
    if ($filter_stage != '') {

        $st = mysqli_real_escape_string($conn, $filter_stage);

        // نحدد العمود الذي سيُستخدم في حساب الأوائل حسب الفلتر
        $colName = metric_to_column($metric_req);
        $allowedCols = [
            'month1','month2','month3','daily1','semester1','midterm',
            'month4','month5','month6','daily2','semester2','annual_average','final_result'
        ];
        if (!in_array($colName, $allowedCols, true)) {
            $colName = 'final_result';
        }

        // شرط المرحلة فقط (بدون تقييد المادة أو المدرس أو الشعبة)
        $wStage = " WHERE 1 ";
        $wStage .= " AND REPLACE(s.stage,'الأ','ا') LIKE REPLACE('%$st%','الأ','ا') ";

        $sql = "
            SELECT
                s.id   AS student_id,
                s.name,
                s.stage,
                s.class,
                SUM(
                    CASE
                        WHEN g.`$colName` IS NOT NULL AND g.`$colName` > 0
                        THEN g.`$colName`
                        ELSE 0
                    END
                ) AS sum_score,
                SUM(
                    CASE
                        WHEN g.`$colName` IS NOT NULL AND g.`$colName` > 0
                        THEN 1
                        ELSE 0
                    END
                ) AS cnt_valid,
                ROUND(
                    SUM(
                        CASE
                            WHEN g.`$colName` IS NOT NULL AND g.`$colName` > 0
                            THEN g.`$colName`
                            ELSE 0
                        END
                    )
                    / NULLIF(
                        SUM(
                            CASE
                                WHEN g.`$colName` IS NOT NULL AND g.`$colName` > 0
                                THEN 1
                                ELSE 0
                            END
                        ),0
                    ),2
                ) AS avg_score
            FROM students s
            JOIN grades g ON g.student_id = s.id
            $wStage
            GROUP BY s.id, s.name, s.stage, s.class
            HAVING cnt_valid > 0
            ORDER BY avg_score DESC, s.name ASC
            LIMIT 200
        ";

        $q = mysqli_query($conn, $sql);

        $rows_tmp = [];
        while ($r = mysqli_fetch_assoc($q)) {
            $rows_tmp[] = $r;
        }

        $last_score   = null;
        $current_rank = 0;
        $with_ranks   = [];

        foreach ($rows_tmp as $idx => $r) {
            $score = (float)$r['avg_score'];

            if ($last_score === null || $score < $last_score) {
                $current_rank = $idx + 1;
            }
            $last_score = $score;

            if      ($current_rank == 1) $rank_label = 'الأول';
            else if ($current_rank == 2) $rank_label = 'الثاني';
            else if ($current_rank == 3) $rank_label = 'الثالث';
            else if ($current_rank == 4) $rank_label = 'الرابع';
            else                         $rank_label = 'المركز '.$current_rank;

            $with_ranks[] = [
                'rank_num'    => $current_rank,
                'rank_label'  => $rank_label,
                'name'        => $r['name'],
                'stage'       => $r['stage'],
                'class'       => $r['class'],
                // نخزن المعدّل في total_final لاستخدامه في الواجهة الحالية
                'total_final' => $score,
                'subjects'    => (int)$r['cnt_valid']
            ];
        }

        // نأخذ فقط أول 4 طلاب (بدون تمديد المركز الرابع بسبب التكرار)
        foreach ($with_ranks as $wr) {
            if (count($top_by_stage) >= 4) {
                break;
            }

            $wr['is_repeat'] = 0;
            if (!empty($top_by_stage)
                && (float)$wr['total_final'] == (float)$top_by_stage[count($top_by_stage)-1]['total_final']) {
                $wr['is_repeat'] = 1; // فقط لعرض (مكرر) لو حبيت
            }

            $top_by_stage[] = $wr;
        }
    }

    /* ✅ جديد: أعلى الطلبة حسب الشعبة (متوسط درجات كل المواد) */
    $top_by_class = [];
    if ($filter_stage != '' && $filter_class != '') {

        $st = mysqli_real_escape_string($conn, $filter_stage);
        $cl = mysqli_real_escape_string($conn, $filter_class);

        $colName = metric_to_column($metric_req);
        $allowedCols = [
            'month1','month2','month3','daily1','semester1','midterm',
            'month4','month5','month6','daily2','semester2','annual_average','final_result'
        ];
        if (!in_array($colName, $allowedCols, true)) {
            $colName = 'final_result';
        }

        // مرحلة + شعبة
        $wClass = " WHERE 1 ";
        $wClass .= " AND REPLACE(s.stage,'الأ','ا') LIKE REPLACE('%$st%','الأ','ا') ";
        $wClass .= " AND s.class = '$cl' ";

        $sqlClass = "
            SELECT
                s.id   AS student_id,
                s.name,
                s.stage,
                s.class,
                SUM(
                    CASE
                        WHEN g.`$colName` IS NOT NULL AND g.`$colName` > 0
                        THEN g.`$colName`
                        ELSE 0
                    END
                ) AS sum_score,
                SUM(
                    CASE
                        WHEN g.`$colName` IS NOT NULL AND g.`$colName` > 0
                        THEN 1
                        ELSE 0
                    END
                ) AS cnt_valid,
                ROUND(
                    SUM(
                        CASE
                            WHEN g.`$colName` IS NOT NULL AND g.`$colName` > 0
                            THEN g.`$colName`
                            ELSE 0
                        END
                    )
                    / NULLIF(
                        SUM(
                            CASE
                                WHEN g.`$colName` IS NOT NULL AND g.`$colName` > 0
                                THEN 1
                                ELSE 0
                            END
                        ),0
                    ),2
                ) AS avg_score
            FROM students s
            JOIN grades g ON g.student_id = s.id
            $wClass
            GROUP BY s.id, s.name, s.stage, s.class
            HAVING cnt_valid > 0
            ORDER BY avg_score DESC, s.name ASC
            LIMIT 200
        ";

        $q = mysqli_query($conn, $sqlClass);
        $rows_tmp_c = [];
        while ($r = mysqli_fetch_assoc($q)) {
            $rows_tmp_c[] = $r;
        }

        $last_score_c   = null;
        $current_rank_c = 0;
        $with_ranks_c   = [];

        foreach ($rows_tmp_c as $idx => $r) {
            $score = (float)$r['avg_score'];

            if ($last_score_c === null || $score < $last_score_c) {
                $current_rank_c = $idx + 1;
            }
            $last_score_c = $score;

            if      ($current_rank_c == 1) $rank_label = 'الأول';
            else if ($current_rank_c == 2) $rank_label = 'الثاني';
            else if ($current_rank_c == 3) $rank_label = 'الثالث';
            else if ($current_rank_c == 4) $rank_label = 'الرابع';
            else                           $rank_label = 'المركز '.$current_rank_c;

            $with_ranks_c[] = [
                'rank_num'    => $current_rank_c,
                'rank_label'  => $rank_label,
                'name'        => $r['name'],
                'stage'       => $r['stage'],
                'class'       => $r['class'],
                'total_final' => $score,
                'subjects'    => (int)$r['cnt_valid']
            ];
        }

        foreach ($with_ranks_c as $wr) {
            if (count($top_by_class) >= 4) {
                break;
            }

            $wr['is_repeat'] = 0;
            if (!empty($top_by_class)
                && (float)$wr['total_final'] == (float)$top_by_class[count($top_by_class)-1]['total_final']) {
                $wr['is_repeat'] = 1;
            }

            $top_by_class[] = $wr;
        }
    }

    /* ====== متوسطات للرسوم ====== */
    $chart_subject_labels   = [];
    $chart_subject_success  = [];
    $chart_subject_finalavg = [];
    foreach ($subjects_stats as $ss) {
        $chart_subject_labels[]   = $ss['subject'].' - '.$ss['stage'];
        $chart_subject_success[]  = (float)$ss['success_rate'];
        $chart_subject_finalavg[] = 0;
    }

    $avg_months = ['m1'=>0,'m2'=>0,'m3'=>0,'m4'=>0,'m5'=>0,'m6'=>0];
    $q = mysqli_query($conn, "
        SELECT
            ROUND(AVG(g.month1),2) AS m1,
            ROUND(AVG(g.month2),2) AS m2,
            ROUND(AVG(g.month3),2) AS m3,
            ROUND(AVG(g.month4),2) AS m4,
            ROUND(AVG(g.month5),2) AS m5,
            ROUND(AVG(g.month6),2) AS m6
        FROM grades g
        JOIN students s ON s.id = g.student_id
        $w
    ");
    if ($q && $row = mysqli_fetch_assoc($q)) {
        $avg_months = $row;
    }

    $avg_terms = ['sem1'=>0,'midterm'=>0,'sem2'=>0,'annual'=>0,'final'=>0];
    $q = mysqli_query($conn, "
        SELECT
            ROUND(AVG(g.semester1),2)      AS sem1,
            ROUND(AVG(g.midterm),2)        AS midterm,
            ROUND(AVG(g.semester2),2)      AS sem2,
            ROUND(AVG(g.annual_average),2) AS annual,
            ROUND(AVG(g.final_result),2)   AS final
        FROM grades g
        JOIN students s ON s.id = g.student_id
        $w
    ");
    if ($q && $row = mysqli_fetch_assoc($q)) {
        $avg_terms = $row;
    }

    header("Content-Type: application/json; charset=utf-8");
    echo json_encode([
        "kpis" => [
            "total_students" => $total_students,
            "total_subjects" => $total_subjects,
            "total_teachers" => $total_teachers,
            "pass_cnt"       => $pass_cnt,
            "fail_cnt"       => $fail_cnt,
            "excellent_cnt"  => $excellent_cnt
        ],
        "subjects_stats"      => $subjects_stats,
        "teachers_stats"      => $teachers_stats,
        "stages_distribution" => $stages_distribution,
        "top_students"        => $top_students,
        "top_by_stage"        => $top_by_stage,
        "top_by_class"        => $top_by_class, // ✅ جديد
        "charts" => [
            "subject_labels"   => $chart_subject_labels,
            "subject_success"  => $chart_subject_success,
            "subject_finalavg" => $chart_subject_finalavg,
            "months_labels"    => ["ش1","ش2","ش3","ش4","ش5","ش6"],
            "months_data"      => [
                (float)$avg_months['m1'],
                (float)$avg_months['m2'],
                (float)$avg_months['m3'],
                (float)$avg_months['m4'],
                (float)$avg_months['m5'],
                (float)$avg_months['m6']
            ],
            "terms_labels"     => ["ف1","نصف السنة","ف2","المعدل السنوي","النهائي"],
            "terms_data"       => [
                (float)$avg_terms['sem1'],
                (float)$avg_terms['midterm'],
                (float)$avg_terms['sem2'],
                (float)$avg_terms['annual'],
                (float)$avg_terms['final']
            ],
            "stage_labels"     => $chart_stage_labels,
            "stage_pass"       => $chart_stage_pass,
            "stage_fail"       => $chart_stage_fail
        ]
    ], JSON_UNESCAPED_UNICODE);
    exit;
}

/* ======================
   تصدير Excel
   ====================== */
if (isset($_GET['action']) && $_GET['action'] == 'export_excel') {
    $w = build_where($filter_stage, $filter_subject, $filter_teacher, $filter_class); // ✅ إضافة الشعبة في التصفية

    header("Content-Type: application/vnd.ms-excel; charset=utf-8");
    header("Content-Disposition: attachment; filename=statistics_export.xls");
    header("Pragma: no-cache");
    header("Expires: 0");

    echo "<table border='1' style='font-family:Tahoma'>";
    echo "<tr><th colspan='6' style='background:#e6f0ff'>تفاصيل حسب المادة</th></tr>";
    echo "<tr>
            <th>المادة</th>
            <th>المدرس</th>
            <th>المرحلة</th>
            <th>الشعب</th>
            <th>عدد الطلبة</th>
            <th>نسبة النجاح (نهائي)</th>
          </tr>";

    $q = mysqli_query($conn, "
        SELECT
            g.subject,
            COALESCE(t.name,'غير محدد') AS teacher_name,
            s.stage,
            GROUP_CONCAT(DISTINCT s.class ORDER BY s.class SEPARATOR ', ') AS classes_text,
            COUNT(*) AS total_students,
            ROUND(
                SUM(CASE WHEN g.final_result >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.final_result > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS success_rate
        FROM grades g
        JOIN students s ON s.id = g.student_id
        LEFT JOIN teachers t ON t.id = g.teacher_id
        $w
        GROUP BY g.subject, teacher_name, s.stage
        ORDER BY g.subject, s.stage
    ");
    while ($row = mysqli_fetch_assoc($q)) {
        echo "<tr>
            <td>".esc($row['subject'])."</td>
            <td>".esc($row['teacher_name'])."</td>
            <td>".esc($row['stage'])."</td>
            <td>".esc($row['classes_text'])."</td>
            <td>".esc($row['total_students'])."</td>
            <td>".esc($row['success_rate'])."%</td>
        </tr>";
    }
    echo "</table>";
    exit;
}
?>
<!DOCTYPE html>
<html lang="ar" dir="rtl">
<head>
    <meta charset="UTF-8">
    <title>الإحصائيات والتحليل الأكاديمي — أدمن</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <style>
        body{font-family:'Cairo',Arial,sans-serif;background:#f5f6fb;margin:0;color:#222}
        .top{display:flex;justify-content:space-between;align-items:center;padding:14px 18px;background:#fff;border-bottom:1px solid #eee}
        .top .title{font-weight:700}
        .container{max-width:1250px;margin:18px auto;padding:0 16px}
        .card{background:#fff;border:1px solid #eee;border-radius:14px;box-shadow:0 8px 20px rgba(0,0,0,.03);padding:14px;margin-bottom:12px}
        .section-title{font-weight:700;margin:14px 0 8px}
        .small{font-size:12px;color:#777}
        .grid{display:grid;grid-template-columns:repeat(auto-fit,minmax(220px,1fr));gap:14px}
        .kpi{display:flex;align-items:center;gap:10px}
        .kpi .num{font-weight:800;font-size:22px}
        .kpi .tag{font-size:12px;color:#777}
        .filters{display:flex;gap:8px;flex-wrap:wrap;align-items:center;margin-bottom:6px}
        select,button,a.btn{padding:8px 10px;border:1px solid #ddd;border-radius:8px;background:#fff}
        a.btn{background:#2d6cdf;color:#fff;text-decoration:none}
        a.btn.alt{background:#6c757d}
        a.btn.green{background:#27ae60}
        a.btn.orange{background:#ff7a00}
        .table-wrap{overflow-x:auto}
        table{width:100%;border-collapse:collapse;background:#fff;border-radius:12px;overflow:hidden}
        th,td{border-bottom:1px solid #eee;padding:8px;text-align:center;font-size:13px;white-space:nowrap}
        thead th{background:#f4f6ff}
        .chart-card{padding:18px;min-height:360px;position:relative}
        .chart-holder{max-height:380px;overflow-x:auto;overflow-y:hidden}
        .chart-holder canvas{height:360px !important;max-height:360px}
        canvas{display:block}
        .badge-rank{display:inline-block;padding:2px 9px;border-radius:999px;font-size:11px;font-weight:700;color:#fff;background:#2d6cdf;margin-left:6px}
        .badge-rank.repeat{background:#e67e22}
    </style>
</head>
<body>
    <div class="top">
        <div class="title">📊 الإحصائيات والتحليل الأكاديمي</div>
        <div>
            <a class="btn alt" href="admin_dashboard.php">لوحة الأدمن</a>
        </div>
    </div>

    <div class="container" id="dashboard">
        <!-- فلاتر -->
        <div class="card">
            <div class="filters">
                <div>
                    المرحلة:
                    <select id="fStage">
                        <option value="">الكل</option>
                        <?php for ($i=0; $i<count($stages); $i++): ?>
                            <option value="<?php echo esc($stages[$i]); ?>" <?php echo ($filter_stage==$stages[$i] ? 'selected' : ''); ?>>
                                <?php echo esc($stages[$i]); ?>
                            </option>
                        <?php endfor; ?>
                    </select>
                </div>

                <!-- ✅ فلتر جديد للشعبة -->
                <div>
                    الشعبة:
                    <select id="fClass">
                        <option value="">الكل</option>
                        <?php for ($i=0; $i<count($classes); $i++): ?>
                            <option value="<?php echo esc($classes[$i]); ?>" <?php echo ($filter_class==$classes[$i] ? 'selected' : ''); ?>>
                                <?php echo esc($classes[$i]); ?>
                            </option>
                        <?php endfor; ?>
                    </select>
                </div>

                <div>
                    المادة:
                    <select id="fSubject">
                        <option value="">الكل</option>
                        <?php for ($i=0; $i<count($subjects_all); $i++): ?>
                            <option value="<?php echo esc($subjects_all[$i]); ?>" <?php echo ($filter_subject==$subjects_all[$i] ? 'selected' : ''); ?>>
                                <?php echo esc($subjects_all[$i]); ?>
                            </option>
                        <?php endfor; ?>
                    </select>
                </div>
                <div>
                    المدرّس:
                    <select id="fTeacher">
                        <option value="">الكل</option>
                        <?php for ($i=0; $i<count($teachers); $i++): ?>
                            <option value="<?php echo (int)$teachers[$i]['id']; ?>" <?php echo ($filter_teacher==$teachers[$i]['id'] ? 'selected' : ''); ?>>
                                <?php echo esc($teachers[$i]['name']); ?>
                            </option>
                        <?php endfor; ?>
                    </select>
                </div>
                <div>
                    نسبة حسب:
                    <select id="fMetric">
                        <option value="suc_m1" selected>الفصل الأول ش1</option>
                        <option value="suc_m2">الفصل الأول ش2</option>
                        <option value="suc_m3">الفصل الأول ش3</option>
                        <option value="suc_daily1">اليومي ف1</option>
                        <option value="suc_sem1">الفصل الأول</option>
                        <option value="suc_midterm">نصف السنة</option>
                        <option value="suc_m4">الفصل الثاني ش4</option>
                        <option value="suc_m5">الفصل الثاني ش5</option>
                        <option value="suc_m6">الفصل الثاني ش6</option>
                        <option value="suc_daily2">اليومي ف2</option>
                        <option value="suc_sem2">الفصل الثاني</option>
                        <option value="suc_annual">المعدل السنوي</option>
                        <option value="success_rate">النهائي</option>
                    </select>
                </div>

                <a id="btnReload" class="btn">تحديث</a>
                <a id="btnExcel" class="btn green" href="#">تصدير Excel</a>
                <a id="btnPDF" class="btn orange" href="#">تصدير PDF للجدول</a>
            </div>
        </div>

        <!-- KPIs -->
        <div class="grid">
            <div class="card">
                <div class="kpi">
                    <div class="num" id="kTotalStudents">0</div>
                    <div>
                        <div>عدد الطلبة</div>
                        <div class="tag">ضمن الفلاتر</div>
                    </div>
                </div>
            </div>
            <div class="card">
                <div class="kpi">
                    <div class="num" id="kTotalSubjects">0</div>
                    <div>
                        <div>عدد المواد</div>
                        <div class="tag">ضمن الفلاتر</div>
                    </div>
                </div>
            </div>
            <div class="card">
                <div class="kpi">
                    <div class="num" id="kTotalTeachers">0</div>
                    <div>
                        <div>عدد المدرسين</div>
                        <div class="tag">ضمن الفلاتر</div>
                    </div>
                </div>
            </div>
            <div class="card">
                <div class="kpi">
                    <div class="num" id="kPass">0</div>
                    <div>
                        <div>ناجحون</div>
                        <div class="tag">final ≥ 50</div>
                    </div>
                </div>
            </div>
            <div class="card">
                <div class="kpi">
                    <div class="num" id="kFail">0</div>
                    <div>
                        <div>راسبون</div>
                        <div class="tag">final &lt; 50</div>
                    </div>
                </div>
            </div>
            <div class="card">
                <div class="kpi">
                    <div class="num" id="kExcellent">0</div>
                    <div>
                        <div>متفوّقون</div>
                        <div class="tag">final ≥ 85</div>
                    </div>
                </div>
            </div>
        </div>

        <!-- Charts -->
        <div class="grid">
            <div class="card chart-card">
                <h3>نسبة النجاح لكل مادة (%)</h3>
                <div class="chart-holder"><canvas id="chartSubjects"></canvas></div>
                <div class="small">التمرير أفقي متاح عند كثرة المواد.</div>
            </div>
            <div class="card chart-card">
                <h3>متوسط شهري عام (ش1 → ش6)</h3>
                <div class="chart-holder"><canvas id="chartMonths"></canvas></div>
            </div>
        </div>

        <div class="card chart-card">
            <h3>متوسطات الفترات: ف1 / نصف السنة / ف2 / المعدل السنوي / النهائي</h3>
            <div class="chart-holder"><canvas id="chartTerms"></canvas></div>
        </div>

        <div class="card chart-card">
            <h3>توزيع النجاح والرسوب حسب المرحلة</h3>
            <div class="chart-holder"><canvas id="chartStages"></canvas></div>
            <div class="small">مكدّس (ناجح/راسب).</div>
        </div>

        <!-- جدول المواد -->
        <div class="section-title">تفاصيل حسب المادة</div>
        <div class="table-wrap card" id="subjectsCard">
            <table id="tblSubjects">
                <thead>
                <tr>
                    <th>المادة</th>
                    <th>المدرّس</th>
                    <th>المرحلة</th>
                    <th>الشعب</th>
                    <th>عدد الطلبة</th>
                    <th id="thSubSuccess">نسبة النجاح (الفصل الأول ش1)</th>
                </tr>
                </thead>
                <tbody>
                <tr><td colspan="6" class="small">جارِ التحميل...</td></tr>
                </tbody>
            </table>
        </div>

        <!-- أعلى الطلبة حسب المرحلة -->
        <div class="section-title">أعلى الطلبة في المرحلة (متوسط درجات كل المواد)</div>
        <div class="card">
            <div class="small" style="margin-bottom:6px">اختر مرحلة من الأعلى ليظهر أعلى الطلبة.</div>
            <div class="table-wrap">
                <table id="tblStageTop">
                    <thead>
                    <tr>
                        <th>المركز</th>
                        <th>الاسم</th>
                        <th>المرحلة</th>
                        <th>الشعبة</th>
                        <th>المعدل</th>
                        <th>عدد المواد</th>
                    </tr>
                    </thead>
                    <tbody>
                    <tr><td colspan="6" class="small">لم تُحدّد مرحلة بعد.</td></tr>
                    </tbody>
                </table>
            </div>
        </div>

        <!-- ✅ أعلى الطلبة حسب الشعبة -->
        <div class="section-title">أعلى الطلبة في الشعبة (متوسط درجات كل المواد)</div>
        <div class="card">
            <div class="small" style="margin-bottom:6px">اختر مرحلة وشعبة من الأعلى ليظهر أعلى الطلبة.</div>
            <div class="table-wrap">
                <table id="tblClassTop">
                    <thead>
                    <tr>
                        <th>المركز</th>
                        <th>الاسم</th>
                        <th>المرحلة</th>
                        <th>الشعبة</th>
                        <th>المعدل</th>
                        <th>عدد المواد</th>
                    </tr>
                    </thead>
                    <tbody>
                    <tr><td colspan="6" class="small">لم تُحدّد مرحلة/شعبة بعد.</td></tr>
                    </tbody>
                </table>
            </div>
        </div>
        <!-- تم حذف جدول المدرّسين المتأخرين بالكامل -->
    </div>

    <footer style="padding:12px;text-align:center;color:#777;font-size:12px">
        © <?php echo date('Y'); ?> — لوحة الإحصائيات الأكاديمية
    </footer>

    <!-- المكتبات -->
    <script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.1/dist/chart.umd.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/html2canvas@1.4.1/dist/html2canvas.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/jspdf@2.5.1/dist/jspdf.umd.min.js"></script>

    <script>
        let chSubjects = null,
            chMonths   = null,
            chTerms    = null,
            chStages   = null;
        let lastData  = null;

        const teacherColors = {};
        const palette       = ['#fff7e6', '#e6f7ff', '#e8f5e9', '#fce4ec', '#ede7f6', '#fff3e0', '#e0f2f1', '#f3e5f5'];
        let paletteIdx      = 0;

        function getTeacherColor(name) {
            if (!name) name = 'غير محدد';
            if (!teacherColors[name]) {
                teacherColors[name] = palette[paletteIdx % palette.length];
                paletteIdx++;
            }
            return teacherColors[name];
        }

        function qs(id) { return document.getElementById(id); }

        function currentFilters() {
            return {
                stage: qs('fStage').value,
                class: qs('fClass').value,   // ✅ فلتر الشعبة
                subject: qs('fSubject').value,
                teacher_id: qs('fTeacher').value,
                metric: qs('fMetric').value
            };
        }

        function buildQuery(params) {
            const p = [];
            for (const k in params) {
                if (params[k] !== '' && params[k] != null) {
                    p.push(encodeURIComponent(k) + '=' + encodeURIComponent(params[k]));
                }
            }
            return p.join('&');
        }

        function escapeHtml(s) {
            if (s == null) return '';
            return s.toString().replace(/[&<>"']/g, function (m) {
                return {
                    '&': '&amp;',
                    '<': '&lt;',
                    '>': '&gt;',
                    '"': '&quot;',
                    "'": '&#039;'
                }[m];
            });
        }

        async function loadData() {
            const f  = currentFilters();
            const q  = buildQuery({ajax: '1', stage: f.stage, class: f.class, subject: f.subject, teacher_id: f.teacher_id, metric: f.metric});
            const res = await fetch('admin_statistics.php?' + q);
            const data = await res.json();
            lastData = data;

            fillKpis(data.kpis);

            const metric = f.metric || 'suc_m1';
            fillTables(data, metric);
            fillStageTop(data.top_by_stage, f.stage);
            fillClassTop(data.top_by_class, f.stage, f.class); // ✅
            drawCharts(data.charts);

            const eq = buildQuery({action: 'export_excel', stage: f.stage, class: f.class, subject: f.subject, teacher_id: f.teacher_id});
            qs('btnExcel').setAttribute('href', 'admin_statistics.php?' + eq);
        }

        function fillKpis(k) {
            qs('kTotalStudents').innerText = k.total_students || 0;
            qs('kTotalSubjects').innerText = k.total_subjects || 0;
            qs('kTotalTeachers').innerText = k.total_teachers || 0;
            qs('kPass').innerText          = k.pass_cnt || 0;
            qs('kFail').innerText          = k.fail_cnt || 0;
            qs('kExcellent').innerText     = k.excellent_cnt || 0;
        }

        function fillTables(d, metric) {
            const metricNames = {
                success_rate: 'النهائي',
                suc_annual: 'المعدل السنوي',
                suc_sem1: 'الفصل الأول',
                suc_m1: 'الفصل الأول ش1',
                suc_m2: 'الفصل الأول ش2',
                suc_m3: 'الفصل الأول ش3',
                suc_daily1: 'اليومي ف1',
                suc_midterm: 'نصف السنة',
                suc_sem2: 'الفصل الثاني',
                suc_m4: 'الفصل الثاني ش4',
                suc_m5: 'الفصل الثاني ش5',
                suc_m6: 'الفصل الثاني ش6',
                suc_daily2: 'اليومي ف2'
            };

            qs('thSubSuccess').innerText   = 'نسبة النجاح (' + (metricNames[metric] || 'النهائي') + ')';

            // جدول المواد
            const tb1 = qs('tblSubjects').querySelector('tbody');
            tb1.innerHTML = '';
            if (!d.subjects_stats || d.subjects_stats.length == 0) {
                tb1.innerHTML = '<tr><td colspan="6" class="small">لا توجد بيانات</td></tr>';
            } else {
                d.subjects_stats.forEach(it => {
                    const rate = (typeof it[metric] !== 'undefined') ? it[metric] : it.success_rate;
                    const tr   = document.createElement('tr');
                    tr.style.background = getTeacherColor(it.teacher_name || 'غير محدد');
                    tr.innerHTML = `
                        <td>${escapeHtml(it.subject || '')}</td>
                        <td>${escapeHtml(it.teacher_name || 'غير محدد')}</td>
                        <td>${escapeHtml(it.stage || '')}</td>
                        <td>${escapeHtml(it.classes_text || '')}</td>
                        <td>${it.total_students || 0}</td>
                        <td>${(rate || 0)}%</td>
                    `;
                    tb1.appendChild(tr);
                });
            }
        }

        function fillStageTop(rows, stageSelected) {
            const tb = qs('tblStageTop').querySelector('tbody');
            tb.innerHTML = '';

            if (!stageSelected) {
                tb.innerHTML = '<tr><td colspan="6" class="small">اختر مرحلة من الأعلى.</td></tr>';
                return;
            }
            if (!rows || rows.length === 0) {
                tb.innerHTML = '<tr><td colspan="6" class="small">لا توجد بيانات لهذه المرحلة.</td></tr>';
                return;
            }

            rows.forEach(r => {
                const tr    = document.createElement('tr');
                const label = r.is_repeat ? (r.rank_label + ' (مكرر)') : r.rank_label;
                tr.innerHTML = `
                    <td><span class="badge-rank ${r.is_repeat ? 'repeat' : ''}">${label}</span></td>
                    <td>${escapeHtml(r.name)}</td>
                    <td>${escapeHtml(r.stage)}</td>
                    <td>${escapeHtml(r.class)}</td>
                    <td>${r.total_final}</td>
                    <td>${r.subjects}</td>
                `;
                tb.appendChild(tr);
            });
        }

        // ✅ جديد: أعلى الطلبة في الشعبة
        function fillClassTop(rows, stageSelected, classSelected) {
            const tb = qs('tblClassTop').querySelector('tbody');
            tb.innerHTML = '';

            if (!stageSelected || !classSelected) {
                tb.innerHTML = '<tr><td colspan="6" class="small">اختر مرحلة وشعبة من الأعلى.</td></tr>';
                return;
            }
            if (!rows || rows.length === 0) {
                tb.innerHTML = '<tr><td colspan="6" class="small">لا توجد بيانات لهذه الشعبة.</td></tr>';
                return;
            }

            rows.forEach(r => {
                const tr    = document.createElement('tr');
                const label = r.is_repeat ? (r.rank_label + ' (مكرر)') : r.rank_label;
                tr.innerHTML = `
                    <td><span class="badge-rank ${r.is_repeat ? 'repeat' : ''}">${label}</span></td>
                    <td>${escapeHtml(r.name)}</td>
                    <td>${escapeHtml(r.stage)}</td>
                    <td>${escapeHtml(r.class)}</td>
                    <td>${r.total_final}</td>
                    <td>${r.subjects}</td>
                `;
                tb.appendChild(tr);
            });
        }

        function drawCharts(c) {
            if (chSubjects) chSubjects.destroy();
            chSubjects = new Chart(qs('chartSubjects'), {
                type: 'bar',
                data: {
                    labels: c.subject_labels || [],
                    datasets: [
                        { label: 'نسبة النجاح %', data: c.subject_success || [] }
                    ]
                },
                options: {
                    responsive: true,
                    maintainAspectRatio: true,
                    plugins: { legend: { position: 'bottom' } },
                    scales: { y: { beginAtZero: true, max: 100 } }
                }
            });

            if (chMonths) chMonths.destroy();
            chMonths = new Chart(qs('chartMonths'), {
                type: 'line',
                data: {
                    labels: c.months_labels || [],
                    datasets: [
                        { label: 'متوسط شهري عام', data: c.months_data || [] }
                    ]
                },
                options: {
                    responsive: true,
                    maintainAspectRatio: true,
                    plugins: { legend: { position: 'bottom' } },
                    scales: { y: { beginAtZero: true, max: 100 } }
                }
            });

            if (chTerms) chTerms.destroy();
            chTerms = new Chart(qs('chartTerms'), {
                type: 'bar',
                data: {
                    labels: c.terms_labels || [],
                    datasets: [
                        { label: 'متوسط الفترة', data: c.terms_data || [] }
                    ]
                },
                options: {
                    responsive: true,
                    maintainAspectRatio: true,
                    plugins: { legend: { position: 'bottom' } },
                    scales: { y: { beginAtZero: true, max: 100 } }
                }
            });

            if (chStages) chStages.destroy();
            chStages = new Chart(qs('chartStages'), {
                type: 'bar',
                data: {
                    labels: c.stage_labels || [],
                    datasets: [
                        { label: 'ناجح', data: c.stage_pass || [], stack: 's' },
                        { label: 'راسب', data: c.stage_fail || [], stack: 's' }
                    ]
                },
                options: {
                    responsive: true,
                    maintainAspectRatio: true,
                    plugins: { legend: { position: 'bottom' } },
                    scales: {
                        x: { stacked: true },
                        y: { stacked: true, beginAtZero: true }
                    }
                }
            });
        }

        qs('btnReload').addEventListener('click', () => {
            const f    = currentFilters();
            const link = 'admin_statistics.php?' + buildQuery(f);
            history.replaceState(null, '', link);
            loadData();
        });

        qs('fStage').addEventListener('change', () => {
            const f    = currentFilters();
            const link = 'admin_statistics.php?' + buildQuery(f);
            history.replaceState(null, '', link);
            loadData();
        });

        // ✅ عند تغيير الشعبة
        qs('fClass').addEventListener('change', () => {
            const f    = currentFilters();
            const link = 'admin_statistics.php?' + buildQuery(f);
            history.replaceState(null, '', link);
            loadData();
        });

        qs('fMetric').addEventListener('change', () => {
            const f    = currentFilters();
            const link = 'admin_statistics.php?' + buildQuery(f);
            history.replaceState(null, '', link);
            loadData();
        });

        // تصدير PDF للجدول فقط
        qs('btnPDF').addEventListener('click', async () => {
            const { jsPDF } = window.jspdf;
            const tableEl   = qs('subjectsCard');

            const canvas = await html2canvas(tableEl, {
                scale: 4,
                useCORS: true,
                background: '#ffffff',
                windowWidth: tableEl.scrollWidth
            });

            const pdf   = new jsPDF({orientation: 'landscape', unit: 'px', format: 'a4'});
            const pageW = pdf.internal.pageSize.getWidth();
            const pageH = pdf.internal.pageSize.getHeight();

            const marginX    = 20;
            const headerH    = 90;
            const bodyH      = pageH - headerH - 20;
            const availableW = pageW - marginX * 2;

            const imgW       = availableW;
            const imgH       = canvas.height * imgW / canvas.width;

            let remainingH = imgH;
            let srcY       = 0;
            const sliceH   = (canvas.height * bodyH) / imgH;

            const logoUrl = 'logo.png';
            const logoW   = 70;
            const logoH   = 70;
            const logoX   = (pageW - logoW) / 2;

            let isFirst = true;
            while (remainingH > 0) {
                if (!isFirst) {
                    pdf.addPage();
                }
                isFirst = false;

                try {
                    pdf.addImage(logoUrl, 'PNG', logoX, 10, logoW, logoH);
                } catch (e) {}

                const pageCanvas  = document.createElement('canvas');
                pageCanvas.width  = canvas.width;
                pageCanvas.height = Math.min(sliceH, canvas.height - srcY);
                const ctx         = pageCanvas.getContext('2d');

                ctx.fillStyle = '#ffffff';
                ctx.fillRect(0, 0, pageCanvas.width, pageCanvas.height);
                ctx.drawImage(
                    canvas,
                    0, srcY,
                    canvas.width, pageCanvas.height,
                    0, 0,
                    pageCanvas.width, pageCanvas.height
                );

                const partImg  = pageCanvas.toDataURL('image/png');
                const partImgH = pageCanvas.height * imgW / pageCanvas.width;

                pdf.addImage(partImg, 'PNG', marginX, headerH, imgW, partImgH, undefined, 'FAST');

                srcY       += sliceH;
                remainingH -= partImgH;
            }

            pdf.save('subjects_table.pdf');
        });

        // أول تحميل
        loadData();
    </script>
</body>
</html>
