drop view v_vehiclepositions_all_trips_with_last_position;
create view v_vehiclepositions_all_trips_with_last_position as
select
    vt.id,
	vt.agency_name_real,
	vt.agency_name_scheduled,
	vt.cis_line_id,
	vt.cis_trip_number,
	vt.gtfs_route_id,
	vt.gtfs_route_short_name,
	vt.gtfs_route_type,
	vt.gtfs_trip_headsign,
	vt.gtfs_trip_short_name,
	vt.gtfs_trip_id,
	vt.origin_route_name,
	vt.run_number,
	vt.vehicle_registration_number,
    vt.vehicle_type_id,
    vt.wheelchair_accessible,
    vt.updated_at,
	convert_timestamptz_to_iso_string(vt.start_timestamp) as start_timestamp_isostring,
	vp.bearing,
	vp.delay,
	vp.delay_stop_arrival,
	vp.delay_stop_departure,
	vp.is_canceled,
	vp.last_stop_id,
	vp.last_stop_sequence,
    vp.last_stop_headsign,
    vp.lat,
    vp.lng,
	vp.next_stop_id,
	vp.next_stop_sequence,
	vp.shape_dist_traveled,
	vp.speed,
	vp.state_position,
	vp.is_tracked,
	convert_timestamptz_to_iso_string(vp.last_stop_arrival_time) as last_stop_arrival_time_isostring,
	convert_timestamptz_to_iso_string(vp.last_stop_departure_time) as last_stop_departure_time_isostring,
	convert_timestamptz_to_iso_string(vp.next_stop_arrival_time) as next_stop_arrival_time_isostring,
	convert_timestamptz_to_iso_string(vp.next_stop_departure_time) as next_stop_departure_time_isostring,
	convert_timestamptz_to_iso_string(vp.origin_timestamp) as origin_timestamp_isostring
from
	vehiclepositions_trips vt
inner join vehiclepositions_positions vp on
	vt.last_position_id = vp.id
	and (vp.valid_to is null
		or vp.valid_to >= now())
	and vp.state_process = 'processed'
    and (vp.lat != 0 and vp.lat is not null)
    and (vp.lng != 0 and vp.lng is not null);

comment on view v_vehiclepositions_all_trips_with_last_position is '
View of all trips with last processed position. Used for serving data to the vehiclepositions output API.
';
