-- ABS-150021 Create new field and database structure for distribution point

CREATE SEQUENCE SEQ_PRODUCTIONCUSTOMER
START WITH 1
INCREMENT BY 1;

CREATE TABLE PRODUCTIONCUSTOMER (
  PRODUCTIONCUSTOMER_ID NUMBER NOT NULL,
  BUSINESSUNIT_ID NUMBER NOT NULL,
  DISTRIBUTIONPOINT_ID NUMBER NOT NULL,
  INTERNALCUSTOMERNUMBER NUMBER NOT NULL,
  CREATIONTIMESTAMP DATE NOT NULL,
  CREATIONUSER_ID NUMBER NOT NULL,
  TIMESTAMP DATE NOT NULL,
  SYSTEMUSER_ID NUMBER NOT NULL
);
  
ALTER TABLE PRODUCTIONCUSTOMER 
ADD CONSTRAINT XPKPRODUCTIONCUSTOMER PRIMARY KEY (PRODUCTIONCUSTOMER_ID);

CREATE UNIQUE INDEX XAK1PRODUCTIONCUSTOMER ON PRODUCTIONCUSTOMER (BUSINESSUNIT_ID, DISTRIBUTIONPOINT_ID);
CREATE UNIQUE INDEX XAK2PRODUCTIONCUSTOMER ON PRODUCTIONCUSTOMER (BUSINESSUNIT_ID, INTERNALCUSTOMERNUMBER);
CREATE INDEX XIF1PRODUCTIONCUSTOMER ON PRODUCTIONCUSTOMER (BUSINESSUNIT_ID);
CREATE INDEX XIF2RODUCTIONCUSTOMER ON PRODUCTIONCUSTOMER (DISTRIBUTIONPOINT_ID);

ALTER TABLE PRODUCTIONCUSTOMER
ADD CONSTRAINT FK_PRODUCTIONCUSTOMER_BUSINESSUNITID FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE CASCADE;

ALTER TABLE PRODUCTIONCUSTOMER
ADD CONSTRAINT FK_PRODUCTIONCUSTOMER_DISTRIBUTIONPOINTID FOREIGN KEY (DISTRIBUTIONPOINT_ID) REFERENCES DISTRIBUTIONPOINT (DISTRIBUTIONPOINT_ID) ON DELETE CASCADE;