"""update_delphi_views

Revision ID: bb7e9cb8760d
Revises: qfep3td233b2
Create Date: 2021-02-09 09:27:14.511123

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'bb7e9cb8760d'
down_revision = 'qfep3td233b2'
branch_labels = None
depends_on = None


def upgrade():
    op.execute(
        """
            DROP VIEW "UserLabelsView";             -- 1 user_labels_view
            DROP VIEW "DecibelLabelsView";          -- 2 decibel_labels_view
            DROP VIEW "ProjectsView";               -- 3 projects_view
            DROP VIEW "ProjectArtistsView";         -- 4 project_artists_view
            DROP VIEW "PhaseCampaignsView";         -- 5 phase_campaigns_view
            DROP VIEW "PhasesView";                 -- 6 phases_view
            DROP VIEW "ProjectCampaignsView";       -- 7 project_campaigns_view
            DROP VIEW "UserProjectsView";           -- 8 user_projects_view
            DROP VIEW "CampaignsView";              -- 9 campaigns_view
            DROP VIEW "CampaignCategoryView";       -- 10 campaign_category_view
            DROP VIEW "CampaignSubCategoryView";    -- 11 campaign_sub_category_view
            DROP VIEW "CampaignObjectiveView";      -- 12 campaign_objective_view
            DROP VIEW "CampaignProviderView";       -- 13 campaign_provider_view
            DROP VIEW "CampaignPlatformsView";      -- 14 campaign_platforms_view
        """
    )
    op.execute(
        """
            CREATE OR REPLACE FUNCTION dsp_id(TEXT, VARCHAR) RETURNS TEXT
            AS $$ SELECT $1 || '_' || $2; $$
            LANGUAGE SQL;
        """
    )

    op.execute(
        """
        CREATE OR REPLACE VIEW decibel_labels_view AS
            SELECT
                label.id,
                label.name
            FROM "Label" AS label;
        """
    )

    op.execute(
        """
            CREATE OR REPLACE VIEW user_labels_view AS
                SELECT
                    user_label.user_id,
                    user_label.label_id AS decibel_label_id
                FROM "UserLabel" AS user_label;
        """
    )

    op.execute(
        """
            CREATE OR REPLACE VIEW projects_view AS
                SELECT
                    dsp_id('GRAS', gras_project.external_id) AS id,
                    project.name,
                    project.planned_budget AS budget,
                    project.label_id AS decibel_label_id,
                    psd.start_date AS start_date,
                    project.end_date
                FROM "Project" AS project
                JOIN "GRASProject" AS gras_project ON project.gras_project_id = gras_project.id
                JOIN (
                        SELECT project_id,
                               MIN(start_date) as start_date
                        FROM "ProjectPhase"
                        GROUP BY project_id
                    ) AS psd ON psd.project_id = project.id
                WHERE project.is_deleted IS FALSE;
        """
    )

    op.execute(
        """
            CREATE OR REPLACE VIEW project_artists_view AS
                SELECT
                    dsp_id('GRAS', gras_project.external_id) as project_id,
                    artist.external_id as artist_id
                FROM "Project" AS project
                JOIN "GRASProject" AS gras_project ON project.gras_project_id = gras_project.id
                JOIN "ProjectTargetItem" AS project_target_item ON (
                        project_target_item.project_id = project.id
                        AND project_target_item.is_deleted IS FALSE
                        AND project_target_item.entity_type IN (0,1)
                    )
                JOIN "Polymorphable" AS polymorphable ON polymorphable.id = project_target_item.entity_id
                JOIN "Artist" AS artist ON artist.id = polymorphable.id
                WHERE project.is_deleted IS FALSE
                GROUP BY gras_project.external_id, artist.external_id;
        """
    )

    op.execute(
        """
            CREATE OR REPLACE VIEW campaign_category_view AS
                SELECT ctg.id, ctg.name
                FROM "CampaignTypeGroup" as ctg;
        """
    )

    op.execute(
        """
            CREATE OR REPLACE VIEW campaign_sub_category_view AS
                SELECT ctv.id, ctv.name
                FROM "CampaignTypeValue" as ctv
                WHERE ctv.group_id is not null;
        """
    )

    op.execute(
        """
            CREATE OR REPLACE VIEW campaign_objective_view AS
                SELECT co.id, co.name
                FROM "CampaignObjective" as co;
        """
    )

    op.execute(
        """
            CREATE OR REPLACE VIEW campaign_provider_view AS
                SELECT cp.id, cp.name
                FROM "CampaignProvider" as cp;
        """
    )

    op.execute(
        """
            CREATE OR REPLACE VIEW campaign_platforms_view AS
                SELECT
                    dsp_id(campaign.source, campaign.external_id) AS campaign_id,
                    campaign_type_value.name AS name
                FROM "Campaign" AS campaign
                JOIN "CampaignType" AS campaign_type ON campaign_type.campaign_id = campaign.id
                JOIN "CampaignTypeCategory" AS campaign_type_category ON (
                    campaign_type_category.id = campaign_type.category_id AND campaign_type_category.name = 'Platforms'
                )
                JOIN "CampaignType" AS platform_campaign_type ON (
                    platform_campaign_type.campaign_id = campaign.id
                    AND platform_campaign_type.category_id = campaign_type_category.id
                )
                JOIN "CampaignTypeValue" AS campaign_type_value ON (
                    campaign_type_value.id = platform_campaign_type.value_id
                )
                WHERE campaign.external_id IS NOT NULL AND campaign.is_deleted IS FALSE
                GROUP BY campaign.id, campaign_type_value.name;
        """
    )

    op.execute(
        """
             CREATE OR REPLACE VIEW phases_view AS
                SELECT
                    project_phase.id,
                    project_phase.name,
                    dsp_id('GRAS', gras_project.external_id) AS project_id,
                    project_phase.start_date,
                    -- BEGIN end_date calculation
                    GREATEST(
                        CAST(
                            COALESCE(
                                -- If the next line contains the same project
                                CASE WHEN
                                    LEAD(project_phase.project_id)
                                        OVER(ORDER BY project_phase.project_id, project_phase.order) = project_phase.project_id
                                -- Then take start_date - 1 DAY as phase end_date
                                THEN
                                    LEAD(project_phase.start_date - CAST('1 DAY' AS INTERVAL))
                                        OVER(ORDER BY project_phase.project_id, project_phase.order)
                                END,
                                -- For the last phase in project we should use project end_date as phase end_date
                                project.end_date
                            )
                            AS DATE
                        ),
                        -- In rare cases start_date could be greater than end_date
                        project_phase.start_date
                    ) AS end_date
                    -- END end_date calculation
                FROM "ProjectPhase" AS project_phase
                JOIN "Project" as project ON project.id = project_phase.project_id AND project.is_deleted IS FALSE
                JOIN "GRASProject" as gras_project ON gras_project.id = project.gras_project_id
                GROUP BY project_phase.id,
                         project_phase.name,
                         gras_project.external_id,
                         project_phase.start_date,
                         project.end_date;
        """
    )

    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;
        """
    )

    op.execute(
        """
            CREATE OR REPLACE VIEW user_projects_view AS
                SELECT
                    dsp_id('GRAS', gras_project.external_id) as project_id,
                    dec_user.id as user_id
                FROM "Project" as project
                JOIN "GRASProject" as gras_project ON gras_project.id = project.gras_project_id
                LEFT OUTER JOIN "UserProject" as user_project ON user_project.project_id = project.id
                JOIN "User" as dec_user ON dec_user.id = user_project.user_id OR dec_user.id = project.owner_id
                WHERE project.is_deleted IS FALSE
                GROUP BY gras_project.external_id, dec_user.id;
        """
    )

    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;
        """
    )

    op.execute(
        """
            CREATE OR REPLACE VIEW phase_campaigns_view AS
                SELECT
                    dsp_id(campaign.source, campaign.external_id) AS campaign_id,
                    project_phases.id AS phase_id
                FROM phases_view AS project_phases
                JOIN "GRASProject" AS gras_project ON
                    project_phases.project_id = dsp_id('GRAS', gras_project.external_id)
                JOIN "Project" AS project ON (
                     project.gras_project_id = gras_project.id
                     AND project.is_deleted is FALSE
                )
                LEFT OUTER JOIN "GRASProjectCampaign" AS gras_project_campaign ON (
                    gras_project.id = gras_project_campaign.gras_project_id AND gras_project_campaign.status = 0
                )
                LEFT JOIN "Campaign" AS campaign ON (
                    (project.id = campaign.project_id OR gras_project_campaign.campaign_id = campaign.id)
                    AND campaign.is_deleted = FALSE
                    AND daterange(project_phases.start_date, project_phases.end_date, '[]') @> campaign.start_date
                )
                WHERE campaign.id IS NOT NULL
                      AND campaign.external_id IS NOT NULL
                      AND project_phases.id IS NOT NULL
                      AND project_phases.start_date <= project_phases.end_date
                GROUP BY campaign.source, campaign.external_id, project_phases.id;
        """
    )


def downgrade():

    op.execute(
        """
            DROP VIEW user_labels_view;
            DROP VIEW decibel_labels_view;
            DROP VIEW projects_view;
            DROP VIEW project_artists_view;
            DROP VIEW phase_campaigns_view;
            DROP VIEW phases_view;
            DROP VIEW project_campaigns_view;
            DROP VIEW user_projects_view;
            DROP VIEW campaigns_view;
            DROP VIEW campaign_category_view;
            DROP VIEW campaign_sub_category_view;
            DROP VIEW campaign_objective_view;
            DROP VIEW campaign_provider_view;
            DROP VIEW campaign_platforms_view;
        """
    )
    op.execute(
        """
        CREATE VIEW "DecibelLabelsView" AS
            SELECT
                label.id,
                label.name
            FROM "Label" AS label;
        """
    )
    # table UserLabels {
    #     user_id int
    #     decibel_label_id integer[ref: > DecibelLabels.id]
    # }
    op.execute(
        """
            CREATE VIEW "UserLabelsView" AS
                SELECT
                    user_label.user_id,
                    user_label.label_id AS decibel_label_id
                FROM "UserLabel" AS user_label;
        """
    )
    # table Projects {
    #     id varchar // External ID
    #     name varchar
    #     budget numeric(12,2)
    #     decibel_label_id  integer [ref: > DecibelLabels.id]
    #     start_date "timestamp with time zone"
    #     end_date "timestamp with time zone"
    # }
    op.execute(
        """
            CREATE OR REPLACE VIEW "ProjectsView" AS
                SELECT
                    dsp_id('GRAS', gras_project.external_id) AS id,
                    project.name,
                    project.planned_budget AS budget,
                    project.label_id AS decibel_label_id,
                    psd.start_date AS start_date,
                    project.end_date
                FROM "Project" AS project
                JOIN "GRASProject" AS gras_project ON project.gras_project_id = gras_project.id
                JOIN (
                        SELECT project_id,
                               MIN(start_date) as start_date
                        FROM "ProjectPhase"
                        GROUP BY project_id
                    ) AS psd ON psd.project_id = project.id
                WHERE project.is_deleted IS FALSE;
        """
    )
    # table ProjectArtists {
    #     project_id varchar [ref: > Projects.id]
    #     artist_id varchar
    # }
    op.execute(
        """
            CREATE VIEW "ProjectArtistsView" AS
                SELECT
                    dsp_id('GRAS', gras_project.external_id) as project_id,
                    artist.external_id as external_id
                FROM "Project" AS project
                JOIN "GRASProject" AS gras_project ON project.gras_project_id = gras_project.id
                JOIN "ProjectTargetItem" AS project_target_item ON (
                        project_target_item.project_id = project.id
                        AND project_target_item.is_deleted IS FALSE
                        AND project_target_item.entity_type IN (0,1)
                    )
                JOIN "Polymorphable" AS polymorphable ON polymorphable.id = project_target_item.entity_id
                JOIN "Artist" AS artist ON artist.id = polymorphable.id
                WHERE project.is_deleted IS FALSE
                GROUP BY gras_project.external_id, artist.external_id;
        """
    )
    # table Phases {
    #     id integer
    #     name varchar
    #     project_id varchar [ref: > Projects.id]
    #     start_date "timestamp with time zone"
    #     end_date "timestamp with time zone"
    # }
    op.execute(
        """
             CREATE VIEW "PhasesView" AS
                SELECT
                    project_phase.id,
                    project_phase.name,
                    dsp_id('GRAS', gras_project.external_id) AS project_id,
                    project_phase.start_date,
                    -- BEGIN end_date calculation
                    GREATEST(
                        CAST(
                            COALESCE(
                                -- If the next line contains the same project
                                CASE WHEN
                                    LEAD(project_phase.project_id)
                                        OVER(ORDER BY project_phase.project_id, project_phase.order) = project_phase.project_id
                                -- Then take start_date - 1 DAY as phase end_date
                                THEN
                                    LEAD(project_phase.start_date - CAST('1 DAY' AS INTERVAL))
                                        OVER(ORDER BY project_phase.project_id, project_phase.order)
                                END,
                                -- For the last phase in project we should use project end_date as phase end_date
                                project.end_date
                            )
                            AS DATE
                        ),
                        -- In rare cases start_date could be greater than end_date
                        project_phase.start_date
                    ) AS end_date
                    -- END end_date calculation
                FROM "ProjectPhase" AS project_phase
                JOIN "Project" as project ON project.id = project_phase.project_id
                JOIN "GRASProject" as gras_project ON gras_project.id = project.gras_project_id
                GROUP BY project_phase.id,
                         project_phase.name,
                         gras_project.external_id,
                         project_phase.start_date,
                         project.end_date;
        """
    )
    # table ProjectCampaigns {
    #     project_id varchar [ref: > Projects.id]
    #     campaign_id varchar [ref: > Campaigns.id]
    # }
    op.execute(
        """
            CREATE VIEW "ProjectCampaignsView" 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.project_id = project.id
                    OR (gras_project_campaign.campaign_id = campaign.id AND gras_project_campaign.status = 0)
                )
                GROUP BY gras_project.external_id, campaign.external_id;
        """
    )

    # table UserProjects {
    #     user_id int
    #     project_id varchar [ref: > Projects.id]
    # }
    op.execute(
        """
            CREATE VIEW "UserProjectsView" AS
                SELECT
                    dsp_id('GRAS', gras_project.external_id) as project_id,
                    dec_user.id as user_id
                FROM "Project" as project
                JOIN "GRASProject" as gras_project ON gras_project.id = project.gras_project_id
                LEFT OUTER JOIN "UserProject" as user_project ON user_project.project_id = project.id
                JOIN "User" as dec_user ON dec_user.id = user_project.user_id OR dec_user.id = project.owner_id
                GROUP BY gras_project.external_id, dec_user.id;
        """
    )
    # table Campaigns {
    #     id varchar // External ID
    #     name varchar
    #     budget numeric(12,2)
    #     spend numeric(12,2)
    #     decibel_label_id integer [ref: > DecibelLabels.id]
    #     provider_id integer [ref: > CampaignProvider.id]
    #     start_date "timestamp with time zone"
    #     end_date "timestamp with time zone"
    #     is_pending boolean
    #     objective_id integer [ref: > CampaignObjective.id]
    #     category_id integer  [ref: > CampaignCategory.id]
    #     sub_category_id integer [ref: > CampaignSubCategory.id]
    # }
    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;
        """
    )

    # table CampaignCategory {
    #     id integer
    #     name varchar
    # }
    op.execute(
        """
            CREATE VIEW "CampaignCategoryView" AS
                SELECT ctg.id, ctg.name
                FROM "CampaignTypeGroup" as ctg;
        """
    )

    # table CampaignSubCategory {
    #     id integer
    #     name varchar
    # }
    op.execute(
        """
            CREATE VIEW "CampaignSubCategoryView" AS
                SELECT ctv.id, ctv.name
                FROM "CampaignTypeValue" as ctv
                WHERE ctv.group_id is not null;
        """
    )

    # table CampaignObjective {
    #     id integer
    #     name varchar
    # }
    op.execute(
        """
            CREATE VIEW "CampaignObjectiveView" AS
                SELECT co.id, co.name
                FROM "CampaignObjective" as co;
        """
    )

    # table CampaignProvider {
    #     id integer
    #     name varchar
    # }
    op.execute(
        """
            CREATE VIEW "CampaignProviderView" AS
                SELECT cp.id, cp.name
                FROM "CampaignProvider" as cp;
        """
    )

    # table CampaignPlatforms {
    #     campaign_id string [ref: > Campaigns.id]
    #     platform varchar
    # }
    op.execute(
        """
            CREATE VIEW "CampaignPlatformsView" AS
                SELECT
                    dsp_id(campaign.source, campaign.external_id) AS campaign_id,
                    campaign_type_value.name AS name
                FROM "Campaign" AS campaign
                JOIN "CampaignType" AS campaign_type ON campaign_type.campaign_id = campaign.id
                JOIN "CampaignTypeCategory" AS campaign_type_category ON (
                    campaign_type_category.id = campaign_type.category_id AND campaign_type_category.name = 'Platforms'
                )
                JOIN "CampaignType" AS platform_campaign_type ON (
                    platform_campaign_type.campaign_id = campaign.id
                    AND platform_campaign_type.category_id = campaign_type_category.id
                )
                JOIN "CampaignTypeValue" AS campaign_type_value ON (
                    campaign_type_value.id = platform_campaign_type.value_id
                )
                GROUP BY campaign.id, campaign_type_value.name;
        """
    )

    # table PhaseCampaigns {
    #     phase_id integer [ref: > Phases.id]
    #     campaign_id string [ref: > Campaigns.id]
    # }
    op.execute(
        """
            CREATE VIEW "PhaseCampaignsView" AS
                SELECT
                    dsp_id(campaign.source, campaign.external_id) AS campaign_id,
                    project_phases.id AS phase_id
                FROM "PhasesView" AS project_phases
                JOIN "GRASProject" AS gras_project ON
                    project_phases.project_id = dsp_id('GRAS', gras_project.external_id)
                JOIN "Project" AS project ON (
                     project.gras_project_id = gras_project.id
                     AND project.is_deleted is FALSE
                )
                LEFT OUTER JOIN "GRASProjectCampaign" AS gras_project_campaign ON (
                    gras_project.id = gras_project_campaign.gras_project_id AND gras_project_campaign.status = 0
                )
                LEFT JOIN "Campaign" AS campaign ON (
                    (project.id = campaign.project_id OR gras_project_campaign.campaign_id = campaign.id)
                    AND campaign.is_deleted = FALSE
                    AND daterange(project_phases.start_date, project_phases.end_date, '[]') @> campaign.start_date
                )
                WHERE campaign.id IS NOT NULL
                      AND campaign.external_id IS NOT NULL
                      AND project_phases.id IS NOT NULL
                      AND project_phases.start_date <= project_phases.end_date
                GROUP BY campaign.source, campaign.external_id, project_phases.id;
        """
    )