 CREATE TABLE CMS_Module
  (
    ModuleId    NUMBER NOT NULL              ,
    moduleName  VARCHAR2(100) NOT NULL UNIQUE,
    DisplayName VARCHAR2(100) NOT NULL UNIQUE,
    PRIMARY KEY (moduleid)
  );

CREATE sequence cms_module_seq
  START WITH 1 increment BY 1;

CREATE OR REPLACE TRIGGER "TRG_CMS_Module_SEQ" BEFORE
   INSERT ON CMS_Module FOR EACH ROW BEGIN IF :NEW.ModuleId IS NULL THEN
   SELECT cms_module_seq.NEXTVAL INTO :NEW.ModuleId FROM DUAL;
END IF;
END;
/

 CREATE TABLE CMS_permission
  (
    permissionId   NUMBER NOT NULL       ,
    permissionName VARCHAR2(100) NOT NULL,
    DisplayName    VARCHAR2(100) NOT NULL,
    ModuleId       NUMBER                ,
    ordernumber    NUMBER                ,
    PRIMARY KEY (permissionId)           ,
    UNIQUE (ModuleId,permissionName)     ,
    UNIQUE (ModuleId, DisplayName)       ,
    FOREIGN KEY (moduleid) REFERENCES cms_module(moduleid)
  );

CREATE sequence CMS_Permission_seq
  START WITH 1 increment BY 1;

CREATE OR REPLACE TRIGGER "TRG_CMS_permission_SEQ" BEFORE
   INSERT ON CMS_permission FOR EACH ROW BEGIN IF :NEW.permissionId IS NULL THEN
   SELECT CMS_Permission_seq.NEXTVAL INTO :NEW.permissionId FROM DUAL;
END IF;
END;
/

 CREATE TABLE CMS_Role
  (
    RoleId      NUMBER NOT NULL              ,
    role_Name   VARCHAR2(100) NOT NULL UNIQUE,
    Description VARCHAR2(1000) ,
    externalkey varchar2(500) unique,
    PRIMARY KEY (roleid)
  );

CREATE sequence CMS_role_seq
  START WITH 1 increment BY 1;

CREATE OR REPLACE TRIGGER "TRG_CMS_Role_SEQ" BEFORE
   INSERT ON CMS_Role FOR EACH ROW BEGIN IF :NEW.RoleId IS NULL THEN
   SELECT CMS_role_Seq.NEXTVAL INTO :NEW.RoleId FROM DUAL;
END IF;
END;
/

CREATE TABLE CMS_User
  (
    UserId   NUMBER NOT NULL              ,
    UserName VARCHAR2(100) NOT NULL UNIQUE,
    Password VARCHAR2(100) NOT NULL       ,
    PasswordModifiedDate DATE NOT NULL    ,
    Active     VARCHAR(1) NOT NULL            ,
    FirstName  VARCHAR2(100)                  ,
    LastName   VARCHAR2(100)                  ,
    Email      VARCHAR2(200)                  ,
    Phone      VARCHAR2(50)                   ,
    Extension  VARCHAR2(20)                    ,
    EmployeeId VARCHAR2(100)                  ,
    Department VARCHAR2(100)                  ,
    Location   VARCHAR2(100)                  ,
    PRIMARY KEY (userid)
  );

CREATE sequence CMS_user_Seq
  START WITH 1 increment BY 1;

CREATE OR REPLACE TRIGGER "TRG_CMS_user_SEQ" BEFORE
   INSERT ON CMS_user FOR EACH ROW BEGIN IF :NEW.UserId IS NULL THEN
   SELECT CMS_user_seq.NEXTVAL INTO :NEW.UserId FROM DUAL;
END IF;
END;
/

CREATE TABLE CMS_UserRoles
  (
    UserId NUMBER NOT NULL                          ,
    RoleId NUMBER NOT NULL                          ,
    PRIMARY KEY (userid,roleid)                     ,
    FOREIGN KEY (roleid) REFERENCES CMS_Role(RoleId),
    FOREIGN KEY (userid) REFERENCES cms_user(userid)
  );

 CREATE TABLE CMS_Rolepermissions
  (
    RoleId       NUMBER NOT NULL                    ,
    permissionId NUMBER NOT NULL                    ,
    PRIMARY KEY (roleid,permissionid)               ,
    FOREIGN KEY (roleid) REFERENCES CMS_Role(RoleId),
    FOREIGN KEY (permissionId) REFERENCES cms_permission(permissionid)
  );

