-- CREATE THE SCHEMA (MUST BE EXECUTED AS SINGLE STATEMENT)
CREATE SCHEMA PERMISSIONANALYZER;

SET SCHEMA PERMISSIONANALYZER;

/*
DROP TABLE ACLITEM;
DROP TABLE MEMBERSHIP;
DROP TABLE MEMBER;
DROP TABLE FILEINFO;
DROP TABLE FILTER;
DROP TABLE REPORTFILTERSET;
DROP TABLE FILTERSET;
DROP TABLE REPORTHISTORY;
DROP TABLE REPORT;
DROP TABLE RDN;
DROP TABLE DOMAIN;
DROP TABLE INFO;
DROP TABLE PERMISSIONLOOKUP;
DROP TABLE SCANLOGITEM;
*/

-- PERMISSION LOOKUP TABLE
CREATE TABLE PERMISSIONLOOKUP
	(ID INT NOT NULL CONSTRAINT PERMISSION_PK PRIMARY KEY, 
	P0 SMALLINT NOT NULL,
	P1 SMALLINT NOT NULL,
	P2 SMALLINT NOT NULL,
	P3 SMALLINT NOT NULL,
	P4 SMALLINT NOT NULL,
	P5 SMALLINT NOT NULL,
	P6 SMALLINT NOT NULL,
	P7 SMALLINT NOT NULL,
	P8 SMALLINT NOT NULL,
	P9 SMALLINT NOT NULL,
	P10 SMALLINT NOT NULL,
	P11 SMALLINT NOT NULL,
	P12 SMALLINT NOT NULL);
	
-- RDN TABLE
CREATE TABLE RDN
	(ID INT NOT NULL CONSTRAINT RDN_PK PRIMARY KEY, 
	RDN VARCHAR(1000) NOT NULL);

-- DOMAIN TABLE
CREATE TABLE DOMAIN
	(ID INT NOT NULL CONSTRAINT DOMAIN_PK PRIMARY KEY, 
	DOMAIN VARCHAR(255));
	
-- MEMBER TABLE	
CREATE TABLE MEMBER
	(ID INT NOT NULL CONSTRAINT MEMBER_PK PRIMARY KEY, 
	NAME VARCHAR(255),
	NAME_LOWERCASE VARCHAR(255),
	DISPLAY_NAME VARCHAR(1024),
	DISPLAY_NAME_LOWERCASE VARCHAR(1024),
	IS_GROUP SMALLINT,
	IS_ENABLED SMALLINT,
	GROUP_TYPE INT,
	RDN_ID INT,
	DOMAIN_ID INT,
	CN VARCHAR(255),
	IS_BUILTIN SMALLINT);
	
CREATE INDEX MEMBER_NAME_I ON MEMBER(NAME_LOWERCASE);
CREATE INDEX MEMBER_DISPLAY_NAME_I ON MEMBER(DISPLAY_NAME_LOWERCASE);
CREATE INDEX MEMBER_CN_I ON MEMBER(CN);
CREATE INDEX MEMBER_TYPE_I ON MEMBER(GROUP_TYPE);
ALTER TABLE MEMBER ADD CONSTRAINT MEMBER_RDN_ID_FK FOREIGN KEY (RDN_ID) REFERENCES RDN(ID);
ALTER TABLE MEMBER ADD CONSTRAINT MEMBER_DOMAIN_ID_FK FOREIGN KEY (DOMAIN_ID) REFERENCES DOMAIN(ID);

-- MEMBERSHIP TABLE
CREATE TABLE MEMBERSHIP
	(ID INT NOT NULL CONSTRAINT MEMBERSHIP_PK PRIMARY KEY, 
	GROUP_ID INT NOT NULL, 
	MEMBER_ID INT NOT NULL,
	VIA_GROUP_ID INT);

