--ABSN-8313


--USERROLETEMPLATE TABLE
ALTER TABLE USERROLETEMPLATE DROP COLUMN SCREENTEMPLATE_ID;
ALTER TABLE USERROLETEMPLATE DROP COLUMN USAGE;
DELETE FROM USERROLETEMPLATE;
COMMIT;

--SCREENCOMPONENTLINE TABLE
DROP TABLE SCREENTEMPLATELINE;
DROP SEQUENCE SEQ_SCREENTEMPLATELINE;

--SCREENCOMPONENT TABLE 
DROP TABLE SCREENCOMPONENT;
DROP SEQUENCE SEQ_SCREENCOMPONENT;

CREATE TABLE SCREENCOMPONENT (
SCREENCOMPONENT_ID NUMBER NOT NULL,
COMPONENTTYPE      NUMBER NOT NULL CONSTRAINT CHK_SCREENCOMPONENT_COMPONENTTYPE CHECK (COMPONENTTYPE IN (0,1,2)),
COMPONENTNAME      VARCHAR2(30) NOT NULL,
INTERNALNAME       VARCHAR2(50) NOT NULL,
SYSTEMUSER_ID      NUMBER DEFAULT 0 NOT NULL,
TIMESTAMP          DATE DEFAULT SYSDATE NOT NULL
);

COMMENT ON COLUMN SCREENCOMPONENT.COMPONENTTYPE IS '0 = Ribbon / Navigation Rail 1 = Widget 2 = Action Button';
COMMENT ON COLUMN SCREENCOMPONENT.INTERNALNAME IS 'Java name of the component';

ALTER TABLE SCREENCOMPONENT ADD CONSTRAINT XPKSCREENCOMPONENT PRIMARY KEY (SCREENCOMPONENT_ID);


CREATE UNIQUE INDEX XAK1SCREENCOMPONENT ON SCREENCOMPONENT (COMPONENTTYPE ASC, COMPONENTNAME ASC);
CREATE UNIQUE INDEX XAK2SCREENCOMPONENT ON SCREENCOMPONENT (INTERNALNAME ASC);

--SCREENFUNCTION TABLE
CREATE TABLE SCREENFUNCTION (
SCREENFUNCTION_ID NUMBER NOT NULL,
SCREENNAME        VARCHAR2(40) NOT NULL,
SYSTEMUSER_ID     NUMBER DEFAULT 0 NOT NULL,
TIMESTAMP         DATE DEFAULT SYSDATE NOT NULL
);

ALTER TABLE SCREENFUNCTION ADD CONSTRAINT XPKSCREENFUNCTION PRIMARY KEY (SCREENFUNCTION_ID);

CREATE UNIQUE INDEX XAK1SCREENFUNCTION ON SCREENFUNCTION (SCREENNAME ASC);

--SCREENCOMPONENTFUNCTION TABLE
CREATE TABLE SCREENCOMPONENTFUNCTION (
SCREENCOMPONENT_ID NUMBER NOT NULL,
SCREENFUNCTION_ID  NUMBER NOT NULL,
SYSTEMUSER_ID      NUMBER DEFAULT 0 NOT NULL,
TIMESTAMP          DATE DEFAULT SYSDATE NOT NULL
);

ALTER TABLE SCREENCOMPONENTFUNCTION ADD CONSTRAINT XPKSCREENCOMPONENTFUNCTION PRIMARY KEY (SCREENCOMPONENT_ID, SCREENFUNCTION_ID);

CREATE INDEX XIF1SCREENCOMPONENTFUNCTION ON SCREENCOMPONENTFUNCTION (SCREENCOMPONENT_ID ASC);
CREATE INDEX XIF2SCREENCOMPONENTFUNCTION ON SCREENCOMPONENTFUNCTION (SCREENFUNCTION_ID ASC);

ALTER TABLE SCREENCOMPONENTFUNCTION ADD CONSTRAINT FK_SCREENCMPNENTFNCTN_SCREENCMPNENT_ID FOREIGN KEY (SCREENCOMPONENT_ID) REFERENCES SCREENCOMPONENT (SCREENCOMPONENT_ID) ON DELETE CASCADE;
ALTER TABLE SCREENCOMPONENTFUNCTION ADD CONSTRAINT FK_SCREENCMPNENTFNCTN_SCREENFNCTION_ID FOREIGN KEY (SCREENFUNCTION_ID) REFERENCES SCREENFUNCTION (SCREENFUNCTION_ID) ON DELETE CASCADE;


--SCREENTEMPLATE TABLE
DROP TABLE SCREENTEMPLATE;
DROP SEQUENCE SEQ_SCREENTEMPLATE;

CREATE TABLE SCREENTEMPLATE (
SCREENFUNCTION_ID  NUMBER NOT NULL,
SCREENTEMPLATECODE VARCHAR2(12) NOT NULL,
DESCRIPTION        VARCHAR2(50) NOT NULL,
DEFAULTTEMPLATE    VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_SCREENTEMPLATE_DEFAULTTEMPLATE CHECK (DEFAULTTEMPLATE IN ('Y','N')),
SYSTEMUSER_ID      NUMBER DEFAULT 0 NOT NULL,
TIMESTAMP          DATE DEFAULT SYSDATE NOT NULL
);

