"""consolidate_databases

Revision ID: 7833b2378161
Revises: d279720144e8
Create Date: 2016-07-30 16:47:08.855035

"""

# revision identifiers, used by Alembic.
revision = '7833b2378161'
down_revision = None
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa


def upgrade(engine_name):
    globals()["upgrade_%s" % engine_name]()


def downgrade(engine_name):
    globals()["downgrade_%s" % engine_name]()

def upgrade_data_broker():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('email_template_type',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('email_template_type_id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Text(), nullable=True),
    sa.Column('description', sa.Text(), nullable=True),
    sa.PrimaryKeyConstraint('email_template_type_id')
    )
    op.create_table('email_token',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('email_token_id', sa.Integer(), nullable=False),
    sa.Column('token', sa.Text(), nullable=True),
    sa.Column('salt', sa.Text(), nullable=True),
    sa.PrimaryKeyConstraint('email_token_id')
    )
    op.create_table('error_type',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('error_type_id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Text(), nullable=True),
    sa.Column('description', sa.Text(), nullable=True),
    sa.PrimaryKeyConstraint('error_type_id')
    )
    op.create_table('field_type',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('field_type_id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Text(), nullable=True),
    sa.Column('description', sa.Text(), nullable=True),
    sa.PrimaryKeyConstraint('field_type_id')
    )
    op.create_table('file_status',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('file_status_id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Text(), nullable=True),
    sa.Column('description', sa.Text(), nullable=True),
    sa.PrimaryKeyConstraint('file_status_id')
    )
    op.create_table('file_type',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('file_type_id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Text(), nullable=True),
    sa.Column('description', sa.Text(), nullable=True),
    sa.PrimaryKeyConstraint('file_type_id')
    )
    op.create_table('file_type_validation',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('file_id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Text(), nullable=True),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('file_order', sa.Integer(), server_default='0', nullable=False),
    sa.PrimaryKeyConstraint('file_id')
    )
    op.create_table('job_status',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('job_status_id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Text(), nullable=True),
    sa.Column('description', sa.Text(), nullable=True),
    sa.PrimaryKeyConstraint('job_status_id')
    )
    op.create_table('job_type',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('job_type_id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Text(), nullable=True),
    sa.Column('description', sa.Text(), nullable=True),
    sa.PrimaryKeyConstraint('job_type_id')
    )
    op.create_table('permission_type',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('permission_type_id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Text(), nullable=True),
    sa.Column('description', sa.Text(), nullable=True),
    sa.PrimaryKeyConstraint('permission_type_id')
    )
    op.create_table('rule_severity',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('rule_severity_id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Text(), nullable=False),
    sa.Column('description', sa.Text(), nullable=False),
    sa.PrimaryKeyConstraint('rule_severity_id')
    )
    op.create_table('submission',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('submission_id', sa.Integer(), nullable=False),
    sa.Column('datetime_utc', sa.DateTime(), nullable=True),
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.Column('cgac_code', sa.Text(), nullable=True),
    sa.Column('reporting_start_date', sa.Date(), nullable=True),
    sa.Column('reporting_end_date', sa.Date(), nullable=True),
    sa.Column('is_quarter_format', sa.Boolean(), server_default='False', nullable=False),
    sa.PrimaryKeyConstraint('submission_id')
    )
    op.create_table('user_status',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('user_status_id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Text(), nullable=True),
    sa.Column('description', sa.Text(), nullable=True),
    sa.PrimaryKeyConstraint('user_status_id')
    )
    op.create_table('email_template',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('email_template_id', sa.Integer(), nullable=False),
    sa.Column('template_type_id', sa.Integer(), nullable=True),
    sa.Column('subject', sa.Text(), nullable=True),
    sa.Column('content', sa.Text(), nullable=True),
    sa.ForeignKeyConstraint(['template_type_id'], ['email_template_type.email_template_type_id'], ),
    sa.PrimaryKeyConstraint('email_template_id')
    )
    op.create_table('error_metadata',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('error_metadata_id', sa.Integer(), nullable=False),
    sa.Column('job_id', sa.Integer(), nullable=True),
    sa.Column('filename', sa.Text(), nullable=True),
    sa.Column('field_name', sa.Text(), nullable=True),
    sa.Column('error_type_id', sa.Integer(), nullable=True),
    sa.Column('occurrences', sa.Integer(), nullable=True),
    sa.Column('first_row', sa.Integer(), nullable=True),
    sa.Column('rule_failed', sa.Text(), nullable=True),
    sa.Column('file_type_id', sa.Integer(), nullable=True),
    sa.Column('target_file_type_id', sa.Integer(), nullable=True),
    sa.Column('original_rule_label', sa.Text(), nullable=True),
    sa.ForeignKeyConstraint(['error_type_id'], ['error_type.error_type_id'], ),
    sa.PrimaryKeyConstraint('error_metadata_id')
    )
    op.create_table('file',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('file_id', sa.Integer(), nullable=False),
    sa.Column('job_id', sa.Integer(), nullable=True),
    sa.Column('filename', sa.Text(), nullable=True),
    sa.Column('file_status_id', sa.Integer(), nullable=True),
    sa.Column('headers_missing', sa.Text(), nullable=True),
    sa.Column('headers_duplicated', sa.Text(), nullable=True),
    sa.Column('row_errors_present', sa.Boolean(), nullable=True),
    sa.ForeignKeyConstraint(['file_status_id'], ['file_status.file_status_id'], name='fk_file_status_id'),
    sa.PrimaryKeyConstraint('file_id'),
    sa.UniqueConstraint('job_id')
    )
    op.create_table('file_columns',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('file_column_id', sa.Integer(), nullable=False),
    sa.Column('file_id', sa.Integer(), nullable=True),
    sa.Column('field_types_id', sa.Integer(), nullable=True),
    sa.Column('name', sa.Text(), nullable=True),
    sa.Column('name_short', sa.Text(), nullable=True),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('required', sa.Boolean(), nullable=True),
    sa.Column('padded_flag', sa.Boolean(), server_default='False', nullable=False),
    sa.Column('length', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['field_types_id'], ['field_type.field_type_id'], ),
    sa.ForeignKeyConstraint(['file_id'], ['file_type_validation.file_id'], ),
    sa.PrimaryKeyConstraint('file_column_id')
    )
    op.create_table('job',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('job_id', sa.Integer(), nullable=False),
    sa.Column('filename', sa.Text(), nullable=True),
    sa.Column('job_status_id', sa.Integer(), nullable=True),
    sa.Column('job_type_id', sa.Integer(), nullable=True),
    sa.Column('submission_id', sa.Integer(), nullable=True),
    sa.Column('file_type_id', sa.Integer(), nullable=True),
    sa.Column('original_filename', sa.Text(), nullable=True),
    sa.Column('file_size', sa.Integer(), nullable=True),
    sa.Column('number_of_rows', sa.Integer(), nullable=True),
    sa.Column('number_of_rows_valid', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['file_type_id'], ['file_type.file_type_id'], ),
    sa.ForeignKeyConstraint(['job_status_id'], ['job_status.job_status_id'], name='fk_job_status_id'),
    sa.ForeignKeyConstraint(['job_type_id'], ['job_type.job_type_id'], name='fk_job_type_id'),
    sa.ForeignKeyConstraint(['submission_id'], ['submission.submission_id'], name='fk_job_submission_id', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('job_id')
    )
    op.create_table('rule_sql',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('rule_sql_id', sa.Integer(), nullable=False),
    sa.Column('rule_sql', sa.Text(), nullable=False),
    sa.Column('rule_label', sa.Text(), nullable=True),
    sa.Column('rule_description', sa.Text(), nullable=False),
    sa.Column('rule_error_message', sa.Text(), nullable=False),
    sa.Column('rule_cross_file_flag', sa.Boolean(), nullable=False),
    sa.Column('file_id', sa.Integer(), nullable=True),
    sa.Column('rule_severity_id', sa.Integer(), nullable=False),
    sa.Column('target_file_id', sa.Integer(), nullable=True),
    sa.Column('query_name', sa.Text(), nullable=True),
    sa.ForeignKeyConstraint(['file_id'], ['file_type_validation.file_id'], name='fk_file'),
    sa.ForeignKeyConstraint(['rule_severity_id'], ['rule_severity.rule_severity_id'], ),
    sa.ForeignKeyConstraint(['target_file_id'], ['file_type_validation.file_id'], name='fk_target_file'),
    sa.PrimaryKeyConstraint('rule_sql_id')
    )
    op.create_table('users',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.Column('username', sa.Text(), nullable=True),
    sa.Column('email', sa.Text(), nullable=True),
    sa.Column('name', sa.Text(), nullable=True),
    sa.Column('cgac_code', sa.Text(), nullable=True),
    sa.Column('title', sa.Text(), nullable=True),
    sa.Column('permissions', sa.Integer(), nullable=True),
    sa.Column('user_status_id', sa.Integer(), nullable=True),
    sa.Column('password_hash', sa.Text(), nullable=True),
    sa.Column('salt', sa.Text(), nullable=True),
    sa.Column('last_login_date', sa.DateTime(), nullable=True),
    sa.Column('is_active', sa.Boolean(), server_default='True', nullable=False),
    sa.Column('incorrect_password_attempts', sa.Integer(), server_default='0', nullable=False),
    sa.Column('skip_guide', sa.Boolean(), server_default='False', nullable=False),
    sa.ForeignKeyConstraint(['user_status_id'], ['user_status.user_status_id'], ),
    sa.PrimaryKeyConstraint('user_id')
    )
    op.create_table('job_dependency',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('dependency_id', sa.Integer(), nullable=False),
    sa.Column('job_id', sa.Integer(), nullable=True),
    sa.Column('prerequisite_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['job_id'], ['job.job_id'], ),
    sa.ForeignKeyConstraint(['prerequisite_id'], ['job.job_id'], ),
    sa.PrimaryKeyConstraint('dependency_id')
    )
    ### end Alembic commands ###


def downgrade_data_broker():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('job_dependency')
    op.drop_table('users')
    op.drop_table('rule_sql')
    op.drop_table('job')
    op.drop_table('file_columns')
    op.drop_table('file')
    op.drop_table('error_metadata')
    op.drop_table('email_template')
    op.drop_table('user_status')
    op.drop_table('submission')
    op.drop_table('rule_severity')
    op.drop_table('permission_type')
    op.drop_table('job_type')
    op.drop_table('job_status')
    op.drop_table('file_type_validation')
    op.drop_table('file_type')
    op.drop_table('file_status')
    op.drop_table('field_type')
    op.drop_table('error_type')
    op.drop_table('email_token')
    op.drop_table('email_template_type')
    ### end Alembic commands ###

