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

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

/**
 * Data class for tag statistics
 */
data class TagStat(
    val tagValue: String,
    val tagType: String,
    val imageCount: Int,
    val tagId: String
)

/**
 * TagDao - Tag management with face recognition integration
 *
 * NO DEFAULT PARAMETERS - Room doesn't support them in @Query methods
 */
@Dao
interface TagDao {

    // ======================
    // BASIC OPERATIONS
    // ======================

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun insert(tag: TagEntity): Long

    @Query("SELECT * FROM tags WHERE value = :value LIMIT 1")
    suspend fun getByValue(value: String): TagEntity?

    @Query("SELECT * FROM tags WHERE tagId = :tagId")
    suspend fun getById(tagId: String): TagEntity?

    @Query("SELECT * FROM tags ORDER BY value ASC")
    suspend fun getAll(): List<TagEntity>

    @Query("SELECT * FROM tags ORDER BY value ASC")
    fun getAllFlow(): Flow<List<TagEntity>>

    @Query("SELECT * FROM tags WHERE type = :type ORDER BY value ASC")
    suspend fun getByType(type: String): List<TagEntity>

    @Query("DELETE FROM tags WHERE tagId = :tagId")
    suspend fun delete(tagId: String)

    // ======================
    // STATISTICS (returns TagWithUsage)
    // ======================

    /**
     * Get most used tags WITH usage counts
     *
     * @param limit Maximum number of tags to return
     */
    @Query("""
        SELECT t.tagId, t.type, t.value, t.createdAt, 
               COUNT(it.imageId) as usage_count 
        FROM tags t
        LEFT JOIN image_tags it ON t.tagId = it.tagId
        GROUP BY t.tagId
        ORDER BY usage_count DESC
        LIMIT :limit
    """)
    suspend fun getMostUsedTags(limit: Int): List<TagWithUsage>

    /**
     * Get tag usage count
     */
    @Query("""
        SELECT COUNT(DISTINCT it.imageId)
        FROM image_tags it
        WHERE it.tagId = :tagId
    """)
    suspend fun getTagUsageCount(tagId: String): Int

    // ======================
    // PERSON INTEGRATION
    // ======================

    /**
     * Get all tags used for images containing a specific person
     */
    @Query("""
        SELECT DISTINCT t.* FROM tags t
        INNER JOIN image_tags it ON t.tagId = it.tagId
        INNER JOIN photo_face_tags pft ON it.imageId = pft.imageId
        INNER JOIN face_models fm ON pft.faceModelId = fm.id
        WHERE fm.personId = :personId
        ORDER BY t.value ASC
    """)
    suspend fun getTagsForPerson(personId: String): List<TagEntity>

    /**
     * Get images that have both a specific tag AND contain a specific person
     */
    @Query("""
        SELECT DISTINCT i.* FROM images i
        INNER JOIN image_tags it ON i.imageId = it.imageId
        INNER JOIN photo_face_tags pft ON i.imageId = pft.imageId
        INNER JOIN face_models fm ON pft.faceModelId = fm.id
        WHERE it.tagId = :tagId AND fm.personId = :personId
        ORDER BY i.capturedAt DESC
    """)
    suspend fun getImagesWithTagAndPerson(
        tagId: String,
        personId: String
    ): List<ImageEntity>

    /**
     * Get images with tag and person as Flow
     */
    @Query("""
        SELECT DISTINCT i.* FROM images i
        INNER JOIN image_tags it ON i.imageId = it.imageId
        INNER JOIN photo_face_tags pft ON i.imageId = pft.imageId
        INNER JOIN face_models fm ON pft.faceModelId = fm.id
        WHERE it.tagId = :tagId AND fm.personId = :personId
        ORDER BY i.capturedAt DESC
    """)
    fun getImagesWithTagAndPersonFlow(
        tagId: String,
        personId: String
    ): Flow<List<ImageEntity>>

    /**
     * Count images with tag and person
     */
    @Query("""
        SELECT COUNT(DISTINCT i.imageId) FROM images i
        INNER JOIN image_tags it ON i.imageId = it.imageId
        INNER JOIN photo_face_tags pft ON i.imageId = pft.imageId
        INNER JOIN face_models fm ON pft.faceModelId = fm.id
        WHERE it.tagId = :tagId AND fm.personId = :personId
    """)
    suspend fun countImagesWithTagAndPerson(
        tagId: String,
        personId: String
    ): Int

