max / makenotwork

3.1 KB · 122 lines History Blame Raw

1	//! Chapter CRUD: markers for audio items (timestamps, titles, ordering).
2
3	use sqlx::PgPool;
4
5	use super::models::DbChapter;
6	use super::{ChapterId, ItemId};
7	use crate::error::Result;
8
9	/// List all chapters for an audio item, ordered by sort_order then start time.
10	///
11	/// Capped at 500 as a safety limit.
12	#[tracing::instrument(skip_all)]
13	pub(crate) async fn get_chapters_by_item(pool: &PgPool, item_id: ItemId) -> Result<Vec<DbChapter>> {
14	let chapters = sqlx::query_as::<_, DbChapter>(
15	"SELECT * FROM chapters WHERE item_id = $1 ORDER BY sort_order, start_seconds LIMIT 500",
16	)
17	.bind(item_id)
18	.fetch_all(pool)
19	.await?;
20
21	Ok(chapters)
22	}
23
24	/// Batch-load chapters for multiple items, grouped by item_id.
25	#[tracing::instrument(skip_all)]
26	pub(crate) async fn get_chapters_by_items(
27	pool: &PgPool,
28	item_ids: &[ItemId],
29	) -> Result<std::collections::HashMap<ItemId, Vec<DbChapter>>> {
30	let chapters = sqlx::query_as::<_, DbChapter>(
31	"SELECT * FROM chapters WHERE item_id = ANY($1) ORDER BY item_id, sort_order, start_seconds",
32	)
33	.bind(item_ids)
34	.fetch_all(pool)
35	.await?;
36
37	let mut map: std::collections::HashMap<ItemId, Vec<DbChapter>> =
38	std::collections::HashMap::new();
39	for ch in chapters {
40	map.entry(ch.item_id).or_default().push(ch);
41	}
42	Ok(map)
43	}
44
45	/// Insert a new chapter marker for an audio item.
46	#[tracing::instrument(skip_all)]
47	pub(crate) async fn create_chapter(
48	pool: &PgPool,
49	item_id: ItemId,
50	title: &str,
51	start_seconds: f32,
52	sort_order: i32,
53	) -> Result<DbChapter> {
54	let chapter = sqlx::query_as::<_, DbChapter>(
55	r"
56	INSERT INTO chapters (item_id, title, start_seconds, sort_order)
57	VALUES ($1, $2, $3, $4)
58	RETURNING *
59	",
60	)
61	.bind(item_id)
62	.bind(title)
63	.bind(start_seconds)
64	.bind(sort_order)
65	.fetch_one(pool)
66	.await?;
67
68	Ok(chapter)
69	}
70
71	/// Update a chapter's title, start time, and sort order.
72	#[tracing::instrument(skip_all)]
73	pub(crate) async fn update_chapter(
74	pool: &PgPool,
75	chapter_id: ChapterId,
76	title: &str,
77	start_seconds: f32,
78	sort_order: i32,
79	) -> Result<DbChapter> {
80	let chapter = sqlx::query_as::<_, DbChapter>(
81	r"
82	UPDATE chapters
83	SET title = $2, start_seconds = $3, sort_order = $4
84	WHERE id = $1
85	RETURNING *
86	",
87	)
88	.bind(chapter_id)
89	.bind(title)
90	.bind(start_seconds)
91	.bind(sort_order)
92	.fetch_one(pool)
93	.await?;
94
95	Ok(chapter)
96	}
97
98	/// Permanently delete a chapter by ID.
99	#[tracing::instrument(skip_all)]
100	pub(crate) async fn delete_chapter(pool: &PgPool, chapter_id: ChapterId) -> Result<()> {
101	sqlx::query("DELETE FROM chapters WHERE id = $1")
102	.bind(chapter_id)
103	.execute(pool)
104	.await?;
105
106	Ok(())
107	}
108
109	/// Fetch a chapter by primary key. Returns `None` if not found.
110	#[tracing::instrument(skip_all)]
111	pub(crate) async fn get_chapter_by_id(
112	pool: &PgPool,
113	chapter_id: ChapterId,
114	) -> Result<Option<DbChapter>> {
115	let chapter = sqlx::query_as::<_, DbChapter>("SELECT * FROM chapters WHERE id = $1")
116	.bind(chapter_id)
117	.fetch_optional(pool)
118	.await?;
119
120	Ok(chapter)
121	}
122