-- vehicle type trolleybus
insert into vehiclepositions_vehicle_types (id, abbreviation, description_cs, description_en) VALUES
    (18, 'Trolejbus', 'trolejbus', 'trolleybus'); -- see MPVRouteTypesEnum.TROLLEYBUS


-- shape id
alter table ropidgtfs_precomputed_trip_schedule add column shape_id varchar(15);
alter table vehiclepositions_trips add column gtfs_shape_id varchar(15);
alter table vehiclepositions_trips_history add column gtfs_shape_id varchar(15);


-- history view
create view v_public_vehiclepositions_past_stop_times as
select
    trips_id as rt_trip_id,
    last_stop_sequence as stop_sequence,
    min(delay_stop_arrival) as stop_arr_delay,
    max(delay_stop_departure) as stop_dep_delay
from (
    select
    	trips_id,
        last_stop_sequence,
        delay_stop_arrival,
        delay_stop_departure,
        row_number() over seq as rn
    from
        vehiclepositions_positions rt_position
    where
        (delay_stop_arrival is not null or delay_stop_departure is not null)
        and state_position in ('on_track', 'at_stop', 'after_track')
    window
        seq as (partition by trips_id, last_stop_sequence, state_position order by id)
) as sub
where
    rn = 1
group by
    trips_id,
    last_stop_sequence
order by
    trips_id,
    last_stop_sequence;


-- prediction view
create view v_public_vehiclepositions_future_stop_times as
with recursive stop_times as (
    select
    	rst.trip_id,
    	vt.id as trips_id,
        vt.provider_source_type,
    	vp.delay,
    	coalesce(vp.last_stop_sequence, 1) as initial_stop_sequence,
        vp.state_position,
    	rst.stop_sequence,
    	rst.computed_dwell_time_seconds,
    	case when vp.last_stop_sequence is null
            then vp.delay
            else vp.delay_stop_arrival
        end as arrival_delay_seconds,
    	case when vp.last_stop_sequence is null
            then predict_delay_seconds(vp.delay, rst.computed_dwell_time_seconds, vt.provider_source_type)
            when vp.state_position = 'at_stop'
                then case when vt.provider_source_type = '1' then greatest(0, vp.delay) else vp.delay end
            else vp.delay_stop_departure
        end as departure_delay_seconds
    from
        ropidgtfs_stop_times rst
    join
        vehiclepositions_trips vt
            on vt.gtfs_trip_id = rst.trip_id
    join
        vehiclepositions_positions vp
            on vp.id = vt.last_position_id and (vp.valid_to is null or vp.valid_to >= now()) and vp.state_position in ('on_track', 'at_stop')
    where
        vt.gtfs_trip_id is not null
        and rst.stop_sequence = coalesce(vp.last_stop_sequence, 1)
union all
    select
    	rst.trip_id,
    	previous_row.trips_id as trips_id,
        previous_row.provider_source_type,
    	previous_row.delay,
    	previous_row.initial_stop_sequence,
        previous_row.state_position,
    	rst.stop_sequence,
    	rst.computed_dwell_time_seconds,
    	case when rst.stop_sequence - previous_row.initial_stop_sequence = 1 and previous_row.state_position != 'at_stop'
            then previous_row.delay
            else previous_row.departure_delay_seconds
        end as arrival_delay_seconds,
        predict_delay_seconds(
            case when rst.stop_sequence - previous_row.initial_stop_sequence = 1 and previous_row.state_position != 'at_stop' then previous_row.delay else previous_row.departure_delay_seconds end,
            rst.computed_dwell_time_seconds,
            previous_row.provider_source_type
        ) as departure_delay_seconds
    from
        stop_times previous_row
    join
        ropidgtfs_stop_times rst
            on rst.trip_id = previous_row.trip_id and rst.stop_sequence = previous_row.stop_sequence + 1
)
select
    trips_id as rt_trip_id,
    stop_sequence,
    coalesce(arrival_delay_seconds, 0) as stop_arr_delay,
    coalesce(departure_delay_seconds, 0) as stop_dep_delay
from
    stop_times
order by
    trip_id,
    stop_sequence;


-- combined view
create view v_public_vehiclepositions_combined_stop_times as
select distinct on (rt_trip_id, stop_sequence)
    stop_times.rt_trip_id,
	vt.gtfs_trip_id,
	stop_times.stop_sequence,
	stop_times.stop_arr_delay,
	stop_times.stop_dep_delay
from (
	select
		*
	from
		v_public_vehiclepositions_future_stop_times prediction
	union all
	select
		*
	from
		v_public_vehiclepositions_past_stop_times history
) as stop_times
join vehiclepositions_trips vt on vt.id = stop_times.rt_trip_id
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_position != 'after_track'
order by
	rt_trip_id,
    stop_sequence;
