<?php
// In library_function.php
function validateSubjectForClass($conn_rjllearning_specific, $class_id, $subject_id) {
    $query = "SELECT COUNT(*) FROM subject_info WHERE class_id = ? AND id = ?";
    $stmt = $conn_rjllearning_specific->prepare($query);
    $stmt->execute([$class_id, $subject_id]);

    return $stmt->fetchColumn() > 0;
}
function validateGenre($genre, $genres) {
    return in_array($genre, $genres);
}

function addLibraryRecord($conn_rjllearning_specific, $institution_id, $branch_id, $class_id, $subject_id, $barcode, $book_name, $author, $genre, $date_of_purchase, $bill, $bill_reference, $book_reference, $opening_quantity, $year_of_publication, $price, $language, $publisher, $username, $date_added, $time_created, $status, $workspace_id)
{
    if ($bill == null) {
        $bill = null;
    } else {
        $currentYear = date('Y');
        $currentMonth = date('n');
        $currentDay = date('d');

        $directoryPathMain = "user_uploads/" . $workspace_id . "/" . $institution_id . "/" . $branch_id . "/library_management/" . $currentYear . "/" . $currentMonth . "/";

        if (!file_exists($GLOBALS['base_path_to_learning'] . $directoryPathMain)) {
            mkdir($GLOBALS['base_path_to_learning'] . $directoryPathMain, 0777, true);
        }
        $file_name = $_FILES['bill']['name'];
        $fileTmpName = $_FILES['bill']["tmp_name"];
        $fileSize = $_FILES['bill']["size"];
        $fileType = $_FILES['bill']["type"];
        $file_extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));

        // Check if it's a valid file
        $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_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);

        $image_name = date('d-m-Y') . "." . $file_extension;
        $attachment = $directoryPathMain . $image_name;
        try {
            $GLOBALS['s3_learning_bucket']->putObject([
                'Bucket' => $GLOBALS['learning_bucketName'],
                'Key' => $attachment,
                'SourceFile' => $fileTmpName,
            ]);
        } catch (Exception $e) {
            errorreturn($e->getMessage());
        }
        $bill = json_encode(["filepath" => $attachment, "original_name" => $file_name]);
    }

    // Set current_quantity to be the same as opening_quantity
    $current_quantity = $opening_quantity;

    // SQL query to insert the data into the library_management table
    $sql = "INSERT INTO library_management (
                institution_id, branch_id, class_id, subject_id, barcode, 
                book_name, author, genre, date_of_purchase, 
                bill, bill_reference, book_reference, opening_quantity, 
                current_quantity, year_of_publication, price, language, 
                publisher, added_by, last_modified_by, date_added, time_created, status
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    // Prepare the statement
    $stmt = $conn_rjllearning_specific->prepare($sql);

    // Execute the statement with the parameters
    $stmt->execute([
        $institution_id,
        $branch_id,
        $class_id,
        $subject_id,
        $barcode,
        $book_name,
        $author,
        $genre,
        $date_of_purchase,
        $bill,
        $bill_reference,
        $book_reference,
        $opening_quantity,
        $current_quantity,
        $year_of_publication,
        $price,
        $language,
        $publisher,
        $username,
        $username,
        $date_added,
        $time_created,
        $status
    ]);

    // Return the last inserted ID
    return $conn_rjllearning_specific->lastInsertId();
}




