--ABSN-948

--REPAIRTYPEGROUP TABLE

CREATE SEQUENCE SEQ_REPAIRTYPEGROUP
START WITH 1
INCREMENT BY 1;

CREATE TABLE REPAIRTYPEGROUP (
REPAIRTYPEGROUP_ID NUMBER NOT NULL,
CODE               VARCHAR2(6) NOT NULL CONSTRAINT CHK_REPAIRTYPEGRP_CODE CHECK (CODE = UPPER(CODE)),
FACTORYDESCRIPTION VARCHAR2(30) NOT NULL,
ACTIVE             VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_REPAIRTYPEGRP_ACTIVE CHECK (ACTIVE IN ('Y', 'N')),
USAGE              NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_REPAIRTYPEGRP_USAGE CHECK (USAGE IN (0,1,2)),
SYSTEMUSER_ID      NUMBER NOT NULL,
TIMESTAMP          DATE NOT NULL);

COMMENT ON COLUMN REPAIRTYPEGROUP.USAGE IS '0 = Grouping of Repair Types 1 = Grouping of Variable Service Charges 2 = Grouping of Container Repair Types';

ALTER TABLE REPAIRTYPEGROUP ADD CONSTRAINT XPKREPAIRTYPEGROUP PRIMARY KEY (REPAIRTYPEGROUP_ID);

CREATE UNIQUE INDEX XAK1REPAIRTYPEGROUP ON REPAIRTYPEGROUP (CODE ASC, USAGE ASC);


--REPAIRTYPEGROUP_DESC TABLE

CREATE TABLE REPAIRTYPEGROUP_DESC (
REPAIRTYPEGROUP_ID NUMBER NOT NULL,
LANGUAGE_ID        NUMBER NOT NULL,
DESCRIPTION        VARCHAR2(30) NOT NULL,
SYSTEMUSER_ID      NUMBER NOT NULL,
TIMESTAMP          DATE DEFAULT SYSDATE NOT NULL);

ALTER TABLE REPAIRTYPEGROUP_DESC ADD CONSTRAINT XPKREPAIRTYPEGROUP_DESC PRIMARY KEY (REPAIRTYPEGROUP_ID, LANGUAGE_ID);

CREATE INDEX XIF1REPAIRTYPEGROUP_DESC ON REPAIRTYPEGROUP_DESC (REPAIRTYPEGROUP_ID ASC);
CREATE INDEX XIF2REPAIRTYPEGROUP_DESC ON REPAIRTYPEGROUP_DESC (LANGUAGE_ID ASC);

ALTER TABLE REPAIRTYPEGROUP_DESC ADD CONSTRAINT FK_REPAIRTYPEGROUP_DESC_REPAIRTYPEGROUP_ID FOREIGN KEY (REPAIRTYPEGROUP_ID) REFERENCES REPAIRTYPEGROUP (REPAIRTYPEGROUP_ID) ON DELETE CASCADE;
ALTER TABLE REPAIRTYPEGROUP_DESC ADD CONSTRAINT FK_REPAIRTYPEGROUP_DESC_LANGUAGE_ID FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGES (LANGUAGE_ID);

--REPAIRTYPE TABLE

ALTER TABLE REPAIRTYPE ADD (REPAIRTYPEGROUP_ID NUMBER NULL);
CREATE INDEX XIF3REPAIRTYPE ON REPAIRTYPE (REPAIRTYPEGROUP_ID);
ALTER TABLE REPAIRTYPE ADD CONSTRAINT FK_REPAIRTYPE_REPAIRTYPEGROUP_ID FOREIGN KEY (REPAIRTYPEGROUP_ID) REFERENCES REPAIRTYPEGROUP (REPAIRTYPEGROUP_ID);

