3 files changed, 138 insertions, 6 deletions
diff --git a/internal/store/pages.go b/internal/store/pages.go
index 55cad33..3d5b02d 100644
--- a/internal/store/pages.go
+++ b/internal/store/pages.go
@@ -105,8 +105,10 @@ func (bs *BlogStore) UpdatePage(ctx context.Context, p *Page) error {
 }
 
 func (bs *BlogStore) DeletePage(ctx context.Context, id int64) error {
-	_, err := bs.db.Exec(ctx, `DELETE FROM pages WHERE id=$1 AND NOT is_home`, id)
-	return err
+	if _, err := bs.db.Exec(ctx, `DELETE FROM pages WHERE id=$1 AND NOT is_home`, id); err != nil {
+		return err
+	}
+	return deleteOrphanTags(ctx, bs.db) // its posts went with it
 }
 
 // SetHomePage moves the home flag to the given page.
diff --git a/internal/store/posts.go b/internal/store/posts.go
index 569640b..706d132 100644
--- a/internal/store/posts.go
+++ b/internal/store/posts.go
@@ -18,16 +18,25 @@ type Post struct {
 	// joined
 	PageSlug  string
 	PageTitle string
+	Tags      []Tag // by name; IDs are not loaded
 }
 
-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`
+// The tags come along as two parallel arrays (names and slugs, both by name)
+// so every post query stays a single round trip; the alias p is the posts row.
+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,
+	coalesce((SELECT array_agg(t.name ORDER BY t.name) FROM post_tags pt JOIN tags t ON t.id=pt.tag_id WHERE pt.post_id=p.id), '{}'),
+	coalesce((SELECT array_agg(t.slug ORDER BY t.name) FROM post_tags pt JOIN tags t ON t.id=pt.tag_id WHERE pt.post_id=p.id), '{}')`
 
 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)
+	var names, slugs []string
+	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, &names, &slugs)
 	if err != nil {
 		return nil, wrap(err)
 	}
+	for i := range names {
+		p.Tags = append(p.Tags, Tag{Name: names[i], Slug: slugs[i]})
+	}
 	return &p, nil
 }
 
@@ -130,6 +139,8 @@ func (bs *BlogStore) UpdatePost(ctx context.Context, p *Post) error {
 }
 
 func (bs *BlogStore) DeletePost(ctx context.Context, id int64) error {
-	_, err := bs.db.Exec(ctx, `DELETE FROM posts WHERE id=$1`, id)
-	return err
+	if _, err := bs.db.Exec(ctx, `DELETE FROM posts WHERE id=$1`, id); err != nil {
+		return err
+	}
+	return deleteOrphanTags(ctx, bs.db)
 }
