---ABS-147268

---WEARERFUNCTIONSUBTYPE TABLE
CREATE SEQUENCE SEQ_WEARERFUNCTIONSUBTYPE
START WITH 1
INCREMENT BY 1;

CREATE TABLE WEARERFUNCTIONSUBTYPE (
WEARERFUNCTIONSUBTYPE_ID           NUMBER NOT NULL,
WEARERFUNCTION_ID                  NUMBER NOT NULL,
CODE                               VARCHAR2(12) NOT NULL, 
DESCRIPTION                        VARCHAR2(30) NOT NULL,
SYSTEMUSER_ID                      NUMBER NOT NULL,
TIMESTAMP                          DATE NOT NULL);


ALTER TABLE WEARERFUNCTIONSUBTYPE
      ADD CONSTRAINT XPKWEARERFUNCTIONSUBTYPE PRIMARY KEY (WEARERFUNCTIONSUBTYPE_ID);

CREATE UNIQUE INDEX XAK1WEARERFUNCTIONSUBTYPE ON WEARERFUNCTIONSUBTYPE (WEARERFUNCTION_ID ASC, CODE ASC);

CREATE INDEX XIF1WEARERFUNCTIONSUBTYPE ON WEARERFUNCTIONSUBTYPE (WEARERFUNCTION_ID ASC);

ALTER TABLE WEARERFUNCTIONSUBTYPE
      ADD CONSTRAINT FK_WEARERFUNCSUBTYPE_WEARERFUNC_ID FOREIGN KEY (WEARERFUNCTION_ID)
      REFERENCES WEARERFUNCTION (WEARERFUNCTION_ID) ON DELETE CASCADE; 

---WEARER TABLE
ALTER TABLE WEAREREMPLOYMENT ADD (WEARERFUNCTIONSUBTYPE_ID NUMBER NULL);

ALTER TABLE WEAREREMPLOYMENT ADD CONSTRAINT 
CHK_WEAREREMPLOYMENT_WEARERFUNCSUBTYPE_ID CHECK ((WEARERFUNCTION_ID IS NOT NULL)  
                                          OR (WEARERFUNCTION_ID IS NULL AND WEARERFUNCTIONSUBTYPE_ID IS NULL));

CREATE INDEX XIF716WEAREREMPLOYMENT ON WEAREREMPLOYMENT (WEARERFUNCTIONSUBTYPE_ID);

ALTER TABLE WEAREREMPLOYMENT
      ADD CONSTRAINT FK_WEAREREMPLOYMENT_WEARERFUNC_ID FOREIGN KEY (WEARERFUNCTIONSUBTYPE_ID)
      REFERENCES WEARERFUNCTIONSUBTYPE (WEARERFUNCTIONSUBTYPE_ID);
      
      
---EMBLEMTEMPLATE TABLE
ALTER TABLE EMBLEMTEMPLATE ADD (FUNCTIONRELATEDTEMPLATE VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_EMBLEMTEMPLATE_FUNCTIONRELATEDTEMPLATE CHECK (FUNCTIONRELATEDTEMPLATE IN ('Y', 'N')));



---EMBLEMTEMPLATELINE TABLE
ALTER TABLE EMBLEMTEMPLATELINE ADD (WEARERFUNCTIONSUBTYPE_ID NUMBER NULL);

CREATE INDEX XIF12EMBLEMTEMPLATELINE on EMBLEMTEMPLATELINE (WEARERFUNCTIONSUBTYPE_ID);

ALTER TABLE EMBLEMTEMPLATELINE
      ADD CONSTRAINT FK_EMBLEMTEMPLATELINE_WEARERFUNCSUBTYPE_ID FOREIGN KEY (WEARERFUNCTIONSUBTYPE_ID)
      REFERENCES WEARERFUNCTIONSUBTYPE (WEARERFUNCTIONSUBTYPE_ID) ON DELETE CASCADE;
      
DROP INDEX XAK1EMBLEMTEMPLATELINE;

CREATE UNIQUE INDEX  XAK1EMBLEMTEMPLATELINE on EMBLEMTEMPLATELINE (EMBLEMTEMPLATE_ID ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, TEXTCOLOR_ID ASC, BORDERCOLOR_ID ASC, BKGCOLOR_ID ASC, FABRIC_ID ASC, PLACEMENT_ID ASC, EMBLEMFONT_ID ASC, EMBLEMTYPE ASC, WEARERFUNCTIONSUBTYPE_ID ASC);

---EMBLEMTEMPLATEFUNCTION TABLE

CREATE SEQUENCE SEQ_EMBLEMTEMPLATEFUNCTION
START WITH 1
INCREMENT BY 1;


CREATE TABLE EMBLEMTEMPLATEFUNCTION (
EMBLEMTEMPLATEFUNCTION_ID           NUMBER NOT NULL,
EMBLEMTEMPLATE_ID                   NUMBER NOT NULL,
WEARERFUNCTION_ID                   NUMBER NOT NULL,
SYSTEMUSER_ID                       NUMBER NOT NULL,
TIMESTAMP                           DATE NOT NULL);

ALTER TABLE EMBLEMTEMPLATEFUNCTION
      ADD CONSTRAINT XPKEMBLEMTEMPLATEFUNCTION PRIMARY KEY (EMBLEMTEMPLATEFUNCTION_ID);
      
CREATE UNIQUE INDEX XAK1EMBLEMTEMPLATEFUNCTION ON EMBLEMTEMPLATEFUNCTION (EMBLEMTEMPLATE_ID ASC, WEARERFUNCTION_ID ASC);

CREATE INDEX XIF1EMBLEMTEMPLATEFUNCTION ON EMBLEMTEMPLATEFUNCTION (EMBLEMTEMPLATE_ID ASC);

CREATE INDEX XIF2EMBLEMTEMPLATEFUNCTION ON EMBLEMTEMPLATEFUNCTION (WEARERFUNCTION_ID ASC);

ALTER TABLE EMBLEMTEMPLATEFUNCTION
      ADD CONSTRAINT FK_EMBTEMPFUNC_EMBLEMTEMPLATE_ID FOREIGN KEY (EMBLEMTEMPLATE_ID)
      REFERENCES EMBLEMTEMPLATE (EMBLEMTEMPLATE_ID) ON DELETE CASCADE;

ALTER TABLE EMBLEMTEMPLATEFUNCTION
      ADD CONSTRAINT FK_EMBTEMPFUNC_WEARERFUNC_ID FOREIGN KEY (WEARERFUNCTION_ID)
      REFERENCES WEARERFUNCTION (WEARERFUNCTION_ID) ON DELETE CASCADE;
            


