<?php 

$customer_data = [];
$sql = Querydb("SELECT * from customers WHERE business_id = '$business_id'");
foreach ($sql as $customer) {
    $customer_data[$customer['customer_id']] = ['address' => $customer['address']];
}
 
$customer_result = Querydb('SELECT * FROM customers');
$customer_data_map = [];
foreach ($customer_result as $customer) {
    $customer_data_map[$customer['customer_id']] = ['address' => $customer['address']];
}

// Get the current month and year for comparison
$current_month = date('m');
$current_year = date('Y');
$last_month_timestamp = strtotime('last month');
$last_month = date('m', $last_month_timestamp);
$last_month_year = date('Y', $last_month_timestamp);
// Assuming $business_id is retrieved from your session: $_SESSION['business_id']
$current_cost_query = "
    SELECT SUM(cost) as total 
    FROM maintenance 
    WHERE business_id = '$business_id' 
    AND `month` = '$current_month' 
    AND `year` = '$current_year'
";
$current_result = Querydb($current_cost_query);
$current_total = mysqli_fetch_assoc($current_result)['total'] ?? 0;
$last_cost_query = "
    SELECT SUM(cost) as total 
    FROM maintenance 
    WHERE business_id = '$business_id' 
    AND `month` = '$last_month' 
    AND `year` = '$last_month_year'
";
$last_result = Querydb($last_cost_query);
$last_total = mysqli_fetch_assoc($last_result)['total'] ?? 0;
$current_total = (float) $current_total;
$last_total = (float) $last_total;
if ($last_total > 0) {
    $difference = $current_total - $last_total;
    $percentage = round(($difference / $last_total) * 100);
    $trend_text = ($percentage >= 0) ? "<span class='text-success'><i data-feather='trending-up' width='15'></i> +{$percentage}%</span>" : "<span class='text-danger'><i data-feather='trending-down' width='15'></i> {$percentage}%</span>";
} else {
    $trend_text = ($current_total > 0) ? "<span class='text-success'>N/A (First Month)</span>" : "N/A";
}


// Assuming $business_id is retrieved from your session: $_SESSION['business_id']
$chart_query = "SELECT 
                    DATE_FORMAT(date_performed, '%b-%y') AS month_label, 
                    SUM(cost) AS total_cost 
                FROM maintenance 
                WHERE business_id = '$business_id' 
                AND date_performed >= DATE_SUB(NOW(), INTERVAL 6 MONTH) 
                GROUP BY month_label, DATE_FORMAT(date_performed, '%Y-%m')
                ORDER BY DATE_FORMAT(date_performed, '%Y-%m') ASC";
$chart_results = Querydb($chart_query);
$chart_data_array = [];
if (is_array($chart_results)) {
    foreach ($chart_results as $row) {
        $chart_data_array[] = [
            'label' => $row['month_label'],
            'cost' => (float)$row['total_cost']
        ];
    }
} 
else if ($chart_results) { 
    while ($row = mysqli_fetch_assoc($chart_results)) {
        $chart_data_array[] = [
            'label' => $row['month_label'],
            'cost' => (float)$row['total_cost']
        ];
    }
}
$chart_data_json = json_encode($chart_data_array);


// Chart for truck trip count query 

// $result = Querydb("
//     SELECT 
//         t.license_plate AS label, 
//         COUNT(tr.truck_id) AS trip_count
//     FROM 
//         trucks t
//     LEFT JOIN 
//         trips tr ON t.truck_id = tr.truck_id 
//     GROUP BY 
//         t.license_plate
//     ORDER BY 
//         trip_count DESC
// ");
// $chart_data = [];
// if (is_array($result)) {
//     foreach ($result as $row) {
//         $chart_data[] = [
//             'license_plate' => $row['label'],
//             'trips' => (int)$row['trip_count']
//         ];
//     }
// } else if ($result) { 
//     while ($row = mysqli_fetch_assoc($result)) {
//         $chart_data[] = [
//             'license_plate' => $row['label'],
//             'trips' => (int)$row['trip_count']
//         ];
//     }
// }
// $json_data = json_encode($chart_data);





