-- +goose Up
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()
);

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,62}$'),
    title       text NOT NULL,
    tagline     text NOT NULL DEFAULT '',
    theme       jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE pages (
    id           bigserial PRIMARY KEY,
    blog_id      bigint NOT NULL REFERENCES blogs(id) ON DELETE CASCADE,
    slug         text NOT NULL,
    title        text NOT NULL,
    intro_md     text NOT NULL DEFAULT '',
    intro_html   text NOT NULL DEFAULT '',
    nav_order    integer NOT NULL DEFAULT 0,
    show_in_nav  boolean NOT NULL DEFAULT true,
    is_home      boolean NOT NULL DEFAULT false,
    created_at   timestamptz NOT NULL DEFAULT now(),
    UNIQUE (blog_id, slug)
);
CREATE UNIQUE INDEX pages_one_home_per_blog ON pages (blog_id) WHERE is_home;

CREATE TABLE posts (
    id          bigserial PRIMARY KEY,
    page_id     bigint NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
    slug        text NOT NULL,
    title       text NOT NULL,
    body_md     text NOT NULL DEFAULT '',
    body_html   text NOT NULL DEFAULT '',
    published   boolean NOT NULL DEFAULT true,
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now(),
    UNIQUE (page_id, slug)
);
CREATE INDEX posts_page_created ON posts (page_id, created_at DESC);

CREATE TABLE images (
    id            uuid PRIMARY KEY,
    blog_id       bigint NOT NULL REFERENCES blogs(id) ON DELETE CASCADE,
    filename      text NOT NULL,
    content_type  text NOT NULL,
    size          integer NOT NULL,
    data          bytea NOT NULL,
    created_at    timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX images_blog ON images (blog_id, created_at DESC);

-- +goose Down
DROP TABLE images;
DROP TABLE posts;
DROP TABLE pages;
DROP TABLE blogs;
DROP TABLE users;