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

$mysqli1->set_charset('utf8mb4');

// Get parameters
$start_date = $_GET['start_date'] ?? date('Y-m-01');
$end_date = $_GET['end_date'] ?? date('Y-m-t');
$filter_college = isset($_GET['filter_college']) && !empty($_GET['filter_college']) ? (int)$_GET['filter_college'] : null;
$filter_program = isset($_GET['filter_program']) && !empty($_GET['filter_program']) ? (int)$_GET['filter_program'] : null;
$filter_batch = isset($_GET['filter_batch']) && !empty($_GET['filter_batch']) ? (int)$_GET['filter_batch'] : null;
$filter_section = isset($_GET['filter_section']) && !empty($_GET['filter_section']) ? (int)$_GET['filter_section'] : null;
$filter_semester = isset($_GET['filter_semester']) && !empty($_GET['filter_semester']) ? (int)$_GET['filter_semester'] : null;
$search_query = isset($_GET['search']) && !empty($_GET['search']) ? trim($_GET['search']) : '';

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

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

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

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

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

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

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

// Get students data with fees calculation
$sql = "
  SELECT 
    s.student_id,
    s.student_name,
    s.student_name_eng,
    s.phone_number,
    c.name as college_name,
    p.name as program_name,
    b.value as batch_name,
    sec.name as section_name,
    (
        SELECT COALESCE(SUM(payment_amount), 0) 
        FROM registrations 
        WHERE student_id = s.student_id 
        AND is_paid = 1 
        AND deleted_at IS NULL
        " . ($filter_semester ? " AND semester_id = $filter_semester" : "") . "
    ) as paid_registrations,
    (
        SELECT COALESCE(SUM(payment_amount), 0) 
        FROM registrations 
        WHERE student_id = s.student_id 
        AND is_paid = 0 
        AND deleted_at IS NULL
        " . ($filter_semester ? " AND semester_id = $filter_semester" : "") . "
    ) as unpaid_registrations,
    (
        SELECT COALESCE(SUM(amount), 0) 
        FROM other_fees 
        WHERE phone_number = s.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 = s.phone_number 
        AND is_paid = 0 
        AND deleted_at IS NULL
    ) as unpaid_other_fees,
    (
        SELECT bnk.name 
        FROM payments pmt
        LEFT JOIN banks bnk ON pmt.bank_id = bnk.id
        WHERE pmt.student_id = s.student_id 
        AND pmt.deleted_at IS NULL
        ORDER BY pmt.created_at DESC 
        LIMIT 1
    ) AS bank_name
  FROM students s
  LEFT JOIN colleges c ON c.id = s.college_id
  LEFT JOIN programs p ON p.id = s.program_id
  LEFT JOIN batches b ON b.id = s.batch_id
  LEFT JOIN sections sec ON sec.id = s.section_id
  $where_clause
  ORDER BY s.student_id DESC
";

$stmt = $mysqli1->prepare($sql);
if ($types !== '') {
    $stmt->bind_param($types, ...$params);
}
$stmt->execute();
$result = $stmt->get_result();
$students = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();

// Set headers for Excel download
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="طلاب_' . date('Y-m-d_H-i') . '.xls"');
header('Cache-Control: max-age=0');
header('Expires: 0');
header('Pragma: public');

// Start Excel content
echo '<html dir="rtl">';
echo '<head>';
echo '<meta charset="UTF-8">';
echo '</head>';
echo '<body>';

echo '<table border="1">';
echo '<tr>';
echo '<th>#</th>';
echo '<th>الرقم الجامعي</th>';
echo '<th>اسم الطالب</th>';
echo '<th>رقم الهاتف</th>';
echo '<th>الكلية</th>';
echo '<th>البرنامج</th>';
echo '<th>الدفعة</th>';
echo '<th>القسم</th>';
echo '<th>البنك</th>';
echo '<th>المدفوع للتسجيل</th>';
echo '<th>المستحق للتسجيل</th>';
echo '<th>المدفوع لرسوم أخرى</th>';
echo '<th>المستحق لرسوم أخرى</th>';
echo '<th>إجمالي المدفوع</th>';
echo '<th>إجمالي المستحق</th>';
echo '<th>نسبة الدفع</th>';
echo '</tr>';