// Query for Bar Chart: Maintenance Cost per Truck
// $filter = $_GET['filter'] ?? 'month';
// $start_date = $_GET['start_date'] ?? '';
// $end_date = $_GET['end_date'] ?? '';

// $date_condition = "";
// $prev_condition = "";

// switch ($filter) {
//     case 'custom':
//         if (!empty($start_date) && !empty($end_date)) {
//             // Current Range
//             $date_condition = "WHERE DATE(FROM_UNIXTIME(m.create_at)) BETWEEN '$start_date' AND '$end_date'";
            
//             // Calculate Interval for Previous Period
//             $diff = strtotime($end_date) - strtotime($start_date);
//             $days = round($diff / (60 * 60 * 24)) + 1;
            
//             $prev_s = date('Y-m-d', strtotime($start_date . " - $days days"));
//             $prev_e = date('Y-m-d', strtotime($end_date . " - $days days"));
//         }
//         break;

//     case 'day':
//         $date_condition = "WHERE DATE(FROM_UNIXTIME(m.create_at)) = CURDATE()";
//         break;
        
//     case 'week':
//         $date_condition = "WHERE YEARWEEK(FROM_UNIXTIME(m.create_at), 1) = YEARWEEK(CURDATE(), 1)";
//         break;

//     case 'month':
//         $date_condition = "WHERE MONTH(FROM_UNIXTIME(m.create_at)) = MONTH(CURDATE()) AND YEAR(FROM_UNIXTIME(m.create_at)) = YEAR(CURDATE())";
//         break;

//     case 'year':
//     default:
//         $date_condition = "WHERE YEAR(FROM_UNIXTIME(m.create_at)) = YEAR(CURDATE())";
//         break;
// }
// // 1. Truck Cost Query (Bar Chart)
// $truck_cost_query = "
//     SELECT t.license_plate, SUM(m.cost) as total_cost 
//     FROM maintenance m
//     LEFT JOIN trucks t ON m.truck_id = t.truck_id 
//     $date_condition 
//     AND m.business_id = '$business_id' 
//     GROUP BY t.license_plate 
//     ORDER BY total_cost DESC
// ";

// $truck_results = Querydb($truck_cost_query);
// $truck_labels = []; $truck_data = [];
    
// foreach ($truck_results as $row) {
//     $truck_labels[] = $row['license_plate']; 
//     $truck_data[] = (float)$row['total_cost'];
// }

// // 2. Service Type Query (Pie Chart)
// // Note: We use 'm' alias here to stay consistent with the switch condition
// $cat_query = "
//     SELECT service_type, SUM(cost) as total 
//     FROM maintenance m 
//     $date_condition AND m.business_id = '$business_id' 
//     GROUP BY service_type
// ";

// $cat_results = Querydb($cat_query);
// $cat_labels = []; $cat_data = [];

// foreach ($cat_results as $row) {
//     $cat_labels[] = $row['service_type'];
//     $cat_data[] = (float)$row['total'];
// }

// // 3. Package for JSON
// $combined_data = [
//     'truck_labels' => $truck_labels,
//     'truck_data'   => $truck_data,
//     'cat_labels'   => $cat_labels,
//     'cat_data'     => $cat_data
// ];

// $main_data = json_encode($combined_data);



// Get the filter value from the URL, default to 'month'
$filter = $_GET['filter'] ?? 'month';
$start_date = $_GET['start_date'] ?? '';
$end_date = $_GET['end_date'] ?? '';

$date_condition = "";
$prev_condition = "";

