--ABS-85031 / ABS-120789
-- CONTAINERREPAIR --
CREATE SEQUENCE SEQ_CONTAINERREPAIR
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTAINERREPAIR
(
  CONTAINERREPAIR_ID         NUMBER NOT NULL,
  REPAIRTYPE_ID              NUMBER NOT NULL,
  BUSINESSUNIT_ID            NUMBER NOT NULL,
  STARTTIMESTAMP             DATE NOT NULL,
  DURATION                   NUMBER DEFAULT 1 NOT NULL CONSTRAINT CHK_CONTAINERREPAIR_DURATION 
                             CHECK (DURATION BETWEEN 1 AND 999),
  QUANTITY                   NUMBER DEFAULT 1 NOT NULL CONSTRAINT CHK_CONTAINERREPAIR_QUANTITY 
                             CHECK (QUANTITY BETWEEN 1 AND 999),
  CREATIONUSER_ID            NUMBER NOT NULL,
  CONTAINER_ID               NUMBER NOT NULL,
  SYSTEMUSER_ID              NUMBER NOT NULL,
  TIMESTAMP                  DATE NOT NULL
);

COMMENT ON COLUMN CONTAINERREPAIR.DURATION IS
'1-999. Duration in minutes';
COMMENT ON COLUMN CONTAINERREPAIR.CREATIONUSER_ID IS
'ID from the systemuser that created the repair registration (i.e. the user that did the repair)';

ALTER TABLE CONTAINERREPAIR
  ADD CONSTRAINT XPKCONTAINERREPAIR PRIMARY KEY (CONTAINERREPAIR_ID);

CREATE UNIQUE INDEX XAK1CONTAINERREPAIR ON CONTAINERREPAIR (CONTAINER_ID ASC, REPAIRTYPE_ID ASC, STARTTIMESTAMP ASC);
CREATE INDEX XIE1CONTAINERREPAIR ON CONTAINERREPAIR (CONTAINER_ID ASC, STARTTIMESTAMP ASC); 
CREATE INDEX XIF1CONTAINERREPAIR ON CONTAINERREPAIR (REPAIRTYPE_ID ASC);
CREATE INDEX XIF2CONTAINERREPAIR ON CONTAINERREPAIR (BUSINESSUNIT_ID ASC);
CREATE INDEX XIF3CONTAINERREPAIR ON CONTAINERREPAIR (CREATIONUSER_ID ASC);
CREATE INDEX XIF4CONTAINERREPAIR ON CONTAINERREPAIR (CONTAINER_ID ASC);

ALTER TABLE CONTAINERREPAIR
  ADD CONSTRAINT FK_CONTAINERREPAIR_REPTYPE FOREIGN KEY (REPAIRTYPE_ID)
  REFERENCES REPAIRTYPE (REPAIRTYPE_ID);
ALTER TABLE CONTAINERREPAIR
  ADD CONSTRAINT FK_CONTAINERREPAIR_BU FOREIGN KEY (BUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);
ALTER TABLE CONTAINERREPAIR
  ADD CONSTRAINT FK_CONTAINERREPAIR_CREATUSER FOREIGN KEY (CREATIONUSER_ID)
  REFERENCES SYSTEMUSER (SYSTEMUSER_ID);
ALTER TABLE CONTAINERREPAIR
  ADD CONSTRAINT FK_CONTAINERREPAIR_CONTAINER FOREIGN KEY (CONTAINER_ID)
  REFERENCES CONTAINER (CONTAINER_ID) ON DELETE CASCADE;


-- RESIDENTIALREPAIR --
CREATE SEQUENCE SEQ_RESIDENTIALREPAIR
START WITH 1
INCREMENT BY 1;

