/* *****************************************************************************************************************************
 * Copyright 2020 VMware, Inc.   All rights reserved. -- VMware Confidential
 * ****************************************************************************************************************************/

/*================================================================================================================================*/
/* VCDB_NDU_EXP_ADD_COL_DEF_VAL.sql                                                                                               */
/*================================================================================================================================*/
/* Execution Instructions & Examples:                                                                                             */
/*                                                                                                                                */
/* --Scenario 1 -> modify an existing EMPTY column of a table (change data type)                                                  */
/* call vcdb_ndu_mod_col_data_type('vcdb_team', 701, 'vc', 'vpx_vcdb_test', 'my_json', 0, 'VARCHAR(11)','');                      */
/*                                                                                                                                */
/* --Scenario 2 -> modify an existing non-empty column of a table (change data type)                                              */
/* call vcdb_ndu_mod_col_data_type('vcdb_team', 701, 'vc', 'vpx_vcdb_test', 'my_columna', 1, 'TEXT','my_new_col_with_changed_DT');*/
/*                                                                                                                                */
/* NOTE:                                                                                                                          */
/* Procedure does not support array, json, special data type or user defined data types modifications for an array and json.      */
/*                                                                                                                                */
/* For additional detail visit confluence page:                                                                                   */
/* https://confluence.eng.vmware.com/display/VCUSOF/Operation+ALTER+TABLE+ALTER+COLUMN+SET+DATA+TYPE+template                     */
/*================================================================================================================================*/

CREATE OR REPLACE PROCEDURE vcdb_ndu_mod_col_data_type
(
cln_id          INT,
cln_owner       TEXT,
release         INT,
tab_schema      VARCHAR(50),
tab_name        VARCHAR(50),
old_col_name    VARCHAR(50),
is_empty        INT, -- 0 for empty, 1 for not empty
data_type       TEXT,
new_col_name    VARCHAR(50) DEFAULT ''
)
 LANGUAGE plpgsql
AS $vcdb_ndu_mod_col_data_type$

DECLARE
    l_alter_col                 TEXT;
    l_add_col                   TEXT;
    l_create_trigger_func       TEXT;
    l_create_trigger_statement  TEXT;
    l_update_sync_col_statement TEXT;
    cln_timestamp               TIMESTAMP;

