-- ABS-47292/ ABS-114625

--------- CONTAINERBILLPERCUSTOMER TABLE ---------------
CREATE SEQUENCE SEQ_CONTAINERBILLPERCUSTOMER
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTAINERBILLPERCUSTOMER
(
  CONTAINERBILLPERCUSTOMER_ID NUMBER NOT NULL,
  CUSTOMER_ID                 NUMBER NOT NULL,
  CONTAINERTYPE_ID            NUMBER NOT NULL,
  DATEACTIVE                  DATE NOT NULL,
  DATEINACTIVE                DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
  COSTCENTER_LINK_ID          NUMBER NOT NULL,
  RENTPERIODDELAYWEEKS        NUMBER DEFAULT 0 NOT NULL
                              CONSTRAINT CHK_CONTAINERBILLPERCUST_RENT CHECK (RENTPERIODDELAYWEEKS BETWEEN 0 AND 999),
  QTYALLOWEDFREEONSITE        NUMBER DEFAULT 0 NOT NULL
                              CONSTRAINT CHK_CONTAINERBILLPERCUST_QTY CHECK (QTYALLOWEDFREEONSITE BETWEEN 0 AND 999),
  SYSTEMUSER_ID               NUMBER NOT NULL,
  TIMESTAMP                   DATE NOT NULL
)
;

ALTER TABLE CONTAINERBILLPERCUSTOMER
  ADD CONSTRAINT XPKCONTAINERBILLPERCUSTOMER PRIMARY KEY (CONTAINERBILLPERCUSTOMER_ID);

CREATE UNIQUE INDEX XAK1CONTAINERBILLPERCUSTOMER ON CONTAINERBILLPERCUSTOMER (CUSTOMER_ID ASC, CONTAINERTYPE_ID ASC);
  
CREATE INDEX XIF1CONTAINERBILLPERCUSTOMER ON CONTAINERBILLPERCUSTOMER (CUSTOMER_ID ASC);
CREATE INDEX XIF2CONTAINERBILLPERCUSTOMER ON CONTAINERBILLPERCUSTOMER (CONTAINERTYPE_ID ASC);
CREATE INDEX XIF3CONTAINERBILLPERCUSTOMER ON CONTAINERBILLPERCUSTOMER (COSTCENTER_LINK_ID ASC);

ALTER TABLE CONTAINERBILLPERCUSTOMER
  ADD CONSTRAINT FK_CONTAINERBILLPERCUST_CUST FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;
  
ALTER TABLE CONTAINERBILLPERCUSTOMER
  ADD CONSTRAINT FK_CONTAINERBILLPERCUST_CT FOREIGN KEY (CONTAINERTYPE_ID)
  REFERENCES CONTAINERTYPE (CONTAINERTYPE_ID) ON DELETE CASCADE;
  
ALTER TABLE CONTAINERBILLPERCUSTOMER
  ADD CONSTRAINT FK_CONTAINERBILLPERCUST_CCL FOREIGN KEY (COSTCENTER_LINK_ID)
  REFERENCES COSTCENTER_LINK (COSTCENTER_LINK_ID);

--------- CONTAINERRENT TABLE -------------
CREATE SEQUENCE SEQ_CONTAINERRENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTAINERRENT
(
  CONTAINERRENT_ID            NUMBER NOT NULL,
  CONTAINER_ID                NUMBER NOT NULL,
  DELIVERYDATE                DATE NOT NULL,
  RETURNDATE                  DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
  CONTAINERBILLPERCUSTOMER_ID NUMBER NOT NULL,
  DISTRIBUTIONPOINT_LINK_ID   NUMBER NOT NULL,
  SYSTEMUSER_ID               NUMBER NOT NULL,
  TIMESTAMP                   DATE NOT NULL
)
;

ALTER TABLE CONTAINERRENT
  ADD CONSTRAINT XPKCONTAINERRENT PRIMARY KEY (CONTAINERRENT_ID);

CREATE UNIQUE INDEX XAK1CONTAINERRENT ON CONTAINERRENT (CONTAINER_ID ASC, DELIVERYDATE ASC);

CREATE INDEX XIF1CONTAINERRENT ON CONTAINERRENT (CONTAINER_ID ASC);
CREATE INDEX XIF3CONTAINERRENT ON CONTAINERRENT (CONTAINERBILLPERCUSTOMER_ID ASC);
CREATE INDEX XIF2CONTAINERRENT ON CONTAINERRENT (DISTRIBUTIONPOINT_LINK_ID ASC);

