<?php
session_start();
require_once "inc/connect.php";

$searchTerm = $_GET['q'] ?? '';
$results = [];

if ($searchTerm !== '') {
    $query = "
        SELECT 
            s.serial AS student_serial,
            CONCAT(s.surname, ' ', s.middlename, ' ', s.lastname) AS full_name,
            s.school_id,
            tm.token_id,
            tm.examination_id,
            et.examination_title,
            COALESCE(qs.total_questions, 0) AS total_questions,
            COALESCE(sal.total_score, 0) AS total_score,
            ROUND((COALESCE(sal.total_score, 0) / COALESCE(qs.total_questions, 1)) * 100, 2) AS percentage
        FROM student_table s
        LEFT JOIN token_management tm ON s.serial = tm.student_id
        LEFT JOIN examination_table et ON et.examination_id = tm.examination_id

        -- Join total questions per token
        LEFT JOIN (
            SELECT token_id, COUNT(*) AS total_questions
            FROM question_set
            GROUP BY token_id
        ) qs ON qs.token_id = tm.token_id

        -- Join total score per token
        LEFT JOIN (
            SELECT token_id, SUM(score) AS total_score
            FROM student_attempt_log
            WHERE status = 'open'
            GROUP BY token_id
        ) sal ON sal.token_id = tm.token_id

        WHERE 
            s.school_id LIKE CONCAT('%', ?, '%') 
            OR CONCAT(s.surname, ' ', s.middlename, ' ', s.lastname) LIKE CONCAT('%', ?, '%')
            OR tm.token_id LIKE CONCAT('%', ?, '%')
        ORDER BY total_score DESC
    ";

    $stmt = $mysqli->prepare($query);
    $stmt->bind_param("sss", $searchTerm, $searchTerm, $searchTerm);
    $stmt->execute();
    $results = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
}


?>

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Individual Result Lookup</title>
    <link rel="stylesheet" href="/plugins/fontawesome-free/css/all.min.css">
    <link rel="stylesheet" href="/dist/css/adminlte.min.css">
</head>
<body class="hold-transition layout-top-nav">
<div class="wrapper">

  <?php include 'inc/admin_nav.php'; ?>

    <div class="content-wrapper">
        <div class="content-header">
            <div class="container">
                <h1 class="m-0">Check Individual Exam Result</h1>
            </div>
        </div>

        <div class="content">
            <div class="container">
                <form method="get" class="mb-4">
                    <div class="input-group">
                        <input type="text" name="q" class="form-control" placeholder="Enter Student ID, Name or Token ID" value="<?= htmlspecialchars($searchTerm) ?>">
                        <div class="input-group-append">
                            <button class="btn btn-primary">Search</button>
                        </div>
                    </div>
                </form>

                <?php if ($searchTerm && $results): ?>
                    <div class="card">
                        <div class="card-header"><strong>Results for: <?= htmlspecialchars($searchTerm) ?></strong></div>
                        <div class="card-body table-responsive p-0">
                            <table class="table table-bordered table-hover">
                                <thead>
                                    <tr>
                                        <th>Student Name</th>
                                        <th>Student ID</th>
                                        <th>Exam</th>
                                        <th>Token</th>
                                        <th>Total Questions</th>
                                        <th>Score</th>
                                        <th>Percentage</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    $seen = [];
                                    foreach ($results as $r):
                                        $key = $r['student_serial'] . "_" . $r['examination_id'];
                                        if (isset($seen[$key])) continue; // only keep highest score
                                        $seen[$key] = true;

                                        $percent = $r['total_questions'] > 0 
                                            ? round(($r['total_score'] / $r['total_questions']) * 100, 2)
                                            : 0;
                                    ?>
                                    <tr>
                                        <td><?= htmlspecialchars($r['full_name']) ?></td>
                                        <td><?= htmlspecialchars($r['school_id']) ?></td>
                                        <td><?= htmlspecialchars($r['examination_title']) ?></td>
                                        <td><?= htmlspecialchars($r['token_id']) ?></td>
                                        <td><?= $r['total_questions'] ?></td>
                                        <td><?= $r['total_score'] ?></td>
                                        <td><?= $percent ?>%</td>
                                    </tr>
                                    <?php endforeach; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                <?php elseif ($searchTerm): ?>
                    <div class="alert alert-warning">No matching result found.</div>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

<script src="/plugins/jquery/jquery.min.js"></script>
<script src="/plugins/bootstrap/js/bootstrap.bundle.min.js"></script>
<script src="/dist/js/adminlte.min.js"></script>
</body>
</html>
