-- 2023-04-04: Create initial tables

-- +migrate Up

-- User accounts
-- Many fields in this table are only relevant for local users, but all blogs are associated with an account
-- to make the database schema simpler.
create table accounts (
    id       text primary key,
    username text not null,
    domain   text, -- if null, is a local account
    email    text,
    password bytea,

    unique (username, domain)
);

-- Blogs
-- These are the AP actors.
create table blogs (
    id     text primary key,
    name   text not null,
    domain text, -- if null, is a local blog
    bio    text not null,

    account_id text not null references accounts (id) on delete cascade,

    unique (name, domain)
);

create type post_visibility as enum ('public', 'unlisted', 'followers', 'direct');

create table posts (
    id         text primary key,
    blog_id    text not null references blogs (id) on delete cascade,
    content    text,
    source     text,
    visibility post_visibility not null
);

create table applications (
    id text primary key,
    name text not null
);

create table tokens (
    id      text primary key,
    app_id  text not null references applications (id) on delete cascade,
    user_id text not null references accounts (id) on delete cascade,
    scopes  text[] not null default array[]::text[],
    expires timestamptz not null
);

create table config (
    id int primary key not null default 1,
    name text not null, -- instance name
    admin_id text references blogs (id) on delete set null, -- admin contact
    internal_application text references applications (id) on delete set null,
    constraint singleton check (id = 1) -- only one config table entry
);

-- +migrate Down
drop table config;
drop table tokens;
drop table applications;
drop table accounts;
drop table blogs;
drop table posts;