-- ABS-151605 Store images in multiple formats in the database

CREATE TABLE IMAGEPURPOSE
(
  IMAGEPURPOSE_ID  NUMBER NOT NULL,
  IMAGETYPE        NUMBER NOT NULL CONSTRAINT CHK_IMAGEPURPOSE_IMAGETYPE CHECK (IMAGETYPE BETWEEN 1 AND 7),
  PURPOSE          VARCHAR2(30) NOT NULL,
  REMARKS          VARCHAR2(80) NOT NULL,
  INUSE            VARCHAR2(1) NOT NULL CONSTRAINT CHK_IMAGEPUROSE_INUSE CHECK (INUSE IN ('Y','N')),
  SYSTEMUSER_ID    NUMBER NOT NULL,
  TIMESTAMP        DATE NOT NULL
);
  
COMMENT ON COLUMN IMAGEPURPOSE.IMAGETYPE
IS  '1 = Product
2 = Placement
3 = Garment
4 = Unique Residential Item
5 = Company Stamp
6 = RepairType
7 = Cleanroom Pack Instructions
';

COMMENT ON COLUMN IMAGEPURPOSE.REMARKS
IS  'Specifies format and size';

ALTER TABLE IMAGEPURPOSE
ADD CONSTRAINT XPKIMAGEPURPOSE PRIMARY KEY (IMAGEPURPOSE_ID);

-- Create table COMPRESSEDIMAGE
CREATE SEQUENCE SEQ_COMPRESSEDIMAGE
START WITH 1
INCREMENT BY 1;

CREATE TABLE COMPRESSEDIMAGE
(
  COMPRESSEDIMAGE_ID NUMBER NOT NULL,
  IMAGE_ID           NUMBER NOT NULL,
  IMAGEPURPOSE_ID    NUMBER NOT NULL,
  COMPRESSEDIMAGE    BLOB NULL,
  SYSTEMUSER_ID      NUMBER NOT NULL,
  TIMESTAMP          DATE NOT NULL
);

ALTER TABLE COMPRESSEDIMAGE
  ADD CONSTRAINT XPKCOMPRESSEDIMAGE PRIMARY KEY (COMPRESSEDIMAGE_ID);

-- CREATE/RECREATE INDEXES 
CREATE UNIQUE INDEX XIF1COMPRESSEDIMAGE ON COMPRESSEDIMAGE (IMAGE_ID,IMAGEPURPOSE_ID);
  
ALTER TABLE COMPRESSEDIMAGE
  ADD CONSTRAINT FK_COMPRESSEDIMAGE_IMAGE_ID FOREIGN KEY (IMAGE_ID)
  REFERENCES IMAGE (IMAGE_ID) ON DELETE CASCADE;
  
ALTER TABLE COMPRESSEDIMAGE
  ADD CONSTRAINT FK_COMPRESSEDIMAGE_IMAGEPURPOSE_ID FOREIGN KEY (IMAGEPURPOSE_ID)
  REFERENCES IMAGEPURPOSE (IMAGEPURPOSE_ID);