--SCREENCOMPONENT TABLE
CREATE SEQUENCE SEQ_SCREENCOMPONENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE SCREENCOMPONENT(
SCREENCOMPONENT_ID   NUMBER NOT NULL,
COMPONENTTYPE        NUMBER NOT NULL CONSTRAINT CHK_SCREENCOMPONENT_COMPONENTTYPE CHECK (COMPONENTTYPE IN (0,1,2)),
DESCRIPTION          VARCHAR2(30) NOT NULL,
INTERNALNAME         VARCHAR2(50) NOT NULL,
MODULENAME           VARCHAR2(100),
USESOILSCAN          VARCHAR2(1) NOT NULL CONSTRAINT CHK_SCREENCOMPONENT_USESOILSCAN CHECK (USESOILSCAN IN ('Y','N')),
USECLEANSCAN         VARCHAR2(1) NOT NULL CONSTRAINT CHK_SCREENCOMPONENT_USECLEANSCAN CHECK (USECLEANSCAN IN ('Y','N')),
USECHECKPOINTSCAN    VARCHAR2(1) NOT NULL CONSTRAINT CHK_SCREENCOMPONENT_USECHECKPOINTSCAN CHECK (USECHECKPOINTSCAN IN ('Y','N')),
USEREWASHSCAN        VARCHAR2(1) NOT NULL CONSTRAINT CHK_SCREENCOMPONENT_USEREWASHSCAN CHECK (USEREWASHSCAN IN ('Y','N')),
USERETURNTOSTOCKSCAN VARCHAR2(1) NOT NULL CONSTRAINT CHK_SCREENCOMPONENT_USERETURNTOSTOCKSCAN CHECK (USERETURNTOSTOCKSCAN IN ('Y','N')),
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';

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

CREATE UNIQUE INDEX XAK1SCREENCOMPONENT ON SCREENCOMPONENT (COMPONENTTYPE ASC, DESCRIPTION ASC);

--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,
DESCRIPTION       VARCHAR2(50) NOT NULL,
ACTIVE            VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_SCREENTEMPLATE_ACTIVE CHECK (ACTIVE IN ('Y','N')),
USAGE             NUMBER NOT NULL CONSTRAINT CHK_SCREENTEMPLATE_USAGE CHECK (USAGE IN (1,2,3,4,5)),
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.USAGE IS '0 = Not defined, 1 = Soil Scan 2 = Clean scan 3 = Checkpoint scan 4 = Rewash scan 5 = Return to stock scan';
COMMENT ON COLUMN SCREENTEMPLATE.DEFAULTTEMPLATE IS 'Per ScanFunction there should be exactly 1 marked as default (default must be active)';

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

CREATE UNIQUE INDEX XAK1SCREENTEMPLATE ON SCREENTEMPLATE (USAGE ASC, CODE ASC);

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

CREATE TABLE SCREENTEMPLATELINE(
SCREENTEMPLATELINE_ID NUMBER NOT NULL,
SCREENTEMPLATE_ID     NUMBER NOT NULL,
SCREENCOMPONENT_ID    NUMBER NOT NULL,
COMPONENTTYPE         NUMBER NOT NULL CONSTRAINT CHK_SCREENTEMPLATELINE_COMPONENTTYPE CHECK (COMPONENTTYPE IN (0,1,2)),                             
SEQUENCENUMBER        NUMBER NOT NULL,
SHOWCOMPONENT         NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_SCREENTEMPLATELINE_SHOWCOMPONENT CHECK (SHOWCOMPONENT IN (0,1)),
DEFAULTAPPEARANCE     NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_SCREENTEMPLATELINE_DEFAULTAPPEARANCE CHECK (DEFAULTAPPEARANCE IN (0,1)),
SYSTEMUSER_ID         NUMBER DEFAULT 0 NOT NULL,
TIMESTAMP             DATE DEFAULT SYSDATE NOT NULL
);

COMMENT ON COLUMN SCREENTEMPLATELINE.COMPONENTTYPE IS '0 = Ribbon / Navigation Rail 1 = Widget 2 = Action Button';
COMMENT ON COLUMN SCREENTEMPLATELINE.SHOWCOMPONENT IS '0 = Show 1 = Do not Show';
COMMENT ON COLUMN SCREENTEMPLATELINE.DEFAULTAPPEARANCE IS '0 = Collapsed 1 = Expanded';

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

CREATE UNIQUE INDEX XAK1SCREENTEMPLATELINE ON SCREENTEMPLATELINE (SCREENTEMPLATE_ID ASC, COMPONENTTYPE ASC, SEQUENCENUMBER ASC);
CREATE UNIQUE INDEX XAK2SCREENTEMPLATELINE ON SCREENTEMPLATELINE (SCREENTEMPLATE_ID ASC, SCREENCOMPONENT_ID ASC);

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

ALTER TABLE SCREENTEMPLATELINE ADD CONSTRAINT FK_SCREENTEMPLATELINE_SCREENTEMPLATE_ID FOREIGN KEY (SCREENTEMPLATE_ID) REFERENCES SCREENTEMPLATE (SCREENTEMPLATE_ID) ON DELETE CASCADE;
ALTER TABLE SCREENTEMPLATELINE ADD CONSTRAINT FK_SCREENTEMPLATELINE_SCREENCOMPONENT_ID FOREIGN KEY (SCREENCOMPONENT_ID) REFERENCES SCREENCOMPONENT (SCREENCOMPONENT_ID) ON DELETE CASCADE;

--USERROLE TABLE
CREATE SEQUENCE SEQ_USERROLE
START WITH 1
INCREMENT BY 1;

CREATE TABLE USERROLE (
USERROLE_ID   NUMBER NOT NULL,
CODE          VARCHAR2(12) NOT NULL CONSTRAINT CHK_USERROLE_CODE CHECK (CODE = UPPER(CODE)),
DESCRIPTION   VARCHAR2(50) NOT NULL,
ACTIVE        VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_USERROLE_ACTIVE CHECK (ACTIVE IN ('Y', 'N')),
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP     DATE NOT NULL
);

ALTER TABLE USERROLE ADD CONSTRAINT XPKUSERROLE PRIMARY KEY (USERROLE_ID);

CREATE UNIQUE INDEX XAK1USERROLE ON USERROLE (CODE ASC);

--USERROLETEMPLATE
CREATE SEQUENCE SEQ_USERROLETEMPLATE
START WITH 1
INCREMENT BY 1;

CREATE TABLE USERROLETEMPLATE(
USERROLETEMPLATE_ID NUMBER NOT NULL,
USERROLE_ID         NUMBER NOT NULL,
USAGE               NUMBER NOT NULL CONSTRAINT CHK_USERROLETEMPLATE_USAGE CHECK (USAGE IN (1,2,3,4,5)),
SCREENTEMPLATE_ID   NUMBER NOT NULL,
SYSTEMUSER_ID       NUMBER NOT NULL,
TIMESTAMP           DATE NOT NULL
);

COMMENT ON COLUMN USERROLETEMPLATE.USAGE IS '0 = Not defined, 1 = Soil Scan 2 = Clean scan 3 = Checkpoint scan 4 = Rewash scan 5 = Return to stock scan';

ALTER TABLE USERROLETEMPLATE ADD CONSTRAINT XPKUSERROLETEMPLATE PRIMARY KEY (USERROLETEMPLATE_ID);

CREATE UNIQUE INDEX XAK1USERROLETEMPLATE ON USERROLETEMPLATE (USERROLE_ID ASC, USAGE ASC);
CREATE UNIQUE INDEX XAK2USERROLETEMPLATE ON USERROLETEMPLATE (USERROLE_ID ASC, SCREENTEMPLATE_ID ASC);

CREATE INDEX XIF1USERROLETEMPLATE ON USERROLETEMPLATE (USERROLE_ID ASC);
CREATE INDEX XIF2USERROLETEMPLATE ON USERROLETEMPLATE (SCREENTEMPLATE_ID ASC);

ALTER TABLE USERROLETEMPLATE ADD CONSTRAINT FK_USERROLETEMPLATE_USERROLE_ID FOREIGN KEY (USERROLE_ID) REFERENCES USERROLE (USERROLE_ID) ON DELETE CASCADE;
ALTER TABLE USERROLETEMPLATE ADD CONSTRAINT FK_USERROLETEMPLATE_SCREENTEMPLATE_ID FOREIGN KEY (SCREENTEMPLATE_ID) REFERENCES SCREENTEMPLATE (SCREENTEMPLATE_ID);

--SYSTEMUSER TABLE
ALTER TABLE SYSTEMUSER ADD (USERROLE_ID NUMBER);

CREATE INDEX XIF1SYSTEMUSER ON SYSTEMUSER (USERROLE_ID ASC);

ALTER TABLE SYSTEMUSER ADD CONSTRAINT FK_SYSTEMUSER_USERROLE FOREIGN KEY (USERROLE_ID) REFERENCES USERROLE (USERROLE_ID);
