package com.rugvedsarika.docscan.data.dao

import androidx.room.*
import com.rugvedsarika.docscan.data.entity.Document
import com.rugvedsarika.docscan.data.entity.Page
import kotlinx.coroutines.flow.Flow

@Dao
interface DocumentDao {
    @Query("SELECT * FROM documents ORDER BY createdDate DESC")
    fun getAllDocuments(): Flow<List<Document>>

    @Query("""
        SELECT DISTINCT d.* FROM documents d 
        LEFT JOIN pages p ON d.id = p.documentId 
        WHERE d.name LIKE :query OR p.extractedText LIKE :query 
        ORDER BY d.createdDate DESC
    """)
    fun searchDocuments(query: String): Flow<List<Document>>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertDocument(document: Document): Long

    @Update
    suspend fun updateDocument(document: Document)

    @Delete
    suspend fun deleteDocument(document: Document)

    @Query("SELECT * FROM documents WHERE id = :documentId")
    suspend fun getDocumentById(documentId: Long): Document?

    // Page operations
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertPage(page: Page): Long

    @Query("SELECT * FROM pages WHERE documentId = :documentId ORDER BY orderIndex ASC")
    fun getPagesForDocument(documentId: Long): Flow<List<Page>>

    @Query("SELECT * FROM pages WHERE documentId = :documentId ORDER BY orderIndex ASC")
    suspend fun getPagesListForDocument(documentId: Long): List<Page>

    @Delete
    suspend fun deletePage(page: Page)

    @Query("DELETE FROM pages WHERE documentId = :documentId")
    suspend fun deletePagesForDocument(documentId: Long)
    
    @Transaction
    suspend fun deleteDocumentWithPages(document: Document) {
        deletePagesForDocument(document.id)
        deleteDocument(document)
    }
}
