Delimiter $$
Drop Procedure If Exists BUSINESS_INVOICES_LOADER_INIT $$
Create Procedure BUSINESS_INVOICES_LOADER_INIT()
Begin
    Drop Temporary Table If Exists TMP_BUSINESS_INVOICES_LOADER_DATA;
    Create Temporary Table TMP_BUSINESS_INVOICES_LOADER_DATA (
        MODEL_OBJECT Text
        , NUM Varchar(20)
        , `DATE` Datetime
        , STATUS Varchar(5)
        , STATUS_DATE Datetime
        , STATUS_COMMENT Varchar(2000)
        , EVENT_SUBJECT Varchar(90)
        , DATA_TYPE Varchar(50) Not Null
        , DATA_ID Bigint Unsigned Not Null
        , DATA_JSON Text
        , Primary Key (`DATA_TYPE`, `DATA_ID`)
        , Index (`NUM`, `DATE`, `STATUS`)
    );
End $$

Drop Procedure If Exists BUSINESS_INVOICES_LOADER_FEED $$
Create Procedure BUSINESS_INVOICES_LOADER_FEED(
    In MODEL_OBJECT Text
    , In NUM Varchar(20)
    , In `DATE` Datetime
    , In STATUS Varchar(5)
    , In STATUS_DATE Datetime
    , In STATUS_COMMENT Varchar(2000)
    , In EVENT_SUBJECT Varchar(90)
    , In DATA_TYPE Varchar(50)
    , In DATA_ID Bigint Unsigned
    , In DATA_JSON Text
)
Begin
    Insert Ignore Into
        TMP_BUSINESS_INVOICES_LOADER_DATA
    Values (
        MODEL_OBJECT
        , NUM
        , `DATE`
        , STATUS
        , STATUS_DATE
        , STATUS_COMMENT
        , EVENT_SUBJECT
        , DATA_TYPE
        , DATA_ID
        , DATA_JSON
    );
End $$

