<?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);

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

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

// Get filter values
$filter_college = isset($_GET['filter_college']) && !empty($_GET['filter_college']) ? (int)$_GET['filter_college'] : null;
$filter_program = isset($_GET['filter_program']) && !empty($_GET['filter_program']) ? (int)$_GET['filter_program'] : null;
$filter_batch = isset($_GET['filter_batch']) && !empty($_GET['filter_batch']) ? (int)$_GET['filter_batch'] : null;

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

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

/* ---------- Top KPIs - Financial Focus ---------- */
$sqlKpi = "
  SELECT
    COUNT(*) AS total_students,
    
    -- Financial KPIs
    SUM(CASE WHEN r.id IS NOT NULL AND r.is_paid = 1 THEN 1 ELSE 0 END) AS paid_registrations,
    SUM(CASE WHEN r.id IS NOT NULL AND r.is_paid = 0 THEN 1 ELSE 0 END) AS unpaid_registrations,
    SUM(CASE WHEN of.id IS NOT NULL AND of.is_paid = 1 THEN 1 ELSE 0 END) AS paid_other_fees,
    SUM(CASE WHEN of.id IS NOT NULL AND of.is_paid = 0 THEN 1 ELSE 0 END) AS unpaid_other_fees,
    
    -- Total amounts
    COALESCE(SUM(CASE WHEN r.is_paid = 1 THEN r.payment_amount ELSE 0 END), 0) AS total_paid_registrations,
    COALESCE(SUM(CASE WHEN r.is_paid = 0 THEN r.payment_amount ELSE 0 END), 0) AS total_unpaid_registrations,
    COALESCE(SUM(CASE WHEN of.is_paid = 1 THEN of.amount ELSE 0 END), 0) AS total_paid_other_fees,
    COALESCE(SUM(CASE WHEN of.is_paid = 0 THEN of.amount ELSE 0 END), 0) AS total_unpaid_other_fees
    
  FROM students s
  LEFT JOIN registrations r ON r.student_id = s.student_id AND r.deleted_at IS NULL
  LEFT JOIN other_fees of ON of.phone_number = s.phone_number AND of.deleted_at IS NULL
  $where_clause
";

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

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

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

