ALTER TABLE ropidgtfs_precomputed_departures ADD COLUMN trip_connections JSONB NULL;

-- Create parent table for precomputed trip connection at one stop with indexes

CREATE TABLE ropidgtfs_precomputed_trip_connections (
    to_stop_id varchar(255) NOT NULL,
    to_trip_id varchar(255) NOT NULL,
    connections jsonb NOT NULL,
    CONSTRAINT ropidgtfs_precomputed_trip_connections_pk PRIMARY KEY (to_stop_id, to_trip_id)
);

CREATE INDEX ropidgtfs_precomputed_trip_connections_to_stop_id_idx ON ropidgtfs_precomputed_trip_connections USING btree (to_stop_id);
CREATE INDEX ropidgtfs_precomputed_trip_connections_to_trip_id_idx ON ropidgtfs_precomputed_trip_connections USING btree (to_trip_id);

-- Create child (actual) table for precomputed stop/trip connections

CREATE TABLE ropidgtfs_precomputed_trip_connections_actual (
    LIKE ropidgtfs_precomputed_trip_connections INCLUDING ALL
) INHERITS (ropidgtfs_precomputed_trip_connections);

-- Update procedures for working with tmp tables

CREATE OR REPLACE PROCEDURE ropidgtfs_preparePrecomputedTmpTables()
 LANGUAGE plpgsql
 SET search_path FROM CURRENT
AS $procedure$
    begin
        create table "ropidgtfs_precomputed_services_calendar_tmp" (like "ropidgtfs_precomputed_services_calendar" including all);
        create table "ropidgtfs_precomputed_minmax_stop_sequences_tmp" (like "ropidgtfs_precomputed_minmax_stop_sequences" including all);
        create table "ropidgtfs_precomputed_trip_schedule_tmp" (like "ropidgtfs_precomputed_trip_schedule" including all);
        create table "ropidgtfs_precomputed_trip_connections_tmp" (like "ropidgtfs_precomputed_trip_connections" including all);
    end
$procedure$;

-- Update procedures for working with old tables

CREATE OR REPLACE PROCEDURE ropidgtfs_cleanoldtables()
 LANGUAGE plpgsql
 SET search_path from CURRENT
AS $procedure$
    begin
        drop table if exists "ropidgtfs_agency_old";
        drop table if exists "ropidgtfs_calendar_old";
        drop table if exists "ropidgtfs_calendar_dates_old";
        drop table if exists "ropidgtfs_shapes_old";
        drop table if exists "ropidgtfs_stop_times_old";
        drop table if exists "ropidgtfs_stops_old";
        drop table if exists "ropidgtfs_routes_old";
        drop table if exists "ropidgtfs_route_sub_agencies_old";
        drop table if exists "ropidgtfs_transfers_old";
        drop table if exists "ropidgtfs_trips_old";
        drop table if exists "ropidgtfs_cis_stops_old";
        drop table if exists "ropidgtfs_cis_stop_groups_old";
        drop table if exists "ropidgtfs_run_numbers_old";
        drop table if exists "ropidgtfs_ois_old";
        drop table if exists "ropidgtfs_precomputed_services_calendar_old";
        drop table if exists "ropidgtfs_precomputed_minmax_stop_sequences_old";
        drop table if exists "ropidgtfs_precomputed_trip_connections_old";
        drop table if exists "ropidgtfs_precomputed_trip_schedule_old";
    end
$procedure$
;

-- Update procedures for working with tmp tables

CREATE OR REPLACE PROCEDURE ropidgtfs_cleantmptables()
 LANGUAGE plpgsql
 SET search_path from CURRENT
AS $procedure$
    begin
        drop table if exists "ropidgtfs_agency_tmp";
        drop table if exists "ropidgtfs_calendar_tmp";
        drop table if exists "ropidgtfs_calendar_dates_tmp";
        drop table if exists "ropidgtfs_shapes_tmp";
        drop table if exists "ropidgtfs_stop_times_tmp";
        drop table if exists "ropidgtfs_stops_tmp";
        drop table if exists "ropidgtfs_routes_tmp";
        drop table if exists "ropidgtfs_route_sub_agencies_tmp";
        drop table if exists "ropidgtfs_transfers_tmp";
        drop table if exists "ropidgtfs_trips_tmp";
        drop table if exists "ropidgtfs_cis_stops_tmp";
        drop table if exists "ropidgtfs_cis_stop_groups_tmp";
        drop table if exists "ropidgtfs_run_numbers_tmp";
        drop table if exists "ropidgtfs_ois_tmp";
        drop table if exists "ropidgtfs_precomputed_services_calendar_tmp";
        drop table if exists "ropidgtfs_precomputed_minmax_stop_sequences_tmp";
        drop table if exists "ropidgtfs_precomputed_trip_connections_tmp";
        drop table if exists "ropidgtfs_precomputed_trip_schedule_tmp";
    end
$procedure$
;
