-- ABS-83744 / ABS-84453
----------- CONTACT -----------
ALTER TABLE CONTACT ADD (CHECKBEFOREROUTESTART NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_CONTACT_CHECKBEFOREROUTEST
  CHECK (CHECKBEFOREROUTESTART IN (0,1)));

COMMENT ON COLUMN CONTACT.CHECKBEFOREROUTESTART
  IS 'Defines if contact needs to be read before route is started on RA.
0 = Do not check/not applicable (default)
1 = Do check before route start';

------------ CONTACTFOLLOWUPNOTE ------------
CREATE SEQUENCE SEQ_CONTACTFOLLOWUPNOTE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTACTFOLLOWUPNOTE
( CONTACTFOLLOWUPNOTE_ID NUMBER NOT NULL,
  CONTACT_ID             NUMBER NOT NULL,
  CREATIONTIMESTAMP      DATE NOT NULL,
  CREATEDBYUSERNAME      VARCHAR2(30) NOT NULL,
  CREATEDBYUSERFULLNAME  VARCHAR2(40) NOT NULL,
  CREATEDBYUSER_ID       NUMBER NULL,
  NOTE                   VARCHAR2(500) NOT NULL,
  SYSTEMUSERNAME         VARCHAR2(30) NOT NULL,
  SYSTEMUSER_ID          NUMBER NOT NULL,
  TIMESTAMP              DATE NOT NULL);

ALTER TABLE CONTACTFOLLOWUPNOTE
  ADD CONSTRAINT XPKCONTACTFOLLOWUPNOTE PRIMARY KEY (CONTACTFOLLOWUPNOTE_ID);

CREATE UNIQUE INDEX XAK1CONTACTFOLLOWUPNOTE ON CONTACTFOLLOWUPNOTE (CONTACT_ID ASC, CREATIONTIMESTAMP ASC, CREATEDBYUSER_ID ASC);
CREATE INDEX XIF1CONTACTFOLLOWUPNOTE ON CONTACTFOLLOWUPNOTE (CONTACT_ID ASC);

ALTER TABLE CONTACTFOLLOWUPNOTE
  ADD CONSTRAINT FK_CONTACTFOLLOWUPNOTE_CONTACT FOREIGN KEY (CONTACT_ID)
  REFERENCES CONTACT (CONTACT_ID) ON DELETE CASCADE;
  
ALTER TABLE CONTACTFOLLOWUPNOTE
  ADD CONSTRAINT FK_CONTACTFOLLOWUPNOTE_USER FOREIGN KEY (CREATEDBYUSER_ID)
  REFERENCES SYSTEMUSER (SYSTEMUSER_ID) ON DELETE SET NULL;

INSERT INTO CONTACTFOLLOWUPNOTE 
(CONTACTFOLLOWUPNOTE_ID, CONTACT_ID, CREATIONTIMESTAMP, CREATEDBYUSERNAME, CREATEDBYUSERFULLNAME,CREATEDBYUSER_ID, NOTE, SYSTEMUSERNAME , SYSTEMUSER_ID, TIMESTAMP)
SELECT SEQ_CONTACTFOLLOWUPNOTE.NEXTVAL, C.CONTACT_ID, C.FOLLOWUPDATE, NVL(C.FOLLOWUPBYUSERNAME, '-'), NVL((SELECT SU.FULLNAME FROM SYSTEMUSER SU WHERE SU.SYSTEMUSER_ID = C.FOLLOWUPBYUSER_ID), '-'),
       NVL(C.FOLLOWUPBYUSER_ID, GETMINSYSTEMUSER()) , C.FOLLOWUPNOTES, NVL(C.FOLLOWUPBYUSERNAME, '-'), NVL(C.FOLLOWUPBYUSER_ID, GETMINSYSTEMUSER()), C.FOLLOWUPDATE
FROM CONTACT C
WHERE C.FOLLOWUPNOTES IS NOT NULL;
COMMIT;
