--ABSN-10337

--SCREENCOMPONENT TABLE
ALTER TABLE SCREENCOMPONENT ADD (ACTIVE VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_SCREENCOMPONENT_ACTIVE CHECK (ACTIVE IN ('Y', 'N')),
                                 ACCESSMODE NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_SCREENCOMPONENT_ACCESS CHECK (ACCESSMODE IN (0, 1, 2)));

ALTER TABLE SCREENCOMPONENT ADD CONSTRAINT CHK_SCREENCOMPONENT_COMPACC CHECK ((COMPONENTTYPE IN (0, 2) AND ACCESSMODE = 0) OR (COMPONENTTYPE NOT IN (0, 2)));

COMMENT ON COLUMN SCREENCOMPONENT.ACCESSMODE IS '0 = Fixed Full Access
                                             1 = Fixed Read Only Access
                                             2 = User can choose Full or ReadOnly. This applies to ScreeTemplateLine components';

--DROP
DROP SEQUENCE SEQ_SCREENTEMPLATELINE;
DROP TABLE SCREENTEMPLATELINE;
DROP TABLE SCREENCOMPONENTFUNCTION;

--DELETE FROM SCREENCOMPONENT
BEGIN
    EXECUTE IMMEDIATE 'DELETE FROM SCREENCOMPONENT';
    COMMIT;
EXCEPTION WHEN OTHERS THEN
     NULL;
END;
/
--DELETE FROM USERROLETEMPLATE
BEGIN
    EXECUTE IMMEDIATE 'DELETE FROM USERROLETEMPLATE';
    COMMIT;
EXCEPTION WHEN OTHERS THEN
     NULL;
END;
/

ALTER TABLE USERROLETEMPLATE DROP CONSTRAINT XPKUSERROLETEMPLATE;

ALTER TABLE USERROLETEMPLATE DROP CONSTRAINT FK_USERROLETEMPLATE_SCREENTEMPLATE;

--columns from USERROLETEMPLATE
ALTER TABLE USERROLETEMPLATE DROP COLUMN USERROLETEMPLATE_ID;
ALTER TABLE USERROLETEMPLATE DROP COLUMN SCREENFUNCTION_ID;
ALTER TABLE USERROLETEMPLATE DROP COLUMN SCREENTEMPLATECODE;

DROP SEQUENCE SEQ_SCREENTEMPLATE;
DROP TABLE SCREENTEMPLATE;

--SCREENCOMPONENT_DESC TABLE
CREATE TABLE SCREENCOMPONENT_DESC(
SCREENCOMPONENT_ID NUMBER NOT NULL,
LANGUAGE_ID        NUMBER NOT NULL,
DESCRIPTION        VARCHAR2(30) NOT NULL,
SYSTEMUSER         NUMBER NOT NULL,
TIMESTAMP          DATE DEFAULT SYSDATE NOT NULL);

ALTER TABLE SCREENCOMPONENT_DESC ADD CONSTRAINT XPKSCREENCOMPONENT_DESC PRIMARY KEY (SCREENCOMPONENT_ID, LANGUAGE_ID);

CREATE INDEX XIF1SCREENCOMPONENT_DESC ON SCREENCOMPONENT_DESC (SCREENCOMPONENT_ID ASC);
CREATE INDEX XIF2SCREENCOMPONENT_DESC ON SCREENCOMPONENT_DESC (LANGUAGE_ID ASC);

ALTER TABLE SCREENCOMPONENT_DESC
  ADD CONSTRAINT FK_SCREENCOMPONENT_DESC_SCRNCMP_ID FOREIGN KEY (SCREENCOMPONENT_ID) REFERENCES SCREENCOMPONENT (SCREENCOMPONENT_ID) ON DELETE CASCADE;

ALTER TABLE SCREENCOMPONENT_DESC
  ADD CONSTRAINT FK_SCREENCOMPONENT_DESC_LNG_ID FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGES (LANGUAGE_ID);
  
--SCREENCOMPONENTDIALOG TABLE
CREATE SEQUENCE SEQ_SCREENCOMPONENTDIALOG
START WITH 1
INCREMENT BY 1;

CREATE TABLE SCREENCOMPONENTDIALOG(
SCREENCOMPONENTDIALOG_ID NUMBER NOT NULL,
SCREENCOMPONENT_ID       NUMBER NOT NULL,
DIALOG_ID                NUMBER NOT NULL,
SYSTEMUSER_ID            NUMBER DEFAULT 0 NOT NULL,
TIMESTAMP                DATE DEFAULT SYSDATE NOT NULL);

ALTER TABLE SCREENCOMPONENTDIALOG ADD CONSTRAINT XPKSCREENCOMPONENTDIALOG PRIMARY KEY (SCREENCOMPONENTDIALOG_ID);

CREATE UNIQUE INDEX XAK1SCREENCOMPONENTDIALOG ON SCREENCOMPONENTDIALOG (SCREENCOMPONENT_ID ASC, DIALOG_ID ASC);
CREATE INDEX XIF1SCREENCOMPONENTDIALOG ON SCREENCOMPONENTDIALOG (SCREENCOMPONENT_ID ASC);
CREATE INDEX XIF2SCREENCOMPONENTDIALOG ON SCREENCOMPONENTDIALOG (DIALOG_ID ASC);

