max / makenotwork

3.5 KB · 138 lines History Blame Raw

1	//! Item section CRUD: tabbed markdown content blocks within items.
2
3	use sqlx::PgPool;
4
5	use super::models::DbItemSection;
6	use super::{ItemId, ItemSectionId};
7	use crate::error::Result;
8
9	/// List all sections for an item, ordered by sort_order.
10	#[tracing::instrument(skip_all)]
11	pub(crate) async fn list_by_item(pool: &PgPool, item_id: ItemId) -> Result<Vec<DbItemSection>> {
12	let sections = sqlx::query_as::<_, DbItemSection>(
13	"SELECT * FROM item_sections WHERE item_id = $1 ORDER BY sort_order LIMIT 500",
14	)
15	.bind(item_id)
16	.fetch_all(pool)
17	.await?;
18
19	Ok(sections)
20	}
21
22	/// Fetch a section by primary key. Returns `None` if not found.
23	#[tracing::instrument(skip_all)]
24	pub(crate) async fn get_by_id(
25	pool: &PgPool,
26	section_id: ItemSectionId,
27	) -> Result<Option<DbItemSection>> {
28	let section = sqlx::query_as::<_, DbItemSection>("SELECT * FROM item_sections WHERE id = $1")
29	.bind(section_id)
30	.fetch_optional(pool)
31	.await?;
32
33	Ok(section)
34	}
35
36	/// Insert a new section for an item.
37	pub(crate) async fn create(
38	pool: &PgPool,
39	item_id: ItemId,
40	title: &str,
41	slug: &str,
42	body: &str,
43	sort_order: i32,
44	) -> Result<DbItemSection> {
45	let section = sqlx::query_as::<_, DbItemSection>(
46	r"
47	INSERT INTO item_sections (item_id, title, slug, body, sort_order)
48	VALUES ($1, $2, $3, $4, $5)
49	RETURNING *
50	",
51	)
52	.bind(item_id)
53	.bind(title)
54	.bind(slug)
55	.bind(body)
56	.bind(sort_order)
57	.fetch_one(pool)
58	.await?;
59
60	Ok(section)
61	}
62
63	/// Update a section's title, slug, and body.
64	#[tracing::instrument(skip_all)]
65	pub(crate) async fn update(
66	pool: &PgPool,
67	section_id: ItemSectionId,
68	title: &str,
69	slug: &str,
70	body: &str,
71	) -> Result<DbItemSection> {
72	let section = sqlx::query_as::<_, DbItemSection>(
73	r"
74	UPDATE item_sections
75	SET title = $2, slug = $3, body = $4, updated_at = now()
76	WHERE id = $1
77	RETURNING *
78	",
79	)
80	.bind(section_id)
81	.bind(title)
82	.bind(slug)
83	.bind(body)
84	.fetch_one(pool)
85	.await?;
86
87	Ok(section)
88	}
89
90	/// Permanently delete a section by ID.
91	#[tracing::instrument(skip_all)]
92	pub(crate) async fn delete(pool: &PgPool, section_id: ItemSectionId) -> Result<()> {
93	sqlx::query("DELETE FROM item_sections WHERE id = $1")
94	.bind(section_id)
95	.execute(pool)
96	.await?;
97
98	Ok(())
99	}
100
101	/// Reorder sections by setting sort_order from an ordered list of IDs.
102	///
103	/// A single `UNNEST ... WITH ORDINALITY` update: atomic (no partial ordering on
104	/// failure) and one round-trip instead of one query per section.
105	#[tracing::instrument(skip_all)]
106	pub(crate) async fn reorder(
107	pool: &PgPool,
108	item_id: ItemId,
109	section_ids: &[ItemSectionId],
110	) -> Result<()> {
111	let ids: Vec<uuid::Uuid> = section_ids.iter().map(\|id\| *id.as_uuid()).collect();
112	sqlx::query(
113	r"
114	UPDATE item_sections AS s
115	SET sort_order = ord.pos::int - 1, updated_at = now()
116	FROM UNNEST($1::uuid[]) WITH ORDINALITY AS ord(id, pos)
117	WHERE s.id = ord.id AND s.item_id = $2
118	",
119	)
120	.bind(&ids)
121	.bind(item_id)
122	.execute(pool)
123	.await?;
124
125	Ok(())
126	}
127
128	/// Count sections for an item.
129	#[tracing::instrument(skip_all)]
130	pub(crate) async fn count_by_item(pool: &PgPool, item_id: ItemId) -> Result<i64> {
131	let row: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM item_sections WHERE item_id = $1")
132	.bind(item_id)
133	.fetch_one(pool)
134	.await?;
135
136	Ok(row.0)
137	}
138