"""Add tas_id field to staging models

Revision ID: 6f79cf30f2d7
Revises: 807a203713a4
Create Date: 2016-11-11 17:20:25.108568

"""

# revision identifiers, used by Alembic.
revision = '6f79cf30f2d7'
down_revision = '807a203713a4'
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]()


upgrade_sql = """
    UPDATE {table_name}
    SET tas_id = (
        SELECT min(tas.tas_id)
        FROM submission AS sub
        LEFT JOIN tas_lookup AS tas ON (
            {table_name}.allocation_transfer_agency
                IS NOT DISTINCT FROM tas.allocation_transfer_agency
            AND {table_name}.agency_identifier
                IS NOT DISTINCT FROM tas.agency_identifier
            AND {table_name}.beginning_period_of_availa
                IS NOT DISTINCT FROM tas.beginning_period_of_availability
            AND {table_name}.ending_period_of_availabil
                IS NOT DISTINCT FROM tas.ending_period_of_availability
            AND {table_name}.availability_type_code
                IS NOT DISTINCT FROM tas.availability_type_code
            AND {table_name}.main_account_code
                IS NOT DISTINCT FROM tas.main_account_code
            AND {table_name}.sub_account_code
                IS NOT DISTINCT FROM tas.sub_account_code
            AND (sub.reporting_start_date, sub.reporting_end_date) OVERLAPS
                -- A null end date indicates "still open". To make OVERLAPS
                -- work, we'll use the day after the end date of the submission
                -- to achieve the same result
                (tas.internal_start_date,
                 COALESCE(tas.internal_end_date,
                          sub.reporting_end_date + interval '1 day')
                )
        )
        WHERE sub.submission_id = {table_name}.submission_id
    )
"""


def upgrade_data_broker():
    ### commands auto generated by Alembic - please adjust! ###
    op.add_column('appropriation', sa.Column('tas_id', sa.Integer(), nullable=True))
    op.create_foreign_key('fk_tas', 'appropriation', 'tas_lookup', ['tas_id'], ['tas_id'])
    op.add_column('award_financial', sa.Column('tas_id', sa.Integer(), nullable=True))
    op.create_foreign_key('fk_tas', 'award_financial', 'tas_lookup', ['tas_id'], ['tas_id'])
    op.add_column('object_class_program_activity', sa.Column('tas_id', sa.Integer(), nullable=True))
    op.create_foreign_key('fk_tas', 'object_class_program_activity', 'tas_lookup', ['tas_id'], ['tas_id'])
    ### end Alembic commands ###
    for table_name in ('appropriation', 'award_financial',
                       'object_class_program_activity'):
        op.execute(upgrade_sql.format(table_name=table_name))


def downgrade_data_broker():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint('fk_tas', 'object_class_program_activity', type_='foreignkey')
    op.drop_column('object_class_program_activity', 'tas_id')
    op.drop_constraint('fk_tas', 'award_financial', type_='foreignkey')
    op.drop_column('award_financial', 'tas_id')
    op.drop_constraint('fk_tas', 'appropriation', type_='foreignkey')
    op.drop_column('appropriation', 'tas_id')
    ### end Alembic commands ###

