dependencies {
    implementation "androidx.room:room-ktx:2.4.0"
}

@Entity(tableName = "user")
data class User(
    @PrimaryKey val id: Int,
    @ColumnInfo(name = "name") val name: String,
    @ColumnInfo(name = "email") val email: String
)

@Dao
interface UserDao {
    @Query("SELECT * FROM user")
    fun getAll(): List<User>

    @Query("SELECT * FROM user WHERE id = :id")
    fun getById(id: Int): User?

    @Insert
    fun insert(user: User)

    @Update
    fun update(user: User)

    @Delete
    fun delete(user: User)
}

@Database(entities = [User::class], version = 1)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao

    companion object {
        private var INSTANCE: AppDatabase? = null

        fun getInstance(context: Context): AppDatabase {
            return INSTANCE ?: synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    "app_database"
                ).build()
                INSTANCE = instance
                instance
            }
        }
    }
}

val db = AppDatabase.getInstance(context)
val userDao = db.userDao()

val users = userDao.getAll()
val user = userDao.getById(1)
val newUser = User(2, "雞你太美", "jinitaimei@qq.com")
userDao.insert(newUser)
newUser.email = "jinitaimei@qq.com"
userDao.update(newUser)
userDao.delete(newUser)

@Transaction
fun updateUserData(user: User, address: Address) {
    userDao.update(user)
    addressDao.update(address)
}

@Database(entities = [User::class], version = 2, exportSchema = false,
    migrations = [Migration(1, 2) { database ->
        database.execSQL("ALTER TABLE user ADD COLUMN phone TEXT NOT NULL DEFAULT ''")
    }]
)
abstract class AppDatabase : RoomDatabase() {
    // ...
}

@DatabaseView(
    "SELECT user.id, user.name, address.city, address.country FROM user " +
    "INNER JOIN address ON user.address_id = address.id"
)
data class UserAddress(
    val id: Int,
    val name: String,
    val city: String,
    val country: String
)

@Dao
interface UserAddressDao {
    @Query("SELECT * FROM user_address")
    fun getAll(): List<UserAddress>
}

@Dao
interface UserDao {
	@Query("SELECT * FROM user WHERE id = :id")
    fun getById(id: Int): Flow<User>

    @Query("SELECT * FROM user")
    fun getAll(): Flow<List<User>>
}