1use rusqlite::{Connection, params};
2use serde::Serialize;
3
4use crate::errors::AppError;
5
6use super::repository::QueryState;
7
8#[derive(Debug, Clone, Serialize)]
9pub struct SearchItem {
10 pub item_id: i64,
11 pub created_at: String,
12 pub score: f64,
13 pub matched_fields: Vec<String>,
14 pub preview: String,
15 pub content_type: Option<String>,
16 pub validation_status: Option<String>,
17}
18
19#[derive(Debug, Clone, Copy)]
20pub enum ReportPeriod {
21 Week,
22 Month,
23}
24
25#[derive(Debug, Clone)]
26pub struct ReportRangeQuery {
27 pub period: String,
28 pub from: String,
29 pub to: String,
30 pub timezone: String,
31}
32
33#[derive(Debug, Clone, Serialize)]
34pub struct NameCount {
35 pub name: String,
36 pub count: i64,
37}
38
39#[derive(Debug, Clone, Serialize)]
40pub struct ReportRange {
41 pub from: String,
42 pub to: String,
43 pub timezone: String,
44}
45
46#[derive(Debug, Clone, Serialize)]
47pub struct ReportTotals {
48 pub captured: i64,
49 pub enriched: i64,
50 pub pending: i64,
51}
52
53#[derive(Debug, Clone, Serialize)]
54pub struct ReportSummary {
55 pub period: String,
56 pub range: ReportRange,
57 pub totals: ReportTotals,
58 pub top_categories: Vec<NameCount>,
59 pub top_tags: Vec<NameCount>,
60 pub top_content_types: Vec<NameCount>,
61 pub validation_status_totals: Vec<NameCount>,
62}
63
64pub fn search_items(
65 conn: &Connection,
66 query: &str,
67 state: QueryState,
68 limit: usize,
69) -> Result<Vec<SearchItem>, AppError> {
70 let state_filter = match state {
71 QueryState::All => "1 = 1",
72 QueryState::Pending => {
73 "not exists (
74 select 1 from item_derivations d
75 where d.item_id = i.item_id and d.is_active = 1 and d.status = 'accepted'
76 )"
77 }
78 QueryState::Enriched => {
79 "exists (
80 select 1 from item_derivations d
81 where d.item_id = i.item_id and d.is_active = 1 and d.status = 'accepted'
82 )"
83 }
84 };
85
86 let sql = format!(
87 "select
88 i.item_id,
89 i.created_at,
90 bm25(item_search_fts) as score,
91 substr(coalesce(doc.derived_text, i.raw_text), 1, 120) as preview,
92 json_extract(ad.payload_json, '$.content_type') as content_type,
93 json_extract(ad.payload_json, '$.validation_status') as validation_status
94 from item_search_fts
95 join item_search_documents doc on doc.item_id = item_search_fts.rowid
96 join inbox_items i on i.item_id = doc.item_id
97 left join item_derivations ad
98 on ad.derivation_id = (
99 select d.derivation_id
100 from item_derivations d
101 where d.item_id = i.item_id
102 and d.is_active = 1
103 and d.status = 'accepted'
104 order by d.derivation_version desc, d.derivation_id desc
105 limit 1
106 )
107 where item_search_fts match ?1
108 and {state_filter}
109 order by score asc, i.created_at desc, i.item_id desc
110 limit ?2"
111 );
112
113 let mut stmt = conn.prepare(&sql).map_err(AppError::db_query)?;
114 let rows = stmt
115 .query_map(params![query, limit as i64], |row| {
116 Ok(SearchItem {
117 item_id: row.get(0)?,
118 created_at: row.get(1)?,
119 score: row.get(2)?,
120 matched_fields: vec!["raw_text".to_string()],
121 preview: row.get(3)?,
122 content_type: row.get(4)?,
123 validation_status: row.get(5)?,
124 })
125 })
126 .map_err(AppError::db_query)?;
127
128 rows.collect::<Result<Vec<_>, _>>()
129 .map_err(AppError::db_query)
130}
131
132pub fn report_summary(conn: &Connection, period: ReportPeriod) -> Result<ReportSummary, AppError> {
133 let (period_name, from_sql, to_sql) = match period {
134 ReportPeriod::Week => (
135 "week",
136 "strftime('%Y-%m-%dT%H:%M:%fZ', 'now', '-7 days')",
137 "strftime('%Y-%m-%dT%H:%M:%fZ', 'now')",
138 ),
139 ReportPeriod::Month => (
140 "month",
141 "strftime('%Y-%m-%dT%H:%M:%fZ', 'now', 'start of month')",
142 "strftime('%Y-%m-%dT%H:%M:%fZ', 'now')",
143 ),
144 };
145
146 let (from, to): (String, String) = conn
147 .query_row(&format!("select {from_sql}, {to_sql}"), [], |row| {
148 Ok((row.get(0)?, row.get(1)?))
149 })
150 .map_err(AppError::db_query)?;
151
152 let query = ReportRangeQuery {
153 period: period_name.to_string(),
154 from,
155 to,
156 timezone: "UTC".to_string(),
157 };
158 report_summary_with_range(conn, &query)
159}
160
161pub fn report_summary_with_range(
162 conn: &Connection,
163 query: &ReportRangeQuery,
164) -> Result<ReportSummary, AppError> {
165 let from = &query.from;
166 let to = &query.to;
167
168 let captured: i64 = conn
169 .query_row(
170 "select count(*)
171 from inbox_items
172 where julianday(created_at) >= julianday(?1)
173 and julianday(created_at) <= julianday(?2)",
174 params![from, to],
175 |row| row.get(0),
176 )
177 .map_err(AppError::db_query)?;
178
179 let enriched: i64 = conn
180 .query_row(
181 "select count(distinct i.item_id)
182 from inbox_items i
183 join item_derivations d on d.item_id = i.item_id
184 where d.is_active = 1
185 and d.status = 'accepted'
186 and julianday(i.created_at) >= julianday(?1)
187 and julianday(i.created_at) <= julianday(?2)",
188 params![from, to],
189 |row| row.get(0),
190 )
191 .map_err(AppError::db_query)?;
192
193 let pending = (captured - enriched).max(0);
194 let top_categories = collect_top_categories(conn, from, to)?;
195 let top_tags = collect_top_tags(conn, from, to)?;
196 let top_content_types = collect_top_content_types(conn, from, to)?;
197 let validation_status_totals = collect_validation_status_totals(conn, from, to)?;
198
199 Ok(ReportSummary {
200 period: query.period.clone(),
201 range: ReportRange {
202 from: from.clone(),
203 to: to.clone(),
204 timezone: query.timezone.clone(),
205 },
206 totals: ReportTotals {
207 captured,
208 enriched,
209 pending,
210 },
211 top_categories,
212 top_tags,
213 top_content_types,
214 validation_status_totals,
215 })
216}
217
218fn collect_top_categories(
219 conn: &Connection,
220 from: &str,
221 to: &str,
222) -> Result<Vec<NameCount>, AppError> {
223 let mut stmt = conn
224 .prepare(
225 "select coalesce(nullif(trim(d.category), ''), 'uncategorized') as category_name,
226 count(*) as category_count
227 from item_derivations d
228 join inbox_items i on i.item_id = d.item_id
229 where d.is_active = 1
230 and d.status = 'accepted'
231 and julianday(i.created_at) >= julianday(?1)
232 and julianday(i.created_at) <= julianday(?2)
233 group by category_name
234 order by category_count desc, category_name asc
235 limit 5",
236 )
237 .map_err(AppError::db_query)?;
238
239 let rows = stmt
240 .query_map(params![from, to], |row| {
241 Ok(NameCount {
242 name: row.get(0)?,
243 count: row.get(1)?,
244 })
245 })
246 .map_err(AppError::db_query)?;
247
248 rows.collect::<Result<Vec<_>, _>>()
249 .map_err(AppError::db_query)
250}
251
252fn collect_top_tags(conn: &Connection, from: &str, to: &str) -> Result<Vec<NameCount>, AppError> {
253 let mut stmt = conn
254 .prepare(
255 "select t.tag_name, count(*) as tag_count
256 from item_tags it
257 join tags t on t.tag_id = it.tag_id
258 join item_derivations d on d.derivation_id = it.derivation_id
259 join inbox_items i on i.item_id = d.item_id
260 where d.is_active = 1
261 and d.status = 'accepted'
262 and julianday(i.created_at) >= julianday(?1)
263 and julianday(i.created_at) <= julianday(?2)
264 group by t.tag_name
265 order by tag_count desc, t.tag_name asc
266 limit 5",
267 )
268 .map_err(AppError::db_query)?;
269
270 let rows = stmt
271 .query_map(params![from, to], |row| {
272 Ok(NameCount {
273 name: row.get(0)?,
274 count: row.get(1)?,
275 })
276 })
277 .map_err(AppError::db_query)?;
278
279 rows.collect::<Result<Vec<_>, _>>()
280 .map_err(AppError::db_query)
281}
282
283fn collect_top_content_types(
284 conn: &Connection,
285 from: &str,
286 to: &str,
287) -> Result<Vec<NameCount>, AppError> {
288 let mut stmt = conn
289 .prepare(
290 "select coalesce(nullif(trim(json_extract(d.payload_json, '$.content_type')), ''), 'unknown') as content_type,
291 count(*) as content_type_count
292 from item_derivations d
293 join inbox_items i on i.item_id = d.item_id
294 where d.is_active = 1
295 and d.status = 'accepted'
296 and julianday(i.created_at) >= julianday(?1)
297 and julianday(i.created_at) <= julianday(?2)
298 group by content_type
299 order by content_type_count desc, content_type asc
300 limit 7",
301 )
302 .map_err(AppError::db_query)?;
303
304 let rows = stmt
305 .query_map(params![from, to], |row| {
306 Ok(NameCount {
307 name: row.get(0)?,
308 count: row.get(1)?,
309 })
310 })
311 .map_err(AppError::db_query)?;
312
313 rows.collect::<Result<Vec<_>, _>>()
314 .map_err(AppError::db_query)
315}
316
317fn collect_validation_status_totals(
318 conn: &Connection,
319 from: &str,
320 to: &str,
321) -> Result<Vec<NameCount>, AppError> {
322 let mut stmt = conn
323 .prepare(
324 "select coalesce(nullif(trim(json_extract(d.payload_json, '$.validation_status')), ''), 'unknown') as validation_status,
325 count(*) as validation_status_count
326 from item_derivations d
327 join inbox_items i on i.item_id = d.item_id
328 where d.is_active = 1
329 and d.status = 'accepted'
330 and julianday(i.created_at) >= julianday(?1)
331 and julianday(i.created_at) <= julianday(?2)
332 group by validation_status
333 order by validation_status_count desc, validation_status asc",
334 )
335 .map_err(AppError::db_query)?;
336
337 let rows = stmt
338 .query_map(params![from, to], |row| {
339 Ok(NameCount {
340 name: row.get(0)?,
341 count: row.get(1)?,
342 })
343 })
344 .map_err(AppError::db_query)?;
345
346 rows.collect::<Result<Vec<_>, _>>()
347 .map_err(AppError::db_query)
348}