<?php
function getTransactions($conn, $tab, $filter_borrower, $filter_status, $filter_date_start, $filter_date_end) {
    // Base query with updated table structure
    $query = "SELECT st.*, bu.username as borrower_name, bu.lokasi_posisi
              FROM stock_transactions_surabaya st
              JOIN borrower_users_surabaya 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 DATE(st.tanggal_transaksi) >= ?";
        $params[] = $filter_date_start;
        $types .= "s";
    }
    
    if (!empty($filter_date_end)) {
        $query .= " AND DATE(st.tanggal_transaksi) <= ?";
        $params[] = $filter_date_end;
        $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;
}

/**
 * Update transaction status based on item approvals
 */
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_surabaya 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_surabaya 
                               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_surabaya 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_surabaya 
                          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_surabaya SET status_peminjaman = ? WHERE id = ?");
        $stmt->bind_param("si", $status_peminjaman, $transaction_id);
        $stmt->execute();
        $stmt->close();
    }
}

/**
 * Calculate return time status
 */
function calculateReturnTimeStatus($transaction_data) {
    $now = new DateTime();
    $transaction_date = new DateTime($transaction_data['tanggal_transaksi']);
    
    // Calculate expected return date based on duration type
    if ($transaction_data['duration_type'] === 'hours') {
        $expected_return = clone $transaction_date;
        $expected_return->add(new DateInterval('PT' . $transaction_data['expected_duration'] . 'H'));
    } else {
        $expected_return = clone $transaction_date;
        $expected_return->add(new DateInterval('P' . $transaction_data['expected_duration'] . 'D'));
    }
    
    // Compare current time with expected return time
    if ($now <= $expected_return) {
        return 'tepat'; // On time or early
    } else {
        return 'overdue'; // Late
    }
}

/**
 * Get status badge HTML
 */
function getStatusBadge($status) {
    $badges = [
        'Diajukan' => 'bg-yellow-100 text-yellow-800',
        'Disetujui' => 'bg-green-100 text-green-800',
        'Ditolak' => 'bg-red-100 text-red-800',
        'OnProccesing' => 'bg-blue-100 text-blue-800'
    ];
    
    $class = isset($badges[$status]) ? $badges[$status] : 'bg-gray-100 text-gray-800';
    return '<span class="px-2 py-1 rounded-full text-xs font-semibold ' . $class . '">' . htmlspecialchars($status) . '</span>';
}

/**
 * Get status peminjaman badge HTML
 */
function getStatusPeminjamanBadge($status) {
    $badges = [
        'None' => 'bg-gray-100 text-gray-800',
        'Dipinjam' => 'bg-blue-100 text-blue-800',
        'Dikembalikan' => 'bg-green-100 text-green-800'
    ];
    
    $class = isset($badges[$status]) ? $badges[$status] : 'bg-gray-100 text-gray-800';
    return '<span class="px-2 py-1 rounded-full text-xs font-semibold ' . $class . '">' . htmlspecialchars($status) . '</span>';
}

/**
 * Get time status badge HTML
 */
function getTimeStatusBadge($status) {
    $badges = [
        'none' => ['class' => 'bg-gray-100 text-gray-800', 'text' => 'Dipinjam', 'indicator' => 'time-status-none'],
        'tepat' => ['class' => 'bg-green-100 text-green-800', 'text' => 'Tepat Waktu', 'indicator' => 'time-status-tepat'],
        'overdue' => ['class' => 'bg-red-100 text-red-800', 'text' => 'Terlambat', 'indicator' => 'time-status-overdue']
    ];
    
    $badge = isset($badges[$status]) ? $badges[$status] : $badges['none'];
    
    return '<span class="status-badge px-2 py-1 rounded-full text-xs font-semibold ' . $badge['class'] . '">' .
           '<span class="time-status-indicator ' . $badge['indicator'] . '"></span>' .
           htmlspecialchars($badge['text']) . '</span>';
}

/**
 * Check if borrower can borrow (no overdue items)
 */
function canBorrowerBorrow($conn, $borrower_id) {
    $stmt = $conn->prepare("
        SELECT COUNT(*) as overdue_count 
        FROM transaction_items_surabaya ti 
        JOIN stock_transactions_surabaya st ON ti.transaction_id = st.id 
        WHERE st.borrower_id = ? 
        AND ti.dikembalikan = 0 
        AND ti.quantity_approved > 0 
        AND ti.status_waktu = 'overdue'
    ");
    $stmt->bind_param("i", $borrower_id);
    $stmt->execute();
    $result = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    
    return $result['overdue_count'] == 0;
}

/**
 * Get available quantity for a color component
 */
function getAvailableQuantity($conn, $color_id, $component_id) {
    $stmt = $conn->prepare("
        SELECT available_quantity 
        FROM color_panel_components_surabaya 
        WHERE color_id = ? AND component_id = ?
    ");
    $stmt->bind_param("ii", $color_id, $component_id);
    $stmt->execute();
    $result = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    
    return $result ? $result['available_quantity'] : 0;
}

/**
 * Update available quantity for a color component
 */
function updateAvailableQuantity($conn, $color_id, $component_id, $new_quantity) {
    $stmt = $conn->prepare("
        UPDATE color_panel_components_surabaya 
        SET available_quantity = ? 
        WHERE color_id = ? AND component_id = ?
    ");
    $stmt->bind_param("iii", $new_quantity, $color_id, $component_id);
    $stmt->execute();
    $stmt->close();
}

/**
 * Generate response array for AJAX
 */
function generateResponse($status, $message, $data = null) {
    $response = [
        'status' => $status,
        'message' => $message
    ];
    
    if ($data !== null) {
        $response['data'] = $data;
    }
    
    return $response;
}
?>