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

/*====================================================================================================*/
/* Change 65181 in b2b_PostgreSQL.sql                                                                 */
/*                                                                                                    */
/* Change description:                                                                                */
/* Rename table VPX_HOST_SERVICE_MODE_STATE to VPX_HOST_PARTIAL_MM_STATUS                             */
/*                                                                                                    */
/* Purpose of this procedure:                                                                         */
/* Make B2B change 65181 compatible with NDU process                                                  */
/*====================================================================================================*/

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

BEGIN

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

   IF EXISTS ( SELECT TRUE
                 FROM pg_tables
                WHERE schemaname = 'vc'
                  AND tablename  = 'vpx_host_service_mode_state' )
     THEN

      /* Add table VPX_HOST_PARTIAL_MM_STATUS */
      CREATE TABLE IF NOT EXISTS VPX_HOST_PARTIAL_MM_STATUS(
         HOST_ID              BIGINT  NOT NULL,
         PARTIAL_MM_STATUS  TEXT NOT NULL,
         CONSTRAINT PK_VPX_HOST_PARTIAL_MM_STATUS PRIMARY KEY (HOST_ID),
         CONSTRAINT FK_VPX_HOST_PARTIAL_MM_STATUS FOREIGN KEY (HOST_ID)
            REFERENCES VPX_HOST (ID) ON DELETE CASCADE);

      /* Population of table VPX_HOST_PARTIAL_MM_STATUS from VPX_HOST_SERVICE_MODE_STATE */
      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_rnm_trig_65181'
                           AND a.relname = 'vpx_host_service_mode_state' )
           THEN INSERT INTO VPX_HOST_PARTIAL_MM_STATUS (HOST_ID, PARTIAL_MM_STATUS)
                   SELECT t.HOST_ID AS HOST_ID,
                          t.SERVICE_MODE_STATES AS PARTIAL_MM_STATUS
                     FROM VPX_HOST_SERVICE_MODE_STATE 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_rnm_65181' )
            THEN CREATE OR REPLACE FUNCTION vcdb_ndu_trigger_func_rnm_65181()
                 RETURNS TRIGGER
                 AS
                 $BODY$
                   BEGIN
                       INSERT INTO VPX_HOST_PARTIAL_MM_STATUS ( HOST_ID, PARTIAL_MM_STATUS )
                           VALUES(new.HOST_ID,new.SERVICE_MODE_STATES);
                       RETURN new;
                   END;
                 $BODY$
                 LANGUAGE plpgsql;
         END IF;
      END;

      /* Trigger for newly inserted rows in VPX_HOST_SERVICE_MODE_STATE */
      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_rnm_trig_65181'
                           AND a.relname = 'vpx_host_service_mode_state' )
           THEN CREATE TRIGGER vcdb_ndu_trigger_rnm_trig_65181
                 AFTER INSERT ON vpx_host_service_mode_state
                 FOR EACH ROW
                 EXECUTE PROCEDURE vcdb_ndu_trigger_func_rnm_65181();
        END IF;
      END;
   END IF;
EXCEPTION
  WHEN OTHERS THEN
     RAISE LOG 'NDU Failed to perform migration change (B2B-65181 - sqlstate: %, sqlerrm: %', SQLSTATE, SQLERRM;
END;
$vcdb_ndu_rnm_b2b_chg_65181$;

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