switch ($filter) {
    case 'custom':
        if (!empty($start_date) && !empty($end_date)) {
            // Current Range
            $date_condition = "WHERE DATE(FROM_UNIXTIME(t.create_at)) BETWEEN '$start_date' AND '$end_date' AND status = 'completed'";
            
            // Calculate Interval for Previous Period
            $diff = strtotime($end_date) - strtotime($start_date);
            $days = round($diff / (60 * 60 * 24)) + 1;
            
            $prev_s = date('Y-m-d', strtotime($start_date . " - $days days"));
            $prev_e = date('Y-m-d', strtotime($end_date . " - $days days"));
            
            $prev_condition = "WHERE DATE(FROM_UNIXTIME(t.create_at)) BETWEEN '$prev_s' AND '$prev_e' AND status = 'completed'";
        }
        break;

    case 'day':
        $date_condition = "WHERE DATE(FROM_UNIXTIME(t.create_at)) = CURDATE() AND status = 'completed'";
        $prev_condition = "WHERE DATE(FROM_UNIXTIME(t.create_at)) = DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND status = 'completed'";
        break;
        
    case 'week':
        $date_condition = "WHERE YEARWEEK(FROM_UNIXTIME(t.create_at), 1) = YEARWEEK(CURDATE(), 1) AND status = 'completed'";
        $prev_condition = "WHERE YEARWEEK(FROM_UNIXTIME(t.create_at), 1) = YEARWEEK(DATE_SUB(CURDATE(), INTERVAL 1 WEEK), 1) AND status = 'completed'";
        break;

    case 'month':
        $date_condition = "WHERE MONTH(FROM_UNIXTIME(t.create_at)) = MONTH(CURDATE()) AND YEAR(FROM_UNIXTIME(t.create_at)) = YEAR(CURDATE()) AND status = 'completed'";
        $prev_condition = "WHERE MONTH(FROM_UNIXTIME(t.create_at)) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND YEAR(FROM_UNIXTIME(t.create_at)) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND status = 'completed'";
        break;

    case 'year':
    default:
        $date_condition = "WHERE YEAR(FROM_UNIXTIME(t.create_at)) = YEAR(CURDATE()) AND status = 'completed'";
        $prev_condition = "WHERE YEAR(FROM_UNIXTIME(t.create_at)) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) AND status = 'completed'";
        break;
}

// Updated Query with Date Condition
$query = "
    SELECT c.name as customer_name, sum(t.unit) as trip_count 
    FROM trips t
    INNER JOIN customers c ON t.customer_id = c.customer_id
    $date_condition AND t.business_id = '$business_id'
    GROUP BY t.customer_id 
    ORDER BY trip_count DESC 
    LIMIT 10
";

$trip_count_sql = Querydb($query);
$customer_names = [];
$trip_totals = [];

while ($row = mysqli_fetch_assoc($trip_count_sql)) {
    $customer_names[] = $row['customer_name'] ?? 'Unknown';
    $trip_totals[] = (int)$row['trip_count'];
}

$custome_trip = [
    'customer_name' => $customer_names ?? [],
    'trip_count' => $trip_totals ?? []
];

$trip_totals = json_encode($custome_trip);




$filter = $_GET['filter'] ?? 'month';
$start_date = $_GET['start_date'] ?? '';
$end_date = $_GET['end_date'] ?? '';

$filter_trips = "";
$filter_maint = "";
$filter_earnings = "";
$filter_atc = "";

