create database insights;

\c insights;

create table amplitude_events
(
    server_received_time      timestamp,
    app                       integer,
    device_carrier            varchar(65535),
    "$schema"                 integer,
    city                      varchar(65535),
    user_id                   integer,
    uuid                      varchar(65535),
    event_time                timestamp,
    platform                  varchar(65535),
    os_version                varchar(65535),
    amplitude_id              bigint,
    processed_time            timestamp,
    user_creation_time        timestamp,
    version_name              varchar(65535),
    ip_address                varchar(65535),
    paying                    boolean,
    dma                       varchar(65535),
    group_properties          varchar(65535),
    user_properties           varchar(65535),
    client_upload_time        timestamp,
    "$insert_id"              varchar(65535),
    event_type                varchar(65535),
    library                   varchar(65535),
    amplitude_attribution_ids varchar(65535),
    device_type               varchar(65535),
    device_manufacturer       varchar(65535),
    start_version             varchar(65535),
    location_lng              double precision,
    server_upload_time        timestamp,
    event_id                  integer not null,
    location_lat              double precision,
    os_name                   varchar(65535),
    amplitude_event_type      varchar(65535),
    device_brand              varchar(65535),
    groups                    varchar(65535),
    event_properties          varchar(65535),
    data                      varchar(65535),
    device_id                 varchar(65535),
    language                  varchar(65535),
    device_model              varchar(65535),
    country                   varchar(65535),
    region                    varchar(65535),
    is_attribution_event      boolean,
    adid                      varchar(65535),
    session_id                bigint,
    device_family             varchar(65535),
    sample_rate               integer,
    idfa                      varchar(65535),
    client_event_time         timestamp,
    instance_id               integer,
    old_instance_id           varchar
);

create table api_events
(
    uuid        varchar,
    instance_id integer,
    user_id     integer,
    event_type  varchar,
    timestamp   timestamp,
    ip          varchar,
    agent       varchar,
    platform    varchar,
    internal    boolean,
    actor_id    integer
);

CREATE TABLE department_members
(
    last_sync     TIMESTAMP,
    department_id INT,
    user_id       INT,
    instance_id   INT
);

CREATE TABLE group_post_events
(
    uuid                 VARCHAR(36),
    instance_id          INT,
    user_id              INT,
    group_id             INT,
    resource_id          INT,
    resource_type        VARCHAR(32),
    parent_id            INT,
    parent_type          VARCHAR(32),
    timestamp            TIMESTAMP,
    deleted_at           TIMESTAMP,
    deleted_by_user_id   INT
);

CREATE TABLE feed_post_events
(
    uuid                 VARCHAR(36),
    instance_id          INT,
    user_id              INT,
    resource_id          INT,
    resource_type        VARCHAR(32),
    parent_id            INT,
    parent_type          VARCHAR(32),
    timestamp            TIMESTAMP,
    deleted_at           TIMESTAMP,
    deleted_by_user_id   INT
);

create table daily_active_users
(
    user_id     integer        not null,
    instance_id integer        not null,
    date        date           not null,
    platforms   varchar(100)[] not null,
    modules     varchar(100)[],
    events      varchar(100)[],
    primary key (user_id, instance_id, date)
);

create index daily_active_users_instance_id_date_index
    on daily_active_users (instance_id, date);