1 files changed, 128 insertions, 0 deletions
diff --git a/internal/store/menu.go b/internal/store/menu.go
new file mode 100644
index 0000000..2d3eabd
--- /dev/null
+++ b/internal/store/menu.go
@@ -0,0 +1,128 @@
+package store
+
+import (
+	"context"
+
+	"github.com/jackc/pgx/v5/pgconn"
+)
+
+// MenuItem is one entry of a blog's menu: either a page of the blog or a custom link.
+type MenuItem struct {
+	ID        int64
+	BlogID    int64
+	PageID    *int64 // nil for custom links
+	Label     string // custom links only; pages use their title
+	URL       string // custom links only
+	SortOrder int
+	// joined from pages
+	PageTitle string
+	PageSlug  string
+	IsHome    bool
+}
+
+// Text is what the visitor reads.
+func (m MenuItem) Text() string {
+	if m.PageID != nil {
+		return m.PageTitle
+	}
+	return m.Label
+}
+
+// Href is where the entry points.
+func (m MenuItem) Href() string {
+	switch {
+	case m.PageID == nil:
+		return m.URL
+	case m.IsHome:
+		return "/"
+	}
+	return "/" + m.PageSlug
+}
+
+const menuCols = `m.id, m.blog_id, m.page_id, m.label, m.url, m.sort_order, coalesce(p.title,''), coalesce(p.slug,''), coalesce(p.is_home,false)`
+
+func (s *Store) ListMenu(ctx context.Context, blogID int64) ([]MenuItem, error) {
+	rows, err := s.db.Query(ctx, `SELECT `+menuCols+` FROM menu_items m LEFT JOIN pages p ON p.id=m.page_id
+		WHERE m.blog_id=$1 ORDER BY m.sort_order, m.id`, blogID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var out []MenuItem
+	for rows.Next() {
+		var m MenuItem
+		if err := rows.Scan(&m.ID, &m.BlogID, &m.PageID, &m.Label, &m.URL, &m.SortOrder, &m.PageTitle, &m.PageSlug, &m.IsHome); err != nil {
+			return nil, err
+		}
+		out = append(out, m)
+	}
+	return out, rows.Err()
+}
+
+// AddMenuPage appends a page to the menu; a page already in the menu is left where it is.
+func (s *Store) AddMenuPage(ctx context.Context, blogID, pageID int64) error {
+	return addMenuPage(ctx, s.db, blogID, pageID)
+}
+
+func addMenuPage(ctx context.Context, db querier, blogID, pageID int64) error {
+	_, err := db.Exec(ctx, `INSERT INTO menu_items (blog_id, page_id, sort_order)
+		SELECT $1, id, (SELECT coalesce(max(sort_order),-1)+1 FROM menu_items WHERE blog_id=$1)
+		FROM pages WHERE blog_id=$1 AND id=$2 ON CONFLICT (page_id) WHERE page_id IS NOT NULL DO NOTHING`, blogID, pageID)
+	return err
+}
+
+// SetPageInMenu adds or removes a page's menu entry.
+func (s *Store) SetPageInMenu(ctx context.Context, blogID, pageID int64, on bool) error {
+	if on {
+		return s.AddMenuPage(ctx, blogID, pageID)
+	}
+	_, err := s.db.Exec(ctx, `DELETE FROM menu_items WHERE blog_id=$1 AND page_id=$2`, blogID, pageID)
+	return err
+}
+
+// AddMenuLink appends a custom link to the menu.
+func (s *Store) AddMenuLink(ctx context.Context, blogID int64, label, url string) error {
+	_, err := s.db.Exec(ctx, `INSERT INTO menu_items (blog_id, label, url, sort_order)
+		VALUES ($1,$2,$3,(SELECT coalesce(max(sort_order),-1)+1 FROM menu_items WHERE blog_id=$1))`, blogID, label, url)
+	return err
+}
+
+func (s *Store) DeleteMenuItem(ctx context.Context, blogID, id int64) error {
+	_, err := s.db.Exec(ctx, `DELETE FROM menu_items WHERE blog_id=$1 AND id=$2`, blogID, id)
+	return err
+}
+
+// MoveMenuItem swaps sort_order with the neighbouring entry (dir = -1 up, +1 down).
+func (s *Store) MoveMenuItem(ctx context.Context, blogID, id int64, dir int) error {
+	items, err := s.ListMenu(ctx, blogID)
+	if err != nil {
+		return err
+	}
+	idx := -1
+	for i := range items {
+		if items[i].ID == id {
+			idx = i
+		}
+	}
+	j := idx + dir
+	if idx < 0 || j < 0 || j >= len(items) {
+		return nil
+	}
+	items[idx], items[j] = items[j], items[idx]
+	tx, err := s.db.Begin(ctx)
+	if err != nil {
+		return err
+	}
+	defer tx.Rollback(ctx)
+	for i, m := range items { // renumber everything; keeps orders dense
+		if _, err := tx.Exec(ctx, `UPDATE menu_items SET sort_order=$3 WHERE blog_id=$1 AND id=$2`, blogID, m.ID, i); err != nil {
+			return err
+		}
+	}
+	return tx.Commit(ctx)
+}
+
+// querier is what the helpers shared with page transactions need: the pool or a pgx.Tx.
+type querier interface {
+	Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error)
+}