<?php
// /committee_dashboard.php - لوحة تحكم مسجل الكلية
header("Cache-Control: no-cache, no-store, must-revalidate");
header("Pragma: no-cache");
header("Expires: 0");

session_start();

// تحقق من تسجيل الدخول كمسجل كلية
if (!isset($_SESSION['committee_id']) || $_SESSION['user_role'] !== 'committee') {
    header('Location: committee_login.php');
    exit;
}

// تمكين الإبلاغ عن الأخطاء للتصحيح
error_reporting(E_ALL);
ini_set('display_errors', 1);

try {
    // تضمين الملفات المطلوبة
    if (!file_exists('db.php')) {
        throw new Exception('File db.php not found');
    }
    require 'db.php';
    
    $committeeName = (string)($_SESSION['committee_name'] ?? 'مسجل الكلية');
    $committeeId = (int)$_SESSION['committee_id'];
    $canAll = (bool)($_SESSION['committee_can_all'] ?? false);
    $scopeCollege = isset($_SESSION['committee_college_id']) ? (int)$_SESSION['committee_college_id'] : null;

    // تحقق من اتصال قاعدة البيانات
    if (!isset($mysqli1)) {
        throw new Exception('Database connection $mysqli1 not found');
    }

    $mysqli1->set_charset('utf8mb4');
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

    /* ---------- تحقق من وجود الجداول ---------- */
    $tableCheck = $mysqli1->query("SHOW TABLES LIKE 'students'");
    if ($tableCheck->num_rows === 0) {
        throw new Exception('Table "students" does not exist');
    }
    
    $tableCheck = $mysqli1->query("SHOW TABLES LIKE 'colleges'");
    if ($tableCheck->num_rows === 0) {
        throw new Exception('Table "colleges" does not exist');
    }
    
    $tableCheck = $mysqli1->query("SHOW TABLES LIKE 'payments'");
    if ($tableCheck->num_rows === 0) {
        $payments_table_exists = false;
    } else {
        $payments_table_exists = true;
    }

    // التحقق من الجداول الإضافية المطلوبة للتصفية
    $programs_table_exists = $mysqli1->query("SHOW TABLES LIKE 'programs'")->num_rows > 0;
    $batches_table_exists = $mysqli1->query("SHOW TABLES LIKE 'batches'")->num_rows > 0;
    $sections_table_exists = $mysqli1->query("SHOW TABLES LIKE 'sections'")->num_rows > 0;

    /* ---------- الحصول على قيم التصفية ---------- */
    $filter_college = isset($_GET['filter_college']) && !empty($_GET['filter_college']) ? (int)$_GET['filter_college'] : null;
    $filter_program = isset($_GET['filter_program']) && !empty($_GET['filter_program']) ? (int)$_GET['filter_program'] : null;
    $filter_batch = isset($_GET['filter_batch']) && !empty($_GET['filter_batch']) ? (int)$_GET['filter_batch'] : null;
    $filter_section = isset($_GET['filter_section']) && !empty($_GET['filter_section']) ? (int)$_GET['filter_section'] : null;
    
    // التصفية حسب تاريخ النطاق (من accountant_dashboard)
    $start_date = $_GET['start_date'] ?? date('Y-m-01'); // أول يوم من الشهر الحالي
    $end_date = $_GET['end_date'] ?? date('Y-m-t'); // آخر يوم من الشهر الحالي
    
    if (!empty($_GET['start_date']) && !empty($_GET['end_date'])) {
        $start_date = $_GET['start_date'];
        $end_date = $_GET['end_date'];
    }

    /* ---------- بناء شروط WHERE للتصفية ---------- */
    $where_conditions = [];
    
    // تصفية الكلية - للمستخدمين الذين لا يستطيعون رؤية كل شيء
    if (!$canAll && $scopeCollege !== null) {
        $where_conditions[] = "s.college_id = " . (int)$scopeCollege;
    } elseif ($filter_college) {
        // إذا كان المستخدم يستطيع رؤية كل شيء واختار كلية محددة
        $where_conditions[] = "s.college_id = " . (int)$filter_college;
    }
    
    // تصفية القسم
    if ($filter_section && $sections_table_exists) {
        $where_conditions[] = "s.section_id = " . (int)$filter_section;
    }
    
    // تصفية البرنامج
    if ($filter_program && $programs_table_exists) {
        $where_conditions[] = "s.program_id = " . (int)$filter_program;
    }
    
    // تصفية الدفعة
    if ($filter_batch && $batches_table_exists) {
        $where_conditions[] = "s.batch_id = " . (int)$filter_batch;
    }
    
    $where_clause = !empty($where_conditions) ? "WHERE " . implode(" AND ", $where_conditions) : "";

    /* ---------- مؤشرات الأداء الرئيسية ---------- */
    $sqlKpi = "
        SELECT
            COUNT(*) AS total_students,
            SUM(CASE WHEN s.profile_completed = 1 THEN 1 ELSE 0 END) AS profile_complete,
            SUM(CASE WHEN s.profile_completed = 0 OR s.profile_completed IS NULL THEN 1 ELSE 0 END) AS profile_incomplete,
            SUM(CASE WHEN s.medically_fit = 1 THEN 1 ELSE 0 END) AS medical_fit,
            SUM(CASE WHEN s.medically_fit = 0 THEN 1 ELSE 0 END) AS medical_unfit,
            SUM(CASE WHEN (s.medically_fit IS NULL OR s.medically_fit = '') AND s.profile_completed = 1 THEN 1 ELSE 0 END) AS medical_pending,
            SUM(CASE WHEN s.interview_status = 'accepted' THEN 1 ELSE 0 END) AS interview_accepted,
            SUM(CASE WHEN s.interview_status = 'rejected' THEN 1 ELSE 0 END) AS interview_rejected,
            SUM(CASE WHEN s.interview_status = 'pending' OR s.interview_status IS NULL THEN 1 ELSE 0 END) AS interview_pending,
            -- إحصائيات المدفوعات
            SUM(CASE WHEN r.id IS NOT NULL AND r.is_paid = 1 THEN 1 ELSE 0 END) AS paid_registrations,
            SUM(CASE WHEN r.id IS NOT NULL AND r.is_paid = 0 THEN 1 ELSE 0 END) AS unpaid_registrations,
            COALESCE(SUM(CASE WHEN r.is_paid = 1 THEN r.payment_amount ELSE 0 END), 0) AS total_paid_registrations,
            COALESCE(SUM(CASE WHEN r.is_paid = 0 THEN r.payment_amount ELSE 0 END), 0) AS total_unpaid_registrations
        FROM students s
        LEFT JOIN registrations r ON r.student_id = s.student_id AND r.deleted_at IS NULL
        $where_clause
    ";
    
    $kpi_result = $mysqli1->query($sqlKpi);
    if (!$kpi_result) {
        throw new Exception('SQL Error in KPI query: ' . $mysqli1->error);
    }
    
    $kpi = $kpi_result->fetch_assoc();
    $kpi_result->close();

    // تأكد من أن جميع قيم المؤشرات هي أعداد صحيحة
    foreach ($kpi as $key => $value) {
        $kpi[$key] = (int)($value ?? 0);
    }
    
    // حساب الإجماليات المالية
    $kpi['total_paid_all'] = ($kpi['total_paid_registrations'] ?? 0);
    $kpi['total_unpaid_all'] = ($kpi['total_unpaid_registrations'] ?? 0);
    $kpi['total_receivable'] = $kpi['total_paid_all'] + $kpi['total_unpaid_all'];

    /* ---------- إجمالي الرسوم المدفوعة ---------- */
    $total_fees = $kpi['total_paid_all'];

    /* ---------- الطلاب حسب الكلية ---------- */
    $college_where = ($filter_college && $canAll) ? "WHERE c.id = " . (int)$filter_college : "";
    
    // إذا كان المستخدم مقيداً بكلية واحدة
    if (!$canAll && $scopeCollege !== null) {
        $college_where = "WHERE c.id = " . (int)$scopeCollege;
    }
    
    $sqlColleges = "
        SELECT 
            c.id,
            c.name as college_name,
            COUNT(s.student_id) as student_count,
            COALESCE(SUM(CASE WHEN r.is_paid = 1 THEN r.payment_amount ELSE 0 END), 0) as total_fees,
            SUM(CASE WHEN s.profile_completed = 1 THEN 1 ELSE 0 END) as profile_complete,
            SUM(CASE WHEN s.medically_fit = 1 THEN 1 ELSE 0 END) as medical_fit,
            SUM(CASE WHEN s.medically_fit = 0 THEN 1 ELSE 0 END) as medical_unfit,
            SUM(CASE WHEN s.interview_status = 'accepted' THEN 1 ELSE 0 END) as interview_accepted
        FROM colleges c
        LEFT JOIN students s ON c.id = s.college_id
        LEFT JOIN registrations r ON s.student_id = r.student_id AND r.deleted_at IS NULL
        $college_where
        " . ($filter_program || $filter_batch || $filter_section ? " AND " . substr($where_clause, 6) : "") . "
        GROUP BY c.id, c.name
        ORDER BY student_count DESC
    ";
    
    $colleges_result = $mysqli1->query($sqlColleges);
    if (!$colleges_result) {
        throw new Exception('SQL Error in colleges query: ' . $mysqli1->error);
    }
    
    $college_stats = $colleges_result->fetch_all(MYSQLI_ASSOC);
    $colleges_result->close();

    /* ---------- أحدث الطلاب ---------- */
    $sqlRecent = "
        SELECT 
            s.student_id,
            s.student_name_eng,
            s.student_name,
            c.name as college_name,
            sec.name as section_name,
            s.profile_completed,
            s.medical_status,
            s.medically_fit,
            s.interview_status,
            COALESCE(SUM(CASE WHEN r.is_paid = 1 THEN r.payment_amount ELSE 0 END), 0) as paid_amount,
            COALESCE(SUM(CASE WHEN r.is_paid = 0 THEN r.payment_amount ELSE 0 END), 0) as unpaid_amount
        FROM students s
        LEFT JOIN colleges c ON s.college_id = c.id
        LEFT JOIN sections sec ON s.section_id = sec.id
        LEFT JOIN registrations r ON s.student_id = r.student_id AND r.deleted_at IS NULL
        $where_clause
        GROUP BY s.student_id
        ORDER BY s.created_at DESC
        LIMIT 10
    ";
    
    $recent_result = $mysqli1->query($sqlRecent);
    if (!$recent_result) {
        throw new Exception('SQL Error in recent students query: ' . $mysqli1->error);
    }
    
    $recent_students = $recent_result->fetch_all(MYSQLI_ASSOC);
    $recent_result->close();

    /* ---------- إحصائيات المدفوعات حسب القسم ---------- */
    $bySection = [];
    if ($sections_table_exists) {
        $sqlBySection = "
            SELECT 
                sec.id, 
                sec.name,
                COUNT(DISTINCT s.student_id) AS total_students,
                SUM(CASE WHEN r.id IS NOT NULL AND r.is_paid = 0 THEN 1 ELSE 0 END) AS unpaid_registrations,
                SUM(CASE WHEN r.id IS NOT NULL AND r.is_paid = 1 THEN 1 ELSE 0 END) AS paid_registrations,
                COALESCE(SUM(CASE WHEN r.is_paid = 1 THEN r.payment_amount ELSE 0 END), 0) AS total_paid_registrations,
                COALESCE(SUM(CASE WHEN r.is_paid = 0 THEN r.payment_amount ELSE 0 END), 0) AS total_unpaid_registrations
            FROM students s
            LEFT JOIN sections sec ON sec.id = s.section_id
            LEFT JOIN registrations r ON r.student_id = s.student_id AND r.deleted_at IS NULL
            $where_clause
            GROUP BY sec.id, sec.name
            ORDER BY total_students DESC, sec.name ASC
            LIMIT 10
        ";
        
        $bySection_result = $mysqli1->query($sqlBySection);
        if ($bySection_result) {
            $bySection = $bySection_result->fetch_all(MYSQLI_ASSOC);
            $bySection_result->close();
        }
    }

    $data_loaded = true;

} catch (Exception $e) {
    $error_message = $e->getMessage();
    $data_loaded = false;
    
    // بيانات احتياطية
    $kpi = [
        'total_students' => 0,
        'profile_complete' => 0,
        'profile_incomplete' => 0,
        'medical_fit' => 0,
        'medical_unfit' => 0,
        'medical_pending' => 0,
        'interview_accepted' => 0,
        'interview_rejected' => 0,
        'interview_pending' => 0,
        'paid_registrations' => 0,
        'unpaid_registrations' => 0,
        'total_paid_registrations' => 0,
        'total_unpaid_registrations' => 0,
        'total_paid_all' => 0,
        'total_unpaid_all' => 0,
        'total_receivable' => 0
    ];
    $total_fees = 0;
    $college_stats = [];
    $recent_students = [];
    $bySection = [];
}
?>
<!DOCTYPE html>
<html lang="ar" dir="rtl">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>لوحة التحكم - مسجل الكلية</title>
    <link href="https://fonts.googleapis.com/css2?family=Cairo:wght@400;600;700;800&display=swap" rel="stylesheet">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css">
    
    <!-- منع التخزين المؤقت -->
    <meta http-equiv="Cache-Control" content="no-cache, no-store, must-revalidate">
    <meta http-equiv="Pragma" content="no-cache">
    <meta http-equiv="Expires" content="0">
    
    <style>
        :root {
            --g-900: #1F3A2E;
            --g-700: #3E6B54;
            --g-500: #6F9E84;
            --g-300: #A8C5B4;
            --g-100: #ECF3EF;
            --primary-600: #006400;
            --primary-700: #004d00;
            --blue-500: #3498db;
            --green-500: #27ae60;
            --orange-500: #f39c12;
            --red-500: #e74c3c;
            --teal-500: #1abc9c;
        }

        body {
            font-family: 'Cairo', system-ui, -apple-system, sans-serif;
            background: var(--g-100);
            min-height: 100vh;
        }

        .dashboard-header {
            background: linear-gradient(135deg, var(--primary-600), var(--primary-700));
            color: white;
            padding: 2rem 0;
            margin-bottom: 2rem;
            box-shadow: 0 4px 12px rgba(0,0,0,0.1);
        }

        .welcome-message {
            font-size: 1.8rem;
            font-weight: 800;
            margin-bottom: 0.5rem;
        }

        .dash-card {
            background: white;
            border-radius: 1rem;
            padding: 1.5rem;
            box-shadow: 0 4px 12px rgba(0,0,0,0.08);
            margin-bottom: 2rem;
        }

        .filter-icon {
            width: 40px;
            height: 40px;
            background: linear-gradient(135deg, var(--blue-500), #2980b9);
            color: white;
            border-radius: 10px;
            display: flex;
            align-items: center;
            justify-content: center;
            font-size: 1.2rem;
        }

        .filter-summary-card {
            background: #f8f9fa;
            border-radius: 0.75rem;
            padding: 1rem;
            border: 1px solid #e9ecef;
            height: 100%;
        }

        .summary-header {
            display: flex;
            align-items: center;
            margin-bottom: 1rem;
            padding-bottom: 0.5rem;
            border-bottom: 1px solid #dee2e6;
            color: var(--g-700);
        }

        .summary-item {
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin-bottom: 0.5rem;
            padding: 0.25rem 0;
        }

        .summary-label {
            font-size: 0.85rem;
            color: #6c757d;
        }

        .summary-value .badge {
            font-size: 0.75rem;
            padding: 0.25rem 0.5rem;
        }

        .stats-cards {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(280px, 1fr));
            gap: 1.5rem;
            margin-bottom: 3rem;
        }

        .stat-card {
            background: white;
            padding: 1.5rem;
            border-radius: 1rem;
            box-shadow: 0 4px 12px rgba(0,0,0,0.08);
            border-right: 4px solid var(--primary-600);
            text-align: center;
            transition: all 0.3s ease;
            cursor: pointer;
            position: relative;
            overflow: hidden;
        }

        .stat-card:hover {
            transform: translateY(-5px);
            box-shadow: 0 8px 25px rgba(0,0,0,0.15);
        }

        .stat-icon {
            font-size: 2.5rem;
            margin-bottom: 1rem;
            opacity: 0.9;
        }

        .stat-number {
            font-size: 2.2rem;
            font-weight: 800;
            margin-bottom: 0.5rem;
            color: var(--g-900);
        }

        .stat-label {
            color: #64748B;
            font-weight: 600;
            font-size: 1rem;
            margin-bottom: 0.5rem;
        }

        .stat-subtext {
            font-size: 0.85rem;
            color: #6c757d;
            margin-top: 0.5rem;
        }

        .college-card {
            background: white;
            border-radius: 0.75rem;
            padding: 1.5rem;
            border-right: 4px solid var(--primary-600);
            margin-bottom: 1rem;
            box-shadow: 0 2px 8px rgba(0,0,0,0.08);
            transition: all 0.3s ease;
            cursor: pointer;
        }

        .college-card:hover {
            transform: translateY(-3px);
            box-shadow: 0 6px 20px rgba(0,0,0,0.12);
            border-right-color: var(--blue-500);
        }

        .college-name {
            font-weight: 800;
            color: var(--g-900);
            margin-bottom: 1rem;
            font-size: 1.1rem;
        }

        .college-stats {
            display: grid;
            grid-template-columns: repeat(2, 1fr);
            gap: 0.5rem;
            margin-bottom: 1rem;
        }

        .college-stat-item {
            text-align: center;
            padding: 0.5rem;
            background: var(--g-100);
            border-radius: 0.5rem;
        }

        .college-stat-number {
            font-weight: 700;
            font-size: 1.1rem;
            color: var(--g-900);
        }

        .college-stat-label {
            font-size: 0.75rem;
            color: #64748B;
            margin-top: 0.25rem;
        }

        .error-alert {
            background: #fee2e2;
            border: 1px solid #fecaca;
            color: #dc2626;
            padding: 1rem;
            border-radius: 0.75rem;
            margin-bottom: 2rem;
            font-weight: 600;
        }

        .logout-btn {
            background: var(--primary-600);
            color: white;
            border: none;
            padding: 0.75rem 1.5rem;
            border-radius: 0.75rem;
            font-weight: 700;
            cursor: pointer;
            transition: all 0.3s ease;
            font-family: 'Cairo', sans-serif;
            text-decoration: none;
            display: inline-block;
        }

        .logout-btn:hover {
            background: var(--primary-700);
            transform: translateY(-2px);
            box-shadow: 0 4px 12px rgba(0, 100, 0, 0.3);
            color: white;
        }

        .section-title {
            font-size: 1.5rem;
            font-weight: 800;
            color: var(--g-900);
            margin-bottom: 1.5rem;
            padding-bottom: 0.5rem;
            border-bottom: 2px solid var(--g-300);
        }

        .data-section {
            background: white;
            border-radius: 1rem;
            padding: 2rem;
            box-shadow: 0 4px 12px rgba(0,0,0,0.08);
            margin-bottom: 2rem;
        }

        .form-control {
            border-radius: 0.5rem;
            padding: 0.5rem 0.75rem;
            font-family: 'Cairo', sans-serif;
        }

        .input-group-text {
            border-radius: 0.5rem;
            background-color: #f8f9fa;
        }

        .financial-card {
            background: linear-gradient(135deg, var(--primary-600), var(--primary-700));
            color: white;
            border-radius: 10px;
            padding: 15px;
            margin-bottom: 15px;
        }

        .financial-stat {
            text-align: center;
            padding: 8px;
        }

        .financial-stat .number {
            font-size: 1.3rem;
            font-weight: bold;
            margin-bottom: 3px;
        }

        .financial-stat .label {
            font-size: 0.75rem;
            opacity: 0.9;
        }

        .chart-container {
            height: 160px;
            position: relative;
        }

        .table th {
            font-size: 0.8rem;
            padding: 8px 6px;
        }

        .table td {
            font-size: 0.8rem;
            padding: 6px;
        }

        .chip {
            display: inline-block;
            padding: .15rem .4rem;
            border-radius: 999px;
            font-size: .75rem;
        }

        .chip.green {
            background: #d1fae5;
            color: #065f46;
        }

        .chip.gray {
            background: #e5e7eb;
            color: #1f2937;
        }

        .chip.red {
            background: #fee2e2;
            color: #991b1b;
        }

        .chip.blue {
            background: #dbeafe;
            color: #1e3a8a;
        }

        .chip.orange {
            background: #fed7aa;
            color: #9a3412;
        }

        .chip.purple {
            background: #e9d5ff;
            color: #7e22ce;
        }

        .professional-summary-card {
            background: #ffffff;
            border: 1px solid #e0e6ef;
            border-radius: 12px;
            overflow: hidden;
            box-shadow: 0 2px 8px rgba(0,0,0,0.06);
            height: 100%;
            direction: rtl;
        }

        .summary-section {
            padding: 20px;
            border-bottom: 1px solid #f1f5f9;
        }

        .section-header {
            display: flex;
            align-items: center;
            margin-bottom: 16px;
            padding-bottom: 12px;
            border-bottom: 1px solid #f1f5f9;
        }

        .section-title-small {
            font-size: 0.9rem;
            font-weight: 700;
            color: #1e293b;
        }

        .criteria-item {
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin-bottom: 12px;
            padding: 8px 0;
        }

        .criteria-label {
            font-size: 0.8rem;
            font-weight: 600;
            color: #64748b;
            white-space: nowrap;
        }

        .criteria-value {
            font-size: 0.8rem;
            font-weight: 600;
            color: #1e293b;
        }

        .value-tag {
            display: inline-flex;
            align-items: center;
            background: #f1f5f9;
            color: #475569;
            padding: 4px 10px;
            border-radius: 6px;
            font-size: 0.75rem;
            font-weight: 600;
            border: 1px solid #e2e8f0;
        }

        .summary-stats {
            padding: 16px 20px;
            background: #f8fafc;
        }

        .stat-item {
            display: flex;
            align-items: center;
            justify-content: space-between;
        }

        .stat-icon {
            width: 40px;
            height: 40px;
            background: var(--primary-600);
            border-radius: 10px;
            display: flex;
            align-items: center;
            justify-content: center;
            color: white;
            font-size: 1rem;
        }

        .stat-info {
            text-align: right;
        }

        .stat-value-small {
            font-size: 1.2rem;
            font-weight: 800;
            color: #1e293b;
            line-height: 1;
        }

        .stat-label-small {
            font-size: 0.75rem;
            color: #64748b;
            font-weight: 600;
            margin-top: 2px;
        }
    </style>
