Delimiter //
/**
 *   tables est procédures pour l'importation des entries
 */
Drop Procedure if exists LOADER_CREATE_TABLE_ENTRIES//

Create Procedure LOADER_CREATE_TABLE_ENTRIES ()
Begin

    Drop Table If Exists LOADER_ENTRIES;

    Create Table LOADER_ENTRIES(
        -- champs pour le traitements de l'import
        LOADER_STATUS_ID_LOADER_STATUS Int Not Null ,

        -- champ auto increment, initialisé avant chanque import
        ID_LOADER_ENTRIES Int Unsigned Not Null Auto_increment,

        -- General, references
        ACCOUNTS_REF Varchar(60) Null, -- not null
        ACCOUNTS_ID_ACCOUNTS Int Unsigned Null,
        ENTRIES_REF Varchar(60) Null, -- not null
        ENTRIES_ACC_EXT_REF Varchar(45) Null, -- not null
        ID_ENTRIES Int Unsigned Null,
        ID_ENTRIES_NEW Int Unsigned Null,

        ENTRIES_LABEL Varchar(50) Null,

        -- Type
        TYPE Varchar(20) Null,
        DF_ENTR_TYPE_ID_DF_ENTR_TYPE Int Unsigned Null,

        -- status
        ENTRIES_STATUS_CODE Varchar(5) null,
        DF_ENTR_STT_ID_DF_ENTR_STT Int Unsigned Null,

        -- Payment
        PAYMENT_TYPE Varchar(20) Null,
        DF_PAY_TYPE_ID_DF_PAY_TYPE Int Unsigned Null,
        ENTRIES_PURCHASE_ORDER Varchar(20), -- numéro de commande
        ENTRIES_ACCOUTING_ACCOUNTS Varchar(15),

        -- Given amounts
		-- devise de l'entité => ENTRIES_ENT_XXX
        ENT_DEBIT Decimal(19,4) Null,
        ENT_CREDIT Decimal(19,4) Null,
        ENT_AMOUNT Decimal(19,4) Null,
        ENT_BALANCE Decimal(19,4) Null,

		-- devise de la transaction => ENTRIES_XXX
        DEBIT Decimal(19,4) Null,
        CREDIT Decimal(19,4) Null,
        AMOUNT Decimal(19,4) Null,

		-- devise de consolidation => ENTRIES_ADMIN_XXX
        ADMIN_DEBIT Decimal(19,4) Null,
        ADMIN_CREDIT Decimal(19,4) Null,
        ADMIN_AMOUNT Decimal(19,4) Null,

        -- Normalized amounts
		-- ne pas mapper directement
        ENTRIES_ENT_DEBIT Decimal(19,4) Null,
        ENTRIES_ENT_CREDIT Decimal(19,4) Null,
	        -- Balance
        ENTRIES_ENT_BALANCE Decimal(19,4) Null,

        ENTRIES_DEBIT Decimal(19,4) Null,
        ENTRIES_CREDIT Decimal(19,4) Null,
        ENTRIES_ADMIN_DEBIT Decimal(19,4) Null,
        ENTRIES_ADMIN_CREDIT Decimal(19,4) Null,

        ENTRIES_PROMISE Decimal(19,4) Null,

        -- contact attaché
        CONTACTS_REF VARCHAR(40) Null,
        CONTACTS_ID_CONTACTS Int Unsigned Null,

        -- Currency
        CURRENCY Varchar(3) Null,
        DF_CURRENCIES_ID_DF_CURRENCIES  Int Unsigned Null,

        -- Dates
        ENTRIES_DATE Date Null,
        ENTRIES_DUE_DATE Date Null,

        ENTRIES_PROMISE_DATE Date Null,

		-- ticket #6291 statue de lettrage forcé (indépendant de la balance)
		-- statues de lettrage forcé
		ENTRIES_MATCHINGCODES_STATE_CODE Varchar(45) Null, 
		ENTRIES_MATCHINGCODES_STATE Int Unsigned Null , -- ici si on map directement l'id, permet de vérifier que l'id existe bien
		ENTRIES_MATCHINGCODE_STATE Int Unsigned Null , -- deprecated pour retro compatibilité, remplacé par le champ ENTRIES_MATCHINGCODES_STATE
		MATCHINGCODE_STATE_FORCED SmallInt Null Default 0, -- flag pour simplifier le tests permettant de savoir si
                                                           -- le status de lettrage est forcé ou valeur par défaut issue
                                                           -- de la normalisation
        -- Fixed data
        DF_MATCH_STAT_ID_DF_MATCH_STAT Int(10) Unsigned Null,
        DF_SAND_STATE_ID_DF_SAND_STATE Int(10) Unsigned Null,
        DF_PAY_SCHED_STATUS_ID_DF_PAY_SCHED_STATUS Int(10) Unsigned Null,

        primary key (ID_LOADER_ENTRIES)
        , Index (LOADER_STATUS_ID_LOADER_STATUS)
        , Index (ACCOUNTS_REF)
        , Index (ACCOUNTS_ID_ACCOUNTS)
        , Index (ENTRIES_REF)
        , Index (ENTRIES_ACC_EXT_REF)
        , Index (ID_ENTRIES)
        , Index (ID_ENTRIES_NEW)

    ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

End //

Delimiter ;
CALL LOADER_CREATE_TABLE_ENTRIES();