<?php

// Create a new connection instance
$connection = new Connection();
$db = $connection->connect();

$student_id = $_GET['student_id'];
// $student_id = 1;
// Fetch student data
$student_query = "SELECT student_first_name, student_last_name,student_email, student_address, student_phone_1,student_phone_2 FROM student WHERE student_id = ? ";
$student_statement = $db->prepare($student_query);
$student_statement->bind_param("i", $student_id);
$student_statement->execute();
$student_result = $student_statement->get_result();
// Fetch O/L document data
// $ol_document_query = "SELECT education_doc_path FROM education WHERE student_id = ? AND education_type_id=1 AND (education_qual_id=1 OR education_qual_id=2)";
// $ol_document_statement = $db->prepare($ol_document_query);
// $ol_document_statement->bind_param("i", $student_id);
// $ol_document_statement->execute();
// $ol_document_result = $ol_document_statement->get_result();

// //  Fetching document data
// if ($ol_document_row = $ol_document_result->fetch_assoc()) {
//     $ol_certificate = $ol_document_row['education_doc_path'];
// }

// Fetch O/L document data
$ol_document_query = "SELECT 
    e.education_doc_path, 
    e.education_doc_status, 
    e.education_qual_id,
    q.education_qual -- Fetch the qualification name
FROM 
    education e
JOIN 
    education_qual q ON e.education_qual_id = q.education_qual_id
WHERE 
    e.student_id = ? 
    AND e.education_type_id = 1 
    AND e.education_qual_id IN (1, 2)";
$ol_document_statement = $db->prepare($ol_document_query);
$ol_document_statement->bind_param("i", $student_id);
$ol_document_statement->execute();
$ol_document_result = $ol_document_statement->get_result();

// Fetching all document paths and their statuses
$ol_certificates = []; // Initialize an array to store all document paths and statuses
while ($ol_document_row = $ol_document_result->fetch_assoc()) {
    if (!empty($ol_document_row['education_doc_path'])) {
        $ol_certificates[] = [
            'doc_path' => $ol_document_row['education_doc_path'],
            'doc_status' => $ol_document_row['education_doc_status'],
            'qual_id' => $ol_document_row['education_qual_id'],
            'qual_name' => $ol_document_row['education_qual'] // Add qualification ID
        ];
    }
}

// Check if we have any certificates
$has_ol_Certificates = count($ol_certificates) > 0;


// Fetch A/L document data
// $al_document_query = "SELECT education_doc_path, education_doc_status FROM education WHERE student_id = ? AND education_type_id = 1  AND(education_qual_id = 3 OR education_qual_id = 4)";
$al_document_query = "SELECT 
    e.education_doc_path, 
    e.education_doc_status, 
    e.education_qual_id,
    q.education_qual -- Fetch the qualification name
FROM 
    education e
JOIN 
    education_qual q ON e.education_qual_id = q.education_qual_id
WHERE 
    e.student_id = ? 
    AND e.education_type_id = 1 
    AND e.education_qual_id IN (3, 4)";
$al_document_statement = $db->prepare($al_document_query);
$al_document_statement->bind_param("i", $student_id);
$al_document_statement->execute();
$al_document_result = $al_document_statement->get_result();

// Fetching all document paths
$al_certificates = []; // Initialize an array to store all document paths
while ($al_document_row = $al_document_result->fetch_assoc()) {
    if (!empty($al_document_row['education_doc_path'])) {
        // $al_certificates[$al_document_row['education_doc_path']] = $al_document_row['education_doc_status'];
        $al_certificates[] = [
            'doc_path' => $al_document_row['education_doc_path'],
            'doc_status' => $al_document_row['education_doc_status'],
            'qual_id' => $al_document_row['education_qual_id'],
            'qual_name' => $al_document_row['education_qual'] // Add qualification ID
        ];
    }
}
// Check if we have any certificates
$has_al_Certificates = count($al_certificates) > 0;

// Fetch Higher Education document data
// $higher_edu_document_query = "SELECT education_doc_path, education_doc_status, education_qual_id FROM education WHERE student_id = ? AND education_type_id = 2 AND(education_qual_id = 5 OR education_qual_id = 6 OR education_qual_id = 7 OR education_qual_id = 8 OR education_qual_id = 9 OR education_qual_id = 10)";
$higher_edu_document_query = "SELECT 
    e.education_doc_path, 
    e.education_doc_status, 
    e.education_qual_id,
    q.education_qual -- Fetch the qualification name
