--SO 20014815

--ACCESSRIGHTSLIST TABLE

CREATE SEQUENCE SEQ_ACCESSRIGHTSLIST
START WITH 1
INCREMENT BY 1;

CREATE TABLE ACCESSRIGHTSLIST (
ACCESSRIGHTSLIST_ID       NUMBER NOT NULL,
CODE                      VARCHAR2(10) NOT NULL,
NAME                      VARCHAR2(30) NOT NULL,
DIALOG_ID                 NUMBER NOT NULL,
ADDRIGHT                  VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD_2 CHECK (ADDRIGHT IN ('Y', 'N')),
MODIFYRIGHT               VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD_3 CHECK (MODIFYRIGHT IN ('Y', 'N')),
DELETERIGHT               VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD_4 CHECK (DELETERIGHT IN ('Y', 'N')),
SYSTEMUSER_ID             NUMBER NOT NULL,
TIMESTAMP                 DATE NOT NULL
);

ALTER TABLE ACCESSRIGHTSLIST
ADD CONSTRAINT  XPKACCESSRIGHTSLIST PRIMARY KEY (ACCESSRIGHTSLIST_ID);

CREATE INDEX XIF1ACCESSRIGHTSLIST ON ACCESSRIGHTSLIST
(DIALOG_ID   ASC);

ALTER TABLE ACCESSRIGHTSLIST
ADD CONSTRAINT ACCESSRIGHTSLIST_DIAL FOREIGN KEY (DIALOG_ID) REFERENCES DIALOGS (DIALOG_ID) ON DELETE CASCADE;


--FIELDSPERDIALOG TABLE

CREATE SEQUENCE SEQ_FIELDSPERDIALOG
START WITH 1
INCREMENT BY 1;

CREATE TABLE FIELDSPERDIALOG (
FIELDSPERDIALOG_ID       NUMBER NOT NULL,
DIALOG_ID                NUMBER NOT NULL,
FIELD                    VARCHAR2(30) NOT NULL,
TIMESTAMP                DATE NOT NULL
);

ALTER TABLE FIELDSPERDIALOG
ADD CONSTRAINT  XPKFIELDSPERDIALOG PRIMARY KEY (FIELDSPERDIALOG_ID);

CREATE INDEX XIF1FIELDSPERDIALOG ON FIELDSPERDIALOG
(DIALOG_ID   ASC);

ALTER TABLE FIELDSPERDIALOG
ADD CONSTRAINT FIELDSPERDIALOGT_DIAL FOREIGN KEY (DIALOG_ID) REFERENCES DIALOGS (DIALOG_ID) ON DELETE CASCADE;

--ACCESSRIGHTFIELDLISTS TABLE


CREATE TABLE ACCESSRIGHTFIELDLISTS (
FIELDSPERDIALOG_ID        NUMBER NOT NULL,
ACCESSRIGHTSLIST_ID       NUMBER NOT NULL,
ACCESSRIGHT               NUMBER DEFAULT 0 NOT NULL CONSTRAINT ACCESSRIGHT_VALID CHECK (ACCESSRIGHT IN (0, 1, 2)),
SYSTEMUSER_ID             NUMBER NOT NULL,
TIMESTAMP                 DATE NOT NULL
);

ALTER TABLE ACCESSRIGHTFIELDLISTS
ADD CONSTRAINT  XPKACCESSRIGHTFIELDLISTS PRIMARY KEY (FIELDSPERDIALOG_ID, ACCESSRIGHTSLIST_ID);

COMMENT ON COLUMN ACCESSRIGHTFIELDLISTS.ACCESSRIGHT IS '0 – full access (view & modify)
1 – read only (view only)
2 – not visible
';

CREATE INDEX XIF1ACCESSRIGHTFIELDLISTS ON ACCESSRIGHTFIELDLISTS
(FIELDSPERDIALOG_ID   ASC);

CREATE INDEX XIF2ACCESSRIGHTFIELDLISTS ON ACCESSRIGHTFIELDLISTS
(ACCESSRIGHTSLIST_ID   ASC);

ALTER TABLE ACCESSRIGHTFIELDLISTS
ADD CONSTRAINT ACCESSRIGHTFIELDLISTS_FIELD FOREIGN KEY (FIELDSPERDIALOG_ID) REFERENCES FIELDSPERDIALOG (FIELDSPERDIALOG_ID) ON DELETE CASCADE;

