<?php
/**
 * رفع درجات الفصل الدراسي من ملف Excel/CSV
 * Upload Semester Grades from Excel/CSV File
 * بنفس جودة وأسلوب رفع الطلاب
 */

require_once __DIR__ . '/config/config.php';
require_once __DIR__ . '/classes/Auth.php';
require_once __DIR__ . '/classes/GradeCalculator.php';
require_once __DIR__ . '/classes/ExcelReader.php';

$auth = new Auth();
if (!$auth->isLoggedIn() || (!$auth->hasPermission('enter_grades') && !$auth->hasPermission('manage_grades') && !$auth->hasRole('admin'))) {
    header('Location: index.php');
    exit;
}

$db = Database::getInstance();
$gradeCalculator = new GradeCalculator();
$entered_by = (int)($_SESSION['user_id'] ?? 0);
if ($entered_by <= 0) {
    $admin_user = $db->fetchOne("SELECT id FROM users WHERE role = 'admin' LIMIT 1");
    $entered_by = $admin_user ? (int)$admin_user['id'] : 1;
}

$college_id = isset($_GET['college_id']) ? (int)$_GET['college_id'] : 0;
$major_id = isset($_GET['major_id']) ? (int)$_GET['major_id'] : 0;
$semester_number = isset($_GET['semester_number']) ? (int)$_GET['semester_number'] : 0;
$academic_year = isset($_GET['academic_year']) ? trim($_GET['academic_year']) : '';
$semester_id = 0;

$message = '';
$message_type = 'success';

// جلب الكليات
$available_colleges = [];
if ($auth->hasRole('admin')) {
    $available_colleges = $db->fetchAll("SELECT id, name_ar FROM colleges ORDER BY name_ar");
} else {
    $user = $auth->getCurrentUser();
    if ($user && !empty($user['college_id'])) {
        $college = $db->fetchOne("SELECT id, name_ar FROM colleges WHERE id = ?", [$user['college_id']]);
        if ($college) {
            $available_colleges = [$college];
            if ($college_id <= 0) $college_id = (int)$college['id'];
        }
    }
}

// جلب التخصصات
$available_majors = [];
if ($college_id > 0 && ($auth->hasRole('admin') || $auth->belongsToCollege($college_id))) {
    $available_majors = $db->fetchAll("SELECT id, name_ar, code FROM majors WHERE college_id = ? ORDER BY name_ar", [$college_id]);
}

// جلب أرقام الفصول
$available_semester_numbers = [];
if ($major_id > 0) {
    $rows = $db->fetchAll("SELECT DISTINCT semester_number FROM semesters WHERE major_id = ? ORDER BY semester_number", [$major_id]);
    $available_semester_numbers = array_column($rows, 'semester_number');
}

// جلب السنوات
$available_academic_years = [];
if ($major_id > 0 && $semester_number > 0) {
    $rows = $db->fetchAll("SELECT DISTINCT academic_year FROM semesters WHERE major_id = ? AND semester_number = ? ORDER BY academic_year DESC", [$major_id, $semester_number]);
    $available_academic_years = array_column($rows, 'academic_year');
}

// تحديد semester_id
if ($major_id > 0 && $semester_number > 0 && $academic_year !== '') {
    $sem = $db->fetchOne("SELECT id FROM semesters WHERE major_id = ? AND semester_number = ? AND academic_year = ?", [$major_id, $semester_number, $academic_year]);
    if ($sem) $semester_id = (int)$sem['id'];
}

