---ABS-135198

--RESTRESOURCE TABLE
CREATE TABLE RESTRESOURCE 
( RESTRESOURCE_ID NUMBER NOT NULL,
  RESOURCENAME VARCHAR2(60) NOT NULL,
  ALLOWUSAGE NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_RESTRESOURCE_ALLOWUSAGE CHECK (ALLOWUSAGE IN(0,1)), 
  TIMESTAMP DATE NOT NULL);

COMMENT ON COLUMN RESTRESOURCE.ALLOWUSAGE IS '0 = All usage allowed (Get, Post, Patch, Delete)
                                              1 = Retrieve (Get) only Other options may be added. This field is used to limit the user options.';
                                          
ALTER TABLE RESTRESOURCE
      ADD CONSTRAINT XPKRESTRESOURCE PRIMARY KEY (RESTRESOURCE_ID);

CREATE UNIQUE INDEX XAK1RESTRESOURCE ON RESTRESOURCE (RESOURCENAME ASC);

--RESTRESOURCEUSER TABLE
CREATE SEQUENCE SEQ_RESTRESOURCEUSER
START WITH 1 
INCREMENT BY 1;

CREATE TABLE RESTRESOURCEUSER 
( RESTRESOURCEUSER_ID NUMBER NOT NULL,
  RESTRESOURCE_ID NUMBER NOT NULL,
  RESTSYSTEMUSER_ID NUMBER NOT NULL,
  ALLOWRETRIEVE NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_RESTRESOURCEUSER_ALLOWRETRIEVE CHECK (ALLOWRETRIEVE IN(0,1)),
  ALLOWCREATE NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_RESTRESOURCEUSER_ALLOWCREATE CHECK (ALLOWCREATE IN(0,1)),
  ALLOWUPDATE NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_RESTRESOURCEUSER_ALLOWUPDATE CHECK (ALLOWUPDATE IN(0,1)),
  ALLOWDELETE NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_RESTRESOURCEUSER_ALLOWDELETE CHECK (ALLOWDELETE IN(0,1)),
  TIMESTAMP DATE NOT NULL,
  SYSTEMUSER_ID NUMBER NOT NULL);

COMMENT ON COLUMN RESTRESOURCEUSER.ALLOWRETRIEVE IS '0 = Not Allowed
                                                     1 = Allowed';
                                                     
COMMENT ON COLUMN RESTRESOURCEUSER.ALLOWCREATE IS '0 = Not Allowed
                                                   1 = Allowed';                                                     
                                                     
COMMENT ON COLUMN RESTRESOURCEUSER.ALLOWUPDATE IS '0 = Not Allowed
                                                   1 = Allowed';
                                                   
COMMENT ON COLUMN RESTRESOURCEUSER.ALLOWDELETE IS '0 = Not Allowed
                                                   1 = Allowed';
      
ALTER TABLE RESTRESOURCEUSER
      ADD CONSTRAINT XPKRESTRESOURCEUSER PRIMARY KEY (RESTRESOURCEUSER_ID);
      
CREATE INDEX XIF1RESTRESOURCEUSER ON RESTRESOURCEUSER (RESTRESOURCE_ID ASC);
CREATE INDEX XIF2RESTRESOURCEUSER ON RESTRESOURCEUSER (RESTSYSTEMUSER_ID ASC);
CREATE UNIQUE INDEX XAK1RESTRESOURCEUSER ON RESTRESOURCEUSER (RESTRESOURCE_ID ASC, RESTSYSTEMUSER_ID ASC);            

ALTER TABLE RESTRESOURCEUSER 
  ADD CONSTRAINT FK_RESTRESOURCEUSER_RESTRESOURCE_ID FOREIGN KEY (RESTRESOURCE_ID) REFERENCES RESTRESOURCE (RESTRESOURCE_ID) ON DELETE CASCADE;                                                               

ALTER TABLE RESTRESOURCEUSER 
  ADD CONSTRAINT FK_RESTRESOURCEUSER_RESTSYSTEMUSER_ID FOREIGN KEY (RESTSYSTEMUSER_ID) REFERENCES SYSTEMUSER (SYSTEMUSER_ID) ON DELETE CASCADE;                                                            
