package expo.modules.updates.db

import android.content.Context
import androidx.room.Database
import androidx.room.Room
import androidx.room.RoomDatabase
import androidx.room.TypeConverters
import androidx.room.migration.Migration
import androidx.sqlite.db.SupportSQLiteDatabase
import expo.modules.updates.db.dao.AssetDao
import expo.modules.updates.db.dao.JSONDataDao
import expo.modules.updates.db.dao.UpdateDao
import expo.modules.updates.db.entity.AssetEntity
import expo.modules.updates.db.entity.JSONDataEntity
import expo.modules.updates.db.entity.UpdateAssetEntity
import expo.modules.updates.db.entity.UpdateEntity
import kotlinx.coroutines.CoroutineDispatcher
import kotlinx.coroutines.asExecutor
import java.util.*

/**
 * SQLite database that keeps track of updates currently loaded/loading to disk, including the
 * update manifest and metadata, status, and the individual assets (including bundles/bytecode) that
 * comprise the update. (Assets themselves are stored on the device's file system, and a relative
 * path is kept in SQLite.)
 *
 * SQLite allows a many-to-many relationship between updates and assets, which means we can keep
 * only one copy of each asset on disk at a time while also being able to clear unused assets with
 * relative ease (see [Reaper]).
 *
 * We use the Android Room library here, which provides a friendly abstraction over SQLite. The
 * database schema is autogenerated from the `Entity` classes, and `DAO` classes provide access to
 * the actual data.
 *
 * Occasionally it's necessary to add migrations when the data structures for updates or assets must
 * change. Extra care must be taken here, since these migrations will happen on users' devices for
 * apps we do not control. See
 * https://github.com/expo/expo/blob/main/packages/expo-updates/guides/migrations.md for step by
 * step instructions.
 *
 * [DatabaseHolder] provides a rudimentary locking mechanism, and most other classes access the
 * database through this class. This allows control over what high-level operations involving the
 * database can occur simultaneously - e.g. we don't want to be trying to download a new update at
 * the same time the [Reaper] is running.
 */
@Database(
  entities = [UpdateEntity::class, UpdateAssetEntity::class, AssetEntity::class, JSONDataEntity::class],
  exportSchema = false,
  version = 13
)
@TypeConverters(Converters::class)
abstract class UpdatesDatabase : RoomDatabase() {
  abstract fun updateDao(): UpdateDao
  abstract fun assetDao(): AssetDao
  abstract fun jsonDataDao(): JSONDataDao?

