package com.rugvedsarika.easymath.data.db

import androidx.room.Dao
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import kotlinx.coroutines.flow.Flow
import com.rugvedsarika.easymath.data.db.AchievementEntity

@Dao
interface MathDao {
    // User Operations
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertUser(user: UserEntity): Long

    @Query("SELECT * FROM user_table")
    fun getAllUsers(): Flow<List<UserEntity>>
    
    @Query("SELECT * FROM user_table WHERE id = :userId")
    suspend fun getUserById(userId: Int): UserEntity?

    @Query("SELECT COUNT(*) FROM user_table")
    suspend fun getUserCount(): Int

    // History Operations
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertHistory(history: MathHistoryEntity)

    @Query("SELECT * FROM math_history WHERE userId = :userId ORDER BY timestamp DESC")
    fun getAllHistory(userId: Int): Flow<List<MathHistoryEntity>>

    @Query("SELECT * FROM math_history WHERE category = :category AND userId = :userId ORDER BY score DESC LIMIT 5")
    fun getTopScores(category: String, userId: Int): Flow<List<MathHistoryEntity>>

    @Query("SELECT AVG(accuracy) FROM math_history WHERE userId = :userId")
    fun getOverallAccuracy(userId: Int): Flow<Float?>

    @Query("DELETE FROM math_history")
    suspend fun clearHistory()

    // Achievements
    @Query("SELECT * FROM achievements WHERE userId = :userId")
    fun getAchievements(userId: Int): Flow<List<AchievementEntity>>
    
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertAchievement(achievement: AchievementEntity)
    
    @Query("UPDATE achievements SET unlocked = 1, timestamp = :timestamp WHERE userId = :userId AND code = :code")
    suspend fun unlockAchievement(userId: Int, code: String, timestamp: Long)
    
    @Query("SELECT COUNT(*) FROM math_history WHERE userId = :userId")
    suspend fun getGamesPlayedCount(userId: Int): Int

    @Query("SELECT SUM(score) FROM math_history WHERE userId = :userId")
    suspend fun getTotalScore(userId: Int): Int?
}
