1 files changed, 124 insertions, 0 deletions
diff --git a/internal/store/pages.go b/internal/store/pages.go
new file mode 100644
index 0000000..87d440f
--- /dev/null
+++ b/internal/store/pages.go
@@ -0,0 +1,124 @@
+package store
+
+import (
+	"context"
+	"time"
+)
+
+type Page struct {
+	ID        int64
+	BlogID    int64
+	Slug      string
+	Title     string
+	IntroMD   string
+	IntroHTML string
+	NavOrder  int
+	ShowInNav bool
+	IsHome    bool
+	CreatedAt time.Time
+	PostCount int // filled by ListPages only
+}
+
+const pageCols = `id, blog_id, slug, title, intro_md, intro_html, nav_order, show_in_nav, is_home, created_at`
+
+func scanPage(row interface{ Scan(...any) error }) (*Page, error) {
+	var p Page
+	err := row.Scan(&p.ID, &p.BlogID, &p.Slug, &p.Title, &p.IntroMD, &p.IntroHTML, &p.NavOrder, &p.ShowInNav, &p.IsHome, &p.CreatedAt)
+	if err != nil {
+		return nil, wrap(err)
+	}
+	return &p, nil
+}
+
+func (s *Store) ListPages(ctx context.Context, blogID int64) ([]Page, error) {
+	rows, err := s.db.Query(ctx, `SELECT `+pageCols+`, (SELECT count(*) FROM posts WHERE page_id=pages.id)
+		FROM pages WHERE blog_id=$1 ORDER BY nav_order, id`, blogID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var out []Page
+	for rows.Next() {
+		var p Page
+		if err := rows.Scan(&p.ID, &p.BlogID, &p.Slug, &p.Title, &p.IntroMD, &p.IntroHTML, &p.NavOrder, &p.ShowInNav, &p.IsHome, &p.CreatedAt, &p.PostCount); err != nil {
+			return nil, err
+		}
+		out = append(out, p)
+	}
+	return out, rows.Err()
+}
+
+func (s *Store) PageByID(ctx context.Context, blogID, id int64) (*Page, error) {
+	return scanPage(s.db.QueryRow(ctx, `SELECT `+pageCols+` FROM pages WHERE blog_id=$1 AND id=$2`, blogID, id))
+}
+
+func (s *Store) PageBySlug(ctx context.Context, blogID int64, slug string) (*Page, error) {
+	return scanPage(s.db.QueryRow(ctx, `SELECT `+pageCols+` FROM pages WHERE blog_id=$1 AND slug=$2`, blogID, slug))
+}
+
+func (s *Store) HomePage(ctx context.Context, blogID int64) (*Page, error) {
+	return scanPage(s.db.QueryRow(ctx, `SELECT `+pageCols+` FROM pages WHERE blog_id=$1 ORDER BY is_home DESC, nav_order, id LIMIT 1`, blogID))
+}
+
+func (s *Store) CreatePage(ctx context.Context, p *Page) (*Page, error) {
+	return scanPage(s.db.QueryRow(ctx, `INSERT INTO pages (blog_id, slug, title, intro_md, intro_html, nav_order, show_in_nav)
+		VALUES ($1,$2,$3,$4,$5,(SELECT coalesce(max(nav_order),-1)+1 FROM pages WHERE blog_id=$1),$6) RETURNING `+pageCols,
+		p.BlogID, p.Slug, p.Title, p.IntroMD, p.IntroHTML, p.ShowInNav))
+}
+
+func (s *Store) UpdatePage(ctx context.Context, p *Page) error {
+	_, err := s.db.Exec(ctx, `UPDATE pages SET slug=$3, title=$4, intro_md=$5, intro_html=$6, show_in_nav=$7 WHERE blog_id=$1 AND id=$2`,
+		p.BlogID, p.ID, p.Slug, p.Title, p.IntroMD, p.IntroHTML, p.ShowInNav)
+	return wrap(err)
+}
+
+func (s *Store) DeletePage(ctx context.Context, blogID, id int64) error {
+	_, err := s.db.Exec(ctx, `DELETE FROM pages WHERE blog_id=$1 AND id=$2 AND NOT is_home`, blogID, id)
+	return err
+}
+
+// SetHomePage moves the home flag to the given page.
+func (s *Store) SetHomePage(ctx context.Context, blogID, id int64) error {
+	tx, err := s.db.Begin(ctx)
+	if err != nil {
+		return err
+	}
+	defer tx.Rollback(ctx)
+	if _, err := tx.Exec(ctx, `UPDATE pages SET is_home=false WHERE blog_id=$1 AND is_home`, blogID); err != nil {
+		return err
+	}
+	if _, err := tx.Exec(ctx, `UPDATE pages SET is_home=true WHERE blog_id=$1 AND id=$2`, blogID, id); err != nil {
+		return err
+	}
+	return tx.Commit(ctx)
+}
+
+// MovePage swaps nav_order with the neighbouring page (dir = -1 up, +1 down).
+func (s *Store) MovePage(ctx context.Context, blogID, id int64, dir int) error {
+	pages, err := s.ListPages(ctx, blogID)
+	if err != nil {
+		return err
+	}
+	idx := -1
+	for i := range pages {
+		if pages[i].ID == id {
+			idx = i
+		}
+	}
+	j := idx + dir
+	if idx < 0 || j < 0 || j >= len(pages) {
+		return nil
+	}
+	pages[idx], pages[j] = pages[j], pages[idx]
+	tx, err := s.db.Begin(ctx)
+	if err != nil {
+		return err
+	}
+	defer tx.Rollback(ctx)
+	for i, p := range pages { // renumber everything; keeps orders dense
+		if _, err := tx.Exec(ctx, `UPDATE pages SET nav_order=$3 WHERE blog_id=$1 AND id=$2`, blogID, p.ID, i); err != nil {
+			return err
+		}
+	}
+	return tx.Commit(ctx)
+}