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

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

/**
 * UserFeedbackDao - Query user corrections and feedback
 *
 * KEY QUERIES:
 * - Get feedback for cluster validation
 * - Find rejected faces to exclude from training
 * - Track feedback statistics for quality metrics
 * - Support cluster refinement workflow
 */
@Dao
interface UserFeedbackDao {

    // ═══════════════════════════════════════
    // INSERT / UPDATE
    // ═══════════════════════════════════════

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(feedback: UserFeedbackEntity): Long

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

    @Update
    suspend fun update(feedback: UserFeedbackEntity)

    @Delete
    suspend fun delete(feedback: UserFeedbackEntity)

    // ═══════════════════════════════════════
    // CLUSTER VALIDATION QUERIES
    // ═══════════════════════════════════════

    /**
     * Get all feedback for a cluster
     * Used during validation to see what user has reviewed
     */
    @Query("SELECT * FROM user_feedback WHERE clusterId = :clusterId ORDER BY timestamp DESC")
    suspend fun getFeedbackForCluster(clusterId: Int): List<UserFeedbackEntity>

    /**
     * Get rejected faces for a cluster
     * These faces should be EXCLUDED from training
     */
    @Query("""
        SELECT * FROM user_feedback 
        WHERE clusterId = :clusterId 
        AND feedbackType = 'REJECTED_MATCH'
    """)
    suspend fun getRejectedFacesForCluster(clusterId: Int): List<UserFeedbackEntity>

    /**
     * Get confirmed faces for a cluster
     * These faces are SAFE for training
     */
    @Query("""
        SELECT * FROM user_feedback 
        WHERE clusterId = :clusterId 
        AND feedbackType = 'CONFIRMED_MATCH'
    """)
    suspend fun getConfirmedFacesForCluster(clusterId: Int): List<UserFeedbackEntity>

    /**
     * Count feedback by type for a cluster
     * Used to show stats: "15 confirmed, 3 rejected"
     */
    @Query("""
        SELECT feedbackType, COUNT(*) as count
        FROM user_feedback
        WHERE clusterId = :clusterId
        GROUP BY feedbackType
    """)
    suspend fun getFeedbackStatsByCluster(clusterId: Int): List<FeedbackStat>

    // ═══════════════════════════════════════
    // PERSON FEEDBACK QUERIES
    // ═══════════════════════════════════════

    /**
     * Get all feedback for a person
     * Used to show history of corrections
     */
    @Query("SELECT * FROM user_feedback WHERE personId = :personId ORDER BY timestamp DESC")
    suspend fun getFeedbackForPerson(personId: String): List<UserFeedbackEntity>

    /**
     * Get rejected faces for a person
     * User said "this is NOT X" - exclude from model improvement
     */
    @Query("""
        SELECT * FROM user_feedback 
        WHERE personId = :personId 
        AND feedbackType = 'REJECTED_MATCH'
    """)
    suspend fun getRejectedFacesForPerson(personId: String): List<UserFeedbackEntity>

    /**
     * Flow version for reactive UI
     */
    @Query("SELECT * FROM user_feedback WHERE personId = :personId ORDER BY timestamp DESC")
    fun observeFeedbackForPerson(personId: String): Flow<List<UserFeedbackEntity>>

    // ═══════════════════════════════════════
    // IMAGE QUERIES
    // ═══════════════════════════════════════

    /**
     * Get feedback for a specific image
     */
    @Query("SELECT * FROM user_feedback WHERE imageId = :imageId")
    suspend fun getFeedbackForImage(imageId: String): List<UserFeedbackEntity>

    /**
     * Check if user has provided feedback for a specific face
     */
    @Query("""
        SELECT EXISTS(
            SELECT 1 FROM user_feedback 
            WHERE imageId = :imageId 
            AND faceIndex = :faceIndex
        )
    """)
    suspend fun hasFeedbackForFace(imageId: String, faceIndex: Int): Boolean

    // ═══════════════════════════════════════
    // STATISTICS & ANALYTICS
    // ═══════════════════════════════════════

    /**
     * Get total feedback count
     */
    @Query("SELECT COUNT(*) FROM user_feedback")
    suspend fun getTotalFeedbackCount(): Int

    /**
     * Get feedback count by type (global)
     */
    @Query("""
        SELECT feedbackType, COUNT(*) as count
        FROM user_feedback
        GROUP BY feedbackType
    """)
    suspend fun getGlobalFeedbackStats(): List<FeedbackStat>

    /**
     * Get average original confidence for rejected faces
     * Helps identify if low confidence → more rejections
     */
    @Query("""
        SELECT AVG(originalConfidence) 
        FROM user_feedback 
        WHERE feedbackType = 'REJECTED_MATCH'
    """)
    suspend fun getAverageConfidenceForRejectedFaces(): Float?

    /**
     * Find faces with low confidence that were confirmed
     * These are "surprising successes" - model worked despite low confidence
     */
    @Query("""
        SELECT * FROM user_feedback 
        WHERE feedbackType = 'CONFIRMED_MATCH' 
        AND originalConfidence < :threshold
        ORDER BY originalConfidence ASC
    """)
    suspend fun getLowConfidenceSuccesses(threshold: Float = 0.7f): List<UserFeedbackEntity>

    // ═══════════════════════════════════════
    // CLEANUP
    // ═══════════════════════════════════════

    /**
     * Delete all feedback for a cluster
     * Called when cluster is deleted or refined
     */
    @Query("DELETE FROM user_feedback WHERE clusterId = :clusterId")
    suspend fun deleteFeedbackForCluster(clusterId: Int)

    /**
     * Delete all feedback for a person
     * Called when person is deleted
     */
    @Query("DELETE FROM user_feedback WHERE personId = :personId")
    suspend fun deleteFeedbackForPerson(personId: String)

    /**
     * Delete old feedback (older than X days)
     * Keep database size manageable
     */
    @Query("DELETE FROM user_feedback WHERE timestamp < :cutoffTimestamp")
    suspend fun deleteOldFeedback(cutoffTimestamp: Long)

    /**
     * Clear all feedback (nuclear option)
     */
    @Query("DELETE FROM user_feedback")
    suspend fun deleteAll()
}

/**
 * Result class for feedback statistics
 */
data class FeedbackStat(
    val feedbackType: String,
    val count: Int
)