<?php
include '../includes/conn.php';
include '../includes/header.php';

requireRole('teacher');

$teacher_id = $_SESSION['user_id'] ?? 0;

// Get selected session and term from GET, fallback to null
$selected_session = isset($_GET['session_id']) && $_GET['session_id'] !== '' ? intval($_GET['session_id']) : null;
$selected_term = isset($_GET['term_id']) && $_GET['term_id'] !== '' ? intval($_GET['term_id']) : null;

// Fetch available sessions and terms for dropdown
$sessions = $pdo->query("SELECT id, name FROM sessions ORDER BY name")->fetchAll(PDO::FETCH_ASSOC);
$terms = $pdo->query("SELECT id, name FROM terms ORDER BY name")->fetchAll(PDO::FETCH_ASSOC);

/*
  Main query explanation:
  - qc: derived table with total question count per exam
  - r.score is assumed to be the number of correct answers (raw marks). We compute percentage:
      (r.score / qc.total_q) * 100
    Guard divisions by zero with qc.total_q > 0.
  - Aggregations compute average / max / min percentages and pass/fail counts (>=50% pass).
*/
$query = "
    SELECT 
        e.id AS exam_id,
        e.title,
        e.created_at,
        c.name AS class_name,
        s.name AS session_name,
        t.name AS term_name,
        COUNT(r.id) AS total_attempts,
        -- average percentage (0..100)
        COALESCE(AVG(
            CASE 
                WHEN qc.total_q > 0 THEN (r.score / qc.total_q) * 100 
                ELSE NULL
            END
        ), 0) AS avg_score_percent,
        COALESCE(MAX(
            CASE 
                WHEN qc.total_q > 0 THEN (r.score / qc.total_q) * 100 
                ELSE NULL
            END
        ), 0) AS max_score_percent,
        COALESCE(MIN(
            CASE 
                WHEN qc.total_q > 0 THEN (r.score / qc.total_q) * 100 
                ELSE NULL
            END
        ), 0) AS min_score_percent,
        -- pass / fail counts (>=50% pass)
        SUM(
            CASE 
                WHEN qc.total_q > 0 AND (r.score / qc.total_q) * 100 >= 50 THEN 1 
                ELSE 0 
            END
        ) AS passed_count,
        SUM(
            CASE 
                WHEN qc.total_q > 0 AND (r.score / qc.total_q) * 100 < 50 THEN 1 
                ELSE 0 
            END
        ) AS failed_count,
        qc.total_q
    FROM cbt_exams e
    LEFT JOIN classrooms c ON e.classroom_id = c.id
    LEFT JOIN sessions s ON e.session_id = s.id
    LEFT JOIN terms t ON e.term_id = t.id
    LEFT JOIN (
        SELECT exam_id, COUNT(*) AS total_q
        FROM cbt_questions
        GROUP BY exam_id
    ) qc ON qc.exam_id = e.id
    LEFT JOIN cbt_exam_results r ON e.id = r.exam_id
    WHERE e.teacher_id = ?
";

$params = [$teacher_id];

if ($selected_session !== null) {
    $query .= " AND e.session_id = ?";
    $params[] = $selected_session;
}

if ($selected_term !== null) {
    $query .= " AND e.term_id = ?";
    $params[] = $selected_term;
}

$query .= " GROUP BY e.id ORDER BY e.created_at DESC";

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

// Prepare chart data
$chart_labels = [];
$chart_data = [];
$pass_data = [];
$fail_data = [];

