DROP VIEW v_public_vehiclepositions_combined_stop_times;
DROP VIEW v_public_vehiclepositions_future_stop_times;

ALTER TABLE ropidgtfs_stop_times DROP COLUMN computed_dwell_time_seconds;
ALTER TABLE ropidgtfs_stop_times ADD COLUMN computed_dwell_time_seconds int2 NOT NULL GENERATED ALWAYS AS (
    calculate_dwell_time_seconds(arrival_time, departure_time)
) STORED;

CREATE OR REPLACE 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,
        coalesce(vp.delay, 0) AS delay,
        coalesce(vp.last_stop_sequence, 1) AS initial_stop_sequence,
        vp.state_position,
        rst.stop_id,
        rst.stop_sequence,
        rst.computed_dwell_time_seconds,
        rst.arrival_time,
        rst.departure_time,
        CASE WHEN vp.last_stop_sequence IS NULL THEN
            coalesce(vp.delay, 0)
        ELSE
            coalesce(vp.delay_stop_arrival, 0)
        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::text = 'at_stop'::text THEN
            CASE WHEN vt.provider_source_type::text = '1'::text 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::text = rst.trip_id::text
        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::text = ANY (ARRAY['on_track'::character varying::text,
                    'at_stop'::character varying::text,
                    'before_track'::character varying::text,
                    'before_track_delayed'::character varying::text]))
    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,
        previous_row.provider_source_type,
        previous_row.delay,
        previous_row.initial_stop_sequence,
        previous_row.state_position,
        rst.stop_id,
        rst.stop_sequence,
        rst.computed_dwell_time_seconds,
        rst.arrival_time,
        rst.departure_time,
        CASE WHEN (rst.stop_sequence - previous_row.initial_stop_sequence) = 1
            AND previous_row.state_position::text <> 'at_stop'::text THEN
            CASE WHEN (rst.arrival_time::interval +(interval '1 second' * previous_row.delay) < previous_row.departure_time::interval +(interval '1 second' *(previous_row.departure_delay_seconds))) THEN
                NULL
            ELSE
                previous_row.delay
            END
        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::text <> 'at_stop'::text 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::text = previous_row.trip_id::text
            AND rst.stop_sequence =(previous_row.stop_sequence + 1))
    SELECT
        stop_times.trips_id AS rt_trip_id,
        stop_times.stop_sequence,
        stop_times.arrival_delay_seconds AS stop_arr_delay,
        CASE WHEN (stop_times.arrival_time::interval +(interval '1 second' * coalesce(stop_times.arrival_delay_seconds, 0))) >(stop_times.departure_time::interval +(interval '1 second' * coalesce(stop_times.departure_delay_seconds, 0))) THEN
            NULL
        ELSE
            stop_times.departure_delay_seconds
        END AS stop_dep_delay,
        stop_times.stop_id
    FROM
        stop_times
    ORDER BY
        stop_times.trip_id,
        stop_times.stop_sequence;

create
or replace 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,
    vt.gtfs_route_type,
    vt.gtfs_route_short_name,
    vt.run_number,
    vt.internal_run_number,
    vt.provider_source_type,
    vt.cis_trip_number,
    vt.vehicle_registration_number,
    stop_times.stop_id,
    vcs.cis_stop_platform_code,
    rs.platform_code
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'
    left join ropidgtfs_stops rs on rs.stop_id = stop_times.stop_id
    left join ropidgtfs_cis_stops rcs on rcs.id = rs.computed_cis_stop_id
    left join vehiclepositions_cis_stops vcs
        on vcs.rt_trip_id = stop_times.rt_trip_id
        and vcs.cis_stop_group_id = rcs.cis
order by
    rt_trip_id,
    stop_sequence;