COMMENT ON COLUMN SCREENTEMPLATE.DEFAULTTEMPLATE IS 'There should be exactly 1 marked as default per ScreenFunction';

ALTER TABLE SCREENTEMPLATE ADD CONSTRAINT XPKSCREENTEMPLATE PRIMARY KEY (SCREENFUNCTION_ID, SCREENTEMPLATECODE);

--SCREENTEMPLATELINE TABLE 
CREATE SEQUENCE SEQ_SCREENTEMPLATELINE
START WITH 1
INCREMENT BY 1;

CREATE TABLE SCREENTEMPLATELINE (
SCREENTEMPLATELINE_ID NUMBER NOT NULL,
SCREENCOMPONENT_ID    NUMBER NOT NULL,
SCREENFUNCTION_ID     NUMBER NOT NULL,
SCREENTEMPLATECODE    VARCHAR2(12) NOT NULL,
SEQUENCENUMBER        NUMBER NOT NULL,
EXPANDED              VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_SCREENTEMPLATELINE_EXPANDED CHECK (EXPANDED IN ('Y','N')),
SYSTEMUSER_ID         NUMBER DEFAULT 0 NOT NULL,
TIMESTAMP              DATE DEFAULT SYSDATE NOT NULL
);

COMMENT ON COLUMN SCREENTEMPLATELINE.SEQUENCENUMBER IS 'max(sequencenumber) + 1 per ScreenTemplate';
COMMENT ON COLUMN SCREENTEMPLATELINE.EXPANDED IS '0 = Collapsed 1 = Expanded Only applicable for ComponentType = 1 (Widget) and ShowComponent = 0';

ALTER TABLE SCREENTEMPLATELINE ADD CONSTRAINT XPKSCREENTEMPLATELINE PRIMARY KEY (SCREENTEMPLATELINE_ID);

CREATE UNIQUE INDEX XAK1SCREENTEMPLATELINE ON SCREENTEMPLATELINE (SCREENFUNCTION_ID ASC, SCREENTEMPLATECODE ASC, SEQUENCENUMBER ASC);
CREATE UNIQUE INDEX XAK2SCREENTEMPLATELINE ON SCREENTEMPLATELINE (SCREENFUNCTION_ID ASC, SCREENTEMPLATECODE ASC, SCREENCOMPONENT_ID ASC);

CREATE INDEX XIF1SCREENTEMPLATELINE ON SCREENTEMPLATELINE (SCREENFUNCTION_ID ASC, SCREENTEMPLATECODE ASC);
CREATE INDEX XIF2SCREENTEMPLATELINE ON SCREENTEMPLATELINE (SCREENCOMPONENT_ID ASC, SCREENFUNCTION_ID ASC);

ALTER TABLE SCREENTEMPLATELINE ADD CONSTRAINT FK_SCREENTEMPLATELINE_SCREENTEMPLATE FOREIGN KEY (SCREENFUNCTION_ID, SCREENTEMPLATECODE) REFERENCES SCREENTEMPLATE (SCREENFUNCTION_ID, SCREENTEMPLATECODE);
ALTER TABLE SCREENTEMPLATELINE ADD CONSTRAINT FK_SCREENTEMPLATELINE_SCREENCOMPONENTFNCTN FOREIGN KEY (SCREENCOMPONENT_ID, SCREENFUNCTION_ID) REFERENCES SCREENCOMPONENTFUNCTION (SCREENCOMPONENT_ID, SCREENFUNCTION_ID);

--USERROLETEMPLATE TABLE
ALTER TABLE USERROLETEMPLATE ADD(
SCREENFUNCTION_ID  NUMBER NOT NULL,
SCREENTEMPLATECODE VARCHAR2(12) NOT NULL
);

CREATE UNIQUE INDEX XAK1USERROLETEMPLATE ON USERROLETEMPLATE (USERROLE_ID ASC, SCREENFUNCTION_ID ASC);
CREATE UNIQUE INDEX XAK2USERROLETEMPLATE ON USERROLETEMPLATE (USERROLE_ID ASC, SCREENFUNCTION_ID ASC, SCREENTEMPLATECODE ASC);

CREATE INDEX XIF2USERROLETEMPLATE ON USERROLETEMPLATE (SCREENFUNCTION_ID ASC, SCREENTEMPLATECODE ASC);
CREATE INDEX XIF3USERROLETEMPLATE ON USERROLETEMPLATE (SCREENFUNCTION_ID ASC);

ALTER TABLE USERROLETEMPLATE ADD CONSTRAINT FK_USERROLETEMPLATE_SCREENTEMPLATE FOREIGN KEY (SCREENFUNCTION_ID, SCREENTEMPLATECODE) REFERENCES SCREENTEMPLATE (SCREENFUNCTION_ID, SCREENTEMPLATECODE);
ALTER TABLE USERROLETEMPLATE ADD CONSTRAINT FK_USERROLETEMPLATE_SCREENFUNCTION FOREIGN KEY (SCREENFUNCTION_ID) REFERENCES SCREENFUNCTION;