function updateLibraryRecord($conn_rjllearning_specific, $institution_id, $postdata, $branch_id, $class_id, $subject_id, $book_name, $author, $genre, $date_of_purchase, $bill, $bill_reference, $book_reference, $opening_quantity, $year_of_publication, $price, $language, $publisher, $username, $date_added, $time_created, $status, $workspace_id) {
    // Check if library_management_id is set and not empty
    if (!isset($postdata['library_management_id']) || empty($postdata['library_management_id'])) {
        errorreturn('Error in updating the library record.');
    }

    $id = $postdata['library_management_id'];
    $remove_bool = isset($postdata['remove_bool']) ? trim($postdata['remove_bool']) : 0;
    $attachment = isset($_FILES['bill']) ? $_FILES['bill'] : null;

    // Fetch existing attachment and quantities
    $res = $conn_rjllearning_specific->prepare("SELECT bill, opening_quantity FROM library_management WHERE id = ? AND institution_id = ? AND branch_id = ?");
    $res->execute([$id, $institution_id, $branch_id]);
    $result1 = $res->fetchAll();

    if (count($result1) != 1) {
        errorreturn("Library record does not exist.");
    }

    $existing_attachment = $result1[0]['bill'];
    $old_opening_quantity = $result1[0]['opening_quantity'];
    
    // *** Corrected logic for opening_quantity and current_quantity ***

    if (isset($postdata['opening_quantity']) && trim($postdata['opening_quantity']) !== "" and is_numeric($postdata['opening_quantity'])) {
        $new_opening_quantity = floatval($postdata['opening_quantity']);
    } else {
        $new_opening_quantity = 0;
    }
    if($old_opening_quantity != $new_opening_quantity) {
        $diff_current_qty = $new_opening_quantity - $old_opening_quantity;
    } else {
        $diff_current_qty = 0;
    }

    // Handle new attachment upload
    if ($attachment != null) {
        $currentYear = date('Y');
        $currentMonth = date('m');
        $currentDay = date('d');

        $directoryPathMain = "user_uploads/" . $workspace_id . "/" . $institution_id. "/" . $branch_id . "/library_management/" . $currentYear . "/" . $currentMonth . "/";
        $file_name = $attachment['name'];
        $fileTmpName = $attachment["tmp_name"];
        $fileSize = $attachment["size"];
        $fileType = $attachment["type"];
        $fileError = $attachment["error"];
        $file_extension = strtolower(pathinfo($attachment['name'], PATHINFO_EXTENSION));
        $size = filesize($fileTmpName);
        // Check if it's a valid file
        $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_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);
    }

    // $old_opening_quantity = $result1[0]['opening_quantity'];
    // // Calculate new opening_quantity and new current_quantity
    // $new_opening_quantity = $old_opening_quantity + $opening_quantity;
    // $new_current_quantity = $current_quantity + $opening_quantity;
    
    if ($existing_attachment != null) {
        $data_attachment = json_decode($existing_attachment, true);
        $filePath = $data_attachment['filepath'];
    }

    // Update other fields
    $sql_users = "UPDATE library_management SET 
        class_id = ?, subject_id = ?, book_name = ?, author = ?, genre = ?, date_of_purchase = ?, 
        bill_reference = ?, book_reference = ?, opening_quantity = ?, current_quantity = current_quantity + ?, 
        year_of_publication = ?, price = ?, language = ?, publisher = ?, last_modified_by = ?, date_added = ?, 
        time_created = ?, status = ?
    WHERE id = ? AND institution_id = ? AND branch_id = ?";

    $stmt = $conn_rjllearning_specific->prepare($sql_users);
    $stmt->execute([
        $class_id,
        $subject_id,
        $book_name,
        $author,
        $genre,
        $date_of_purchase,
        $bill_reference,
        $book_reference,
        $new_opening_quantity,
        $diff_current_qty,
        $year_of_publication,
        $price,
        $language,
        $publisher,
        $username,
        $date_added,
        $time_created,
        $status,
        $id,
        $institution_id,
        $branch_id
    ]);
    
       // Handle removal of existing attachment
    if ($remove_bool == 1 && $existing_attachment != null) {
        awsS3UserUploadDelete($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $conn_rjllearning_specific, $institution_id, $filePath, null, null);
        $sql = "UPDATE library_management SET bill = ? WHERE id = ? AND institution_id = ?";
        $conn_rjllearning_specific->prepare($sql)->execute([null, $id, $institution_id]);
    }
    
    if ($bill !=  null) {
        $image_name = "library_" . $id . "." . $file_extension; // Define the image name dynamically
        $bill_path = $directoryPathMain . $image_name; // Create the full path for the logo
        awsS3UserUploadInsert($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $bill, $bill_path, $multiple_files_bool = false, $multiple_file_index = 0);
        $attachment_image = json_encode(array("filepath" => $bill_path, "original_name" => $file_name));
        $sql_logo = "UPDATE library_management SET bill = ? WHERE id=  ? AND institution_id = ?";
        $conn_rjllearning_specific->prepare($sql_logo)->execute(array($attachment_image, $id,$institution_id));
    }

    successreturn("Updated library_management successfully.");
}


function deletelibraryDetails($conn_rjllearning_specific, $institution_id, $library_management_id)
{
    // Correct table name and use parameterized query to prevent SQL injection
    $sql = "DELETE FROM library_management WHERE id = ? AND institution_id = ?";
    $stmt = $conn_rjllearning_specific->prepare($sql);

    try {
        $stmt->execute([$library_management_id, $institution_id]);
        $count = $stmt->rowCount();

        if ($count == 1) {
            return "Library management record successfully deleted.";
        } else {
            return "No record found or record was not deleted.";
        }
    } catch (PDOException $e) {
        return "Error deleting library management record: " . $e->getMessage();
    }
}



