Delimiter //
Drop procedure if exists MakeMoke //
Create procedure MakeMoke (
    In tableName varchar(50)
)
Begin
    Declare rs text ;-- default 'Select Concat(''a'',';
--     if tableName Regexp '(LOADER\\_)?' Then
    if tableName Like 'BUSINESS\\_%' Then
        -- moke business
        set @tableName = substring(tableName, locate('_', tableName)+1);
        Set @procPrefix = Concat('LOADER_TEST_MOCK_BUSINESS_PUSH(''''', @tableName, ''''', ');
        Set @procSuffix = '';
    Else
        -- moke loader
        if tableName Like 'LOADER\\_%' Then
            set @tableName = substring(tableName, locate('_', tableName)+1);
        else 
            set @tableName = tableName;
        End if;

        if @tableName in ('ACCOUNTS', 'BALANCECONTROL','CONTACTS', 'ENTRIES', 'MATCHINGCODE') Then
            -- specific procedure
            Set @procPrefix = Concat('LOADER_TEST_MOCK_', @tableName, '_PUSH($ID$, ');
            Set @procSuffix = ',$ID_STATUS$, null';
        Else
            -- generic procedure
            Set @procPrefix = Concat('LOADER_TEST_MOCK_PUSH(''''', @tableName, ''''', $ID$, ');
            Set @procSuffix = ',$ID_STATUS$, null';
        End if;

        Set @tableName = concat('LOADER_', @tableName);

    End if;

    
    Set rs =  concat('Select Replace(Replace(Concat(''Call ', @procPrefix, ''',''''');
    main: Begin
        Declare primaryKey varchar(50);
        Declare col varchar(50);
        Declare dataType varchar(50);
        Declare pcursor cursor for 
            Select 
                `COLUMN_NAME`
            From 
                information_schema.KEY_COLUMN_USAGE 
            Where 
                TABLE_SCHEMA = schema()
                And `TABLE_NAME` Collate UTF8_general_ci  = @tableName Collate UTF8_general_ci 
                And `CONSTRAINT_NAME` = 'PRIMARY'
                And ORDINAL_POSITION = 1  
        ;

        Declare cur1 cursor for
            Select 
                COLUMN_NAME, DATA_TYPE
            From 
                information_schema.COLUMNS
            Where
                TABLE_SCHEMA = schema()
                And `TABLE_NAME` Collate UTF8_general_ci = @tableName Collate UTF8_general_ci
                And COLUMN_NAME <> 'LOADER_STATUS_ID_LOADER_STATUS'
                And COLUMN_NAME <> Concat ('ID_', TABLE_NAME)
            ;

        Declare exit Handler for Not Found
        atend: begin
            Set rs = concat(rs, '''''''',  @procSuffix, ')''), ''$ID$'', ', primaryKey, '), ''$ID_STATUS$'', ''1'') result From ', @tableName);
            close cur1;
        end;
        
        open pcursor;
            fetch pcursor Into primaryKey;
        close pcursor;
        
        set @sep = '';

        open cur1;
        reed_loop: Loop
            Fetch cur1 Into col, dataType;
            -- specification de la valeur null
            Set rs = concat(rs, @sep, '''`', col, '` = '', Case When `', col , 
                            '` Is Null Then ''Null'' Else ');
            if dataType in ('tinyint', 'smallint', 'int' , 'decimal', 'float', 'double') Then
                Set rs = concat(rs, ' `', col, '`');
            Else 
                Set @delimiter = '''''''''''''';
                Set rs = concat(rs, ' Concat(', @delimiter, ', `', col, '`,', @delimiter, ')');
            End if;

            Set rs = concat(rs, ' End, ' );
            Set @sep = ''', '', ';
            
        End Loop;
    End;

    set @query = rs;
    PREPARE targetstmt FROM @query; 
	execute targetstmt ;
    Deallocate prepare targetstmt; 

End //
Delimiter ;
