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

$committeeId   = (int)($_SESSION['committee_id'] ?? 0);
$committeeName = (string)($_SESSION['committee_name'] ?? '');
$canAll        = (bool)($_SESSION['committee_can_all'] ?? false);
$scopeCollege  = isset($_SESSION['committee_college_id']) ? (int)$_SESSION['committee_college_id'] : null;

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

// 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)$_filter_semester : null;

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

/* ---------- helpers ---------- */
function col_exists(mysqli $db, string $table, string $col): bool {
  $st = $db->prepare("SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=? LIMIT 1");
  $st->bind_param("ss", $table, $col);
  $st->execute();
  $ok = (bool)$st->get_result()->fetch_row();
  $st->close();
  return $ok;
}

// 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
$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 ---------- */
$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 join
$registration_join = "LEFT JOIN registrations r ON r.student_id = s.student_id AND r.deleted_at IS NULL";

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

// KPI Query - Counting distinct students with payment status
$sqlKpi = "
  SELECT
    COUNT(DISTINCT s.student_id) AS total_students,
    
    -- Financial KPIs: Students with paid registrations in specified semester
    COUNT(DISTINCT CASE WHEN r.id IS NOT NULL AND r.is_paid = 1 THEN s.student_id END) AS paid_registrations,
    
    -- Students with unpaid registrations in specified semester
    COUNT(DISTINCT CASE WHEN r.id IS NOT NULL AND r.is_paid = 0 THEN s.student_id END) AS unpaid_registrations,
    
    -- 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
    
  FROM students s
  {$registration_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_unpaid_all'] = ($kpi['total_unpaid_registrations'] ?? 0);
$kpi['total_receivable'] = $kpi['total_paid_all'] + $kpi['total_unpaid_all'];

// Calculate payment percentage
$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'] as $k) {
    $kpi[$k] = (int)($kpi[$k] ?? 0);
}

/* ---------- Payments by Bank ---------- */
$bankPayments = [];
$totalBankPayments = 0;

// Build bank payments query with all filters
$bank_where_conditions = [];
$bank_params = [];
$bank_types = 'ss'; // للتاريخين

// Add student filters for bank queries
if (!$canAll && $scopeCollege !== null) {
    $bank_where_conditions[] = "s.college_id = ?";
    $bank_types .= 'i';
    $bank_params[] = $scopeCollege;
} elseif (!$canAll && $scopeCollege === null) {
    $bank_where_conditions[] = "1=0";
}

if ($filter_college) {
    $bank_where_conditions[] = "s.college_id = ?";
    $bank_types .= 'i';
    $bank_params[] = $filter_college;
}

if ($filter_program && $programs_table_exists) {
    $bank_where_conditions[] = "s.program_id = ?";
    $bank_types .= 'i';
    $bank_params[] = $filter_program;
}

if ($filter_batch && $batches_table_exists) {
    $bank_where_conditions[] = "s.batch_id = ?";
    $bank_types .= 'i';
    $bank_params[] = $filter_batch;
}

if ($filter_section && $sections_table_exists) {
    $bank_where_conditions[] = "s.section_id = ?";
    $bank_types .= 'i';
    $bank_params[] = $filter_section;
}

// Add search to bank payments
if ($search_query) {
    $bank_where_conditions[] = "(s.student_name LIKE ? OR s.student_name_eng LIKE ? OR s.student_id LIKE ? OR s.phone_number LIKE ?)";
    $bank_types .= 'ssss';
    $search_param = "%{$search_query}%";
    $bank_params[] = $search_param;
    $bank_params[] = $search_param;
    $bank_params[] = $search_param;
    $bank_params[] = $search_param;
}

$bank_where_clause = !empty($bank_where_conditions) ? "WHERE " . implode(" AND ", $bank_where_conditions) : "";

// Add semester filter
$semester_filter = "";
if ($filter_semester && $semesters_table_exists) {
    $semester_filter = "AND p.semester_id = " . (int)$filter_semester;
}

// Bank payments query
$sqlBankPayments = "
  SELECT 
    b.id,
    b.name AS bank_name,
    COUNT(p.id) AS payment_count,
    COALESCE(SUM(p.payment_amount), 0) AS total_amount,
    COALESCE(SUM(p.payment_amount), 0) AS grand_total
  FROM banks b
  LEFT JOIN payments p ON p.bank_id = b.id 
    AND p.deleted_at IS NULL
    AND DATE(p.created_at) BETWEEN ? AND ?
    {$semester_filter}
  LEFT JOIN students s ON p.student_id = s.student_id
  {$bank_where_clause}
  GROUP BY b.id, b.name
  HAVING total_amount > 0
  ORDER BY grand_total DESC
";

$st = $mysqli1->prepare($sqlBankPayments);
if (!empty($bank_params)) {
    $st->bind_param($bank_types, $start_date, $end_date, ...$bank_params);
} else {
    $st->bind_param("ss", $start_date, $end_date);
}
$st->execute();
$bankPayments = $st->get_result()->fetch_all(MYSQLI_ASSOC);
$st->close();

