package org.terst.feedreader.data

import android.content.Context
import android.util.Log
import androidx.room.Database
import androidx.room.Room
import androidx.room.RoomDatabase
import androidx.sqlite.db.SupportSQLiteDatabase
import androidx.room.migration.Migration

private const val TAG = "FeedMigration"

@Database(
    entities = [FeedEntity::class, ArticleEntity::class, FilterRuleEntity::class],
    version = 11,
    exportSchema = false
)
abstract class AppDatabase : RoomDatabase() {
    abstract fun feedDao(): FeedDao
    abstract fun articleDao(): ArticleDao
    abstract fun filterRuleDao(): FilterRuleDao

    companion object {
        @Volatile private var instance: AppDatabase? = null

        // Renames a feed's id (its primary key) to newUrl, reassigning its articles to
        // match, safely and without duplicating this dance in every migration that needs
        // it (v6-v9 all do). Two things make this safe where a plain pair of UPDATEs
        // wasn't:
        // - The feeds rename happens FIRST, not last. feeds.id is a PRIMARY KEY; if a row
        //   at newUrl already exists (e.g. the user manually re-subscribed at the fixed
        //   URL before this migration got a chance to run -- confirmed as the likely
        //   cause of one v9 entry needing a second fix), that UPDATE throws before
        //   anything touches articles at all. articles.feedId has no FK constraint (Room
        //   doesn't enforce one here), so it essentially can't fail on its own -- putting
        //   the one statement that CAN fail first means a failure leaves nothing
        //   reassigned rather than leaving articles pointing at a feedId that doesn't
        //   correspond to any real feeds row.
        // - The whole thing is wrapped in try/catch: a failure here (or a fix based on
        //   research done outside this device, not the actual on-disk rows) must never
        //   abort Room's migration transaction, which would crash the app on every future
        //   launch -- Room retries the same failing migration every time the DB is opened.
        private fun renameFeedSafely(db: SupportSQLiteDatabase, oldUrl: String, newUrl: String) {
            if (oldUrl == newUrl) return
            try {
                db.execSQL("UPDATE feeds SET id = ? WHERE id = ?", arrayOf(newUrl, oldUrl))
                db.execSQL("UPDATE articles SET feedId = ? WHERE feedId = ?", arrayOf(newUrl, oldUrl))
            } catch (e: Exception) {
                Log.e(TAG, "Failed to rename feed '$oldUrl' -> '$newUrl'", e)
            }
        }

        // v2: added ArticleEntity.imageUrl (thumbnail support). Real migrations,
        // not fallbackToDestructiveMigration, since this app already has real
        // subscriptions/read-state on-device during active development --
        // wiping the DB on every schema tweak would mean re-importing OPML
        // and losing read history each time.
        private val MIGRATION_1_2 = object : Migration(1, 2) {
            override fun migrate(db: SupportSQLiteDatabase) {
                db.execSQL("ALTER TABLE articles ADD COLUMN imageUrl TEXT")
            }
        }

        // v3: added FeedEntity.readerModeDisabled (per-feed reader-mode opt-out, paired with
        // an auto-extract-on-thin-content heuristic). Superseded by v4 below.
        private val MIGRATION_2_3 = object : Migration(2, 3) {
            override fun migrate(db: SupportSQLiteDatabase) {
                db.execSQL("ALTER TABLE feeds ADD COLUMN readerModeDisabled INTEGER NOT NULL DEFAULT 0")
            }
        }

        // v4: replaced readerModeDisabled with readerModeEnabled -- reader mode is no longer
        // ever auto-triggered by a content-length heuristic (that's what kept "auto-switching"
        // kottke articles even after the v3 opt-out); it's now a plain per-feed opt-in, set
        // only by explicitly tapping the reader-mode toggle. Starts everyone fresh at off
        // rather than trying to translate the old opt-out semantics, which don't map cleanly
        // onto "opt in". A plain ADD COLUMN + leaving the old one in place (as v1->v2 and
        // v2->v3 do) crashes here: Room's post-migration validation requires the table's
        // columns to match FeedEntity's fields EXACTLY, and readerModeDisabled isn't a field
        // anymore -- confirmed live ("Migration didn't properly handle: feeds"). SQLite has no
        // DROP COLUMN on the versions bundled with older Android, so this rebuilds the table
        // instead: create with the new shape, copy the columns that still exist, swap in.
        private val MIGRATION_3_4 = object : Migration(3, 4) {
            override fun migrate(db: SupportSQLiteDatabase) {
                db.execSQL(
                    """
                    CREATE TABLE feeds_new (
                        id TEXT NOT NULL PRIMARY KEY,
                        title TEXT NOT NULL,
                        siteUrl TEXT,
                        lastFetchedAt INTEGER,
                        etag TEXT,
                        lastModified TEXT,
                        lastError TEXT,
                        readerModeEnabled INTEGER NOT NULL DEFAULT 0
                    )
                    """.trimIndent()
                )
                db.execSQL(
                    """
                    INSERT INTO feeds_new (id, title, siteUrl, lastFetchedAt, etag, lastModified, lastError)
                    SELECT id, title, siteUrl, lastFetchedAt, etag, lastModified, lastError FROM feeds
                    """.trimIndent()
                )
                db.execSQL("DROP TABLE feeds")
                db.execSQL("ALTER TABLE feeds_new RENAME TO feeds")
            }
        }

        // v5: a feed with a literal empty <guid>/<id> element (confirmed live: a Mother
        // Jones entry) produced an ArticleEntity with id = "" -- FeedParser fell back to
        // itemLink only when the guid was null, not when it was blank. An empty-string
        // primary key broke Navigation Compose's route matching and crashed on tap. The
        // parser is fixed for future fetches; this cleans out any row already on-device
        // with the bad id so it doesn't keep crashing. It'll come back with a real,
        // hash-based id on the next fetch of that feed.
        private val MIGRATION_4_5 = object : Migration(4, 5) {
            override fun migrate(db: SupportSQLiteDatabase) {
                db.execSQL("DELETE FROM articles WHERE trim(id) = ''")
            }
        }

        // v6: added FeedEntity.folder (OPML category, for grouping the feed list). A plain
        // ADD COLUMN is safe here (unlike v3->v4) since folder is a genuinely new field with
        // no prior column it needs to replace/rebuild around.
        private val MIGRATION_5_6 = object : Migration(5, 6) {
            override fun migrate(db: SupportSQLiteDatabase) {
                db.execSQL("ALTER TABLE feeds ADD COLUMN folder TEXT")
            }
        }

        // v7: this app now upgrades http:// feed URLs to https:// at add/import time (see
        // AddFeedScreen.preferHttps), but that only ever runs on the way IN -- feeds already
        // subscribed to before this change stay http:// forever otherwise. Android blocks
        // cleartext by default (targetSdk 28+), so every one of these was failing every fetch
        // ("CLEARTEXT communication ... not permitted"). Checked live (curl) which of this
        // user's http feeds actually serve valid content over https and rewrite just those --
        // three hosts genuinely have no usable HTTPS (no TLS listener at all, a broken TLS
        // handshake, or a certificate that doesn't match the hostname) and are deliberately
        // left alone, since switching them would just trade one permanent failure for another.
        private val MIGRATION_6_7 = object : Migration(6, 7) {
            override fun migrate(db: SupportSQLiteDatabase) {
                val httpToHttps = mapOf(
                    "http://blog.cryptographyengineering.com/feeds/posts/default" to
                        "https://blog.cryptographyengineering.com/feeds/posts/default",
                    "http://mybiasedcoin.blogspot.com/feeds/posts/default?alt=rss" to
                        "https://mybiasedcoin.blogspot.com/feeds/posts/default?alt=rss",
                    "http://processalgebra.blogspot.com/feeds/posts/default?alt=rss" to
                        "https://processalgebra.blogspot.com/feeds/posts/default?alt=rss",
                    "http://www.rowetel.com/blog/?feed=rss2" to
                        "https://www.rowetel.com/blog/?feed=rss2",
                    "http://www.thecodelesscode.com/rss" to
                        "https://www.thecodelesscode.com/rss",
                    // This one's own https endpoint redirects through an insecure (http)
                    // feedburner hop before landing on real content -- point straight at
                    // feedburner's own https endpoint instead of a URL that would still fail
                    // cleartext-blocked partway through the redirect chain.
                    "http://www.divingforpearlsblog.com/feeds/posts/default" to
                        "https://feeds.feedburner.com/blogspot/sjomU"
                )
                for ((oldUrl, newUrl) in httpToHttps) {
                    renameFeedSafely(db, oldUrl, newUrl)
                }
            }
        }

        // v8: one more http feed turned out to have a real https home after all --
        // blog.securitymouse.com's own TLS handshake is broken, but it's actually a Blogger
        // blog under the hood (its feed XML is Blogger's own Atom format, "Server: GSE" on
        // its homepage response), and Blogger blogs are always reachable at a stable,
        // always-https URL keyed by blog ID regardless of a custom domain's own DNS/TLS setup.
        // The other two hosts left over from v7 (bit-player.org, webpolicy.org) genuinely have
        // no https path forward: webpolicy.org's feed itself is dead (last post 2015, long
        // before this check), and bit-player.org's site was rebuilt on a static generator that
        // no longer appears to expose a feed at any path (checked /feed, /feed/, /feed.xml,
        // /rss, /atom.xml, and the homepage's own <link rel="alternate"> -- nothing).
        private val MIGRATION_7_8 = object : Migration(7, 8) {
            override fun migrate(db: SupportSQLiteDatabase) {
                renameFeedSafely(
                    db,
                    "http://blog.securitymouse.com/feeds/posts/default",
                    "https://www.blogger.com/feeds/5803873077727212399/posts/default"
                )
            }
        }

        // v9: batch of feed fixes from a live triage pass over every subscription showing
        // "Last fetch failed" (2026-07-31). Unlike v6-v8's exact-string matches, this uses a
        // host-substring LIKE match for the URL rewrites: the exact previously-stored URL for
        // most of these wasn't known ahead of time (only the feed's title and, for some, the
        // failing host from its error message), so matching on host is what's actually
        // verifiable here. Worst case for a wrong guess is a silent no-op (the row just stays
        // whatever it already was) -- there's no destructive failure mode. Every replacement
        // URL below was fetched live and confirmed to return real RSS/Atom XML before being
        // written into this migration.
        private val MIGRATION_8_9 = object : Migration(8, 9) {
            override fun migrate(db: SupportSQLiteDatabase) {
                // host/old-scheme fragment (LIKE-matched against feeds.id) -> confirmed-working URL.
                val fixes = listOf(
                    "%sailingworld.com%" to "https://www.sailingworld.com/feed/",
                    "%cruisingworld.com%" to "https://www.cruisingworld.com/feed/",
                    "%shapeof.com%" to "https://shapeof.com/rss.xml",
                    "%furbo.org%" to "https://furbo.org/feed/",
                    "%netnewswire.blog%" to "https://netnewswire.blog/feed.xml",
                    "%isdonaldtrumpalive.com%" to "https://isdonaldtrumpalive.com/latest/rss/",
                    "%amateurgourmet.com%" to "https://amateurgourmet.substack.com/feed",
                    // Scoped to the http:// + exact feedburner slug so this can't ever touch
                    // the OTHER feedburner-hosted feed (divingforpearlsblog, migrated in v7)
                    // that already lives at a different path on the same feedburner.com host.
                    "http://feeds.feedburner.com/GoogleOnlineSecurityBlog%" to
                        "https://feeds.feedburner.com/GoogleOnlineSecurityBlog",
                    "http://www.darknet.org.uk%" to "https://www.darknet.org.uk/feed/",
                    // Re-fixes cryptographyengineering.com: v7 already migrated ONE exact old
                    // URL for this blog, but it's showing the same cleartext failure again --
                    // most likely re-subscribed since with a plain http:// URL that missed that
                    // exact-match migration. Scoped to http:// specifically so this can't touch
                    // v7's already-https row even if that one's somehow still present verbatim.
                    "http://blog.cryptographyengineering.com%" to
                        "https://blog.cryptographyengineering.com/feed/"
                )
                // Looked up per pattern, then renamed one row at a time via renameFeedSafely
                // (not one big per-pattern try/catch): this migration is built entirely from
                // live research done outside this device, not from reading its actual on-disk
                // rows first, so a LIKE pattern matching more than one existing row is a real
                // possibility (e.g. two slightly different subscriptions to the same site).
                // Isolating failures per row means one bad row can't stop a pattern's OTHER
                // matches from getting fixed too.
                for ((oldUrlLike, newUrl) in fixes) {
                    val oldUrls = mutableListOf<String>()
                    try {
                        db.query("SELECT id FROM feeds WHERE id LIKE ?", arrayOf(oldUrlLike)).use { cursor ->
                            while (cursor.moveToNext()) oldUrls.add(cursor.getString(0))
                        }
                    } catch (e: Exception) {
                        Log.e(TAG, "v9 lookup failed for pattern '$oldUrlLike'", e)
                    }
                    for (oldUrl in oldUrls) {
                        renameFeedSafely(db, oldUrl, newUrl)
                    }
                }

                // Two feeds from the same triage pass whose failing error (a real XML parse
                // failure, e.g. "Parameter entity references are not supported") confirmed a
                // feed genuinely exists, but neither title resolved to a specific identifiable
                // blog -- dropped outright rather than guessing at a replacement URL.
                val titlesToDrop = listOf("%Knowing and Doing%", "%Malware Reversing%")
                for (titleLike in titlesToDrop) {
                    try {
                        db.execSQL(
                            "DELETE FROM articles WHERE feedId IN (SELECT id FROM feeds WHERE title LIKE ?)",
                            arrayOf(titleLike)
                        )
                        db.execSQL("DELETE FROM feeds WHERE title LIKE ?", arrayOf(titleLike))
                    } catch (e: Exception) {
                        Log.e(TAG, "v9 drop failed for title pattern '$titleLike'", e)
                    }
                }
            }
        }

        // v10: added ArticleEntity.readAt (epoch millis, null while unread). Plain nullable
        // ADD COLUMN is safe here the way v2/v3's were -- no column is being removed or
        // renamed, so there's nothing for Room's exact-match post-migration validation to
        // trip on (see v4's comment above for what DOES require a full rebuild).
        private val MIGRATION_9_10 = object : Migration(9, 10) {
            override fun migrate(db: SupportSQLiteDatabase) {
                db.execSQL("ALTER TABLE articles ADD COLUMN readAt INTEGER")
            }
        }

        // v11: seeds a MUTE rule for emptywheel's recurring "Fridays with Nicole Sandler"
        // series (reported via the in-app bug report, 2026-08-22 -- each post is numbered,
        // e.g. "-111", so a plain substring match on the un-numbered title part catches every
        // installment going forward). Scoped to that one feed's id, not global, since the
        // phrase itself is specific enough it'd only ever match that series anyway, and
        // scoping costs nothing. Inserted directly rather than requiring the user to add it
        // by hand in Settings, matching this file's existing pattern of shipping
        // known-specific personal-data fixes as migrations (see v6-v9 above).
        private val MIGRATION_10_11 = object : Migration(10, 11) {
            override fun migrate(db: SupportSQLiteDatabase) {
                db.execSQL(
                    "INSERT INTO filter_rules (pattern, action, feedId, createdAt) VALUES (?, ?, ?, ?)",
                    arrayOf(
                        "Fridays with Nicole Sandler",
                        FilterAction.MUTE.name,
                        "https://www.emptywheel.net/feed/",
                        System.currentTimeMillis()
                    )
                )
            }
        }

        fun get(context: Context): AppDatabase = instance ?: synchronized(this) {
            instance ?: Room.databaseBuilder(
                context.applicationContext,
                AppDatabase::class.java,
                "feedreader.db"
            ).addMigrations(
                MIGRATION_1_2, MIGRATION_2_3, MIGRATION_3_4, MIGRATION_4_5,
                MIGRATION_5_6, MIGRATION_6_7, MIGRATION_7_8, MIGRATION_8_9, MIGRATION_9_10,
                MIGRATION_10_11
            ).build().also { instance = it }
        }
    }
}