"""fix_project_campaigns_view

Revision ID: 64ccce30ddf6
Revises: rteo58d273b3
Create Date: 2021-02-17 08:36:33.231488

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '64ccce30ddf6'
down_revision = 'rteo58d273b3'
branch_labels = None
depends_on = None


def upgrade():
    op.execute("DROP VIEW project_campaigns_view;")
    op.execute(
        """
            CREATE OR REPLACE VIEW project_campaigns_view AS
                SELECT
                    dsp_id('GRAS', gras_project.external_id) as project_id,
                    dsp_id(campaign.source, campaign.external_id) AS campaign_id
                FROM "Project" as project
                JOIN "GRASProject" AS gras_project ON project.gras_project_id = gras_project.id
                LEFT OUTER JOIN "GRASProjectCampaign" AS gras_project_campaign ON (
                    gras_project_campaign.gras_project_id = gras_project.id AND gras_project_campaign.status = 0
                )
                JOIN "Campaign" as campaign ON (
                    campaign.external_id IS NOT NULL
                    AND campaign.is_deleted IS FALSE
                    AND campaign.project_id = project.id
                    OR (gras_project_campaign.campaign_id = campaign.id AND gras_project_campaign.status = 0)
                )
                WHERE project.is_deleted IS FALSE
                GROUP BY gras_project.external_id, campaign.source, campaign.external_id;
        """
    )


def downgrade():
    op.execute("DROP VIEW project_campaigns_view;")
    op.execute(
        """
            CREATE OR REPLACE VIEW project_campaigns_view AS
                SELECT
                    dsp_id('GRAS', gras_project.external_id) as project_id,
                    campaign.external_id as campaign_id
                FROM "Project" as project
                JOIN "GRASProject" AS gras_project ON project.gras_project_id = gras_project.id
                LEFT OUTER JOIN "GRASProjectCampaign" AS gras_project_campaign ON (
                    gras_project_campaign.gras_project_id = gras_project.id AND gras_project_campaign.status = 0
                )
                JOIN "Campaign" as campaign ON (
                    campaign.external_id IS NOT NULL
                    AND campaign.is_deleted IS FALSE
                    AND campaign.project_id = project.id
                    OR (gras_project_campaign.campaign_id = campaign.id AND gras_project_campaign.status = 0)
                )
                WHERE project.is_deleted IS FALSE
                GROUP BY gras_project.external_id, campaign.external_id;
        """
    )