
--Migration_RCCP_20230727_165705.txt
CREATE TABLE [dbo].[GtfsAgencies] (
    [id] [uniqueidentifier] NOT NULL DEFAULT newsequentialid(),
    [agency_id] [nvarchar](max),
    [agency_name] [nvarchar](max) NOT NULL,
    [agency_url] [nvarchar](max) NOT NULL,
    [agency_timezone] [nvarchar](max) NOT NULL,
    [agency_lang] [nvarchar](max),
    [agency_phone] [nvarchar](max),
    [agency_fare_url] [nvarchar](max),
    [company_id] [bigint] NOT NULL,
    CONSTRAINT [PK_dbo.GtfsAgencies] PRIMARY KEY ([id])
)
CREATE TABLE [dbo].[GtfsCalendars] (
    [id] [uniqueidentifier] NOT NULL DEFAULT newsequentialid(),
    [service_id] [nvarchar](max) NOT NULL,
    [monday] [int] NOT NULL,
    [tuesday] [int] NOT NULL,
    [wednesday] [int] NOT NULL,
    [thursday] [int] NOT NULL,
    [friday] [int] NOT NULL,
    [saturday] [int] NOT NULL,
    [sunday] [int] NOT NULL,
    [start_date] [datetime] NOT NULL,
    [end_date] [datetime] NOT NULL,
    [company_id] [bigint] NOT NULL,
    CONSTRAINT [PK_dbo.GtfsCalendars] PRIMARY KEY ([id])
)
CREATE TABLE [dbo].[GtfsCalendarDates] (
    [id] [uniqueidentifier] NOT NULL DEFAULT newsequentialid(),
    [service_id] [nvarchar](max) NOT NULL,
    [date] [datetime] NOT NULL,
    [exception_type] [int] NOT NULL,
    [company_id] [bigint] NOT NULL,
    CONSTRAINT [PK_dbo.GtfsCalendarDates] PRIMARY KEY ([id])
)
CREATE TABLE [dbo].[GtfsRoutes] (
    [id] [uniqueidentifier] NOT NULL DEFAULT newsequentialid(),
    [route_id] [nvarchar](max) NOT NULL,
    [agency_id] [nvarchar](max),
    [route_short_name] [nvarchar](max),
    [route_long_name] [nvarchar](max),
    [route_desc] [nvarchar](max),
    [route_type] [int] NOT NULL,
    [route_url] [nvarchar](max),
    [route_color] [nvarchar](max),
    [route_text_color] [nvarchar](max),
    [company_id] [bigint] NOT NULL,
    CONSTRAINT [PK_dbo.GtfsRoutes] PRIMARY KEY ([id])
)
CREATE TABLE [dbo].[GtfsShapes] (
    [id] [uniqueidentifier] NOT NULL DEFAULT newsequentialid(),
    [shape_id] [nvarchar](max) NOT NULL,
    [shape_pt_lat] [float] NOT NULL,
    [shape_pt_lon] [float] NOT NULL,
    [shape_pt_sequence] [int] NOT NULL,
    [shape_dist_traveled] [real] NOT NULL,
    [company_id] [bigint] NOT NULL,
    CONSTRAINT [PK_dbo.GtfsShapes] PRIMARY KEY ([id])
)
CREATE TABLE [dbo].[GtfsStops] (
    [id] [uniqueidentifier] NOT NULL DEFAULT newsequentialid(),
    [stop_id] [nvarchar](max) NOT NULL,
    [stop_code] [nvarchar](max),
    [stop_name] [nvarchar](max),
    [stop_desc] [nvarchar](max),
    [stop_lat] [float] NOT NULL,
    [stop_lon] [float] NOT NULL,
    [zone_id] [nvarchar](max),
    [stop_url] [nvarchar](max),
    [location_type] [int] NOT NULL,
    [parent_station] [nvarchar](max),
    [company_id] [bigint] NOT NULL,
    CONSTRAINT [PK_dbo.GtfsStops] PRIMARY KEY ([id])
)
CREATE TABLE [dbo].[GtfsStopTimes] (
    [id] [uniqueidentifier] NOT NULL DEFAULT newsequentialid(),
    [trip_id] [nvarchar](max),
    [arrival_time] [nvarchar](max),
    [departure_time] [nvarchar](max),
    [stop_id] [nvarchar](max),
    [stop_sequence] [int] NOT NULL,
    [stop_headsign] [nvarchar](max),
    [pickup_type] [int] NOT NULL,
    [drop_off_type] [int] NOT NULL,
    [shape_dist_traveled] [real] NOT NULL,
    [company_id] [bigint] NOT NULL,
    CONSTRAINT [PK_dbo.GtfsStopTimes] PRIMARY KEY ([id])
)
CREATE TABLE [dbo].[GtfsTrips] (
    [id] [uniqueidentifier] NOT NULL DEFAULT newsequentialid(),
    [route_id] [nvarchar](max),
    [service_id] [nvarchar](max),
    [trip_id] [nvarchar](max) NOT NULL,
    [trip_headsign] [nvarchar](max),
    [trip_short_name] [nvarchar](max),
    [direction_id] [int] NOT NULL,
    [block_id] [nvarchar](max),
    [shape_id] [nvarchar](max),
    [wheelchair_accessible] [int] NOT NULL,
    [company_id] [bigint] NOT NULL,
    CONSTRAINT [PK_dbo.GtfsTrips] PRIMARY KEY ([id])
)
CREATE INDEX [IX_company_id] ON [dbo].[GtfsAgencies]([company_id])
CREATE INDEX [IX_company_id] ON [dbo].[GtfsCalendars]([company_id])
CREATE INDEX [IX_company_id] ON [dbo].[GtfsCalendarDates]([company_id])
CREATE INDEX [IX_company_id] ON [dbo].[GtfsRoutes]([company_id])
CREATE INDEX [IX_company_id] ON [dbo].[GtfsShapes]([company_id])
CREATE INDEX [IX_company_id] ON [dbo].[GtfsStops]([company_id])
CREATE INDEX [IX_company_id] ON [dbo].[GtfsStopTimes]([company_id])
CREATE INDEX [IX_company_id] ON [dbo].[GtfsTrips]([company_id])
ALTER TABLE [dbo].[GtfsAgencies] ADD CONSTRAINT [FK_dbo.GtfsAgencies_dbo.Companies_company_id] FOREIGN KEY ([company_id]) REFERENCES [dbo].[Companies] ([ID]) ON DELETE CASCADE
ALTER TABLE [dbo].[GtfsCalendars] ADD CONSTRAINT [FK_dbo.GtfsCalendars_dbo.Companies_company_id] FOREIGN KEY ([company_id]) REFERENCES [dbo].[Companies] ([ID]) ON DELETE CASCADE
ALTER TABLE [dbo].[GtfsCalendarDates] ADD CONSTRAINT [FK_dbo.GtfsCalendarDates_dbo.Companies_company_id] FOREIGN KEY ([company_id]) REFERENCES [dbo].[Companies] ([ID]) ON DELETE CASCADE
ALTER TABLE [dbo].[GtfsRoutes] ADD CONSTRAINT [FK_dbo.GtfsRoutes_dbo.Companies_company_id] FOREIGN KEY ([company_id]) REFERENCES [dbo].[Companies] ([ID]) ON DELETE CASCADE
ALTER TABLE [dbo].[GtfsShapes] ADD CONSTRAINT [FK_dbo.GtfsShapes_dbo.Companies_company_id] FOREIGN KEY ([company_id]) REFERENCES [dbo].[Companies] ([ID]) ON DELETE CASCADE
ALTER TABLE [dbo].[GtfsStops] ADD CONSTRAINT [FK_dbo.GtfsStops_dbo.Companies_company_id] FOREIGN KEY ([company_id]) REFERENCES [dbo].[Companies] ([ID]) ON DELETE CASCADE
ALTER TABLE [dbo].[GtfsStopTimes] ADD CONSTRAINT [FK_dbo.GtfsStopTimes_dbo.Companies_company_id] FOREIGN KEY ([company_id]) REFERENCES [dbo].[Companies] ([ID]) ON DELETE CASCADE
ALTER TABLE [dbo].[GtfsTrips] ADD CONSTRAINT [FK_dbo.GtfsTrips_dbo.Companies_company_id] FOREIGN KEY ([company_id]) REFERENCES [dbo].[Companies] ([ID]) ON DELETE CASCADE
