-- +goose Up
-- The control database: accounts and the list of blogs. Everything a blog
-- contains lives in its own database (see migrations/blog).
CREATE TABLE users (
    id             bigserial PRIMARY KEY,
    username       text NOT NULL UNIQUE,
    password_hash  text NOT NULL,
    role           text NOT NULL CHECK (role IN ('superadmin', 'blogger')),
    disabled       boolean NOT NULL DEFAULT false,
    token_version  integer NOT NULL DEFAULT 0,
    created_at     timestamptz NOT NULL DEFAULT now()
);

-- "blog_" + subdomain must fit a 63-char Postgres identifier, hence 58.
CREATE TABLE blogs (
    id          bigserial PRIMARY KEY,
    owner_id    bigint NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
    subdomain   text NOT NULL UNIQUE CHECK (subdomain ~ '^[a-z0-9](-?[a-z0-9]){0,57}$'),
    db_name     text NOT NULL UNIQUE,
    created_at  timestamptz NOT NULL DEFAULT now()
);

-- +goose Down
DROP TABLE blogs;
DROP TABLE users;