/*-
 * ============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.ENFORCINGTYPE;
DROP TABLE IF EXISTS XACML.ACTIONPOLICYDICT;
DROP TABLE IF EXISTS XACML.TERM;
DROP TABLE IF EXISTS XACML.PROTOCOLLIST;
DROP TABLE IF EXISTS XACML.PORTLIST;
DROP TABLE IF EXISTS XACML.PREFIXLIST;
DROP TABLE IF EXISTS XACML.ADDRESSGROUP;
DROP TABLE IF EXISTS XACML.POLICYSCORE;
DROP TABLE IF EXISTS XACML.VSCLACTION;
DROP TABLE IF EXISTS XACML.VNFTYPE;
DROP TABLE IF EXISTS XACML.ATTRIBUTE;
DROP TABLE IF EXISTS XACML.DECISIONSETTINGS;
DROP TABLE IF EXISTS XACML.POLICY_MANAGEMENT;
DROP TABLE IF EXISTS XACML.SCOPES;

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),
`desc` 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,
 connectionQuery VARCHAR(255) NOT NULL,
 enforcingType VARCHAR(255) NOT NULL UNIQUE,
 script VARCHAR(255) NOT NULL,
 valueQuery VARCHAR(255) NOT NULL,
 PRIMARY KEY (id)
)
;

DROP TABLE IF EXISTS XACML.ActionPolicyDict;
CREATE TABLE XACML.ActionPolicyDict
(
id INTEGER NOT NULL,
 ATTRIBUTE_NAME VARCHAR(45) NOT NULL,
 Body VARCHAR(4096),
 created_by VARCHAR(255) NOT NULL,
 created_date TIMESTAMP,
 description VARCHAR(2048),
 Headers VARCHAR(1024),
 Method VARCHAR(45) NOT NULL,
 modified_by VARCHAR(255) NOT NULL,
 modified_date TIMESTAMP NOT NULL,
 Type VARCHAR(45) NOT NULL,
 URL VARCHAR(45) NOT NULL,
 PRIMARY KEY (id)
)
;

DROP TABLE IF EXISTS XACML.TERM;
CREATE TABLE XACML.TERM
(
id INTEGER NOT NULL,
 description VARCHAR(100),
 action VARCHAR(100),
 destIPList VARCHAR(100),
 destPortList VARCHAR(100),
 portList VARCHAR(100),
 protocolList VARCHAR(100),
 srcIPList VARCHAR(100),
 srcPortList VARCHAR(100),
 termName VARCHAR(45) NOT NULL,
 PRIMARY KEY (id)
)
;

DROP TABLE IF EXISTS XACML.PROTOCOLLIST;
CREATE TABLE XACML.PROTOCOLLIST
(
id INTEGER NOT NULL,
 description VARCHAR(64),
 protocolname VARCHAR(45) NOT NULL,
 PRIMARY KEY (id)
)
;

DROP TABLE IF EXISTS XACML.PORTLIST;
CREATE TABLE XACML.PORTLIST
(
id INTEGER NOT NULL,
 description VARCHAR(64),
 portname VARCHAR(45) NOT NULL,
 PRIMARY KEY (id)
)
;

DROP TABLE IF EXISTS XACML.PREFIXLIST;
CREATE TABLE XACML.PREFIXLIST
(
id INTEGER NOT NULL,
 pl_name VARCHAR(45) NOT NULL,
 pl_value VARCHAR(64) NOT NULL,
 description VARCHAR(255),
 PRIMARY KEY (id)
)
;

DROP TABLE IF EXISTS XACML.ADDRESSGROUP;
CREATE TABLE ADDRESSGROUP
(
 id INTEGER NOT NULL,
 name varchar(32),
 description VARCHAR(64),
 prefixlist varchar(255)
);

CREATE INDEX idx_addressgroup on XACML.ADDRESSGROUP(ID);

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

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)
);

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)
)
;

DROP TABLE IF EXISTS XACML.ATTRIBUTE;
CREATE TABLE XACML.ATTRIBUTE
(
id INTEGER NOT NULL,
 ATTRIBUTE_VALUE VARCHAR(255),
 created_by VARCHAR(255) NOT NULL,
 created_date TIMESTAMP,
 description VARCHAR(2048),
 is_designator CHAR NOT NULL,
 modified_by VARCHAR(255) NOT NULL,
 modified_date TIMESTAMP NOT NULL,
 PRIORITY VARCHAR(45),
 selector_path VARCHAR(2048),
 xacml_id VARCHAR(100) NOT NULL UNIQUE,
 category INTEGER,
 constraint_type INTEGER,
 datatype INTEGER,
 PRIMARY KEY (id)
)
;

DROP TABLE IF EXISTS XACML.DECISIONSETTINGS;
CREATE TABLE XACML.DECISIONSETTINGS
(
id INTEGER NOT NULL,
 created_by VARCHAR(255) NOT NULL,
 created_date TIMESTAMP,
 description VARCHAR(2048),
 modified_by VARCHAR(255) NOT NULL,
 modified_date TIMESTAMP NOT NULL,
 PRIORITY VARCHAR(45),
 xacml_id VARCHAR(45) NOT NULL UNIQUE,
 datatype INTEGER,
 PRIMARY KEY (id)
)
;

DROP TABLE IF EXISTS XACML.POLICY_MANANGEMENT;
CREATE TABLE XACML.POLICY_MANANGEMENT
(
id INTEGER NOT NULL,
 CONFIG_NAME VARCHAR(45) NOT NULL,
 CREATE_DATE_TIME TIMESTAMP NOT NULL,
 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 XACML.ENFORCINGTYPE;
GRANT INSERT, UPDATE, DELETE, SELECT XACML.ACTIONPOLICYDICT;
GRANT INSERT, UPDATE, DELETE, SELECT XACML.TERM;
GRANT INSERT, UPDATE, DELETE, SELECT XACML.PROTOCOLLIST;
GRANT INSERT, UPDATE, DELETE, SELECT XACML.PORTLIST;
GRANT INSERT, UPDATE, DELETE, SELECT XACML.PREFIXLIST;
GRANT INSERT, UPDATE, DELETE, SELECT XACML.ADDRESSGROUP;
GRANT INSERT, UPDATE, DELETE, SELECT XACML.POLICYSCORE;
GRANT INSERT, UPDATE, DELETE, SELECT XACML.VSCLACTION;
GRANT INSERT, UPDATE, DELETE, SELECT XACML.VNFTYPE;
GRANT INSERT, UPDATE, DELETE, SELECT XACML.ATTRIBUTE;
GRANT INSERT, UPDATE, DELETE, SELECT XACML.DECISIONSETTINGS;
GRANT INSERT, UPDATE, DELETE, SELECT XACML.POLICY_MANAGEMENT;
GRANT INSERT, UPDATE, DELETE, SELECT XACML.SCOPES;


set foreign_key_checks=1;