1 files changed, 105 insertions, 0 deletions
diff --git a/internal/store/posts.go b/internal/store/posts.go
new file mode 100644
index 0000000..06bea7f
--- /dev/null
+++ b/internal/store/posts.go
@@ -0,0 +1,105 @@
+package store
+
+import (
+	"context"
+	"time"
+)
+
+type Post struct {
+	ID        int64
+	PageID    int64
+	Slug      string
+	Title     string
+	BodyMD    string
+	BodyHTML  string
+	Published bool
+	CreatedAt time.Time
+	UpdatedAt time.Time
+	// joined
+	PageSlug  string
+	PageTitle string
+}
+
+const postCols = `p.id, p.page_id, p.slug, p.title, p.body_md, p.body_html, p.published, p.created_at, p.updated_at, g.slug, g.title`
+
+func scanPost(row interface{ Scan(...any) error }) (*Post, error) {
+	var p Post
+	err := row.Scan(&p.ID, &p.PageID, &p.Slug, &p.Title, &p.BodyMD, &p.BodyHTML, &p.Published, &p.CreatedAt, &p.UpdatedAt, &p.PageSlug, &p.PageTitle)
+	if err != nil {
+		return nil, wrap(err)
+	}
+	return &p, nil
+}
+
+func (s *Store) collectPosts(ctx context.Context, q string, args ...any) ([]Post, error) {
+	rows, err := s.db.Query(ctx, q, args...)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var out []Post
+	for rows.Next() {
+		p, err := scanPost(rows)
+		if err != nil {
+			return nil, err
+		}
+		out = append(out, *p)
+	}
+	return out, rows.Err()
+}
+
+// ListPosts returns all posts of a blog for the dashboard, optionally filtered by page.
+func (s *Store) ListPosts(ctx context.Context, blogID int64, pageID int64) ([]Post, error) {
+	return s.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id
+		WHERE g.blog_id=$1 AND ($2=0 OR p.page_id=$2) ORDER BY p.created_at DESC, p.id DESC`, blogID, pageID)
+}
+
+// PublishedPosts returns a page of published posts for the public site.
+func (s *Store) PublishedPosts(ctx context.Context, pageID int64, limit, offset int) ([]Post, int, error) {
+	posts, err := s.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id
+		WHERE p.page_id=$1 AND p.published ORDER BY p.created_at DESC, p.id DESC LIMIT $2 OFFSET $3`, pageID, limit, offset)
+	if err != nil {
+		return nil, 0, err
+	}
+	var total int
+	err = s.db.QueryRow(ctx, `SELECT count(*) FROM posts WHERE page_id=$1 AND published`, pageID).Scan(&total)
+	return posts, total, err
+}
+
+// RecentPublishedPosts returns the newest published posts across a whole blog (for feeds).
+func (s *Store) RecentPublishedPosts(ctx context.Context, blogID int64, limit int) ([]Post, error) {
+	return s.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id
+		WHERE g.blog_id=$1 AND p.published ORDER BY p.created_at DESC, p.id DESC LIMIT $2`, blogID, limit)
+}
+
+func (s *Store) PostByID(ctx context.Context, blogID, id int64) (*Post, error) {
+	return scanPost(s.db.QueryRow(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id
+		WHERE g.blog_id=$1 AND p.id=$2`, blogID, id))
+}
+
+func (s *Store) PublishedPostBySlug(ctx context.Context, pageID int64, slug string) (*Post, error) {
+	return scanPost(s.db.QueryRow(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id
+		WHERE p.page_id=$1 AND p.slug=$2 AND p.published`, pageID, slug))
+}
+
+func (s *Store) CreatePost(ctx context.Context, p *Post) (*Post, error) {
+	var id int64
+	err := s.db.QueryRow(ctx, `INSERT INTO posts (page_id, slug, title, body_md, body_html, published) VALUES ($1,$2,$3,$4,$5,$6) RETURNING id`,
+		p.PageID, p.Slug, p.Title, p.BodyMD, p.BodyHTML, p.Published).Scan(&id)
+	if err != nil {
+		return nil, wrap(err)
+	}
+	return scanPost(s.db.QueryRow(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id WHERE p.id=$1`, id))
+}
+
+// UpdatePost updates a post; the page must belong to the same blog (checked by handler).
+func (s *Store) UpdatePost(ctx context.Context, p *Post) error {
+	_, err := s.db.Exec(ctx, `UPDATE posts SET page_id=$2, slug=$3, title=$4, body_md=$5, body_html=$6, published=$7, updated_at=now() WHERE id=$1`,
+		p.ID, p.PageID, p.Slug, p.Title, p.BodyMD, p.BodyHTML, p.Published)
+	return wrap(err)
+}
+
+func (s *Store) DeletePost(ctx context.Context, blogID, id int64) error {
+	_, err := s.db.Exec(ctx, `DELETE FROM posts p USING pages g WHERE g.id=p.page_id AND g.blog_id=$1 AND p.id=$2`, blogID, id)
+	return err
+}