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

/*==============================================================*/
/* Table: VPX_VCDB_NDU_EXP                                      */
/*==============================================================*/
CREATE TABLE IF NOT EXISTS vc.VPX_VCDB_NDU_EXP (
   CLN_ID         INT NOT NULL,
   RELEASE        INT,
   CREATE_DATE    TIMESTAMP,
   CLN_STATUS     TEXT,
   CONSTRAINT PK_VPX_CLN_ID PRIMARY KEY (CLN_ID)
);

/*==============================================================*/
/* Table: VPX_VCDB_NDU_RVT                                      */
/*==============================================================*/
CREATE TABLE IF NOT EXISTS vc.VPX_VCDB_NDU_RVT (
   CLN_ID       INT,
   RELEASE      INT,
   CREATE_DATE  TIMESTAMP,
   REVERT_PROC  TEXT,
   CLN_STATUS   TEXT,
   CONSTRAINT FK_VPX_CLN_ID FOREIGN KEY(CLN_ID) REFERENCES VPX_VCDB_NDU_EXP(CLN_ID)
);

/*==============================================================*/
/* Table: VPX_VCDB_NDU_TRK                                      */
/*==============================================================*/
CREATE TABLE IF NOT EXISTS vc.VPX_VCDB_PROC_TRK (
   CLN_ID        INT,
   CLN_OWNER     TEXT,
   PROC          TEXT,
   RELEASE       INT,
   CREATE_DATE   TIMESTAMP
);

/*==============================================================*/
/* Table: VPX_VCDB_NDU_EXP_APP + dependencies                   */
/*==============================================================*/
CREATE TABLE IF NOT EXISTS vc.VPX_VCDB_NDU_EXP_APP (
   CLN_ID         INT NOT NULL,
   RELEASE        INT,
   CLN_STATUS     TEXT,
   CONSTRAINT CLN_ID_UNIQUE UNIQUE (CLN_ID)
);

/* Keep track only of applied changes in VPX_VCDB_NDU_EXP_APP */
DO
$$
  BEGIN
     IF NOT EXISTS ( SELECT oid::regproc
                       FROM pg_proc
                      WHERE proname LIKE 'applied_cln_ids_func%' )
        THEN CREATE OR REPLACE FUNCTION vc.applied_cln_ids_func()
             RETURNS TRIGGER
             AS $body_start$
             BEGIN
                BEGIN
                   INSERT INTO vc.VPX_VCDB_NDU_EXP_APP (CLN_ID, RELEASE, CLN_STATUS)
                    ( SELECT CLN_ID,
                             RELEASE,
                             CASE WHEN CLN_STATUS = 'SUCCESS'
                                    THEN 'SUCCESS'
                                  WHEN CLN_STATUS LIKE '%Fail%'
                                    THEN 'Failed'
                                  ELSE NULL
                             END
                        FROM vc.VPX_VCDB_NDU_EXP
                       WHERE CLN_STATUS IS NOT NULL
                         AND CLN_ID NOT IN (SELECT CLN_ID
                                              FROM vc.VPX_VCDB_NDU_EXP_APP ));
                END;
                BEGIN
                   DELETE
                     FROM vc.VPX_VCDB_NDU_EXP_APP src
                    WHERE EXISTS (SELECT 1
                                    FROM vc.VPX_VCDB_NDU_EXP trg
                                   WHERE src.cln_id = trg.cln_id
                                     AND trg.cln_status LIKE '%Fail%' OR cln_status LIKE '%FAIL%');
                END;
             RETURN NEW;
             END;
             $body_start$
             LANGUAGE plpgsql;
     END IF;
  END;
$$;

/* Trigger body */
DO
$$
  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 = 'applied_cln_ids_trg'
                       AND a.relname = 'vpx_vcdb_ndu_exp' )
       THEN CREATE TRIGGER applied_cln_ids_trg
              AFTER UPDATE ON vc.VPX_VCDB_NDU_EXP
              FOR EACH ROW
              WHEN (NEW.CLN_STATUS = 'SUCCESS' OR NEW.CLN_STATUS LIKE '%Fail%' OR NEW.CLN_STATUS LIKE '%FAIL%')
              EXECUTE PROCEDURE vc.applied_cln_ids_func();
    END IF;
  EXCEPTION
     WHEN OTHERS THEN
       RAISE NOTICE 'Trigger APPLIED_CLN_IDS_TRG already created  - sqlstate: %, sqlerrm: %', SQLSTATE, SQLERRM;
  END;
$$;

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