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 COLLATE NOCASE ASC")
    fun observeAll(): Flow<List<FeedEntity>>

    @Query("SELECT * FROM feeds ORDER BY title COLLATE NOCASE 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 {
    // The default "what's new" view -- clamped to the last RECENT_WINDOW_MS (see
    // MainActivity) so a backlog of hundreds of older unread items doesn't bury
    // what's actually new. Undated articles (publishedAt failed to parse) are kept
    // here rather than silently dropped from both views.
    @Query(
        "SELECT $ARTICLE_LIST_COLUMNS FROM $ARTICLE_LIST_FROM " +
            "WHERE articles.isRead = 0 AND (articles.publishedAt IS NULL OR articles.publishedAt >= :cutoff) " +
            "ORDER BY articles.publishedAt DESC"
    )
    fun observeUnread(cutoff: Long): Flow<List<ArticleListItem>>

    // The rest of the unread backlog, older than the cutoff -- its own view rather
    // than mixed into the main one.
    @Query(
        "SELECT $ARTICLE_LIST_COLUMNS FROM $ARTICLE_LIST_FROM " +
            "WHERE articles.isRead = 0 AND articles.publishedAt IS NOT NULL AND articles.publishedAt < :cutoff " +
            "ORDER BY articles.publishedAt DESC"
    )
    fun observeOlderUnread(cutoff: Long): Flow<List<ArticleListItem>>

    @Query("SELECT COUNT(*) FROM articles WHERE isRead = 0 AND (publishedAt IS NULL OR publishedAt >= :cutoff)")
    fun observeUnreadCount(cutoff: Long): Flow<Int>

    @Query("SELECT COUNT(*) FROM articles WHERE isRead = 0 AND publishedAt IS NOT NULL AND publishedAt < :cutoff")
    fun observeOlderUnreadCount(cutoff: Long): Flow<Int>

    // Per-feed unread badge counts in the feed list -- deliberately NOT time-clamped,
    // so a feed's number reflects its whole backlog regardless of the Unread/Older split.
    @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>>

    // The Saved count is a "how much is on my shelf" total, not an unread badge --
    // includes already-read articles, unlike every other count in this DAO.
    @Query("SELECT COUNT(*) FROM articles WHERE isSaved = 1")
    fun observeSavedCount(): Flow<Int>

    @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)

    // Explicit bulk action ("mark read from here") -- batched per confirmed tap,
    // never called implicitly (e.g. by scrolling).
    @Query("UPDATE articles SET isRead = 1 WHERE id IN (:ids)")
    suspend fun markReadBatch(ids: List<String>)

    @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 extractedContent = NULL WHERE id = :id")
    suspend fun clearExtractedContent(id: 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)
}

// A single markReadBatch(ids) call builds one query with one bound parameter per id --
// "mark read from here" can realistically span hundreds of rows in a long-unread feed,
// and SQLite has a hard per-statement limit on bound variables (historically 999 on many
// Android SQLite builds). Chunking well under any realistic limit means this can't throw
// "too many SQL variables" and crash on an otherwise routine bulk mark-read tap.
suspend fun ArticleDao.markReadBatchChunked(ids: List<String>) {
    ids.chunked(500).forEach { chunk -> markReadBatch(chunk) }
}

@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)
}