class DelphiViews:
    def __init__(self, bind):
        self.bind = bind

    def create(self, prefix = None):
        views = [
            self.projects_view,
            self.user_labels_view,
            self.user_projects_view,
            self.project_campaigns_view,
            self.project_artists_view,
            self.phases_view,
            self.phase_campaigns_view,
            self.decibel_labels_view,
            self.campaigns_view,
            self.campaign_sub_category_view,
            self.campaign_provider_view,
            self.campaign_platforms_view,
            self.campaign_objective_view,
            self.campaign_category_view
        ]
        for view in views:
            self.bind.execute(view(prefix=prefix))

    def drop(self, prefix = None):
        self.bind.execute(
            f"""
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'user_labels_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'decibel_labels_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'projects_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'project_artists_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'phase_campaigns_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'phases_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'project_campaigns_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'user_projects_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'campaigns_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'campaign_category_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'campaign_sub_category_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'campaign_provider_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'campaign_objective_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'campaign_platforms_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'linkfire_link_project_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'linkfire_link_campaign_view')};
            """
        )

    def __view_name(self, prefix, table_name):
        if not prefix:
            return table_name
        return f"{prefix}_{table_name}"

    def projects_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'projects_view')} AS
                SELECT
                    dsp_id('PRS'::text, project.prs_project_code) AS id,
                    dsp_id('GRAS'::text, project.gras_project_code) AS gras_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;
        """

    def user_labels_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'user_labels_view')} AS
                SELECT user_label.user_id,
                       user_label.label_id AS decibel_label_id
                FROM "UserLabel" user_label;
        """

    def user_projects_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'user_projects_view')} AS
                SELECT dsp_id('PRS'::text, project.prs_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.prs_project_code, dec_user.id;
        """

    def project_campaigns_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'project_campaigns_view')} AS
                SELECT dsp_id('PRS'::text, project.prs_project_code) AS project_id,
                       campaign_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 = ANY (ARRAY[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 OR project.id = campaign.project_id)
                WHERE project.is_deleted IS FALSE AND project.owner_id IS NOT NULL
                GROUP BY project.prs_project_code, campaign.source, campaign.external_id;
        """

    def project_artists_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'project_artists_view')} AS
                SELECT dsp_id('PRS'::text, project.prs_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 = 0
                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 AND project.owner_id IS NOT NULL
                GROUP BY project.prs_project_code, artist.external_id;
        """

    def phases_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'phases_view')} AS
                SELECT project_phase.id,
                       project_phase.name,
                       dsp_id('PRS'::text, project.prs_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.prs_project_code,
                         project_phase.id,
                         project_phase.name,
                         project_phase.start_date,
                         project.end_date;
        """

    def phase_campaigns_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'phase_campaigns_view')} AS
                SELECT campaign_dsp_id(campaign.source::text, campaign.external_id) AS campaign_id,
                       project_phases.id AS phase_id
                FROM {self.__view_name(prefix, 'phases_view')} project_phases
                JOIN "Project" project ON
                    dsp_id('PRS'::text, project.prs_project_code) = project_phases.project_id
                    AND project.is_deleted IS FALSE AND project.owner_id IS NOT NULL
                LEFT JOIN "ProjectCampaign" project_campaign ON project.id = project_campaign.project_id
                    AND (project_campaign.status = ANY (ARRAY[0, 1]))
                LEFT JOIN "Campaign" campaign ON (project_campaign.campaign_id = campaign.id OR project.id = campaign.project_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;
        """

    def decibel_labels_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'decibel_labels_view')} AS
                SELECT label.id, label.name, label.rep_owner_key
                FROM "Label" label;
        """

    def campaigns_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'campaigns_view')} AS
                SELECT campaign_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,
                    COALESCE(marketing_account.label_id, project.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 OR campaign.project_id = project.id
                LEFT 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);
        """

    def campaign_sub_category_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'campaign_sub_category_view')} AS
                SELECT ctv.id,
                       ctv.name
                FROM "CampaignTypeValue" ctv
                WHERE ctv.group_id IS NOT NULL;
        """

    def campaign_provider_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'campaign_provider_view')} AS
                SELECT cp.id, cp.name
                FROM "CampaignProvider" as cp;
        """

    def campaign_platforms_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'campaign_platforms_view')} AS
                SELECT campaign_dsp_id(campaign.source::text, campaign.external_id) AS campaign_id,
                       campaign_type_value.name
                FROM "Campaign" campaign
                JOIN "CampaignType" campaign_type ON campaign_type.campaign_id = campaign.id
                JOIN "CampaignTypeCategory" campaign_type_category ON
                    campaign_type_category.id = campaign_type.category_id
                    AND campaign_type_category.name::text = 'Platforms'::text
                JOIN "CampaignType" platform_campaign_type ON platform_campaign_type.campaign_id = campaign.id
                    AND platform_campaign_type.category_id = campaign_type_category.id
                JOIN "CampaignTypeValue" 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;
        """

    def campaign_objective_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'campaign_objective_view')} AS
                SELECT co.id,
                       co.name
                FROM "CampaignObjective" co;
        """

    def campaign_category_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'campaign_category_view')} AS
                SELECT ctg.id, ctg.name
                FROM "CampaignTypeGroup" ctg;
        """

    def linkfire_project_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'linkfire_link_project_view')} AS
                SELECT dsp_id('PRS', project.prs_project_code) AS project_id,
                       linkfire_link.link_id AS link_id
                FROM "LinkfireProject" AS linkfire_project
                JOIN "Linkfire" AS linkfire_link ON linkfire_project.linkfire_id = linkfire_link.id
                JOIN "Project" AS project ON linkfire_project.project_id = project.id
                WHERE project.is_deleted IS FALSE
                GROUP BY project.prs_project_code, linkfire_link.link_id;
        """

    def linkfire_campaign_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'linkfire_link_campaign_view')} AS
                SELECT campaign_dsp_id(campaign.source::text, campaign.external_id) AS campaign_id,
                       linkfire_link.link_id AS link_id
                FROM "LinkfireCampaign" AS linkfire_campaign
                JOIN "Linkfire" AS linkfire_link ON linkfire_campaign.linkfire_id = linkfire_link.id
                JOIN "Campaign" AS campaign ON linkfire_campaign.campaign_id = campaign.id
                WHERE campaign.external_id IS NOT NULL
                    AND campaign.is_deleted IS FALSE
                GROUP BY campaign.source, campaign.external_id, linkfire_link.link_id;
        """


