"""add_new_campaign_placements

Revision ID: 00790fffcaad
Revises: 03e32671c45b
Create Date: 2022-09-21 11:00:28.516644

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql
from migrations.utils import get_table
from sqlalchemy import select, and_, or_

# revision identifiers, used by Alembic.
revision = '00790fffcaad'
down_revision = '03e32671c45b'
branch_labels = None
depends_on = None

placements = [
    {"name": "Business Explore", "platforms": ["Facebook", "Instagram"]},
    {"name": "In-Stream Video", "platforms": ["Facebook", "Audience Network"]},
    {"name": "Marketplace", "platforms": ["Facebook"]},
    {"name": "Reels Overlay", "platforms": ["Facebook"]},
    {"name": "Reels", "platforms": ["Facebook"]},
    {"name": "Right Column", "platforms": ["Facebook"]},
    {"name": "Search Results", "platforms": ["Facebook"]},
    {"name": "Video Feeds", "platforms": ["Facebook"]},
    {"name": "Explore", "platforms": ["Instagram"]},
    {"name": "Instant Articles", "platforms": ["Instagram"]},
    {"name": "Shop", "platforms": ["Instagram"]},
    {"name": "Banner", "platforms": ["Audience Network"]},
    {"name": "Interstitial", "platforms": ["Audience Network"]},
    {"name": "Native", "platforms": ["Audience Network"]},
    {"name": "Rewarded Video", "platforms": ["Audience Network"]},
    {"name": "Inbox", "platforms": ["Messenger"]},
    {"name": "Sponsored Messages", "platforms": ["Messenger"]},
    {"name": "Stories", "platforms": ["Messenger"]},
]


def upgrade():
    bind = op.get_bind()
    campaign_placements = get_table(bind, "CampaignPlacements")
    campaign_platform = get_table(bind, "CampaignPlatforms")
    campaign_platform_placements = get_table(bind, "CampaignPlatformPlacements")
    campaign_placements_links = get_table(bind, "CampaignPlacementsLinks")

    #   add new placement
    op.execute(
        campaign_placements.insert().values(name="Feed")
    )

    in_feed_placement_id = bind.execute(
            select([campaign_placements.c.id]).where(
                campaign_placements.c.name=="In Feed"
            )
        ).fetchone()[0]

    feed_placement_id = bind.execute(
            select([campaign_placements.c.id]).where(
                campaign_placements.c.name=="Feed"
            )
        ).fetchone()[0]

    #   update placement link to Feed in case where platform only meta
    bind.execute('''
        UPDATE "CampaignPlacementsLinks"
        SET placement_id={feed_placement_id}
        WHERE placement_id={in_feed_placement_id}
        AND campaign_id IN (
            select CPlatLink.campaign_id
            from "CampaignPlatformsLinks" AS CPlatLink
            join "CampaignPlatforms" AS CPlat ON Cplat.id = CPlatLink.platform_id
            join "CampaignPlacementsLinks" AS CPlacLink ON CPlacLink.campaign_id = CPlatLink.campaign_id
            join "CampaignPlacements" AS Cplac ON Cplac.id = CPlacLink.placement_id
            join "CampaignPlatformPlacements" AS CPP ON CPP.placement_id = CPlacLink.placement_id AND CPP.platform_id = CPlatLink.platform_id
            where Cplac.name = 'In Feed'
            group by CPlatLink.campaign_id
            HAVING NOT array_agg(Cplat.name)::text[] && ARRAY['Twitter', 'TikTok', 'Reddit', 'Snapchat'] AND array_agg(Cplat.name)::text[] && ARRAY['Instagram', 'Facebook']
        )
    '''.format(feed_placement_id=feed_placement_id, in_feed_placement_id=in_feed_placement_id))

    campaigns_ids = bind.execute('''
        select CPlatLink.campaign_id
        from "CampaignPlatformsLinks" AS CPlatLink
        join "CampaignPlatforms" AS CPlat ON Cplat.id = CPlatLink.platform_id
        join "CampaignPlacementsLinks" AS CPlacLink ON CPlacLink.campaign_id = CPlatLink.campaign_id
        join "CampaignPlacements" AS Cplac ON Cplac.id = CPlacLink.placement_id
        join "CampaignPlatformPlacements" AS CPP ON CPP.placement_id = CPlacLink.placement_id AND CPP.platform_id = CPlatLink.platform_id
        where Cplac.name = 'In Feed'
        group by CPlatLink.campaign_id
        HAVING array_agg(Cplat.name)::text[] && ARRAY['Twitter', 'TikTok', 'Reddit', 'Snapchat'] AND array_agg(Cplat.name)::text[] && ARRAY['Instagram', 'Facebook']
    ''')
    for campaign_id in campaigns_ids:
        op.execute(
            campaign_placements_links.insert().values(
                placement_id=feed_placement_id,
                campaign_id=campaign_id
            )
        )

    #  update facebook, instagram link In Feed -> Feed
    op.execute(campaign_platform_placements.update().values(placement_id=feed_placement_id).where(
        and_(
            campaign_platform_placements.c.platform_id.in_(
            [platform_id[0] for platform_id in bind.execute(
                select([campaign_platform.c.id]).where(or_(
                    campaign_platform.c.name == "Instagram",
                    campaign_platform.c.name == "Facebook"
                ))
            ).fetchall()]
        ),
            campaign_platform_placements.c.placement_id == in_feed_placement_id
        )
    ))


    #  add new placements and link them
    for placement in placements:
        placement_name = placement.get("name")

        placement_object = bind.execute(
            select([campaign_placements.c.id]).where(
                campaign_placements.c.name==placement_name
            )
        ).fetchone()
        if placement_object is None:
            op.execute(
                campaign_placements.insert().values(name=placement_name)
            )
        placement_id = bind.execute(
            select([campaign_placements.c.id]).where(
                campaign_placements.c.name==placement_name
            )
        ).fetchone()[0]
        for platform in placement.get("platforms"):
            platform_id = bind.execute(
                select([campaign_platform.c.id]).where(
                    campaign_platform.c.name==platform
                )
            ).fetchone()[0]
            platform_placement_object = bind.execute(
                select().where(and_(
                    campaign_platform_placements.c.placement_id==placement_id,
                    campaign_platform_placements.c.platform_id==platform_id,
                    ))
            ).fetchone()
            if platform_placement_object is None:
                op.execute(
                    campaign_platform_placements.insert().values(
                        placement_id=placement_id,
                        platform_id=platform_id
                    )
                )

def downgrade():
    pass