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

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

/**
 * CollectionDao - Data Access Object for managing user-defined and system-generated collections.
 * * Provides an interface for CRUD operations on the 'collections' table and manages the
 * many-to-many relationships between collections and images using junction tables.
 */
@Dao
interface CollectionDao {

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

    /**
     * Persists a new collection entity.
     * @param collection The entity to be inserted.
     * @return The row ID of the newly inserted item.
     * Strategy: REPLACE ensures that if a collection with the same ID exists, it is overwritten.
     */
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(collection: CollectionEntity): Long

    /**
     * Updates an existing collection based on its primary key.
     * @param collection The entity containing updated fields.
     */
    @Update
    suspend fun update(collection: CollectionEntity)

    /**
     * Removes a specific collection entity from the database.
     * @param collection The entity object to be deleted.
     */
    @Delete
    suspend fun delete(collection: CollectionEntity)

    /**
     * Deletes a collection entry directly by its unique string identifier.
     * @param collectionId The unique ID of the collection to remove.
     */
    @Query("DELETE FROM collections WHERE collectionId = :collectionId")
    suspend fun deleteById(collectionId: String)

    /**
     * One-shot fetch for a specific collection.
     * @param collectionId The unique ID of the collection.
     * @return The CollectionEntity if found, null otherwise.
     */
    @Query("SELECT * FROM collections WHERE collectionId = :collectionId")
    suspend fun getById(collectionId: String): CollectionEntity?

    /**
     * Reactive stream for a specific collection.
     * @param collectionId The unique ID of the collection.
     * @return A Flow that emits the CollectionEntity whenever that specific row changes.
     */
    @Query("SELECT * FROM collections WHERE collectionId = :collectionId")
    fun getByIdFlow(collectionId: String): Flow<CollectionEntity?>

    // =========================================================================================
    // LIST QUERIES (Observables)
    // =========================================================================================

    /**
     * Retrieves all collections for the main UI list.
     * Ordering: Prioritizes 'pinned' items first, then sorts by newest creation date.
     * @return A Flow emitting a list of collections, updating automatically on table changes.
     */
    @Query("""
        SELECT * FROM collections
        ORDER BY isPinned DESC, createdAt DESC
    """)
    fun getAllCollections(): Flow<List<CollectionEntity>>

    /**
     * Retrieves collections filtered by their type (e.g., SMART, STATIC, FAVORITE).
     * @param type The category string to filter by.
     * @return A Flow emitting the filtered list.
     */
    @Query("""
        SELECT * FROM collections
        WHERE type = :type
        ORDER BY isPinned DESC, createdAt DESC
    """)
    fun getCollectionsByType(type: String): Flow<List<CollectionEntity>>

    /**
     * Retrieves the single system-defined Favorite collection.
     * Used for quick access to the standard 'Likes' functionality.
     */
    @Query("SELECT * FROM collections WHERE type = 'FAVORITE' LIMIT 1")
    suspend fun getFavoriteCollection(): CollectionEntity?

    // =========================================================================================
    // COMPLEX RELATIONSHIPS & DATA MODELS
    // =========================================================================================

    /**
     * Retrieves a specialized model [CollectionWithDetails] which includes the base collection
     * data plus a dynamically calculated photo count from the junction table.
     * * @Transaction is required here because the query involves a subquery/multiple operations
     * to ensure data consistency across the result set.
     */
    @Transaction
    @Query("""
        SELECT 
            c.*,
            (SELECT COUNT(*) 
             FROM collection_images ci
             WHERE ci.collectionId = c.collectionId) as actualPhotoCount
        FROM collections c
        WHERE c.collectionId = :collectionId
    """)
    fun getCollectionWithDetails(collectionId: String): Flow<CollectionWithDetails?>

    // =========================================================================================
    // IMAGE MANAGEMENT (Junction Table: collection_images)
    // =========================================================================================

    /**
     * Maps an image to a collection in the junction table.
     */
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun addImage(collectionImage: CollectionImageEntity)

    /**
     * Batch maps multiple images to a collection. Useful for bulk imports or multi-selection.
     */
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun addImages(collectionImages: List<CollectionImageEntity>)

    /**
     * Removes a specific image from a specific collection.
     * Note: This does not delete the image from the 'images' table, only the relationship.
     */
    @Query("""
        DELETE FROM collection_images
        WHERE collectionId = :collectionId AND imageId = :imageId
    """)
    suspend fun removeImage(collectionId: String, imageId: String)

    /**
     * Clears all image associations for a specific collection.
     */
    @Query("DELETE FROM collection_images WHERE collectionId = :collectionId")
    suspend fun clearAllImages(collectionId: String)

    /**
     * Performs a JOIN to retrieve actual ImageEntity objects associated with a collection.
     * Ordered by the user's custom sort order, then by the time the image was added.
     */
    @Query("""
        SELECT i.* FROM images i
        JOIN collection_images ci ON i.imageId = ci.imageId
        WHERE ci.collectionId = :collectionId
        ORDER BY ci.sortOrder ASC, ci.addedAt DESC
    """)
    fun getImagesInCollection(collectionId: String): Flow<List<ImageEntity>>

