/**
 * @ Author: Marshall Telaumbanua
 * @ Create Time: 2023-06-07 19:47:45
 * @ Usage: CALL MWCONFIG.MERGE_CLIENT(ADAPTOR_ID, IDLE_WAIT, TIMEOUT, CLOSE_QUEUE, CONTROL_QUEUE, KEEP_CONN, REUSE_DELAY, NODE)
 */

CREATE OR REPLACE PROCEDURE MERGE_CLIENT (
    IN ADAPTOR_ID VARCHAR(256),
    IN IDLE_WAIT BIGINT,
    IN TIMEOUT SMALLINT,
    IN CLOSE_QUEUE VARCHAR(256),
    IN CONTROL_QUEUE VARCHAR(256),
    IN KEEP_CONN SMALLINT,
    IN REUSE_DELAY SMALLINT,
    IN NODE VARCHAR(256)
)
BEGIN
    DECLARE SQL_STMT VARCHAR(4000);

    SET SQL_STMT = 'MERGE INTO MWCONFIG.CLIENT AS T ' ||
        'USING (VALUES (''' || ADAPTOR_ID || ''', ' ||
        CASE WHEN IDLE_WAIT IS NOT NULL THEN CAST(IDLE_WAIT AS VARCHAR) ELSE 'NULL' END || ', ' || 
        CASE WHEN TIMEOUT IS NOT NULL THEN CAST(TIMEOUT AS VARCHAR) ELSE 'NULL' END || ', ' || 
        CASE WHEN CLOSE_QUEUE IS NOT NULL THEN '''' || CLOSE_QUEUE || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN CONTROL_QUEUE IS NOT NULL THEN '''' || CONTROL_QUEUE || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN KEEP_CONN IS NOT NULL THEN CAST(KEEP_CONN AS VARCHAR) ELSE 'NULL' END || ', ' || 
        CASE WHEN REUSE_DELAY IS NOT NULL THEN CAST(REUSE_DELAY AS VARCHAR) ELSE 'NULL' END || ', ' || 
        CASE WHEN NODE IS NOT NULL THEN '''' || NODE || '''' ELSE 'NULL' END || ')) '  ||
        'AS S (ADAPTOR_ID, IDLE_WAIT, TIMEOUT, CLOSE_QUEUE, CONTROL_QUEUE, KEEP_CONN, REUSE_DELAY, NODE) ' ||
        'ON (T.ADAPTOR_ID = S.ADAPTOR_ID) ' ||
        'WHEN MATCHED THEN ' ||
        'UPDATE SET T.IDLE_WAIT = S.IDLE_WAIT, T.TIMEOUT = S.TIMEOUT, T.CLOSE_QUEUE = S.CLOSE_QUEUE, ' ||
        'T.CONTROL_QUEUE = S.CONTROL_QUEUE, T.KEEP_CONN = S.KEEP_CONN, T.REUSE_DELAY = S.REUSE_DELAY, ' ||
        'T.NODE = S.NODE ' ||
        'WHEN NOT MATCHED THEN ' ||
        'INSERT (ADAPTOR_ID, IDLE_WAIT, TIMEOUT, CLOSE_QUEUE, CONTROL_QUEUE, KEEP_CONN, REUSE_DELAY, NODE) ' ||
        'VALUES (''' || ADAPTOR_ID || ''', ' ||
        CASE WHEN IDLE_WAIT IS NOT NULL THEN CAST(IDLE_WAIT AS VARCHAR) ELSE 'NULL' END || ', ' || 
        CASE WHEN TIMEOUT IS NOT NULL THEN CAST(TIMEOUT AS VARCHAR) ELSE 'NULL' END || ', ' || 
        CASE WHEN CLOSE_QUEUE IS NOT NULL THEN '''' || CLOSE_QUEUE || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN CONTROL_QUEUE IS NOT NULL THEN '''' || CONTROL_QUEUE || '''' ELSE 'NULL' END || ', ' || 
        CASE WHEN KEEP_CONN IS NOT NULL THEN CAST(KEEP_CONN AS VARCHAR) ELSE 'NULL' END || ', ' || 
        CASE WHEN REUSE_DELAY IS NOT NULL THEN CAST(REUSE_DELAY AS VARCHAR) ELSE 'NULL' END || ', ' || 
        CASE WHEN NODE IS NOT NULL THEN '''' || NODE || '''' ELSE 'NULL' END || ')';

    PREPARE STMT FROM SQL_STMT;
    EXECUTE STMT;
END;
@