class CampaignsQuery:

    @staticmethod
    def select_dsp_id_for_campaign(where_clause="campaign.id = NEW.campaign_id"):
        return f'SELECT campaign_dsp_id(campaign.source::text, campaign.external_id) FROM "Campaign" campaign WHERE {where_clause}'

    @staticmethod
    def select_campaign(where_clause="campaign.external_id = NEW.external_id"):
        return f"""
            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 project.id = campaign.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
                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
                  {where_clause} AND
                  campaign.is_deleted IS FALSE AND
                  (project.is_deleted = false OR project.id IS NULL)
        """