ALTER TABLE CONTAINERRENT
  ADD CONSTRAINT FK_CONTAINERRENT_C FOREIGN KEY (CONTAINER_ID)
  REFERENCES CONTAINER (CONTAINER_ID);
  
ALTER TABLE CONTAINERRENT
  ADD CONSTRAINT FK_CONTAINERRENT_CBC FOREIGN KEY (CONTAINERBILLPERCUSTOMER_ID)
  REFERENCES CONTAINERBILLPERCUSTOMER (CONTAINERBILLPERCUSTOMER_ID);
  
ALTER TABLE CONTAINERRENT
  ADD CONSTRAINT FK_CONTAINERRENT_DP FOREIGN KEY (DISTRIBUTIONPOINT_LINK_ID)
  REFERENCES DISTRIBUTIONPOINT (DISTRIBUTIONPOINT_ID) ON DELETE CASCADE;

--------- CUSTOMERCONTAINERRENTPRICE TABLE ------------
CREATE SEQUENCE SEQ_CUSTOMERCONTAINERRENTPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CUSTOMERCONTAINERRENTPRICE
(
  CUSTOMERCONTAINERRENTPRICE_ID NUMBER NOT NULL,
  CUSTOMERBILLPERCONTAINER_ID   NUMBER NOT NULL,
  PRICE                         NUMBER(15,6) NOT NULL,
  STARTDATE                     DATE NOT NULL,
  PRICETYPE                     NUMBER DEFAULT 1 NOT NULL,
  SYSTEMUSER_ID                 NUMBER NOT NULL,
  TIMESTAMP                     DATE NOT NULL
)
;

COMMENT ON COLUMN CUSTOMERCONTAINERRENTPRICE.PRICETYPE
  IS '1 = Rent ';

ALTER TABLE CUSTOMERCONTAINERRENTPRICE
  ADD CONSTRAINT XPKCUSTOMERCONTAINERRENTPRICE PRIMARY KEY (CUSTOMERCONTAINERRENTPRICE_ID);

CREATE UNIQUE INDEX XAK1CUSTOMERCONTAINERRENTPRICE ON CUSTOMERCONTAINERRENTPRICE (CUSTOMERBILLPERCONTAINER_ID ASC, PRICETYPE ASC, STARTDATE ASC);

CREATE INDEX XIF1CUSTOMERCONTAINERRENTPRICE ON CUSTOMERCONTAINERRENTPRICE (CUSTOMERBILLPERCONTAINER_ID ASC);

ALTER TABLE CUSTOMERCONTAINERRENTPRICE
  ADD CONSTRAINT FK_CUSTOMERCONTRENTPRICE_CBC FOREIGN KEY (CUSTOMERBILLPERCONTAINER_ID)
  REFERENCES CONTAINERBILLPERCUSTOMER (CONTAINERBILLPERCUSTOMER_ID) ON DELETE CASCADE;

---------- GENERALPRICELIST TABLE -----------
COMMENT ON COLUMN GENERALPRICELIST.PRICETYPE
  IS '1 = Wash 2 = Rent 3 = Replacement 4 = Sales 5 = Weight 6 = Repair 7 = Emblem 8 = Emblem Template 9 = Garment Insurance 10 = Special Charge 11 = Wearer Function Special Type 12 = Special Size Charge 13 = Container Rent';

ALTER TABLE GENERALPRICELIST
  DROP CONSTRAINT PRICETYPE_VALID4;
ALTER TABLE GENERALPRICELIST
  ADD CONSTRAINT PRICETYPE_VALID4
  CHECK (PRICETYPE IN (1,2,3,4,5,6,7,8,9,10,11,12, 13));

--------- GENERALCONTAINERRENTPRICE TABLE ------------
CREATE SEQUENCE SEQ_GENERALCONTAINERRENTPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE GENERALCONTAINERRENTPRICE
(
  GENERALCONTAINERRENTPRICE_ID NUMBER NOT NULL,
  GENERALPRICELIST_ID          NUMBER NOT NULL,
  CONTAINERTYPE_ID             NUMBER NOT NULL,
  PRICE                        NUMBER(15,6) NOT NULL,
  STARTDATE                    DATE NOT NULL,
  PRICETYPE                    NUMBER DEFAULT 1 NOT NULL,
  SYSTEMUSER_ID                NUMBER NOT NULL,
  TIMESTAMP                    DATE NOT NULL
)
;

COMMENT ON COLUMN GENERALCONTAINERRENTPRICE.SYSTEMUSER_ID
  IS '1 = Rent';

