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

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

/**
 * PersonAgeTagDao - Manage searchable age tags for children
 *
 * USAGE EXAMPLES:
 * - Search "emma age 3" → getImageIdsForTag("emma_age3")
 * - Find all photos of Emma at age 5 → getImageIdsForPersonAtAge(emmaId, 5)
 * - Get age progression → getTagsForPerson(emmaId) sorted by age
 */
@Dao
interface PersonAgeTagDao {

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

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

    /**
     * Get all age tags for a person (sorted by age)
     * Useful for age progression timeline
     */
    @Query("SELECT * FROM person_age_tags WHERE personId = :personId ORDER BY ageAtCapture ASC")
    suspend fun getTagsForPerson(personId: String): List<PersonAgeTagEntity>

    /**
     * Get all age tags for an image
     */
    @Query("SELECT * FROM person_age_tags WHERE imageId = :imageId")
    suspend fun getTagsForImage(imageId: String): List<PersonAgeTagEntity>

    /**
     * Search by tag value (e.g., "emma_age3")
     * Returns all image IDs matching this tag
     */
    @Query("SELECT DISTINCT imageId FROM person_age_tags WHERE tagValue = :tagValue")
    suspend fun getImageIdsForTag(tagValue: String): List<String>

    /**
     * Get images of a person at a specific age
     */
    @Query("SELECT DISTINCT imageId FROM person_age_tags WHERE personId = :personId AND ageAtCapture = :age")
    suspend fun getImageIdsForPersonAtAge(personId: String, age: Int): List<String>

    /**
     * Get images of a person in an age range
     */
    @Query("""
        SELECT DISTINCT imageId FROM person_age_tags 
        WHERE personId = :personId 
        AND ageAtCapture BETWEEN :minAge AND :maxAge
        ORDER BY ageAtCapture ASC
    """)
    suspend fun getImageIdsForPersonAgeRange(personId: String, minAge: Int, maxAge: Int): List<String>

    /**
     * Get all unique ages for a person (for age picker UI)
     */
    @Query("SELECT DISTINCT ageAtCapture FROM person_age_tags WHERE personId = :personId ORDER BY ageAtCapture ASC")
    suspend fun getAgesForPerson(personId: String): List<Int>

    /**
     * Delete all tags for a person
     */
    @Query("DELETE FROM person_age_tags WHERE personId = :personId")
    suspend fun deleteTagsForPerson(personId: String)

    /**
     * Delete all tags for an image
     */
    @Query("DELETE FROM person_age_tags WHERE imageId = :imageId")
    suspend fun deleteTagsForImage(imageId: String)

    /**
     * Get count of photos at each age (for statistics)
     */
    @Query("""
        SELECT ageAtCapture, COUNT(DISTINCT imageId) as count 
        FROM person_age_tags 
        WHERE personId = :personId 
        GROUP BY ageAtCapture 
        ORDER BY ageAtCapture ASC
    """)
    suspend fun getPhotoCountByAge(personId: String): List<AgePhotoCount>

    /**
     * Flow version for reactive UI
     */
    @Query("SELECT * FROM person_age_tags WHERE personId = :personId ORDER BY ageAtCapture ASC")
    fun getTagsForPersonFlow(personId: String): Flow<List<PersonAgeTagEntity>>
}

/**
 * Data class for age photo count statistics
 */
data class AgePhotoCount(
    val ageAtCapture: Int,
    val count: Int
)