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

import androidx.room.Dao
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import androidx.room.Transaction
import com.placeholder.sherpai2.data.local.entity.ImageEntity
import com.placeholder.sherpai2.data.local.model.ImageWithEverything
import kotlinx.coroutines.flow.Flow

/**
 * Data classes for statistics queries
 */
data class DateCount(
    val date: String,  // YYYY-MM-DD format
    val count: Int
)

data class MonthCount(
    val month: String, // YYYY-MM format
    val count: Int
)

data class YearCount(
    val year: String,  // YYYY format
    val count: Int
)

data class DayOfWeekCount(
    val dayOfWeek: Int, // 0 = Sunday, 6 = Saturday
    val count: Int
)

data class HourCount(
    val hour: Int,     // 0-23
    val count: Int
)

/**
 * Face detection cache statistics
 */
data class FaceCacheStats(
    val totalImages: Int,
    val imagesWithFaceCache: Int,
    val imagesWithFaces: Int,
    val imagesWithoutFaces: Int,
    val needsScanning: Int
)

@Dao
interface ImageDao {

    /**
     * Insert images.
     *
     * IGNORE prevents duplicate insertion
     * when sha256 or imageUri already exists.
     */
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun insertImages(images: List<ImageEntity>)

    /**
     * Get image by ID.
     */
    @Query("SELECT * FROM images WHERE imageId = :imageId")
    suspend fun getImageById(imageId: String): ImageEntity?

    @Query("SELECT * FROM images WHERE imageUri = :uri LIMIT 1")
    suspend fun getImageByUri(uri: String): ImageEntity?

    /**
     * Stream images ordered by capture time (newest first).
     *
     * Flow is critical:
     * - UI auto-updates
     * - No manual refresh
     */
    @Query("""
        SELECT * FROM images
        ORDER BY capturedAt DESC
    """)
    fun observeAllImages(): Flow<List<ImageEntity>>

    /**
     * Fetch images in a time range.
     * Used for:
     * - event auto-assignment
     * - timeline views
     */
    @Query("""
        SELECT * FROM images
        WHERE capturedAt BETWEEN :start AND :end
        ORDER BY capturedAt ASC
    """)
    suspend fun getImagesInRange(
        start: Long,
        end: Long
    ): List<ImageEntity>

    @Transaction
    @Query("SELECT * FROM images ORDER BY capturedAt DESC LIMIT :limit")
    fun getRecentImages(limit: Int): Flow<List<ImageWithEverything>>

    @Query("SELECT COUNT(*) > 0 FROM images WHERE sha256 = :sha256")
    suspend fun existsBySha256(sha256: String): Boolean

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun insert(image: ImageEntity)

    /**
     * Get images by list of IDs.
     */
    @Query("SELECT * FROM images WHERE imageId IN (:imageIds)")
    suspend fun getImagesByIds(imageIds: List<String>): List<ImageEntity>

    @Query("SELECT COUNT(*) FROM images")
    suspend fun getImageCount(): Int

    /**
     * Get all images (for utilities processing)
     */
    @Query("SELECT * FROM images ORDER BY capturedAt DESC")
    suspend fun getAllImages(): List<ImageEntity>

    /**
     * Get all images sorted by time (for burst detection)
     */
    @Query("SELECT * FROM images ORDER BY capturedAt ASC")
    suspend fun getAllImagesSortedByTime(): List<ImageEntity>

    // ==========================================
    // FACE DETECTION CACHE QUERIES - CRITICAL FOR OPTIMIZATION
    // ==========================================

    /**
     * Get all images that have faces (cached).
     * This is the PRIMARY optimization query.
     *
     * Use this for person scanning instead of scanning ALL images.
     * Estimated speed improvement: 50-70% for typical photo libraries.
     */
    @Query("""
        SELECT * FROM images
        WHERE hasFaces = 1
        AND faceDetectionVersion = :currentVersion
        ORDER BY capturedAt DESC
    """)
    suspend fun getImagesWithFaces(currentVersion: Int = ImageEntity.CURRENT_FACE_DETECTION_VERSION): List<ImageEntity>

    /**
     * Get images with faces, limited (for progressive scanning)
     */
    @Query("""
        SELECT * FROM images
        WHERE hasFaces = 1
        AND faceDetectionVersion = :currentVersion
        ORDER BY capturedAt DESC
        LIMIT :limit
    """)
    suspend fun getImagesWithFacesLimited(
        limit: Int,
        currentVersion: Int = ImageEntity.CURRENT_FACE_DETECTION_VERSION
    ): List<ImageEntity>

