"""update PublishedAwardFinancialAssistance table to include inactive rows

Revision ID: ada3c1420257
Revises: 0ca75433c435
Create Date: 2017-08-03 13:26:42.099322

"""

# revision identifiers, used by Alembic.
revision = 'ada3c1420257'
down_revision = '0ca75433c435'
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.add_column('detached_award_financial_assistance', sa.Column('afa_generated_unique', sa.Text(), nullable=True))
    op.add_column('published_award_financial_assistance', sa.Column('modified_at', sa.DateTime(), nullable=True))
    op.create_index(op.f('ix_detached_award_financial_assistance_afa_generated_unique'), 'detached_award_financial_assistance', ['afa_generated_unique'], unique=False)
    op.drop_index('ix_detached_award_financial_assistance_fain', table_name='detached_award_financial_assistance')
    op.drop_index('ix_detached_award_financial_assistance_uri', table_name='detached_award_financial_assistance')
    op.add_column('published_award_financial_assistance', sa.Column('afa_generated_unique', sa.Text(), nullable=True))
    op.add_column('published_award_financial_assistance', sa.Column('is_active', sa.Boolean(), nullable=False, server_default=sa.text('false')))
    op.create_index(op.f('ix_published_award_financial_assistance_afa_generated_unique'), 'published_award_financial_assistance', ['afa_generated_unique'], unique=False)
    op.create_index(op.f('ix_published_award_financial_assistance_correction_late_delete_ind'), 'published_award_financial_assistance', ['correction_late_delete_ind'], unique=False)
    op.drop_index('ix_published_award_financial_assistance_award_modificat_2eb3', table_name='published_award_financial_assistance')
    op.drop_index('ix_published_award_financial_assistance_awarding_sub_ti_f74b', table_name='published_award_financial_assistance')
    op.drop_index('ix_published_award_financial_assistance_fain', table_name='published_award_financial_assistance')
    op.drop_index('ix_published_award_financial_assistance_uri', table_name='published_award_financial_assistance')
    op.drop_constraint('uniq_award_mod_sub_tier_fain_uri', 'published_award_financial_assistance', type_='unique')
    op.execute("""
                UPDATE published_award_financial_assistance
                SET afa_generated_unique = CONCAT(COALESCE(award_modification_amendme, '-none-'), COALESCE(awarding_sub_tier_agency_c, '-none-'), COALESCE(fain, '-none-'), COALESCE(uri, '-none-'))
            """)
    op.execute("""
                UPDATE detached_award_financial_assistance
                SET afa_generated_unique = CONCAT(COALESCE(award_modification_amendme, '-none-'), COALESCE(awarding_sub_tier_agency_c, '-none-'), COALESCE(fain, '-none-'), COALESCE(uri, '-none-'))
            """)
    op.alter_column('published_award_financial_assistance', 'afa_generated_unique',
               existing_type=sa.Text(),
               nullable=False)
    op.alter_column('detached_award_financial_assistance', 'afa_generated_unique',
               existing_type=sa.Text(),
               nullable=False)
    ### end Alembic commands ###


def downgrade_data_broker():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_unique_constraint('uniq_award_mod_sub_tier_fain_uri', 'published_award_financial_assistance', ['awarding_sub_tier_agency_c', 'award_modification_amendme', 'fain', 'uri'])
    op.create_index('ix_published_award_financial_assistance_uri', 'published_award_financial_assistance', ['uri'], unique=False)
    op.create_index('ix_published_award_financial_assistance_fain', 'published_award_financial_assistance', ['fain'], unique=False)
    op.create_index('ix_published_award_financial_assistance_awarding_sub_ti_f74b', 'published_award_financial_assistance', ['awarding_sub_tier_agency_c'], unique=False)
    op.create_index('ix_published_award_financial_assistance_award_modificat_2eb3', 'published_award_financial_assistance', ['award_modification_amendme'], unique=False)
    op.drop_index(op.f('ix_published_award_financial_assistance_correction_late_delete_ind'), table_name='published_award_financial_assistance')
    op.drop_index(op.f('ix_published_award_financial_assistance_afa_generated_unique'), table_name='published_award_financial_assistance')
    op.drop_column('published_award_financial_assistance', 'is_active')
    op.drop_column('published_award_financial_assistance', 'afa_generated_unique')
    op.create_index('ix_detached_award_financial_assistance_uri', 'detached_award_financial_assistance', ['uri'], unique=False)
    op.create_index('ix_detached_award_financial_assistance_fain', 'detached_award_financial_assistance', ['fain'], unique=False)
    op.drop_index(op.f('ix_detached_award_financial_assistance_afa_generated_unique'), table_name='detached_award_financial_assistance')
    op.drop_column('published_award_financial_assistance', 'modified_at')
    op.drop_column('detached_award_financial_assistance', 'afa_generated_unique')
    ### end Alembic commands ###

