1 files changed, 50 insertions, 9 deletions
diff --git a/src/db/artworks.rs b/src/db/artworks.rs
index 619f42d..0b62d1d 100644
--- a/src/db/artworks.rs
+++ b/src/db/artworks.rs
@@ -1,13 +1,49 @@
 use sqlx::{Pool, Postgres};
 use uuid::Uuid;
 
-use crate::artist::Artist;
-use crate::artwork::Artwork;
-use crate::file::File;
 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>);
 
@@ -21,16 +57,17 @@ impl Artworks {
     }
 
     pub async fn create(&self, artwork: Artwork) -> Result<i32> {
-        let artist_id = if let Some(artist_id) = artwork.artist.id() {
+        // 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 id", artwork.title, artwork.description, artwork.url_source, artist_id).fetch_one(&self.0).await?.id;
+        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 (id, alt_text, extension, artwork_id) values ($1, $2, $3, $4)",
-                file.id(),
+                "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
@@ -43,8 +80,12 @@ impl Artworks {
 
     pub async fn read_all(&self) -> Result<Vec<Artwork>> {
         // TODO: join comments and files
-        Ok(sqlx::query_as(
-            "select * from artworks left join artists on artworks.artist_id = artists.id left join artwork_files on artworks.id = artwork_files.artwork_id group by artworks.id, artists.id, artwork_files.id",
+        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?)