1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
use crate::library::MediaType;
use crate::DatabaseError;

use serde::Deserialize;
use serde::Serialize;

/// Marker trait used to mark media types that inherit from Media.
/// Used internally by InsertableTVShow.
pub trait MediaTrait {}

/// Media struct that represents a media object, usually a movie, tv show or a episode of a tv
/// show. This struct is returned by several methods and can be serialized to json.
#[derive(Clone, Serialize, Deserialize, Debug, Default)]
pub struct Media {
    /// unique id automatically assigned by postgres.
    pub id: i64,
    /// id of the library that this media objects belongs to.
    pub library_id: i64,
    /// name of this media object. Usually the title of a movie, episode or tv show.
    pub name: String,
    /// description of this media object. Usually overview of a movie etc.
    pub description: Option<String>,
    /// rating provided by any API that is encoded as a signed integer. Usually TMDB rating.
    pub rating: Option<i64>,
    /// Year in which this movie/tv show/episode was released/aired.
    pub year: Option<i64>,
    /// Date when this media object was created and inserted into the database. Used by several
    /// routes to return sorted lists of medias, based on when they were scanned and inserted into
    /// the db.
    pub added: Option<String>,
    /// Path to the media poster.
    pub poster_path: Option<String>,
    /// Path to the backdrop for this media object.
    pub backdrop_path: Option<String>,
    /// Media type encoded as a string. Either movie/tv/episode or none.
    #[serde(flatten)]
    pub media_type: MediaType,
}

impl PartialEq for Media {
    fn eq(&self, other: &Media) -> bool {
        self.id == other.id
    }
}

impl Media {
    /// Method returns all Media objects associated with a Library. Its exactly the same as
    /// [`Library::get`](Library::get) except it takes in a Library object instead of a id.
    /// [`Library::get`](Library::get) is a intermediary to this function, as it calls this
    /// function.
    ///
    /// # Arguments
    /// * `conn` - mutable reference to a sqlx transaction.
    /// * `library_id` - a [`Library`](Library) id.
    pub async fn get_all(
        conn: &mut crate::Transaction<'_>,
        library_id: i64,
    ) -> Result<Vec<Self>, DatabaseError> {
        Ok(sqlx::query_as!(
                Media,
                r#"SELECT id, library_id, name, description, rating, year, added, poster_path, backdrop_path, media_type as "media_type: _" FROM media WHERE library_id = ? AND NOT media_type = "episode""#,
                library_id
            )
            .fetch_all(&mut *conn)
            .await?)
    }

    /// Method returns a media object based on its id
    ///
    /// # Arguments
    /// * `conn` - mutable reference to a sqlx transaction.
    /// * `req_id` - id of a media that we'd like to match against.
    pub async fn get(conn: &mut crate::Transaction<'_>, id: i64) -> Result<Self, DatabaseError> {
        Ok(sqlx::query_as!(
                Media,
                r#"SELECT id, library_id, name, description, rating, year, added, poster_path, backdrop_path, media_type as "media_type: _" FROM media WHERE id = ?"#,
                id
            )
            .fetch_one(&mut *conn)
            .await?)
    }

    /// Method to get a entry in a library based on name and library
    ///
    /// # Arguments
    /// * `conn` - mutable reference to a sqlx transaction.
    /// * `library_id` - a library id.
    /// * `name` - string slice reference containing the name we would like to filter by.
    pub async fn get_by_name_and_lib(
        conn: &mut crate::Transaction<'_>,
        library_id: i64,
        name: &str,
    ) -> Result<Self, DatabaseError> {
        Ok(sqlx::query_as!(
                Media,
                r#"SELECT id, library_id, name, description, rating, year, added, poster_path, backdrop_path, media_type as "media_type: _" FROM media WHERE library_id = ? AND name = ? AND NOT media_type = "episode""#,
                library_id,
                name,
            )
            .fetch_one(&mut *conn)
            .await?)
    }