foreach ($exams as $exam) {
    $label = $exam['title'];
    if (!empty($exam['class_name'])) $label .= ' (' . $exam['class_name'] . ')';
    $chart_labels[] = $label;

    // avg_score_percent might be a float; ensure numeric
    $avg = is_null($exam['avg_score_percent']) ? 0 : (float)$exam['avg_score_percent'];
    $chart_data[] = round($avg, 2);

    $pass_data[] = (int)$exam['passed_count'];
    $fail_data[] = (int)$exam['failed_count'];
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Exam Reports</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</head>
<body class="bg-light">
<div class="container mt-5">
    <h2 class="mb-4">Exam Reports</h2>

    <!-- Filter Form -->
    <form class="row g-3 mb-4" method="get">
        <div class="col-md-4">
            <label for="session_id" class="form-label">Select Session</label>
            <select name="session_id" id="session_id" class="form-select">
                <option value="">All Sessions</option>
                <?php foreach ($sessions as $s): ?>
                    <option value="<?= $s['id'] ?>" <?= ($selected_session == $s['id']) ? 'selected' : '' ?>>
                        <?= htmlspecialchars($s['name']) ?>
                    </option>
                <?php endforeach; ?>
            </select>
        </div>
        <div class="col-md-4">
            <label for="term_id" class="form-label">Select Term</label>
            <select name="term_id" id="term_id" class="form-select">
                <option value="">All Terms</option>
                <?php foreach ($terms as $t): ?>
                    <option value="<?= $t['id'] ?>" <?= ($selected_term == $t['id']) ? 'selected' : '' ?>>
                        <?= htmlspecialchars($t['name']) ?>
                    </option>
                <?php endforeach; ?>
            </select>
        </div>
        <div class="col-md-4 align-self-end">
            <button type="submit" class="btn btn-primary">Filter</button>
        </div>
    </form>

    <?php if (!empty($exams)): ?>
        <div class="row mb-4">
            <div class="col-md-6">
                <div class="card shadow-sm">
                    <div class="card-body">
                        <h5 class="card-title">Average Score Per Exam</h5>
                        <canvas id="avgScoreChart"></canvas>
                    </div>
                </div>
            </div>

            <div class="col-md-6">
                <div class="card shadow-sm">
                    <div class="card-body">
                        <h5 class="card-title">Pass / Fail per Exam</h5>
                        <canvas id="passFailChart"></canvas>
                    </div>
                </div>
            </div>
        </div>
    <?php endif; ?>

    <!-- Exams Table -->
    <?php if (empty($exams)): ?>
        <div class="alert alert-info">No exams found.</div>
    <?php else: ?>
        <table class="table table-bordered table-striped">
            <thead class="table-dark">
                <tr>
                    <th>Exam Title</th>
                    <th>Class</th>
                    <th>Session</th>
                    <th>Term</th>
                    <th>Date Created</th>
                    <th>Total Attempts</th>
                    <th>Total Questions</th>
                    <th>Average Score</th>
                    <th>Highest</th>
                    <th>Lowest</th>
                    <th>Passed</th>
                    <th>Failed</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($exams as $exam): 
                    $avgFmt = number_format((float)$exam['avg_score_percent'], 2);
                    $maxFmt = number_format((float)$exam['max_score_percent'], 2);
                    $minFmt = number_format((float)$exam['min_score_percent'], 2);
                ?>
                    <tr>
                        <td><?= htmlspecialchars($exam['title']) ?></td>
                        <td><?= htmlspecialchars($exam['class_name']) ?></td>
                        <td><?= htmlspecialchars($exam['session_name']) ?></td>
                        <td><?= htmlspecialchars($exam['term_name']) ?></td>
                        <td><?= htmlspecialchars($exam['created_at']) ?></td>
                        <td><?= (int)$exam['total_attempts'] ?></td>
                        <td><?= (int)$exam['total_q'] ?></td>
                        <td class="<?= ($avgFmt >= 50) ? 'text-success' : 'text-danger' ?>"><?= $avgFmt ?>%</td>
                        <td><?= $maxFmt ?>%</td>
                        <td><?= $minFmt ?>%</td>
                        <td><?= (int)$exam['passed_count'] ?></td>
                        <td><?= (int)$exam['failed_count'] ?></td>
                        <td>
                            <a href="exam_details.php?exam_id=<?= $exam['exam_id'] ?>" class="btn btn-sm btn-primary">View Details</a>
                        </td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    <?php endif; ?>
</div>

<script>
const labels = <?= json_encode($chart_labels) ?>;
const avgData = <?= json_encode($chart_data) ?>;
const passData = <?= json_encode($pass_data) ?>;
const failData = <?= json_encode($fail_data) ?>;

// Average bar chart
const avgCtx = document.getElementById('avgScoreChart')?.getContext('2d');
if (avgCtx) {
    new Chart(avgCtx, {
        type: 'bar',
        data: {
            labels: labels,
            datasets: [{
                label: 'Average Score (%)',
                data: avgData,
                backgroundColor: 'rgba(13, 110, 253, 0.7)',
                borderColor: 'rgba(13, 110, 253, 1)',
                borderWidth: 1
            }]
        },
        options: {
            responsive: true,
            plugins: { legend: { display: false } },
            scales: { y: { beginAtZero: true, max: 100 } }
        }
    });
}

// Pass/Fail stacked bar chart
const pfCtx = document.getElementById('passFailChart')?.getContext('2d');
if (pfCtx) {
    new Chart(pfCtx, {
        type: 'bar',
        data: {
            labels: labels,
            datasets: [
                {
                    label: 'Passed',
                    data: passData,
                    backgroundColor: 'rgba(40, 167, 69, 0.8)'
                },
                {
                    label: 'Failed',
                    data: failData,
                    backgroundColor: 'rgba(220, 53, 69, 0.8)'
                }
            ]
        },
        options: {
            responsive: true,
            plugins: { legend: { position: 'top' } },
            scales: { x: { stacked: true }, y: { stacked: true, beginAtZero: true } }
        }
    });
}
</script>
</body>
</html>
