<?php
// admin_bulk_exam_results.php
// Page for admin to view bulk results exam-wise for all students

include 'connectiondb.php';

// Fetch all exams for dropdown
$exams = [];
$exam_query = "SELECT id, exam_name FROM exams ORDER BY exam_name";
$stmt = $pdo->prepare($exam_query);
$stmt->execute();
$exams = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Handle selected exam and sorting
$selected_exam_id = isset($_GET['exam_id']) ? intval($_GET['exam_id']) : 0;
$sort_column = isset($_GET['sort_column']) ? $_GET['sort_column'] : 'student_id';
$sort_order = isset($_GET['sort_order']) && strtolower($_GET['sort_order']) === 'desc' ? 'DESC' : 'ASC';

$allowed_sort_columns = ['student_id'];
if (!in_array($sort_column, $allowed_sort_columns)) {
    $sort_column = 'student_id';
}

$results = [];
$total_questions = 0;

if ($selected_exam_id > 0) {
    // Base SQL with LEFT JOIN
    $sql = "SELECT s.id AS id, s.student_id AS student_id, s.name AS student_name, se.status, se.completed_at, se.attempts
            FROM students s
            LEFT JOIN student_exams se ON s.id = se.student_id AND se.exam_id = :exam_id
            ORDER BY s.student_id $sort_order";

    $params = ['exam_id' => $selected_exam_id];

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Get total questions count once for the exam
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM questions WHERE exam_id = ?");
    $stmt->execute([$selected_exam_id]);
    $total_questions = (int)$stmt->fetchColumn();

    // Calculate marks_obtained for each student
    foreach ($results as &$row) {
        if (is_null($row['status'])) {
            $row['marks_obtained'] = null;
        } else {
            $row['marks_obtained'] = calculate_score($pdo, $row['id'], $selected_exam_id);
        }
        $row['total_marks'] = $total_questions;
    }
    unset($row);
}

function calculate_score($pdo, $student_id, $exam_id) {
    // Get count of correct answers by comparing selected_option_id with correct option
    $stmt = $pdo->prepare("
        SELECT COUNT(*) FROM student_answers sa
        JOIN questions q ON sa.question_id = q.id
        JOIN options o ON sa.selected_option_id = o.id
        WHERE sa.student_id = ? AND q.exam_id = ? AND o.is_correct = 1
    ");
    $stmt->execute([$student_id, $exam_id]);
    $correct_answers = (int)$stmt->fetchColumn();

    return $correct_answers;
}

?>

<!DOCTYPE html>
<html>
<head>
    <title>Bulk Exam Results - Admin</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; }
        table { border-collapse: collapse; width: 100%; margin-top: 20px; }
        th, td { border: 1px solid #ccc; padding: 8px; text-align: left; }
        th { background-color: #f2f2f2; }
        select { padding: 5px; font-size: 16px; }
        input[type="submit"] { padding: 6px 12px; font-size: 16px; }
        a { text-decoration: none; color: inherit; }
    </style>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet"/>
</head>

<body>
    <?php include 'navbar.php'; ?>
    <h1>Bulk Exam Results</h1>
    <form method="get" action="admin_bulk_exam_results.php" class="mb-3">
        <label for="exam_id">Select Exam:</label>
        <select name="exam_id" id="exam_id" required>
            <option value="">-- Select Exam --</option>
            <?php foreach ($exams as $exam): ?>
                <option value="<?php echo $exam['id']; ?>" <?php if ($exam['id'] == $selected_exam_id) echo 'selected'; ?>>
                    <?php echo htmlspecialchars($exam['exam_name']); ?>
                </option>
            <?php endforeach; ?>
        </select>
        <input type="submit" value="View Results" style="margin-left: 20px;" />
    </form>

    <?php if ($selected_exam_id > 0): ?>
        <h2>Results for Exam: 
            <?php 
                foreach ($exams as $exam) {
                    if ($exam['id'] == $selected_exam_id) {
                        echo htmlspecialchars($exam['exam_name']);
                        break;
                    }
                }
            ?>
        </h2>

        <?php if (count($results) > 0): ?>
            <table>
                <thead>
                    <tr>
                        <?php
                        // Helper function to generate sortable column headers
                        function sortable_header($label, $column, $current_sort_column, $current_sort_order) {
                            $arrow = '';
                            $new_sort_order = 'asc';
                            if ($current_sort_column === $column) {
                                if ($current_sort_order === 'ASC') {
                                    $arrow = ' ▲';
                                    $new_sort_order = 'desc';
                                } else {
                                    $arrow = ' ▼';
                                    $new_sort_order = 'asc';
                                }
                            }
                            $url_params = $_GET;
                            $url_params['sort_column'] = $column;
                            $url_params['sort_order'] = $new_sort_order;
                            $query_string = http_build_query($url_params);
                            return "<a href=\"?" . htmlspecialchars($query_string) . "\">" . htmlspecialchars($label) . $arrow . "</a>";
                        }
                        ?>
                        <th><?php echo sortable_header('Student ID', 'student_id', $sort_column, $sort_order); ?></th>
                        <th>Student Name</th>
                        <th>Attempts</th>
                        <th>Status</th>
                       
                        <th>Marks Obtained</th>
                        <th>Total Marks</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($results as $row): ?>
                        <tr>
                            <td><?php echo htmlspecialchars($row['student_id']); ?></td>
                            <td><?php echo htmlspecialchars($row['student_name']); ?></td>
                            <td><?php echo htmlspecialchars($row['attempts'] ?? 0); ?></td>
                            <td>
                                <?php 
                                    if (is_null($row['status'])) {
                                        echo "Not Assigned";
                                    } else if (strtolower($row['status']) === 'completed') {
                                        echo "Completed";
                                    } else {
                                        echo htmlspecialchars($row['status']);
                                    }
                                ?>
                            </td>
                           
                            <td>
                                <?php 
                                    if (is_null($row['status'])) {
                                        echo "-";
                                    } else {
                                        echo $row['marks_obtained'];
                                    }
                                ?>
                            </td>
                            <td><?php echo $row['total_marks']; ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        <?php else: ?>
            <p>No results found for this exam.</p>
        <?php endif; ?>
    <?php endif; ?>
</body>
</html>
