QueryOptimization: indexes.sql

-- VW_ACTIVE_TRIPS
CREATE INDEX idx_trip_status
    ON TRIP (STATUS);

-- VW_ROUTE_TIMETABLE
CREATE INDEX idx_stop_time_trip_seq
    ON STOP_TIME (TRIP_ID, STOP_SEQUENCE);

-- VW_VEHICLE_CURRENT_LOCATION
CREATE INDEX idx_vehicle_log_vehicle_recorded
    ON VEHICLE_LOG (VEHICLE_ID, RECORDED_AT DESC);

-- VW_TICKET_DETAILS
CREATE INDEX idx_ticket_passenger_id
    ON TICKET (PASSENGER_ID);

CREATE INDEX idx_stops_stop_vehicle_type
    ON STOPS (STOP_ID, VEHICLE_TYPE_ID);

-- VW_STAFF_SCHEDULE
CREATE INDEX idx_driver_schedule_composite
    ON DRIVER_SCHEDULE (DRIVER_ID, TRIP_ID, SHIFT_ID, SCHEDULE_DATE);

CREATE INDEX idx_conductor_schedule_composite
    ON CONDUCTOR_SCHEDULE (CONDUCTOR_ID, TRIP_ID, SHIFT_ID, SCHEDULE_DATE);

-- VW_PASSENGER_NOTIFICATIONS
CREATE INDEX idx_notification_passenger_status
    ON NOTIFICATION (PASSENGER_ID, STATUS);