max / makenotwork

3.2 KB · 126 lines History Blame Raw

1	//! Media library queries: user-scoped files for inline markdown content.
2
3	use sqlx::PgPool;
4
5	use super::id_types::*;
6	use super::models::DbMediaFile;
7	use crate::error::Result;
8
9	/// Insert a new media file record.
10	#[allow(clippy::too_many_arguments)]
11	#[tracing::instrument(skip_all)]
12	pub async fn create<'e>(
13	executor: impl sqlx::PgExecutor<'e>,
14	user_id: UserId,
15	folder: &str,
16	filename: &str,
17	s3_key: &str,
18	content_type: &str,
19	file_size_bytes: i64,
20	media_type: &str,
21	scan_status: &str,
22	) -> Result<DbMediaFile> {
23	let row = sqlx::query_as::<_, DbMediaFile>(
24	r#"
25	INSERT INTO media_files (user_id, folder, filename, s3_key, content_type, file_size_bytes, media_type, scan_status)
26	VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
27	RETURNING *
28	"#,
29	)
30	.bind(user_id)
31	.bind(folder)
32	.bind(filename)
33	.bind(s3_key)
34	.bind(content_type)
35	.bind(file_size_bytes)
36	.bind(media_type)
37	.bind(scan_status)
38	.fetch_one(executor)
39	.await?;
40
41	Ok(row)
42	}
43
44	/// List media files for a user, optionally filtered by folder.
45	#[tracing::instrument(skip_all)]
46	pub async fn list_by_user_folder(
47	pool: &PgPool,
48	user_id: UserId,
49	folder: Option<&str>,
50	) -> Result<Vec<DbMediaFile>> {
51	let rows = if let Some(f) = folder {
52	sqlx::query_as::<_, DbMediaFile>(
53	"SELECT * FROM media_files WHERE user_id = $1 AND folder = $2 AND scan_status = 'clean' ORDER BY created_at DESC LIMIT 500",
54	)
55	.bind(user_id)
56	.bind(f)
57	.fetch_all(pool)
58	.await?
59	} else {
60	sqlx::query_as::<_, DbMediaFile>(
61	"SELECT * FROM media_files WHERE user_id = $1 AND scan_status = 'clean' ORDER BY created_at DESC LIMIT 500",
62	)
63	.bind(user_id)
64	.fetch_all(pool)
65	.await?
66	};
67
68	Ok(rows)
69	}
70
71	/// List distinct folder names for a user.
72	#[tracing::instrument(skip_all)]
73	pub async fn list_folders(pool: &PgPool, user_id: UserId) -> Result<Vec<String>> {
74	let folders: Vec<String> = sqlx::query_scalar(
75	"SELECT DISTINCT folder FROM media_files WHERE user_id = $1 ORDER BY folder",
76	)
77	.bind(user_id)
78	.fetch_all(pool)
79	.await?;
80
81	Ok(folders)
82	}
83
84	/// Get a single media file by ID.
85	#[tracing::instrument(skip_all)]
86	pub async fn get_by_id(pool: &PgPool, id: MediaFileId) -> Result<Option<DbMediaFile>> {
87	let row = sqlx::query_as::<_, DbMediaFile>(
88	"SELECT * FROM media_files WHERE id = $1",
89	)
90	.bind(id)
91	.fetch_optional(pool)
92	.await?;
93
94	Ok(row)
95	}
96
97	/// Delete a media file by ID.
98	#[tracing::instrument(skip_all)]
99	pub async fn delete<'e>(
100	executor: impl sqlx::PgExecutor<'e>,
101	id: MediaFileId,
102	) -> Result<Option<DbMediaFile>> {
103	let row = sqlx::query_as::<_, DbMediaFile>(
104	"DELETE FROM media_files WHERE id = $1 RETURNING *",
105	)
106	.bind(id)
107	.fetch_optional(executor)
108	.await?;
109
110	Ok(row)
111	}
112
113	/// Count media files for a user.
114	#[allow(dead_code)]
115	#[tracing::instrument(skip_all)]
116	pub async fn count_by_user(pool: &PgPool, user_id: UserId) -> Result<i64> {
117	let count: i64 = sqlx::query_scalar(
118	"SELECT COUNT(*) FROM media_files WHERE user_id = $1",
119	)
120	.bind(user_id)
121	.fetch_one(pool)
122	.await?;
123
124	Ok(count)
125	}
126