/* **************************************************************************** *
 * Copyright (c) 2021 VMware, Inc.  All rights reserved. -- VMware Confidential *
 * **************************************************************************** */

/*
 * This script is for applying the database and schema contract for vstats_coredb present in VCDB
 */

SELECT COUNT(*)=0 as not_vcdb, 'vstats_coredb' as dbname, 'vstats_coredb_schema' as db_schema  FROM pg_database WHERE datname = 'VCDB'
\gset
\if :not_vcdb
   \echo 'vstats_coredb is not present.'
   SELECT raise_exception('vstats_coredb is not present.');
\else
   \echo 'vstats_coredb is present'
   \set dbname 'VCDB'
   \connect :dbname

   SET SEARCH_PATH = :db_schema;

   \set ID1st 101

   CALL vc.vcdb_ndu_add_foreign_key_const('vstats_coredb_schema', 'acq_spec_X_endpoint', 'acq_spec_X_endpoint_acq_spec_id_fkey', 'acq_spec_id', 'acq_spec', 'id', 'ON DELETE CASCADE');

    /*
    * Drop default from column created_ts and modified_ts in acq_spec table
    */
   CALL vc.vcdb_ndu_cnt_drop_def_val('vstats_coredb_schema','acq_spec', 'created_ts');
   CALL vc.vcdb_ndu_cnt_drop_def_val('vstats_coredb_schema','acq_spec', 'modified_ts');

   /*
    * Drop trigger trigger_on_update_acq_spec from table acq_spec
    */
   DROP TRIGGER IF EXISTS trigger_on_update_acq_spec ON acq_spec;


   /*
    * Delete the current unique constraint in acq_spec table and create new unique constraint
    */
   DO
   $$
	DECLARE r record;
   BEGIN
		FOR r IN
			SELECT nsp.nspname AS tab_schema,
					rel.relname AS table_name,
					con.conname AS con_name
         FROM pg_catalog.pg_constraint con
               INNER JOIN pg_catalog.pg_class rel
                     ON rel.oid = con.conrelid
               INNER JOIN pg_catalog.pg_namespace nsp
                     ON nsp.oid = connamespace
               WHERE rel.relname LIKE 'acq_spec%'
               AND con.contype = 'u'
         LOOP
            EXECUTE 'CALL vc.vcdb_ndu_cnt_drop_constraint('''|| r.tab_schema ||''', '''|| r.table_name ||''', '''|| r.con_name ||''')';
         END LOOP;
	END;
   $$ LANGUAGE plpgsql;


   /*ALTER TABLE acq_spec ADD UNIQUE(counters, resources, interval_millis, userid); */
   CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS ACQSPEC_IDX ON acq_spec(counters, resources, interval_millis, userid);
   CALL vc.vcdb_ndu_add_unique_key_const('vstats_coredb_schema', 'acq_spec', 'ACQSPEC_IDX', 'acq_spec_counters_resources_interval_millis_userid_key');

    /*
    * Rename column vs_status to status
    */
   CALL vc.vcdb_ndu_cnt_drop_column('vstats_coredb_schema', 'acq_spec', 'vs_status');

   /*
    * Rename vs_status_ts to status_ts
    */
   CALL vc.vcdb_ndu_cnt_drop_column('vstats_coredb_schema', 'acq_spec', 'vs_status_ts');

   /* Rename TYPE vstats_status to acq_spec_status & Add new value INVALID */
   DROP TYPE IF EXISTS vstats_status CASCADE;

   DROP FUNCTION IF EXISTS raise_exception(text);
\endif
