"""create_delphi_views

Revision ID: 1a1b468be264
Revises: 0b4eed214b27
Create Date: 2021-01-27 10:35:59.063975

"""
from alembic import op
import sqlalchemy as sa


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


def upgrade():
    op.execute(
        """
            CREATE OR REPLACE FUNCTION dsp_id(TEXT, VARCHAR) RETURNS TEXT
            AS $$ SELECT UPPER($1) || '_' || $2; $$
            LANGUAGE SQL;
        """
    )
    # table DecibelLabels {
    #   id integer
    #   name varchar
    # }
    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 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;
        """
    )

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


def downgrade():
    op.execute(
        """
            DROP VIEW "UserLabelsView";
            DROP VIEW "DecibelLabelsView";
            DROP VIEW "ProjectsView";
            DROP VIEW "ProjectArtistsView";
            DROP VIEW "PhaseCampaignsView";
            DROP VIEW "PhasesView";
            DROP VIEW "ProjectCampaignsView";
            DROP VIEW "UserProjectsView";
            DROP VIEW "CampaignsView";
            DROP VIEW "CampaignCategoryView";
            DROP VIEW "CampaignSubCategoryView";
            DROP VIEW "CampaignObjectiveView";
            DROP VIEW "CampaignProviderView";
            DROP VIEW "CampaignPlatformsView";
            DROP FUNCTION dsp_id;
        """
    )