-- ABS-6175

---CLEANROOMBATCH table---

CREATE SEQUENCE SEQ_CLEANROOMBATCH
START WITH 1
INCREMENT BY 1;

CREATE TABLE CLEANROOMBATCH
(
  CLEANROOMBATCH_ID      NUMBER NOT NULL,
  BUSINESSUNIT_ID        NUMBER NOT NULL,
  PLANTDEPARTMENT_ID     NUMBER NOT NULL,
  CLEANROOMBATCHNUMBER   NUMBER NOT NULL,
  BATCHSTATUS            NUMBER DEFAULT 0 NOT NULL CONSTRAINT BATCHSTATUS_VALID1_1
                         CHECK (BATCHSTATUS BETWEEN 0 AND 2),
  CREATIONDATE           DATE NOT NULL,
  CLOSINGDATE            DATE NULL,
  REMARK                 VARCHAR2(30) NULL,
  TREATMENTTYPE_ID       NUMBER NULL,
  CREATIONWORKSTATION_ID NUMBER NOT NULL,
  SYSTEMUSER_ID          NUMBER NOT NULL,
  TIMESTAMP              DATE NOT NULL
);

COMMENT ON COLUMN CLEANROOMBATCH.BATCHSTATUS
  IS '0 = Open 
1 = Closed 
2 = Rejected
';

ALTER TABLE CLEANROOMBATCH
  ADD CONSTRAINT XPKCLEANROOMBATCH PRIMARY KEY (CLEANROOMBATCH_ID);
  
CREATE UNIQUE INDEX XAK1CLEANROOMBATCH ON CLEANROOMBATCH (CLEANROOMBATCHNUMBER ASC, BUSINESSUNIT_ID ASC);

CREATE INDEX XIF1CLEANROOMBATCH ON CLEANROOMBATCH (BUSINESSUNIT_ID ASC);

CREATE INDEX XIF2CLEANROOMBATCH ON CLEANROOMBATCH (PLANTDEPARTMENT_ID ASC);

CREATE INDEX XIF3CLEANROOMBATCH ON CLEANROOMBATCH (TREATMENTTYPE_ID ASC);

CREATE INDEX XIF4CLEANROOMBATCH ON CLEANROOMBATCH (CREATIONWORKSTATION_ID ASC);
 
ALTER TABLE CLEANROOMBATCH
  ADD CONSTRAINT CLEANROOMBATCH_BU FOREIGN KEY (BUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);

ALTER TABLE CLEANROOMBATCH
  ADD CONSTRAINT CLEANROOMBATCH_PLANTDEP FOREIGN KEY (PLANTDEPARTMENT_ID)
  REFERENCES PLANTDEPARTMENT (PLANTDEPARTMENT_ID);
  
ALTER TABLE CLEANROOMBATCH
  ADD CONSTRAINT CLEANROOMBATCH_TREATMENTTYPE FOREIGN KEY (TREATMENTTYPE_ID)
  REFERENCES TREATMENTTYPE (TREATMENTTYPE_ID) ON DELETE SET NULL;
  
ALTER TABLE CLEANROOMBATCH
  ADD CONSTRAINT CLEANROOMBATCH_CREATIONWS FOREIGN KEY (CREATIONWORKSTATION_ID)
  REFERENCES WORKSTATION (WORKSTATION_ID);

---CLEANROOMBATCHCONTENT table---

CREATE SEQUENCE SEQ_CLEANROOMBATCHCONTENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE CLEANROOMBATCHCONTENT
(
  CLEANROOMBATCHCONTENT_ID NUMBER NOT NULL,
  CLEANROOMBATCH_ID        NUMBER NOT NULL,
  UNIQUEITEM_ID            NUMBER NOT NULL,
  SCANNINGWORKSTATION_ID   NUMBER NOT NULL,
  SYSTEMUSER_ID            NUMBER NOT NULL,
  TIMESTAMP                DATE NOT NULL
);

ALTER TABLE CLEANROOMBATCHCONTENT
  ADD CONSTRAINT XPKCLEANROOMBATCHCONTENT PRIMARY KEY (CLEANROOMBATCHCONTENT_ID);

CREATE UNIQUE INDEX XAK1CLEANROOMBATCHCONTENT ON CLEANROOMBATCHCONTENT (UNIQUEITEM_ID ASC, CLEANROOMBATCH_ID ASC);

CREATE INDEX XIF1CLEANROOMBATCHCONTENT ON CLEANROOMBATCHCONTENT (CLEANROOMBATCH_ID ASC);

CREATE INDEX XIF2CLEANROOMBATCHCONTENT ON CLEANROOMBATCHCONTENT (UNIQUEITEM_ID ASC);

CREATE INDEX XIF3CLEANROOMBATCHCONTENT ON CLEANROOMBATCHCONTENT (SCANNINGWORKSTATION_ID ASC);

ALTER TABLE CLEANROOMBATCHCONTENT
  ADD CONSTRAINT CLEANROOMBATCHCONTENT_CRB FOREIGN KEY (CLEANROOMBATCH_ID)
  REFERENCES CLEANROOMBATCH (CLEANROOMBATCH_ID) ON DELETE CASCADE;

ALTER TABLE CLEANROOMBATCHCONTENT
  ADD CONSTRAINT CLEANROOMBATCHCONTENT_UI FOREIGN KEY (UNIQUEITEM_ID)
  REFERENCES UNIQUEITEM (UNIQUEITEM_ID);

ALTER TABLE CLEANROOMBATCHCONTENT
  ADD CONSTRAINT CLEANROOMBATCHCONTENT_SCANWS FOREIGN KEY (SCANNINGWORKSTATION_ID)
  REFERENCES WORKSTATION (WORKSTATION_ID);
