"""create tables for certified DABS (A, B, C) data

Revision ID: b43b300d718b
Revises: b4229d85c9bb
Create Date: 2018-12-06 21:25:37.028008

"""

# revision identifiers, used by Alembic.
revision = 'b43b300d718b'
down_revision = 'b4229d85c9bb'
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('certified_appropriation',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('certified_appropriation_id', sa.Integer(), nullable=False),
    sa.Column('submission_id', sa.Integer(), nullable=False),
    sa.Column('job_id', sa.Integer(), nullable=True),
    sa.Column('row_number', sa.Integer(), nullable=True),
    sa.Column('adjustments_to_unobligated_cpe', sa.Numeric(), nullable=True),
    sa.Column('agency_identifier', sa.Text(), nullable=True),
    sa.Column('allocation_transfer_agency', sa.Text(), nullable=True),
    sa.Column('availability_type_code', sa.Text(), nullable=True),
    sa.Column('beginning_period_of_availa', sa.Text(), nullable=True),
    sa.Column('borrowing_authority_amount_cpe', sa.Numeric(), nullable=True),
    sa.Column('budget_authority_appropria_cpe', sa.Numeric(), nullable=True),
    sa.Column('total_budgetary_resources_cpe', sa.Numeric(), nullable=True),
    sa.Column('budget_authority_unobligat_fyb', sa.Numeric(), nullable=True),
    sa.Column('contract_authority_amount_cpe', sa.Numeric(), nullable=True),
    sa.Column('deobligations_recoveries_r_cpe', sa.Numeric(), nullable=True),
    sa.Column('ending_period_of_availabil', sa.Text(), nullable=True),
    sa.Column('gross_outlay_amount_by_tas_cpe', sa.Numeric(), nullable=True),
    sa.Column('main_account_code', sa.Text(), nullable=True),
    sa.Column('obligations_incurred_total_cpe', sa.Numeric(), nullable=True),
    sa.Column('other_budgetary_resources_cpe', sa.Numeric(), nullable=True),
    sa.Column('spending_authority_from_of_cpe', sa.Numeric(), nullable=True),
    sa.Column('status_of_budgetary_resour_cpe', sa.Numeric(), nullable=True),
    sa.Column('sub_account_code', sa.Text(), nullable=True),
    sa.Column('unobligated_balance_cpe', sa.Numeric(), nullable=True),
    sa.Column('tas', sa.Text(), nullable=True),
    sa.Column('tas_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['submission_id'], ['submission.submission_id'], name='fk_certified_appropriation_submission_id', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('certified_appropriation_id')
    )
    op.create_index(op.f('ix_certified_appropriation_submission_id'), 'certified_appropriation', ['submission_id'], unique=False)
    op.create_table('certified_award_financial',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('certified_award_financial_id', sa.Integer(), nullable=False),
    sa.Column('submission_id', sa.Integer(), nullable=False),
    sa.Column('job_id', sa.Integer(), nullable=True),
    sa.Column('row_number', sa.Integer(), nullable=True),
    sa.Column('agency_identifier', sa.Text(), nullable=True),
    sa.Column('allocation_transfer_agency', sa.Text(), nullable=True),
    sa.Column('availability_type_code', sa.Text(), nullable=True),
    sa.Column('beginning_period_of_availa', sa.Text(), nullable=True),
    sa.Column('by_direct_reimbursable_fun', sa.Text(), nullable=True),
    sa.Column('deobligations_recov_by_awa_cpe', sa.Numeric(), nullable=True),
    sa.Column('ending_period_of_availabil', sa.Text(), nullable=True),
    sa.Column('fain', sa.Text(), nullable=True),
    sa.Column('gross_outlay_amount_by_awa_cpe', sa.Numeric(), nullable=True),
    sa.Column('gross_outlay_amount_by_awa_fyb', sa.Numeric(), nullable=True),
    sa.Column('gross_outlays_delivered_or_cpe', sa.Numeric(), nullable=True),
    sa.Column('gross_outlays_delivered_or_fyb', sa.Numeric(), nullable=True),
    sa.Column('gross_outlays_undelivered_cpe', sa.Numeric(), nullable=True),
    sa.Column('gross_outlays_undelivered_fyb', sa.Numeric(), nullable=True),
    sa.Column('main_account_code', sa.Text(), nullable=True),
    sa.Column('object_class', sa.Text(), nullable=True),
    sa.Column('obligations_delivered_orde_cpe', sa.Numeric(), nullable=True),
    sa.Column('obligations_delivered_orde_fyb', sa.Numeric(), nullable=True),
    sa.Column('obligations_incurred_byawa_cpe', sa.Numeric(), nullable=True),
    sa.Column('obligations_undelivered_or_cpe', sa.Numeric(), nullable=True),
    sa.Column('obligations_undelivered_or_fyb', sa.Numeric(), nullable=True),
    sa.Column('parent_award_id', sa.Text(), nullable=True),
    sa.Column('piid', sa.Text(), nullable=True),
    sa.Column('program_activity_code', sa.Text(), nullable=True),
    sa.Column('program_activity_name', sa.Text(), nullable=True),
    sa.Column('sub_account_code', sa.Text(), nullable=True),
    sa.Column('transaction_obligated_amou', sa.Numeric(), nullable=True),
    sa.Column('uri', sa.Text(), nullable=True),
    sa.Column('ussgl480100_undelivered_or_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl480100_undelivered_or_fyb', sa.Numeric(), nullable=True),
    sa.Column('ussgl480200_undelivered_or_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl480200_undelivered_or_fyb', sa.Numeric(), nullable=True),
    sa.Column('ussgl483100_undelivered_or_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl483200_undelivered_or_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl487100_downward_adjus_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl487200_downward_adjus_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl488100_upward_adjustm_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl488200_upward_adjustm_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl490100_delivered_orde_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl490100_delivered_orde_fyb', sa.Numeric(), nullable=True),
    sa.Column('ussgl490200_delivered_orde_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl490800_authority_outl_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl490800_authority_outl_fyb', sa.Numeric(), nullable=True),
    sa.Column('ussgl493100_delivered_orde_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl497100_downward_adjus_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl497200_downward_adjus_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl498100_upward_adjustm_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl498200_upward_adjustm_cpe', sa.Numeric(), nullable=True),
    sa.Column('tas', sa.Text(), nullable=True),
    sa.Column('tas_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['submission_id'], ['submission.submission_id'], name='fk_certified_award_financial_submission_id', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('certified_award_financial_id')
    )
    op.create_index(op.f('ix_certified_award_financial_submission_id'), 'certified_award_financial', ['submission_id'], unique=False)
    op.create_table('certified_object_class_program_activity',
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('certified_object_class_program_activity_id', sa.Integer(), nullable=False),
    sa.Column('submission_id', sa.Integer(), nullable=False),
    sa.Column('job_id', sa.Integer(), nullable=True),
    sa.Column('row_number', sa.Integer(), nullable=True),
    sa.Column('agency_identifier', sa.Text(), nullable=True),
    sa.Column('allocation_transfer_agency', sa.Text(), nullable=True),
    sa.Column('availability_type_code', sa.Text(), nullable=True),
    sa.Column('beginning_period_of_availa', sa.Text(), nullable=True),
    sa.Column('by_direct_reimbursable_fun', sa.Text(), nullable=True),
    sa.Column('deobligations_recov_by_pro_cpe', sa.Numeric(), nullable=True),
    sa.Column('ending_period_of_availabil', sa.Text(), nullable=True),
    sa.Column('gross_outlay_amount_by_pro_cpe', sa.Numeric(), nullable=True),
    sa.Column('gross_outlay_amount_by_pro_fyb', sa.Numeric(), nullable=True),
    sa.Column('gross_outlays_delivered_or_cpe', sa.Numeric(), nullable=True),
    sa.Column('gross_outlays_delivered_or_fyb', sa.Numeric(), nullable=True),
    sa.Column('gross_outlays_undelivered_cpe', sa.Numeric(), nullable=True),
    sa.Column('gross_outlays_undelivered_fyb', sa.Numeric(), nullable=True),
    sa.Column('main_account_code', sa.Text(), nullable=True),
    sa.Column('object_class', sa.Text(), nullable=True),
    sa.Column('obligations_delivered_orde_cpe', sa.Numeric(), nullable=True),
    sa.Column('obligations_delivered_orde_fyb', sa.Numeric(), nullable=True),
    sa.Column('obligations_incurred_by_pr_cpe', sa.Numeric(), nullable=True),
    sa.Column('obligations_undelivered_or_cpe', sa.Numeric(), nullable=True),
    sa.Column('obligations_undelivered_or_fyb', sa.Numeric(), nullable=True),
    sa.Column('program_activity_code', sa.Text(), nullable=True),
    sa.Column('program_activity_name', sa.Text(), nullable=True),
    sa.Column('sub_account_code', sa.Text(), nullable=True),
    sa.Column('ussgl480100_undelivered_or_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl480100_undelivered_or_fyb', sa.Numeric(), nullable=True),
    sa.Column('ussgl480200_undelivered_or_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl480200_undelivered_or_fyb', sa.Numeric(), nullable=True),
    sa.Column('ussgl483100_undelivered_or_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl483200_undelivered_or_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl487100_downward_adjus_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl487200_downward_adjus_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl488100_upward_adjustm_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl488200_upward_adjustm_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl490100_delivered_orde_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl490100_delivered_orde_fyb', sa.Numeric(), nullable=True),
    sa.Column('ussgl490200_delivered_orde_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl490800_authority_outl_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl490800_authority_outl_fyb', sa.Numeric(), nullable=True),
    sa.Column('ussgl493100_delivered_orde_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl497100_downward_adjus_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl497200_downward_adjus_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl498100_upward_adjustm_cpe', sa.Numeric(), nullable=True),
    sa.Column('ussgl498200_upward_adjustm_cpe', sa.Numeric(), nullable=True),
    sa.Column('tas', sa.Text(), nullable=True),
    sa.Column('tas_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['submission_id'], ['submission.submission_id'], name='fk_certified_object_class_program_activity_submission_id', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('certified_object_class_program_activity_id')
    )
    op.create_index(op.f('ix_certified_object_class_program_activity_submission_id'), 'certified_object_class_program_activity', ['submission_id'], unique=False)
    # ### end Alembic commands ###


def downgrade_data_broker():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_certified_object_class_program_activity_submission_id'), table_name='certified_object_class_program_activity')
    op.drop_table('certified_object_class_program_activity')
    op.drop_index(op.f('ix_certified_award_financial_submission_id'), table_name='certified_award_financial')
    op.drop_table('certified_award_financial')
    op.drop_index(op.f('ix_certified_appropriation_submission_id'), table_name='certified_appropriation')
    op.drop_table('certified_appropriation')
    # ### end Alembic commands ###

