-- CREATE THE USER
CREATE USER PERMISSIONANALYZER IDENTIFIED BY mypassword DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;
GRANT CREATE SESSION TO PERMISSIONANALYZER;
GRANT CREATE TABLE TO 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;
DROP TABLE PERMISSIONANALYZER.SCANLOGITEM;
*/

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

-- DOMAIN TABLE
CREATE TABLE PERMISSIONANALYZER.DOMAIN
	(ID NUMBER(10) PRIMARY KEY, 
	DOMAIN VARCHAR2(255));
	
-- MEMBER TABLE	
CREATE TABLE PERMISSIONANALYZER.MEMBER
	(ID NUMBER(10) PRIMARY KEY, 
	NAME VARCHAR2(255),
	NAME_LOWERCASE VARCHAR2(255),
	DISPLAY_NAME VARCHAR2(1024),
	DISPLAY_NAME_LOWERCASE VARCHAR2(1024),
	IS_GROUP NUMBER(1),
	IS_ENABLED NUMBER(1),
	GROUP_TYPE NUMBER(10),
	RDN_ID NUMBER(10),
	DOMAIN_ID NUMBER(10),
	CN VARCHAR2(255),
	IS_BUILTIN NUMBER(1));
	
CREATE INDEX PERMISSIONANALYZER.MEMBER_NAME_I ON PERMISSIONANALYZER.MEMBER(NAME_LOWERCASE);
CREATE INDEX PERMISSIONANALYZER.MEMBER_DISPLAY_NAME_I ON PERMISSIONANALYZER.MEMBER(DISPLAY_NAME_LOWERCASE);
CREATE INDEX PERMISSIONANALYZER.MEMBER_CN_I ON PERMISSIONANALYZER.MEMBER(CN);
CREATE INDEX PERMISSIONANALYZER.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 NUMBER(10) PRIMARY KEY, 
	GROUP_ID NUMBER(10) NOT NULL, 
	MEMBER_ID NUMBER(10) NOT NULL,
	VIA_GROUP_ID NUMBER(10));

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 NUMBER(10) PRIMARY KEY, 
	PARENT_ID NUMBER(10),
	NAME VARCHAR2(255) NOT NULL,
	NAME_LOWERCASE VARCHAR2(255) NOT NULL,
	IS_DIRECTORY NUMBER(1) NOT NULL,
	PARENT_FILE_IDS VARCHAR2(255),
	IS_DACL_PROTECTED NUMBER(1) NOT NULL,
	OWNER_ID NUMBER(11));

ALTER TABLE PERMISSIONANALYZER.FILEINFO ADD CONSTRAINT FILEINFO_OWNER_ID_FK FOREIGN KEY (OWNER_ID) REFERENCES PERMISSIONANALYZER.MEMBER(ID);
CREATE INDEX PERMISSIONANALYZER.FILE_NAME_I ON PERMISSIONANALYZER.FILEINFO(NAME_LOWERCASE);
CREATE INDEX PERMISSIONANALYZER.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 NUMBER(10) PRIMARY KEY, 
	MEMBER_ID NUMBER(10) NOT NULL,
	FILEINFO_ID NUMBER(10) NOT NULL,
	PERMISSION_ID NUMBER(4) NOT NULL,
	INHERITED_FROM_FOLDER_ID NUMBER(10),
	IS_DENY NUMBER(1) NOT NULL,
	FLAG_DIRECTORY_INHERIT NUMBER(1) NOT NULL,
	FLAG_FILE_INHERIT NUMBER(1) NOT NULL,
	FLAG_INHERIT_ONLY NUMBER(1) NOT NULL,
	FLAG_NO_PROPAGATE_INHERIT NUMBER(1) NOT NULL,
	FLAG_INHERITED_ACE NUMBER(1) NOT NULL,
	GENERIC_ALL NUMBER(1) NOT NULL,
	GENERIC_EXECUTE NUMBER(1) NOT NULL,
	GENERIC_WRITE NUMBER(1) NOT NULL,
	GENERIC_READ NUMBER(1) NOT NULL,
	IS_DIRECTORY NUMBER(1) 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 NUMBER(10) PRIMARY KEY, 
	NAME VARCHAR2(255) NOT NULL,
	DESCRIPTION VARCHAR2(3000),
	LAST_RUN_DATE DATE,
	LAST_COUNT NUMBER(10),
	LAST_FILE_PATH VARCHAR2(3000) NULL,
	REPORT_TYPE NUMBER(10) NOT NULL,
	FILE_TYPE NUMBER(10) NOT NULL,
	FILE_PATH VARCHAR2(3000) NOT NULL,
	TEMPLATE_PATH VARCHAR2(3000),
	EMAIL_ADDRESS VARCHAR2(255),
	EMAIL_THRESHOLD NUMBER(10),
	LAST_STATUS_MESSAGE VARCHAR2(3000),
	USE_SIMPLE_PRESENTATION NUMBER(1));

-- REPORTHISTORY TABLE
CREATE TABLE PERMISSIONANALYZER.REPORTHISTORY
	(ID NUMBER(10) PRIMARY KEY, 
	REPORT_ID NUMBER(10) NOT NULL,
	RUN_DATE DATE,
	RESULT_COUNT NUMBER(10),
	FILE_PATH VARCHAR2(3000) NULL,
	REPORT_TYPE NUMBER(10) NOT NULL,
	FILE_TYPE NUMBER(10) NOT NULL,
	EMAIL_ADDRESS VARCHAR2(255),
	THRESHOLD NUMBER(10),
	STATUS_MESSAGE VARCHAR2(3000));
	
ALTER TABLE PERMISSIONANALYZER.REPORTHISTORY ADD CONSTRAINT REPORTHISTORY_REPORT_ID_FK FOREIGN KEY (REPORT_ID) REFERENCES PERMISSIONANALYZER.REPORT(ID);
	
-- FILTERSET TABLE	
CREATE TABLE PERMISSIONANALYZER.FILTERSET
	(ID NUMBER(10) PRIMARY KEY, 
	NAME VARCHAR2(255) NOT NULL,
	FILTER_SET_TYPE NUMBER(10) NOT NULL);
	
-- FILTER TABLE
CREATE TABLE PERMISSIONANALYZER.FILTER
	(ID NUMBER(10) PRIMARY KEY, 
	NAME VARCHAR2(255) NOT NULL,
	FILTER_TYPE NUMBER(10) NOT NULL,
	INCLUDE NUMBER(1) NOT NULL,
	FILTERSET_ID NUMBER(10),
	REPORT_ID NUMBER(10),
	FILTER_VALUE VARCHAR2(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 NUMBER(10) PRIMARY KEY, 
	REPORT_ID NUMBER(10) NOT NULL, 
	FILTERSET_ID NUMBER(10));

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 NUMBER(10) PRIMARY KEY, 
	SERVER VARCHAR2(255) NOT NULL,
	LOG_DATE DATE NOT NULL,
	SEVERITY NUMBER(10) NOT NULL,
	MESSAGE VARCHAR2(2000) NOT NULL,
	DETAILS VARCHAR2(3000));

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