// Calculate total bank payments for the period
foreach ($bankPayments as $bank) {
    $totalBankPayments += $bank['grand_total'];
}

/* ---------- 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>
<script src="https://cdn.jsdelivr.net/npm/sweetalert2@11"></script>

<style>
  /* Apply Cairo font to ALL elements */
  * {
    font-family: 'Cairo', system-ui, -apple-system, 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', system-ui, -apple-system, sans-serif !important;
  }
  
  /* Ensure form elements use Cairo */
  .form-control, .form-control-sm, .form-select, .form-select-sm {
    font-family: 'Cairo', system-ui, -apple-system, sans-serif !important;
  }
  
  /* Ensure buttons use Cairo */
  .btn, .btn-sm, .btn-primary, .btn-secondary, .btn-success, .btn-outline-secondary {
    font-family: 'Cairo', system-ui, -apple-system, sans-serif !important;
  }
  
  /* Ensure table elements use Cairo */
  .table, .table th, .table td {
    font-family: 'Cairo', system-ui, -apple-system, sans-serif !important;
  }
  
  /* Ensure chip elements use Cairo */
  .chip, .chip.green, .chip.gray, .chip.red, .chip.blue, .chip.orange, .chip.purple {
    font-family: 'Cairo', system-ui, -apple-system, 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;
  }
  
  /* Advanced Search Box */
  .advanced-search-box {
    background: linear-gradient(135deg, #f5f7fa 0%, #c3cfe2 100%);
    border-radius: 10px;
    padding: 15px;
    margin-bottom: 15px;
  }
  
  .student-payment-progress {
    height: 20px;
    margin-top: 5px;
    border-radius: 5px;
  }
  
  .student-payment-percentage {
    font-size: 0.85rem;
    font-weight: bold;
  }
  
  /* Loading spinner */
  .loading-overlay {
    display: none;
    position: fixed;
    top: 0;
    left: 0;
    width: 100%;
    height: 100%;
    background: rgba(255, 255, 255, 0.8);
    z-index: 9999;
    justify-content: center;
    align-items: center;
  }
  
  .loading-spinner {
    width: 50px;
    height: 50px;
    border: 5px solid #f3f3f3;
    border-top: 5px solid #3E6B54;
    border-radius: 50%;
    animation: spin 1s linear infinite;
  }
  
  @keyframes spin {
    0% { transform: rotate(0deg); }
    100% { transform: rotate(360deg); }
  }
  
  /* Quick Stats */
  .quick-stats {
    display: flex;
    gap: 10px;
    flex-wrap: wrap;
    margin-bottom: 15px;
  }
  
  .quick-stat-item {
    flex: 1;
    min-width: 120px;
    background: white;
    border-radius: 8px;
    padding: 10px;
    text-align: center;
    box-shadow: 0 2px 4px rgba(0,0,0,0.1);
  }
  
  .quick-stat-value {
    font-size: 1.2rem;
    font-weight: bold;
    color: #3E6B54;
  }
  
  .quick-stat-label {
    font-size: 0.9rem;
    color: #666;
  }
  
  /* Print Styles */
  @media print {
    .no-print {
      display: none !important;
    }
    .printable-table {
      page-break-inside: avoid;
    }
    .dash-card {
      box-shadow: none !important;
      border: 1px solid #ddd !important;
    }
  }
</style>

<div class="loading-overlay" id="loadingOverlay">
  <div class="loading-spinner"></div>
</div>

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

<!-- Advanced Search Box -->
<div class="advanced-search-box">
  <div class="row">
    <div class="col-md-8">
      <form method="get" action="" class="row g-2">
        <div class="col-md-12">
          <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>
        </div>
        
        <?php if ($search_query): ?>
          <div class="col-md-12 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 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>
  </div>
</div>

<!-- Quick Stats -->
<div class="quick-stats">
  <div class="quick-stat-item">
    <div class="quick-stat-value"><?= number_format($kpi['total_students']) ?></div>
    <div class="quick-stat-label">إجمالي الطلاب</div>
  </div>
  <div class="quick-stat-item">
    <div class="quick-stat-value"><?= number_format($kpi['paid_registrations']) ?></div>
    <div class="quick-stat-label">طلاب مدفوعين</div>
  </div>
  <div class="quick-stat-item">
    <div class="quick-stat-value"><?= number_format($kpi['unpaid_registrations']) ?></div>
    <div class="quick-stat-label">طلاب غير مدفوعين</div>
  </div>
  <div class="quick-stat-item">
    <div class="quick-stat-value"><?= number_format($kpi['total_paid_all']) ?> SDG</div>
    <div class="quick-stat-label">إجمالي المدفوع</div>
  </div>
  <div class="quick-stat-item">
    <div class="quick-stat-value"><?= number_format($kpi['total_unpaid_all']) ?> SDG</div>
    <div class="quick-stat-label">إجمالي المستحق</div>
  </div>
</div>

<!-- Date Range Filter -->
<div class="date-filter-card" dir="rtl">
  <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">
    
    <div class="row">
      <!-- Date Range -->
      <div class="col-md-3">
        <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-md-3">
        <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>
      
      <!-- College Filter -->
      <div class="col-md-3">
        <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>
      
    
      <!-- Section Filter -->
      <div class="col-md-3">
        <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>


    </div>
    
    <div class="row mt-2">
        
              <!-- Program Filter -->
      <div class="col-md-3">
        <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-md-3">
        <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>
      
      <!-- Semester Filter -->
      <div class="col-md-3">
        <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>
      

      
      <!-- Action Buttons -->
      <div class="col-md-3">
        <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>
        </div>
      </div>
    </div>
  </form>
</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>

<!-- Payments by Bank -->
<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">الفترة: <?= date('d/m/Y', strtotime($start_date)) ?> - <?= date('d/m/Y', strtotime($end_date)) ?></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>

  <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>
        </tr>
      </thead>
      <tbody>
        <?php if (empty($bankPayments)): ?>
          <tr>
            <td colspan="4" class="text-center text-muted py-2">
              لا توجد مدفوعات في الفترة المحددة.
            </td>
          </tr>
        <?php else: 
          foreach ($bankPayments as $bank): 
            $percentage = $totalBankPayments > 0 ? ($bank['grand_total'] / $totalBankPayments) * 100 : 0;
        ?>
          <tr>
            <td><?= e($bank['bank_name']) ?></td>
            <td class="text-end"><?= number_format($bank['payment_count']) ?></td>
            <td class="text-end fw-bold text-success"><?= number_format($bank['grand_total']) ?> SDG</td>
            <td class="text-end">
              <div class="d-flex align-items-center">
                <span class="me-2" style="font-size:0.95rem"><?= number_format($percentage, 1) ?>%</span>
                <div class="progress bank-progress" style="width: 100%;">
                  <div class="progress-bar bg-success" role="progressbar" style="width: <?= $percentage ?>%"></div>
                </div>
              </div>
            </td>
          </tr>
        <?php 
          endforeach; 
        endif; ?>
      </tbody>
      <tfoot class="table-light">
        <tr>
          <th class="text-start">الإجمالي:</th>
          <th class="text-end"><?= number_format(array_sum(array_column($bankPayments, 'payment_count'))) ?></th>
          <th class="text-end fw-bold text-primary"><?= number_format($totalBankPayments) ?> SDG</th>
          <th class="text-end">100%</th>
        </tr>
      </tfoot>
    </table>
  </div>
</div>

<!-- College Reports with Payment Percentage -->
<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
  // Query for statistics by college with same filters
  $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
  ";
  
  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
    ";
  }
  
  // Fixed query: Correctly counts students with paid/unpaid registrations in specified semester
  $collegesStatsSql = "
    SELECT 
      c.id,
      c.name,
      COUNT(DISTINCT s.student_id) as total_students,
      COUNT(DISTINCT CASE WHEN r.is_paid = 1 THEN s.student_id END) as paid_registrations,
      COUNT(DISTINCT CASE WHEN r.is_paid = 0 THEN s.student_id 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,
      CASE 
        WHEN SUM(CASE WHEN r.is_paid IN (0,1) THEN r.payment_amount ELSE 0 END) > 0
        THEN ROUND(
          (SUM(CASE WHEN r.is_paid = 1 THEN r.payment_amount ELSE 0 END)) /
          (SUM(CASE WHEN r.is_paid IN (0,1) THEN r.payment_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'];
  
  // Calculate overall payment percentage
  $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>

<!-- Students Table with Pagination and Payment Percentage -->
<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
            ";
            
            $fees_stmt = $mysqli1->prepare($fees_sql);
            $fees_stmt->bind_param("ss", $r['student_id'], $r['student_id']);
            $fees_stmt->execute();
            $fees_data = $fees_stmt->get_result()->fetch_assoc();
            $fees_stmt->close();

            $total_paid = ($fees_data['paid_registrations'] ?? 0);
            $total_unpaid = ($fees_data['unpaid_registrations'] ?? 0);
            $has_unpaid = $total_unpaid > 0;
            $bank_name = $r['bank_name'] ?? '—';
            
            // Calculate student payment percentage
            $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>
// 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);
    }
  });
  
  // Show loading overlay on form submit
  $('#filterForm').submit(function() {
    $('#loadingOverlay').show();
  });
});

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', Arial, 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 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 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;
  const startDate = document.querySelector('input[name="start_date"]').value;
  const endDate = document.querySelector('input[name="end_date"]').value;
  
  // Build Excel export URL
  let reportUrl = `export_students_excel.php`;
  
  // Add all filters
  const params = new URLSearchParams();
  
  if (filterCollege) params.append('filter_college', filterCollege);
  if (filterProgram) params.append('filter_program', filterProgram);
  if (filterBatch) params.append('filter_batch', filterBatch);
  if (filterSection) params.append('filter_section', filterSection);
  if (filterSemester) params.append('filter_semester', filterSemester);
  if (searchQuery) params.append('search', searchQuery);
  if (startDate) params.append('start_date', startDate);
  if (endDate) params.append('end_date', endDate);
  
  if (params.toString()) {
    reportUrl += '?' + params.toString();
  }
  
  window.open(reportUrl, '_blank');
}
</script>

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