-- ABS-62329/ ABS-64991

-- LOCALPRINTSERVER TABLE --
CREATE SEQUENCE SEQ_LOCALPRINTSERVER
START WITH 1
INCREMENT BY 1;

CREATE TABLE LOCALPRINTSERVER
(
  LOCALPRINTSERVER_ID NUMBER NOT NULL,
  PLANT_ID            NUMBER NOT NULL,
  CODE                VARCHAR2(6) NOT NULL,
  DESCRIPTION         VARCHAR2(30) NOT NULL,
  HOSTNAME            VARCHAR2(60) NOT NULL,
  CONSOLEPORTNUMBER   NUMBER NOT NULL,
  WEBSRVPORTNUMBER    NUMBER NOT NULL,
  SYSTEMUSER_ID       NUMBER NOT NULL,
  TIMESTAMP           DATE NOT NULL
);

COMMENT ON COLUMN LOCALPRINTSERVER.CONSOLEPORTNUMBER
  IS 'Portnumber for admin console';
COMMENT ON COLUMN LOCALPRINTSERVER.WEBSRVPORTNUMBER
  IS 'Portnumber for published webservices';

ALTER TABLE LOCALPRINTSERVER
  ADD CONSTRAINT XPKLOCALPRINTSERVER PRIMARY KEY (LOCALPRINTSERVER_ID);
  
CREATE UNIQUE INDEX XAK1LOCALPRINTSERVER ON LOCALPRINTSERVER (CODE ASC);
CREATE UNIQUE INDEX XAK2LOCALPRINTSERVER ON LOCALPRINTSERVER (HOSTNAME ASC, CONSOLEPORTNUMBER ASC);
CREATE UNIQUE INDEX XAK3LOCALPRINTSERVER ON LOCALPRINTSERVER (HOSTNAME ASC, WEBSRVPORTNUMBER ASC);

CREATE INDEX XIF1LOCALPRINTSERVER ON LOCALPRINTSERVER (PLANT_ID ASC);

ALTER TABLE LOCALPRINTSERVER
  ADD CONSTRAINT LOCALPRINTSERVER_PLANT FOREIGN KEY (PLANT_ID)
  REFERENCES PLANT (PLANT_ID) ON DELETE CASCADE;

-- WORKSTATION table --
ALTER TABLE WORKSTATION ADD (LOCALPRINTSERVER_ID NUMBER NULL);

CREATE INDEX XIF13WORKSTATION ON WORKSTATION (LOCALPRINTSERVER_ID ASC);

ALTER TABLE WORKSTATION
  ADD CONSTRAINT WORKSTATION_LOCALPRINTSERVER FOREIGN KEY (LOCALPRINTSERVER_ID)
  REFERENCES LOCALPRINTSERVER (LOCALPRINTSERVER_ID) ON DELETE SET NULL;
