/**
 * @ Author: Marshall Telaumbanua
 * @ Create Time: 2023-06-07 19:52:33
 * @ Usage: CALL MWCONFIG.MERGE_MAPPING_GROUP(MAPPING_ID, ID, SOURCE, TARGET, INCLUDE_MAPPING_ID, INCLUDE_ID)
 */

CREATE OR REPLACE PROCEDURE MERGE_MAPPING_GROUP (
    IN MAPPING_ID VARCHAR(256),
    IN ID BIGINT,
    IN SOURCE CLOB,
    IN TARGET CLOB,
    INCLUDE_MAPPING_ID VARCHAR(256),
    INCLUDE_ID BIGINT
)
BEGIN
    DECLARE SQL_STMT VARCHAR(10000);

    SET SQL_STMT = 'MERGE INTO MWCONFIG.MAPPING_GROUP AS T ' ||
        'USING (VALUES (''' || MAPPING_ID || ''', ' || ID || ', ''' || SOURCE || ''', ''' || TARGET || ''', ' ||
        CASE
            WHEN INCLUDE_MAPPING_ID IS NULL THEN 'NULL'
            ELSE '''' || INCLUDE_MAPPING_ID || ''''
        END || ', ' || 
        CASE
            WHEN INCLUDE_ID IS NULL THEN 'NULL'
            ELSE '' || INCLUDE_ID || ''
        END || ')) AS S (MAPPING_ID, ID, SOURCE, TARGET, INCLUDE_MAPPING_ID, INCLUDE_ID) ' ||
        'ON (T.MAPPING_ID = S.MAPPING_ID AND T.ID = S.ID) ' ||
        'WHEN MATCHED THEN ' ||
        '  UPDATE SET ' ||
        '    T.SOURCE = S.SOURCE, ' ||
        '    T.TARGET = S.TARGET, ' ||
        '    T.INCLUDE_MAPPING_ID = S.INCLUDE_MAPPING_ID, ' ||
        '    T.INCLUDE_ID = S.INCLUDE_ID ' ||
        'WHEN NOT MATCHED THEN ' ||
        '  INSERT (MAPPING_ID, ID, SOURCE, TARGET, INCLUDE_MAPPING_ID, INCLUDE_ID) ' ||
        '  VALUES (S.MAPPING_ID, S.ID, S.SOURCE, S.TARGET, S.INCLUDE_MAPPING_ID, S.INCLUDE_ID)';

    PREPARE STMT FROM SQL_STMT;
    EXECUTE STMT;
END
@