max / makenotwork

3.7 KB · 140 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	/// Safety cap on the media picker listing. Hitting it is logged at WARN so a
45	/// creator with more than this many clean files in one folder isn't silently
46	/// truncated (mirrors `versions::VERSIONS_LIST_HARD_CAP`).
47	pub const MEDIA_LIST_HARD_CAP: i64 = 500;
48
49	/// List media files for a user, optionally filtered by folder.
50	#[tracing::instrument(skip_all)]
51	pub async fn list_by_user_folder(
52	pool: &PgPool,
53	user_id: UserId,
54	folder: Option<&str>,
55	) -> Result<Vec<DbMediaFile>> {
56	let rows = if let Some(f) = folder {
57	sqlx::query_as::<_, DbMediaFile>(
58	"SELECT * FROM media_files WHERE user_id = $1 AND folder = $2 AND scan_status = 'clean' ORDER BY created_at DESC LIMIT $3",
59	)
60	.bind(user_id)
61	.bind(f)
62	.bind(MEDIA_LIST_HARD_CAP)
63	.fetch_all(pool)
64	.await?
65	} else {
66	sqlx::query_as::<_, DbMediaFile>(
67	"SELECT * FROM media_files WHERE user_id = $1 AND scan_status = 'clean' ORDER BY created_at DESC LIMIT $2",
68	)
69	.bind(user_id)
70	.bind(MEDIA_LIST_HARD_CAP)
71	.fetch_all(pool)
72	.await?
73	};
74
75	if rows.len() as i64 == MEDIA_LIST_HARD_CAP {
76	tracing::warn!(
77	%user_id, cap = MEDIA_LIST_HARD_CAP,
78	"list_by_user_folder hit hard cap; some media omitted from the picker"
79	);
80	}
81
82	Ok(rows)
83	}
84
85	/// List distinct folder names for a user.
86	#[tracing::instrument(skip_all)]
87	pub async fn list_folders(pool: &PgPool, user_id: UserId) -> Result<Vec<String>> {
88	let folders: Vec<String> = sqlx::query_scalar(
89	"SELECT DISTINCT folder FROM media_files WHERE user_id = $1 ORDER BY folder",
90	)
91	.bind(user_id)
92	.fetch_all(pool)
93	.await?;
94
95	Ok(folders)
96	}
97
98	/// Get a single media file by ID.
99	#[tracing::instrument(skip_all)]
100	pub async fn get_by_id(pool: &PgPool, id: MediaFileId) -> Result<Option<DbMediaFile>> {
101	let row = sqlx::query_as::<_, DbMediaFile>(
102	"SELECT * FROM media_files WHERE id = $1",
103	)
104	.bind(id)
105	.fetch_optional(pool)
106	.await?;
107
108	Ok(row)
109	}
110
111	/// Delete a media file by ID.
112	#[tracing::instrument(skip_all)]
113	pub async fn delete<'e>(
114	executor: impl sqlx::PgExecutor<'e>,
115	id: MediaFileId,
116	) -> Result<Option<DbMediaFile>> {
117	let row = sqlx::query_as::<_, DbMediaFile>(
118	"DELETE FROM media_files WHERE id = $1 RETURNING *",
119	)
120	.bind(id)
121	.fetch_optional(executor)
122	.await?;
123
124	Ok(row)
125	}
126
127	/// Count media files for a user.
128	#[allow(dead_code)]
129	#[tracing::instrument(skip_all)]
130	pub async fn count_by_user(pool: &PgPool, user_id: UserId) -> Result<i64> {
131	let count: i64 = sqlx::query_scalar(
132	"SELECT COUNT(*) FROM media_files WHERE user_id = $1",
133	)
134	.bind(user_id)
135	.fetch_one(pool)
136	.await?;
137
138	Ok(count)
139	}
140