text: """affidavit
  
  Revision ID: 23a3d94ee00d
  Revises: 422daf97fd19
  Create Date: 2020-06-11 16:43:08.259779
  
  """
  import sqlalchemy as sa
  from alembic import op
  from sqlalchemy import Boolean, String
  from sqlalchemy.sql import column, table
  
  
  # revision identifiers, used by Alembic.
  revision = '23a3d94ee00d'
  down_revision = '422daf97fd19'
  branch_labels = None
  depends_on = None
  
  
  def upgrade():
      # ### commands auto generated by Alembic - please adjust! ###
      affidavit_status_table = op.create_table('affidavit_status',
                                               sa.Column('created', sa.DateTime(), nullable=True),
                                               sa.Column('modified', sa.DateTime(), nullable=True),
                                               sa.Column('code', sa.String(length=15), nullable=False),
                                               sa.Column('desc', sa.String(length=100), nullable=True),
                                               sa.Column('default', sa.Boolean(), nullable=False),
                                               sa.Column('created_by_id', sa.Integer(), nullable=True),
                                               sa.Column('modified_by_id', sa.Integer(), nullable=True),
                                               sa.ForeignKeyConstraint(['created_by_id'], ['user.id'], ),
                                               sa.ForeignKeyConstraint(['modified_by_id'], ['user.id'], ),
                                               sa.PrimaryKeyConstraint('code')
                                               )
      op.create_table('affidavit',
                      sa.Column('created', sa.DateTime(), nullable=True),
                      sa.Column('modified', sa.DateTime(), nullable=True),
                      sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
                      sa.Column('document_id', sa.String(length=50), nullable=True),
                      sa.Column('issuer', sa.String(length=250), nullable=True),
                      sa.Column('status_code', sa.String(length=15), nullable=False),
                      sa.Column('decision_made_by', sa.String(length=250), nullable=True),
                      sa.Column('decision_made_on', sa.DateTime(), nullable=True),
                      sa.Column('user_id', sa.Integer(), nullable=False),
                      sa.Column('created_by_id', sa.Integer(), nullable=True),
                      sa.Column('modified_by_id', sa.Integer(), nullable=True),
                      sa.ForeignKeyConstraint(['created_by_id'], ['user.id'], ),
                      sa.ForeignKeyConstraint(['modified_by_id'], ['user.id'], ),
                      sa.ForeignKeyConstraint(['status_code'], ['affidavit_status.code'], ),
                      sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
                      sa.PrimaryKeyConstraint('id')
                      )
      op.create_index(op.f('ix_affidavit_document_id'), 'affidavit', ['document_id'], unique=False)
      op.add_column('contact_link', sa.Column('affidavit_id', sa.Integer(), nullable=True))
      op.create_foreign_key('contact_link_affidavit_fk', 'contact_link', 'affidavit', ['affidavit_id'], ['id'])
      op.alter_column('entity', 'corp_type_code',
                      existing_type=sa.VARCHAR(length=15),
                      nullable=False)
      op.create_index(op.f('ix_entity_folio_number'), 'entity', ['folio_number'], unique=False)
      op.drop_constraint('org_type_code_key', 'org_type', type_='unique')
      op.drop_constraint('payment_type_code_key', 'payment_type', type_='unique')
  
      op.add_column('user', sa.Column('idp_userid', sa.String(length=256), nullable=True))
      op.create_index(op.f('ix_user_idp_userid'), 'user', ['idp_userid'], unique=False)
  
      # Insert affidavit statuses
      op.bulk_insert(
          affidavit_status_table,
          [
              {'code': 'APPROVED', 'desc': 'Approved affidavit', 'default': False},
              {'code': 'PENDING', 'desc': 'Pending decision', 'default': True},
              {'code': 'REJECTED', 'desc': 'Rejected Affidavit', 'default': False}
          ]
      )
  
      org_status_table = table('org_status',
                               column('code', String),
                               column('desc', String),
                               column('default', Boolean)
                               )
      op.bulk_insert(
          org_status_table,
          [
              {'code': 'REJECTED', 'desc': 'Status for a rejected account',
               'default': False}
          ]
      )
  
      op.execute('update org set access_type=\'REGULAR\' where access_type=\'BCSC\'')
      # ### end Alembic commands ###
  
  
  def downgrade():
      # ### commands auto generated by Alembic - please adjust! ###
      op.drop_index(op.f('ix_user_idp_userid'), table_name='user')
      op.drop_column('user', 'idp_userid')
  
      op.create_unique_constraint('payment_type_code_key', 'payment_type', ['code'])
      op.create_unique_constraint('org_type_code_key', 'org_type', ['code'])
      op.drop_index(op.f('ix_entity_folio_number'), table_name='entity')
      op.alter_column('entity', 'corp_type_code',
                      existing_type=sa.VARCHAR(length=15),
                      nullable=True)
      op.drop_constraint('contact_link_affidavit_fk', 'contact_link', type_='foreignkey')
      op.drop_column('contact_link', 'affidavit_id')
      op.drop_index(op.f('ix_affidavit_document_id'), table_name='affidavit')
      op.drop_table('affidavit')
      op.drop_table('affidavit_status')
  
      op.execute('update org set access_type=\'BCSC\' where access_type=\'REGULAR\'')
      op.execute('delete from org_status where code=\'REJECTED\'')
  
      # ### end Alembic commands ###
  