<?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;

/* ======================
   ✅ مهم جداً:
   1) نعتمد أحدث سجل لكل (طالب + مادة) حسب saved_at ثم id
   2) نطبّع اسم المادة بإزالة (ال) + trim + lower
   ====================== */
function subj_norm_expr($alias) {
    return "TRIM(LOWER(REPLACE($alias.subject,'ال','')))";
}

/* ✅ هذا الاستعلام ينتج "جدول افتراضي" يحتوي أحدث سجل فقط لكل (student_id + subject_norm) */
$LATEST_GRADES = "(
    SELECT g1.*
    FROM grades g1
    LEFT JOIN grades g2
        ON g1.student_id = g2.student_id
        AND " . subj_norm_expr('g1') . " = " . subj_norm_expr('g2') . "
        AND (
            COALESCE(g2.saved_at,'1970-01-01') > COALESCE(g1.saved_at,'1970-01-01')
            OR (
                COALESCE(g2.saved_at,'1970-01-01') = COALESCE(g1.saved_at,'1970-01-01')
                AND g2.id > g1.id
            )
        )
    WHERE g1.subject IS NOT NULL AND TRIM(g1.subject) <> ''
      AND g2.id IS NULL
)";

/* ======================
   ✅ الفصل الأول (semester1)
   ✅ هنا نستخدم الدرجة كما هي من القاعدة (بدون تطبيع *3)
   ✅ والأهم: عند حساب النسب سنستبعد 0 من المقام
   ====================== */
$USE_SEM1_NORM = false;

$SEM1_REF = "GREATEST(
    IFNULL(g.month1,0),
    IFNULL(g.month2,0),
    IFNULL(g.month3,0),
    IFNULL(g.daily1,0),
    IFNULL(g.midterm,0)
)";

if ($USE_SEM1_NORM) {
    $SEM1_NORM = "(CASE
        WHEN g.semester1 IS NULL OR g.semester1 <= 0 THEN NULL
        WHEN g.semester1 <= 40
             AND $SEM1_REF >= 50
             AND g.semester1 < ($SEM1_REF / 2)
        THEN LEAST(100, g.semester1 * 3)
        ELSE g.semester1
    END)";
} else {
    $SEM1_NORM = "g.semester1";
}

function metric_expr($col) {
    $col = trim($col);
    if ($col === 'semester1') {
        return $GLOBALS['SEM1_NORM'];
    }
    return "g.`$col`";
}

/* ======================
   ✅✅✅ (تعديلنا الوحيد) — دوال حساب الفصل الأول Live مثل الكود الصحيح السابق
   ====================== */
