<?php


function createStaff($conn_rjllearning, $conn_rjllearning_specific, $workspace_id, $institution_id, $branch_id, $print_bank_details, $account_holder_name, $account_name, $account_no, $ifsc_code, $branch_address, $account_type, $adhar_card, $pan_card, $driving_licence, $driving_expiry_date, $username, $faculty_id)
{
    

    //adharcard pdf ,image
    if ($adhar_card == null) {
        $adhar_card = null;
    } else {
        $currentYear = date('Y');
        $currentMonth = date('m');
        $currentDay =  date('d');

        $directoryPathMain = "user_uploads/" . $workspace_id .  "/" . $institution_id . "/staff/important_documents/" . $currentYear . "/" . $currentMonth . "/";

        $file_name = $_FILES['adhar_card']['name'];
        $fileTmpName = $_FILES['adhar_card']["tmp_name"];
        $fileSize = $_FILES['adhar_card']["size"];
        $fileType = $_FILES['adhar_card']["type"];
        $fileError = $_FILES['adhar_card']["error"];
        $file_extension = strtolower(pathinfo($_FILES['adhar_card']['name'], PATHINFO_EXTENSION));
        $size = filesize($fileTmpName);
        // Check if it's a valid image
        $allowedTypes = [
            'image/jpeg',
            'image/png',
            'image/gif',
            'image/heic',
            'application/pdf',
            'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'application/vnd.ms-excel',
            'application/msword',
            'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
        ]; // Allowed image types
        $allowed_extensions = ['jpg', 'gif', 'png', 'heic', 'jpeg', 'pdf', 'xlsx', 'xls', 'doc', 'docx'];
        $maxFileSize = 2 * 1024 * 1024; // Maximum file size (2MB)
        checkFileValidation($fileSize, $fileType, $file_extension, $maxFileSize, $allowedTypes, $allowed_extensions);
    }

    //adharcard pdf ,image
    if ($pan_card == null) {
        $pan_card = null;
    } else {
        $currentYear = date('Y');
        $currentMonth = date('m');
        $currentDay =  date('d');

        $directoryPathMain = "user_uploads/" . $workspace_id .  "/" . $institution_id . "/staff/important_documents/" . $currentYear . "/" . $currentMonth . "/";

        $file_name = $_FILES['pan_card']['name'];
        $fileTmpName = $_FILES['pan_card']["tmp_name"];
        $fileSize = $_FILES['pan_card']["size"];
        $fileType = $_FILES['pan_card']["type"];
        $fileError = $_FILES['pan_card']["error"];
        $file_extension = strtolower(pathinfo($_FILES['pan_card']['name'], PATHINFO_EXTENSION));
        $size = filesize($fileTmpName);
        // Check if it's a valid image
        $allowedTypes = [
            'image/jpeg',
            'image/png',
            'image/gif',
            'image/heic',
            'application/pdf',
            'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'application/vnd.ms-excel',
            'application/msword',
            'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
        ]; // Allowed image types
        $allowed_extensions = ['jpg', 'gif', 'png', 'heic', 'jpeg', 'pdf', 'xlsx', 'xls', 'doc', 'docx'];
        $maxFileSize = 2 * 1024 * 1024; // Maximum file size (2MB)
        checkFileValidation($fileSize, $fileType, $file_extension, $maxFileSize, $allowedTypes, $allowed_extensions);
    }

    if ($driving_licence == null) {
        $driving_licence = null;
    } else {
        $currentYear = date('Y');
        $currentMonth = date('m');
        $currentDay =  date('d');

        $directoryPathMain = "user_uploads/" . $workspace_id .  "/" . $institution_id . "/staff/important_documents/" . $currentYear . "/" . $currentMonth . "/";

        $file_name = $_FILES['driving_licence']['name'];
        $fileTmpName = $_FILES['driving_licence']["tmp_name"];
        $fileSize = $_FILES['driving_licence']["size"];
        $fileType = $_FILES['driving_licence']["type"];
        $fileError = $_FILES['driving_licence']["error"];
        $file_extension = strtolower(pathinfo($_FILES['driving_licence']['name'], PATHINFO_EXTENSION));
        $size = filesize($fileTmpName);
        // Check if it's a valid image
        $allowedTypes = [
            'image/jpeg',
            'image/png',
            'image/gif',
            'image/heic',
            'application/pdf',
            'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'application/vnd.ms-excel',
            'application/msword',
            'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
        ]; // Allowed image types
        $allowed_extensions = ['jpg', 'gif', 'png', 'heic', 'jpeg', 'pdf', 'xlsx', 'xls', 'doc', 'docx'];
        $maxFileSize = 2 * 1024 * 1024; // Maximum file size (2MB)
        checkFileValidation($fileSize, $fileType, $file_extension, $maxFileSize, $allowedTypes, $allowed_extensions);
    }

    // Insert staff details
    $sql = "INSERT INTO	other_staff_details (institution_id, branch_id,faculty_id, print_bank_details, account_holder_name, account_name, account_no, ifsc_code, branch_address, account_type, added_by, date_created, time_created, adhar_card, pan_card, driving_licence, driving_expiry_date, status) 
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)";
    $insert_ledger = $conn_rjllearning_specific->prepare($sql);
    $insert_ledger->execute([$institution_id, $branch_id, $faculty_id, $print_bank_details, $account_holder_name, $account_name, $account_no, $ifsc_code, $branch_address, $account_type, $username, date('Y-m-d'), time(), null, null, null, $driving_expiry_date, 1]);
    $staff_id =  $conn_rjllearning_specific->lastInsertId();

    if ($adhar_card !=  null) {
        $image_name = "adharcard_" . $staff_id . "." . $file_extension; // Define the image name dynamically
        $adhar_path = $directoryPathMain . $image_name; // Create the full path for the logo
        awsS3UserUploadInsert($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $adhar_card, $adhar_path, $multiple_files_bool = false, $multiple_file_index = 0);
        $attachment_image = json_encode(array("filepath" => $adhar_path, "original_name" => $_FILES['adhar_card']['name']));
        $sql_logo = "UPDATE other_staff_details SET adhar_card = ? WHERE id= ?";
        $conn_rjllearning_specific->prepare($sql_logo)->execute(array($attachment_image, $staff_id));
    }

    if ($pan_card !=  null) {
        $pan_image_name = "pancard_" . $staff_id . "." . $file_extension; // Define the image name dynamically
        $pan_path = $directoryPathMain . $pan_image_name; // Create the full path for the logo
        awsS3UserUploadInsert($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $pan_card, $pan_path, $multiple_files_bool = false, $multiple_file_index = 0);
        $attachment_image_pan = json_encode(array("filepath" => $pan_path, "original_name" => $_FILES['pan_card']['name']));
        $sql_logo = "UPDATE other_staff_details SET pan_card = ? WHERE id= ?";
        $conn_rjllearning_specific->prepare($sql_logo)->execute(array($attachment_image_pan, $staff_id));
    }

    if ($driving_licence !=  null) {
        $driving_image_name = "drivinglicence_" . $staff_id . "." . $file_extension; // Define the image name dynamically
        $driving_path = $directoryPathMain . $driving_image_name; // Create the full path for the logo
        awsS3UserUploadInsert($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $driving_licence, $driving_path, $multiple_files_bool = false, $multiple_file_index = 0);
        $driving_attachment_image = json_encode(array("filepath" => $driving_path, "original_name" => $_FILES['driving_licence']['name']));
        $sql_logo = "UPDATE other_staff_details SET driving_licence = ? WHERE id= ?";
        $conn_rjllearning_specific->prepare($sql_logo)->execute(array($driving_attachment_image, $staff_id));
    }
    
    $sql = $conn_rjllearning_specific->prepare("SELECT role FROM faculty_info WHERE id = ? and institution_id=?");
    $sql->execute(array($faculty_id, $institution_id));
    $result = $sql->fetch();
    $faculty_role = $result['role'];

    
    // if ($faculty_role == 'teacher' or $faculty_role == 'principal') {
    //     updateUserRoleTimestamp($conn_rjllearning, $conn_rjllearning_specific, $workspace_id, $institution_id, $faculty_id);
    // }


    return $staff_id;
}


