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

import androidx.room.*
import kotlinx.coroutines.flow.Flow
import com.placeholder.sherpai2.data.local.entity.PhotoFaceTagEntity

@Dao
interface PhotoFaceTagDao {

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertTag(tag: PhotoFaceTagEntity): Long

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertTags(tags: List<PhotoFaceTagEntity>)

    @Update
    suspend fun updateTag(tag: PhotoFaceTagEntity)

    /**
     * FIXED: Removed default parameter
     */
    @Query("UPDATE photo_face_tags SET verifiedByUser = 1, verifiedAt = :timestamp WHERE id = :tagId")
    suspend fun markTagAsVerified(tagId: String, timestamp: Long)

    // ===== QUERY BY IMAGE =====

    @Query("SELECT * FROM photo_face_tags WHERE imageId = :imageId")
    suspend fun getTagsForImage(imageId: String): List<PhotoFaceTagEntity>

    @Query("SELECT COUNT(*) FROM photo_face_tags WHERE imageId = :imageId")
    suspend fun getFaceCountForImage(imageId: String): Int

    @Query("SELECT EXISTS(SELECT 1 FROM photo_face_tags WHERE imageId = :imageId AND faceModelId = :faceModelId)")
    suspend fun imageHasPerson(imageId: String, faceModelId: String): Boolean

    // ===== QUERY BY FACE MODEL =====

    @Query("SELECT DISTINCT imageId FROM photo_face_tags WHERE faceModelId = :faceModelId ORDER BY detectedAt DESC")
    suspend fun getImageIdsForFaceModel(faceModelId: String): List<String>

    @Query("SELECT DISTINCT imageId FROM photo_face_tags WHERE faceModelId = :faceModelId ORDER BY detectedAt DESC")
    fun getImageIdsForFaceModelFlow(faceModelId: String): Flow<List<String>>

    @Query("SELECT faceModelId, COUNT(DISTINCT imageId) as photoCount FROM photo_face_tags GROUP BY faceModelId")
    suspend fun getPhotoCountPerFaceModel(): List<FaceModelPhotoCount>

    @Query("SELECT * FROM photo_face_tags WHERE faceModelId = :faceModelId ORDER BY detectedAt DESC")
    suspend fun getAllTagsForFaceModel(faceModelId: String): List<PhotoFaceTagEntity>

    // ===== DELETE =====

    @Delete
    suspend fun deleteTag(tag: PhotoFaceTagEntity)

    @Query("DELETE FROM photo_face_tags WHERE id = :tagId")
    suspend fun deleteTagById(tagId: String)

    @Query("DELETE FROM photo_face_tags WHERE faceModelId = :faceModelId")
    suspend fun deleteTagsForFaceModel(faceModelId: String)

    @Query("DELETE FROM photo_face_tags WHERE imageId = :imageId")
    suspend fun deleteTagsForImage(imageId: String)

    // ===== STATISTICS =====

    /**
     * FIXED: Removed default parameter
     */
    @Query("SELECT * FROM photo_face_tags WHERE confidence < :threshold ORDER BY confidence ASC")
    suspend fun getLowConfidenceTags(threshold: Float): List<PhotoFaceTagEntity>

    @Query("SELECT * FROM photo_face_tags WHERE verifiedByUser = 0 ORDER BY detectedAt DESC")
    suspend fun getUnverifiedTags(): List<PhotoFaceTagEntity>

    @Query("SELECT COUNT(*) FROM photo_face_tags WHERE verifiedByUser = 0")
    suspend fun getUnverifiedTagCount(): Int

    @Query("SELECT AVG(confidence) FROM photo_face_tags WHERE faceModelId = :faceModelId")
    suspend fun getAverageConfidenceForFaceModel(faceModelId: String): Float?

    /**
     * FIXED: Removed default parameter
     */
    @Query("SELECT * FROM photo_face_tags ORDER BY detectedAt DESC LIMIT :limit")
    suspend fun getRecentlyDetectedFaces(limit: Int): List<PhotoFaceTagEntity>

    // ===== CO-OCCURRENCE QUERIES =====

    /**
     * Find people who appear in photos together with a given person.
     * Returns list of (otherFaceModelId, count) sorted by count descending.
     * Use case: "Who appears most with Mom?" or "Show photos of Mom WITH Dad"
     */
    @Query("""
        SELECT pft2.faceModelId as otherFaceModelId, COUNT(DISTINCT pft1.imageId) as coCount
        FROM photo_face_tags pft1
        INNER JOIN photo_face_tags pft2 ON pft1.imageId = pft2.imageId
        WHERE pft1.faceModelId = :faceModelId
        AND pft2.faceModelId != :faceModelId
        GROUP BY pft2.faceModelId
        ORDER BY coCount DESC
    """)
    suspend fun getCoOccurrences(faceModelId: String): List<PersonCoOccurrence>

    /**
     * Get images where BOTH people appear together.
     */
    @Query("""
        SELECT DISTINCT pft1.imageId
        FROM photo_face_tags pft1
        INNER JOIN photo_face_tags pft2 ON pft1.imageId = pft2.imageId
        WHERE pft1.faceModelId = :faceModelId1
        AND pft2.faceModelId = :faceModelId2
        ORDER BY pft1.detectedAt DESC
    """)
    suspend fun getImagesWithBothPeople(faceModelId1: String, faceModelId2: String): List<String>

    /**
     * Get images where person appears ALONE (no other trained faces).
     */
    @Query("""
        SELECT imageId FROM photo_face_tags
        WHERE faceModelId = :faceModelId
        AND imageId NOT IN (
            SELECT imageId FROM photo_face_tags
            WHERE faceModelId != :faceModelId
        )
        ORDER BY detectedAt DESC
    """)
    suspend fun getImagesWithPersonAlone(faceModelId: String): List<String>

    /**
     * Get images where ALL specified people appear (N-way intersection).
     * For "Intersection Search" moonshot feature.
     */
    @Query("""
        SELECT imageId FROM photo_face_tags
        WHERE faceModelId IN (:faceModelIds)
        GROUP BY imageId
        HAVING COUNT(DISTINCT faceModelId) = :requiredCount
    """)
    suspend fun getImagesWithAllPeople(faceModelIds: List<String>, requiredCount: Int): List<String>

    /**
     * Get images with at least N of the specified people (family portrait detection).
     */
    @Query("""
        SELECT imageId, COUNT(DISTINCT faceModelId) as memberCount
        FROM photo_face_tags
        WHERE faceModelId IN (:faceModelIds)
        GROUP BY imageId
        HAVING memberCount >= :minMembers
        ORDER BY memberCount DESC
    """)
    suspend fun getFamilyPortraits(faceModelIds: List<String>, minMembers: Int): List<FamilyPortraitResult>
}

data class FamilyPortraitResult(
    val imageId: String,
    val memberCount: Int
)

data class FaceModelPhotoCount(
    val faceModelId: String,
    val photoCount: Int
)

data class PersonCoOccurrence(
    val otherFaceModelId: String,
    val coCount: Int
)