    pub async fn get_of_mediafile(
        conn: &mut crate::Transaction<'_>,
        mediafile_id: i64,
    ) -> Result<Self, DatabaseError> {
        Ok(sqlx::query_as!(
                Media,
                r#"SELECT media.id, media.library_id, name, description, rating, year, added, poster_path, backdrop_path, media_type as "media_type: _"
                FROM media
                INNER JOIN mediafile ON mediafile.media_id = media.id
                WHERE mediafile.id = ?"#,
                mediafile_id
            ).fetch_one(&mut *conn).await?)
    }

    /// Method returns the top rated medias
    pub async fn get_top_rated(
        conn: &mut crate::Transaction<'_>,
        limit: i64,
    ) -> Result<Vec<i64>, DatabaseError> {
        Ok(sqlx::query_scalar!(
            r#"SELECT _tblmedia.id
                FROM _tblmedia
                JOIN library ON library.id = _tblmedia.library_id
                WHERE NOT _tblmedia.media_type = "episode" AND NOT library.hidden
                ORDER BY rating DESC
                LIMIT ?"#,
            limit
        )
        .fetch_all(&mut *conn)
        .await?)
    }

    /// Method returns the recently added medias
    pub async fn get_recently_added(
        conn: &mut crate::Transaction<'_>,
        limit: i64,
    ) -> Result<Vec<i64>, DatabaseError> {
        Ok(sqlx::query_scalar!(
            r#"SELECT _tblmedia.id
                FROM _tblmedia
                JOIN library ON library.id = _tblmedia.library_id
                WHERE NOT _tblmedia.media_type = "episode" AND NOT library.hidden
                ORDER BY added DESC
                LIMIT ?"#,
            limit
        )
        .fetch_all(&mut *conn)
        .await?)
    }

    pub async fn get_random_with(
        conn: &mut crate::Transaction<'_>,
        limit: i64,
    ) -> Result<Vec<Self>, DatabaseError> {
        Ok(sqlx::query_as!(
                Media,
                r#"SELECT media.id, media.library_id, media.name, description, rating, year, added, poster_path as "poster_path?", backdrop_path as "backdrop_path?", media.media_type as "media_type: _"
                FROM media
                JOIN library ON media.library_id = library.id
                WHERE NOT media.media_type = "episode" AND NOT library.hidden
                GROUP BY media.id
                ORDER BY RANDOM()
                LIMIT ?
                "#,
                limit
        ).fetch_all(&mut *conn).await?)
    }

    pub async fn get_search(
        conn: &mut crate::Transaction<'_>,
        query: &str,
        limit: i64,
    ) -> Result<Vec<Self>, DatabaseError> {
        let query = format!("%{}%", query);
        Ok(sqlx::query_as!(
                Media,
                r#"SELECT media.id, media.library_id, media.name, description, rating, year, added, poster_path, backdrop_path, media.media_type as "media_type: _"
                FROM media
                JOIN library ON library.id = media.library_id
                WHERE NOT media.media_type = "episode" AND NOT library.hidden
                AND UPPER(media.name) LIKE ?
                LIMIT ?
                "#,
                query,
                limit
        ).fetch_all(&mut *conn).await?)
    }

    pub async fn get_of_genre(
        conn: &mut crate::Transaction<'_>,
        genre_id: i64,
    ) -> Result<Vec<Self>, DatabaseError> {
        Ok(sqlx::query_as!(
                Media,
                r#"SELECT media.id, media.library_id, media.name, description, rating, year, added, poster_path, backdrop_path, media.media_type as "media_type: _"
                FROM media
                INNER JOIN genre_media ON genre_media.media_id = media.id
                JOIN library ON library.id = media.library_id
                WHERE NOT media.media_type = "episode" AND NOT library.hidden
                AND genre_media.genre_id = ?
                "#,
                genre_id,
        ).fetch_all(&mut *conn).await?)
    }

    pub async fn get_of_year(
        conn: &mut crate::Transaction<'_>,
        year: i64,
    ) -> Result<Vec<Self>, DatabaseError> {
        Ok(sqlx::query_as!(
                Media,
                r#"SELECT media.id, media.library_id, media.name, description, rating, year, added, poster_path, backdrop_path, media.media_type as "media_type: _"
                FROM media
                JOIN library ON library.id = media.library_id
                WHERE NOT media.media_type = "episode" AND NOT library.hidden
                AND year = ?
                "#,
                year,
        ).fetch_all(&mut *conn).await?)
    }

