<?php
$veh_id = $fleet_id; // The vehicle ID to check

// Check contracts for the original vehicle
$original_query = $db2->prepare("
    SELECT 
        contract_id, 
        IFNULL(ce.ext_new_date, c.cont_return) AS effective_return_date 
    FROM contracts c
    LEFT JOIN contract_extensions ce ON c.cont_ext_id = ce.cont_ext_cid
    WHERE 
        c.vehfleet_id = ? 
        AND c.cont_cancelled = 0 
        AND c.returned = 0
        AND (NOW() < IFNULL(ce.ext_new_date, c.cont_return))
    LIMIT 1;
");
$original_query->bind_param("i", $veh_id);
$original_query->execute();
$original_result = $original_query->get_result();

// If no active contract for the original vehicle, check for vehicle changes
if ($original_result && $contract = $original_result->fetch_assoc()) {
    $return_date = $contract['effective_return_date'];
    echo "<p><strong>Status:</strong> Out on Rental</p>";
    echo "<p><strong>Return Date:</strong> " . date('d/m/Y H:i', $return_date) . "</p>";
} else {
    $changed_query = $db2->prepare("
        SELECT 
            vc.veh_id AS changed_vehicle_id, 
            IFNULL(ce.ext_new_date, c.cont_return) AS effective_return_date
        FROM veh_change vc
        JOIN contracts c ON vc.contract_id = c.contract_id
        LEFT JOIN contract_extensions ce ON c.cont_ext_id = ce.cont_ext_cid
        WHERE 
            vc.veh_id = ? 
            AND c.cont_cancelled = 0 
            AND c.returned = 0
            AND (NOW() < IFNULL(ce.ext_new_date, c.cont_return))
        LIMIT 1;
    ");
    $changed_query->bind_param("i", $veh_id);
    $changed_query->execute();
    $changed_result = $changed_query->get_result();

    if ($changed_result && $contract = $changed_result->fetch_assoc()) {
        $return_date = $contract['effective_return_date'];
        echo "<p><strong>Status:</strong> Out on Rental (Replaced)</p>";
        echo "<p><strong>Return Date:</strong> " . date('d/m/Y H:i', $return_date) . "</p>";
    } else {
        echo "<p><strong>Status:</strong> Available</p>";
    }
}

?>
