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


class LinkfireProjectTrigger(BaseTrigger):
    table = "LinkfireProject"
    function_name = "log_linkfire_project_changes"

    def on_insert(self):
        return f"""
            IF EXISTS({self.__select_project(columns='1', state='NEW')}) THEN
                INSERT INTO linkfire_link_project(project_id, link_id)
                    {self.__select_linkfire_project()};
            END IF;
        """

    def on_delete(self):
        return f"""
            IF EXISTS({self.__select_project(columns='1', state='OLD')}) THEN
                DELETE FROM linkfire_link_project
                    WHERE linkfire_link_project.project_id = ({self.__select_project(state='OLD')})
                        AND linkfire_link_project.link_id = ({self.__select_link_id(state="OLD")});
            END IF;
        """


    def __select_linkfire_project(
        self,
        columns="dsp_id('PRS', project.prs_project_code) AS project_id, linkfire_link.link_id::uuid AS link_id",
        state="NEW"
    ):
        return f"""
            SELECT {columns}
            FROM "LinkfireProject" AS linkfire_project
            JOIN "Linkfire" AS linkfire_link ON linkfire_project.linkfire_id = linkfire_link.id
            JOIN "Project" AS project ON linkfire_project.project_id = project.id
            WHERE project.is_deleted IS FALSE
                  AND project.id = {state}.project_id
                  AND linkfire_project.id = {state}.id
        """

    def __select_project(
        self,
        columns="id",
        state="NEW"
    ):
        return f"""
            SELECT {columns} FROM projects WHERE id = (
                SELECT dsp_id('PRS', project.prs_project_code)
                FROM "Project" as project WHERE project.id = {state}.project_id
            )
        """

    def __select_link_id(self, state="NEW"):
        return f"""
            SELECT linkfire.link_id::uuid FROM "Linkfire" linkfire WHERE linkfire.id = {state}.linkfire_id
        """