6 files changed, 504 insertions, 0 deletions
diff --git a/internal/store/blogs.go b/internal/store/blogs.go
new file mode 100644
index 0000000..57a7c10
--- /dev/null
+++ b/internal/store/blogs.go
@@ -0,0 +1,84 @@
+package store
+
+import (
+	"context"
+	"encoding/json"
+	"time"
+)
+
+type Blog struct {
+	ID        int64
+	OwnerID   int64
+	Subdomain string
+	Title     string
+	Tagline   string
+	ThemeJSON json.RawMessage
+	CreatedAt time.Time
+	UpdatedAt time.Time
+}
+
+const blogCols = `id, owner_id, subdomain, title, tagline, theme, created_at, updated_at`
+
+func scanBlog(row interface{ Scan(...any) error }) (*Blog, error) {
+	var b Blog
+	err := row.Scan(&b.ID, &b.OwnerID, &b.Subdomain, &b.Title, &b.Tagline, &b.ThemeJSON, &b.CreatedAt, &b.UpdatedAt)
+	if err != nil {
+		return nil, wrap(err)
+	}
+	return &b, nil
+}
+
+// CreateBlogger creates a user, their blog and a default home page in one transaction.
+func (s *Store) CreateBlogger(ctx context.Context, username, passwordHash, subdomain, title string) (*User, *Blog, error) {
+	tx, err := s.db.Begin(ctx)
+	if err != nil {
+		return nil, nil, err
+	}
+	defer tx.Rollback(ctx)
+
+	u, err := scanUser(tx.QueryRow(ctx, `INSERT INTO users (username, password_hash, role) VALUES ($1,$2,$3) RETURNING `+userCols,
+		username, passwordHash, RoleBlogger))
+	if err != nil {
+		return nil, nil, err
+	}
+	b, err := scanBlog(tx.QueryRow(ctx, `INSERT INTO blogs (owner_id, subdomain, title) VALUES ($1,$2,$3) RETURNING `+blogCols,
+		u.ID, subdomain, title))
+	if err != nil {
+		return nil, nil, err
+	}
+	_, err = tx.Exec(ctx, `INSERT INTO pages (blog_id, slug, title, nav_order, is_home) VALUES ($1,'home','Home',0,true)`, b.ID)
+	if err != nil {
+		return nil, nil, wrap(err)
+	}
+	if err := tx.Commit(ctx); err != nil {
+		return nil, nil, err
+	}
+	return u, b, nil
+}
+
+func (s *Store) BlogByID(ctx context.Context, id int64) (*Blog, error) {
+	return scanBlog(s.db.QueryRow(ctx, `SELECT `+blogCols+` FROM blogs WHERE id=$1`, id))
+}
+
+func (s *Store) BlogBySubdomain(ctx context.Context, sub string) (*Blog, error) {
+	return scanBlog(s.db.QueryRow(ctx, `SELECT `+blogCols+` FROM blogs WHERE subdomain=$1`, sub))
+}
+
+func (s *Store) BlogByOwner(ctx context.Context, ownerID int64) (*Blog, error) {
+	return scanBlog(s.db.QueryRow(ctx, `SELECT `+blogCols+` FROM blogs WHERE owner_id=$1`, ownerID))
+}
+
+func (s *Store) UpdateBlogSettings(ctx context.Context, id int64, title, tagline string) error {
+	_, err := s.db.Exec(ctx, `UPDATE blogs SET title=$2, tagline=$3, updated_at=now() WHERE id=$1`, id, title, tagline)
+	return err
+}
+
+func (s *Store) UpdateBlogTheme(ctx context.Context, id int64, theme json.RawMessage) error {
+	_, err := s.db.Exec(ctx, `UPDATE blogs SET theme=$2, updated_at=now() WHERE id=$1`, id, theme)
+	return err
+}
+
+func (s *Store) DeleteBlog(ctx context.Context, id int64) error {
+	_, err := s.db.Exec(ctx, `DELETE FROM blogs WHERE id=$1`, id)
+	return err
+}
diff --git a/internal/store/images.go b/internal/store/images.go
new file mode 100644
index 0000000..3699e75
--- /dev/null
+++ b/internal/store/images.go
@@ -0,0 +1,58 @@
+package store
+
+import (
+	"context"
+	"time"
+
+	"github.com/google/uuid"
+)
+
+type Image struct {
+	ID          uuid.UUID
+	BlogID      int64
+	Filename    string
+	ContentType string
+	Size        int
+	Data        []byte // only populated by ImageData
+	CreatedAt   time.Time
+}
+
+func (s *Store) CreateImage(ctx context.Context, blogID int64, filename, contentType string, data []byte) (*Image, error) {
+	img := &Image{ID: uuid.New(), BlogID: blogID, Filename: filename, ContentType: contentType, Size: len(data)}
+	err := s.db.QueryRow(ctx, `INSERT INTO images (id, blog_id, filename, content_type, size, data) VALUES ($1,$2,$3,$4,$5,$6) RETURNING created_at`,
+		img.ID, blogID, filename, contentType, len(data), data).Scan(&img.CreatedAt)
+	return img, err
+}
+
+func (s *Store) ListImages(ctx context.Context, blogID int64) ([]Image, error) {
+	rows, err := s.db.Query(ctx, `SELECT id, blog_id, filename, content_type, size, created_at FROM images WHERE blog_id=$1 ORDER BY created_at DESC`, blogID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var out []Image
+	for rows.Next() {
+		var i Image
+		if err := rows.Scan(&i.ID, &i.BlogID, &i.Filename, &i.ContentType, &i.Size, &i.CreatedAt); err != nil {
+			return nil, err
+		}
+		out = append(out, i)
+	}
+	return out, rows.Err()
+}
+
+// ImageData loads an image including its bytes.
+func (s *Store) ImageData(ctx context.Context, id uuid.UUID) (*Image, error) {
+	var i Image
+	err := s.db.QueryRow(ctx, `SELECT id, blog_id, filename, content_type, size, data, created_at FROM images WHERE id=$1`, id).
+		Scan(&i.ID, &i.BlogID, &i.Filename, &i.ContentType, &i.Size, &i.Data, &i.CreatedAt)
+	if err != nil {
+		return nil, wrap(err)
+	}
+	return &i, nil
+}
+
+func (s *Store) DeleteImage(ctx context.Context, blogID int64, id uuid.UUID) error {
+	_, err := s.db.Exec(ctx, `DELETE FROM images WHERE blog_id=$1 AND id=$2`, blogID, id)
+	return err
+}
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)
+}
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
+}
diff --git a/internal/store/store.go b/internal/store/store.go
new file mode 100644
index 0000000..472bb2a
--- /dev/null
+++ b/internal/store/store.go
@@ -0,0 +1,34 @@
+// Package store holds the data models and all SQL queries.
+package store
+
+import (
+	"errors"
+
+	"github.com/jackc/pgx/v5"
+	"github.com/jackc/pgx/v5/pgconn"
+	"github.com/jackc/pgx/v5/pgxpool"
+)
+
+var ErrNotFound = errors.New("not found")
+var ErrConflict = errors.New("already exists")
+
+type Store struct {
+	db *pgxpool.Pool
+}
+
+func New(db *pgxpool.Pool) *Store { return &Store{db: db} }
+
+// wrap maps driver errors onto the store's sentinel errors.
+func wrap(err error) error {
+	if err == nil {
+		return nil
+	}
+	if errors.Is(err, pgx.ErrNoRows) {
+		return ErrNotFound
+	}
+	var pgErr *pgconn.PgError
+	if errors.As(err, &pgErr) && pgErr.Code == "23505" { // unique_violation
+		return ErrConflict
+	}
+	return err
+}
diff --git a/internal/store/users.go b/internal/store/users.go
new file mode 100644
index 0000000..2910888
--- /dev/null
+++ b/internal/store/users.go
@@ -0,0 +1,99 @@
+package store
+
+import (
+	"context"
+	"time"
+)
+
+const (
+	RoleSuperadmin = "superadmin"
+	RoleBlogger    = "blogger"
+)
+
+type User struct {
+	ID           int64
+	Username     string
+	PasswordHash string
+	Role         string
+	Disabled     bool
+	TokenVersion int
+	CreatedAt    time.Time
+}
+
+func (u *User) IsSuperadmin() bool { return u.Role == RoleSuperadmin }
+
+const userCols = `id, username, password_hash, role, disabled, token_version, created_at`
+
+func scanUser(row interface{ Scan(...any) error }) (*User, error) {
+	var u User
+	err := row.Scan(&u.ID, &u.Username, &u.PasswordHash, &u.Role, &u.Disabled, &u.TokenVersion, &u.CreatedAt)
+	if err != nil {
+		return nil, wrap(err)
+	}
+	return &u, nil
+}
+
+func (s *Store) CreateUser(ctx context.Context, username, passwordHash, role string) (*User, error) {
+	row := s.db.QueryRow(ctx, `INSERT INTO users (username, password_hash, role) VALUES ($1,$2,$3) RETURNING `+userCols,
+		username, passwordHash, role)
+	return scanUser(row)
+}
+
+func (s *Store) UserByID(ctx context.Context, id int64) (*User, error) {
+	return scanUser(s.db.QueryRow(ctx, `SELECT `+userCols+` FROM users WHERE id=$1`, id))
+}
+
+func (s *Store) UserByUsername(ctx context.Context, username string) (*User, error) {
+	return scanUser(s.db.QueryRow(ctx, `SELECT `+userCols+` FROM users WHERE username=$1`, username))
+}
+
+func (s *Store) CountSuperadmins(ctx context.Context) (int, error) {
+	var n int
+	err := s.db.QueryRow(ctx, `SELECT count(*) FROM users WHERE role=$1`, RoleSuperadmin).Scan(&n)
+	return n, err
+}
+
+// SetPassword replaces the hash and bumps token_version so existing sessions die.
+func (s *Store) SetPassword(ctx context.Context, id int64, passwordHash string) error {
+	_, err := s.db.Exec(ctx, `UPDATE users SET password_hash=$2, token_version=token_version+1 WHERE id=$1`, id, passwordHash)
+	return err
+}
+
+func (s *Store) SetUserDisabled(ctx context.Context, id int64, disabled bool) error {
+	_, err := s.db.Exec(ctx, `UPDATE users SET disabled=$2, token_version=token_version+1 WHERE id=$1`, id, disabled)
+	return err
+}
+
+func (s *Store) DeleteUser(ctx context.Context, id int64) error {
+	_, err := s.db.Exec(ctx, `DELETE FROM users WHERE id=$1`, id)
+	return err
+}
+
+// UserWithBlog is a row for the admin overview.
+type UserWithBlog struct {
+	User
+	BlogID    *int64
+	Subdomain *string
+	BlogTitle *string
+}
+
+func (s *Store) ListUsers(ctx context.Context) ([]UserWithBlog, error) {
+	rows, err := s.db.Query(ctx, `SELECT u.id, u.username, u.password_hash, u.role, u.disabled, u.token_version, u.created_at,
+		b.id, b.subdomain, b.title
+		FROM users u LEFT JOIN blogs b ON b.owner_id = u.id
+		ORDER BY u.role, u.username`)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var out []UserWithBlog
+	for rows.Next() {
+		var r UserWithBlog
+		if err := rows.Scan(&r.ID, &r.Username, &r.PasswordHash, &r.Role, &r.Disabled, &r.TokenVersion, &r.CreatedAt,
+			&r.BlogID, &r.Subdomain, &r.BlogTitle); err != nil {
+			return nil, err
+		}
+		out = append(out, r)
+	}
+	return out, rows.Err()
+}