<?php
// /admission3333/accountant_dashboard.php
require 'db.php';
require 'helpers.php';
require_committee();

require 'committee_session.php';

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

/* ---------- Config: centralize the interview list file ---------- */
const INTERVIEW_LIST_PAGE = 'applicants.php';

// Get date range from GET parameters
$start_date = $_GET['start_date'] ?? date('Y-m-01'); // First day of current month
$end_date = $_GET['end_date'] ?? date('Y-m-t'); // Last day of current month

// Get filter values
$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;
$filter_semester = isset($_GET['filter_semester']) && !empty($_GET['filter_semester']) ? (int)$_GET['filter_semester'] : null;

// New: Student search filter
$search_query = isset($_GET['search']) && !empty($_GET['search']) ? trim($_GET['search']) : '';

// Validate dates
if (!empty($_GET['start_date']) && !empty($_GET['end_date'])) {
    $start_date = $_GET['start_date'];
    $end_date = $_GET['end_date'];
}

/* ---------- Pagination Setup ---------- */
$per_page = 25; // Records per page
$page = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
$offset = ($page - 1) * $per_page;

// Build WHERE conditions for filters
$where_conditions = [];
$types = '';
$params = [];

// Permission scope
if (!$canAll && $scopeCollege !== null) {
    $where_conditions[] = "s.college_id = ?";
    $types .= 'i';
    $params[] = $scopeCollege;
} elseif (!$canAll && $scopeCollege === null) {
    $where_conditions[] = "1=0"; // No access
}

// College filter
if ($filter_college) {
    $where_conditions[] = "s.college_id = ?";
    $types .= 'i';
    $params[] = $filter_college;
}

// Program filter
$programs_table_exists = $mysqli1->query("SHOW TABLES LIKE 'programs'")->num_rows > 0;
if ($filter_program && $programs_table_exists) {
    $where_conditions[] = "s.program_id = ?";
    $types .= 'i';
    $params[] = $filter_program;
}

// Batch filter
$batches_table_exists = $mysqli1->query("SHOW TABLES LIKE 'batches'")->num_rows > 0;
if ($filter_batch && $batches_table_exists) {
    $where_conditions[] = "s.batch_id = ?";
    $types .= 'i';
    $params[] = $filter_batch;
}

// Section filter
$sections_table_exists = $mysqli1->query("SHOW TABLES LIKE 'sections'")->num_rows > 0;
if ($filter_section && $sections_table_exists) {
    $where_conditions[] = "s.section_id = ?";
    $types .= 'i';
    $params[] = $filter_section;
}

// Semester filter - Applied to registrations in KPI query
$semesters_table_exists = $mysqli1->query("SHOW TABLES LIKE 'semesters'")->num_rows > 0;

// Student search filter
if ($search_query) {
    $where_conditions[] = "(s.student_name LIKE ? OR s.student_name_eng LIKE ? OR s.student_id LIKE ? OR s.phone_number LIKE ?)";
    $types .= 'ssss';
    $search_param = "%{$search_query}%";
    $params[] = $search_param;
    $params[] = $search_param;
    $params[] = $search_param;
    $params[] = $search_param;
}

$where_clause = !empty($where_conditions) ? "WHERE " . implode(" AND ", $where_conditions) : "";

/* ---------- Top KPIs - Financial Focus ---------- */
// Modify KPI query to include semester filter
$kpi_where_conditions = [];
$kpi_types = '';
$kpi_params = [];

// Copy all student filters to KPI
if (!$canAll && $scopeCollege !== null) {
    $kpi_where_conditions[] = "s.college_id = ?";
    $kpi_types .= 'i';
    $kpi_params[] = $scopeCollege;
} elseif (!$canAll && $scopeCollege === null) {
    $kpi_where_conditions[] = "1=0";
}

if ($filter_college) {
    $kpi_where_conditions[] = "s.college_id = ?";
    $kpi_types .= 'i';
    $kpi_params[] = $filter_college;
}

if ($filter_program && $programs_table_exists) {
    $kpi_where_conditions[] = "s.program_id = ?";
    $kpi_types .= 'i';
    $kpi_params[] = $filter_program;
}

if ($filter_batch && $batches_table_exists) {
    $kpi_where_conditions[] = "s.batch_id = ?";
    $kpi_types .= 'i';
    $kpi_params[] = $filter_batch;
}

if ($filter_section && $sections_table_exists) {
    $kpi_where_conditions[] = "s.section_id = ?";
    $kpi_types .= 'i';
    $kpi_params[] = $filter_section;
}

// Add search to KPI
if ($search_query) {
    $kpi_where_conditions[] = "(s.student_name LIKE ? OR s.student_name_eng LIKE ? OR s.student_id LIKE ? OR s.phone_number LIKE ?)";
    $kpi_types .= 'ssss';
    $search_param = "%{$search_query}%";
    $kpi_params[] = $search_param;
    $kpi_params[] = $search_param;
    $kpi_params[] = $search_param;
    $kpi_params[] = $search_param;
}

$kpi_where_clause = !empty($kpi_where_conditions) ? "WHERE " . implode(" AND ", $kpi_where_conditions) : "";

// Add semester filter to registration and other_fees joins
$registration_join = "LEFT JOIN registrations r ON r.student_id = s.student_id AND r.deleted_at IS NULL";
$other_fees_join = "LEFT JOIN other_fees of ON of.phone_number = s.phone_number AND of.deleted_at IS NULL";

if ($filter_semester && $semesters_table_exists) {
    $registration_join .= " AND r.semester_id = " . (int)$filter_semester;
}

$sqlKpi = "
  SELECT
    COUNT(*) AS total_students,
    
    -- Financial KPIs
    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,
    SUM(CASE WHEN of.id IS NOT NULL AND of.is_paid = 1 THEN 1 ELSE 0 END) AS paid_other_fees,
    SUM(CASE WHEN of.id IS NOT NULL AND of.is_paid = 0 THEN 1 ELSE 0 END) AS unpaid_other_fees,
    
    -- Total amounts
    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,
    COALESCE(SUM(CASE WHEN of.is_paid = 1 THEN of.amount ELSE 0 END), 0) AS total_paid_other_fees,
    COALESCE(SUM(CASE WHEN of.is_paid = 0 THEN of.amount ELSE 0 END), 0) AS total_unpaid_other_fees
    
  FROM students s
  {$registration_join}
  {$other_fees_join}
  {$kpi_where_clause}
";

