"""delete_duplicate_campaign_providers

Revision ID: 3fd263a27e38
Revises: a2016e2b7112
Create Date: 2022-08-17 12:33:55.111088

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision = '3fd263a27e38'
down_revision = 'a2016e2b7112'
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()
    meta = sa.MetaData(bind=bind)
    meta.reflect()
    campaigns_table = meta.tables["Campaign"]
    po_table = meta.tables["PRSPurchaseOrder"]
    providers_table = meta.tables["CampaignProvider"]

    result_tuple = bind.execute('''
        select array_agg("CampaignProvider".id) from "CampaignProvider"
        group by lower("CampaignProvider".name)
        having count("CampaignProvider".id) > 1
    ''').fetchall()

    for result in result_tuple:
        for ids in result:
            first_id = ids.pop(0)

            bind.execute(
                campaigns_table.update().values(provider_id=first_id).where(campaigns_table.c.provider_id.in_(ids))
            )

            bind.execute(
                po_table.update().values(provider_id=first_id).where(po_table.c.provider_id.in_(ids))
            )

            bind.execute(providers_table.delete().where(providers_table.c.id.in_(ids)))


def downgrade():
    pass