"""add_new_campaign_fields

Revision ID: 6064e85260dd
Revises: ddacf0175c71
Create Date: 2020-08-11 08:51:33.071974

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

# revision identifiers, used by Alembic.
revision = '6064e85260dd'
down_revision = '35ac159d7b02'
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()

    op.create_table('Gender',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(), nullable=False),
        sa.PrimaryKeyConstraint('id', name=op.f('pk_Gender'))
    )
    utils.import_data("Gender", "campaign_genders.csv")

    op.create_table('CampaignGender',
    sa.Column('campaign_id', sa.Integer(), nullable=False),
    sa.Column('gender_id', sa.Integer(), nullable=False)
    )
    op.create_foreign_key(op.f('fk_CampaignGender_campaign_id_Campaign'), 'CampaignGender', 'Campaign',
                          ['campaign_id'], ['id'])
    op.create_foreign_key(op.f('fk_CampaignGender_gender_id_Gender'), 'CampaignGender', 'Gender', ['gender_id'], ['id'])

    op.create_table('CampaignProvider',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_CampaignProvider'))
    )
    utils.import_data("CampaignProvider", "campaign_providers.csv")

    op.add_column('Campaign', sa.Column('age_range', postgresql.INT4RANGE(), nullable=False, server_default='[0, 60)'))
    op.add_column('Campaign', sa.Column('gender_id', sa.Integer(), nullable=False, server_default='1'))
    op.add_column('Campaign', sa.Column('provider_id', sa.Integer(), nullable=False, server_default='1'))
    bind.execute('''ALTER TABLE "Campaign" RENAME COLUMN "budget_spend" TO "planned_budget"''')
    op.add_column('Campaign', sa.Column('budget_spend', sa.NUMERIC(precision=12, scale=2), nullable=True))

    op.drop_constraint('fk_Campaign_goal_id_CampaignGoal', 'Campaign', type_='foreignkey')
    op.create_foreign_key(op.f('fk_Campaign_provider_id_CampaignProvider'), 'Campaign', 'CampaignProvider', ['provider_id'], ['id'])
    op.drop_column('Campaign', 'goal_id')

    bind.execute('''UPDATE "Territory" SET name='Global' WHERE name='Worldwide' ''')


def downgrade():
    bind = op.get_bind()

    op.add_column('Campaign', sa.Column('goal_id', sa.INTEGER(), autoincrement=False, nullable=False, server_default="1"))
    op.drop_constraint(op.f('fk_Campaign_provider_id_CampaignProvider'), 'Campaign', type_='foreignkey')
    op.create_foreign_key('fk_Campaign_goal_id_CampaignGoal', 'Campaign', 'CampaignGoal', ['goal_id'], ['id'])

    op.drop_column('Campaign', 'provider_id')
    op.drop_column('Campaign', 'budget_spend')
    bind.execute('''ALTER TABLE "Campaign" RENAME COLUMN "planned_budget" TO "budget_spend"''')
    op.drop_column('Campaign', 'gender_id')
    op.drop_column('Campaign', 'age_range')
    op.drop_table('Gender')
    op.drop_table('CampaignProvider')
    op.drop_table('CampaignGender')

    bind.execute('''UPDATE "Territory" SET name='Worldwide' WHERE name='Global' ''')