class PhaseCampaignsQuery:

    @staticmethod
    def select_phase_campaigns(
        state="NEW",
        columns="campaign_dsp_id(campaign.source::text, campaign.external_id) AS campaign_id, project_phases.id AS phase_id",
        where_clause="project.id = NEW.project_id AND campaign.id = NEW.campaign_id"
    ):
        return f"""
            SELECT {columns}
            FROM (
                SELECT project_phase.id, project.id 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.id, project_phase.id, project_phase.name, project.end_date
            ) project_phases
            JOIN "Project" project ON (
                project.id = 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 campaign.project_id = project.id) AND
                campaign.is_deleted = false AND
                campaign.project_phase_id = project_phases.id
            )
            WHERE campaign.id IS NOT NULL AND
                  campaign.external_id IS NOT NULL AND
                  project_phases.id IS NOT NULL AND
                  {where_clause}
            GROUP BY campaign.source, campaign.external_id, project_phases.id
        """