-- ABS-67690/ ABS-71344

-- STERILIZATIONRUNPERCRB TABLE --
CREATE TABLE STERILIZATIONRUNPERCRB
(
  STERILIZATIONRUN_ID NUMBER NOT NULL,
  CLEANROOMBATCH_ID   NUMBER NOT NULL,
  SYSTEMUSER_ID       NUMBER NOT NULL,
  TIMESTAMP           DATE NOT NULL
)
;

ALTER TABLE STERILIZATIONRUNPERCRB
  ADD CONSTRAINT XPKSTERILIZATIONRUNPERCRB PRIMARY KEY (STERILIZATIONRUN_ID, CLEANROOMBATCH_ID);
  
CREATE INDEX XIF1STERILIZATIONRUNPERCRB ON STERILIZATIONRUNPERCRB (STERILIZATIONRUN_ID ASC);
CREATE INDEX XIF2STERILIZATIONRUNPERCRB ON STERILIZATIONRUNPERCRB (CLEANROOMBATCH_ID ASC);

ALTER TABLE STERILIZATIONRUNPERCRB
  ADD CONSTRAINT STERILIZATIONRUNPERCRB_SR FOREIGN KEY (STERILIZATIONRUN_ID)
  REFERENCES STERILIZATIONRUN (STERILIZATIONRUN_ID) ON DELETE CASCADE;
  
ALTER TABLE STERILIZATIONRUNPERCRB
  ADD CONSTRAINT STERILIZATIONRUNPERCRB_CB FOREIGN KEY (CLEANROOMBATCH_ID)
  REFERENCES CLEANROOMBATCH (CLEANROOMBATCH_ID) ON DELETE CASCADE;

INSERT INTO STERILIZATIONRUNPERCRB(STERILIZATIONRUN_ID, CLEANROOMBATCH_ID, SYSTEMUSER_ID, TIMESTAMP)
SELECT SR.STERILIZATIONRUN_ID,
       SR.CLEANROOMBATCH_ID,
       SR.SYSTEMUSER_ID,
       SR.TIMESTAMP
FROM STERILIZATIONRUN SR
WHERE SR.CLEANROOMBATCH_ID IS NOT NULL;
COMMIT;

-- STERILIZATIONRUN table --
ALTER TABLE STERILIZATIONRUN RENAME COLUMN CLEANROOMBATCH_ID TO OLDCLEANROOMBATCHID;

DROP INDEX XIF9STERILIZATIONRUN;

ALTER TABLE STERILIZATIONRUN
  DROP CONSTRAINT STERILIZATIONRUN_CRBATCH;
