-- ABS-57449/ ABS-70449 (part 1)

-- FLAG table --
ALTER TABLE FLAG ADD (REPAIR VARCHAR2(1) DEFAULT 'N' NOT NULL
                      CONSTRAINT YN_VALIDATIONFIELD442 CHECK (REPAIR IN ('Y', 'N')));

COMMENT ON COLUMN FLAG.REPAIR
  IS 'Only applicable for Flag.Type = 1 (Garment / Wearers)';

-- STAY table --
ALTER TABLE STAY ADD (LOCATION NUMBER NULL
                               CONSTRAINT LOCATION_VALID1 CHECK (LOCATION BETWEEN 0 AND 4));

COMMENT ON COLUMN STAY.LOCATION
  IS '0 = Not applicable
1 = Laundry
2 = Customer
3 = Repair
4 = Transport
Only used for garment stays';

-- STAY table --
UPDATE STAY ST
SET ST.LOCATION = 0
WHERE ST.STAYTYPE IN (2, 3, 4, 5, 6);
COMMIT;

UPDATE STAY ST
SET ST.LOCATION = 1
WHERE ST.STAYTYPE = 1
AND ST.STAYNR IN (10, 11, 30, 31, 33, 34, 35, 36, 37, 38, 39, 41);
COMMIT;

UPDATE STAY ST
SET ST.LOCATION = 2
WHERE ST.STAYTYPE = 1
AND ST.STAYNR IN (20, 22, 23, 24, 25, 26, 27, 40, 42);
COMMIT;

UPDATE STAY ST
SET ST.LOCATION = 3
WHERE ST.STAYTYPE = 1
AND ST.STAYNR = 32;
COMMIT;

UPDATE STAY ST
SET ST.LOCATION = 4
WHERE ST.STAYTYPE = 1
AND ST.STAYNR IN (28, 29);
COMMIT;

-- PLANTIMAGE TABLE --
CREATE TABLE PLANTIMAGE
(
  PLANT_ID      NUMBER NOT NULL,
  IMAGE         BLOB NOT NULL,
  SYSTEMUSER_ID NUMBER NOT NULL,
  TIMESTAMP     DATE NOT NULL
)
;

ALTER TABLE PLANTIMAGE
  ADD CONSTRAINT XPKPLANTIMAGE PRIMARY KEY (PLANT_ID);
  
ALTER TABLE PLANTIMAGE
  ADD CONSTRAINT PLANTIMAGE_PLANT FOREIGN KEY (PLANT_ID)
  REFERENCES PLANT (PLANT_ID) ON DELETE CASCADE;

-- PRODUCTIMAGE table --
CREATE TABLE PRODUCTIMAGE
(
  PRODUCT_ID    NUMBER NOT NULL,
  IMAGE         BLOB NOT NULL,
  SYSTEMUSER_ID NUMBER NOT NULL,
  TIMESTAMP     DATE NOT NULL
)
;

ALTER TABLE PRODUCTIMAGE
  ADD CONSTRAINT XPKPRODUCTIMAGE PRIMARY KEY (PRODUCT_ID);
  
ALTER TABLE PRODUCTIMAGE
  ADD CONSTRAINT PRODUCTIMAGE_PR FOREIGN KEY (PRODUCT_ID)
  REFERENCES PRODUCT (PRODUCT_ID) ON DELETE CASCADE;