create table preview_processed_visits
(
    id                 bigserial primary key,
    user_id            bigint                   not null,
    place_id           bigint                   not null,
    start_time         timestamp with time zone not null,
    end_time           timestamp with time zone not null,
    duration_seconds   bigint                   not null,
    version            bigint                   not null DEFAULT 0,
    preview_id         VARCHAR(36)              NOT NULL DEFAULT '',
    preview_created_at TIMESTAMP                         DEFAULT now(),
    constraint unique_processed_visit_start_end_user unique (start_time, end_time, user_id, preview_id)
);

create table preview_raw_location_points
(
    id                 bigint generated by default as identity primary key,
    accuracy_meters    double precision            not null,
    timestamp          timestamp(6) with time zone not null,
    user_id            bigint                      not null,
    geom               geometry(Point, 4326),
    processed          boolean                              default false,
    version            bigint                               default 0 not null,
    preview_id         VARCHAR(36)                 NOT NULL DEFAULT '',
    preview_created_at TIMESTAMP                            DEFAULT now()
);

create table preview_trips
(
    id                        bigint generated by default as identity primary key,
    duration_seconds          bigint                      not null,
    end_time                  timestamp(6) with time zone not null,
    estimated_distance_meters double precision,
    start_time                timestamp(6) with time zone not null,
    transport_mode_inferred   varchar(255),
    user_id                   bigint                      not null,
    travelled_distance_meters double precision,
    start_visit_id            bigint                      not null
        references preview_processed_visits (id) on delete cascade,
    end_visit_id              bigint                      not null
        references preview_processed_visits (id) on delete cascade,
    version                   bigint                               default 0 not null,
    preview_id                VARCHAR(36)                 NOT NULL DEFAULT '',
    preview_created_at        TIMESTAMP                            DEFAULT now(),
    constraint unique_trip_start_end_user unique (start_time, end_time, user_id, preview_id)
);

create table preview_visits
(
    id                 bigint generated by default as identity primary key,
    duration_seconds   bigint                      not null,
    end_time           timestamp(6) with time zone not null,
    start_time         timestamp(6) with time zone not null,
    user_id            bigint                      not null,
    processed          boolean                              default false,
    latitude           double precision,
    longitude          double precision,
    version            bigint                               default 0 not null,
    preview_id         VARCHAR(36)                 NOT NULL DEFAULT '',
    preview_created_at TIMESTAMP                            DEFAULT now(),
    constraint unique_visit_start_end_user unique (start_time, end_time, user_id, preview_id)
);

CREATE TABLE preview_visit_detection_parameters (LIKE visit_detection_parameters);


ALTER TABLE preview_visit_detection_parameters ADD COLUMN preview_id VARCHAR(36) NOT NULL DEFAULT '';
ALTER TABLE preview_visit_detection_parameters ADD COLUMN preview_created_at TIMESTAMP DEFAULT now();

ALTER TABLE preview_visit_detection_parameters ALTER id ADD GENERATED ALWAYS AS IDENTITY;

