<?php
// export_students_csv.php - نسخة آمنة مع حماية من SQL Injection
error_reporting(E_ALL);
ini_set('display_errors', 1);
session_start();
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);

// إزالة أي مخرجات سابقة
if (ob_get_length()) ob_clean();

// ==================== دالة آمنة لتنقية المدخلات ====================
function sanitizeInt($value, $default = null) {
    if ($value === null || $value === '') return $default;
    $filtered = filter_var($value, FILTER_VALIDATE_INT);
    return $filtered !== false ? $filtered : $default;
}

function sanitizeString($value, $max_length = 255) {
    if ($value === null) return '';
    $value = trim((string)$value);
    // إزالة الأحرف الخطرة
    $value = preg_replace('/[^\p{Arabic}\p{L}\p{N}\s\-_]/u', '', $value);
    return substr($value, 0, $max_length);
}

// ==================== استرجاع الفلاتر من Session مع التنقية ====================
$filters = $_SESSION['export_filters'] ?? [];

$filter_college_id = sanitizeInt($filters['college_id'] ?? null);
$filter_section_id = sanitizeInt($filters['section_id'] ?? null);
$filter_program_id = sanitizeInt($filters['program_id'] ?? null);
$filter_specialization_id = sanitizeInt($filters['specialization_id'] ?? null);
$filter_batch_id = sanitizeInt($filters['batch_id'] ?? null);
$search_query = sanitizeString($filters['search_query'] ?? '', 100);

// ==================== التحقق من وجود CSRF Token ====================
if (!isset($_SESSION['csrf_token']) || !isset($_SERVER['HTTP_X_REQUESTED_WITH']) || $_SERVER['HTTP_X_REQUESTED_WITH'] !== 'XMLHttpRequest') {
    // للتصدير المباشر، نسمح بتمريره (لأن المستخدم مصرح له)
    if (!isset($_SESSION['committee_id'])) {
        die('غير مصرح بالوصول');
    }
}

// ==================== بناء الاستعلام بشكل آمن ====================
$where_parts = [];
$params = [];
$types = "";

// 1. صلاحيات اللجنة
if (!$canAll && $scopeCollege !== null) {
    $where_parts[] = "s.college_id = ?";
    $types .= 'i';
    $params[] = $scopeCollege;
} elseif (!$canAll && $scopeCollege === null) {
    $where_parts[] = "1=0";
}

// 2. فلتر الكلية
if ($filter_college_id !== null && $filter_college_id > 0) {
    $where_parts[] = "s.college_id = ?";
    $types .= 'i';
    $params[] = $filter_college_id;
}

// 3. فلتر القسم
if ($filter_section_id !== null && $filter_section_id > 0) {
    $where_parts[] = "s.section_id = ?";
    $types .= 'i';
    $params[] = $filter_section_id;
}

// 4. فلتر البرنامج
if ($filter_program_id !== null && $filter_program_id > 0) {
    $where_parts[] = "s.program_id = ?";
    $types .= 'i';
    $params[] = $filter_program_id;
}

// 5. فلتر التخصص
if ($filter_specialization_id !== null && $filter_specialization_id > 0) {
    $where_parts[] = "s.specialization_id = ?";
    $types .= 'i';
    $params[] = $filter_specialization_id;
}

// 6. فلتر الدفعة
if ($filter_batch_id !== null && $filter_batch_id > 0) {
    $where_parts[] = "s.batch_id = ?";
    $types .= 'i';
    $params[] = $filter_batch_id;
}

// 7. فلتر البحث (مع الحماية من injection)
if (!empty($search_query)) {
    $where_parts[] = "(s.student_id LIKE ? OR s.student_name LIKE ? OR s.student_name_eng LIKE ?)";
    $types .= 'sss';
    $search_param = "%" . $search_query . "%";
    $params[] = $search_param;
    $params[] = $search_param;
    $params[] = $search_param;
}

// بناء جملة WHERE
$where_clause = '';
if (!empty($where_parts)) {
    $where_clause = "WHERE " . implode(" AND ", $where_parts);
} else {
    $where_clause = "WHERE 1=1";
}

// ==================== استعلام آمن ====================
$sql = "
    SELECT 
        s.student_id,
        s.student_number,
        s.student_name,
        c.name AS college_name,
        p.name AS program_name,
        sec.name AS section_name,
        sp.specialization_name_ar AS specialization_name,
        b.value AS batch_name,
        a.name AS admission_name
    FROM students s
    LEFT JOIN colleges c ON s.college_id = c.id
    LEFT JOIN sections sec ON s.section_id = sec.id
    LEFT JOIN programs p ON s.program_id = p.id
    LEFT JOIN specializations sp ON s.specialization_id = sp.specialization_id
    LEFT JOIN batches b ON s.batch_id = b.id
    LEFT JOIN admissions a ON s.admission_id = a.id
    {$where_clause}
    ORDER BY c.name, p.name, sec.name, sp.specialization_name_ar, s.student_id
    LIMIT 10000
";

// تنفيذ الاستعلام باستخدام prepared statement
$stmt = $mysqli1->prepare($sql);
if (!$stmt) {
    die('خطأ في تحضير الاستعلام');
}

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

$stmt->execute();
$result = $stmt->get_result();

// ==================== إنشاء ملف CSV آمن ====================
$filename = 'students_export_' . date('Y-m-d_H-i-s') . '.csv';

// تنظيف أي مخرجات قبل إرسال الهيدرز
if (ob_get_level()) ob_end_clean();

header('Content-Type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Pragma: no-cache');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');