CREATE TABLE RESIDENTIALREPAIR
(
  RESIDENTIALREPAIR_ID         NUMBER NOT NULL,
  REPAIRTYPE_ID                NUMBER NOT NULL,
  BUSINESSUNIT_ID              NUMBER NOT NULL,
  STARTTIMESTAMP               DATE NOT NULL,
  DURATION                     NUMBER DEFAULT 1 NOT NULL CONSTRAINT CHK_RESIDENTIALREPAIR_DURATION 
                               CHECK (DURATION BETWEEN 1 AND 999),
  QUANTITY                     NUMBER DEFAULT 1 NOT NULL CONSTRAINT CHK_RESIDENTIALREPAIR_QUANTITY 
                               CHECK (QUANTITY BETWEEN 1 AND 999),
  PROCESSED_YN                 VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_RESIDENTIALREPAIR_PROCESSED
                               CHECK (PROCESSED_YN IN ('Y', 'N')),
  CREATIONUSER_ID              NUMBER NOT NULL,
  STATUS                       NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_RESIDENTIALREPAIR_STATUS
                               CHECK (STATUS IN (0,1,2)),
  INVOICEHEADER_ID             NUMBER NULL,
  AMOUNT                       NUMBER NULL,
  UNIQUERESIDENTIALITEM_LINK_ID NUMBER NOT NULL,
  CLIENTDELIVERYNOTE_ID        NUMBER NULL,
  SPECIFICUNITPRICE            NUMBER NULL,
  SYSTEMUSER_ID                NUMBER NOT NULL,
  TIMESTAMP                    DATE NOT NULL
);

COMMENT ON COLUMN RESIDENTIALREPAIR.DURATION
IS '1 – 999 Duration in minutes';
COMMENT ON COLUMN RESIDENTIALREPAIR.PROCESSED_YN
IS 'To avoid that old repairs are connected to a CDN once more when client delivery notes are being deleted, an extra flag is required';
COMMENT ON COLUMN RESIDENTIALREPAIR.CREATIONUSER_ID
IS 'ID from the systemuser that created the repair registration (i.e. the user that did the repair)';
COMMENT ON COLUMN RESIDENTIALREPAIR.STATUS
IS '0 = (To be) Charged
1 = Free of Charge
2 = Charge cancelled
Default 0';
COMMENT ON COLUMN RESIDENTIALREPAIR.AMOUNT
IS 'Calculated invoice amount for this repair (filled after invoicing)';
COMMENT ON COLUMN RESIDENTIALREPAIR.SPECIFICUNITPRICE
IS 'Manual Price entry';

ALTER TABLE RESIDENTIALREPAIR
  ADD CONSTRAINT XPKRESIDENTIALREPAIR PRIMARY KEY (RESIDENTIALREPAIR_ID);

CREATE UNIQUE INDEX XAK1RESIDENTIALREPAIR ON RESIDENTIALREPAIR (UNIQUERESIDENTIALITEM_LINK_ID ASC, REPAIRTYPE_ID ASC, STARTTIMESTAMP ASC);
CREATE INDEX XIE1RESIDENTIALREPAIR ON RESIDENTIALREPAIR(BUSINESSUNIT_ID ASC, PROCESSED_YN ASC);
CREATE INDEX XIF1RESIDENTIALREPAIR ON RESIDENTIALREPAIR (REPAIRTYPE_ID ASC);
CREATE INDEX XIF2RESIDENTIALREPAIR ON RESIDENTIALREPAIR (BUSINESSUNIT_ID ASC);
CREATE INDEX XIF3RESIDENTIALREPAIR ON RESIDENTIALREPAIR (CREATIONUSER_ID ASC);
CREATE INDEX XIF4RESIDENTIALREPAIR ON RESIDENTIALREPAIR (INVOICEHEADER_ID ASC);
CREATE INDEX XIF5RESIDENTIALREPAIR ON RESIDENTIALREPAIR (UNIQUERESIDENTIALITEM_LINK_ID ASC);
CREATE INDEX XIF6RESIDENTIALREPAIR ON RESIDENTIALREPAIR (CLIENTDELIVERYNOTE_ID ASC);

ALTER TABLE RESIDENTIALREPAIR
  ADD CONSTRAINT FK_RESIDENTIALREPAIR_REPTYPE FOREIGN KEY (REPAIRTYPE_ID)
  REFERENCES REPAIRTYPE (REPAIRTYPE_ID);
ALTER TABLE RESIDENTIALREPAIR
  ADD CONSTRAINT FK_RESIDENTIALREPAIR_BU FOREIGN KEY (BUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);
ALTER TABLE RESIDENTIALREPAIR
  ADD CONSTRAINT FK_RESIDENTIALREPAIR_CRUSER FOREIGN KEY (CREATIONUSER_ID)
  REFERENCES SYSTEMUSER (SYSTEMUSER_ID);
