"""add_objective_and_goal_fields_to_activity

Revision ID: 83e4b15ab747
Revises: 450a11f3d06a
Create Date: 2020-02-07 10:27:51.094447

"""
from alembic import op
import sqlalchemy as sa
from migrations import utils


# revision identifiers, used by Alembic.
revision = '83e4b15ab747'
down_revision = '450a11f3d06a'
branch_labels = None
depends_on = None

def migrate_objectives(current_objective, new_objective_name, new_goal_name):
    bind = op.get_bind()

    goal_id = bind.execute(''' SELECT id FROM "ActivityGoal" WHERE name = '{0}' '''.format(new_goal_name)).scalar()
    objective_id = bind.execute(''' SELECT id FROM "ActivityObjective" WHERE name='{0}' and goal_id={1} '''.format(new_objective_name, goal_id)).scalar()

    if current_objective is not None:
        result = bind.execute('''
            SELECT Activity.id FROM "Activity" as Activity
            JOIN "ActivityType" as ActivityType ON (ActivityType.activity_id = Activity.id)
            JOIN "ActivityTypeValue" as ActivityTypeValue ON (ActivityTypeValue.id = ActivityType.value_id)
            WHERE ActivityTypeValue.name = '{0}'
            '''.format(current_objective))

        ids = ', '.join([str(row[0]) for row in result])

        if len(ids) == 0:
            return

        bind.execute(''' UPDATE "Activity" SET goal_id={0}, objective_id={1} WHERE id IN ({2})'''.format(goal_id, objective_id, ids))
    else:
        bind.execute(''' UPDATE "Activity" SET goal_id={0}, objective_id={1} WHERE goal_id is NULL '''.format(goal_id, objective_id))


def upgrade():

    op.create_table('ActivityGoal',
    sa.Column('id', sa.Integer(), primary_key=True),
    sa.Column('name', sa.String(), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_ActivityGoal'))
    )

    op.create_table('ActivityObjective',
    sa.Column('id', sa.Integer(), primary_key=True),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('goal_id', sa.BigInteger(), nullable=False),
    sa.ForeignKeyConstraint(['goal_id'], ['ActivityGoal.id'], name=op.f('fk_ActivityObjective_goal_id_ActivityGoal'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_ActivityObjective'))
    )

    op.add_column('Activity', sa.Column('goal_id', sa.Integer(), nullable=True))
    op.add_column('Activity', sa.Column('objective_id', sa.Integer(), nullable=True))

    op.create_foreign_key(op.f('fk_Activity_goal_id_ActivityGoal'), 'Activity', 'ActivityGoal', ['goal_id'], ['id'])
    op.create_foreign_key(op.f('fk_Activity_objective_id_ActivityObjective'), 'Activity', 'ActivityObjective', ['objective_id'], ['id'])

    utils.import_data("ActivityGoal", "activity_goals.csv")
    utils.import_data("ActivityObjective", "activity_objectives.csv")

    bind = op.get_bind()

    migrate_objectives('Acquisition', 'Conversions', 'Acquisition')
    migrate_objectives('Brand awareness', 'Brand Awareness', 'Engagement')
    migrate_objectives('Direct response', 'Streams', 'Consumption')
    migrate_objectives('Re-targeting', 'Conversions', 'Acquisition')
    migrate_objectives(None, 'Brand Awareness', 'Engagement')

    bind.execute('''
        DELETE FROM "ActivityType" WHERE id IN (
            SELECT ActivityType.id FROM "ActivityType" as ActivityType
            JOIN "ActivityTypeCategory" as ActivityTypeCategory ON (ActivityTypeCategory.id = ActivityType.category_id)
            WHERE ActivityTypeCategory.name = 'Objective'
        )
        ''')
    bind.execute(''' DELETE FROM "ActivityTypeCategory" WHERE name='Objective' ''')

    op.alter_column('Activity', 'goal_id', nullable=False)
    op.alter_column('Activity', 'objective_id', nullable=False)


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(op.f('fk_Activity_objective_id_ActivityObjective'), 'Activity', type_='foreignkey')
    op.drop_constraint(op.f('fk_Activity_goal_id_ActivityGoal'), 'Activity', type_='foreignkey')
    op.drop_column('Activity', 'objective_id')
    op.drop_column('Activity', 'goal_id')
    op.drop_table('ActivityObjective')
    op.drop_table('ActivityGoal')
    # ### end Alembic commands ###