--ABS-60230/ ABS-61651

-------------- LOCKERTRACKINGIDLINK --------------
CREATE SEQUENCE SEQ_LOCKERTRACKINGIDLINK
START WITH 1
INCREMENT BY 1;

CREATE TABLE LOCKERTRACKINGIDLINK
( LOCKERTRACKINGIDLINK_ID NUMBER NOT NULL,
  CUSTOMER_ID             NUMBER NOT NULL,
  LOCKERTRACKINGID        VARCHAR2(24) NOT NULL);

ALTER TABLE LOCKERTRACKINGIDLINK
  ADD CONSTRAINT XPKLOCKERTRACKINGIDLINK PRIMARY KEY (LOCKERTRACKINGIDLINK_ID);

CREATE UNIQUE INDEX XAK1LOCKERTRACKINGIDLINK ON LOCKERTRACKINGIDLINK (CUSTOMER_ID ASC, LOCKERTRACKINGID ASC);
CREATE INDEX XIF1LOCKERTRACKINGIDLINK ON LOCKERTRACKINGIDLINK (CUSTOMER_ID ASC);

ALTER TABLE LOCKERTRACKINGIDLINK
  ADD CONSTRAINT LOCKERTRACKINGIDLINK_CUSTOMER FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;

-------------- LOCKER --------------
ALTER TABLE LOCKER DROP COLUMN LOCKERTRACKINGID CASCADE CONSTRAINTS;
ALTER TABLE LOCKER ADD (LOCKERTRACKINGIDLINK_ID NUMBER NULL);

CREATE INDEX XIF4LOCKER ON LOCKER (LOCKERTRACKINGIDLINK_ID ASC);

ALTER TABLE LOCKER
  ADD CONSTRAINT LOCKER_LOCKERTRACKINGIDLINK FOREIGN KEY (LOCKERTRACKINGIDLINK_ID)
  REFERENCES LOCKERTRACKINGIDLINK (LOCKERTRACKINGIDLINK_ID) ON DELETE CASCADE;

-------------- SCAN --------------
ALTER TABLE SCAN ADD (LOCKERFEEDSCAN_ID NUMBER NULL);

COMMENT ON COLUMN SCAN.LOCKERFEEDSCAN_ID
  IS 'Scan_ID for any corresponding locker scan, note, no Foreign Key';