ALTER TABLE RESIDENTIALREPAIR
  ADD CONSTRAINT FK_RESIDENTIALREPAIR_INVHEADER FOREIGN KEY (INVOICEHEADER_ID)
  REFERENCES INVOICEHEADER (INVOICEHEADER_ID) ON DELETE SET NULL;
ALTER TABLE RESIDENTIALREPAIR
  ADD CONSTRAINT FK_RESIDENTIALREPAIR_URESITEM FOREIGN KEY (UNIQUERESIDENTIALITEM_LINK_ID)
  REFERENCES UNIQUERESIDENTIALITEM_LINK (UNIQUERESIDENTIALITEM_LINK_ID) ON DELETE CASCADE;
ALTER TABLE RESIDENTIALREPAIR
  ADD CONSTRAINT FK_RESIDENTIALREPAIR_CDELNOTE FOREIGN KEY (CLIENTDELIVERYNOTE_ID)
  REFERENCES CLIENTDELIVERYNOTE (CLIENTDELIVERYNOTE_ID) ON DELETE SET NULL;
  
---- REPAIRTYPE ----

ALTER TABLE REPAIRTYPE ADD (REPAIRUSAGE NUMBER DEFAULT 0 NOT NULL
                            CONSTRAINT CHK_REPAIRTYPE_REPAIRUSAGE CHECK (REPAIRUSAGE IN (0,1,2,3)),
                            ALLOWMANUALPRICE NUMBER DEFAULT 0 NOT NULL
                            CONSTRAINT CHK_REPAIRTYPE_ALLOWMANUALP CHECK (ALLOWMANUALPRICE IN (0,1)),
                            IMAGE_ID NUMBER NULL,
                            SEQUENCENUMBER NUMBER DEFAULT 0 NOT NULL
                            CONSTRAINT CHK_REPAIRTYPE_SEQUENCE CHECK (SEQUENCENUMBER BETWEEN 0 AND 999));

CREATE INDEX XIE1REPAIRTYPE ON REPAIRTYPE (CATEGORY ASC, SEQUENCENUMBER ASC, CODE ASC);
CREATE INDEX XIF2REPAIRTYPE ON REPAIRTYPE (IMAGE_ID ASC);
ALTER TABLE REPAIRTYPE
  ADD CONSTRAINT FK_REPAIRTYPE_IMAGE FOREIGN KEY (IMAGE_ID)
  REFERENCES IMAGE (IMAGE_ID) ON DELETE SET NULL;
  
COMMENT ON COLUMN REPAIRTYPE.REPAIRUSAGE
  IS '0 = Both or not applicable
1 = Garments only
2 = Residential only
3 = Container only
Option 1 and 2 and 3 are only allowed for Category = 1 (regular repair)
';    
COMMENT ON COLUMN REPAIRTYPE.ALLOWMANUALPRICE
  IS '0 = Not allowed or not applicable
1 = Allow manual price entry
Option 1 can only be used in combination with residential repairs (i.e. RepairUsage = 0 or RepairUsage = 2)
';
COMMENT ON COLUMN REPAIRTYPE.SEQUENCENUMBER
  IS '0 – 999
Only used for regular repairs (Category = 1)
';  

-- IMAGE -- 
ALTER TABLE IMAGE DROP CONSTRAINT IMAGETYPE_VALID;
ALTER TABLE IMAGE ADD CONSTRAINT CHK_IMAGE_IMAGETYPE CHECK (IMAGETYPE IN (1,2,3,4,5,6));
COMMENT ON COLUMN IMAGE.IMAGETYPE
IS '1 = Product
2 = Placement
3 = Garment
4 = Unique Residential Item
5 = Company Stamp
6 = RepairType
';

-- CLIENTINVOICECATEGORY --
ALTER TABLE CLIENTINVOICECATEGORY ADD (INVOICEREPAIRS NUMBER DEFAULT 0 NOT NULL
                                       CONSTRAINT CHK_CLIENTINVCATEG_INVREPAIRS CHECK (INVOICEREPAIRS IN (0, 1, 2, 3)));

COMMENT ON COLUMN CLIENTINVOICECATEGORY.INVOICEREPAIRS
  IS '0 = To customer
1 = To client
2 = Free of charge
3 = No repair allowed / invoiced';