<?php
require_once('source/dbconfig.php');
require_once('source/function.php');
session_start();

// Check if user is logged in and has permission
if (!isset($_SESSION['user_type']) {
    header("Location: login.php");
    exit();
}

// Get filter parameters
$class = $_GET['class'] ?? '';
$term = $_GET['term'] ?? '';
$session = $_GET['session'] ?? '';

try {
    // Get all distinct subjects for the selected filters
    $subjectQuery = "SELECT DISTINCT subject FROM tbl_cbt_results WHERE 1=1";
    $params = [];
    
    if (!empty($class)) {
        $subjectQuery .= " AND class = :class";
        $params[':class'] = $class;
    }
    if (!empty($term)) {
        $subjectQuery .= " AND term = :term";
        $params[':term'] = $term;
    }
    if (!empty($session)) {
        $subjectQuery .= " AND session = :session";
        $params[':session'] = $session;
    }
    
    $stmt = $DBH->prepare($subjectQuery);
    $stmt->execute($params);
    $subjects = $stmt->fetchAll(PDO::FETCH_COLUMN);

    // Get all students with their results
    $studentQuery = "SELECT DISTINCT registration_no, name FROM tbl_cbt_results WHERE 1=1";
    if (!empty($class)) {
        $studentQuery .= " AND class = :class";
    }
    if (!empty($term)) {
        $studentQuery .= " AND term = :term";
    }
    if (!empty($session)) {
        $studentQuery .= " AND session = :session";
    }
    $studentQuery .= " ORDER BY name";
    
    $stmt = $DBH->prepare($studentQuery);
    $stmt->execute($params);
    $students = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Get results for each student
    $results = [];
    foreach ($students as $student) {
        $resultQuery = "SELECT subject, score FROM tbl_cbt_results 
                       WHERE registration_no = :reg_no";
        if (!empty($class)) {
            $resultQuery .= " AND class = :class";
        }
        if (!empty($term)) {
            $resultQuery .= " AND term = :term";
        }
        if (!empty($session)) {
            $resultQuery .= " AND session = :session";
        }
        
        $stmt = $DBH->prepare($resultQuery);
        $studentParams = [':reg_no' => $student['registration_no']] + $params;
        $stmt->execute($studentParams);
        $studentResults = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
        
        // Calculate total and pass/fail status
        $totalScore = array_sum($studentResults);
        $passFail = ($totalScore / count($studentResults)) >= 50 ? 'Pass' : 'Fail';
        
        $results[] = [
            'name' => $student['name'],
            'reg_no' => $student['registration_no'],
            'scores' => $studentResults,
            'total' => $totalScore,
            'result' => $passFail
        ];
    }

} catch (PDOException $e) {
    die("Database error: " . $e->getMessage());
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>CBT Results</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; }
        h2 { text-align: center; margin-bottom: 20px; }
        .filters { margin-bottom: 20px; padding: 10px; background: #f5f5f5; }
        table { width: 100%; border-collapse: collapse; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: center; }
        th { background-color: #f2f2f2; position: sticky; top: 0; }
        .subject-col { min-width: 80px; }
        .total-col, .result-col { font-weight: bold; }
        .pass { color: green; }
        .fail { color: red; }
    </style>
</head>
<body>
    <h2>CBT Results</h2>
    
    <div class="filters">
        <form method="get">
            <label>Class:
                <input type="text" name="class" value="<?= htmlspecialchars($class) ?>">
            </label>
            <label>Term:
                <input type="text" name="term" value="<?= htmlspecialchars($term) ?>">
            </label>
            <label>Session:
                <input type="text" name="session" value="<?= htmlspecialchars($session) ?>">
            </label>
            <button type="submit">Filter</button>
        </form>
    </div>

    <?php if (!empty($results)): ?>
    <div style="overflow-x: auto;">
        <table>
            <thead>
                <tr>
                    <th>Name</th>
                    <th>Admission No</th>
                    <?php foreach ($subjects as $subject): ?>
                        <th class="subject-col"><?= htmlspecialchars($subject) ?></th>
                    <?php endforeach; ?>
                    <th class="total-col">Total</th>
                    <th class="result-col">Result</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($results as $result): ?>
                    <tr>
                        <td><?= htmlspecialchars($result['name']) ?></td>
                        <td><?= htmlspecialchars($result['reg_no']) ?></td>
                        <?php foreach ($subjects as $subject): ?>
                            <td><?= $result['scores'][$subject] ?? '-' ?></td>
                        <?php endforeach; ?> 
                        <td><?= $result['total'] ?></td>
                        <td class="<?= strtolower($result['result']) ?>"><?= $result['result'] ?></td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
    <?php else: ?>
        <p>No results found for the selected filters.</p>
    <?php endif; ?>
</body>
</html>