diff --git a/internal/store/tags.go b/internal/store/tags.go
new file mode 100644
index 0000000..249a82e
--- /dev/null
+++ b/internal/store/tags.go
@@ -0,0 +1,119 @@
+package store
+
+import "context"
+
+// Tag is a label shared by posts. The slug is the URL (/tag/<slug>) and the
+// dedupe key: two spellings with the same slug are one tag.
+type Tag struct {
+	ID   int64
+	Name string
+	Slug string
+}
+
+// TagCount is a tag with the number of published posts that carry it.
+type TagCount struct {
+	Name  string
+	Slug  string
+	Count int
+}
+
+// ListTags returns every tag that is on at least one post, by name.
+func (bs *BlogStore) ListTags(ctx context.Context) ([]Tag, error) {
+	rows, err := bs.db.Query(ctx, `SELECT t.id, t.name, t.slug FROM tags t
+		WHERE EXISTS (SELECT 1 FROM post_tags WHERE tag_id=t.id) ORDER BY t.name`)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var out []Tag
+	for rows.Next() {
+		var t Tag
+		if err := rows.Scan(&t.ID, &t.Name, &t.Slug); err != nil {
+			return nil, err
+		}
+		out = append(out, t)
+	}
+	return out, rows.Err()
+}
+
+func (bs *BlogStore) TagBySlug(ctx context.Context, slug string) (*Tag, error) {
+	var t Tag
+	err := bs.db.QueryRow(ctx, `SELECT id, name, slug FROM tags WHERE slug=$1`, slug).Scan(&t.ID, &t.Name, &t.Slug)
+	if err != nil {
+		return nil, wrap(err)
+	}
+	return &t, nil
+}
+
+// TagCounts counts published posts per tag, by name. Hidden posts are left
+// out so a tag used only on them never shows on the blog.
+func (bs *BlogStore) TagCounts(ctx context.Context) ([]TagCount, error) {
+	rows, err := bs.db.Query(ctx, `SELECT t.name, t.slug, count(*) FROM tags t
+		JOIN post_tags pt ON pt.tag_id=t.id JOIN posts p ON p.id=pt.post_id
+		WHERE p.published GROUP BY t.id ORDER BY t.name`)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var out []TagCount
+	for rows.Next() {
+		var t TagCount
+		if err := rows.Scan(&t.Name, &t.Slug, &t.Count); err != nil {
+			return nil, err
+		}
+		out = append(out, t)
+	}
+	return out, rows.Err()
+}
+
+// PublishedPostsByTag returns a page of the published posts carrying the tag, from every page of the blog.
+func (bs *BlogStore) PublishedPostsByTag(ctx context.Context, tagID int64, limit, offset int) ([]Post, int, error) {
+	posts, err := bs.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id
+		JOIN post_tags pt ON pt.post_id=p.id
+		WHERE pt.tag_id=$1 AND p.published ORDER BY p.created_at DESC, p.id DESC LIMIT $2 OFFSET $3`, tagID, limit, offset)
+	if err != nil {
+		return nil, 0, err
+	}
+	var total int
+	err = bs.db.QueryRow(ctx, `SELECT count(*) FROM posts p JOIN post_tags pt ON pt.post_id=p.id WHERE pt.tag_id=$1 AND p.published`, tagID).Scan(&total)
+	return posts, total, err
+}
+
+// SetPostTags makes the given tags the post's tags: new ones are created,
+// dropped ones unlinked, and tags no post uses any more are deleted.
+func (bs *BlogStore) SetPostTags(ctx context.Context, postID int64, tags []Tag) error {
+	tx, err := bs.db.Begin(ctx)
+	if err != nil {
+		return err
+	}
+	defer tx.Rollback(ctx)
+	ids := make([]int64, 0, len(tags))
+	for _, t := range tags {
+		var id int64
+		// The no-op update makes RETURNING fire on conflict; the stored spelling stays.
+		if err := tx.QueryRow(ctx, `INSERT INTO tags (name, slug) VALUES ($1,$2)
+			ON CONFLICT (slug) DO UPDATE SET name=tags.name RETURNING id`, t.Name, t.Slug).Scan(&id); err != nil {
+			return err
+		}
+		ids = append(ids, id)
+	}
+	if _, err := tx.Exec(ctx, `DELETE FROM post_tags WHERE post_id=$1 AND tag_id <> ALL($2)`, postID, ids); err != nil {
+		return err
+	}
+	for _, id := range ids {
+		if _, err := tx.Exec(ctx, `INSERT INTO post_tags (post_id, tag_id) VALUES ($1,$2) ON CONFLICT DO NOTHING`, postID, id); err != nil {
+			return err
+		}
+	}
+	if err := deleteOrphanTags(ctx, tx); err != nil {
+		return err
+	}
+	return tx.Commit(ctx)
+}
+
+// deleteOrphanTags drops tags no post carries any more, so the post form's
+// list only offers tags that are in use.
+func deleteOrphanTags(ctx context.Context, q querier) error {
+	_, err := q.Exec(ctx, `DELETE FROM tags t WHERE NOT EXISTS (SELECT 1 FROM post_tags WHERE tag_id=t.id)`)
+	return err
+}