  companion object {
    private const val DB_NAME = "updates.db"

    @Volatile
    private var INSTANCE: UpdatesDatabase? = null

    fun getInstance(context: Context, dispatcher: CoroutineDispatcher?): UpdatesDatabase {
      return INSTANCE ?: synchronized(this) {
        val instance = Room.databaseBuilder(
          context.applicationContext,
          UpdatesDatabase::class.java,
          DB_NAME
        ).apply {
          if (dispatcher != null) {
            setQueryExecutor(dispatcher.asExecutor())
          }
        }.addMigrations(
          MIGRATION_4_5,
          MIGRATION_5_6,
          MIGRATION_6_7,
          MIGRATION_7_8,
          MIGRATION_8_9,
          MIGRATION_9_10,
          MIGRATION_10_11,
          MIGRATION_11_12,
          MIGRATION_12_13
        )
          .allowMainThreadQueries()
          .fallbackToDestructiveMigration()
          .build()

        INSTANCE = instance
        instance
      }
    }

    private fun SupportSQLiteDatabase.runInTransaction(block: SupportSQLiteDatabase.() -> Unit) {
      beginTransaction()
      try {
        block()
        setTransactionSuccessful()
      } finally {
        endTransaction()
      }
    }

    private fun SupportSQLiteDatabase.runInTransactionWithForeignKeysOff(block: SupportSQLiteDatabase.() -> Unit) {
      // https://www.sqlite.org/lang_altertable.html#otheralter
      try {
        execSQL("PRAGMA foreign_keys=OFF")
        runInTransaction(block)
      } finally {
        execSQL("PRAGMA foreign_keys=ON")
      }
    }

    val MIGRATION_4_5: Migration = object : Migration(4, 5) {
      override fun migrate(db: SupportSQLiteDatabase) {
        db.runInTransactionWithForeignKeysOff {
          execSQL("CREATE TABLE `new_assets` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `url` TEXT, `key` TEXT, `headers` TEXT, `type` TEXT NOT NULL, `metadata` TEXT, `download_time` INTEGER, `relative_path` TEXT, `hash` BLOB, `hash_type` INTEGER NOT NULL, `marked_for_deletion` INTEGER NOT NULL)")
          execSQL(
            "INSERT INTO `new_assets` (`id`, `url`, `key`, `headers`, `type`, `metadata`, `download_time`, `relative_path`, `hash`, `hash_type`, `marked_for_deletion`)" +
              " SELECT `id`, `url`, `key`, `headers`, `type`, `metadata`, `download_time`, `relative_path`, `hash`, `hash_type`, `marked_for_deletion` FROM `assets`"
          )
          execSQL("DROP TABLE `assets`")
          execSQL("ALTER TABLE `new_assets` RENAME TO `assets`")
          execSQL("CREATE UNIQUE INDEX `index_assets_key` ON `assets` (`key`)")
        }
      }
    }

    val MIGRATION_5_6: Migration = object : Migration(5, 6) {
      override fun migrate(db: SupportSQLiteDatabase) {
        db.runInTransactionWithForeignKeysOff {
          execSQL("CREATE TABLE `new_updates` (`id` BLOB NOT NULL, `scope_key` TEXT NOT NULL, `commit_time` INTEGER NOT NULL, `runtime_version` TEXT NOT NULL, `launch_asset_id` INTEGER, `manifest` TEXT, `status` INTEGER NOT NULL, `keep` INTEGER NOT NULL, `last_accessed` INTEGER NOT NULL, PRIMARY KEY(`id`), FOREIGN KEY(`launch_asset_id`) REFERENCES `assets`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )")
          // insert current time as lastAccessed date for all existing updates
          val currentTime = Date().time
          execSQL(
            "INSERT INTO `new_updates` (`id`, `scope_key`, `commit_time`, `runtime_version`, `launch_asset_id`, `manifest`, `status`, `keep`, `last_accessed`)" +
              " SELECT `id`, `scope_key`, `commit_time`, `runtime_version`, `launch_asset_id`, `metadata` AS `manifest`, `status`, `keep`, ?1 AS `last_accessed` FROM `updates`",
            arrayOf<Any>(currentTime)
          )
          execSQL("DROP TABLE `updates`")
          execSQL("ALTER TABLE `new_updates` RENAME TO `updates`")
          execSQL("CREATE INDEX `index_updates_launch_asset_id` ON `updates` (`launch_asset_id`)")
          execSQL("CREATE UNIQUE INDEX `index_updates_scope_key_commit_time` ON `updates` (`scope_key`, `commit_time`)")
        }
      }
    }

    /**
     * Make the `assets` table `type` column nullable
     */
    val MIGRATION_6_7: Migration = object : Migration(6, 7) {
      override fun migrate(db: SupportSQLiteDatabase) {
        db.runInTransactionWithForeignKeysOff {
          execSQL("CREATE TABLE IF NOT EXISTS `new_assets` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `url` TEXT, `key` TEXT, `headers` TEXT, `type` TEXT, `metadata` TEXT, `download_time` INTEGER, `relative_path` TEXT, `hash` BLOB, `hash_type` INTEGER NOT NULL, `marked_for_deletion` INTEGER NOT NULL)")
          execSQL(
            "INSERT INTO `new_assets` (`id`, `url`, `key`, `headers`, `type`, `metadata`, `download_time`, `relative_path`, `hash`, `hash_type`, `marked_for_deletion`)" +
              " SELECT `id`, `url`, `key`, `headers`, `type`, `metadata`, `download_time`, `relative_path`, `hash`, `hash_type`, `marked_for_deletion` FROM `assets`"
          )
          execSQL("DROP TABLE `assets`")
          execSQL("ALTER TABLE `new_assets` RENAME TO `assets`")
          execSQL("CREATE UNIQUE INDEX `index_assets_key` ON `assets` (`key`)")
        }
      }
    }

    /**
     * Add the `successful_launch_count` and `failed_launch_count` columns to `updates`
     */
    val MIGRATION_7_8: Migration = object : Migration(7, 8) {
      override fun migrate(db: SupportSQLiteDatabase) {
        db.runInTransactionWithForeignKeysOff {
          execSQL("CREATE TABLE `new_updates` (`id` BLOB NOT NULL, `scope_key` TEXT NOT NULL, `commit_time` INTEGER NOT NULL, `runtime_version` TEXT NOT NULL, `launch_asset_id` INTEGER, `manifest` TEXT, `status` INTEGER NOT NULL, `keep` INTEGER NOT NULL, `last_accessed` INTEGER NOT NULL, `successful_launch_count` INTEGER NOT NULL DEFAULT 0, `failed_launch_count` INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(`id`), FOREIGN KEY(`launch_asset_id`) REFERENCES `assets`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )")

          // insert `1` for successful_launch_count for all existing updates
          // to make sure we don't roll back past them
          execSQL(
            "INSERT INTO `new_updates` (`id`, `scope_key`, `commit_time`, `runtime_version`, `launch_asset_id`, `manifest`, `status`, `keep`, `last_accessed`, `successful_launch_count`, `failed_launch_count`)" +
              " SELECT `id`, `scope_key`, `commit_time`, `runtime_version`, `launch_asset_id`, `manifest`, `status`, `keep`, `last_accessed`, 1 AS `successful_launch_count`, 0 AS `failed_launch_count` FROM `updates`"
          )
          execSQL("DROP TABLE `updates`")
          execSQL("ALTER TABLE `new_updates` RENAME TO `updates`")
          execSQL("CREATE INDEX `index_updates_launch_asset_id` ON `updates` (`launch_asset_id`)")
          execSQL("CREATE UNIQUE INDEX `index_updates_scope_key_commit_time` ON `updates` (`scope_key`, `commit_time`)")
        }
      }
    }

    val MIGRATION_8_9: Migration = object : Migration(8, 9) {
      override fun migrate(db: SupportSQLiteDatabase) {
        db.runInTransactionWithForeignKeysOff {
          execSQL("ALTER TABLE `assets` ADD COLUMN `extra_request_headers` TEXT")
        }
      }
    }

    val MIGRATION_9_10: Migration = object : Migration(9, 10) {
      override fun migrate(db: SupportSQLiteDatabase) {
        db.runInTransactionWithForeignKeysOff {
          execSQL("ALTER TABLE `assets` ADD COLUMN `expected_hash` TEXT")
        }
      }
    }

    val MIGRATION_10_11: Migration = object : Migration(10, 11) {
      override fun migrate(db: SupportSQLiteDatabase) {
        db.runInTransaction {
          execSQL("UPDATE `assets` SET `expected_hash` = NULL")
        }
      }
    }

    /**
     * Change the `updates.manifest` column to be non-null
     */
    val MIGRATION_11_12: Migration = object : Migration(11, 12) {
      override fun migrate(db: SupportSQLiteDatabase) {
        db.runInTransactionWithForeignKeysOff {
          execSQL("CREATE TABLE `new_updates` (`id` BLOB NOT NULL, `scope_key` TEXT NOT NULL, `commit_time` INTEGER NOT NULL, `runtime_version` TEXT NOT NULL, `launch_asset_id` INTEGER, `manifest` TEXT NOT NULL, `status` INTEGER NOT NULL, `keep` INTEGER NOT NULL, `last_accessed` INTEGER NOT NULL, `successful_launch_count` INTEGER NOT NULL DEFAULT 0, `failed_launch_count` INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(`id`), FOREIGN KEY(`launch_asset_id`) REFERENCES `assets`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )")

          execSQL(
            "INSERT INTO `new_updates` (`id`, `scope_key`, `commit_time`, `runtime_version`, `launch_asset_id`, `manifest`, `status`, `keep`, `last_accessed`, `successful_launch_count`, `failed_launch_count`)" +
              " SELECT `id`, `scope_key`, `commit_time`, `runtime_version`, `launch_asset_id`, `manifest`, `status`, `keep`, `last_accessed`, `successful_launch_count`, `failed_launch_count` FROM `updates` WHERE `manifest` IS NOT NULL"
          )
          execSQL("DROP TABLE `updates`")
          execSQL("ALTER TABLE `new_updates` RENAME TO `updates`")
          execSQL("CREATE INDEX `index_updates_launch_asset_id` ON `updates` (`launch_asset_id`)")
          execSQL("CREATE UNIQUE INDEX `index_updates_scope_key_commit_time` ON `updates` (`scope_key`, `commit_time`)")
        }
      }
    }

    /**
     * Add the `url` and `headers` columns to `updates`
     */
    val MIGRATION_12_13: Migration = object : Migration(12, 13) {
      override fun migrate(db: SupportSQLiteDatabase) {
        db.runInTransaction {
          execSQL("ALTER TABLE `updates` ADD COLUMN `url` TEXT")
          execSQL("ALTER TABLE `updates` ADD COLUMN `headers` TEXT")
        }
      }
    }
  }
}