package com.fieldlabel.collector.data.local

import androidx.room.*
import kotlinx.coroutines.flow.Flow

@Dao
interface CollectionRecordDao {

    @Query("SELECT * FROM collection_records ORDER BY createdAt DESC")
    fun getAllFlow(): Flow<List<CollectionRecordEntity>>

    /** 全レコードを一回取得する（インポート時の重複チェック等に使用） */
    @Query("SELECT * FROM collection_records ORDER BY createdAt DESC")
    suspend fun getAll(): List<CollectionRecordEntity>

    @Query("SELECT * FROM collection_records WHERE id = :id")
    suspend fun getById(id: String): CollectionRecordEntity?

    @Query("SELECT * FROM collection_records WHERE synced = 0")
    suspend fun getUnsynced(): List<CollectionRecordEntity>

    @Query("SELECT COUNT(*) FROM collection_records WHERE locationId = :locationId")
    suspend fun countByLocation(locationId: String): Int

    /** 年ごとの地点通し番号の最大値を返す（採番に使用） */
    @Query("""
        SELECT MAX(CAST(SUBSTR(locationId, 10) AS INTEGER))
        FROM collection_records
        WHERE SUBSTR(locationId, 5, 4) = :year
    """)
    suspend fun maxLocationSeqForYear(year: String): Int?

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(entity: CollectionRecordEntity)

    @Update
    suspend fun update(entity: CollectionRecordEntity)

    @Query("UPDATE collection_records SET synced = 1 WHERE id = :id")
    suspend fun markSynced(id: String)

    @Delete
    suspend fun delete(entity: CollectionRecordEntity)

    @Query("SELECT * FROM collection_records WHERE locationId = :locationId ORDER BY specimenNumber ASC")
    suspend fun getByLocationId(locationId: String): List<CollectionRecordEntity>

    @Query("SELECT * FROM collection_records ORDER BY createdAt DESC LIMIT :limit")
    fun getRecentFlow(limit: Int): Flow<List<CollectionRecordEntity>>

    /** 指定地点の全標本を削除する */
    @Query("DELETE FROM collection_records WHERE locationId = :locationId")
    suspend fun deleteByLocationId(locationId: String)

    /** 指定地点の全標本の採集者名を一括更新する */
    @Query("UPDATE collection_records SET collectorName = :collectorName, synced = 0 WHERE locationId = :locationId")
    suspend fun updateCollectorByLocationId(locationId: String, collectorName: String)
}
