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.CacheSource
import com.fabledsword.minstrel.cache.db.entities.AudioCacheIndexEntity
import kotlinx.coroutines.flow.Flow

@Dao
interface AudioCacheIndexDao {
    /** Used to render the "Downloaded" / "Offline" listing. */
    @Query("SELECT * FROM audio_cache_index ORDER BY lastPlayedAt DESC NULLS LAST")
    fun observeAll(): Flow<List<AudioCacheIndexEntity>>

    @Query("SELECT * FROM audio_cache_index WHERE trackId = :trackId")
    suspend fun get(trackId: String): AudioCacheIndexEntity?

    @Query("SELECT * FROM audio_cache_index WHERE trackId IN (:trackIds)")
    suspend fun getByTrackIds(trackIds: List<String>): List<AudioCacheIndexEntity>

    /** All cached track IDs — bucket-membership lookup for eviction. */
    @Query("SELECT trackId FROM audio_cache_index")
    suspend fun allCachedTrackIds(): List<String>

    /**
     * Cached track IDs most-recently-played first (nulls last so
     * never-played downloads sort after real plays). Backs the
     * offline-pool shuffle sources.
     */
    @Query("SELECT trackId FROM audio_cache_index ORDER BY lastPlayedAt DESC NULLS LAST")
    suspend fun trackIdsByRecency(): List<String>

    /** Eviction-candidate ordering — oldest lastPlayedAt first within a source. */
    @Query(
        "SELECT * FROM audio_cache_index " +
            "WHERE source = :source " +
            "ORDER BY lastPlayedAt ASC NULLS FIRST, cachedAt ASC",
    )
    suspend fun evictionCandidates(source: CacheSource): List<AudioCacheIndexEntity>

    /** Sum of bytes across rows; the rolling/liked bucket caps compare against this. */
    @Query("SELECT COALESCE(SUM(sizeBytes), 0) FROM audio_cache_index")
    suspend fun totalBytes(): Long

    @Query(
        "SELECT COALESCE(SUM(sizeBytes), 0) FROM audio_cache_index WHERE source = :source",
    )
    suspend fun bytesBySource(source: CacheSource): Long

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun upsert(row: AudioCacheIndexEntity)

    @Query(
        "UPDATE audio_cache_index SET lastPlayedAt = :at WHERE trackId = :trackId",
    )
    suspend fun touchLastPlayed(trackId: String, at: kotlinx.datetime.Instant)

    @Query("DELETE FROM audio_cache_index WHERE trackId = :trackId")
    suspend fun deleteByTrackId(trackId: String)

    @Query("DELETE FROM audio_cache_index WHERE trackId IN (:trackIds)")
    suspend fun deleteByTrackIds(trackIds: List<String>)

    @Query("DELETE FROM audio_cache_index")
    suspend fun clear()
}
