Delimiter //
Drop Procedure If Exists BUSINESS_PREPARE_INDEXES_TABLE//
Create Procedure BUSINESS_PREPARE_INDEXES_TABLE (
    In maxlangId Int  Unsigned
    , In forceErase tinyint
    , In maxloop int
) 
Begin
    if forceErase = 1 Then
        truncate table DF_INDEXES;
    End if;
    Drop temporary table If exists TMP_POPULATE ;

    Create Temporary Table TMP_POPULATE ( 
--             TMP_POPULATE_BATCH_NUM Int Not Null Primary key auto_increment, 
            TMP_POPULATE_BATCH_VAL int
    ) ;

    Insert Into TMP_POPULATE ( 
        TMP_POPULATE_BATCH_VAL
    ) Values 
        (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
        , (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
        , (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
        , (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
        , (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
    ;

    prepareloop: loop

        Drop Temporary Table if Exists TMP_POPULATE2;
        Create temporary Table TMP_POPULATE2 as 
            Select TMP_POPULATE_BATCH_VAL  From TMP_POPULATE 
        ;

        Insert Into TMP_POPULATE ( TMP_POPULATE_BATCH_VAL) 
        Select TMP_POPULATE_BATCH_VAL  
        From TMP_POPULATE2 
        ;

        set maxloop = maxloop-1;

        if maxloop < 1 Then
            leave prepareloop;
        End if;

    end loop;
    Drop Temporary Table if Exists TMP_POPULATE2;
    -- in ne faut "auti_icrmenter" qu'une seul foix,  sinon risque de "trous" dans la numératoration
    Create Temporary Table TMP_POPULATE2 ( 
            TMP_POPULATE_BATCH_NUM Int Not Null Primary key auto_increment, 
            TMP_POPULATE_BATCH_VAL int
    ) ;
    Insert Into TMP_POPULATE2(TMP_POPULATE_BATCH_VAL) Select TMP_POPULATE_BATCH_VAL from TMP_POPULATE;
    if maxlangId > 0 Then
        -- popolation avec un nombre spécifier de langue
        populateloop: loop
            if maxlangId < 1 Then
                leave populateloop;
            End if;

            Insert Into DF_INDEXES (
                DF_INDEXES_EXTID, DF_INDEXES_LNGID
            )
            Select TMP_POPULATE_BATCH_NUM, maxlangId
            From TMP_POPULATE2
            ;

            set maxlangId = maxlangId -1;
        end loop;
    else
        -- popolation avec autodetmination des langues
        -- => utilisation de DF_LANGUAGES
        populateAuto: Begin
            Declare lngind Int Unsigned;
            Declare lngCursor Cursor For
                    Select ID_DF_LANGUAGES
                    From DF_LANGUAGES
                ;
            Open lngCursor;
            populateAutoBlnk: Begin
                Declare Exit Handler for NOT FOUND 
                    Begin
                        Close lngCursor;
                    End ;
                populateAutoLoop: Loop
                    fetch lngCursor Into lngind;

                    Insert Into DF_INDEXES (
                        DF_INDEXES_EXTID, DF_INDEXES_LNGID
                    )
                    Select TMP_POPULATE_BATCH_NUM, lngind
                    From TMP_POPULATE2
                    ;
                end loop populateAutoLoop;
            end ;
        End ;



    End if;
    
    Drop temporary table If exists TMP_POPULATE ;
    Drop table If exists TMP_POPULATE ;

	-- population de la table des dates
	truncate table DF_DATES; 
	insert into DF_DATES (DF_DATES_YEAR, DF_DATES_MONTH, DF_DATES_DAY, DF_DATES_FULL ) 
	select 
	-- Select DF_INDEXES_EXTID
		year(dt) dfm, month(dt) dfm, day(dt) dft, dt  
	from (
		Select  DF_INDEXES_EXTID, DATE_ADD(curdate(), interval -DF_INDEXES_EXTID+9000 DAY) dt 
		from DF_INDEXES 
		Where DF_INDEXES_LNGID = 1 and DF_INDEXES_EXTID < 11000
	) tmpdt order by DF_INDEXES_EXTID desc;

end //
Delimiter ;

