"""create_new_tables_in_place_of_taxonomy

Revision ID: 0ad8e83655b7
Revises: 493a68bb330f
Create Date: 2021-10-21 12:56:48.295277

"""
from alembic import op
import sqlalchemy as sa
from migrations.fixtures.delphi_tables.v3.data_refresher import DelphiTablesDataRefresher
from migrations.fixtures.delphi_tables.v4.data_refresher import DelphiTablesDataRefresher as DelphiTablesDataRefresherV4

# revision identifiers, used by Alembic.
revision = '0ad8e83655b7'
down_revision = 'd2fb13ec07aa'
branch_labels = None
depends_on = None


def upgrade():
    delphi_refresher = DelphiTablesDataRefresher()
    bind = op.get_bind()
    delphi_refresher.drop_triggers(bind)

    campaign_types_table = op.create_table(
        'CampaignTypes',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(), nullable=False, index=True),
        sa.Column('group_id', sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ['group_id'],
            ['CampaignTypeGroup.id'],
            name=op.f('fk_CampaignTypes_category_id_CampaignTypeGroup'),
        ),
        sa.PrimaryKeyConstraint('id', name=op.f('pk_CampaignTypes')),
    )

    op.add_column(
        'Campaign',
        sa.Column('type_id', sa.Integer(), nullable=True, server_default=None),
    )

    op.add_column(
        'Campaign',
        sa.Column('gratis', sa.Boolean(), nullable=True),
    )

    op.create_foreign_key(op.f('fk_Campaign_type_id_CampaignTypes'), 'Campaign', 'CampaignTypes', ['type_id'], ['id'])

    campaign_platforms_table = op.create_table(
        'CampaignPlatforms',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(), nullable=False, index=True),
        sa.Column('label_id', sa.Integer(), nullable=True, server_default=None),
        sa.PrimaryKeyConstraint('id', name=op.f('pk_CampaignPlatforms')),
        sa.ForeignKeyConstraint(
            ['label_id'],
            ['Label.id'],
            name=op.f('fk_CampaignPlatforms_label_id_Label'),
            ondelete="CASCADE",
        ),
    )

    campaign_platforms_links_table = op.create_table(
        'CampaignPlatformsLinks',
        sa.Column('campaign_id', sa.Integer(), nullable=False),
        sa.Column('platform_id', sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ['campaign_id'],
            ['Campaign.id'],
            name=op.f('fk_CampaignPlatformsLinks_campaign_id_Campaign'),
            ondelete="CASCADE",
        ),
        sa.ForeignKeyConstraint(
            ['platform_id'],
            ['CampaignPlatforms.id'],
            name=op.f('fk_CampaignPlatformsLinks_platform_id_CampaignPlatforms'),
            ondelete="CASCADE",
        ),
        sa.PrimaryKeyConstraint('campaign_id', 'platform_id', name=op.f('pk_CampaignPlatformsLinks')),
    )

    op.create_table(
        'CampaignPlacements',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(), nullable=False),
        sa.Column('label_id', sa.Integer(), nullable=True),
        sa.Column('is_custom', sa.Boolean(), nullable=False),
        sa.ForeignKeyConstraint(['label_id'], ['Label.id'], name=op.f('fk_CampaignPlacements_label_id_Label')),
        sa.PrimaryKeyConstraint('id', name=op.f('pk_CampaignPlacements')),
    )

    op.create_table(
        'CampaignPlacementsLinks',
        sa.Column('campaign_id', sa.Integer(), nullable=False),
        sa.Column('placement_id', sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ['campaign_id'],
            ['Campaign.id'],
            name=op.f('fk_CampaignPlacementsLinks_campaign_id_Campaign'),
            ondelete="CASCADE",
        ),
        sa.ForeignKeyConstraint(
            ['placement_id'],
            ['CampaignPlacements.id'],
            name=op.f('fk_CampaignPlacementsLinks_platform_id_CampaignPlacements'),
            ondelete="CASCADE",
        ),
        sa.PrimaryKeyConstraint('campaign_id', 'placement_id', name=op.f('pk_CampaignPlacementsLinks')),
    )

    types = bind.execute('''
        select name, group_id from "CampaignTypeValue"
        where category_id = 1
     ''').fetchall()

    types_insert = []
    for name, group_id in types:
        types_insert.append({
            "name": name,
            "group_id": group_id,
        })
    op.bulk_insert(campaign_types_table, types_insert)

    platforms = bind.execute('''
    select DISTINCT name, label_id from "CampaignTypeValue"
    where category_id between 2 and 25
    ''').fetchall()

    platforms_insert = []
    for name, label_id in platforms:
        platforms_insert.append({
            "name": name,
            "label_id": label_id,
        })
    op.bulk_insert(campaign_platforms_table, platforms_insert)

    campaign_platforms = bind.execute('''
    SELECT campaign.id, ARRAY_AGG(DISTINCT campaign_type_value.name) as platforms
    FROM "Campaign" campaign
    JOIN "CampaignType" campaign_type ON campaign_type.campaign_id = campaign.id
    JOIN "CampaignTypeCategory" campaign_type_category ON campaign_type_category.id = campaign_type.category_id AND campaign_type_category.name::text = 'Platforms'::text
    JOIN "CampaignType" platform_campaign_type ON platform_campaign_type.campaign_id = campaign.id AND platform_campaign_type.category_id = campaign_type_category.id
    JOIN "CampaignTypeValue" campaign_type_value ON campaign_type_value.id = platform_campaign_type.value_id
    WHERE campaign.external_id IS NOT NULL AND campaign.is_deleted IS FALSE
    GROUP BY campaign.id
    ''').fetchall()

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

    platform_links_insert = []
    for campaign_id, platforms in campaign_platforms:
        for platform in platforms:
            platform_links_insert.append({
                "campaign_id": campaign_id,
                "platform_id": platforms_key_by_name[platform],
            })

    op.bulk_insert(campaign_platforms_links_table, platform_links_insert)

    bind.execute('''
        with t as (
        SELECT cp.id as campaign_id, ctv.name as name
            FROM "Campaign" cp
            JOIN "CampaignType" ct ON cp.id = ct.campaign_id
            JOIN "CampaignTypeValue" ctv ON ct.value_id = ctv.id AND ctv.group_id IS NOT NULL
            GROUP BY cp.id, ctv.id
        ), t1 as (
            SELECT t.campaign_id as campaign_id, "CampaignTypes".id as campaign_type_id from "CampaignTypes"
            join t on "CampaignTypes".name = t.name
        )
        UPDATE "Campaign"
        SET type_id = t1.campaign_type_id
        from t1
        where "Campaign".id = t1.campaign_id
    ''')

    bind.execute('''
        with t as (
        SELECT campaign.id,
          case
              when (ARRAY_AGG(campaign_type_value.name))[1]='Gratis' then true
              else False
              end
          as gratis
        FROM "Campaign" campaign
        JOIN "CampaignType" campaign_type ON campaign_type.campaign_id = campaign.id
        JOIN "CampaignTypeCategory" campaign_type_category ON campaign_type_category.id = campaign_type.category_id AND campaign_type_category.name::text = 'Gratis'::text
        JOIN "CampaignType" platform_campaign_type ON platform_campaign_type.campaign_id = campaign.id AND platform_campaign_type.category_id = campaign_type_category.id
        JOIN "CampaignTypeValue" campaign_type_value ON campaign_type_value.id = platform_campaign_type.value_id
        WHERE campaign.external_id IS NOT NULL AND campaign.is_deleted IS FALSE
        GROUP BY campaign.id)
        UPDATE "Campaign"
        set gratis = t.gratis
        from t
        where "Campaign".id = t.id;
    ''')

    bind.execute('''
        update "Campaign"
        set metainfo = metainfo::jsonb #- '{fields, campaignTypes}'
    ''')

    DelphiTablesDataRefresherV4().refresh(bind)


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###