Room Database (SQLite)

# gradle/libs.versions.toml
ksp = "2.0.21-1.0.28"
room = "2.6.1"
androidx-room-runtime = { group = "androidx.room", name = "room-runtime", version.ref = "room" }
androidx-room-ktx = { group = "androidx.room", name = "room-ktx", version.ref = "room" }
androidx-room-compiler = { group = "androidx.room", name = "room-compiler", version.ref = "room" }
ksp = { id = "com.google.devtools.ksp", version.ref = "ksp" }        # under [plugins]

// app/build.gradle.kts
plugins { alias(libs.plugins.ksp) }
dependencies {
    implementation(libs.androidx.room.runtime)
    implementation(libs.androidx.room.ktx)       // coroutines + Flow support
    ksp(libs.androidx.room.compiler)             // generates the DAO code
}

package com.codingeasily.studentdiary.data

import androidx.room.Entity
import androidx.room.PrimaryKey

/** One row of the "notes" table. */
@Entity(tableName = "notes")
data class Note(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val title: String,
    val content: String = "",
    val subject: String = "General",
    val priority: Int = PRIORITY_MEDIUM,      // 0 = low, 1 = medium, 2 = high
    val dueAt: Long? = null,                  // reminder time in millis, null = no reminder
    val isDone: Boolean = false,
    val isFavorite: Boolean = false,
    val imagePath: String? = null,            // photo copied into app storage
    val createdAt: Long = System.currentTimeMillis()
) {
    companion object {
        const val PRIORITY_LOW = 0
        const val PRIORITY_MEDIUM = 1
        const val PRIORITY_HIGH = 2
    }
}

/** Result of a GROUP BY query: how many notes each subject has. */
data class SubjectCount(val subject: String, val total: Int)

-- What Room creates for the Note entity
CREATE TABLE notes (
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  subject TEXT NOT NULL,
  priority INTEGER NOT NULL,
  dueAt INTEGER,                -- Long? → nullable
  isDone INTEGER NOT NULL,      -- Boolean → 0/1
  isFavorite INTEGER NOT NULL,
  imagePath TEXT,
  createdAt INTEGER NOT NULL
);

package com.codingeasily.studentdiary.data

import androidx.room.Dao
import androidx.room.Delete
import androidx.room.Insert
import androidx.room.Query
import androidx.room.Update
import kotlinx.coroutines.flow.Flow

/** All SQL for the notes table lives here. Room generates the implementation. */
@Dao
interface NoteDao {

    // Flow = the UI is updated automatically whenever the table changes
    @Query("SELECT * FROM notes ORDER BY isDone ASC, priority DESC, createdAt DESC")
    fun getAll(): Flow<List<Note>>

    @Query("SELECT * FROM notes WHERE isDone = :done ORDER BY priority DESC, createdAt DESC")
    fun getByDone(done: Boolean): Flow<List<Note>>

    @Query("SELECT * FROM notes WHERE isFavorite = 1 ORDER BY createdAt DESC")
    fun getFavorites(): Flow<List<Note>>

    @Query("SELECT * FROM notes WHERE subject = :subject ORDER BY createdAt DESC")
    fun getBySubject(subject: String): Flow<List<Note>>

    @Query("SELECT * FROM notes WHERE title LIKE '%' || :q || '%' OR content LIKE '%' || :q || '%' ORDER BY createdAt DESC")
    fun search(q: String): Flow<List<Note>>

    @Query("SELECT subject, COUNT(*) AS total FROM notes GROUP BY subject ORDER BY total DESC")
    fun subjectCounts(): Flow<List<SubjectCount>>

    @Query("SELECT COUNT(*) FROM notes")
    fun countAll(): Flow<Int>

    @Query("SELECT COUNT(*) FROM notes WHERE isDone = 1")
    fun countDone(): Flow<Int>

    @Query("SELECT * FROM notes WHERE id = :id")
    suspend fun getById(id: Long): Note?

    @Insert
    suspend fun insert(note: Note): Long

    @Update
    suspend fun update(note: Note)

    @Delete
    suspend fun delete(note: Note)

    @Query("DELETE FROM notes WHERE isDone = 1")
    suspend fun deleteDone(): Int
}

package com.codingeasily.studentdiary.data

import android.content.Context
import androidx.room.Database
import androidx.room.Room
import androidx.room.RoomDatabase

@Database(entities = [Note::class], version = 1, exportSchema = false)
abstract class AppDatabase : RoomDatabase() {

    abstract fun noteDao(): NoteDao

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

        /** Singleton: the whole app shares one database connection. */
        fun get(context: Context): AppDatabase =
            INSTANCE ?: synchronized(this) {
                INSTANCE ?: Room.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    "student_diary.db"
                ).build().also { INSTANCE = it }
            }
    }
}

package com.codingeasily.studentdiary.data

import kotlinx.coroutines.flow.Flow

/**
 * The single source of truth for notes. ViewModels talk to the repository,
 * never directly to the DAO. Later you could add a network source here.
 */
class NoteRepository(private val dao: NoteDao) {
    val allNotes: Flow<List<Note>> = dao.getAll()
    val pendingNotes: Flow<List<Note>> = dao.getByDone(false)
    val doneNotes: Flow<List<Note>> = dao.getByDone(true)
    val favorites: Flow<List<Note>> = dao.getFavorites()
    val subjectCounts: Flow<List<SubjectCount>> = dao.subjectCounts()
    val countAll: Flow<Int> = dao.countAll()
    val countDone: Flow<Int> = dao.countDone()

    fun bySubject(subject: String) = dao.getBySubject(subject)
    fun search(q: String) = dao.search(q)

    suspend fun get(id: Long) = dao.getById(id)
    suspend fun save(note: Note): Long =
        if (note.id == 0L) dao.insert(note) else { dao.update(note); note.id }
    suspend fun delete(note: Note) = dao.delete(note)
    suspend fun deleteDone() = dao.deleteDone()
}

// Quick test inside an Activity (later this moves into a ViewModel)
val dao = AppDatabase.get(this).noteDao()

lifecycleScope.launch {
    dao.insert(Note(title = "Maths homework", subject = "Maths", priority = Note.PRIORITY_HIGH))
}

lifecycleScope.launch {
    repeatOnLifecycle(Lifecycle.State.STARTED) {
        dao.getAll().collect { notes ->               // runs again after every change
            adapter.submitList(notes)
        }
    }
}

// Version 2 adds a "color" column to notes
@Database(entities = [Note::class], version = 2, exportSchema = false)
abstract class AppDatabase : RoomDatabase() { /* … */ }

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("ALTER TABLE notes ADD COLUMN color INTEGER NOT NULL DEFAULT 0")
    }
}

Room.databaseBuilder(context, AppDatabase::class.java, "student_diary.db")
    .addMigrations(MIGRATION_1_2)
    .build()

// While developing only (deletes all data!):  .fallbackToDestructiveMigration()