    /**
     * Get images with a specific face count.
     * Use cases:
     * - Solo photos (faceCount = 1)
     * - Couple photos (faceCount = 2)
     * - Filter out groups (faceCount <= 2)
     */
    @Query("""
        SELECT * FROM images
        WHERE hasFaces = 1
        AND faceCount = :count
        AND faceDetectionVersion = :currentVersion
        ORDER BY capturedAt DESC
    """)
    suspend fun getImagesByFaceCount(
        count: Int,
        currentVersion: Int = ImageEntity.CURRENT_FACE_DETECTION_VERSION
    ): List<ImageEntity>

    /**
     * Get images with face count in range.
     * Examples:
     * - Solo or couple: minFaces=1, maxFaces=2
     * - Groups only: minFaces=3, maxFaces=999
     */
    @Query("""
        SELECT * FROM images
        WHERE hasFaces = 1
        AND faceCount BETWEEN :minFaces AND :maxFaces
        AND faceDetectionVersion = :currentVersion
        ORDER BY capturedAt DESC
    """)
    suspend fun getImagesByFaceCountRange(
        minFaces: Int,
        maxFaces: Int,
        currentVersion: Int = ImageEntity.CURRENT_FACE_DETECTION_VERSION
    ): List<ImageEntity>

    /**
     * Get images that need face detection scanning.
     * These images have:
     * - Never been scanned (hasFaces = null)
     * - Old detection version
     * - Invalid cache
     */
    @Query("""
        SELECT * FROM images
        WHERE hasFaces IS NULL
        OR faceDetectionVersion IS NULL
        OR faceDetectionVersion < :currentVersion
        ORDER BY capturedAt DESC
    """)
    suspend fun getImagesNeedingFaceDetection(
        currentVersion: Int = ImageEntity.CURRENT_FACE_DETECTION_VERSION
    ): List<ImageEntity>

    /**
     * Get count of images needing face detection.
     */
    @Query("""
        SELECT COUNT(*) FROM images
        WHERE hasFaces IS NULL
        OR faceDetectionVersion IS NULL
        OR faceDetectionVersion < :currentVersion
    """)
    suspend fun getImagesNeedingFaceDetectionCount(
        currentVersion: Int = ImageEntity.CURRENT_FACE_DETECTION_VERSION
    ): Int

    /**
     * Update face detection cache for a single image.
     * Called after detecting faces in an image.
     */
    @Query("""
        UPDATE images
        SET hasFaces = :hasFaces,
            faceCount = :faceCount,
            facesLastDetected = :timestamp,
            faceDetectionVersion = :version
        WHERE imageId = :imageId
    """)
    suspend fun updateFaceDetectionCache(
        imageId: String,
        hasFaces: Boolean,
        faceCount: Int,
        timestamp: Long = System.currentTimeMillis(),
        version: Int = ImageEntity.CURRENT_FACE_DETECTION_VERSION
    )

    /**
     * Batch update face detection cache.
     * More efficient when updating many images at once.
     *
     * Note: Room doesn't support batch updates directly,
     * so this needs to be called multiple times in a transaction.
     */
    @Transaction
    suspend fun updateFaceDetectionCacheBatch(updates: List<FaceDetectionCacheUpdate>) {
        updates.forEach { update ->
            updateFaceDetectionCache(
                imageId = update.imageId,
                hasFaces = update.hasFaces,
                faceCount = update.faceCount,
                timestamp = update.timestamp,
                version = update.version
            )
        }
    }

    /**
     * Get face detection cache statistics.
     * Useful for UI display and determining background scan needs.
     */
    @Query("""
        SELECT 
            COUNT(*) as totalImages,
            SUM(CASE WHEN hasFaces IS NOT NULL THEN 1 ELSE 0 END) as imagesWithFaceCache,
            SUM(CASE WHEN hasFaces = 1 THEN 1 ELSE 0 END) as imagesWithFaces,
            SUM(CASE WHEN hasFaces = 0 THEN 1 ELSE 0 END) as imagesWithoutFaces,
            SUM(CASE WHEN hasFaces IS NULL OR faceDetectionVersion < :currentVersion THEN 1 ELSE 0 END) as needsScanning
        FROM images
    """)
    suspend fun getFaceCacheStats(
        currentVersion: Int = ImageEntity.CURRENT_FACE_DETECTION_VERSION
    ): FaceCacheStats?

    /**
     * Invalidate face detection cache (force re-scan).
     * Call this when upgrading face detection algorithm.
     */
    @Query("""
        UPDATE images
        SET faceDetectionVersion = NULL
        WHERE faceDetectionVersion < :newVersion
    """)
    suspend fun invalidateFaceDetectionCache(newVersion: Int)

