<?php
// /admission3333/accountant_dashboard.php - النسخة الكاملة مع إصلاح مشكلة البرامج
require 'db.php';
require 'helpers.php';
require_committee();

// Enable strict error reporting
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli1->set_charset('utf8mb4');

// Get committee information
require 'committee_session.php';

// ============================================
// INPUT VALIDATION AND SANITIZATION
// ============================================

// Date range validation
$start_date = isset($_GET['start_date']) && preg_match('/^\d{4}-\d{2}-\d{2}$/', $_GET['start_date']) 
    ? $_GET['start_date'] 
    : date('Y-m-01');
$end_date = isset($_GET['end_date']) && preg_match('/^\d{4}-\d{2}-\d{2}$/', $_GET['end_date']) 
    ? $_GET['end_date'] 
    : date('Y-m-t');

if ($start_date > $end_date) {
    $end_date = $start_date;
}

// Filter values with strict validation
$filter_college = isset($_GET['filter_college']) && is_numeric($_GET['filter_college']) && $_GET['filter_college'] > 0 
    ? (int)$_GET['filter_college'] 
    : null;
$filter_program = isset($_GET['filter_program']) && is_numeric($_GET['filter_program']) && $_GET['filter_program'] > 0 
    ? (int)$_GET['filter_program'] 
    : null;
$filter_specialization = isset($_GET['filter_specialization']) && is_numeric($_GET['filter_specialization']) && $_GET['filter_specialization'] > 0 
    ? (int)$_GET['filter_specialization'] 
    : null;
$filter_batch = isset($_GET['filter_batch']) && is_numeric($_GET['filter_batch']) && $_GET['filter_batch'] > 0 
    ? (int)$_GET['filter_batch'] 
    : null;
$filter_section = isset($_GET['filter_section']) && is_numeric($_GET['filter_section']) && $_GET['filter_section'] > 0 
    ? (int)$_GET['filter_section'] 
    : null;
$filter_semester = isset($_GET['filter_semester']) && is_numeric($_GET['filter_semester']) && $_GET['filter_semester'] > 0 
    ? (int)$_GET['filter_semester'] 
    : null;
$filter_admission_type = isset($_GET['filter_admission_type']) && is_numeric($_GET['filter_admission_type']) && $_GET['filter_admission_type'] > 0 
    ? (int)$_GET['filter_admission_type'] 
    : null;

// Search validation
$search_query = isset($_GET['search']) ? trim($_GET['search']) : '';
$search_query = substr($search_query, 0, 100);
$search_query = preg_replace('/[<>\"\'%_\\\\]/', '', $search_query);
$search_query_for_db = !empty($search_query) ? "%{$search_query}%" : '';

// Pagination
$per_page = 25;
$page = isset($_GET['page']) && is_numeric($_GET['page']) && $_GET['page'] > 0 ? (int)$_GET['page'] : 1;
$offset = ($page - 1) * $per_page;

// ============================================
// BUILD WHERE CLAUSE
// ============================================

$where_parts = [];
$params = [];
$types = '';

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

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

// Program filter
if ($filter_program) {
    $where_parts[] = "s.program_id = ?";
    $types .= 'i';
    $params[] = $filter_program;
}

// Specialization filter
if ($filter_specialization) {
    $where_parts[] = "s.specialization_id = ?";
    $types .= 'i';
    $params[] = $filter_specialization;
}

// Batch filter
if ($filter_batch) {
    $where_parts[] = "s.batch_id = ?";
    $types .= 'i';
    $params[] = $filter_batch;
}

// Section filter
if ($filter_section) {
    $where_parts[] = "s.section_id = ?";
    $types .= 'i';
    $params[] = $filter_section;
}

// Admission type filter
if ($filter_admission_type) {
    $where_parts[] = "s.admission_id = ?";
    $types .= 'i';
    $params[] = $filter_admission_type;
}

// Search filter
if (!empty($search_query_for_db)) {
    $where_parts[] = "(s.student_name LIKE ? OR s.student_name_eng LIKE ? OR s.student_id LIKE ? OR s.phone_number LIKE ?)";
    $types .= 'ssss';
    $params[] = $search_query_for_db;
    $params[] = $search_query_for_db;
    $params[] = $search_query_for_db;
    $params[] = $search_query_for_db;
}

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

// ============================================
// COUNT QUERY
// ============================================

$count_sql = "SELECT COUNT(*) as total FROM students s {$where_clause}";
$count_stmt = $mysqli1->prepare($count_sql);

