--ABSN-4786

--LAUNDRYBAGCATEGORY TABLE
CREATE TABLE LAUNDRYBAGCATEGORY (
LAUNDRYBAGCATEGORY_ID NUMBER NOT NULL,
FACTORYDESCRIPTION    VARCHAR2(100) NOT NULL,
LEGACYCODE            VARCHAR2(12) NULL,
SYSTEMUSER_ID         NUMBER NOT NULL,
TIMESTAMP             DATE NOT NULL
);

COMMENT ON COLUMN LAUNDRYBAGCATEGORY.LAUNDRYBAGCATEGORY_ID IS 'Fixed numbering';

ALTER TABLE LAUNDRYBAGCATEGORY ADD CONSTRAINT XPKLAUNDRYBAGCATEGORY PRIMARY KEY (LAUNDRYBAGCATEGORY_ID);

--LAUNDRYBAGCATEGORY_DESC TABLE
CREATE TABLE LAUNDRYBAGCATEGORY_DESC(
LAUNDRYBAGCATEGORY_ID NUMBER NOT NULL,
LANGUAGE_ID           NUMBER NOT NULL,
DESCRIPTION           VARCHAR2(30) NOT NULL,
SYSTEMUSER_ID         NUMBER NOT NULL,
TIMESTAMP             DATE NOT NULL
);

ALTER TABLE LAUNDRYBAGCATEGORY_DESC ADD CONSTRAINT XPKLAUNDRYBAGCATEGORY_DESC PRIMARY KEY (LAUNDRYBAGCATEGORY_ID, LANGUAGE_ID);

CREATE INDEX XIF1LAUNDRYBAGCATEGORY_DESC ON LAUNDRYBAGCATEGORY_DESC (LAUNDRYBAGCATEGORY_ID ASC);
CREATE INDEX XIF2LAUNDRYBAGCATEGORY_DESC ON LAUNDRYBAGCATEGORY_DESC (LANGUAGE_ID ASC);

ALTER TABLE LAUNDRYBAGCATEGORY_DESC ADD CONSTRAINT FK_LAUNDRYBAGCATEGORY_DESC_LNDRYBAGCTGRY_ID FOREIGN KEY (LAUNDRYBAGCATEGORY_ID) REFERENCES LAUNDRYBAGCATEGORY (LAUNDRYBAGCATEGORY_ID) ON DELETE CASCADE;
ALTER TABLE LAUNDRYBAGCATEGORY_DESC ADD CONSTRAINT FK_LAUNDRYBAGCATEGORY_DESC_LANGUAGE_ID FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGES (LANGUAGE_ID);


--LAUNDRYBAGTYPE TABLE
CREATE SEQUENCE SEQ_LAUNDRYBAGTYPE
START WITH 1
INCREMENT BY 1;

CREATE TABLE LAUNDRYBAGTYPE(
LAUNDRYBAGTYPE_ID     NUMBER NOT NULL,
CODE                  VARCHAR2(12) NOT NULL,
LAUNDRYBAGCATEGORY_ID NUMBER NOT NULL,
DEFAULTTAREWEIGHT     NUMBER(6,2) NOT NULL,
MAXCONTENTWEIGHT      NUMBER(15,6) NOT NULL,
MAXVOLUMEUNITS        NUMBER(15,6) NULL,
ACTIVE                VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_LAUNDRYBAGTYPE_ACTIVE CHECK (ACTIVE IN ('Y','N')),
TRANSPORTARTICLE_ID   NUMBER NULL,
PRODUCT_LINK_ID       NUMBER NULL,
CREATIONSYSTEMUSER_ID NUMBER NOT NULL,
CREATIONTIMESTAMP     DATE DEFAULT SYSDATE NOT NULL,
SYSTEMUSER_ID         NUMBER NOT NULL,
TIMESTAMP             DATE NOT NULL
);

COMMENT ON COLUMN LAUNDRYBAGTYPE.DEFAULTTAREWEIGHT IS 'Default empty weight for this type of bag';
COMMENT ON COLUMN LAUNDRYBAGTYPE.MAXCONTENTWEIGHT IS ' Maximum weight for this type of bag when filled';
COMMENT ON COLUMN LAUNDRYBAGTYPE.MAXVOLUMEUNITS IS 'Maximum volume in volume units';

ALTER TABLE LAUNDRYBAGTYPE ADD CONSTRAINT XPKLAUNDRYBAGTYPE PRIMARY KEY (LAUNDRYBAGTYPE_ID);

CREATE UNIQUE INDEX XAK1LAUNDRYBAGTYPE ON LAUNDRYBAGTYPE (CODE ASC);
CREATE UNIQUE INDEX XAK2LAUNDRYBAGTYPE ON LAUNDRYBAGTYPE (PRODUCT_LINK_ID ASC);

CREATE INDEX XIF1LAUNDRYBAGTYPE ON LAUNDRYBAGTYPE (LAUNDRYBAGCATEGORY_ID ASC);
CREATE INDEX XIF2LAUNDRYBAGTYPE ON LAUNDRYBAGTYPE (TRANSPORTARTICLE_ID ASC);

