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
use crate::opt_update;
use crate::DatabaseError;

use serde::{Deserialize, Serialize};

/// Struct represents a season entry in the database.
#[derive(Serialize, Deserialize, PartialEq, Debug, Clone)]
pub struct Season {
    pub id: i64,
    /// Season number
    pub season_number: i64,
    /// Foreign key to the tv show we'd like to link against
    pub tvshowid: i64,
    /// String holding the date when the season was added to the database.
    pub added: Option<String>,
    /// Id of the asset pointing to the poster.
    pub poster: Option<String>,
}

impl Season {
    /// Method returns all of the seasons that are linked to a tv show based on a tvshow id
    ///
    /// # Arguments
    /// * `conn` - mutable reference to a sqlx transaction.
    /// * `tv_id` - id of the tv show we'd like to discriminate against.
    pub async fn get_all(
        conn: &mut crate::Transaction<'_>,
        tv_id: i64,
    ) -> Result<Vec<Self>, DatabaseError> {
        Ok(sqlx::query_as!(
            Self,
            r#"SELECT id as "id!", season_number, tvshowid, added, poster as "poster?" FROM season WHERE tvshowid = ?"#,
            tv_id
        )
        .fetch_all(&mut *conn)
        .await?)
    }

    /// Method returns the season based on the season number belonging to a tv show.
    ///
    /// # Arguments
    /// * `conn` - mutable reference to a sqlx transaction.
    /// * `tv_id` - id of the tv show we'd like to discriminate against.
    /// * `season_num` - season number we'd like to fetch.
    pub async fn get(
        conn: &mut crate::Transaction<'_>,
        tv_id: i64,
        season_num: i64,
    ) -> Result<Season, DatabaseError> {
        Ok(sqlx::query_as!(
            Self,
            r#"SELECT id as "id!", season_number ,
                    tvshowid , added, poster as "poster?"
               FROM season WHERE id = ? AND season_number = ?"#,
            tv_id,
            season_num
        )
        .fetch_one(&mut *conn)
        .await?)
    }

    /// Method deletes a season entry that belongs to a tv show.
    ///
    /// # Arguments
    /// * `conn` - mutable reference to a sqlx transaction.
    /// * `tv_id` - id of the tv show we'd like to discriminate against.
    /// * `season_num` - season number we'd like to fetch.
    pub async fn delete(
        conn: &mut crate::Transaction<'_>,
        tv_id: i64,
        season_num: i64,
    ) -> Result<usize, DatabaseError> {
        Ok(sqlx::query!(
            "DELETE FROM season where tvshowid = ? AND season_number = ?",
            tv_id,
            season_num
        )
        .execute(&mut *conn)
        .await?
        .rows_affected() as usize)
    }

    pub async fn delete_by_id(
        conn: &mut crate::Transaction<'_>,
        season_id: i64,
    ) -> Result<usize, DatabaseError> {
        Ok(
            sqlx::query!("DELETE FROM _tblseason where id = ?", season_id)
                .execute(&mut *conn)
                .await?
                .rows_affected() as usize,
        )
    }

    /// Method will return the oldest season for a tv show that is available.
    ///
    /// # Arguments
    /// * `conn` - mutable reference to a sqlx transaction.
    /// * `tv_id` - id of the tv show.
    pub async fn get_first(
        conn: &mut crate::Transaction<'_>,
        tv_id: i64,
    ) -> Result<Self, DatabaseError> {
        Ok(sqlx::query_as!(
            Self,
            r#"SELECT id as "id!", season_number, tvshowid, added, poster as "poster?"
            FROM season
            WHERE tvshowid = ?
            ORDER BY season_number ASC"#,
            tv_id,
        )
        .fetch_one(&mut *conn)
        .await?)
    }

    pub async fn get_by_id(
        conn: &mut crate::Transaction<'_>,
        season_id: i64,
    ) -> Result<Self, DatabaseError> {
        Ok(sqlx::query_as!(
            Self,
            r#"SELECT id, season_number, tvshowid, added, poster as "poster?"
            FROM season WHERE id = ?"#,
            season_id,
        )
        .fetch_one(&mut *conn)
        .await?)
    }
}

/// Struct representing a insertable season
/// Its exactly the same as [`Season`](Season) except it misses the tvshowid field and the id
/// field.
#[derive(Clone, Serialize, Deserialize, Default)]
pub struct InsertableSeason {
    pub season_number: i64,
    pub added: String,
    pub poster: Option<i64>,
}

impl InsertableSeason {
    /// Method inserts a new season and links it to a tv show based on the id specified.
    ///
    /// # Arguments
    /// * `conn` - mutable reference to a sqlx transaction.
    /// * `id` - id of the tv show we'd like to link this season to
    pub async fn insert(
        &self,
        conn: &mut crate::Transaction<'_>,
        id: i64,
    ) -> Result<i64, DatabaseError> {
        let result = sqlx::query!(
            r#"SELECT id as "id!" FROM _tblseason WHERE season_number = ? AND tvshowid = ?"#,
            self.season_number,
            id
        )
        .fetch_optional(&mut *conn)
        .await?;

        if let Some(season) = result {
            return Ok(season.id);
        }

        let id = sqlx::query!(
            r#"INSERT INTO _tblseason (season_number, added, poster, tvshowid)
            VALUES ($1, $2, $3, $4)
            ON CONFLICT DO UPDATE
            SET poster = $3
            RETURNING id as "id!: i64""#,
            self.season_number,
            self.added,
            self.poster,
            id
        )
        .fetch_one(&mut *conn)
        .await?
        .id;

        Ok(id)
    }
}

/// Struct used to update information about a season in the database.
/// All fields are updateable and optional except the primary key id
#[derive(Clone, Default, Deserialize, PartialEq, Debug)]
pub struct UpdateSeason {
    pub season_number: Option<i64>,
    pub tvshowid: Option<i64>,
    pub added: Option<String>,
    pub poster: Option<i64>,
}

impl UpdateSeason {
    /// Method updates a seasons entry based on tv show id and season number.
    ///
    /// # Arguments
    /// * `conn` - mutable reference to a sqlx transaction.
    /// * `id` - id of the tv show we'd like to discriminate against.
    /// * `season_num` - Season number we'd like to update.
    pub async fn update(
        self,
        conn: &mut crate::Transaction<'_>,
        id: i64,
    ) -> Result<usize, DatabaseError> {
        opt_update!(conn,
            "UPDATE _tblseason SET season_number = $1 WHERE id = ?2" => (self.season_number, id),
            "UPDATE _tblseason SET tvshowid = $1 WHERE id = ?2" => (self.tvshowid, id),
            "UPDATE _tblseason SET added = $1 WHERE id = ?2" => (self.added, id),
            "UPDATE _tblseason SET poster = $1 WHERE id = ?2" => (self.poster, id)
        );

        Ok(1)
    }
}