FROM 
    education e
JOIN 
    education_qual q ON e.education_qual_id = q.education_qual_id
WHERE 
    e.student_id = ? 
    AND e.education_type_id = 2 
    AND e.education_qual_id IN (5, 6, 7, 8, 9, 10)";
$higher_edu_document_statement = $db->prepare($higher_edu_document_query);
$higher_edu_document_statement->bind_param("i", $student_id);
$higher_edu_document_statement->execute();
$higher_edu_document_result = $higher_edu_document_statement->get_result();

// Fetching all document paths
$higher_edu_certificates = []; // Initialize an array to store all document paths
while ($higher_edu_document_row = $higher_edu_document_result->fetch_assoc()) {
    if (!empty($higher_edu_document_row['education_doc_path'])) {
        // $higher_edu_certificates[$higher_edu_document_row['education_doc_path']] = $higher_edu_document_row['education_doc_status'];
        $higher_edu_certificates[] = [
            'doc_path' => $higher_edu_document_row['education_doc_path'],
            'doc_status' => $higher_edu_document_row['education_doc_status'],
            'qual_id' => $higher_edu_document_row['education_qual_id'],
            'qual_name' => $higher_edu_document_row['education_qual'] // Add qualification ID
        ];
    }
}
// Check if we have any certificates
$has_higher_edu_Certificates = count($higher_edu_certificates) > 0;

/// Fetch Other document data
$other_document_query = "SELECT 
    e.education_doc_path, 
    e.education_doc_status, 
    e.education_qual_id,
    q.education_qual -- Fetch the qualification name
FROM 
    education e
JOIN 
    education_qual q ON e.education_qual_id = q.education_qual_id
WHERE 
    e.student_id = ? 
    AND e.education_type_id = 3 
    AND e.education_qual_id = 11";
$other_document_statement = $db->prepare($other_document_query);
$other_document_statement->bind_param("i", $student_id);
$other_document_statement->execute();
$other_document_result = $other_document_statement->get_result();

// Fetching all document paths
$other_certificates = []; // Initialize an array to store all document paths
while ($other_document_row = $other_document_result->fetch_assoc()) {
    if (!empty($other_document_row['education_doc_path'])) {
        $other_certificates[] = [
            'doc_path' => $other_document_row['education_doc_path'],
            'doc_status' => $other_document_row['education_doc_status'],
            'qual_id' => $other_document_row['education_qual_id'],
            'qual_name' => $other_document_row['education_qual'] // Add qualification ID
        ];;
    }
}
// Check if we have any certificates
$has_other_Certificates = count($other_certificates) > 0;


// Fetching student data
if ($student_row = $student_result->fetch_assoc()) {
    $first_name = $student_row['student_first_name'];
    $last_name = $student_row['student_last_name'];
}



$update_successful = false;
$clear_successful = false;




// Check if the form is submitted
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (isset($_POST['accept_selected'])) {

        if (isset($_POST['selected_docs']) && !empty($_POST['selected_docs'])) {

            foreach ($_POST['selected_docs'] as $selected_doc) {
                // Sanitize the selected document URL to prevent SQL injection
                $selected_doc = htmlspecialchars($selected_doc);

                // Prepare and execute the update query
                $query = "UPDATE education SET education_doc_status = 2 WHERE student_id = ? AND education_doc_path = ?";
                $stmt = $db->prepare($query);
                if (!$stmt) {
                    // Handle query preparation error
                    echo "Error preparing query: " . $db->error;
                    exit();
                }
                $stmt->bind_param("is", $student_id, $selected_doc);
                if (!$stmt->execute()) {
                    // Handle query execution error
                    echo "Error executing query: " . $stmt->error;
                    exit();
                }
                $education['education_doc_status'] = 2;
            }

            // Redirect back to the same page after updating
            header("Location: " . $_SERVER['PHP_SELF'] . "?student_id=" . $student_id);
            exit();
        } else {
            // No documents are selected, handle it accordingly
            echo "No documents selected.";
        }
    }
    if (isset($_POST['reject_selected'])) {

        if (isset($_POST['selected_docs']) && !empty($_POST['selected_docs'])) {

            foreach ($_POST['selected_docs'] as $selected_doc) {
                // Sanitize the selected document URL to prevent SQL injection
                $selected_doc = htmlspecialchars($selected_doc);

                // Prepare and execute the update query
                $query = "UPDATE education SET education_doc_status = 3 WHERE student_id = ? AND education_doc_path = ?";
                $stmt = $db->prepare($query);
                if (!$stmt) {
                    // Handle query preparation error
                    echo "Error preparing query: " . $db->error;
                    exit();
                }
                $stmt->bind_param("is", $student_id, $selected_doc);
                if (!$stmt->execute()) {
                    // Handle query execution error
                    echo "Error executing query: " . $stmt->error;
                    exit();
                }
                $education['education_doc_status'] = 3;
            }

            // Redirect back to the same page after updating
            header("Location: " . $_SERVER['PHP_SELF'] . "?student_id=" . $student_id);
            exit();
        } else {
            // No documents are selected, handle it accordingly
            echo "No documents selected.";
        }
    }
}