$st = $mysqli1->prepare($sqlKpi);
if ($kpi_types) $st->bind_param($kpi_types, ...$kpi_params);
$st->execute(); 
$kpi = $st->get_result()->fetch_assoc(); 
$st->close();

// Calculate derived financial metrics
$kpi['total_paid_all'] = ($kpi['total_paid_registrations'] ?? 0) + ($kpi['total_paid_other_fees'] ?? 0);
$kpi['total_unpaid_all'] = ($kpi['total_unpaid_registrations'] ?? 0) + ($kpi['total_unpaid_other_fees'] ?? 0);
$kpi['total_receivable'] = $kpi['total_paid_all'] + $kpi['total_unpaid_all'];

// حساب نسبة الدفع العامة
$kpi['payment_percentage'] = 0;
if ($kpi['total_receivable'] > 0) {
    $kpi['payment_percentage'] = round(($kpi['total_paid_all'] / $kpi['total_receivable']) * 100, 2);
}

foreach (['total_students','paid_registrations','unpaid_registrations','paid_other_fees','unpaid_other_fees'] as $k)
  $kpi[$k] = (int)($kpi[$k] ?? 0);

/* ---------- Recent Students with Pagination ---------- */
// First get total count for pagination
$countSql = "SELECT COUNT(*) as total FROM students s $where_clause";
$countStmt = $mysqli1->prepare($countSql);
if ($types !== '') { 
    $countStmt->bind_param($types, ...$params); 
}
$countStmt->execute();
$totalResult = $countStmt->get_result()->fetch_assoc();
$total_students = $totalResult['total'] ?? 0;
$countStmt->close();

$total_pages = ceil($total_students / $per_page);

// Get paginated students data with optimized query
$sqlRecent = "
  SELECT 
    s.student_id, s.student_name, s.student_name_eng, s.phone_number,
    s.section_id, sec.name AS section_name,
    s.batch_id, b.value AS batch_name,
    s.gender,
    s.college_id,
    c.name as college_name,
    p.name as program_name,
    -- Get the bank name from the latest registration payment
    (
        SELECT bnk.name 
        FROM payments pmt
        LEFT JOIN banks bnk ON pmt.bank_id = bnk.id
        WHERE pmt.student_id = s.student_id 
        AND pmt.deleted_at IS NULL
        ORDER BY pmt.created_at DESC 
        LIMIT 1
    ) AS bank_name
  FROM students s
  LEFT JOIN sections sec ON sec.id = s.section_id
  LEFT JOIN batches b ON b.id = s.batch_id
  LEFT JOIN colleges c ON c.id = s.college_id
  LEFT JOIN programs p ON p.id = s.program_id
  $where_clause
  ORDER BY s.student_id DESC
  LIMIT ? OFFSET ?
";

// Add pagination parameters
if ($types === '') {
    $limit_types = 'ii';
    $limit_params = [$per_page, $offset];
} else {
    $limit_types = $types . 'ii';
    $limit_params = array_merge($params, [$per_page, $offset]);
}

$st = $mysqli1->prepare($sqlRecent);
if ($limit_types !== '') { 
    $st->bind_param($limit_types, ...$limit_params); 
}
$st->execute();
$recentAccepted = $st->get_result()->fetch_all(MYSQLI_ASSOC);
$st->close();

// Function to build pagination URL
function build_pagination_url($page, $params = []) {
    $current_params = $_GET;
    $current_params['page'] = $page;
    return '?' . http_build_query($current_params);
}

// Get college, program, batch, section and semester names for display
$college_name = '';
$program_name = '';
$batch_name = '';
$section_name = '';
$semester_name = '';

if ($filter_college) {
    $college_result = $mysqli1->query("SELECT name FROM colleges WHERE id = $filter_college");
    $college_data = $college_result->fetch_assoc();
    $college_name = $college_data['name'] ?? '';
}

if ($filter_program && $programs_table_exists) {
    $program_result = $mysqli1->query("SELECT name FROM programs WHERE id = $filter_program");
    $program_data = $program_result->fetch_assoc();
    $program_name = $program_data['name'] ?? '';
}

if ($filter_batch && $batches_table_exists) {
    $batch_result = $mysqli1->query("SELECT value FROM batches WHERE id = $filter_batch");
    $batch_data = $batch_result->fetch_assoc();
    $batch_name = $batch_data['value'] ?? '';
}

if ($filter_section && $sections_table_exists) {
    $section_result = $mysqli1->query("SELECT name FROM sections WHERE id = $filter_section");
    $section_data = $section_result->fetch_assoc();
    $section_name = $section_data['name'] ?? '';
}

if ($filter_semester && $semesters_table_exists) {
    $semester_result = $mysqli1->query("SELECT name FROM semesters WHERE id = $filter_semester");
    $semester_data = $semester_result->fetch_assoc();
    $semester_name = $semester_data['name'] ?? '';
}

/* ---------- UI ---------- */
include 'header.php';
?>
<link href="https://fonts.googleapis.com/css2?family=Cairo:wght@400;600;700;800&display=swap" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.1/dist/chart.umd.min.js"></script>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>