BEGIN

   SET LOCAL statement_timeout = 2000;
   SET LOCAL lock_timeout = '2s';

   /* Prepare function variables */
   l_alter_col := FORMAT('ALTER TABLE %I.%I ALTER COLUMN %I SET DATA TYPE %I', tab_schema, tab_name, old_col_name, data_type);
   l_add_col := FORMAT('ALTER TABLE %I.%I ADD COLUMN %I %I', tab_schema, tab_name, new_col_name, data_type);
   l_update_sync_col_statement := FORMAT('UPDATE %I.%I SET %I = %I', tab_schema, tab_name, new_col_name, old_col_name);
   l_create_trigger_func := FORMAT('CREATE OR REPLACE FUNCTION vc.vcdb_ndu_col_data_type_trigger_func_%I()
                                    RETURNS TRIGGER
                                     LANGUAGE plpgsql
                                    AS $$
                                    BEGIN
                                       UPDATE %I.%I
                                          SET %I = new.%I
                                        WHERE %I IS NULL;
                                       RETURN new;
                                    END;
                                    $$', tab_name, tab_schema, tab_name, new_col_name, old_col_name, new_col_name);
   l_create_trigger_statement := FORMAT ('CREATE TRIGGER vcdb_ndu_trigger_%I
                                            AFTER INSERT
                                            ON %I.%I
                                            FOR EACH ROW
                                            EXECUTE PROCEDURE vcdb_ndu_col_data_type_trigger_func_%I()', tab_name, tab_schema, tab_name, tab_name);

   /* Create a CLN_ID for the change */
      INSERT INTO vc.VPX_VCDB_PROC_TRK (cln_id, cln_owner, proc, release, CREATE_DATE)
        SELECT cln_id,
                cln_owner,
                'vc.vcdb_ndu_mod_col_data_type('''||tab_schema||''','''||tab_name||''','''||old_col_name||''')',
                release,
                cln_timestamp;

   /* Generate respective revert statement */
   BEGIN
      INSERT INTO vc.VPX_VCDB_NDU_RVT (cln_id, release, CREATE_DATE, revert_proc)
        SELECT cln_id,
                release,
                cln_timestamp,
                'vc.vcdb_ndu_RVT_mod_col_data_type('''||tab_schema||''','''||tab_name||''','''||new_col_name||''')';
   END;

   /* Scenario 1 */
   BEGIN
   /*===============================================================================*/
   /* Scenario 1:                                                                   */
   /* Alter an empty column to change its data type mind that no warning/check from */
   /* TEXT to int or similar to such data type modification is implemented, this    */
   /* ensures that the data type will be modified as long as the column exists,     */
   /* however application must be able to work with such structural change.         */
   /*===============================================================================*/

      IF ( is_empty = 0 )
        THEN EXECUTE l_alter_col;
        EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_EXP SET CLN_STATUS = ''SUCCESS'' WHERE CLN_ID = %s', cln_id);
      END IF;
   EXCEPTION
      WHEN OTHERS THEN
         EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_EXP SET CLN_STATUS = ''Failed and Rolledback'' WHERE CLN_ID = %s', cln_id);
         RAISE LOG 'Failed to modify data type % on table %.% - sqlstate: %, sqlerrm: %', old_col_name, tab_schema, tab_name, SQLSTATE, SQLERRM;
   END;

   /* Scenario 2 */
   BEGIN
   /*==================================================================================*/
   /* Scenario 2:                                                                      */
   /* Alter a non-empty column on a table used by the app. Old data must be            */
   /* set in a manner that can be transfered to the column with new data type.         */
   /* Service must be aware that a new column will be added so it should work with it. */
   /*==================================================================================*/

      IF ( is_empty = 1 ) THEN
      /* modify the structure as needed */
         BEGIN
            EXECUTE l_add_col;
            EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_EXP SET CLN_STATUS = ''SUCCESS'' WHERE CLN_ID = %s', cln_id);
         EXCEPTION
            WHEN OTHERS THEN
              EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_EXP SET CLN_STATUS = ''Failed and Rolledback'' WHERE CLN_ID = %s', cln_id);
              RAISE LOG 'Failed creating new column with new data type - sqlstate: %, sqlerrm: %', SQLSTATE, SQLERRM;
         END;

         BEGIN
            EXECUTE l_update_sync_col_statement;
         EXCEPTION
            WHEN OTHERS THEN
              EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_EXP SET CLN_STATUS = ''Failed and Rolledback'' WHERE CLN_ID = %s', cln_id);
              RAISE LOG 'Failed syncing up old and new column with new data type - sqlstate: %, sqlerrm: %', SQLSTATE, SQLERRM;
         END;

      /* Build + Execute the trigger function */

         BEGIN
            EXECUTE l_create_trigger_func;
         EXCEPTION
            WHEN OTHERS THEN
              EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_EXP SET CLN_STATUS = ''Failed and Rolledback'' WHERE CLN_ID =%s', cln_id);
              RAISE LOG 'Failed creating trigger function - sqlstate: %, sqlerrm: %', SQLSTATE, SQLERRM;
         END;

      /* Build + Execute the trigger */
         BEGIN
            EXECUTE FORMAT('DROP TRIGGER IF EXISTS vcdb_ndu_trigger ON %I.%I CASCADE', tab_schema, tab_name);
            EXECUTE l_create_trigger_statement;
         EXCEPTION
            WHEN OTHERS THEN
              EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_EXP SET CLN_STATUS = ''Failed and Rolledback'' WHERE CLN_ID = %s', cln_id);
              RAISE LOG 'Failed creating the trigger - sqlstate: %, sqlerrm: %', SQLSTATE, SQLERRM;
         END;
      END IF;
    END;

END;
$vcdb_ndu_mod_col_data_type$;

----------------------------------------------------
-- END OF FUNCTION
----------------------------------------------------