class DelphiViewsV2(DelphiViews):

    def create(self, prefix = None):
        views = [
            self.projects_view,
            self.user_labels_view,
            self.user_projects_view,
            self.project_campaigns_view,
            self.project_artists_view,
            self.phases_view,
            self.phase_campaigns_view,
            self.decibel_labels_view,
            self.campaigns_view,
            self.campaign_sub_category_view,
            self.campaign_provider_view,
            self.campaign_platforms_view,
            self.campaign_objective_view,
            self.campaign_category_view,
            self.linkfire_campaign_view,
            self.linkfire_project_view
        ]
        for view in views:
            self.bind.execute(view(prefix=prefix))

    def projects_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'projects_view')} AS
                SELECT
                    dsp_id('PRS'::text, project.prs_project_code) AS id,
                    dsp_id('GRAS'::text, project.gras_project_code) AS gras_id,
                    project.name,
                    project.budget,
                    project.label_id AS decibel_label_id,
                    project.is_confidential,
                    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;
        """

    def user_projects_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'user_projects_view')} AS
                SELECT dsp_id('PRS'::text, project.prs_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
                LEFT JOIN "ProjectTargetItem" project_target_item ON (
                  project_target_item.project_id = project.id AND
                  project_target_item.entity_type = 0 AND
                  project_target_item.is_deleted is false AND
                  project_target_item.add_type = 2 AND
                  project.is_confidential is false
                )
                LEFT JOIN "Artist" as artist ON (artist.id = project_target_item.entity_id AND artist.is_unknown IS FALSE)
                LEFT JOIN "ArtistTeam" as artist_team ON (artist_team.artist_id = artist.id)
                LEFT JOIN "ArtistTeamUser" as artist_team_user ON artist_team.id = artist_team_user.artist_team_id
                JOIN "User" dec_user ON (
                  dec_user.id = user_project.user_id OR
                  dec_user.id = project.owner_id OR
                  (dec_user.id = artist_team_user.user_id AND project.is_confidential is false)
                )
                WHERE project.is_deleted IS FALSE
                GROUP BY project.prs_project_code, dec_user.id;
        """

    def campaigns_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'campaigns_view')} AS
                SELECT campaign_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,
                    COALESCE(project.label_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 OR campaign.project_id = project.id
                LEFT 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);
        """

    def __view_name(self, prefix, table_name):
        if not prefix:
            return table_name
        return f"{prefix}_{table_name}"


class DelphiViewsV3(DelphiViews):

    def create(self, prefix = None):
        views = [
            self.projects_view,
            self.user_labels_view,
            self.user_projects_view,
            self.project_campaigns_view,
            self.project_artists_view,
            self.phases_view,
            self.phase_campaigns_view,
            self.decibel_labels_view,
            self.campaigns_view,
            self.campaign_sub_category_view,
            self.campaign_provider_view,
            self.campaign_platforms_view,
            self.campaign_objective_view,
            self.campaign_category_view,
            self.linkfire_campaign_view,
            self.linkfire_project_view
        ]
        for view in views:
            self.bind.execute(view(prefix=prefix))

    def projects_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'projects_view')} AS
                SELECT
                    dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_project_code)) AS id,
                    dsp_id('GRAS'::text, project.gras_project_code) AS gras_id,
                    project.name,
                    project.budget,
                    project.label_id AS decibel_label_id,
                    project.is_confidential,
                    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;
        """

    def user_projects_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'user_projects_view')} AS
                SELECT dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_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
                LEFT JOIN "ProjectTargetItem" project_target_item ON (
                  project_target_item.project_id = project.id AND
                  project_target_item.entity_type = 0 AND
                  project_target_item.is_deleted is false AND
                  project_target_item.add_type = 2 AND
                  project.is_confidential is false
                )
                LEFT JOIN "Artist" as artist ON (artist.id = project_target_item.entity_id AND artist.is_unknown IS FALSE)
                LEFT JOIN "ArtistTeam" as artist_team ON (artist_team.artist_id = artist.id)
                LEFT JOIN "ArtistTeamUser" as artist_team_user ON artist_team.id = artist_team_user.artist_team_id
                JOIN "User" dec_user ON (
                  dec_user.id = user_project.user_id OR
                  dec_user.id = project.owner_id OR
                  (dec_user.id = artist_team_user.user_id AND project.is_confidential is false)
                )
                WHERE project.is_deleted IS FALSE
                GROUP BY project.prs_project_code, dec_user.id, project.source, project.ccp_project_code;
        """

    def campaigns_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'campaigns_view')} AS
                SELECT campaign_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,
                    COALESCE(project.label_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 OR campaign.project_id = project.id
                LEFT 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);
        """

    def project_campaigns_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'project_campaigns_view')} AS
                SELECT dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_project_code)) AS project_id,
                       campaign_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 = ANY (ARRAY[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 OR project.id = campaign.project_id)
                WHERE project.is_deleted IS FALSE AND project.owner_id IS NOT NULL
                GROUP BY project.prs_project_code, campaign.source, campaign.external_id, project.source, project.ccp_project_code;
        """

    def project_artists_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'project_artists_view')} AS
                SELECT dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_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 = 0
                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 AND project.owner_id IS NOT NULL
                GROUP BY project.prs_project_code, artist.external_id, project.source, project.ccp_project_code;
        """

    def phases_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'phases_view')} AS
                SELECT project_phase.id,
                       project_phase.name,
                       dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_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.prs_project_code,
                         project.ccp_project_code,
                         project.source,
                         project_phase.id,
                         project_phase.name,
                         project_phase.start_date,
                         project.end_date;
        """

    def phase_campaigns_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'phase_campaigns_view')} AS
                SELECT campaign_dsp_id(campaign.source::text, campaign.external_id) AS campaign_id,
                       project_phases.id AS phase_id
                FROM {self.__view_name(prefix, 'phases_view')} project_phases
                JOIN "Project" project ON
                    dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_project_code)) = project_phases.project_id
                    AND project.is_deleted IS FALSE AND project.owner_id IS NOT NULL
                LEFT JOIN "ProjectCampaign" project_campaign ON project.id = project_campaign.project_id
                    AND (project_campaign.status = ANY (ARRAY[0, 1]))
                LEFT JOIN "Campaign" campaign ON (project_campaign.campaign_id = campaign.id OR project.id = campaign.project_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;
        """

    def linkfire_project_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'linkfire_link_project_view')} AS
                SELECT dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_project_code)) AS project_id,
                       linkfire_link.link_id AS link_id
                FROM "LinkfireProject" AS linkfire_project
                JOIN "Linkfire" AS linkfire_link ON linkfire_project.linkfire_id = linkfire_link.id
                JOIN "Project" AS project ON linkfire_project.project_id = project.id
                WHERE project.is_deleted IS FALSE
                GROUP BY project.prs_project_code, project.source, project.ccp_project_code, linkfire_link.link_id;
        """

    def __view_name(self, prefix, table_name):
        if not prefix:
            return table_name
        return f"{prefix}_{table_name}"


class DelphiViewsV4(DelphiViews):

    def create(self, prefix = None):
        views = [
            self.projects_view,
            self.user_labels_view,
            self.user_projects_view,
            self.project_campaigns_view,
            self.project_artists_view,
            self.phases_view,
            self.phase_campaigns_view,
            self.decibel_labels_view,
            self.campaigns_view,
            self.campaign_sub_category_view,
            self.campaign_provider_view,
            self.campaign_platforms_view,
            self.campaign_objective_view,
            self.campaign_category_view,
            self.linkfire_campaign_view,
            self.linkfire_project_view
        ]
        for view in views:
            self.bind.execute(view(prefix=prefix))

    def projects_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'projects_view')} AS
                SELECT
                    dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_project_code)) AS id,
                    dsp_id('GRAS'::text, project.gras_project_code) AS gras_id,
                    project.name,
                    project.budget,
                    project.label_id AS decibel_label_id,
                    project.is_confidential,
                    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;
        """

    def user_projects_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'user_projects_view')} AS
                SELECT dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_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
                LEFT JOIN "ProjectTargetItem" project_target_item ON (
                  project_target_item.project_id = project.id AND
                  project_target_item.entity_type = 0 AND
                  project_target_item.is_deleted is false AND
                  project_target_item.add_type = 2 AND
                  project.is_confidential is false
                )
                LEFT JOIN "Artist" as artist ON (artist.id = project_target_item.entity_id AND artist.is_unknown IS FALSE)
                LEFT JOIN "ArtistTeam" as artist_team ON (artist_team.artist_id = artist.id)
                LEFT JOIN "ArtistTeamUser" as artist_team_user ON artist_team.id = artist_team_user.artist_team_id
                JOIN "User" dec_user ON (
                  dec_user.id = user_project.user_id OR
                  dec_user.id = project.owner_id OR
                  (dec_user.id = artist_team_user.user_id AND project.is_confidential is false)
                )
                WHERE project.is_deleted IS FALSE
                GROUP BY project.prs_project_code, dec_user.id, project.source, project.ccp_project_code;
        """

    def campaigns_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'campaigns_view')} AS
                SELECT campaign_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,
                    COALESCE(project.label_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 OR campaign.project_id = project.id
                LEFT JOIN "MarketingAccount" marketing_account ON
                    marketing_account.external_id::text = campaign.external_marketing_account_id::text
                JOIN (
                    SELECT campaign.id AS campaign_id,
                           ct.id AS sub_category_id,
                           ct.group_id AS category_id
                    FROM "Campaign" campaign
                    LEFT JOIN "CampaignTypes" as ct on ct.id = campaign.type_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);
        """

    def project_campaigns_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'project_campaigns_view')} AS
                SELECT dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_project_code)) AS project_id,
                       campaign_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 = ANY (ARRAY[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 OR project.id = campaign.project_id)
                WHERE project.is_deleted IS FALSE AND project.owner_id IS NOT NULL
                GROUP BY project.prs_project_code, campaign.source, campaign.external_id, project.source, project.ccp_project_code;
        """

    def project_artists_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'project_artists_view')} AS
                SELECT dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_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 = 0
                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 AND project.owner_id IS NOT NULL
                GROUP BY project.prs_project_code, artist.external_id, project.source, project.ccp_project_code;
        """

    def phases_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'phases_view')} AS
                SELECT project_phase.id,
                       project_phase.name,
                       dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_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.prs_project_code,
                         project.ccp_project_code,
                         project.source,
                         project_phase.id,
                         project_phase.name,
                         project_phase.start_date,
                         project.end_date;
        """

    def phase_campaigns_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'phase_campaigns_view')} AS
                SELECT campaign_dsp_id(campaign.source::text, campaign.external_id) AS campaign_id,
                       project_phases.id AS phase_id
                FROM {self.__view_name(prefix, 'phases_view')} project_phases
                JOIN "Project" project ON
                    dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_project_code)) = project_phases.project_id
                    AND project.is_deleted IS FALSE AND project.owner_id IS NOT NULL
                LEFT JOIN "ProjectCampaign" project_campaign ON project.id = project_campaign.project_id
                    AND (project_campaign.status = ANY (ARRAY[0, 1]))
                LEFT JOIN "Campaign" campaign ON (project_campaign.campaign_id = campaign.id OR project.id = campaign.project_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;
        """

    def linkfire_project_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'linkfire_link_project_view')} AS
                SELECT dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_project_code)) AS project_id,
                       linkfire_link.link_id AS link_id
                FROM "LinkfireProject" AS linkfire_project
                JOIN "Linkfire" AS linkfire_link ON linkfire_project.linkfire_id = linkfire_link.id
                JOIN "Project" AS project ON linkfire_project.project_id = project.id
                WHERE project.is_deleted IS FALSE
                GROUP BY project.prs_project_code, project.source, project.ccp_project_code, linkfire_link.link_id;
        """

    def campaign_platforms_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'campaign_platforms_view')} AS
                SELECT campaign_dsp_id(campaign.source::text, campaign.external_id) AS campaign_id,
                       campaign_platforms.name
                FROM "Campaign" campaign
                JOIN "CampaignPlatformsLinks" cpl ON cpl.campaign_id = campaign.id
                JOIN "CampaignPlatforms" campaign_platforms ON cpl.platform_id = campaign_platforms.id
                WHERE campaign.external_id IS NOT NULL AND campaign.is_deleted IS FALSE
                GROUP BY campaign.id, campaign_platforms.name;
        """

    def campaign_sub_category_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'campaign_sub_category_view')} AS
                SELECT ctv.id,
                       ctv.name
                FROM "CampaignTypes" ctv
                WHERE ctv.group_id IS NOT NULL;
        """

    def __view_name(self, prefix, table_name):
        if not prefix:
            return table_name
        return f"{prefix}_{table_name}"


class DelphiViewsV5(DelphiViews):
    def create(self, prefix=None):
        views = [
            self.projects_view,
            self.user_labels_view,
            self.user_projects_view,
            self.project_campaigns_view,
            self.project_artists_view,
            self.phases_view,
            self.phase_campaigns_view,
            self.decibel_labels_view,
            self.campaigns_view,
            self.campaign_sub_category_view,
            self.campaign_provider_view,
            self.campaign_platforms_view,
            self.campaign_objective_view,
            self.campaign_category_view,
            self.linkfire_campaign_view,
            self.linkfire_project_view
        ]
        for view in views:
            self.bind.execute(view(prefix=prefix))

    def projects_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'projects_view')} AS
                SELECT
                    dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_project_code)) AS id,
                    dsp_id('GRAS'::text, project.gras_project_code) AS gras_id,
                    project.name,
                    project.budget,
                    project.label_id AS decibel_label_id,
                    project.is_confidential,
                    project.initial_start_date as start_date,
                    project.end_date
                FROM "Project" project
                WHERE project.is_deleted IS FALSE AND project.owner_id IS NOT NULL;
        """

    def user_projects_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'user_projects_view')} AS
                SELECT dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_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
                LEFT JOIN "ProjectTargetItem" project_target_item ON (
                  project_target_item.project_id = project.id AND
                  project_target_item.entity_type = 0 AND
                  project_target_item.is_deleted is false AND
                  project_target_item.add_type = 2 AND
                  project.is_confidential is false
                )
                LEFT JOIN "Artist" as artist ON (artist.id = project_target_item.entity_id AND artist.is_unknown IS FALSE)
                LEFT JOIN "ArtistTeam" as artist_team ON (artist_team.artist_id = artist.id)
                LEFT JOIN "ArtistTeamUser" as artist_team_user ON artist_team.id = artist_team_user.artist_team_id
                JOIN "User" dec_user ON (
                  dec_user.id = user_project.user_id OR
                  dec_user.id = project.owner_id OR
                  (dec_user.id = artist_team_user.user_id AND project.is_confidential is false)
                )
                WHERE project.is_deleted IS FALSE
                GROUP BY project.prs_project_code, dec_user.id, project.source, project.ccp_project_code;
        """

    def campaigns_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'campaigns_view')} AS
                SELECT campaign_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,
                    COALESCE(project.label_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 OR campaign.project_id = project.id
                LEFT JOIN "MarketingAccount" marketing_account ON
                    marketing_account.external_id::text = campaign.external_marketing_account_id::text
                JOIN (
                    SELECT campaign.id AS campaign_id,
                           ct.id AS sub_category_id,
                           ct.group_id AS category_id
                    FROM "Campaign" campaign
                    LEFT JOIN "CampaignTypes" as ct on ct.id = campaign.type_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);
        """

    def project_campaigns_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'project_campaigns_view')} AS
                SELECT dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_project_code)) AS project_id,
                       campaign_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 = ANY (ARRAY[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 OR project.id = campaign.project_id)
                WHERE project.is_deleted IS FALSE AND project.owner_id IS NOT NULL
                GROUP BY project.prs_project_code, campaign.source, campaign.external_id, project.source, project.ccp_project_code;
        """

    def project_artists_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'project_artists_view')} AS
                SELECT dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_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 = 0
                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 AND project.owner_id IS NOT NULL
                GROUP BY project.prs_project_code, artist.external_id, project.source, project.ccp_project_code;
        """

    def phases_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'phases_view')} AS
                SELECT project_phase.id,
                       project_phase.name,
                       dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_project_code)) AS project_id
                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.prs_project_code,
                         project.ccp_project_code,
                         project.source,
                         project_phase.id,
                         project_phase.name;
        """

    def phase_campaigns_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'phase_campaigns_view')} AS
                SELECT campaign_dsp_id(campaign.source::text, campaign.external_id) AS campaign_id,
                       project_phases.id AS phase_id
                FROM {self.__view_name(prefix, 'phases_view')} project_phases
                JOIN "Project" project ON
                    dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_project_code)) = project_phases.project_id
                    AND project.is_deleted IS FALSE AND project.owner_id IS NOT NULL
                LEFT JOIN "ProjectCampaign" project_campaign ON project.id = project_campaign.project_id
                    AND (project_campaign.status = ANY (ARRAY[0, 1]))
                LEFT JOIN "Campaign" campaign ON (project_campaign.campaign_id = campaign.id OR project.id = campaign.project_id)
                    AND campaign.is_deleted = false
                    AND project_phases.id = campaign.project_phase_id
                WHERE campaign.id IS NOT NULL
                    AND campaign.external_id IS NOT NULL
                    AND project_phases.id IS NOT NULL
                GROUP BY campaign.source, campaign.external_id, project_phases.id;
        """

    def linkfire_project_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'linkfire_link_project_view')} AS
                SELECT dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_project_code)) AS project_id,
                       linkfire_link.link_id AS link_id
                FROM "LinkfireProject" AS linkfire_project
                JOIN "Linkfire" AS linkfire_link ON linkfire_project.linkfire_id = linkfire_link.id
                JOIN "Project" AS project ON linkfire_project.project_id = project.id
                WHERE project.is_deleted IS FALSE
                GROUP BY project.prs_project_code, project.source, project.ccp_project_code, linkfire_link.link_id;
        """

    def campaign_platforms_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'campaign_platforms_view')} AS
                SELECT campaign_dsp_id(campaign.source::text, campaign.external_id) AS campaign_id,
                       campaign_platforms.name
                FROM "Campaign" campaign
                JOIN "CampaignPlatformsLinks" cpl ON cpl.campaign_id = campaign.id
                JOIN "CampaignPlatforms" campaign_platforms ON cpl.platform_id = campaign_platforms.id
                WHERE campaign.external_id IS NOT NULL AND campaign.is_deleted IS FALSE
                GROUP BY campaign.id, campaign_platforms.name;
        """

    def campaign_sub_category_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'campaign_sub_category_view')} AS
                SELECT ctv.id,
                       ctv.name
                FROM "CampaignTypes" ctv
                WHERE ctv.group_id IS NOT NULL;
        """

    def __view_name(self, prefix, table_name):
        if not prefix:
            return table_name
        return f"{prefix}_{table_name}"


class DelphiViewsV6(DelphiViewsV5):
    def create(self, prefix=None):
        views = [
            self.projects_view,
            self.user_labels_view,
            self.user_projects_view,
            self.project_campaigns_view,
            self.project_artists_view,
            self.decibel_labels_view,
            self.campaigns_view,
            self.campaign_sub_category_view,
            self.campaign_provider_view,
            self.campaign_platforms_view,
            self.campaign_objective_view,
            self.campaign_category_view,
            self.linkfire_campaign_view,
            self.linkfire_project_view
        ]
        for view in views:
            self.bind.execute(view(prefix=prefix))


class DelphiViewsV7(DelphiViewsV6):
    def projects_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'projects_view')} AS
                SELECT
                    dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_project_code)) AS id,
                    dsp_id('GRAS'::text, project.gras_project_code) AS gras_id,
                    project.name,
                    project.budget,
                    project.label_id AS decibel_label_id,
                    project.is_confidential,
                    project.initial_start_date as start_date,
                    project.end_date
                FROM "Project" project
                WHERE project.is_deleted IS FALSE AND project.is_claimed is TRUE;
        """

    def user_projects_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'user_projects_view')} AS
                SELECT dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_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
                LEFT JOIN "ProjectTargetItem" project_target_item ON (
                  project_target_item.project_id = project.id AND
                  project_target_item.entity_type = 0 AND
                  project_target_item.is_deleted is false AND
                  project_target_item.add_type = 2 AND
                  project.is_confidential is false
                )
                LEFT JOIN "Artist" as artist ON (artist.id = project_target_item.entity_id AND artist.is_unknown IS FALSE)
                LEFT JOIN "ArtistTeam" as artist_team ON (artist_team.artist_id = artist.id)
                LEFT JOIN "ArtistTeamUser" as artist_team_user ON artist_team.id = artist_team_user.artist_team_id
                JOIN "User" dec_user ON (
                  dec_user.id = user_project.user_id OR
                  (dec_user.id = artist_team_user.user_id AND project.is_confidential is false)
                )
                WHERE project.is_deleted IS FALSE
                GROUP BY project.prs_project_code, dec_user.id, project.source, project.ccp_project_code;
        """

    def project_campaigns_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'project_campaigns_view')} AS
                SELECT dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_project_code)) AS project_id,
                       campaign_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 = ANY (ARRAY[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 OR project.id = campaign.project_id)
                WHERE project.is_deleted IS FALSE AND project.is_claimed IS TRUE
                GROUP BY project.prs_project_code, campaign.source, campaign.external_id, project.source, project.ccp_project_code;
        """

    def project_artists_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'project_artists_view')} AS
                SELECT dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_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 = 0
                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 AND project.is_claimed IS TRUE
                GROUP BY project.prs_project_code, artist.external_id, project.source, project.ccp_project_code;
        """

    def create(self, prefix=None):
        views = [
            self.projects_view,
            self.user_labels_view,
            self.user_projects_view,
            self.project_campaigns_view,
            self.project_artists_view,
            self.decibel_labels_view,
            self.campaigns_view,
            self.campaign_sub_category_view,
            self.campaign_provider_view,
            self.campaign_platforms_view,
            self.campaign_objective_view,
            self.campaign_category_view,
            self.linkfire_campaign_view,
            self.linkfire_project_view
        ]
        for view in views:
            self.bind.execute(view(prefix=prefix))

    def __view_name(self, prefix, table_name):
        if not prefix:
            return table_name
        return f"{prefix}_{table_name}"


class DelphiViewsV8(DelphiViewsV7):

    def create(self, prefix=None):
        views = [
            self.projects_view,
            self.user_labels_view,
            self.user_projects_view,
            self.project_campaigns_view,
            self.project_artists_view,
            self.decibel_labels_view,
            self.campaigns_view,
            self.campaign_sub_category_view,
            self.campaign_provider_view,
            self.campaign_platforms_view,
            self.campaign_objective_view,
            self.campaign_category_view,
            self.linkfire_campaign_view,
            self.linkfire_project_view
        ]
        for view in views:
            self.bind.execute(view(prefix=prefix))


class DelphiViewsV9(DelphiViewsV8):

    def create(self, prefix=None):
        views = [
            self.projects_view,
            self.user_labels_view,
            self.user_projects_view,
            self.project_campaigns_view,
            self.project_artists_view,
            self.decibel_labels_view,
            self.campaigns_view,
            self.campaign_sub_category_view,
            self.campaign_provider_view,
            self.campaign_platforms_view,
            self.campaign_objective_view,
            self.campaign_category_view,
            self.linkfire_campaign_view,
            self.linkfire_project_view,
            self.connectors_view,
            self.ad_accounts_view
        ]
        for view in views:
            self.bind.execute(view(prefix=prefix))

    def drop(self, prefix = None):
        self.bind.execute(
            f"""
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'user_labels_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'decibel_labels_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'projects_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'project_artists_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'phase_campaigns_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'phases_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'project_campaigns_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'user_projects_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'campaigns_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'campaign_category_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'campaign_sub_category_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'campaign_provider_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'campaign_objective_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'campaign_platforms_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'linkfire_link_project_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'linkfire_link_campaign_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'connectors_view')};
                DROP VIEW IF EXISTS {self.__view_name(prefix, 'ad_accounts_view')};
            """
        )

    def __view_name(self, prefix, table_name):
        if not prefix:
            return table_name
        return f"{prefix}_{table_name}"

    def connectors_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'connectors_view')} AS
                SELECT connector.external_id AS id,
                       connector.name AS schema_name,
                       connector.platform AS platform,
                       connector.created_at :: DATE AS date_created,
                       connector.owner_id AS user_id,
                       connector.label_id AS decibel_label_id,
                       CASE
                           WHEN connector.status = 1 THEN TRUE
                           ELSE FALSE
                       END is_active,
                       connector.last_synced_at AS last_sync_at,
                       manager.email AS manager
                FROM "Connector" connector
                JOIN "User" owner ON connector.owner_id = owner.id
                LEFT JOIN "User" manager ON owner.manager_id = manager.id
        """

    def ad_accounts_view(self, prefix: str = None):
        return f"""
            CREATE OR REPLACE VIEW {self.__view_name(prefix, 'ad_accounts_view')} AS
                SELECT connector.external_id AS connector_id,
                       ad_account.ad_account_id AS ad_account_id
                FROM "AdAccount" ad_account
                JOIN "Connector" connector ON ad_account.connector_id = connector.id
        """