""" Adding TAS, Display TAS to tas_lookup

Revision ID: bbc020fa7961
Revises: 77bd1ef8c53b
Create Date: 2021-06-11 17:42:12.252432

"""

# revision identifiers, used by Alembic.
revision = 'bbc020fa7961'
down_revision = '77bd1ef8c53b'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

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('tas_lookup', sa.Column('display_tas', sa.Text(), nullable=True))
    op.add_column('tas_lookup', sa.Column('tas', sa.Text()))
    op.create_index(op.f('ix_tas_lookup_tas'), 'tas_lookup', ['tas'], unique=False)
    # populate the existing TAS/Display TASs
    tas_backfill = """
        UPDATE tas_lookup
        SET tas =
            UPPER(CONCAT(
                COALESCE(allocation_transfer_agency, '000'),
                COALESCE(agency_identifier, '000'),
                COALESCE(beginning_period_of_availa, '0000'),
                COALESCE(ending_period_of_availabil, '0000'),
                COALESCE(availability_type_code, ' '),
                COALESCE(main_account_code, '0000'),
                COALESCE(sub_account_code, '000')
            ))
        WHERE tas IS NULL;
    """
    op.execute(tas_backfill)
    display_tas_backfill = """
        UPDATE tas_lookup
        SET display_tas =
            UPPER(CONCAT_WS(
                '-',
                allocation_transfer_agency,
                agency_identifier,
                CASE WHEN availability_type_code IS NOT NULL AND availability_type_code <> '' THEN availability_type_code
                    ELSE CONCAT_WS('/', beginning_period_of_availa, ending_period_of_availabil)
                    END,
                main_account_code,
                sub_account_code
            ))
        WHERE display_tas IS NULL;
    """
    op.execute(display_tas_backfill)

    # Add nullable restraint in
    op.alter_column('tas_lookup', 'tas', nullable=False)

    # ### end Alembic commands ###


def downgrade_data_broker():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_tas_lookup_tas'), table_name='tas_lookup')
    op.drop_column('tas_lookup', 'tas')
    op.drop_column('tas_lookup', 'display_tas')
    # ### end Alembic commands ###