/* ---------- Payments by Bank ---------- */
$sqlBankPayments = "
  SELECT 
    b.id,
    b.name AS bank_name,
    COUNT(p.id) AS payment_count,
    COALESCE(SUM(p.payment_amount), 0) AS total_amount,
    COUNT(op.id) AS other_payment_count,
    COALESCE(SUM(op.amount), 0) AS other_total_amount,
    (COUNT(p.id) + COUNT(op.id)) AS total_count,
    (COALESCE(SUM(p.payment_amount), 0) + COALESCE(SUM(op.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 ?
  LEFT JOIN other_payments op ON op.bank_id = b.id 
    AND op.deleted_at IS NULL
    AND DATE(op.created_at) BETWEEN ? AND ?
  GROUP BY b.id, b.name
  HAVING grand_total > 0
  ORDER BY grand_total DESC
";

$st = $mysqli1->prepare($sqlBankPayments);
$st->bind_param("ssss", $start_date, $end_date, $start_date, $end_date);
$st->execute();
$bankPayments = $st->get_result()->fetch_all(MYSQLI_ASSOC);
$st->close();

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

/* ---------- Bank Transaction Details ---------- */
$bankTransactions = [];
$selectedBankName = '';
$selected_bank_id = $_GET['bank_id'] ?? null;

if ($selected_bank_id) {
    // Get bank name
    $bankStmt = $mysqli1->prepare("SELECT name FROM banks WHERE id = ?");
    $bankStmt->bind_param("i", $selected_bank_id);
    $bankStmt->execute();
    $bankResult = $bankStmt->get_result()->fetch_assoc();
    $selectedBankName = $bankResult['name'] ?? '';
    $bankStmt->close();

    // Get registration payments for selected bank
    $sqlBankDetails = "
        SELECT 
            'registration' AS payment_type,
            p.id,
            p.student_id,
            s.student_name,
            s.student_name_eng,
            p.payment_amount AS amount,
            p.payment_reference,
            p.transaction_ref,
            p.created_at,
            ft.name AS fee_type,
            sem.name AS semester_name,
            NULL AS other_fee_reference
        FROM payments p
        LEFT JOIN students s ON p.student_id = s.student_id
        LEFT JOIN fee_types ft ON p.fee_type_id = ft.id
        LEFT JOIN semesters sem ON p.semester_id = sem.id
        WHERE p.bank_id = ? 
        AND p.deleted_at IS NULL
        AND DATE(p.created_at) BETWEEN ? AND ?
        
        UNION ALL
        
        SELECT 
            'other_payment' AS payment_type,
            op.id,
            NULL AS student_id,
            NULL AS student_name,
            NULL AS student_name_eng,
            op.amount,
            op.reference AS payment_reference,
            op.transaction_ref,
            op.created_at,
            ft.name AS fee_type,
            NULL AS semester_name,
            of.reference AS other_fee_reference
        FROM other_payments op
        LEFT JOIN fee_types ft ON op.fee_type_id = ft.id
        LEFT JOIN other_fees of ON op.other_fee_id = of.id
        WHERE op.bank_id = ? 
        AND op.deleted_at IS NULL
        AND DATE(op.created_at) BETWEEN ? AND ?
        
        ORDER BY created_at DESC
    ";
    
    $st = $mysqli1->prepare($sqlBankDetails);
    $st->bind_param("isssis", $selected_bank_id, $start_date, $end_date, $selected_bank_id, $start_date, $end_date);
    $st->execute();
    $bankTransactions = $st->get_result()->fetch_all(MYSQLI_ASSOC);
    $st->close();
}

/* ---------- 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
  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 and batch names for display
$college_name = '';
$program_name = '';
$batch_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'] ?? '';
}

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

<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;
  }
  
  .chart-legend {
    font-size: 0.7rem;
  }
  
  .legend-item {
    padding: 3px 6px;
    border-radius: 4px;
    transition: background-color 0.2s;
  }
  
  .legend-item:hover {
    background-color: #f8f9fa;
  }
  
  .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;
  }
  
  .chart-container {
    height: 160px;
    position: relative;
  }
  
  .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;
  }
  
  .form-control-sm {
    font-size: 0.8rem;
  }
  
  .bank-progress {
    height: 8px;
    margin-top: 4px;
  }
  
  .transaction-row:hover {
    background-color: #f8f9fa;
  }
  
  .back-button {
    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;
  }
  
  /* Filter Icon */
  .filter-icon {
    width: 40px;
    height: 40px;
    background: linear-gradient(135deg, #3498db, #2980b9);
    color: white;
    border-radius: 10px;
    display: flex;
    align-items: center;
    justify-content: center;
    font-size: 1.2rem;
  }
  
  /* Professional Summary Card */
  .professional-summary-card {
    background: #ffffff;
    border: 1px solid #e0e6ef;
    border-radius: 12px;
    overflow: hidden;
    box-shadow: 0 2px 8px rgba(0,0,0,0.06);
    height: 100%;
    direction: rtl;
  }
  
  .summary-section {
    padding: 20px;
    border-bottom: 1px solid #f1f5f9;
  }
  
  .section-header {
    display: flex;
    align-items: center;
    margin-bottom: 16px;
    padding-bottom: 12px;
    border-bottom: 1px solid #f1f5f9;
  }
  
  .section-title {
    font-size: 0.9rem;
    font-weight: 700;
    color: #1e293b;
  }
  
  .section-header i {
    color: #3b82f6;
    font-size: 0.9rem;
  }
  
  .criteria-item {
    display: flex;
    justify-content: space-between;
    align-items: center;
    margin-bottom: 12px;
    padding: 8px 0;
  }
  
  .criteria-item:last-child {
    margin-bottom: 0;
  }
  
  .criteria-label {
    font-size: 0.8rem;
    font-weight: 600;
    color: #64748b;
    white-space: nowrap;
  }
  
  .criteria-value {
    font-size: 0.8rem;
    font-weight: 600;
    color: #1e293b;
  }
  
  .value-tag {
    display: inline-flex;
    align-items: center;
    background: #f1f5f9;
    color: #475569;
    padding: 4px 10px;
    border-radius: 6px;
    font-size: 0.75rem;
    font-weight: 600;
    border: 1px solid #e2e8f0;
  }
  
  .value-tag.all {
    background: #dbeafe;
    color: #1d4ed8;
    border-color: #bfdbfe;
  }
  
  .value-tag i {
    font-size: 0.7rem;
    margin-right: 0;
    margin-left: 6px;
  }
  
  /* Summary Stats */
  .summary-stats {
    padding: 16px 20px;
    background: #f8fafc;
  }
  
  .stat-item {
    display: flex;
    align-items: center;
    justify-content: space-between;
  }
  
  .stat-icon {
    width: 40px;
    height: 40px;
    background: #3b82f6;
    border-radius: 10px;
    display: flex;
    align-items: center;
    justify-content: center;
    color: white;
    font-size: 1rem;
  }
  
  .stat-info {
    text-align: right;
  }
  
  .stat-value {
    font-size: 1.2rem;
    font-weight: 800;
    color: #1e293b;
    line-height: 1;
  }
  
  .stat-label {
    font-size: 0.75rem;
    color: #64748b;
    font-weight: 600;
    margin-top: 2px;
  }
  
  /* RTL specific adjustments */
  .ms-1 {
    margin-left: 0.25rem !important;
    margin-right: 0 !important;
  }
  
  .ms-2 {
    margin-left: 0.5rem !important;
    margin-right: 0 !important;
  }
  
  /* Responsive adjustments */
  @media (max-width: 768px) {
    .professional-summary-card {
      margin-bottom: 20px;
    }
    
    .criteria-item {
      flex-direction: column;
      gap: 4px;
      align-items: flex-start;
    }
    
    .criteria-value {
      text-align: right;
      width: 100%;
    }
  }
  
  /* Filter Summary Card */
  .filter-summary-card {
    background: #f8f9fa;
    border-radius: 0.75rem;
    padding: 1rem;
    border: 1px solid #e9ecef;
    height: 100%;
  }
  
  .summary-header {
    display: flex;
    align-items: center;
    margin-bottom: 1rem;
    padding-bottom: 0.5rem;
    border-bottom: 1px solid #dee2e6;
    color: #3E6B54;
  }
  
  .summary-item {
    display: flex;
    justify-content: space-between;
    align-items: center;
    margin-bottom: 0.5rem;
    padding: 0.25rem 0;
  }
  
  .summary-label {
    font-size: 0.85rem;
    color: #6c757d;
  }
  
  .summary-value .badge {
    font-size: 0.75rem;
    padding: 0.25rem 0.5rem;
  }
  
  /* Print Styles */
  @media print {
    body * {
      visibility: hidden;
    }
    .dash-card, .dash-card * {
      visibility: visible;
    }
    .dash-card {
      position: absolute;
      left: 0;
      top: 0;
      width: 100%;
      border: none;
      box-shadow: none;
      padding: 0;
    }
    .btn, .chip, .back-button {
      display: none !important;
    }
    .table {
      font-size: 12px;
    }
    .table th, .table td {
      padding: 4px;
    }
  }
</style>

<div class="container py-3">
  <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>
 
<!-- Date Range Filter -->
<div class="date-filter-card" dir="rtl">
  <div class="row align-items-center">
    <!-- Report Settings Card -->
    <div class="col-md-4">
      <div class="professional-summary-card">
        <div class="summary-section">
          <div class="section-header">
            <i class="fas fa-cog ms-1"></i>
            <span class="section-title">إعدادات التقرير</span>
          </div>
          <div class="section-content">
            <div class="criteria-item">
              <span class="criteria-label">الفترة الزمنية:</span>
              <span class="criteria-value"><?= date('d/m/Y', strtotime($start_date)) ?> - <?= date('d/m/Y', strtotime($end_date)) ?></span>
            </div>
            
            <?php if ($filter_college): ?>
              <div class="criteria-item">
                <span class="criteria-label">الكلية:</span>
                <span class="criteria-value">
                  <span class="value-tag">
                    <?= e($college_name) ?>
                  </span>
                </span>
              </div>
            <?php else: ?>
              <div class="criteria-item">
                <span class="criteria-label">الكلية:</span>
                <span class="criteria-value">
                  <span class="value-tag all">جميع الكليات</span>
                </span>
              </div>
            <?php endif; ?>
            
            <?php if ($filter_program && $programs_table_exists): ?>
              <div class="criteria-item">
                <span class="criteria-label">البرنامج:</span>
                <span class="criteria-value">
                  <span class="value-tag">
                    <?= e($program_name) ?>
                  </span>
                </span>
              </div>
            <?php else: ?>
              <div class="criteria-item">
                <span class="criteria-label">البرنامج:</span>
                <span class="criteria-value">
                  <span class="value-tag all">جميع البرامج</span>
                </span>
              </div>
            <?php endif; ?>
            
            <?php if ($filter_batch && $batches_table_exists): ?>
              <div class="criteria-item">
                <span class="criteria-label">الدفعة:</span>
                <span class="criteria-value">
                  <span class="value-tag">
                    <?= e($batch_name) ?>
                  </span>
                </span>
              </div>
            <?php else: ?>
              <div class="criteria-item">
                <span class="criteria-label">الدفعة:</span>
                <span class="criteria-value">
                  <span class="value-tag all">جميع الدفعات</span>
                </span>
              </div>
            <?php endif; ?>
          </div>
        </div>
        
        <div class="summary-stats">
          <div class="stat-item">
            <div class="stat-info">
              <div class="stat-value"><?= number_format($kpi['total_students'] ?? 0) ?></div>
              <div class="stat-label">إجمالي الطلاب</div>
            </div>
            <div class="stat-icon">
              <i class="fas fa-database"></i>
            </div>
          </div>
        </div>
      </div>
    </div>
   
    <!-- Filters Section -->
    <div class="col-md-8">
      <div class="d-flex align-items-center mb-3">
        <div class="filter-icon me-2">
          <i class="fas fa-filter"></i>
        </div>
        <h6 class="fw-bold mb-0 text-dark">تقارير الفترة الزمنية</h6>
        <div class="ms-3">
          <span class="badge bg-light text-dark border">
            نظام التصفية
          </span>
        </div>
      </div>
      
      <form method="get" class="row g-3 align-items-end">
        <input type="hidden" name="bank_id" value="<?= e($selected_bank_id ?? '') ?>">
        <input type="hidden" name="page" value="1">
        
        <!-- College Filter -->
        <div class="col-lg-4 col-md-6">
          <div class="form-group">
            <label class="form-label fw-semibold text-muted mb-2">
              <i class="fas fa-university me-1"></i>
              الكلية
            </label>
            <div class="input-group input-group-sm">
              <select class="form-control border-end-0" name="filter_college">
                <option value="">جميع الكليات</option>
                <?php
                $colleges_filter_sql = "SELECT id, name FROM colleges ORDER BY name";
                $colleges_filter_result = $mysqli1->query($colleges_filter_sql);
                while ($college = $colleges_filter_result->fetch_assoc()) {
                    $selected = (isset($_GET['filter_college']) && $_GET['filter_college'] == $college['id']) ? 'selected' : '';
                    echo "<option value=\"{$college['id']}\" $selected>" . htmlspecialchars($college['name']) . "</option>";
                }
                ?>
              </select>
              <span class="input-group-text bg-light border-start-0">
                <i class="fas fa-building"></i>
              </span>
            </div>
          </div>
        </div>
        
        <!-- Program Filter -->
        <div class="col-lg-4 col-md-6">
          <div class="form-group">
            <label class="form-label fw-semibold text-muted mb-2">
              <i class="fas fa-graduation-cap me-1"></i>
              البرنامج
            </label>
            <div class="input-group input-group-sm">
              <select class="form-control border-end-0" name="filter_program">
                <option value="">جميع البرامج</option>
                <?php if ($programs_table_exists): ?>
                    <?php
                    $filter_programs_sql = "SELECT id, name FROM programs WHERE deleted_at IS NULL ORDER BY name";
                    $filter_programs_result = $mysqli1->query($filter_programs_sql);
                    while ($program = $filter_programs_result->fetch_assoc()) {
                        $selected = (isset($_GET['filter_program']) && $_GET['filter_program'] == $program['id']) ? 'selected' : '';
                        echo "<option value=\"{$program['id']}\" $selected>" . htmlspecialchars($program['name']) . "</option>";
                    }
                    ?>
                <?php endif; ?>
              </select>
              <span class="input-group-text bg-light border-start-0">
                <i class="fas fa-book"></i>
              </span>
            </div>
          </div>
        </div>
        
        <!-- Batch Filter -->
        <div class="col-lg-4 col-md-6">
          <div class="form-group">
            <label class="form-label fw-semibold text-muted mb-2">
              <i class="fas fa-users me-1"></i>
              الدفعة
            </label>
            <div class="input-group input-group-sm">
              <select class="form-control border-end-0" name="filter_batch">
                <option value="">جميع الدفعات</option>
                <?php if ($batches_table_exists): ?>
                    <?php
                    $filter_batches_sql = "SELECT id, value FROM batches WHERE deleted_at IS NULL ORDER BY id";
                    $filter_batches_result = $mysqli1->query($filter_batches_sql);
                    while ($batch = $filter_batches_result->fetch_assoc()) {
                        $selected = (isset($_GET['filter_batch']) && $_GET['filter_batch'] == $batch['id']) ? 'selected' : '';
                        echo "<option value=\"{$batch['id']}\" $selected>" . htmlspecialchars($batch['value']) . "</option>";
                    }
                    ?>
                <?php endif; ?>
              </select>
              <span class="input-group-text bg-light border-start-0">
                <i class="fas fa-calendar-alt"></i>
              </span>
            </div>
          </div>
        </div>
        
        <!-- Date Filters -->
        <div class="col-lg-4 col-md-6">
          <div class="form-group">
            <label class="form-label fw-semibold text-muted mb-2">
              <i class="fas fa-calendar-day me-1"></i>
              من تاريخ
            </label>
            <div class="input-group input-group-sm">
              <input type="date" class="form-control border-end-0" name="start_date" value="<?= e($start_date) ?>" required>
              <span class="input-group-text bg-light border-start-0">
                <i class="fas fa-play text-success"></i>
              </span>
            </div>
          </div>
        </div>
        
        <div class="col-lg-4 col-md-6">
          <div class="form-group">
            <label class="form-label fw-semibold text-muted mb-2">
              <i class="fas fa-calendar-day me-1"></i>
              إلى تاريخ
            </label>
            <div class="input-group input-group-sm">
              <input type="date" class="form-control border-end-0" name="end_date" value="<?= e($end_date) ?>" required>
              <span class="input-group-text bg-light border-start-0">
                <i class="fas fa-flag text-danger"></i>
              </span>
            </div>
          </div>
        </div>
        
        <!-- Action Buttons -->
        <div class="col-lg-4 col-md-6">
          <div class="d-flex gap-2 pt-4">
            <button type="submit" class="btn btn-primary btn-sm px-3">
              <i class="fas fa-chart-bar me-1"></i>
              عرض التقرير
            </button>
            <a href="?" class="btn btn-outline-secondary btn-sm px-3">
              <i class="fas fa-sync-alt me-1"></i>
              هذا الشهر
            </a>
            <button type="button" class="btn btn-success btn-sm px-3" onclick="generateReport()">
              <i class="fas fa-file-export me-1"></i>
              تصدير التقرير
            </button>
          </div>
        </div>
      </form>
      
      <!-- Filter Summary -->
      <div class="mt-3">
        <div class="filter-summary-card">
          <div class="summary-header">
            <i class="fas fa-info-circle me-2"></i>
            <span class="fw-semibold">ملخص التصفية</span>
          </div>
          <div class="summary-content">
            <?php
            $hasActiveFilters = false;
            
            // College filter summary
            if ($filter_college): 
                $hasActiveFilters = true;
            ?>
                <div class="summary-item">
                    <span class="summary-label">الكلية:</span>
                    <span class="summary-value">
                        <span class="badge bg-primary bg-opacity-10 text-primary">
                            <?= htmlspecialchars($college_name) ?>
                        </span>
                    </span>
                </div>
            <?php endif; ?>
            
            <?php if ($filter_program && $programs_table_exists): 
                $hasActiveFilters = true;
            ?>
                <div class="summary-item">
                    <span class="summary-label">البرنامج:</span>
                    <span class="summary-value">
                        <span class="badge bg-success bg-opacity-10 text-success">
                            <?= htmlspecialchars($program_name) ?>
                        </span>
                    </span>
                </div>
            <?php endif; ?>
            
            <?php if ($filter_batch && $batches_table_exists): 
                $hasActiveFilters = true;
            ?>
                <div class="summary-item">
                    <span class="summary-label">الدفعة:</span>
                    <span class="summary-value">
                        <span class="badge bg-info bg-opacity-10 text-info">
                            <?= htmlspecialchars($batch_name) ?>
                        </span>
                    </span>
                </div>
            <?php endif; ?>
            
            <?php if (!$hasActiveFilters): ?>
                <div class="summary-item">
                    <span class="summary-label">الحالة:</span>
                    <span class="summary-value">
                        <span class="badge bg-secondary bg-opacity-10 text-secondary">
                            بدون تصفية
                        </span>
                    </span>
                </div>
            <?php endif; ?>
          </div>
        </div>
      </div>
    </div>
  </div>
</div>

<!-- Financial Overview Cards -->
<div class="row mb-3">
  <div class="col-md-3">
    <div class="financial-card">
      <div class="financial-stat">
        <div class="number"><?= number_format($kpi['total_paid_all']) ?> SDG</div>
        <div class="label">إجمالي المدفوع</div>
      </div>
    </div>
  </div>
  <div class="col-md-3">
    <div class="financial-card" style="background: linear-gradient(135deg, #f093fb 0%, #f5576c 100%);">
      <div class="financial-stat">
        <div class="number"><?= number_format($kpi['total_unpaid_all']) ?> SDG</div>
        <div class="label">إجمالي المستحق</div>
      </div>
    </div>
  </div>
  <div class="col-md-3">
    <div class="financial-card" style="background: linear-gradient(135deg, #4facfe 0%, #00f2fe 100%);">
      <div class="financial-stat">
        <div class="number"><?= number_format($kpi['total_receivable']) ?> SDG</div>
        <div class="label">إجمالي المتحصلات</div>
      </div>
    </div>
  </div>
  <div class="col-md-3">
    <div class="financial-card" style="background: linear-gradient(135deg, #43e97b 0%, #38f9d7 100%);">
      <div class="financial-stat">
        <div class="number"><?= number_format($totalBankPayments) ?> SDG</div>
        <div class="label">المدفوعات في الفترة</div>
      </div>
    </div>
  </div>
</div>

<!-- Payment Status Distribution -->
<div class="row mb-3">
  <div class="col-md-6">
    <div class="dash-card">
      <h6 class="fw-bold mb-2">حالة التسجيلات</h6>
      <div class="chart-container">
        <canvas id="registrationsChart"></canvas>
      </div>
    </div>
  </div>
  <div class="col-md-6">
    <div class="dash-card">
      <h6 class="fw-bold mb-2">حالة الرسوم الأخرى</h6>
      <div class="chart-container">
        <canvas id="otherFeesChart"></canvas>
      </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>
    </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>
          <th class="text-end">الإجراءات</th>
        </tr>
      </thead>
      <tbody>
        <?php if (empty($bankPayments)): ?>
          <tr>
            <td colspan="8" 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"><?= number_format($bank['total_amount']) ?> SDG</td>
            <td class="text-end"><?= number_format($bank['other_payment_count']) ?></td>
            <td class="text-end"><?= number_format($bank['other_total_amount']) ?> SDG</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.75rem"><?= number_format($percentage, 1) ?>%</span>
                <div class="progress bank-progress" style="width: 80px;">
                  <div class="progress-bar bg-success" role="progressbar" style="width: <?= $percentage ?>%"></div>
                </div>
              </div>
            </td>
            <td class="text-end">
              <a href="?bank_id=<?= $bank['id'] ?>&start_date=<?= e($start_date) ?>&end_date=<?= e($end_date) ?>&page=<?= $page ?>&filter_college=<?= $filter_college ?>&filter_program=<?= $filter_program ?>&filter_batch=<?= $filter_batch ?>" 
                 class="btn btn-sm btn-outline-primary">
                التفاصيل
              </a>
            </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"><?= number_format(array_sum(array_column($bankPayments, 'total_amount'))) ?> SDG</th>
          <th class="text-end"><?= number_format(array_sum(array_column($bankPayments, 'other_payment_count'))) ?></th>
          <th class="text-end"><?= number_format(array_sum(array_column($bankPayments, 'other_total_amount'))) ?> SDG</th>
          <th class="text-end fw-bold text-primary"><?= number_format($totalBankPayments) ?> SDG</th>
          <th class="text-end">100%</th>
          <th></th>
        </tr>
      </tfoot>
    </table>
  </div>
</div>

<!-- Bank Transaction Details -->
<?php if ($selected_bank_id && !empty($bankTransactions)): ?>
<div class="dash-card mb-3">
  <div class="d-flex align-items-center mb-2">
    <h6 class="fw-bold mb-0">كشف حساب البنك: <?= e($selectedBankName) ?></h6>
    <div class="ms-auto">
      <button type="button" class="btn btn-sm btn-success me-2" onclick="printBankStatement()">
        <i class="fas fa-print me-1"></i>طباعة الكشف
      </button>
      <button type="button" class="btn btn-sm btn-warning me-2" onclick="exportToExcel()">
        <i class="fas fa-file-excel me-1"></i>تصدير لإكسل
      </button>
      <a href="?page=<?= $page ?>&start_date=<?= e($start_date) ?>&end_date=<?= e($end_date) ?>&filter_college=<?= $filter_college ?>&filter_program=<?= $filter_program ?>&filter_batch=<?= $filter_batch ?>" class="btn btn-sm btn-outline-secondary back-button">
        <i class="fas fa-arrow-right me-1"></i>عودة للقائمة
      </a>
      <span class="chip blue">الفترة: <?= date('d/m/Y', strtotime($start_date)) ?> - <?= date('d/m/Y', strtotime($end_date)) ?></span>
    </div>
  </div>

  <div class="table-responsive">
    <table class="table table-sm">
      <thead class="table-light">
        <tr>
          <th>نوع المعاملة</th>
          <th>الطالب</th>
          <th>نوع الرسوم</th>
          <th>رقم المرجع</th>
          <th>رقم المعاملة</th>
          <th>الفصل</th>
          <th>المبلغ</th>
          <th>التاريخ</th>
        </tr>
      </thead>
      <tbody>
        <?php 
        $totalAmount = 0;
        foreach ($bankTransactions as $transaction): 
          $totalAmount += $transaction['amount'];
        ?>
          <tr class="transaction-row">
            <td>
              <span class="chip <?= $transaction['payment_type'] === 'registration' ? 'blue' : 'purple' ?>">
                <?= $transaction['payment_type'] === 'registration' ? 'تسجيل' : 'رسوم أخرى' ?>
              </span>
            </td>
            <td>
              <?php if ($transaction['student_id']): ?>
                <div><?= e($transaction['student_name'] ?: $transaction['student_name_eng'] ?: '—') ?></div>
                <small class="text-muted"><?= e($transaction['student_id']) ?></small>
              <?php else: ?>
                <span class="text-muted">—</span>
              <?php endif; ?>
            </td>
            <td><?= e($transaction['fee_type'] ?? '—') ?></td>
            <td>
              <?php if ($transaction['payment_type'] === 'registration'): ?>
                <?= e($transaction['payment_reference']) ?>
              <?php else: ?>
                <?= e($transaction['other_fee_reference'] ?? $transaction['payment_reference']) ?>
              <?php endif; ?>
            </td>
            <td><?= e($transaction['transaction_ref'] ?? '—') ?></td>
            <td><?= e($transaction['semester_name'] ?? '—') ?></td>
            <td class="text-success fw-bold"><?= number_format($transaction['amount']) ?> SDG</td>
            <td><?= date('d/m/Y H:i', strtotime($transaction['created_at'])) ?></td>
          </tr>
        <?php endforeach; ?>
      </tbody>
      <tfoot class="table-light">
        <tr>
          <th colspan="6" class="text-start">إجمالي المعاملات:</th>
          <th class="text-success fw-bold"><?= number_format($totalAmount) ?> SDG</th>
          <th><?= count($bankTransactions) ?> معاملة</th>
        </tr>
      </tfoot>
    </table>
  </div>
</div>

<script>
function printBankStatement() {
  // Create print-friendly content
  const printContent = `
    <!DOCTYPE html>
    <html dir="rtl" lang="ar">
    <head>
      <meta charset="UTF-8">
      <title>كشف حساب البنك - <?= e($selectedBankName) ?></title>
      <style>
        body {
          font-family: 'Cairo', Arial, sans-serif;
          margin: 20px;
          line-height: 1.6;
        }
        .header {
          text-align: center;
          margin-bottom: 20px;
          border-bottom: 2px solid #333;
          padding-bottom: 10px;
        }
        .header h1 {
          margin: 0;
          color: #333;
          font-size: 24px;
        }
        .header .subtitle {
          color: #666;
          font-size: 16px;
        }
        .info-section {
          margin-bottom: 20px;
          padding: 10px;
          background: #f8f9fa;
          border-radius: 5px;
        }
        .info-row {
          display: flex;
          justify-content: space-between;
          margin-bottom: 5px;
        }
        .table {
          width: 100%;
          border-collapse: collapse;
          margin-bottom: 20px;
          font-size: 14px;
        }
        .table th {
          background-color: #343a40;
          color: white;
          padding: 8px;
          text-align: right;
          border: 1px solid #dee2e6;
        }
        .table td {
          padding: 6px;
          border: 1px solid #dee2e6;
          text-align: right;
        }
        .table tfoot {
          font-weight: bold;
          background-color: #e9ecef;
        }
        .total-section {
          text-align: left;
          margin-top: 20px;
          padding: 10px;
          border-top: 2px solid #333;
          font-size: 16px;
        }
        .print-date {
          text-align: left;
          margin-top: 20px;
          color: #666;
          font-size: 14px;
        }
        .serial-number {
          width: 50px;
          text-align: center;
        }
        @media print {
          body { margin: 0; }
          .header { margin-top: 0; }
          .table { font-size: 12px; }
        }
      </style>
    </head>
    <body>
      <div class="header">
        <h1>كشف حساب البنك</h1>
        <div class="subtitle"><?= e($selectedBankName) ?></div>
      </div>
      
      <div class="info-section">
        <div class="info-row">
          <strong>الفترة:</strong>
          <span>من <?= date('d/m/Y', strtotime($start_date)) ?> إلى <?= date('d/m/Y', strtotime($end_date)) ?></span>
        </div>
        <div class="info-row">
          <strong>تاريخ الطباعة:</strong>
          <span><?= date('d/m/Y H:i') ?></span>
        </div>
        <div class="info-row">
          <strong>عدد المعاملات:</strong>
          <span><?= count($bankTransactions) ?> معاملة</span>
        </div>
      </div>

      <table class="table">
        <thead>
          <tr>
            <th class="serial-number">م</th>
            <th>رقم الطالب</th>
            <th>اسم الطالب</th>
            <th>الفصل</th>
            <th>المبلغ</th>
            <th>التاريخ والوقت</th>
          </tr>
        </thead>
        <tbody>
          <?php 
          $totalAmount = 0;
          $serialNumber = 1;
          foreach ($bankTransactions as $transaction): 
            $totalAmount += $transaction['amount'];
            
            // Get student information
            $studentId = $transaction['student_id'] ?? '—';
            $studentName = $transaction['student_name'] ?: $transaction['student_name_eng'] ?: '—';
            $semester = $transaction['semester_name'] ?? '—';
            $amount = $transaction['amount'];
            $dateTime = date('d/m/Y H:i', strtotime($transaction['created_at']));
          ?>
            <tr>
              <td class="serial-number"><?= $serialNumber++ ?></td>
              <td><?= e($studentId) ?></td>
              <td><?= e($studentName) ?></td>
              <td><?= e($semester) ?></td>
              <td><?= number_format($amount) ?> SDG</td>
              <td><?= e($dateTime) ?></td>
            </tr>
          <?php endforeach; ?>
        </tbody>
        <tfoot>
          <tr>
            <td colspan="4" style="text-align: left;">الإجمالي:</td>
            <td colspan="2" style="text-align: right;">
              <strong><?= number_format($totalAmount) ?> SDG</strong>
            </td>
          </tr>
        </tfoot>
      </table>

      <div class="total-section">
        <strong>إجمالي المبلغ: <?= number_format($totalAmount) ?> جنيه سوداني</strong>
      </div>

      <div class="print-date">
        تم الطباعة في: <?= date('d/m/Y H:i') ?>
      </div>
    </body>
    </html>
  `;

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

function exportToExcel() {
  // Create HTML table content that Excel can open
  let htmlContent = `
    <html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">
    <head>
      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
      <!--[if gte mso 9]>
      <xml>
        <x:ExcelWorkbook>
          <x:ExcelWorksheets>
            <x:ExcelWorksheet>
              <x:Name>كشف الحساب</x:Name>
              <x:WorksheetOptions>
                <x:DisplayGridlines/>
              </x:WorksheetOptions>
            </x:ExcelWorksheet>
          </x:ExcelWorksheets>
        </x:ExcelWorkbook>
      </xml>
      <![endif]-->
      <style>
        table {
          border-collapse: collapse;
          width: 100%;
          font-family: Arial, sans-serif;
          direction: rtl;
        }
        th {
          background-color: #f2f2f2;
          border: 1px solid #ddd;
          padding: 8px;
          text-align: right;
          font-weight: bold;
        }
        td {
          border: 1px solid #ddd;
          padding: 8px;
          text-align: right;
        }
        .header {
          text-align: center;
          margin-bottom: 20px;
          font-size: 18px;
          font-weight: bold;
        }
        .footer {
          margin-top: 20px;
          font-weight: bold;
        }
      </style>
    </head>
    <body>
      <div class="header">كشف حساب البنك - <?= e($selectedBankName) ?></div>
      <div style="margin-bottom: 10px; text-align: center;">
        الفترة: من <?= date('d/m/Y', strtotime($start_date)) ?> إلى <?= date('d/m/Y', strtotime($end_date)) ?><br>
        تاريخ التصدير: <?= date('d/m/Y H:i') ?>
      </div>
      <table>
        <thead>
          <tr>
            <th>م</th>
            <th>رقم الطالب</th>
            <th>اسم الطالب</th>
            <th>نوع المعاملة</th>
            <th>نوع الرسوم</th>
            <th>رقم المرجع</th>
            <th>رقم المعاملة</th>
            <th>الفصل</th>
            <th>المبلغ (SDG)</th>
            <th>التاريخ والوقت</th>
          </tr>
        </thead>
        <tbody>
  `;

  <?php 
  $serialNumber = 1;
  $totalAmount = 0;
  foreach ($bankTransactions as $transaction): 
    $totalAmount += $transaction['amount'];
    $studentId = $transaction['student_id'] ?? '—';
    $studentName = $transaction['student_name'] ?: $transaction['student_name_eng'] ?: '—';
    $paymentType = $transaction['payment_type'] === 'registration' ? 'تسجيل' : 'رسوم أخرى';
    $feeType = $transaction['fee_type'] ?? '—';
    $reference = $transaction['payment_type'] === 'registration' ? 
      $transaction['payment_reference'] : 
      ($transaction['other_fee_reference'] ?? $transaction['payment_reference']);
    $transactionRef = $transaction['transaction_ref'] ?? '—';
    $semester = $transaction['semester_name'] ?? '—';
    $amount = $transaction['amount'];
    $dateTime = date('d/m/Y H:i', strtotime($transaction['created_at']));
  ?>
    htmlContent += `
          <tr>
            <td><?= $serialNumber++ ?></td>
            <td><?= e($studentId) ?></td>
            <td><?= e($studentName) ?></td>
            <td><?= e($paymentType) ?></td>
            <td><?= e($feeType) ?></td>
            <td><?= e($reference) ?></td>
            <td><?= e($transactionRef) ?></td>
            <td><?= e($semester) ?></td>
            <td><?= number_format($amount) ?></td>
            <td><?= e($dateTime) ?></td>
          </tr>
    `;
  <?php endforeach; ?>

  htmlContent += `
        </tbody>
      </table>
      <div class="footer">
        إجمالي المبلغ: <?= number_format($totalAmount) ?> SDG<br>
        عدد المعاملات: <?= count($bankTransactions) ?> معاملة
      </div>
    </body>
    </html>
  `;

  // Create and download file
  const blob = new Blob([htmlContent], { type: 'application/vnd.ms-excel;charset=utf-8' });
  const url = URL.createObjectURL(blob);
  const link = document.createElement('a');
  link.href = url;
  
  // Create filename
  const fileName = "كشف_حساب_<?= e($selectedBankName) ?>_<?= date('Y-m-d', strtotime($start_date)) ?>_إلى_<?= date('Y-m-d', strtotime($end_date)) ?>.xls";
  link.download = fileName;
  
  document.body.appendChild(link);
  link.click();
  document.body.removeChild(link);
  URL.revokeObjectURL(url);
}
</script>
<?php endif; ?>

 <!-- التقارير حسب الكليات - إحصاءات الرسوم -->
<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>
    </div>
  </div>

  <?php
  // استعلام للحصول على إحصاءات حسب الكلية مع تطبيق نفس عوامل التصفية
  $college_where = $where_clause;
  
  $collegesStatsSql = "
    SELECT 
      c.id,
      c.name,
      COUNT(DISTINCT s.student_id) as total_students,
      SUM(CASE WHEN r.is_paid = 1 THEN 1 ELSE 0 END) as paid_registrations,
      SUM(CASE WHEN r.is_paid = 0 THEN 1 ELSE 0 END) as unpaid_registrations,
      SUM(CASE WHEN r.is_paid = 1 THEN r.payment_amount ELSE 0 END) as total_paid_registrations,
      SUM(CASE WHEN r.is_paid = 0 THEN r.payment_amount ELSE 0 END) as total_unpaid_registrations,
      SUM(CASE WHEN of.is_paid = 1 THEN 1 ELSE 0 END) as paid_other_fees,
      SUM(CASE WHEN of.is_paid = 0 THEN 1 ELSE 0 END) as unpaid_other_fees,
      SUM(CASE WHEN of.is_paid = 1 THEN of.amount ELSE 0 END) as total_paid_other_fees,
      SUM(CASE WHEN of.is_paid = 0 THEN of.amount ELSE 0 END) as total_unpaid_other_fees
    FROM colleges c
    LEFT JOIN students s ON c.id = s.college_id
    LEFT JOIN registrations r ON s.student_id = r.student_id AND r.deleted_at IS NULL
    LEFT JOIN other_fees of ON s.phone_number = of.phone_number AND of.deleted_at IS NULL
    $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,
    'unpaid_other_fees' => 0,
    'paid_other_fees' => 0,
    'total_paid_registrations' => 0,
    'total_paid_other_fees' => 0,
    'total_unpaid_registrations' => 0,
    'total_unpaid_other_fees' => 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['unpaid_other_fees'] += $college['unpaid_other_fees'] ?? 0;
    $grand_totals['paid_other_fees'] += $college['paid_other_fees'] ?? 0;
    $grand_totals['total_paid_registrations'] += $college['total_paid_registrations'] ?? 0;
    $grand_totals['total_paid_other_fees'] += $college['total_paid_other_fees'] ?? 0;
    $grand_totals['total_unpaid_registrations'] += $college['total_unpaid_registrations'] ?? 0;
    $grand_totals['total_unpaid_other_fees'] += $college['total_unpaid_other_fees'] ?? 0;
  }
  
  $grand_totals['total_paid_all'] = $grand_totals['total_paid_registrations'] + $grand_totals['total_paid_other_fees'];
  $grand_totals['total_unpaid_all'] = $grand_totals['total_unpaid_registrations'] + $grand_totals['total_unpaid_other_fees'];
  ?>

  <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>
          <th class="text-end">إجمالي غير المدفوع</th>
        </tr>
      </thead>
      <tbody>
        <?php if (empty($byCollege)): ?>
          <tr>
            <td colspan="8" class="text-center text-muted py-2">
              لا توجد بيانات لعرضها.
            </td>
          </tr>
        <?php else: foreach ($byCollege as $college): 
          $college_paid = ($college['total_paid_registrations'] ?? 0) + ($college['total_paid_other_fees'] ?? 0);
          $college_unpaid = ($college['total_unpaid_registrations'] ?? 0) + ($college['total_unpaid_other_fees'] ?? 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">
              <span class="text-success"><?= number_format($college['paid_other_fees'] ?? 0) ?></span>
              <br>
              <small class="text-muted" style="font-size:0.7rem"><?= number_format($college['total_paid_other_fees'] ?? 0) ?> SDG</small>
            </td>
            <td class="text-end">
              <span class="text-danger"><?= number_format($college['unpaid_other_fees'] ?? 0) ?></span>
              <br>
              <small class="text-muted" style="font-size:0.7rem"><?= number_format($college['total_unpaid_other_fees'] ?? 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>
          </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">
            <span class="text-success"><?= number_format($grand_totals['paid_other_fees']) ?></span>
            <br>
            <small class="text-muted" style="font-size:0.65rem"><?= number_format($grand_totals['total_paid_other_fees']) ?> SDG</small>
          </th>
          <th class="text-end" style="font-size:0.8rem">
            <span class="text-danger"><?= number_format($grand_totals['unpaid_other_fees']) ?></span>
            <br>
            <small class="text-muted" style="font-size:0.65rem"><?= number_format($grand_totals['total_unpaid_other_fees']) ?> 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>
        </tr>
      </tfoot>
    </table>
  </div>
</div>

<!-- جميع الطلبات مع ترقيم الصفحات -->
<div class="dash-card">
  <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>
    </div>
  </div>

  <!-- Pagination Top -->
  <?php if ($total_pages > 1): ?>
  <div class="d-flex justify-content-between align-items-center mb-3">
    <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">
      <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 style="width:90px"></th>
        </tr>
      </thead>
      <tbody>
        <?php if (empty($recentAccepted)): ?>
          <tr><td colspan="11" class="text-center text-muted py-2">— لا توجد نتائج —</td></tr>
        <?php else: 
          $counter = $offset + 1;
          foreach ($recentAccepted as $r): 
            // Calculate fees for this student
            $fees_sql = "
              SELECT 
                (SELECT COALESCE(SUM(payment_amount), 0) FROM registrations WHERE student_id = ? AND is_paid = 1 AND deleted_at IS NULL) AS paid_registrations,
                (SELECT COALESCE(SUM(payment_amount), 0) FROM registrations WHERE student_id = ? AND is_paid = 0 AND deleted_at IS NULL) AS unpaid_registrations,
                (SELECT COALESCE(SUM(amount), 0) FROM other_fees WHERE phone_number = ? AND is_paid = 1 AND deleted_at IS NULL) AS paid_other_fees,
                (SELECT COALESCE(SUM(amount), 0) FROM other_fees WHERE phone_number = ? AND is_paid = 0 AND deleted_at IS NULL) AS unpaid_other_fees
            ";
            $fees_stmt = $mysqli1->prepare($fees_sql);
            $fees_stmt->bind_param("ssss", $r['student_id'], $r['student_id'], $r['phone_number'], $r['phone_number']);
            $fees_stmt->execute();
            $fees_data = $fees_stmt->get_result()->fetch_assoc();
            $fees_stmt->close();

            $total_paid = ($fees_data['paid_registrations'] ?? 0) + ($fees_data['paid_other_fees'] ?? 0);
            $total_unpaid = ($fees_data['unpaid_registrations'] ?? 0) + ($fees_data['unpaid_other_fees'] ?? 0);
            $has_unpaid = $total_unpaid > 0;
        ?>
            <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>
                <?php 
                $gender = $r['gender'] ?? 0;
                $genderText = $gender == 1 ? 'ذكر' : ($gender == 2 ? 'أنثى' : 'غير محدد');
                $genderClass = $gender == 1 ? 'chip blue' : ($gender == 2 ? 'chip purple' : 'chip gray');
                ?>
                <span class="<?= $genderClass ?>"><?= e($genderText) ?></span>
              </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="btn-group btn-group-sm" role="group">
                  <a class="btn btn-sm btn-primary" href="committee_student_profile.php?student_id=<?= urlencode($r['student_id']) ?>">الملف</a>
                </div>
              </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">
    <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>
// Registration Status Chart
const registrationsChart = new Chart(document.getElementById('registrationsChart'), {
  type: 'doughnut',
  data: {
    labels: ['مدفوعة', 'غير مدفوعة'],
    datasets: [{
      data: [<?= $kpi['paid_registrations'] ?>, <?= $kpi['unpaid_registrations'] ?>],
      backgroundColor: [
        'rgba(16, 185, 129, 0.8)',
        'rgba(239, 68, 68, 0.8)'
      ],
      borderColor: [
        'rgb(16, 185, 129)',
        'rgb(239, 68, 68)'
      ],
      borderWidth: 1,
      hoverBackgroundColor: [
        'rgba(16, 185, 129, 1)',
        'rgba(239, 68, 68, 1)'
      ],
      hoverOffset: 6
    }]
  },
  options: {
    responsive: true,
    maintainAspectRatio: false,
    cutout: '55%',
    plugins: {
      legend: { 
        position: 'bottom',
        labels: {
          usePointStyle: true,
          padding: 10,
          font: {
            size: 10
          }
        }
      },
      tooltip: {
        bodyFont: {
          size: 10
        },
        callbacks: {
          label: function(context) {
            const total = <?= $kpi['paid_registrations'] + $kpi['unpaid_registrations'] ?>;
            const value = context.raw;
            const percentage = total > 0 ? ((value / total) * 100).toFixed(1) : 0;
            return `${context.label}: ${value} (${percentage}%)`;
          }
        }
      }
    },
    animation: {
      animateScale: true,
      animateRotate: true
    }
  }
});

// Other Fees Status Chart
const otherFeesChart = new Chart(document.getElementById('otherFeesChart'), {
  type: 'doughnut',
  data: {
    labels: ['مدفوعة', 'غير مدفوعة'],
    datasets: [{
      data: [<?= $kpi['paid_other_fees'] ?>, <?= $kpi['unpaid_other_fees'] ?>],
      backgroundColor: [
        'rgba(59, 130, 246, 0.8)',
        'rgba(245, 158, 11, 0.8)'
      ],
      borderColor: [
        'rgb(59, 130, 246)',
        'rgb(245, 158, 11)'
      ],
      borderWidth: 1,
      hoverOffset: 6
    }]
  },
  options: {
    responsive: true,
    maintainAspectRatio: false,
    cutout: '55%',
    plugins: {
      legend: { 
        position: 'bottom',
        labels: {
          usePointStyle: true,
          padding: 10,
          font: {
            size: 10
          }
        }
      },
      tooltip: {
        bodyFont: {
          size: 10
        },
        callbacks: {
          label: function(context) {
            const total = <?= $kpi['paid_other_fees'] + $kpi['unpaid_other_fees'] ?>;
            const value = context.raw;
            const percentage = total > 0 ? ((value / total) * 100).toFixed(1) : 0;
            return `${context.label}: ${value} (${percentage}%)`;
          }
        }
      }
    },
    animation: {
      animateScale: true,
      animateRotate: true
    }
  }
});

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

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