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


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

    def on_insert(self):
        return f"""
            IF EXISTS ({self.__select_project('1')}) THEN
                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")});
                IF EXISTS ({self.__user_projects_query(columns='1', state="OLD")}) THEN
                    INSERT INTO user_projects(project_id, user_id)
                        {self.__user_projects_query(state="OLD")};
                END IF;
            END IF;
        """

    def __select_project(self, columns="dsp_id('PRS'::text, project.prs_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
        """

    def __user_projects_query(
        self,
        columns="dsp_id('PRS'::text, project.prs_project_code) AS project_id, dec_user.id AS user_id",
        state='NEW'
    ):
        return f"""
            SELECT {columns}
            FROM "Project" project
            LEFT JOIN "UserProject" user_project ON user_project.project_id = project.id
            JOIN "User" dec_user ON dec_user.id = user_project.user_id OR dec_user.id = project.owner_id
            WHERE project.is_deleted IS FALSE AND project.id = {state}.project_id
            GROUP BY project.prs_project_code, dec_user.id
        """