rocksky.app
A decentralized music tracking and discovery platform built on AT Protocol 馃幍
rocksky.app
spotify
atproto
lastfm
musicbrainz
scrobbling
listenbrainz
tsiry-sandratraina.com
1use std::sync::{Arc, Mutex};
2
3use crate::read_payload;
4use crate::types::stats::{
5 Compatibility, GetCompatibilityParams, GetNeighboursParams, SharedArtist,
6};
7use crate::types::{
8 scrobble::{ScrobblesPerDay, ScrobblesPerMonth, ScrobblesPerYear},
9 stats::{
10 GetAlbumScrobblesParams, GetArtistScrobblesParams, GetScrobblesPerDayParams,
11 GetScrobblesPerMonthParams, GetScrobblesPerYearParams, GetStatsParams,
12 GetTrackScrobblesParams, Neighbour,
13 },
14};
15use actix_web::{web, HttpRequest, HttpResponse};
16use anyhow::Error;
17use duckdb::Connection;
18use serde_json::json;
19use tokio_stream::StreamExt;
20
21pub async fn get_stats(
22 payload: &mut web::Payload,
23 _req: &HttpRequest,
24 conn: Arc<Mutex<Connection>>,
25) -> Result<HttpResponse, Error> {
26 let body = read_payload!(payload);
27
28 let params = serde_json::from_slice::<GetStatsParams>(&body)?;
29 tracing::info!(user_did = ?params.user_did, "Get stats");
30
31 let conn = conn.lock().unwrap();
32 let mut stmt = conn.prepare("SELECT COUNT(DISTINCT s.created_at) FROM scrobbles s LEFT JOIN users u ON s.user_id = u.id WHERE u.did = ? OR u.handle = ?")?;
33 let scrobbles: i64 = stmt.query_row([¶ms.user_did, ¶ms.user_did], |row| row.get(0))?;
34
35 let mut stmt = conn.prepare(
36 r#"
37 SELECT COUNT(*) FROM (
38 SELECT
39 s.artist_id AS id,
40 ar.name AS artist_name,
41 ar.picture AS picture,
42 ar.sha256 AS sha256,
43 ar.uri AS uri,
44 COUNT(DISTINCT s.created_at) AS play_count,
45 COUNT(DISTINCT s.user_id) AS unique_listeners
46 FROM
47 scrobbles s
48 LEFT JOIN
49 artists ar ON s.artist_id = ar.id
50 LEFT JOIN
51 users u ON s.user_id = u.id
52 WHERE
53 s.artist_id IS NOT NULL AND (u.did = ? OR u.handle = ?)
54 GROUP BY
55 s.artist_id, ar.name, ar.uri, ar.picture, ar.sha256
56 )
57 "#,
58 )?;
59 let artists: i64 = stmt.query_row([¶ms.user_did, ¶ms.user_did], |row| row.get(0))?;
60
61 let mut stmt = conn.prepare("SELECT COUNT(*) FROM loved_tracks LEFT JOIN users u ON loved_tracks.user_id = u.id WHERE u.did = ? OR u.handle = ?")?;
62 let loved_tracks: i64 =
63 stmt.query_row([¶ms.user_did, ¶ms.user_did], |row| row.get(0))?;
64
65 let mut stmt = conn.prepare(r#"SELECT COUNT(*) FROM (
66 SELECT
67 s.album_id AS id,
68 a.title AS title,
69 ar.name AS artist,
70 ar.uri AS artist_uri,
71 a.album_art AS album_art,
72 a.release_date,
73 a.year,
74 a.uri,
75 a.sha256,
76 COUNT(DISTINCT s.created_at) AS play_count,
77 COUNT(DISTINCT s.user_id) AS unique_listeners
78 FROM
79 scrobbles s
80 LEFT JOIN
81 albums a ON s.album_id = a.id
82 LEFT JOIN
83 artists ar ON a.artist = ar.name
84 LEFT JOIN
85 users u ON s.user_id = u.id
86 WHERE s.album_id IS NOT NULL AND (u.did = ? OR u.handle = ?)
87 GROUP BY
88 s.album_id, a.title, ar.name, a.release_date, a.year, a.uri, a.album_art, a.sha256, ar.uri
89 )"#)?;
90 let albums: i64 = stmt.query_row([¶ms.user_did, ¶ms.user_did], |row| row.get(0))?;
91
92 let mut stmt = conn.prepare(
93 r#"
94 SELECT COUNT(*) FROM tracks t
95 LEFT JOIN user_tracks ut ON ut.track_id = t.id
96 LEFT JOIN users u ON ut.user_id = u.id
97 WHERE u.did = ? OR u.handle = ?
98 "#,
99 )?;
100 let tracks: i64 = stmt.query_row([¶ms.user_did, ¶ms.user_did], |row| row.get(0))?;
101
102 Ok(HttpResponse::Ok().json(json!({
103 "scrobbles": scrobbles,
104 "artists": artists,
105 "loved_tracks": loved_tracks,
106 "albums": albums,
107 "tracks": tracks,
108 })))
109}
110
111pub async fn get_scrobbles_per_day(
112 payload: &mut web::Payload,
113 _req: &HttpRequest,
114 conn: Arc<Mutex<Connection>>,
115) -> Result<HttpResponse, Error> {
116 let body = read_payload!(payload);
117 let params = serde_json::from_slice::<GetScrobblesPerDayParams>(&body)?;
118 let start = params
119 .start
120 .unwrap_or(GetScrobblesPerDayParams::default().start.unwrap());
121 let end = params
122 .end
123 .unwrap_or(GetScrobblesPerDayParams::default().end.unwrap());
124 let did = params.user_did;
125 let genre = params.genre;
126 tracing::info!(start = %start, end = %end, user_did = ?did, "Get scrobbles per day");
127
128 let conn = conn.lock().unwrap();
129 match (did, genre) {
130 (Some(did), None) => {
131 let mut stmt = conn.prepare(
132 r#"
133 SELECT
134 date_trunc('day', created_at) AS date,
135 COUNT(DISTINCT scrobbles.created_at) AS count
136 FROM
137 scrobbles
138 LEFT JOIN users u ON scrobbles.user_id = u.id
139 WHERE
140 u.did = ? OR u.handle = ?
141 AND created_at BETWEEN ? AND ?
142 GROUP BY
143 date_trunc('day', created_at)
144 ORDER BY
145 date;
146 "#,
147 )?;
148 let scrobbles = stmt.query_map([&did, &did, &start, &end], |row| {
149 Ok(ScrobblesPerDay {
150 date: row.get(0)?,
151 count: row.get(1)?,
152 })
153 })?;
154 let scrobbles: Result<Vec<_>, _> = scrobbles.collect();
155 Ok(HttpResponse::Ok().json(scrobbles?))
156 }
157 (None, Some(genre)) => {
158 let mut stmt = conn.prepare(
159 r#"
160 SELECT
161 date_trunc('day', created_at) AS date,
162 COUNT(DISTINCT s.created_at) AS count
163 FROM
164 scrobbles s
165 LEFT JOIN users u ON s.user_id = u.id
166 LEFT JOIN artists a ON s.artist_id = a.id
167 WHERE list_contains(a.genres, ?) AND created_at BETWEEN ? AND ?
168 GROUP BY
169 date_trunc('day', created_at)
170 ORDER BY
171 date;
172 "#,
173 )?;
174 let scrobbles = stmt.query_map([&genre, &start, &end], |row| {
175 Ok(ScrobblesPerDay {
176 date: row.get(0)?,
177 count: row.get(1)?,
178 })
179 })?;
180 let scrobbles: Result<Vec<_>, _> = scrobbles.collect();
181 Ok(HttpResponse::Ok().json(scrobbles?))
182 }
183 _ => {
184 let mut stmt = conn.prepare(
185 r#"
186 SELECT
187 date_trunc('day', s.created_at) AS date,
188 COUNT(DISTINCT (u.did, s.created_at)) AS count
189 FROM scrobbles s
190 JOIN users u ON u.id = s.user_id
191 WHERE s.created_at BETWEEN ? AND ?
192 GROUP BY 1
193 ORDER BY 1;
194 "#,
195 )?;
196 let scrobbles = stmt.query_map([start, end], |row| {
197 Ok(ScrobblesPerDay {
198 date: row.get(0)?,
199 count: row.get(1)?,
200 })
201 })?;
202 let scrobbles: Result<Vec<_>, _> = scrobbles.collect();
203 Ok(HttpResponse::Ok().json(scrobbles?))
204 }
205 }
206}
207
208pub async fn get_scrobbles_per_month(
209 payload: &mut web::Payload,
210 _req: &HttpRequest,
211 conn: Arc<Mutex<Connection>>,
212) -> Result<HttpResponse, Error> {
213 let body = read_payload!(payload);
214 let params = serde_json::from_slice::<GetScrobblesPerMonthParams>(&body)?;
215 let start = params
216 .start
217 .unwrap_or(GetScrobblesPerDayParams::default().start.unwrap());
218 let end = params
219 .end
220 .unwrap_or(GetScrobblesPerDayParams::default().end.unwrap());
221 let did = params.user_did;
222 tracing::info!(start = %start, end = %end, user_did = ?did, "Get scrobbles per month");
223
224 let conn = conn.lock().unwrap();
225 match did {
226 Some(did) => {
227 let mut stmt = conn.prepare(
228 r#"
229 SELECT
230 EXTRACT(YEAR FROM created_at) || '-' ||
231 LPAD(EXTRACT(MONTH FROM created_at)::VARCHAR, 2, '0') AS year_month,
232 COUNT(DISTINCT scrobbles.created_at) AS count
233 FROM
234 scrobbles
235 LEFT JOIN users u ON scrobbles.user_id = u.id
236 WHERE
237 u.did = ? OR u.handle = ?
238 AND created_at BETWEEN ? AND ?
239 GROUP BY
240 EXTRACT(YEAR FROM created_at),
241 EXTRACT(MONTH FROM created_at)
242 ORDER BY
243 year_month;
244 "#,
245 )?;
246 let scrobbles = stmt.query_map([&did, &did, &start, &end], |row| {
247 Ok(ScrobblesPerMonth {
248 year_month: row.get(0)?,
249 count: row.get(1)?,
250 })
251 })?;
252 let scrobbles: Result<Vec<_>, _> = scrobbles.collect();
253 Ok(HttpResponse::Ok().json(scrobbles?))
254 }
255 None => {
256 let mut stmt = conn.prepare(
257 r#"
258 SELECT
259 EXTRACT(YEAR FROM created_at) || '-' ||
260 LPAD(EXTRACT(MONTH FROM created_at)::VARCHAR, 2, '0') AS year_month,
261 COUNT(DISTINCT scrobbles.created_at) AS count
262 FROM
263 scrobbles
264 WHERE
265 created_at BETWEEN ? AND ?
266 GROUP BY
267 EXTRACT(YEAR FROM created_at),
268 EXTRACT(MONTH FROM created_at)
269 ORDER BY
270 year_month;
271 "#,
272 )?;
273 let scrobbles = stmt.query_map([start, end], |row| {
274 Ok(ScrobblesPerMonth {
275 year_month: row.get(0)?,
276 count: row.get(1)?,
277 })
278 })?;
279 let scrobbles: Result<Vec<_>, _> = scrobbles.collect();
280 Ok(HttpResponse::Ok().json(scrobbles?))
281 }
282 }
283}
284
285pub async fn get_scrobbles_per_year(
286 payload: &mut web::Payload,
287 _req: &HttpRequest,
288 conn: Arc<Mutex<Connection>>,
289) -> Result<HttpResponse, Error> {
290 let body = read_payload!(payload);
291 let params = serde_json::from_slice::<GetScrobblesPerYearParams>(&body)?;
292 let start = params
293 .start
294 .unwrap_or(GetScrobblesPerDayParams::default().start.unwrap());
295 let end = params
296 .end
297 .unwrap_or(GetScrobblesPerDayParams::default().end.unwrap());
298 let did = params.user_did;
299 tracing::info!(start = %start, end = %end, user_did = ?did, "Get scrobbles per year");
300
301 let conn = conn.lock().unwrap();
302 match did {
303 Some(did) => {
304 let mut stmt = conn.prepare(
305 r#"
306 SELECT
307 EXTRACT(YEAR FROM created_at) AS year,
308 COUNT(DISTINCT scrobbles.created_at) AS count
309 FROM
310 scrobbles
311 LEFT JOIN users u ON scrobbles.user_id = u.id
312 WHERE
313 u.did = ? OR u.handle = ?
314 AND created_at BETWEEN ? AND ?
315 GROUP BY
316 EXTRACT(YEAR FROM created_at)
317 ORDER BY
318 year;
319 "#,
320 )?;
321 let scrobbles = stmt.query_map([&did, &did, &start, &end], |row| {
322 Ok(ScrobblesPerYear {
323 year: row.get(0)?,
324 count: row.get(1)?,
325 })
326 })?;
327 let scrobbles: Result<Vec<_>, _> = scrobbles.collect();
328 Ok(HttpResponse::Ok().json(scrobbles?))
329 }
330 None => {
331 let mut stmt = conn.prepare(
332 r#"
333 SELECT
334 EXTRACT(YEAR FROM created_at) AS year,
335 COUNT(DISTINCT scrobbles.created_at) AS count
336 FROM
337 scrobbles
338 WHERE
339 created_at BETWEEN ? AND ?
340 GROUP BY
341 EXTRACT(YEAR FROM created_at)
342 ORDER BY
343 year;
344 "#,
345 )?;
346 let scrobbles = stmt.query_map([start, end], |row| {
347 Ok(ScrobblesPerYear {
348 year: row.get(0)?,
349 count: row.get(1)?,
350 })
351 })?;
352 let scrobbles: Result<Vec<_>, _> = scrobbles.collect();
353 Ok(HttpResponse::Ok().json(scrobbles?))
354 }
355 }
356}
357
358pub async fn get_album_scrobbles(
359 payload: &mut web::Payload,
360 _req: &HttpRequest,
361 conn: Arc<Mutex<Connection>>,
362) -> Result<HttpResponse, Error> {
363 let body = read_payload!(payload);
364 let params = serde_json::from_slice::<GetAlbumScrobblesParams>(&body)?;
365 let start = params
366 .start
367 .unwrap_or(GetAlbumScrobblesParams::default().start.unwrap());
368 let end = params
369 .end
370 .unwrap_or(GetAlbumScrobblesParams::default().end.unwrap());
371 let conn = conn.lock().unwrap();
372 tracing::info!(album_id = %params.album_id, start = %start, end = %end, "Get album scrobbles");
373
374 let mut stmt = conn.prepare(
375 r#"
376 SELECT
377 date_trunc('day', s.created_at) AS date,
378 COUNT(s.album_id) AS count
379 FROM
380 scrobbles s
381 LEFT JOIN albums a ON s.album_id = a.id
382 WHERE
383 a.id = ? OR a.uri = ?
384 AND s.created_at BETWEEN ? AND ?
385 GROUP BY
386 date_trunc('day', s.created_at)
387 ORDER BY
388 date;
389 "#,
390 )?;
391 let scrobbles = stmt.query_map([¶ms.album_id, ¶ms.album_id, &start, &end], |row| {
392 Ok(ScrobblesPerDay {
393 date: row.get(0)?,
394 count: row.get(1)?,
395 })
396 })?;
397 let scrobbles: Result<Vec<_>, _> = scrobbles.collect();
398 Ok(HttpResponse::Ok().json(scrobbles?))
399}
400
401pub async fn get_artist_scrobbles(
402 payload: &mut web::Payload,
403 _req: &HttpRequest,
404 conn: Arc<Mutex<Connection>>,
405) -> Result<HttpResponse, Error> {
406 let body = read_payload!(payload);
407 let params = serde_json::from_slice::<GetArtistScrobblesParams>(&body)?;
408 let start = params
409 .start
410 .unwrap_or(GetArtistScrobblesParams::default().start.unwrap());
411 let end = params
412 .end
413 .unwrap_or(GetArtistScrobblesParams::default().end.unwrap());
414 let conn = conn.lock().unwrap();
415 tracing::info!(artist_id = %params.artist_id, start = %start, end = %end, "Get artist scrobbles");
416
417 let mut stmt = conn.prepare(
418 r#"
419 SELECT
420 date_trunc('day', s.created_at) AS date,
421 COUNT(s.artist_id) AS count
422 FROM
423 scrobbles s
424 LEFT JOIN artists a ON s.artist_id = a.id
425 WHERE
426 a.id = ? OR a.uri = ?
427 AND s.created_at BETWEEN ? AND ?
428 GROUP BY
429 date_trunc('day', s.created_at)
430 ORDER BY
431 date;
432 "#,
433 )?;
434
435 let scrobbles = stmt.query_map(
436 [¶ms.artist_id, ¶ms.artist_id, &start, &end],
437 |row| {
438 Ok(ScrobblesPerDay {
439 date: row.get(0)?,
440 count: row.get(1)?,
441 })
442 },
443 )?;
444
445 let scrobbles: Result<Vec<_>, _> = scrobbles.collect();
446 Ok(HttpResponse::Ok().json(scrobbles?))
447}
448
449pub async fn get_track_scrobbles(
450 payload: &mut web::Payload,
451 _req: &HttpRequest,
452 conn: Arc<Mutex<Connection>>,
453) -> Result<HttpResponse, Error> {
454 let body = read_payload!(payload);
455 let params = serde_json::from_slice::<GetTrackScrobblesParams>(&body)?;
456 let start = params
457 .start
458 .unwrap_or(GetTrackScrobblesParams::default().start.unwrap());
459 let end = params
460 .end
461 .unwrap_or(GetTrackScrobblesParams::default().end.unwrap());
462 let conn = conn.lock().unwrap();
463 tracing::info!(track_id = %params.track_id, start = %start, end = %end, "Get track scrobbles");
464
465 let mut stmt = conn.prepare(
466 r#"
467 SELECT
468 date_trunc('day', s.created_at) AS date,
469 COUNT(s.track_id) AS count
470 FROM
471 scrobbles s
472 LEFT JOIN tracks t ON s.track_id = t.id
473 WHERE
474 t.id = ? OR t.uri = ?
475 AND s.created_at BETWEEN ? AND ?
476 GROUP BY
477 date_trunc('day', s.created_at)
478 ORDER BY
479 date;
480 "#,
481 )?;
482
483 let scrobbles = stmt.query_map([¶ms.track_id, ¶ms.track_id, &start, &end], |row| {
484 Ok(ScrobblesPerDay {
485 date: row.get(0)?,
486 count: row.get(1)?,
487 })
488 })?;
489
490 let scrobbles: Result<Vec<_>, _> = scrobbles.collect();
491 Ok(HttpResponse::Ok().json(scrobbles?))
492}
493
494pub async fn get_neighbours(
495 payload: &mut web::Payload,
496 _req: &HttpRequest,
497 conn: Arc<Mutex<Connection>>,
498) -> Result<HttpResponse, Error> {
499 let body = read_payload!(payload);
500 let params = serde_json::from_slice::<GetNeighboursParams>(&body)?;
501 let conn = conn.lock().unwrap();
502 tracing::info!(user_id = %params.user_id, "Get neighbours");
503
504 let mut stmt = conn.prepare(
505 r#"
506 WITH user_top_artists AS (
507 SELECT
508 user_id,
509 artist_id,
510 COUNT(*) as play_count,
511 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY COUNT(*) DESC) as artist_rank
512 FROM scrobbles s
513 INNER JOIN artists a ON a.id = s.artist_id
514 WHERE s.artist_id IS NOT NULL
515 AND a.name != 'Various Artists'
516 GROUP BY user_id, artist_id
517 ),
518 weighted_similarity AS (
519 SELECT
520 u1.user_id as target_user,
521 u2.user_id as neighbor_user,
522 SUM(1.0 / (u1.artist_rank + u2.artist_rank)) as similarity_score,
523 COUNT(DISTINCT u1.artist_id) as shared_artists,
524 ARRAY_AGG(DISTINCT u1.artist_id) FILTER (WHERE u1.artist_rank <= 20) as top_shared_artists
525 FROM user_top_artists u1
526 JOIN user_top_artists u2
527 ON u1.artist_id = u2.artist_id
528 AND u1.user_id != u2.user_id
529 WHERE u1.user_id = ?
530 AND u1.artist_rank <= 50
531 AND u2.artist_rank <= 50
532 GROUP BY u1.user_id, u2.user_id
533 HAVING shared_artists >= 3
534 AND top_shared_artists IS NOT NULL
535 )
536 SELECT
537 ws.neighbor_user,
538 u.display_name,
539 u.handle,
540 u.did,
541 u.avatar,
542 ws.similarity_score,
543 ws.shared_artists,
544 to_json(LIST(a.name ORDER BY array_position(ws.top_shared_artists, a.id))) as top_shared_artist_names,
545 to_json(LIST({'id': a.id, 'name': a.name, 'picture': a.picture, 'uri': a.uri}
546 ORDER BY array_position(ws.top_shared_artists, a.id))) as top_shared_artists_details
547 FROM weighted_similarity ws
548 LEFT JOIN users u ON u.id = ws.neighbor_user
549 INNER JOIN UNNEST(ws.top_shared_artists) AS t(artist_id) ON true
550 INNER JOIN artists a ON a.id = t.artist_id
551 GROUP BY ws.neighbor_user, u.display_name, u.handle, u.did, u.avatar, ws.similarity_score, ws.shared_artists, ws.top_shared_artists
552 ORDER BY ws.similarity_score DESC
553 LIMIT 20
554 "#,
555 )?;
556
557 let neighbours = stmt.query_map([¶ms.user_id], |row| {
558 let top_shared_artist_names_json: String = row.get(7)?;
559 let top_shared_artists_details_json: String = row.get(8)?;
560
561 let top_shared_artist_names: Vec<String> =
562 serde_json::from_str(&top_shared_artist_names_json).unwrap_or_else(|_| Vec::new());
563 let top_shared_artists_details: Vec<crate::types::stats::NeighbourArtist> =
564 serde_json::from_str(&top_shared_artists_details_json).unwrap_or_else(|_| Vec::new());
565
566 Ok(Neighbour {
567 user_id: row.get(0)?,
568 display_name: row.get(1)?,
569 handle: row.get(2)?,
570 did: row.get(3)?,
571 avatar: row.get(4)?,
572 similarity_score: row.get(5)?,
573 shared_artists_count: row.get(6)?,
574 top_shared_artist_names,
575 top_shared_artists_details,
576 })
577 })?;
578
579 let neighbours: Result<Vec<_>, _> = neighbours.collect();
580 Ok(HttpResponse::Ok().json(neighbours?))
581}
582
583pub async fn get_compatibility(
584 payload: &mut web::Payload,
585 _req: &HttpRequest,
586 conn: Arc<Mutex<Connection>>,
587) -> Result<HttpResponse, Error> {
588 let body = read_payload!(payload);
589 let params = serde_json::from_slice::<GetCompatibilityParams>(&body)?;
590 let conn = conn.lock().unwrap();
591 tracing::info!(user_id_1 = %params.user_id1, user_id_2 = %params.user_id2, "Get compatibility");
592
593 let mut stmt = conn.prepare(
594 r#"
595 WITH user_top_artists AS (
596 SELECT
597 user_id,
598 artist_id,
599 COUNT(*) as play_count,
600 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY COUNT(*) DESC) as artist_rank
601 FROM scrobbles s
602 INNER JOIN artists a ON a.id = s.artist_id
603 WHERE s.artist_id IS NOT NULL
604 AND a.name != 'Various Artists'
605 AND user_id IN (?, ?)
606 GROUP BY user_id, artist_id
607 ),
608 user_totals AS (
609 SELECT
610 user_id,
611 COUNT(DISTINCT artist_id) as total_artists
612 FROM user_top_artists
613 WHERE artist_rank <= 50
614 GROUP BY user_id
615 ),
616 shared_weighted AS (
617 SELECT
618 u1.artist_id,
619 u1.artist_rank as user1_rank,
620 u2.artist_rank as user2_rank,
621 (1.0 / u1.artist_rank) * (1.0 / u2.artist_rank) as artist_weight,
622 ROW_NUMBER() OVER (ORDER BY (1.0 / u1.artist_rank) * (1.0 / u2.artist_rank) DESC) as weight_rank
623 FROM user_top_artists u1
624 INNER JOIN user_top_artists u2
625 ON u1.artist_id = u2.artist_id
626 AND u1.user_id = ?
627 AND u2.user_id = ?
628 WHERE u1.artist_rank <= 50
629 AND u2.artist_rank <= 50
630 ),
631 compatibility_calc AS (
632 SELECT
633 SUM(sw.artist_weight) as weighted_overlap,
634 COUNT(*) as shared_count,
635 (SELECT total_artists FROM user_totals WHERE user_id = ?) as user1_total,
636 (SELECT total_artists FROM user_totals WHERE user_id = ?) as user2_total
637 FROM shared_weighted sw
638 )
639 SELECT
640 ROUND(
641 (shared_count * 1.0 / LEAST(user1_total, user2_total)) * 100,
642 1
643 ) as compatibility_percentage,
644 CASE
645 WHEN (shared_count * 1.0 / LEAST(user1_total, user2_total)) * 100 < 20 THEN 'Low'
646 WHEN (shared_count * 1.0 / LEAST(user1_total, user2_total)) * 100 < 40 THEN 'Medium'
647 WHEN (shared_count * 1.0 / LEAST(user1_total, user2_total)) * 100 < 60 THEN 'High'
648 WHEN (shared_count * 1.0 / LEAST(user1_total, user2_total)) * 100 < 75 THEN 'Very High'
649 WHEN (shared_count * 1.0 / LEAST(user1_total, user2_total)) * 100 < 90 THEN 'Super'
650 ELSE 'ZOMG!1!'
651 END as compatibility_level,
652 shared_count as shared_artists,
653 user1_total as user1_artist_count,
654 user2_total as user2_artist_count,
655 to_json(LIST(a.name ORDER BY sw.artist_weight DESC) FILTER (WHERE sw.weight_rank <= 10)) as top_shared_artists,
656 to_json(LIST({
657 'id': a.id,
658 'name': a.name,
659 'picture': a.picture,
660 'uri': a.uri,
661 'user1_rank': sw.user1_rank,
662 'user2_rank': sw.user2_rank,
663 'weight': sw.artist_weight
664 } ORDER BY sw.artist_weight DESC) FILTER (WHERE sw.weight_rank <= 10)) as top_shared_detailed_artists
665 FROM compatibility_calc
666 CROSS JOIN shared_weighted sw
667 INNER JOIN artists a ON a.id = sw.artist_id
668 GROUP BY weighted_overlap, shared_count, user1_total, user2_total;
669 "#,
670 )?;
671
672 let compatibility = stmt.query_map(
673 [
674 ¶ms.user_id1,
675 ¶ms.user_id2,
676 ¶ms.user_id1,
677 ¶ms.user_id2,
678 ¶ms.user_id1,
679 ¶ms.user_id2,
680 ],
681 |row| {
682 let top_shared_artists_json: String = row.get(5)?;
683 let top_shared_artists: Vec<String> =
684 serde_json::from_str(&top_shared_artists_json).unwrap_or_else(|_| Vec::new());
685 let top_shared_detailed_artists_json: String = row.get(6)?;
686 let top_shared_detailed_artists: Vec<SharedArtist> =
687 serde_json::from_str(&top_shared_detailed_artists_json)
688 .unwrap_or_else(|_| Vec::new());
689 Ok(Compatibility {
690 compatibility_percentage: row.get(0)?,
691 compatibility_level: row.get(1)?,
692 shared_artists: row.get(2)?,
693 user1_artist_count: row.get(3)?,
694 user2_artist_count: row.get(4)?,
695 top_shared_artists,
696 top_shared_detailed_artists,
697 })
698 },
699 )?;
700
701 let compatibility = compatibility.collect::<Result<Vec<_>, _>>()?;
702 let compatibility = compatibility.into_iter().next();
703
704 Ok(HttpResponse::Ok().json(compatibility))
705}