-- ABS-69053 / ABS-69108
--- CONTRACTBRAND ---
CREATE SEQUENCE SEQ_CONTRACTBRAND
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTRACTBRAND
(
CONTRACTBRAND_ID NUMBER NOT NULL,
CONTRACT_ID NUMBER NOT NULL,
CODE VARCHAR2(15) NOT NULL,
DESCRIPTION VARCHAR2(50) NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE CONTRACTBRAND ADD CONSTRAINT XPKCONTRACTBRAND PRIMARY KEY (CONTRACTBRAND_ID);

CREATE INDEX XAK1CONTRACTBRAND ON CONTRACTBRAND (CONTRACT_ID ASC, CODE ASC);
CREATE INDEX XIF1CONTRACTBRAND ON CONTRACTBRAND (CONTRACT_ID ASC);

ALTER TABLE CONTRACTBRAND ADD CONSTRAINT CONTRACTBRAND_CONTRACT FOREIGN KEY (CONTRACT_ID)
REFERENCES CONTRACT (CONTRACT_ID) ON DELETE CASCADE;

--- CONTRACTCUSTOMERBRAND ---
CREATE TABLE CONTRACTCUSTOMERBRAND
(
CONTRACTCUSTOMER_ID NUMBER NOT NULL,
CONTRACTBRAND_ID NUMBER NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE CONTRACTCUSTOMERBRAND ADD CONSTRAINT XPKCONTRACTCUSTOMERBRAND PRIMARY KEY (CONTRACTCUSTOMER_ID, CONTRACTBRAND_ID);

CREATE INDEX XIF1CONTRACTCUSTOMERBRAND ON CONTRACTCUSTOMERBRAND (CONTRACTCUSTOMER_ID ASC);
CREATE INDEX XIF2CONTRACTCUSTOMERBRAND ON CONTRACTCUSTOMERBRAND (CONTRACTBRAND_ID ASC);

ALTER TABLE CONTRACTCUSTOMERBRAND ADD CONSTRAINT CONTRACTCUSTOMERBRAND_CONCUST FOREIGN KEY (CONTRACTCUSTOMER_ID)
REFERENCES CONTRACTCUSTOMER (CONTRACTCUSTOMER_ID) ON DELETE CASCADE;

ALTER TABLE CONTRACTCUSTOMERBRAND ADD CONSTRAINT CONTRACTCUSTOMERBRAND_CONBRAND FOREIGN KEY (CONTRACTBRAND_ID)
REFERENCES CONTRACTBRAND (CONTRACTBRAND_ID) ON DELETE CASCADE;

--- CONTRACTPRODUCTMISCELLANEOUS ---
ALTER TABLE CONTRACTPRODUCTMISCELLANEOUS ADD (BRANDLESS VARCHAR2(1) DEFAULT 'Y' NULL
						CONSTRAINT YN_VALIDATIONFIELD431 CHECK (BRANDLESS IN ('Y','N')));

--- CONTRACTPRODUCTBRAND ---
CREATE TABLE CONTRACTPRODUCTBRAND
(
CONTRACTPRODUCT_ID NUMBER NOT NULL,
CONTRACTBRAND_ID NUMBER NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE CONTRACTPRODUCTBRAND ADD CONSTRAINT XPKCONTRACTPRODUCTBRAND PRIMARY KEY (CONTRACTPRODUCT_ID, CONTRACTBRAND_ID);

CREATE INDEX XIF1CONTRACTPRODUCTBRAND ON CONTRACTPRODUCTBRAND (CONTRACTPRODUCT_ID ASC);
CREATE INDEX XIF2CONTRACTPRODUCTBRAND ON CONTRACTPRODUCTBRAND (CONTRACTBRAND_ID ASC);

ALTER TABLE CONTRACTPRODUCTBRAND ADD CONSTRAINT CONTRACTPRODBRAND_CONPROD FOREIGN KEY (CONTRACTPRODUCT_ID)
REFERENCES CONTRACTPRODUCT (CONTRACTPRODUCT_ID) ON DELETE CASCADE;

ALTER TABLE CONTRACTPRODUCTBRAND ADD CONSTRAINT CONTRACTPRODBRAND_CONBRAND FOREIGN KEY (CONTRACTBRAND_ID)
REFERENCES CONTRACTBRAND (CONTRACTBRAND_ID) ON DELETE CASCADE;