switch ($filter) {
    case 'custom':
        if (!empty($start_date) && !empty($end_date)) {
            $cond = "DATE(FROM_UNIXTIME(create_at)) BETWEEN '$start_date' AND '$end_date'";
            
            // Logic for the comparison period (Previous X days)
            $diff = strtotime($end_date) - strtotime($start_date);
            $days = round($diff / (60 * 60 * 24)) + 1;
            $prev_s = date('Y-m-d', strtotime($start_date . " - $days days"));
            $prev_e = date('Y-m-d', strtotime($end_date . " - $days days"));
            $prev_cond = "DATE(FROM_UNIXTIME(create_at)) BETWEEN '$prev_s' AND '$prev_e' AND business_id = '$business_id'";
        }
        break;
        
    case 'day':
        $cond = "DATE(FROM_UNIXTIME(create_at)) = CURDATE()";
        $prev_cond = "DATE(FROM_UNIXTIME(create_at)) = DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND business_id = '$business_id'";
        break;

    case 'week':
        $cond = "YEARWEEK(FROM_UNIXTIME(create_at), 1) = YEARWEEK(CURDATE(), 1)";
        $prev_cond = "YEARWEEK(FROM_UNIXTIME(create_at), 1) = YEARWEEK(DATE_SUB(CURDATE(), INTERVAL 1 WEEK), 1) AND business_id = '$business_id'";
        break;

    case 'month':
        $cond = "MONTH(FROM_UNIXTIME(create_at)) = MONTH(CURDATE()) AND YEAR(FROM_UNIXTIME(create_at)) = YEAR(CURDATE())";
        $prev_cond = "MONTH(FROM_UNIXTIME(create_at)) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND YEAR(FROM_UNIXTIME(create_at)) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND business_id = '$business_id'";
        break;

    case 'year':
    default:
        $cond = "YEAR(FROM_UNIXTIME(create_at)) = YEAR(CURDATE()) ";
        $prev_cond = "YEAR(FROM_UNIXTIME(create_at)) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) AND business_id = '$business_id' ";
        break;
}

// Prefixing with WHERE for the queries
$filter_trips = "WHERE " . $cond ." AND business_id = '$business_id'";
$filter_maint = "WHERE " . $cond ." AND business_id = '$business_id'";
$filter_earnings = "WHERE " . $cond ." AND business_id = '$business_id'";
$filter_atc = "WHERE " . $cond ." AND business_id = '$business_id'";

    // 2. Total Trips & Completed Trips
    // Using the conditional logic within the filter
    $trip_query = "SELECT COUNT(DISTINCT atc_id) as total, 
                   SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END) as finished 
                   FROM trips t $filter_trips AND status = 'Completed'";
    $trip_sql = Querydb($trip_query);
    $trip_result = mysqli_fetch_assoc($trip_sql);
    $total_trips = $trip_result['total'] ?? 0;
    $completed_trips = $trip_result['finished'] ?? 0;

// CURRENT TOTALS
    $curr_m = mysqli_fetch_assoc(Querydb("SELECT SUM(cost) as total FROM maintenance m $filter_trips"))['total'] ?? 0;
    $curr_t = mysqli_fetch_assoc(Querydb("SELECT SUM(driver_allowence) as driver_allowence, sum(cost_price * unit) as cost_price, sum(cost_per_liter * liters_filled) as total_fuel FROM trips t $filter_trips AND status = 'Completed'"));
    $curr_tri = $curr_t['driver_allowence'] + $curr_t['cost_price'] + $curr_t['total_fuel']; 
    $total_maintenance = $curr_m + $curr_tri;

    // PREVIOUS TOTALS
    // Make sure $prev_cond doesn't have "WHERE" at the start if you're adding it manually
    $prev_m = mysqli_fetch_assoc(Querydb("SELECT SUM(cost) as total FROM maintenance m WHERE $prev_cond"))['total'] ?? 0;
    $prev_t = mysqli_fetch_assoc(Querydb("SELECT SUM(driver_allowence + (cost_price * unit) + (cost_per_liter * liters_filled)) as total FROM trips t WHERE $prev_cond AND status = 'Completed'"))['total'] ?? 0;
    $prev_total_maintenance = $prev_m + $prev_t;

    // 3. Calculate Difference for the badge
    $maint_diff = $total_maintenance - $prev_total_maintenance;
    $maint_trend_color = ($total_maintenance > $prev_total_maintenance) ? 'text-danger' : 'text-success';

    // 4. Sales Volume (from atc_records table)
    $vol_query = "SELECT SUM(unit) as total_vol FROM trips t $filter_atc AND status = 'Completed' ";
    $vol_sql = Querydb($vol_query);
    $vol_result = mysqli_fetch_assoc($vol_sql);
    $total_sales_vol = $vol_result['total_vol'] ?? 0;


    // 1. Get Current Total Trips (using your existing $filter_trips logic)
    $curr_sql = Querydb("SELECT COUNT(*) as total FROM trips t $filter_trips AND status = 'Completed'");
    $curr_trips = mysqli_fetch_assoc($curr_sql)['total'] ?? 0;

    // 2. Get Previous Total Trips
    $prev_sql = Querydb("SELECT COUNT(*) as total FROM trips t where $prev_cond AND status = 'Completed'");
    $prev_trips = mysqli_fetch_assoc($prev_sql)['total'] ?? 0;

    // 3. Calculate Percentage Change
    if ($prev_trips > 0) {
        $diff = (($curr_trips - $prev_trips) / $prev_trips) * 100;
        $trend_color = ($diff >= 0) ? 'text-white' : 'text-danger';
        $trend_icon = ($diff >= 0) ? '↑' : '↓';
    } else {
        $diff = 0;
        $trend_color = 'text-muted';
        $trend_icon = '';
    }
            

