max / makenotwork

3.1 KB · 118 lines History Blame Raw

1	//! Chapter CRUD: markers for audio items (timestamps, titles, ordering).
2
3	use sqlx::PgPool;
4
5	use super::models::*;
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 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 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>> = std::collections::HashMap::new();
38	for ch in chapters {
39	map.entry(ch.item_id).or_default().push(ch);
40	}
41	Ok(map)
42	}
43
44	/// Insert a new chapter marker for an audio item.
45	#[tracing::instrument(skip_all)]
46	pub async fn create_chapter(
47	pool: &PgPool,
48	item_id: ItemId,
49	title: &str,
50	start_seconds: f32,
51	sort_order: i32,
52	) -> Result<DbChapter> {
53	let chapter = sqlx::query_as::<_, DbChapter>(
54	r#"
55	INSERT INTO chapters (item_id, title, start_seconds, sort_order)
56	VALUES ($1, $2, $3, $4)
57	RETURNING *
58	"#,
59	)
60	.bind(item_id)
61	.bind(title)
62	.bind(start_seconds)
63	.bind(sort_order)
64	.fetch_one(pool)
65	.await?;
66
67	Ok(chapter)
68	}
69
70	/// Update a chapter's title, start time, and sort order.
71	#[tracing::instrument(skip_all)]
72	pub async fn update_chapter(
73	pool: &PgPool,
74	chapter_id: ChapterId,
75	title: &str,
76	start_seconds: f32,
77	sort_order: i32,
78	) -> Result<DbChapter> {
79	let chapter = sqlx::query_as::<_, DbChapter>(
80	r#"
81	UPDATE chapters
82	SET title = $2, start_seconds = $3, sort_order = $4
83	WHERE id = $1
84	RETURNING *
85	"#,
86	)
87	.bind(chapter_id)
88	.bind(title)
89	.bind(start_seconds)
90	.bind(sort_order)
91	.fetch_one(pool)
92	.await?;
93
94	Ok(chapter)
95	}
96
97	/// Permanently delete a chapter by ID.
98	#[tracing::instrument(skip_all)]
99	pub async fn delete_chapter(pool: &PgPool, chapter_id: ChapterId) -> Result<()> {
100	sqlx::query("DELETE FROM chapters WHERE id = $1")
101	.bind(chapter_id)
102	.execute(pool)
103	.await?;
104
105	Ok(())
106	}
107
108	/// Fetch a chapter by primary key. Returns `None` if not found.
109	#[tracing::instrument(skip_all)]
110	pub async fn get_chapter_by_id(pool: &PgPool, chapter_id: ChapterId) -> Result<Option<DbChapter>> {
111	let chapter = sqlx::query_as::<_, DbChapter>("SELECT * FROM chapters WHERE id = $1")
112	.bind(chapter_id)
113	.fetch_optional(pool)
114	.await?;
115
116	Ok(chapter)
117	}
118