function  alterStaff(
    $conn_rjllearning,
    $conn_rjllearning_specific,
    $workspace_id,
    $institution_id,
    $branch_id,
    $print_bank_details,
    $account_holder_name,
    $account_name,
    $account_no,
    $ifsc_code,
    $branch_address,
    $account_type,
    $adhar_card,
    $pan_card,
    $driving_licence,
    $driving_expiry_date,
    $username,
    $faculty_id,
    $remove_bool_adhar,
    $remove_bool_pan,
    $remove_bool_driving
) {

    $sql = $conn_rjllearning_specific->prepare("SELECT adhar_card,pan_card,driving_licence FROM other_staff_details WHERE faculty_id = ? and institution_id=?");
    $sql->execute(array($faculty_id, $institution_id));
    $result = $sql->fetchAll();
    if (count($result) != 1) {
        errorreturn("Staff not found.");
    } else {
        $existing_adhar_card = $result[0]['adhar_card'];

        if ($existing_adhar_card !=  null) {
            $data_adhar_card = json_decode($existing_adhar_card, true);
            $adhar_file_path = $data_adhar_card['filepath'];
        }
        $existing_driving_licence = $result[0]['driving_licence'];

        if ($existing_driving_licence !=  null) {
            $data_attachment = json_decode($existing_driving_licence, true);
            $driving_licence_path = $data_attachment['filepath'];
        }
        $existing_pan_card = $result[0]['pan_card'];

        if ($existing_pan_card !=  null) {
            $data_attachment = json_decode($existing_pan_card, true);
            $pan_card_path = $data_attachment['filepath'];
        }
    }

    if ($remove_bool_adhar == 1 and $adhar_card != null) {

        if ($existing_adhar_card === null) {

            $currentYear = date('Y');
            $currentMonth = date('m');
            $currentDay =  date('d');

            $directoryPathMain =  "user_uploads/" . $workspace_id .  "/" . $institution_id . "/staff/important_documents/" . $currentYear . "/" . $currentMonth . "/";
        } else {

            $directoryPathMain = substr($adhar_file_path, 0, strrpos($adhar_file_path, '/') + 1);
        }
        $file_name = $_FILES['adhar_card']['name'];
        $fileTmpName = $_FILES['adhar_card']["tmp_name"];
        $fileSize = $_FILES['adhar_card']["size"];
        $fileType = $_FILES['adhar_card']["type"];
        $fileError = $_FILES['adhar_card']["error"];
        $file_extension = strtolower(pathinfo($_FILES['adhar_card']['name'], PATHINFO_EXTENSION));
        $size = filesize($fileTmpName);
        // Check if it's a valid image
        $allowedTypes = [
            'image/jpeg',
            'image/png',
            'image/gif',
            'image/heic',
            'application/pdf',
            'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'application/vnd.ms-excel',
            'application/msword',
            'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
        ]; // Allowed image types
        $allowed_extensions = ['jpg', 'gif', 'png', 'heic', 'jpeg', 'pdf', 'xlsx', 'xls', 'doc', 'docx'];
        $maxFileSize = 2; // Maximum file size (2MB)

        checkFileValidation($fileSize, $fileType, $file_extension, $maxFileSize, $allowedTypes, $allowed_extensions);
    }



    if ($remove_bool_pan == 1 and $pan_card != null) {

        if ($existing_pan_card === null) {

            $currentYear = date('Y');
            $currentMonth = date('m');
            $currentDay =  date('d');

            $directoryPathMain =  "user_uploads/" . $workspace_id .  "/" . $institution_id . "/staff/important_documents/" . $currentYear . "/" . $currentMonth . "/";
        } else {

            $directoryPathMain = substr($pan_card_path, 0, strrpos($pan_card_path, '/') + 1);
        }
        $file_name = $_FILES['pan_card']['name'];
        $fileTmpName = $_FILES['pan_card']["tmp_name"];
        $fileSize = $_FILES['pan_card']["size"];
        $fileType = $_FILES['pan_card']["type"];
        $fileError = $_FILES['pan_card']["error"];
        $file_extension = strtolower(pathinfo($_FILES['pan_card']['name'], PATHINFO_EXTENSION));
        $size = filesize($fileTmpName);
        // Check if it's a valid image
        $allowedTypes = [
            'image/jpeg',
            'image/png',
            'image/gif',
            'image/heic',
            'application/pdf',
            'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'application/vnd.ms-excel',
            'application/msword',
            'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
        ]; // Allowed image types
        $allowed_extensions = ['jpg', 'gif', 'png', 'heic', 'jpeg', 'pdf', 'xlsx', 'xls', 'doc', 'docx'];
        $maxFileSize = 2; // Maximum file size (2MB)
        checkFileValidation($fileSize, $fileType, $file_extension, $maxFileSize, $allowedTypes, $allowed_extensions);
    }


    if ($remove_bool_driving == 1 and $driving_licence != null) {

        if ($existing_driving_licence === null) {

            $currentYear = date('Y');
            $currentMonth = date('m');
            $currentDay =  date('d');

            $directoryPathMain =  "user_uploads/" . $workspace_id .  "/" . $institution_id . "/staff/important_documents/" . $currentYear . "/" . $currentMonth . "/";
        } else {

            $directoryPathMain = substr($driving_licence_path, 0, strrpos($driving_licence_path, '/') + 1);
        }
        $file_name = $_FILES['driving_licence']['name'];
        $fileTmpName = $_FILES['driving_licence']["tmp_name"];
        $fileSize = $_FILES['driving_licence']["size"];
        $fileType = $_FILES['driving_licence']["type"];
        $fileError = $_FILES['driving_licence']["error"];
        $file_extension = strtolower(pathinfo($_FILES['driving_licence']['name'], PATHINFO_EXTENSION));
        $size = filesize($fileTmpName);
        // Check if it's a valid image
        $allowedTypes = [
            'image/jpeg',
            'image/png',
            'image/gif',
            'image/heic',
            'application/pdf',
            'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'application/vnd.ms-excel',
            'application/msword',
            'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
        ]; // Allowed image types
        $allowed_extensions = ['jpg', 'gif', 'png', 'heic', 'jpeg', 'pdf', 'xlsx', 'xls', 'doc', 'docx'];
        $maxFileSize = 2; // Maximum file size (2MB)

        checkFileValidation($fileSize, $fileType, $file_extension, $maxFileSize, $allowedTypes, $allowed_extensions);
    }

    $sql = "Update  other_staff_details set print_bank_details = ?,account_holder_name = ?,account_name = ?,account_no = ?,ifsc_code = ?,branch_address= ?,account_type = ?,driving_expiry_date =? where faculty_id = ? and institution_id = ?";
    $insert_staff = $conn_rjllearning_specific->prepare($sql);
    $insert_staff->execute(array($print_bank_details, $account_holder_name, $account_name, $account_no, $ifsc_code, $branch_address, $account_type, $driving_expiry_date, $faculty_id, $institution_id));

    // Define file-related parameters
    $allowed_extensions = ['jpg', 'jpeg', 'png', 'pdf', 'doc', 'docx'];
    $max_file_size = 2 * 1024 * 1024; // 2MB

    // Aadhaar Card
    if ($adhar_card == null && $remove_bool_adhar == 1) {
        deleteExistingFile($conn_rjllearning_specific, $faculty_id, $institution_id, 'adhar_card', 'other_staff_details', $GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName']);
    }
    if ($adhar_card !== null) {
        uploadNewFile('adhar_card', $faculty_id, $adhar_card, 'other_staff_details', $directoryPathMain, $file_extension, $conn_rjllearning_specific, $GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName']);
    }

    // PAN Card
    if ($remove_bool_pan == 1 && $existing_pan_card != null) {
        deleteExistingFile($conn_rjllearning_specific, $faculty_id, $institution_id, 'pan_card', 'other_staff_details', $GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName']);
    }
    if ($pan_card !== null) {
        uploadNewFile('pan_card', $faculty_id, $pan_card, 'other_staff_details', $directoryPathMain, $file_extension, $conn_rjllearning_specific, $GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName']);
    }

    // Driving Licence
    if ($remove_bool_driving == 1 && $existing_driving_licence != null) {
        deleteExistingFile($conn_rjllearning_specific, $faculty_id, $institution_id, 'driving_licence', 'other_staff_details', $GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName']);
    }
    if ($driving_licence !== null) {
        uploadNewFile('driving_licence', $faculty_id, $driving_licence, 'other_staff_details', $directoryPathMain, $file_extension, $conn_rjllearning_specific, $GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName']);
    }
    
    $sql = $conn_rjllearning_specific->prepare("SELECT role FROM faculty_info WHERE id = ? and institution_id=?");
    $sql->execute(array($faculty_id, $institution_id));
    $result = $sql->fetch();
    $faculty_role = $result['role'];
    
    if ($faculty_role == 'teacher' or $faculty_role == 'principal') {
        updateUserRoleTimestamp($conn_rjllearning, $conn_rjllearning_specific, $workspace_id, $institution_id, $faculty_id);
    }
}


  // Helper function to delete existing file
function deleteExistingFile($conn, $faculty_id, $institution_id, $field, $table, $s3_bucket, $bucket_name)
    {
        $sql_image = $conn->prepare("SELECT $field FROM $table WHERE faculty_id = ? AND institution_id = ?");
        $sql_image->execute(array($faculty_id, $institution_id));
        $result_image = $sql_image->fetch();

        if ($result_image && $result_image[$field] !== null) {
            $existing_file = json_decode($result_image[$field], true);
            $file_path = $existing_file['filepath'];
            awsS3UserUploadDelete($s3_bucket, $bucket_name, $conn, $institution_id, $file_path, null, null);

            $sql = "UPDATE $table SET $field = ? WHERE faculty_id = ? AND institution_id = ?";
            $conn->prepare($sql)->execute(array(null, $faculty_id, $institution_id));
        }
}

function uploadNewFile($file_key, $faculty_id, $field, $table, $directory_path, $file_extension, $conn, $s3_bucket, $bucket_name)
{
   

    // Determine the correct temporary file name based on the field (Adhar Card or Pan Card)
    if ($file_key == 'adhar_card') {
         // Define the file name dynamically
        $file_name = 'adhar_card' . "_" . $faculty_id . "." . $file_extension; 
        // Create the full path
        $file_path = $directory_path . $file_name; 
        // If it's an adhar card, use the adhar_card tmp_name
        $tmp_name = isset($_FILES['adhar_card']['name']) ? $_FILES['adhar_card']['name'] : null;
    } elseif ($file_key == 'pan_card') {
        // Define the file name dynamically
        $file_name = 'pan_card' . "_" . $faculty_id . "." . $file_extension; 
        // Create the full path
        $file_path = $directory_path . $file_name; 
        // If it's a pan card, use the pan_card tmp_name
        $tmp_name = isset($_FILES['pan_card']['name']) ? $_FILES['pan_card']['name'] : null;
    } elseif ($file_key == 'driving_licence'){
         // Define the file name dynamically
        $file_name = 'driving_licence' . "_" . $faculty_id . "." . $file_extension; 
        // Create the full path
        $file_path = $directory_path . $file_name; 
        // For other fields, use the generic $field tmp_name
        $tmp_name = isset($_FILES['driving_licence']['name']) ? $_FILES['driving_licence']['name'] : null;
    }

    // Now safely pass the correct tmp_name to the S3 upload function
    awsS3UserUploadInsert($s3_bucket, $bucket_name, $field, $file_path, false, 0);

    // Encode attachment data in JSON
    $attachment_image = json_encode(array("filepath" => $file_path, "original_name" => $tmp_name));

    // Update the database
    $sql = "UPDATE $table SET $file_key = ? WHERE faculty_id = ?";
    $conn->prepare($sql)->execute(array($attachment_image, $faculty_id));
  
}

