"""DAIMS 2.1 D2 Updates

Revision ID: 1ddbc8a39215
Revises: b135db94bb15
Create Date: 2021-08-13 12:29:21.955658

"""

# revision identifiers, used by Alembic.
revision = '1ddbc8a39215'
down_revision = 'b135db94bb15'
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! ###
    # Staging table
    op.add_column('award_financial_assistance', sa.Column('awardee_or_recipient_uei', sa.Text(), nullable=True))
    op.add_column('award_financial_assistance', sa.Column('funding_opportunity_goals', sa.Text(), nullable=True))
    op.add_column('award_financial_assistance', sa.Column('funding_opportunity_number', sa.Text(), nullable=True))
    op.add_column('award_financial_assistance', sa.Column('indirect_federal_sharing', sa.Numeric(), nullable=True))
    op.add_column('award_financial_assistance', sa.Column('ultimate_parent_uei', sa.Text(), nullable=True))
    op.create_index(op.f('ix_award_financial_assistance_awardee_or_recipient_uei'), 'award_financial_assistance', ['awardee_or_recipient_uei'], unique=False)

    # Certified table
    op.add_column('certified_award_financial_assistance', sa.Column('awardee_or_recipient_uei', sa.Text(), nullable=True))
    op.add_column('certified_award_financial_assistance', sa.Column('funding_opportunity_goals', sa.Text(), nullable=True))
    op.add_column('certified_award_financial_assistance', sa.Column('funding_opportunity_number', sa.Text(), nullable=True))
    op.add_column('certified_award_financial_assistance', sa.Column('indirect_federal_sharing', sa.Numeric(), nullable=True))
    op.add_column('certified_award_financial_assistance', sa.Column('ultimate_parent_uei', sa.Text(), nullable=True))

    # Renaming DUNS columns
    op.execute("ALTER TABLE award_financial_assistance RENAME COLUMN awardee_or_recipient_uniqu TO awardee_or_recipient_duns")
    op.execute("ALTER TABLE award_financial_assistance RENAME COLUMN ultimate_parent_unique_ide TO ultimate_parent_duns")
    op.execute("ALTER INDEX ix_award_financial_assistance_awardee_or_recipient_uniqu RENAME TO ix_award_financial_assistance_awardee_or_recipient_duns")

    op.execute("ALTER TABLE certified_award_financial_assistance RENAME COLUMN awardee_or_recipient_uniqu TO awardee_or_recipient_duns")
    op.execute( "ALTER TABLE certified_award_financial_assistance RENAME COLUMN ultimate_parent_unique_ide TO ultimate_parent_duns")
    # ### end Alembic commands ###


def downgrade_data_broker():
    # ### commands auto generated by Alembic - please adjust! ###
    # Staging table
    op.drop_index(op.f('ix_award_financial_assistance_awardee_or_recipient_uei'), table_name='award_financial_assistance')
    op.drop_column('award_financial_assistance', 'ultimate_parent_uei')
    op.drop_column('award_financial_assistance', 'indirect_federal_sharing')
    op.drop_column('award_financial_assistance', 'funding_opportunity_number')
    op.drop_column('award_financial_assistance', 'funding_opportunity_goals')
    op.drop_column('award_financial_assistance', 'awardee_or_recipient_uei')

    # Certified table
    op.drop_column('certified_award_financial_assistance', 'ultimate_parent_uei')
    op.drop_column('certified_award_financial_assistance', 'indirect_federal_sharing')
    op.drop_column('certified_award_financial_assistance', 'funding_opportunity_number')
    op.drop_column('certified_award_financial_assistance', 'funding_opportunity_goals')
    op.drop_column('certified_award_financial_assistance', 'awardee_or_recipient_uei')

    # Renaming DUNS columns
    op.execute("ALTER TABLE award_financial_assistance RENAME COLUMN awardee_or_recipient_duns TO awardee_or_recipient_uniqu")
    op.execute("ALTER TABLE award_financial_assistance RENAME COLUMN ultimate_parent_duns TO ultimate_parent_unique_ide")
    op.execute("ALTER INDEX ix_award_financial_assistance_awardee_or_recipient_duns RENAME TO ix_award_financial_assistance_awardee_or_recipient_uniqu")

    op.execute("ALTER TABLE certified_award_financial_assistance RENAME COLUMN awardee_or_recipient_duns TO awardee_or_recipient_uniqu")
    op.execute("ALTER TABLE certified_award_financial_assistance RENAME COLUMN ultimate_parent_duns TO ultimate_parent_unique_ide")
    # ### end Alembic commands ###

