<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);

$pageTitle = 'درجات الطلاب';
include '../../includes/header.php';

if (session_status() == PHP_SESSION_NONE) { session_start(); }
checkLogin();

if (!hasPermission('view_scores') && empty($_SESSION['student_id']) && empty($_SESSION['teacher_id'])) {
    $_SESSION['error_message'] = 'ليس لديك صلاحية للوصول إلى هذه الصفحة.';
    header('Location: ../../index.php');
    exit();
}

$db = getDB();
$pdo = method_exists($db, 'getConnection') ? $db->getConnection() : $db;

// Helpers
function gradeToNumber($grade_level) {
    $map = ['المرحلة الأولى'=>'1','المرحلة الثانية'=>'2','المرحلة الثالثة'=>'3','المرحلة الرابعة'=>'4','المرحلة الخامسة'=>'5'];
    return $map[$grade_level] ?? '1';
}

$colleges = [];
$departments = [];
try {
    $colleges = $pdo->query("SELECT College_ID, College FROM colleges ORDER BY College ASC")->fetchAll(PDO::FETCH_ASSOC);
    $departments = $pdo->query("SELECT Departement_ID, College_ID, Departement FROM departemenets ORDER BY Departement ASC")->fetchAll(PDO::FETCH_ASSOC);
} catch (Exception $e) {
    $error_msg = $e->getMessage();
}

$sel_college = $_GET['college_id'] ?? '';
$sel_dept = $_GET['department_id'] ?? '';
$sel_grade = $_GET['grade'] ?? '';
$sel_semester = $_GET['semester'] ?? '1';
$sel_year = $_GET['academic_year'] ?? '2025-2026';

$students_data = [];
$stats = ['total' => 0, 'passed' => 0, 'highest' => 0];

if (!empty($sel_dept) && !empty($sel_grade)) {
    try {
        // Fetch Students
        $stmt = $pdo->prepare("SELECT id, full_name, status FROM students WHERE Departement_ID = ? AND grade_level = ?");
        $stmt->execute([$sel_dept, $sel_grade]);
        $students = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Fetch Lectures & ECTS
        $grade_num = gradeToNumber($sel_grade);
        $stmt = $pdo->prepare("SELECT Lecture_ID, ECTS FROM lectures WHERE Department_ID = ? AND Grade = ?");
        $stmt->execute([$sel_dept, $grade_num]);
        $lectures = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        $lecture_ects = [];
        foreach ($lectures as $l) {
            $lecture_ects[$l['Lecture_ID']] = $l['ECTS'];
        }

        // Fetch Max Scores per Lecture
        $stmt = $pdo->prepare("SELECT Lecture_ID, SUM(Max_Score) as total_max FROM score_components GROUP BY Lecture_ID");
        $stmt->execute();
        $components_max = [];
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $components_max[$row['Lecture_ID']] = $row['total_max'];
        }

        // Fetch all scores for these students for the given semester & year
        $student_ids = array_column($students, 'id');
        $scores_grouped = [];
        if (!empty($student_ids)) {
            $in = str_repeat('?,', count($student_ids) - 1) . '?';
            $params = array_merge([$sel_year, $sel_semester], $student_ids);
            $stmt = $pdo->prepare("SELECT Student_ID, Lecture_ID, SUM(Score) as earned_score FROM student_scores WHERE Academic_Year = ? AND Semester = ? AND Student_ID IN ($in) GROUP BY Student_ID, Lecture_ID");
            $stmt->execute($params);
            foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $s) {
                $scores_grouped[$s['Student_ID']][$s['Lecture_ID']] = $s['earned_score'];
            }
        }

        // Calculate Weighted Averages & Pass/Fail Logic
        foreach ($students as $stu) {
            $sid = $stu['id'];
            $total_weighted_sum = 0;
            $total_ects = 0;
            $has_failed_lecture = false; // راصد رسوب الطالب في أي مادة

            foreach ($lecture_ects as $lid => $ects) {
                $max = $components_max[$lid] ?? 0;
                if ($max > 0) {
                    $earned = $scores_grouped[$sid][$lid] ?? 0;
                    $pct = ($earned / $max) * 100;
                    
                    // إذا قلت النسبة عن 50% في هذه المادة، يتم وسم الطالب كراسب
                    if ($pct < 50) {
                        $has_failed_lecture = true;
                    }

                    $total_weighted_sum += ($pct * $ects);
                    $total_ects += $ects;
                }
            }

            $avg = $total_ects > 0 ? round($total_weighted_sum / $total_ects, 2) : 0;
            
            // الطالب ناجح فقط إذا كان معدله أعلى من 50% ولم يرسب في أي مادة
            $is_passed = ($avg >= 50 && !$has_failed_lecture);

            $students_data[] = [
                'id' => $sid,
                'name' => $stu['full_name'],
                'avg' => $avg,
                'is_passed' => $is_passed,
                'total_ects' => $total_ects,
                'status' => $stu['status']
            ];

            $stats['total']++;
            if ($is_passed && $total_ects > 0) {
                $stats['passed']++;
                if ($avg > $stats['highest']) $stats['highest'] = $avg; // يتم حساب أعلى معدل للناجحين فقط
            }
        }

    } catch (Exception $e) {
        $_SESSION['error_message'] = $e->getMessage();
    }
}
?>

