// Subject teacher
								<?php
								$selected_class = $_POST['class'] ?? '';
								$selected_subject = $_POST['subject'] ?? '';
								$subjects = [];

								if (!empty($selected_class)) {
									$stmt = $connection->prepare("SELECT DISTINCT subject FROM subject_teacher WHERE staff_id = ? AND class = ?");
									$stmt->bind_param("is", $staff_id, $selected_class);
									$stmt->execute();
									$result = $stmt->get_result();
									
									while ($row = $result->fetch_assoc()) {
										$subjects[] = $row['subject'];
									}
									$stmt->close();
								}
								?>
<?php
if (isset($_POST['result_upload_btn'])) {
    if (empty($_POST["total_num"])) {
        $_SESSION['msg'] = "Error: total_num is missing or empty.";
        $_SESSION['type'] = "error";
        header("Location: subject_teacher_upload_exam");
        exit();
    }

    $total_num = (int)$_POST["total_num"];

    for ($j = 0; $j < $total_num; $j++) {
        $student_id      = inject_checker($connection, $_POST['student_id'][$j]);
        $student_class   = inject_checker($connection, $_POST['student_class'][$j]);
        $result_term     = inject_checker($connection, $_POST['result_term'][$j]);
        $result_session  = inject_checker($connection, $_POST['result_session'][$j]);
        $result_subject  = inject_checker($connection, $_POST['result_subject'][$j]);
        $student_exam    = inject_checker($connection, $_POST['student_exam'][$j]);
        $date            = date('d/M/Y');

        // Check if result record exists
        $select_sql = "SELECT ca1, ca2 FROM results1 WHERE class = ? AND term = ? AND session = ? AND subjects = ? AND reg_number = ?";
        $stmt = mysqli_prepare($connection, $select_sql);

        if (!$stmt) {
            $_SESSION['msg'] = "Failed to prepare SELECT statement.";
            $_SESSION['type'] = "error";
            header("Location: subject_teacher_upload_exam");
            exit();
        }

        mysqli_stmt_bind_param($stmt, "sssss", $student_class, $result_term, $result_session, $result_subject, $student_id);
        mysqli_stmt_execute($stmt);
        mysqli_stmt_store_result($stmt);

        if (mysqli_stmt_num_rows($stmt) > 0) {
            mysqli_stmt_bind_result($stmt, $student_ca, $student_project);
            mysqli_stmt_fetch($stmt);
            $student_total = $student_ca + $student_project + $student_exam;

            $update_sql = "UPDATE results1 SET exam = ?, subject_total = ?, date_of_upload = ? WHERE class = ? AND term = ? AND session = ? AND subjects = ? AND reg_number = ?";
            $update_stmt = mysqli_prepare($connection, $update_sql);

            if (!$update_stmt) {
                $_SESSION['msg'] = "Failed to prepare UPDATE statement.";
                $_SESSION['type'] = "error";
                header("Location: subject_teacher_upload_exam");
                exit();
            }

            mysqli_stmt_bind_param($update_stmt, "iissssss", $student_exam, $student_total, $date, $student_class, $result_term, $result_session, $result_subject, $student_id);
            $exec = mysqli_stmt_execute($update_stmt);
            mysqli_stmt_close($update_stmt);

            if (!$exec) {
                $_SESSION['msg'] = "Failed to upload exam score. Please try again.";
                $_SESSION['type'] = "error";
                header("Location: subject_teacher_upload_exam");
                exit();
            }

        } else {
            mysqli_stmt_close($stmt);
            $_SESSION['msg'] = "CA1 and CA2 scores are missing. Upload them first.";
            $_SESSION['type'] = "error";
            header("Location: subject_teacher_upload_exam");
            exit();
        }

        mysqli_stmt_close($stmt);
    }

    // Rank update logic
    $rank_sql = "SELECT reg_number, subject_total FROM results1 WHERE subjects = ? AND class = ? AND term = ? AND session = ? ORDER BY subject_total DESC";
    $rank_stmt = mysqli_prepare($connection, $rank_sql);

    if (!$rank_stmt) {
        $_SESSION['msg'] = "Failed to prepare RANK SELECT statement.";
        $_SESSION['type'] = "error";
        header("Location: subject_teacher_upload_exam");
        exit();
    }

    mysqli_stmt_bind_param($rank_stmt, "ssss", $result_subject, $student_class, $result_term, $result_session);
    mysqli_stmt_execute($rank_stmt);
    $result = mysqli_stmt_get_result($rank_stmt);

    $i = 1;
    $previous_total = null;
    $rank = 1;

    $update_rank_sql = "UPDATE results1 SET subject_rank = ? WHERE reg_number = ?";
    $update_rank_stmt = mysqli_prepare($connection, $update_rank_sql);

    if (!$update_rank_stmt) {
        $_SESSION['msg'] = "Failed to prepare RANK UPDATE statement.";
        $_SESSION['type'] = "error";
        header("Location: subject_teacher_upload_exam");
        exit();
    }

    while ($row = mysqli_fetch_assoc($result)) {
        $reg_no = $row['reg_number'];
        $subject_total = $row['subject_total'];

        if ($subject_total != $previous_total) {
            $rank = $i;
        }

        mysqli_stmt_bind_param($update_rank_stmt, "is", $rank, $reg_no);
        mysqli_stmt_execute($update_rank_stmt);

        $i++;
        $previous_total = $subject_total;
    }

    mysqli_stmt_close($update_rank_stmt);
    mysqli_stmt_close($rank_stmt);

    $_SESSION['msg'] = "Exam scores uploaded and ranked successfully.";
    $_SESSION['type'] = "success";
    header("Location: subject_teacher_upload_exam");
    exit();
}
?>



