use sqlx::{Pool, Postgres};
use uuid::Uuid;

use crate::Result;

use super::Database;

use time::{OffsetDateTime, PrimitiveDateTime};

use crate::{artist::Artist, comment::Comment, file::File};

#[derive(sqlx::FromRow)]
pub struct Artwork {
    /// artwork id
    artwork_id: Option<i32>,
    /// name of the artwork
    pub title: Option<String>,
    /// description of the artwork
    pub description: Option<String>,
    /// source url of the artwork
    pub url_source: Option<String>,
    /// artwork creation time
    created_at: Option<PrimitiveDateTime>,
    /// id of the artist
    pub artist: Artist,
    /// ids of files
    pub files: Vec<File>,
    // /// TODO: comments in thread,
    // #[sqlx(Flatten)]
    // comments: Vec<Comment>,
}

impl Artwork {
    pub fn new(title: Option<String>, description: Option<String>, url_source: Option<String>, artist: Artist, files: Vec<File>) -> Self {
        Self {
            artwork_id: None,
            title,
            description,
            url_source,
            created_at: None,
            artist,
            files,
        }
    }
}

#[derive(Clone)]
pub struct Artworks(Pool<Postgres>);

impl Artworks {
    pub fn new(pool: Pool<Postgres>) -> Self {
        Self(pool)
    }

    pub fn downcast(&self) -> Database {
        Database(self.0.clone())
    }

    pub async fn create(&self, artwork: Artwork) -> Result<i32> {
        // TODO: efficiency?
        let artist_id = if let Some(artist_id) = self.downcast().artists().read_handle(&artwork.artist.handle).await.map(|artist| artist.artist_id()).unwrap_or(artwork.artist.artist_id()) {
            artist_id
        } else {
            self.downcast().artists().create(artwork.artist).await?
        };
        let artwork_id = sqlx::query!("insert into artworks (title, description, url_source, artist_id) values ($1, $2, $3, $4) returning artwork_id", artwork.title, artwork.description, artwork.url_source, artist_id).fetch_one(&self.0).await?.artwork_id;
        for file in artwork.files {
            sqlx::query!(
                "insert into artwork_files (file_id, alt_text, extension, artwork_id) values ($1, $2, $3, $4)",
                file.file_id(),
                file.alt_text,
                file.extension(),
                artwork_id
            )
            .execute(&self.0)
            .await?;
        }
        Ok(artwork_id)
    }

    pub async fn read_all(&self) -> Result<Vec<Artwork>> {
        // TODO: join comments and files
        Ok(sqlx::query_as!(Artwork, 
            r#"select artworks.artwork_id, artworks.title, artworks.description, artworks.url_source, artworks.created_at, coalesce(artists.*) as "artist!: Artist", coalesce(array_agg((artwork_files.file_id, artwork_files.alt_text, artwork_files.extension, artwork_files.artwork_id)) filter (where artwork_files.file_id is not null), '{}') as "files!: Vec<File>" 
            from artworks
            left join artists on artworks.artist_id = artists.artist_id 
            left join artwork_files on artworks.artwork_id = artwork_files.artwork_id 
            group by artworks.artwork_id, artists.artist_id"#,
        )
        .fetch_all(&self.0)
        .await?)
    }
}