    // ======================
    // AUTO-SUGGESTIONS
    // ======================

    /**
     * Suggest tags based on person's relationship
     *
     * @param limit Maximum number of suggestions
     */
    @Query("""
        SELECT DISTINCT t.* FROM tags t
        INNER JOIN image_tags it ON t.tagId = it.tagId
        INNER JOIN photo_face_tags pft ON it.imageId = pft.imageId
        INNER JOIN face_models fm ON pft.faceModelId = fm.id
        INNER JOIN persons p ON fm.personId = p.id
        WHERE p.relationship = :relationship
        AND p.id != :excludePersonId
        GROUP BY t.tagId
        ORDER BY COUNT(it.imageId) DESC
        LIMIT :limit
    """)
    suspend fun suggestTagsBasedOnRelationship(
        relationship: String,
        excludePersonId: String,
        limit: Int
    ): List<TagEntity>

    /**
     * Get tags commonly used with this tag
     *
     * @param limit Maximum number of related tags
     */
    @Query("""
        SELECT DISTINCT t2.* FROM tags t2
        INNER JOIN image_tags it2 ON t2.tagId = it2.tagId
        WHERE it2.imageId IN (
            SELECT it1.imageId FROM image_tags it1
            WHERE it1.tagId = :tagId
        )
        AND t2.tagId != :tagId
        GROUP BY t2.tagId
        ORDER BY COUNT(it2.imageId) DESC
        LIMIT :limit
    """)
    suspend fun getRelatedTags(
        tagId: String,
        limit: Int
    ): List<TagEntity>

    // ======================
    // SEARCH
    // ======================

    /**
     * Search tags by value (partial match)
     *
     * @param limit Maximum number of results
     */
    @Query("""
        SELECT * FROM tags 
        WHERE value LIKE '%' || :query || '%'
        ORDER BY value ASC
        LIMIT :limit
    """)
    suspend fun searchTags(query: String, limit: Int): List<TagEntity>

    /**
     * Search tags with usage count
     *
     * @param limit Maximum number of results
     */
    @Query("""
        SELECT t.tagId, t.type, t.value, t.createdAt,
               COUNT(it.imageId) as usage_count 
        FROM tags t
        LEFT JOIN image_tags it ON t.tagId = it.tagId
        WHERE t.value LIKE '%' || :query || '%'
        GROUP BY t.tagId
        ORDER BY usage_count DESC, t.value ASC
        LIMIT :limit
    """)
    suspend fun searchTagsWithUsage(query: String, limit: Int): List<TagWithUsage>

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

    /**
     * Get system tag statistics (for utilities stats display)
     * Returns tag value, type, and count of tagged images
     */
    @Query("""
        SELECT 
            t.value as tagValue,
            t.type as tagType,
            COUNT(DISTINCT it.imageId) as imageCount,
            t.tagId as tagId
        FROM tags t
        INNER JOIN image_tags it ON t.tagId = it.tagId
        WHERE t.type = 'SYSTEM'
        GROUP BY t.tagId
        ORDER BY imageCount DESC
    """)
    suspend fun getSystemTagStats(): List<TagStat>

    /**
     * Get system tag statistics (Flow version for reactive UI)
     */
    @Query("""
        SELECT 
            t.value as tagValue,
            t.type as tagType,
            COUNT(DISTINCT it.imageId) as imageCount,
            t.tagId as tagId
        FROM tags t
        INNER JOIN image_tags it ON t.tagId = it.tagId
        WHERE t.type = 'SYSTEM'
        GROUP BY t.tagId
        ORDER BY imageCount DESC
    """)
    fun getSystemTagStatsFlow(): Flow<List<TagStat>>

    /**
     * Get count of photos with a specific system tag
     */
    @Query("""
        SELECT COUNT(DISTINCT it.imageId)
        FROM image_tags it
        INNER JOIN tags t ON it.tagId = t.tagId
        WHERE t.value = :tagValue AND t.type = 'SYSTEM'
    """)
    suspend fun getSystemTagCount(tagValue: String): Int

    /**
     * Get all tag types with counts
     * Shows breakdown of SYSTEM vs USER vs GENERIC tags
     */
    @Query("""
        SELECT 
            t.type as tagValue,
            t.type as tagType,
            COUNT(DISTINCT t.tagId) as imageCount,
            '' as tagId
        FROM tags t
        GROUP BY t.type
        ORDER BY imageCount DESC
    """)
    suspend fun getTagTypeBreakdown(): List<TagStat>
}