Drop Procedure If Exists BUSINESS_INVOICES_LOADER_COMPUTE $$
Create Procedure BUSINESS_INVOICES_LOADER_COMPUTE(
    In SUMMARIES_CODE Varchar(12)
    , In ENTITY_ID Int Unsigned
    , In ACCOUNT_ID Int Unsigned
)
Begin
    -- Copie requise pour les requêtes à suivre (https://dev.mysql.com/doc/refman/5.6/en/temporary-table-problems.html)
    Drop Temporary Table If Exists TMP_BUSINESS_INVOICES_LOADER_DATA_COPY;
    Create Temporary Table TMP_BUSINESS_INVOICES_LOADER_DATA_COPY As (
        Select
            *
        From
            TMP_BUSINESS_INVOICES_LOADER_DATA tbild
            Inner Join DF_ENTRIES_STATUT des
                On des.DF_ENTRIES_STATUT_CODE = tbild.STATUS
    );
    Alter Table TMP_BUSINESS_INVOICES_LOADER_DATA_COPY
        Add Index (NUM, DATA_TYPE, DATA_ID)
    ;

    -- Sélection des écritures à traiter
    Drop Temporary Table If Exists TMP_BUSINESS_INVOICES_LOADER_FOUND;
    Create Temporary Table TMP_BUSINESS_INVOICES_LOADER_FOUND As (
        Select
            tbild.DATA_TYPE
            , tbild.DATA_ID
            , a.ID_ACCOUNTS
            , e.ID_ENTRIES
            , des.ID_DF_ENTRIES_STATUT
        From
            ENTRIES e
            Inner Join ENTRIES_has_ACCOUNTS eha
                On eha.ENTRIES_ID_ENTRIES = e.ID_ENTRIES
            Inner Join ACCOUNTS a
                On a.ID_ACCOUNTS = eha.ACCOUNTS_ID_ACCOUNTS
            Inner Join DF_ENTRIES_TYPE det
                On det.ID_DF_ENTRIES_TYPE = e.DF_ENTR_TYPE_ID_DF_ENTR_TYPE
            Inner Join (
                TMP_BUSINESS_INVOICES_LOADER_DATA tbild
                Left Join TMP_BUSINESS_INVOICES_LOADER_DATA_COPY tbildc
                    On tbildc.NUM = tbild.NUM
                        And tbildc.DATA_TYPE = tbild.DATA_TYPE
                        And tbildc.DATA_ID > tbild.DATA_ID
                        And tbildc.STATUS_DATE >= tbild.STATUS_DATE
            ) On tbild.NUM = e.ENTRIES_REF
            Inner Join DF_ENTRIES_STATUT des
                On des.DF_ENTRIES_STATUT_CODE = tbild.STATUS
        Where
            tbildc.DATA_ID Is Null -- statut le plus récent par type
            And det.DF_ENTRIES_TYPE_CODE = 'INV' -- l'écriture est une facture
            And If(ENTITY_ID Is Not Null, a.ENTITIES_ID_ENTITIES = ENTITY_ID, True)
            And If(ACCOUNT_ID Is Not Null, a.ID_ACCOUNTS = ACCOUNT_ID, True)
    );

    -- Mise à jour des statuts d'écriture
    Update ENTRIES e
        Inner Join (
            TMP_BUSINESS_INVOICES_LOADER_FOUND tbilf
            Inner Join (
                TMP_BUSINESS_INVOICES_LOADER_DATA tbild
                Left Join TMP_BUSINESS_INVOICES_LOADER_DATA_COPY tbildc
                    On tbildc.NUM = tbild.NUM
                        And tbildc.STATUS_DATE > tbild.STATUS_DATE
            ) On tbild.DATA_TYPE = tbilf.DATA_TYPE
                And tbild.DATA_ID = tbilf.DATA_ID
        ) On tbilf.ID_ENTRIES = e.ID_ENTRIES
        Left Join DF_ENTRIES_STATUT des
            On des.ID_DF_ENTRIES_STATUT = e.DF_ENTR_STT_ID_DF_ENTR_STT
    Set
        e.DF_ENTR_STT_ID_DF_ENTR_STT = tbilf.ID_DF_ENTRIES_STATUT
    Where
        tbildc.DATA_ID Is Null -- statut le plus récent (par id ou date)
        And Coalesce(des.ID_DF_ENTRIES_STATUT, '') <> tbilf.ID_DF_ENTRIES_STATUT -- statut mis à jour
    ;

    -- Mise à jour de l'historique
    Insert Into SUMMARIES (
        ENTRIES_ID_ENTRIES
        , DF_SUMMARIES_ID_DF_SUMMR
        , DF_PRIORITY_ID_DF_PRIORITY
        , SUMMARIES_MEMBER_AUTHOR
        , SUMMARIES_DATE
        , SUMMARIES_SUBJECT
        , SUMMARIES_COMMENTS
    )
    Select
        tbilf.ID_ENTRIES
        , ds.ID_DF_SUMMARIES
        , ds.DF_PRIORITY_ID_DF_PRIORITY
        , m.ID_MEMBERS
        , tbild.STATUS_DATE
        , Concat( -- la màj des statuts d'écriture dépend du formatage du sujet
            des.DF_ENTRIES_STATUT_CODE, ' (' , tbild.EVENT_SUBJECT, ')'
        )
        , tbild.STATUS_COMMENT
    From
        TMP_BUSINESS_INVOICES_LOADER_FOUND tbilf
        Inner Join TMP_BUSINESS_INVOICES_LOADER_DATA tbild
            On tbild.DATA_TYPE = tbilf.DATA_TYPE
                And tbild.DATA_ID = tbilf.DATA_ID
        Inner Join DF_SUMMARIES ds
            On ds.DF_SUMMARIES_CODE = `SUMMARIES_CODE`
        Inner Join (
            MEMBERS m
            Inner Join CONTACTS_INDIVIDUAL ci
                On ci.ID_CONTACTS_INDIVIDUAL = m.CONTACT_INDIV_ID_CONTACT_INDIV
            Inner Join CONTACTS c
                On c.ID_CONTACTS = ci.CONTACTS_ID_CONTACTS
        ) On c.CONTACTS_REF = 'BATCH'
        Left Join (
            DF_ENTRIES_STATUT des
            Inner Join DF_ENTRIES_STATUT_LNG desl
                On desl.DF_ENTR_STT_ID_DF_ENTR_STT = des.ID_DF_ENTRIES_STATUT
        ) On des.ID_DF_ENTRIES_STATUT = tbilf.ID_DF_ENTRIES_STATUT
            And desl.DF_LANG_ID_DEF_LANG = c.DF_LANGUAGES_ID_DF_LANGUAGE
        Left Join SUMMARIES s
            On s.ENTRIES_ID_ENTRIES = tbilf.ID_ENTRIES
                And s.DF_SUMMARIES_ID_DF_SUMMR = ds.ID_DF_SUMMARIES
                And s.SUMMARIES_MEMBER_AUTHOR = m.ID_MEMBERS
                And s.SUMMARIES_DATE = tbild.STATUS_DATE
    Where
        s.ID_SUMMARIES Is Null -- l'historique de ce statut n'existe pas
    ;
End $$

Drop Procedure If Exists BUSINESS_INVOICES_LOADER_READ $$
Create Procedure BUSINESS_INVOICES_LOADER_READ(
    In `LIMIT` Int Unsigned
    , In `OFFSET` Int Unsigned
)
Begin
    -- Retourner les objets relatifs aux factures trouvées (depuis la copie, ça libère l'originale)
    Select
        tbildc.MODEL_OBJECT
    From
        TMP_BUSINESS_INVOICES_LOADER_DATA_COPY tbildc
        Inner Join TMP_BUSINESS_INVOICES_LOADER_FOUND tbilf
            On tbilf.DATA_TYPE = tbildc.DATA_TYPE
                And tbilf.DATA_ID = tbildc.DATA_ID
    Limit `LIMIT`
    Offset `OFFSET`
    ;
End $$
Delimiter ;