from migrations.fixtures.delphi_tables.v1.triggers.base_trigger import BaseTrigger
from migrations.fixtures.delphi_tables.v5.triggers.queries.phase_campaigns_query import PhaseCampaignsQuery


class ProjectPhaseTrigger(BaseTrigger):
    table = "ProjectPhase"
    function_name = "log_project_phase_changes"

    def on_insert(self):
        return f"""
           DELETE FROM phases WHERE phases.project_id IN (
              select dsp_id(source, coalesce(prs_project_code, ccp_project_code)) from "Project" WHERE id = NEW.project_id
           );
           insert into phases(id, name, project_id)
                {self.__select_query()};
           {self.__delete_phase_campaigns()};
           {self.__insert_phase_campaigns_query()};

        """

    def on_update(self):
        return f"""
           DELETE FROM phases WHERE phases.project_id = (
              select dsp_id(source, coalesce(prs_project_code, ccp_project_code)) from "Project" WHERE id = NEW.project_id
           );
           insert into phases(id, name, project_id)
                {self.__select_query()};

           {self.__delete_phase_campaigns()};
           {self.__insert_phase_campaigns_query()};
        """

    def on_delete(self):
        return f"""
           DELETE FROM phases WHERE phases.project_id IN (
              select dsp_id(source, coalesce(prs_project_code, ccp_project_code)) from "Project" WHERE id = OLD.project_id
           );

           insert into phases(id, name, project_id)
             {self.__select_query(where_clause="project.id = OLD.project_id")};

           {self.__delete_phase_campaigns(state="OLD")};
           {self.__insert_phase_campaigns_query(state="OLD")};
        """

    def __select_query(self, where_clause="project.id = NEW.project_id"):
        return f'''
            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
            )
            WHERE {where_clause}
            GROUP BY project.prs_project_code,
                     project_phase.id,
                     project_phase.name,
                     project.source,
                     project.ccp_project_code
        '''

    def __delete_phase_campaigns(self, state="NEW"):
        return f"""
            DELETE FROM phase_campaigns
                WHERE phase_id IN (
                    SELECT phase.id
                    FROM "ProjectPhase" phase
                    WHERE phase.project_id = {state}.project_id
                )
        """

    def __insert_phase_campaigns_query(self, state="NEW"):
        return f"""
            INSERT INTO phase_campaigns(campaign_id, phase_id)
                {PhaseCampaignsQuery.select_phase_campaigns(where_clause=f"project.id = {state}.project_id")}
        """