create table if not exists public.users
(
    id                       uuid                                     not null
        constraint users_pkey
            primary key,
    email                    varchar(100)
        constraint users_email_key
            unique,
    password                 bytea,
    created_at               timestamp with time zone                 not null,
    updated_at               timestamp with time zone                 not null,
    last_auth_at             timestamp with time zone,
    location                 varchar default 'UTC'::character varying not null,
    mute_email               boolean default false                    not null,
    locale                   varchar default 'en'::character varying  not null,
    public_key               text                                     not null,
    private_key              text                                     not null,
    name                     varchar                                  not null,
    display_name             varchar                                  not null,
    about                    text                                     not null,
    accept_followers         boolean default true                     not null,
    actor_id                 uuid                                     not null,
    reply_collection_updates boolean default false                    not null
);

create unique index if not exists users_username_uindex
    on public.users (name);

create table if not exists public.actors
(
    id         uuid                                   not null
        constraint actors_pk
            primary key,
    actor_id   varchar                                not null
        constraint actors_actor_id
            unique,
    payload    jsonb                                  not null,
    created_at timestamp with time zone               not null,
    updated_at timestamp with time zone default now() not null
);

create table if not exists public.peers
(
    id         uuid                     not null
        constraint peers_pk
            primary key,
    inbox      varchar(100)             not null,
    created_at timestamp with time zone not null
);

create unique index if not exists peers_inbox_uindex
    on public.peers (inbox);

create table if not exists public.images
(
    id           uuid              not null
        constraint asset_image_pk
            primary key,
    location     varchar(200)      not null,
    checksum     varchar(200)      not null,
    content_type integer default 0 not null,
    size         integer default 0 not null,
    height       integer default 0 not null,
    width        integer default 0 not null,
    blur         varchar(100)      not null,
    created_at   timestamp with time zone
);

create unique index if not exists asset_image_checksum_uindex
    on public.images (checksum);

create unique index if not exists asset_image_location_uindex
    on public.images (location);

create table if not exists public.image_aliases
(
    id         uuid                     not null
        constraint image_aliases_pk
            primary key,
    image_id   uuid                     not null,
    alias      varchar(200)             not null,
    created_at timestamp with time zone not null
);

create unique index if not exists image_aliases_pairs
    on public.image_aliases (image_id, alias);

create table if not exists public.schema_migrations
(
    version bigint  not null
        constraint schema_migrations_pkey
            primary key,
    dirty   boolean not null
);

create table if not exists public.network_graph
(
    id                  uuid                     not null
        constraint network_graph_pk
            primary key,
    user_id             uuid                     not null,
    actor_id            uuid                     not null,
    activity            jsonb                    not null,
    relationship_type   integer default 0        not null,
    relationship_status integer default 0        not null,
    created_at          timestamp with time zone not null,
    updated_at          timestamp with time zone not null,
    constraint network_graph_user_actor_rel
        unique (user_id, actor_id, relationship_type)
);

create table if not exists public.actor_aliases
(
    id         uuid                     not null
        constraint actor_subjects_pk
            primary key,
    actor_id   uuid                     not null,
    alias      varchar                  not null,
    created_at timestamp with time zone not null,
    updated_at timestamp with time zone not null,
    alias_type integer default 0        not null
);

create unique index if not exists actor_aliases_alias_uindex
    on public.actor_aliases (alias);

create table if not exists public.actor_keys
(
    id         uuid                     not null
        constraint actor_keys_pk
            primary key,
    actor_id   uuid                     not null,
    key_id     varchar                  not null,
    pem        text                     not null,
    created_at timestamp with time zone not null,
    updated_at timestamp with time zone not null,
    constraint actor_keys_lookup
        unique (actor_id, key_id)
);

create table if not exists public.objects
(
    id         uuid                     not null
        constraint objects_pk
            primary key,
    created_at timestamp with time zone not null,
    updated_at timestamp with time zone not null,
    payload    jsonb                    not null,
    object_id  varchar                  not null
        constraint objects_object_uindex
            unique
);

create table if not exists public.object_events
(
    id          uuid                     not null
        constraint object_events_pk
            primary key,
    created_at  timestamp with time zone not null,
    updated_at  timestamp with time zone not null,
    activity_id varchar                  not null
        constraint object_events_activity_uindex
            unique,
    object_id   uuid                     not null,
    payload     jsonb                    not null
);

create table if not exists public.user_object_events
(
    id          uuid                     not null
        constraint user_object_events_pk
            primary key,
    created_at  timestamp with time zone not null,
    updated_at  timestamp with time zone not null,
    user_id     uuid                     not null,
    activity_id uuid                     not null,
    object_id   uuid                     not null,
    public      boolean default false    not null,
    constraint user_object_events_user_activity_uindex
        unique (user_id, activity_id, object_id)
);

create table if not exists public.object_tags
(
    id         uuid                     not null
        constraint object_tags_pk
            primary key,
    created_at timestamp with time zone not null,
    updated_at timestamp with time zone not null,
    object_id  uuid                     not null,
    tag        varchar                  not null,
    constraint object_tags_tagged_uindex
        unique (object_id, tag)
);

create table if not exists public.user_feed
(
    id          uuid                     not null
        constraint user_feed_pk
            primary key,
    created_at  timestamp with time zone not null,
    updated_at  timestamp with time zone not null,
    activity_id uuid                     not null,
    object_id   uuid                     not null,
    user_id     uuid                     not null,
    constraint user_feed_activity_uindex
        unique (user_id, activity_id, object_id)
);

create table if not exists public.object_replies
(
    id               uuid                     not null
        constraint object_replies_pk
            primary key,
    created_at       timestamp with time zone not null,
    updated_at       timestamp with time zone not null,
    object_id        uuid                     not null,
    parent_object_id uuid                     not null,
    constraint object_replies_reply_uindex
        unique (object_id, parent_object_id)
);

create table if not exists public.threads
(
    id          uuid                     not null
        constraint threads_pk
            primary key,
    created_at  timestamp with time zone not null,
    updated_at  timestamp with time zone not null,
    thread_hash varchar                  not null
        constraint threads_uindex
            unique
);

create table if not exists public.object_threads
(
    id         uuid                     not null
        constraint object_threads_pk
            primary key,
    created_at timestamp with time zone not null,
    updated_at timestamp with time zone not null,
    object_id  uuid                     not null,
    thread_id  uuid                     not null,
    constraint object_threads_uindex
        unique (object_id, thread_id)
);

create table if not exists public.object_boosts
(
    id          uuid                     not null
        constraint object_boosts_pk
            primary key,
    created_at  timestamp with time zone not null,
    updated_at  timestamp with time zone not null,
    actor_id    uuid                     not null,
    activity_id uuid                     not null,
    object_id   uuid                     not null
);