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, 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.id, project_phase.id, project_phase.name, project_phase.start_date, 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
                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 AND
                  {where_clause}
            GROUP BY campaign.source, campaign.external_id, project_phases.id
        """