"""add_campaign_creative_link_table

Revision ID: ee9d964c5966
Revises: b46b611aebf1
Create Date: 2022-04-06 10:10:27.280068

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'ee9d964c5966'
down_revision = 'b46b611aebf1'
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()
    creative_links_table = op.create_table(
        'CampaignCreativeLink',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('campaign_id', sa.Integer(), nullable=False),
        sa.Column('url', sa.String(), nullable=False),
        sa.PrimaryKeyConstraint('id', name=op.f('pk_CreativeLink')),
        sa.ForeignKeyConstraint(
            ['campaign_id'],
            ['Campaign.id'],
            name=op.f('fk_CampaignCreativeLink_campaign_id_Campaign'),
            ondelete="CASCADE",
        ),
    )
    creative_links = (
        bind.execute(f'''SELECT id, ad_creative_link FROM "Campaign" where ad_creative_link is not null''')
            .fetchall()
    )

    creative_links_insert = []

    for campaign_id, url in creative_links:
        creative_links_insert.append({
            "campaign_id": campaign_id,
            "url": url,
        })

    op.bulk_insert(creative_links_table, creative_links_insert)


def downgrade():
    op.drop_table("CampaignCreativeLink")