--ABS-91777/ABS-105749
---------------------STOPGPSDATA-------------

CREATE TABLE STOPGPSDATA
(
STOP_ID         NUMBER NOT NULL,
EVENTTYPE       NUMBER NOT NULL CONSTRAINT CHK_STOPGPS_EVENT CHECK (EVENTTYPE IN (1, 2, 3, 4, 5)),
EMPLOYEE_ID     NUMBER NOT NULL,
MOBILEDEVICE_ID NUMBER NOT NULL,
GPSLATITUDE     VARCHAR2(12) NOT NULL,
GPSLONGITUDE    VARCHAR2(12) NOT NULL,
GPSTIMESTAMP    DATE NOT NULL,
ALLOWEDDISTANCE NUMBER DEFAULT 0 NOT NULL,
CALCULATEDDISTANCE NUMBER DEFAULT 0 NOT NULL,
SYSTEMUSER_ID   NUMBER NOT NULL,
TIMESTAMP       DATE NOT NULL
);

COMMENT ON COLUMN STOPGPSDATA.EVENTTYPE IS '1 = Arrival at stop
2 = Departure from stop
3 = Mark stop undeliverable
4 = Proof of visit scan
5 = Capture signature
';

COMMENT ON COLUMN STOPGPSDATA.GPSTIMESTAMP IS 'Date/time the GPS coordinates were captured';

COMMENT ON COLUMN STOPGPSDATA.ALLOWEDDISTANCE IS 'Allowed distance to delivery point when capturing GPS date for event';

COMMENT ON COLUMN STOPGPSDATA.CALCULATEDDISTANCE IS 'Calculated distance to delivery point when GPS data was captured for event';

ALTER TABLE STOPGPSDATA ADD CONSTRAINT XPKSTOPGPSDATA PRIMARY KEY (STOP_ID, EVENTTYPE);

CREATE INDEX XIF1STOPGPSDATA ON STOPGPSDATA (STOP_ID ASC);
CREATE INDEX XIF2STOPGPSDATA ON STOPGPSDATA (EMPLOYEE_ID ASC);
CREATE INDEX XIF3STOPGPSDATA ON STOPGPSDATA (MOBILEDEVICE_ID ASC);

ALTER TABLE STOPGPSDATA ADD CONSTRAINT FK_STOPGPSDATA_STOP FOREIGN KEY (STOP_ID)
REFERENCES STOP (STOP_ID) ON DELETE CASCADE;

ALTER TABLE STOPGPSDATA ADD CONSTRAINT FK_STOPGPSDATA_EMPLOYEE FOREIGN KEY (EMPLOYEE_ID)
REFERENCES EMPLOYEE (EMPLOYEE_ID);

ALTER TABLE STOPGPSDATA ADD CONSTRAINT FK_STOPGPSDATA_MOBDEV FOREIGN KEY (MOBILEDEVICE_ID)
REFERENCES MOBILEDEVICE (MOBILEDEVICE_ID);

