package org.terst.feedreader.data

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

@Database(
    entities = [FeedEntity::class, ArticleEntity::class, FilterRuleEntity::class],
    version = 5,
    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

        // 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) = ''")
            }
        }

        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).build().also { instance = it }
        }
    }
}