<?php
///////////////////// POST ACTION TO PUBLISH STUDENTS RESULTS //////////////////
if (isset($_POST['publish_result_btn'])) {
    $publish_result_class = inject_checker($connection, $_POST['publish_result_class']);
    $publish_result_term = inject_checker($connection, $_POST['show_publish_result_term']);
    $publish_result_session = inject_checker($connection, $_POST['publish_result_session']);

    if ($publish_result_class === $select) {
        $_SESSION['msg'] = "Please Select Class to Publish Result";
        $_SESSION['type'] = "error";
        header("Location: publish_result");
        exit();
    }

    if ($publish_result_term === $select) {
        $_SESSION['msg'] = "Please Select Term to Publish Result";
        $_SESSION['type'] = "error";
        header("Location: publish_result");
        exit();
    }

    // Step 1: Get the level of the selected class
    $level_query = mysqli_query($connection, "SELECT `level` FROM `classes` WHERE `classes` = '$publish_result_class'");
    if (mysqli_num_rows($level_query) == 0) {
        $_SESSION['msg'] = "Class level not found.";
        $_SESSION['type'] = "error";
        header("Location: publish_result");
        exit();
    }

    $level_result = mysqli_fetch_assoc($level_query);
    $class_level = $level_result['level'];

    // Step 2: Get subject count for that level
    $subject_query = mysqli_query($connection, "SELECT `number_of_subject` FROM `subject_number_by_level` WHERE `level` = '$class_level'");
    if (mysqli_num_rows($subject_query) == 0) {
        $_SESSION['msg'] = "Subject number not configured for the level: $class_level.";
        $_SESSION['type'] = "error";
        header("Location: publish_result");
        exit();
    }

    $subject_result = mysqli_fetch_assoc($subject_query);
    $subject_count_required = (int) $subject_result['number_of_subject'];

    // Step 3: Get active students in class (exclude archived)
    $student_query = mysqli_query($connection, "
        SELECT * FROM `students` 
        WHERE `class` = '$publish_result_class' 
        AND `status` != 'archived'
    ");

    if (mysqli_num_rows($student_query) == 0) {
        $_SESSION['msg'] = "No active students found in class: $publish_result_class";
        $_SESSION['type'] = "error";
        header("Location: publish_result");
        exit();
    }

    while ($student = mysqli_fetch_assoc($student_query)) {
        $reg_number = $student['reg_number'];
        $get_subjects_total = 0;
        $subject_count_actual = 0;

        // Step 4: Fetch student results for term/session
        $result_query = mysqli_query($connection, "
            SELECT * FROM `results1` 
            WHERE `reg_number` = '$reg_number' 
              AND `class` = '$publish_result_class' 
              AND `term` = '$publish_result_term' 
              AND `session` = '$publish_result_session'
        ");

        if (mysqli_num_rows($result_query) == 0) {
            $_SESSION['msg'] = "Missing results for $student[firstname] $student[lastname] in $publish_result_class.";
            $_SESSION['type'] = "error";
            header("Location: publish_result");
            exit();
        }

        while ($res = mysqli_fetch_assoc($result_query)) {
            $student_subjects_total = $res['subject_total'];
            $student_name = $res['name'];
            $get_subjects_total += $student_subjects_total;
            $subject_count_actual++;
        }

        // Step 5: Validate subject count
        if ($subject_count_actual < $subject_count_required) {
            $_SESSION['msg'] = "$student_name did not register enough subjects (Registered: $subject_count_actual, Required: $subject_count_required)";
            $_SESSION['type'] = "error";
            header("Location: publish_result");
            exit();
        }

        // Step 6: Check if result already published
        $check_query = mysqli_query($connection, "
            SELECT * FROM `positions` 
            WHERE `class` = '$publish_result_class' 
              AND `reg_number` = '$reg_number' 
              AND `term` = '$publish_result_term' 
              AND `session` = '$publish_result_session'
        ");
        if (mysqli_num_rows($check_query) > 0) {
            $_SESSION['msg'] = "Result for $student_name has already been published!";
            $_SESSION['type'] = "error";
            header("Location: publish_result");
            exit();
        }

        // Step 7: Insert position
        $average = $subject_count_actual > 0 ? ($get_subjects_total / $subject_count_actual) : 0;
        $insert_position = mysqli_query($connection, "
            INSERT INTO `positions`
                (`reg_number`, `name`, `class`, `term`, `session`, `students_sub_total`, `students_sub_average`, `upload_date`) 
            VALUES
                ('$reg_number', '$student_name', '$publish_result_class', '$publish_result_term', '$publish_result_session', '$get_subjects_total', '$average', '$date')
        ");
    }

    // Step 8: Rank students (exclude archived)
    $rank_query = mysqli_query($connection, "
        SELECT * FROM `positions` 
        WHERE `class` = '$publish_result_class' 
          AND `term` = '$publish_result_term' 
          AND `session` = '$publish_result_session'
          AND `reg_number` IN (SELECT reg_number FROM students WHERE status != 'archived')
        ORDER BY `students_sub_average` DESC
    ");

    $rank = 1;
    while ($row = mysqli_fetch_assoc($rank_query)) {
        $reg = $row['reg_number'];
        mysqli_query($connection, "
            UPDATE `positions` 
            SET `class_position` = '$rank' 
            WHERE `reg_number` = '$reg' 
              AND `class` = '$publish_result_class' 
              AND `term` = '$publish_result_term' 
              AND `session` = '$publish_result_session'
        ");
        $rank++;
    }

    $_SESSION['msg'] = "Result Publishing Successful";
    $_SESSION['type'] = "success";
    header("Location: publish_result");
    exit();
}
?>

<div class='page-title'>
  <div>
    <h1><i class='fa fa-laptop'></i> Result Management</h1>
    <p>Publish Result</p>
  </div>
</div>

<div class='card'>
    <div class='panel panel-primary'>
        <div class='panel-heading'>
            <h4 class='text-center'>Publish Uploaded Results</h4>
        </div>
        <div class='panel-body'>
            <p class='text-warning'><b>NOTE: Before Publishing The Result of any Class, Make Sure that All the results for each subject for that particular Class have been Uploaded Properly. Click On Result Upload Tab to view Uploaded Results ...</b></p>
            <form method='POST' action=''>
                <div class='row'>
                    <div class='col-md-3'>
                        <div class='input-group'>
                            <span class='input-group-addon'>Class:</span>
                            <select class='form-control' name='publish_result_class'>
                                <option selected><?php echo $select; ?></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)){
                                            echo "<option>{$result['classes']}</option>";
                                        }
                                    }
                                ?>
                            </select>
                        </div>
                    </div>

                    <div class='col-md-3'>
                        <div class='input-group'>
                            <span class='input-group-addon'>Term:</span>
                            <select class='form-control' name='show_publish_result_term'>
                                <option selected>Select Current Term</option>
                                <option>First Term</option>
                                <option>Second Term</option>
                                <option>Third Term</option>
                            </select>
                        </div>
                    </div>

                    <div class='col-md-3'>
                        <div class='input-group'>
                            <span class='input-group-addon'>Session:</span>
                            <select class='form-control' name='publish_result_session'>
                                <?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)) {
                                            echo "<option>{$result['sessions']}</option>";
                                        }
                                    }
                                ?>
                            </select>
                        </div>
                    </div>

                    <div class='col-md-3'>
                        <input type='submit' name='publish_result_btn' value='PUBLISH RESULT' class='btn btn-primary' />
                    </div>
                </div>
            </form>
            <br />
        </div>
    </div>
</div>
