<?php
// /admission3333/export_to_excel.php - نسخة مطابقة لبيانات صفحة المحاسب
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

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

// ============================================
// 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}%" : '';

// ============================================
// 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) : "";

// ============================================
// GET NAMES FOR DISPLAY IN HEADER
// ============================================

$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();
    }
}

// ============================================
// EXPORT QUERY - مطابق تماماً لاستعلام الصفحة الرئيسية
// ============================================

$export_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.gender,
        s.section_id, 
        sec.name AS section_name,
        s.batch_id, 
        b.value AS batch_name,
        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
";

// Prepare and execute the query
$export_stmt = $mysqli1->prepare($export_sql);

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

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

$export_stmt->execute();
$students = $export_stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$export_stmt->close();

// Calculate summary statistics (مطابق للصفحة الرئيسية)
$total_students = count($students);
$total_paid_all = 0;
$total_unpaid_all = 0;

foreach ($students as $student) {
    $total_paid_all += (float)($student['total_paid'] ?? 0);
    $total_unpaid_all += (float)($student['total_unpaid'] ?? 0);
}

$total_receivable = $total_paid_all + $total_unpaid_all;
$payment_percentage = $total_receivable > 0 ? round(($total_paid_all / $total_receivable) * 100, 2) : 0;

// ============================================
// GENERATE EXCEL FILE
// ============================================

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

// Create Excel table with proper formatting
echo '<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">';
echo '<head>';
echo '<meta charset="UTF-8">';
echo '<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">';
echo '<title>تقرير الطلاب - المحاسب</title>';
echo '<style>';
echo 'body { font-family: "Cairo", Arial, sans-serif; direction: rtl; margin: 20px; }';
echo 'h1 { color: #3E6B54; text-align: center; margin-bottom: 20px; }';
echo 'h2 { color: #1F3A2E; margin-top: 30px; border-bottom: 2px solid #3E6B54; padding-bottom: 5px; }';
echo '.header-info { background-color: #f8f9fa; padding: 15px; margin-bottom: 20px; border-radius: 5px; border-right: 5px solid #3E6B54; }';
echo '.summary-table { background-color: #f0f7f0; margin: 20px 0; border-collapse: collapse; width: 100%; }';
echo '.summary-table th { background-color: #3E6B54; color: white; padding: 10px; text-align: center; }';
echo '.summary-table td { padding: 8px; border: 1px solid #3E6B54; text-align: center; }';
echo 'table { border-collapse: collapse; width: 100%; margin-top: 20px; font-size: 11px; }';
echo 'th { background-color: #3E6B54; color: white; padding: 8px; text-align: center; border: 1px solid #1F3A2E; font-weight: bold; }';
echo 'td { padding: 6px; border: 1px solid #3E6B54; text-align: right; }';
echo 'tr:nth-child(even) { background-color: #f8f9fa; }';
echo '.total-row { background-color: #e9ecef; font-weight: bold; }';
echo '.money { text-align: left; direction: ltr; }';
echo '.percentage { text-align: center; }';
echo '.chip-green { background-color: #d1fae5; color: #065f46; padding: 3px 8px; border-radius: 12px; font-size: 10px; display: inline-block; }';
echo '.chip-orange { background-color: #fed7aa; color: #9a3412; padding: 3px 8px; border-radius: 12px; font-size: 10px; display: inline-block; }';
echo '.chip-red { background-color: #fee2e2; color: #991b1b; padding: 3px 8px; border-radius: 12px; font-size: 10px; display: inline-block; }';
echo '.chip-blue { background-color: #dbeafe; color: #1e3a8a; padding: 3px 8px; border-radius: 12px; font-size: 10px; display: inline-block; }';
echo '.chip-purple { background-color: #e9d5ff; color: #7e22ce; padding: 3px 8px; border-radius: 12px; font-size: 10px; display: inline-block; }';
echo '</style>';
echo '</head>';
echo '<body>';

// Report Header
echo '<h1>تقرير الطلاب - المحاسب</h1>';
echo '<div class="header-info">';
echo '<table style="width: 100%; background: transparent; border: none;">';
echo '<tr><td style="border: none; width: 50%;"><strong>تاريخ التقرير:</strong> ' . date('Y-m-d H:i:s') . '</td>';
echo '<td style="border: none;"><strong>تم التصدير بواسطة:</strong> ' . htmlspecialchars($committeeName ?: 'المحاسب') . '</td></tr>';
echo '<tr><td style="border: none;"><strong>الفترة:</strong> ' . date('d/m/Y', strtotime($start_date)) . ' - ' . date('d/m/Y', strtotime($end_date)) . '</td>';
echo '<td style="border: none;"><strong>إجمالي الطلاب:</strong> ' . number_format($total_students) . '</td></tr>';
echo '</table>';
echo '</div>';