function getlibrary($conn_rjllearning_specific, $institution_id, $search_string, $branch_id, $subject_id, $class_id, $from_date, $to_date, $limit, $offset)
{
    $sql_count = "SELECT  count(*) as total_rows FROM  library_management  where institution_id =?";
    $array_params = [$institution_id];
    if ($branch_id == 'all') {
        $sql_count .= "";
    } else if ($branch_id !=  null  && $branch_id != 'all') {
        $sql_count .= " AND branch_id = ? ";
        $array_params[] = $branch_id;
    };
    
    if ($class_id == 'all') {
        $sql_count .= "";
    } else if ($class_id !=  null  && $class_id != 'all') {
        $sql_count .= " AND class_id = ? ";
        $array_params[] = $class_id;
    }

    if ($subject_id == 'all') {
        $sql_count .= "";
    } else if ($subject_id !=  null  && $subject_id != 'all') {
        $sql_count .= " AND subject_id = ? ";
        $array_params[] = $subject_id;
    }


    if ($search_string != null) {
        $sql_count .= " AND book_name LIKE ? ";
        $array_params[] = '%' . $search_string . '%'; // Append to $array_params
    }
    
    // Add date range condition
    if ($from_date != null && $to_date != null) {
        $sql_count .= " AND date_added BETWEEN ? AND ?";
        $array_params[] = $from_date;
        $array_params[] = $to_date;
    }

    $stmt = $conn_rjllearning_specific->prepare($sql_count);
    $stmt->execute($array_params);
    $result_total = $stmt->fetchAll();
    $totalrows = $result_total[0]['total_rows'];
    

    if ($limit == 'all') {

        $sql_session = "SELECT * FROM  library_management  where institution_id =? and book_name LIKE ?";
        $array_params = [$institution_id, '%' . $search_string . '%'];
        if ($branch_id == 'all') {
            $sql_session .= "";
        } else if ($branch_id !=  null  && $branch_id != 'all') {
            $sql_session .= " AND branch_id = ? ";
            $array_params[] = $branch_id;
        }

        if ($class_id == 'all') {
            $sql_session .= "";
        } else if ($class_id !=  null  && $class_id != 'all') {
            $sql_session .= " AND class_id = ? ";
            $array_params[] = $class_id;
        }

        if ($subject_id == 'all') {
            $sql_session .= "";
        } else if ($subject_id !=  null  && $subject_id != 'all') {
            $sql_session .= " AND subject_id = ? ";
            $array_params[] = $subject_id;
        }


        if ($search_string != null) {
            $sql_session .= " AND book_name LIKE ? ";
            $array_params[] = '%' . $search_string . '%'; // Append to $array_params
        }

        // Add date range condition
        if ($from_date != null && $to_date != null) {
            $sql_session .= " AND date_added BETWEEN ? AND ?";
            $array_params[] = $from_date;
            $array_params[] = $to_date;
        }


        $sql_session .=  "order by date_added desc, time_created desc";
    } else {
        $sql_session = "SELECT * FROM library_management WHERE institution_id = ?";
        $array_params = [$institution_id];

        if ($branch_id == 'all') {
            $sql_session .= "";
        } else if ($branch_id !=  null  && $branch_id != 'all') {
            $sql_session .= " AND branch_id = ? ";
            $array_params[] = $branch_id;
        }

        if ($class_id == 'all') {
            $sql_session .= "";
        } else if ($class_id !=  null  && $class_id != 'all') {
            $sql_session .= " AND class_id = ? ";
            $array_params[] = $class_id;
        }

        if ($subject_id == 'all') {
            $sql_session .= "";
        } else if ($subject_id !=  null  && $subject_id != 'all') {
            $sql_session .= " AND subject_id = ? ";
            $array_params[] = $subject_id;
        }


        if ($search_string != null) {
            $sql_session .= " AND (book_name LIKE ? OR author LIKE ? OR genre LIKE ?)";
            $search_term = '%' . $search_string . '%';
            $array_params[] = $search_term;
            $array_params[] = $search_term;
            $array_params[] = $search_term;
        }

        // Add date range condition
        if ($from_date != null && $to_date != null) {
            $sql_session .= " AND date_created BETWEEN ? AND ?";
            $array_params[] = $from_date;
            $array_params[] = $to_date;
        }
        $sql_session .= " order by date_added desc, time_created desc ";
        $sql_session .= "LIMIT " . $offset . " ," . $limit . "";
    }
    $stmt = $conn_rjllearning_specific->prepare($sql_session);
    $stmt->execute($array_params);
    $session_rows = $stmt->fetchAll();



    $array_return = [];

    foreach ($session_rows as $rows) {

        if ($rows['bill'] != null and !is_array($rows['bill'])) {
            $data_attachment = json_decode($rows['bill'], true);
            $filePath = $data_attachment['filepath'];
            $originalName = $data_attachment['original_name'];
            $aws_attachment = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
            $final_array_attachment   = array("filepath" => $aws_attachment, "original_name" => $originalName);
        } else {
            $final_array_attachment =  null;
        }


        // Check if class_id is not null before calling getClass function
        $class_info = null;
        if (!empty($rows['class_id'])) {
            $class_info = getClass($conn_rjllearning_specific, $institution_id, $rows['class_id']);
        }

        // Check if subject_id is not null before calling getSubject function
        $subject_info = null;
        if (!empty($rows['subject_id'])) {
            $subject_info = getSubject($conn_rjllearning_specific, $institution_id, $rows['subject_id']);
        }

        $branch_info = getBranchInfo($conn_rjllearning_specific, $institution_id, $rows['branch_id']);
        $array_return[] = array(
            'book_id' => $rows['id'],
            'branch_id' => $branch_info,
            'class_info' => $class_info,
            'subject_info' => $subject_info,
            'book_name' => $rows['book_name'],
            'bill' => $final_array_attachment,
            'author' => $rows['author'],
            'genre' => $rows['genre'],
            'date_of_purchase' => $rows['date_of_purchase'],
            'bill_reference' => $rows['bill_reference'],
            'book_reference' => $rows['book_reference'],
            'opening_quantity' => $rows['opening_quantity'],
            'current_quantity' => $rows['current_quantity'],
            'year_of_publication' => $rows['year_of_publication'],
            'price' => $rows['price'],
            'language' => $rows['language'],
            'added_by' => $rows['added_by'],
            'last_modified_by' => $rows['last_modified_by'],
            'date_added' => $rows['date_added'],
            'time_created' => $rows['time_created'],
            'status' => $rows['status'],
        );
    }

    if (count($session_rows) < 50) {

        return array('total_rows' => $totalrows, 'library_info' => $array_return, 'maxreach' => 1);
    } else {
        return array('total_rows' => $totalrows, 'library_info' => $array_return, 'maxreach' => 0);
    }
}


