--SO 20012621

--------------------WORKSTATION-----------------------
ALTER TABLE WORKSTATION  ADD (IMAGESIZE NUMBER DEFAULT 0 NOT NULL CONSTRAINT IMAGEZISE_valid CHECK (IMAGESIZE IN (0, 1, 2, 3, 4, 5)), 
                                                               WEBCAMDRIVERNAME VARCHAR2(20) NULL,
                                                               WEBCAMDEVICENAME VARCHAR2(20) NULL);
            
COMMENT ON COLUMN WORKSTATION.IMAGESIZE IS '0 = No Web Cam Images taken on the workstation
1 = 320x240
2 = 640x480
3 = 800x600
4 = 1024x768
5 = 1366x768
';              

------------------UNIQUEITEMIMAGE---------------------
CREATE SEQUENCE SEQ_UNIQUEITEMIMAGE
START WITH 1
INCREMENT BY 1;

CREATE TABLE UNIQUEITEMIMAGE
   (UNIQUEITEMIMAGE_ID NUMBER NOT NULL,
    UNIQUEITEM_LINK_ID NUMBER NOT NULL,
    IMAGE_ID NUMBER NOT NULL,
    SCAN_ID NUMBER NULL,
    SYSTEMUSER_ID    NUMBER NOT NULL,
    TIMESTAMP        DATE NOT NULL);

ALTER TABLE UNIQUEITEMIMAGE
ADD CONSTRAINT  XPKUNIQUEITEMIMAGE PRIMARY KEY (UNIQUEITEMIMAGE_ID); 

ALTER TABLE UNIQUEITEMIMAGE
  ADD CONSTRAINT UIIMAGE_UI_LINK FOREIGN KEY (UNIQUEITEM_LINK_ID)
  REFERENCES UNIQUEITEM_LINK(UNIQUEITEM_LINK_ID) ON DELETE CASCADE;

CREATE INDEX XIF1UNIQUEITEMIMAGE  ON UNIQUEITEMIMAGE
(UNIQUEITEM_LINK_ID  ASC);

 ALTER TABLE UNIQUEITEMIMAGE
  ADD CONSTRAINT UIIMAGE_IMAGE FOREIGN KEY (IMAGE_ID)
  REFERENCES IMAGE(IMAGE_ID) ON DELETE CASCADE;

CREATE INDEX XIF2UNIQUEITEMIMAGE  ON UNIQUEITEMIMAGE
(IMAGE_ID  ASC);

ALTER TABLE UNIQUEITEMIMAGE
  ADD CONSTRAINT UIIMAGE_SCAN FOREIGN KEY (SCAN_ID)
  REFERENCES SCAN(SCAN_ID) ON DELETE SET NULL;

CREATE INDEX XIF3UNIQUEITEMIMAGE  ON UNIQUEITEMIMAGE
(SCAN_ID  ASC);

CREATE UNIQUE INDEX XAK1UNIQUEITEMIMAGE ON UNIQUEITEMIMAGE
(UNIQUEITEM_LINK_ID   ASC, IMAGE_ID ASC);


--------------------------------IMAGE------------------
ALTER TABLE IMAGE  ADD (CAPTURETIMESTAMP DATE NULL, 
                                   FREETEXT CLOB NULL);

ALTER TABLE IMAGE ADD CONSTRAINT IMAGETYPE_valid CHECK (IMAGETYPE IN (1, 2, 3, 4));
            
COMMENT ON COLUMN IMAGE.IMAGETYPE IS '1 = Product
2 = Placement
3 = Garment
4 = Unique Residential Item
';  

-------------------------TRANSACTIONTYPE-------------------

insert into transactiontype
  (transactiontype_id, factorydescription, systemuser_id, timestamp)
  select 17,
         'Image Capture',
         getminsystemuser(),
         sysdate
    from dual;
commit;

insert into transactiontype_desc
  (transactiontype_id, language_id, description, systemuser_id, timestamp)
  select transactiontype_id,
         getminlanguage(),
         factorydescription,
         systemuser_id,
         timestamp
    from transactiontype
   where transactiontype.transactiontype_id = 17 ;
commit;
            
----------------------------UNIQUERESIDITEMIMAGE--------------

CREATE SEQUENCE SEQ_UNIQUERESIDITEMIMAGE
START WITH 1
INCREMENT BY 1;

CREATE TABLE UNIQUERESIDITEMIMAGE
   (UNIQUERESIDITEMIMAGE_ID NUMBER NOT NULL,
    UNIQUERESIDENTIALITEM_LINK_ID NUMBER NOT NULL,
    IMAGE_ID NUMBER NOT NULL,
    RESIDENTIALITEMSCAN_ID NUMBER NULL,
    SYSTEMUSER_ID    NUMBER NOT NULL,
    TIMESTAMP        DATE NOT NULL);

ALTER TABLE UNIQUERESIDITEMIMAGE
ADD CONSTRAINT  XPKUNIQUERESIDITEMIMAGE PRIMARY KEY (UNIQUERESIDITEMIMAGE_ID); 

ALTER TABLE UNIQUERESIDITEMIMAGE
  ADD CONSTRAINT UIRESIDITEMIMG_UIRESLINK FOREIGN KEY 

(UNIQUERESIDENTIALITEM_LINK_ID)
  REFERENCES UNIQUERESIDENTIALITEM_LINK(UNIQUERESIDENTIALITEM_LINK_ID) ON DELETE 

CASCADE;

CREATE INDEX XIF1UNIQUERESIDITEMIMAGE ON UNIQUERESIDITEMIMAGE
(UNIQUERESIDENTIALITEM_LINK_ID  ASC);

 ALTER TABLE UNIQUERESIDITEMIMAGE
  ADD CONSTRAINT UIRESIDITEMIMG_IMG FOREIGN KEY (IMAGE_ID)
  REFERENCES IMAGE(IMAGE_ID) ON DELETE CASCADE;

CREATE INDEX XIF2UNIQUERESIDITEMIMAGE  ON UNIQUERESIDITEMIMAGE
(IMAGE_ID  ASC);

ALTER TABLE UNIQUERESIDITEMIMAGE
  ADD CONSTRAINT UIRESIDITEMIMG_RESIDSCAN FOREIGN KEY (RESIDENTIALITEMSCAN_ID)
  REFERENCES RESIDENTIALITEMSCAN(RESIDENTIALITEMSCAN_ID) ON DELETE SET NULL;

CREATE INDEX XIF3UNIQUERESIDITEMIMAGE  ON UNIQUERESIDITEMIMAGE
(RESIDENTIALITEMSCAN_ID  ASC);

CREATE UNIQUE INDEX XAK1UNIQUERESIDITEMIMAGE ON UNIQUERESIDITEMIMAGE
(UNIQUERESIDENTIALITEM_LINK_ID   ASC, IMAGE_ID ASC);
