Delimiter //
/**
 * Copy ref data from one entity to another
 * - si inTransaction = 1 , la table TMP_ENTTCP_REFDATAS_SRC sera préservé pour utilisation exterieur
 * - si inTransaction = 0 , la table TMP_ENTTCP_REFDATAS_SRC sera préservé purgé
 * @author Armen
 */
Drop Procedure if Exists BUSINESS_ENTITIES_COPY_REF_DATA//

Create Procedure BUSINESS_ENTITIES_COPY_REF_DATA(
    In srcEntityId Int Unsigned
    , In tarEntityId Int Unsigned
    , In inTransaction smallint
)
Begin
    becrd_Main: begin
        Declare becrd_transactionStarted int default 0 ;
        DECLARE Exit HANDLER FOR SQLEXCEPTION
            Begin
                if becrd_transactionStarted  <> 0 then
                    rollback;
                    set becrd_transactionStarted  = 0;
                End if;
                resignal;
            end ;
        if Coalesce(inTransaction, 0) = 0 Then
            Start Transaction;
            Set becrd_transactionStarted  = 1;
        else
            Set becrd_transactionStarted  = 0;
        end if;

        Set @becrd_srcEntityId = srcEntityId;
        Set @becrd_tarEntityId = tarEntityId;

        becrd_cp_refdt_tbl: Begin

            Drop Temporary table if exists TMP_ENTTCP_REFDATAS_SRC;
            Create Temporary table TMP_ENTTCP_REFDATAS_SRC(
                        ID_TMP_ENTTCP_REFDATAS_SRC          int unsigned not null Primary Key AUTO_INCREMENT
                        , SRC_ID_ENTITIES_REF_DATAS         int unsigned not null
                        , SRC_ENTITIES_REF_ID_ENTITIES_REF  int unsigned not null

                        , TAR_ID_ENTITIES_REF_DATAS         int unsigned null
                        , TAR_ID_ENTITIES_REF_DATAS_NEWID   int unsigned null
                        , TAR_ENTITIES_REF_ID_ENTITIES_REF  int unsigned null

                        , ENTITIES_REF_DATAS_CODE           varchar(20)

                        , ENT_REF_TYPE_ID_ENT_REF_TYPES     int unsigned null
                        , ENTITIES_REF_SEQUENCE             smallint unsigned not null
                        , ENTITIES_REF_TYPES_CODE           varchar(20)

                        , Index (SRC_ID_ENTITIES_REF_DATAS)
                        , Index (SRC_ENTITIES_REF_ID_ENTITIES_REF)
                        , Index (ENT_REF_TYPE_ID_ENT_REF_TYPES, ENTITIES_REF_SEQUENCE, ENTITIES_REF_DATAS_CODE)
                        , Index (TAR_ID_ENTITIES_REF_DATAS)
                        , Index (TAR_ENTITIES_REF_ID_ENTITIES_REF)
                        , Index (ENTITIES_REF_TYPES_CODE)
                    )
                ;
            Insert Into TMP_ENTTCP_REFDATAS_SRC (
                SRC_ID_ENTITIES_REF_DATAS, SRC_ENTITIES_REF_ID_ENTITIES_REF
                , ENTITIES_REF_DATAS_CODE
                , ENT_REF_TYPE_ID_ENT_REF_TYPES, ENTITIES_REF_SEQUENCE
                , ENTITIES_REF_TYPES_CODE
            )
            Select
                entrddt.ID_ENTITIES_REF_DATAS, entr.ID_ENTITIES_REF
                , entrddt.ENTITIES_REF_DATAS_CODE
                , entr.ENT_REF_TYPE_ID_ENT_REF_TYPES, entr.ENTITIES_REF_SEQUENCE
                , entrftp.ENTITIES_REF_TYPES_CODE
            From
                ENTITIES_REF entr
                Inner Join ENTITIES_REF_DATAS entrddt
                    On entr.ID_ENTITIES_REF = entrddt.ENTITIES_REF_ID_ENTITIES_REF
                Inner Join ENTITIES_REF_TYPES entrftp
                    On entr.ENT_REF_TYPE_ID_ENT_REF_TYPES = entrftp.ID_ENTITIES_REF_TYPES
            Where entr.ENTITIES_ID_ENTITIES = @becrd_srcEntityId
            ;

            Drop Temporary table if exists TMP_ENTTCP_REFDATAS_TAR;
            Create Temporary table TMP_ENTTCP_REFDATAS_TAR(
                        ID_ENTITIES_REF_DATAS             int unsigned not null
                        , ENT_REF_TYPE_ID_ENT_REF_TYPES     int unsigned not null
                        , ENTITIES_REF_SEQUENCE             smallint unsigned not null
                        , ENTITIES_REF_ID_ENTITIES_REF      int unsigned not null
                        , ENTITIES_REF_DATAS_CODE           varchar(20)
                        , ENTITIES_REF_TYPES_CODE           varchar(20)

                        , Index (ID_ENTITIES_REF_DATAS)
                        , Index (ENTITIES_REF_ID_ENTITIES_REF)
                        , Index (ENT_REF_TYPE_ID_ENT_REF_TYPES, ENTITIES_REF_SEQUENCE, ENTITIES_REF_DATAS_CODE)
                        , Index (ENTITIES_REF_TYPES_CODE)
                    )
                ;
            Insert Into TMP_ENTTCP_REFDATAS_TAR (
                ID_ENTITIES_REF_DATAS
                , ENT_REF_TYPE_ID_ENT_REF_TYPES, ENTITIES_REF_SEQUENCE
                , ENTITIES_REF_TYPES_CODE
                , ENTITIES_REF_ID_ENTITIES_REF
                , ENTITIES_REF_DATAS_CODE
            )
            -- explain
            Select
                entrddt.ID_ENTITIES_REF_DATAS
                 , entr.ENT_REF_TYPE_ID_ENT_REF_TYPES, entr.ENTITIES_REF_SEQUENCE
                 , entrftp.ENTITIES_REF_TYPES_CODE
                 , entr.ID_ENTITIES_REF
                 , entrddt.ENTITIES_REF_DATAS_CODE
            From
                ENTITIES_REF entr
                Inner Join ENTITIES_REF_DATAS entrddt
                           On entr.ID_ENTITIES_REF = entrddt.ENTITIES_REF_ID_ENTITIES_REF
                Inner Join ENTITIES_REF_TYPES entrftp
                           On entr.ENT_REF_TYPE_ID_ENT_REF_TYPES = entrftp.ID_ENTITIES_REF_TYPES
            Where entr.ENTITIES_ID_ENTITIES = @becrd_tarEntityId
                And Exists (
                    Select SRC_ID_ENTITIES_REF_DATAS
                    From TMP_ENTTCP_REFDATAS_SRC src
                    Where src.ENT_REF_TYPE_ID_ENT_REF_TYPES = entrftp.ID_ENTITIES_REF_TYPES
                        And src.ENTITIES_REF_SEQUENCE = entr.ENTITIES_REF_SEQUENCE
                        And src.ENTITIES_REF_DATAS_CODE = entrddt.ENTITIES_REF_DATAS_CODE
                    Limit 1
                )
            ;

            -- mise à jour du TAR_ID_ENTITIES_REF_DATAS pour les codes ana existants
            -- dans l'entité cible
            -- explain
            Update TMP_ENTTCP_REFDATAS_SRC src
                Inner Join TMP_ENTTCP_REFDATAS_TAR tar
                    Using(ENT_REF_TYPE_ID_ENT_REF_TYPES, ENTITIES_REF_SEQUENCE, ENTITIES_REF_DATAS_CODE)