function getSpecificlibrary($conn_rjllearning_specific, $institution_id, $branch_id, $library_management_id)
{

    $sql = $conn_rjllearning_specific->prepare("SELECT * FROM library_management where id = ? and institution_id =? AND branch_id = ?");
    $sql->execute(array($library_management_id, $institution_id, $branch_id));
    $rows = $sql->fetchAll();

    if (count($rows) == 1) {
        if ($rows[0]['bill'] != null and !is_array($rows[0]['bill'])) {
            $data_attachment = json_decode($rows[0]['bill'], true);
            $filePath = $data_attachment['filepath'];
            $originalName = $data_attachment['original_name'];
            $aws_attachment = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
            $final_array_attachment   = array("filepath" => $aws_attachment, "original_name" => $originalName);
        } else {
            $final_array_attachment =  null;
        }
        
        $class_info = null;
        $subject_info = null;

        if ($rows[0]['class_id'] !== null) { 
            $class_info = getClass($conn_rjllearning_specific, $institution_id, $rows[0]['class_id']);
        }

        if ($rows[0]['subject_id'] !== null) { 
            $subject_info = getSubject($conn_rjllearning_specific, $institution_id, $rows[0]['subject_id']);
        }

        $branch_info  = getBranchInfo($conn_rjllearning_specific, $institution_id, $rows[0]['branch_id']);
        
        return array(
            'library_management_id' => $rows[0]['id'],
            'branch_id' => $branch_info,
            'class_info' => $class_info,
            'subject_info' => $subject_info,
            'barcode' => $rows[0]['barcode'],
            'book_name' => $rows[0]['book_name'],
            'bill' => $final_array_attachment,
            'author' => $rows[0]['author'],
            'genre' => $rows[0]['genre'],
            'date_of_purchase' => $rows[0]['date_of_purchase'],
            'bill_reference' => $rows[0]['bill_reference'],
            'book_reference' => $rows[0]['book_reference'],
            'opening_quantity' => $rows[0]['opening_quantity'],
            'current_quantity' => $rows[0]['current_quantity'],
            'year_of_publication' => $rows[0]['year_of_publication'],
            'price' => $rows[0]['price'],
            'language' => $rows[0]['language'],
            'publisher' => $rows[0]['publisher'],
            'added_by' => $rows[0]['added_by'],
            'last_modified_by' => $rows[0]['last_modified_by'],
            'date_added' => $rows[0]['date_added'],
            'time_created' => $rows[0]['time_created'],
            'status' => $rows[0]['status'],
        );
    } else {
        errorreturn("Enter valid library info.");
    }
}





// Function to issue a book, preventing reissue of the same book to the same student
function issueBook(
    $conn_rjllearning_specific,
    $conn_rjllearning,
    $workspace_id,
    $institution_id,
    $branch_id,
    $student_id,
    $book_id,
    $date_of_issue,
    $issue_time,
    $due_date,
    $date_of_return,
    $return_time,
    $username,
    $fine_amount,
    $remarks,
    $date_created,
    $time_created,
    $academic_id
) {
    $currenttime = time();
    
    $student_check_query = "SELECT COUNT(*) FROM students WHERE id = ? and academic_id =? AND can_activate =1";
    $stmt = $conn_rjllearning_specific->prepare($student_check_query);
    $stmt->execute([$student_id,$academic_id]);
    
    $student_exists = $stmt->fetchColumn();
    
    if (!$student_exists) {
        errorreturn ("Student not found.");
    }
    // Validate that book_id exists and current_quantity is not zero
    $book_check_query = "SELECT current_quantity FROM library_management WHERE id = ? AND current_quantity > 0";
    $stmt = $conn_rjllearning_specific->prepare($book_check_query);
    $stmt->execute([$book_id]);

    $current_quantity = $stmt->fetchColumn();

    if (!$current_quantity) {
        // Book does not exist or current_quantity is zero
        errorreturn("Book not available for issue.");
    }

    // Check if the same book_id and student_id already exist in the book_issues table
    // Check if the same book_id and student_id already exist in the book_issues table
    $reissue_check_query = "SELECT COUNT(*) FROM book_issues WHERE book_id = ? AND student_id = ? AND academic_id = ? AND date_of_return IS NULL";
    $stmt = $conn_rjllearning_specific->prepare($reissue_check_query);
    $stmt->execute([$book_id, $student_id, $academic_id]);

    $existing_issue_count = $stmt->fetchColumn();

    if ($existing_issue_count > 0) {
        // Book has already been issued to this student and not yet returned
        errorreturn('This book has already been issued to the student and has not been returned.');
    }

    // SQL query to insert data into book_issues table
    $insert_issue_query = "INSERT INTO book_issues (
        institution_id, branch_id, student_id, academic_id, book_id, date_of_issue, 
        issue_time, due_date, date_of_return, return_time, issued_by, 
        return_to, added_by, fine_amount, last_modified_by, remarks, 
        date_created, time_created, status
    ) VALUES (
        ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
    )";

    $stmt = $conn_rjllearning_specific->prepare($insert_issue_query);
    $stmt->execute([
        $institution_id, $branch_id, $student_id, $academic_id, $book_id, $date_of_issue,
        $issue_time, $due_date, $date_of_return, $return_time, $username,
        null, $username, $fine_amount, $username, $remarks,
        $date_created, $time_created, 1 // Add status value
    ]);
    $book_issue_id = $conn_rjllearning_specific->lastInsertId();
    // Decrease the book quantity by 1
    $update_quantity_sql = "UPDATE library_management SET current_quantity = current_quantity - 1 WHERE id = ?";
    $update_stmt = $conn_rjllearning_specific->prepare($update_quantity_sql);
    $update_stmt->execute([$book_id]);

    // Task scheduled for later
        manageSchedulerTask($conn_rjllearning, $workspace_id, $institution_id, 'book_issues', $book_issue_id, $date_of_issue, $issue_time, 1, $process_type = 'create');

    // Return success message
    successreturn("Book issued successfully.");
}