if (!$count_stmt) {
    error_log("Failed to prepare count query: " . $mysqli1->error);
    die("Database error occurred");
}

if (!empty($params)) {
    $count_stmt->bind_param($types, ...$params);
}

$count_stmt->execute();
$total_students = $count_stmt->get_result()->fetch_assoc()['total'] ?? 0;
$count_stmt->close();
$total_pages = ceil($total_students / $per_page);

// ============================================
// MAIN QUERY - WITH SPECIALIZATION AND ADMISSION TYPE
// ============================================

$main_sql = "
    WITH student_financials AS (
        SELECT 
            r.student_id,
            COALESCE(SUM(CASE WHEN r.is_paid = 1 THEN r.payment_amount ELSE 0 END), 0) as total_paid,
            COALESCE(SUM(CASE WHEN r.is_paid = 0 THEN r.payment_amount ELSE 0 END), 0) as total_unpaid,
            COUNT(CASE WHEN r.is_paid = 1 THEN 1 END) as paid_count,
            COUNT(CASE WHEN r.is_paid = 0 THEN 1 END) as unpaid_count
        FROM registrations r
        WHERE r.deleted_at IS NULL
        GROUP BY r.student_id
    ),
    latest_payments AS (
        SELECT 
            p.student_id,
            b.name as bank_name,
            ROW_NUMBER() OVER (PARTITION BY p.student_id ORDER BY p.created_at DESC) as rn
        FROM payments p
        LEFT JOIN banks b ON p.bank_id = b.id
        WHERE p.deleted_at IS NULL
    )
    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,
        s.specialization_id,
        sp.specialization_name_ar as specialization_name,
        ad.name as admission_type_name,
        COALESCE(sf.total_paid, 0) as total_paid,
        COALESCE(sf.total_unpaid, 0) as total_unpaid,
        COALESCE(sf.paid_count, 0) as paid_count,
        COALESCE(sf.unpaid_count, 0) as unpaid_count,
        lp.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
    LEFT JOIN specializations sp ON sp.specialization_id = s.specialization_id
    LEFT JOIN admissions ad ON ad.id = s.admission_id
    LEFT JOIN student_financials sf ON sf.student_id = s.student_id
    LEFT JOIN latest_payments lp ON lp.student_id = s.student_id AND lp.rn = 1
    {$where_clause}
    ORDER BY s.student_id DESC
    LIMIT ? OFFSET ?
";

// Prepare main statement
$main_stmt = $mysqli1->prepare($main_sql);

if (!$main_stmt) {
    error_log("Failed to prepare main query: " . $mysqli1->error);
    die("Database error occurred");
}

// Build parameters for main query
$main_params = $params;
$main_types = $types;

// Add pagination parameters
$main_params[] = $per_page;
$main_params[] = $offset;
$main_types .= 'ii';

// Bind parameters
if (!empty($main_params)) {
    $main_stmt->bind_param($main_types, ...$main_params);
}

$main_stmt->execute();
$recentAccepted = $main_stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$main_stmt->close();

// ============================================
// KPI QUERY
// ============================================

$kpi_sql = "
    SELECT 
        COUNT(DISTINCT s.student_id) AS total_students,
        COUNT(DISTINCT CASE WHEN r.is_paid = 1 THEN s.student_id END) AS paid_registrations,
        COALESCE(SUM(CASE WHEN r.is_paid = 1 THEN r.payment_amount ELSE 0 END), 0) AS total_paid_registrations,
        COUNT(DISTINCT CASE WHEN r.is_paid = 0 THEN s.student_id END) AS unpaid_registrations,
        COALESCE(SUM(CASE WHEN r.is_paid = 0 THEN r.payment_amount ELSE 0 END), 0) AS total_unpaid_registrations
    FROM students s
    LEFT JOIN registrations r ON r.student_id = s.student_id AND r.deleted_at IS NULL
    {$where_clause}
";

$kpi_stmt = $mysqli1->prepare($kpi_sql);

if (!$kpi_stmt) {
    error_log("Failed to prepare KPI query: " . $mysqli1->error);
    die("Database error occurred");
}

if (!empty($params)) {
    $kpi_stmt->bind_param($types, ...$params);
}

$kpi_stmt->execute();
$kpi = $kpi_stmt->get_result()->fetch_assoc();
$kpi_stmt->close();