ALTER TABLE SCREENCOMPONENTDIALOG
  ADD CONSTRAINT FK_SCREENCOMPONENTDIALOG_SCRNCMP_ID FOREIGN KEY (SCREENCOMPONENT_ID) REFERENCES SCREENCOMPONENT (SCREENCOMPONENT_ID) ON DELETE CASCADE;

ALTER TABLE SCREENCOMPONENTDIALOG
  ADD CONSTRAINT FK_SCREENCOMPONENTDIALOG_DLG_ID FOREIGN KEY (DIALOG_ID) REFERENCES DIALOGS (DIALOG_ID);

--SCREENTEMPLATE TABLE
CREATE SEQUENCE SEQ_SCREENTEMPLATE
START WITH 1
INCREMENT BY 1;

CREATE TABLE SCREENTEMPLATE(
SCREENTEMPLATE_ID NUMBER NOT NULL,
CODE              VARCHAR2(12) NOT NULL,
DIALOG_ID         NUMBER NOT NULL,
REMARK            VARCHAR2(500) NULL,
DESCRIPTION       VARCHAR2(40) NOT NULL,
ACCESSMODE        NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_SCREENTEMPLATE_ACCESSMODE CHECK (ACCESSMODE IN (0, 1)),
SYSTEMUSER_ID     NUMBER DEFAULT 0 NOT NULL,
TIMESTAMP         DATE DEFAULT SYSDATE NOT NULL);

COMMENT ON COLUMN SCREENTEMPLATE.ACCESSMODE IS '0 = FullAccess 1 = ReadOnlyAccess';

ALTER TABLE SCREENTEMPLATE ADD CONSTRAINT XPKSCREENTEMPLATE PRIMARY KEY (SCREENTEMPLATE_ID);

CREATE UNIQUE INDEX XAKSCREENTEMPLATE ON SCREENTEMPLATE (CODE ASC, DIALOG_ID ASC);
CREATE INDEX XIF1SCREENTEMPLATE ON SCREENTEMPLATE (DIALOG_ID ASC);

ALTER TABLE SCREENTEMPLATE
  ADD CONSTRAINT FK_SCREENTEMPLATE_DIALOG_ID FOREIGN KEY (DIALOG_ID) REFERENCES DIALOGS (DIALOG_ID);
  
--SCREENTEMPLATELINE TABLE
CREATE TABLE SCREENTEMPLATELINE(
SCREENTEMPLATE_ID        NUMBER NOT NULL, 
SCREENCOMPONENTDIALOG_ID NUMBER NOT NULL,
ACCESSMODE               NUMBER NOT NULL CONSTRAINT CHK_SCREENTEMPLATELINE_ACCESSMODE CHECK (ACCESSMODE IN (0, 1)),
SEQUENCENUMBER           NUMBER NOT NULL,
EXPANDED                 VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_SCREENTEMPLATELINE_EXPND CHECK (EXPANDED IN ('Y', 'N')),
SYSTEMUSER_ID            NUMBER DEFAULT 0 NOT NULL,
TIMESTAMP                DATE   DEFAULT SYSDATE NOT NULL);

COMMENT ON COLUMN SCREENTEMPLATELINE.ACCESSMODE IS '0 = FullAccess 1 = ReadOnlyAccess';
COMMENT ON COLUMN SCREENTEMPLATELINE.EXPANDED IS 'N = Collapsed Y = Expanded. Only applicable for ComponentType = 1 (Widget)';

ALTER TABLE SCREENTEMPLATELINE ADD CONSTRAINT XPKSCREENTEMPLATELINE PRIMARY KEY (SCREENTEMPLATE_ID, SCREENCOMPONENTDIALOG_ID);

CREATE INDEX XIF1SCREENTEMPLATELINE ON SCREENTEMPLATELINE (SCREENTEMPLATE_ID ASC);
CREATE INDEX XIF2SCREENTEMPLATELINE ON SCREENTEMPLATELINE (SCREENCOMPONENTDIALOG_ID ASC);

ALTER TABLE SCREENTEMPLATELINE
  ADD CONSTRAINT FK_SCREENTEMPLATELINE_SCRNTMP_ID FOREIGN KEY (SCREENTEMPLATE_ID) REFERENCES SCREENTEMPLATE (SCREENTEMPLATE_ID) ON DELETE CASCADE;

ALTER TABLE SCREENTEMPLATELINE
  ADD CONSTRAINT FK_SCREENTEMPLATELINE_SCRNCMPDLG_ID FOREIGN KEY (SCREENCOMPONENTDIALOG_ID) REFERENCES SCREENCOMPONENTDIALOG (SCREENCOMPONENTDIALOG_ID) ON DELETE CASCADE; 

--ROLEGROUP TABLE
CREATE SEQUENCE SEQ_ROLEGROUP
START WITH 1
INCREMENT BY 1;

