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.CachedPlaylistEntity
import kotlinx.coroutines.flow.Flow

@Dao
interface CachedPlaylistDao {
    /** All playlists, user + system, sorted by name. */
    @Query("SELECT * FROM cached_playlists ORDER BY name COLLATE NOCASE ASC")
    fun observeAll(): Flow<List<CachedPlaylistEntity>>

    /** User playlists only (systemVariant IS NULL) — for the add-to-playlist sheet. */
    @Query(
        "SELECT * FROM cached_playlists " +
            "WHERE systemVariant IS NULL ORDER BY name COLLATE NOCASE ASC",
    )
    fun observeUserPlaylists(): Flow<List<CachedPlaylistEntity>>

    /** System playlists for the home screen. */
    @Query(
        "SELECT * FROM cached_playlists " +
            "WHERE systemVariant IS NOT NULL ORDER BY name COLLATE NOCASE ASC",
    )
    fun observeSystemPlaylists(): Flow<List<CachedPlaylistEntity>>

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

    /**
     * Per-playlist count of member tracks resident in the audio cache index.
     * LEFT JOINs so playlists with zero cached tracks still appear
     * (cachedCount = 0). Drives the offline "fully cached" greying.
     */
    @Query(
        "SELECT p.id AS playlistId, COUNT(a.trackId) AS cachedCount " +
            "FROM cached_playlists p " +
            "LEFT JOIN cached_playlist_tracks t ON t.playlistId = p.id " +
            "LEFT JOIN audio_cache_index a ON a.trackId = t.trackId " +
            "GROUP BY p.id",
    )
    fun observeCachedCounts(): Flow<List<PlaylistCachedCount>>

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

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

    @Query("DELETE FROM cached_playlists WHERE userId = :userId")
    suspend fun deleteAllOwned(userId: String)

    @Query("DELETE FROM cached_playlists WHERE userId = :userId AND id NOT IN (:keepIds)")
    suspend fun deleteOwnedNotIn(userId: String, keepIds: List<String>)

    /**
     * Atomically reconciles the cache against the fresh list response.
     * Mirrors `flutter_client/lib/playlists/playlists_provider.dart:54` —
     * `BuildSystemPlaylists` rotates system-playlist UUIDs every
     * rebuild, so upsert alone leaves stale rows whose detail fetch
     * 404s. Delete any of the user's rows not in [freshOwnedIds] (this
     * catches both deleted owned playlists AND old system-playlist
     * UUIDs, since system playlists are user-scoped and appear in
     * [all] under their new id rather than in [freshOwnedIds]), then
     * upsert the fresh set.
     */
    @Transaction
    suspend fun replaceList(
        userId: String,
        freshOwnedIds: List<String>,
        all: List<CachedPlaylistEntity>,
    ) {
        if (freshOwnedIds.isEmpty()) {
            deleteAllOwned(userId)
        } else {
            deleteOwnedNotIn(userId, freshOwnedIds)
        }
        upsertAll(all)
    }
}
