"""campaign_history_extention

Revision ID: fbd61549dfe8
Revises: 12c5b9ea245d
Create Date: 2020-04-03 11:35:02.525257

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql
from migrations import utils

# revision identifiers, used by Alembic.
revision = 'fbd61549dfe8'
down_revision = '5122d67ae08e'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###

    bind = op.get_bind()

    op.create_table('CampaignHistoryAction',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('type', sa.String(), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_CampaignHistoryAction'))
    )

    utils.import_data("CampaignHistoryAction", "campaign_history_actions.csv")

    op.create_table('CampaignHistoryItem',
    sa.Column('id', sa.BigInteger(), nullable=False),
    sa.Column('created_at', sa.TIMESTAMP(timezone=True), server_default=sa.text('CURRENT_TIMESTAMP'), nullable=False),
    sa.Column('campaign_id', sa.BigInteger(), nullable=False),
    sa.Column('activity_id', sa.BigInteger(), nullable=True),
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.Column('action_id', sa.Integer(), nullable=False),
    sa.Column('old_value', sa.JSON(), nullable=False, server_default='{}'),
    sa.Column('new_value', sa.JSON(), nullable=False, server_default='{}'),
    sa.ForeignKeyConstraint(['action_id'], ['CampaignHistoryAction.id'], name=op.f('fk_CampaignHistoryItem_action_id_CampaignHistoryAction')),
    sa.ForeignKeyConstraint(['activity_id'], ['Activity.id'], name=op.f('fk_CampaignHistoryItem_activity_id_Activity')),
    sa.ForeignKeyConstraint(['campaign_id'], ['Campaign.id'], name=op.f('fk_CampaignHistoryItem_campaign_id_Campaign')),
    sa.ForeignKeyConstraint(['user_id'], ['User.id'], name=op.f('fk_CampaignHistoryItem_user_id_User')),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_CampaignHistoryItem'))
    )


    bind.execute('''
    INSERT INTO "CampaignHistoryItem" (created_at, campaign_id, activity_id, user_id, action_id)
    SELECT created_at, campaign_id, activity_id, user_id, 1
    FROM "CampaignHistory" as old 
    WHERE old.history_action_id=1 AND old.activity_id IS NOT NULL;
    
    INSERT INTO "CampaignHistoryItem" (created_at, campaign_id, activity_id, user_id, action_id)
    SELECT created_at, campaign_id, activity_id, user_id, 14
    FROM "CampaignHistory" as old 
    WHERE old.history_action_id=1 AND old.activity_id IS NULL;
    
    INSERT INTO "CampaignHistoryItem" (created_at, campaign_id, activity_id, user_id, action_id)
    SELECT created_at, campaign_id, activity_id, user_id, 2
    FROM "CampaignHistory" as old 
    WHERE old.history_action_id=3 AND old.activity_id IS NOT NULL;
    
    UPDATE "CampaignHistoryItem" as history_item
    SET new_value=json_build_object('value', campaign.name)
    FROM "Campaign" as campaign 
    WHERE campaign.id=history_item.campaign_id AND history_item.action_id=14;
    
    UPDATE "CampaignHistoryItem" as history_item
    SET new_value=json_build_object('value', activity.budget_spend)
    FROM "Activity" as activity 
    WHERE activity.id=history_item.activity_id AND history_item.action_id=1;
    
    UPDATE "CampaignHistoryItem" as history_item
    SET old_value=json_build_object('value', activity.budget_spend)
    FROM "Activity" as activity 
    WHERE activity.id=history_item.activity_id AND history_item.action_id=2;
    ''')



    op.drop_table('CampaignHistory')
    op.drop_table('HistoryAction')
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('HistoryAction',
    sa.Column('id', sa.INTEGER(), server_default=sa.text('nextval(\'"HistoryAction_id_seq"\'::regclass)'), autoincrement=True, nullable=False),
    sa.Column('name', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.PrimaryKeyConstraint('id', name='pk_HistoryAction'),
    postgresql_ignore_search_path=False
    )
    op.create_table('CampaignHistory',
    sa.Column('id', sa.BIGINT(), server_default=sa.text('nextval(\'"CampaignHistory_id_seq"\'::regclass)'), autoincrement=True, nullable=False),
    sa.Column('edit_date', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('CURRENT_TIMESTAMP'), autoincrement=False, nullable=False),
    sa.Column('endpoint', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('parameters', postgresql.JSON(astext_type=sa.Text()), autoincrement=False, nullable=False),
    sa.Column('campaign_id', sa.BIGINT(), autoincrement=False, nullable=False),
    sa.Column('history_action_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('history_user_id', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('activity_id', sa.BIGINT(), autoincrement=False, nullable=True),
    sa.Column('user_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.ForeignKeyConstraint(['activity_id'], ['Activity.id'], name='fk_CampaignHistory_activity_id_Activity'),
    sa.ForeignKeyConstraint(['campaign_id'], ['Campaign.id'], name='fk_CampaignHistory_campaign_id_Campaign'),
    sa.ForeignKeyConstraint(['history_action_id'], ['HistoryAction.id'], name='fk_CampaignHistory_history_action_id_HistoryAction'),
    sa.ForeignKeyConstraint(['history_user_id'], ['HistoryUser.id'], name='fk_CampaignHistory_history_user_id_HistoryUser'),
    sa.ForeignKeyConstraint(['user_id'], ['User.id'], name='fk_CampaignHistory_user_id_User'),
    sa.PrimaryKeyConstraint('id', name='pk_CampaignHistory')
    )
    op.drop_table('CampaignHistoryItem')
    op.drop_table('CampaignHistoryAction')
    # ### end Alembic commands ###