-- ABS-10106/ ABS-58426

--- FABRICCHARGE table ---
CREATE SEQUENCE SEQ_FABRICCHARGE
START WITH 1
INCREMENT BY 1;

CREATE TABLE FABRICCHARGE
(
  FABRICCHARGE_ID NUMBER NOT NULL,
  FABRIC_ID       NUMBER NOT NULL,
  STOCKROOM_ID    NUMBER NOT NULL,
  CHARGEDATE      DATE NOT NULL,
  CHARGE          VARCHAR2(20) NOT NULL,
  SUPPLIER_ID     NUMBER NOT NULL,
  CONSUMED        NUMBER DEFAULT 0 NOT NULL CONSTRAINT CONSUMED_VALID1_1
                  CHECK (CONSUMED IN (0, 1)),
  SYSTEMUSER_ID   NUMBER NOT NULL,
  TIMESTAMP       DATE NOT NULL
);

COMMENT ON COLUMN FABRICCHARGE.CONSUMED
  IS '0 = Not consumed
1 = Consumed
';

ALTER TABLE FABRICCHARGE
  ADD CONSTRAINT XPKFABRICCHARGE PRIMARY KEY (FABRICCHARGE_ID);
  
CREATE UNIQUE INDEX XAK1FABRICCHARGE ON FABRICCHARGE (FABRIC_ID ASC, STOCKROOM_ID ASC, SUPPLIER_ID ASC, CHARGEDATE ASC, CHARGE ASC);

CREATE INDEX XIF1FABRICCHARGE ON FABRICCHARGE (FABRIC_ID ASC);
CREATE INDEX XIF2FABRICCHARGE ON FABRICCHARGE (STOCKROOM_ID ASC);
CREATE INDEX XIF3FABRICCHARGE ON FABRICCHARGE (SUPPLIER_ID ASC);

ALTER TABLE FABRICCHARGE
  ADD CONSTRAINT FABRICCHARGE_FABRIC FOREIGN KEY (FABRIC_ID)
  REFERENCES FABRIC (FABRIC_ID) ON DELETE CASCADE;
ALTER TABLE FABRICCHARGE
  ADD CONSTRAINT FABRICCHARGE_ST FOREIGN KEY (STOCKROOM_ID)
  REFERENCES STOCKROOM (STOCKROOM_ID);
ALTER TABLE FABRICCHARGE
  ADD CONSTRAINT FABRICCHARGE_SU FOREIGN KEY (SUPPLIER_ID)
  REFERENCES SUPPLIER (SUPPLIER_ID);

--- PRODUCT table ---
ALTER TABLE PRODUCT ADD (FABRIC2_ID NUMBER NULL,
                         FABRIC3_ID NUMBER NULL);

COMMENT ON COLUMN PRODUCT.FABRIC2_ID
  IS 'Second Fabric, only used for module FABRICCHARGE.';
COMMENT ON COLUMN PRODUCT.FABRIC3_ID
  IS 'Third Fabric, only used for module FABRICCHARGE.';

CREATE INDEX XIF29PRODUCT ON PRODUCT (FABRIC2_ID ASC);
CREATE INDEX XIF30PRODUCT ON PRODUCT (FABRIC3_ID ASC);

ALTER TABLE PRODUCT
  ADD CONSTRAINT PROD_FABRIC2 FOREIGN KEY (FABRIC2_ID)
  REFERENCES FABRIC (FABRIC_ID) ON DELETE SET NULL;
ALTER TABLE PRODUCT
  ADD CONSTRAINT PROD_FABRIC3 FOREIGN KEY (FABRIC3_ID)
  REFERENCES FABRIC (FABRIC_ID) ON DELETE SET NULL;
  
ALTER TABLE PRODUCT
  ADD CONSTRAINT PRODUCTFABRIC_VALID1
  CHECK (NVL(FABRIC2_ID, 0) = 0	OR NVL(FABRIC2_ID, 0) <> FABRIC_ID);
ALTER TABLE PRODUCT
  ADD CONSTRAINT PRODUCTFABRIC_VALID2
  CHECK (NVL(FABRIC3_ID, 0) = 0 OR (NVL(FABRIC3_ID, 0) <> FABRIC_ID AND NVL(FABRIC3_ID, 0) <> NVL(FABRIC2_ID, 0)));

--- UNIQUEITEMFABRICCHARGE table ---
CREATE TABLE UNIQUEITEMFABRICCHARGE
(
  UNIQUEITEM_LINK_ID NUMBER NOT NULL,
  SEQUENCENUMBER     NUMBER DEFAULT 1 NOT NULL CONSTRAINT SEQUENCENUMBER_VALID2_1
                     CHECK (SEQUENCENUMBER BETWEEN 1 AND 3),
  FABRICCHARGE_ID    NUMBER NOT NULL,
  SYSTEMUSER_ID      NUMBER NOT NULL,
  TIMESTAMP          DATE NOT NULL
);

COMMENT ON COLUMN UNIQUEITEMFABRICCHARGE.FABRICCHARGE_ID
  IS 'If a unique item is linked to multiple fabriccharges they should belong to different fabrics.';

ALTER TABLE UNIQUEITEMFABRICCHARGE
  ADD CONSTRAINT XPKUNIQUEITEMFABRICCHARGE PRIMARY KEY (UNIQUEITEM_LINK_ID, SEQUENCENUMBER);

CREATE UNIQUE INDEX XAK1UNIQUEITEMFABRICCHARGE ON UNIQUEITEMFABRICCHARGE (UNIQUEITEM_LINK_ID ASC, FABRICCHARGE_ID ASC);

CREATE INDEX XIF1UNIQUEITEMFABRICCHARGE ON UNIQUEITEMFABRICCHARGE (UNIQUEITEM_LINK_ID ASC);
CREATE INDEX XIF2UNIQUEITEMFABRICCHARGE ON UNIQUEITEMFABRICCHARGE (FABRICCHARGE_ID ASC);

ALTER TABLE UNIQUEITEMFABRICCHARGE
  ADD CONSTRAINT UNIQUEITEMFABRICCHARGE_UI FOREIGN KEY (UNIQUEITEM_LINK_ID)
  REFERENCES UNIQUEITEM_LINK (UNIQUEITEM_LINK_ID) ON DELETE CASCADE;
ALTER TABLE UNIQUEITEMFABRICCHARGE
  ADD CONSTRAINT UNIQUEITEMFABRICCHARGE_FC FOREIGN KEY (FABRICCHARGE_ID)
  REFERENCES FABRICCHARGE (FABRICCHARGE_ID);