// Filter Information
if ($filter_college || $filter_program || $filter_specialization || $filter_section || $filter_batch || $filter_admission_type || $filter_semester || !empty($search_query)) {
    echo '<h2>معايير التصفية المطبقة</h2>';
    echo '<div class="header-info">';
    echo '<table style="width: 100%; background: transparent; border: none;">';
    
    $filter_display = [];
    if ($filter_college) $filter_display[] = '<strong>الكلية:</strong> ' . htmlspecialchars($college_name);
    if ($filter_program) $filter_display[] = '<strong>البرنامج:</strong> ' . htmlspecialchars($program_name);
    if ($filter_section) $filter_display[] = '<strong>القسم:</strong> ' . htmlspecialchars($section_name);
    if ($filter_specialization) $filter_display[] = '<strong>التخصص:</strong> ' . htmlspecialchars($specialization_name);
    if ($filter_batch) $filter_display[] = '<strong>الدفعة:</strong> ' . htmlspecialchars($batch_name);
    if ($filter_admission_type) $filter_display[] = '<strong>نوع القبول:</strong> ' . htmlspecialchars($admission_type_name);
    if ($filter_semester) $filter_display[] = '<strong>الفصل الدراسي:</strong> ' . htmlspecialchars($semester_name);
    if (!empty($search_query)) $filter_display[] = '<strong>بحث:</strong> ' . htmlspecialchars($search_query);
    
    // Display filters in two columns
    for ($i = 0; $i < count($filter_display); $i += 2) {
        echo '<tr>';
        echo '<td style="border: none; width: 50%;">' . $filter_display[$i] . '</td>';
        if (isset($filter_display[$i + 1])) {
            echo '<td style="border: none; width: 50%;">' . $filter_display[$i + 1] . '</td>';
        } else {
            echo '<td style="border: none;"></td>';
        }
        echo '</tr>';
    }
    
    echo '</table>';
    echo '</div>';
}

// Summary Statistics
echo '<h2>ملخص إحصائي</h2>';
echo '<table class="summary-table">';
echo '<tr>';
echo '<th>إجمالي الطلاب</th>';
echo '<th>إجمالي المدفوع</th>';
echo '<th>إجمالي المستحق</th>';
echo '<th>إجمالي المتحصلات</th>';
echo '<th>نسبة التحصيل</th>';
echo '</tr>';
echo '<tr>';
echo '<td style="text-align: center;">' . number_format($total_students) . '</td>';
echo '<td class="money">' . number_format($total_paid_all, 2) . ' SDG</td>';
echo '<td class="money">' . number_format($total_unpaid_all, 2) . ' SDG</td>';
echo '<td class="money">' . number_format($total_receivable, 2) . ' SDG</td>';
echo '<td class="percentage"><strong>' . number_format($payment_percentage, 2) . '%</strong></td>';
echo '</tr>';
echo '</table>';

// Detailed Students Table (مطابق لجدول الصفحة الرئيسية)
echo '<h2>قائمة الطلاب</h2>';
echo '<table>';
echo '<thead>';
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 '</tr>';
echo '</thead>';
echo '<tbody>';

$counter = 1;
foreach ($students as $student) {
    $total_paid = (float)($student['total_paid'] ?? 0);
    $total_unpaid = (float)($student['total_unpaid'] ?? 0);
    $has_unpaid = $total_unpaid > 0;
    $bank_name = $student['bank_name'] ?? '—';
    
    $student_payment_percentage = 0;
    if (($total_paid + $total_unpaid) > 0) {
        $student_payment_percentage = round(($total_paid / ($total_paid + $total_unpaid)) * 100, 1);
    }
    
    // Determine status class
    $status_class = '';
    $status_text = '';
    if ($total_paid > 0 && !$has_unpaid) {
        $status_class = 'chip-green';
        $status_text = 'مسدد بالكامل';
    } elseif ($total_paid > 0 && $has_unpaid) {
        $status_class = 'chip-orange';
        $status_text = 'مسدد جزئياً';
    } else {
        $status_class = 'chip-red';
        $status_text = 'غير مسدد';
    }
    
    echo '<tr>';
    echo '<td style="text-align: center;">' . $counter++ . '</td>';
    echo '<td>' . htmlspecialchars($student['student_name'] ?: $student['student_name_eng'] ?: '—') . '</td>';
    echo '<td style="text-align: center;">' . htmlspecialchars($student['student_id'] ?? '—') . '</td>';
    echo '<td>' . htmlspecialchars($student['college_name'] ?? '—') . '</td>';
    echo '<td>' . htmlspecialchars($student['program_name'] ?? '—') . '</td>';
    echo '<td>' . htmlspecialchars($student['specialization_name'] ?? '—') . '</td>';
    echo '<td>' . htmlspecialchars($student['admission_type_name'] ?? '—') . '</td>';
    echo '<td>' . htmlspecialchars($student['batch_name'] ?? '—') . '</td>';
    echo '<td>' . htmlspecialchars($student['section_name'] ?? '—') . '</td>';
    echo '<td>';
    if ($bank_name !== '—') {
        echo '<span class="chip-blue">' . htmlspecialchars($bank_name) . '</span>';
    } else {
        echo '—';
    }
    echo '</td>';
    echo '<td><span class="' . $status_class . '">' . $status_text . '</span></td>';
    echo '<td class="money">' . number_format($total_paid, 2) . ' SDG</td>';
    echo '<td class="money">' . number_format($total_unpaid, 2) . ' SDG</td>';
    echo '<td class="percentage">' . number_format($student_payment_percentage, 1) . '%</td>';
    echo '</tr>';
}

// Summary Row
echo '<tr class="total-row">';
echo '<td colspan="11" style="text-align: left;"><strong>الإجمالي العام:</strong></td>';
echo '<td class="money"><strong>' . number_format($total_paid_all, 2) . ' SDG</strong></td>';
echo '<td class="money"><strong>' . number_format($total_unpaid_all, 2) . ' SDG</strong></td>';
echo '<td class="percentage"><strong>' . number_format($payment_percentage, 2) . '%</strong></td>';
echo '</tr>';

echo '</tbody>';
echo '</table>';

// Footer with generation info
echo '<div style="margin-top: 30px; padding-top: 20px; border-top: 2px solid #3E6B54; text-align: center; color: #666; font-size: 11px;">';
echo '<p>تم إنشاء هذا التقرير بواسطة نظام القبول الجامعي</p>';
echo '<p>تاريخ الإنشاء: ' . date('Y-m-d H:i:s') . '</p>';
echo '<p>جميع المبالغ بالعملة المحلية (SDG)</p>';
echo '</div>';

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