// Calculate KPIs
$kpi['total_paid_all'] = (float)($kpi['total_paid_registrations'] ?? 0);
$kpi['total_unpaid_all'] = (float)($kpi['total_unpaid_registrations'] ?? 0);
$kpi['total_receivable'] = $kpi['total_paid_all'] + $kpi['total_unpaid_all'];
$kpi['payment_percentage'] = $kpi['total_receivable'] > 0 
    ? round(($kpi['total_paid_all'] / $kpi['total_receivable']) * 100, 2) 
    : 0;

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

// ============================================
// BANK PAYMENTS QUERY
// ============================================

$bank_sql = "
    SELECT 
        b.id,
        b.name AS bank_name,
        COUNT(p.id) AS payment_count,
        COALESCE(SUM(p.payment_amount), 0) AS total_amount
    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 ?
    LEFT JOIN students s ON p.student_id = s.student_id
    {$where_clause}
    GROUP BY b.id, b.name
    HAVING total_amount > 0
    ORDER BY total_amount DESC
";

$bank_params = [$start_date, $end_date];
$bank_types = 'ss';

// Add WHERE clause parameters
if (!empty($params)) {
    $bank_params = array_merge($bank_params, $params);
    $bank_types .= $types;
}

$bank_stmt = $mysqli1->prepare($bank_sql);

if (!$bank_stmt) {
    error_log("Failed to prepare bank query: " . $mysqli1->error);
    die("Database error occurred");
}

if (!empty($bank_params)) {
    $bank_stmt->bind_param($bank_types, ...$bank_params);
}

$bank_stmt->execute();
$bankPayments = $bank_stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$bank_stmt->close();

// Calculate total bank payments
$totalBankPayments = 0;
foreach ($bankPayments as $bank) {
    $totalBankPayments += $bank['total_amount'];
}

// ============================================
// COLLEGE STATS QUERY
// ============================================

$college_stats_sql = "
    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,
        COALESCE(SUM(CASE WHEN r.is_paid = 1 THEN r.payment_amount ELSE 0 END), 0) as total_paid_registrations,
        COUNT(DISTINCT CASE WHEN r.is_paid = 0 THEN s.student_id END) as unpaid_registrations,
        COALESCE(SUM(CASE WHEN r.is_paid = 0 THEN r.payment_amount ELSE 0 END), 0) as total_unpaid_registrations
    FROM colleges c
    LEFT JOIN students s ON c.id = s.college_id
    LEFT JOIN registrations r ON r.student_id = s.student_id AND r.deleted_at IS NULL
    {$where_clause}
    GROUP BY c.id, c.name
    ORDER BY c.name
";

$college_stmt = $mysqli1->prepare($college_stats_sql);

if (!$college_stmt) {
    error_log("Failed to prepare college stats query: " . $mysqli1->error);
    die("Database error occurred");
}

if (!empty($params)) {
    $college_stmt->bind_param($types, ...$params);
}

$college_stmt->execute();
$byCollege = $college_stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$college_stmt->close();

// Calculate grand totals for colleges
$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'] += (int)($college['total_students'] ?? 0);
    $grand_totals['unpaid_registrations'] += (int)($college['unpaid_registrations'] ?? 0);
    $grand_totals['paid_registrations'] += (int)($college['paid_registrations'] ?? 0);
    $grand_totals['total_paid_registrations'] += (float)($college['total_paid_registrations'] ?? 0);
    $grand_totals['total_unpaid_registrations'] += (float)($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
    );
}

// ============================================
// GET NAMES FOR DISPLAY
// ============================================

$college_name = $program_name = $specialization_name = $batch_name = $section_name = $semester_name = $admission_type_name = '';

if ($filter_college) {
    $stmt = $mysqli1->prepare("SELECT name FROM colleges WHERE id = ?");
    if ($stmt) {
        $stmt->bind_param('i', $filter_college);
        $stmt->execute();
        $result = $stmt->get_result();
        $college_name = $result->fetch_assoc()['name'] ?? '';
        $stmt->close();
    }
}

if ($filter_program) {
    $stmt = $mysqli1->prepare("SELECT name FROM programs WHERE id = ?");
    if ($stmt) {
        $stmt->bind_param('i', $filter_program);
        $stmt->execute();
        $result = $stmt->get_result();
        $program_name = $result->fetch_assoc()['name'] ?? '';
        $stmt->close();
    }
}

if ($filter_specialization) {
    $stmt = $mysqli1->prepare("SELECT specialization_name_ar as name FROM specializations WHERE specialization_id = ?");
    if ($stmt) {
        $stmt->bind_param('i', $filter_specialization);
        $stmt->execute();
        $result = $stmt->get_result();
        $specialization_name = $result->fetch_assoc()['name'] ?? '';
        $stmt->close();
    }
}

