Delimiter //
/**
 * Author:  armen
 * Created: 13 déc. 2019
 * fournir entity = null pour traiter toutes les entités
 */

Drop Procedure if Exists BUSINESS_ATTACHED_MEMBERS_UPDATE_SEQUENCES//
Create Procedure BUSINESS_ATTACHED_MEMBERS_UPDATE_SEQUENCES(
    entity Int Unsigned
)
Begin

    bamus_main: Begin
        set @bamus_entity = entity;
        Drop Temporary Table If Exists TMP_ENT_ACC_ATT_MBR_PRF_SEQ;
        Create Temporary Table TMP_ENT_ACC_ATT_MBR_PRF_SEQ (
                ID_TMP_ENT_ACC_ATT_MBR_PRF_SEQ Smallint Not Null Primary Key AUTO_INCREMENT
                , ENTTS_ACC_ATTCHD_MBRS_PRFLS_ID_ENTTS_ACC_ATTCHD_MBRS_PRFLS Int Unsigned Not Null
                , OLD_ID_DF_ACCESS_PROFILES Int Unsigned Null
                , OLD_SEQUENCE Smallint 
                , Index (ENTTS_ACC_ATTCHD_MBRS_PRFLS_ID_ENTTS_ACC_ATTCHD_MBRS_PRFLS)
            )
        ;
        Insert Into TMP_ENT_ACC_ATT_MBR_PRF_SEQ (
                ENTTS_ACC_ATTCHD_MBRS_PRFLS_ID_ENTTS_ACC_ATTCHD_MBRS_PRFLS
                , OLD_ID_DF_ACCESS_PROFILES, OLD_SEQUENCE
            )
        Select
            eaamp1.ID_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES
            , dap1.ID_DF_ACCESS_PROFILES, dap1.DF_ACCESS_PROFILES_MNGT_LVL
        From
            ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES eaamp1

            Inner Join DF_ACCESS_PROFILES dap1
                On eaamp1.DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES = dap1.ID_DF_ACCESS_PROFILES
        Where
            eaamp1.ENTITIES_ID_ENTITIES = Coalesce(@bamus_entity, eaamp1.ENTITIES_ID_ENTITIES )
        Order By 
            dap1.DF_ACCESS_PROFILES_MNGT_LVL, eaamp1.ID_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES
        ;
        select * from TMP_ENT_ACC_ATT_MBR_PRF_SEQ;

        -- maj sequence en 2 etape pour eviter viol de contrainte d'unicité
        -- on met provisoirement une valeur négative
        Update ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES eaamp1
        Inner Join TMP_ENT_ACC_ATT_MBR_PRF_SEQ src
            On eaamp1.ID_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES = src.ENTTS_ACC_ATTCHD_MBRS_PRFLS_ID_ENTTS_ACC_ATTCHD_MBRS_PRFLS
        Set eaamp1.ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES_SEQUENCE = - eaamp1.ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES_SEQUENCE 
        Where eaamp1.ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES_SEQUENCE > 0
        ;
        -- mise à jour avec numero de sequence final
        Update ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES eaamp1
        Inner Join TMP_ENT_ACC_ATT_MBR_PRF_SEQ src
            On eaamp1.ID_ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES = src.ENTTS_ACC_ATTCHD_MBRS_PRFLS_ID_ENTTS_ACC_ATTCHD_MBRS_PRFLS
        Set eaamp1.ENTITIES_ACCNTS_ATTCHD_MBRS_PRFLES_SEQUENCE = src.ID_TMP_ENT_ACC_ATT_MBR_PRF_SEQ
        ;
        Drop Temporary Table If Exists TMP_ENT_ACC_ATT_MBR_PRF_SEQ;
        
    End;
End //
Delimiter ;



