-- ABS-21030
                     
--------- UHFREADERSTATUS ---------                
CREATE TABLE UHFREADERSTATUS (
UHFREADERCONFIGURATION_ID NUMBER NOT NULL,  
STATUS                    NUMBER NOT NULL CONSTRAINT STATUS_VALID8_1 CHECK (STATUS IN (-1,0,1)) ,                   
TIMESTAMPLASTSEEN         DATE NULL,
SYSTEMUSER_ID             NUMBER NOT NULL,
TIMESTAMP                 DATE NOT NULL);

COMMENT ON COLUMN UHFREADERSTATUS.STATUS IS 
'-1 = not responding
0 = not in use
1 = connected';

ALTER TABLE UHFREADERSTATUS ADD CONSTRAINT XPKUHFREADERSTATUS PRIMARY KEY (UHFREADERCONFIGURATION_ID);

ALTER TABLE UHFREADERSTATUS ADD CONSTRAINT UHFREADERSTATUS_UHFREADERCONF FOREIGN KEY (UHFREADERCONFIGURATION_ID) 
                            REFERENCES UHFREADERCONFIGURATION (UHFREADERCONFIGURATION_ID) ON DELETE CASCADE;


---------  UHFCONCENTRATOR ---------  
CREATE SEQUENCE SEQ_UHFCONCENTRATOR
START WITH 1
INCREMENT BY 1;

CREATE TABLE UHFCONCENTRATOR (
UHFCONCENTRATOR_ID        NUMBER NOT NULL,  
PLANT_ID                  NUMBER NOT NULL,
CODE                      VARCHAR2(6) NOT NULL,                   
DESCRIPTION               VARCHAR2(30) NOT NULL,
ACCESSIBLEFROMCENTRAL     VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD301 CHECK (ACCESSIBLEFROMCENTRAL IN ('Y','N')),
HOSTNAME                  VARCHAR2(60) NOT NULL,
HTTPPORTNUMBER            NUMBER NOT NULL,
SYSTEMUSER_ID             NUMBER NOT NULL,
TIMESTAMP                 DATE NOT NULL);

ALTER TABLE UHFCONCENTRATOR ADD CONSTRAINT XPKUHFCONCENTRATOR PRIMARY KEY (UHFCONCENTRATOR_ID);

CREATE UNIQUE INDEX XAK1UHFCONCENTRATOR ON UHFCONCENTRATOR (HOSTNAME ASC, HTTPPORTNUMBER ASC);

CREATE INDEX XIF1UHFCONCENTRATOR ON UHFCONCENTRATOR (PLANT_ID ASC);

ALTER TABLE UHFCONCENTRATOR ADD CONSTRAINT UHFCONCENTRATOR_PLANT FOREIGN KEY (PLANT_ID) 
                                REFERENCES PLANT (PLANT_ID);

--------- WORKSTATION ---------
ALTER TABLE WORKSTATION ADD (UNATTENDEDUHFSCANSTATION VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD302 CHECK (UNATTENDEDUHFSCANSTATION IN ('Y','N')),
                            SHOWATUHFMONITOR VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD303 CHECK (SHOWATUHFMONITOR IN ('Y','N')),
                            UHFCONCENTRATOR_ID NUMBER NULL,
                            UNATTENDEDSCANTRANSACTTYPE_ID NUMBER NULL);

CREATE INDEX XIF9WORKSTATION ON WORKSTATION (UHFCONCENTRATOR_ID ASC);

CREATE INDEX XIF10WORKSTATION ON WORKSTATION (UNATTENDEDSCANTRANSACTTYPE_ID ASC);

ALTER TABLE WORKSTATION  ADD CONSTRAINT WORKSTATION_UHFCONCENTRATOR FOREIGN KEY (UHFCONCENTRATOR_ID)
                         REFERENCES UHFCONCENTRATOR (UHFCONCENTRATOR_ID);
                         
ALTER TABLE WORKSTATION ADD CONSTRAINT WORKSTATION_UNATTENDEDTRANS FOREIGN KEY (UNATTENDEDSCANTRANSACTTYPE_ID)
                         REFERENCES TRANSACTIONTYPE (TRANSACTIONTYPE_ID);