"""Create the database from scratch. Revision: #f11cd76d2f45 at 2020-08-06 23:24:32 """ import os import sqlalchemy as sa from alembic import op from sqlalchemy.dialects import postgresql from urban_meal_delivery import configuration revision = 'f11cd76d2f45' down_revision = None branch_labels = None depends_on = None config = configuration.make_config('testing' if os.getenv('TESTING') else 'production') def upgrade(): """Upgrade to revision f11cd76d2f45.""" op.execute(f'CREATE SCHEMA {config.CLEAN_SCHEMA};') op.create_table( # noqa:ECE001 'cities', sa.Column('id', sa.SmallInteger(), autoincrement=False, nullable=False), sa.Column('name', sa.Unicode(length=10), nullable=False), sa.Column('kml', sa.UnicodeText(), nullable=False), sa.Column('center_latitude', postgresql.DOUBLE_PRECISION(), nullable=False), sa.Column('center_longitude', postgresql.DOUBLE_PRECISION(), nullable=False), sa.Column('northeast_latitude', postgresql.DOUBLE_PRECISION(), nullable=False), sa.Column('northeast_longitude', postgresql.DOUBLE_PRECISION(), nullable=False), sa.Column('southwest_latitude', postgresql.DOUBLE_PRECISION(), nullable=False), sa.Column('southwest_longitude', postgresql.DOUBLE_PRECISION(), nullable=False), sa.Column('initial_zoom', sa.SmallInteger(), nullable=False), sa.PrimaryKeyConstraint('id', name=op.f('pk_cities')), *( [ # noqa:WPS504 sa.ForeignKeyConstraint( ['id'], [f'{config.ORIGINAL_SCHEMA}.cities.id'], name=op.f('pk_cities_sanity'), onupdate='RESTRICT', ondelete='RESTRICT', ), ] if not config.TESTING else [] ), schema=config.CLEAN_SCHEMA, ) op.create_table( # noqa:ECE001 'couriers', sa.Column('id', sa.Integer(), autoincrement=False, nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('vehicle', sa.Unicode(length=10), nullable=False), sa.Column('speed', postgresql.DOUBLE_PRECISION(), nullable=False), sa.Column('capacity', sa.SmallInteger(), nullable=False), sa.Column('pay_per_hour', sa.SmallInteger(), nullable=False), sa.Column('pay_per_order', sa.SmallInteger(), nullable=False), sa.CheckConstraint( "vehicle IN ('bicycle', 'motorcycle')", name=op.f('ck_couriers_on_available_vehicle_types'), ), sa.CheckConstraint( '0 <= capacity AND capacity <= 200', name=op.f('ck_couriers_on_capacity_under_200_liters'), ), sa.CheckConstraint( '0 <= pay_per_hour AND pay_per_hour <= 1500', name=op.f('ck_couriers_on_realistic_pay_per_hour'), ), sa.CheckConstraint( '0 <= pay_per_order AND pay_per_order <= 650', name=op.f('ck_couriers_on_realistic_pay_per_order'), ), sa.CheckConstraint( '0 <= speed AND speed <= 30', name=op.f('ck_couriers_on_realistic_speed'), ), sa.PrimaryKeyConstraint('id', name=op.f('pk_couriers')), *( [ # noqa:WPS504 sa.ForeignKeyConstraint( ['id'], [f'{config.ORIGINAL_SCHEMA}.couriers.id'], name=op.f('pk_couriers_sanity'), onupdate='RESTRICT', ondelete='RESTRICT', ), ] if not config.TESTING else [] ), schema=config.CLEAN_SCHEMA, ) op.create_table( 'customers', sa.Column('id', sa.Integer(), autoincrement=False, nullable=False), sa.PrimaryKeyConstraint('id', name=op.f('pk_customers')), schema=config.CLEAN_SCHEMA, ) op.create_table( # noqa:ECE001 'addresses', sa.Column('id', sa.Integer(), autoincrement=False, nullable=False), sa.Column('primary_id', sa.Integer(), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('place_id', sa.Unicode(length=120), nullable=False), sa.Column('latitude', postgresql.DOUBLE_PRECISION(), nullable=False), sa.Column('longitude', postgresql.DOUBLE_PRECISION(), nullable=False), sa.Column('city_id', sa.SmallInteger(), nullable=False), sa.Column('city', sa.Unicode(length=25), nullable=False), sa.Column('zip_code', sa.Integer(), nullable=False), sa.Column('street', sa.Unicode(length=80), nullable=False), sa.Column('floor', sa.SmallInteger(), nullable=True), sa.CheckConstraint( '-180 <= longitude AND longitude <= 180', name=op.f('ck_addresses_on_longitude_between_180_degrees'), ), sa.CheckConstraint( '-90 <= latitude AND latitude <= 90', name=op.f('ck_addresses_on_latitude_between_90_degrees'), ), sa.CheckConstraint( '0 <= floor AND floor <= 40', name=op.f('ck_addresses_on_realistic_floor'), ), sa.CheckConstraint( '30000 <= zip_code AND zip_code <= 99999', name=op.f('ck_addresses_on_valid_zip_code'), ), sa.ForeignKeyConstraint( ['city_id'], [f'{config.CLEAN_SCHEMA}.cities.id'], name=op.f('fk_addresses_to_cities_via_city_id'), onupdate='RESTRICT', ondelete='RESTRICT', ), sa.ForeignKeyConstraint( ['primary_id'], [f'{config.CLEAN_SCHEMA}.addresses.id'], name=op.f('fk_addresses_to_addresses_via_primary_id'), onupdate='RESTRICT', ondelete='RESTRICT', ), sa.PrimaryKeyConstraint('id', name=op.f('pk_addresses')), *( [ # noqa:WPS504 sa.ForeignKeyConstraint( ['id'], [f'{config.ORIGINAL_SCHEMA}.addresses.id'], name=op.f('pk_addresses_sanity'), onupdate='RESTRICT', ondelete='RESTRICT', ), ] if not config.TESTING else [] ), schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_addresses_on_city_id'), 'addresses', ['city_id'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_addresses_on_place_id'), 'addresses', ['place_id'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_addresses_on_primary_id'), 'addresses', ['primary_id'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_addresses_on_zip_code'), 'addresses', ['zip_code'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_table( # noqa:ECE001 'restaurants', sa.Column('id', sa.SmallInteger(), autoincrement=False, nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('name', sa.Unicode(length=45), nullable=False), sa.Column('address_id', sa.Integer(), nullable=False), sa.Column('estimated_prep_duration', sa.SmallInteger(), nullable=False), sa.CheckConstraint( '0 <= estimated_prep_duration AND estimated_prep_duration <= 2400', name=op.f('ck_restaurants_on_realistic_estimated_prep_duration'), ), sa.ForeignKeyConstraint( ['address_id'], [f'{config.CLEAN_SCHEMA}.addresses.id'], name=op.f('fk_restaurants_to_addresses_via_address_id'), onupdate='RESTRICT', ondelete='RESTRICT', ), sa.PrimaryKeyConstraint('id', name=op.f('pk_restaurants')), *( [ # noqa:WPS504 sa.ForeignKeyConstraint( ['id'], [f'{config.ORIGINAL_SCHEMA}.businesses.id'], name=op.f('pk_restaurants_sanity'), onupdate='RESTRICT', ondelete='RESTRICT', ), ] if not config.TESTING else [] ), schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_restaurants_on_address_id'), 'restaurants', ['address_id'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_table( # noqa:ECE001 'orders', sa.Column('id', sa.Integer(), autoincrement=False, nullable=False), sa.Column('delivery_id', sa.Integer(), nullable=True), sa.Column('customer_id', sa.Integer(), nullable=False), sa.Column('placed_at', sa.DateTime(), nullable=False), sa.Column('ad_hoc', sa.Boolean(), nullable=False), sa.Column('scheduled_delivery_at', sa.DateTime(), nullable=True), sa.Column('scheduled_delivery_at_corrected', sa.Boolean(), nullable=True), sa.Column('first_estimated_delivery_at', sa.DateTime(), nullable=True), sa.Column('cancelled', sa.Boolean(), nullable=False), sa.Column('cancelled_at', sa.DateTime(), nullable=True), sa.Column('cancelled_at_corrected', sa.Boolean(), nullable=True), sa.Column('sub_total', sa.Integer(), nullable=False), sa.Column('delivery_fee', sa.SmallInteger(), nullable=False), sa.Column('total', sa.Integer(), nullable=False), sa.Column('restaurant_id', sa.SmallInteger(), nullable=False), sa.Column('restaurant_notified_at', sa.DateTime(), nullable=True), sa.Column('restaurant_notified_at_corrected', sa.Boolean(), nullable=True), sa.Column('restaurant_confirmed_at', sa.DateTime(), nullable=True), sa.Column('restaurant_confirmed_at_corrected', sa.Boolean(), nullable=True), sa.Column('estimated_prep_duration', sa.Integer(), nullable=True), sa.Column('estimated_prep_duration_corrected', sa.Boolean(), nullable=True), sa.Column('estimated_prep_buffer', sa.Integer(), nullable=False), sa.Column('courier_id', sa.Integer(), nullable=True), sa.Column('dispatch_at', sa.DateTime(), nullable=True), sa.Column('dispatch_at_corrected', sa.Boolean(), nullable=True), sa.Column('courier_notified_at', sa.DateTime(), nullable=True), sa.Column('courier_notified_at_corrected', sa.Boolean(), nullable=True), sa.Column('courier_accepted_at', sa.DateTime(), nullable=True), sa.Column('courier_accepted_at_corrected', sa.Boolean(), nullable=True), sa.Column('utilization', sa.SmallInteger(), nullable=False), sa.Column('pickup_address_id', sa.Integer(), nullable=False), sa.Column('reached_pickup_at', sa.DateTime(), nullable=True), sa.Column('pickup_at', sa.DateTime(), nullable=True), sa.Column('pickup_at_corrected', sa.Boolean(), nullable=True), sa.Column('pickup_not_confirmed', sa.Boolean(), nullable=True), sa.Column('left_pickup_at', sa.DateTime(), nullable=True), sa.Column('left_pickup_at_corrected', sa.Boolean(), nullable=True), sa.Column('delivery_address_id', sa.Integer(), nullable=False), sa.Column('reached_delivery_at', sa.DateTime(), nullable=True), sa.Column('delivery_at', sa.DateTime(), nullable=True), sa.Column('delivery_at_corrected', sa.Boolean(), nullable=True), sa.Column('delivery_not_confirmed', sa.Boolean(), nullable=True), sa.Column('courier_waited_at_delivery', sa.Boolean(), nullable=True), sa.Column('logged_delivery_distance', sa.SmallInteger(), nullable=True), sa.Column('logged_avg_speed', postgresql.DOUBLE_PRECISION(), nullable=True), sa.Column('logged_avg_speed_distance', sa.SmallInteger(), nullable=True), sa.CheckConstraint( '0 <= estimated_prep_buffer AND estimated_prep_buffer <= 900', name=op.f('ck_orders_on_estimated_prep_buffer_between_0_and_900'), ), sa.CheckConstraint( '0 <= estimated_prep_duration AND estimated_prep_duration <= 2700', name=op.f('ck_orders_on_estimated_prep_duration_between_0_and_2700'), ), sa.CheckConstraint( '0 <= utilization AND utilization <= 100', name=op.f('ck_orders_on_utilization_between_0_and_100'), ), sa.CheckConstraint( '(cancelled_at IS NULL AND cancelled_at_corrected IS NULL) OR (cancelled_at IS NULL AND cancelled_at_corrected IS TRUE) OR (cancelled_at IS NOT NULL AND cancelled_at_corrected IS NOT NULL)', # noqa:E501 name=op.f('ck_orders_on_corrections_only_for_set_value_1'), ), sa.CheckConstraint( '(courier_accepted_at IS NULL AND courier_accepted_at_corrected IS NULL) OR (courier_accepted_at IS NULL AND courier_accepted_at_corrected IS TRUE) OR (courier_accepted_at IS NOT NULL AND courier_accepted_at_corrected IS NOT NULL)', # noqa:E501 name=op.f('ck_orders_on_corrections_only_for_set_value_7'), ), sa.CheckConstraint( '(courier_notified_at IS NULL AND courier_notified_at_corrected IS NULL) OR (courier_notified_at IS NULL AND courier_notified_at_corrected IS TRUE) OR (courier_notified_at IS NOT NULL AND courier_notified_at_corrected IS NOT NULL)', # noqa:E501 name=op.f('ck_orders_on_corrections_only_for_set_value_6'), ), sa.CheckConstraint( '(delivery_at IS NULL AND delivery_at_corrected IS NULL) OR (delivery_at IS NULL AND delivery_at_corrected IS TRUE) OR (delivery_at IS NOT NULL AND delivery_at_corrected IS NOT NULL)', # noqa:E501 name=op.f('ck_orders_on_corrections_only_for_set_value_10'), ), sa.CheckConstraint( '(dispatch_at IS NULL AND dispatch_at_corrected IS NULL) OR (dispatch_at IS NULL AND dispatch_at_corrected IS TRUE) OR (dispatch_at IS NOT NULL AND dispatch_at_corrected IS NOT NULL)', # noqa:E501 name=op.f('ck_orders_on_corrections_only_for_set_value_5'), ), sa.CheckConstraint( '(estimated_prep_duration IS NULL AND estimated_prep_duration_corrected IS NULL) OR (estimated_prep_duration IS NULL AND estimated_prep_duration_corrected IS TRUE) OR (estimated_prep_duration IS NOT NULL AND estimated_prep_duration_corrected IS NOT NULL)', # noqa:E501 name=op.f('ck_orders_on_corrections_only_for_set_value_4'), ), sa.CheckConstraint( '(left_pickup_at IS NULL AND left_pickup_at_corrected IS NULL) OR (left_pickup_at IS NULL AND left_pickup_at_corrected IS TRUE) OR (left_pickup_at IS NOT NULL AND left_pickup_at_corrected IS NOT NULL)', # noqa:E501 name=op.f('ck_orders_on_corrections_only_for_set_value_9'), ), sa.CheckConstraint( '(pickup_at IS NULL AND pickup_at_corrected IS NULL) OR (pickup_at IS NULL AND pickup_at_corrected IS TRUE) OR (pickup_at IS NOT NULL AND pickup_at_corrected IS NOT NULL)', # noqa:E501 name=op.f('ck_orders_on_corrections_only_for_set_value_8'), ), sa.CheckConstraint( '(restaurant_confirmed_at IS NULL AND restaurant_confirmed_at_corrected IS NULL) OR (restaurant_confirmed_at IS NULL AND restaurant_confirmed_at_corrected IS TRUE) OR (restaurant_confirmed_at IS NOT NULL AND restaurant_confirmed_at_corrected IS NOT NULL)', # noqa:E501 name=op.f('ck_orders_on_corrections_only_for_set_value_3'), ), sa.CheckConstraint( '(restaurant_notified_at IS NULL AND restaurant_notified_at_corrected IS NULL) OR (restaurant_notified_at IS NULL AND restaurant_notified_at_corrected IS TRUE) OR (restaurant_notified_at IS NOT NULL AND restaurant_notified_at_corrected IS NOT NULL)', # noqa:E501 name=op.f('ck_orders_on_corrections_only_for_set_value_2'), ), sa.CheckConstraint( '(scheduled_delivery_at IS NULL AND scheduled_delivery_at_corrected IS NULL) OR (scheduled_delivery_at IS NULL AND scheduled_delivery_at_corrected IS TRUE) OR (scheduled_delivery_at IS NOT NULL AND scheduled_delivery_at_corrected IS NOT NULL)', # noqa:E501 name=op.f('ck_orders_on_corrections_only_for_set_value_0'), ), sa.CheckConstraint( '(ad_hoc IS TRUE AND scheduled_delivery_at IS NULL) OR (ad_hoc IS FALSE AND scheduled_delivery_at IS NOT NULL)', # noqa:E501 name=op.f('ck_orders_on_either_ad_hoc_or_scheduled_order'), ), sa.CheckConstraint( 'NOT (EXTRACT(EPOCH FROM scheduled_delivery_at - placed_at) < 1800)', name=op.f('ck_orders_on_scheduled_orders_not_within_30_minutes'), ), sa.CheckConstraint( 'NOT (ad_hoc IS FALSE AND ((EXTRACT(HOUR FROM scheduled_delivery_at) <= 11 AND NOT (EXTRACT(HOUR FROM scheduled_delivery_at) = 11 AND EXTRACT(MINUTE FROM scheduled_delivery_at) = 45)) OR EXTRACT(HOUR FROM scheduled_delivery_at) > 22))', # noqa:E501 name=op.f('ck_orders_on_scheduled_orders_within_business_hours'), ), sa.CheckConstraint( 'NOT (ad_hoc IS TRUE AND (EXTRACT(HOUR FROM placed_at) < 11 OR EXTRACT(HOUR FROM placed_at) > 22))', # noqa:E501 name=op.f('ck_orders_on_ad_hoc_orders_within_business_hours'), ), sa.CheckConstraint( 'NOT (cancelled IS FALSE AND cancelled_at IS NOT NULL)', name=op.f('ck_orders_on_only_cancelled_orders_may_have_cancelled_at'), ), sa.CheckConstraint( 'NOT (cancelled IS TRUE AND delivery_at IS NOT NULL)', name=op.f('ck_orders_on_cancelled_orders_must_not_be_delivered'), ), sa.CheckConstraint( 'cancelled_at > courier_accepted_at', name=op.f('ck_orders_on_ordered_timestamps_16'), ), sa.CheckConstraint( 'cancelled_at > courier_notified_at', name=op.f('ck_orders_on_ordered_timestamps_15'), ), sa.CheckConstraint( 'cancelled_at > delivery_at', name=op.f('ck_orders_on_ordered_timestamps_21'), ), sa.CheckConstraint( 'cancelled_at > dispatch_at', name=op.f('ck_orders_on_ordered_timestamps_14'), ), sa.CheckConstraint( 'cancelled_at > left_pickup_at', name=op.f('ck_orders_on_ordered_timestamps_19'), ), sa.CheckConstraint( 'cancelled_at > pickup_at', name=op.f('ck_orders_on_ordered_timestamps_18'), ), sa.CheckConstraint( 'cancelled_at > reached_delivery_at', name=op.f('ck_orders_on_ordered_timestamps_20'), ), sa.CheckConstraint( 'cancelled_at > reached_pickup_at', name=op.f('ck_orders_on_ordered_timestamps_17'), ), sa.CheckConstraint( 'cancelled_at > restaurant_confirmed_at', name=op.f('ck_orders_on_ordered_timestamps_13'), ), sa.CheckConstraint( 'cancelled_at > restaurant_notified_at', name=op.f('ck_orders_on_ordered_timestamps_12'), ), sa.CheckConstraint( 'courier_accepted_at < delivery_at', name=op.f('ck_orders_on_ordered_timestamps_42'), ), sa.CheckConstraint( 'courier_accepted_at < left_pickup_at', name=op.f('ck_orders_on_ordered_timestamps_40'), ), sa.CheckConstraint( 'courier_accepted_at < pickup_at', name=op.f('ck_orders_on_ordered_timestamps_39'), ), sa.CheckConstraint( 'courier_accepted_at < reached_delivery_at', name=op.f('ck_orders_on_ordered_timestamps_41'), ), sa.CheckConstraint( 'courier_accepted_at < reached_pickup_at', name=op.f('ck_orders_on_ordered_timestamps_38'), ), sa.CheckConstraint( 'courier_notified_at < courier_accepted_at', name=op.f('ck_orders_on_ordered_timestamps_32'), ), sa.CheckConstraint( 'courier_notified_at < delivery_at', name=op.f('ck_orders_on_ordered_timestamps_37'), ), sa.CheckConstraint( 'courier_notified_at < left_pickup_at', name=op.f('ck_orders_on_ordered_timestamps_35'), ), sa.CheckConstraint( 'courier_notified_at < pickup_at', name=op.f('ck_orders_on_ordered_timestamps_34'), ), sa.CheckConstraint( 'courier_notified_at < reached_delivery_at', name=op.f('ck_orders_on_ordered_timestamps_36'), ), sa.CheckConstraint( 'courier_notified_at < reached_pickup_at', name=op.f('ck_orders_on_ordered_timestamps_33'), ), sa.CheckConstraint( 'dispatch_at < courier_accepted_at', name=op.f('ck_orders_on_ordered_timestamps_26'), ), sa.CheckConstraint( 'dispatch_at < courier_notified_at', name=op.f('ck_orders_on_ordered_timestamps_25'), ), sa.CheckConstraint( 'dispatch_at < delivery_at', name=op.f('ck_orders_on_ordered_timestamps_31'), ), sa.CheckConstraint( 'dispatch_at < left_pickup_at', name=op.f('ck_orders_on_ordered_timestamps_29'), ), sa.CheckConstraint( 'dispatch_at < pickup_at', name=op.f('ck_orders_on_ordered_timestamps_28'), ), sa.CheckConstraint( 'dispatch_at < reached_delivery_at', name=op.f('ck_orders_on_ordered_timestamps_30'), ), sa.CheckConstraint( 'dispatch_at < reached_pickup_at', name=op.f('ck_orders_on_ordered_timestamps_27'), ), sa.CheckConstraint( 'estimated_prep_buffer % 60 = 0', name=op.f('ck_orders_on_estimated_prep_buffer_must_be_whole_minutes'), ), sa.CheckConstraint( 'estimated_prep_duration % 60 = 0', name=op.f('ck_orders_on_estimated_prep_duration_must_be_whole_minutes'), ), sa.CheckConstraint( 'left_pickup_at < delivery_at', name=op.f('ck_orders_on_ordered_timestamps_51'), ), sa.CheckConstraint( 'left_pickup_at < reached_delivery_at', name=op.f('ck_orders_on_ordered_timestamps_50'), ), sa.CheckConstraint( 'pickup_at < delivery_at', name=op.f('ck_orders_on_ordered_timestamps_49'), ), sa.CheckConstraint( 'pickup_at < left_pickup_at', name=op.f('ck_orders_on_ordered_timestamps_47'), ), sa.CheckConstraint( 'pickup_at < reached_delivery_at', name=op.f('ck_orders_on_ordered_timestamps_48'), ), sa.CheckConstraint( 'placed_at < cancelled_at', name=op.f('ck_orders_on_ordered_timestamps_2'), ), sa.CheckConstraint( 'placed_at < courier_accepted_at', name=op.f('ck_orders_on_ordered_timestamps_7'), ), sa.CheckConstraint( 'placed_at < courier_notified_at', name=op.f('ck_orders_on_ordered_timestamps_6'), ), sa.CheckConstraint( 'placed_at < delivery_at', name=op.f('ck_orders_on_ordered_timestamps_11'), ), sa.CheckConstraint( 'placed_at < dispatch_at', name=op.f('ck_orders_on_ordered_timestamps_5'), ), sa.CheckConstraint( 'placed_at < first_estimated_delivery_at', name=op.f('ck_orders_on_ordered_timestamps_1'), ), sa.CheckConstraint( 'placed_at < left_pickup_at', name=op.f('ck_orders_on_ordered_timestamps_9'), ), sa.CheckConstraint( 'placed_at < reached_delivery_at', name=op.f('ck_orders_on_ordered_timestamps_10'), ), sa.CheckConstraint( 'placed_at < reached_pickup_at', name=op.f('ck_orders_on_ordered_timestamps_8'), ), sa.CheckConstraint( 'placed_at < restaurant_confirmed_at', name=op.f('ck_orders_on_ordered_timestamps_4'), ), sa.CheckConstraint( 'placed_at < restaurant_notified_at', name=op.f('ck_orders_on_ordered_timestamps_3'), ), sa.CheckConstraint( 'placed_at < scheduled_delivery_at', name=op.f('ck_orders_on_ordered_timestamps_0'), ), sa.CheckConstraint( 'reached_delivery_at < delivery_at', name=op.f('ck_orders_on_ordered_timestamps_52'), ), sa.CheckConstraint( 'reached_pickup_at < delivery_at', name=op.f('ck_orders_on_ordered_timestamps_46'), ), sa.CheckConstraint( 'reached_pickup_at < left_pickup_at', name=op.f('ck_orders_on_ordered_timestamps_44'), ), sa.CheckConstraint( 'reached_pickup_at < pickup_at', name=op.f('ck_orders_on_ordered_timestamps_43'), ), sa.CheckConstraint( 'reached_pickup_at < reached_delivery_at', name=op.f('ck_orders_on_ordered_timestamps_45'), ), sa.CheckConstraint( 'restaurant_confirmed_at < pickup_at', name=op.f('ck_orders_on_ordered_timestamps_24'), ), sa.CheckConstraint( 'restaurant_notified_at < pickup_at', name=op.f('ck_orders_on_ordered_timestamps_23'), ), sa.CheckConstraint( 'restaurant_notified_at < restaurant_confirmed_at', name=op.f('ck_orders_on_ordered_timestamps_22'), ), sa.CheckConstraint( '(pickup_at IS NULL AND pickup_not_confirmed IS NULL) OR (pickup_at IS NOT NULL AND pickup_not_confirmed IS NOT NULL)', # noqa:E501 name=op.f('pickup_not_confirmed_only_if_pickup'), ), sa.CheckConstraint( '(delivery_at IS NULL AND delivery_not_confirmed IS NULL) OR (delivery_at IS NOT NULL AND delivery_not_confirmed IS NOT NULL)', # noqa:E501 name=op.f('delivery_not_confirmed_only_if_delivery'), ), sa.CheckConstraint( '(delivery_at IS NULL AND courier_waited_at_delivery IS NULL) OR (delivery_at IS NOT NULL AND courier_waited_at_delivery IS NOT NULL)', # noqa:E501 name=op.f('courier_waited_at_delivery_only_if_delivery'), ), sa.ForeignKeyConstraint( ['courier_id'], [f'{config.CLEAN_SCHEMA}.couriers.id'], name=op.f('fk_orders_to_couriers_via_courier_id'), onupdate='RESTRICT', ondelete='RESTRICT', ), sa.ForeignKeyConstraint( ['customer_id'], [f'{config.CLEAN_SCHEMA}.customers.id'], name=op.f('fk_orders_to_customers_via_customer_id'), onupdate='RESTRICT', ondelete='RESTRICT', ), sa.ForeignKeyConstraint( ['delivery_address_id'], [f'{config.CLEAN_SCHEMA}.addresses.id'], name=op.f('fk_orders_to_addresses_via_delivery_address_id'), onupdate='RESTRICT', ondelete='RESTRICT', ), sa.ForeignKeyConstraint( ['pickup_address_id'], [f'{config.CLEAN_SCHEMA}.addresses.id'], name=op.f('fk_orders_to_addresses_via_pickup_address_id'), onupdate='RESTRICT', ondelete='RESTRICT', ), sa.ForeignKeyConstraint( ['restaurant_id'], [f'{config.CLEAN_SCHEMA}.restaurants.id'], name=op.f('fk_orders_to_restaurants_via_restaurant_id'), onupdate='RESTRICT', ondelete='RESTRICT', ), sa.PrimaryKeyConstraint('id', name=op.f('pk_orders')), *( [ # noqa:WPS504 sa.ForeignKeyConstraint( ['id'], [f'{config.ORIGINAL_SCHEMA}.orders.id'], name=op.f('pk_orders_sanity'), onupdate='RESTRICT', ondelete='RESTRICT', ), sa.ForeignKeyConstraint( ['delivery_id'], [f'{config.ORIGINAL_SCHEMA}.deliveries.id'], name=op.f('pk_deliveries_sanity'), onupdate='RESTRICT', ondelete='RESTRICT', ), ] if not config.TESTING else [] ), schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_cancelled'), 'orders', ['cancelled'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_cancelled_at_corrected'), 'orders', ['cancelled_at_corrected'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_courier_accepted_at_corrected'), 'orders', ['courier_accepted_at_corrected'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_courier_id'), 'orders', ['courier_id'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_courier_notified_at_corrected'), 'orders', ['courier_notified_at_corrected'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_customer_id'), 'orders', ['customer_id'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_delivery_address_id'), 'orders', ['delivery_address_id'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_delivery_at_corrected'), 'orders', ['delivery_at_corrected'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_delivery_id'), 'orders', ['delivery_id'], unique=True, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_dispatch_at_corrected'), 'orders', ['dispatch_at_corrected'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_estimated_prep_buffer'), 'orders', ['estimated_prep_buffer'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_estimated_prep_duration'), 'orders', ['estimated_prep_duration'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_estimated_prep_duration_corrected'), 'orders', ['estimated_prep_duration_corrected'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_left_pickup_at_corrected'), 'orders', ['left_pickup_at_corrected'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_pickup_address_id'), 'orders', ['pickup_address_id'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_pickup_at_corrected'), 'orders', ['pickup_at_corrected'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_placed_at'), 'orders', ['placed_at'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_restaurant_confirmed_at_corrected'), 'orders', ['restaurant_confirmed_at_corrected'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_restaurant_id'), 'orders', ['restaurant_id'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_restaurant_notified_at_corrected'), 'orders', ['restaurant_notified_at_corrected'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_scheduled_delivery_at'), 'orders', ['scheduled_delivery_at'], unique=False, schema=config.CLEAN_SCHEMA, ) op.create_index( op.f('ix_orders_on_scheduled_delivery_at_corrected'), 'orders', ['scheduled_delivery_at_corrected'], unique=False, schema=config.CLEAN_SCHEMA, ) def downgrade(): """Downgrade to revision None.""" op.execute(f'DROP SCHEMA {config.CLEAN_SCHEMA} CASCADE;')