function updateBookIssue(
    $conn_rjllearning_specific,
    $conn_rjllearning,
    $workspace_id,
    $institution_id,
    $book_issue_id,
    $date_of_issue,
    $issue_time,
    $due_date,
    $date_of_return,
    $return_time,
    $fine_amount,
    $remarks,
    $username,
    $date_created,
    $time_created
    
) {
    // // Check if the book_issue_id exists for the given institution_id
    //  if (!isset($book_issue_id) || empty($book_issue_id)) {
    //     return ['success' => false, 'message' => 'Book issue ID is required.'];
    // }
    
    $currenttime = time();

    // SQL query to update data in book_issues table
    $update_issue_query = "UPDATE book_issues SET 
        date_of_issue = ?, 
        issue_time = ?, 
        due_date = ?, 
        date_of_return = ?, 
        return_time = ?, 
        fine_amount = ?, 
        remarks = ?, 
        last_modified_by = ?, 
        date_created = ?, 
        time_created = ? 
    WHERE id = ? AND institution_id = ?";

    $stmt = $conn_rjllearning_specific->prepare($update_issue_query);
    $stmt->execute([
        $date_of_issue, 
        $issue_time, 
        $due_date, 
        $date_of_return, 
        $return_time, 
        $fine_amount, 
        $remarks, 
        $username, 
        $date_created, 
        $time_created, 
        $book_issue_id,
        $institution_id
    ]);

    if ($currenttime == strtotime($issue_time)) {
            // scheduled right now
            manageSchedulerTask($conn_rjllearning, $workspace_id, $institution_id, 'book_issues', $book_issue_id, $date_of_issue, (time() + 120), 1, $process_type = 'alter');
        } else {
            // scheduled for later
            manageSchedulerTask($conn_rjllearning, $workspace_id, $institution_id, 'book_issues', $book_issue_id, $date_of_issue, strtotime($issue_time), 1, $process_type = 'alter');
        }

    // Return success message
    successreturn ("Book issue record updated successfully.");
}

function getbookissue($conn_rjllearning_specific, $institution_id, $search_string, $branch_id, $academic_id, $from_date, $to_date, $limit, $offset)
{
    // Get total rows
    $sql = "SELECT COUNT(*) as total_rows FROM book_issues WHERE institution_id = ? and academic_id =?";
    $array_params = [$institution_id,$academic_id];

    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute($array_params);
    $result_total = $stmt->fetchAll();
    $totalrows = $result_total[0]['total_rows'];

    // Build query for fetching data
    if ($limit == 'all') {
        $sql_session = "SELECT * FROM book_issues WHERE institution_id = ? and academic_id=?";
        $array_params = [$institution_id,$academic_id];
    } else {
        $sql_session = "SELECT * FROM book_issues WHERE institution_id = ? and academic_id=?";
        $array_params = [$institution_id,$academic_id];
    }

    // Apply branch filter
    if ($branch_id != null && $branch_id != 'all') {
        $sql_session .= " AND branch_id = ?";
        $array_params[] = $branch_id;
    }

    // Apply book_id search filter
    if ($search_string != null) {
        $sql_session .= " AND book_id LIKE ?";
        $array_params[] = '%' . $search_string . '%';
    }

    // Apply date range filter
    if ($from_date != null && $to_date != null) {
        $sql_session .= " AND date_created BETWEEN ? AND ?";
        $array_params[] = $from_date;
        $array_params[] = $to_date;
    }

    // Add sorting
    $sql_session .= " ORDER BY date_created DESC, time_created DESC";

    // Apply limit and offset if limit is not 'all'
    if ($limit != 'all') {
        $sql_session .= " LIMIT " . $offset . ", " . $limit;
    }

    $stmt = $conn_rjllearning_specific->prepare($sql_session);
    $stmt->execute($array_params);
    $session_rows = $stmt->fetchAll();

    $array_return = [];

    foreach ($session_rows as $rows) {
        $branch_info = getBranchInfo($conn_rjllearning_specific, $institution_id, $rows['branch_id']);
        $array_return[] = array(
            'book_issue_id' => $rows['id'],
            'institution_id' => $rows['institution_id'],
            'branch_id' => $branch_info,
            'student_id' => $rows['student_id'],
            'book_id' => $rows['book_id'],
            'date_of_issue' => $rows['date_of_issue'],
            'issue_time' => $rows['issue_time'],
            'due_date' => $rows['due_date'],
            'date_of_return' => $rows['date_of_return'],
            'return_time' => $rows['return_time'],
            'issued_by' => $rows['issued_by'],
            'return_to' => $rows['return_to'],
            'added_by' => $rows['added_by'],
            'fine_amount' => $rows['fine_amount'],
            'last_modified_by' => $rows['last_modified_by'],
            'remarks' => $rows['remarks'],
            'date_created' => $rows['date_created'],
            'time_created' => $rows['time_created'],
            'status' => $rows['status']
        );
    }

    // Determine maxreach
    $maxreach = (count($session_rows) < $limit || $limit == 'all') ? 1 : 0;

    return array('total_rows' => $totalrows, 'book_issues' => $array_return, 'maxreach' => $maxreach);
}