<div class="content-area">
    <div class="page-header d-flex justify-content-between align-items-center mb-4">
        <div>
            <h2 class="page-title">درجات الطلاب</h2>
            <p class="page-subtitle text-muted">عرض وإدارة نتائج الطلاب</p>
        </div>
    </div>

    <?php if (isset($_SESSION['success_message'])): ?>
        <div class="alert alert-success alert-dismissible fade show">
            <?php echo $_SESSION['success_message']; unset($_SESSION['success_message']); ?>
            <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
        </div>
    <?php endif; ?>
    <?php if (isset($_SESSION['error_message'])): ?>
        <div class="alert alert-danger alert-dismissible fade show">
            <?php echo $_SESSION['error_message']; unset($_SESSION['error_message']); ?>
            <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
        </div>
    <?php endif; ?>

    <div class="card shadow-sm border-0 mb-4">
        <div class="card-body">
            <form method="GET" action="" class="row g-3 align-items-end">
                <div class="col-md-2">
                    <label class="form-label">الكلية</label>
                    <select class="form-select" id="college_id" name="college_id" onchange="filterDepartments()">
                        <option value="">-- اختر --</option>
                        <?php foreach ($colleges as $c): ?>
                            <option value="<?= $c['College_ID'] ?>" <?= $sel_college == $c['College_ID'] ? 'selected' : '' ?>><?= htmlspecialchars($c['College']) ?></option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div class="col-md-3">
                    <label class="form-label">القسم</label>
                    <select class="form-select" id="department_id" name="department_id" required>
                        <option value="">-- اختر الكلية أولاً --</option>
                    </select>
                </div>
                <div class="col-md-2">
                    <label class="form-label">المرحلة</label>
                    <select class="form-select" name="grade" required>
                        <option value="">-- اختر --</option>
                        <?php foreach(['المرحلة الأولى','المرحلة الثانية','المرحلة الثالثة','المرحلة الرابعة','المرحلة الخامسة'] as $g): ?>
                            <option value="<?= $g ?>" <?= $sel_grade == $g ? 'selected' : '' ?>><?= $g ?></option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div class="col-md-2">
                    <label class="form-label">الفصل الدراسي</label>
                    <select class="form-select" name="semester" required>
                        <option value="1" <?= $sel_semester == '1' ? 'selected' : '' ?>>الفصل الأول</option>
                        <option value="2" <?= $sel_semester == '2' ? 'selected' : '' ?>>الفصل الثاني</option>
                    </select>
                </div>
                <div class="col-md-2">
                    <label class="form-label">العام الدراسي</label>
                    <input type="text" class="form-control" name="academic_year" value="<?= htmlspecialchars($sel_year) ?>" required>
                </div>
                <div class="col-md-1">
                    <button type="submit" class="btn btn-primary w-100"><i class="fas fa-search"></i> عرض</button>
                </div>
            </form>
        </div>
    </div>

    <?php if (!empty($sel_dept) && !empty($sel_grade)): ?>
        <div class="row mb-4">
            <div class="col-md-4">
                <div class="card shadow-sm border-0 bg-primary text-white">
                    <div class="card-body d-flex justify-content-between align-items-center">
                        <div>
                            <h6 class="mb-0">عدد الطلاب</h6>
                            <h3 class="mt-2 mb-0"><?= $stats['total'] ?></h3>
                        </div>
                        <i class="fas fa-users fa-3x opacity-50"></i>
                    </div>
                </div>
            </div>
            <div class="col-md-4">
                <div class="card shadow-sm border-0 bg-success text-white">
                    <div class="card-body d-flex justify-content-between align-items-center">
                        <div>
                            <h6 class="mb-0">معدل النجاح</h6>
                            <h3 class="mt-2 mb-0"><?= $stats['total'] > 0 ? round(($stats['passed'] / $stats['total']) * 100, 1) : 0 ?>%</h3>
                        </div>
                        <i class="fas fa-chart-line fa-3x opacity-50"></i>
                    </div>
                </div>
            </div>
            <div class="col-md-4">
                <div class="card shadow-sm border-0 bg-info text-white">
                    <div class="card-body d-flex justify-content-between align-items-center">
                        <div>
                            <h6 class="mb-0">أعلى معدل</h6>
                            <h3 class="mt-2 mb-0"><?= $stats['highest'] ?>%</h3>
                        </div>
                        <i class="fas fa-trophy fa-3x opacity-50"></i>
                    </div>
                </div>
            </div>
        </div>

        <div class="card shadow-sm border-0">
            <div class="card-body p-0 table-responsive">
                <table class="table table-hover align-middle table-striped mb-0">
                    <thead class="table-light">
                        <tr>
                            <th class="ps-3">#</th>
                            <th>اسم الطالب</th>
                            <th>المعدل المرجح للفصل</th>
                            <th>الحالة</th>
                            <th class="text-center">إجراءات</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (empty($students_data)): ?>
                            <tr><td colspan="5" class="text-center py-4 text-muted">لا يوجد طلاب مطابقين للبحث.</td></tr>
                        <?php else: ?>
                            <?php foreach ($students_data as $index => $s): ?>
                                <tr>
                                    <td class="ps-3"><?= $index + 1 ?></td>
                                    <td class="fw-bold"><?= htmlspecialchars($s['name']) ?></td>
                                    
                                    <td>
                                        <?php if ($s['total_ects'] == 0): ?>
                                            <span class="text-muted">---</span>
                                        <?php elseif (!$s['is_passed']): ?>
                                            <span class="text-danger fw-bold">راسب</span>
                                        <?php else: ?>
                                            <?= $s['avg'] ?>%
                                        <?php endif; ?>
                                    </td>
                                    
                                    <td>
                                        <?php if ($s['total_ects'] == 0): ?>
                                            <span class="badge bg-secondary px-3">غير متوفر</span>
                                        <?php elseif ($s['is_passed']): ?>
                                            <span class="badge bg-success px-3">ناجح</span>
                                        <?php else: ?>
                                            <span class="badge bg-danger px-3">راسب</span>
                                        <?php endif; ?>
                                    </td>
                                    
                                    <td class="text-center">
                                        <a href="student_detail.php?student_id=<?= $s['id'] ?>&dept=<?= $sel_dept ?>&grade=<?= urlencode($sel_grade) ?>&semester=<?= $sel_semester ?>&year=<?= urlencode($sel_year) ?>" class="btn btn-sm btn-outline-primary" title="عرض التفاصيل">
                                            <i class="fas fa-eye"></i>
                                        </a>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    <?php endif; ?>
</div>

<script>
const allDepartments = <?php echo json_encode($departments); ?>;
const selectedDeptId = "<?php echo $sel_dept; ?>";

function filterDepartments() {
    const collegeSelect = document.getElementById('college_id');
    const departmentSelect = document.getElementById('department_id');
    const selectedCollegeId = collegeSelect.value;
    departmentSelect.innerHTML = '<option value="">-- اختر القسم --</option>';

    if (selectedCollegeId) {
        const filteredDepts = allDepartments.filter(dept => dept.College_ID == selectedCollegeId);
        filteredDepts.forEach(dept => {
            const option = document.createElement('option');
            option.value = dept.Departement_ID;
            option.textContent = dept.Departement;
            if (dept.Departement_ID == selectedDeptId) option.selected = true;
            departmentSelect.appendChild(option);
        });
    }
}
document.addEventListener('DOMContentLoaded', function() {
    if (document.getElementById('college_id').value) filterDepartments();
});
</script>

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