"""fix_end_dates_in_campaigns_view

Revision ID: e2d4ff9617b6
Revises: 1a1b468be264
Create Date: 2021-02-01 14:59:08.096525

"""
from alembic import op


# revision identifiers, used by Alembic.
revision = 'e2d4ff9617b6'
down_revision = '1a1b468be264'
branch_labels = None
depends_on = None


def upgrade():
    op.execute(
        """
        CREATE OR REPLACE VIEW "CampaignsView" AS
            SELECT
                dsp_id(campaign.source, campaign.external_id) AS id,
                campaign.name,
                campaign.budget_spend AS spend,
                campaign.provider_id,
                campaign.start_date,
                -- End date of campaign should fallback to project end_date if campaign is approved
                coalesce(campaign.end_date, CASE WHEN NOT is_pending THEN project.end_date END) as end_date,
                -- If is_pending is NULL, then campaign in unassigned
                coalesce(gras_status.is_pending, false) AS is_pending,
                campaign.objective_id,
                marketing_account.label_id AS decibel_label_id,
                sub_category.category_id,
                sub_category.sub_category_id
            FROM "Campaign" AS campaign
                LEFT JOIN (
                    SELECT
                        campaign_id,
                        -- Should be only used for approved campaigns
                        min(gras_project_id) as gras_project_id,
                        min(status) = 0 as is_pending
                    FROM "GRASProjectCampaign"
                    -- [0 -> Pending] for is_pending status
                    -- [1 -> Approved] for end_date fallback
                    -- [2 -> Rejected] should be ignored
                    WHERE status != 2
                    GROUP BY campaign_id
                ) AS gras_status ON gras_status.campaign_id = campaign.id
                LEFT JOIN "Project" AS project ON project.gras_project_id = gras_status.gras_project_id
                JOIN "MarketingAccount" AS marketing_account ON
                    marketing_account.external_id = campaign.external_marketing_account_id
                JOIN (
                    SELECT cp.id AS campaign_id, CTV.id AS sub_category_id, CTV.group_id AS category_id
                    FROM "Campaign" AS 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
                ) AS sub_category ON campaign.id = sub_category.campaign_id
            WHERE campaign.external_id IS NOT NULL AND campaign.is_deleted IS FALSE;
        """
    )


def downgrade():
    op.execute(
        """
            CREATE OR REPLACE VIEW "CampaignsView" AS
                SELECT
                    dsp_id(campaign.source, campaign.external_id) AS id,
                    campaign.name,
                    campaign.budget_spend AS spend,
                    campaign.provider_id,
                    campaign.start_date,
                    campaign.end_date,
                    coalesce(gras_status.is_pending, false) AS is_pending,
                    campaign.objective_id,
                    marketing_account.label_id AS decibel_label_id,
                    sub_category.category_id,
                    sub_category.sub_category_id
                FROM "Campaign" AS campaign
                    LEFT JOIN (
                        SELECT campaign_id, min(status) = 0 AS is_pending
                        FROM "GRASProjectCampaign"
                        GROUP BY campaign_id
                    ) AS gras_status ON gras_status.campaign_id = campaign.id
                    JOIN "MarketingAccount" AS marketing_account ON
                        marketing_account.external_id = campaign.external_marketing_account_id
                    JOIN (
                        SELECT cp.id AS campaign_id, CTV.id AS sub_category_id, CTV.group_id AS category_id
                        FROM "Campaign" AS 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
                    ) AS sub_category ON campaign.id = sub_category.campaign_id
                WHERE campaign.external_id IS NOT NULL AND campaign.is_deleted IS FALSE;
        """
    )