"""update_campaigns_view

Revision ID: a9fa50e7d623
Revises: 8f7bd3021fa2
Create Date: 2021-03-03 17:40:32.752312

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'a9fa50e7d623'
down_revision = '5893e1fbcf45'
branch_labels = None
depends_on = None


def upgrade():
    op.execute(
        """
        CREATE OR REPLACE VIEW campaigns_view AS
            SELECT
                dsp_id(campaign.source, campaign.external_id) AS id,
                campaign.name,
                campaign.planned_budget AS budget,
                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
            		AND (project.is_deleted = FALSE or project.id is NULL)
            );
        """
    )


def downgrade():
    op.execute(
        """
        CREATE OR REPLACE VIEW campaigns_view AS
            SELECT
                dsp_id(campaign.source, campaign.external_id) AS id,
                campaign.name,
                campaign.planned_budget AS budget,
                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
                    AND project.is_deleted IS FALSE
                )
                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;
        """
    )