    pub async fn get_first_duration(&self, conn: &mut crate::Transaction<'_>) -> i64 {
        sqlx::query!(
            r#"
            SELECT COALESCE(mediafile.duration, 0) as "duration!: i64" FROM mediafile
            WHERE mediafile.media_id = ?
            ORDER BY mediafile.duration DESC
            LIMIT 1
        "#,
            self.id
        )
        .fetch_one(&mut *conn)
        .await
        .map(|x| x.duration)
        .unwrap_or(0)
    }

    pub async fn media_mediatype(
        conn: &mut crate::Transaction<'_>,
        id: i64,
    ) -> Result<MediaType, DatabaseError> {
        Ok(sqlx::query_scalar!(
            r#"SELECT media.media_type as "media_type: _" FROM media WHERE media.id = ?"#,
            id
        )
        .fetch_one(&mut *conn)
        .await?)
    }

    pub async fn decouple_mediafiles(
        conn: &mut crate::Transaction<'_>,
        id: i64,
    ) -> Result<Vec<i64>, DatabaseError> {
        Ok(sqlx::query_scalar!(
            r#"UPDATE mediafile SET media_id = NULL WHERE media_id = ? RETURNING id AS "id: i64""#,
            id
        )
        .fetch_all(&mut *conn)
        .await?)
    }

    /// Method deletes a media object based on its id.
    ///
    /// # Arguments
    /// * `conn` - mutable reference to a sqlx transaction.
    /// * `id` - id of a media object we want to delete
    pub async fn delete(
        conn: &mut crate::Transaction<'_>,
        id: i64,
    ) -> Result<usize, DatabaseError> {
        Ok(sqlx::query!("DELETE FROM _tblmedia WHERE id = ?", id)
            .execute(&mut *conn)
            .await?
            .rows_affected() as usize)
    }

    /// This function exists because for some reason `CASCADE DELETE` doesnt work with a sqlite
    /// backend. Thus we must manually delete entries when deleting a library.
    pub async fn delete_by_lib_id(
        conn: &mut crate::Transaction<'_>,
        library_id: i64,
    ) -> Result<usize, DatabaseError> {
        Ok(
            sqlx::query!("DELETE FROM _tblmedia WHERE library_id = ?", library_id)
                .execute(&mut *conn)
                .await?
                .rows_affected() as usize,
        )
    }
}

impl Into<super::tv::TVShow> for Media {
    fn into(self) -> super::tv::TVShow {
        super::tv::TVShow { id: self.id }
    }
}

/// Struct which represents a insertable media object. It is usually used only by the scanners to
/// insert new media objects. It is the same as [`Media`](Media) except it doesnt have the
/// [`id`](Media::id) field.
#[derive(Clone, Default, Debug)]
pub struct InsertableMedia {
    pub library_id: i64,
    pub name: String,
    pub description: Option<String>,
    pub rating: Option<i64>,
    pub year: Option<i64>,
    pub added: String,
    pub poster: Option<i64>,
    pub backdrop: Option<i64>,
    pub media_type: MediaType,
}