ALTER TABLE ACCESSRIGHTFIELDLISTS
ADD CONSTRAINT ACCESSRIGHTFIELDLISTS_LIST FOREIGN KEY (ACCESSRIGHTSLIST_ID) REFERENCES ACCESSRIGHTSLIST (ACCESSRIGHTSLIST_ID) ON DELETE CASCADE;


--FIELDSRELATIONS TABLE


CREATE TABLE FIELDSRELATIONS (
FIELDSPERDIALOG_ID        NUMBER NOT NULL,
FIELD_SUCCESSOR           VARCHAR2(30) NOT NULL,
TIMESTAMP                 DATE NOT NULL
);

ALTER TABLE FIELDSRELATIONS
ADD CONSTRAINT  XPKFIELDSRELATIONS PRIMARY KEY (FIELDSPERDIALOG_ID, FIELD_SUCCESSOR);

CREATE INDEX XIF1FIELDSRELATIONS ON FIELDSRELATIONS
(FIELDSPERDIALOG_ID   ASC);

ALTER TABLE FIELDSRELATIONS
ADD CONSTRAINT FIELDSRELATIONS_FIELD FOREIGN KEY (FIELDSPERDIALOG_ID) REFERENCES FIELDSPERDIALOG (FIELDSPERDIALOG_ID) ON DELETE CASCADE;


--USERACCESSRIGHTSLIST TABLE

CREATE TABLE USERACCESSRIGHTSLIST (
USER_ID                   NUMBER NOT NULL,
ACCESSRIGHTSLIST_ID       NUMBER NOT NULL,
SYSTEMUSER_ID             NUMBER NOT NULL,
TIMESTAMP                 DATE NOT NULL
);

ALTER TABLE USERACCESSRIGHTSLIST
ADD CONSTRAINT  XPKUSERACCESSRIGHTSLIST PRIMARY KEY (USER_ID, ACCESSRIGHTSLIST_ID);

CREATE INDEX XIF1USERACCESSRIGHTSLIST ON USERACCESSRIGHTSLIST
(USER_ID   ASC);

CREATE INDEX XIF2USERACCESSRIGHTSLIST ON USERACCESSRIGHTSLIST
(ACCESSRIGHTSLIST_ID   ASC);

ALTER TABLE USERACCESSRIGHTSLIST
ADD CONSTRAINT USERACCESSRIGHTSLIST_USER FOREIGN KEY (USER_ID) REFERENCES SYSTEMUSER (SYSTEMUSER_ID) ON DELETE CASCADE;

ALTER TABLE USERACCESSRIGHTSLIST
ADD CONSTRAINT USERACCESSRIGHTSLIST_ACCESS FOREIGN KEY (ACCESSRIGHTSLIST_ID) REFERENCES ACCESSRIGHTSLIST (ACCESSRIGHTSLIST_ID) ON DELETE CASCADE;


--USERGROUPACCESSRIGHTSLIST TABLE

CREATE TABLE USERGROUPACCESSRIGHTSLIST (
USERGROUP_ID                   NUMBER NOT NULL,
ACCESSRIGHTSLIST_ID       NUMBER NOT NULL,
SYSTEMUSER_ID             NUMBER NOT NULL,
TIMESTAMP                 DATE NOT NULL
);

ALTER TABLE USERGROUPACCESSRIGHTSLIST
ADD CONSTRAINT  XPKUSERGROUPACCESSRIGHTSLIST PRIMARY KEY (USERGROUP_ID, ACCESSRIGHTSLIST_ID);

CREATE INDEX XIF1USERGROUPACCESSRIGHTSLIST ON USERGROUPACCESSRIGHTSLIST
(USERGROUP_ID   ASC);

CREATE INDEX XIF2USERGROUPACCESSRIGHTSLIST ON USERGROUPACCESSRIGHTSLIST
(ACCESSRIGHTSLIST_ID   ASC);

ALTER TABLE USERGROUPACCESSRIGHTSLIST
ADD CONSTRAINT USERGROUPACCESSRIGHTSLIST_UG FOREIGN KEY (USERGROUP_ID) REFERENCES USERGROUP (USERGROUP_ID) ON DELETE CASCADE;

ALTER TABLE USERGROUPACCESSRIGHTSLIST
ADD CONSTRAINT USERGROUPACCESSRIGHTSLIST_AC FOREIGN KEY (ACCESSRIGHTSLIST_ID) REFERENCES ACCESSRIGHTSLIST (ACCESSRIGHTSLIST_ID) ON DELETE CASCADE;

