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

/*====================================================================================================*/
/* Change 65167 in b2b_PostgreSQL.sql                                                                 */
/*                                                                                                    */
/* Change description:                                                                                */
/* Migrate VPX_HOST.MASTER_GEN and VPX_HOST.MASTER_SPEC_GEN to VPX_HOST_SYNC_GEN table                */
/*                                                                                                    */
/* Purpose of this procedure:                                                                         */
/* Make B2B change 65167 compatible with NDU process                                                  */
/*====================================================================================================*/

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

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 ( 'master_gen',
                            'master_spec_gen' )
           AND NOT attisdropped
           AND attnum > 0 ) = 2
     THEN
      /* Add table VPX_HOST_SYNC_GEN*/
      CREATE TABLE IF NOT EXISTS VPX_HOST_SYNC_GEN(
         HOST_ID          BIGINT             NOT NULL,
         MASTER_GEN       INTEGER DEFAULT 0  NOT NULL,
         MASTER_SPEC_GEN  INTEGER DEFAULT 0  NOT NULL,
         CONSTRAINT PK_HOST_SYNC_GEN PRIMARY KEY (HOST_ID),
         CONSTRAINT FK_HOST_SYNC_GEN_REF_VPX_HOST FOREIGN KEY (HOST_ID)
            REFERENCES VPX_HOST(ID) ON DELETE CASCADE);

      /* Population of table VPX_HOST_SYNC_GEN 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_65167'
                           AND a.relname = 'vpx_host' )
           THEN INSERT INTO VPX_HOST_SYNC_GEN (HOST_ID, MASTER_GEN, MASTER_SPEC_GEN)
                   SELECT t.ID AS HOST_ID,
                          CAST(t.MASTER_GEN AS INTEGER) AS MASTER_GEN,
                          CAST(t.MASTER_SPEC_GEN AS INTEGER) AS MASTER_SPEC_GEN
                     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_65167' )
            THEN CREATE OR REPLACE FUNCTION vcdb_ndu_trigger_func_mig_col_65167()
                 RETURNS TRIGGER
                 AS
                 $BODY$
                   BEGIN
                       INSERT INTO VPX_HOST_SYNC_GEN ( HOST_ID, MASTER_GEN, MASTER_SPEC_GEN )
                           VALUES(new.ID,
                                  CAST(new.MASTER_GEN as INTEGER),
                                  CAST(new.MASTER_SPEC_GEN as INTEGER)
                                 );
                       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_65167'
                           AND a.relname = 'vpx_host' )
           THEN CREATE TRIGGER vcdb_ndu_trigger_mig_trig_65167
                 AFTER INSERT ON VPX_HOST
                 FOR EACH ROW
                 EXECUTE PROCEDURE vcdb_ndu_trigger_func_mig_col_65167();
        END IF;
      END;
   END IF;
EXCEPTION
  WHEN OTHERS THEN
     RAISE LOG 'NDU Failed to perform migration change (B2b-65167) - sqlstate: %, sqlerrm: %', SQLSTATE, SQLERRM;
END;
$vcdb_ndu_mig_b2b_chg_65167$;

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