-- ABS-83744 / ABS-84432
---------------------- USERGROUP ---------------------

ALTER TABLE USERGROUP ADD (ALLOWCONTACTMGRDMAPPROVAL VARCHAR2(1) DEFAULT 'N' NOT NULL 
                           CONSTRAINT CHK_USERGROUP_ALLOWCONTMGRDMAP CHECK (ALLOWCONTACTMGRDMAPPROVAL IN ('Y','N')),
                           ALLOWCONTACTMGRCOMPLETION VARCHAR2(1) DEFAULT 'N' NOT NULL 
                           CONSTRAINT CHK_USERGROUP_ALLOWCONTMGRCOMP CHECK (ALLOWCONTACTMGRCOMPLETION IN ('Y','N')));
                           
-- ABS-83744 / ABS-84453                           
--------------------- CONTACT -----------------
ALTER TABLE CONTACT ADD CONSTRAINT CHK_CONTACT_STATUS CHECK (STATUS IN(1,2,3,4,5,6));

ALTER TABLE CONTACT MODIFY CREATEDBYUSER_ID NUMBER NULL;

ALTER TABLE CONTACT ADD (COMPLETEDDATE DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
                         COMPLETEDBYUSERNAME VARCHAR2(30) NULL,
                         COMPLETEDBYUSER_ID NUMBER NULL,
                         REVIEWDATE DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY') NOT NULL,
                         REVIEWEDBYUSERNAME VARCHAR2(30) NULL,
                         REVIEWEDBYUSER_ID NUMBER NULL,
                         BLOCKDEPARTUREONSTOP NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_CONTACT_BLOCKDEPARTUREONST CHECK(BLOCKDEPARTUREONSTOP IN(0,1)),
                         FOLLOWUPBYROUTE VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_CONTACT_FOLLOWUPBYROUTE CHECK(FOLLOWUPBYROUTE IN('Y','N')));
                         
CREATE INDEX XIF12CONTACTFOLLOWUPNOTE ON CONTACT(INPROGRESSBYUSER_ID ASC);
CREATE INDEX XIF13CONTACTFOLLOWUPNOTE ON CONTACT(CLOSEDBYUSER_ID ASC);
CREATE INDEX XIF14CONTACTFOLLOWUPNOTE ON CONTACT(COMPLETEDBYUSER_ID ASC);
CREATE INDEX XIF15CONTACTFOLLOWUPNOTE ON CONTACT(REVIEWEDBYUSER_ID ASC);
                         
ALTER TABLE CONTACT DROP CONSTRAINT CONTACT_CREATEDBYUSER_ID;
ALTER TABLE CONTACT ADD CONSTRAINT FK_CONTACT_CREATEDBYUSER_ID
FOREIGN KEY(CREATEDBYUSER_ID)
REFERENCES SYSTEMUSER(SYSTEMUSER_ID) ON DELETE SET NULL;

ALTER TABLE CONTACT DROP CONSTRAINT CONTACT_FOLLOWUPBYUSER_ID;
ALTER TABLE CONTACT ADD CONSTRAINT FK_CONTACT_FOLLOWUPBYUSER_ID
FOREIGN KEY(FOLLOWUPBYUSER_ID)
REFERENCES SYSTEMUSER(SYSTEMUSER_ID) ON DELETE SET NULL;

ALTER TABLE CONTACT ADD CONSTRAINT FK_CONTACT_INPROGRESSBYUSER_ID
FOREIGN KEY(INPROGRESSBYUSER_ID)
REFERENCES SYSTEMUSER(SYSTEMUSER_ID) ON DELETE SET NULL;

ALTER TABLE CONTACT ADD CONSTRAINT FK_CONTACT_CLOSEDBYUSER_ID
FOREIGN KEY(CLOSEDBYUSER_ID)
REFERENCES SYSTEMUSER(SYSTEMUSER_ID) ON DELETE SET NULL;

ALTER TABLE CONTACT ADD CONSTRAINT FK_CONTACT_COMPLETEDBYUSER_ID
FOREIGN KEY(COMPLETEDBYUSER_ID)
REFERENCES SYSTEMUSER(SYSTEMUSER_ID) ON DELETE SET NULL;

ALTER TABLE CONTACT ADD CONSTRAINT FK_CONTACT_REVIEWEDBYUSER_ID
FOREIGN KEY(REVIEWEDBYUSER_ID)
REFERENCES SYSTEMUSER(SYSTEMUSER_ID) ON DELETE SET NULL;


COMMENT ON COLUMN CONTACT.STATUS
IS'1=New
2=Needs Follow-up
3=In Progress
4=Completed
5=Reviewed by manager
6=Closed
The statuses 4 and 5 are optional and will be enabled via a system setting';
COMMENT ON COLUMN CONTACT.REVIEWEDBYUSER_ID 
IS 'System User(Manager) that reviewed this contact';
COMMENT ON COLUMN CONTACT.BLOCKDEPARTUREONSTOP
IS 'Defines if contact needs to be changed before departure on stop is allowed on RA. Possible values:
0 = Do not block/not applicable (default)
1 = Do block departure on stop';

UPDATE CONTACT C
SET C.FOLLOWUPBYROUTE='N',
    C.BLOCKDEPARTUREONSTOP=0
WHERE C.STATUS=4;
COMMIT;

UPDATE CONTACT C
SET C.STATUS=3,
    C.INPROGRESSBYUSER_ID=C.FOLLOWUPBYUSER_ID,
    C.INPROGRESSBYUSERNAME=C.FOLLOWUPBYUSERNAME,
	C.INPROGRESSDATE=SYSDATE
WHERE C.STATUS=2;
COMMIT;

UPDATE CONTACT C
SET C.STATUS=6
WHERE C.STATUS=4;
COMMIT; 