from migrations.fixtures.delphi_tables.v1.triggers.base_trigger import BaseTrigger


class ProjectTargetItemTrigger(BaseTrigger):
    table = "ProjectTargetItem"
    function_name = "log_project_target_item_changes"

    def on_insert(self):
        return f"""
            DELETE FROM project_artists WHERE project_id in ({self.__select_project()});
            INSERT INTO project_artists(project_id, artist_id)
                {self.select_project_artists()};
        """

    def on_update(self):
        return f"""
            DELETE FROM project_artists WHERE project_id in ({self.__select_project()});
            INSERT INTO project_artists(project_id, artist_id)
                {self.select_project_artists()};
        """

    def on_delete(self):
        return f"""
            DELETE FROM project_artists WHERE project_id in ({self.__select_project(state="OLD")});
            INSERT INTO project_artists(project_id, artist_id)
                {self.select_project_artists(state="OLD")};
        """

    def select_project_artists(
        self,
        columns = "dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_project_code)) AS project_id, artist.external_id AS artist_id",
        state = 'NEW'
    ):
        return f"""
            SELECT {columns}
            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 AND project.id = {state}.project_id
            GROUP BY project.prs_project_code, artist.external_id, project.source, project.ccp_project_code
        """

    def __select_project(self, columns="dsp_id(project.source, coalesce(project.prs_project_code, project.ccp_project_code)) as project_id", state='NEW'):
        return f"""
            SELECT {columns}
            FROM "Project" project
            WHERE project.is_deleted IS FALSE AND
                  project.owner_id IS NOT NULL AND
                  project.id = {state}.project_id
        """