$filter = $_GET['filter'] ?? 'month';
$start_date = $_GET['start_date'] ?? '';
$end_date = $_GET['end_date'] ?? '';

$cond = "";
$prev_cond = "";

switch ($filter) {
    case 'custom':
        if (!empty($start_date) && !empty($end_date)) {
            $cond = "DATE(FROM_UNIXTIME(create_at)) BETWEEN '$start_date' AND '$end_date' AND business_id = '$business_id'";
            $diff = strtotime($end_date) - strtotime($start_date);
            $days = round($diff / (60 * 60 * 24)) + 1;
            $prev_s = date('Y-m-d', strtotime($start_date . " - $days days"));
            $prev_e = date('Y-m-d', strtotime($end_date . " - $days days"));
            $prev_cond = "DATE(FROM_UNIXTIME(create_at)) BETWEEN '$prev_s' AND '$prev_e' AND business_id = '$business_id'";
        }
        break;
    case 'day':
        $cond = "DATE(FROM_UNIXTIME(create_at)) = CURDATE() AND business_id = '$business_id'";
        $prev_cond = "DATE(FROM_UNIXTIME(create_at)) = DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND business_id = '$business_id'";
        break;
    case 'week':
        $cond = "YEARWEEK(FROM_UNIXTIME(create_at), 1) = YEARWEEK(CURDATE(), 1) AND business_id = '$business_id'";
        $prev_cond = "YEARWEEK(FROM_UNIXTIME(create_at), 1) = YEARWEEK(DATE_SUB(CURDATE(), INTERVAL 1 WEEK), 1) AND business_id = '$business_id'";
        break;
    case 'month':
        $cond = "MONTH(FROM_UNIXTIME(create_at)) = MONTH(CURDATE()) AND YEAR(FROM_UNIXTIME(create_at)) = YEAR(CURDATE()) AND business_id = '$business_id'";
        $prev_cond = "MONTH(FROM_UNIXTIME(create_at)) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND YEAR(FROM_UNIXTIME(create_at)) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND business_id = '$business_id'";
        break;
    case 'year':
    default:
        $cond = "YEAR(FROM_UNIXTIME(create_at)) = YEAR(CURDATE()) AND business_id = '$business_id'";
        $prev_cond = "YEAR(FROM_UNIXTIME(create_at)) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) AND business_id = '$business_id'";
        break;
}

