package com.placeholder.sherpai2.data.local.dao

import androidx.room.Dao
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import androidx.room.Transaction
import com.placeholder.sherpai2.data.local.entity.ImageTagEntity
import com.placeholder.sherpai2.data.local.entity.TagEntity
import kotlinx.coroutines.flow.Flow

/**
 * Data class for burst statistics
 */
data class BurstStats(
    val totalBurstPhotos: Int,
    val estimatedBurstGroups: Int,
    val burstRepresentatives: Int
)

@Dao
interface ImageTagDao {

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun upsert(imageTag: ImageTagEntity)

    @Query("""
        SELECT * FROM image_tags
        WHERE imageId = :imageId
        AND visibility != 'HIDDEN'
    """)
    fun observeTagsForImage(imageId: String): Flow<List<ImageTagEntity>>

    /**
     * FIXED: Removed default parameter
     */
    @Query("""
        SELECT imageId FROM image_tags
        WHERE tagId = :tagId
        AND visibility = 'PUBLIC'
        AND confidence >= :minConfidence
    """)
    suspend fun findImagesByTag(
        tagId: String,
        minConfidence: Float
    ): List<String>

    @Transaction
    @Query("""
        SELECT t.* 
        FROM tags t
        INNER JOIN image_tags it ON t.tagId = it.tagId
        WHERE it.imageId = :imageId AND it.visibility = 'PUBLIC'
    """)
    fun getTagsForImage(imageId: String): Flow<List<TagEntity>>

    /**
     * Insert image tag (for utilities tagging)
     */
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun insert(imageTag: ImageTagEntity): Long

    // ==========================================
    // BURST STATISTICS - ADDED FOR STATS SECTION
    // ==========================================

    /**
     * Get comprehensive burst statistics
     * Returns total burst photos, estimated groups, and representative count
     */
    @Query("""
        SELECT 
            (SELECT COUNT(DISTINCT it.imageId) 
             FROM image_tags it 
             INNER JOIN tags t ON it.tagId = t.tagId 
             WHERE t.value = 'burst') as totalBurstPhotos,
            (SELECT COUNT(DISTINCT it.imageId) / 3
             FROM image_tags it 
             INNER JOIN tags t ON it.tagId = t.tagId 
             WHERE t.value = 'burst') as estimatedBurstGroups,
            (SELECT COUNT(DISTINCT it.imageId)
             FROM image_tags it 
             INNER JOIN tags t ON it.tagId = t.tagId 
             WHERE t.value = 'burst_representative') as burstRepresentatives
    """)
    suspend fun getBurstStats(): BurstStats?

    /**
     * Get burst statistics (Flow version for reactive UI)
     */
    @Query("""
        SELECT 
            (SELECT COUNT(DISTINCT it.imageId) 
             FROM image_tags it 
             INNER JOIN tags t ON it.tagId = t.tagId 
             WHERE t.value = 'burst') as totalBurstPhotos,
            (SELECT COUNT(DISTINCT it.imageId) / 3
             FROM image_tags it 
             INNER JOIN tags t ON it.tagId = t.tagId 
             WHERE t.value = 'burst') as estimatedBurstGroups,
            (SELECT COUNT(DISTINCT it.imageId)
             FROM image_tags it 
             INNER JOIN tags t ON it.tagId = t.tagId 
             WHERE t.value = 'burst_representative') as burstRepresentatives
    """)
    fun getBurstStatsFlow(): Flow<BurstStats?>

    /**
     * Get count of burst photos
     */
    @Query("""
        SELECT COUNT(DISTINCT it.imageId)
        FROM image_tags it
        INNER JOIN tags t ON it.tagId = t.tagId
        WHERE t.value = 'burst'
    """)
    suspend fun getBurstPhotoCount(): Int

    /**
     * Get count of burst representative photos
     * (photos marked as the best in each burst sequence)
     */
    @Query("""
        SELECT COUNT(DISTINCT it.imageId)
        FROM image_tags it
        INNER JOIN tags t ON it.tagId = t.tagId
        WHERE t.value = 'burst_representative'
    """)
    suspend fun getBurstRepresentativeCount(): Int

    /**
     * Get estimated number of burst groups
     * Assumes average of 3 photos per burst
     */
    @Query("""
        SELECT COUNT(DISTINCT it.imageId) / 3
        FROM image_tags it
        INNER JOIN tags t ON it.tagId = t.tagId
        WHERE t.value = 'burst'
    """)
    suspend fun getEstimatedBurstGroupCount(): Int
}