create table if not exists table_periods (
    id                  serial          primary key not null,
    name                text            not null,
    provider            text            not null,
    date_start          timestamptz     not null,
    date_end            timestamptz     null
);

create table if not exists agencies (
    table_period_id     int             not null,
    agency_id           text            not null,
    agency_name         text            not null,
    agency_url          text            null,
    agency_timezone     text            not null,
    foreign key (table_period_id)       references table_periods(id) on delete cascade
);

alter table if exists calendar rename to calendars;

create table if not exists calendars (
    table_period_id     int             not null,
    service_id          text            not null,
    monday              smallint        not null,
    tuesday             smallint        not null,
    wednesday           smallint        not null,
    thursday            smallint        not null,
    friday              smallint        not null,
    saturday            smallint        not null,
    sunday              smallint        not null,
    start_date          date            not null,
    end_date            date            not null,
    foreign key (table_period_id)       references table_periods(id) on delete cascade
);

create table if not exists calendar_dates (
    table_period_id     int             not null,
    service_id          text            not null,
    date                date            not null,
    exception_type      smallint        not null,       -- 1 = added, 2 = removed
    foreign key (table_period_id)       references table_periods(id) on delete cascade
);

create table if not exists routes (
    table_period_id         int             not null,
    route_id                text            not null,
    route_short_name        text            not null,
    route_long_name         text            not null,
    route_type              smallint        not null,       -- 3 = bus
    route_alias             text            null,
    direction_id            smallint        null,
    route_direction_name    text            not null,
    foreign key (table_period_id)           references table_periods(id) on delete cascade
);
create index if not exists rt_route_id_idx on routes(table_period_id, route_id);
create index if not exists rt_route_short_name_idx on routes(table_period_id, route_short_name);

create table if not exists shapes (
    table_period_id     int             not null,
    shape_id            text            not null,       -- same as route_id
    shape_pt_lat        decimal(11, 8)  not null,
    shape_pt_lon        decimal(11, 8)  not null,
    shape_pt_x          decimal(11, 2)  not null,
    shape_pt_y          decimal(11, 2)  not null,
    shape_pt_sequence   int             not null,
    shape_dist_traveled int             not null,
    foreign key (table_period_id)       references table_periods(id) on delete cascade
);
create index if not exists shp_shape_id_idx on shapes(table_period_id, shape_id);

create table if not exists stop_links (
    table_period_id     int             not null,
    stop_link_id        text            not null,
    routes              text            null,
    shape_dist_traveled int             not null,
    foreign key (table_period_id)       references table_periods(id) on delete cascade
);
create index if not exists slr_stop_link_id_idx on stop_links(table_period_id, stop_link_id);

create table if not exists stop_link_shapes (
    table_period_id     int             not null,
    shape_id            text            not null,       -- same as stop_link_id
    shape_pt_lat        decimal(11, 8)  not null,
    shape_pt_lon        decimal(11, 8)  not null,
    shape_pt_x          decimal(11, 2)  not null,
    shape_pt_y          decimal(11, 2)  not null,
    shape_pt_sequence   int             not null,
    shape_dist_traveled int             not null,
    foreign key (table_period_id)       references table_periods(id) on delete cascade
);
create index if not exists sls_shape_id_idx on stop_link_shapes(table_period_id, shape_id);

create table if not exists stops (
    table_period_id     int             not null,
    stop_id             text            not null,
    stop_code           text            null,
    stop_name           text            not null,
    stop_lat            decimal(11, 8)  not null,
    stop_lon            decimal(11, 8)  not null,
    stop_x              decimal(11, 2)  not null,
    stop_y              decimal(11, 2)  not null,
    zone_id             text            null,
    county_code         smallint        null,
    municipality_code   smallint        null,
    location_type       smallint        not null,       -- 0 = stop, 1 = station
    parent_station      text            null,
    stop_node           int             null,
    foreign key (table_period_id)       references table_periods(id) on delete cascade
);
create index if not exists stp_idx on stops(stop_id);

create table if not exists stop_times (
    table_period_id     int             not null,
    trip_id             text            not null,
    arrival_time        text            not null,
    departure_time      text            not null,
    stop_id             text            not null,
    stop_sequence       smallint        not null,
    stop_headsign       text            null,
    shape_dist_traveled int             not null,
    foreign key (table_period_id)       references table_periods(id) on delete cascade
);
create index if not exists stt_trip_stop_idx on stop_times(table_period_id, trip_id, stop_id);

create table if not exists trips (
    table_period_id     int             not null,
    route_id            text            not null,
    service_id          text            not null,
    trip_id             text            not null,
    trip_headsign       text            null,
    trip_short_name     text            null,
    direction_id        smallint        not null,       -- 0 / 1
    block_id            text            not null,
    shape_id            text            not null,
    foreign key (table_period_id)       references table_periods(id) on delete cascade
);

create table if not exists squares_by_transit_route (
    table_period_id     int             not null,
    route_id            text            not null,
    square_id           bigint          not null,
    distance            int             not null,
    foreign key (table_period_id)       references table_periods(id) on delete cascade
);
create index if not exists str_route_distance_idx on squares_by_transit_route(table_period_id, route_id, distance);


create table if not exists route_direction_stops (
    table_period_id     int             not null,
    route_short_name    text            not null,
    direction_id        smallint        not null,
    stop_id             text            not null,
    stop_sequence       smallint        not null,
    foreign key (table_period_id)       references table_periods(id) on delete cascade
);
create index if not exists rds_route_short_name_idx on route_direction_stops(table_period_id, route_short_name);

