--ABS-25834/ ABS-48322

---WORKSTATIONPLCCONFIG table---
DROP TABLE WORKSTATIONPLCCONFIG;

CREATE SEQUENCE SEQ_WORKSTATIONPLCCONFIG
START WITH 1
INCREMENT BY 1;

CREATE TABLE WORKSTATIONPLCCONFIG
(
  WORKSTATIONPLCCONFIG_ID NUMBER NOT NULL,
  WORKSTATION_ID  NUMBER NOT NULL,
  RELAISNUMBER    NUMBER(2) NOT NULL CONSTRAINT RELAISNUMBER_VALID1_1
        CHECK (RELAISNUMBER BETWEEN 1 AND 99),
  BUSINESSUNIT_ID NUMBER NULL,
  DEPOT_ID        NUMBER NULL,
  SOUND_ID        NUMBER NULL,
  SYSTEMUSER_ID   NUMBER NOT NULL,
  TIMESTAMP       DATE NOT NULL
);

COMMENT ON COLUMN WORKSTATIONPLCCONFIG.RELAISNUMBER
  IS 'Internal relais number from the PLC (associated with a rail)';
COMMENT ON COLUMN WORKSTATIONPLCCONFIG.BUSINESSUNIT_ID
  IS 'BusinessUnit that is associated with the rail.';
COMMENT ON COLUMN WORKSTATIONPLCCONFIG.DEPOT_ID
  IS 'A rail should be associated with either a Business Unit or a depot, but not both.';
COMMENT ON COLUMN WORKSTATIONPLCCONFIG.SOUND_ID
  IS 'Sound associated with this rail.';

ALTER TABLE WORKSTATIONPLCCONFIG
  ADD CONSTRAINT XPKWORKSTATIONPLCCONFIG PRIMARY KEY (WORKSTATIONPLCCONFIG_ID);

CREATE UNIQUE INDEX XAK1WORKSTATIONPLCCONFIG ON WORKSTATIONPLCCONFIG (WORKSTATION_ID ASC, RELAISNUMBER ASC);
CREATE UNIQUE INDEX XAK2WORKSTATIONPLCCONFIG ON WORKSTATIONPLCCONFIG (WORKSTATION_ID ASC, BUSINESSUNIT_ID ASC, DEPOT_ID ASC);

CREATE INDEX XIF1WORKSTATIONPLCCONFIG ON WORKSTATIONPLCCONFIG (WORKSTATION_ID ASC);
CREATE INDEX XIF2WORKSTATIONPLCCONFIG ON WORKSTATIONPLCCONFIG (BUSINESSUNIT_ID ASC);
CREATE INDEX XIF3WORKSTATIONPLCCONFIG ON WORKSTATIONPLCCONFIG (DEPOT_ID ASC);
CREATE INDEX XIF4WORKSTATIONPLCCONFIG ON WORKSTATIONPLCCONFIG (SOUND_ID ASC);

ALTER TABLE WORKSTATIONPLCCONFIG
  ADD CONSTRAINT WORKSTATIONPLCCONFIG_WS FOREIGN KEY (WORKSTATION_ID)
  REFERENCES WORKSTATION (WORKSTATION_ID) ON DELETE CASCADE;
  
ALTER TABLE WORKSTATIONPLCCONFIG
  ADD CONSTRAINT WORKSTATIONPLCCONFIG_BU FOREIGN KEY (BUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE SET NULL;
  
ALTER TABLE WORKSTATIONPLCCONFIG
  ADD CONSTRAINT WORKSTATIONPLCCONFIG_DEP FOREIGN KEY (DEPOT_ID)
  REFERENCES DEPOT (DEPOT_ID) ON DELETE SET NULL;
  
ALTER TABLE WORKSTATIONPLCCONFIG
  ADD CONSTRAINT WORKSTATIONPLCCONFIG_SOUND FOREIGN KEY (SOUND_ID)
  REFERENCES SOUND (SOUND_ID) ON DELETE SET NULL;