package com.example.data.local.dao

import androidx.room.Dao
import androidx.room.Delete
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import androidx.room.Update
import com.example.data.local.entity.AuditLogEntity
import com.example.data.local.entity.CampaignEntity
import com.example.data.local.entity.CampaignRecipientEntity
import com.example.data.local.entity.ConsumerAccountEntity
import com.example.data.local.entity.ContactCategoryEntity
import com.example.data.local.entity.ContactEntity
import com.example.data.local.entity.ConversationMessageEntity
import com.example.data.local.entity.LeadEntity
import com.example.data.local.entity.PartnerEntity
import com.example.data.local.entity.ProductEntity
import com.example.data.local.entity.SubscriptionPlanEntity
import com.example.data.local.entity.WhatsAppTemplateEntity
import kotlinx.coroutines.flow.Flow

@Dao
interface ConsumerAccountDao {
    @Query("SELECT * FROM consumer_accounts WHERE id = :id LIMIT 1")
    fun getAccount(id: String = "primary_account"): Flow<ConsumerAccountEntity?>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertOrUpdate(account: ConsumerAccountEntity)

    @Query("UPDATE consumer_accounts SET quotaUsed = quotaUsed + :count WHERE id = :id")
    suspend fun incrementQuotaUsed(count: Int, id: String = "primary_account")
}

@Dao
interface ContactCategoryDao {
    @Query("SELECT * FROM contact_categories ORDER BY id ASC")
    fun getAllCategories(): Flow<List<ContactCategoryEntity>>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(category: ContactCategoryEntity): Long

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

    @Update
    suspend fun update(category: ContactCategoryEntity)

    @Delete
    suspend fun delete(category: ContactCategoryEntity)
}

@Dao
interface ContactDao {
    @Query("SELECT * FROM contacts ORDER BY id DESC")
    fun getAllContacts(): Flow<List<ContactEntity>>

    @Query("SELECT * FROM contacts WHERE isConsumerUpload = 1 ORDER BY id DESC")
    fun getConsumerContacts(): Flow<List<ContactEntity>>

    @Query("SELECT * FROM contacts WHERE isConsumerUpload = 1 AND optInStatus = 'OPTED_IN' AND isApprovedByAdmin = 1 ORDER BY id DESC")
    fun getEligibleConsumerContacts(): Flow<List<ContactEntity>>

    @Query("SELECT * FROM contacts WHERE optInStatus = 'OPTED_IN' AND isApprovedByAdmin = 1 ORDER BY id DESC")
    fun getEligibleOptedInContacts(): Flow<List<ContactEntity>>

    @Query("SELECT * FROM contacts WHERE categoryId = :categoryId ORDER BY id DESC")
    fun getContactsByCategory(categoryId: Long): Flow<List<ContactEntity>>

    @Query("SELECT * FROM contacts WHERE optInStatus = 'OPTED_OUT'")
    fun getOptedOutContacts(): Flow<List<ContactEntity>>

    @Query("SELECT * FROM contacts WHERE isApprovedByAdmin = 0")
    fun getPendingApprovalContacts(): Flow<List<ContactEntity>>

    @Query("SELECT COUNT(*) FROM contacts")
    fun getTotalContactCount(): Flow<Int>

    @Query("SELECT COUNT(*) FROM contacts WHERE optInStatus = 'OPTED_IN' AND isApprovedByAdmin = 1")
    fun getEligibleContactCount(): Flow<Int>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(contact: ContactEntity): Long

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

    @Update
    suspend fun update(contact: ContactEntity)

    @Delete
    suspend fun delete(contact: ContactEntity)

    @Query("UPDATE contacts SET isApprovedByAdmin = 1 WHERE id = :contactId")
    suspend fun approveContact(contactId: Long)

    @Query("UPDATE contacts SET isApprovedByAdmin = 1")
    suspend fun approveAllPending()

    @Query("UPDATE contacts SET optInStatus = 'OPTED_OUT' WHERE e164Phone = :phone")
    suspend fun suppressContact(phone: String)

    @Query("UPDATE contacts SET optInStatus = :status WHERE id = :contactId")
    suspend fun updateOptInStatus(contactId: Long, status: String)

    @Query("DELETE FROM contacts WHERE id = :contactId")
    suspend fun deleteContactById(contactId: Long)

    @Query("SELECT * FROM contacts WHERE e164Phone = :phone LIMIT 1")
    suspend fun findByPhone(phone: String): ContactEntity?
}

