CREATE OR REPLACE VIEW vehicles_with_latest_gps AS
SELECT 
    v.id AS vehicle_id,
    v.vehicle_register_number,
    v.vehicle_plate_number,
    vehicle_gps_data.latitude,
    vehicle_gps_data.longitude,
    vehicle_gps_data.gps_timestamp,
    vehicle_gps_data.driver_id,
    vehicle_gps_data.created_at AS gps_record_created_at,
    vehicle_gps_data.status_id AS gps_status_id,
	v.company_id
FROM 
    vehicles v
    INNER JOIN (
        SELECT DISTINCT ON (vehicle_id)
            vehicle_id,
            latitude,
            longitude,
            gps_timestamp,
            driver_id,
            created_at,
            status_id
        FROM 
            vehicle_gps_data
        WHERE 
            status_id != 3  -- Exclude inactive/deleted GPS records
        ORDER BY 
            vehicle_id, 
            gps_timestamp DESC
    ) vehicle_gps_data ON v.id = vehicle_gps_data.vehicle_id;