def upgrade():
    op.drop_constraint('fk_vfolder_attachment_vfolder_vfolders', 'vfolder_attachment', type_='foreignkey')
    op.drop_constraint('fk_vfolder_attachment_kernel_kernels', 'vfolder_attachment', type_='foreignkey')
    op.drop_constraint('pk_kernels', 'kernels', type_='primary')
    op.add_column('kernels',
                  sa.Column('id', GUID(),
                            server_default=sa.text('uuid_generate_v4()'),
                            nullable=False))
    op.add_column('kernels', sa.Column('role', sa.String(length=16), nullable=False, default='master'))
    op.create_primary_key('pk_kernels', 'kernels', ['id'])
    op.alter_column(
        'kernels', 'sess_id',
        existing_type=postgresql.UUID(),
        type_=sa.String(length=64),
        nullable=True,
        existing_server_default=sa.text('uuid_generate_v4()'))
    op.create_index(op.f('ix_kernels_sess_id'), 'kernels', ['sess_id'], unique=False)
    op.create_index(op.f('ix_kernels_sess_id_role'), 'kernels', ['sess_id', 'role'], unique=False)
    op.create_foreign_key('fk_vfolder_attachment_vfolder_vfolders',
                          'vfolder_attachment', 'vfolders',
                          ['vfolder'], ['id'], onupdate='CASCADE', ondelete='CASCADE')
    op.create_foreign_key('fk_vfolder_attachment_kernel_kernels',
                          'vfolder_attachment', 'kernels',
                          ['kernel'], ['id'], onupdate='CASCADE', ondelete='CASCADE') 

def downgrade():
    op.add_column(
        "projects",
        sa.Column("client_id", postgresql.UUID(), autoincrement=False, nullable=True),
    )
    op.add_column(
        "projects",
        sa.Column("secret_key", postgresql.UUID(), autoincrement=False, nullable=True),
    )
    op.drop_index(
        "release_username_password_is_active_idx", table_name="project_credentials"
    )
    op.drop_index(
        op.f("ix_project_credentials_is_active"), table_name="project_credentials"
    )
    op.drop_table("project_credentials") 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('output_file', sa.Column('uploaded_movie_url', sa.VARCHAR(length=600), autoincrement=False, nullable=True))
    op.add_column('output_file', sa.Column('uploaded_movie_name', sa.VARCHAR(length=150), autoincrement=False, nullable=True))
    op.drop_constraint(None, 'output_file', type_='foreignkey')
    op.drop_constraint('output_file_uc', 'output_file', type_='unique')
    op.create_unique_constraint('output_file_uc', 'output_file', ['name', 'entity_id', 'output_type_id', 'task_type_id', 'representation', 'revision'])
    op.drop_column('output_file', 'temporal_entity_id')
    op.drop_constraint(None, 'asset_instance', type_='foreignkey')
    op.create_index('ix_asset_instance_entity_type_id', 'asset_instance', ['entity_type_id'], unique=False)
    op.create_index('ix_asset_instance_entity_id', 'asset_instance', ['entity_id'], unique=False)
    op.drop_constraint('asset_instance_uc', 'asset_instance', type_='unique')
    op.create_unique_constraint('asset_instance_uc', 'asset_instance', ['asset_id', 'entity_id', 'number'])
    op.drop_constraint('asset_instance_name_uc', 'asset_instance', type_='unique')
    op.create_unique_constraint('asset_instance_name_uc', 'asset_instance', ['entity_id', 'name'])
    op.drop_index(op.f('ix_asset_instance_scene_id'), table_name='asset_instance')
    op.alter_column('asset_instance', 'entity_type_id',
               existing_type=postgresql.UUID(),
               nullable=False)
    op.alter_column('asset_instance', 'entity_id',
               existing_type=postgresql.UUID(),
               nullable=False)
    op.drop_column('asset_instance', 'scene_id')
    op.drop_table('asset_instance_link')
    # ### end Alembic commands ### 

