<?php
require_once('source/session.php');
require_once('source/error.php');
require_once('source/dbconfig.php');
require_once('source/function.php');
require_once('source/restrict_admin.php');

// Fetch all classes, subjects, terms, and sessions for dropdowns
$stmt_classes = $DBH->query("SELECT DISTINCT class FROM tbl_exam_class");
$classes = $stmt_classes->fetchAll(PDO::FETCH_COLUMN);

$stmt_subjects = $DBH->query("SELECT * FROM tbl_subject");
$subjects = $stmt_subjects->fetchAll(PDO::FETCH_ASSOC);

$stmt_terms = $DBH->query("SELECT DISTINCT term FROM tbl_exam_class");
$terms = $stmt_terms->fetchAll(PDO::FETCH_COLUMN);

$stmt_sessions = $DBH->query("SELECT DISTINCT session FROM tbl_exam_class");
$sessions = $stmt_sessions->fetchAll(PDO::FETCH_COLUMN);

// Handle search form submission
$search_results = [];
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $class = $_POST['class'];
    $subject_id = $_POST['subject_id'];
    $term = $_POST['term'];
    $session = $_POST['session'];

    // Build the SQL query based on search criteria
    $sql = "
        SELECT q.id, q.question_text, q.option_a, q.option_b, q.option_c, q.option_d, q.correct_answer, q.file_path,
               e.exam_name, s.subject, ec.class, ec.term, ec.session
        FROM tbl_question q
        JOIN tbl_exam_class ec ON q.exam_class_id = ec.id
        JOIN tbl_exam e ON ec.exam_id = e.id
        JOIN tbl_subject s ON q.subject_id = s.id
        WHERE 1=1
    ";

    if (!empty($class)) {
        $sql .= " AND ec.class = :class";
    }
    if (!empty($subject_id)) {
        $sql .= " AND q.subject_id = :subject_id";
    }
    if (!empty($term)) {
        $sql .= " AND ec.term = :term";
    }
    if (!empty($session)) {
        $sql .= " AND ec.session = :session";
    }

    $stmt = $DBH->prepare($sql);

    if (!empty($class)) {
        $stmt->bindParam(':class', $class);
    }
    if (!empty($subject_id)) {
        $stmt->bindParam(':subject_id', $subject_id);
    }
    if (!empty($term)) {
        $stmt->bindParam(':term', $term);
    }
    if (!empty($session)) {
        $stmt->bindParam(':session', $session);
    }

    $stmt->execute();
    $search_results = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Manage Questions - CBT Portal</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css">
    <style>
        .logo {
            text-align: center;
            margin-bottom: 20px;
        }
        .logo img {
            max-width: 150px;
        }
        .footer {
            text-align: center;
            margin-top: 20px;
            padding: 10px;
            background-color: #f8f9fa;
            border-top: 1px solid #ddd;
        }
    </style>
</head>
<body>
    <div class="container mt-5">
        <!-- Logo -->
        <div class="logo">
            <img src="/path/to/logo.png" alt="School Logo">
        </div>

        <!-- Search Form -->
        <div class="card mb-4">
            <div class="card-header">
                <h3 class="text-center">Search Questions</h3>
            </div>
            <div class="card-body">
                <form method="POST">
                    <div class="row">
                        <div class="col-md-3">
                            <div class="form-group">
                                <label>Class</label>
                                <select name="class" class="form-control">
                                    <option value="">-- Select Class --</option>
                                    <?php foreach ($classes as $class): ?>
                                        <option value="<?php echo $class; ?>"><?php echo $class; ?></option>
                                    <?php endforeach; ?>
                                </select>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="form-group">
                                <label>Subject</label>
                                <select name="subject_id" class="form-control">
                                    <option value="">-- Select Subject --</option>
                                    <?php foreach ($subjects as $subject): ?>
                                        <option value="<?php echo $subject['id']; ?>"><?php echo $subject['subject']; ?></option>
                                    <?php endforeach; ?>
                                </select>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="form-group">
                                <label>Term</label>
                                <select name="term" class="form-control">
                                    <option value="">-- Select Term --</option>
                                    <?php foreach ($terms as $term): ?>
                                        <option value="<?php echo $term; ?>"><?php echo $term; ?></option>
                                    <?php endforeach; ?>
                                </select>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="form-group">
                                <label>Session</label>
                                <select name="session" class="form-control">
                                    <option value="">-- Select Session --</option>
                                    <?php foreach ($sessions as $session): ?>
                                        <option value="<?php echo $session; ?>"><?php echo $session; ?></option>
                                    <?php endforeach; ?>
                                </select>
                            </div>
                        </div>
                    </div>
                    <div class="text-center mt-3">
                        <button type="submit" class="btn btn-primary">Search</button>
                        <button type="reset" class="btn btn-secondary">Reset</button>
                    </div>
                </form>
            </div>
        </div>

        <!-- Search Results -->
        <div class="card">
            <div class="card-header">
                <h3 class="text-center">Question List</h3>
            </div>
            <div class="card-body">
                <table class="table table-bordered">
                    <thead>
                        <tr>
                            <th>ID</th>
                            <th>Question</th>
                            <th>Options</th>
                            <th>Correct Answer</th>
                            <th>File</th>
                            <th>Exam</th>
                            <th>Subject</th>
                            <th>Class</th>
                            <th>Term</th>
                            <th>Session</th>
                            <th>Actions</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (!empty($search_results)): ?>
                            <?php foreach ($search_results as $question): ?>
                                <tr>
                                    <td><?php echo $question['id']; ?></td>
                                    <td><?php echo $question['question_text']; ?></td>
                                    <td>
                                        A: <?php echo $question['option_a']; ?><br>
                                        B: <?php echo $question['option_b']; ?><br>
                                        C: <?php echo $question['option_c']; ?><br>
                                        D: <?php echo $question['option_d']; ?>
                                    </td>
                                    <td><?php echo $question['correct_answer']; ?></td>
                                    <td>
                                        <?php if (!empty($question['file_path'])): ?>
                                            <a href="<?php echo $question['file_path']; ?>" target="_blank">View File</a>
                                        <?php else: ?>
                                            No File
                                        <?php endif; ?>
                                    </td>
                                    <td><?php echo $question['exam_name']; ?></td>
                                    <td><?php echo $question['subject']; ?></td>
                                    <td><?php echo $question['class']; ?></td>
                                    <td><?php echo $question['term']; ?></td>
                                    <td><?php echo $question['session']; ?></td>
                                    <td>
                                        <a href="edit_question.php?id=<?php echo $question['id']; ?>" class="btn btn-sm btn-warning">Edit</a>
                                        <a href="delete_question.php?id=<?php echo $question['id']; ?>" class="btn btn-sm btn-danger" onclick="return confirm('Are you sure you want to delete this question?')">Delete</a>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        <?php else: ?>
                            <tr>
                                <td colspan="11" class="text-center">No questions found. Use the search form above to filter questions.</td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>

        <!-- Footer -->
        <div class="footer">
            <p>© <?php echo date('Y'); ?> British Kiddies Model School. All rights reserved.</p>
            <p><a href="/path/to/settings.php">Settings</a></p>
        </div>
    </div>

    <!-- Bootstrap JS and dependencies -->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>