package com.fieldlabel.collector.data.local

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

@Dao
interface CustomFieldValueDao {

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

    @Delete
    suspend fun delete(entity: CustomFieldValueEntity)

    /** 地点IDに紐づく全フィールド値を返す（Flow） */
    @Query("SELECT * FROM custom_field_values WHERE locationId = :locationId")
    fun getByLocationIdFlow(locationId: String): Flow<List<CustomFieldValueEntity>>

    /** 地点IDに紐づく全フィールド値を一回取得 */
    @Query("SELECT * FROM custom_field_values WHERE locationId = :locationId")
    suspend fun getByLocationId(locationId: String): List<CustomFieldValueEntity>

    /** 特定標本IDに紐づく全フィールド値を返す（Flow） */
    @Query("SELECT * FROM custom_field_values WHERE specimenId = :specimenId")
    fun getBySpecimenIdFlow(specimenId: String): Flow<List<CustomFieldValueEntity>>

    /** specimenId + fieldDefId でユニークな値を upsert する */
    @Query("""
        INSERT OR REPLACE INTO custom_field_values (id, fieldDefId, locationId, specimenId, value)
        VALUES (
            COALESCE((SELECT id FROM custom_field_values WHERE fieldDefId = :fieldDefId AND specimenId = :specimenId AND locationId = :locationId), :newId),
            :fieldDefId, :locationId, :specimenId, :value
        )
    """)
    suspend fun upsertSpecimenValue(
        newId: String,
        fieldDefId: String,
        locationId: String,
        specimenId: String,
        value: String
    )

    /** locationId + fieldDefId で地点レベルの値を upsert する */
    @Query("""
        INSERT OR REPLACE INTO custom_field_values (id, fieldDefId, locationId, specimenId, value)
        VALUES (
            COALESCE((SELECT id FROM custom_field_values WHERE fieldDefId = :fieldDefId AND specimenId IS NULL AND locationId = :locationId), :newId),
            :fieldDefId, :locationId, NULL, :value
        )
    """)
    suspend fun upsertLocationValue(
        newId: String,
        fieldDefId: String,
        locationId: String,
        value: String
    )

    /** 地点の全フィールド値を削除 */
    @Query("DELETE FROM custom_field_values WHERE locationId = :locationId")
    suspend fun deleteByLocationId(locationId: String)

    /** フィールド定義が削除されたときに紐づく値も削除 */
    @Query("DELETE FROM custom_field_values WHERE fieldDefId = :fieldDefId")
    suspend fun deleteByFieldDefId(fieldDefId: String)

    /** エクスポート用: 全値を一括取得 */
    @Query("SELECT * FROM custom_field_values")
    suspend fun getAll(): List<CustomFieldValueEntity>
}
