"""restructure_delphi_views

Revision ID: 40c29bfde73f
Revises: fc9a4aa5060a
Create Date: 2021-04-01 09:05:26.786181

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '40c29bfde73f'
down_revision = 'fc9a4aa5060a'
branch_labels = None
depends_on = None


def upgrade():
    # new projects view
    op.execute(
        """
            CREATE OR REPLACE VIEW projects_view AS
                SELECT dsp_id('GRAS'::text, project.gras_project_code) AS id,
                    project.name,
                    project.budget,
                    project.label_id AS decibel_label_id,
                    psd.start_date,
                    project.end_date
                FROM "Project" project
                JOIN (
                    SELECT "ProjectPhase".project_id, min("ProjectPhase".start_date) AS start_date
                    FROM "ProjectPhase"
                    GROUP BY "ProjectPhase".project_id
                ) psd ON psd.project_id = project.id
                WHERE project.is_deleted IS FALSE AND project.owner_id IS NOT NULL;
        """
    )

    # new campaigns view
    op.execute(
        """
            CREATE OR REPLACE VIEW campaigns_view AS
                SELECT dsp_id(campaign.source::text, campaign.external_id) AS id,
                   campaign.name,
                   campaign.planned_budget                             AS budget,
                   campaign.budget_spend                               AS spend,
                   campaign.provider_id,
                   campaign.start_date,
                   COALESCE(campaign.end_date,
                            CASE
                                WHEN NOT status.is_pending THEN project.end_date
                                ELSE NULL::date
                                END
                            )                                          AS end_date,
                   COALESCE(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" campaign
                     LEFT JOIN (SELECT "ProjectCampaign".campaign_id,
                                       min("ProjectCampaign".project_id) AS project_id,
                                       min("ProjectCampaign".status) = 0 AS is_pending
                                FROM "ProjectCampaign"
                                WHERE "ProjectCampaign".status <> 2
                                GROUP BY "ProjectCampaign".campaign_id) status ON status.campaign_id = campaign.id
                     LEFT JOIN "Project" project ON project.id = status.project_id
                     JOIN "MarketingAccount" marketing_account
                          ON marketing_account.external_id::text = campaign.external_marketing_account_id::text
                     JOIN (SELECT cp.id        AS campaign_id,
                                  ctv.id       AS sub_category_id,
                                  ctv.group_id AS category_id
                           FROM "Campaign" 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) 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);
        """
    )

    # phases_view
    op.execute(
        """
            CREATE OR REPLACE VIEW phases_view AS
                SELECT project_phase.id,
                       project_phase.name,
                       dsp_id('GRAS'::text, project.gras_project_code) AS project_id,
                       project_phase.start_date,
                       GREATEST(COALESCE(
                            CASE
                            WHEN lead(project_phase.project_id) OVER (ORDER BY project_phase.project_id, project_phase."order") = project_phase.project_id THEN lead(project_phase.start_date - '1 day'::interval) OVER (ORDER BY project_phase.project_id, project_phase."order")
                            ELSE NULL::timestamp without time zone
                            END, project.end_date::timestamp without time zone)::date, project_phase.start_date
                       ) AS end_date
                FROM "ProjectPhase" project_phase
                JOIN "Project" project ON (
                    project.id = project_phase.project_id AND
                    project.is_deleted IS FALSE AND
                    project.owner_id IS NOT NULL
                )
                GROUP BY project.gras_project_code, project_phase.id, project_phase.name, project_phase.start_date, project.end_date;
        """
    )

    # new phase_campaigns_view
    op.execute(
        """
             CREATE OR REPLACE VIEW phase_campaigns_view AS
                SELECT
                    dsp_id(campaign.source::text, campaign.external_id) AS campaign_id,
                    project_phases.id AS phase_id
                FROM phases_view project_phases
                JOIN "Project" project ON dsp_id('GRAS'::text, project.gras_project_code) = project_phases.project_id AND project.is_deleted IS FALSE
                LEFT JOIN "ProjectCampaign" project_campaign ON project.id = project_campaign.project_id AND project_campaign.status in (0, 1)
                LEFT JOIN "Campaign" campaign ON (
                    project_campaign.campaign_id = campaign.id AND
                    campaign.is_deleted = false AND
                    daterange(project_phases.start_date, project_phases.end_date, '[]'::text) @> 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;
        """
    )

    # new project_artists_view
    op.execute(
        """
            CREATE OR REPLACE VIEW project_artists_view AS
                SELECT dsp_id('GRAS'::text, project.gras_project_code) AS project_id,
                       artist.external_id AS artist_id
                FROM "Project" project
                JOIN "ProjectTargetItem" 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 = ANY (ARRAY[0::bigint, 1::bigint]))
                JOIN "Polymorphable" polymorphable ON polymorphable.id = project_target_item.entity_id
                JOIN "Artist" artist ON artist.id = polymorphable.id
                WHERE project.is_deleted IS FALSE
                GROUP BY project.gras_project_code, artist.external_id;
        """
    )

    # new project_campaigns_view
    op.execute(
        """
        CREATE OR REPLACE VIEW project_campaigns_view AS
            SELECT dsp_id('GRAS'::text, project.gras_project_code) AS project_id,
                   dsp_id(campaign.source::text, campaign.external_id) AS campaign_id
            FROM "Project" project
            LEFT JOIN "ProjectCampaign" project_campaign ON project_campaign.project_id = project.id AND project_campaign.status in (0,1)
            JOIN "Campaign" campaign ON (
                campaign.external_id IS NOT NULL AND
                campaign.is_deleted IS FALSE AND
                project_campaign.campaign_id = campaign.id
            )
            WHERE project.is_deleted IS FALSE
            GROUP BY project.gras_project_code, campaign.source, campaign.external_id;
        """
    )
    # user_projects_view
    op.execute(
        """
            CREATE OR REPLACE VIEW user_projects_view AS
                SELECT dsp_id('GRAS'::text, project.gras_project_code) AS project_id,
                       dec_user.id AS user_id
                FROM "Project" project
                 LEFT JOIN "UserProject" user_project ON user_project.project_id = project.id
                 JOIN "User" 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 project.gras_project_code, dec_user.id;
        """
    )


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

    # campaigns view
    op.execute(
        """
            CREATE OR REPLACE VIEW projects_view AS
                 SELECT dsp_id(campaign.source::text, campaign.external_id) AS id,
                       campaign.name,
                       campaign.planned_budget                             AS budget,
                       campaign.budget_spend                               AS spend,
                       campaign.provider_id,
                       campaign.start_date,
                       COALESCE(campaign.end_date,
                                CASE
                                    WHEN NOT gras_status.is_pending THEN project.end_date
                                    ELSE NULL::date
                                    END)                                   AS 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" campaign
                         LEFT JOIN (SELECT "ProjectCampaign".campaign_id,
                                           min("ProjectCampaign".project_id) AS gras_project_id,
                                           min("ProjectCampaign".status) = 0 AS is_pending
                                    FROM "ProjectCampaign"
                                    WHERE "ProjectCampaign".status <> 2
                                    GROUP BY "ProjectCampaign".campaign_id) gras_status ON gras_status.campaign_id = campaign.id
                         LEFT JOIN "Project" project ON project.gras_project_id = gras_status.gras_project_id
                         JOIN "MarketingAccount" marketing_account
                              ON marketing_account.external_id::text = campaign.external_marketing_account_id::text
                         JOIN (SELECT cp.id        AS campaign_id,
                                      ctv.id       AS sub_category_id,
                                      ctv.group_id AS category_id
                               FROM "Campaign" 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) 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);
        """
    )

    # phases_view
    op.execute(
        """
            CREATE OR REPLACE VIEW phases_view AS
                SELECT project_phase.id,
                       project_phase.name,
                       dsp_id('GRAS'::text, gras_project.external_id) AS project_id,
                       project_phase.start_date,
                       GREATEST(COALESCE(
                            CASE
                            WHEN lead(project_phase.project_id) OVER (ORDER BY project_phase.project_id, project_phase."order") = project_phase.project_id THEN lead(project_phase.start_date - '1 day'::interval) OVER (ORDER BY project_phase.project_id, project_phase."order")
                            ELSE NULL::timestamp without time zone
                            END, project.end_date::timestamp without time zone)::date, project_phase.start_date
                       ) AS end_date
            FROM "ProjectPhase" project_phase
            JOIN "Project" project ON project.id = project_phase.project_id AND project.is_deleted IS FALSE
            JOIN "GRASProject" 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;
        """
    )
    # phase_campaigns_view
    op.execute(
        """
            CREATE OR REPLACE VIEW phase_campaigns_view AS
                SELECT dsp_id(campaign.source::text, campaign.external_id) AS campaign_id,
                    project_phases.id AS phase_id
                FROM phases_view project_phases
                JOIN "GRASProject" gras_project ON project_phases.project_id = dsp_id('GRAS'::text, gras_project.external_id)
                JOIN "Project" project ON project.gras_project_id = gras_project.id AND project.is_deleted IS FALSE
                LEFT JOIN "ProjectCampaign" project_campaign ON gras_project.id = project_campaign.project_id AND project_campaign.status = 0
                LEFT JOIN "Campaign" campaign ON (project.id = campaign.project_id OR project_campaign.campaign_id = campaign.id) AND campaign.is_deleted = false AND daterange(project_phases.start_date, project_phases.end_date, '[]'::text) @> 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;
        """
    )
    # project_artists_view
    op.execute(
        """
            CREATE OR REPLACE VIEW project_artists_view AS
                SELECT dsp_id('GRAS'::text, gras_project.external_id) AS project_id,
                       artist.external_id AS artist_id
                FROM "Project" project
                JOIN "GRASProject" gras_project ON project.gras_project_id = gras_project.id
                JOIN "ProjectTargetItem" 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 = ANY (ARRAY[0::bigint, 1::bigint]))
                JOIN "Polymorphable" polymorphable ON polymorphable.id = project_target_item.entity_id
                JOIN "Artist" artist ON artist.id = polymorphable.id
                WHERE project.is_deleted IS FALSE
                GROUP BY gras_project.external_id, artist.external_id;
        """
    )
    # project_campaigns_view
    op.execute(
        """
        CREATE OR REPLACE VIEW project_campaigns_view AS
            SELECT dsp_id('GRAS'::text, gras_project.external_id) AS project_id,
                   dsp_id(campaign.source::text, campaign.external_id) AS campaign_id
            FROM "Project" project
            JOIN "GRASProject" gras_project ON project.gras_project_id = gras_project.id
            LEFT JOIN "ProjectCampaign" gras_project_campaign ON gras_project_campaign.project_id = gras_project.id AND gras_project_campaign.status = 0
            JOIN "Campaign" 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;
        """
    )

    # user_projects_view
    op.execute(
        """
            CREATE OR REPLACE VIEW user_projects_view AS
                SELECT dsp_id('GRAS'::text, gras_project.external_id) AS project_id,
                       dec_user.id AS user_id
                FROM "Project" project
                 JOIN "GRASProject" gras_project ON gras_project.id = project.gras_project_id
                 LEFT JOIN "UserProject" user_project ON user_project.project_id = project.id
                 JOIN "User" 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;
        """
    )