"""add_placements_platforms_table

Revision ID: e9cc7764b6ea
Revises: f44c9321eda2
Create Date: 2021-11-17 10:16:42.974922

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'e9cc7764b6ea'
down_revision = '72d99002afee'
branch_labels = None
depends_on = None

placements = [
    {"name": "Boost", "platforms": ["Facebook", "Instagram", "Reddit", "Snapchat", "TikTok", "Twitter"]},
    {"name": "In Feed", "platforms": ["Facebook", "Instagram", "Reddit", "Snapchat", "TikTok", "Twitter"]},
    {"name": "Stories", "platforms": ["Facebook", "Instagram", "Snapchat", "Twitter"]},
    {"name": "Discovery", "platforms": ["YouTube"]},
    {"name": "Skippable In-Stream", "platforms": ["YouTube"]},
    {"name": "Non-Skippable In-Stream", "platforms": ["YouTube"]},
    {"name": "YouTube Audio", "platforms": ["YouTube"]},
]

def upgrade():
    bind = op.get_bind()
    campaign_platform_placements_table = op.create_table(
        'CampaignPlatformPlacements',
        sa.Column('platform_id', sa.Integer(), nullable=False),
        sa.Column('placement_id', sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ['platform_id'],
            ['CampaignPlatforms.id'],
            name=op.f('fk_CampaignPlatformPlacements_platform_id_CampaignPlatforms'),
            ondelete="CASCADE",
        ),
        sa.ForeignKeyConstraint(
            ['placement_id'],
            ['CampaignPlacements.id'],
            name=op.f('fk_CampaignPlatformPlacements_placement_id_CampaignPlacements'),
            ondelete="CASCADE",
        ),
        sa.PrimaryKeyConstraint('platform_id', 'placement_id', name=op.f('pk_CampaignPlatformPlacements')),
    )
    op.drop_column('CampaignPlacements', 'is_custom')

    platforms = bind.execute('''SELECT id, name from "CampaignPlatforms"''')
    platforms_key_by_name = {name: id for id, name in platforms}

    for placement in placements:
        bind.execute(f'''INSERT INTO "CampaignPlacements" (name) VALUES ('{placement["name"]}') ''')
        placement_id = bind.execute(f'''
            SELECT id from "CampaignPlacements" where name = '{placement["name"]}'
        ''').fetchone()[0]

        campaign_platform_placements = []
        for platform in placement["platforms"]:
            campaign_platform_placements.append({
                "platform_id": platforms_key_by_name[platform],
                "placement_id": placement_id
            })
        op.bulk_insert(campaign_platform_placements_table, campaign_platform_placements)



def downgrade():
    op.drop_table('CampaignPlatformPlacements')