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

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

/**
 * FaceCacheDao - ENHANCED with Rolling Scan support
 *
 * FIXED: Replaced Map return type with proper data class
 */
@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()

    // ═══════════════════════════════════════════════════════════════════════════
    // NEW: ROLLING SCAN SUPPORT
    // ═══════════════════════════════════════════════════════════════════════════

    /**
     * CRITICAL: Batch get face cache entries by image IDs
     *
     * Used by FaceSimilarityScorer to retrieve embeddings for scoring
     *
     * Performance: ~10ms for 1000 images with index on imageId
     */
    @Query("""
        SELECT * FROM face_cache 
        WHERE imageId IN (:imageIds)
          AND embedding IS NOT NULL
        ORDER BY qualityScore DESC
    """)
    suspend fun getFaceCacheByImageIds(imageIds: List<String>): List<FaceCacheEntity>

    /**
     * Get ALL photos with cached faces for rolling scan
     *
     * Returns all high-quality faces with embeddings
     * Sorted by quality (solo photos first due to quality boost)
     */
    @Query("""
        SELECT * FROM face_cache
        WHERE embedding IS NOT NULL
          AND qualityScore >= :minQuality
          AND faceAreaRatio >= :minRatio
        ORDER BY qualityScore DESC, faceAreaRatio DESC
    """)
    suspend fun getAllPhotosWithFacesForScanning(
        minQuality: Float = 0.6f,
        minRatio: Float = 0.03f
    ): List<FaceCacheEntity>

    /**
     * Get embedding for a single image
     *
     * If multiple faces in image, returns the highest quality face
     */
    @Query("""
        SELECT * FROM face_cache
        WHERE imageId = :imageId
          AND embedding IS NOT NULL
        ORDER BY qualityScore DESC
        LIMIT 1
    """)
    suspend fun getEmbeddingByImageId(imageId: String): FaceCacheEntity?

    /**
     * Get distinct image IDs with cached embeddings
     *
     * Useful for getting list of all scannable images
     */
    @Query("""
        SELECT DISTINCT imageId FROM face_cache
        WHERE embedding IS NOT NULL
          AND qualityScore >= :minQuality
        ORDER BY qualityScore DESC
    """)
    suspend fun getDistinctImageIdsWithEmbeddings(
        minQuality: Float = 0.6f
    ): List<String>

    /**
     * Get face count per image (for quality boosting)
     *
     * FIXED: Returns List<ImageFaceCount> instead of Map
     */
    @Query("""
        SELECT imageId, COUNT(*) as faceCount
        FROM face_cache
        WHERE embedding IS NOT NULL
        GROUP BY imageId
    """)
    suspend fun getFaceCountsPerImage(): List<ImageFaceCount>

    /**
     * Get embeddings for specific images (for centroid calculation)
     *
     * Used when initializing rolling scan with seed photos
     */
    @Query("""
        SELECT * FROM face_cache
        WHERE imageId IN (:imageIds)
          AND embedding IS NOT NULL
        ORDER BY qualityScore DESC
    """)
    suspend fun getEmbeddingsForImages(imageIds: List<String>): List<FaceCacheEntity>

    // ═══════════════════════════════════════════════════════════════════════════
    // PREMIUM FACES - For training photo selection
    // ═══════════════════════════════════════════════════════════════════════════

    /**
     * Get PREMIUM faces only - ideal for training seeds
     *
     * Premium = solo photo (faceCount=1) + large face + frontal + high quality
     *
     * These are the clearest, most unambiguous faces for user to pick seeds from.
     */
    @Query("""
        SELECT fc.* FROM face_cache fc
        INNER JOIN images i ON fc.imageId = i.imageId
        WHERE i.faceCount = 1
          AND fc.faceAreaRatio >= :minAreaRatio
          AND fc.isFrontal = 1
          AND fc.qualityScore >= :minQuality
          AND fc.embedding IS NOT NULL
        ORDER BY fc.qualityScore DESC, fc.faceAreaRatio DESC
        LIMIT :limit
    """)
    suspend fun getPremiumFaces(
        minAreaRatio: Float = 0.10f,
        minQuality: Float = 0.7f,
        limit: Int = 500
    ): List<FaceCacheEntity>

    /**
     * Get premium face CANDIDATES - same criteria but WITHOUT embedding requirement.
     * Used to find faces that need embedding generation.
     */
    @Query("""
        SELECT fc.* FROM face_cache fc
        INNER JOIN images i ON fc.imageId = i.imageId
        WHERE i.faceCount = 1
          AND fc.faceAreaRatio >= :minAreaRatio
          AND fc.isFrontal = 1
          AND fc.qualityScore >= :minQuality
          AND fc.embedding IS NULL
        ORDER BY fc.qualityScore DESC, fc.faceAreaRatio DESC
        LIMIT :limit
    """)
    suspend fun getPremiumFaceCandidatesNeedingEmbeddings(
        minAreaRatio: Float = 0.10f,
        minQuality: Float = 0.7f,
        limit: Int = 500
    ): List<FaceCacheEntity>

    /**
     * Update embedding for a face cache entry
     */
    @Query("UPDATE face_cache SET embedding = :embedding WHERE imageId = :imageId AND faceIndex = :faceIndex")
    suspend fun updateEmbedding(imageId: String, faceIndex: Int, embedding: String)

    /**
     * Count of premium faces available
     */
    @Query("""
        SELECT COUNT(*) FROM face_cache fc
        INNER JOIN images i ON fc.imageId = i.imageId
        WHERE i.faceCount = 1
          AND fc.faceAreaRatio >= 0.10
          AND fc.isFrontal = 1
          AND fc.qualityScore >= 0.7
          AND fc.embedding IS NOT NULL
    """)
    suspend fun countPremiumFaces(): Int
}

/**
 * Data class for face count per image
 *
 * Used by getFaceCountsPerImage() query
 */
data class ImageFaceCount(
    val imageId: String,
    val faceCount: Int
)

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