impl InsertableMedia {
    /// Method used to insert a new media object.
    ///
    /// # Arguments
    /// * `conn` - mutable reference to a sqlx transaction.
    #[tracing::instrument(skip(self, conn), fields(self.name = %self.name, self.library_id = %self.library_id))]
    pub async fn insert(&self, conn: &mut crate::Transaction<'_>) -> Result<i64, DatabaseError> {
        if let Some(record) = sqlx::query!(r#"SELECT id FROM _tblmedia where name = ?"#, self.name)
            .fetch_optional(&mut *conn)
            .await?
        {
            return Ok(record.id);
        }

        let id = sqlx::query!(
            r#"INSERT INTO _tblmedia (library_id, name, description, rating, year, added, poster, backdrop, media_type)
            VALUES ($1, $2, $3, $4, $5, $6,$7, $8, $9)
            ON CONFLICT DO UPDATE
            SET name = $2
            RETURNING _tblmedia.id as "id!: i64"
            "#,
            self.library_id,
            self.name,
            self.description,
            self.rating,
            self.year,
            self.added,
            self.poster,
            self.backdrop,
            self.media_type
        ).fetch_one(&mut *conn).await?.id;

        Ok(id)
    }

    /// Method used to insert a new media object with a specific id.
    ///
    /// # Arguments
    /// * `conn` - mutable reference to a sqlx transaction.
    /// * `id` - Unused ID to use for this media object.
    pub async fn insert_with_id(
        &self,
        conn: &mut crate::Transaction<'_>,
        id: i64,
    ) -> Result<i64, DatabaseError> {
        if let Some(record) = sqlx::query!(r#"SELECT id FROM _tblmedia where name = ?"#, self.name)
            .fetch_optional(&mut *conn)
            .await?
        {
            return Ok(record.id);
        }

        sqlx::query!(
            r#"INSERT INTO _tblmedia (id, library_id, name, description, rating, year, added, poster, backdrop, media_type)
            VALUES ($1, $2, $3, $4, $5, $6,$7, $8, $9, $10)
            "#,
            id,
            self.library_id,
            self.name,
            self.description,
            self.rating,
            self.year,
            self.added,
            self.poster,
            self.backdrop,
            self.media_type
        ).execute(&mut *conn).await?;

        Ok(id)
    }

    /// Method blindly inserts `self` into the database without checking whether a similar entry exists.
    /// This is especially useful for tv shows as they usually have similar metadata with key differences
    /// which are not indexed in the database.
    pub async fn insert_blind(
        &self,
        conn: &mut crate::Transaction<'_>,
    ) -> Result<i64, DatabaseError> {
        Ok(sqlx::query!(
            r#"INSERT INTO _tblmedia (library_id, name, description, rating, year, added, poster, backdrop, media_type)
            VALUES ($1, $2, $3, $4, $5, $6,$7, $8, $9)"#,
            self.library_id,
            self.name,
            self.description,
            self.rating,
            self.year,
            self.added,
            self.poster,
            self.backdrop,
            self.media_type
        ).execute(&mut *conn).await?.last_insert_rowid())
    }
}

/// Struct which is used when we need to update information about a media object. Same as
/// [`InsertableMedia`](InsertableMedia) except `library_id` cannot be changed and everything field
/// is a `Option<T>`.
#[derive(Clone, Default, Deserialize, Debug)]
pub struct UpdateMedia {
    pub name: Option<String>,
    pub description: Option<String>,
    pub rating: Option<i64>,
    pub year: Option<i64>,
    pub added: Option<String>,
    pub poster: Option<i64>,
    pub backdrop: Option<i64>,
    pub media_type: Option<MediaType>,
}

impl UpdateMedia {
    /// Method used to update the fields of a media object that is in the database using the id of
    /// this object as a discriminator.
    ///
    /// # Arguments
    /// * `conn` - mutable reference to a sqlx transaction.
    /// * `_id` - id of the media object we want to update
    pub async fn update(
        &self,
        conn: &mut crate::Transaction<'_>,
        id: i64,
    ) -> Result<usize, DatabaseError> {
        crate::opt_update!(conn,
            "UPDATE _tblmedia SET name = ? WHERE id = ?" => (self.name, id),
            "UPDATE _tblmedia SET description = ? WHERE id = ?" => (self.description, id),
            "UPDATE _tblmedia SET rating = ? WHERE id = ?" => (self.rating, id),
            "UPDATE _tblmedia SET year = ? WHERE id = ?" => (self.year, id),
            "UPDATE _tblmedia SET added = ? WHERE id = ?" => (self.added, id),
            "UPDATE _tblmedia SET poster = ? WHERE id = ?" => (self.poster, id),
            "UPDATE _tblmedia SET backdrop = ? WHERE id = ?" => (self.backdrop, id),
            "UPDATE _tblmedia SET media_type = ? WHERE id = ?" => (self.media_type, id)
        );

        Ok(1)
    }
}