function round_half_up_int($v) {
    $v = (float)$v;
    $i = floor($v);
    return ($v - $i >= 0.5) ? $i + 1 : $i;
}
function top3_avg_admin($arr) {
    $clean = array();
    foreach ($arr as $v) $clean[] = (float)$v;
    rsort($clean);
    $top3 = array_slice($clean, 0, 3);
    $sum = array_sum($top3);
    $count = count($top3) > 0 ? count($top3) : 1;
    return round($sum / $count, 2);
}
function avg_if_two_only($arr) {
    $real = array();
    foreach ($arr as $v) {
        $v = (float)$v;
        if ($v > 0) $real[] = $v;
    }
    if (count($real) == 2) {
        return round(array_sum($real) / 2, 2);
    }
    return null;
}
function norm_stage_live($s){
    $s = trim($s);
    $s = str_replace(array('أ','إ','آ'), 'ا', $s);
    $s = str_replace(array('الأ','ال',' '), array('ا','', ''), $s);
    $s = strtolower($s);
    return $s;
}
function norm_subject_live($s){
    $s = trim($s);
    $s = str_replace(array('أ','إ','آ'), 'ا', $s);
    $s = str_replace(array('ى'), 'ي', $s);
    $s = str_replace(array('ة'), 'ه', $s);
    $s = str_replace(array('ـ'), '', $s);
    $s = str_replace(array('َ','ً','ُ','ٌ','ِ','ٍ','ْ','ّ'), '', $s);
    $s = str_replace(array('ال',' '), array('',''), $s);
    $s = strtolower($s);
    return $s;
}
function use_special_sem1_rule_live($stage, $subject){

    $ns  = norm_stage_live($stage);
    $sub = norm_subject_live($subject);

    $allowedStages = array('اولمتوسط','ثانيمتوسط','رابععلمي','خامسعلمي','سادسعلمي');

    $allowedSubjectsCommon = array(
        'تربيهاسلاميه',
        'لغهانكليزيه',
        'لغهعربيه',
        'لغعربيه',
        'لغهفرنسيه',
        'لغفرنسيه'
    );

    $allowedSubjectsWithKurdish = array_merge($allowedSubjectsCommon, array(
        'لغهكرديه',
        'لغكرديه'
    ));

    if (!in_array($ns, $allowedStages)) return false;

    // ✅ السادس العلمي: بدون الكردي
    if ($ns === 'سادسعلمي') {
        return in_array($sub, $allowedSubjectsCommon);
    }

    return in_array($sub, $allowedSubjectsWithKurdish);
}
function sem1_best2_months_live($m1, $m2, $m3){
    $vals = array(
        'month1' => (float)$m1,
        'month2' => (float)$m2,
        'month3' => (float)$m3
    );
    $min_key = null;
    $min_val = null;
    foreach ($vals as $k => $v) {
        if ($min_val === null || $v < $min_val) {
            $min_val = $v;
            $min_key = $k;
        }
    }
    $sum2 = ((float)$m1 + (float)$m2 + (float)$m3) - (float)$min_val;
    $avg2 = round($sum2 / 2, 2);
    return array($avg2, $min_key);
}
function calc_sem1_live($stage, $subject, $month1, $month2, $month3, $daily1){

    $m1 = ($month1 === null || $month1 === '' ? 0 : (float)$month1);
    $m2 = ($month2 === null || $month2 === '' ? 0 : (float)$month2);
    $m3 = ($month3 === null || $month3 === '' ? 0 : (float)$month3);
    $d1 = ($daily1 === null || $daily1 === '' ? 0 : (float)$daily1);

    $use_special = use_special_sem1_rule_live($stage, $subject);

    // ✅ special: إذا اليومي1 غائب -> نحذف أقل شهر ونقسم على 2
    if ($use_special && $d1 <= 0) {
        $tmp = sem1_best2_months_live($m1, $m2, $m3);
        $sem1 = $tmp[0];
    } else {
        // ✅ درجتين فقط؟
        $twoOnly = avg_if_two_only(array($m1, $m2, $m3, $d1));
        if ($twoOnly !== null) {
            $sem1 = $twoOnly;
        } else {
            if ($d1 > 0) {
                $sem1 = top3_avg_admin(array($m1, $m2, $m3, $d1));
            } else {
                $sem1 = round(($m1 + $m2 + $m3) / 3, 2);
            }
        }
    }

    // ✅ override درجتين فقط (كما في منطقك)
    $twoOnly2 = avg_if_two_only(array($m1, $m2, $m3, $d1));
    if ($twoOnly2 !== null) {
        $sem1 = $twoOnly2;
    }

    return round_half_up_int($sem1);
}

/* ======================
   مصادر الفلاتر
   ====================== */
$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 MIN(g.subject) AS subject
    FROM $LATEST_GRADES g
    WHERE g.subject <> ''
    GROUP BY " . subj_norm_expr('g') . "
    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 Builder
   ====================== */
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 " . subj_norm_expr('g') . " = 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;
}