ALTER TABLE MEMBERSHIP ADD CONSTRAINT MEMBERSHIP_GROUP_ID_FK FOREIGN KEY (GROUP_ID) REFERENCES MEMBER(ID);
ALTER TABLE MEMBERSHIP ADD CONSTRAINT MEMBERSHIP_MEMBER_ID_FK FOREIGN KEY (MEMBER_ID) REFERENCES MEMBER(ID);
CREATE INDEX MEMBERSHIP_GROUP_ID_I ON PERMISSIONANALYZER.MEMBERSHIP(GROUP_ID);
CREATE INDEX MEMBERSHIP_MEMBER_ID_I ON PERMISSIONANALYZER.MEMBERSHIP(MEMBER_ID);

-- FILE TABLE
CREATE TABLE FILEINFO
	(ID INT NOT NULL CONSTRAINT FILEINFO_PK PRIMARY KEY, 
	PARENT_ID INT,
	NAME VARCHAR(255) NOT NULL,
	NAME_LOWERCASE VARCHAR(255) NOT NULL,
	IS_DIRECTORY SMALLINT NOT NULL,
	PARENT_FILE_IDS VARCHAR(255),
	IS_DACL_PROTECTED SMALLINT NOT NULL,
	OWNER_ID INT);

ALTER TABLE FILEINFO ADD CONSTRAINT FILEINFO_OWNER_ID_FK FOREIGN KEY (OWNER_ID) REFERENCES MEMBER(ID);
CREATE INDEX FILE_NAME_I ON FILEINFO(NAME_LOWERCASE);
CREATE INDEX PARENT_FILE_IDS_I ON FILEINFO(PARENT_FILE_IDS);
CREATE INDEX PARENTID_I ON FILEINFO(PARENT_ID)
CREATE INDEX FILEINFO_OWNER_ID_I ON PERMISSIONANALYZER.FILEINFO(OWNER_ID);

-- ACLITEM TABLE
CREATE TABLE ACLITEM
	(ID INT NOT NULL CONSTRAINT ACLITEM_PK PRIMARY KEY, 
	MEMBER_ID INT NOT NULL,
	FILEINFO_ID INT NOT NULL,
	PERMISSION_ID INT NOT NULL,
	INHERITED_FROM_FOLDER_ID INT,
	IS_DENY SMALLINT NOT NULL,
	FLAG_DIRECTORY_INHERIT SMALLINT NOT NULL,
	FLAG_FILE_INHERIT SMALLINT NOT NULL,
	FLAG_INHERIT_ONLY SMALLINT NOT NULL,
	FLAG_NO_PROPAGATE_INHERIT SMALLINT NOT NULL,
	FLAG_INHERITED_ACE SMALLINT NOT NULL,
	GENERIC_ALL SMALLINT NOT NULL,
	GENERIC_EXECUTE SMALLINT NOT NULL,
	GENERIC_WRITE SMALLINT NOT NULL,
	GENERIC_READ SMALLINT NOT NULL,
	IS_DIRECTORY SMALLINT NOT NULL);

ALTER TABLE ACLITEM ADD CONSTRAINT ACLITEM_MEMBER_ID_FK FOREIGN KEY (MEMBER_ID) REFERENCES MEMBER(ID);
ALTER TABLE ACLITEM ADD CONSTRAINT ACLITEM_FILEINFO_ID_FK FOREIGN KEY (FILEINFO_ID) REFERENCES FILEINFO(ID) ON DELETE CASCADE;
ALTER TABLE ACLITEM ADD CONSTRAINT ACLITEM_PERMISSION_ID_FK FOREIGN KEY (PERMISSION_ID) REFERENCES PERMISSIONLOOKUP(ID);
CREATE INDEX ACLITEM_MEMBER_ID_I ON PERMISSIONANALYZER.ACLITEM(MEMBER_ID);
CREATE INDEX ACLITEM_FILEINFO_ID_I ON PERMISSIONANALYZER.ACLITEM(FILEINFO_ID);
CREATE INDEX ACLITEM_PERMISSION_ID_I ON PERMISSIONANALYZER.ACLITEM(PERMISSION_ID);