    /**
     * Clear ALL face detection cache (force full rebuild).
     * Sets all face detection fields to NULL for all images.
     *
     * Use this for "Force Rebuild Cache" button.
     * This is different from invalidateFaceDetectionCache which only
     * invalidates old versions - this clears EVERYTHING.
     */
    @Query("""
        UPDATE images
        SET hasFaces = NULL,
            faceCount = NULL,
            facesLastDetected = NULL,
            faceDetectionVersion = NULL
    """)
    suspend fun clearAllFaceDetectionCache()

    // ==========================================
    // STATISTICS QUERIES
    // ==========================================

    /**
     * Get photo counts by date (daily granularity)
     * Returns all days that have at least one photo
     */
    @Query("""
        SELECT 
            date(capturedAt/1000, 'unixepoch') as date,
            COUNT(*) as count
        FROM images
        GROUP BY date
        ORDER BY date ASC
    """)
    suspend fun getPhotoCountsByDate(): List<DateCount>

    /**
     * Get photo counts by month (monthly granularity)
     */
    @Query("""
        SELECT 
            strftime('%Y-%m', capturedAt/1000, 'unixepoch') as month,
            COUNT(*) as count
        FROM images
        GROUP BY month
        ORDER BY month ASC
    """)
    suspend fun getPhotoCountsByMonth(): List<MonthCount>

    /**
     * Get photo counts by year (yearly granularity)
     */
    @Query("""
        SELECT 
            strftime('%Y', capturedAt/1000, 'unixepoch') as year,
            COUNT(*) as count
        FROM images
        GROUP BY year
        ORDER BY year DESC
    """)
    suspend fun getPhotoCountsByYear(): List<YearCount>

    /**
     * Get photo counts by year (Flow version for reactive UI)
     */
    @Query("""
        SELECT 
            strftime('%Y', capturedAt/1000, 'unixepoch') as year,
            COUNT(*) as count
        FROM images
        GROUP BY year
        ORDER BY year DESC
    """)
    fun getPhotoCountsByYearFlow(): Flow<List<YearCount>>

    /**
     * Get photo counts by day of week (0 = Sunday, 6 = Saturday)
     * Shows which days you take the most photos
     */
    @Query("""
        SELECT 
            CAST(strftime('%w', capturedAt/1000, 'unixepoch') AS INTEGER) as dayOfWeek,
            COUNT(*) as count
        FROM images
        GROUP BY dayOfWeek
        ORDER BY dayOfWeek ASC
    """)
    suspend fun getPhotoCountsByDayOfWeek(): List<DayOfWeekCount>

    /**
     * Get photo counts by hour of day (0-23)
     * Shows when you take the most photos
     */
    @Query("""
        SELECT 
            CAST(strftime('%H', capturedAt/1000, 'unixepoch') AS INTEGER) as hour,
            COUNT(*) as count
        FROM images
        GROUP BY hour
        ORDER BY hour ASC
    """)
    suspend fun getPhotoCountsByHour(): List<HourCount>

    /**
     * Get earliest and latest photo timestamps
     * Used for date range calculations
     */
    @Query("""
        SELECT 
            MIN(capturedAt) as earliest,
            MAX(capturedAt) as latest
        FROM images
    """)
    suspend fun getPhotoDateRange(): PhotoDateRange?

    /**
     * Get photo count for a specific year
     */
    @Query("""
        SELECT COUNT(*) FROM images
        WHERE strftime('%Y', capturedAt/1000, 'unixepoch') = :year
    """)
    suspend fun getPhotoCountForYear(year: String): Int

    /**
     * Get average photos per day (for stats display)
     */
    @Query("""
        SELECT 
            CAST(COUNT(*) AS REAL) / 
            CAST((MAX(capturedAt) - MIN(capturedAt)) / 86400000 AS REAL) as avgPerDay
        FROM images
        WHERE (SELECT COUNT(*) FROM images) > 0
    """)
    suspend fun getAveragePhotosPerDay(): Float?

    @Query("SELECT * FROM images WHERE hasFaces = 1 ORDER BY faceCount DESC")
    suspend fun getImagesWithFaces(): List<ImageEntity>

}

/**
 * Data class for date range result
 */
data class PhotoDateRange(
    val earliest: Long,
    val latest: Long
)

/**
 * Data class for batch face detection cache updates
 */
data class FaceDetectionCacheUpdate(
    val imageId: String,
    val hasFaces: Boolean,
    val faceCount: Int,
    val timestamp: Long = System.currentTimeMillis(),
    val version: Int = ImageEntity.CURRENT_FACE_DETECTION_VERSION
)