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)

    // Batched, not one upsert() call per feed in a loop: an OPML import can realistically
    // be 100+ feeds (this app has seen a 107-feed subscription list in the wild -- see
    // AppDatabase's v9 migration comments), and a per-feed getById()+upsert() pair is 2N
    // sequential suspend DB round-trips for N feeds. One @Insert(REPLACE) call covers the
    // whole list.
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun upsertAll(feeds: List<FeedEntity>)

    @Update
    suspend fun update(feed: FeedEntity)

    // Targeted, not the full-row @Update above: FeedFetchWorker reads every subscribed
    // feed once at the start of a fetch cycle, then (after a per-feed network round-trip
    // that can take a while, especially across 100+ feeds) writes back a `.copy()` of
    // that now-possibly-stale snapshot. If the user toggles readerModeEnabled or edits a
    // feed's folder via FeedListScreen while that feed's fetch is still in flight, a
    // full-row update() overwrites the WHOLE row from the stale pre-fetch copy --
    // including the field the user just changed -- silently reverting their edit. This
    // only ever touches the columns a fetch actually produces, so it can't clobber
    // anything the user might be changing concurrently.
    @Query(
        "UPDATE feeds SET title = :title, siteUrl = :siteUrl, lastFetchedAt = :lastFetchedAt, " +
            "etag = :etag, lastModified = :lastModified, lastError = :lastError WHERE id = :id"
    )
    suspend fun updateFetchResult(
        id: String,
        title: String,
        siteUrl: String?,
        lastFetchedAt: Long,
        etag: String?,
        lastModified: String?,
        lastError: String?
    )

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

// Persists the toggle and returns the exact confirmation-snackbar text, shared by
// FeedListScreen's per-row toggle and ReaderScreen's toggleFeedReaderMode (which layers
// its own extraction logic around this same persist-and-message step) -- previously two
// independently-typed copies of "Reader mode enabled/disabled for $label", one edit away
// from silently drifting to say different things for the same action.
suspend fun FeedDao.setReaderModeEnabled(feed: FeedEntity, enabled: Boolean): String {
    update(feed.copy(readerModeEnabled = enabled))
    val label = feed.title.ifBlank { "this feed" }
    return if (enabled) "Reader mode enabled for $label" else "Reader mode disabled for $label"
}

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.
    //
    // "isRead = 0 OR readAt >= sinceFetch" (not just "isRead = 0"): a just-read article
    // stays in this view until the NEXT fetch completes, not the instant it's marked
    // read. Without this, Room's live Flow re-emits the moment isRead flips, so (a) a
    // read row vanishes out from under you mid-scroll, and (b) the reader's Previous
    // button -- which re-queries this same flow to find its neighbors -- can no longer
    // find the article you just came from, since Next already marked it read a moment
    // earlier. sinceFetch is Settings.lastFetchAt, the same durable "when did a fetch
    // last finish" signal the new-articles snackbar already uses.
    @Query(
        "SELECT $ARTICLE_LIST_COLUMNS FROM $ARTICLE_LIST_FROM " +
            "WHERE (articles.isRead = 0 OR articles.readAt >= :sinceFetch) AND (articles.publishedAt IS NULL OR articles.publishedAt >= :cutoff) " +
            "ORDER BY articles.publishedAt DESC"
    )
    fun observeUnread(cutoff: Long, sinceFetch: Long): Flow<List<ArticleListItem>>

    // The rest of the unread backlog, older than the cutoff -- its own view rather
    // than mixed into the main one. See observeUnread for why sinceFetch is here.
    @Query(
        "SELECT $ARTICLE_LIST_COLUMNS FROM $ARTICLE_LIST_FROM " +
            "WHERE (articles.isRead = 0 OR articles.readAt >= :sinceFetch) AND articles.publishedAt IS NOT NULL AND articles.publishedAt < :cutoff " +
            "ORDER BY articles.publishedAt DESC"
    )
    fun observeOlderUnread(cutoff: Long, sinceFetch: 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)

    // readAt is caller-supplied (not CURRENT_TIMESTAMP in SQL) so it's directly comparable
    // to Settings.lastFetchAt, which is also a Kotlin System.currentTimeMillis() epoch
    // value, not a SQLite datetime string. Pass null when read=false (explicit "mark
    // unread") so a stale readAt can't linger on a row that's unread again.
    @Query("UPDATE articles SET isRead = :read, readAt = :readAt WHERE id = :id")
    suspend fun setRead(id: String, read: Boolean, readAt: Long?)

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

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

    // 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>, readAt: Long = System.currentTimeMillis()) {
    ids.chunked(500).forEach { chunk -> markReadBatch(chunk, readAt) }
}

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