// معالجة رفع الملف
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'upload_grades' && isset($_FILES['grades_file']) && $_FILES['grades_file']['error'] === UPLOAD_ERR_OK) {
    $file = $_FILES['grades_file'];
    $file_ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
    $post_semester_id = (int)($_POST['semester_id'] ?? 0);
    $post_major_id = (int)($_POST['major_id'] ?? 0);

    $max_size = 10 * 1024 * 1024; // 10MB
    if ($file['size'] > $max_size) {
        $message = '<div class="alert alert-error">خطأ: حجم الملف كبير جداً. الحد الأقصى 10MB</div>';
        $message_type = 'error';
    } elseif (!in_array($file_ext, ['csv', 'xlsx', 'xls'])) {
        $message = '<div class="alert alert-error">خطأ: نوع الملف غير مدعوم. يرجى رفع ملف CSV أو Excel (.xlsx, .xls)</div>';
        $message_type = 'error';
    } elseif ($post_semester_id <= 0 || $post_major_id <= 0) {
        $message = '<div class="alert alert-error">خطأ: يرجى تحديد الفصل الدراسي بشكل صحيح</div>';
        $message_type = 'error';
    } else {
        try {
            $excelReader = new ExcelReader();
            $grades_data = $excelReader->readFile($file['tmp_name'], $file_ext);

            if ($grades_data === false || empty($grades_data)) {
                $message = '<div class="alert alert-error">خطأ: فشل قراءة الملف أو الملف فارغ. يرجى التحقق من صحة الملف والتنسيق</div>';
                $message_type = 'error';
            } else {
                $success_count = 0;
                $error_count = 0;
                $skipped_count = 0;
                $errors = [];
                $skipped_students = [];

                $first_row = $grades_data[0];
                $all_keys = array_keys($first_row);
                $student_col_key = null;
                foreach ($all_keys as $h) {
                    $ht = trim($h);
                    if (in_array($ht, ['student_number', 'student_id']) || preg_match('/^(رقم[_\s]?الطالب|الرقم[_\s]?الجامعي|رقم[_\s]?جامعي)$/u', $ht) || preg_match('/student|رقم|number/i', $ht)) {
                        $student_col_key = $h;
                        break;
                    }
                }
                if (!$student_col_key) $student_col_key = $all_keys[0] ?? 'student_number';

                $course_keys = array_filter($all_keys, function($k) use ($student_col_key) {
                    return $k !== $student_col_key && trim($k) !== '';
                });
                $course_keys = array_values($course_keys);

                $courses_map = [];
                $courses = $db->fetchAll("SELECT id, code, name_ar FROM courses WHERE major_id = ? AND semester_id = ?", [$post_major_id, $post_semester_id]);
                foreach ($courses as $c) {
                    $courses_map[$c['code']] = $c;
                    $courses_map[trim($c['code'])] = $c;
                }

                $cellStr = function($v) {
                    if ($v === null || $v === '') return '';
                    if (is_numeric($v)) return (string)(int)(float)$v;
                    return trim((string)$v);
                };

                try {
                    $db->getConnection()->beginTransaction();
                    $updated_student_ids = [];

                    foreach ($grades_data as $row_index => $row) {
                        $row = array_combine(array_map('trim', array_keys($row)), array_values($row));
                        $student_number = $cellStr($row[$student_col_key] ?? '');

                        if ($student_number === '') continue;

                        $student = $db->fetchOne("SELECT id FROM students WHERE student_number = ? AND major_id = ?", [$student_number, $post_major_id]);
                        if (!$student && strpos($student_number, '-') !== false) {
                            $parts = explode('-', $student_number, 2);
                            if (count($parts) === 2) {
                                $alt = trim($parts[1]) . '-' . trim($parts[0]);
                                if ($alt !== $student_number) {
                                    $student = $db->fetchOne("SELECT id FROM students WHERE student_number = ? AND major_id = ?", [$alt, $post_major_id]);
                                }
                            }
                        }
                        if (!$student) {
                            $error_count++;
                            $errors[] = "السطر " . ($row_index + 2) . ": الطالب $student_number غير موجود في التخصص";
                            continue;
                        }
                        $student_id = (int)$student['id'];

                        foreach ($course_keys as $course_header) {
                            $course_code = trim($course_header);
                            if (!isset($courses_map[$course_code])) continue;

                            $course = $courses_map[$course_code];
                            $course_id = $course['id'];
                            $grade_value = $cellStr($row[$course_header] ?? '');
                            if ($grade_value === '') continue;

                            $grade_data = $gradeCalculator->processGradeInput($grade_value);
                            if (!$grade_data) {
                                $error_count++;
                                $errors[] = "السطر " . ($row_index + 2) . " مادة $course_code: درجة غير صالحة '$grade_value'";
                                continue;
                            }

                            $marks = $grade_data['is_text'] ? $grade_value : ($grade_data['marks'] !== null ? $grade_data['marks'] : 0);
                            $letter_grade = $grade_data['letter_grade'];
                            $points = $grade_data['points'];

                            $student_course = $db->fetchOne("SELECT id FROM student_courses WHERE student_id = ? AND course_id = ? AND semester_id = ?",
                                [$student_id, $course_id, $post_semester_id]);

                            if (!$student_course) {
                                try {
                                    $db->query("INSERT INTO student_courses (student_id, course_id, semester_id, registration_date) VALUES (?, ?, ?, CURDATE())",
                                        [$student_id, $course_id, $post_semester_id]);
                                    $student_course = $db->fetchOne("SELECT id FROM student_courses WHERE student_id = ? AND course_id = ? AND semester_id = ?",
                                        [$student_id, $course_id, $post_semester_id]);
                                } catch (PDOException $e) {
                                    $error_count++;
                                    $errors[] = "السطر " . ($row_index + 2) . ": فشل تسجيل $student_number في $course_code";
                                    continue;
                                }
                            }
                            if (!$student_course) continue;

                            $sc_id = (int)$student_course['id'];
                            $existing = $db->fetchOne("SELECT id FROM grades WHERE student_course_id = ?", [$sc_id]);

                            try {
                                if ($existing) {
                                    $db->query("UPDATE grades SET marks = ?, letter_grade = ?, points = ?, entered_by = ? WHERE student_course_id = ?",
                                        [$marks, $letter_grade, $points, $entered_by, $sc_id]);
                                } else {
                                    $db->query("INSERT INTO grades (student_course_id, student_id, course_id, semester_id, marks, letter_grade, points, entered_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
                                        [$sc_id, $student_id, $course_id, $post_semester_id, $marks, $letter_grade, $points, $entered_by]);
                                }
                                $success_count++;
                                if (!in_array($student_id, $updated_student_ids)) $updated_student_ids[] = $student_id;
                            } catch (PDOException $e) {
                                $error_count++;
                                $errors[] = "السطر " . ($row_index + 2) . " $student_number - $course_code: " . $e->getMessage();
                            }
                        }
                    }

                    foreach ($updated_student_ids as $sid) {
                        try { $gradeCalculator->updateAllGPAs($sid); } catch (Exception $e) { error_log("GPA update: " . $e->getMessage()); }
                    }

                    $db->getConnection()->commit();

                    $message = '<div class="alert alert-success">✓ تم رفع <strong>' . $success_count . '</strong> درجة بنجاح</div>';
                    if ($error_count > 0) {
                        $message .= '<div class="alert alert-warning">⚠ ' . $error_count . ' خطأ أثناء المعالجة</div>';
                        if (count($errors) > 0) {
                            $show_err = array_slice($errors, 0, 20);
                            $message .= '<div class="grade-errors-box">' . implode('<br>', array_map('htmlspecialchars', $show_err)) . '</div>';
                            if (count($errors) > 20) $message .= '<p class="more-errors">... و ' . (count($errors) - 20) . ' خطأ آخر</p>';
                        }
                    }
                } catch (Exception $e) {
                    $db->getConnection()->rollBack();
                    $message = '<div class="alert alert-error">خطأ: ' . htmlspecialchars($e->getMessage()) . '</div>';
                    $message_type = 'error';
                }
            }
        } catch (Exception $e) {
            $message = '<div class="alert alert-error">خطأ: ' . htmlspecialchars($e->getMessage()) . '<br>جرّب استخدام ملف CSV بصيغة UTF-8 أو ثبّت PhpSpreadsheet لـ Excel</div>';
            $message_type = 'error';
        }
    }
} elseif (isset($_FILES['grades_file']) && $_FILES['grades_file']['error'] !== UPLOAD_ERR_OK) {
    $msg = ['UPLOAD_ERR_INI_SIZE' => 'حجم الملف كبير جداً', 'UPLOAD_ERR_FORM_SIZE' => 'حجم الملف كبير جداً', 'UPLOAD_ERR_PARTIAL' => 'تم رفع جزء من الملف', 'UPLOAD_ERR_NO_FILE' => 'لم يتم اختيار ملف', 'UPLOAD_ERR_NO_TMP_DIR' => 'مجلد مؤقت غير موجود', 'UPLOAD_ERR_CANT_WRITE' => 'فشل الكتابة'];
    $message = '<div class="alert alert-error">' . ($msg[$_FILES['grades_file']['error']] ?? 'خطأ في رفع الملف') . '</div>';
    $message_type = 'error';
}

