get.rs

use crate::{
    demon::MinimalDemon,
    error::{DemonlistError, Result},
    nationality::Nationality,
    player::DatabasePlayer,
    record::{FullRecord, MinimalRecordD, MinimalRecordP, RecordStatus},
    submitter::Submitter,
};
use chrono::{NaiveDateTime, TimeZone, Utc};
use futures::stream::StreamExt;
use sqlx::{Error, PgConnection};

// Required until https://github.com/launchbadge/sqlx/pull/108 is merged
struct FetchedRecord {
    progress: i16,
    video: Option<String>,
    raw_footage: Option<String>,
    status: String,
    date: NaiveDateTime,
    player_id: i32,
    player_name: String,
    player_banned: bool,
    demon_id: i32,
    demon_name: String,
    position: i16,
    submitter_id: i32,
    submitter_banned: bool,
}

impl FullRecord {
    pub async fn by_id(id: i32, connection: &mut PgConnection) -> Result<FullRecord> {
        let result = sqlx::query_file_as!(FetchedRecord, "sql/record_by_id.sql", id)
            .fetch_one(&mut *connection)
            .await;

        match result {
            Ok(row) => Ok(FullRecord {
                id,
                progress: row.progress,
                video: row.video,
                raw_footage: row.raw_footage,
                status: RecordStatus::from_sql(&row.status),
                player: DatabasePlayer {
                    id: row.player_id,
                    name: row.player_name,
                    banned: row.player_banned,
                },
                demon: MinimalDemon {
                    id: row.demon_id,
                    position: row.position,
                    name: row.demon_name,
                },
                submitter: Some(Submitter {
                    id: row.submitter_id,
                    banned: row.submitter_banned,
                }),
                date: Utc.from_utc_datetime(&row.date),
            }),

            Err(Error::RowNotFound) => Err(DemonlistError::RecordNotFound { record_id: id }),
            Err(err) => Err(err.into()),
        }
    }
}

pub async fn approved_records_by(player: &DatabasePlayer, connection: &mut PgConnection) -> Result<Vec<MinimalRecordD>> {
    let mut stream = sqlx::query!(
        r#"SELECT records.id, progress, CASE WHEN players.link_banned THEN NULL ELSE records.video::text END, demons.id AS demon_id, 
         demons.name, demons.position FROM records INNER JOIN demons ON records.demon = demons.id INNER JOIN players ON players.id 
         = $1 WHERE status_ = 'APPROVED' AND records.player = $1"#,
        player.id
    )
    .fetch(connection);

    let mut records = Vec::new();

    while let Some(row) = stream.next().await {
        let row = row?;

        records.push(MinimalRecordD {
            id: row.id,
            progress: row.progress,
            video: row.video,
            status: RecordStatus::Approved,
            demon: MinimalDemon {
                id: row.demon_id,
                position: row.position,
                name: row.name,
            },
        })
    }

    Ok(records)
}

pub async fn approved_records_on(demon: &MinimalDemon, connection: &mut PgConnection) -> Result<Vec<MinimalRecordP>> {
    struct Fetched {
        id: i32,
        progress: i16,
        video: Option<String>,
        player_id: i32,
        name: String,
        banned: bool,
        nation: Option<String>,
        iso_country_code: Option<String>,
    }

    let mut stream = sqlx::query_as!(
        Fetched,
        r#"SELECT records.id, progress, CASE WHEN players.link_banned THEN NULL ELSE video::text END, players.id AS player_id, 
         players.name, players.banned, nation::TEXT, iso_country_code::TEXT FROM records INNER JOIN players ON records.player = players.id LEFT OUTER JOIN nationalities ON nationality = iso_country_code WHERE status_ = 'APPROVED' AND 
         records.demon = $1 ORDER BY progress DESC, date ASC"#,
        demon.id
    )
    .fetch(connection);

    let mut records = Vec::new();

    while let Some(row) = stream.next().await {
        let row = row?;

        records.push(MinimalRecordP {
            id: row.id,
            progress: row.progress,
            video: row.video,
            status: RecordStatus::Approved,
            player: DatabasePlayer {
                id: row.player_id,
                name: row.name,
                banned: row.banned,
            },
            nationality: match (row.nation, row.iso_country_code) {
                (Some(nation), Some(code)) => Some(Nationality {
                    iso_country_code: code,
                    nation,
                    subdivision: None, // don't display states in the records list
                }),
                _ => None,
            },
        })
    }

    Ok(records)
}

pub async fn submission_count(connection: &mut PgConnection) -> Result<i64> {
    Ok(sqlx::query!("SELECT COUNT(*) FROM records WHERE status_='SUBMITTED'")
        .fetch_one(connection)
        .await?
        .count
        .unwrap_or_default())
}

#[cfg(test)]
mod test {
    use sqlx::{pool::PoolConnection, Postgres};

    use crate::record::get::submission_count;

    #[sqlx::test(migrations = "../migrations")]
    fn test_submission_count(mut conn: PoolConnection<Postgres>) {
        assert_eq!(submission_count(&mut conn).await.unwrap(), 0);
    }
}