/*-
 * ============LICENSE_START=======================================================
 * Base Package
 * ================================================================================
 * Copyright (C) 2017 AT&T Intellectual Property. All rights reserved.
 * ================================================================================
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *      http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 * ============LICENSE_END=========================================================
 */

set foreign_key_checks=0; 
DROP TABLE IF EXISTS XACML.SERVICEGROUP;
CREATE TABLE XACML.SERVICEGROUP
(
id integer NOT NULL,
name varchar(32),
type varchar(16),
transportprotocol varchar(255),
appprotocol varchar(255),
ports varchar(255),
description varchar(255),
primary key(id)
)
;

GRANT INSERT, UPDATE, DELETE, SELECT ON XACML.SERVICEGROUP TO POLICY_USER;

DROP TABLE IF EXISTS XACML.ENFORCINGTYPE;
CREATE TABLE XACML.ENFORCINGTYPE
(
ID INTEGER NOT NULL,
ENFORCINGTYPE VARCHAR(255) NOT NULL,
SCRIPT VARCHAR(5000) NOT NULL,
CONNECTIONQUERY VARCHAR(1000) NOT NULL,
VALUEQUERY VARCHAR(1000) NOT NULL,
PRIMARY KEY(ID)
);

GRANT INSERT, UPDATE, DELETE, SELECT ON XACML.ENFORCINGTYPE TO POLICY_USER;

DROP TABLE IF EXISTS XACML.ACTIONPOLICYDICT;
CREATE TABLE XACML.ACTIONPOLICYDICT 
(
ID INTEGER NOT NULL,
TYPE VARCHAR(45) NOT NULL,
URL VARCHAR(45) NOT NULL,
METHOD VARCHAR(45) NOT NULL,
HEADERS VARCHAR(1024) NOT NULL,
BODY VARCHAR(4096) NOT NULL,
CREATED_DATE   TIMESTAMP NOT NULL,
CREATED_BY    VARCHAR(45) NOT NULL,
MODIFIED_DATE  TIMESTAMP NOT NULL,
MODIFIED_BY    VARCHAR(45) NOT NULL,
ATTRIBUTE_NAME VARCHAR(45) NOT NULL,
DESCRIPTION VARCHAR(1024),
PRIMARY KEY(ID)
);

GRANT INSERT, UPDATE, DELETE, SELECT ON XACML.ACTIONPOLICYDICT TO POLICY_USER;

DROP TABLE IF EXISTS XACML.TERM;
CREATE TABLE XACML.TERM
(
ID INTEGER NOT NULL,
TERMNAME VARCHAR(45) NOT NULL,
SRCIPLIST VARCHAR(100),
DESTIPLIST VARCHAR(100),
PROTOCOLLIST VARCHAR(100),
PORTLIST VARCHAR(100),
SRCPORTLIST VARCHAR(100),
DESTPORTLIST VARCHAR(100),
ACTION VARCHAR(100),
DESCRIPTION VARCHAR(100),
PRIMARY KEY(ID)
);

GRANT INSERT, UPDATE, DELETE, SELECT ON XACML.TERM TO POLICY_USER;

DROP TABLE IF EXISTS XACML.PROTOCOLLIST;
CREATE TABLE XACML.PROTOCOLLIST
(
ID INTEGER NOT NULL,
PROTOCOLNAME VARCHAR(45) NOT NULL,
DESCRIPTION VARCHAR(64),
PRIMARY KEY(ID)
);

GRANT INSERT, UPDATE, DELETE, SELECT ON XACML.PROTOCOLLIST TO POLICY_USER;

DROP TABLE IF EXISTS XACML.PORTLIST;
CREATE TABLE XACML.PORTLIST
(
ID INTEGER NOT NULL,
PORTNAME VARCHAR(45) NOT NULL,
DESCRIPTION VARCHAR(64),
PRIMARY KEY(ID)
);

GRANT INSERT, UPDATE, DELETE, SELECT ON XACML.PORTLIST TO POLICY_USER;

DROP TABLE IF EXISTS XACML.PREFIXLIST;
CREATE TABLE XACML.PREFIXLIST
(
ID INTEGER NOT NULL,
PL_NAME VARCHAR(45) NOT NULL,
PL_VALUE VARCHAR(64),
DESCRIPTION VARCHAR(255) NULL, 
PRIMARY KEY(ID)
);

GRANT INSERT, UPDATE, DELETE, SELECT ON XACML.PREFIXLIST TO POLICY_USER;

DROP TABLE IF EXISTS XACML.ADDRESSGROUP;
CREATE TABLE XACML.ADDRESSGROUP
(
 ID INTEGER NOT NULL,
 NAME VARCHAR(32),
 DESCRIPTION VARCHAR(64),
 PREFIXLIST VARCHAR(255), 
 PRIMARY KEY(ID)
);

GRANT INSERT, UPDATE, DELETE, SELECT ON XACML.ADDRESSGROUP TO POLICY_USER;

