--
-- Maintains the modified_at in a table
--

create or replace function update_modified_at_column()
    returns trigger as $$
begin
    NEW.modified_at = now();
    return NEW;
end;
$$ language 'plpgsql';


--
-- Users, Passwords
--

create type user_principal_status_type as enum (
    'Active',
    'Suspended',
    'Pending Approval'
    );


create table user_principal (
    acct varchar not null primary key, -- user_name@host_name
    auth_data jsonb not null,
    last_seen_data jsonb default '[]'::jsonb not null,
    last_login_data jsonb default '[]'::jsonb not null,
    system_data jsonb,
    preferences jsonb,
    status user_principal_status_type default 'Pending Approval' not null,
    system_permissions jsonb default '[]'::jsonb not null,
    created_at timestamptz default now() not null,
    modified_at timestamptz default now() not null
);

create index on user_principal(created_at); -- used for pagination

create trigger update_userprincipal_modified_at
    before update on user_principal
    for each row execute procedure update_modified_at_column();

--
-- Site Info
--

create table site_info (
    host_name varchar,
    site_data jsonb not null,
    created_at timestamptz default now() not null,
    modified_at timestamptz default now() not null,
    primary key (host_name)
);

create trigger update_siteinfo_modified_at
    before update on site_info
    for each row execute procedure update_modified_at_column();


--
-- System Roles
--

create type system_role_type as enum(
    'User Administrator',
    'Actor Administrator',
    'Content Administrator'
    );

create table system_role (
    role_type system_role_type not null,
    member varchar not null references user_principal(acct),
    host_name varchar null references site_info(host_name),
    created_at timestamptz default now() not null,
    modified_at timestamptz default now() not null,
    primary key(role_type, member, host_name)
);

create or replace function make_system_permissions()
    returns trigger as $$
declare
    user_acct varchar;
    user_system_permissions jsonb;
begin
    if (tg_op = 'DELETE') then
        user_acct = OLD.member;
    else
        user_acct = NEW.member;
    end if;
    select into user_system_permissions 
        jsonb_agg(
            jsonb_build_object(
                'role', role_type,
                'host_name', host_name
            )
        )
    from system_role
    where member=user_acct group by member;
    if (user_system_permissions IS NULL) then
        user_system_permissions := '[]'::jsonb;
    end if;
    update user_principal
    set system_permissions=user_system_permissions
    where acct=user_acct;
    return null;
end
$$ language 'plpgsql';

create trigger make_system_permissions
    after update or insert or delete on system_role
    for each row execute procedure make_system_permissions();

create index on system_role(role_type, host_name);

--
-- Actors
--

create type actor_visibility_type as enum (
    'Visible', -- publicly visible and discoverable
    'Invisible', -- visible only to the user and administrators
    'Banned' -- visible only to the administrators
    );

-- should mirror activity pub actor types
create type actor_type as enum (
    'Application',
    'Group',
    'Organization',
    'Person',
    'Service'
    );

create table actor (
    id varchar primary key,
    type actor_type not null,
    acct varchar unique,
    webfinger_json jsonb,
    activity_pub_json jsonb not null,
    owner_data jsonb,
    private_key_pem varchar,
    visibility actor_visibility_type default 'Visible',
    system_data jsonb,
    created_at timestamptz default now() not null,
    modified_at timestamptz default now() not null
);

create index on actor(modified_at); -- used for pagination

create trigger update_actor_modified_at
    before update on actor
    for each row execute procedure update_modified_at_column();

create table actor_owner (
    actor_id varchar references actor(id) not null,
    user_owner varchar references user_principal(acct) not null,
    default_actor boolean default false,
    created_at timestamptz default now() not null,
    modified_at timestamptz default now() not null,
    primary key (actor_id, user_owner)
);

-- creates a unique index for the default actor allowing multiple false
create unique index on actor_owner(user_owner, default_actor) where (default_actor);

create trigger update_actor_owner_modified_at
    before update on actor_owner
    for each row execute procedure update_modified_at_column();

--
-- Actor Reference
--

-- represents the type of reference
create type actor_reference_type as enum (
    'Following',
    'Follower',
    'Liked',
    'Likes',
    'Member'
);

