<?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(ana.total_correct,0) AS total_correct,

    COALESCE(ana.total_attempted,0) AS total_attempted,

    COALESCE(ana.total_skipped,0) AS total_skipped,

    COALESCE(ana.total_repeated,0) AS total_repeated,

    COALESCE(ana.total_score,0) AS total_score,

    ROUND(
        (COALESCE(ana.total_score,0) / 
        COALESCE(qs.total_questions,1)) * 100,
    2) AS percentage,

    ROUND(COALESCE(ana.avg_time_per_question,0),2)
        AS avg_time_per_question,

    ROUND(COALESCE(ana.speed_score,0),2)
        AS speed_score,

    ROUND(COALESCE(ana.confidence_score,0),2)
        AS confidence_score,

    ROUND(COALESCE(ana.cheating_probability,0),2)
        AS cheating_probability,

    CASE
        WHEN (
            (COALESCE(ana.total_score,0) /
            COALESCE(qs.total_questions,1)) * 100
        ) < 40
        THEN 'Weak'
        ELSE 'Average/Strong'
    END AS weak_topics,

    CASE
        WHEN (
            (COALESCE(ana.total_score,0) /
            COALESCE(qs.total_questions,1)) * 100
        ) >= 70
        THEN 'Strong'
        ELSE 'Needs Improvement'
    END AS strong_topics

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

LEFT JOIN (
    SELECT
        token_id,
        COUNT(*) AS total_questions
    FROM question_set
    GROUP BY token_id
) qs
    ON qs.token_id = tm.token_id

LEFT JOIN (

    SELECT

        sal.token_id,

        SUM(sal.score) AS total_score,

        SUM(
            CASE
                WHEN sal.score > 0
                THEN 1
                ELSE 0
            END
        ) AS total_correct,

        COUNT(*) AS total_attempted,

        SUM(
            CASE
                WHEN sal.submitted_status='skipped'
                OR sal.submitted_answer IS NULL
                OR sal.submitted_answer=''
                THEN 1
                ELSE 0
            END
        ) AS total_skipped,

        SUM(
            CASE
                WHEN qs.second_attempt IS NOT NULL
                THEN 1
                ELSE 0
            END
        ) AS total_repeated,

        AVG(
            TIMESTAMPDIFF(
                SECOND,
                sal.loading_time,
                sal.time_of_submission
            )
        ) AS avg_time_per_question,

        /* SPEED SCORE */
        ROUND(
            100 - (
                AVG(
                    TIMESTAMPDIFF(
                        SECOND,
                        sal.loading_time,
                        sal.time_of_submission
                    )
                ) / 60
            ),
        2) AS speed_score,

        /* CONFIDENCE SCORE */
        ROUND(
            (
                SUM(sal.score) /
                COUNT(*)
            ) * 100
        ,2) AS confidence_score,

        /* CHEATING PROBABILITY */
        ROUND(
            (
                SUM(
                    CASE
                        WHEN TIMESTAMPDIFF(
                            SECOND,
                            sal.loading_time,
                            sal.time_of_submission
                        ) <= 5
                        THEN 1
                        ELSE 0
                    END
                )
                /
                COUNT(*)
            ) * 100
        ,2) AS cheating_probability

    FROM student_attempt_log sal

    LEFT JOIN question_set qs
        ON qs.serial = sal.question_set_ref

    WHERE sal.submitted_status IN ('submitted','skipped')

    GROUP BY sal.token_id

) ana
    ON ana.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 percentage 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 sidebar-mini layout-fixed">
<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>Correct</th>
										<th>Attempted</th>
										<th>Skipped</th>
										<th>Repeated</th>
										<th>Avg Time</th>
										<th>Speed</th>
										<th>Confidence</th>
										<th>Cheat Risk</th>
										<th>Weakness</th>
										<th>Strength</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;
                                    ?>
<td><?= $r['total_correct'] ?></td>
<td><?= $r['total_attempted'] ?></td>
<td><?= $r['total_skipped'] ?></td>
<td><?= $r['total_repeated'] ?></td>
<td><?= $r['avg_time_per_question'] ?> sec</td>

<td>
    <span class="badge badge-info">
        <?= $r['speed_score'] ?>
    </span>
</td>

<td>
    <span class="badge badge-success">
        <?= $r['confidence_score'] ?>%
    </span>
</td>

<td>
    <?php
    $risk = $r['cheating_probability'];

    if($risk >= 70){
        echo '<span class="badge badge-danger">High</span>';
    }elseif($risk >= 40){
        echo '<span class="badge badge-warning">Medium</span>';
    }else{
        echo '<span class="badge badge-success">Low</span>';
    }
    ?>
</td>

<td><?= $r['weak_topics'] ?></td>
<td><?= $r['strong_topics'] ?></td>
                                    <?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>