DROP TABLE IF EXISTS XACML.POLICYSCORE;
CREATE TABLE XACML.POLICYSCORE
(
POLICY_NAME VARCHAR(100) NOT NULL,
POLICY_SCORE VARCHAR(100),
PRIMARY KEY(POLICY_NAME)
);

GRANT INSERT, UPDATE, DELETE, SELECT ON XACML.POLICYSCORE TO POLICY_USER;

DROP TABLE IF EXISTS XACML.VSCLACTION;
CREATE TABLE XACML.VSCLACTION
(
ID INTEGER NOT NULL,
VSCL_ACTION VARCHAR(45) NOT NULL,
DESCRIPTION VARCHAR(45) NULL,
CREATED_DATE TIMESTAMP NOT NULL,
CREATED_BY VARCHAR(45) NOT NULL,
MODIFIED_DATE TIMESTAMP NOT NULL,
MODIFIED_BY VARCHAR(45) NOT NULL,
PRIMARY KEY(ID)
);

GRANT INSERT, UPDATE, DELETE, SELECT ON XACML.VSCLACTION TO POLICY_USER;

DROP TABLE IF EXISTS XACML.VNFTYPE;
CREATE TABLE XACML.VNFTYPE
(
ID INTEGER NOT NULL,
VNF_TYPE VARCHAR(45) NOT NULL,
DESCRIPTION VARCHAR(45) NULL,
CREATED_DATE TIMESTAMP NOT NULL,
CREATED_BY VARCHAR(45) NOT NULL,
MODIFIED_DATE TIMESTAMP NOT NULL,
MODIFIED_BY VARCHAR(45) NOT NULL,
PRIMARY KEY(ID)
);

GRANT INSERT, UPDATE, DELETE, SELECT ON XACML.VNFTYPE TO POLICY_USER;

SET FOREIGN_KEY_CHECKS=0; 

DROP TABLE IF EXISTS XACML.ATTRIBUTE;
CREATE TABLE XACML.ATTRIBUTE
(
ID INTEGER NOT NULL, 
CREATED_BY VARCHAR(255) NOT NULL, 
CREATED_DATE TIMESTAMP, 
DESCRIPTION VARCHAR(2048),
IS_DESIGNATOR CHAR(1) NOT NULL, 
MODIFIED_BY VARCHAR(255) NOT NULL, 
MODIFIED_DATE TIMESTAMP NOT NULL, 
SELECTOR_PATH VARCHAR(2048),
XACML_ID VARCHAR(500) NOT NULL,  
CATEGORY INTEGER, 
CONSTRAINT_TYPE INTEGER, 
DATATYPE INTEGER, 
PRIORITY VARCHAR(45), 
ATTRIBUTE_VALUE VARCHAR(45),
PRIMARY KEY(ID)
);

GRANT INSERT, UPDATE, DELETE, SELECT ON XACML.ATTRIBUTE TO POLICY_USER;

DROP TABLE IF EXISTS XACML.DECISIONSETTINGS;
CREATE TABLE XACML.DECISIONSETTINGS
(
  ID INTEGER NOT NULL, 
  XACML_ID VARCHAR(45) NOT NULL, 
  DESCRIPTION VARCHAR(45), 
  DATATYPE VARCHAR(45) NOT NULL, 
  CREATED_BY VARCHAR(45) NOT NULL, 
  MODIFIED_DATE TIMESTAMP NOT NULL, 
  MODIFIED_BY VARCHAR(45) NOT NULL, 
  PRIORITY VARCHAR(45), 
  CREATED_DATE TIMESTAMP NOT NULL,
  PRIMARY KEY(ID)
); 

GRANT INSERT, UPDATE, DELETE, SELECT ON XACML.DECISIONSETTINGS TO POLICY_USER;

DROP TABLE IF EXISTS XACML.POLICY_MANAGEMENT;
CREATE TABLE XACML.POLICY_MANAGEMENT
(
  ID INTEGER NOT NULL, 
  CONFIG_NAME VARCHAR(45) NOT NULL, 
  CREATE_DATE_TIME TIMESTAMP, 
  CREATED_BY VARCHAR(45) NOT NULL, 
  ECOMP_NAME VARCHAR(45) NOT NULL, 
  POLICY_NAME VARCHAR(45) NOT NULL, 
  SCOPE VARCHAR(45) NOT NULL, 
  UPDATE_DATE_TIME TIMESTAMP NOT NULL, 
  UPDATED_BY VARCHAR(45) NOT NULL, 
  XML TEXT NOT NULL,
  PRIMARY KEY(ID)
); 

GRANT INSERT, UPDATE, DELETE, SELECT ON XACML.POLICY_MANAGEMENT TO POLICY_USER;

DROP TABLE IF EXISTS XACML.SCOPES;
CREATE TABLE XACML.SCOPES
(
  ID INTEGER NOT NULL, 
  SCOPE VARCHAR(45) NOT NULL, 
  PARENT_SCOPE INTEGER, 
  PRIMARY KEY(ID)
); 

GRANT INSERT, UPDATE, DELETE, SELECT ON XACML.SCOPES TO POLICY_USER;


set foreign_key_checks=1;