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

/*====================================================================================================*/
/* Change 65173 in b2b_PostgreSQL.sql                                                                 */
/*                                                                                                    */
/* Change description:                                                                                */
/* Migrate VPX_HOST.LAST_PERF_TIME and VPX_HOST.PERF_REFRESH_RATE to VPX_HOST_PERFINFO table.         */
/*                                                                                                    */
/* Purpose of this procedure:                                                                         */
/* Make B2B change 65173 compatible with NDU process.                                                 */
/*====================================================================================================*/

CREATE OR REPLACE PROCEDURE vcdb_ndu_mig_b2b_chg_65173()
 LANGUAGE plpgsql
AS $vcdb_ndu_mig_b2b_chg_65173$

BEGIN

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

   IF ( SELECT COUNT(1)
          FROM pg_attribute
         WHERE attrelid = 'vpx_host'::regclass
           AND attname IN ( 'last_perf_time',
                            'perf_refresh_rate' )
           AND NOT attisdropped
           AND attnum > 0 ) = 2
     THEN
      /* Add table VPX_HOST_PERFINFO*/
      CREATE TABLE IF NOT EXISTS VPX_HOST_PERFINFO(
         HOST_ID              BIGINT  NOT NULL,
         LAST_PERF_TIME       TIMESTAMP,
         PERF_REFRESH_RATE    INTEGER,
         CONSTRAINT PK_HOST_PERFINFO PRIMARY KEY (HOST_ID),
         CONSTRAINT FK_HOST_PERFINFO FOREIGN KEY (HOST_ID)
            REFERENCES VPX_HOST(ID) ON DELETE CASCADE);

      /* Population of table VPX_HOST_PERFINFO with columns from VPX_HOST */
      BEGIN
        IF NOT EXISTS ( SELECT a.relname AS table_with_trigger,
                               b.tgname AS trigger_name
                          FROM pg_class a,
                               pg_trigger b
                         WHERE b.tgname = 'vcdb_ndu_trigger_mig_trig_65173'
                           AND a.relname = 'vpx_host' )
           THEN INSERT INTO VPX_HOST_PERFINFO (HOST_ID, LAST_PERF_TIME, PERF_REFRESH_RATE)
                   SELECT t.ID AS HOST_ID,
                          t.LAST_PERF_TIME AS LAST_PERF_TIME,
                          t.PERF_REFRESH_RATE AS PERF_REFRESH_RATE
                     FROM VPX_HOST t;
        END IF;
      END;

      /* Trigger(body) function for newly inserted rows */
      BEGIN
         IF NOT EXISTS ( SELECT oid::regproc
                           FROM pg_proc
                          WHERE proname = 'vcdb_ndu_trigger_func_mig_col_65173' )
            THEN CREATE OR REPLACE FUNCTION vcdb_ndu_trigger_func_mig_col_65173()
                 RETURNS TRIGGER
                 AS
                 $BODY$
                   BEGIN
                       INSERT INTO VPX_HOST_PERFINFO (HOST_ID, LAST_PERF_TIME, PERF_REFRESH_RATE)
                           VALUES(new.ID,new.LAST_PERF_TIME,new.PERF_REFRESH_RATE);
                       RETURN new;
                   END;
                 $BODY$
                 LANGUAGE plpgsql;
         END IF;
      END;

      /* Trigger for newly inserted rows in VPX_HOST */
      BEGIN
        IF NOT EXISTS ( SELECT a.relname AS table_with_trigger,
                               b.tgname AS trigger_name
                          FROM pg_class a,
                               pg_trigger b
                         WHERE b.tgname = 'vcdb_ndu_trigger_mig_trig_65173'
                           AND a.relname = 'vpx_host' )
           THEN CREATE TRIGGER vcdb_ndu_trigger_mig_trig_65173
                 AFTER INSERT ON VPX_HOST
                 FOR EACH ROW
                 EXECUTE PROCEDURE vcdb_ndu_trigger_func_mig_col_65173();
        END IF;
      END;
   END IF;
EXCEPTION
  WHEN OTHERS THEN
     RAISE LOG 'NDU Failed to perform migration change (B2B-65173) - sqlstate: %, sqlerrm: %', SQLSTATE, SQLERRM;
END;
$vcdb_ndu_mig_b2b_chg_65173$;

-----------------------------------------------------------------------------
-- END OF FILE
-----------------------------------------------------------------------------