// if ($_SERVER['REQUEST_METHOD'] == 'POST') {
// if (isset($_POST['update_ol_verify'])) {
// $student_id = intval($_POST['student_id']);
// // Update document_ol_verify to 1
// $sql = "UPDATE education SET education_doc_status = 2 WHERE student_id = ? AND education_type_id = ? AND
// education_qual_id = ?";
// $stmt = $db->prepare($sql);
// $stmt->bind_param("iii", $student_id, $education_type_id, $education_qual_id);

// if ($stmt->execute()) {
// $update_successful = true;
// // header("Location: view-details.php?" . $student_id);
// header("Location: " . $_SERVER['PHP_SELF'] . "?student_id=" . $student_id);
// exit();
// } else {
// echo "Error updating record: " . $db->error;
// }
// $stmt->close();
// }
// if ($_POST['clear_ol_path']) {
// // Clear the document_ol_path
// $sql = "UPDATE education SET education_doc_status = 3 WHERE student_id = ? AND
// education_type_id = ? AND education_qual_id = ?";
// $stmt = $db->prepare($sql);
// $stmt = $db->prepare($sql);
// $stmt->bind_param("iii", $student_id, $education_type_id, $education_qual_id);

// if ($stmt->execute()) {
// $clear_successful = true;
// header("Location: " . $_SERVER['PHP_SELF'] . "?student_id=" . $student_id);

// exit();
// } else {
// echo "Error updating record: " . $db->error;
// }
// $stmt->close();
// }
// if (isset($_POST['update_al_verify'])) {
// $student_id = intval($_POST['student_id']);

// // Update document_al_verify to 1
// $sql = "UPDATE education SET education_doc_verify = 1 WHERE student_id = ?";
// $stmt = $db->prepare($sql);
// $stmt->bind_param("i", $student_id);

// if ($stmt->execute()) {
// $update_successful = true;
// header("Location: " . $_SERVER['PHP_SELF'] . "?student_id=" . $student_id);
// exit();
// } else {
// echo "Error updating record: " . $db->error;
// }
// $stmt->close();
// }
// if ($_POST['clear_al_path']) {
// // Clear the document_al_path
// $sql = "UPDATE education SET education_doc_path = '' ,education_doc_verify= 2 WHERE student_id = ?";
// $stmt = $db->prepare($sql);
// $stmt->bind_param("i", $student_id);

// if ($stmt->execute()) {
// $clear_successful = true;
// header("Location: " . $_SERVER['PHP_SELF'] . "?student_id=" . $student_id);
// exit();
// } else {
// echo "Error updating record: " . $db->error;
// }
// $stmt->close();
// }
// if (isset($_POST['update_campus_verify'])) {
// // $student_id = intval($_POST['student_id']);

// // Update document_campus_verify to 1
// $sql = "UPDATE education SET education_doc_verify = 1 WHERE student_id = ?";
// $stmt = $db->prepare($sql);
// $stmt->bind_param("i", $student_id);

// if ($stmt->execute()) {
// $update_successful = true;
// header("Location: " . $_SERVER['PHP_SELF'] . "?student_id=" . $student_id);
// exit();
// } else {
// echo "Error updating record: " . $db->error;
// }
// $stmt->close();
// }
// if (isset($_POST['clear_campus_path'])) {
// // Clear the document_campus_path
// $sql = "UPDATE education SET education_doc_path = '' , education_doc_verify = 2 WHERE student_id = ?";
// $stmt = $db->prepare($sql);
// $stmt->bind_param("i", $student_id);

// if ($stmt->execute()) {
// $clear_successful = true;
// header("Location: " . $_SERVER['PHP_SELF'] . "?student_id=" . $student_id);
// exit();
// } else {
// echo "Error updating record: " . $db->error;
// }
// $stmt->close();
// }
// }