<div class='page-title hidden-print'>
  <div>
    <h1><i class='fa fa-laptop'></i> Result Management</h1>
    <p>Upload Exam Scores</p>
  </div>
</div>

<div class='card'>
    <div class='panel panel-primary print_div'>
        <div class='panel-body'>
            <form class='noprint' method='POST' action='' id='result_upload'>
                <div class='row'>
					<div class='col-md-4'>
						<div class='input-group'>
							<span class='input-group-addon' id='basic-addon2'>Class:</span>
							<select class='form-control' name='result_upload_class' onchange='this.form.submit()' required>
								<option disabled <?= $selected_class == '' ? 'selected' : '' ?>>Select Class</option>
								<?php
								$stmt = $connection->prepare("SELECT DISTINCT class FROM subject_teacher WHERE staff_id = ?");
								$stmt->bind_param("i", $staff_id);
								$stmt->execute();
								$result = $stmt->get_result();

								while ($row = $result->fetch_assoc()) {
								    $class = htmlspecialchars($row['class']);
								    $selected = ($selected_class == $class) ? 'selected' : '';
								    echo "<option value='{$class}' $selected>{$class}</option>";
								}
								$stmt->close();
								?>
							</select>
                        </div>
                    </div>

                    <div class='col-md-4'>
                        <div class='input-group'>
                            <span class='input-group-addon' id='basic-addon2'>Subject:</span>
                            <select class='form-control' name='result_upload_subject' id='subject-select'>                                
                              <?php
								if (!empty($subjects)) {
								    echo "<option disabled>Select Subject</option>";
								    foreach ($subjects as $subj) {
								        $subj = htmlspecialchars($subj);
								        $selected = ($selected_subject == $subj) ? 'selected' : '';
								        echo "<option value='{$subj}' {$selected}>{$subj}</option>";
								    }
								} else {
								    echo "<option>Select a class first</option>";
								}
								?>
							</select>
						</div>
					</div>

					<div class='col-md-4'>
                        <div class='input-group'>
                            <span class='input-group-addon' id='basic-addon2'>Term:</span>
                            <select class='form-control' name='result_upload_term' required>
                                <?php
                                    $query = " SELECT * FROM `current_season` ";
                                    $run_query = mysqli_query($connection, $query);
                                    if(mysqli_num_rows($run_query) == 1){
                                        while($result = mysqli_fetch_assoc($run_query)){
                                            $show_current_term = $result['current_term'];
                                            echo "<option>{$show_current_term}</option>";
                                        }
                                    }
                                ?>
                            </select>
                        </div>
                        <br />
                    </div>


                    <div class='col-md-3'>
                        <div class='input-group'>
                            <span class='input-group-addon' id='basic-addon2'>Session:</span>
                            <select class='form-control' name='result_upload_session'>
                                <?php
                                    ///////////// POST ACTION TO DISPLAY CURRENT SESSION //////////////
                                    $query = " SELECT * FROM `current_season` ";
                                    $run_query = mysqli_query($connection, $query);
                                    if(mysqli_num_rows($run_query) == 1){
                                        while($result = mysqli_fetch_assoc($run_query)){
                                            $show_current_session = $result['current_session'];
                                            echo"
                                                <option>{$show_current_session}</option>
                                            ";
                                        }
                                    }
                                ?>
                            </select>
                        </div>
                    </div>

                    <div>
                        <input type='submit' name='go_upload' value='GET' class='btn btn-md btn-primary' />
                    </div>

                </div>
            </form>
            <br />
			<?php
			if (isset($_POST['go_upload'])) {
				$result_upload_class = inject_checker($connection, $_POST['result_upload_class'] ?? '');
				$result_upload_subject = inject_checker($connection, $_POST['result_upload_subject'] ?? '');
				$result_upload_term = inject_checker($connection, $_POST['result_upload_term'] ?? '');
				$result_upload_session = inject_checker($connection, $_POST['result_upload_session'] ?? '');

				$error = [];

				// Error checking for empty fields
				if ($result_upload_class === $select) {
					$error[] = "Please select the Class for result upload.";
				}
				if ($result_upload_subject === $select) {
					$error[] = "Please select the Subject for result upload.";
				}
				if ($result_upload_term === $select) {
					$error[] = "Please select the Term for result upload.";
				}
				// Optional: check session if needed
				if ($result_upload_session === $select) {
					$error[] = "Please select the Session for result upload.";
				}

				if (!empty($error)) {
					// Save error messages in session and redirect or display
					$_SESSION['msg'] = implode("<br>", $error);
					$_SESSION['type'] = "error"; // Bootstrap alert-error for errors

					// Optionally redirect or just display message below
					// header("Location: your_form_page.php");
					// exit();
				} else {
					// Prepare statement instead of direct SQL
					$sql = "
						SELECT s.name, s.reg_number, s.class, r.ca1, r.ca2
						FROM subjects s
						LEFT JOIN results1 r ON 
						    s.reg_number = r.reg_number 
						    AND s.class = r.class 
						    AND s.subjects = r.subjects 
						    AND s.term = r.term 
						    AND s.session = r.session
						WHERE s.class = ? AND s.subjects = ? AND s.term = ? AND s.session = ?
						ORDER BY s.name ASC
					";
					$stmt = $connection->prepare($sql);
					if (!$stmt) {
						$_SESSION['msg'] = "Database error: Failed to prepare statement.";
						$_SESSION['type'] = "error";
					} else {
						$stmt->bind_param("ssss", $result_upload_class, $result_upload_subject, $result_upload_term, $result_upload_session);
						$stmt->execute();
						$result = $stmt->get_result();

						if ($result->num_rows > 0) {
						    // Success message
						    $_SESSION['msg'] = "Student records loaded successfully. <br />You may now enter scores.";
						    $_SESSION['type'] = "success";

						    // Output your table and form here
						    echo "
						        <div class='row text-center'>
						            <div class='col-md-12 col-xs-12'>                            
										<h4>{$school_name}</h4>
						                <h4>EXAM SCORE SHEET</h4>
						            </div>
						            <div class='col-md-3 col-xs-3'>
						                <p><b>CLASS: </b><br>{$result_upload_class}</p>
						            </div>
						            <div class='col-md-3 col-xs-3'>
						                <p><b>SESSION: </b><br>{$result_upload_session}</p>
						            </div>
						            <div class='col-md-3 col-xs-3'>
						                <p><b>TERM: </b><br>{$result_upload_term}</p>
						            </div>
						            <div class='col-md-3 col-xs-3'>
						                <p><b>SUBJECT: </b><br>{$result_upload_subject}</p>
						            </div>
						        </div>
						        <div class='table-responsive'>
						            <form method='POST' action=''>
						                <table class='table table-bordered' id='myTable'>
						                    <thead>
						                        <tr>
						                            <th>S/N</th>
						                            <th>NAMES</th>
						                            <th>REG NO</th>
						                            <th>1st CA (20)</th>
						                            <th>2nd CA (20)</th>
						                            <th>EXAM (60)</th>
						                        </tr>
						                    </thead>
						                    <tbody>
						    ";

						    $i = 0;
						    $k = 1;
						    while ($row = $result->fetch_assoc()) {
						        $students_reg_no = htmlspecialchars($row['reg_number']);
						        $student_names = htmlspecialchars($row['name']);
						        $student_class = htmlspecialchars($row['class']);
						        $student_ca = isset($row['ca1']) ? htmlspecialchars($row['ca1']) : '0';
						        $student_project = isset($row['ca2']) ? htmlspecialchars($row['ca2']) : '0';

						        echo "
									<tr>
										<td>{$k}</td>

										<td>
											{$student_names}
											<input type='hidden' name='student_name[]' value='{$student_names}' />
										</td>

										<td>
											{$students_reg_no}
											<input type='hidden' name='student_id[]' value='{$students_reg_no}' />
										</td>

										<input type='hidden' name='student_class[]' value='{$student_class}' />
										<input type='hidden' name='result_term[]' value='{$result_upload_term}' />
										<input type='hidden' name='result_session[]' value='{$result_upload_session}' />
										<input type='hidden' name='result_subject[]' value='{$result_upload_subject}' />

										<td>
											{$student_ca}
											<input type='hidden' name='student_ca[]' value='{$student_ca}' />
										</td>

										<td>
											{$student_project}
											<input type='hidden' name='student_project[]' value='{$student_project}' />
										</td>

										<td>
											<input type='text' name='student_exam[]' required placeholder='Exam Score' />
										</td>
									</tr>

						        ";
						        $i++;
						        $k++;
						    }

						    echo "
						                    </tbody>
						                </table>
						                <br />
						                <div class='row'>
						                    <div>
						                        <input type='hidden' name='total_num' value='{$i}' />
						                    </div>
						                    <div class='col-md-4'>
						                        <input type='submit' name='result_upload_btn' id='submit' value='Upload' class='btn btn-large btn-primary login_btn text-center noprint' />
						                        <button type='button' class='btn btn-default noprint' onclick='window.print()' value='print a div!'>
						                            <span class='glyphicon glyphicon-print'></span> Print
						                        </button>
						                    </div>
						                </div>
						            </form>
						        </div>
						    ";
						} else {
						    $_SESSION['msg'] = "No records found for the selected criteria.";
						    $_SESSION['type'] = "warning";
						}
						$stmt->close();
					}
				}
			}
			?>
		</div>
    </div>
</div>
