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

/*==============================================================*/
/* Create Table: cl_security_policy                             */
/*==============================================================*/
SELECT vcdb_ndu_cln_processor(
$$INSERT INTO VPX_VCDB_NDU_EXP (CLN_ID, RELEASE, CLN_STATUS) VALUES (43, 703, '') ON CONFLICT (CLN_ID) DO NOTHING;$$,
$$CALL vcdb_ndu_add_table(43,'cls',703,
'CREATE TABLE IF NOT EXISTS vc.cl_security_policy (
       id UUID NOT NULL,
       name VARCHAR(50) NOT NULL,
       creationDate TIMESTAMP,
       lastModifiedDate TIMESTAMP,
       CONSTRAINT cl_security_policy_pk PRIMARY KEY (id))');$$
);

/*==============================================================*/
/* Create Table: cl_security_rule                               */
/*==============================================================*/
SELECT vcdb_ndu_cln_processor(
$$INSERT INTO VPX_VCDB_NDU_EXP (CLN_ID, RELEASE, CLN_STATUS) VALUES (44, 703, '') ON CONFLICT (CLN_ID) DO NOTHING;$$,
$$CALL vcdb_ndu_add_table(44,'cls', 703,
'CREATE TABLE IF NOT EXISTS vc.cl_security_rule (
       id UUID NOT NULL,
       itemType VARCHAR(10) NOT NULL,
       ruleType VARCHAR(40) NOT NULL,
       CONSTRAINT cl_security_rule_pk PRIMARY KEY (id))');$$
);

/*==============================================================*/
/* Create Table: cl_security_policy_rule                        */
/*==============================================================*/

SELECT vcdb_ndu_cln_processor(
$$INSERT INTO VPX_VCDB_NDU_EXP (CLN_ID, RELEASE, CLN_STATUS) VALUES (45, 703, '') ON CONFLICT (CLN_ID) DO NOTHING;$$,
$$CALL vcdb_ndu_add_table(45,'cls', 703,
'CREATE TABLE IF NOT EXISTS vc.cl_security_policy_rule (
       policyId UUID NOT NULL REFERENCES cl_security_policy(id) ON DELETE CASCADE,
       ruleId UUID NOT NULL REFERENCES cl_security_rule(id) ON DELETE CASCADE,
       CONSTRAINT cl_security_policy_rule_pk PRIMARY KEY (policyid, ruleid))');$$
);

/*==============================================================*/
/* Create Table: cl_libraryitem_certificate_verification        */
/*==============================================================*/
SELECT vcdb_ndu_cln_processor(
$$INSERT INTO VPX_VCDB_NDU_EXP (CLN_ID, RELEASE, CLN_STATUS) VALUES (46, 703, '') ON CONFLICT (CLN_ID) DO NOTHING;$$,
$$CALL vcdb_ndu_add_table(46,'cls', 703,
'CREATE TABLE IF NOT EXISTS vc.cl_libraryitem_certificate_verification (
       id UUID NOT NULL,
       itemId UUID NOT NULL,
       verificationStatus VARCHAR(35) NOT NULL,
       certChain TEXT,
       CONSTRAINT cl_libraryitem_certificate_verification_pk PRIMARY KEY (id))');$$
);

/*==============================================================*/
/* Alter Table: Add securitypolicyid to cl_library table        */
/*==============================================================*/

SELECT vcdb_ndu_cln_processor(
$$INSERT INTO VPX_VCDB_NDU_EXP (CLN_ID, RELEASE, CLN_STATUS) VALUES (47, 703, '') ON CONFLICT (CLN_ID) DO NOTHING;$$,
$$CALL vcdb_ndu_add_column(47,'cls', 703, 'vc', 'cl_library', 'securitypolicyid', 'UUID');$$
);

/*==============================================================*/
/* Alter Table: Add securityCompliance to cl_libraryItem table  */
/*==============================================================*/
SELECT vcdb_ndu_cln_processor(
$$INSERT INTO VPX_VCDB_NDU_EXP (CLN_ID, RELEASE, CLN_STATUS) VALUES (48, 703, '') ON CONFLICT (CLN_ID) DO NOTHING;$$,
$$CALL vcdb_ndu_add_column(48,'cls', 703, 'vc', 'cl_libraryitem', 'securityCompliance', 'BOOLEAN');$$
);
