/* USAGE

call vcdb_ndu_rename_col('vcdb_team',701,'vpx_vcdb_test', 'my_json', 'renamed_my_my_json'); --> integer type
call vcdb_ndu_rename_col('vcdb_team',701,'vpx_vcdb_test', 'my_columna', 'renamed_my_columna'); --> varchar(32) type
call vcdb_ndu_rename_col('vcdb_team',701,'vpx_vcdb_test', 'number_related', 'renamed_my_number_related_column'); --> numeric(10) type
call vcdb_ndu_rename_col('vcdb_team',701,'vpx_vcdb_test', 'time_related', 'renamed_my_time_related_column'); --> timestamp w/o timezone type

*/
CREATE OR REPLACE PROCEDURE vcdb_ndu_rename_col(
cln_id       int,
cln_owner    text,
release      int,
tab_schema   varchar(50),
tab_name     varchar(50),
old_col_name varchar(50),
new_col_name varchar(50)
)
language plpgsql
AS $ndu_rnm_column$
DECLARE
  l_create_trigger_func      text;
  l_create_trigger_statement text;
  l_alter_tbl_statement      text;
  l_old_col_data_type        varchar(50);
  usr_def_data_type          varchar(50);
  is_cln_prst                int :=0;
  cln_timestamp              timestamp;
/* probably not needed as def but still I like it */
  func_tab_name            varchar(50);
  func_tab_schema          varchar(50);
  func_old_col_name        varchar(50);
  func_new_col_name        varchar(50);