if ($filter_batch) {
    $stmt = $mysqli1->prepare("SELECT value FROM batches WHERE id = ?");
    if ($stmt) {
        $stmt->bind_param('i', $filter_batch);
        $stmt->execute();
        $result = $stmt->get_result();
        $batch_name = $result->fetch_assoc()['value'] ?? '';
        $stmt->close();
    }
}

if ($filter_section) {
    $stmt = $mysqli1->prepare("SELECT name FROM sections WHERE id = ?");
    if ($stmt) {
        $stmt->bind_param('i', $filter_section);
        $stmt->execute();
        $result = $stmt->get_result();
        $section_name = $result->fetch_assoc()['name'] ?? '';
        $stmt->close();
    }
}

if ($filter_semester) {
    $stmt = $mysqli1->prepare("SELECT name FROM semesters WHERE id = ?");
    if ($stmt) {
        $stmt->bind_param('i', $filter_semester);
        $stmt->execute();
        $result = $stmt->get_result();
        $semester_name = $result->fetch_assoc()['name'] ?? '';
        $stmt->close();
    }
}

if ($filter_admission_type) {
    $stmt = $mysqli1->prepare("SELECT name FROM admissions WHERE id = ?");
    if ($stmt) {
        $stmt->bind_param('i', $filter_admission_type);
        $stmt->execute();
        $result = $stmt->get_result();
        $admission_type_name = $result->fetch_assoc()['name'] ?? '';
        $stmt->close();
    }
}

// ============================================
// GET DROPDOWN DATA
// ============================================

// Colleges
$colleges_filter_result = null;
$colleges_filter_sql = "SELECT id, name FROM colleges ORDER BY name";
$colleges_filter_stmt = $mysqli1->prepare($colleges_filter_sql);
if ($colleges_filter_stmt) {
    $colleges_filter_stmt->execute();
    $colleges_filter_result = $colleges_filter_stmt->get_result();
}

// Programs - جلب البرامج حسب الكلية المحددة
$filter_programs_result = null;
if ($filter_college) {
    $filter_programs_sql = "
        SELECT DISTINCT p.id, p.name 
        FROM programs p
        INNER JOIN specializations s ON s.program_id = p.id
        WHERE s.college_id = ? AND p.deleted_at IS NULL
        ORDER BY p.name
    ";
    $filter_programs_stmt = $mysqli1->prepare($filter_programs_sql);
    if ($filter_programs_stmt) {
        $filter_programs_stmt->bind_param('i', $filter_college);
        $filter_programs_stmt->execute();
        $filter_programs_result = $filter_programs_stmt->get_result();
    }
} else {
    // إذا لم يتم تحديد كلية، جلب جميع البرامج
    $filter_programs_sql = "SELECT id, name FROM programs WHERE deleted_at IS NULL ORDER BY name";
    $filter_programs_stmt = $mysqli1->prepare($filter_programs_sql);
    if ($filter_programs_stmt) {
        $filter_programs_stmt->execute();
        $filter_programs_result = $filter_programs_stmt->get_result();
    }
}

// Batches
$filter_batches_result = null;
$filter_batches_sql = "SELECT id, value FROM batches WHERE deleted_at IS NULL ORDER BY id";
$filter_batches_stmt = $mysqli1->prepare($filter_batches_sql);
if ($filter_batches_stmt) {
    $filter_batches_stmt->execute();
    $filter_batches_result = $filter_batches_stmt->get_result();
}

// Semesters
$filter_semesters_result = null;
$filter_semesters_sql = "SELECT id, name FROM semesters WHERE deleted_at IS NULL ORDER BY id DESC";
$filter_semesters_stmt = $mysqli1->prepare($filter_semesters_sql);
if ($filter_semesters_stmt) {
    $filter_semesters_stmt->execute();
    $filter_semesters_result = $filter_semesters_stmt->get_result();
}

// Admission Types
$admission_types = [];
$admission_sql = "SELECT id, name FROM admissions ORDER BY name";
$admission_stmt = $mysqli1->prepare($admission_sql);
if ($admission_stmt) {
    $admission_stmt->execute();
    $admission_result = $admission_stmt->get_result();
    while ($row = $admission_result->fetch_assoc()) {
        $admission_types[] = [
            'id' => $row['id'],
            'name' => $row['name']
        ];
    }
    $admission_stmt->close();
}

// ============================================
// HELPER FUNCTIONS
// ============================================

