create table raw_location_points
(
    id                bigint generated by default as identity,
    accuracy_meters   float(53)                   not null,
    activity_provided varchar(255),
    latitude          float(53)                   not null,
    longitude         float(53)                   not null,
    timestamp         timestamp(6) with time zone not null,
    user_id           bigint                      not null,
    primary key (id)
);
create table significant_places
(
    id                 bigint generated by default as identity,
    address            varchar(255),
    category           varchar(255),
    first_seen         timestamp(6) with time zone not null,
    last_seen          timestamp(6) with time zone not null,
    latitude_centroid  float(53)                   not null,
    longitude_centroid float(53)                   not null,
    name               varchar(255),
    user_id            bigint                      not null,
    primary key (id)
);
create table trips
(
    id                        bigint generated by default as identity,
    duration_seconds          bigint                      not null,
    end_time                  timestamp(6) with time zone not null,
    estimated_distance_meters float(53),
    start_time                timestamp(6) with time zone not null,
    transport_mode_inferred   varchar(255),
    end_place_id              bigint,
    start_place_id            bigint,
    user_id                   bigint                      not null,
    primary key (id)
);
create table users
(
    id           bigint generated by default as identity,
    display_name varchar(255) not null,
    username     varchar(255) not null,
    primary key (id)
);
create table visits
(
    id               bigint generated by default as identity,
    duration_seconds bigint                      not null,
    end_time         timestamp(6) with time zone not null,
    start_time       timestamp(6) with time zone not null,
    place_id         bigint                      not null,
    user_id          bigint                      not null,
    primary key (id)
);
alter table if exists users
    drop constraint if exists UKr43af9ap4edm43mmtq01oddj6;
alter table if exists users
    add constraint UKr43af9ap4edm43mmtq01oddj6 unique (username);
alter table if exists raw_location_points
    add constraint FKp2ffgs5y6vk7eul2x4x5topr7
        foreign key (user_id)
            references users;
alter table if exists significant_places
    add constraint FK2cx1himtc2aapqh5yc6cvuwee
        foreign key (user_id)
            references users;
alter table if exists trips
    add constraint FK88p5fhq7aynk5n7cem6tgk556
        foreign key (end_place_id)
            references significant_places;
alter table if exists trips
    add constraint FKb3mmvmk5hbu58jq5hhse0ln0d
        foreign key (start_place_id)
            references significant_places;
alter table if exists trips
    add constraint FK8wb14dx6ed0bpp3planbay88u
        foreign key (user_id)
            references users;
alter table if exists visits
    add constraint FKhkvqtop7xrbnkpjqyqickus6c
        foreign key (place_id)
            references significant_places;
alter table if exists visits
    add constraint FK5kmnbgokfpcalwrminoedrb68
        foreign key (user_id)
            references users;