    /**
     * Fetches the top 4 images for a collection to be used as UI thumbnails/previews.
     */
    @Query("""
        SELECT i.* FROM images i
        JOIN collection_images ci ON i.imageId = ci.imageId
        WHERE ci.collectionId = :collectionId
        ORDER BY ci.sortOrder ASC, ci.addedAt DESC
        LIMIT 4
    """)
    suspend fun getPreviewImages(collectionId: String): List<ImageEntity>

    /**
     * Returns the current number of images associated with a collection.
     */
    @Query("""
        SELECT COUNT(*) FROM collection_images
        WHERE collectionId = :collectionId
    """)
    suspend fun getPhotoCount(collectionId: String): Int

    /**
     * Checks if a specific image is already present in a collection.
     * Returns true if a record exists.
     */
    @Query("""
        SELECT EXISTS(
            SELECT 1 FROM collection_images
            WHERE collectionId = :collectionId AND imageId = :imageId
        )
    """)
    suspend fun containsImage(collectionId: String, imageId: String): Boolean

    // =========================================================================================
    // FILTER MANAGEMENT (For Smart/Dynamic Collections)
    // =========================================================================================

    /**
     * Inserts a filter criteria for a Smart Collection.
     */
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertFilter(filter: CollectionFilterEntity)

    /**
     * Batch inserts multiple filter criteria.
     */
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertFilters(filters: List<CollectionFilterEntity>)

    /**
     * Removes all dynamic filter rules for a collection.
     */
    @Query("DELETE FROM collection_filters WHERE collectionId = :collectionId")
    suspend fun clearFilters(collectionId: String)

    /**
     * Retrieves the list of rules used to populate a Smart Collection.
     */
    @Query("""
        SELECT * FROM collection_filters
        WHERE collectionId = :collectionId
        ORDER BY createdAt ASC
    """)
    suspend fun getFilters(collectionId: String): List<CollectionFilterEntity>

    /**
     * Observable stream of filters for a Smart Collection.
     */
    @Query("""
        SELECT * FROM collection_filters
        WHERE collectionId = :collectionId
        ORDER BY createdAt ASC
    """)
    fun getFiltersFlow(collectionId: String): Flow<List<CollectionFilterEntity>>

    // =========================================================================================
    // AGGREGATE STATISTICS
    // =========================================================================================

    /** Total number of collections defined. */
    @Query("SELECT COUNT(*) FROM collections")
    suspend fun getCollectionCount(): Int

    /** Count of collections that update dynamically based on filters. */
    @Query("SELECT COUNT(*) FROM collections WHERE type = 'SMART'")
    suspend fun getSmartCollectionCount(): Int

    /** Count of manually curated collections. */
    @Query("SELECT COUNT(*) FROM collections WHERE type = 'STATIC'")
    suspend fun getStaticCollectionCount(): Int

    /**
     * Returns the sum of the photoCount cache across all collections.
     * Returns nullable Int in case the table is empty.
     */
    @Query("""
        SELECT SUM(photoCount) FROM collections
    """)
    suspend fun getTotalPhotosInCollections(): Int?

    // =========================================================================================
    // GRANULAR UPDATES (Optimization)
    // =========================================================================================

    /**
     * Synchronizes the 'photoCount' denormalized field in the collections table with
     * the actual count in the junction table. Should be called after image additions/removals.
     * * @param updatedAt Timestamp of the operation.
     */
    @Query("""
        UPDATE collections
        SET photoCount = (
            SELECT COUNT(*) FROM collection_images
            WHERE collectionId = :collectionId
        ),
        updatedAt = :updatedAt
        WHERE collectionId = :collectionId
    """)
    suspend fun updatePhotoCount(collectionId: String, updatedAt: Long)

    /**
     * Updates the thumbnail/cover image for the collection card.
     */
    @Query("""
        UPDATE collections
        SET coverImageUri = :imageUri, updatedAt = :updatedAt
        WHERE collectionId = :collectionId
    """)
    suspend fun updateCoverImage(collectionId: String, imageUri: String?, updatedAt: Long)

    /**
     * Toggles the pinned status of a collection.
     */
    @Query("""
        UPDATE collections
        SET isPinned = :isPinned, updatedAt = :updatedAt
        WHERE collectionId = :collectionId
    """)
    suspend fun updatePinned(collectionId: String, isPinned: Boolean, updatedAt: Long)

    /**
     * Updates the name and description of a collection.
     */
    @Query("""
        UPDATE collections
        SET name = :name, description = :description, updatedAt = :updatedAt
        WHERE collectionId = :collectionId
    """)
    suspend fun updateDetails(
        collectionId: String,
        name: String,
        description: String?,
        updatedAt: Long
    )
}