
Delimiter //
Drop Procedure If Exists LOADER_EDMINDEX_NORMALIZE_MAP_ENTRIES_DOCUMENT_PROVIDED//
Create Procedure LOADER_EDMINDEX_NORMALIZE_MAP_ENTRIES_DOCUMENT_PROVIDED(
    In batchnum Int
)
Begin
	getDocId: Begin
		-- recherche des documents attaché aux ecriture ayant le même nom que le fichier importé
		-- => permet d'exclure les documents déjà attaché
		-- TODO permetre la supression ou la mise à jour
        Update
            LOADER_EDMINDEX LEDM
            Inner Join LOADER_STATUS LST
                On LST.ID_LOADER_STATUS = LEDM.LOADER_STATUS_ID_LOADER_STATUS
        Set
            LEDM.ID_DOCUMENTS = (
                Select
                    DOCS.ID_DOCUMENTS
                From DOCUMENTS DOCS
                    Inner Join ATTACHED_DOCUMENTS ad1
                        On DOCS.ID_DOCUMENTS = ad1.DOCUMENTS_ID_DOCUMENTS
                    Inner Join ENTRIES_has_ATTACHED_DOCUMENTS ehad1
                        On ad1.ID_ATTACHED_DOCUMENTS = ehad1.ATTACHED_DOC_ID_ATTACHED_DOC
                    Inner Join LOADER_EDMINDEX_ENTRIES_HAS_DOCUMENTS leehd1
                        On ehad1.ENTRIES_ID_ENTRIES = leehd1.ENTRIES_ID_ENTRIES
                Where
--                     LEDM.LOADER_STATUS_ID_LOADER_STATUS = leehd1.LOADER_STATUS_ID_LOADER_STATUS
                    LEDM.ID_LOADER_EDMINDEX = leehd1.LOADER_EDMINDEX_ID_LOADER_EDMINDEX
                    And LEDM.DOCUMENTS_NAME = DOCS.DOCUMENTS_NAME
                    
                Limit 1
            )
        Where
            LST.LOADER_STATUS_BATCH_NUM = batchnum
			And LST.LOADER_STATUS_ERRCOUNT = 0
            And LEDM.LOADER_EDMINDEX_TYPE  = 'ENTRIES'
        ;

--         Update
--             LOADER_EDMINDEX LEDM
--             Inner Join LOADER_STATUS LST
--                 On LST.ID_LOADER_STATUS = LEDM.LOADER_STATUS_ID_LOADER_STATUS
-- 			Inner Join DOCUMENTS DOCS
-- 				On LEDM.DOCUMENTS_NAME = DOCS.DOCUMENTS_NAME
--         Set
--             LEDM.ID_DOCUMENTS = DOCS.ID_DOCUMENTS
--         Where
--             LST.LOADER_STATUS_BATCH_NUM = batchnum
-- 			And LST.LOADER_STATUS_ERRCOUNT = 0
--             And LEDM.LOADER_EDMINDEX_TYPE  = 'ENTRIES'
-- 			And LEDM.LOADER_STATUS_ID_LOADER_STATUS In (
-- 				Select
-- 					LEEHD.LOADER_STATUS_ID_LOADER_STATUS
-- 				From LOADER_EDMINDEX_ENTRIES_HAS_DOCUMENTS LEEHD
-- 					Inner Join ENTRIES_has_ATTACHED_DOCUMENTS EHAD
-- 						On LEEHD.ENTRIES_ID_ENTRIES = EHAD.ENTRIES_ID_ENTRIES
-- 				Where
-- 					LEDM.LOADER_STATUS_ID_LOADER_STATUS = LEEHD.LOADER_STATUS_ID_LOADER_STATUS
-- 			)
--         ;
    End;
	
	setDocNewIds: Begin
	-- Stores new ids in a temporary table
		DROP TEMPORARY TABLE IF EXISTS temp_DOCUMENTS;
		CREATE TEMPORARY TABLE temp_DOCUMENTS (
			ID_DOCUMENTS Int Unsigned Not Null Auto_increment
			, LOADER_STATUS_ID_LOADER_STATUS Int Unsigned Not Null
			, Primary Key (ID_DOCUMENTS)
			, Index (LOADER_STATUS_ID_LOADER_STATUS)
		);
		Insert Into temp_DOCUMENTS (
			LOADER_STATUS_ID_LOADER_STATUS
		)
		Select 
			LST.ID_LOADER_STATUS
		From LOADER_EDMINDEX LEDM
            Inner Join LOADER_STATUS LST
                On LST.ID_LOADER_STATUS = LEDM.LOADER_STATUS_ID_LOADER_STATUS
		Where
			LST.LOADER_STATUS_BATCH_NUM = batchnum
			And LST.LOADER_STATUS_ERRCOUNT = 0
			And LEDM.LOADER_EDMINDEX_TYPE  = 'ENTRIES'
			And LEDM.ID_DOCUMENTS Is Null
		;
		Update LOADER_EDMINDEX LEDM 
			Inner Join temp_DOCUMENTS TDOCS
				On LEDM.LOADER_STATUS_ID_LOADER_STATUS = TDOCS.LOADER_STATUS_ID_LOADER_STATUS
		Set LEDM.ID_DOCUMENTS_NEW = TDOCS.ID_DOCUMENTS
		;
	End;

End //
Delimiter ;