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

import androidx.room.*
import com.placeholder.sherpai2.data.local.entity.FaceCacheEntity

/**
 * FaceCacheDao - NO SOLO-PHOTO FILTER
 *
 * CRITICAL CHANGE:
 * ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
 * Removed all faceCount filters from queries
 *
 * WHY:
 * - Group photos contain high-quality faces (especially for children)
 * - IoU matching ensures we extract the CORRECT face from group photos
 * - Rejecting group photos was eliminating 60-70% of quality faces!
 *
 * RESULT:
 * - 2-3x more faces for clustering
 * - Quality remains high (still filter by size + score)
 * - Better clusters, especially for children
 */
@Dao
interface FaceCacheDao {

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(faceCacheEntity: FaceCacheEntity)

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

    @Update
    suspend fun update(faceCacheEntity: FaceCacheEntity)

    /**
     * Get ALL quality faces - INCLUDES GROUP PHOTOS!
     *
     * Quality filters (still strict):
     * - faceAreaRatio >= minRatio (default 3% of image)
     * - qualityScore >= minQuality (default 0.6 = 60%)
     * - Has embedding
     *
     * NO faceCount filter!
     */
    @Query("""
        SELECT fc.* 
        FROM face_cache fc
        WHERE fc.faceAreaRatio >= :minRatio
          AND fc.qualityScore >= :minQuality
          AND fc.embedding IS NOT NULL
        ORDER BY fc.qualityScore DESC, fc.faceAreaRatio DESC
        LIMIT :limit
    """)
    suspend fun getAllQualityFaces(
        minRatio: Float = 0.03f,
        minQuality: Float = 0.6f,
        limit: Int = Int.MAX_VALUE
    ): List<FaceCacheEntity>

    /**
     * Get quality faces WITHOUT embeddings - FOR PATH 2
     *
     * These have good metadata but need embeddings generated.
     * INCLUDES GROUP PHOTOS - IoU matching will handle extraction!
     */
    @Query("""
        SELECT fc.* 
        FROM face_cache fc
        WHERE fc.faceAreaRatio >= :minRatio
          AND fc.qualityScore >= :minQuality
          AND fc.embedding IS NULL
        ORDER BY fc.qualityScore DESC, fc.faceAreaRatio DESC
        LIMIT :limit
    """)
    suspend fun getQualityFacesWithoutEmbeddings(
        minRatio: Float = 0.03f,
        minQuality: Float = 0.6f,
        limit: Int = 5000
    ): List<FaceCacheEntity>

    /**
     * Count faces WITH embeddings (Path 1 check)
     */
    @Query("""
        SELECT COUNT(*) 
        FROM face_cache 
        WHERE embedding IS NOT NULL 
          AND qualityScore >= :minQuality
    """)
    suspend fun countFacesWithEmbeddings(minQuality: Float = 0.6f): Int

    /**
     * Count faces WITHOUT embeddings (Path 2 check)
     */
    @Query("""
        SELECT COUNT(*) 
        FROM face_cache 
        WHERE embedding IS NULL 
          AND qualityScore >= :minQuality
    """)
    suspend fun countFacesWithoutEmbeddings(minQuality: Float = 0.6f): Int

    /**
     * Get faces for specific image (for IoU matching)
     */
    @Query("SELECT * FROM face_cache WHERE imageId = :imageId")
    suspend fun getFaceCacheForImage(imageId: String): List<FaceCacheEntity>

    /**
     * Cache statistics
     */
    @Query("""
        SELECT 
            COUNT(*) as totalFaces,
            COUNT(CASE WHEN embedding IS NOT NULL THEN 1 END) as withEmbeddings,
            AVG(qualityScore) as avgQuality,
            AVG(faceAreaRatio) as avgSize
        FROM face_cache
    """)
    suspend fun getCacheStats(): CacheStats

    @Query("DELETE FROM face_cache WHERE imageId = :imageId")
    suspend fun deleteCacheForImage(imageId: String)

    @Query("DELETE FROM face_cache")
    suspend fun deleteAll()
}

data class CacheStats(
    val totalFaces: Int,
    val withEmbeddings: Int,
    val avgQuality: Float,
    val avgSize: Float
)