--ABS-62480/ ABS-64157

-- CONTRACT TABLE --
COMMENT ON COLUMN CONTRACT.CATEGORY
  IS '1 = Secondary
2 = Primary
3 = Not currently used
4 = Route Service Contract';

-- CONTRACTPRIMARYTOSECLINK TABLE --
CREATE SEQUENCE SEQ_CONTRACTPRIMARYTOSECLINK
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTRACTPRIMARYTOSECLINK
(
  CONTRACTPRIMARYTOSECLINK_ID NUMBER NOT NULL,
  PRIMARYCONTRACT_ID          NUMBER NOT NULL,
  SECONDARYCONTRACT_ID        NUMBER NOT NULL,
  TIMESTAMP                   DATE NOT NULL,
  SYSTEMUSER_ID               NUMBER NOT NULL
)
;

ALTER TABLE CONTRACTPRIMARYTOSECLINK
  ADD CONSTRAINT XPKCONTRACTPRIMARYTOSECLINK PRIMARY KEY (CONTRACTPRIMARYTOSECLINK_ID);
 
CREATE UNIQUE INDEX XAK1CONTRACTPRIMARYTOSECLINK ON CONTRACTPRIMARYTOSECLINK (PRIMARYCONTRACT_ID ASC, SECONDARYCONTRACT_ID ASC);
CREATE INDEX XIF1CONTRACTPRIMARYTOSECLINK ON CONTRACTPRIMARYTOSECLINK (PRIMARYCONTRACT_ID ASC);
CREATE INDEX XIF2CONTRACTPRIMARYTOSECLINK ON CONTRACTPRIMARYTOSECLINK (SECONDARYCONTRACT_ID ASC);

ALTER TABLE CONTRACTPRIMARYTOSECLINK
  ADD CONSTRAINT CONTRACTPRIMARYTOSECLINK_C1 FOREIGN KEY (PRIMARYCONTRACT_ID)
  REFERENCES CONTRACT (CONTRACT_ID);
ALTER TABLE CONTRACTPRIMARYTOSECLINK
  ADD CONSTRAINT CONTRACTPRIMARYTOSECLINK_C2 FOREIGN KEY (SECONDARYCONTRACT_ID)
  REFERENCES CONTRACT (CONTRACT_ID) ON DELETE CASCADE;
