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

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

@Dao
interface CachedPlaylistTrackDao {
    @Query(
        "SELECT * FROM cached_playlist_tracks " +
            "WHERE playlistId = :playlistId ORDER BY position ASC",
    )
    fun observeByPlaylist(playlistId: String): Flow<List<CachedPlaylistTrackEntity>>

    @Query(
        "SELECT * FROM cached_playlist_tracks " +
            "WHERE playlistId = :playlistId ORDER BY position ASC",
    )
    suspend fun getByPlaylist(playlistId: String): List<CachedPlaylistTrackEntity>

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

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun insertOrIgnore(row: CachedPlaylistTrackEntity)

    /**
     * Highest existing position for [playlistId], or null if the
     * playlist has no rows. Used by appendTrack to assign the next
     * position before the optimistic Room write.
     */
    @Query("SELECT MAX(position) FROM cached_playlist_tracks WHERE playlistId = :playlistId")
    suspend fun maxPosition(playlistId: String): Int?

    @Query("DELETE FROM cached_playlist_tracks WHERE playlistId = :playlistId")
    suspend fun deleteByPlaylist(playlistId: String)

    /**
     * Replaces a playlist's whole membership in ONE transaction; called
     * after a refresh or a sync delta lands.
     *
     * Atomic on purpose. Room's InvalidationTracker only notifies observers
     * after the transaction commits, so [observeByPlaylist] never sees the
     * empty gap between the delete and the re-insert. Un-transacted, that
     * gap is a real observed state — it's what made every Home row visibly
     * collapse to empty and refill before issue #2327 fixed the equivalent
     * write in `CachedHomeIndexDao`.
     *
     * Nothing observes [observeByPlaylist] live today, so this is
     * pre-emptive: it means making playlist detail cache-first later can't
     * silently reintroduce that flicker.
     */
    @Transaction
    suspend fun replacePlaylistTracks(
        playlistId: String,
        rows: List<CachedPlaylistTrackEntity>,
    ) {
        deleteByPlaylist(playlistId)
        if (rows.isNotEmpty()) upsertAll(rows)
    }

    @Query(
        "DELETE FROM cached_playlist_tracks " +
            "WHERE playlistId = :playlistId AND trackId IN (:trackIds)",
    )
    suspend fun deleteTracksFromPlaylist(playlistId: String, trackIds: List<String>)
}