ALTER TABLE LAUNDRYBAGTYPE ADD CONSTRAINT FK_LAUNDRYBAGTYPE_LAUNDRYBAGCATEGORY_ID FOREIGN KEY (LAUNDRYBAGCATEGORY_ID) REFERENCES LAUNDRYBAGCATEGORY (LAUNDRYBAGCATEGORY_ID);
ALTER TABLE LAUNDRYBAGTYPE ADD CONSTRAINT FK_LAUNDRYBAGTYPE_TRANSPORTARTICLE_ID FOREIGN KEY (TRANSPORTARTICLE_ID) REFERENCES TRANSPORTARTICLE (TRANSPORTARTICLE_ID) ON DELETE SET NULL;
ALTER TABLE LAUNDRYBAGTYPE ADD CONSTRAINT FK_LAUNDRYBAGTYPE_PRODUCT_LINK_ID FOREIGN KEY (PRODUCT_LINK_ID) REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID) ON DELETE SET NULL;

--LAUNDRYBAGTYPE_DESC
CREATE TABLE LAUNDRYBAGTYPE_DESC(
LAUNDRYBAGTYPE_ID NUMBER NOT NULL,
LANGUAGE_ID       NUMBER NOT NULL,
DESCRIPTION       VARCHAR2(30) NOT NULL,
SYSTEMUSER_ID     NUMBER NOT NULL,
TIMESTAMP         DATE NOT NULL
);

ALTER TABLE LAUNDRYBAGTYPE_DESC ADD CONSTRAINT XPKLAUNDRYBAGTYPE_DESC PRIMARY KEY (LAUNDRYBAGTYPE_ID, LANGUAGE_ID);

CREATE INDEX XIF1LAUNDRYBAGTYPE_DESC ON LAUNDRYBAGTYPE_DESC (LAUNDRYBAGTYPE_ID ASC);
CREATE INDEX XIF2LAUNDRYBAGTYPE_DESC ON LAUNDRYBAGTYPE_DESC (LANGUAGE_ID ASC);

ALTER TABLE LAUNDRYBAGTYPE_DESC ADD CONSTRAINT FK_LAUNDRYBAGTYPE_DESC_LAUNDRYBAGTYPE_ID FOREIGN KEY (LAUNDRYBAGTYPE_ID) REFERENCES LAUNDRYBAGTYPE (LAUNDRYBAGTYPE_ID) ON DELETE CASCADE;
ALTER TABLE LAUNDRYBAGTYPE_DESC ADD CONSTRAINT FK_LAUNDRYBAGTYPE_DESC_LANGUAGE_ID FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGES (LANGUAGE_ID);

--LAUNDRYBAG TABLE
ALTER TABLE LAUNDRYBAG DROP COLUMN LAUNDRYBAGTYPE;
ALTER TABLE LAUNDRYBAG ADD(LAUNDRYBAGTYPE_ID NUMBER NOT NULL);

CREATE UNIQUE INDEX XAK2LAUNDRYBAG ON LAUNDRYBAG (IDCODE ASC);

CREATE INDEX XIF2LAUNDRYBAG ON LAUNDRYBAG (LAUNDRYBAGTYPE_ID ASC);

ALTER TABLE LAUNDRYBAG ADD CONSTRAINT FK_LAUNDRYBAG_LAUNDRYBAGTYPE_ID FOREIGN KEY (LAUNDRYBAGTYPE_ID) REFERENCES LAUNDRYBAGTYPE (LAUNDRYBAGTYPE_ID);

--SECONDARYLAUNDRYBAG
CREATE TABLE SECONDARYLAUNDRYBAG(
LAUNDRYBAG_ID NUMBER NOT NULL,
SECONDARYID   VARCHAR2(24) NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP     DATE NOT NULL
);

COMMENT ON COLUMN SECONDARYLAUNDRYBAG.SECONDARYID IS 'Laundry secondary identification: mostly chip code';

ALTER TABLE SECONDARYLAUNDRYBAG ADD CONSTRAINT XPKSECONDARYLAUNDRYBAG PRIMARY KEY (LAUNDRYBAG_ID);

CREATE UNIQUE INDEX XAK1SECONDARYLAUNDRYBAG ON SECONDARYLAUNDRYBAG (SECONDARYID ASC);

ALTER TABLE SECONDARYLAUNDRYBAG ADD CONSTRAINT FK_SCNDRYLAUNDRYBAG_LAUNDRYBAG_ID FOREIGN KEY (LAUNDRYBAG_ID) REFERENCES LAUNDRYBAG (LAUNDRYBAG_ID) ON DELETE CASCADE;

--UNIQUEIDCONFIGURATION TABLE
COMMENT ON COLUMN UNIQUEIDCONFIGURATION.PACKAGETYPE IS '1 = Product 2 = Storage Location 3 = Container 4 = Bundle (only available for module license) 5 = Residential/domestic 6 = OR Packs 7 = Carts 8 = Bags 9 = Packers 10 = Curtains 11 = Reject Bags 12 = Laundry Bags 13 = Biohazard Bags 14 = Specialty Bags 15 = Residential Services Bags 16 = Sock Nets';

ALTER TABLE UNIQUEIDCONFIGURATION DROP CONSTRAINT CHK_UNIQUEIDCONFIG_PACKAGETYPE;

ALTER TABLE UNIQUEIDCONFIGURATION ADD CONSTRAINT CHK_UNIQUEIDCONFIG_PACKAGETYPE CHECK (PACKAGETYPE IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16));