$page_title = 'رفع درجات الفصل من Excel/CSV';
?>
<!DOCTYPE html>
<html lang="ar" dir="rtl">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title><?php echo htmlspecialchars($page_title); ?></title>
    <?php include __DIR__ . '/includes/compatibility.php'; ?>
    <link rel="stylesheet" href="assets/css/style.css">
    <style>
        .grades-upload-page { max-width: 900px; margin: 20px auto; }
        .grades-hero { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; padding: 28px 24px; border-radius: 12px; margin-bottom: 24px; box-shadow: 0 8px 24px rgba(102,126,234,0.35); }
        .grades-hero h1 { margin: 0 0 8px 0; font-size: 1.6rem; }
        .grades-hero p { margin: 0; opacity: 0.95; font-size: 0.95rem; }
        .grades-steps { display: flex; flex-wrap: wrap; gap: 12px; margin-bottom: 20px; }
        .grades-step { flex: 1; min-width: 140px; }
        .grades-step label { display: block; margin-bottom: 6px; font-weight: 600; color: #5a67d8; }
        .grades-step select { width: 100%; padding: 10px 12px; border: 1px solid #e2e8f0; border-radius: 8px; font-size: 0.95rem; }
        .grades-card { background: white; border-radius: 12px; box-shadow: 0 2px 12px rgba(0,0,0,0.08); margin-bottom: 20px; overflow: hidden; border: 2px solid #e2e8f0; }
        .grades-card-header { background: linear-gradient(135deg, #48bb78 0%, #38a169 100%); color: white; padding: 18px 22px; }
        .grades-card-header h2 { margin: 0; font-size: 1.25rem; }
        .grades-card-body { padding: 24px; }
        .grades-file-zone { border: 2px dashed #48bb78; border-radius: 10px; padding: 28px; text-align: center; background: #f0fff4; margin: 16px 0; transition: all 0.2s; }
        .grades-file-zone:hover { background: #e6fffa; border-color: #38b2ac; }
        .grades-file-zone label { cursor: pointer; font-weight: 600; color: #276749; }
        .grades-file-zone input[type="file"] { display: none; }
        .grades-file-name { margin-top: 10px; color: #2d3748; font-size: 0.9rem; }
        .grade-errors-box { background: #fff5f5; padding: 12px 16px; border-radius: 8px; margin-top: 10px; font-size: 0.88rem; max-height: 200px; overflow-y: auto; border: 1px solid #feb2b2; }
        .more-errors { color: #c53030; font-size: 0.85rem; margin: 6px 0 0 0; }
        .grades-template-box { background: #f7fafc; padding: 16px; border-radius: 8px; margin-top: 16px; border-right: 4px solid #4299e1; }
        .grades-template-box h4 { margin: 0 0 10px 0; color: #2b6cb0; }
        .grades-template-box table { border-collapse: collapse; font-size: 12px; margin-top: 8px; width: 100%; }
        .grades-template-box th, .grades-template-box td { border: 1px solid #e2e8f0; padding: 6px 10px; text-align: right; }
        .grades-template-box th { background: #edf2f7; }
        .btn-upload-grades { background: linear-gradient(135deg, #48bb78 0%, #38a169 100%); color: white !important; padding: 12px 28px; border: none; border-radius: 8px; font-size: 1rem; font-weight: 600; cursor: pointer; }
        .btn-upload-grades:hover { opacity: 0.95; }
    </style>
</head>
<body>
<?php include 'includes/header.php'; ?>

<div class="container grades-upload-page">
    <div class="grades-hero">
        <h1>📊 رفع درجات الفصل الدراسي من Excel/CSV</h1>
        <p style="margin: 8px 0;">ارفع ملفاً يحتوي على أرقام الطلاب والدرجات لكل مادة — بنفس أسلوب رفع الطلاب، مع دعم Excel و CSV والترميز العربي</p>
        <div style="margin-top: 16px; padding: 12px; background: rgba(255,255,255,0.15); border-radius: 8px;">
            <strong style="display: block; margin-bottom: 8px;">💡 لتجنب مشاكل الترميز والأحرف العربية:</strong>
            <a href="paste_grades_list.php" class="btn" style="background: white; color: #667eea; padding: 10px 20px; border-radius: 6px; font-weight: 600; text-decoration: none; display: inline-block;">
                📋 لصق القائمة
            </a>
        </div>
    </div>

    <?php echo $message; ?>

    <div class="grades-card">
        <div class="grades-card-header">
            <h2>1️⃣ اختر الفصل الدراسي</h2>
        </div>
        <div class="grades-card-body">
            <form method="GET" id="grades-selection-form">
                <div class="grades-steps">
                    <div class="grades-step">
                        <label for="college_id">الكلية</label>
                        <select name="college_id" id="college_id" onchange="this.form.submit()">
                            <option value="0">-- اختر الكلية --</option>
                            <?php foreach ($available_colleges as $col): ?>
                                <option value="<?php echo (int)$col['id']; ?>" <?php echo $college_id == $col['id'] ? 'selected' : ''; ?>><?php echo htmlspecialchars($col['name_ar']); ?></option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="grades-step">
                        <label for="major_id">التخصص</label>
                        <select name="major_id" id="major_id" onchange="this.form.submit()" <?php echo $college_id <= 0 ? 'disabled' : ''; ?>>
                            <option value="0">-- اختر التخصص --</option>
                            <?php foreach ($available_majors as $maj): ?>
                                <option value="<?php echo (int)$maj['id']; ?>" <?php echo $major_id == $maj['id'] ? 'selected' : ''; ?>><?php echo htmlspecialchars($maj['name_ar']); ?></option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="grades-step">
                        <label for="semester_number">رقم الفصل</label>
                        <select name="semester_number" id="semester_number" onchange="this.form.submit()" <?php echo $major_id <= 0 ? 'disabled' : ''; ?>>
                            <option value="0">-- اختر الفصل --</option>
                            <?php foreach ($available_semester_numbers as $sn): ?>
                                <option value="<?php echo (int)$sn; ?>" <?php echo $semester_number == $sn ? 'selected' : ''; ?>>الفصل <?php echo (int)$sn; ?></option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="grades-step">
                        <label for="academic_year">السنة الدراسية</label>
                        <select name="academic_year" id="academic_year" onchange="this.form.submit()" <?php echo $semester_number <= 0 ? 'disabled' : ''; ?>>
                            <option value="">-- اختر السنة --</option>
                            <?php foreach ($available_academic_years as $ay): ?>
                                <option value="<?php echo htmlspecialchars($ay); ?>" <?php echo $academic_year === $ay ? 'selected' : ''; ?>><?php echo htmlspecialchars($ay); ?></option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                </div>
            </form>
        </div>
    </div>

    <?php if ($semester_id > 0): ?>
    <div class="grades-card">
        <div class="grades-card-header">
            <h2>2️⃣ رفع ملف الدرجات</h2>
        </div>
        <div class="grades-card-body">
            <div style="background: #fff3cd; border: 2px solid #ffc107; border-radius: 8px; padding: 16px; margin-bottom: 20px;">
                <strong style="color: #856404;">💡 نصيحة:</strong> 
                لتجنب مشاكل الترميز العربي تماماً، استخدم 
                <a href="paste_grades_list.php" style="color: #0066cc; font-weight: 600; text-decoration: underline;">
                    لصق القائمة
                </a> 
                بدلاً من رفع الملف مباشرة.
            </div>
            
            <form method="POST" enctype="multipart/form-data">
                <input type="hidden" name="action" value="upload_grades">
                <input type="hidden" name="semester_id" value="<?php echo $semester_id; ?>">
                <input type="hidden" name="major_id" value="<?php echo $major_id; ?>">

                <div class="grades-file-zone">
                    <label for="grades_file">📁 انقر لاختيار ملف Excel أو CSV</label>
                    <input type="file" name="grades_file" id="grades_file" accept=".csv,.xlsx,.xls" required
                           onchange="document.getElementById('grades-file-name').textContent = this.files[0] ? this.files[0].name : 'لم يتم اختيار ملف'">
                    <div id="grades-file-name" class="grades-file-name">لم يتم اختيار ملف</div>
                </div>

                <p><strong>تنسيق الملف:</strong> العمود الأول = رقم الطالب، الأعمدة التالية = أكواد المواد، القيم = الدرجات (رقمية أو نصية مثل بد، غياب)</p>

                <button type="submit" class="btn btn-upload-grades">📤 رفع الدرجات</button>
                <a href="dashboard.php" class="btn btn-secondary" style="margin-right:12px;">العودة</a>
            </form>

            <div class="grades-template-box">
                <h4>📋 مثال على شكل الملف</h4>
                <table>
                    <tr><th>student_number</th><th>CS101</th><th>MATH101</th><th>ENG101</th></tr>
                    <tr><td>11079388-23</td><td>85</td><td>90</td><td>75</td></tr>
                    <tr><td>11079100-23</td><td>80</td><td>بد</td><td>غياب</td></tr>
                </table>
                <p style="margin: 12px 0 0 0; font-size: 0.9rem; color: #4a5568;">
                    <a href="download_templates.php?type=grades&major_id=<?php echo $major_id; ?>&semester_id=<?php echo $semester_id; ?>" class="btn btn-secondary" style="padding: 8px 16px;">📥 تحميل قالب جاهز لهذا الفصل</a>
                </p>
            </div>
        </div>
    </div>
    <?php else: ?>
    <div class="alert alert-info">يرجى اختيار الكلية والتخصص والفصل والسنة الدراسية أولاً</div>
    <?php endif; ?>
</div>

<?php include 'includes/footer.php'; ?>
</body>
</html>
