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

/* ON_ERROR_STOP- when set to true, the psql would stop on the first error and exit with status code 3*/
\set ON_ERROR_STOP on

/*============================================================================================*/
/*  Re-creates all system procedures to their latest version                                  */
/*============================================================================================*/
BEGIN;
  \ir vcdb_system_procedures.sql
COMMIT;

/*==============================================================================*/
/* Update VPX_VERSION to set the desired VC version.                            */
/*==============================================================================*/

UPDATE vc.vpx_version
   SET ver_id = 703,
       version_value = 'VirtualCenter Database 7.0';

/*======================================================================================================================*/
/*  To ensure VPXD re-populates these table upon start-up with the current schema information they need to be truncated */
/*======================================================================================================================*/
DELETE FROM vc.VPX_TABLE;
DELETE FROM vc.VPX_INDEX_COLUMN;
DELETE FROM vc.VPX_SCHEMA_HASH;

/*======================================================================================================================*/
/*  To ensure CNS re-populates these table upon start-up with the current schema information they need to be truncated  */
/*======================================================================================================================*/
TRUNCATE TABLE cns.vpx_table;
TRUNCATE TABLE cns.vpx_index_column;

/*======================================================================================================================*/
/*  Create pg_stat_statements extension in public schema                                                                */
/*======================================================================================================================*/
CREATE EXTENSION IF NOT EXISTS pg_stat_statements WITH SCHEMA public;
ALTER EXTENSION pg_stat_statements SET SCHEMA public;

/*==================================================================*/
/*  Increase the size of TABLE_NAME and COL_NAME columns            */
/*==================================================================*/
SELECT vcdb_ndu_cln_processor(
$$INSERT INTO vc.VPX_VCDB_NDU_EXP (CLN_ID, RELEASE, CLN_STATUS) VALUES (34, 702, '') ON CONFLICT (CLN_ID) DO NOTHING;$$,
$$ALTER TABLE vc.VPX_TABLE ALTER COLUMN TABLE_NAME TYPE VARCHAR(63);$$,
$$ALTER TABLE vc.VPX_TABLE ALTER COLUMN COL_NAME TYPE VARCHAR(63);$$
);

/*==================================================================================================*/
/*  Change 65163 in B2B, corresponding NDU change ID is 12:                                         */
/*  "Add backupNfc pool to VPX_DVS_INFRA_RES_POOL"                                                  */
/*  This change is only a DML update hence its execution will be done in vcdb_db_upgrade.sql        */
/*==================================================================================================*/
SELECT vcdb_ndu_cln_processor(
$$INSERT INTO vc.VPX_VCDB_NDU_EXP (CLN_ID, RELEASE, CLN_STATUS) VALUES (12, 701, '') ON CONFLICT (CLN_ID) DO NOTHING;$$);
DO
$$
BEGIN
-- This check ensures that if the change has been applied by B2B we will not re-execute.
  IF NOT EXISTS ( SELECT PATCH_VERSION
                    FROM vc.VPX_PATCH_CONTENT
                   WHERE PATCH_VERSION = '65163')
  THEN
-- This check ensures that if the change has been applied by NDU we will not re-execute.
    IF NOT EXISTS ( SELECT CLN_ID
                      FROM vc.VPX_VCDB_NDU_EXP_APP
                     WHERE CLN_ID = 12 )
    THEN
      INSERT INTO VPX_DVS_INFRA_RES_POOL
         SELECT ID AS DVS_ID,
                'backupNfc' AS NRP_KEY,
                'vSphere Backup NFC Traffic Type' AS DESCRIPTION,
                -1 AS ALLOCATION_LIMIT,
                50 AS ALLOCATION_SHARES_SHARES,
                'normal' AS ALLOCATION_SHARES_LEVEL,
                0 AS ALLOCATION_RESERVATION
           FROM vc.VPX_DVS
          WHERE NETWORK_RESOURCE_CTRL_VERSION='version3'
            AND PRODUCT_VERSION='7.0.0'
            AND ID NOT IN (SELECT DVS_ID
                             FROM VPX_DVS_INFRA_RES_POOL
                            WHERE NRP_KEY = 'backupNfc');
    END IF;
  END IF;
END;
$$;

/*==================================================================================================*/
/*  Change 65187 in B2B, was split in two: corresponding NDU changes are ID 28(executed in expand   */
/*  phase) and ID 29(executed in vcdb_db_upgrade.sql):                                              */
/*  "Rename column IS_RESTRICTED_USER to IS_ONLY_MANAGED_BY_SP_USER and Add table VPX_EXT_UPGRADE"  */
/*==================================================================================================*/
SELECT vcdb_ndu_cln_processor(
$$INSERT INTO vc.VPX_VCDB_NDU_EXP (CLN_ID, RELEASE, CLN_STATUS) VALUES (29, 702, '') ON CONFLICT (CLN_ID) DO NOTHING;$$);
DO
$$
BEGIN
-- This check ensures that if the change has been applied by B2B we will not re-execute.
  IF NOT EXISTS ( SELECT PATCH_VERSION
                    FROM vc.VPX_PATCH_CONTENT
                   WHERE PATCH_VERSION = '65187' )
  THEN
-- This check ensures that if the change has been applied by NDU we will not re-execute.
    IF NOT EXISTS ( SELECT CLN_ID
                      FROM vc.VPX_VCDB_NDU_EXP_APP
                     WHERE CLN_ID = 29 )
    THEN
      DELETE FROM vc.VPX_EXT_UPGRADE WHERE STATUS = 0;
      INSERT INTO vc.VPX_EXT_UPGRADE (STATUS) VALUES (0);
    END IF;
  END IF;
END;
$$;

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