"""remove_unmatched_label_pending_project_campaigns

Revision ID: 81afc0752e2f
Revises: bbb653d9454f
Create Date: 2023-03-06 12:46:34.557504

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

# revision identifiers, used by Alembic.
revision = '81afc0752e2f'
down_revision = 'bbb653d9454f'
branch_labels = None
depends_on = None


def upgrade():
    op.execute(
        """
        DELETE FROM "ProjectCampaign"
        WHERE "ProjectCampaign".id IN (
            SELECT PC.id
            FROM "ProjectCampaign" AS PC
            JOIN "Campaign" AS C ON PC.campaign_id = C.id AND PC.status = 0
            JOIN "Project" AS P ON PC.project_id = P.id
            JOIN "MarketingAccount" AS MA ON C.external_marketing_account_id = MA.external_id
            JOIN "Label" AS MaL ON MaL.id = MA.label_id
            WHERE MA.label_id != P.label_id
        )"""
    )

def downgrade():
    pass