// Fetch Current Totals
$sql = Querydb("
    SELECT 
    /* --- Current Period Totals (Single Scan) --- */
    SUM(CASE WHEN $cond THEN (haulage_amount * unit) ELSE 0 END) AS current_haulage,
    SUM(CASE WHEN $cond THEN (promo_amount * unit) ELSE 0 END) AS current_promo,
    SUM(CASE WHEN $cond THEN (incentive_amount * unit) ELSE 0 END) AS current_incentive,
    SUM(CASE WHEN $cond THEN (selling_price * unit) ELSE 0 END) AS current_selling,
    SUM(CASE WHEN $cond THEN (cost_per_liter * liters_filled) ELSE 0 END) AS current_fuel,
    SUM(CASE WHEN $cond THEN (cost_price * unit) ELSE 0 END) AS current_cost,
    SUM(CASE WHEN $cond THEN driver_allowence ELSE 0 END) AS current_allowance,

    /* --- Previous Period Totals (Same Scan) --- */
    SUM(CASE WHEN $prev_cond THEN (haulage_amount * unit) ELSE 0 END) AS prev_haulage,
    SUM(CASE WHEN $prev_cond THEN (promo_amount * unit) ELSE 0 END) AS prev_promo,
    SUM(CASE WHEN $prev_cond THEN (incentive_amount * unit) ELSE 0 END) AS prev_incentive,
    SUM(CASE WHEN $prev_cond THEN (selling_price * unit) ELSE 0 END) AS prev_selling,

    /* --- Maintenance (External Table Subquery) --- */
    (SELECT COALESCE(SUM(cost), 0) FROM maintenance WHERE $cond AND status = 'Completed') AS current_maintenance

FROM trips 
WHERE status = 'Completed' 
  AND (($cond) OR ($prev_cond))
");

$result = mysqli_fetch_assoc($sql);

// Calculate Gross Revenue (What you billed the customer)
$haul = $result['current_haulage'] ?? 0;
$promo = $result['current_promo'] ?? 0;
$inc = $result['current_incentive'] ?? 0;
$sales = $result['current_selling'] ?? 0;
$gross_revenue = $haul + $promo + $inc + $sales;

// Calculate Total Operating Costs (Maintenance + Fuel + Driver Pay)
$maintenance_costs = $result['current_maintenance'] ?? 0;
$fuel_costs = $result['current_fuel'] ?? 0;
$allowances = $result['current_allowance'] ?? 0;
$cost_price = $result['current_cost'] ?? 0;
$operating_costs = $maintenance_costs + $fuel_costs + $allowances + $cost_price;

// Net Profit after Promos and Expenses
$net_profit = $gross_revenue - $operating_costs;

// Margin & Growth
$haul_prev = $result['prev_haulage'] ?? 0;
$promo_prev = $result['prev_promo'] ?? 0;
$inc_prev = $result['prev_incentive'] ?? 0;
$sales_prev = $result['prev_selling'] ?? 0;
$prev_revenue = $haul_prev + $promo_prev + $inc_prev + $sales_prev;
$margin_percentage = ($gross_revenue > 0) ? ($net_profit / $gross_revenue) * 100 : 0;
$rev_diff = ($prev_revenue > 0) ? (($gross_revenue - $prev_revenue) / $prev_revenue) * 100 : 0;

// Health Color Logic
$health_color = ($margin_percentage >= 20) ? 'text-success' : (($margin_percentage > 0) ? 'text-warning' : 'text-danger');

// Revenue Trend Calculation
$rev_diff = ($prev_revenue > 0) ? (($gross_revenue - $prev_revenue) / $prev_revenue) * 100 : 0;
$rev_trend_color = ($rev_diff >= 0) ? 'text-success' : 'text-danger';   
if ($prev_revenue > 0) {
    $revenue_ratio = ($gross_revenue / $prev_revenue) * 100;
} else {
    $revenue_ratio = ($gross_revenue > 0) ? 100 : 0;
}
$bar_width = min($revenue_ratio, 100);
$rev_diff_percent = ($prev_revenue > 0) ? (($gross_revenue - $prev_revenue) / $prev_revenue) * 100 : 0;  


$earnings_current_total = (float)$gross_revenue;
$last_total = (float)$prev_revenue ?? 0;
$percentage_change = 0;
if ($last_total > 0) {
    $raw_percentage = (($earnings_current_total - $last_total) / $last_total) * 100;
    $percentage_change = round($raw_percentage);
    $radial_value = round(($earnings_current_total / max(1, $last_total)) * 100);
    $radial_value = min(100, $radial_value); 
} else {
    $radial_value = ($earnings_current_total > 0) ? 100 : 0;
    $percentage_change = 0;
}

$formatted_current_total = number_format($earnings_current_total, 0, '.', ',');
$formatted_last_total = number_format($last_total, 0, '.', ',');

$trend_sign = ($percentage_change >= 0) ? '+' : '-';
$trend_class = ($percentage_change >= 0) ? 'text-success' : 'text-danger';
$trend_text_earn = "$trend_sign{$percentage_change}%";









$filter = $_GET['filter'] ?? 'month';
$start_date = $_GET['start_date'] ?? '';
$end_date = $_GET['end_date'] ?? '';

// $t is the alias (e.g., 'm' for maintenance, 't' for trips)
$t = "m"; 

switch ($filter) {
    case 'custom':
        if (!empty($start_date) && !empty($end_date)) {
            $cond = "DATE(FROM_UNIXTIME($t.create_at)) BETWEEN '$start_date' AND '$end_date' AND $t.business_id = '$business_id'";
            
            // Logic for the comparison period (Previous X days)
            $diff = strtotime($end_date) - strtotime($start_date);
            $days = round($diff / (60 * 60 * 24)) + 1;
            $prev_s = date('Y-m-d', strtotime($start_date . " - $days days"));
            $prev_e = date('Y-m-d', strtotime($end_date . " - $days days"));
            $prev_cond = "DATE(FROM_UNIXTIME($t.create_at)) BETWEEN '$prev_s' AND '$prev_e' AND $t.business_id = '$business_id'";
        }
        break;
        
    case 'day':
        $cond = "DATE(FROM_UNIXTIME($t.create_at)) = CURDATE()";
        $prev_cond = "DATE(FROM_UNIXTIME($t.create_at)) = DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND $t.business_id = '$business_id'";
        break;

    case 'week':
        $cond = "YEARWEEK(FROM_UNIXTIME($t.create_at), 1) = YEARWEEK(CURDATE(), 1)";
        $prev_cond = "YEARWEEK(FROM_UNIXTIME($t.create_at), 1) = YEARWEEK(DATE_SUB(CURDATE(), INTERVAL 1 WEEK), 1) AND $t.business_id = '$business_id'";
        break;

    case 'month':
        $cond = "MONTH(FROM_UNIXTIME($t.create_at)) = MONTH(CURDATE()) AND YEAR(FROM_UNIXTIME($t.create_at)) = YEAR(CURDATE()) AND $t.business_id = '$business_id'";
        $prev_cond = "MONTH(FROM_UNIXTIME($t.create_at)) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND YEAR(FROM_UNIXTIME($t.create_at)) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND $t.business_id = '$business_id'";
        break;

    case 'year':
    default:
        $cond = "YEAR(FROM_UNIXTIME($t.create_at)) = YEAR(CURDATE()) AND $t.business_id = '$business_id'";
        $prev_cond = "YEAR(FROM_UNIXTIME($t.create_at)) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) AND $t.business_id = '$business_id'";
        break;
}

// Query for Current Period Maintenance per Truck with disambiguated columns
$cond_trips = str_replace('m.create_at', 'tr.create_at', $cond);

// Assuming $business_id is from your session
$truck_table_query = "
    SELECT 
        t.license_plate, 
        SUM(m.cost) as current_maint_total,
        COUNT(DISTINCT tr.trip_id) as trip_count,
        (SELECT SUM(m2.cost) 
         FROM maintenance m2 
         WHERE m2.truck_id = t.truck_id 
         AND m2.business_id = '$business_id' -- Critical: Filter subquery
         AND " . str_replace('m.create_at', 'm2.create_at', $prev_cond) . ") as prev_maint_total
    FROM trucks t
    LEFT JOIN maintenance m ON t.truck_id = m.truck_id 
        AND m.business_id = '$business_id' -- Filter Join
        AND $cond
    LEFT JOIN trips tr ON t.truck_id = tr.truck_id 
        AND tr.status = 'Completed' 
        AND tr.business_id = '$business_id' -- Filter Join
        AND $cond_trips
    WHERE t.business_id = '$business_id' -- Filter Main Table
    GROUP BY t.truck_id
    ORDER BY current_maint_total DESC
";

$truck_table_results = Querydb($truck_table_query);



























 ?>