Delimiter //
/**
 *   Procédure de création de la table d'importation des contacts
 *   (table cible principale ACCOUNTS)
 */
Drop Procedure if exists LOADER_CREATE_TABLE_CONTACTS//

Create Procedure LOADER_CREATE_TABLE_CONTACTS ()
Begin

    Drop Table If Exists LOADER_CONTACTS;

    Create Table LOADER_CONTACTS (

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

        -- champs pour le traitements de l'import
        LOADER_STATUS_ID_LOADER_STATUS Int Unsigned Not Null,

        -- General, references
        ACCOUNTS_REF VARCHAR(40) Null, -- not null
        ACCOUNTS_ID_ACCOUNTS Int Unsigned Null,
        CONTACTS_REF VARCHAR(40) Null, -- not null
        ID_CONTACTS Int Unsigned Null,
        ID_CONTACTS_NEW Int Unsigned Null,
        IS_MAIN_CONTACT Int Null default 0,
		
		CONTACTS_NOTE VARCHAR(250) Null, -- null

        -- Addresses
        DF_COUNTRY_LABEL varchar(255) Null,
        DF_COUNTRY_SHORT_CODE varchar(2) Null,
        DF_COUNTRY_LONG_CODE varchar(3) Null,
        DF_COUNTRY_ID_DF_COUNTRY Int Unsigned Null,
        CONTACTS_ADDRESSES_LINE_1 varchar(135) Null,
        CONTACTS_ADDRESSES_LINE_2 varchar(135) Null,
        CONTACTS_ADDRESSES_LINE_3 varchar(135) Null,
        CONTACTS_ADDRESSES_LINE_4 varchar(135) Null,
        CONTACTS_ADDRESSES_ZIP_CODE varchar(30) Null,
        CONTACTS_ADDRESSES_CITY varchar(135) Null,
        DF_CHECK_STAT_ID_DF_CHECK_STAT int(10) Unsigned Null, -- not null

        -- Name, Coordinates
        DF_TITLES_SHORT_TITLE varchar(8) Null,
        DF_TITLES_FULL_TITLE varchar(25) Null,
        DF_TITLES_ID_DF_TITLES Int Unsigned Null,
        CONTACTS_INDIVIDUAL_FIRST_NAME varchar(180) Null,
        CONTACTS_INDIVIDUAL_LAST_NAME varchar(210) Null,
        CONTACTS_INDIVIDUAL_BIRTH_NAME varchar(210) Null,
        CONTACTS_PHONE1 Varchar(25) Null,
        CONTACTS_PHONE1_DF_PHONE_TYP_CDE Varchar(30) Null,
        CONTACTS_PHONE1_ID_DF_PHONE_TYP Int Unsigned Null,
        CONTACTS_PHONE2 Varchar(25) Null,
        CONTACTS_PHONE2_DF_PHONE_TYP_CDE Varchar(30) Null,
        CONTACTS_PHONE2_ID_DF_PHONE_TYP Int Unsigned Null,
        CONTACTS_PHONE3 Varchar(25) Null,
        CONTACTS_PHONE3_DF_PHONE_TYP_CDE Varchar(30) Null,
        CONTACTS_PHONE3_ID_DF_PHONE_TYP Int Unsigned Null,

        -- Email
        CONTACTS_EMAILS_EMAIL varchar(255) Null,
        CONTACTS_EMAILS_EMAIL2 varchar(255) Null,
        CONTACTS_EMAILS_EMAIL3 varchar(255) Null,

        -- Compagny
        CONTACTS_BUSINESS_COMPANY_NAME varchar(100) Null,
        CONTACTS_BUSINESS_BRAND_NAME varchar(70) Null,
        CONTACTS_BUSINESS_SIRET Varchar(15) Null,

        DF_FORMS_ID_DF_FORMS Int Unsigned Null,
        DF_FORMS_SHORT_FORM varchar(10) Null,
        -- Language, Localization
        DF_LANGUAGES_CODE char(5) Null,
        DF_LANGUAGES_LABEL varchar(25) Null,
        DF_LANGUAGES_ID_DF_LANGUAGES Int Unsigned Null,

        -- Function
        DF_FUNCTIONS_SHORT_LABEL varchar(10) Null,
        DF_FUNCTIONS_ID_DF_FUNCTIONS Int Unsigned Null,

        -- Fixed data
        DF_SAND_STATE_ID_DF_SAND_STATE Int(10) Unsigned Null,
        CONTACTS_TRASHED Smallint(5) Unsigned Null,

        -- Dates
        CONTACTS_LAST_UPDATE Date Null, -- not null

        -- contraintes et indexes
        primary key (ID_LOADER_CONTACTS)
        , Index (LOADER_STATUS_ID_LOADER_STATUS)
--         , UNIQUE KEY (LOADER_STATUS_ID_LOADER_STATUS)
        , Index (ACCOUNTS_ID_ACCOUNTS)
        , Index (ACCOUNTS_REF)
        , Index (CONTACTS_REF)
        , Index (ID_CONTACTS)

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

End //

Delimiter ;
CALL LOADER_CREATE_TABLE_CONTACTS();