function getSpecificbookissue($conn_rjllearning_specific, $institution_id, $book_issue_id)
{
    // Query to fetch the specific book issue record
    $sql = $conn_rjllearning_specific->prepare("SELECT * FROM book_issues WHERE id = ? AND institution_id = ?");
    $sql->execute(array($book_issue_id, $institution_id));
    $rows = $sql->fetchAll();

    if (count($rows) == 1) {
        $branch_info = getBranchInfo($conn_rjllearning_specific, $institution_id, $rows[0]['branch_id']);
        $student_info = getStudentInfo($conn_rjllearning_specific, $institution_id, $rows[0]['student_id']);
        return array(
            'book_issue_id' => $rows[0]['id'],
            'institution_id' => $rows[0]['institution_id'],
            'branch_id' => $branch_info,
            'student_id' => $student_info,
            'book_id' => $rows[0]['book_id'],
            'date_of_issue' => $rows[0]['date_of_issue'],
            'issue_time' => $rows[0]['issue_time'],
            'due_date' => $rows[0]['due_date'],
            'date_of_return' => $rows[0]['date_of_return'],
            'return_time' => $rows[0]['return_time'],
            'issued_by' => $rows[0]['issued_by'],
            'return_to' => $rows[0]['return_to'],
            'added_by' => $rows[0]['added_by'],
            'fine_amount' => $rows[0]['fine_amount'],
            'last_modified_by' => $rows[0]['last_modified_by'],
            'remarks' => $rows[0]['remarks'],
            'date_created' => $rows[0]['date_created'],
            'time_created' => $rows[0]['time_created'],
            'status' => $rows[0]['status'],
        );
    } else {
        errorreturn("Invalid book issue ID.");
    }
}



function getStudentInfo($conn_rjllearning_specific, $institution_id, $student_id)
{
    
    // Prepare the SQL query to fetch student name and admission number
    $res = $conn_rjllearning_specific->prepare("SELECT id, name, admission_number ,branch_id , class_id , section_id FROM students WHERE id = ? AND institution_id = ?");
    $res->execute([$student_id, $institution_id]);
    
    // Fetch the result
    $result_student = $res->fetchAll(PDO::FETCH_ASSOC);
    
    // Check if the student was found
    if (count($result_student) > 0) {
        // Return student information
        $student_info =[
            "student_id" => $result_student[0]['id'], 
            "name" => $result_student[0]['name'],
            "admission_number" => $result_student[0]['admission_number'],
            "branch_id" => $result_student[0]['branch_id'],
            "class_id" => $result_student[0]['class_id'],
            "section_id" => $result_student[0]['section_id'],
        ];
    } else {
        errorreturn("student not found.");
    }
    
    return $student_info;
}


function returnBook($conn_rjllearning_specific, $institution_id, $branch_id, $book_issue_id, $date_of_return, $return_time, $username, $fine_amount, $remarks)
{
    // Fetch the issue record to check if the book has already been returned
    $check_issue_query = "SELECT * FROM book_issues WHERE id = ? AND institution_id = ? AND branch_id = ? AND date_of_return IS NULL";
    $stmt = $conn_rjllearning_specific->prepare($check_issue_query);
    $stmt->execute([$book_issue_id, $institution_id, $branch_id]);

    $issue_record = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$issue_record) {
        errorreturn("Book has already been returned or issue record not found.");
    }
    
    $student_id = $issue_record['student_id'];
    $fetch_student_name_query = "SELECT name FROM students WHERE id = ? AND institution_id = ? AND branch_id = ?";
    $stmt = $conn_rjllearning_specific->prepare($fetch_student_name_query);
    $stmt->execute([$student_id, $institution_id, $branch_id]);

    $student = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$student) {
        errorreturn("Student record not found.");
    }

    $vendor_name = $student['name'];

    // Update the return date, return time, return_to, fine amount, and remarks
    $update_return_query = "UPDATE book_issues SET date_of_return = ?, return_time = ?, return_to = ?, fine_amount = ?, remarks = ? WHERE id = ? AND institution_id = ? AND branch_id= ?";
    $stmt = $conn_rjllearning_specific->prepare($update_return_query);
    $stmt->execute([$date_of_return, $return_time, $username, $fine_amount, $remarks, $book_issue_id, $institution_id, $branch_id]);

    // Increase the current quantity of the book in library management
    $book_id = $issue_record['book_id'];
    $update_quantity_sql = "UPDATE library_management SET current_quantity = current_quantity + 1 WHERE id = ?  AND institution_id = ? AND branch_id= ?";
    $stmt = $conn_rjllearning_specific->prepare($update_quantity_sql);
    $stmt->execute([$book_id, $institution_id, $branch_id]);
    
    // Handle fine transaction if fine amount is present
    if ($fine_amount != null) {

        $check_expense_sql = "SELECT id FROM unmarked_transaction_type_group WHERE group_name = 'Fine Amount' AND institution_id = ? ";
        $insert_expense_sql = "INSERT INTO unmarked_transaction_type_group (institution_id, group_name, created_by, date_created, time_created, status) VALUES ( ?, ?, ?, ?, ?, ?)";
        $fetch_new_id_sql = "SELECT id FROM unmarked_transaction_type_group WHERE group_name = 'Fine Amount' AND institution_id = ? ";
        
        $stmt = $conn_rjllearning_specific->prepare($check_expense_sql);
        $stmt->execute([$institution_id]);
        
        if ($stmt->rowCount() > 0) {
            // If the record exists, fetch the existing id
            $existing_record = $stmt->fetch(PDO::FETCH_ASSOC);
            if ($existing_record && isset($existing_record['id'])) {
                $existing_id = $existing_record['id'];
            } else {
                errorreturn("Failed to retrieve the existing record ID.");
            }
        } else {
            // If not exists, insert the new expense_id
            $stmt_insert = $conn_rjllearning_specific->prepare($insert_expense_sql);
            $stmt_insert->execute([$institution_id, 'Fine Amount', $username, date('Y-m-d'), time(), 0]);
        
            // Fetch the new id after insertion
            $stmt_fetch = $conn_rjllearning_specific->prepare($fetch_new_id_sql);
            $stmt_fetch->execute([$institution_id]);
            $new_record = $stmt_fetch->fetch(PDO::FETCH_ASSOC);
            if ($new_record && isset($new_record['id'])) {
                $existing_id = $new_record['id'];
            } else {
                errorreturn("Failed to retrieve the newly inserted record ID.");
            }
        }

        // Insert the transaction
        $insert_transaction_sql = "INSERT INTO unmarked_transaction (institution_id, branch_id, vendor_name, category_id, amount, date, transaction_reference_id, payment_bool, payment_method, paid_on, attachment, remarks, created_by, date_created, time_created, last_edit, last_edit_at, approved_by, recorded_in_finware, transaction_type_group, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        $stmt = $conn_rjllearning_specific->prepare($insert_transaction_sql);
        $stmt->execute([
            $institution_id,
            $branch_id, 
            $vendor_name, // Assuming $vendor_name is available in the scope or passed to the function
            $existing_id, // Assuming $existing_id is available in the scope or passed to the function
            $fine_amount,
            $date_of_return,
            $book_issue_id,
            1, 
            'cash',  // Assuming 'cash' as the payment method
            date('Y-m-d'),
            null,
            $remarks,
            $username,
            date('Y-m-d'),
            date('H:i:s'),
            null,
            null,
            null,
            0,  // Assuming not recorded in Finware
            'receipt',
            1  // Assuming 1 means active status
        ]);
    }

    // Success return message
    successreturn("Book returned successfully.");
}





