"""delete_prs_campaigns

Revision ID: 55911bcdbc01
Revises: e9cc7764b6ea
Create Date: 2021-11-22 09:21:36.901367

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


# revision identifiers, used by Alembic.
revision = '55911bcdbc01'
down_revision = 'e9cc7764b6ea'
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()
    data_tables_refresher =  DelphiTablesDataRefresher()

    data_tables_refresher.drop_triggers(bind)
    prs_campaigns_subquery = '''
      select id
        from "Campaign"
        where source = 'PRS' and edit_user_id = (select id from "User" where email='prs@prs.com')
    '''
    ##DELETE CampaignPlatformsLinks
    op.execute(f'''
        DELETE FROM "CampaignPlatformsLinks" WHERE campaign_id in ({prs_campaigns_subquery});
    ''')

    ##DELETE CampaignPlacementsLinks
    op.execute(f'''
        DELETE FROM "CampaignPlacementsLinks" WHERE campaign_id in ({prs_campaigns_subquery});
    ''')

    ##DELETE CampaignGender
    op.execute(f'''
        DELETE FROM "CampaignGender" WHERE campaign_id in ({prs_campaigns_subquery});
    ''')

    ## DELETE CampaignLink
    op.execute(f'''
        DELETE FROM "CampaignLink" WHERE campaign_id in ({prs_campaigns_subquery});
    ''')

    ## DELETE CampaignTerritory
    op.execute(f'''
        DELETE FROM "CampaignTerritory" WHERE campaign_id in ({prs_campaigns_subquery});
    ''')

    ## DELETE CampaignType
    op.execute(f'''
        DELETE FROM "CampaignType" WHERE campaign_id in ({prs_campaigns_subquery});
    ''')

    ## DELETE ProjectCampaign
    op.execute(f'''
        DELETE FROM "ProjectCampaign" WHERE campaign_id in ({prs_campaigns_subquery});
    ''')

    ## DELETE LinkfireCampaign
    op.execute(f'''
        DELETE FROM "LinkfireCampaign" WHERE campaign_id in ({prs_campaigns_subquery});
    ''')

    ## DELETE ProjectHistoryItem
    op.execute(f'''
        DELETE FROM "ProjectHistoryItem" WHERE campaign_id in ({prs_campaigns_subquery});
    ''')

    ## DELETE RecentSearchItem
    op.execute(f'''
        DELETE FROM "RecentSearchItem" WHERE campaign_id in ({prs_campaigns_subquery});
    ''')

    ## DELETE CampaignAd
    op.execute(f'''
        DELETE FROM "CampaignAd" WHERE campaign_id in ({prs_campaigns_subquery});
    ''')

    ## DELETE Campaign
    op.execute(f'''
        ALTER TABLE "Campaign" DROP CONSTRAINT "fk_Campaign_create_user_id_User";
        ALTER TABLE "Campaign" DROP CONSTRAINT "fk_Campaign_objective_id_CampaignObjective";
        ALTER TABLE "Campaign" DROP CONSTRAINT "fk_Campaign_purchase_order_id_PRSPurchaseOrder";
        ALTER TABLE "Campaign" DROP CONSTRAINT "fk_Campaign_edit_user_id_User";
        ALTER TABLE "Campaign" DROP CONSTRAINT "fk_Campaign_external_marketing_account_id_MarketingAccount";
        ALTER TABLE "Campaign" DROP CONSTRAINT "fk_Campaign_currency_id_Currency";
        ALTER TABLE "Campaign" DROP CONSTRAINT "fk_Campaign_project_id_Project";
        ALTER TABLE "Campaign" DROP CONSTRAINT "fk_Campaign_provider_id_CampaignProvider";
        ALTER TABLE "CampaignPlatformsLinks" DROP CONSTRAINT "fk_CampaignPlatformsLinks_campaign_id_Campaign";
        ALTER TABLE "CampaignPlacementsLinks" DROP CONSTRAINT "fk_CampaignPlacementsLinks_campaign_id_Campaign";
        ALTER TABLE "CampaignGender" DROP CONSTRAINT "fk_CampaignGender_campaign_id_Campaign";
        ALTER TABLE "CampaignLink" DROP CONSTRAINT "fk_CampaignLink_campaign_id_Campaign";
        ALTER TABLE "CampaignTerritory" DROP CONSTRAINT "fk_CampaignTerritory_campaign_id_Campaign";
        ALTER TABLE "CampaignType" DROP CONSTRAINT "fk_CampaignType_campaign_id_Campaign";
        ALTER TABLE "ProjectCampaign" DROP CONSTRAINT "fk_ProjectCampaign_campaign_id_Campaign";
        ALTER TABLE "LinkfireCampaign" DROP CONSTRAINT "fk_LinkfireCampaign_campaign_id_Campaign";
        ALTER TABLE "ProjectHistoryItem" DROP CONSTRAINT "fk_ProjectHistoryItem_campaign_id_Campaign";
        ALTER TABLE "RecentSearchItem" DROP CONSTRAINT "fk_RecentSearchItem_campaign_id_Campaign";
        ALTER TABLE "CampaignAd" DROP CONSTRAINT "fk_CampaignAd_campaign_id_Campaign";
    ''')

    op.execute(f'''
        DELETE FROM "Campaign" WHERE source = 'PRS' AND edit_user_id = (SELECT id FROM "User" WHERE email='prs@prs.com');
    ''')

    op.execute(f'''
        ALTER TABLE "Campaign" ADD CONSTRAINT "fk_Campaign_create_user_id_User" FOREIGN KEY ("create_user_id") REFERENCES "public"."User"("id");
        ALTER TABLE "Campaign" ADD CONSTRAINT "fk_Campaign_objective_id_CampaignObjective" FOREIGN KEY ("objective_id") REFERENCES "public"."CampaignObjective"("id");
        ALTER TABLE "Campaign" ADD CONSTRAINT "fk_Campaign_purchase_order_id_PRSPurchaseOrder" FOREIGN KEY ("purchase_order_id") REFERENCES "public"."PRSPurchaseOrder"("id");
        ALTER TABLE "Campaign" ADD CONSTRAINT "fk_Campaign_edit_user_id_User" FOREIGN KEY ("edit_user_id") REFERENCES "public"."User"("id");
        ALTER TABLE "Campaign" ADD CONSTRAINT "fk_Campaign_external_marketing_account_id_MarketingAccount" FOREIGN KEY ("external_marketing_account_id") REFERENCES "public"."MarketingAccount"("external_id");
        ALTER TABLE "Campaign" ADD CONSTRAINT "fk_Campaign_currency_id_Currency" FOREIGN KEY ("currency_id") REFERENCES "public"."Currency"("id");
        ALTER TABLE "Campaign" ADD CONSTRAINT "fk_Campaign_project_id_Project" FOREIGN KEY ("project_id") REFERENCES "public"."Project"("id") ON DELETE CASCADE;
        ALTER TABLE "Campaign" ADD CONSTRAINT "fk_Campaign_provider_id_CampaignProvider" FOREIGN KEY ("provider_id") REFERENCES "public"."CampaignProvider"("id");
        ALTER TABLE "CampaignPlatformsLinks" ADD CONSTRAINT "fk_CampaignPlatformsLinks_campaign_id_Campaign" FOREIGN KEY ("campaign_id") REFERENCES "public"."Campaign"("id") ON DELETE CASCADE;
        ALTER TABLE "CampaignPlacementsLinks" ADD CONSTRAINT "fk_CampaignPlacementsLinks_campaign_id_Campaign" FOREIGN KEY ("campaign_id") REFERENCES "public"."Campaign"("id") ON DELETE CASCADE;
        ALTER TABLE "CampaignGender" ADD CONSTRAINT "fk_CampaignGender_campaign_id_Campaign" FOREIGN KEY ("campaign_id") REFERENCES "public"."Campaign"("id") ON DELETE CASCADE;
        ALTER TABLE "CampaignLink" ADD CONSTRAINT "fk_CampaignLink_campaign_id_Campaign" FOREIGN KEY ("campaign_id") REFERENCES "public"."Campaign"("id") ON DELETE CASCADE;
        ALTER TABLE "CampaignTerritory" ADD CONSTRAINT "fk_CampaignTerritory_campaign_id_Campaign" FOREIGN KEY ("campaign_id") REFERENCES "public"."Campaign"("id") ON DELETE CASCADE;
        ALTER TABLE "CampaignType" ADD CONSTRAINT "fk_CampaignType_campaign_id_Campaign" FOREIGN KEY ("campaign_id") REFERENCES "public"."Campaign"("id") ON DELETE CASCADE;
        ALTER TABLE "ProjectCampaign" ADD CONSTRAINT "fk_ProjectCampaign_campaign_id_Campaign" FOREIGN KEY ("campaign_id") REFERENCES "public"."Campaign"("id") ON DELETE CASCADE;
        ALTER TABLE "LinkfireCampaign" ADD CONSTRAINT "fk_LinkfireCampaign_campaign_id_Campaign" FOREIGN KEY ("campaign_id") REFERENCES "public"."Campaign"("id") ON DELETE CASCADE;
        ALTER TABLE "ProjectHistoryItem" ADD CONSTRAINT "fk_ProjectHistoryItem_campaign_id_Campaign" FOREIGN KEY ("campaign_id") REFERENCES "public"."Campaign"("id") ON DELETE CASCADE;
        ALTER TABLE "RecentSearchItem" ADD CONSTRAINT "fk_RecentSearchItem_campaign_id_Campaign" FOREIGN KEY ("campaign_id") REFERENCES "public"."Campaign"("id") ON DELETE CASCADE;
        ALTER TABLE "CampaignAd" ADD CONSTRAINT "fk_CampaignAd_campaign_id_Campaign" FOREIGN KEY ("campaign_id") REFERENCES "public"."Campaign"("id") ON DELETE CASCADE;
    ''')

    op.execute(f'''
        update "Campaign"
        set type_id = NULL
        where type_id in (1,2,3,4);
    ''')

    op.execute(f'''
        DELETE FROM "CampaignTypes" where id in (1,2,3,4);
    ''')

    data_tables_refresher.refresh(bind)


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