-- ABS-35939

----------------------- SUBCONTRACTOR -----------------------
CREATE SEQUENCE SEQ_SUBCONTRACTOR
START WITH 1
INCREMENT BY 1;

CREATE TABLE SUBCONTRACTOR
( SUBCONTRACTOR_ID NUMBER NOT NULL,
  NAME             VARCHAR2(40) NOT NULL,
  FAX              VARCHAR2(30),
  PHONE            VARCHAR2(30),
  EMAIL            VARCHAR2(300),
  WEBSITE          VARCHAR2(40),
  ADDRESS_ID       NUMBER NOT NULL,
  DATEACTIVE       DATE NOT NULL,
  DATEINACTIVE     DATE,
  SYSTEMUSER_ID    NUMBER NOT NULL,
  TIMESTAMP        DATE NOT NULL);

ALTER TABLE SUBCONTRACTOR
  ADD CONSTRAINT XPKSUBCONTRACTOR PRIMARY KEY (SUBCONTRACTOR_ID);
  
CREATE UNIQUE INDEX XAK1SUBCONTRACTOR ON SUBCONTRACTOR (NAME ASC);

CREATE INDEX XIF1SUBCONTRACTOR ON SUBCONTRACTOR (ADDRESS_ID ASC);

ALTER TABLE SUBCONTRACTOR
  ADD CONSTRAINT SUBCONTRACTOR_ADDRESS FOREIGN KEY (ADDRESS_ID)  
  REFERENCES ADDRESS (ADDRESS_ID);


----------------------- TREATMENTSPERSUBCONTRACTOR -----------------------
CREATE SEQUENCE SEQ_TREATMENTSPERSUBCONTRACTOR
START WITH 1
INCREMENT BY 1;

CREATE TABLE TREATMENTSPERSUBCONTRACTOR
( TREATMENTSPERSUBCONTRACTOR_ID NUMBER NOT NULL,
  PLANT_ID                     NUMBER NOT NULL,
  SUBCONTRACTOR_ID             NUMBER NOT NULL,
  TREATMENTTYPE_ID             NUMBER NOT NULL,
  SYSTEMUSER_ID                NUMBER NOT NULL,
  TIMESTAMP                    DATE NOT NULL);

ALTER TABLE TREATMENTSPERSUBCONTRACTOR
  ADD CONSTRAINT XPKTREATMENTSPERSUBCONTRACTOR PRIMARY KEY (TREATMENTSPERSUBCONTRACTOR_ID);

CREATE UNIQUE INDEX XAK1TREATMENTSPERSUBCONTRACTOR ON TREATMENTSPERSUBCONTRACTOR (PLANT_ID ASC, SUBCONTRACTOR_ID ASC, TREATMENTTYPE_ID ASC);
  
CREATE INDEX XIF1TREATMENTSPERSUBCONTRACTOR ON TREATMENTSPERSUBCONTRACTOR (PLANT_ID ASC);
CREATE INDEX XIF2TREATMENTSPERSUBCONTRACTOR ON TREATMENTSPERSUBCONTRACTOR (SUBCONTRACTOR_ID ASC);
CREATE INDEX XIF3TREATMENTSPERSUBCONTRACTOR ON TREATMENTSPERSUBCONTRACTOR (TREATMENTTYPE_ID ASC);

ALTER TABLE TREATMENTSPERSUBCONTRACTOR
  ADD CONSTRAINT TREATMENTSPERSUBCONTR_PLANT FOREIGN KEY (PLANT_ID)
  REFERENCES PLANT (PLANT_ID);
ALTER TABLE TREATMENTSPERSUBCONTRACTOR
  ADD CONSTRAINT TREATMENTSPERSUBCONTR_SUBCONTR FOREIGN KEY (SUBCONTRACTOR_ID)
  REFERENCES SUBCONTRACTOR (SUBCONTRACTOR_ID);
ALTER TABLE TREATMENTSPERSUBCONTRACTOR
  ADD CONSTRAINT TREATMENTSPERSUBCONTR_TREAT FOREIGN KEY (TREATMENTTYPE_ID)
  REFERENCES TREATMENTTYPE (TREATMENTTYPE_ID);
  
  
----------------------- TREATMENTTYPE -----------------------
ALTER TABLE TREATMENTTYPE ADD (EXTERNAL VARCHAR2(1) DEFAULT 'N' 
                          CONSTRAINT YN_VALIDATIONFIELD314 CHECK (EXTERNAL IN ('Y', 'N')));
                          

----------------------- PRODUCTLISTPERCUSTOMER -----------------------
ALTER TABLE PRODUCTLISTPERCUSTOMER ADD (STERILISATIONREQUIRED VARCHAR2(1) DEFAULT 'N'  NOT NULL CONSTRAINT YN_VALIDATIONFIELD315
                                                                              CHECK (STERILISATIONREQUIRED IN ('Y', 'N')),
                                        TREATMENTSPERSUBCONTRACTOR_ID NUMBER);
  
CREATE INDEX XIF27PRODUCTLISTPERCUSTOMER ON PRODUCTLISTPERCUSTOMER (TREATMENTSPERSUBCONTRACTOR_ID ASC);

ALTER TABLE PRODUCTLISTPERCUSTOMER
  ADD CONSTRAINT PLPC_TREATMENTPERSUBCONTRACTOR FOREIGN KEY (TREATMENTSPERSUBCONTRACTOR_ID)
  REFERENCES TREATMENTSPERSUBCONTRACTOR (TREATMENTSPERSUBCONTRACTOR_ID);
