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

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

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

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

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

ALTER TABLE PERMISSIONANALYZER.MEMBERSHIP ADD CONSTRAINT MEMBERSHIP_GROUP_ID_FK FOREIGN KEY (GROUP_ID) REFERENCES PERMISSIONANALYZER.MEMBER(ID);
ALTER TABLE PERMISSIONANALYZER.MEMBERSHIP ADD CONSTRAINT MEMBERSHIP_MEMBER_ID_FK FOREIGN KEY (MEMBER_ID) REFERENCES PERMISSIONANALYZER.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 PERMISSIONANALYZER.FILEINFO
	(ID INT PRIMARY KEY, 
	PARENT_ID INT,
	NAME VARCHAR(255) NOT NULL,
	NAME_LOWERCASE VARCHAR(255) NOT NULL,
	IS_DIRECTORY BIT NOT NULL,
	PARENT_FILE_IDS VARCHAR(255),
	IS_DACL_PROTECTED BIT NOT NULL,
	OWNER_ID INT);

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

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

ALTER TABLE PERMISSIONANALYZER.ACLITEM ADD CONSTRAINT ACLITEM_MEMBER_ID_FK FOREIGN KEY (MEMBER_ID) REFERENCES PERMISSIONANALYZER.MEMBER(ID);
ALTER TABLE PERMISSIONANALYZER.ACLITEM ADD CONSTRAINT ACLITEM_FILEINFO_ID_FK FOREIGN KEY (FILEINFO_ID) REFERENCES PERMISSIONANALYZER.FILEINFO(ID) ON DELETE CASCADE;
ALTER TABLE PERMISSIONANALYZER.ACLITEM ADD CONSTRAINT ACLITEM_PERMISSION_ID_FK FOREIGN KEY (PERMISSION_ID) REFERENCES PERMISSIONANALYZER.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 PERMISSIONANALYZER.REPORT
	(ID INT PRIMARY KEY, 
	NAME VARCHAR(255) NOT NULL,
	DESCRIPTION VARCHAR(3000),
	LAST_RUN_DATE DATETIME,
	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 BIT,
	CREATE_COMPARISON_REPORT BIT);
										
-- REPORTHISTORY TABLE
CREATE TABLE PERMISSIONANALYZER.REPORTHISTORY
	(ID INT PRIMARY KEY, 
	REPORT_ID INT NOT NULL,
	RUN_DATE DATETIME,
	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),
	WAS_COMPARISON BIT);
	
ALTER TABLE PERMISSIONANALYZER.REPORTHISTORY ADD CONSTRAINT REPORTHISTORY_REPORT_ID_FK FOREIGN KEY (REPORT_ID) REFERENCES PERMISSIONANALYZER.REPORT(ID);

-- REPORTCOMPARATOR1 TABLE
CREATE TABLE PERMISSIONANALYZER.REPORTCOMPARATOR1 
	(ID INT PRIMARY KEY,REPORT1_ROW_KEY VARCHAR(3000),
	REPORT1_ROW_VALUE VARCHAR(3000) NOT NULL);

-- REPORTCOMPARATOR2 TABLE	
CREATE TABLE PERMISSIONANALYZER.REPORTCOMPARATOR2 
	(ID INT PRIMARY KEY,REPORT2_ROW_KEY VARCHAR(3000),
	REPORT2_ROW_VALUE VARCHAR(3000) NOT NULL);

CREATE INDEX REPORTCOMPARATOR1_ROWKEY_I ON PERMISSIONANALYZER.REPORTCOMPARATOR1(REPORT1_ROW_KEY);
CREATE INDEX REPORTCOMPARATOR2_ROWKEY_I ON PERMISSIONANALYZER.REPORTCOMPARATOR2(REPORT2_ROW_KEY);
	
-- FILTERSET TABLE	
CREATE TABLE PERMISSIONANALYZER.FILTERSET
	(ID INT PRIMARY KEY, 
	NAME VARCHAR(255) NOT NULL,
	FILTER_SET_TYPE INT NOT NULL);

-- FILTER TABLE
CREATE TABLE PERMISSIONANALYZER.FILTER
	(ID INT PRIMARY KEY, 
	NAME VARCHAR(255) NOT NULL,
	FILTER_TYPE INT NOT NULL,
	INCLUDE BIT NOT NULL,
	FILTERSET_ID INT,
	REPORT_ID INT,
	FILTER_VALUE VARCHAR(255) NOT NULL);

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

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

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

-- SCANLOGITEM TABLE
CREATE TABLE PERMISSIONANALYZER.SCANLOGITEM
	(ID INT PRIMARY KEY, 
	SERVER VARCHAR(255) NOT NULL,
	LOG_DATE DATETIME NOT NULL,
	SEVERITY INT NOT NULL,
	MESSAGE VARCHAR(2000) NOT NULL,
	DETAILS VARCHAR(3000));

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