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

/*====================================================================================================*/
/* VCDB_NDU_CLEAN_UP_PROCESS.sql                                                                      */
/*====================================================================================================*/
/*                                                                                                    */
/* This procedure is responsible for the clean-up of the environment after NDU has completed.         */
/*                                                                                                    */
/*====================================================================================================*/

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

DECLARE
    _tbl_trg_in RECORD;
BEGIN

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

   /* Clean-up of table vpx_vcdb_ndu_exp */
   BEGIN
     IF EXISTS (SELECT *
                  FROM pg_tables
                 WHERE schemaname = 'vc'
                   AND tablename  = 'vpx_vcdb_ndu_exp')
        THEN TRUNCATE TABLE vpx_vcdb_ndu_exp CASCADE;
        RAISE LOG 'Table vpx_vcdb_ndu_exp truncated';
     END IF;
   EXCEPTION
     WHEN OTHERS THEN
       RAISE LOG 'Failed to truncate vpx_vcdb_ndu_exp - sqlstate: %, sqlerrm: %', SQLSTATE, SQLERRM;
   END;

   /* Clean-up of table vpx_vcdb_ndu_rvt */
   BEGIN
     IF EXISTS (SELECT *
                  FROM pg_tables
                 WHERE schemaname = 'vc'
                   AND tablename  = 'vpx_vcdb_ndu_rvt')
        THEN TRUNCATE TABLE vpx_vcdb_ndu_rvt;
        RAISE LOG 'Table vpx_vcdb_ndu_rvt truncated';
     END IF;
   EXCEPTION
     WHEN OTHERS THEN
       RAISE LOG 'Failed to truncate table vpx_vcdb_ndu_rvt - sqlstate: %, sqlerrm: %', SQLSTATE, SQLERRM;
   END;

   /* Clean-up of table vpx_vcdb_proc_trk */
   BEGIN
     IF EXISTS (SELECT *
                  FROM pg_tables
                 WHERE schemaname = 'vc'
                   AND tablename  = 'vpx_vcdb_proc_trk')
        THEN TRUNCATE TABLE vpx_vcdb_proc_trk CASCADE;
        RAISE LOG 'Table vpx_vcdb_proc_trk truncated';
     END IF;
   EXCEPTION
     WHEN OTHERS THEN
       RAISE LOG 'Failed to truncate table vpx_vcdb_proc_trk - sqlstate: %, sqlerrm: %', SQLSTATE, SQLERRM;
   END;

   /* Clean-up the environement from NDU created trigger/s */
   BEGIN
     FOR _tbl_trg_in IN
       SELECT n.nspname AS tab_schema,
              a.relname AS table_with_trigger,
              b.tgname AS trigger_name
         FROM pg_class a, pg_trigger b, pg_namespace n
        WHERE n.oid = a.relnamespace
          AND n.nspname IN ('vc','cns','vstats_coredb_schema', 'cls')
          AND a.oid IN ( SELECT tgrelid
                           FROM pg_trigger
                          WHERE tgname LIKE 'vcdb_ndu_trigger_%%' )
          AND a.oid = b.tgrelid
          AND tgname LIKE 'vcdb_ndu_trigger_%%'
     LOOP
         EXECUTE 'DROP TRIGGER IF EXISTS ' || quote_ident(_tbl_trg_in.trigger_name) || ' ON ' || quote_ident(_tbl_trg_in.tab_schema)||'.'||quote_ident(_tbl_trg_in.table_with_trigger);
         RAISE LOG 'Trigger dropped: %', quote_ident(_tbl_trg_in.trigger_name);
     END LOOP;
   EXCEPTION
     WHEN OTHERS THEN
       RAISE LOG 'Failed to drop trigger % on table %.% - sqlstate: %, sqlerrm: %', _tbl_trg_in.trigger_name, _tbl_trg_in.tab_schema, _tbl_trg_in.table_with_trigger, SQLSTATE, SQLERRM;
   END;
END;
$vcdb_ndu_clean_up_process$;

----------------------------------------------------
-- END OF FUNCTION
----------------------------------------------------