$counter = 1;
foreach ($students as $student) {
    $paid_reg = $student['paid_registrations'] ?? 0;
    $unpaid_reg = $student['unpaid_registrations'] ?? 0;
    $paid_other = $student['paid_other_fees'] ?? 0;
    $unpaid_other = $student['unpaid_other_fees'] ?? 0;
    
    $total_paid = $paid_reg + $paid_other;
    $total_unpaid = $unpaid_reg + $unpaid_other;
    
    // حساب نسبة الدفع
    $payment_percentage = 0;
    if (($total_paid + $total_unpaid) > 0) {
        $payment_percentage = round(($total_paid / ($total_paid + $total_unpaid)) * 100, 2);
    }
    
    echo '<tr>';
    echo '<td>' . $counter++ . '</td>';
    echo '<td>' . htmlspecialchars($student['student_id'] ?? '') . '</td>';
    echo '<td>' . htmlspecialchars($student['student_name'] ?? $student['student_name_eng'] ?? '') . '</td>';
    echo '<td>' . htmlspecialchars($student['phone_number'] ?? '') . '</td>';
    echo '<td>' . htmlspecialchars($student['college_name'] ?? '') . '</td>';
    echo '<td>' . htmlspecialchars($student['program_name'] ?? '') . '</td>';
    echo '<td>' . htmlspecialchars($student['batch_name'] ?? '') . '</td>';
    echo '<td>' . htmlspecialchars($student['section_name'] ?? '') . '</td>';
    echo '<td>' . htmlspecialchars($student['bank_name'] ?? '') . '</td>';
    echo '<td>' . number_format($paid_reg, 2) . '</td>';
    echo '<td>' . number_format($unpaid_reg, 2) . '</td>';
    echo '<td>' . number_format($paid_other, 2) . '</td>';
    echo '<td>' . number_format($unpaid_other, 2) . '</td>';
    echo '<td>' . number_format($total_paid, 2) . '</td>';
    echo '<td>' . number_format($total_unpaid, 2) . '</td>';
    echo '<td>' . number_format($payment_percentage, 2) . '%</td>';
    echo '</tr>';
}

// Add summary row
echo '<tr style="font-weight:bold; background-color:#f2f2f2;">';
echo '<td colspan="9">الإجمالي</td>';
echo '<td>' . number_format(array_sum(array_column($students, 'paid_registrations')), 2) . '</td>';
echo '<td>' . number_format(array_sum(array_column($students, 'unpaid_registrations')), 2) . '</td>';
echo '<td>' . number_format(array_sum(array_column($students, 'paid_other_fees')), 2) . '</td>';
echo '<td>' . number_format(array_sum(array_column($students, 'unpaid_other_fees')), 2) . '</td>';
echo '<td>' . number_format(array_sum(array_column($students, 'paid_registrations')) + array_sum(array_column($students, 'paid_other_fees')), 2) . '</td>';
echo '<td>' . number_format(array_sum(array_column($students, 'unpaid_registrations')) + array_sum(array_column($students, 'unpaid_other_fees')), 2) . '</td>';

// Calculate total payment percentage
$total_paid_all = array_sum(array_column($students, 'paid_registrations')) + array_sum(array_column($students, 'paid_other_fees'));
$total_unpaid_all = array_sum(array_column($students, 'unpaid_registrations')) + array_sum(array_column($students, 'unpaid_other_fees'));
$total_payment_percentage = 0;
if (($total_paid_all + $total_unpaid_all) > 0) {
    $total_payment_percentage = round(($total_paid_all / ($total_paid_all + $total_unpaid_all)) * 100, 2);
}
echo '<td>' . number_format($total_payment_percentage, 2) . '%</td>';
echo '</tr>';

echo '</table>';

// Add footer information
echo '<br><br>';
echo '<div style="text-align:center; font-size:12px;">';
echo '<strong>تقرير تصدير الطلاب</strong><br>';
echo 'تاريخ التصدير: ' . date('Y/m/d H:i') . '<br>';
echo 'الفترة: من ' . $start_date . ' إلى ' . $end_date . '<br>';
echo 'إجمالي الطلاب: ' . count($students) . '<br>';
echo '</div>';

echo '</body>';
echo '</html>';
exit();