package com.example.data.local

import androidx.room.*
import com.example.data.model.*
import kotlinx.coroutines.flow.Flow

@Dao
interface CompanyDao {
    @Query("SELECT * FROM companies ORDER BY riskScore ASC")
    fun getAllCompanies(): Flow<List<Company>>

    @Query("SELECT * FROM companies WHERE status = :status ORDER BY nameEn ASC")
    fun getCompaniesByStatus(status: VerificationStatus): Flow<List<Company>>

    @Query("SELECT * FROM companies WHERE id = :id")
    suspend fun getCompanyById(id: String): Company?

    @Query("""
        SELECT * FROM companies 
        WHERE nameEn LIKE '%' || :query || '%' 
           OR nameBn LIKE '%' || :query || '%' 
           OR rjscRegNo LIKE '%' || :query || '%'
           OR tradeLicenseNo LIKE '%' || :query || '%'
           OR phone LIKE '%' || :query || '%'
           OR bkashMerchantNo LIKE '%' || :query || '%'
    """)
    fun searchCompanies(query: String): Flow<List<Company>>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertCompanies(companies: List<Company>)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertCompany(company: Company)

    @Update
    suspend fun updateCompany(company: Company)
}

@Dao
interface ScamReportDao {
    @Query("SELECT * FROM scam_reports ORDER BY dateReported DESC")
    fun getAllReports(): Flow<List<ScamReport>>

    @Query("SELECT * FROM scam_reports WHERE status = :status ORDER BY dateReported DESC")
    fun getReportsByStatus(status: ReportStatus): Flow<List<ScamReport>>

    @Query("SELECT * FROM scam_reports WHERE id = :id")
    suspend fun getReportById(id: String): ScamReport?

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertReport(report: ScamReport)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertReports(reports: List<ScamReport>)

    @Update
    suspend fun updateReport(report: ScamReport)
}

@Dao
interface PublicWarningDao {
    @Query("SELECT * FROM public_warnings ORDER BY isPinned DESC, date DESC")
    fun getAllWarnings(): Flow<List<PublicWarning>>

    @Query("SELECT * FROM public_warnings WHERE division = :division ORDER BY date DESC")
    fun getWarningsByDivision(division: String): Flow<List<PublicWarning>>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertWarnings(warnings: List<PublicWarning>)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertWarning(warning: PublicWarning)
}

@Dao
interface NotificationDao {
    @Query("SELECT * FROM notifications ORDER BY timestamp DESC")
    fun getAllNotifications(): Flow<List<AppNotification>>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertNotifications(notifications: List<AppNotification>)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertNotification(notification: AppNotification)

    @Query("UPDATE notifications SET isRead = 1")
    suspend fun markAllAsRead()
}