create table actor_reference (
    subject_actor_id varchar references actor(id),
    object_actor_id varchar references actor(id),
    reference_type actor_reference_type not null,
    created_at timestamptz default now() not null,
    modified_at timestamptz default now() not null,
    primary key (subject_actor_id, object_actor_id, reference_type)
);

create trigger update_actor_reference_modified_at
    before update on actor_reference
    for each row execute procedure update_modified_at_column();

--
-- User Actor Data
-- allows users to keep arbitrary data about actors
--

create table user_actor_data (
    actor_id varchar references actor(id)             not null,
    user_acct varchar references user_principal(acct) not null,
    owner_data jsonb                                  not null,
    created_at timestamptz default now()              not null,
    modified_at timestamptz default now()             not null,
    primary key (actor_id, user_acct)
);

create trigger update_user_actor_data_modified_at
    before update on user_actor_data
    for each row execute procedure update_modified_at_column();

--
-- Job Queues
--

create type job_type as enum (
    'No Operation',
    'Make Thumbnail',
    'Resize Actor Icon',
    'Resize Actor Banner',
    'Fetch Remote Icon',
    'Fetch Remote Banner',
    'Send Activity Pub'
    );

create table job_queue(
    id integer generated always as identity primary key,
    job_type job_type not null,
    details jsonb not null,
    attempts_remaining int default 1 not null,
    no_sooner_than timestamptz default now() not null,
    created_at timestamptz default now() not null
);

create index on job_queue(job_type, attempts_remaining, no_sooner_than);

--
-- AP Object - activitypub objects that can be "posted"
--

-- these should mirror the ActivityPub object type names
create type ap_object_type as enum (
    'Note',
    'Article',
    'Document',
    'Image',
    'Video',
    'Audio',
    'Page'
);

create type ap_object_visibility_type as enum (
    'Visible',
    'Invisible'
);

create table ap_object (
    id varchar primary key,
    type ap_object_type not null,
    actor_owner varchar references actor(id),
    received_for_host varchar references site_info(host_name) not null,
    activity_pub_json jsonb not null,
    owner_data jsonb,
    visibility ap_object_visibility_type not null default 'Visible',
    system_data jsonb,
    created_at timestamptz default now() not null,
    modified_at timestamptz default now() not null
);

create index on ap_object(modified_at, received_for_host); -- used for pagination
create index on ap_object(modified_at, actor_owner); -- used for pagination
create index on ap_object(modified_at, actor_owner, type); -- used for pagination

-- these expression indexes support queries such as
-- select activity_pub_json from ap_object where activity_pub_json -> 'to' ? 'http://example.com/foo';
create index on ap_object USING gin ((activity_pub_json->'to'));
create index on ap_object USING gin ((activity_pub_json->'bto'));
create index on ap_object USING gin ((activity_pub_json->'cc'));
create index on ap_object USING gin ((activity_pub_json->'bcc'));
create index on ap_object USING gin ((activity_pub_json->'actor'));
create index on ap_object USING gin ((activity_pub_json->'attributedTo'));

create trigger update_ap_object_modified_at
    before update on ap_object
    for each row execute procedure update_modified_at_column();

--
-- AP Object Reference
--

-- represents the type of reference
create type ap_object_reference_type as enum (
    'Dislike',
    'Flag',
    'Like',
    'Read'
    );

create table ap_object_reference (
    actor_id varchar references actor(id),
    ap_object_id varchar references ap_object(id),
    reference_type ap_object_reference_type not null,
    created_at timestamptz default now() not null,
    modified_at timestamptz default now() not null,
    primary key (actor_id, ap_object_id, reference_type)
);

create trigger update_ap_object_reference_modified_at
    before update on ap_object_reference
    for each row execute procedure update_modified_at_column();


--
-- User AP Object Data
-- allows users to keep arbitrary data about AP objects
--

create table user_ap_object_data (
    ap_object_id varchar references ap_object(id)     not null,
    user_acct varchar references user_principal(acct) not null,
    owner_data jsonb                                  not null,
    created_at timestamptz default now()              not null,
    modified_at timestamptz default now()             not null,
    primary key (ap_object_id, user_acct)
);

create trigger update_user_ap_object_data_modified_at
    before update on user_ap_object_data
    for each row execute procedure update_modified_at_column();