/**
 * @ Author: Marshall Telaumbanua
 * @ Create Time: 2023-06-07 19:52:33
 * @ Usage: CALL MWCONFIG.MERGE_DBCONN(ID,DRIVER,URL,USERNAME,PASSWORD,SCHEMA,TIMEOUT)
 */

CREATE OR REPLACE PROCEDURE MERGE_DBCONN (
    IN ID VARCHAR(256),
    IN DRIVER VARCHAR(256),
    IN URL VARCHAR(256),
    IN USERNAME VARCHAR(64),
    IN PASSWORD VARCHAR(256),
    IN SCHEMA VARCHAR(32),
    IN TIMEOUT INTEGER
)
BEGIN
    DECLARE SQL_STMT VARCHAR(1000);

    SET SQL_STMT = 'MERGE INTO MWCONFIG.DBCONN AS T ' ||
        'USING (VALUES (''' || ID || ''', ''' || DRIVER || ''', ''' || URL || ''', ''' ||
        USERNAME || ''', ''' || PASSWORD || ''', ''' || SCHEMA || ''', ' || 
        CASE WHEN TIMEOUT IS NOT NULL THEN CAST(TIMEOUT AS VARCHAR) ELSE 'NULL' END || 
        ', CURRENT USER, CURRENT TIMESTAMP )) ' ||
        'AS S (ID, DRIVER, URL, USERNAME, PASSWORD, SCHEMA, TIMEOUT, MODIFIED_BY, TIMESTAMP) ' ||
        'ON (T.ID = S.ID) ' ||
        'WHEN MATCHED THEN ' ||
        'UPDATE SET T.DRIVER = S.DRIVER, T.URL = S.URL, T.USERNAME = S.USERNAME, T.PASSWORD = S.PASSWORD, ' ||
        'T.MODIFIED_BY = CURRENT USER, T.TIMESTAMP = CURRENT TIMESTAMP, T.SCHEMA = S.SCHEMA, ' ||
        'T.TIMEOUT = ' || 
        CASE WHEN TIMEOUT IS NOT NULL THEN CAST(TIMEOUT AS VARCHAR) ELSE 'NULL' END || ' ' ||
        'WHEN NOT MATCHED THEN ' ||
        'INSERT (ID, DRIVER, URL, USERNAME, PASSWORD, SCHEMA, TIMEOUT, MODIFIED_BY, TIMESTAMP) ' ||
        'VALUES (S.ID, S.DRIVER, S.URL, S.USERNAME, S.PASSWORD, ' ||
        'S.SCHEMA, ' || 
        CASE WHEN TIMEOUT IS NOT NULL THEN CAST(TIMEOUT AS VARCHAR) ELSE 'NULL' END || ', CURRENT USER, CURRENT TIMESTAMP)';

    PREPARE STMT FROM SQL_STMT;
    EXECUTE STMT;
END
@