CREATE TABLE gps_trips (
    id bigserial,
    member_id bigint,
    start_members_tracking_id bigint REFERENCES members_tracking(id),
    last_members_tracking_id bigint REFERENCES members_tracking(id),
    travel_date timestamp,
    travel_date_end timestamp,
    duration real,
    distance real,
    location_start_lat double precision,
    location_start_lng double precision,
    location_start geography,
    location_start_id bigint,
    location_end_lat double precision,
    location_end_lng double precision,
    location_end geography,
    location_end_id bigint,
    avg_speed real,
    PRIMARY KEY(id)
);

CREATE TABLE gps_trips_legs (
    id bigserial,
    member_id bigint,
    start_members_tracking_id bigint REFERENCES members_tracking(id),
    last_members_tracking_id bigint REFERENCES members_tracking(id),
    gps_trip_id bigint REFERENCES gps_trips(id),
    travel_date timestamp,
    travel_date_end timestamp,
    duration real,
    distance real,
    location_start_lat double precision,
    location_start_lng double precision,
    location_start geography,
    location_start_id bigint,
    location_end_lat double precision,
    location_end_lng double precision,
    location_end geography,
    location_end_id bigint,
    avg_speed real,
    transport_mode varchar(30),
    PRIMARY KEY(id)
);

comment on column gps_trips.duration is 'in minutes';
comment on column gps_trips.distance is 'in kms';

comment on column gps_trips_legs.duration is 'in minutes';
comment on column gps_trips_legs.distance is 'in kms';