function build_pagination_url($page, $params = []) {
    $current_params = $_GET;
    $current_params['page'] = $page;
    
    // Remove empty values
    foreach ($current_params as $key => $value) {
        if ($value === '' || $value === null) {
            unset($current_params[$key]);
        }
    }
    
    return '?' . http_build_query($current_params);
}

// ============================================
// UI STARTS HERE
// ============================================
include 'header.php';
?>

<!-- Include Cairo Font -->
<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', sans-serif !important;
  }
  
  /* Specific element overrides */
  body, h1, h2, h3, h4, h5, h6, p, a, button, label, input, select, textarea, table, th, td, span, div {
    font-family: 'Cairo', sans-serif !important;
  }
  
  /* Form elements */
  .form-control, .form-control-sm, .form-select, .form-select-sm,
  .btn, .btn-sm, .btn-primary, .btn-secondary, .btn-success, .btn-outline-secondary,
  .table, .table th, .table td,
  .chip, .chip.green, .chip.gray, .chip.red, .chip.blue, .chip.orange, .chip.purple {
    font-family: 'Cairo', sans-serif !important;
  }

  /* Dashboard cards */
  .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">
  <!-- Header -->
  <div class="d-flex align-items-center mb-3">
    <h3 class="mb-0 fw-bold">لوحة التحكم - المحاسب</h3>
    <div class="ms-auto">
      <span class="chip blue">مرحباً، <?= htmlspecialchars($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="#">التقارير المالية</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="<?= htmlspecialchars($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 (!empty($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 (!empty($search_query)): ?>
            <div class="col-md-12 mt-2">
              <span class="badge bg-info">
                <i class="fas fa-search me-1"></i>
                نتائج البحث عن: "<?= htmlspecialchars($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="<?= htmlspecialchars($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="<?= htmlspecialchars($start_date) ?>" required>
              <span class="input-group-text bg-light border-start-0">
                <i class="fas fa-play text-success"></i>
              </span>
            </div>
          </div>
        </div>
        
        <div class="col-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="<?= htmlspecialchars($end_date) ?>" required>
              <span class="input-group-text bg-light border-start-0">
                <i class="fas fa-flag text-danger"></i>
              </span>
            </div>
          </div>
        </div>
        
        <!-- 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
                if ($colleges_filter_result) {
                    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" data-selected="<?= htmlspecialchars($filter_section ?? '') ?>">
                <option value="">جميع الأقسام</option>
                <?php if ($filter_college): ?>
                    <?php
                    $sections_stmt = $mysqli1->prepare("SELECT id, name FROM sections WHERE parent = ? AND deleted_at IS NULL ORDER BY name");
                    if ($sections_stmt) {
                        $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" id="filter_program" data-selected="<?= htmlspecialchars($filter_program ?? '') ?>">
                <option value="">جاري تحميل البرامج...</option>
                <?php if ($filter_programs_result): ?>
                  <?php while ($program = $filter_programs_result->fetch_assoc()): ?>
                    <option value="<?= $program['id'] ?>" <?= (isset($_GET['filter_program']) && $_GET['filter_program'] == $program['id']) ? 'selected' : '' ?>>
                      <?= htmlspecialchars($program['name']) ?>
                    </option>
                  <?php endwhile; ?>
                <?php endif; ?>
              </select>
              <span class="input-group-text bg-light border-start-0">
                <i class="fas fa-book"></i>
              </span>
            </div>
          </div>
        </div>
        
        <!-- Specialization Filter -->
        <div class="col-md-3">
          <div class="form-group">
            <label class="form-label fw-semibold text-muted mb-2">
              <i class="fas fa-microscope me-1"></i>
              التخصص
            </label>
            <div class="input-group input-group-sm">
              <select class="form-control border-end-0" name="filter_specialization" id="filter_specialization" data-selected="<?= htmlspecialchars($filter_specialization ?? '') ?>" disabled>
                <option value="">اختر القسم والبرنامج أولاً</option>
                <?php if ($filter_section && $filter_program): ?>
                    <?php
                    $spec_stmt = $mysqli1->prepare("SELECT specialization_id, specialization_name_ar FROM specializations WHERE section_id = ? AND program_id = ? AND deleted_at IS NULL ORDER BY specialization_name_ar");
                    if ($spec_stmt) {
                        $spec_stmt->bind_param('ii', $filter_section, $filter_program);
                        $spec_stmt->execute();
                        $spec_result = $spec_stmt->get_result();
                        while ($spec = $spec_result->fetch_assoc()) {
                            $selected = (isset($_GET['filter_specialization']) && $_GET['filter_specialization'] == $spec['specialization_id']) ? 'selected' : '';
                            echo "<option value=\"{$spec['specialization_id']}\" $selected>" . htmlspecialchars($spec['specialization_name_ar']) . "</option>";
                        }
                        $spec_stmt->close();
                    }
                    ?>
                <?php endif; ?>
              </select>
              <span class="input-group-text bg-light border-start-0">
                <i class="fas fa-flask"></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 ($filter_batches_result) {
                    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>";
                    }
                }
                ?>
              </select>
              <span class="input-group-text bg-light border-start-0">
                <i class="fas fa-calendar-alt"></i>
              </span>
            </div>
          </div>
        </div>
        
        <!-- Admission Type Filter -->
        <div class="col-md-3">
          <div class="form-group">
            <label class="form-label fw-semibold text-muted mb-2">
              <i class="fas fa-door-open me-1"></i>
              نوع القبول
            </label>
            <div class="input-group input-group-sm">
              <select class="form-control border-end-0" name="filter_admission_type">
                <option value="">جميع أنواع القبول</option>
                <?php
                foreach ($admission_types as $type) {
                    $selected = (isset($_GET['filter_admission_type']) && $_GET['filter_admission_type'] == $type['id']) ? 'selected' : '';
                    echo "<option value=\"{$type['id']}\" $selected>" . htmlspecialchars($type['name']) . "</option>";
                }
                ?>
              </select>
              <span class="input-group-text bg-light border-start-0">
                <i class="fas fa-sign-in-alt"></i>
              </span>
            </div>
          </div>
        </div>
      </div> <!-- نهاية الصف الثاني -->
      
      <!-- الصف الثالث: الفصل الدراسي وأزرار التحكم -->
      <div class="row mt-2">
        <!-- 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 ($filter_semesters_result) {
                    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>";
                    }
                }
                ?>
              </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): ?>
          <span class="chip purple ms-1">الفصل: <?= htmlspecialchars($semester_name) ?></span>
        <?php endif; ?>
        <?php if (!empty($search_query)): ?>
          <span class="chip orange ms-1">بحث: <?= htmlspecialchars($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['total_amount'] / $totalBankPayments) * 100 : 0;
          ?>
            <tr>
              <td><?= htmlspecialchars($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['total_amount']) ?> 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): ?>
          <span class="chip purple ms-1">الفصل: <?= htmlspecialchars($semester_name) ?></span>
        <?php endif; ?>
        <?php if (!empty($search_query)): ?>
          <span class="chip orange ms-1">بحث: <?= htmlspecialchars($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>
            <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_paid + $college_unpaid) > 0 
                ? round(($college_paid / ($college_paid + $college_unpaid)) * 100, 2) 
                : 0;
          ?>
            <tr>
              <td><?= htmlspecialchars($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 Specialization and Admission Type -->
  <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): ?>
          <span class="chip purple ms-1">الفصل: <?= htmlspecialchars($semester_name) ?></span>
        <?php endif; ?>
        <?php if (!empty($search_query)): ?>
          <span class="chip orange ms-1">بحث: <?= htmlspecialchars($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
        $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>
            <th>المستحق</th>
            <th>نسبة الدفع</th>
          </tr>
        </thead>
        <tbody>
          <?php if (empty($recentAccepted)): ?>
            <tr><td colspan="14" class="text-center text-muted py-2">— لا توجد نتائج —</td></tr>
          <?php else: 
            $counter = $offset + 1;
            foreach ($recentAccepted as $r): 
              $total_paid = (float)($r['total_paid'] ?? 0);
              $total_unpaid = (float)($r['total_unpaid'] ?? 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"><?= htmlspecialchars($r['student_name'] ?: $r['student_name_eng'] ?: '—') ?></td>
                <td style="font-size:0.8rem"><code><?= htmlspecialchars($r['student_id']) ?></code></td>
                <td style="font-size:0.8rem"><?= htmlspecialchars($r['college_name'] ?? '—') ?></td>
                <td style="font-size:0.8rem"><?= htmlspecialchars($r['program_name'] ?? '—') ?></td>
                <td style="font-size:0.8rem"><?= htmlspecialchars($r['specialization_name'] ?? '—') ?></td>
                <td style="font-size:0.8rem">
                  <?php if (!empty($r['admission_type_name'])): ?>
                    <span class="chip purple"><?= htmlspecialchars($r['admission_type_name']) ?></span>
                  <?php else: ?>
                    <span class="chip gray">—</span>
                  <?php endif; ?>
                </td>
                <td style="font-size:0.8rem"><?= htmlspecialchars($r['batch_name'] ?: $r['batch_id']) ?></td>
                <td style="font-size:0.8rem"><?= htmlspecialchars($r['section_name'] ?? '—') ?></td>
                <td>
                  <?php if ($bank_name !== '—'): ?>
                    <span class="chip blue"><?= htmlspecialchars($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 and programs based on college
$(document).ready(function() {
  // Store initial selected values
  const initialCollege = $('#filter_college').val();
  const initialProgram = $('#filter_program').data('selected');
  const initialSection = $('#filter_section').data('selected');
  const initialSpecialization = $('#filter_specialization').data('selected');
  
  // Load all programs on page load
  loadPrograms(initialCollege, initialProgram);
  
  $('#filter_college').change(function() {
    const collegeId = $(this).val();
    const sectionSelect = $('#filter_section');
    const programSelect = $('#filter_program');
    const specSelect = $('#filter_specialization');
    
    // Clear and disable dependent selects
    sectionSelect.find('option').not(':first').remove();
    specSelect.find('option').not(':first').remove();
    specSelect.prop('disabled', true);
    specSelect.html('<option value="">اختر القسم والبرنامج أولاً</option>');
    
    // Clear program select and show loading
    programSelect.html('<option value="">جاري تحميل البرامج...</option>');
    programSelect.prop('disabled', true);
    
    if (collegeId && collegeId !== '') {
      // Load sections
      loadSections(collegeId, initialSection);
      
      // Load programs
      loadPrograms(collegeId, initialProgram);
      
    } else {
      // If no college selected, load all programs
      sectionSelect.html('<option value="">جميع الأقسام</option>');
      sectionSelect.prop('disabled', false);
      loadPrograms(0, initialProgram);
    }
  });
  
  // Load sections function
  function loadSections(collegeId, selectedSection) {
    $.ajax({
      url: 'ajax/get_sections.php',
      type: 'GET',
      data: { college_id: collegeId },
      dataType: 'json',
      success: function(sections) {
        const sectionSelect = $('#filter_section');
        sectionSelect.html('<option value="">جميع الأقسام</option>');
        
        if (sections && sections.length > 0) {
          $.each(sections, function(index, section) {
            const option = $('<option>', {
              value: section.id,
              text: section.name
            });
            
            // Set selected if matches initial value
            if (selectedSection && section.id == selectedSection) {
              option.attr('selected', 'selected');
            }
            
            sectionSelect.append(option);
          });
        }
        
        sectionSelect.prop('disabled', false);
        
        // Trigger change to load specializations if both section and program are selected
        if ($('#filter_program').val() && $('#filter_section').val()) {
          $('#filter_section, #filter_program').trigger('change');
        }
      },
      error: function() {
        console.error('Error loading sections');
        $('#filter_section').html('<option value="">خطأ في تحميل الأقسام</option>');
        $('#filter_section').prop('disabled', false);
      }
    });
  }
  
  // Load programs function
  function loadPrograms(collegeId, selectedProgram) {
    $.ajax({
      url: 'ajax/get_programs.php',
      type: 'GET',
      data: { college_id: collegeId },
      dataType: 'json',
      success: function(programs) {
        const programSelect = $('#filter_program');
        programSelect.html('<option value="">جميع البرامج</option>');
        
        if (programs && programs.length > 0) {
          $.each(programs, function(index, program) {
            const option = $('<option>', {
              value: program.id,
              text: program.name
            });
            
            // Set selected if matches initial value
            if (selectedProgram && program.id == selectedProgram) {
              option.attr('selected', 'selected');
            }
            
            programSelect.append(option);
          });
        }
        
        programSelect.prop('disabled', false);
        
        // Trigger change to load specializations if both section and program are selected
        if ($('#filter_section').val() && $('#filter_program').val()) {
          $('#filter_section, #filter_program').trigger('change');
        }
      },
      error: function() {
        console.error('Error loading programs');
        $('#filter_program').html('<option value="">خطأ في تحميل البرامج</option>');
        $('#filter_program').prop('disabled', false);
      }
    });
  }
  
  // AJAX for loading specializations based on section AND program
  $('#filter_section, #filter_program').change(function() {
    const sectionId = $('#filter_section').val();
    const programId = $('#filter_program').val();
    const specSelect = $('#filter_specialization');
    const initialSpecialization = $('#filter_specialization').data('selected');
    
    // Clear current options except the first one
    specSelect.find('option').not(':first').remove();
    
    // Only load if both section and program are selected
    if (sectionId && sectionId !== '' && programId && programId !== '') {
      // Show loading
      specSelect.prop('disabled', true);
      specSelect.html('<option value="">جاري التحميل...</option>');
      
      $.ajax({
        url: 'ajax/get_specializations.php',
        type: 'GET',
        data: { 
          section_id: sectionId,
          program_id: programId 
        },
        dataType: 'json',
        success: function(specializations) {
          // Clear and add new options
          specSelect.html('<option value="">جميع التخصصات</option>');
          
          if (specializations && specializations.length > 0) {
            $.each(specializations, function(index, spec) {
              const option = $('<option>', {
                value: spec.specialization_id,
                text: spec.specialization_name_ar
              });
              
              // Set selected if matches initial value
              if (initialSpecialization && spec.specialization_id == initialSpecialization) {
                option.attr('selected', 'selected');
              }
              
              specSelect.append(option);
            });
          } else {
            specSelect.append($('<option>', {
              value: '',
              text: 'لا توجد تخصصات',
              disabled: true
            }));
          }
          
          specSelect.prop('disabled', false);
        },
        error: function() {
          console.error('Error loading specializations');
          specSelect.html('<option value="">خطأ في تحميل التخصصات</option>');
          specSelect.prop('disabled', false);
        }
      });
    } else {
      // If either section or program not selected, disable specialization select
      specSelect.prop('disabled', true);
      specSelect.html('<option value="">اختر القسم والبرنامج أولاً</option>');
    }
  });
  
  // Show loading overlay on form submit
  $('#filterForm').submit(function() {
    $('#loadingOverlay').show();
  });
});

function printStudentsTable() {
  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: 10px;
        }
        .table th {
          background-color: #3E6B54;
          color: white;
          padding: 6px;
          text-align: right;
          border: 1px solid #dee2e6;
          font-weight: bold;
        }
        .table td {
          padding: 5px;
          border: 1px solid #dee2e6;
          text-align: right;
        }
        .table tbody tr:nth-child(even) {
          background-color: #f8f9fa;
        }
        .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: 9px;
          }
        }
      </style>
    </head>
    <body>
      <div class="header">
        <h1>قائمة الطلاب - المحاسب</h1>
        <div class="info">
          <div>اسم المحاسب: <?= htmlspecialchars($committeeName ?: 'المحاسب') ?></div>
          <div>تاريخ الطباعة: <?= date('d/m/Y H:i') ?></div>
          <?php if ($filter_college): ?>
          <div>الكلية: <?= htmlspecialchars($college_name) ?></div>
          <?php endif; ?>
          <?php if ($filter_program): ?>
          <div>البرنامج: <?= htmlspecialchars($program_name) ?></div>
          <?php endif; ?>
          <?php if ($filter_section): ?>
          <div>القسم: <?= htmlspecialchars($section_name) ?></div>
          <?php endif; ?>
          <?php if ($filter_specialization): ?>
          <div>التخصص: <?= htmlspecialchars($specialization_name) ?></div>
          <?php endif; ?>
          <?php if ($filter_admission_type && $admission_type_name): ?>
          <div>نوع القبول: <?= htmlspecialchars($admission_type_name) ?></div>
          <?php endif; ?>
          <?php if ($filter_semester): ?>
          <div>الفصل الدراسي: <?= htmlspecialchars($semester_name) ?></div>
          <?php endif; ?>
          <?php if (!empty($search_query)): ?>
          <div>بحث: <?= htmlspecialchars($search_query) ?></div>
          <?php endif; ?>
          <div>إجمالي الطلاب: <?= number_format($total_students) ?></div>
        </div>
      </div>

      ${tableContent}

      <div class="footer">
        <div>تم إنشاء التقرير بواسطة: <?= htmlspecialchars($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 filterSpecialization = document.querySelector('select[name="filter_specialization"]').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 filterAdmissionType = document.querySelector('select[name="filter_admission_type"]').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_to_excel.php`;
  
  // Add all filters
  const params = new URLSearchParams();
  
  if (filterCollege) params.append('filter_college', filterCollege);
  if (filterProgram) params.append('filter_program', filterProgram);
  if (filterSpecialization) params.append('filter_specialization', filterSpecialization);
  if (filterBatch) params.append('filter_batch', filterBatch);
  if (filterSection) params.append('filter_section', filterSection);
  if (filterSemester) params.append('filter_semester', filterSemester);
  if (filterAdmissionType) params.append('filter_admission_type', filterAdmissionType);
  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'; ?>
