package app.closer.data.local

import androidx.room.Dao
import androidx.room.Delete
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import app.closer.data.local.entity.QuestionEntity

@Dao
interface QuestionDao {
    @Query("SELECT * FROM question WHERE id = :id AND status = 'active' AND TRIM(text) <> '' LIMIT 1")
    suspend fun getQuestionById(id: String): QuestionEntity?

    // Daily-question pools are selected DETERMINISTICALLY per day: a stable `ORDER BY id`
    // plus a date-derived OFFSET (computed in the repository). RANDOM() previously returned a
    // different question on every call, which made the daily question change between reloads
    // (breaking "already answered" detection → re-asking) and differ between the two partners.
    // Ordering by id + a shared day offset yields the same question across reloads and across
    // both partners' devices (they share this question DB).

    @Query("SELECT COUNT(*) FROM question WHERE status = 'active' AND is_premium = 0 AND TRIM(text) <> '' AND category_id <> 'unknown'")
    suspend fun countDailyQuestion(): Int

    @Query("SELECT * FROM question WHERE status = 'active' AND is_premium = 0 AND TRIM(text) <> '' AND category_id <> 'unknown' ORDER BY id LIMIT 1 OFFSET :offset")
    suspend fun getDailyQuestionAt(offset: Int): QuestionEntity?

    @Query("SELECT COUNT(*) FROM question WHERE category_id = 'daily_fun_mc' AND status = 'active' AND tags LIKE '%' || :modeTag || '%' AND TRIM(text) <> ''")
    suspend fun countDailyQuestionByModeTag(modeTag: String): Int

    @Query("SELECT * FROM question WHERE category_id = 'daily_fun_mc' AND status = 'active' AND tags LIKE '%' || :modeTag || '%' AND TRIM(text) <> '' ORDER BY id LIMIT 1 OFFSET :offset")
    suspend fun getDailyQuestionByModeTagAt(modeTag: String, offset: Int): QuestionEntity?

    @Query("SELECT COUNT(*) FROM question WHERE category_id = 'daily_fun_mc' AND status = 'active' AND tags LIKE '%quick_answer%' AND TRIM(text) <> ''")
    suspend fun countDailyQuestionFromPack(): Int

    @Query("SELECT * FROM question WHERE category_id = 'daily_fun_mc' AND status = 'active' AND tags LIKE '%quick_answer%' AND TRIM(text) <> '' ORDER BY id LIMIT 1 OFFSET :offset")
    suspend fun getDailyQuestionFromPackAt(offset: Int): QuestionEntity?

    @Query("SELECT COUNT(*) FROM question WHERE category_id = 'daily_fun_mc' AND status = 'active' AND is_premium = 0 AND tags LIKE '%' || :modeTag || '%' AND TRIM(text) <> ''")
    suspend fun countFreeDailyQuestionByModeTag(modeTag: String): Int

    @Query("SELECT * FROM question WHERE category_id = 'daily_fun_mc' AND status = 'active' AND is_premium = 0 AND tags LIKE '%' || :modeTag || '%' AND TRIM(text) <> '' ORDER BY id LIMIT 1 OFFSET :offset")
    suspend fun getFreeDailyQuestionByModeTagAt(modeTag: String, offset: Int): QuestionEntity?

    @Query("SELECT COUNT(*) FROM question WHERE category_id = 'daily_fun_mc' AND status = 'active' AND is_premium = 0 AND tags LIKE '%quick_answer%' AND TRIM(text) <> ''")
    suspend fun countFreeDailyQuestionFromPack(): Int

    @Query("SELECT * FROM question WHERE category_id = 'daily_fun_mc' AND status = 'active' AND is_premium = 0 AND tags LIKE '%quick_answer%' AND TRIM(text) <> '' ORDER BY id LIMIT 1 OFFSET :offset")
    suspend fun getFreeDailyQuestionFromPackAt(offset: Int): QuestionEntity?

    @Query("SELECT * FROM question WHERE category_id = :categoryId AND status = 'active' AND TRIM(text) <> '' ORDER BY depth_level ASC, id ASC")
    suspend fun getQuestionsByCategory(categoryId: String): List<QuestionEntity>

    @Query("SELECT COUNT(*) FROM question WHERE category_id = :categoryId AND status = 'active' AND TRIM(text) <> ''")
    suspend fun getQuestionCountByCategory(categoryId: String): Int

    @Query("SELECT * FROM question WHERE type = :type AND status = 'active' AND TRIM(text) <> ''")
    suspend fun getQuestionsByType(type: String): List<QuestionEntity>

    @Query("SELECT * FROM question WHERE type IN ('single_choice', 'this_or_that', 'scale') AND status = 'active' AND TRIM(text) <> ''")
    suspend fun getQuestionsForPrediction(): List<QuestionEntity>

    @Query("SELECT * FROM question WHERE sex = :sex AND status = 'active' AND TRIM(text) <> ''")
    suspend fun getDesireSyncQuestions(sex: String): List<QuestionEntity>

    @Query("SELECT * FROM question WHERE is_premium = 0 AND status = 'active' AND TRIM(text) <> ''")
    suspend fun getFreeQuestions(): List<QuestionEntity>

    @Query("SELECT * FROM question WHERE is_premium = 1 AND status = 'active' AND TRIM(text) <> ''")
    suspend fun getPremiumQuestions(): List<QuestionEntity>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(question: QuestionEntity)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertAll(questions: List<QuestionEntity>)

    @Delete
    suspend fun delete(question: QuestionEntity)
}
