-- +goose Up
-- One database per blog: nothing here carries a blog id, the database is the scope.
CREATE TABLE settings (
    id          boolean PRIMARY KEY DEFAULT true CHECK (id), -- exactly one row
    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,
    slug         text NOT NULL UNIQUE,
    title        text NOT NULL,
    intro_md     text NOT NULL DEFAULT '',
    intro_html   text NOT NULL DEFAULT '',
    nav_order    integer NOT NULL DEFAULT 0,
    is_home      boolean NOT NULL DEFAULT false,
    created_at   timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX pages_one_home ON pages ((true)) 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,
    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_created ON images (created_at DESC);

-- Announcements: blog-wide notices shown on every page and post.
CREATE TABLE sections (
    id          bigserial PRIMARY KEY,
    title       text NOT NULL DEFAULT '',
    body_md     text NOT NULL DEFAULT '',
    body_html   text NOT NULL DEFAULT '',
    placement   text NOT NULL DEFAULT 'main-top'
                CHECK (placement IN ('left-top', 'left-bottom', 'main-top', 'main-bottom', 'right-top', 'right-bottom')),
    style       text NOT NULL DEFAULT 'note' CHECK (style IN ('plain', 'note', 'warning')),
    enabled     boolean NOT NULL DEFAULT true,
    sort_order  integer NOT NULL DEFAULT 0,
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX sections_order ON sections (sort_order, id);

-- Layout modules: what each area of a blog (header, columns, footer) shows.
CREATE TABLE modules (
    id          bigserial PRIMARY KEY,
    area        text NOT NULL CHECK (area IN ('header', 'left', 'right', 'above', 'below', 'footer')),
    kind        text NOT NULL CHECK (kind IN ('title', 'logo', 'menu', 'archive', 'recent', 'html', 'rss', 'text', 'sitemap')),
    title       text NOT NULL DEFAULT '',
    body        text NOT NULL DEFAULT '',
    count       integer NOT NULL DEFAULT 5,
    sort_order  integer NOT NULL DEFAULT 0,
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX modules_area ON modules (area, sort_order, id);

-- The menu: blog pages and custom links in one ordered list.
CREATE TABLE menu_items (
    id          bigserial PRIMARY KEY,
    page_id     bigint REFERENCES pages(id) ON DELETE CASCADE,
    label       text NOT NULL DEFAULT '',
    url         text NOT NULL DEFAULT '',
    sort_order  integer NOT NULL DEFAULT 0,
    CHECK (page_id IS NOT NULL OR url <> '')
);
CREATE UNIQUE INDEX menu_items_page ON menu_items (page_id) WHERE page_id IS NOT NULL;
CREATE INDEX menu_items_order ON menu_items (sort_order, id);

-- +goose Down
DROP TABLE menu_items, modules, sections, images, posts, pages, settings;