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

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

/* ======================
   دالة تركيب WHERE
   ====================== */
function build_where($stage, $subject, $teacher_id) {
    $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 ";
    }
    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);

    // إجمالي الطلبة
    $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)/COUNT(*)*100,2) AS suc_m1,
            ROUND(SUM(CASE WHEN g.month2         >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_m2,
            ROUND(SUM(CASE WHEN g.month3         >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_m3,
            ROUND(SUM(CASE WHEN g.daily1         >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_daily1,
            ROUND(SUM(CASE WHEN g.semester1      >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_sem1,
            ROUND(SUM(CASE WHEN g.midterm        >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_midterm,
            ROUND(SUM(CASE WHEN g.month4         >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_m4,
            ROUND(SUM(CASE WHEN g.month5         >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_m5,
            ROUND(SUM(CASE WHEN g.month6         >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_m6,
            ROUND(SUM(CASE WHEN g.daily2         >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_daily2,
            ROUND(SUM(CASE WHEN g.semester2      >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_sem2,
            ROUND(SUM(CASE WHEN g.annual_average >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_annual,
            ROUND(SUM(CASE WHEN g.final_result   >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*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;
    }

    /* ====== إحصائيات حسب المدرس (نخليها في الـ JSON لكن ما نعرضها) ====== */
    $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)/COUNT(*)*100,2) AS suc_m1,
            ROUND(SUM(CASE WHEN g.month2         >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_m2,
            ROUND(SUM(CASE WHEN g.month3         >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_m3,
            ROUND(SUM(CASE WHEN g.daily1         >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_daily1,
            ROUND(SUM(CASE WHEN g.semester1      >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_sem1,
            ROUND(SUM(CASE WHEN g.midterm        >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_midterm,
            ROUND(SUM(CASE WHEN g.month4         >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_m4,
            ROUND(SUM(CASE WHEN g.month5         >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_m5,
            ROUND(SUM(CASE WHEN g.month6         >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_m6,
            ROUND(SUM(CASE WHEN g.daily2         >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_daily2,
            ROUND(SUM(CASE WHEN g.semester2      >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_sem2,
            ROUND(SUM(CASE WHEN g.annual_average >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS suc_annual,
            ROUND(SUM(CASE WHEN g.final_result   >= $PASS_MARK THEN 1 ELSE 0 END)/COUNT(*)*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) نخليهم في الـ JSON بس ====== */
    $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);

        $q = mysqli_query($conn, "
            SELECT
                s.id AS student_id,
                s.name,
                s.stage,
                s.class,
                SUM(COALESCE(g.final_result,0)) AS total_final,
                COUNT(*) AS cnt_subj
            FROM students s
            JOIN grades g ON g.student_id = s.id
            WHERE s.stage = '$st'
            GROUP BY s.id, s.name, s.stage, s.class
            ORDER BY total_final DESC, s.name ASC
            LIMIT 200
        ");

        $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['total_final'];
            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                         $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_subj']
            ];
        }

        foreach ($with_ranks as $wr) {
            $cnt = count($top_by_stage);
            if ($cnt < 3) {
                $wr['is_repeat'] = 0;
                if ($cnt > 0 && (float)$wr['total_final'] == (float)$top_by_stage[$cnt - 1]['total_final']) {
                    $wr['is_repeat'] = 1;
                }
                $top_by_stage[] = $wr;
            } elseif ($cnt == 3) {
                if ((float)$wr['total_final'] == (float)$top_by_stage[2]['total_final']) {
                    $wr['is_repeat'] = 1;
                    $top_by_stage[]  = $wr;
                }
                break;
            } else {
                break;
            }
        }
    }

    /* ====== متوسطات للرسوم ====== */
    $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;
    }

    /* ====== المدرّسون المتأخرون عن رفع الدرجات للبند المحدد ====== */
    $missing_teachers = [];
    if ($col_for_missing !== '') {

        // 1) كل المدرسين اللي ظهروا في درجات ضمن الفلاتر (أي صف)
        $teachers_in_grades = [];
        $q = mysqli_query($conn, "
            SELECT DISTINCT g.teacher_id
            FROM grades g
            JOIN students s ON s.id = g.student_id
            $w
            AND g.teacher_id IS NOT NULL
            AND g.teacher_id <> 0
        ");
        if ($q) {
            while ($row = mysqli_fetch_assoc($q)) {
                $teachers_in_grades[(int)$row['teacher_id']] = true;
            }
        }

        // 2) المدرسين اللي عندهم صفوف لكن العمود المطلوب كلّه فاضي
        $missing_by_metric_ids = [];
        $q = mysqli_query($conn, "
            SELECT
                g.teacher_id,
                t.name AS teacher_name,
                g.subject,
                s.stage,
                s.class,
                COUNT(*) AS total_rows,
                SUM(
                    CASE
                        WHEN g.`$col_for_missing` IS NOT NULL
                             AND g.`$col_for_missing` <> 0
                        THEN 1 ELSE 0
                    END
                ) AS filled_cnt
            FROM grades g
            JOIN students s ON s.id = g.student_id
            JOIN teachers t ON t.id = g.teacher_id
            $w
              AND g.teacher_id IS NOT NULL
              AND g.teacher_id <> 0
            GROUP BY g.teacher_id, t.name, g.subject, s.stage, s.class
            HAVING COALESCE(filled_cnt,0) = 0
            ORDER BY teacher_name, g.subject, s.stage, s.class
        ");
        if ($q) {
            while ($row = mysqli_fetch_assoc($q)) {
                $missing_teachers[] = [
                    'teacher_id'   => (int)$row['teacher_id'],
                    'teacher_name' => $row['teacher_name'],
                    'subject'      => $row['subject'],
                    'stage'        => $row['stage'],
                    'class'        => $row['class'],
                    'total_rows'   => (int)$row['total_rows'],
                ];
                $missing_by_metric_ids[(int)$row['teacher_id']] = true;
            }
        }

        // 3) المدرسين الموجودين في جدول teachers لكن ما ظهروا في درجات الفلاتر
        foreach ($teachers as $t) {
            $tid = (int)$t['id'];
            if ($tid <= 0) continue;

            if (isset($teachers_in_grades[$tid])) {
                continue;
            }

            if (isset($missing_by_metric_ids[$tid])) {
                continue;
            }

            $missing_teachers[] = [
                'teacher_id'   => $tid,
                'teacher_name' => $t['name'],
                'subject'      => '—',
                'stage'        => '—',
                'class'        => '—',
                'total_rows'   => 0
            ];
        }
    }

    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,
        "missing_teachers"    => $missing_teachers,
        "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);

    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)/COUNT(*)*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="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>

        <!-- Subject table -->
        <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="table-wrap card">
            <table id="tblMissing">
                <thead>
                <tr>
                    <th>#</th>
                    <th>اسم المدرّس</th>
                    <th>المادة</th>
                    <th>الصف</th>
                    <th>الشعبة</th>
                </tr>
                </thead>
                <tbody>
                <tr><td colspan="5" class="small">جارِ التحميل...</td></tr>
                </tbody>
            </table>
        </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,
                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, 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);
            fillMissing(data.missing_teachers);
            drawCharts(data.charts);

            const eq = buildQuery({action: 'export_excel', stage: f.stage, 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 fillMissing(list) {
            const tb = qs('tblMissing').querySelector('tbody');
            tb.innerHTML = '';

            if (!list || list.length === 0) {
                tb.innerHTML = '<tr><td colspan="5" class="small">لا يوجد مدرّسون متأخرون لهذا البند.</td></tr>';
                return;
            }

            let i = 1;
            list.forEach(row => {
                const tr = document.createElement('tr');
                tr.innerHTML = `
                    <td>${i++}</td>
                    <td>${escapeHtml(row.teacher_name || '')}</td>
                    <td>${escapeHtml(row.subject || '-')}</td>
                    <td>${escapeHtml(row.stage || '-')}</td>
                    <td>${escapeHtml(row.class || '-')}</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('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>