-- REM INSERTING into CMS_USER  
Insert into CMS_USER (USERID,USERNAME,PASSWORD,PASSWORDMODIFIEDDATE,ACTIVE,FIRSTNAME,LASTNAME,EMAIL,PHONE,EXTENSION,EMPLOYEEID,DEPARTMENT,LOCATION) 
select cms_user_seq.nextval,'admin','d033e22ae348aeb5660fc2140aec35850c4da997',systimestamp,'1','Admin2','Admin','test@test.com',null,null,null,null,null
from dual;

-- REM INSERTING into CMS_ROLE
Insert into CMS_ROLE (ROLEID,ROLE_NAME,DESCRIPTION) values (cms_role_seq.nextval,'Admin','Administrator Role2');

-- REM INSERTING into CMS_USERROLES
Insert into CMS_USERROLES (USERID,ROLEID) values (cms_user_seq.currval,cms_role_seq.currval);

-- REM INSERTING into CMS_MODULE
Insert into CMS_MODULE (MODULEID,MODULENAME,DISPLAYNAME) values (cms_module_seq.nextval,'UserManagement','User Management');
Insert into CMS_PERMISSION (PERMISSIONID,PERMISSIONNAME,DISPLAYNAME,MODULEID,ORDERNUMBER) values (cms_permission_seq.nextval,'View','View',cms_module_seq.currval,0);
Insert into CMS_ROLEPERMISSIONS (ROLEID,PERMISSIONID) values (cms_role_seq.currval,cms_permission_seq.currval);

Insert into CMS_PERMISSION (PERMISSIONID,PERMISSIONNAME,DISPLAYNAME,MODULEID,ORDERNUMBER) values (cms_permission_seq.nextval,'Create','Create',cms_module_seq.currval,1);
Insert into CMS_ROLEPERMISSIONS (ROLEID,PERMISSIONID) values (cms_role_seq.currval,cms_permission_seq.currval);

Insert into CMS_PERMISSION (PERMISSIONID,PERMISSIONNAME,DISPLAYNAME,MODULEID,ORDERNUMBER) values (cms_permission_seq.nextval,'Modify','Modify',cms_module_seq.currval,2);
Insert into CMS_ROLEPERMISSIONS (ROLEID,PERMISSIONID) values (cms_role_seq.currval,cms_permission_seq.currval);

Insert into CMS_PERMISSION (PERMISSIONID,PERMISSIONNAME,DISPLAYNAME,MODULEID,ORDERNUMBER) values (cms_permission_seq.nextval,'Delete','Delete',cms_module_seq.currval,3);
Insert into CMS_ROLEPERMISSIONS (ROLEID,PERMISSIONID) values (cms_role_seq.currval,cms_permission_seq.currval);

-- REM INSERTING into CMS_MODULE
Insert into CMS_MODULE (MODULEID,MODULENAME,DISPLAYNAME) values (cms_module_seq.nextval,'RoleManagement','Role Management');
Insert into CMS_PERMISSION (PERMISSIONID,PERMISSIONNAME,DISPLAYNAME,MODULEID,ORDERNUMBER) values (cms_permission_seq.nextval,'View','View',cms_module_seq.currval,0);
Insert into CMS_ROLEPERMISSIONS (ROLEID,PERMISSIONID) values (cms_role_seq.currval,cms_permission_seq.currval);

Insert into CMS_PERMISSION (PERMISSIONID,PERMISSIONNAME,DISPLAYNAME,MODULEID,ORDERNUMBER) values (cms_permission_seq.nextval,'Create','Create',cms_module_seq.currval,1);
Insert into CMS_ROLEPERMISSIONS (ROLEID,PERMISSIONID) values (cms_role_seq.currval,cms_permission_seq.currval);

Insert into CMS_PERMISSION (PERMISSIONID,PERMISSIONNAME,DISPLAYNAME,MODULEID,ORDERNUMBER) values (cms_permission_seq.nextval,'Modify','Modify',cms_module_seq.currval,2);
Insert into CMS_ROLEPERMISSIONS (ROLEID,PERMISSIONID) values (cms_role_seq.currval,cms_permission_seq.currval);

Insert into CMS_PERMISSION (PERMISSIONID,PERMISSIONNAME,DISPLAYNAME,MODULEID,ORDERNUMBER) values (cms_permission_seq.nextval,'Delete','Delete',cms_module_seq.currval,3);
Insert into CMS_ROLEPERMISSIONS (ROLEID,PERMISSIONID) values (cms_role_seq.currval,cms_permission_seq.currval);

commit;

exit