def upgrade():

    op.create_table('monthly_billing',
                    sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
                    sa.Column('service_id', postgresql.UUID(as_uuid=True), nullable=False),
                    sa.Column('month', sa.String(), nullable=False),
                    sa.Column('year', sa.Float(), nullable=False),
                    sa.Column('notification_type',
                              postgresql.ENUM('email', 'sms', 'letter', name='notification_type', create_type=False),
                              nullable=False),
                    sa.Column('monthly_totals', postgresql.JSON(), nullable=False),
                    sa.Column('updated_at', sa.DateTime, nullable=False),
                    sa.ForeignKeyConstraint(['service_id'], ['services.id'], ),
                    sa.PrimaryKeyConstraint('id')
                    )
    op.create_index(op.f('ix_monthly_billing_service_id'), 'monthly_billing', ['service_id'], unique=False)
    op.create_index(op.f('uix_monthly_billing'), 'monthly_billing', ['service_id', 'month', 'year', 'notification_type'], unique=True) 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('services_history', sa.Column('organisation_id', postgresql.UUID(), autoincrement=False, nullable=True))  # noqa
    op.add_column('services', sa.Column('organisation_id', postgresql.UUID(), autoincrement=False, nullable=True))

    op.create_table(
        'organisation',
        sa.Column('id', postgresql.UUID(), autoincrement=False, nullable=False),
        sa.Column('colour', sa.VARCHAR(length=7), autoincrement=False, nullable=True),
        sa.Column('logo', sa.VARCHAR(length=255), autoincrement=False, nullable=True),
        sa.Column('name', sa.VARCHAR(length=255), autoincrement=False, nullable=True),
        sa.PrimaryKeyConstraint('id', name='organisation_pkey')
    )

    op.create_index('ix_services_history_organisation_id', 'services_history', ['organisation_id'], unique=False)
    op.create_foreign_key('services_organisation_id_fkey', 'services', 'organisation', ['organisation_id'], ['id'])
    op.create_index('ix_services_organisation_id', 'services', ['organisation_id'], unique=False)

    op.alter_column('service_email_branding', 'email_branding_id', nullable=True) 

