Delimiter $$
/**
 * UPDATE MEMBERS_DELEGATED_FOR_VIEW
 * this table is use inside V_MEMBERS_DELEGATED for better performance
 * @author Armen
 */ 

Drop PROCEDURE If Exists BUSINESS_UPDATE_MEMBER_DELECATED $$

CREATE PROCEDURE BUSINESS_UPDATE_MEMBER_DELECATED(
    In idMember Int Unsigned
    , In idMemberDelegated Int Unsigned
) 
Begin

    bumd_main: begin
        Set @bumd_idMember_minval = Coalesce(idMember, 0);
        Set @bumd_idMember_maxval = Coalesce(idMember, 1e6);
#         Set @bumd_idMember_minval = 0;
#         Set @bumd_idMember_maxval =  1e6;

        Set @bumd_idMemberDelegated_minval = Coalesce(idMemberDelegated, 0);
        Set @bumd_idMemberDelegated_maxval = Coalesce(idMemberDelegated, 1e6);
#         Set @bumd_idMemberDelegated_minval = 0;
#         Set @bumd_idMemberDelegated_maxval = 1e6;

        if @bumd_idMember_minval = 0 And @bumd_idMemberDelegated_minval = 0 Then
            truncate table MEMBERS_DELEGATED_FOR_VIEW;
        Else 
            Delete MEMBERS_DELEGATED_FOR_VIEW 
            From MEMBERS_DELEGATED_FOR_VIEW
            Where (ID_MEMBERS Between @bumd_idMember_minval And @bumd_idMember_maxval)
                And (MEMBERS_DELEGATED Between @bumd_idMemberDelegated_minval And @bumd_idMemberDelegated_maxval)
            ;
                
        End if;

        Set @bumd_refDate = curdate();


        Insert into MEMBERS_DELEGATED_FOR_VIEW (
            ID_MEMBERS
            , MEMBERS_DELEGATED
            , ON_TASKS
            , ON_REPORTS
            , ON_ACCESS
        )
# --     -- délégation sur les les équipes
#         SELECT
#             M.ID_MEMBERS
#             ,ThM.MEMBERS_ID_MEMBERS AS MEMBERS_DELEGATED
#             , TD.TEAMS_DELEGATIONS_TASKS AS ON_TASKS
#             , TD.TEAMS_DELEGATIONS_REPORT AS ON_REPORTS
#             , TD.TEAMS_DELEGATIONS_ACCESS AS ON_ACCESS
#         FROM
#             MEMBERS M
#         INNER JOIN
#             TEAMS_DELEGATIONS TD
#             ON TD.TEAMS_ID_MEMBERS_SOURCE = M.ID_MEMBERS
#             AND TD.TEAMS_DELEGATIONS_START_DATE <= DATE(NOW())
#             AND TD.TEAMS_DELEGATIONS_END_DATE >= DATE(NOW())
#         INNER JOIN
#             TEAMS_has_MEMBERS ThM
#             ON TD.TEAMS_ID_TEAMS_TARGET = ThM.TEAMS_ID_TEAMS
#         UNION
            -- délégation sur les les membres
            -- explain
            SELECT
                M.ID_MEMBERS
                , MD.MEMBERS_ID_MEMBERS_TARGET AS MEMBERS_DELEGATED
                , MD.MEMBERS_DELEGATIONS_TASKS AS ON_TASKS
                , MD.MEMBERS_DELEGATIONS_REPORT AS ON_REPORTS
                , MD.MEMBERS_DELEGATIONS_ACCESS AS ON_ACCESS
                FROM 
                    MEMBERS M
                INNER JOIN
                    MEMBERS_DELEGATIONS MD
                    ON MD.MEMBERS_ID_MEMBERS_SOURCE = M.ID_MEMBERS
#                     AND MD.MEMBERS_DELEGATIONS_START_DATE <= @bumd_refDate
#                     AND MD.MEMBERS_DELEGATIONS_END_DATE >= @bumd_refDate

                Where (MD.MEMBERS_ID_MEMBERS_SOURCE Between @bumd_idMember_minval And @bumd_idMember_maxval)
                    And (MD.MEMBERS_ID_MEMBERS_TARGET Between @bumd_idMemberDelegated_minval And @bumd_idMemberDelegated_maxval)
                    AND Coalesce(MD.MEMBERS_DELEGATIONS_START_DATE, @bumd_refDate) <= @bumd_refDate
                    AND Coalesce(NullIf(MD.MEMBERS_DELEGATIONS_END_DATE, '0000-00-00'), @bumd_refDate) >= @bumd_refDate
        UNION
            -- chaque memebre se délègue lui-même
            SELECT
                 MO.ID_MEMBERS
                , MO.ID_MEMBERS AS MEMBERS_DELEGATED
                ,1 AS ON_TASKS
                ,1 AS ON_REPORTS
                ,1 AS ON_ACCESS
            FROM 
                MEMBERS MO
            Where 
                (MO.ID_MEMBERS Between @bumd_idMember_minval And @bumd_idMember_maxval)
                And (MO.ID_MEMBERS  Between @bumd_idMemberDelegated_minval And @bumd_idMemberDelegated_maxval)
        ;
    end;
End $$

Delimiter ;