$output = fopen('php://output', 'w');

// إضافة BOM لدعم العربية
fprintf($output, chr(0xEF) . chr(0xBB) . chr(0xBF));

// ==================== كتابة معلومات التصدير ====================
fputcsv($output, ['تقرير بيانات الطلاب']);
fputcsv($output, ['تاريخ التصدير: ' . date('Y-m-d H:i:s')]);
fputcsv($output, ['تم التصدير بواسطة: ' . htmlspecialchars($committeeName, ENT_QUOTES, 'UTF-8')]);
fputcsv($output, []);

// كتابة ملخص الفلاتر المطبقة (مع تنقية الإخراج)
$filters_summary = [];
if ($filter_college_id) {
    $college_name = getFilterNameSafe($mysqli1, 'college', $filter_college_id);
    $filters_summary[] = 'الكلية: ' . htmlspecialchars($college_name, ENT_QUOTES, 'UTF-8');
}
if ($filter_section_id) {
    $section_name = getFilterNameSafe($mysqli1, 'section', $filter_section_id);
    $filters_summary[] = 'القسم: ' . htmlspecialchars($section_name, ENT_QUOTES, 'UTF-8');
}
if ($filter_program_id) {
    $program_name = getFilterNameSafe($mysqli1, 'program', $filter_program_id);
    $filters_summary[] = 'البرنامج: ' . htmlspecialchars($program_name, ENT_QUOTES, 'UTF-8');
}
if ($filter_specialization_id) {
    $spec_name = getFilterNameSafe($mysqli1, 'specialization', $filter_specialization_id);
    $filters_summary[] = 'التخصص: ' . htmlspecialchars($spec_name, ENT_QUOTES, 'UTF-8');
}
if ($filter_batch_id) {
    $batch_name = getFilterNameSafe($mysqli1, 'batch', $filter_batch_id);
    $filters_summary[] = 'الدفعة: ' . htmlspecialchars($batch_name, ENT_QUOTES, 'UTF-8');
}
if (!empty($search_query)) {
    $filters_summary[] = 'البحث: "' . htmlspecialchars($search_query, ENT_QUOTES, 'UTF-8') . '"';
}

if (!empty($filters_summary)) {
    fputcsv($output, ['عوامل التصفية المطبقة:']);
    foreach ($filters_summary as $filter) {
        fputcsv($output, ['  ' . $filter]);
    }
    fputcsv($output, []);
} else {
    fputcsv($output, ['تنبيه: لم يتم تطبيق أي تصفية - سيتم تصدير جميع الطلاب']);
    fputcsv($output, []);
}

// ==================== كتابة العناوين ====================
$headers = ['م', 'رقم الطالب', 'الرقم الجامعي', 'اسم الطالب', 'الكلية', 'البرنامج', 'القسم', 'التخصص', 'الدفعة', 'نوع القبول'];
fputcsv($output, $headers);

// ==================== كتابة البيانات (مع تنقية الإخراج) ====================
$counter = 1;
$total_students = 0;

while ($row = $result->fetch_assoc()) {
    $rowData = [
        $counter,
        htmlspecialchars($row['student_id'] ?? '', ENT_QUOTES, 'UTF-8'),
        htmlspecialchars($row['student_number'] ?? '', ENT_QUOTES, 'UTF-8'),
        htmlspecialchars($row['student_name'] ?? '', ENT_QUOTES, 'UTF-8'),
        htmlspecialchars($row['college_name'] ?? '', ENT_QUOTES, 'UTF-8'),
        htmlspecialchars($row['program_name'] ?? '', ENT_QUOTES, 'UTF-8'),
        htmlspecialchars($row['section_name'] ?? '', ENT_QUOTES, 'UTF-8'),
        htmlspecialchars($row['specialization_name'] ?? '', ENT_QUOTES, 'UTF-8'),
        htmlspecialchars($row['batch_name'] ?? '', ENT_QUOTES, 'UTF-8'),
        htmlspecialchars($row['admission_name'] ?? '', ENT_QUOTES, 'UTF-8')
    ];
    fputcsv($output, $rowData);
    $counter++;
    $total_students++;
}

// ==================== كتابة الإجمالي ====================
fputcsv($output, []);
fputcsv($output, ['إجمالي عدد الطلاب المصدرين: ' . $total_students]);
fputcsv($output, ['الحد الأقصى للتصدير: 10,000 طالب']);

// إغلاق الموارد
fclose($output);
$stmt->close();
$mysqli1->close();
exit;

// ==================== دالة مساعدة آمنة ====================
function getFilterNameSafe($mysqli, $type, $id) {
    $id = (int)$id; // تأكيد تحويل إلى integer
    
    switch ($type) {
        case 'college':
            $stmt = $mysqli->prepare("SELECT name FROM colleges WHERE id = ?");
            break;
        case 'section':
            $stmt = $mysqli->prepare("SELECT name FROM sections WHERE id = ?");
            break;
        case 'program':
            $stmt = $mysqli->prepare("SELECT name FROM programs WHERE id = ?");
            break;
        case 'specialization':
            $stmt = $mysqli->prepare("SELECT specialization_name_ar FROM specializations WHERE specialization_id = ?");
            break;
        case 'batch':
            $stmt = $mysqli->prepare("SELECT value FROM batches WHERE id = ?");
            break;
        default:
            return '';
    }
    
    if (!$stmt) {
        return '';
    }
    
    $stmt->bind_param('i', $id);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    $stmt->close();
    
    return $row ? array_values($row)[0] : '';
}
?>