<?php
// NEW: Function to handle bulk transaction actions - FIXED
function handleBulkTransactionAction($conn) {
    $response = array();
    
    // Get bulk action parameters
    $item_ids_string = isset($_POST['item_ids']) ? $_POST['item_ids'] : '';
    $transaction_id = isset($_POST['transaction_id']) ? intval($_POST['transaction_id']) : 0;
    $action = $_POST['action'];
    $note = isset($_POST['note']) ? $_POST['note'] : '';
    
    // Parse item IDs
    $item_ids = array_filter(array_map('intval', explode(',', $item_ids_string)));
    
    if (empty($item_ids)) {
        $response['status'] = 'error';
        $response['message'] = 'Tidak ada item yang dipilih';
        return $response;
    }
    
    // Begin transaction
    $conn->begin_transaction();
    
    try {
        $success_count = 0;
        $error_count = 0;
        $processed_items = array();
        
        foreach ($item_ids as $item_id) {
            // Get item data first with proper joins
            $stmt = $conn->prepare("SELECT ti.*, cs.component_id, cs.color_id, cs.kode_barang,
                                   cpc.available_quantity, 
                                   st.tanggal_transaksi, st.tanggal_pengembalian, st.reservation_number,
                                   cs.status as item_status, cs.lokasi as current_lokasi,
                                   bu.lokasi_posisi as borrower_location
                                   FROM transaction_items_semarang ti 
                                   JOIN color_stock_items_semarang cs ON ti.stock_item_id = cs.id
                                   JOIN color_panel_components_semarang cpc ON cs.color_id = cpc.color_id AND cs.component_id = cpc.component_id
                                   JOIN stock_transactions_semarang st ON ti.transaction_id = st.id
                                   JOIN borrower_users_semarang bu ON st.borrower_id = bu.id
                                   WHERE ti.id = ?");
            $stmt->bind_param("i", $item_id);
            $stmt->execute();
            $item_data = $stmt->get_result()->fetch_assoc();
            $stmt->close();
            
            if (!$item_data) {
                $error_count++;
                continue;
            }
            
            // Skip items that are not available (already borrowed)
            if ($item_data['item_status'] === 'Dipinjam' && in_array($action, ['approve', 'onprocess'])) {
                $error_count++;
                continue;
            }
            
            // Process individual item based on action
            $item_result = processSingleItemBulk($conn, $item_id, $transaction_id, $action, $note, $item_data);
            
            if ($item_result['success']) {
                $success_count++;
                $processed_items[] = $item_id;
            } else {
                $error_count++;
            }
        }
        
        // Update transaction status after processing all items
        if ($success_count > 0) {
            updateTransactionStatus($conn, $transaction_id);
        }
        
        // Commit transaction
        $conn->commit();
        
        // Get reservation number for logging
        $stmt = $conn->prepare("SELECT reservation_number FROM stock_transactions_semarang WHERE id = ?");
        $stmt->bind_param("i", $transaction_id);
        $stmt->execute();
        $reservation_data = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        
        // Enhanced logging for bulk actions
        $action_text = ucfirst($action);
        if ($action === 'onprocess') $action_text = 'OnProcess';
        
        log_activity($conn, $_SESSION['user_id'], 'it', "Bulk {$action_text} Items", 
            "IT user performed bulk {$action} on {$success_count} items in reservation: {$reservation_data['reservation_number']} (Transaction ID: {$transaction_id})");
        
        // Prepare response message
        if ($success_count > 0 && $error_count === 0) {
            $response['status'] = 'success';
            $response['message'] = "Berhasil memproses {$success_count} item";
        } elseif ($success_count > 0 && $error_count > 0) {
            $response['status'] = 'partial';
            $response['message'] = "Berhasil memproses {$success_count} item, {$error_count} item gagal/dilewati";
        } else {
            $response['status'] = 'error';
            $response['message'] = "Gagal memproses semua item ({$error_count} item)";
        }
        
        $response['processed_count'] = $success_count;
        $response['error_count'] = $error_count;
        $response['processed_items'] = $processed_items;
        
        return $response;
        
    } catch (Exception $e) {
        // Rollback transaction on error
        $conn->rollback();
        
        error_log("Bulk transaction action error: " . $e->getMessage());
        
        $response['status'] = 'error';
        $response['message'] = 'Terjadi kesalahan: ' . $e->getMessage();
        return $response;
    }
}

// FIXED: Helper function to process single item in bulk operation
function processSingleItemBulk($conn, $item_id, $transaction_id, $action, $note, $item_data) {
    try {
        // FIXED: Ensure lokasi_tujuan is properly set
        $lokasi_tujuan = !empty($item_data['lokasi_tujuan']) ? $item_data['lokasi_tujuan'] : $item_data['borrower_location'];
        
        switch ($action) {
            case 'approve':
                // Update the item status to approved with quantity_approved = 1
                $quantity_approved = 1;
                
                // FIXED: Update transaction_items_semarang with proper lokasi_tujuan
                $stmt = $conn->prepare("UPDATE transaction_items_semarang 
                                       SET quantity_approved = ?,
                                           lokasi_tujuan = ?
                                       WHERE id = ?");
                $stmt->bind_param("isi", $quantity_approved, $lokasi_tujuan, $item_id);
                $stmt->execute();
                $stmt->close();
                
                // Reduce the available quantity in color_panel_components_semarang
                $new_quantity = $item_data['available_quantity'] - $quantity_approved;
                $stmt = $conn->prepare("UPDATE color_panel_components_semarang SET available_quantity = ? 
                                       WHERE color_id = ? AND component_id = ?");
                $stmt->bind_param("iii", $new_quantity, $item_data['color_id'], $item_data['component_id']);
                $stmt->execute();
                $stmt->close();
                
                // Update item location and status
                $stmt = $conn->prepare("UPDATE color_stock_items_semarang SET status = 'Dipinjam', lokasi = ? 
                                       WHERE id = ?");
                $stmt->bind_param("si", $lokasi_tujuan, $item_data['stock_item_id']);
                $stmt->execute();
                $stmt->close();
                
                return ['success' => true, 'message' => 'Item approved'];
                
            case 'reject':
                // Update the item status to rejected
                $bulk_note = !empty($note) ? $note : 'Ditolak melalui aksi massal';
                
                // FIXED: Set quantity_approved to 0 explicitly for rejected items
                $stmt = $conn->prepare("UPDATE transaction_items_semarang 
                                       SET quantity_approved = 0, 
                                           catatan = ?,
                                           lokasi_tujuan = ?
                                       WHERE id = ?");
                $stmt->bind_param("ssi", $bulk_note, $lokasi_tujuan, $item_id);
                $stmt->execute();
                $stmt->close();
                
                return ['success' => true, 'message' => 'Item rejected'];
                
            case 'onprocess':
                // Get the borrower data and calculate return date from tanggal_pengembalian
                $stmt = $conn->prepare("SELECT bu.username, st.tanggal_transaksi, st.tanggal_pengembalian 
                                       FROM stock_transactions_semarang st 
                                       JOIN borrower_users_semarang bu ON st.borrower_id = bu.id 
                                       WHERE st.id = ?");
                $stmt->bind_param("i", $transaction_id);
                $stmt->execute();
                $transaction_data = $stmt->get_result()->fetch_assoc();
                $stmt->close();
                
                $automated_note = "Anda harus menunggu produk code barang pada color code yang anda pinjam sedang dipinjam oleh {$transaction_data['username']} ini mulai tanggal " . 
                                 date('Y-m-d H:i', strtotime($transaction_data['tanggal_transaksi'])) . 
                                 " sampai tanggal " . date('Y-m-d H:i', strtotime($transaction_data['tanggal_pengembalian'])) . 
                                 ". (Diproses melalui aksi massal)";
                
                // Add custom note if provided
                if (!empty($note)) {
                    $automated_note .= " Catatan tambahan: " . $note;
                }
                
                // FIXED: Update the transaction item with proper data
                $stmt = $conn->prepare("UPDATE transaction_items_semarang 
                                       SET catatan = ?,
                                           lokasi_tujuan = ?
                                       WHERE id = ?");
                $stmt->bind_param("ssi", $automated_note, $lokasi_tujuan, $item_id);
                $stmt->execute();
                $stmt->close();
                
                return ['success' => true, 'message' => 'Item set to OnProcessing'];
                
            default:
                return ['success' => false, 'message' => 'Invalid action'];
        }
        
    } catch (Exception $e) {
        error_log("Error processing item {$item_id}: " . $e->getMessage());
        return ['success' => false, 'message' => $e->getMessage()];
    }
}

// REST OF THE FILE REMAINS THE SAME...
function handleTransactionAction($conn) {
    $response = array();
    
    // Get the transaction item ID
    $item_id = isset($_POST['item_id']) ? intval($_POST['item_id']) : 0;
    $transaction_id = isset($_POST['transaction_id']) ? intval($_POST['transaction_id']) : 0;
    $action = $_POST['action'];
    
    // UPDATED: Get the transaction item data with complete transaction information
    $stmt = $conn->prepare("SELECT ti.*, cs.component_id, cs.color_id, cpc.available_quantity, 
                           st.tanggal_transaksi, st.tanggal_pengembalian, st.reservation_number
                           FROM transaction_items_semarang ti 
                           JOIN color_stock_items_semarang cs ON ti.stock_item_id = cs.id
                           JOIN color_panel_components_semarang cpc ON cs.color_id = cpc.color_id AND cs.component_id = cpc.component_id
                           JOIN stock_transactions_semarang st ON ti.transaction_id = st.id
                           WHERE ti.id = ?");
    $stmt->bind_param("i", $item_id);
    $stmt->execute();
    $item_data = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    
    if (!$item_data) {
        $response['status'] = 'error';
        $response['message'] = 'Item tidak ditemukan';
        return $response;
    }
    
    // Begin transaction
    $conn->begin_transaction();
    
    try {
        switch ($action) {
            case 'approve':
                // Update the item status to approved
                $quantity_approved = 1; // Assuming 1 item is approved
                $stmt = $conn->prepare("UPDATE transaction_items_semarang SET quantity_approved = ? WHERE id = ?");
                $stmt->bind_param("ii", $quantity_approved, $item_id);
                $stmt->execute();
                $stmt->close();
                
                // Reduce the available quantity in color_panel_components_semarang
                $new_quantity = $item_data['available_quantity'] - $quantity_approved;
                $stmt = $conn->prepare("UPDATE color_panel_components_semarang SET available_quantity = ? 
                                       WHERE color_id = ? AND component_id = ?");
                $stmt->bind_param("iii", $new_quantity, $item_data['color_id'], $item_data['component_id']);
                $stmt->execute();
                $stmt->close();
                
                // Update the transaction status if all items are processed
                updateTransactionStatus($conn, $transaction_id);
                
                // Get borrower location for item destination
                $stmt = $conn->prepare("SELECT bu.lokasi_posisi 
                                       FROM stock_transactions_semarang st 
                                       JOIN borrower_users_semarang bu ON st.borrower_id = bu.id 
                                       WHERE st.id = ?");
                $stmt->bind_param("i", $transaction_id);
                $stmt->execute();
                $borrower_data = $stmt->get_result()->fetch_assoc();
                $stmt->close();
                
                // Default to the borrower's location if lokasi_tujuan is not set
                $lokasi_tujuan = $item_data['lokasi_tujuan'] ?: $borrower_data['lokasi_posisi'];
                
                // Update item location
                $stmt = $conn->prepare("UPDATE color_stock_items_semarang SET status = 'Dipinjam', lokasi = ? 
                                       WHERE id = ?");
                $stmt->bind_param("si", $lokasi_tujuan, $item_data['stock_item_id']);
                $stmt->execute();
                $stmt->close();
                
                // ENHANCED: Log approval action with reservation number
                log_activity($conn, $_SESSION['user_id'], 'it', 'Approve Item', 
                    "IT user approved item ID: {$item_id} in reservation: {$item_data['reservation_number']}");
                
                $response['status'] = 'success';
                $response['message'] = 'Item berhasil disetujui';
                break;
                
            case 'reject':
                // Get the rejection note
                $note = isset($_POST['note']) ? $_POST['note'] : '';
                
                // Update the item status to rejected
                $stmt = $conn->prepare("UPDATE transaction_items_semarang SET quantity_approved = 0, catatan = ? WHERE id = ?");
                $stmt->bind_param("si", $note, $item_id);
                $stmt->execute();
                $stmt->close();
                
                // Update the transaction status if all items are processed
                updateTransactionStatus($conn, $transaction_id);
                
                // ENHANCED: Log rejection action with reservation number
                log_activity($conn, $_SESSION['user_id'], 'it', 'Reject Item', 
                    "IT user rejected item ID: {$item_id} in reservation: {$item_data['reservation_number']} - Reason: {$note}");
                
                $response['status'] = 'success';
                $response['message'] = 'Item berhasil ditolak';
                break;
                
            case 'onprocess':
                // UPDATED: Get the borrower data and calculate return date from tanggal_pengembalian
                $stmt = $conn->prepare("SELECT bu.username, st.tanggal_transaksi, st.tanggal_pengembalian 
                                       FROM stock_transactions_semarang st 
                                       JOIN borrower_users_semarang bu ON st.borrower_id = bu.id 
                                       WHERE st.id = ?");
                $stmt->bind_param("i", $transaction_id);
                $stmt->execute();
                $transaction_data = $stmt->get_result()->fetch_assoc();
                $stmt->close();
                
                $automated_note = "Anda harus menunggu produk code barang pada color code yang anda pinjam sedang dipinjam oleh {$transaction_data['username']} ini mulai tanggal " . 
                                 date('Y-m-d H:i', strtotime($transaction_data['tanggal_transaksi'])) . 
                                 " sampai tanggal " . date('Y-m-d H:i', strtotime($transaction_data['tanggal_pengembalian'])) . ".";
                
                // Update the transaction item status to OnProcessing
                $stmt = $conn->prepare("UPDATE transaction_items_semarang SET catatan = ? WHERE id = ?");
                $stmt->bind_param("si", $automated_note, $item_id);
                $stmt->execute();
                $stmt->close();
                
                // Update the transaction status
                $stmt = $conn->prepare("UPDATE stock_transactions_semarang SET status = 'OnProccesing', catatan_proses = ? WHERE id = ?");
                $stmt->bind_param("si", $automated_note, $transaction_id);
                $stmt->execute();
                $stmt->close();
                
                // ENHANCED: Log onprocess action with reservation number
                log_activity($conn, $_SESSION['user_id'], 'it', 'OnProcess Item', 
                    "IT user set item ID: {$item_id} to OnProcessing in reservation: {$item_data['reservation_number']}");
                
                $response['status'] = 'success';
                $response['message'] = 'Item berhasil diproses';
                break;
                
            case 'return':
                // Get the return condition and note
                $condition = isset($_POST['condition']) ? $_POST['condition'] : 'Baik';
                $note = isset($_POST['note']) ? $_POST['note'] : '';
                
                // Debug logging
                error_log("=== RETURN ACTION DEBUG (IT USER) ===");
                error_log("Item ID: " . $item_id);
                error_log("Transaction ID: " . $transaction_id);
                error_log("Reservation Number: " . $item_data['reservation_number']);
                error_log("Condition: " . $condition);
                error_log("Transaction Data: " . json_encode($item_data));
                
                // UPDATED: Determine status_waktu based on 15-minute tolerance after expected return time
                $status_waktu = determineReturnStatus($item_data);
                
                error_log("Final Status Waktu: " . $status_waktu);
                error_log("=== END DEBUG ===");
                
                // Update the item status to returned
                $stmt = $conn->prepare("UPDATE transaction_items_semarang SET 
                                       dikembalikan = 1, 
                                       tanggal_kembali = CURRENT_TIMESTAMP(), 
                                       kondisi_kembali = ?, 
                                       catatan = ?,
                                       status_waktu = ?
                                       WHERE id = ?");
                $stmt->bind_param("sssi", $condition, $note, $status_waktu, $item_id);
                $stmt->execute();
                $stmt->close();
                
                // If the condition is 'Baik', restore the available quantity
                if ($condition == 'Baik') {
                    $new_quantity = $item_data['available_quantity'] + $item_data['quantity_approved'];
                    $stmt = $conn->prepare("UPDATE color_panel_components_semarang SET available_quantity = ? 
                                           WHERE color_id = ? AND component_id = ?");
                    $stmt->bind_param("iii", $new_quantity, $item_data['color_id'], $item_data['component_id']);
                    $stmt->execute();
                    $stmt->close();
                }
                
                // Update the item location back to Color Room and status to Tersedia, Rusak, or Hilang
                $new_status = ($condition == 'Baik') ? 'Tersedia' : ($condition == 'Rusak' ? 'Rusak' : 'Hilang');
                $stmt = $conn->prepare("UPDATE color_stock_items_semarang SET status = ?, lokasi = 'Color Room' WHERE id = ?");
                $stmt->bind_param("si", $new_status, $item_data['stock_item_id']);
                $stmt->execute();
                $stmt->close();
                
                // Check if all items are returned and update transaction status
                $stmt = $conn->prepare("SELECT COUNT(*) as total, SUM(dikembalikan) as returned 
                                       FROM transaction_items_semarang WHERE transaction_id = ?");
                $stmt->bind_param("i", $transaction_id);
                $stmt->execute();
                $return_status = $stmt->get_result()->fetch_assoc();
                $stmt->close();
                
                if ($return_status['total'] == $return_status['returned']) {
                    $stmt = $conn->prepare("UPDATE stock_transactions_semarang SET status_peminjaman = 'Dikembalikan',
                                          catatan_pengembalian = ? WHERE id = ?");
                    $stmt->bind_param("si", $note, $transaction_id);
                    $stmt->execute();
                    $stmt->close();
                }
                
                // ENHANCED: Log return action with detailed information
                $return_status_text = ($status_waktu == 'overdue') ? 'Late Return' : 'On Time Return';
                log_activity($conn, $_SESSION['user_id'], 'it', 'Return Item', 
                    "IT user processed return for item ID: {$item_id} in reservation: {$item_data['reservation_number']} - Condition: {$condition}, Status: {$return_status_text}");
                
                $response['status'] = 'success';
                $response['message'] = 'Item berhasil dikembalikan ' . ($status_waktu == 'overdue' ? '(Terlambat)' : '(Tepat Waktu)');
                break;
                
            default:
                $response['status'] = 'error';
                $response['message'] = 'Aksi tidak valid';
                break;
        }
        
        // Commit transaction
        $conn->commit();
        
        return $response;
        
    } catch (Exception $e) {
        // Rollback transaction on error
        $conn->rollback();
        
        // ENHANCED: Log error with reservation number
        error_log("Transaction action error for reservation {$item_data['reservation_number']} (IT user): " . $e->getMessage());
        
        $response['status'] = 'error';
        $response['message'] = 'Terjadi kesalahan: ' . $e->getMessage();
        return $response;
    }
}

// Helper function to update transaction status
function updateTransactionStatus($conn, $transaction_id) {
    // Check if all items are processed (either approved or rejected)
    $stmt = $conn->prepare("SELECT COUNT(*) as total, 
                           SUM(CASE WHEN quantity_approved IS NOT NULL THEN 1 ELSE 0 END) as processed 
                           FROM transaction_items_semarang WHERE transaction_id = ?");
    $stmt->bind_param("i", $transaction_id);
    $stmt->execute();
    $result = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    
    if ($result['total'] == $result['processed']) {
        // Check if any items were approved
        $stmt = $conn->prepare("SELECT COUNT(*) as approved FROM transaction_items_semarang 
                               WHERE transaction_id = ? AND quantity_approved > 0");
        $stmt->bind_param("i", $transaction_id);
        $stmt->execute();
        $approved = $stmt->get_result()->fetch_assoc()['approved'];
        $stmt->close();
        
        $new_status = ($approved > 0) ? 'Disetujui' : 'Ditolak';
        $new_status_peminjaman = ($approved > 0) ? 'Dipinjam' : 'None';
        
        $stmt = $conn->prepare("UPDATE stock_transactions_semarang SET status = ?, status_peminjaman = ? WHERE id = ?");
        $stmt->bind_param("ssi", $new_status, $new_status_peminjaman, $transaction_id);
        $stmt->execute();
        $stmt->close();
    }
    
    // Check if all approved items are returned
    $stmt = $conn->prepare("SELECT 
                          COUNT(*) as total_approved,
                          SUM(CASE WHEN dikembalikan = 1 THEN 1 ELSE 0 END) as total_returned
                          FROM transaction_items_semarang 
                          WHERE transaction_id = ? AND quantity_approved > 0");
    $stmt->bind_param("i", $transaction_id);
    $stmt->execute();
    $return_status = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    
    // If all approved items are returned, update the transaction status to 'Dikembalikan'
    if ($return_status['total_approved'] > 0 && 
        $return_status['total_approved'] == $return_status['total_returned']) {
        $status_peminjaman = 'Dikembalikan';
        $stmt = $conn->prepare("UPDATE stock_transactions_semarang SET status_peminjaman = ? WHERE id = ?");
        $stmt->bind_param("si", $status_peminjaman, $transaction_id);
        $stmt->execute();
        $stmt->close();
    }
}

// UPDATED: Function to determine if a return is on time or overdue based on 15-minute tolerance
function determineReturnStatus($item_data) {
    // Set timezone to match your server/database timezone
    date_default_timezone_set('Asia/Jakarta'); // Adjust this to your timezone
    
    // Get current datetime
    $current_datetime = new DateTime();
    
    // Create DateTime object for expected return time (tanggal_pengembalian)
    $expected_return_datetime = new DateTime($item_data['tanggal_pengembalian']);
    
    // Add 15 minutes tolerance to the expected return time
    $tolerance_datetime = clone $expected_return_datetime;
    $tolerance_datetime->add(new DateInterval('PT15M')); // Add 15 minutes
    
    // Debug logging (can be removed in production)
    error_log("=== RETURN STATUS CALCULATION DEBUG (IT USER) ===");
    error_log("Reservation: " . $item_data['reservation_number']);
    error_log("Current time: " . $current_datetime->format('Y-m-d H:i:s'));
    error_log("Expected return time: " . $expected_return_datetime->format('Y-m-d H:i:s'));
    error_log("Tolerance time (+ 15 min): " . $tolerance_datetime->format('Y-m-d H:i:s'));
    
    // Compare current time with tolerance time (expected return + 15 minutes)
    if ($current_datetime > $tolerance_datetime) {
        error_log("Status: OVERDUE (current time > tolerance time)");
        return 'overdue';
    } else {
        error_log("Status: TEPAT (current time <= tolerance time)");
        return 'tepat';
    }
}

// UPDATED: Function to get transactions based on tab
function getTransactions($conn, $tab, $filter_borrower, $filter_status, $filter_date_start, $filter_date_end) {
    // Base query
    $query = "SELECT st.*, bu.username as borrower_name, bu.lokasi_posisi
              FROM stock_transactions_semarang st
              JOIN borrower_users_semarang bu ON st.borrower_id = bu.id
              WHERE 1=1";
    
    $params = array();
    $types = "";
    
    // Tab specific conditions
    if ($tab === 'process') {
        // Transaksi Proses: status 'Diajukan', 'Ditolak', 'OnProccesing', atau 'Disetujui' dengan status_peminjaman 'Dipinjam'
        $query .= " AND (st.status IN ('Diajukan', 'Ditolak', 'OnProccesing') OR (st.status = 'Disetujui' AND st.status_peminjaman = 'Dipinjam'))";
    } else {
        // Transaksi Selesai: status 'Disetujui' dengan status_peminjaman 'Dikembalikan'
        $query .= " AND st.status = 'Disetujui' AND st.status_peminjaman = 'Dikembalikan'";
    }
    
    // Apply filters
    if (!empty($filter_borrower)) {
        $query .= " AND bu.id = ?";
        $params[] = $filter_borrower;
        $types .= "i";
    }
    
    // Status filter - only apply for process tab
    if (!empty($filter_status) && $tab === 'process') {
        if ($filter_status === 'Dipinjam') {
            $query .= " AND st.status = 'Disetujui' AND st.status_peminjaman = 'Dipinjam'";
        } else {
            $query .= " AND st.status = ?";
            $params[] = $filter_status;
            $types .= "s";
        }
    }
    
    if (!empty($filter_date_start)) {
        $query .= " AND st.tanggal_transaksi >= ?";
        $params[] = $filter_date_start . ' 00:00:00';
        $types .= "s";
    }
    
    if (!empty($filter_date_end)) {
        $query .= " AND st.tanggal_transaksi <= ?";
        $params[] = $filter_date_end . ' 23:59:59';
        $types .= "s";
    }
    
    // Add order by
    $query .= " ORDER BY st.tanggal_transaksi DESC";
    
    // Prepare and execute the query
    $stmt = $conn->prepare($query);
    if (!empty($params)) {
        $stmt->bind_param($types, ...$params);
    }
    $stmt->execute();
    $result = $stmt->get_result();
    $stmt->close();
    
    return $result;
}

// ENHANCED: Function to check and update overdue status for borrowed items with detailed logging
function updateOverdueStatus($conn) {
    // Set timezone to match your server/database timezone
    date_default_timezone_set('Asia/Jakarta');
    
    // Find all borrowed items that are past their tolerance time but not yet marked as overdue
    $query = "SELECT ti.id, st.tanggal_pengembalian, st.reservation_number
              FROM transaction_items_semarang ti
              JOIN stock_transactions_semarang st ON ti.transaction_id = st.id
              WHERE st.status_peminjaman = 'Dipinjam' 
              AND ti.dikembalikan = 0 
              AND ti.status_waktu != 'overdue'
              AND TIMESTAMPDIFF(MINUTE, st.tanggal_pengembalian, NOW()) > 15";
    
    $result = $conn->query($query);
    
    if ($result && $result->num_rows > 0) {
        $overdue_items = [];
        $reservation_numbers = [];
        while ($row = $result->fetch_assoc()) {
            $overdue_items[] = $row['id'];
            $reservation_numbers[] = $row['reservation_number'];
        }
        
        // Update overdue items
        if (!empty($overdue_items)) {
            $placeholders = str_repeat('?,', count($overdue_items) - 1) . '?';
            $update_query = "UPDATE transaction_items_semarang SET status_waktu = 'overdue' WHERE id IN ($placeholders)";
            $stmt = $conn->prepare($update_query);
            $stmt->bind_param(str_repeat('i', count($overdue_items)), ...$overdue_items);
            $stmt->execute();
            $stmt->close();
            
            // ENHANCED: Log overdue updates with reservation numbers
            $reservation_list = implode(', ', array_unique($reservation_numbers));
            error_log("Updated " . count($overdue_items) . " items to overdue status in reservations: " . $reservation_list);
            
            // Log to audit_logs_semarang table for IT user tracking
            if (isset($_SESSION['user_id'])) {
                log_activity($conn, $_SESSION['user_id'], 'it', 'Auto Update Overdue Status', 
                    "System automatically updated " . count($overdue_items) . " items to overdue status in reservations: " . $reservation_list);
            }
        }
    }
}

// NEW: Function to get available items for bulk selection
function getAvailableItemsForBulkAction($conn, $transaction_id) {
    $query = "SELECT ti.id, ti.quantity_approved, cs.id as stock_item_id, cs.status as item_status,
                     cs.kode_barang, mcp.code_color, cc.component_name
              FROM transaction_items_semarang ti
              JOIN color_stock_items_semarang cs ON ti.stock_item_id = cs.id
              JOIN master_color_panel_semarang mcp ON cs.color_id = mcp.id
              JOIN color_components_semarang cc ON cs.component_id = cc.id
              WHERE ti.transaction_id = ? 
              AND ti.quantity_approved IS NULL
              ORDER BY cs.kode_barang";
    
    $stmt = $conn->prepare($query);
    $stmt->bind_param("i", $transaction_id);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $available_items = [];
    $unavailable_items = [];
    
    while ($row = $result->fetch_assoc()) {
        if ($row['item_status'] === 'Tersedia') {
            $available_items[] = $row;
        } else {
            $unavailable_items[] = $row;
        }
    }
    
    $stmt->close();
    
    return [
        'available' => $available_items,
        'unavailable' => $unavailable_items,
        'total_available' => count($available_items),
        'total_unavailable' => count($unavailable_items)
    ];
}
?>