package com.placeholder.sherpai2.data.local.dao

import androidx.room.Dao
import androidx.room.Query
import androidx.room.Transaction
import com.placeholder.sherpai2.data.local.model.ImageWithEverything
import kotlinx.coroutines.flow.Flow

@Dao
interface ImageAggregateDao {

    /**
     * Observe a fully-hydrated image object.
     */
    @Transaction
    @Query("""
        SELECT * FROM images
        WHERE imageId = :imageId
    """)
    fun observeImageWithEverything(
        imageId: String
    ): Flow<ImageWithEverything>

    /**
     * Observe all images.
     */
    @Transaction
    @Query("""
        SELECT * FROM images
        ORDER BY capturedAt DESC
    """)
    fun observeAllImagesWithEverything(): Flow<List<ImageWithEverything>>

    /**
     * Observe images filtered by tag value.
     *
     * Joins images -> image_tags -> tags
     */
    @Transaction
    @Query("""
        SELECT images.* FROM images
        INNER JOIN image_tags ON images.imageId = image_tags.imageId
        INNER JOIN tags ON tags.tagId = image_tags.tagId
        WHERE tags.value = :tag
        ORDER BY images.capturedAt DESC
    """)
    fun observeImagesWithTag(tag: String): Flow<List<ImageWithEverything>>
}