def upgrade():
    op.create_table(
        'domain',
        sa.Column('domain', sa.String(length=255), nullable=False),
        sa.Column('organisation_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.ForeignKeyConstraint(['organisation_id'], ['organisation.id'], ),
        sa.PrimaryKeyConstraint('domain')
    )
    op.create_index(op.f('ix_domain_domain'), 'domain', ['domain'], unique=True)

    op.add_column('organisation', sa.Column('email_branding_id', postgresql.UUID(as_uuid=True), nullable=True))
    op.create_foreign_key('fk_organisation_email_branding_id', 'organisation', 'email_branding', ['email_branding_id'], ['id'])

    op.add_column('organisation', sa.Column('letter_branding_id', postgresql.UUID(as_uuid=True), nullable=True))
    op.create_foreign_key('fk_organisation_letter_branding_id', 'organisation', 'letter_branding', ['letter_branding_id'], ['id'])

    op.add_column('organisation', sa.Column('agreement_signed', sa.Boolean(), nullable=True))
    op.add_column('organisation', sa.Column('agreement_signed_at', sa.DateTime(), nullable=True))
    op.add_column('organisation', sa.Column('agreement_signed_by_id', postgresql.UUID(as_uuid=True), nullable=True))
    op.add_column('organisation', sa.Column('agreement_signed_version', sa.Float(), nullable=True))
    op.add_column('organisation', sa.Column('crown', sa.Boolean(), nullable=True))
    op.add_column('organisation', sa.Column('organisation_type', sa.String(length=255), nullable=True))
    op.create_foreign_key('fk_organisation_agreement_user_id', 'organisation', 'users', ['agreement_signed_by_id'], ['id']) 

def upgrade():
    op.create_table('job_statistics',
    sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
    sa.Column('job_id', postgresql.UUID(as_uuid=True), nullable=False),
    sa.Column('emails_sent', sa.BigInteger(), nullable=False),
    sa.Column('emails_delivered', sa.BigInteger(), nullable=False),
    sa.Column('emails_failed', sa.BigInteger(), nullable=False),
    sa.Column('sms_sent', sa.BigInteger(), nullable=False),
    sa.Column('sms_delivered', sa.BigInteger(), nullable=False),
    sa.Column('sms_failed', sa.BigInteger(), nullable=False),
    sa.Column('letters_sent', sa.BigInteger(), nullable=False),
    sa.Column('letters_failed', sa.BigInteger(), nullable=False),
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['job_id'], ['jobs.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_job_statistics_job_id'), 'job_statistics', ['job_id'], unique=True) 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('provider_details', sa.Column('created_by_id', postgresql.UUID(as_uuid=True), nullable=True))
    op.create_index(op.f('ix_provider_details_created_by_id'), 'provider_details', ['created_by_id'], unique=False)
    op.create_foreign_key('provider_details_created_by_id_fkey', 'provider_details', 'users', ['created_by_id'], ['id'])
    op.add_column('provider_details_history', sa.Column('created_by_id', postgresql.UUID(as_uuid=True), nullable=True))
    op.create_index(
        op.f('ix_provider_details_history_created_by_id'),
        'provider_details_history',
        ['created_by_id'],
        unique=False
    )
    op.create_foreign_key(
        'provider_details_history_created_by_id_fkey',
        'provider_details_history',
        'users',
        ['created_by_id'],
        ['id']
    )
    # ### end Alembic commands ### 

def upgrade():
    notification_types = postgresql.ENUM('email', 'sms', 'letter', name='notification_type', create_type=False)
    op.create_table('rates',
    sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
    sa.Column('valid_from', sa.DateTime(), nullable=False),
    sa.Column('rate', sa.Numeric(), nullable=False),
    sa.Column('notification_type', notification_types, nullable=False),
    sa.PrimaryKeyConstraint('id')
    )

    op.create_index(op.f('ix_rates_notification_type'), 'rates', ['notification_type'], unique=False)

    op.get_bind()
    op.execute("INSERT INTO rates(id, valid_from, rate, notification_type) "
               "VALUES('{}', '2016-05-18 00:00:00', 1.65, 'sms')".format(uuid.uuid4()))
    op.execute("INSERT INTO rates(id, valid_from, rate, notification_type) "
               "VALUES('{}', '2017-04-01 00:00:00', 1.58, 'sms')".format(uuid.uuid4())) 

def upgrade():
    op.create_table(
        'service_contact_list',
        sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('original_file_name', sa.String(), nullable=False),
        sa.Column('row_count', sa.Integer(), nullable=False),
        sa.Column('template_type', postgresql.ENUM(name='template_type', create_type=False), nullable=False),
        sa.Column('service_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('created_by_id', postgresql.UUID(as_uuid=True), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=False),
        sa.Column('updated_at', sa.DateTime(), nullable=True),
        sa.ForeignKeyConstraint(['created_by_id'], ['users.id'], ),
        sa.ForeignKeyConstraint(['service_id'], ['services.id'], ),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_service_contact_list_created_by_id'), 'service_contact_list', ['created_by_id'], unique=False)
    op.create_index(op.f('ix_service_contact_list_service_id'), 'service_contact_list', ['service_id'], unique=False)
    op.add_column('jobs', sa.Column('contact_list_id', postgresql.UUID(as_uuid=True), nullable=True))
    op.create_foreign_key('jobs_contact_list_id_fkey', 'jobs', 'service_contact_list', ['contact_list_id'], ['id']) 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('monthly_billing',
    sa.Column('id', postgresql.UUID(), autoincrement=False, nullable=False),
    sa.Column('service_id', postgresql.UUID(), autoincrement=False, nullable=False),
    sa.Column('notification_type', postgresql.ENUM('email', 'sms', 'letter', name='notification_type'), autoincrement=False, nullable=False),
    sa.Column('monthly_totals', postgresql.JSON(astext_type=sa.Text()), autoincrement=False, nullable=False),
    sa.Column('updated_at', postgresql.TIMESTAMP(), autoincrement=False, nullable=False),
    sa.Column('start_date', postgresql.TIMESTAMP(), autoincrement=False, nullable=False),
    sa.Column('end_date', postgresql.TIMESTAMP(), autoincrement=False, nullable=False),
    sa.ForeignKeyConstraint(['service_id'], ['services.id'], name='monthly_billing_service_id_fkey'),
    sa.PrimaryKeyConstraint('id', name='monthly_billing_pkey'),
    sa.UniqueConstraint('service_id', 'start_date', 'notification_type', name='uix_monthly_billing')
    )
    op.create_index('ix_monthly_billing_service_id', 'monthly_billing', ['service_id'], unique=False)
    # ### end Alembic commands ### 

def downgrade():
    op.create_table('job_statistics',
    sa.Column('id', postgresql.UUID(), autoincrement=False, nullable=False),
    sa.Column('job_id', postgresql.UUID(), autoincrement=False, nullable=False),
    sa.Column('emails_sent', sa.BIGINT(), autoincrement=False, nullable=False),
    sa.Column('emails_delivered', sa.BIGINT(), autoincrement=False, nullable=False),
    sa.Column('emails_failed', sa.BIGINT(), autoincrement=False, nullable=False),
    sa.Column('sms_sent', sa.BIGINT(), autoincrement=False, nullable=False),
    sa.Column('sms_delivered', sa.BIGINT(), autoincrement=False, nullable=False),
    sa.Column('sms_failed', sa.BIGINT(), autoincrement=False, nullable=False),
    sa.Column('letters_sent', sa.BIGINT(), autoincrement=False, nullable=False),
    sa.Column('letters_failed', sa.BIGINT(), autoincrement=False, nullable=False),
    sa.Column('created_at', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
    sa.Column('updated_at', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
    sa.Column('sent', sa.BIGINT(), autoincrement=False, nullable=True),
    sa.Column('delivered', sa.BIGINT(), autoincrement=False, nullable=True),
    sa.Column('failed', sa.BIGINT(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['job_id'], ['jobs.id'], name='job_statistics_job_id_fkey'),
    sa.PrimaryKeyConstraint('id', name='job_statistics_pkey')
    ) 

def upgrade():
    op.create_table('service_sms_senders',
                    sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
                    sa.Column('sms_sender', sa.String(length=11), nullable=False),
                    sa.Column('service_id', postgresql.UUID(as_uuid=True), nullable=False),
                    sa.Column('is_default', sa.Boolean(), nullable=False),
                    sa.Column('inbound_number_id', postgresql.UUID(as_uuid=True), nullable=True),
                    sa.Column('created_at', sa.DateTime(), nullable=False),
                    sa.Column('updated_at', sa.DateTime(), nullable=True),
                    sa.ForeignKeyConstraint(['inbound_number_id'], ['inbound_numbers.id'], ),
                    sa.ForeignKeyConstraint(['service_id'], ['services.id'], ),
                    sa.PrimaryKeyConstraint('id')
                    )
    op.create_index(op.f('ix_service_sms_senders_inbound_number_id'), 'service_sms_senders', ['inbound_number_id'],
                    unique=True)
    op.create_index(op.f('ix_service_sms_senders_service_id'), 'service_sms_senders', ['service_id'], unique=True)

    # populate govuk seeded service
    op.execute("""
        INSERT INTO service_sms_senders
        (id, sms_sender, service_id, is_default, inbound_number_id, created_at, updated_at)
        VALUES ('286d6176-adbe-7ea7-ba26-b7606ee5e2a4', 'GOVUK', 'd6aa2c68-a2d9-4437-ab19-3ae8eb202553', true, null, now(), null)
    """) 

def upgrade():
    op.create_index(op.f('ix_service_letter_contacts_service_id'), 'service_letter_contacts', ['service_id'],
                    unique=False)
    op.drop_index('ix_service_letter_contact_service_id', table_name='service_letter_contacts')
    op.create_index(op.f('ix_service_sms_senders_service_id'), 'service_sms_senders', ['service_id'], unique=False)
    op.execute(
        'ALTER TABLE templates_history ALTER COLUMN template_type TYPE template_type USING template_type::template_type')

    # new table
    op.create_table('notification_to_sms_sender',
    sa.Column('notification_id', postgresql.UUID(as_uuid=True), nullable=False),
    sa.Column('service_sms_sender_id', postgresql.UUID(as_uuid=True), nullable=False),
    sa.ForeignKeyConstraint(['notification_id'], ['notifications.id'], ),
    sa.ForeignKeyConstraint(['service_sms_sender_id'], ['service_sms_senders.id'], ),
    sa.PrimaryKeyConstraint('notification_id', 'service_sms_sender_id')
    )
    op.create_index(op.f('ix_notification_to_sms_sender_notification_id'), 'notification_to_sms_sender', ['notification_id'], unique=True)
    op.create_index(op.f('ix_notification_to_sms_sender_service_sms_sender_id'), 'notification_to_sms_sender', ['service_sms_sender_id'], unique=False) 

def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint("fk_templates_created_by_id", 'templates', type_='foreignkey')
    op.drop_index(op.f('ix_templates_created_by_id'), table_name='templates')
    op.drop_column('templates', 'version')
    op.drop_column('templates', 'created_by_id')
    op.alter_column('api_keys_history', 'created_by_id',
               existing_type=postgresql.UUID(),
               nullable=True)
    op.alter_column('api_keys_history', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True)
    op.drop_index(op.f('ix_templates_history_service_id'), table_name='templates_history')
    op.drop_index(op.f('ix_templates_history_created_by_id'), table_name='templates_history')
    op.drop_table('templates_history')
    ### end Alembic commands ### 

def upgrade():
    op.create_table(
        'inbound_sms',
        sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('service_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('content', sa.String, nullable=False),
        sa.Column('notify_number', sa.String, nullable=False),
        sa.Column('user_number', sa.String, nullable=False),
        sa.Column('created_at', sa.DateTime, nullable=False),
        sa.Column('provider_date', sa.DateTime, nullable=True),
        sa.Column('provider_reference', sa.String, nullable=True),

        sa.ForeignKeyConstraint(['service_id'], ['services.id'], ),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_inbound_sms_service_id'), 'inbound_sms', ['service_id'], unique=False)
    op.create_index(op.f('ix_inbound_sms_user_number'), 'inbound_sms', ['user_number'], unique=False) 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('template_folder',
        sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('service_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('name', sa.String(), nullable=False),
        sa.Column('parent_id', postgresql.UUID(as_uuid=True), nullable=True),
        sa.ForeignKeyConstraint(['parent_id'], ['template_folder.id'], ),
        sa.ForeignKeyConstraint(['service_id'], ['services.id'], ),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_table('template_folder_map',
        sa.Column('template_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column('template_folder_id', postgresql.UUID(as_uuid=True), nullable=False),
        sa.ForeignKeyConstraint(['template_folder_id'], ['template_folder.id'], ),
        sa.ForeignKeyConstraint(['template_id'], ['templates.id'], ),
        sa.PrimaryKeyConstraint('template_id')
    ) 

def downgrade():
    op.drop_table('letter_rates')
    op.create_table('letter_rates',
                    sa.Column('id', postgresql.UUID(), autoincrement=False, nullable=False),
                    sa.Column('valid_from', postgresql.TIMESTAMP(), autoincrement=False, nullable=False),
                    sa.PrimaryKeyConstraint('id', name='letter_rates_pkey'),
                    postgresql_ignore_search_path=False
                    )
    op.create_table('letter_rate_details',
                    sa.Column('id', postgresql.UUID(), autoincrement=False, nullable=False),
                    sa.Column('letter_rate_id', postgresql.UUID(), autoincrement=False, nullable=False),
                    sa.Column('page_total', sa.INTEGER(), autoincrement=False, nullable=False),
                    sa.Column('rate', sa.NUMERIC(), autoincrement=False, nullable=False),
                    sa.ForeignKeyConstraint(['letter_rate_id'], ['letter_rates.id'],
                                            name='letter_rate_details_letter_rate_id_fkey'),
                    sa.PrimaryKeyConstraint('id', name='letter_rate_details_pkey')
                    ) 

def upgrade():
    op.create_table('provider_rates',
    sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
    sa.Column('valid_from', sa.DateTime(), nullable=False),
    sa.Column('provider', sa.Enum('mmg', 'twilio', 'firetext', 'ses', name='providers'), nullable=False),
    sa.Column('rate', sa.Numeric(), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('provider_statistics',
    sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
    sa.Column('day', sa.Date(), nullable=False),
    sa.Column('provider', sa.Enum('mmg', 'twilio', 'firetext', 'ses', name='providers'), nullable=False),
    sa.Column('service_id', postgresql.UUID(as_uuid=True), nullable=False),
    sa.Column('unit_count', sa.BigInteger(), nullable=False),
    sa.ForeignKeyConstraint(['service_id'], ['services.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_provider_statistics_service_id'), 'provider_statistics', ['service_id'], unique=False) 

def upgrade():
    op.create_table('letter_branding',
                    sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
                    sa.Column('name', sa.String(length=255), nullable=False),
                    sa.Column('filename', sa.String(length=255), nullable=False),
                    sa.Column('domain', sa.Text(), nullable=True),
                    sa.PrimaryKeyConstraint('id'),
                    sa.UniqueConstraint('domain'),
                    sa.UniqueConstraint('filename'),
                    sa.UniqueConstraint('name')
                    )
    op.create_table('service_letter_branding',
                    sa.Column('service_id', postgresql.UUID(as_uuid=True), nullable=False),
                    sa.Column('letter_branding_id', postgresql.UUID(as_uuid=True), nullable=False),
                    sa.ForeignKeyConstraint(['letter_branding_id'], ['letter_branding.id'], ),
                    sa.ForeignKeyConstraint(['service_id'], ['services.id'], ),
                    sa.PrimaryKeyConstraint('service_id')
                    )

    op.get_bind() 

def downgrade():
    op.create_table('notification_to_email_reply_to',
                    sa.Column('notification_id', postgresql.UUID(), autoincrement=False, nullable=False),
                    sa.Column('service_email_reply_to_id', postgresql.UUID(), autoincrement=False, nullable=False),
                    sa.ForeignKeyConstraint(['notification_id'], ['notifications.id'],
                                            name='notification_to_email_reply_to_notification_id_fkey'),
                    sa.ForeignKeyConstraint(['service_email_reply_to_id'], ['service_email_reply_to.id'],
                                            name='notification_to_email_reply_to_service_email_reply_to_id_fkey'),
                    sa.PrimaryKeyConstraint('notification_id', 'service_email_reply_to_id',
                                            name='notification_to_email_reply_to_pkey')
                    )
    op.create_table('notification_to_sms_sender',
                    sa.Column('notification_id', postgresql.UUID(), autoincrement=False, nullable=False),
                    sa.Column('service_sms_sender_id', postgresql.UUID(), autoincrement=False, nullable=False),
                    sa.ForeignKeyConstraint(['notification_id'], ['notifications.id'],
                                            name='notification_to_sms_sender_notification_id_fkey'),
                    sa.ForeignKeyConstraint(['service_sms_sender_id'], ['service_sms_senders.id'],
                                            name='notification_to_sms_sender_service_sms_sender_id_fkey'),
                    sa.PrimaryKeyConstraint('notification_id', 'service_sms_sender_id',
                                            name='notification_to_sms_sender_pkey')
                    ) 

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.get_bind()
    query = 'update templates_history set created_by_id = ' \
            '(select created_by_id from templates where templates.id = templates_history.id) ' \
            'where created_by_id is null'
    op.execute(query)
    op.execute('update templates_history set archived = False')
    op.alter_column('api_keys_history', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False)
    op.alter_column('api_keys_history', 'created_by_id',
               existing_type=postgresql.UUID(),
               nullable=False)
    op.alter_column('templates_history', 'archived',
               existing_type=sa.BOOLEAN(),
               nullable=False)
    op.alter_column('templates_history', 'created_by_id',
               existing_type=postgresql.UUID(),
               nullable=False)
    ### end Alembic commands ### 

def find_by_id(cls, request_id: uuid.UUID) -> Optional["VerificationRequest"]:
        """通过ID查找注册请求，如果没找到返回None"""
        try:
            return db_session.query(cls).filter(cls.request_id == request_id).one()
        except NoResultFound:
            return None 

def load_dialect_impl(self, dialect):
        if dialect.name == 'postgresql':
            return dialect.type_descriptor(UUID())
        else:
            return dialect.type_descriptor(CHAR(16)) 

def process_bind_param(self, value, dialect):
        if value is None:
            return value
        elif dialect.name == 'postgresql':
            if isinstance(value, uuid.UUID):
                return str(value)
            else:
                return str(uuid.UUID(value))
        else:
            if isinstance(value, uuid.UUID):
                return value.bytes
            else:
                return uuid.UUID(value).bytes 

def process_result_value(self, value, dialect):
        if value is None:
            return value
        else:
            return uuid.UUID(value) 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('kernels', sa.Column('agent_id', sa.VARCHAR(), autoincrement=False, nullable=True))
    op.drop_constraint(op.f('fk_kernels_agent_agents'), 'kernels', type_='foreignkey')
    op.drop_index(op.f('ix_kernels_terminated_at'), table_name='kernels')
    op.drop_index(op.f('ix_kernels_status'), table_name='kernels')
    op.drop_index(op.f('ix_kernels_created_at'), table_name='kernels')
    op.drop_column('kernels', 'status_info')
    op.drop_column('kernels', 'num_queries')
    op.drop_column('kernels', 'net_tx_bytes')
    op.drop_column('kernels', 'net_rx_bytes')
    op.drop_column('kernels', 'max_mem_bytes')
    op.drop_column('kernels', 'io_write_bytes')
    op.drop_column('kernels', 'io_read_bytes')
    op.drop_column('kernels', 'cur_mem_bytes')
    op.drop_column('kernels', 'cpu_used')
    op.drop_column('kernels', 'allocated_cores')
    op.drop_column('kernels', 'agent')
    op.create_table(
        'usage',
        sa.Column('id', postgresql.UUID(), server_default=sa.text('uuid_generate_v4()'), autoincrement=False, nullable=False),
        sa.Column('access_key_id', sa.VARCHAR(length=20), autoincrement=False, nullable=True),
        sa.Column('kernel_type', sa.VARCHAR(), autoincrement=False, nullable=True),
        sa.Column('kernel_id', sa.VARCHAR(), autoincrement=False, nullable=True),
        sa.Column('started_at', postgresql.TIMESTAMP(timezone=True), autoincrement=False, nullable=True),
        sa.Column('terminated_at', postgresql.TIMESTAMP(timezone=True), autoincrement=False, nullable=True),
        sa.Column('cpu_used', sa.INTEGER(), server_default=sa.text('0'), autoincrement=False, nullable=True),
        sa.Column('mem_used', sa.INTEGER(), server_default=sa.text('0'), autoincrement=False, nullable=True),
        sa.Column('io_used', sa.INTEGER(), server_default=sa.text('0'), autoincrement=False, nullable=True),
        sa.Column('net_used', sa.INTEGER(), server_default=sa.text('0'), autoincrement=False, nullable=True),
        sa.ForeignKeyConstraint(['access_key_id'], ['keypairs.access_key'], name='fk_usage_access_key_id_keypairs'),
        sa.PrimaryKeyConstraint('id', name='pk_usage')
    )
    op.drop_table('vfolder_attachment')
    op.drop_table('vfolders')
    op.drop_table('agents')
    # ### end Alembic commands ### 

def bind_expression(self, value):
        return cast(value, UUID) 

def load_dialect_impl(self, dialect):
        if dialect.name == 'postgresql':
            return dialect.type_descriptor(UUID())
        elif dialect.name == 'oracle':
            return dialect.type_descriptor(RAW(16))
        elif dialect.name == 'mysql':
            return dialect.type_descriptor(BINARY(16))
        else:
            return dialect.type_descriptor(CHAR(32)) 

def process_bind_param(self, value, dialect):
        if value is None:
            return value
        elif dialect.name == 'postgresql':
            return str(value).lower()
        elif dialect.name == 'oracle':
            return uuid.UUID(value).bytes
        elif dialect.name == 'mysql':
            return uuid.UUID(value).bytes
        else:
            if not isinstance(value, uuid.UUID):
                return "%.32x" % uuid.UUID(value)
            else:
                # hexstring
                return "%.32x" % value