// function returnBook($conn_rjllearning_specific, $institution_id, $book_issue_id, $student_id, $date_of_return, $return_time, $username) {
//     // Fetch the issue record to check if the book has already been returned
//     $check_issue_query = "SELECT * FROM book_issues WHERE id = ? AND institution_id = ? AND student_id = ? AND date_of_return IS NULL";
//     $stmt = $conn_rjllearning_specific->prepare($check_issue_query);
//     $stmt->execute([$book_issue_id, $institution_id, $student_id]);

//     $issue_record = $stmt->fetch(PDO::FETCH_ASSOC);

//     if (!$issue_record) {
//         errorreturn("Book has already been returned, issue record not found, or the student ID does not match.");
//     }

//     // Update the return date, return time, and last modified by
//     $update_return_query = "UPDATE book_issues SET date_of_return = ?, return_time = ?, last_modified_by = ? WHERE id = ? AND institution_id = ? AND student_id = ?";
//     $stmt = $conn_rjllearning_specific->prepare($update_return_query);
//     $stmt->execute([$date_of_return, $return_time, $username, $book_issue_id, $institution_id, $student_id]);

//     // Increase the current quantity of the book in library management
//     $book_id = $issue_record['book_id'];
//     $update_quantity_sql = "UPDATE library_management SET current_quantity = current_quantity + 1 WHERE id = ?";
//     $stmt = $conn_rjllearning_specific->prepare($update_quantity_sql);
//     $stmt->execute([$book_id]);

//     // Calculate any fines (if applicable) and update the issue record
//     $due_date = $issue_record['due_date'];
//     $fine_amount = 0;

//     if (strtotime($date_of_return) > strtotime($due_date)) {
//         $days_late = (strtotime($date_of_return) - strtotime($due_date)) / 86400;
//         $fine_amount = ceil($days_late) * 10; // Assuming a fine of 10 units per day late
//     }

//     $update_fine_query = "UPDATE book_issues SET fine_amount = ? WHERE id = ? AND institution_id = ? AND student_id = ?";
//     $stmt = $conn_rjllearning_specific->prepare($update_fine_query);
//     $stmt->execute([$fine_amount, $book_issue_id, $institution_id, $student_id]);

//     successreturn("Book returned successfully.");
// }

function get_students_for_books($conn_rjllearning_specific, $book_id, $institution_id, $branch_id) 
{
    // SQL query to get book issues
    $sql = "SELECT student_id, date_of_issue, date_of_return FROM book_issues WHERE book_id = ? AND institution_id = ? AND branch_id  = ? AND (date_of_issue IS NOT NULL OR date_of_return IS NOT NULL)";
    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute([$book_id, $institution_id, $branch_id]);
    $book_issues = $stmt->fetchAll();

    // Initialize an array to store student details
    $student_details = [];

    // Iterate over each book issue to get student details
    foreach ($book_issues as $book) {
        $student_id = $book['student_id'];
        $issue_date = $book['date_of_issue'];
        $return_date = $book['date_of_return'];
    
        // SQL query to get student name
        $sql = "SELECT name FROM students WHERE id = ? AND institution_id = ? AND branch_id  = ?";
        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute([$student_id, $institution_id, $branch_id]);
        $student_name_row = $stmt->fetch();
        $student_name = $student_name_row ? $student_name_row['name'] : 'Unknown';

        // Check if the book has not yet been returned
        if ($return_date === null) {
            $return_date = 'not yet returned';
        }

        // Add the student details to the array
        $student_details[] = array(
            "student_id" => $student_id,
            "student_name" => $student_name,
            "date_of_issue" => $issue_date,
            "date_of_return" => $return_date
        );
    }

    // Return the array of student details
    return $student_details;
}