@Dao
interface WhatsAppTemplateDao {
    @Query("SELECT * FROM whatsapp_templates ORDER BY templateName ASC")
    fun getAllTemplates(): Flow<List<WhatsAppTemplateEntity>>

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

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(template: WhatsAppTemplateEntity)
}

@Dao
interface CampaignDao {
    @Query("SELECT * FROM campaigns ORDER BY id DESC")
    fun getAllCampaigns(): Flow<List<CampaignEntity>>

    @Query("SELECT * FROM campaigns WHERE id = :id LIMIT 1")
    fun getCampaignById(id: Long): Flow<CampaignEntity?>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(campaign: CampaignEntity): Long

    @Update
    suspend fun update(campaign: CampaignEntity)

    @Query("UPDATE campaigns SET status = :status WHERE id = :id")
    suspend fun updateStatus(id: Long, status: String)

    @Query("UPDATE campaigns SET sentCount = :sent, deliveredCount = :delivered, readCount = :read, failedCount = :failed, status = :status WHERE id = :id")
    suspend fun updateProgress(id: Long, sent: Int, delivered: Int, read: Int, failed: Int, status: String)
}

@Dao
interface CampaignRecipientDao {
    @Query("SELECT * FROM campaign_recipients WHERE campaignId = :campaignId ORDER BY id ASC")
    fun getRecipientsForCampaign(campaignId: Long): Flow<List<CampaignRecipientEntity>>

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

    @Query("UPDATE campaign_recipients SET status = :status, deliveredAt = :deliveredAt, readAt = :readAt, errorMessage = :errorMsg WHERE id = :recipientId")
    suspend fun updateRecipientStatus(recipientId: Long, status: String, deliveredAt: Long, readAt: Long, errorMsg: String)
}

@Dao
interface ProductDao {
    @Query("SELECT * FROM products ORDER BY id DESC")
    fun getAllProducts(): Flow<List<ProductEntity>>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(product: ProductEntity): Long

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

    @Update
    suspend fun update(product: ProductEntity)

    @Delete
    suspend fun delete(product: ProductEntity)
}

@Dao
interface LeadDao {
    @Query("SELECT * FROM leads ORDER BY id DESC")
    fun getAllLeads(): Flow<List<LeadEntity>>

    @Query("SELECT * FROM leads WHERE status = :status ORDER BY id DESC")
    fun getLeadsByStatus(status: String): Flow<List<LeadEntity>>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(lead: LeadEntity): Long

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

    @Update
    suspend fun update(lead: LeadEntity)

    @Query("UPDATE leads SET status = :newStatus WHERE id = :leadId")
    suspend fun updateLeadStatus(leadId: Long, newStatus: String)

    @Delete
    suspend fun delete(lead: LeadEntity)
}

@Dao
interface ConversationDao {
    @Query("SELECT * FROM conversation_messages ORDER BY timestamp ASC")
    fun getAllMessages(): Flow<List<ConversationMessageEntity>>

    @Query("SELECT DISTINCT phone FROM conversation_messages")
    fun getUniqueChatPhones(): Flow<List<String>>

    @Query("SELECT * FROM conversation_messages WHERE phone = :phone ORDER BY timestamp ASC")
    fun getMessagesForPhone(phone: String): Flow<List<ConversationMessageEntity>>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(message: ConversationMessageEntity): Long

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertAll(messages: List<ConversationMessageEntity>)
}

@Dao
interface AuditLogDao {
    @Query("SELECT * FROM audit_logs ORDER BY timestamp DESC")
    fun getAllLogs(): Flow<List<AuditLogEntity>>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(log: AuditLogEntity): Long
}

@Dao
interface SubscriptionPlanDao {
    @Query("SELECT * FROM subscription_plans")
    fun getAllPlans(): Flow<List<SubscriptionPlanEntity>>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertAll(plans: List<SubscriptionPlanEntity>)
}

@Dao
interface PartnerDao {
    @Query("SELECT * FROM partners ORDER BY id DESC")
    fun getAllPartners(): Flow<List<PartnerEntity>>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(partner: PartnerEntity): Long

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

    @Update
    suspend fun update(partner: PartnerEntity)

    @Delete
    suspend fun delete(partner: PartnerEntity)

    @Query("UPDATE partners SET isActive = :isActive WHERE id = :id")
    suspend fun toggleActive(id: Long, isActive: Boolean)
}