<?php
function calculateAverageFuelConsumption($conn_rjllearning_specific, $institution_id, $bus_number, $branch_id = null, $start_date = null, $end_date = null) {
    // Start building the SQL query
    $query = "
        SELECT 
            bus_id,
            (MAX(meter_reading) - MIN(meter_reading)) / SUM(topup) AS average_fuel_consumption
        FROM 
            fuel_expense
        WHERE 
            institution_id = ?
            AND bus_id = ?
    ";

    // Initialize an array for the parameters
    $params = [$institution_id, $bus_number];

    // Check if branch_id is provided
    if ($branch_id) {
        $query .= " AND branch_id = ?"; // Add branch_id filter to the query
        $params[] = $branch_id;
    }

    // Check if date filters are provided
    if ($start_date && $end_date) {
        $query .= " AND date BETWEEN ? AND ?"; // Add date filter to the query
        $params[] = $start_date;
        $params[] = $end_date;
    }
    
    // Group by bus_number
    $query .= " GROUP BY bus_id";

    // Prepare the SQL statement
    $stmt = $conn_rjllearning_specific->prepare($query);
    
    // Execute the statement with the bound parameters
    $stmt->execute($params);
    
    // Fetch the result
    $result = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($result) {
        // Round the average fuel consumption to two decimal places
        $roundedFuelConsumption = round($result['average_fuel_consumption'], 2);
        
        // Append ' per liter' to the rounded value
        $fuelConsumptionWithLabel = $roundedFuelConsumption;
        
        // Return the bus_number and the rounded average fuel consumption with 'per liter'
        return array(
            'bus_number' => $result['bus_id'], 
            'average_fuel_consumption' => $fuelConsumptionWithLabel
        );
    } else {
        // If no data is found, return null or handle accordingly
        return null;
    }
}





// function calculateAverageFuelConsumption($conn_rjllearning_specific, $institution_id, $branch_id, $start_date, $end_date, $bus_id = null) {
//     // Start building the SQL query
//     $query = "
//         SELECT 
//             bus_id,
//             (MAX(meter_reading) - MIN(meter_reading)) / SUM(topup) AS average_fuel_consumption
//         FROM 
//             fuel_expense
//         WHERE 
//             institution_id = ?
//             AND branch_id = ?
//             AND date BETWEEN ? AND ?
//     ";
    
//     // Append condition for bus_id if it is not null
//     if ($bus_id !== null) {
//         $query .= " AND bus_id = ?";
//     }

//     $query .= " GROUP BY bus_id";

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

//     // Bind the parameters
//     if ($bus_id !== null) {
//         $stmt->execute(array($institution_id, $branch_id, $start_date, $end_date, $bus_id));
//     } else {
//         $stmt->execute(array($institution_id, $branch_id, $start_date, $end_date));
//     }

//     // Fetch the result
//     $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

//     if ($bus_id === null) {
//         // Calculate the overall average fuel consumption when bus_id is not provided
//         $totalConsumption = 0;
//         $totalBuses = 0;

//         foreach ($results as $result) {
//             $totalConsumption += $result['average_fuel_consumption'];
//             $totalBuses++;
//         }

//         $averageConsumptionAllBuses = $totalBuses > 0 ? $totalConsumption / $totalBuses : 0;

//         // Include the overall average in the response
//         $results[] = [
//             'bus_id' => 'all',
//             'average_fuel_consumption' => $averageConsumptionAllBuses
//         ];
//     }

//     return $results;
// }


// function calculateAverageFuelConsumption($conn_rjllearning_specific, $institution_id, $branch_id, $start_date, $end_date, $bus_id = null) {
//     // Start building the SQL query
//     $query = "
//         SELECT 
//             bus_id,
//             (MAX(meter_reading) - MIN(meter_reading)) / SUM(topup) AS average_fuel_consumption
//         FROM 
//             fuel_expense
//         WHERE 
//             institution_id = ?
//             AND branch_id = ?
//             AND date BETWEEN ? AND ?
//     ";
    
//     // Append condition for bus_id if it is not null
//     if ($bus_id !== null) {
//         $query .= " AND bus_id = ?";
//     }

//     $query .= " GROUP BY bus_id";

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

//     // Bind the parameters
//     if ($bus_id !== null) {
//         $stmt->execute(array($institution_id, $branch_id, $start_date, $end_date, $bus_id));
//     } else {
//         $stmt->execute(array($institution_id, $branch_id, $start_date, $end_date));
//     }

//     // Fetch the result
//     $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

//     if ($bus_id === null) {
//         // Calculate the overall average fuel consumption when bus_id is not provided
//         $totalConsumption = 0;
//         $totalBuses = 0;

//         foreach ($results as &$result) {
//             // Round the individual bus average fuel consumption to two decimal places
//             $result['average_fuel_consumption'] = round($result['average_fuel_consumption'], 2);
//             $totalConsumption += $result['average_fuel_consumption'];
//             $totalBuses++;
//         }

//         // Calculate overall average consumption for all buses
//         $averageConsumptionAllBuses = $totalBuses > 0 ? $totalConsumption / $totalBuses : 0;

//         // Include the overall average in the response, rounded to two decimal places
//         $results[] = [
//             'bus_id' => 'all',
//             'average_fuel_consumption' => round($averageConsumptionAllBuses, 2)
//         ];
//     } else {
//         // For specific bus_id, round the average fuel consumption
//         foreach ($results as &$result) {
//             $result['average_fuel_consumption'] = round($result['average_fuel_consumption'], 2);
//         }
//     }

//     // Append 'per liter' after all calculations are done
//     foreach ($results as &$result) {
//         $result['average_fuel_consumption'] = $result['average_fuel_consumption'] . " per liter";
//     }

//     return $results;
// }
