from migrations.fixtures.delphi_tables.v1.triggers.base_trigger import BaseTrigger
from migrations.fixtures.delphi_tables.v7.triggers.queries.user_projects_queries import UserProjectsQueries


class UserProjectTrigger(BaseTrigger):
    table = "UserProject"
    function_name = "log_user_project_changes"

    def on_insert(self):
        return f"""
            IF EXISTS ({self.__select_project('1')}) THEN
                DELETE FROM user_projects
                     WHERE user_projects.user_id = NEW.user_id
                           AND user_projects.project_id IN ({self.__select_project(state="NEW")});
                INSERT INTO user_projects(user_id, project_id)
                    VALUES(NEW.user_id, ({self.__select_project()}));
            END IF;
        """

    def on_delete(self):
        return f"""
            IF EXISTS ({self.__select_project(columns='1', state="OLD")}) THEN
                DELETE FROM user_projects
                    WHERE user_projects.project_id IN ({self.__select_project(state="OLD")});
                INSERT INTO user_projects(project_id, user_id)
                    {UserProjectsQueries.get_user_projects(
                        where_clause='project.is_deleted IS FALSE AND project.id = OLD.project_id'
                    )};
            END IF;
        """

    def __select_project(
        self,
        columns="dsp_id(project.source::text, 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.is_claimed IS TRUE AND project.id = {state}.project_id
        """