<?php
require_once("../../public/connection.php");
require_once("../../public/functions.php");

if(isset($_POST['export_result_btn'])){
    $class = htmlentities(trim(mysqli_real_escape_string($connection, $_POST['class'] ?? '')));
    $term = htmlentities(trim(mysqli_real_escape_string($connection, $_POST['term'] ?? '')));
    $session = htmlentities(trim(mysqli_real_escape_string($connection, $_POST['session'] ?? '')));
    $subject = htmlentities(trim(mysqli_real_escape_string($connection, $_POST['subject'] ?? '')));

    if(empty($class) || empty($term) || empty($session) || empty($subject)){
        echo "<script type=\"text/javascript\">
            alert(\"Please select Class, Term, Session, and Subject.\");
            window.location = \"modules.php?excel_result_upload\";
            </script>";
        exit();
    }

    if ($subject === 'ALL') {
        $query = "SELECT * FROM subjects WHERE class = '{$class}' AND term = '{$term}' AND session = '{$session}' ORDER BY name ASC, subjects ASC";
        $clean_subject = "All_Subjects_Combination";
    } else {
        $query = "SELECT * FROM subjects WHERE class = '{$class}' AND subjects = '{$subject}' AND term = '{$term}' AND session = '{$session}' ORDER BY name ASC";
        $clean_subject = preg_replace('/[^A-Za-z0-9_\-]/', '_', $subject);
    }

    $run_query = mysqli_query($connection, $query);

    if($run_query && mysqli_num_rows($run_query) > 0){
        $clean_class = preg_replace('/[^A-Za-z0-9_\-]/', '_', $class);
        $clean_session = preg_replace('/[^A-Za-z0-9_\-]/', '_', $session);
        $clean_term = preg_replace('/[^A-Za-z0-9_\-]/', '_', $term);
        $filename = "{$clean_class}_{$clean_subject}_{$clean_term}_{$clean_session}.csv";

        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename=' . $filename);
        header('Pragma: no-cache');
        header('Expires: 0');

        $output = fopen('php://output', 'w');
        fputcsv($output, array('S/N', 'Class', 'Term', 'Session', 'Reg No', 'Name', 'Subject', '1ST CA', '2ND CA', 'Exam Score'));

        $x = 0;
        while($result = mysqli_fetch_assoc($run_query)){
            $x++;
            $reg_num = $result['reg_number'];
            $sbj_item = $result['subjects'];
            $res_q = mysqli_query($connection, "SELECT ca1, ca2, exam FROM results1 WHERE class = '{$class}' AND term = '{$term}' AND session = '{$session}' AND subjects = '{$sbj_item}' AND reg_number = '{$reg_num}' LIMIT 1");
            $ca1 = ''; $ca2 = ''; $exam = '';
            if($res_q && $res_row = mysqli_fetch_assoc($res_q)){
                $ca1 = $res_row['ca1'] !== null ? $res_row['ca1'] : '';
                $ca2 = $res_row['ca2'] !== null ? $res_row['ca2'] : '';
                $exam = $res_row['exam'] !== null ? $res_row['exam'] : '';
            }
            fputcsv($output, array($x, $result['class'], $result['term'], $result['session'], $result['reg_number'], $result['name'], $result['subjects'], $ca1, $ca2, $exam));
        }
        fclose($output);
        exit();
    } else {
        echo "<script type=\"text/javascript\">
            alert(\"There are no registered students for the selection in the chosen term and session.\");
            window.location = \"modules.php?excel_result_upload\";
            </script>";
        exit();
    }
} else {
    header("Location: modules.php?excel_result_upload");
    exit();
}
?>