/* ======================
   تحويل metric إلى عمود
   ====================== */
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';
    $metric_col  = metric_to_column($metric_req);

    $w = build_where($filter_stage, $filter_subject, $filter_teacher, $filter_class);

    /* ======================
       KPIs (ضمن الفلاتر)
       ====================== */

    // إجمالي الطلبة (ضمن الفلاتر)
    $total_students = 0;
    $q = mysqli_query($conn, "
        SELECT COUNT(DISTINCT s.id) c
        FROM $LATEST_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 " . subj_norm_expr('g') . ") c
        FROM $LATEST_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 $LATEST_GRADES g
        JOIN students s ON s.id = g.student_id
        $w
    ");
    if ($q && $row = mysqli_fetch_assoc($q)) {
        $total_teachers = (int)$row['c'];
    }

    /* ✅ KPI نجاح/رسوب/تفوق حسب (نوع الدرجة المختارة)
       ✅ الأهم: لا نعتبر الدرجة 0 رسوباً، بل (غير مدخلة) ونستبعدها من الحساب
    */
    $pass_cnt = $fail_cnt = $excellent_cnt = 0;

    $allowedMetricCols = array(
        'month1','month2','month3','daily1','semester1','midterm',
        'month4','month5','month6','daily2','semester2','annual_average','final_result'
    );
    if (!in_array($metric_col, $allowedMetricCols, true)) {
        $metric_col = 'final_result';
    }

    $metricExpr = metric_expr($metric_col);

    $q = mysqli_query($conn, "
        SELECT
            SUM(CASE WHEN x.fail_subjects = 0 THEN 1 ELSE 0 END) AS pass_cnt,
            SUM(CASE WHEN x.fail_subjects > 0 THEN 1 ELSE 0 END) AS fail_cnt,
            SUM(CASE WHEN x.fail_subjects = 0 AND x.avg_score >= $EXCELLENT THEN 1 ELSE 0 END) AS excellent_cnt
        FROM (
            SELECT
                g.student_id,
                AVG(CASE
                        WHEN $metricExpr IS NOT NULL AND $metricExpr > 0
                        THEN $metricExpr
                        ELSE NULL
                    END
                ) AS avg_score,
                SUM(CASE
                        WHEN $metricExpr IS NOT NULL AND $metricExpr > 0 AND $metricExpr < $PASS_MARK
                        THEN 1
                        ELSE 0
                    END
                ) AS fail_subjects
            FROM $LATEST_GRADES g
            JOIN students s ON s.id = g.student_id
            $w
            GROUP BY g.student_id
            HAVING AVG(CASE WHEN $metricExpr IS NOT NULL AND $metricExpr > 0 THEN $metricExpr ELSE NULL END) IS NOT NULL
        ) x
    ");
    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'];
    }

    /* ====== إحصائيات حسب المادة ======
       ✅✅✅ هنا فقط صار التعديل:
       - نحسب suc_sem1 Live بالـ PHP مثل المنطق الصحيح
       - بقية النسب تبقى نفسها تماماً
    */
    $subjects_stats = [];

    // نجلب الصفوف الخام ضمن الفلاتر (من أحدث الدرجات)
    $raw = [];
    $q = mysqli_query($conn, "
        SELECT
            g.student_id,
            g.subject,
            g.teacher_id,
            COALESCE(t.name,'غير محدد') AS teacher_name,
            s.stage,
            s.class,
            g.month1, g.month2, g.month3, g.daily1,
            g.midterm,
            g.month4, g.month5, g.month6, g.daily2,
            g.semester2, g.annual_average, g.final_result
        FROM $LATEST_GRADES g
        JOIN students s ON s.id = g.student_id
        LEFT JOIN teachers t ON t.id = g.teacher_id
        $w
    ");
    while ($row = mysqli_fetch_assoc($q)) {
        $raw[] = $row;
    }

    // تجميع حسب (subject_norm + teacher_id + stage)
    $groups = []; // key => agg
    foreach ($raw as $r) {

        $subj = (string)$r['subject'];
        $sub_norm = trim(strtolower(str_replace('ال','', $subj)));

        $tid = (int)$r['teacher_id'];
        $stage = (string)$r['stage'];
        $key = $sub_norm . '|' . $tid . '|' . $stage;

        if (!isset($groups[$key])) {
            $groups[$key] = [
                'subject_min' => null,
                'teacher_name' => ($r['teacher_name'] ? $r['teacher_name'] : 'غير محدد'),
                'stage' => $stage,
                'classes' => [],
                'students' => [],

                'den_m1'=>0,'pass_m1'=>0,
                'den_m2'=>0,'pass_m2'=>0,
                'den_m3'=>0,'pass_m3'=>0,
                'den_d1'=>0,'pass_d1'=>0,
                'den_sem1'=>0,'pass_sem1'=>0,
                'den_mid'=>0,'pass_mid'=>0,
                'den_m4'=>0,'pass_m4'=>0,
                'den_m5'=>0,'pass_m5'=>0,
                'den_m6'=>0,'pass_m6'=>0,
                'den_d2'=>0,'pass_d2'=>0,
                'den_sem2'=>0,'pass_sem2'=>0,
                'den_ann'=>0,'pass_ann'=>0,
                'den_fin'=>0,'pass_fin'=>0,
            ];
        }

        // MIN(subject) مثل MySQL MIN
        if ($groups[$key]['subject_min'] === null || strcmp($subj, $groups[$key]['subject_min']) < 0) {
            $groups[$key]['subject_min'] = $subj;
        }

        // classes
        $cls = (string)$r['class'];
        if ($cls !== '') $groups[$key]['classes'][$cls] = true;

        // distinct students
        $sid = (int)$r['student_id'];
        $groups[$key]['students'][$sid] = true;

        // helper
        $pm = (float)$PASS_MARK;

        // m1
        $v = (float)$r['month1'];
        if ($v > 0) { $groups[$key]['den_m1']++; if ($v >= $pm) $groups[$key]['pass_m1']++; }

        // m2
        $v = (float)$r['month2'];
        if ($v > 0) { $groups[$key]['den_m2']++; if ($v >= $pm) $groups[$key]['pass_m2']++; }

        // m3
        $v = (float)$r['month3'];
        if ($v > 0) { $groups[$key]['den_m3']++; if ($v >= $pm) $groups[$key]['pass_m3']++; }

        // daily1
        $v = (float)$r['daily1'];
        if ($v > 0) { $groups[$key]['den_d1']++; if ($v >= $pm) $groups[$key]['pass_d1']++; }

        // ✅ sem1 LIVE (التعديل المهم)
        $sem1_live = calc_sem1_live($stage, $subj, $r['month1'], $r['month2'], $r['month3'], $r['daily1']);
        if ($sem1_live > 0) { $groups[$key]['den_sem1']++; if ($sem1_live >= $pm) $groups[$key]['pass_sem1']++; }

        // midterm
        $v = (float)$r['midterm'];
        if ($v > 0) { $groups[$key]['den_mid']++; if ($v >= $pm) $groups[$key]['pass_mid']++; }

        // m4
        $v = (float)$r['month4'];
        if ($v > 0) { $groups[$key]['den_m4']++; if ($v >= $pm) $groups[$key]['pass_m4']++; }

        // m5
        $v = (float)$r['month5'];
        if ($v > 0) { $groups[$key]['den_m5']++; if ($v >= $pm) $groups[$key]['pass_m5']++; }

        // m6
        $v = (float)$r['month6'];
        if ($v > 0) { $groups[$key]['den_m6']++; if ($v >= $pm) $groups[$key]['pass_m6']++; }

        // daily2
        $v = (float)$r['daily2'];
        if ($v > 0) { $groups[$key]['den_d2']++; if ($v >= $pm) $groups[$key]['pass_d2']++; }

        // sem2
        $v = (float)$r['semester2'];
        if ($v > 0) { $groups[$key]['den_sem2']++; if ($v >= $pm) $groups[$key]['pass_sem2']++; }

        // annual
        $v = (float)$r['annual_average'];
        if ($v > 0) { $groups[$key]['den_ann']++; if ($v >= $pm) $groups[$key]['pass_ann']++; }

        // final
        $v = (float)$r['final_result'];
        if ($v > 0) { $groups[$key]['den_fin']++; if ($v >= $pm) $groups[$key]['pass_fin']++; }
    }

    // بناء subjects_stats بنفس نفس مفاتيحك القديمة
    foreach ($groups as $g) {

        $classes = array_keys($g['classes']);
        sort($classes);
        $classes_text = implode(', ', $classes);

        $rate = function($pass,$den){
            if ($den <= 0) return 0;
            return round(($pass / $den) * 100, 2);
        };

        $subjects_stats[] = [
            'subject' => $g['subject_min'],
            'teacher_name' => $g['teacher_name'],
            'stage' => $g['stage'],
            'classes_text' => $classes_text,
            'total_students' => count($g['students']),

            'suc_m1' => $rate($g['pass_m1'], $g['den_m1']),
            'suc_m2' => $rate($g['pass_m2'], $g['den_m2']),
            'suc_m3' => $rate($g['pass_m3'], $g['den_m3']),
            'suc_daily1' => $rate($g['pass_d1'], $g['den_d1']),

            // ✅ هنا الصحيح
            'suc_sem1' => $rate($g['pass_sem1'], $g['den_sem1']),

            'suc_midterm' => $rate($g['pass_mid'], $g['den_mid']),
            'suc_m4' => $rate($g['pass_m4'], $g['den_m4']),
            'suc_m5' => $rate($g['pass_m5'], $g['den_m5']),
            'suc_m6' => $rate($g['pass_m6'], $g['den_m6']),
            'suc_daily2' => $rate($g['pass_d2'], $g['den_d2']),
            'suc_sem2' => $rate($g['pass_sem2'], $g['den_sem2']),
            'suc_annual' => $rate($g['pass_ann'], $g['den_ann']),
            'success_rate' => $rate($g['pass_fin'], $g['den_fin']),
        ];
    }

    // نفس ترتيب SQL القديم تقريباً
    usort($subjects_stats, function($a,$b){
        $c = strcmp($a['subject'], $b['subject']);
        if ($c !== 0) return $c;
        $c = strcmp($a['stage'], $b['stage']);
        if ($c !== 0) return $c;
        return strcmp($a['teacher_name'], $b['teacher_name']);
    });

    /* ====== إحصائيات حسب المدرس ====== */
    $teachers_stats = [];
    $q = mysqli_query($conn, "
        SELECT
            COALESCE(t.name,'غير محدد') AS teacher_name,
            COUNT(DISTINCT g.student_id) AS total_students,

            ROUND(AVG(CASE WHEN g.final_result > 0 THEN g.final_result ELSE NULL END),2)     AS avg_final,
            ROUND(AVG(CASE WHEN g.annual_average > 0 THEN g.annual_average ELSE NULL END),2) AS avg_annual,
            ROUND(AVG(CASE WHEN $SEM1_NORM > 0 THEN $SEM1_NORM ELSE NULL END),2)             AS avg_sem1,
            ROUND(AVG(CASE WHEN g.midterm > 0 THEN g.midterm ELSE NULL END),2)               AS avg_midterm,
            ROUND(AVG(CASE WHEN g.semester2 > 0 THEN g.semester2 ELSE NULL END),2)           AS avg_sem2,

            ROUND(
                SUM(CASE WHEN g.month1 >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.month1 IS NOT NULL AND 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 IS NOT NULL AND 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 IS NOT NULL AND 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 IS NOT NULL AND g.daily1 > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS suc_daily1,

            ROUND(
                SUM(CASE WHEN $SEM1_NORM >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN $SEM1_NORM IS NOT NULL AND $SEM1_NORM > 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 IS NOT NULL AND 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 IS NOT NULL AND 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 IS NOT NULL AND 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 IS NOT NULL AND 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 IS NOT NULL AND 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 IS NOT NULL AND 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 IS NOT NULL AND 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 IS NOT NULL AND g.final_result > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS success_rate

        FROM $LATEST_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;
    }

    /* ====== توزيع النجاح/الرسوب حسب المرحلة (بنفس منطق KPI + حسب الدرجة المختارة) ====== */
    $stages_distribution = [];
    $chart_stage_labels  = [];
    $chart_stage_pass    = [];
    $chart_stage_fail    = [];

    $q = mysqli_query($conn, "
        SELECT
            x.stage,
            SUM(CASE WHEN x.fail_subjects = 0 THEN 1 ELSE 0 END) AS pass_cnt,
            SUM(CASE WHEN x.fail_subjects > 0 THEN 1 ELSE 0 END) AS fail_cnt
        FROM (
            SELECT
                s.stage,
                g.student_id,
                AVG(CASE WHEN $metricExpr IS NOT NULL AND $metricExpr > 0 THEN $metricExpr ELSE NULL END) AS avg_score,
                SUM(CASE WHEN $metricExpr IS NOT NULL AND $metricExpr > 0 AND $metricExpr < $PASS_MARK THEN 1 ELSE 0 END) AS fail_subjects
            FROM $LATEST_GRADES g
            JOIN students s ON s.id = g.student_id
            $w
            GROUP BY s.stage, g.student_id
            HAVING AVG(CASE WHEN $metricExpr IS NOT NULL AND $metricExpr > 0 THEN $metricExpr ELSE NULL END) IS NOT NULL
        ) x
        GROUP BY x.stage
        ORDER BY FIELD(x.stage,'أول متوسط','ثاني متوسط','ثالث متوسط','رابع علمي','خامس علمي','سادس علمي'), x.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 $LATEST_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';
        }

        $scoreExpr = "g.`$colName`";
        if ($colName === 'semester1') {
            $scoreExpr = $SEM1_NORM;
        }

        $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 $scoreExpr IS NOT NULL AND $scoreExpr > 0
                        THEN $scoreExpr
                        ELSE 0
                    END
                ) AS sum_score,
                SUM(
                    CASE
                        WHEN $scoreExpr IS NOT NULL AND $scoreExpr > 0
                        THEN 1
                        ELSE 0
                    END
                ) AS cnt_valid,
                ROUND(
                    SUM(
                        CASE
                            WHEN $scoreExpr IS NOT NULL AND $scoreExpr > 0
                            THEN $scoreExpr
                            ELSE 0
                        END
                    )
                    / NULLIF(
                        SUM(
                            CASE
                                WHEN $scoreExpr IS NOT NULL AND $scoreExpr > 0
                                THEN 1
                                ELSE 0
                            END
                        ),0
                    ),2
                ) AS avg_score
            FROM students s
            JOIN $LATEST_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' => $score,
                'subjects'    => (int)$r['cnt_valid']
            ];
        }

        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';
        }

        $scoreExpr = "g.`$colName`";
        if ($colName === 'semester1') {
            $scoreExpr = $SEM1_NORM;
        }

        $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 $scoreExpr IS NOT NULL AND $scoreExpr > 0
                        THEN $scoreExpr
                        ELSE 0
                    END
                ) AS sum_score,
                SUM(
                    CASE
                        WHEN $scoreExpr IS NOT NULL AND $scoreExpr > 0
                        THEN 1
                        ELSE 0
                    END
                ) AS cnt_valid,
                ROUND(
                    SUM(
                        CASE
                            WHEN $scoreExpr IS NOT NULL AND $scoreExpr > 0
                            THEN $scoreExpr
                            ELSE 0
                        END
                    )
                    / NULLIF(
                        SUM(
                            CASE
                                WHEN $scoreExpr IS NOT NULL AND $scoreExpr > 0
                                THEN 1
                                ELSE 0
                            END
                        ),0
                    ),2
                ) AS avg_score
            FROM students s
            JOIN $LATEST_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'];

        $metricKey = $metric_req;
        $val = 0;
        if ($metricKey && isset($ss[$metricKey])) {
            $val = (float)$ss[$metricKey];
        } else {
            $val = (float)$ss['success_rate'];
        }
        $chart_subject_success[]  = $val;
        $chart_subject_finalavg[] = 0;
    }

    /* ✅ المتوسطات الشهرية: استبعاد 0 من المتوسط */
    $avg_months = ['m1'=>0,'m2'=>0,'m3'=>0,'m4'=>0,'m5'=>0,'m6'=>0];
    $q = mysqli_query($conn, "
        SELECT
            ROUND(AVG(CASE WHEN g.month1 > 0 THEN g.month1 ELSE NULL END),2) AS m1,
            ROUND(AVG(CASE WHEN g.month2 > 0 THEN g.month2 ELSE NULL END),2) AS m2,
            ROUND(AVG(CASE WHEN g.month3 > 0 THEN g.month3 ELSE NULL END),2) AS m3,
            ROUND(AVG(CASE WHEN g.month4 > 0 THEN g.month4 ELSE NULL END),2) AS m4,
            ROUND(AVG(CASE WHEN g.month5 > 0 THEN g.month5 ELSE NULL END),2) AS m5,
            ROUND(AVG(CASE WHEN g.month6 > 0 THEN g.month6 ELSE NULL END),2) AS m6
        FROM $LATEST_GRADES g
        JOIN students s ON s.id = g.student_id
        $w
    ");
    if ($q && $row = mysqli_fetch_assoc($q)) {
        $avg_months = $row;
    }

    /* ✅ متوسطات الفترات: استبعاد 0 من المتوسط */
    $avg_terms = ['sem1'=>0,'midterm'=>0,'sem2'=>0,'annual'=>0,'final'=>0];
    $q = mysqli_query($conn, "
        SELECT
            ROUND(AVG(CASE WHEN $SEM1_NORM > 0 THEN $SEM1_NORM ELSE NULL END),2) AS sem1,
            ROUND(AVG(CASE WHEN g.midterm > 0 THEN g.midterm ELSE NULL END),2) AS midterm,
            ROUND(AVG(CASE WHEN g.semester2 > 0 THEN g.semester2 ELSE NULL END),2) AS sem2,
            ROUND(AVG(CASE WHEN g.annual_average > 0 THEN g.annual_average ELSE NULL END),2) AS annual,
            ROUND(AVG(CASE WHEN g.final_result > 0 THEN g.final_result ELSE NULL END),2) AS final
        FROM $LATEST_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
            MIN(g.subject) AS subject,
            COALESCE(t.name,'غير محدد') AS teacher_name,
            s.stage,
            GROUP_CONCAT(DISTINCT s.class ORDER BY s.class SEPARATOR ', ') AS classes_text,
            COUNT(DISTINCT g.student_id) AS total_students,
            ROUND(
                SUM(CASE WHEN g.final_result >= $PASS_MARK THEN 1 ELSE 0 END)
                / NULLIF(SUM(CASE WHEN g.final_result IS NOT NULL AND g.final_result > 0 THEN 1 ELSE 0 END), 0) * 100
            ,2) AS success_rate
        FROM $LATEST_GRADES g
        JOIN students s ON s.id = g.student_id
        LEFT JOIN teachers t ON t.id = g.teacher_id
        $w
        GROUP BY " . subj_norm_expr('g') . ", g.teacher_id, s.stage
        ORDER BY 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">لا يوجد مادة &lt; 50</div>
                    </div>
                </div>
            </div>
            <div class="card">
                <div class="kpi">
                    <div class="num" id="kFail">0</div>
                    <div>
                        <div>راسبون/مكملون</div>
                        <div class="tag">يوجد مادة &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">ناجح + ≥ 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>
