Add job that calculates the delays for timeseries

Summary by Sourcery

Add a new feature to record stop time delays using a new CLI command and service. Refactor database operations to be asynchronous and consolidate schedule retrieval methods. Update the Timescale database schema with new fields and sequences. Enhance the RealTimeService with filtering capabilities. Update CI workflow and documentation to reflect these changes.

New Features:

Introduce a new CLI command 'recorddelays' to record stop time delays for every schedule in the database.
Add a new service, DelaysService, to handle the recording of delays for schedules.
Implement a new repository and model for handling time series stop times in the Timescale database.

Enhancements:

Refactor the ScheduleRepository to consolidate schedule retrieval methods into a single method with optional parameters.
Convert several synchronous database operations to asynchronous operations to improve performance and scalability.
Enhance the RealTimeService with new methods to filter schedules based on due status and the presence of updates.

Build:

Add new Alembic migrations to modify the Timescale database schema, including adding fields for stop times and creating a sequence for IDs.

CI:

Update the CI workflow to run SQL scripts from a new directory structure.

Documentation:

Update the README to include the new 'recorddelays' command.

Reviewer's Guide by Sourcery

This pull request introduces a new feature to calculate and record delays for timeseries data. It involves significant changes to the project structure, including modifications to existing files and the addition of new files. The changes primarily focus on implementing asynchronous operations, improving database interactions, and adding new functionality for delay calculations.

Sequence Diagram

sequenceDiagram
    participant CLI
    participant DelaysService
    participant ScheduleService
    participant RealTimeService
    participant TSStopTimeRepository
    CLI->>DelaysService: record_all_delays()
    DelaysService->>ScheduleService: get_all_schedule_for_day_between_times()
    ScheduleService-->>DelaysService: schedules
    DelaysService->>RealTimeService: get_realtime_schedules_for_static_schedules()
    RealTimeService-->>DelaysService: realtime_schedules
    DelaysService->>DelaysService: filter_to_only_due_schedules()
    DelaysService->>DelaysService: filter_to_only_schedules_with_updates()
    DelaysService->>TSStopTimeRepository: add_many(ts_stop_times)
    TSStopTimeRepository-->>DelaysService: confirmation
    DelaysService-->>CLI: number of delays recorded

File-Level Changes

Change	Details	Files
Implement asynchronous operations for database interactions	Convert synchronous database operations to asynchronous Replace 'session' with 'async_session_factory' Update function signatures to use 'async' and 'await' Modify database queries to use SQLAlchemy's asynchronous API	`SimplyTransport/lib/stop_features_importer.py` `SimplyTransport/domain/schedule/repo.py` `SimplyTransport/lib/gtfs_realtime_importers.py`
Add new functionality for calculating and recording delays	Create new 'DelaysService' class for handling delay calculations Implement 'record_all_delays' method to process and store delay information Add new CLI command 'recorddelays' to trigger delay recording Create new Timescale database model 'TS_StopTimeModel' for storing delay data	`SimplyTransport/timescale/services/delays_service.py` `SimplyTransport/timescale/ts_stop_times/model.py` `SimplyTransport/cli.py` `SimplyTransport/domain/events/event_types.py`
Refactor and optimize existing code	Update 'get_static_schedules' method to handle multiple scenarios Modify 'RealTimeScheduleModel' to include 'is_due' property Implement filtering methods for realtime schedules Update dependencies and database session providers	`SimplyTransport/domain/services/schedule_service.py` `SimplyTransport/domain/realtime/realtime_schedule/model.py` `SimplyTransport/domain/services/realtime_service.py` `SimplyTransport/app.py`
Add database migrations for Timescale	Create migration for adding fields to ts_stop_times table Add migration for creating id sequence for ts_stop_times Implement migration for removing actual_time column	`migrations/timescale/versions/2024_09_25-56f6a9c93651_adding_fields_for_ts_stop_times.py` `migrations/timescale/versions/2024_09_25-9fdbdd5e8677_id_sequence.py` `migrations/timescale/versions/2024_09_25-ffcf1e5100cf_removing_arrival_time.py`

Tips and commands

- Trigger a new Sourcery review by commenting `@sourcery-ai review` on the pull request. - Continue your discussion with Sourcery by replying directly to review comments. - You can change your review settings at any time by accessing your [dashboard](https://app.sourcery.ai): - Enable or disable the Sourcery-generated pull request summary or reviewer's guide; - Change the review language; - You can always [contact us](mailto:support@sourcery.ai) if you have any questions or feedback.

ANIALLATOR114 / SimplyTransport