<style>
  /* Apply Cairo font to ALL elements */
  * {
    font-family: 'Cairo', sans-serif !important;
  }
  
  /* Specific element overrides for consistency */
  body, h1, h2, h3, h4, h5, h6, p, a, button, label, input, select, textarea, table, th, td, span, div, code {
    font-family: 'Cairo', sans-serif !important;
  }
  
  /* Ensure form elements use Cairo */
  .form-control, .form-control-sm, .form-select, .form-select-sm {
    font-family: 'Cairo', sans-serif !important;
  }
  
  /* Ensure buttons use Cairo */
  .btn, .btn-sm, .btn-primary, .btn-secondary, .btn-success, .btn-outline-secondary {
    font-family: 'Cairo', sans-serif !important;
  }
  
  /* Ensure table elements use Cairo */
  .table, .table th, .table td {
    font-family: 'Cairo', sans-serif !important;
  }
  
  /* Ensure chip elements use Cairo */
  .chip, .chip.green, .chip.gray, .chip.red, .chip.blue, .chip.orange, .chip.purple {
    font-family: 'Cairo', sans-serif !important;
  }

  /* Rest of your existing styles */
  .dash-card {
    border: 1px solid #e5e7eb;
    border-radius: 12px;
    padding: 12px;
    background: #fff;
    box-shadow: 0 4px 12px rgba(0,0,0,.06);
    margin-bottom: 20px;
  }
  
  .kpi {
    display: flex;
    gap: 12px;
    flex-wrap: wrap;
  }
  
  .kpi .item {
    flex: 1;
    min-width: 160px;
    border-radius: 10px;
    padding: 12px;
    background: linear-gradient(135deg, #1F3A2E, #3E6B54);
    color: #fff;
  }
  
  .kpi .item .n {
    font-size: 1.4rem;
    font-weight: 800;
  }
  
  .tbl th, .tbl td {
    vertical-align: middle;
    font-size: 0.85rem;
  }
  
  .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;
  }
  
  .progress {
    border-radius: 8px;
    background-color: #e9ecef;
  }
  
  .progress-bar {
    border-radius: 8px;
    transition: width 0.6s ease;
  }
  
  .financial-card {
    background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
    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;
  }
  
  .table th {
    font-size: 0.8rem;
    padding: 8px 6px;
  }
  
  .table td {
    font-size: 0.8rem;
    padding: 6px;
  }
  
  .btn-sm {
    font-size: 0.75rem;
    padding: 0.2rem 0.5rem;
  }
  
  h3 {
    font-size: 1.4rem;
  }
  
  h6 {
    font-size: 0.9rem;
  }
  
  .date-filter-card {
    background: linear-gradient(135deg, #a8edea 0%, #fed6e3 100%);
    border-radius: 10px;
    padding: 15px;
    margin-bottom: 15px;
  }
  
  .payment-percentage-card {
    background: linear-gradient(135deg, #3E6B54 0%, #1F3A2E 100%);
    color: white;
    border-radius: 10px;
    padding: 15px;
    text-align: center;
  }
  
  .payment-percentage-card .percentage {
    font-size: 2.5rem;
    font-weight: bold;
    line-height: 1;
  }
  
  .payment-percentage-card .label {
    font-size: 0.9rem;
    opacity: 0.9;
    margin-top: 5px;
  }
  
  .form-control-sm {
    font-size: 0.8rem;
  }
  
  /* Pagination Styles */
  .pagination .page-link {
    font-size: 0.8rem;
    padding: 0.25rem 0.5rem;
  }
  
  .form-select-sm {
    font-size: 0.8rem;
    padding: 0.25rem 1.75rem 0.25rem 0.5rem;
  }
  
  /* Print Styles */
  @media print {
    body * {
      visibility: hidden;
    }
    .printable-table, .printable-table * {
      visibility: visible;
    }
    .printable-table {
      position: absolute;
      left: 0;
      top: 0;
      width: 100%;
      border: none;
      box-shadow: none;
      padding: 0;
    }
    .btn, .chip, .back-button, .no-print {
      display: none !important;
    }
    .table {
      font-size: 10px !important;
    }
    .table th, .table td {
      padding: 3px !important;
    }
    h6 {
      font-size: 12px !important;
    }
  }

  /* Search box styles */
  .search-box {
    background: linear-gradient(135deg, #f5f7fa 0%, #c3cfe2 100%);
    border-radius: 10px;
    padding: 15px;
    margin-bottom: 15px;
  }
  
  .student-payment-progress {
    height: 10px;
    margin-top: 5px;
    border-radius: 5px;
  }
  
  .student-payment-percentage {
    font-size: 0.75rem;
    font-weight: bold;
  }
</style>

<div class="container py-3" dir="rtl">
  <div class="d-flex align-items-center mb-3">
    <h3 class="mb-0 fw-bold">لوحة التحكم - المحاسب</h3>
    <div class="ms-auto">
      <span class="chip blue">مرحباً، <?= e($committeeName ?: 'المحاسب') ?></span>
      <?php if (!$canAll && $scopeCollege !== null): ?>
        <span class="chip gray">النطاق: كلية محددة</span>
      <?php else: ?>
        <span class="chip gray">النطاق: جميع الأقسام</span>
      <?php endif; ?>
      <a class="btn btn-sm btn-outline-secondary ms-2" href="financial_reports.php">التقارير المالية</a>
    </div>
  </div>

<!-- Search Box -->
<div class="search-box">
  <div class="row align-items-center">
    <div class="col-md-12">
      <form method="get" action="">
        <input type="hidden" name="start_date" value="<?= e($start_date) ?>">
        <input type="hidden" name="end_date" value="<?= e($end_date) ?>">
        <input type="hidden" name="filter_college" value="<?= e($filter_college) ?>">
        <input type="hidden" name="filter_program" value="<?= e($filter_program) ?>">
        <input type="hidden" name="filter_batch" value="<?= e($filter_batch) ?>">
        <input type="hidden" name="filter_section" value="<?= e($filter_section) ?>">
        <input type="hidden" name="filter_semester" value="<?= e($filter_semester) ?>">
        
        <div class="input-group">
          <input type="text" 
                 class="form-control" 
                 name="search" 
                 placeholder="ابحث عن طالب بالاسم، الرقم الجامعي، أو رقم الهاتف..." 
                 value="<?= e($search_query) ?>"
                 style="height: 45px;">
          <button class="btn btn-primary" type="submit" style="height: 45px;">
            <i class="fas fa-search me-1"></i>بحث
          </button>
          <?php if ($search_query): ?>
            <a href="accountant_dashboard.php" class="btn btn-outline-secondary" style="height: 45px;">
              <i class="fas fa-times me-1"></i>إلغاء البحث
            </a>
          <?php endif; ?>
        </div>
        
        <?php if ($search_query): ?>
          <div class="mt-2">
            <span class="badge bg-info">
              <i class="fas fa-search me-1"></i>
              نتائج البحث عن: "<?= e($search_query) ?>"
              <span class="badge bg-white text-info ms-1"><?= number_format($total_students) ?> نتيجة</span>
            </span>
          </div>
        <?php endif; ?>
      </form>
    </div>
  </div>
</div>
 
<!-- Date Range Filter -->
<div class="date-filter-card" dir="rtl">
  <div class="row align-items-center">
    <!-- Report Settings Card -->
    <div class="col-md-4">
      <div class="payment-percentage-card">
        <div class="percentage"><?= number_format($kpi['payment_percentage'], 1) ?>%</div>
        <div class="label">نسبة الدفع العامة</div>
        <div class="progress mt-2" style="height: 8px; background: rgba(255,255,255,0.2);">
          <div class="progress-bar 
            <?= $kpi['payment_percentage'] >= 80 ? 'bg-success' : 
               ($kpi['payment_percentage'] >= 50 ? 'bg-warning' : 'bg-danger') ?>" 
            role="progressbar" 
            style="width: <?= min($kpi['payment_percentage'], 100) ?>%">
          </div>
        </div>
        <small class="mt-2 d-block opacity-75">
          <?= number_format($kpi['total_paid_all']) ?> SDG من أصل <?= number_format($kpi['total_receivable']) ?> SDG
        </small>
      </div>
    </div>
   
    <!-- Filters Section -->
    <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">
            نظام التصفية
          </span>
        </div>
      </div>
      
      <form method="get" class="row g-3 align-items-end" id="filterForm">
        <input type="hidden" name="search" value="<?= e($search_query) ?>">
        <input type="hidden" name="page" value="1">
        
        <!-- College Filter -->
        <div class="col-lg-4 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" id="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>
        
        <!-- Program Filter -->
        <div class="col-lg-4 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 if ($programs_table_exists): ?>
                    <?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>";
                    }
                    ?>
                <?php endif; ?>
              </select>
              <span class="input-group-text bg-light border-start-0">
                <i class="fas fa-book"></i>
              </span>
            </div>
          </div>
        </div>
        
        <!-- Batch Filter -->
        <div class="col-lg-4 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>
        
        <!-- Section Filter - Dynamic based on college -->
        <div class="col-lg-4 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" id="filter_section">
                <option value="">جميع الأقسام</option>
                <?php if ($sections_table_exists && $filter_college): ?>
                    <?php
                    // Get sections for selected college
                    $sections_sql = "SELECT id, name FROM sections WHERE parent = ? AND deleted_at IS NULL ORDER BY name";
                    $sections_stmt = $mysqli1->prepare($sections_sql);
                    $sections_stmt->bind_param('i', $filter_college);
                    $sections_stmt->execute();
                    $sections_result = $sections_stmt->get_result();
                    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>";
                    }
                    $sections_stmt->close();
                    ?>
                <?php endif; ?>
              </select>
              <span class="input-group-text bg-light border-start-0">
                <i class="fas fa-sitemap"></i>
              </span>
            </div>
          </div>
        </div>
        
        <!-- Semester Filter -->
        <div class="col-lg-4 col-md-6">
          <div class="form-group">
            <label class="form-label fw-semibold text-muted mb-2">
              <i class="fas fa-calendar-alt me-1"></i>
              الفصل الدراسي
            </label>
            <div class="input-group input-group-sm">
              <select class="form-control border-end-0" name="filter_semester">
                <option value="">جميع الفصول</option>
                <?php if ($semesters_table_exists): ?>
                    <?php
                    $filter_semesters_sql = "SELECT id, name FROM semesters WHERE deleted_at IS NULL ORDER BY id DESC";
                    $filter_semesters_result = $mysqli1->query($filter_semesters_sql);
                    while ($semester = $filter_semesters_result->fetch_assoc()) {
                        $selected = (isset($_GET['filter_semester']) && $_GET['filter_semester'] == $semester['id']) ? 'selected' : '';
                        echo "<option value=\"{$semester['id']}\" $selected>" . htmlspecialchars($semester['name']) . "</option>";
                    }
                    ?>
                <?php endif; ?>
              </select>
              <span class="input-group-text bg-light border-start-0">
                <i class="fas fa-book-open"></i>
              </span>
            </div>
          </div>
        </div>
        
        <!-- Date Filters -->
        <div class="col-lg-4 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="<?= e($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-4 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="<?= e($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>
        
        <!-- Action Buttons -->
        <div class="col-lg-4 col-md-6">
          <div class="d-flex gap-2 pt-4">
            <button type="submit" class="btn btn-primary btn-sm px-3">
              <i class="fas fa-chart-bar me-1"></i>
              عرض التقرير
            </button>
            <a href="?" class="btn btn-outline-secondary btn-sm px-3">
              <i class="fas fa-sync-alt me-1"></i>
              هذا الشهر
            </a>
            <button type="button" class="btn btn-success btn-sm px-3" onclick="generateReport()">
              <i class="fas fa-file-export me-1"></i>
              تصدير التقرير
            </button>
          </div>
        </div>
      </form>
    </div>
  </div>
</div>

<!-- Financial Overview Cards -->
<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="dash-card mb-3">
  <div class="d-flex align-items-center mb-2">
    <h6 class="fw-bold mb-0">التقارير حسب الكليات (مع نسبة الدفع)</h6>
    <div class="ms-auto">
      <span class="chip blue">لوحة المحاسب - عرض جميع الطلبات</span>
      <?php if ($filter_semester && $semesters_table_exists): ?>
        <span class="chip purple ms-1">الفصل: <?= e($semester_name) ?></span>
      <?php endif; ?>
      <?php if ($search_query): ?>
        <span class="chip orange ms-1">بحث: <?= e($search_query) ?></span>
      <?php endif; ?>
    </div>
  </div>

  <?php
  // استعلام للحصول على إحصاءات حسب الكلية مع تطبيق نفس عوامل التصفية
  $college_where = $where_clause;
  
  // Modify college stats query to include semester filter
  $college_stats_joins = "
    LEFT JOIN registrations r ON s.student_id = r.student_id AND r.deleted_at IS NULL
    LEFT JOIN other_fees of ON s.phone_number = of.phone_number AND of.deleted_at IS NULL
  ";
  
  if ($filter_semester && $semesters_table_exists) {
    $college_stats_joins = "
      LEFT JOIN registrations r ON s.student_id = r.student_id AND r.deleted_at IS NULL AND r.semester_id = $filter_semester
      LEFT JOIN other_fees of ON s.phone_number = of.phone_number AND of.deleted_at IS NULL
    ";
  }
  
  $collegesStatsSql = "
    SELECT 
      c.id,
      c.name,
      COUNT(DISTINCT s.student_id) as total_students,
      SUM(CASE WHEN r.is_paid = 1 THEN 1 ELSE 0 END) as paid_registrations,
      SUM(CASE WHEN r.is_paid = 0 THEN 1 ELSE 0 END) as unpaid_registrations,
      SUM(CASE WHEN r.is_paid = 1 THEN r.payment_amount ELSE 0 END) as total_paid_registrations,
      SUM(CASE WHEN r.is_paid = 0 THEN r.payment_amount ELSE 0 END) as total_unpaid_registrations,
      SUM(CASE WHEN of.is_paid = 1 THEN 1 ELSE 0 END) as paid_other_fees,
      SUM(CASE WHEN of.is_paid = 0 THEN 1 ELSE 0 END) as unpaid_other_fees,
      SUM(CASE WHEN of.is_paid = 1 THEN of.amount ELSE 0 END) as total_paid_other_fees,
      SUM(CASE WHEN of.is_paid = 0 THEN of.amount ELSE 0 END) as total_unpaid_other_fees,
      -- حساب نسبة الدفع للكلية
      CASE 
        WHEN (SUM(CASE WHEN r.is_paid = 1 THEN r.payment_amount ELSE 0 END) + 
              SUM(CASE WHEN of.is_paid = 1 THEN of.amount ELSE 0 END)) > 0
        THEN ROUND(
          (SUM(CASE WHEN r.is_paid = 1 THEN r.payment_amount ELSE 0 END) + 
           SUM(CASE WHEN of.is_paid = 1 THEN of.amount ELSE 0 END)) /
          (SUM(CASE WHEN r.is_paid IN (0,1) THEN r.payment_amount ELSE 0 END) + 
           SUM(CASE WHEN of.is_paid IN (0,1) THEN of.amount ELSE 0 END)) * 100, 2
        )
        ELSE 0
      END as payment_percentage
    FROM colleges c
    LEFT JOIN students s ON c.id = s.college_id
    {$college_stats_joins}
    $college_where
    GROUP BY c.id, c.name
    ORDER BY c.name
  ";

  $collegesStmt = $mysqli1->prepare($collegesStatsSql);
  if ($types !== '') {
    $collegesStmt->bind_param($types, ...$params);
  }
  $collegesStmt->execute();
  $byCollege = $collegesStmt->get_result()->fetch_all(MYSQLI_ASSOC);
  $collegesStmt->close();

  // Calculate totals
  $grand_totals = [
    'total_students' => 0,
    'unpaid_registrations' => 0,
    'paid_registrations' => 0,
    'total_paid_registrations' => 0,
    'total_unpaid_registrations' => 0,
    'total_paid_all' => 0,
    'total_unpaid_all' => 0
  ];

  foreach ($byCollege as $college) {
    $grand_totals['total_students'] += $college['total_students'] ?? 0;
    $grand_totals['unpaid_registrations'] += $college['unpaid_registrations'] ?? 0;
    $grand_totals['paid_registrations'] += $college['paid_registrations'] ?? 0;
    $grand_totals['total_paid_registrations'] += $college['total_paid_registrations'] ?? 0;
    $grand_totals['total_unpaid_registrations'] += $college['total_unpaid_registrations'] ?? 0;
  }
  
  $grand_totals['total_paid_all'] = $grand_totals['total_paid_registrations'];
  $grand_totals['total_unpaid_all'] = $grand_totals['total_unpaid_registrations'];
  
  // حساب نسبة الدفع العامة
  $grand_totals['payment_percentage'] = 0;
  if (($grand_totals['total_paid_all'] + $grand_totals['total_unpaid_all']) > 0) {
    $grand_totals['payment_percentage'] = round(
      ($grand_totals['total_paid_all'] / ($grand_totals['total_paid_all'] + $grand_totals['total_unpaid_all'])) * 100, 
      2
    );
  }
  ?>

  <div class="table-responsive">
    <table class="table table-sm tbl">
      <thead class="table-light">
        <tr>
          <th>الكلية</th>
          <th class="text-end">عدد الطلاب</th>
          <th class="text-end">تسجيلات مدفوعة</th>
          <th class="text-end">تسجيلات غير مدفوعة</th>
          <th class="text-end">إجمالي المدفوع</th>
          <th class="text-end">إجمالي غير المدفوع</th>
          <th class="text-end">نسبة الدفع</th>
        </tr>
      </thead>
      <tbody>
        <?php if (empty($byCollege)): ?>
          <tr>
            <td colspan="7" class="text-center text-muted py-2">
              لا توجد بيانات لعرضها.
            </td>
          </tr>
        <?php else: foreach ($byCollege as $college): 
          $college_paid = ($college['total_paid_registrations'] ?? 0);
          $college_unpaid = ($college['total_unpaid_registrations'] ?? 0);
          $payment_percentage = $college['payment_percentage'] ?? 0;
        ?>
          <tr>
            <td><?= e($college['name'] ?: ('#'.$college['id'])) ?></td>
            <td class="text-end"><?= number_format($college['total_students'] ?? 0) ?></td>
            <td class="text-end">
              <span class="text-success"><?= number_format($college['paid_registrations'] ?? 0) ?></span>
              <br>
              <small class="text-muted" style="font-size:0.7rem"><?= number_format($college['total_paid_registrations'] ?? 0) ?> SDG</small>
            </td>
            <td class="text-end">
              <span class="text-danger"><?= number_format($college['unpaid_registrations'] ?? 0) ?></span>
              <br>
              <small class="text-muted" style="font-size:0.7rem"><?= number_format($college['total_unpaid_registrations'] ?? 0) ?> SDG</small>
            </td>
            <td class="text-end">
              <strong class="text-success" style="font-size:0.85rem">
                <?= number_format($college_paid) ?> SDG
              </strong>
            </td>
            <td class="text-end">
              <strong class="text-danger" style="font-size:0.85rem">
                <?= number_format($college_unpaid) ?> SDG
              </strong>
            </td>
            <td class="text-end">
              <div class="progress student-payment-progress">
                <div class="progress-bar 
                  <?= $payment_percentage >= 80 ? 'bg-success' : 
                     ($payment_percentage >= 50 ? 'bg-warning' : 'bg-danger') ?>" 
                  role="progressbar" 
                  style="width: <?= min($payment_percentage, 100) ?>%"
                  aria-valuenow="<?= $payment_percentage ?>" 
                  aria-valuemin="0" 
                  aria-valuemax="100">
                </div>
              </div>
              <span class="student-payment-percentage 
                <?= $payment_percentage >= 80 ? 'text-success' : 
                   ($payment_percentage >= 50 ? 'text-warning' : 'text-danger') ?>">
                <?= number_format($payment_percentage, 1) ?>%
              </span>
            </td>
          </tr>
        <?php endforeach; endif; ?>
      </tbody>
      <tfoot class="table-light">
        <tr>
          <th class="text-start" style="font-size:0.8rem">الإجمالي العام:</th>
          <th class="text-end" style="font-size:0.8rem"><?= number_format($grand_totals['total_students']) ?></th>
          <th class="text-end" style="font-size:0.8rem">
            <span class="text-success"><?= number_format($grand_totals['paid_registrations']) ?></span>
            <br>
            <small class="text-muted" style="font-size:0.65rem"><?= number_format($grand_totals['total_paid_registrations']) ?> SDG</small>
          </th>
          <th class="text-end" style="font-size:0.8rem">
            <span class="text-danger"><?= number_format($grand_totals['unpaid_registrations']) ?></span>
            <br>
            <small class="text-muted" style="font-size:0.65rem"><?= number_format($grand_totals['total_unpaid_registrations']) ?> SDG</small>
          </th>
          <th class="text-end" style="font-size:0.8rem">
            <strong class="text-success"><?= number_format($grand_totals['total_paid_all']) ?> SDG</strong>
          </th>
          <th class="text-end" style="font-size:0.8rem">
            <strong class="text-danger"><?= number_format($grand_totals['total_unpaid_all']) ?> SDG</strong>
          </th>
          <th class="text-end" style="font-size:0.8rem">
            <div class="progress student-payment-progress">
              <div class="progress-bar 
                <?= $grand_totals['payment_percentage'] >= 80 ? 'bg-success' : 
                   ($grand_totals['payment_percentage'] >= 50 ? 'bg-warning' : 'bg-danger') ?>" 
                role="progressbar" 
                style="width: <?= min($grand_totals['payment_percentage'], 100) ?>%">
              </div>
            </div>
            <strong class="student-payment-percentage 
              <?= $grand_totals['payment_percentage'] >= 80 ? 'text-success' : 
                 ($grand_totals['payment_percentage'] >= 50 ? 'text-warning' : 'text-danger') ?>">
              <?= number_format($grand_totals['payment_percentage'], 1) ?>%
            </strong>
          </th>
        </tr>
      </tfoot>
    </table>
  </div>
</div>

<!-- جدول الطلاب مع ترقيم الصفحات ونسبة الدفع -->
<div class="dash-card printable-table">
  <div class="d-flex align-items-center justify-content-between mb-3">
    <h6 class="fw-bold mb-0">الطلاب (مع نسبة الدفع)</h6>
    <div>
      <span class="chip blue">لوحة المحاسب - عرض جميع الطلاب</span>
      <span class="chip orange ms-1">الصفحة <?= $page ?> من <?= $total_pages ?></span>
      <span class="chip green ms-1">إجمالي الطلاب: <?= number_format($total_students) ?></span>
      <?php if ($filter_semester && $semesters_table_exists): ?>
        <span class="chip purple ms-1">الفصل: <?= e($semester_name) ?></span>
      <?php endif; ?>
      <?php if ($search_query): ?>
        <span class="chip orange ms-1">بحث: <?= e($search_query) ?></span>
      <?php endif; ?>
      <button type="button" class="btn btn-sm btn-success print-btn ms-2" onclick="printStudentsTable()">
        <i class="fas fa-print me-1"></i>طباعة الجدول
      </button>
      <button type="button" class="btn btn-sm btn-warning print-btn ms-2" onclick="exportStudentsExcel()">
        <i class="fas fa-file-excel me-1"></i>تصدير لإكسل
      </button>
    </div>
  </div>

  <!-- Pagination Top -->
  <?php if ($total_pages > 1): ?>
  <div class="d-flex justify-content-between align-items-center mb-3 no-print">
    <div class="d-flex gap-1">
      <?php if ($page > 1): ?>
        <a href="<?= build_pagination_url(1) ?>" class="btn btn-sm btn-outline-secondary">
          <i class="fas fa-angle-double-right"></i>
        </a>
        <a href="<?= build_pagination_url($page - 1) ?>" class="btn btn-sm btn-outline-secondary">
          <i class="fas fa-angle-right"></i>
        </a>
      <?php endif; ?>
      
      <?php
      // Show page numbers
      $start_page = max(1, $page - 2);
      $end_page = min($total_pages, $page + 2);
      
      if ($start_page > 1) {
        echo '<span class="mx-1">...</span>';
      }
      
      for ($i = $start_page; $i <= $end_page; $i++):
      ?>
        <a href="<?= build_pagination_url($i) ?>" 
           class="btn btn-sm <?= $i == $page ? 'btn-primary' : 'btn-outline-secondary' ?>">
          <?= $i ?>
        </a>
      <?php endfor; ?>
      
      <?php if ($end_page < $total_pages): ?>
        <span class="mx-1">...</span>
      <?php endif; ?>
      
      <?php if ($page < $total_pages): ?>
        <a href="<?= build_pagination_url($page + 1) ?>" class="btn btn-sm btn-outline-secondary">
          <i class="fas fa-angle-left"></i>
        </a>
        <a href="<?= build_pagination_url($total_pages) ?>" class="btn btn-sm btn-outline-secondary">
          <i class="fas fa-angle-double-left"></i>
        </a>
      <?php endif; ?>
    </div>
    
    <div class="text-muted" style="font-size: 0.8rem;">
      عرض <?= number_format(min($per_page, $total_students - $offset)) ?> من <?= number_format($total_students) ?> طالب
    </div>
  </div>
  <?php endif; ?>

  <div class="table-responsive">
    <table class="table table-sm" id="studentsTable">
      <thead class="table-light">
        <tr>
          <th>#</th>
          <th>الطالب</th>
          <th>الرقم</th>
          <th>الكلية</th>
          <th>البرنامج</th>
          <th>الدفعة</th>
          <th>القسم</th>
          <th>البنك</th>
          <th>حالة الرسوم</th>
          <th>إجمالي المدفوع</th>
          <th>إجمالي المستحق</th>
          <th>نسبة الدفع</th>
        </tr>
      </thead>
      <tbody>
        <?php if (empty($recentAccepted)): ?>
          <tr><td colspan="12" class="text-center text-muted py-2">— لا توجد نتائج —</td></tr>
        <?php else: 
          $counter = $offset + 1;
          foreach ($recentAccepted as $r): 
            // Calculate fees for this student with semester filter
            $fees_sql = "
              SELECT 
                (SELECT COALESCE(SUM(payment_amount), 0) FROM registrations WHERE student_id = ? AND is_paid = 1 AND deleted_at IS NULL";
            if ($filter_semester && $semesters_table_exists) {
                $fees_sql .= " AND semester_id = " . (int)$filter_semester;
            }
            $fees_sql .= ") AS paid_registrations,
                (SELECT COALESCE(SUM(payment_amount), 0) FROM registrations WHERE student_id = ? AND is_paid = 0 AND deleted_at IS NULL";
            if ($filter_semester && $semesters_table_exists) {
                $fees_sql .= " AND semester_id = " . (int)$filter_semester;
            }
            $fees_sql .= ") AS unpaid_registrations,
                (SELECT COALESCE(SUM(amount), 0) FROM other_fees WHERE phone_number = ? AND is_paid = 1 AND deleted_at IS NULL) AS paid_other_fees,
                (SELECT COALESCE(SUM(amount), 0) FROM other_fees WHERE phone_number = ? AND is_paid = 0 AND deleted_at IS NULL) AS unpaid_other_fees
            ";
            
            $fees_stmt = $mysqli1->prepare($fees_sql);
            $fees_stmt->bind_param("ssss", $r['student_id'], $r['student_id'], $r['phone_number'], $r['phone_number']);
            $fees_stmt->execute();
            $fees_data = $fees_stmt->get_result()->fetch_assoc();
            $fees_stmt->close();

            $total_paid = ($fees_data['paid_registrations'] ?? 0) + ($fees_data['paid_other_fees'] ?? 0);
            $total_unpaid = ($fees_data['unpaid_registrations'] ?? 0) + ($fees_data['unpaid_other_fees'] ?? 0);
            $has_unpaid = $total_unpaid > 0;
            $bank_name = $r['bank_name'] ?? '—';
            
            // حساب نسبة الدفع للطالب
            $student_payment_percentage = 0;
            if (($total_paid + $total_unpaid) > 0) {
                $student_payment_percentage = round(($total_paid / ($total_paid + $total_unpaid)) * 100, 1);
            }
        ?>
            <tr>
              <td class="text-muted" style="font-size: 0.8rem;"><?= $counter++ ?></td>
              <td style="font-size:0.8rem"><?= e($r['student_name'] ?: $r['student_name_eng'] ?: '—') ?></td>
              <td style="font-size:0.8rem"><code><?= e($r['student_id']) ?></code></td>
              <td style="font-size:0.8rem"><?= e($r['college_name'] ?? '—') ?></td>
              <td style="font-size:0.8rem"><?= e($r['program_name'] ?? '—') ?></td>
              <td style="font-size:0.8rem"><?= e($r['batch_name'] ?: $r['batch_id']) ?></td>
              <td style="font-size:0.8rem"><?= e($r['section_name'] ?? '—') ?></td>
              <td>
                <?php if ($bank_name !== '—'): ?>
                  <span class="chip blue"><?= e($bank_name) ?></span>
                <?php else: ?>
                  <span class="chip gray">—</span>
                <?php endif; ?>
              </td>
              <td>
                <?php if ($total_paid > 0 && !$has_unpaid): ?>
                  <span class="chip green">مسدد بالكامل</span>
                <?php elseif ($total_paid > 0 && $has_unpaid): ?>
                  <span class="chip orange">مسدد جزئياً</span>
                <?php else: ?>
                  <span class="chip red">غير مسدد</span>
                <?php endif; ?>
              </td>
              <td class="text-success fw-bold" style="font-size:0.8rem"><?= number_format($total_paid) ?> SDG</td>
              <td class="text-danger fw-bold" style="font-size:0.8rem"><?= number_format($total_unpaid) ?> SDG</td>
              <td>
                <div class="progress student-payment-progress">
                  <div class="progress-bar 
                    <?= $student_payment_percentage >= 80 ? 'bg-success' : 
                       ($student_payment_percentage >= 50 ? 'bg-warning' : 'bg-danger') ?>" 
                    role="progressbar" 
                    style="width: <?= min($student_payment_percentage, 100) ?>%">
                  </div>
                </div>
                <span class="student-payment-percentage 
                  <?= $student_payment_percentage >= 80 ? 'text-success' : 
                     ($student_payment_percentage >= 50 ? 'text-warning' : 'text-danger') ?>"
                  style="font-size:0.7rem">
                  <?= number_format($student_payment_percentage, 1) ?>%
                </span>
              </td>
            </tr>
        <?php endforeach; endif; ?>
      </tbody>
    </table>
  </div>

  <!-- Pagination Bottom -->
  <?php if ($total_pages > 1): ?>
  <div class="d-flex justify-content-between align-items-center mt-3 pt-3 border-top no-print">
    <div class="text-muted" style="font-size: 0.8rem;">
      <?= number_format($per_page) ?> طالب في كل صفحة
    </div>
    
    <div class="d-flex gap-1">
      <?php if ($page > 1): ?>
        <a href="<?= build_pagination_url(1) ?>" class="btn btn-sm btn-outline-secondary">
          <i class="fas fa-angle-double-right"></i> الأولى
        </a>
        <a href="<?= build_pagination_url($page - 1) ?>" class="btn btn-sm btn-outline-secondary">
          <i class="fas fa-angle-right"></i> السابقة
        </a>
      <?php endif; ?>
      
      <div class="d-flex align-items-center mx-2">
        <span class="text-muted me-2" style="font-size: 0.8rem;">الصفحة:</span>
        <select class="form-select form-select-sm" style="width: auto;" onchange="window.location.href=this.value">
          <?php for ($i = 1; $i <= $total_pages; $i++): ?>
            <option value="<?= build_pagination_url($i) ?>" <?= $i == $page ? 'selected' : '' ?>>
              <?= $i ?>
            </option>
          <?php endfor; ?>
        </select>
      </div>
      
      <?php if ($page < $total_pages): ?>
        <a href="<?= build_pagination_url($page + 1) ?>" class="btn btn-sm btn-outline-secondary">
          التالية <i class="fas fa-angle-left"></i>
        </a>
        <a href="<?= build_pagination_url($total_pages) ?>" class="btn btn-sm btn-outline-secondary">
          الأخيرة <i class="fas fa-angle-double-left"></i>
        </a>
      <?php endif; ?>
    </div>
  </div>
  <?php endif; ?>
</div>
</div>

<script>
function generateReport() {
  const startDate = document.querySelector('input[name="start_date"]').value;
  const endDate = document.querySelector('input[name="end_date"]').value;
  const filterCollege = document.querySelector('select[name="filter_college"]').value;
  const filterProgram = document.querySelector('select[name="filter_program"]').value;
  const filterBatch = document.querySelector('select[name="filter_batch"]').value;
  const filterSection = document.querySelector('select[name="filter_section"]').value;
  const filterSemester = document.querySelector('select[name="filter_semester"]').value;
  const searchQuery = document.querySelector('input[name="search"]').value;
  
  if (!startDate || !endDate) {
    alert('يرجى تحديد تاريخ البداية والنهاية');
    return;
  }
  
  // Build report URL with all filters
  let reportUrl = `financial_reports.php?start_date=${startDate}&end_date=${endDate}&export=1`;
  
  if (filterCollege) {
    reportUrl += `&filter_college=${filterCollege}`;
  }
  
  if (filterProgram) {
    reportUrl += `&filter_program=${filterProgram}`;
  }
  
  if (filterBatch) {
    reportUrl += `&filter_batch=${filterBatch}`;
  }
  
  if (filterSection) {
    reportUrl += `&filter_section=${filterSection}`;
  }
  
  if (filterSemester) {
    reportUrl += `&filter_semester=${filterSemester}`;
  }
  
  if (searchQuery) {
    reportUrl += `&search=${encodeURIComponent(searchQuery)}`;
  }
  
  window.open(reportUrl, '_blank');
}

// AJAX for loading sections based on college
$(document).ready(function() {
  $('#filter_college').change(function() {
    const collegeId = $(this).val();
    const sectionSelect = $('#filter_section');
    
    // Clear current options except the first one
    sectionSelect.find('option').not(':first').remove();
    
    if (collegeId && collegeId !== '') {
      // Show loading
      sectionSelect.prop('disabled', true);
      
      $.ajax({
        url: 'ajax/get_sections.php',
        type: 'GET',
        data: { college_id: collegeId },
        dataType: 'json',
        success: function(sections) {
          // Add new options
          if (sections && sections.length > 0) {
            $.each(sections, function(index, section) {
              sectionSelect.append($('<option>', {
                value: section.id,
                text: section.name
              }));
            });
          }
          
          sectionSelect.prop('disabled', false);
        },
        error: function() {
          console.error('Error loading sections');
          sectionSelect.prop('disabled', false);
          sectionSelect.append($('<option>', {
            value: '',
            text: 'خطأ في تحميل الأقسام'
          }));
        }
      });
    } else {
      // If no college selected, just enable the select
      sectionSelect.prop('disabled', false);
    }
  });
});

function printStudentsTable() {
  const originalContent = document.body.innerHTML;
  
  // Create print-friendly content
  const tableContent = document.getElementById('studentsTable').outerHTML;
  const printContent = `
    <!DOCTYPE html>
    <html dir="rtl" lang="ar">
    <head>
      <meta charset="UTF-8">
      <title>قائمة الطلاب - المحاسب</title>
      <style>
        body {
          font-family: 'Cairo', sans-serif;
          margin: 20px;
          line-height: 1.6;
          color: #333;
        }
        .header {
          text-align: center;
          margin-bottom: 20px;
          border-bottom: 2px solid #3E6B54;
          padding-bottom: 10px;
        }
        .header h1 {
          margin: 0;
          color: #3E6B54;
          font-size: 22px;
        }
        .header .info {
          color: #666;
          font-size: 14px;
          margin-top: 5px;
        }
        .table {
          width: 100%;
          border-collapse: collapse;
          margin-bottom: 20px;
          font-size: 11px;
        }
        .table th {
          background-color: #3E6B54;
          color: white;
          padding: 8px;
          text-align: right;
          border: 1px solid #dee2e6;
          font-weight: bold;
        }
        .table td {
          padding: 6px;
          border: 1px solid #dee2e6;
          text-align: right;
        }
        .table tbody tr:nth-child(even) {
          background-color: #f8f9fa;
        }
        .status-paid {
          background-color: #d1fae5;
          color: #065f46;
          padding: 3px 8px;
          border-radius: 12px;
          font-size: 10px;
          display: inline-block;
        }
        .status-partial {
          background-color: #fed7aa;
          color: #9a3412;
          padding: 3px 8px;
          border-radius: 12px;
          font-size: 10px;
          display: inline-block;
        }
        .status-unpaid {
          background-color: #fee2e2;
          color: #991b1b;
          padding: 3px 8px;
          border-radius: 12px;
          font-size: 10px;
          display: inline-block;
        }
        .bank-tag {
          background-color: #dbeafe;
          color: #1e3a8a;
          padding: 3px 8px;
          border-radius: 12px;
          font-size: 10px;
          display: inline-block;
        }
        .footer {
          margin-top: 20px;
          padding-top: 10px;
          border-top: 1px solid #dee2e6;
          font-size: 12px;
          color: #666;
          text-align: center;
        }
        @media print {
          @page {
            margin: 0.5cm;
            size: A4 landscape;
          }
          body {
            margin: 0;
            font-size: 10px;
          }
          .table {
            font-size: 9px;
          }
          .table th, .table td {
            padding: 4px;
          }
        }
      </style>
    </head>
    <body>
      <div class="header">
        <h1>قائمة الطلاب - المحاسب</h1>
        <div class="info">
          <div>اسم المحاسب: <?= e($committeeName ?: 'المحاسب') ?></div>
          <div>الفترة: من <?= date('d/m/Y', strtotime($start_date)) ?> إلى <?= date('d/m/Y', strtotime($end_date)) ?></div>
          <div>تاريخ الطباعة: <?= date('d/m/Y H:i') ?></div>
          <?php if ($filter_college): ?>
          <div>الكلية: <?= e($college_name) ?></div>
          <?php endif; ?>
          <?php if ($filter_semester && $semesters_table_exists): ?>
          <div>الفصل الدراسي: <?= e($semester_name) ?></div>
          <?php endif; ?>
          <?php if ($search_query): ?>
          <div>بحث: <?= e($search_query) ?></div>
          <?php endif; ?>
          <div>إجمالي الطلاب: <?= number_format($total_students) ?></div>
        </div>
      </div>

      ${tableContent}

      <div class="footer">
        <div>تم إنشاء التقرير بواسطة: <?= e($committeeName ?: 'المحاسب') ?></div>
        <div>تاريخ الطباعة: <?= date('d/m/Y H:i') ?></div>
        <div>نظام القبول الجامعي</div>
      </div>
    </body>
    </html>
  `;

  // Open print window
  const printWindow = window.open('', '_blank');
  printWindow.document.write(printContent);
  printWindow.document.close();
  
  // Wait for content to load then print
  printWindow.onload = function() {
    printWindow.print();
  };
}

function exportStudentsExcel() {
  const startDate = document.querySelector('input[name="start_date"]').value;
  const endDate = document.querySelector('input[name="end_date"]').value;
  
  if (!startDate || !endDate) {
    alert('يرجى تحديد تاريخ البداية والنهاية');
    return;
  }
  
  // Build Excel export URL
  let reportUrl = `export_students_excel.php?start_date=${startDate}&end_date=${endDate}`;
  
  // Add all filters
  const filters = ['filter_college', 'filter_program', 'filter_batch', 'filter_section', 'filter_semester', 'search'];
  filters.forEach(filter => {
    const element = document.querySelector(`[name="${filter}"]`);
    if (element && element.value) {
      reportUrl += `&${filter}=${encodeURIComponent(element.value)}`;
    }
  });
  
  window.open(reportUrl, '_blank');
}
</script>

<?php include 'footer.php'; ?>