BEGIN

   /* log in time when proc is executed */
   SELECT clock_timestamp() into cln_timestamp;
  /* check if the new column (renamed) is present */

  SELECT 1 into is_cln_prst
    FROM information_schema.columns
   WHERE table_schema = lower(tab_schema)
     AND table_name = lower(tab_name)
     AND column_name = lower(new_col_name);

  /* if present skip this change */

  IF is_cln_prst = 1 THEN
     RAISE LOG 'Change already applied';
     EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_EXP SET CLN_STATUS = ''Already applied'' WHERE CLN_ID = %s', cln_id);
     RETURN;
  ELSE

   BEGIN

  /* generate CLN id */

      INSERT INTO vc.VPX_VCDB_PROC_TRK (cln_id, cln_owner, proc, release, CREATE_DATE)
        SELECT cln_id,
              cln_owner,
              'vc.vcdb_ndu_rename_col('''||tab_schema||''','''||tab_name||''','''||old_col_name||''','''||new_col_name||''')',
              release,
              cln_timestamp;
   END;

  /* 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_drop_column_proc('''||tab_schema||''','''||tab_name||''','''||new_col_name||''')';
   END;

   /* prevent API dev from mixing up data types of the old / new column */
   /* data types such as json and jsonb were not present on ob-16757282
      VMware VirtualCenter 7.0.1 however must be implemented I think */

      SELECT udt_name into usr_def_data_type
           FROM information_schema.columns c
          where c.table_schema = lower(tab_schema)
        AND c.table_name = lower(tab_name)
        AND c.column_name = lower(old_col_name);

     SELECT CASE WHEN c.data_type = 'character varying' THEN 'varchar' ||'('||c.character_maximum_length||')'
                 WHEN c.data_type = 'character' THEN 'character' ||'('||c.character_maximum_length||')'
                 WHEN c.data_type like '%timestamp%' THEN 'timestamp'
                 WHEN c.data_type = 'numeric' THEN 'numeric'||'('||c.numeric_precision||')'
                 WHEN c.data_type = 'integer' THEN 'int'
                 WHEN c.data_type = 'bigint' then 'bigint'
                 WHEN c.data_type = 'date' then 'date'
                 WHEN c.data_type = 'text' then 'text'
                 WHEN c.data_type = 'boolean' then 'boolean'
                 WHEN c.data_type = 'bytea' then 'bytea'
                 WHEN c.data_type IS NULL then c.udt_name
            ELSE NULL
       END as Col_Data_Type
       into l_old_col_data_type
       FROM information_schema.columns c
      WHERE c.table_schema = lower(tab_schema)
        AND c.table_name = lower(tab_name)
        AND c.column_name = lower(old_col_name);

     l_alter_tbl_statement := 'ALTER TABLE ' || tab_schema || '.' || tab_name || ' ADD COLUMN ' || new_col_name || ' ' || l_old_col_data_type;
  /* if new column's data type is not the same as the old log an error and exit */

   BEGIN
      IF (l_old_col_data_type is NULL) THEN
          l_old_col_data_type := usr_def_data_type;

          l_alter_tbl_statement := 'ALTER TABLE ' || tab_schema || '.' || tab_name || ' ADD COLUMN ' || new_col_name || ' ' || tab_schema || '.'|| l_old_col_data_type;

      END IF;

      IF (l_old_col_data_type is NULL) THEN
          RAISE LOG 'bad column data type';
         EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_EXP SET CLN_STATUS = ''Failed and Rolledback'' WHERE CLN_ID = %s', cln_id);
         EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_RVT SET CLN_STATUS = ''SUCCESS'' WHERE CLN_ID = %s', cln_id);
         RETURN;
      END IF;
   END;

   /* prepare the create statement of the trigger function*/

   func_tab_schema = tab_schema;
   func_tab_name = tab_name;
   func_old_col_name = old_col_name;
   func_new_col_name = new_col_name;

   l_create_trigger_func = '
   CREATE OR REPLACE FUNCTION vcdb_ndu_trigger_func_'||func_tab_name||'()
        RETURNS TRIGGER
        LANGUAGE plpgsql
        AS $$
        BEGIN
           UPDATE '||func_tab_schema||'.'||func_tab_name||'
              SET ' ||func_new_col_name||' = new.'||func_old_col_name||'
            WHERE '|| func_new_col_name||' IS NULL;
           RETURN new;
        END;
        $$;
   ';
   BEGIN

  /* log the trigger statement in stdou */

      RAISE LOG '%', l_create_trigger_func;

  /*execute (create) the trigger function - we need it prior creation of the trigger itself */

      EXECUTE l_create_trigger_func;

  /* if error during trigger creation takes places error out this cln and update to failed */

   EXCEPTION when others then EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_EXP
                                          SET CLN_STATUS = ''Failed and Rolledback''
                                        WHERE CLN_ID = %s', cln_id);
                              EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_RVT SET CLN_STATUS = ''SUCCESS'' WHERE CLN_ID = %s', cln_id);

  /* log exactly where cln failed */

      RAISE LOG 'Failed creating the trigger function, %,%', SQLERRM, SQLSTATE;
   END;

   /* set up the structure to handle the NDU operation rename column
      add the new columun and initiate data sync with an update
      NOTE!!! new column data type is set automatically based on the old column !!! - intentionally */

   BEGIN

  /* abort if statement waits for more than 2 seconds to complete
     abort if statement waits for more than 1 second to obtain lock */

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

  /* modify targeted table by adding a new column with the same data type as old column */

     EXECUTE l_alter_tbl_statement;

  /* set up syncronization between old col and newly added col from the above line */

     EXECUTE 'UPDATE ' || tab_schema || '.' || tab_name || ' SET ' || new_col_name || '=' || old_col_name;

  /* if error during table modification happens error out this cln and update status to failed */

   EXCEPTION when others then EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_EXP
                                          SET CLN_STATUS = ''Failed and Rolledback''
                                        WHERE CLN_ID = %s', cln_id);
                              EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_RVT SET CLN_STATUS = ''SUCCESS'' WHERE CLN_ID = %s', cln_id);
  /* log exactly at which step failed */

      RAISE LOG 'Failed modying the table, %,%', SQLERRM, SQLSTATE;
   END;

   BEGIN

  /* abort if statement waits for more than 2 seconds to complete
     abort if statement waits for more than 1 second to obtain lock */

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

  /* precaution try drop trigger */

      EXECUTE 'DROP TRIGGER IF EXISTS vcdb_ndu_trigger_'||tab_name||' ON '|| tab_schema || '.' || tab_name;

  /* create the trigger which keeps the columns synced*/

     l_create_trigger_statement = '
           CREATE TRIGGER vcdb_ndu_trigger_'||tab_name||'
           AFTER INSERT
           ON ' ||tab_schema||'.'||tab_name||'
           FOR EACH ROW
           EXECUTE PROCEDURE vcdb_ndu_trigger_func_'||func_tab_name||'();
     ';
      EXECUTE l_create_trigger_statement;

  /* if no failure on the above steps we can assume change is successful thus reflecting cln's status */

      EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_EXP SET CLN_STATUS = ''SUCCESS'' WHERE CLN_ID = %s', cln_id);

  /* if error during trigger creating and addition to the table happens error out this cln and update status to failed */

   EXCEPTION when others then EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_EXP
                                          SET CLN_STATUS = ''Failed and Rolledback''
                                        WHERE CLN_ID = %s', cln_id);
                              EXECUTE 'UPDATE vc.VPX_VCDB_NDU_RVT
                                          SET CLN_STATUS = ''SUCCESS''
                                        WHERE CLN_ID = %s', cln_id;

  /* log exactly on which step we fail */

      RAISE LOG 'Failed creating the trigger, %,%',SQLERRM, SQLSTATE;
   END;
  END IF;
END;
$ndu_rnm_column$;