#                     On src.ENT_REF_TYPE_ID_ENT_REF_TYPES = tar.ENT_REF_TYPE_ID_ENT_REF_TYPES
#                     And src.ENTITIES_REF_SEQUENCE = tar.ENTITIES_REF_SEQUENCE
#                     And src.ENTITIES_REF_DATAS_CODE = tar.ENTITIES_REF_DATAS_CODE
            Set
                src.TAR_ID_ENTITIES_REF_DATAS = tar.ID_ENTITIES_REF_DATAS
                , src.TAR_ENTITIES_REF_ID_ENTITIES_REF = tar.ENTITIES_REF_ID_ENTITIES_REF
            ;


            -- explain
            Update TMP_ENTTCP_REFDATAS_SRC src
            Set
                src.TAR_ENTITIES_REF_ID_ENTITIES_REF = (
                    Select entr.ID_ENTITIES_REF
                    From ENTITIES_REF entr
                    Where entr.ENTITIES_ID_ENTITIES = @becrd_tarEntityId
                        And src.ENT_REF_TYPE_ID_ENT_REF_TYPES = entr.ENT_REF_TYPE_ID_ENT_REF_TYPES
                        And src.ENTITIES_REF_SEQUENCE = entr.ENTITIES_REF_SEQUENCE
                    Limit 1
                )
            Where src.TAR_ENTITIES_REF_ID_ENTITIES_REF Is Null
            ;

            -- obtention des nouveau ids
            Drop Temporary table if exists TMP_ENTTCP_REFDATAS_NEWID;
            Create Temporary table TMP_ENTTCP_REFDATAS_NEWID(
                        ID_TMP_ENTTCP_REFDATAS_NEWID        int unsigned not null primary key auto_increment

                        , ENT_REF_TYPE_ID_ENT_REF_TYPES     int unsigned null
                        , ENTITIES_REF_SEQUENCE             smallint unsigned not null
                        , ENTITIES_REF_DATAS_CODE           varchar(20)
                        , TMP_ENTTCP_REFDATAS_DBL_COUNT     int unsigned not null default 0
                        , MIN_ID_TMP_ENTTCP_REFDATAS_SRC    int unsigned null
                        , Index (ENT_REF_TYPE_ID_ENT_REF_TYPES, ENTITIES_REF_SEQUENCE, ENTITIES_REF_DATAS_CODE)
                        , Index (MIN_ID_TMP_ENTTCP_REFDATAS_SRC)
                    )
                ;
            Insert Into TMP_ENTTCP_REFDATAS_NEWID (
                   ENT_REF_TYPE_ID_ENT_REF_TYPES, ENTITIES_REF_SEQUENCE, ENTITIES_REF_DATAS_CODE
                   , TMP_ENTTCP_REFDATAS_DBL_COUNT, MIN_ID_TMP_ENTTCP_REFDATAS_SRC
            )
            -- explain
            -- il faut dédoublonner
            -- comparer les perfs entre un distinct et un group by
            Select ENT_REF_TYPE_ID_ENT_REF_TYPES, ENTITIES_REF_SEQUENCE, ENTITIES_REF_DATAS_CODE
                , Count(*), min(ID_TMP_ENTTCP_REFDATAS_SRC)
            From TMP_ENTTCP_REFDATAS_SRC
            Where TAR_ID_ENTITIES_REF_DATAS is Null
            GROUP BY ENT_REF_TYPE_ID_ENT_REF_TYPES, ENTITIES_REF_SEQUENCE, ENTITIES_REF_DATAS_CODE
            ;

            -- dédoublonnage
            -- explain
            Delete tar
            From TMP_ENTTCP_REFDATAS_SRC tar
                Inner Join TMP_ENTTCP_REFDATAS_NEWID dblchk
                    Using(ENT_REF_TYPE_ID_ENT_REF_TYPES, ENTITIES_REF_SEQUENCE, ENTITIES_REF_DATAS_CODE)
            Where TAR_ID_ENTITIES_REF_DATAS is Null
                And dblchk.TMP_ENTTCP_REFDATAS_DBL_COUNT>1
                And tar.ID_TMP_ENTTCP_REFDATAS_SRC <> dblchk.MIN_ID_TMP_ENTTCP_REFDATAS_SRC

            ;


            Set @becrd_lastid = Coalesce(
                        (
                            Select ID_ENTITIES_REF_DATAS From ENTITIES_REF_DATAS Order By 1 Desc Limit 1
                        ), 0
                    )
                ;
            -- explain
            Update TMP_ENTTCP_REFDATAS_SRC tar
            Inner Join TMP_ENTTCP_REFDATAS_NEWID src
                Using (ENT_REF_TYPE_ID_ENT_REF_TYPES, ENTITIES_REF_SEQUENCE, ENTITIES_REF_DATAS_CODE)
            Set tar.TAR_ID_ENTITIES_REF_DATAS_NEWID = src.ID_TMP_ENTTCP_REFDATAS_NEWID + @becrd_lastid
                , tar.TAR_ID_ENTITIES_REF_DATAS = src.ID_TMP_ENTTCP_REFDATAS_NEWID + @becrd_lastid
            Where
                  -- implicite
                  tar.TAR_ID_ENTITIES_REF_DATAS_NEWID Is Null
            ;

            Insert Into ENTITIES_REF_DATAS (
                  ID_ENTITIES_REF_DATAS
                  , ENTITIES_REF_ID_ENTITIES_REF, ENTITIES_REF_DATAS_CODE
            )
            Select TAR_ID_ENTITIES_REF_DATAS_NEWID
                , TAR_ENTITIES_REF_ID_ENTITIES_REF, ENTITIES_REF_DATAS_CODE
            From TMP_ENTTCP_REFDATAS_SRC
            Where TAR_ID_ENTITIES_REF_DATAS_NEWID Is NOT Null
            ;

        end ;

        if(becrd_transactionStarted <> 0 ) Then
            -- pour dev / debug
#             rollback ;
            commit;
            Set becrd_transactionStarted = 0;
            -- appel isolé de la PS
            -- purge possible
            Drop Temporary table if exists TMP_ENTTCP_REFDATAS_SRC;
#         else
#             !! NE SURTOUT pas supprimer TMP_ENTTCP_REFDATAS_SRC ici
        End if;

        Drop Temporary table if exists TMP_ENTTCP_REFDATAS_TAR;
        Drop Temporary table if exists TMP_ENTTCP_REFDATAS_NEWID;
    end ;
End //
Delimiter ;