-- REPORT TABLE
CREATE TABLE REPORT
	(ID INT NOT NULL CONSTRAINT REPORT_PK PRIMARY KEY, 
	NAME VARCHAR(255) NOT NULL,
	DESCRIPTION VARCHAR(3000),
	LAST_RUN_DATE TIMESTAMP,
	LAST_COUNT INT,
	LAST_FILE_PATH VARCHAR(3000),
	REPORT_TYPE INT NOT NULL,
	FILE_TYPE INT NOT NULL,
	FILE_PATH VARCHAR(3000) NOT NULL,
	TEMPLATE_PATH VARCHAR(3000),
	EMAIL_ADDRESS VARCHAR(255),
	EMAIL_THRESHOLD INT,
	LAST_STATUS_MESSAGE VARCHAR(3000),
	USE_SIMPLE_PRESENTATION SMALLINT);

-- REPORTHISTORY TABLE
CREATE TABLE REPORTHISTORY
	(ID INT NOT NULL CONSTRAINT REPORT_PK PRIMARY KEY,
	REPORT_ID INT NOT NULL,
	RUN_DATE TIMESTAMP,
	RESULT_COUNT INT,
	FILE_PATH VARCHAR(3000) NULL,
	REPORT_TYPE INT NOT NULL,
	FILE_TYPE INT NOT NULL,
	EMAIL_ADDRESS VARCHAR(255),
	THRESHOLD INT,
	STATUS_MESSAGE VARCHAR(3000));
	
ALTER TABLE REPORTHISTORY ADD CONSTRAINT REPORTHISTORY_REPORT_ID_FK FOREIGN KEY (REPORT_ID) REFERENCES REPORT(ID);
	
-- FILTERSET TABLE	
CREATE TABLE FILTERSET
	(ID INT NOT NULL CONSTRAINT FILTERSET_PK PRIMARY KEY, 
	NAME VARCHAR(255) NOT NULL,
	FILTER_SET_TYPE INT NOT NULL);

-- FILTER TABLE
CREATE TABLE FILTER
	(ID INT NOT NULL CONSTRAINT FILTER_PK PRIMARY KEY, 
	NAME VARCHAR(255) NOT NULL,
	FILTER_TYPE INT NOT NULL,
	INCLUDE SMALLINT NOT NULL,
	FILTERSET_ID INT,
	REPORT_ID INT,
	FILTER_VALUE VARCHAR(255) NOT NULL);

ALTER TABLE FILTER ADD CONSTRAINT FILTER_FILTERSET_ID_FK FOREIGN KEY (FILTERSET_ID) REFERENCES FILTERSET(ID) ON DELETE CASCADE;
ALTER TABLE FILTER ADD CONSTRAINT FILTER_REPORT_ID_FK FOREIGN KEY (REPORT_ID) REFERENCES REPORT(ID) ON DELETE CASCADE;

-- REPORTFILTER TABLE
CREATE TABLE REPORTFILTERSET
	(ID INT NOT NULL CONSTRAINT REPORTFILTER_PK PRIMARY KEY, 
	REPORT_ID INT NOT NULL, 
	FILTERSET_ID INT);

ALTER TABLE REPORTFILTERSET ADD CONSTRAINT REPORTFILTERSET_REPORT_ID_FK FOREIGN KEY (REPORT_ID) REFERENCES REPORT(ID) ON DELETE CASCADE;
ALTER TABLE REPORTFILTERSET ADD CONSTRAINT REPORTFILTERSET_SET_ID_FK FOREIGN KEY (FILTERSET_ID) REFERENCES FILTERSET(ID);

-- SCANLOGITEM TABLE
CREATE TABLE SCANLOGITEM
	(ID INT NOT NULL CONSTRAINT SCANLOGITEM_PK PRIMARY KEY, 
	SERVER VARCHAR(255) NOT NULL,
	LOG_DATE TIMESTAMP NOT NULL,
	SEVERITY INT NOT NULL,
	MESSAGE VARCHAR(2000) NOT NULL,
	DETAILS VARCHAR(3000));

CREATE INDEX SCANLOGITEM_SERVER_I ON SCANLOGITEM(SERVER);
CREATE INDEX SCANLOGITEM_LOG_DATE_I ON SCANLOGITEM(LOG_DATE);
	
-- INFO TABLE	
CREATE TABLE INFO
	(VERSION INT);
	
INSERT INTO INFO VALUES (16);