"""add_campaign_platform_groups

Revision ID: f698fe3b0b90
Revises: 85a91068148c
Create Date: 2022-05-11 08:23:51.451406

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'f698fe3b0b90'
down_revision = '85a91068148c'
branch_labels = None
depends_on = None

common_platforms = [
    "Google",
    "YouTube",
    "Facebook",
    "Twitter",
    "Instagram",
    "Snapchat",
    "Messenger",
    "Audience Network"
]

campaign_platform_groups = [
    {
        "id": 1,
        "name": "All Google Platforms",
        "platforms": [
            "Google",
            "YouTube",
        ]
    },
    {
        "id": 2,
        "name": "All Facebook Platforms",
        "platforms": [
            "Facebook",
            "Instagram",
            "Messenger",
            "Audience Network",
        ]
    }
]

def upgrade():
    op.create_table('CampaignPlatformGroups',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('name', sa.String(length=50), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_CampaignPlatformGroups'))
    )

    op.add_column('CampaignPlatforms', sa.Column('common', sa.Boolean(), server_default='false', nullable=False))
    op.add_column('CampaignPlatforms', sa.Column('platform_group_id', sa.Integer(), nullable=True))

    op.create_foreign_key(op.f('fk_CampaignPlatforms_platform_group_id_CampaignPlatformGroups'), 'CampaignPlatforms', 'CampaignPlatformGroups', ['platform_group_id'], ['id'])

    for platform in common_platforms:
        op.execute('''
        UPDATE "CampaignPlatforms" SET common = true WHERE name = '{platform}'
        '''.format(platform=platform))

    for group in campaign_platform_groups:
        op.execute('''INSERT INTO "CampaignPlatformGroups" (id, name) VALUES ('{id}', '{name}')'''.format(
            id=group["id"],
            name=group["name"]
        ))
        for platform in group["platforms"]:
            op.execute('''UPDATE "CampaignPlatforms" SET platform_group_id = '{id}' WHERE name = '{platform}'
            '''.format(id=group["id"], platform=platform))

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

    op.drop_constraint('fk_CampaignPlatforms_platform_group_id_CampaignPlatformGroups', 'CampaignPlatforms', type_='foreignkey')

    op.drop_column('CampaignPlatforms', 'platform_group_id')
    op.drop_column('CampaignPlatforms', 'common')