@Dao
interface MyDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(entity: Entity): Long
 
    @Update
    suspend fun update(entity: Entity)
 
    @Delete
    suspend fun delete(entity: Entity)
 
    @Query("SELECT * FROM table_name WHERE id = :id")
    suspend fun getById(id: Long): Entity?
 
    @Query("SELECT * FROM table_name ORDER BY created_at DESC")
    fun observeAll(): Flow<List<Entity>>
}

// Simple query
@Query("SELECT * FROM rounds WHERE id = :roundId")
suspend fun getRound(roundId: Long): Round?
 
// Query with JOIN
@Query("""
    SELECT r.*, COUNT(es.id) as endCount
    FROM rounds r
    LEFT JOIN end_scores es ON r.id = es.roundId
    GROUP BY r.id
""")
suspend fun getRoundsWithEndCount(): List<RoundWithEndCount>
 
// Query with parameters
@Query("SELECT * FROM rounds WHERE status = :status AND date >= :startDate")
suspend fun getRoundsByStatusAfterDate(
    status: String,
    startDate: Long
): List<Round>
 
// Reactive query
@Query("SELECT * FROM rounds ORDER BY date DESC")
fun observeRounds(): Flow<List<Round>>

@Query("SELECT * FROM rounds WHERE id = :roundId")
suspend fun getRoundById(roundId: Long): Round?
 
@Query("SELECT * FROM rounds WHERE status = :status")
fun observeRoundsByStatus(status: String): Flow<List<Round>>
 
@Transaction
@Query("SELECT * FROM rounds WHERE id = :roundId")
suspend fun getRoundWithEnds(roundId: Long): RoundWithEnds?

@Transaction
@Query("""
    SELECT r.*,
           COUNT(DISTINCT es.id) as endCount,
           COUNT(DISTINCT ars.id) as arrowCount,
           SUM(ars.score) as totalScore
    FROM rounds r
    LEFT JOIN end_scores es ON r.id = es.roundId
    LEFT JOIN arrow_scores ars ON es.id = ars.endScoreId
    WHERE r.status = :status
    GROUP BY r.id
    ORDER BY r.date DESC
""")
suspend fun getRoundStatsByStatus(status: String): List<RoundStats>

@Query("""
    SELECT * FROM rounds
    WHERE id IN (
        SELECT DISTINCT roundId
        FROM end_scores
        WHERE totalScore > :minScore
    )
""")
suspend fun getRoundsWithMinScore(minScore: Int): List<Round>

@Query("""
    SELECT * FROM rounds
    WHERE (:status IS NULL OR status = :status)
      AND (:startDate IS NULL OR date >= :startDate)
      AND (:endDate IS NULL OR date <= :endDate)
    ORDER BY date DESC
""")
suspend fun getRoundsFiltered(
    status: String?,
    startDate: Long?,
    endDate: Long?
): List<Round>

@Dao
interface MyDao {
    @Transaction
    suspend fun createRoundWithEnds(
        round: Round,
        ends: List<EndScore>
    ) {
        val roundId = insertRound(round)
        ends.forEach { end ->
            end.roundId = roundId
            insertEnd(end)
        }
    }
 
    @Insert
    suspend fun insertRound(round: Round): Long
 
    @Insert
    suspend fun insertEnd(end: EndScore): Long
}

@Transaction
suspend fun complexOperation() = withTransaction {
    val id1 = dao1.insert(entity1)
    val id2 = dao2.insert(entity2)
    dao3.link(id1, id2)
}

// BAD: N+1 queries
val rounds = roundDao.getAllRounds()  // 1 query
rounds.forEach { round ->
    val ends = endDao.getEndsForRound(round.id)  // N queries
}

// GOOD: Single query with JOIN
data class RoundWithEnds(
    @Embedded val round: Round,
    @Relation(
        parentColumn = "id",
        entityColumn = "roundId"
    )
    val ends: List<EndScore>
)
 
@Transaction
@Query("SELECT * FROM rounds")
suspend fun getRoundsWithEnds(): List<RoundWithEnds>

@Entity(
    tableName = "rounds",
    indices = [
        Index(value = ["status"]),
        Index(value = ["date"]),
        Index(value = ["status", "date"])  // Composite index
    ]
)
data class Round(...)

@RunWith(AndroidJUnit4::class)
class RoundDaoTest {
    private lateinit var database: AppDatabase
    private lateinit var dao: RoundDao
 
    @Before
    fun setup() {
        database = Room.inMemoryDatabaseBuilder(
            ApplicationProvider.getApplicationContext(),
            AppDatabase::class.java
        ).build()
        dao = database.roundDao()
    }
 
    @After
    fun teardown() {
        database.close()
    }
 
    @Test
    fun insertAndRetrieveRound() = runTest {
        val round = Round(...)
        val id = dao.insert(round)
 
        val retrieved = dao.getRoundById(id)
 
        assertEquals(round.name, retrieved?.name)
    }
}

@Test
fun observeRoundsEmitsUpdates() = runTest {
    val rounds = mutableListOf<List<Round>>()
 
    val job = launch {
        dao.observeRounds().collect {
            rounds.add(it)
        }
    }
 
    dao.insert(round1)
    dao.insert(round2)
 
    advanceUntilIdle()
    job.cancel()
 
    assertEquals(3, rounds.size)  // Initial empty + 2 inserts
}

// GOOD: Suspend for async operations
@Insert
suspend fun insert(entity: Entity): Long
 
// BAD: Blocking main thread
@Insert
fun insert(entity: Entity): Long

// GOOD: Reactive updates
@Query("SELECT * FROM table")
fun observeAll(): Flow<List<Entity>>
 
// BAD: One-time query
@Query("SELECT * FROM table")
suspend fun getAll(): List<Entity>

@Transaction
suspend fun complexOperation() {
    step1()
    step2()
    step3()
}

// GOOD: Nullable return type
@Query("SELECT * FROM table WHERE id = :id")
suspend fun getById(id: Long): Entity?
 
// Use safely
val entity = dao.getById(id) ?: return