package xyz.cottongin.radio247.data.db

import androidx.room.Database
import androidx.room.RoomDatabase
import androidx.room.migration.Migration
import androidx.sqlite.db.SupportSQLiteDatabase
import xyz.cottongin.radio247.data.model.ConnectionSpan
import xyz.cottongin.radio247.data.model.ListeningSession
import xyz.cottongin.radio247.data.model.MetadataSnapshot
import xyz.cottongin.radio247.data.model.Playlist
import xyz.cottongin.radio247.data.model.Station
import xyz.cottongin.radio247.data.model.StationPreference
import xyz.cottongin.radio247.data.model.StationStream

@Database(
    entities = [
        Station::class,
        Playlist::class,
        MetadataSnapshot::class,
        ListeningSession::class,
        ConnectionSpan::class,
        StationStream::class,
        StationPreference::class
    ],
    version = 6,
    exportSchema = true
)
abstract class RadioDatabase : RoomDatabase() {
    abstract fun stationDao(): StationDao
    abstract fun playlistDao(): PlaylistDao
    abstract fun metadataSnapshotDao(): MetadataSnapshotDao
    abstract fun listeningSessionDao(): ListeningSessionDao
    abstract fun connectionSpanDao(): ConnectionSpanDao
    abstract fun stationStreamDao(): StationStreamDao
    abstract fun stationPreferenceDao(): StationPreferenceDao

    companion object {
        val MIGRATION_1_2 = object : Migration(1, 2) {
            override fun migrate(db: SupportSQLiteDatabase) {
                db.execSQL("ALTER TABLE stations ADD COLUMN listenerCount INTEGER NOT NULL DEFAULT 0")
                db.execSQL("ALTER TABLE playlists ADD COLUMN isBuiltIn INTEGER NOT NULL DEFAULT 0")
                SomaFmSeedData.seedStations(db, includeIsHidden = false, includeStreams = false)
            }
        }

        val MIGRATION_2_3 = object : Migration(2, 3) {
            override fun migrate(db: SupportSQLiteDatabase) {
                db.execSQL("ALTER TABLE stations ADD COLUMN isHidden INTEGER NOT NULL DEFAULT 0")
            }
        }

        val MIGRATION_3_4 = object : Migration(3, 4) {
            override fun migrate(db: SupportSQLiteDatabase) {
                db.execSQL(
                    """CREATE TABLE IF NOT EXISTS station_streams (
                        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                        stationId INTEGER NOT NULL,
                        bitrate INTEGER NOT NULL,
                        ssl INTEGER NOT NULL,
                        url TEXT NOT NULL,
                        FOREIGN KEY(stationId) REFERENCES stations(id) ON DELETE CASCADE
                    )"""
                )
                db.execSQL("CREATE INDEX IF NOT EXISTS index_station_streams_stationId ON station_streams(stationId)")
                db.execSQL("ALTER TABLE stations ADD COLUMN qualityOverride TEXT DEFAULT NULL")
                SomaFmSeedData.seedStreamsForExistingStations(db)
            }
        }

        val MIGRATION_4_5 = object : Migration(4, 5) {
            override fun migrate(db: SupportSQLiteDatabase) {
                db.execSQL(
                    """CREATE TABLE IF NOT EXISTS station_preferences (
                        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                        stationId INTEGER NOT NULL,
                        qualityOverride TEXT DEFAULT NULL,
                        FOREIGN KEY(stationId) REFERENCES stations(id) ON DELETE CASCADE
                    )"""
                )
                db.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS index_station_preferences_stationId ON station_preferences(stationId)")
            }
        }

        val MIGRATION_5_6 = object : Migration(5, 6) {
            override fun migrate(db: SupportSQLiteDatabase) {
                db.execSQL("ALTER TABLE playlists ADD COLUMN pinned INTEGER NOT NULL DEFAULT 0")
                db.execSQL("UPDATE playlists SET pinned = 1 WHERE isBuiltIn = 1")
            }
        }
    }
}
