<?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: admin_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: admin_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: admin_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: admin_upload_exam");
                exit();
            }

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

        mysqli_stmt_close($stmt);
    }

    // Rank update logic
    $rank_sql = "SELECT id, 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: admin_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 id = ?";
    $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: admin_upload_exam");
        exit();
    }

    while ($row = mysqli_fetch_assoc($result)) {
        $record_id = (int)$row['id'];
        $subject_total = $row['subject_total'];

        if ($previous_total !== null && $subject_total == $previous_total) {
            // Keep same rank for tied score
        } else {
            $rank = $i;
        }

        mysqli_stmt_bind_param($update_rank_stmt, "ii", $rank, $record_id);
        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: admin_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>
    <a href='modules.php?excel_result_upload' class='btn btn-success'><i class='fa fa-file-excel-o'></i> Excel / CSV Result Upload & Export</a>
  </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' id='class-select'>
                                <option selected disabled>Select Class</option>
								<?php
								$query = "SELECT * FROM `classes`";
								$run_query = mysqli_query($connection, $query);
								if (mysqli_num_rows($run_query) > 0) {
									while ($result = mysqli_fetch_assoc($run_query)) {
										$attendance = $result['classes'];
										echo "<option value='{$attendance}'>{$attendance}</option>";
									}
								}
								?>
                            </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'>                                
                               <option selected>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>
                                <option selected disabled>Select Term</option>
								    <option>First Term</option>
								     <option>Second Term</option>
								    <option>Third 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'>
                                <option selected disabled>Select Session</option>
                  					<?php
                                        $query = "SELECT * FROM `sessions`";
                                        $run_query = mysqli_query($connection, $query);
                                        if (mysqli_num_rows($run_query) > 0) {
                                            while ($result = mysqli_fetch_assoc($run_query)) {
                                                $all_sessions = $result['sessions'];
                                                echo "<option>{$all_sessions}</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 = [];

    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.";
    if ($result_upload_session === $select) $error[] = "Please select the Session for result upload.";

    if (!empty($error)) {
        $_SESSION['msg'] = implode("<br>", $error);
        $_SESSION['type'] = "error"; 
    } else {
        // ✅ Updated SQL: Include exam score and join active students
        $sql = "
            SELECT s.name, s.reg_number, s.class, r.ca1, r.ca2, r.exam
            FROM subjects s
            INNER JOIN students st ON s.reg_number = st.reg_number
            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 = ?
              AND st.status = 'active'
            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) {
                $_SESSION['msg'] = "Student records loaded successfully. <br />You may now edit exam scores.";
                $_SESSION['type'] = "success";

                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_ca1 = isset($row['ca1']) ? htmlspecialchars($row['ca1']) : '0';
                    $student_ca2 = isset($row['ca2']) ? htmlspecialchars($row['ca2']) : '0';
                    $student_exam = isset($row['exam']) ? htmlspecialchars($row['exam']) : '';

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

                            <td class='align-middle p-1 text-nowrap'>
                                {$student_names}
                                <input type='hidden' name='student_name[]' value='{$student_names}' />
                            </td>
                            
                            <td class='align-middle p-1 text-nowrap'>
                                {$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}' />

                            <!-- View-only CA1 -->
                            <td style='width:80px;'>
                                <input type='number' 
                                       value='{$student_ca1}' 
                                       class='form-control text-center bg-light p-1' 
                                       style='width:70px;' 
                                       readonly />
                                <input type='hidden' name='student_ca1[]' value='{$student_ca1}' />
                            </td>
                            
                            <!-- View-only CA2 -->
                            <td style='width:80px;'>
                                <input type='number' 
                                       value='{$student_ca2}' 
                                       class='form-control text-center bg-light p-1' 
                                       style='width:70px;' 
                                       readonly />
                                <input type='hidden' name='student_ca2[]' value='{$student_ca2}' />
                            </td>


                            <td class='p-1 text-center align-middle'>
    <input type='number' 
           name='student_exam[]' 
           value='{$student_exam}' 
           min='0' max='60' 
           class='form-control form-control-sm text-center d-inline-block' 
           style='width:70px;' 
           required />
</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='Save Exam Scores' class='btn btn-large btn-primary login_btn text-center noprint' />						                        
                                </div>
                            </div>
                        </form>
                    </div>
                ";
            } else {
                $_SESSION['msg'] = "No active student records found for the selected criteria.";
                $_SESSION['type'] = "warning";
            }
            $stmt->close();
        }
    }
}
?>

		</div>
    </div>
</div>