--ABS-158303

--PERIPHERALAGENT TABLE
CREATE SEQUENCE SEQ_PERIPHERALAGENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE PERIPHERALAGENT(
PERIPHERALAGENT_ID    NUMBER NOT NULL,
PLANT_ID               NUMBER NOT NULL,
CODE                   VARCHAR2(6) NOT NULL,
DESCRIPTION            VARCHAR2(30) NOT NULL,
HOSTNAME               VARCHAR2(80) NOT NULL,
PORTNUMBER             NUMBER NOT NULL,
CONNECTIONTIMEOUTTIME  NUMBER DEFAULT 30 NOT NULL,
TIMESTAMP             DATE NOT NULL,
SYSTEMUSER_ID          NUMBER NOT NULL);

ALTER TABLE PERIPHERALAGENT ADD CONSTRAINT XPKPERIPHERALAGENT PRIMARY KEY (PERIPHERALAGENT_ID);

CREATE UNIQUE INDEX XAK1PERIPHERALAGENT ON PERIPHERALAGENT (PLANT_ID ASC, CODE ASC);
CREATE UNIQUE INDEX XAK2PERIPHERALAGENT ON PERIPHERALAGENT (HOSTNAME ASC, PORTNUMBER ASC);

CREATE INDEX XIF1PERIPHERALAGENT ON PERIPHERALAGENT (PLANT_ID ASC);

ALTER TABLE PERIPHERALAGENT
  ADD CONSTRAINT FK_PERIPHERALAGENT_PLANT_ID FOREIGN KEY (PLANT_ID)
      REFERENCES PLANT (PLANT_ID) ON DELETE CASCADE;


--WORKSTATION TABLE
ALTER TABLE WORKSTATION ADD (PERIPHERALAGENT_ID NUMBER NULL);

CREATE INDEX XIF8WORKSTATION ON WORKSTATION (PERIPHERALAGENT_ID ASC);

ALTER TABLE WORKSTATION
  ADD CONSTRAINT FK_WORKSTATION_PERIPHERALAGENT_ID FOREIGN KEY (PERIPHERALAGENT_ID)
      REFERENCES PERIPHERALAGENT (PERIPHERALAGENT_ID);


--REMOTEPERIPHERALAGENTUSE TABLE
CREATE TABLE REMOTEPERIPHERALAGENTUSE(
BYWORKSTATION_ID        NUMBER NOT NULL,
FROMWORKSTATION_ID      NUMBER NOT NULL,
SYSTEMUSER_ID           NUMBER NOT NULL,
TIMESTAMP               DATE NOT NULL);

ALTER TABLE REMOTEPERIPHERALAGENTUSE ADD CONSTRAINT XPKREMOTEPERIPHERALAGENTUSE PRIMARY KEY (BYWORKSTATION_ID, FROMWORKSTATION_ID);

CREATE INDEX XIF1REMOTEPERIPHERALAGENTUSE ON REMOTEPERIPHERALAGENTUSE (BYWORKSTATION_ID ASC);
CREATE INDEX XIF2REMOTEPERIPHERALAGENTUSE ON REMOTEPERIPHERALAGENTUSE (FROMWORKSTATION_ID ASC);

ALTER TABLE REMOTEPERIPHERALAGENTUSE
  ADD CONSTRAINT FK_REMOTEPERIAGENTUSE_BYWORKSTATION FOREIGN KEY (BYWORKSTATION_ID)
      REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE CASCADE;

ALTER TABLE REMOTEPERIPHERALAGENTUSE
  ADD CONSTRAINT FK_REMOTEPERIAGENTUSE_FROMWORKSTATION FOREIGN KEY (FROMWORKSTATION_ID)
      REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE CASCADE;

ALTER TABLE REMOTEPERIPHERALAGENTUSE
  ADD CONSTRAINT CHK_REMOTEPERIPHERALAGENTUSE_WORKSTATION
           CHECK (BYWORKSTATION_ID <> FROMWORKSTATION_ID);


      