CREATE TABLE ROLEGROUP (
ROLEGROUP_ID  NUMBER NOT NULL,
CODE          VARCHAR2(12) NOT NULL,
DESCRIPTION   VARCHAR2(50) NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP     DATE NOT NULL);

ALTER TABLE ROLEGROUP ADD CONSTRAINT XPKROLEGROUP PRIMARY KEY (ROLEGROUP_ID);

CREATE UNIQUE INDEX XAK1ROLEGROUP ON ROLEGROUP (CODE ASC);

--USERROLE TABLE
ALTER TABLE USERROLE ADD (ROLEGROUP_ID NUMBER NULL,
                          REMARK       VARCHAR2(500) NULL);

CREATE INDEX XIF1USERROLE ON USERROLE (ROLEGROUP_ID ASC);

ALTER TABLE USERROLE 
  ADD CONSTRAINT FK_USERROLE_ROLEGROUP_ID FOREIGN KEY (ROLEGROUP_ID) REFERENCES ROLEGROUP (ROLEGROUP_ID) ON DELETE SET NULL;

--USERROLETEMPLATE TABLE
ALTER TABLE USERROLETEMPLATE ADD (SCREENTEMPLATE_ID NUMBER NOT NULL);

ALTER TABLE USERROLETEMPLATE ADD CONSTRAINT XPKUSERROLETEMPLATE PRIMARY KEY (USERROLE_ID, SCREENTEMPLATE_ID);

CREATE INDEX XIF2USERROLETEMPLATE ON USERROLETEMPLATE (SCREENTEMPLATE_ID ASC);

ALTER TABLE USERROLETEMPLATE 
  ADD CONSTRAINT FK_USERROLETEMPLATE_SCREENTEMPLATE_ID FOREIGN KEY (SCREENTEMPLATE_ID) REFERENCES SCREENTEMPLATE (SCREENTEMPLATE_ID);

--USERGROUPROLE TABLE
CREATE TABLE USERGROUPROLE(
USERGROUP_ID  NUMBER NOT NULL,
USERROLE_ID   NUMBER NOT NULL,
DATEACTIVE    DATE DEFAULT SYSDATE NOT NULL,
DATEINACTIVE  DATE DEFAULT TO_DATE('31/12/2099', 'DD/MM/YYYY') NOT NULL,
REMARK        VARCHAR2(500) NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP     DATE NOT NULL);

ALTER TABLE USERGROUPROLE ADD CONSTRAINT XPKUSERGROUPROLE PRIMARY KEY (USERGROUP_ID, USERROLE_ID, DATEACTIVE);

CREATE INDEX XIF1USERGROUPROLE ON USERGROUPROLE (USERGROUP_ID ASC);
CREATE INDEX XIF2USERGROUPROLE ON USERGROUPROLE (USERROLE_ID ASC);

ALTER TABLE USERGROUPROLE 
  ADD CONSTRAINT FK_USERGROUPROLE_USERGROUP_ID FOREIGN KEY (USERGROUP_ID) REFERENCES USERGROUP (USERGROUP_ID);

ALTER TABLE USERGROUPROLE
  ADD CONSTRAINT FK_USERGROUPROLE_USERROLE_ID FOREIGN KEY (USERROLE_ID) REFERENCES USERROLE (USERROLE_ID);
  
--SYSTEMUSERROLE TABLE
CREATE TABLE SYSTEMUSERROLE(
SYSTEMUSER_ID       NUMBER NOT NULL,
USERROLE_ID         NUMBER NOT NULL,
DATEACTIVE          DATE DEFAULT SYSDATE NOT NULL,
DATEINACTIVE        DATE DEFAULT TO_DATE('31/12/2099', 'DD/MM/YYYY') NOT NULL,
REMARK              VARCHAR2(500) NULL,
TIMESTAMP           DATE NOT NULL,
UPDATEDBYSYSUSER_ID NUMBER NOT NULL);

COMMENT ON COLUMN SYSTEMUSERROLE.UPDATEDBYSYSUSER_ID IS 'ID of the last systemuser that updated this record. Login in excluded from updating this attribute.';

ALTER TABLE SYSTEMUSERROLE ADD CONSTRAINT XPKSYSTEMUSERROLE PRIMARY KEY (SYSTEMUSER_ID, USERROLE_ID, DATEACTIVE);

CREATE INDEX XIF1SYSTEMUSERROLE ON SYSTEMUSERROLE (SYSTEMUSER_ID ASC);
CREATE INDEX XIF2SYSTEMUSERROLE ON SYSTEMUSERROLE (USERROLE_ID ASC);

ALTER TABLE SYSTEMUSERROLE
   ADD CONSTRAINT FK_SYSTEMUSERROLE_SYSTEMUSER_ID FOREIGN KEY (SYSTEMUSER_ID) REFERENCES SYSTEMUSER (SYSTEMUSER_ID);

ALTER TABLE SYSTEMUSERROLE
  ADD CONSTRAINT FK_SYSTEMUSERROLE_USERROLE_ID FOREIGN KEY (USERROLE_ID) REFERENCES USERROLE (USERROLE_ID);