ALTER TABLE GENERALCONTAINERRENTPRICE
  ADD CONSTRAINT XPKGENERALCONTAINERRENTPRICE PRIMARY KEY (GENERALCONTAINERRENTPRICE_ID);
  
CREATE UNIQUE INDEX XAK1GENERALCONTAINERRENTPRICE ON GENERALCONTAINERRENTPRICE (GENERALPRICELIST_ID ASC, CONTAINERTYPE_ID ASC, PRICETYPE ASC, STARTDATE ASC);
CREATE INDEX XIF1GENERALCONTAINERRENTPRICE ON GENERALCONTAINERRENTPRICE (GENERALPRICELIST_ID ASC);
CREATE INDEX XIF2GENERALCONTAINERRENTPRICE ON GENERALCONTAINERRENTPRICE (CONTAINERTYPE_ID ASC);

ALTER TABLE GENERALCONTAINERRENTPRICE
  ADD CONSTRAINT FK_GENERALCONTRENTPRICE_GPL FOREIGN KEY (GENERALPRICELIST_ID)
  REFERENCES GENERALPRICELIST (GENERALPRICELIST_ID) ON DELETE CASCADE;
  
ALTER TABLE GENERALCONTAINERRENTPRICE
  ADD CONSTRAINT FK_GENERALCONTRENTPRICE_CT FOREIGN KEY (CONTAINERTYPE_ID)
  REFERENCES CONTAINERTYPE (CONTAINERTYPE_ID) ON DELETE CASCADE;

------ INVOICELINECONTAINER TABLE ------------
CREATE TABLE INVOICELINECONTAINER
(
  INVOICELINE_ID            NUMBER NOT NULL,
  CONTAINERTYPE_ID          NUMBER NOT NULL,
  TOTALQTYALLOWEDFREEONSITE NUMBER DEFAULT 0 NOT NULL,
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 DATE NOT NULL
)
;

ALTER TABLE INVOICELINECONTAINER
  ADD CONSTRAINT XPKINVOICELINECONTAINER PRIMARY KEY (INVOICELINE_ID);
  
CREATE INDEX XIF1INVOICELINECONTAINER ON INVOICELINECONTAINER (CONTAINERTYPE_ID ASC);

ALTER TABLE INVOICELINECONTAINER
  ADD CONSTRAINT FK_INVOICELINECONTAINER_IL FOREIGN KEY (INVOICELINE_ID)
  REFERENCES INVOICELINE (INVOICELINE_ID) ON DELETE CASCADE;
  
ALTER TABLE INVOICELINECONTAINER
  ADD CONSTRAINT FK_INVOICELINECONTAINER_CT FOREIGN KEY (CONTAINERTYPE_ID)
  REFERENCES CONTAINERTYPE (CONTAINERTYPE_ID);

-------- INVOICELINETYPE table ------------
COMMENT ON COLUMN INVOICELINETYPE.TYPE
  IS '01= Rent 02= Delivery (wash) 03= Soil Weight 04= Clean Weight 05= Sales 06= Repair 07= Replacement charge 08= Automatic Replacement (non-garm) 09= Loss charge 10= Remaining value charge 11= Overuse charge 12= Prep / Emblem charge 13= Finance charge 14= Cash Delay line 15= Miscellaneous Invoice line 16= Special Charge 17= Minimum invoice amount 18= Tax 19= Emblem Rent 20= Locker Rent 21= Locker service 22= Lease 23= Stock Charge 24= Lost Customer Residual Value Charge 25= Lost Revenue Charge 26= Residential wash 27= Residential labels 28= Service costs 29= Capped Excess Amounts 30= Client rent 31= Budget 32= Down Payment 33= Statement fee 34 = Custom Garment Charge 35 = Service (only for module UNIQUEITEMSERVICE) 36 = Container Billing (only for module CONTAINERBILLING)';

ALTER TABLE INVOICELINETYPE
  DROP CONSTRAINT CHK_INVLINETYPE_TYPE;
  
ALTER TABLE INVOICELINETYPE
  ADD CONSTRAINT CHK_INVLINETYPE_TYPE
  CHECK (TYPE BETWEEN 1 AND 36);

--------- PRICELISTPERCUSTOMER TABLE ----------
COMMENT ON COLUMN PRICELISTPERCUSTOMER.PRICETYPE
  IS '1 = Wash
2 = Rent
3 = Replacement
4 = Sales
5 = Weight
6 = Repair
7 = Emblem
8 = Emblem Template
9 = Garment Insurance
10 = Special Charge 13 = Container Rent';


