package org.terst.feedreader.data

import androidx.room.Dao
import androidx.room.Delete
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import androidx.room.Update
import kotlinx.coroutines.flow.Flow

@Dao
interface FeedDao {
    @Query("SELECT * FROM feeds ORDER BY title ASC")
    fun observeAll(): Flow<List<FeedEntity>>

    @Query("SELECT * FROM feeds ORDER BY title ASC")
    suspend fun getAll(): List<FeedEntity>

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

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun upsert(feed: FeedEntity)

    @Update
    suspend fun update(feed: FeedEntity)

    @Query("DELETE FROM feeds WHERE id = :id")
    suspend fun delete(id: String)
}

data class FeedUnreadCount(val feedId: String, val count: Int)

// A projection excluding feedContentHtml/extractedContent -- the potentially
// large per-article content blobs that list views (row title/date/thumbnail)
// never need. Selecting them for every row of a big result set is what was
// crashing the Unread view once real feed volume (107 feeds worth of
// articles) hit it: Android's per-row/CursorWindow limits are sized for
// "many small rows," not "many rows each carrying a full article's HTML."
data class ArticleListItem(
    val id: String,
    override val feedId: String?,
    override val title: String,
    val link: String,
    val author: String?,
    val publishedAt: Long?,
    val imageUrl: String?,
    val isRead: Boolean,
    val isSaved: Boolean,
    val stashArtifactId: String?,
    val createdAt: Long,
    val feedTitle: String?  // via LEFT JOIN feeds -- null for standalone share-sheet saves with no feedId
) : Titled

private const val ARTICLE_LIST_COLUMNS =
    "articles.id, articles.feedId, articles.title, articles.link, articles.author, articles.publishedAt, " +
        "articles.imageUrl, articles.isRead, articles.isSaved, articles.stashArtifactId, articles.createdAt, " +
        "feeds.title AS feedTitle"

private const val ARTICLE_LIST_FROM = "articles LEFT JOIN feeds ON articles.feedId = feeds.id"

@Dao
interface ArticleDao {
    // Unread-first, newest-first -- the default "what's new" view across every feed.
    @Query("SELECT $ARTICLE_LIST_COLUMNS FROM $ARTICLE_LIST_FROM WHERE articles.isRead = 0 ORDER BY articles.publishedAt DESC")
    fun observeUnread(): Flow<List<ArticleListItem>>

    @Query("SELECT feedId, COUNT(*) as count FROM articles WHERE isRead = 0 AND feedId IS NOT NULL GROUP BY feedId")
    fun observeUnreadCounts(): Flow<List<FeedUnreadCount>>

    @Query("SELECT $ARTICLE_LIST_COLUMNS FROM $ARTICLE_LIST_FROM WHERE articles.feedId = :feedId ORDER BY articles.publishedAt DESC")
    fun observeByFeed(feedId: String): Flow<List<ArticleListItem>>

    @Query("SELECT $ARTICLE_LIST_COLUMNS FROM $ARTICLE_LIST_FROM WHERE articles.isSaved = 1 ORDER BY articles.createdAt DESC")
    fun observeSaved(): Flow<List<ArticleListItem>>

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

    // IGNORE, not REPLACE: a re-fetch of a feed re-parses items we already have:
    // this keeps the existing row (read state, extracted content, saved state)
    // untouched instead of clobbering it with the freshly-parsed feed data.
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun insertNew(articles: List<ArticleEntity>): List<Long>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun upsert(article: ArticleEntity)

    @Query("UPDATE articles SET isRead = :read WHERE id = :id")
    suspend fun setRead(id: String, read: Boolean)

    @Query("UPDATE articles SET isRead = 1 WHERE feedId = :feedId")
    suspend fun markAllReadForFeed(feedId: String)

    // Run when unsubscribing from a feed. Saved articles are deliberately kept
    // regardless (isSaved is a separate, durable "keep forever" concept,
    // independent of subscription) -- everything else is deleted so it doesn't
    // linger forever as an orphaned row cluttering the cross-feed Unread view.
    @Query("DELETE FROM articles WHERE feedId = :feedId AND isSaved = 0")
    suspend fun deleteUnsavedByFeed(feedId: String)

    @Query("UPDATE articles SET extractedContent = :content WHERE id = :id")
    suspend fun setExtractedContent(id: String, content: String)

    @Query("UPDATE articles SET isSaved = :saved WHERE id = :id")
    suspend fun setSaved(id: String, saved: Boolean)

    @Query("UPDATE articles SET stashArtifactId = :artifactId WHERE id = :id")
    suspend fun setStashArtifactId(id: String, artifactId: String)

    @Delete
    suspend fun delete(article: ArticleEntity)
}

@Dao
interface FilterRuleDao {
    @Query("SELECT * FROM filter_rules ORDER BY createdAt DESC")
    fun observeAll(): Flow<List<FilterRuleEntity>>

    @Query("SELECT * FROM filter_rules")
    suspend fun getAll(): List<FilterRuleEntity>

    @Insert
    suspend fun insert(rule: FilterRuleEntity): Long

    @Query("DELETE FROM filter_rules WHERE id = :id")
    suspend fun delete(id: Long)
}