</head>
<body>
    <!-- رأس لوحة التحكم -->
    <div class="dashboard-header">
        <div class="container">
            <div class="row align-items-center">
                <div class="col-md-8">
                    <h1 class="welcome-message">مرحباً، <?php echo htmlspecialchars($committeeName); ?> 👨‍💼</h1>
                    <p class="mb-0">لوحة التحكم - مسجل الكلية - آخر تحديث: <?php echo date('Y-m-d H:i:s'); ?></p>
                    <?php if (!$canAll && $scopeCollege !== null): ?>
                        <small><i class="fas fa-building"></i> الكلية: 
                            <?php 
                            $college_name = $mysqli1->query("SELECT name FROM colleges WHERE id = $scopeCollege")->fetch_assoc()['name'] ?? 'غير محدد';
                            echo htmlspecialchars($college_name);
                            ?>
                        </small>
                    <?php endif; ?>
                </div>
                <div class="col-md-4 text-md-start">
                    <a href="logout.php" class="logout-btn">
                        <i class="fas fa-sign-out-alt"></i> تسجيل الخروج
                    </a>
                </div>
            </div>
        </div>
    </div>

    <div class="container">
        <?php if (isset($error_message)): ?>
            <div class="error-alert">
                <i class="fas fa-exclamation-triangle"></i> <strong>خطأ:</strong> <?php echo htmlspecialchars($error_message); ?>
            </div>
        <?php endif; ?>

        <!-- بطاقة التصفية -->
        <div class="dash-card mb-4">
            <div class="row align-items-center">
                <div class="col-md-8">
                    <div class="d-flex align-items-center mb-3">
                        <div class="filter-icon me-2">
                            <i class="fas fa-filter"></i>
                        </div>
                        <h6 class="fw-bold mb-0 text-dark">تصفية التقارير</h6>
                        <div class="ms-3">
                            <span class="badge bg-light text-dark border">
                                <i class="fas fa-sliders-h me-1"></i>
                                نظام التصفية
                            </span>
                        </div>
                    </div>
                    
                    <form method="get" class="row g-3 align-items-end">
                        <!-- تصفية النطاق الزمني -->
                        <div class="col-lg-3 col-md-6">
                            <div class="form-group">
                                <label class="form-label fw-semibold text-muted mb-2">
                                    <i class="fas fa-calendar-day me-1"></i>
                                    من تاريخ
                                </label>
                                <div class="input-group input-group-sm">
                                    <input type="date" class="form-control border-end-0" name="start_date" value="<?= htmlspecialchars($start_date) ?>" required>
                                    <span class="input-group-text bg-light border-start-0">
                                        <i class="fas fa-play text-success"></i>
                                    </span>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-lg-3 col-md-6">
                            <div class="form-group">
                                <label class="form-label fw-semibold text-muted mb-2">
                                    <i class="fas fa-calendar-day me-1"></i>
                                    إلى تاريخ
                                </label>
                                <div class="input-group input-group-sm">
                                    <input type="date" class="form-control border-end-0" name="end_date" value="<?= htmlspecialchars($end_date) ?>" required>
                                    <span class="input-group-text bg-light border-start-0">
                                        <i class="fas fa-flag text-danger"></i>
                                    </span>
                                </div>
                            </div>
                        </div>
                        
                        <?php if ($canAll): ?>
                        <!-- تصفية الكلية -->
                        <div class="col-lg-3 col-md-6">
                            <div class="form-group">
                                <label class="form-label fw-semibold text-muted mb-2">
                                    <i class="fas fa-university me-1"></i>
                                    الكلية
                                </label>
                                <div class="input-group input-group-sm">
                                    <select class="form-control border-end-0" name="filter_college">
                                        <option value="">جميع الكليات</option>
                                        <?php
                                        $colleges_filter_sql = "SELECT id, name FROM colleges ORDER BY name";
                                        $colleges_filter_result = $mysqli1->query($colleges_filter_sql);
                                        while ($college = $colleges_filter_result->fetch_assoc()) {
                                            $selected = (isset($_GET['filter_college']) && $_GET['filter_college'] == $college['id']) ? 'selected' : '';
                                            echo "<option value=\"{$college['id']}\" $selected>" . htmlspecialchars($college['name']) . "</option>";
                                        }
                                        ?>
                                    </select>
                                    <span class="input-group-text bg-light border-start-0">
                                        <i class="fas fa-building"></i>
                                    </span>
                                </div>
                            </div>
                        </div>
                        <?php endif; ?>
                        
                        <!-- تصفية القسم -->
                        <div class="col-lg-3 col-md-6">
                            <div class="form-group">
                                <label class="form-label fw-semibold text-muted mb-2">
                                    <i class="fas fa-layer-group me-1"></i>
                                    القسم
                                </label>
                                <div class="input-group input-group-sm">
                                    <select class="form-control border-end-0" name="filter_section">
                                        <option value="">جميع الأقسام</option>
                                        <?php
                                        $sections_sql = "SELECT id, name FROM sections WHERE deleted_at IS NULL ORDER BY name";
                                        $sections_result = $mysqli1->query($sections_sql);
                                        while ($section = $sections_result->fetch_assoc()) {
                                            $selected = (isset($_GET['filter_section']) && $_GET['filter_section'] == $section['id']) ? 'selected' : '';
                                            echo "<option value=\"{$section['id']}\" $selected>" . htmlspecialchars($section['name']) . "</option>";
                                        }
                                        ?>
                                    </select>
                                    <span class="input-group-text bg-light border-start-0">
                                        <i class="fas fa-building"></i>
                                    </span>
                                </div>
                            </div>
                        </div>
                        
                        <!-- تصفية البرنامج -->
                        <?php if ($programs_table_exists): ?>
                        <div class="col-lg-3 col-md-6">
                            <div class="form-group">
                                <label class="form-label fw-semibold text-muted mb-2">
                                    <i class="fas fa-graduation-cap me-1"></i>
                                    البرنامج
                                </label>
                                <div class="input-group input-group-sm">
                                    <select class="form-control border-end-0" name="filter_program">
                                        <option value="">جميع البرامج</option>
                                        <?php
                                        $filter_programs_sql = "SELECT id, name FROM programs WHERE deleted_at IS NULL ORDER BY name";
                                        $filter_programs_result = $mysqli1->query($filter_programs_sql);
                                        while ($program = $filter_programs_result->fetch_assoc()) {
                                            $selected = (isset($_GET['filter_program']) && $_GET['filter_program'] == $program['id']) ? 'selected' : '';
                                            echo "<option value=\"{$program['id']}\" $selected>" . htmlspecialchars($program['name']) . "</option>";
                                        }
                                        ?>
                                    </select>
                                    <span class="input-group-text bg-light border-start-0">
                                        <i class="fas fa-book"></i>
                                    </span>
                                </div>
                            </div>
                        </div>
                        <?php endif; ?>
                        
                        <!-- تصفية الدفعة -->
                        <div class="col-lg-3 col-md-6">
                            <div class="form-group">
                                <label class="form-label fw-semibold text-muted mb-2">
                                    <i class="fas fa-users me-1"></i>
                                    الدفعة
                                </label>
                                <div class="input-group input-group-sm">
                                    <select class="form-control border-end-0" name="filter_batch">
                                        <option value="">جميع الدفعات</option>
                                        <?php if ($batches_table_exists): ?>
                                            <?php
                                            $filter_batches_sql = "SELECT id, value FROM batches WHERE deleted_at IS NULL ORDER BY id";
                                            $filter_batches_result = $mysqli1->query($filter_batches_sql);
                                            while ($batch = $filter_batches_result->fetch_assoc()) {
                                                $selected = (isset($_GET['filter_batch']) && $_GET['filter_batch'] == $batch['id']) ? 'selected' : '';
                                                echo "<option value=\"{$batch['id']}\" $selected>" . htmlspecialchars($batch['value']) . "</option>";
                                            }
                                            ?>
                                        <?php endif; ?>
                                    </select>
                                    <span class="input-group-text bg-light border-start-0">
                                        <i class="fas fa-calendar-alt"></i>
                                    </span>
                                </div>
                            </div>
                        </div>
                        
                        <!-- أزرار الإجراءات -->
                        <div class="col-12">
                            <div class="d-flex gap-2 pt-2">
                                <button type="submit" class="btn btn-primary btn-sm px-3">
                                    <i class="fas fa-filter me-1"></i>
                                    تطبيق التصفية
                                </button>
                                <a href="committee_dashboard.php" class="btn btn-outline-secondary btn-sm px-3">
                                    <i class="fas fa-sync-alt me-1"></i>
                                    إعادة تعيين
                                </a>
                                <div class="vr mx-2"></div>
                                <!-- رابط تسجيل الطلاب الجدد -->
                                <a href="registration.php" class="btn btn-success btn-sm px-3">
                                    <i class="fas fa-user-plus me-1"></i>
                                    تسجيل طالب جديد
                                </a>
                                <!-- رابط إدارة الطلاب -->
                                <a href="committee_students.php" class="btn btn-info btn-sm px-3">
                                    <i class="fas fa-users me-1"></i>
                                    إدارة الطلاب
                                </a>
                            </div>
                        </div>
                    </form>
                </div>
                
                <!-- ملخص التصفية -->
                <div class="col-md-4">
                    <div class="professional-summary-card">
                        <div class="summary-section">
                            <div class="section-header">
                                <i class="fas fa-info-circle me-2"></i>
                                <span class="section-title-small">إعدادات التقرير</span>
                            </div>
                            <div class="section-content">
                                <div class="criteria-item">
                                    <span class="criteria-label">الفترة الزمنية:</span>
                                    <span class="criteria-value"><?= date('d/m/Y', strtotime($start_date)) ?> - <?= date('d/m/Y', strtotime($end_date)) ?></span>
                                </div>
                                
                                <?php if ($filter_college): 
                                    $college_name = $mysqli1->query("SELECT name FROM colleges WHERE id = $filter_college")->fetch_assoc()['name'] ?? '';
                                ?>
                                    <div class="criteria-item">
                                        <span class="criteria-label">الكلية:</span>
                                        <span class="criteria-value">
                                            <span class="value-tag">
                                                <?= htmlspecialchars($college_name) ?>
                                            </span>
                                        </span>
                                    </div>
                                <?php elseif (!$canAll && $scopeCollege !== null): 
                                    $college_name = $mysqli1->query("SELECT name FROM colleges WHERE id = $scopeCollege")->fetch_assoc()['name'] ?? '';
                                ?>
                                    <div class="criteria-item">
                                        <span class="criteria-label">الكلية:</span>
                                        <span class="criteria-value">
                                            <span class="value-tag">
                                                <?= htmlspecialchars($college_name) ?>
                                            </span>
                                        </span>
                                    </div>
                                <?php else: ?>
                                    <div class="criteria-item">
                                        <span class="criteria-label">الكلية:</span>
                                        <span class="criteria-value">
                                            <span class="value-tag">جميع الكليات</span>
                                        </span>
                                    </div>
                                <?php endif; ?>
                                
                                <?php if ($filter_section): 
                                    $section_name = $mysqli1->query("SELECT name FROM sections WHERE id = $filter_section")->fetch_assoc()['name'] ?? '';
                                ?>
                                    <div class="criteria-item">
                                        <span class="criteria-label">القسم:</span>
                                        <span class="criteria-value">
                                            <span class="value-tag">
                                                <?= htmlspecialchars($section_name) ?>
                                            </span>
                                        </span>
                                    </div>
                                <?php else: ?>
                                    <div class="criteria-item">
                                        <span class="criteria-label">القسم:</span>
                                        <span class="criteria-value">
                                            <span class="value-tag">جميع الأقسام</span>
                                        </span>
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                        
                        <div class="summary-stats">
                            <div class="stat-item">
                                <div class="stat-info">
                                    <div class="stat-value-small"><?= number_format($kpi['total_students'] ?? 0) ?></div>
                                    <div class="stat-label-small">إجمالي الطلاب</div>
                                </div>
                                <div class="stat-icon">
                                    <i class="fas fa-users"></i>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>

        <?php if ($data_loaded): ?>
        <!-- الإحصائيات الرئيسية -->
        <div class="data-section">
            <h4 class="section-title">📊 الإحصائيات الرئيسية</h4>
            <div class="stats-cards">
                <!-- إجمالي الطلاب -->
                <div class="stat-card" style="border-right-color: var(--blue-500);" onclick="window.location.href='committee_students.php?filter=all<?= $filter_college ? '&filter_college=' . $filter_college : '' ?><?= $filter_section ? '&filter_section=' . $filter_section : '' ?><?= $filter_program ? '&filter_program=' . $filter_program : '' ?><?= $filter_batch ? '&filter_batch=' . $filter_batch : '' ?>'">
                    <div class="stat-icon">🎓</div>
                    <div class="stat-number"><?= number_format($kpi['total_students']) ?></div>
                    <div class="stat-label">إجمالي الطلاب</div>
                    <div class="stat-subtext">انقر لعرض جميع الطلاب</div>
                </div>

                <!-- مكتملو الملف الشخصي -->
                <div class="stat-card" style="border-right-color: var(--green-500);" onclick="window.location.href='committee_students.php?filter=profile_complete<?= $filter_college ? '&filter_college=' . $filter_college : '' ?><?= $filter_section ? '&filter_section=' . $filter_section : '' ?><?= $filter_program ? '&filter_program=' . $filter_program : '' ?><?= $filter_batch ? '&filter_batch=' . $filter_batch : '' ?>'">
                    <div class="stat-icon">✅</div>
                    <div class="stat-number"><?= number_format($kpi['profile_complete']) ?></div>
                    <div class="stat-label">مكتملي الملف الشخصي</div>
                    <div class="stat-subtext"><?= $kpi['total_students'] > 0 ? number_format(($kpi['profile_complete']/$kpi['total_students'])*100, 1) : 0 ?>% من الإجمالي</div>
                </div>

                <!-- لائق طبياً -->
                <div class="stat-card" style="border-right-color: var(--teal-500);" onclick="window.location.href='committee_students.php?filter=medical_fit<?= $filter_college ? '&filter_college=' . $filter_college : '' ?><?= $filter_section ? '&filter_section=' . $filter_section : '' ?><?= $filter_program ? '&filter_program=' . $filter_program : '' ?><?= $filter_batch ? '&filter_batch=' . $filter_batch : '' ?>'">
                    <div class="stat-icon">💪</div>
                    <div class="stat-number"><?= number_format($kpi['medical_fit']) ?></div>
                    <div class="stat-label">لائق طبياً</div>
                    <div class="stat-subtext">انقر لعرض اللائقين طبياً</div>
                </div>

                <!-- إجمالي الرسوم -->
                <div class="stat-card" style="border-right-color: var(--primary-600);" onclick="window.location.href='committee_payments.php<?= $filter_college ? '?filter_college=' . $filter_college : '' ?><?= $filter_section ? ($filter_college ? '&' : '?') . 'filter_section=' . $filter_section : '' ?><?= $filter_program ? ($filter_college || $filter_section ? '&' : '?') . 'filter_program=' . $filter_program : '' ?><?= $filter_batch ? ($filter_college || $filter_section || $filter_program ? '&' : '?') . 'filter_batch=' . $filter_batch : '' ?>'">
                    <div class="stat-icon">💰</div>
                    <div class="stat-number"><?= number_format($total_fees) ?></div>
                    <div class="stat-label">إجمالي الرسوم المدفوعة</div>
                    <div class="stat-subtext">انقر لعرض المدفوعات</div>
                </div>
            </div>
        </div>

        <!-- النظرة المالية -->
        <div class="data-section">
            <h4 class="section-title">💰 النظرة المالية</h4>
            <div class="row mb-3">
                <div class="col-md-3">
                    <div class="financial-card">
                        <div class="financial-stat">
                            <div class="number"><?= number_format($kpi['total_paid_all']) ?> SDG</div>
                            <div class="label">إجمالي المدفوع</div>
                        </div>
                    </div>
                </div>
                <div class="col-md-3">
                    <div class="financial-card" style="background: linear-gradient(135deg, #f093fb 0%, #f5576c 100%);">
                        <div class="financial-stat">
                            <div class="number"><?= number_format($kpi['total_unpaid_all']) ?> SDG</div>
                            <div class="label">إجمالي المستحق</div>
                        </div>
                    </div>
                </div>
                <div class="col-md-3">
                    <div class="financial-card" style="background: linear-gradient(135deg, #4facfe 0%, #00f2fe 100%);">
                        <div class="financial-stat">
                            <div class="number"><?= number_format($kpi['total_receivable']) ?> SDG</div>
                            <div class="label">إجمالي المتحصلات</div>
                        </div>
                    </div>
                </div>
                <div class="col-md-3">
                    <div class="financial-card" style="background: linear-gradient(135deg, #43e97b 0%, #38f9d7 100%);">
                        <div class="financial-stat">
                            <div class="number"><?= number_format($kpi['total_students']) ?></div>
                            <div class="label">إجمالي الطلاب</div>
                        </div>
                    </div>
                </div>
            </div>
            
            <!-- حالة المدفوعات -->
            <div class="row">
                <div class="col-md-6">
                    <div class="dash-card">
                        <h6 class="fw-bold mb-2">حالة التسجيلات المالية</h6>
                        <div class="chart-container">
                            <canvas id="registrationsChart"></canvas>
                        </div>
                    </div>
                </div>
                <div class="col-md-6">
                    <div class="dash-card">
                        <h6 class="fw-bold mb-2">إحصائيات حسب الأقسام</h6>
                        <?php if (!empty($bySection)): ?>
                            <div class="table-responsive">
                                <table class="table table-sm">
                                    <thead>
                                        <tr>
                                            <th>القسم</th>
                                            <th class="text-end">الطلاب</th>
                                            <th class="text-end">مدفوعة</th>
                                            <th class="text-end">غير مدفوعة</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php foreach ($bySection as $section): ?>
                                            <tr>
                                                <td><?= htmlspecialchars($section['name']) ?></td>
                                                <td class="text-end"><?= number_format($section['total_students']) ?></td>
                                                <td class="text-end text-success"><?= number_format($section['paid_registrations']) ?></td>
                                                <td class="text-end text-danger"><?= number_format($section['unpaid_registrations']) ?></td>
                                            </tr>
                                        <?php endforeach; ?>
                                    </tbody>
                                </table>
                            </div>
                        <?php else: ?>
                            <p class="text-muted text-center">لا توجد بيانات للأقسام</p>
                        <?php endif; ?>
                    </div>
                </div>
            </div>
        </div>

        <!-- إحصائيات الكليات -->
        <?php if ($canAll && !empty($college_stats)): ?>
        <div class="data-section">
            <h4 class="section-title">🏫 إحصائيات الكليات</h4>
            <div class="row">
                <?php foreach ($college_stats as $college): ?>
                    <div class="col-md-6 col-lg-4 mb-3">
                        <div class="college-card" onclick="viewCollegeStudents(<?= $college['id'] ?>)">
                            <div class="college-name"><?= htmlspecialchars($college['college_name']) ?></div>
                            
                            <div class="college-stats">
                                <div class="college-stat-item">
                                    <div class="college-stat-number"><?= number_format($college['student_count']) ?></div>
                                    <div class="college-stat-label">إجمالي الطلاب</div>
                                </div>
                                <div class="college-stat-item">
                                    <div class="college-stat-number"><?= number_format($college['total_fees']) ?></div>
                                    <div class="college-stat-label">الرسوم</div>
                                </div>
                                <div class="college-stat-item">
                                    <div class="college-stat-number"><?= number_format($college['profile_complete']) ?></div>
                                    <div class="college-stat-label">ملف مكتمل</div>
                                </div>
                                <div class="college-stat-item">
                                    <div class="college-stat-number"><?= number_format($college['medical_fit']) ?></div>
                                    <div class="college-stat-label">لائق طبياً</div>
                                </div>
                            </div>
                        </div>
                    </div>
                <?php endforeach; ?>
            </div>
        </div>
        <?php endif; ?>

        <!-- أحدث الطلاب المسجلين -->
        <div class="data-section">
            <h4 class="section-title">🎓 أحدث الطلاب المسجلين</h4>
            <?php if (empty($recent_students)): ?>
                <div class="alert alert-info text-center">
                    <i class="fas fa-info-circle"></i> لا توجد طلاب مسجلين بناءً على عوامل التصفية المحددة.
                </div>
            <?php else: ?>
                <div class="table-responsive">
                    <table class="table table-striped table-hover">
                        <thead>
                            <tr>
                                <th>رقم الطالب</th>
                                <th>اسم الطالب</th>
                                <th>الكلية</th>
                                <th>القسم</th>
                                <th>الملف الشخصي</th>
                                <th>الحالة الطبية</th>
                                <th>المبلغ المدفوع</th>
                                <th>المبلغ المستحق</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($recent_students as $student): ?>
                                <tr onclick="window.location.href='committee_student_profile.php?student_id=<?= urlencode($student['student_id']) ?>'" style="cursor: pointer;">
                                    <td><strong><?= htmlspecialchars($student['student_id']) ?></strong></td>
                                    <td>
                                        <?php 
                                        $name = !empty($student['student_name']) ? $student['student_name'] : $student['student_name_eng'];
                                        echo htmlspecialchars($name);
                                        ?>
                                    </td>
                                    <td><?= htmlspecialchars($student['college_name']) ?></td>
                                    <td><?= htmlspecialchars($student['section_name']) ?></td>
                                    <td>
                                        <span class="badge <?= $student['profile_completed'] ? 'bg-success' : 'bg-warning'; ?>">
                                            <?= $student['profile_completed'] ? 'مكتمل' : 'غير مكتمل'; ?>
                                        </span>
                                    </td>
                                    <td>
                                        <?php if ($student['medically_fit'] == 1): ?>
                                            <span class="badge bg-success">لائق</span>
                                        <?php elseif ($student['medically_fit'] == 0): ?>
                                            <span class="badge bg-danger">غير لائق</span>
                                        <?php else: ?>
                                            <span class="badge bg-warning">قيد المراجعة</span>
                                        <?php endif; ?>
                                    </td>
                                    <td>
                                        <span class="text-success fw-bold"><?= number_format($student['paid_amount'], 2) ?> SDG</span>
                                    </td>
                                    <td>
                                        <span class="text-danger fw-bold"><?= number_format($student['unpaid_amount'], 2) ?> SDG</span>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            <?php endif; ?>
        </div>
        <?php endif; ?>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.1/dist/chart.umd.min.js"></script>
    <script>
    function viewCollegeStudents(collegeId) {
        let url = 'committee_students.php?college=' + collegeId + '&filter=all';
        <?php if ($filter_college): ?>url += '&filter_college=<?= $filter_college ?>';<?php endif; ?>
        <?php if ($filter_section): ?>url += '&filter_section=<?= $filter_section ?>';<?php endif; ?>
        <?php if ($filter_program): ?>url += '&filter_program=<?= $filter_program ?>';<?php endif; ?>
        <?php if ($filter_batch): ?>url += '&filter_batch=<?= $filter_batch ?>';<?php endif; ?>
        window.location.href = url;
    }

    // Add click handlers for stat cards
    document.addEventListener('DOMContentLoaded', function() {
        const statCards = document.querySelectorAll('.stat-card');
        statCards.forEach(card => {
            card.addEventListener('click', function() {
                const url = this.getAttribute('onclick')?.match(/window\.location\.href='([^']+)'/)?.[1];
                if (url) {
                    window.location.href = url;
                }
            });
        });

        const collegeCards = document.querySelectorAll('.college-card');
        collegeCards.forEach(card => {
            card.addEventListener('click', function(e) {
                if (!e.target.closest('.college-action-btn')) {
                    const collegeId = this.getAttribute('onclick')?.match(/viewCollegeStudents\((\d+)\)/)?.[1];
                    if (collegeId) {
                        viewCollegeStudents(collegeId);
                    }
                }
            });
        });
    });
    
    // Registration Status Chart
    <?php if ($data_loaded): ?>
    const registrationsChart = new Chart(document.getElementById('registrationsChart'), {
        type: 'doughnut',
        data: {
            labels: ['مدفوعة', 'غير مدفوعة'],
            datasets: [{
                data: [<?= $kpi['paid_registrations'] ?>, <?= $kpi['unpaid_registrations'] ?>],
                backgroundColor: [
                    'rgba(16, 185, 129, 0.8)',
                    'rgba(239, 68, 68, 0.8)'
                ],
                borderColor: [
                    'rgb(16, 185, 129)',
                    'rgb(239, 68, 68)'
                ],
                borderWidth: 1,
                hoverBackgroundColor: [
                    'rgba(16, 185, 129, 1)',
                    'rgba(239, 68, 68, 1)'
                ],
                hoverOffset: 6
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            cutout: '55%',
            plugins: {
                legend: { 
                    position: 'bottom',
                    labels: {
                        usePointStyle: true,
                        padding: 10,
                        font: {
                            size: 10
                        }
                    }
                },
                tooltip: {
                    bodyFont: {
                        size: 10
                    },
                    callbacks: {
                        label: function(context) {
                            const total = <?= $kpi['paid_registrations'] + $kpi['unpaid_registrations'] ?>;
                            const value = context.raw;
                            const percentage = total > 0 ? ((value / total) * 100).toFixed(1) : 0;
                            return `${context.label}: ${value} (${percentage}%)`;
                        }
                    }
                }
            },
            animation: {
                animateScale: true,
                animateRotate: true
            }
        }
    });
    <?php endif; ?>
    </script>
</body>
</html>