function getspecific_book_detail($conn_rjllearning_specific, $book_id, $institution_id, $branch_id)
{
    //check if the book exist
    $sql = "SELECT id FROM library_management WHERE id = ? AND institution_id = ? AND branch_id = ?";
    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute([$book_id,$institution_id,$branch_id]);
    $book = $stmt->fetch();

    if (!$book) {
        errorreturn("No Book exist for the given institution and branch.");
    }
    //count the issue
    $sql = "SELECT COUNT(*) as issue_count FROM book_issues WHERE book_id = ? AND institution_id = ? AND branch_id = ? AND date_of_issue IS NOT NULL";
    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute(array($book_id));
    $issue_count = $stmt->fetch()['issue_count'];

    //count the return
    $sql = "SELECT COUNT(*) as return_count FROM book_issues WHERE book_id = ? AND institution_id = ? AND branch_id = ? AND date_of_return IS NOT NULL";
    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute(array($book_id,$institution_id,$branch_id));
    $return_count = $stmt->fetch()['return_count'];

    //get the last purchase and the current quantity
    $sql = "SELECT MAX(date_added) as last_purchase_date, current_quantity,opening_quantity,book_name,author,genre,year_of_publication,language,publisher FROM library_management WHERE id = ?
    AND institution_id = ? AND branch_id = ?";
    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute(array($book_id,$institution_id,$branch_id));
    $result = $stmt->fetch();

    $last_purchase_date = $result['last_purchase_date'];
    $current_quantity = $result['current_quantity'];
    $total_quantity = $result['opening_quantity'];
    $book_name = $result['book_name'];
    $author_name = $result['author'];
    $book_genre = $result['genre'];
    $publication_year = $result['year_of_publication'];
    $language = $result['language'];
    $publisher = $result['publisher'];

    return array(
        "book_name" => $book_name,
        'author' => $author_name,
        'last_purchase_date' => $last_purchase_date,
        'issued' => $issue_count . " times",
        'returned' => $return_count . " times",
        'genre' => $book_genre,
        'publication_year' => $publication_year,
        'language' => $language,
        'publisher' => $publisher,
        'current_quantity' => $current_quantity,
        'total_quantity' => $total_quantity,
    );
}

function get_student_book_summary($conn_rjllearning_specific, $student_id, $institution_id, $branch_id) {
   
    $sql = "SELECT name FROM students WHERE id = ? AND institution_id = ? AND branch_id = ? AND can_activate = 1";
    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute([$student_id, $institution_id, $branch_id]);
    $student = $stmt->fetch();
    
    if (!$student) {
        errorreturn("Student not found");
    }
    
    // Fetch the book issue/return records for the given student
    $sql = "SELECT book_id, date_of_issue, date_of_return FROM book_issues WHERE student_id = ? AND institution_id = ? AND branch_id = ?";
    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute([$student_id,$institution_id, $branch_id]);
    $book_records = $stmt->fetchAll();
    
    
    $books_issued_count = 0;
    $books_returned_count = 0;
    $book_details = [];
    
    foreach ($book_records as $record) {
        $books_issued_count++;

        if (!empty($record['date_of_return'])) {
            $books_returned_count++;
        }

        // Fetch book name from library_management table
        $book_id = $record['book_id'];
        $sql = "SELECT book_name FROM library_management WHERE id = ? AND institution_id = ? AND branch_id = ?";
        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute([$book_id,$institution_id, $branch_id]);
        $book = $stmt->fetch();
        
        $book_name = $book ? $book['book_name'] : 'Unknown';

        $book_details[] = [
            'name' => $book_name,
            'issued_on' => $record['date_of_issue'],
            'date_of_return' => $record['date_of_return'] ?: 'Not yet returned'
        ];
    }
    
    $total_books = $books_issued_count + $books_returned_count;
    
    $result = [
        // "student_name" => $student['name'],
        "books_issued" => $books_issued_count,
        "books_returned" => $books_returned_count,
        "total_books" => $total_books,
        "books" => $book_details
    ];

    return $result;
}


function getstudent_books($conn_rjllearning_specific, $branch_id, $search_string, $limit, $offset, $institution_id)
{
    $columns = "id, name";

    $sql_count = "SELECT $columns FROM students WHERE branch_id = ? AND (name LIKE ?) AND can_activate = 1";
    $stmt = $conn_rjllearning_specific->prepare($sql_count);
    $stmt->execute(array($branch_id, '%' . $search_string . '%'));
    $result_count = $stmt->fetchAll();
    $total_rows = count($result_count);

    if ($limit == 'all') {
        $sql = $sql_count . "ORDER BY id ASC";
        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute(array($branch_id, '%' . $search_string . '%'));
        $result = $stmt->fetchAll();
    } else {
        $sql = $sql_count . "ORDER BY id ASC LIMIT " . $offset . ", " . $limit;
        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute(array($branch_id, '%' . $search_string . '%'));
        $result = $stmt->fetchAll();
    }

    $array_return = [];
    foreach ($result as $res) {
        $books_summary = get_student_book_summary($conn_rjllearning_specific, $res['id'], $institution_id, $branch_id);
        $field_array = [
            'student_id' => $res['id'],
            'student_name' => $res['name'],
            'book_summary' => $books_summary
        ];
        array_push($array_return, $field_array);
    }

    if (count($result) < 50) {
        return array('total_rows' => $total_rows, 'student_info' => $array_return, 'maxreach' => 1);
    } else {
        return array('total_rows' => $total_rows, 'student_info' => $array_return, 'maxreach' => 0);
    }
}




