<?php
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 (removed expected_duration and duration_type)
    $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 ti 
                           JOIN color_stock_items cs ON ti.stock_item_id = cs.id
                           JOIN color_panel_components cpc ON cs.color_id = cpc.color_id AND cs.component_id = cpc.component_id
                           JOIN stock_transactions 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 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
                $new_quantity = $item_data['available_quantity'] - $quantity_approved;
                $stmt = $conn->prepare("UPDATE color_panel_components 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 st 
                                       JOIN borrower_users 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 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'], 'admin', 'Approve Item', 
                    "Admin 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 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'], 'admin', 'Reject Item', 
                    "Admin 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 st 
                                       JOIN borrower_users 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 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 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'], 'admin', 'OnProcess Item', 
                    "Admin 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 ===");
                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 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 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 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 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 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'], 'admin', 'Return Item', 
                    "Admin 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']}: " . $e->getMessage());
        
        $response['status'] = 'error';
        $response['message'] = 'Terjadi kesalahan: ' . $e->getMessage();
        return $response;
    }
}

// NEW: Function to handle bulk transaction actions
function handleBulkTransactionAction($conn) {
    $response = array();
    
    // Get the bulk action parameters
    $operation = isset($_POST['operation']) ? $_POST['operation'] : '';
    $transaction_id = isset($_POST['transaction_id']) ? intval($_POST['transaction_id']) : 0;
    $item_ids_string = isset($_POST['item_ids']) ? $_POST['item_ids'] : '';
    $note = isset($_POST['note']) ? $_POST['note'] : '';
    
    // Validate inputs
    if (empty($operation) || empty($item_ids_string) || $transaction_id <= 0) {
        $response['status'] = 'error';
        $response['message'] = 'Parameter tidak lengkap';
        return $response;
    }
    
    // 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;
    }
    
    // Get transaction information
    $stmt = $conn->prepare("SELECT reservation_number FROM stock_transactions WHERE id = ?");
    $stmt->bind_param("i", $transaction_id);
    $stmt->execute();
    $transaction_result = $stmt->get_result();
    if ($transaction_result->num_rows === 0) {
        $stmt->close();
        $response['status'] = 'error';
        $response['message'] = 'Transaksi tidak ditemukan';
        return $response;
    }
    $transaction_data = $transaction_result->fetch_assoc();
    $stmt->close();
    
    // Validate that all items are eligible for bulk action (only 'Tersedia' status and not yet processed)
    $placeholders = str_repeat('?,', count($item_ids) - 1) . '?';
    $validate_query = "
        SELECT ti.id, ti.quantity_approved, csi.keterangan, csi.status, csi.kode_barang
        FROM transaction_items ti 
        JOIN color_stock_items csi ON ti.stock_item_id = csi.id 
        WHERE ti.id IN ($placeholders) AND ti.transaction_id = ?
    ";
    
    $stmt = $conn->prepare($validate_query);
    $types = str_repeat('i', count($item_ids)) . 'i';
    $params = array_merge($item_ids, [$transaction_id]);
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $validation_result = $stmt->get_result();
    
    $valid_items = [];
    $invalid_items = [];
    
    while ($row = $validation_result->fetch_assoc()) {
        if ($row['quantity_approved'] !== null) {
            $invalid_items[] = [
                'id' => $row['id'],
                'kode_barang' => $row['kode_barang'],
                'reason' => 'Item sudah diproses'
            ];
        } elseif ($row['status'] !== 'Tersedia') {
            $invalid_items[] = [
                'id' => $row['id'],
                'kode_barang' => $row['kode_barang'],
                'reason' => 'Status item: ' . $row['status']
            ];
        } else {
            $valid_items[] = $row['id'];
        }
    }
    $stmt->close();
    
    if (empty($valid_items)) {
        $response['status'] = 'error';
        $response['message'] = 'Tidak ada item yang dapat diproses. Semua item sudah diproses atau tidak tersedia.';
        $response['invalid_items'] = $invalid_items;
        return $response;
    }
    
    // Begin transaction
    $conn->begin_transaction();
    
    try {
        $processed_count = 0;
        $success_items = [];
        $failed_items = [];
        
        foreach ($valid_items as $item_id) {
            try {
                // Get item details for processing
                $item_query = "
                    SELECT ti.*, cs.component_id, cs.color_id, cpc.available_quantity, 
                           st.tanggal_transaksi, st.tanggal_pengembalian, st.reservation_number,
                           csi.kode_barang, csi.keterangan
                    FROM transaction_items ti 
                    JOIN color_stock_items cs ON ti.stock_item_id = cs.id
                    JOIN color_panel_components cpc ON cs.color_id = cpc.color_id AND cs.component_id = cpc.component_id
                    JOIN stock_transactions st ON ti.transaction_id = st.id
                    JOIN color_stock_items csi ON ti.stock_item_id = csi.id
                    WHERE ti.id = ?
                ";
                
                $stmt = $conn->prepare($item_query);
                $stmt->bind_param("i", $item_id);
                $stmt->execute();
                $item_data = $stmt->get_result()->fetch_assoc();
                $stmt->close();
                
                if (!$item_data) {
                    $failed_items[] = ['id' => $item_id, 'reason' => 'Item data tidak ditemukan'];
                    continue;
                }
                
                switch ($operation) {
                    case 'approve':
                        // Update the item status to approved
                        $quantity_approved = 1;
                        $stmt = $conn->prepare("UPDATE transaction_items 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
                        $new_quantity = $item_data['available_quantity'] - $quantity_approved;
                        $stmt = $conn->prepare("UPDATE color_panel_components 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();
                        
                        // Get borrower location for item destination
                        $stmt = $conn->prepare("SELECT bu.lokasi_posisi 
                                               FROM stock_transactions st 
                                               JOIN borrower_users 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();
                        
                        // Update item location
                        $lokasi_tujuan = $item_data['lokasi_tujuan'] ?: $borrower_data['lokasi_posisi'];
                        $stmt = $conn->prepare("UPDATE color_stock_items SET status = 'Dipinjam', lokasi = ? 
                                               WHERE id = ?");
                        $stmt->bind_param("si", $lokasi_tujuan, $item_data['stock_item_id']);
                        $stmt->execute();
                        $stmt->close();
                        
                        $success_items[] = [
                            'id' => $item_id,
                            'kode_barang' => $item_data['kode_barang'],
                            'action' => 'Disetujui'
                        ];
                        break;
                        
                    case 'reject':
                        // Update the item status to rejected
                        $stmt = $conn->prepare("UPDATE transaction_items SET quantity_approved = 0, catatan = ? WHERE id = ?");
                        $stmt->bind_param("si", $note, $item_id);
                        $stmt->execute();
                        $stmt->close();
                        
                        $success_items[] = [
                            'id' => $item_id,
                            'kode_barang' => $item_data['kode_barang'],
                            'action' => 'Ditolak'
                        ];
                        break;
                        
                    case 'onprocess':
                        // Get the borrower data for automated note
                        $stmt = $conn->prepare("SELECT bu.username, st.tanggal_transaksi, st.tanggal_pengembalian 
                                               FROM stock_transactions st 
                                               JOIN borrower_users bu ON st.borrower_id = bu.id 
                                               WHERE st.id = ?");
                        $stmt->bind_param("i", $transaction_id);
                        $stmt->execute();
                        $transaction_info = $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_info['username']} ini mulai tanggal " . 
                                         date('Y-m-d H:i', strtotime($transaction_info['tanggal_transaksi'])) . 
                                         " sampai tanggal " . date('Y-m-d H:i', strtotime($transaction_info['tanggal_pengembalian'])) . ".";
                        
                        // Update the transaction item status to OnProcessing
                        $stmt = $conn->prepare("UPDATE transaction_items SET catatan = ? WHERE id = ?");
                        $stmt->bind_param("si", $automated_note, $item_id);
                        $stmt->execute();
                        $stmt->close();
                        
                        $success_items[] = [
                            'id' => $item_id,
                            'kode_barang' => $item_data['kode_barang'],
                            'action' => 'OnProcessing'
                        ];
                        break;
                        
                    default:
                        $failed_items[] = ['id' => $item_id, 'reason' => 'Operasi tidak valid'];
                        continue 2;
                }
                
                $processed_count++;
                
            } catch (Exception $e) {
                $failed_items[] = [
                    'id' => $item_id,
                    'reason' => 'Error: ' . $e->getMessage()
                ];
            }
        }
        
        // Update transaction status if needed
        if ($processed_count > 0) {
            updateTransactionStatus($conn, $transaction_id);
            
            // Update transaction status for onprocess operation
            if ($operation === 'onprocess' && $processed_count > 0) {
                $stmt = $conn->prepare("UPDATE stock_transactions SET status = 'OnProccesing' WHERE id = ?");
                $stmt->bind_param("i", $transaction_id);
                $stmt->execute();
                $stmt->close();
            }
        }
        
        // Commit transaction
        $conn->commit();
        
        // Log bulk action
        $action_text = '';
        switch ($operation) {
            case 'approve':
                $action_text = 'Bulk Approve Items';
                break;
            case 'reject':
                $action_text = 'Bulk Reject Items';
                break;
            case 'onprocess':
                $action_text = 'Bulk OnProcess Items';
                break;
        }
        
        log_activity($conn, $_SESSION['user_id'], 'admin', $action_text, 
            "Admin performed bulk {$operation} on {$processed_count} items in reservation: {$transaction_data['reservation_number']} (Transaction ID: {$transaction_id})");
        
        $response['status'] = 'success';
        $response['message'] = "Berhasil memproses {$processed_count} item dari " . count($item_ids) . " item yang dipilih";
        $response['processed_count'] = $processed_count;
        $response['total_selected'] = count($item_ids);
        $response['success_items'] = $success_items;
        $response['failed_items'] = $failed_items;
        $response['invalid_items'] = $invalid_items;
        
        return $response;
        
    } catch (Exception $e) {
        // Rollback transaction on error
        $conn->rollback();
        
        error_log("Bulk transaction action error for reservation {$transaction_data['reservation_number']}: " . $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 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 
                               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 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 
                          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 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 ===");
    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 (removed expected_duration and duration_type references)
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 st
              JOIN borrower_users 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 ti
              JOIN stock_transactions 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 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 table for admin tracking
            if (isset($_SESSION['user_id'])) {
                log_activity($conn, $_SESSION['user_id'], 'admin', 'Auto Update Overdue Status', 
                    "System automatically updated " . count($overdue_items) . " items to overdue status in reservations: " . $reservation_list);
            }
        }
    }
}
?>