package com.fabledsword.minstrel.cache.db.dao

import androidx.room.Dao
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import com.fabledsword.minstrel.cache.db.entities.CachedAlbumEntity
import kotlinx.coroutines.flow.Flow

@Dao
interface CachedAlbumDao {
    @Query("SELECT * FROM cached_albums ORDER BY sortTitle COLLATE NOCASE ASC")
    fun observeAll(): Flow<List<CachedAlbumEntity>>

    @Query(
        "SELECT * FROM cached_albums " +
            "WHERE artistId = :artistId ORDER BY sortTitle COLLATE NOCASE ASC",
    )
    fun observeByArtist(artistId: String): Flow<List<CachedAlbumEntity>>

    @Query("SELECT * FROM cached_albums WHERE id = :id")
    suspend fun getById(id: String): CachedAlbumEntity?

    @Query("SELECT * FROM cached_albums WHERE id = :id")
    fun observeById(id: String): Flow<CachedAlbumEntity?>

    @Query(
        "SELECT * FROM cached_albums " +
            "WHERE title LIKE '%' || :q || '%' COLLATE NOCASE " +
            "ORDER BY sortTitle COLLATE NOCASE ASC " +
            "LIMIT :limit",
    )
    suspend fun searchByTitle(q: String, limit: Int): List<CachedAlbumEntity>

    @Query("SELECT id FROM cached_albums WHERE fetchedAt < :before LIMIT :limit")
    suspend fun idsStaleBefore(before: Long, limit: Int): List<String>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun upsertAll(rows: List<CachedAlbumEntity>)

    @Query("DELETE FROM cached_albums WHERE id IN (:ids)")
    suspend fun deleteByIds(ids: List<String>)
}
