<?php
// T07: display_errors removed for production safety
// error_reporting(E_ALL);
// ini_set('display_errors', 1);

// Define remark logic
function getRemark($avg) {
    if ($avg >= 85) return "Excellent";
    elseif ($avg >= 70) return "Very Good";
    elseif ($avg >= 50) return "Good";
    elseif ($avg >= 40) return "Fair";
    else return "Poor";
}

if (isset($_POST['publish_annual_result_btn'])) {
    $class = $_POST['publish_result_class'] ?? '';
    $session = $_POST['publish_result_session'] ?? '';

    if ($class == "--select--" || empty($session)) {
        $_SESSION['msg'] = "Please select a valid class and session.";
        $_SESSION['type'] = "warning";
        header("Location: publish_annual_result");
        exit();
    }

    // Fetch students with all 3 terms using totals and averages
    $stmt = $connection->prepare("
        SELECT 
            reg_number,
            name,
            class,
            session,

            -- Averages
            ROUND(MAX(CASE WHEN term = 'First Term' THEN students_sub_average END), 2) AS first_term_avg,
            ROUND(MAX(CASE WHEN term = 'Second Term' THEN students_sub_average END), 2) AS second_term_avg,
            ROUND(MAX(CASE WHEN term = 'Third Term' THEN students_sub_average END), 2) AS third_term_avg,

            -- Totals
            MAX(CASE WHEN term = 'First Term' THEN students_sub_total END) AS first_term_total,
            MAX(CASE WHEN term = 'Second Term' THEN students_sub_total END) AS second_term_total,
            MAX(CASE WHEN term = 'Third Term' THEN students_sub_total END) AS third_term_total,

            -- Overall average
            ROUND((
                COALESCE(MAX(CASE WHEN term = 'First Term' THEN students_sub_average END), 0) +
                COALESCE(MAX(CASE WHEN term = 'Second Term' THEN students_sub_average END), 0) +
                COALESCE(MAX(CASE WHEN term = 'Third Term' THEN students_sub_average END), 0)
            ) / 3, 2) AS overall_avg_marks

        FROM positions
        WHERE class = ? AND session = ?
        GROUP BY reg_number
        HAVING COUNT(DISTINCT term) = 3
        ORDER BY overall_avg_marks DESC
    ");

    if (!$stmt) {
        die("Prepare failed: " . $connection->error);
    }

    $stmt->bind_param("ss", $class, $session);
    $stmt->execute();
    $result = $stmt->get_result();

    $students = [];
    while ($row = $result->fetch_assoc()) {
        $students[] = $row;
    }

    // Get students with incomplete term results
    $incomplete_stmt = $connection->prepare("
        SELECT reg_number, name, COUNT(DISTINCT term) AS terms_present
        FROM positions
        WHERE class = ? AND session = ?
        GROUP BY reg_number
        HAVING terms_present < 3
    ");

    $incomplete_students = [];
    if ($incomplete_stmt) {
        $incomplete_stmt->bind_param("ss", $class, $session);
        $incomplete_stmt->execute();
        $incomplete_result = $incomplete_stmt->get_result();
        while ($row = $incomplete_result->fetch_assoc()) {
            $incomplete_students[] = $row;
        }
    }

    // Prepare insert into annual_result_summary
    $insert = $connection->prepare("
        INSERT INTO annual_result_summary (
            reg_number, name, class, session,
            first_term_avg, second_term_avg, third_term_avg,
            total_annual_scores, total_annual_average, overall_avg_marks,
            position, remarks, created_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
        ON DUPLICATE KEY UPDATE 
            name = VALUES(name),
            first_term_avg = VALUES(first_term_avg),
            second_term_avg = VALUES(second_term_avg),
            third_term_avg = VALUES(third_term_avg),
            total_annual_scores = VALUES(total_annual_scores),
            total_annual_average = VALUES(total_annual_average),
            overall_avg_marks = VALUES(overall_avg_marks),
            position = VALUES(position),
            remarks = VALUES(remarks),
            created_at = NOW()
    ");

    if (!$insert) {
        die("Insert prepare failed: " . $connection->error);
    }

    $rank = 1;
    foreach ($students as $i => $student) {
        if ($i > 0 && $student['overall_avg_marks'] < $students[$i - 1]['overall_avg_marks']) {
            $rank = $i + 1;
        }

        $remark = getRemark($student['overall_avg_marks']);

        // Compute total and average of all term totals
        $first_term_total = $student['first_term_total'];
        $second_term_total = $student['second_term_total'];
        $third_term_total = $student['third_term_total'];

        $total_annual_scores = $first_term_total + $second_term_total + $third_term_total;
        $total_annual_average = round($total_annual_scores / 3, 2);

        $insert->bind_param(
            "ssssddddddis",
            $student['reg_number'], $student['name'], $student['class'], $student['session'],
            $student['first_term_avg'], $student['second_term_avg'], $student['third_term_avg'],
            $total_annual_scores, $total_annual_average, $student['overall_avg_marks'],
            $rank, $remark
        );

        if (!$insert->execute()) {
            echo "Error inserting student " . $student['reg_number'] . ": " . $insert->error;
        }
    }

    // Result summary message
    $published = count($students);
    $skipped = count($incomplete_students);

    $_SESSION['msg'] = "Annual result summary published for <strong>$published</strong> students.";
    if ($skipped > 0) {
        $_SESSION['msg'] .= " <br><span class='text-warning'>Skipped $skipped students due to incomplete term results:</span><ul>";
        foreach ($incomplete_students as $s) {
            $_SESSION['msg'] .= "<li>{$s['name']} ({$s['reg_number']}) - Only {$s['terms_present']} term(s)</li>";
        }
        $_SESSION['msg'] .= "</ul>";
    }
    $_SESSION['type'] = "success";

    header("Location: publish_annual_result");
    exit();
}
?>



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

<div class='card'>
    <div class='panel panel-primary'>
        <div class='panel-heading'>
            <h4 class='text-center'>Publish Annual Results</h4>
        </div>
        <div class='panel-body'>
            <p class='text-warning'><b>NOTE: Before Publishing The Annual Result of any Class, Make Sure that All the results for each subject for the Third Term 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' id='basic-addon2'>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)){
                                                $publish_result_classes = $result['classes'];
                                                echo"
                                                    <option>{$publish_result_classes}</option>
                                                ";
                                            }
                                        }
                                    ?>
                            </select>
                        </div>
                    </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='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)) {
                                                $all_sessions = $result['sessions'];
                                                echo "<option>{$all_sessions}</option>";
                                            }
                                        }
                                        ?>
                            </select>
                        </div>
                    </div>

                    <div class='col-md-3'>
                        <input type='submit' name='publish_annual_result_btn' value='PUBLISH' class='btn btn-primary' />
                    </div>

                </div>
            </form>
            <br /> 
            <br />         
        </div>
    </div>
</div>
