1 files changed, 56 insertions, 0 deletions
diff --git a/internal/db/migrations/00003_layout.sql b/internal/db/migrations/00003_layout.sql
new file mode 100644
index 0000000..e8fff23
--- /dev/null
+++ b/internal/db/migrations/00003_layout.sql
@@ -0,0 +1,56 @@
+-- +goose Up
+-- Layout modules: what each area of a blog (header, columns, footer) shows.
+CREATE TABLE modules (
+    id          bigserial PRIMARY KEY,
+    blog_id     bigint NOT NULL REFERENCES blogs(id) ON DELETE CASCADE,
+    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_blog ON modules (blog_id, area, sort_order, id);
+
+-- The menu: blog pages and custom links in one ordered list.
+CREATE TABLE menu_items (
+    id          bigserial PRIMARY KEY,
+    blog_id     bigint NOT NULL REFERENCES blogs(id) ON DELETE CASCADE,
+    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_blog ON menu_items (blog_id, sort_order, id);
+
+-- Existing blogs keep the menu they had: pages marked "show in menu", in menu
+-- order, with the home page only if the theme included it.
+INSERT INTO menu_items (blog_id, page_id, sort_order)
+SELECT p.blog_id, p.id, row_number() OVER (PARTITION BY p.blog_id ORDER BY p.nav_order, p.id) - 1
+FROM pages p JOIN blogs b ON b.id = p.blog_id
+WHERE p.show_in_nav AND (NOT p.is_home OR coalesce((b.theme->>'nav_show_home')::boolean, true));
+ALTER TABLE pages DROP COLUMN show_in_nav;
+
+-- ...and the layout their theme described.
+INSERT INTO modules (blog_id, area, kind, sort_order)
+SELECT id, 'header', 'title', CASE WHEN theme->>'nav_position' = 'top-bar' THEN 1 ELSE 0 END
+FROM blogs WHERE coalesce((theme->>'header_show_title')::boolean, true);
+INSERT INTO modules (blog_id, area, kind, sort_order)
+SELECT id, 'header', 'menu', CASE WHEN theme->>'nav_position' = 'top-bar' THEN 0 ELSE 1 END
+FROM blogs WHERE coalesce(theme->>'nav_position', 'below-header') <> 'left-sidebar';
+INSERT INTO modules (blog_id, area, kind, sort_order)
+SELECT id, 'left', 'menu', 0 FROM blogs WHERE theme->>'nav_position' = 'left-sidebar';
+INSERT INTO modules (blog_id, area, kind, body, sort_order)
+SELECT id, 'footer', 'text', theme->>'footer_text', 0 FROM blogs WHERE coalesce(theme->>'footer_text', '') <> '';
+INSERT INTO modules (blog_id, area, kind, sort_order)
+SELECT id, 'footer', 'rss', 1 FROM blogs WHERE coalesce((theme->>'footer_show_rss')::boolean, true);
+
+-- +goose Down
+ALTER TABLE pages ADD COLUMN show_in_nav boolean NOT NULL DEFAULT true;
+UPDATE pages SET show_in_nav = EXISTS (SELECT 1 FROM menu_items m WHERE m.page_id = pages.id);
+DROP TABLE menu_items;
+DROP TABLE modules;