package com.rosetta.messenger.database

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

// ═══════════════════════════════════════════════════════════
// 📌 PINNED MESSAGES
// ═══════════════════════════════════════════════════════════

/** Entity для закреплённых сообщений в чате (Telegram-style pinned messages) */
@Entity(
        tableName = "pinned_messages",
        indices =
                [
                        Index(
                                value = ["account", "dialog_key", "message_id"],
                                unique = true
                        ),
                        Index(value = ["account", "dialog_key", "pinned_at"])]
)
data class PinnedMessageEntity(
        @PrimaryKey(autoGenerate = true) val id: Long = 0,
        @ColumnInfo(name = "account") val account: String,
        @ColumnInfo(name = "dialog_key") val dialogKey: String,
        @ColumnInfo(name = "message_id") val messageId: String,
        @ColumnInfo(name = "pinned_at") val pinnedAt: Long = System.currentTimeMillis()
)

/** DAO для работы с закреплёнными сообщениями */
@Dao
interface PinnedMessageDao {

    /** Закрепить сообщение (IGNORE если уже закреплено) */
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun insertPin(pin: PinnedMessageEntity): Long

    /** Открепить конкретное сообщение */
    @Query(
            "DELETE FROM pinned_messages WHERE account = :account AND dialog_key = :dialogKey AND message_id = :messageId"
    )
    suspend fun removePin(account: String, dialogKey: String, messageId: String)

    /** Получить все закреплённые сообщения диалога (Flow для реактивных обновлений) */
    @Query(
            """
        SELECT * FROM pinned_messages 
        WHERE account = :account AND dialog_key = :dialogKey
        ORDER BY pinned_at DESC
    """
    )
    fun getPinnedMessages(account: String, dialogKey: String): Flow<List<PinnedMessageEntity>>

    /** Проверить, закреплено ли сообщение */
    @Query(
            "SELECT EXISTS(SELECT 1 FROM pinned_messages WHERE account = :account AND dialog_key = :dialogKey AND message_id = :messageId)"
    )
    suspend fun isPinned(account: String, dialogKey: String, messageId: String): Boolean

    /** Открепить все сообщения диалога */
    @Query("DELETE FROM pinned_messages WHERE account = :account AND dialog_key = :dialogKey")
    suspend fun unpinAll(account: String, dialogKey: String): Int

    /** Количество закреплённых сообщений в диалоге */
    @Query(
            "SELECT COUNT(*) FROM pinned_messages WHERE account = :account AND dialog_key = :dialogKey"
    )
    suspend fun getPinnedCount(account: String, dialogKey: String): Int
}

/** 🔥 Data class для статуса последнего сообщения */
data class LastMessageStatus(
        @ColumnInfo(name = "from_me") val fromMe: Int,
        @ColumnInfo(name = "delivered") val delivered: Int,
        @ColumnInfo(name = "read") val read: Int
)

/** Entity для сообщений - как в React Native версии */
@Entity(
        tableName = "messages",
        indices =
                [
                        Index(value = ["account", "from_public_key", "to_public_key", "timestamp"]),
                        Index(value = ["account", "message_id"], unique = true),
                        Index(value = ["account", "dialog_key", "timestamp"]),
                        Index(value = ["account", "timestamp"]),
                        Index(value = ["account", "primary_attachment_type", "timestamp"])]
)
data class MessageEntity(
        @PrimaryKey(autoGenerate = true) val id: Long = 0,
        @ColumnInfo(name = "account") val account: String, // Мой публичный ключ
        @ColumnInfo(name = "from_public_key") val fromPublicKey: String, // Отправитель
        @ColumnInfo(name = "to_public_key") val toPublicKey: String, // Получатель
        @ColumnInfo(name = "content") val content: String, // Зашифрованное содержимое
        @ColumnInfo(name = "timestamp") val timestamp: Long, // Unix timestamp
        @ColumnInfo(name = "chacha_key") val chachaKey: String, // Зашифрованный ключ
        @ColumnInfo(name = "read") val read: Int = 0, // Прочитано (0/1)
        @ColumnInfo(name = "from_me") val fromMe: Int = 0, // Мое сообщение (0/1)
        @ColumnInfo(name = "delivered")
        val delivered: Int = 0, // Статус доставки (0=WAITING, 1=DELIVERED, 2=ERROR)
        @ColumnInfo(name = "message_id") val messageId: String, // UUID сообщения
        @ColumnInfo(name = "plain_message")
        val plainMessage: String, // 🔒 Зашифрованный текст (encryptWithPassword) для хранения в БД
        @ColumnInfo(name = "attachments") val attachments: String = "[]", // JSON массив вложений
        @ColumnInfo(name = "primary_attachment_type", defaultValue = "-1")
        val primaryAttachmentType: Int = -1, // Денормализованный тип 1-го вложения (-1 если нет)
        @ColumnInfo(name = "reply_to_message_id")
        val replyToMessageId: String? = null, // ID цитируемого сообщения
        @ColumnInfo(name = "dialog_key") val dialogKey: String // Ключ диалога для быстрой выборки
)

/** Локальный денормализованный индекс для поиска по сообщениям без повторной дешифровки. */
@Entity(
        tableName = "message_search_index",
        primaryKeys = ["account", "message_id"],
        indices =
                [
                        Index(value = ["account", "timestamp"]),
                        Index(value = ["account", "opponent_key", "timestamp"])]
)
data class MessageSearchIndexEntity(
        @ColumnInfo(name = "account") val account: String,
        @ColumnInfo(name = "message_id") val messageId: String,
        @ColumnInfo(name = "dialog_key") val dialogKey: String,
        @ColumnInfo(name = "opponent_key") val opponentKey: String,
        @ColumnInfo(name = "timestamp") val timestamp: Long,
        @ColumnInfo(name = "from_me") val fromMe: Int = 0,
        @ColumnInfo(name = "plain_text") val plainText: String,
        @ColumnInfo(name = "plain_text_normalized") val plainTextNormalized: String
)

/** Entity для диалогов (кэш последнего сообщения) */
@Entity(
        tableName = "dialogs",
        indices =
                [
                        Index(value = ["account", "opponent_key"], unique = true),
                        Index(value = ["account", "last_message_timestamp"]),
                        Index(
                                value =
                                        [
                                                "account",
                                                "i_have_sent",
                                                "has_content",
                                                "last_message_timestamp"])]
)
data class DialogEntity(
        @PrimaryKey(autoGenerate = true) val id: Long = 0,
        @ColumnInfo(name = "account") val account: String, // Мой публичный ключ
        @ColumnInfo(name = "opponent_key") val opponentKey: String, // Публичный ключ собеседника
        @ColumnInfo(name = "opponent_title") val opponentTitle: String = "", // Имя собеседника
        @ColumnInfo(name = "opponent_username")
        val opponentUsername: String = "", // Username собеседника
        @ColumnInfo(name = "last_message")
        val lastMessage: String = "", // 🔒 Последнее сообщение (зашифрованное для превью)
        @ColumnInfo(name = "last_message_timestamp")
        val lastMessageTimestamp: Long = 0, // Timestamp последнего сообщения
        @ColumnInfo(name = "unread_count") val unreadCount: Int = 0, // Количество непрочитанных
        @ColumnInfo(name = "is_online") val isOnline: Int = 0, // Онлайн статус
        @ColumnInfo(name = "last_seen") val lastSeen: Long = 0, // Последний раз онлайн
        @ColumnInfo(name = "verified") val verified: Int = 0, // Верифицирован
        @ColumnInfo(name = "i_have_sent", defaultValue = "0")
        val iHaveSent: Int = 0, // Отправлял ли я сообщения в этот диалог (0/1)
        @ColumnInfo(name = "has_content", defaultValue = "0")
        val hasContent: Int = 0, // Есть ли контент в диалоге (0/1)
        @ColumnInfo(name = "last_message_attachment_type", defaultValue = "-1")
        val lastMessageAttachmentType: Int = -1, // Денормализованный тип вложения последнего сообщения
        @ColumnInfo(name = "last_sender_key", defaultValue = "''")
        val lastSenderKey: String = "", // Для групп: публичный ключ последнего отправителя
        @ColumnInfo(name = "last_message_from_me", defaultValue = "0")
        val lastMessageFromMe: Int = 0, // Последнее сообщение от меня (0/1)
        @ColumnInfo(name = "last_message_delivered", defaultValue = "0")
        val lastMessageDelivered: Int =
                0, // Статус доставки последнего сообщения (0=WAITING, 1=DELIVERED, 2=ERROR)
        @ColumnInfo(name = "last_message_read", defaultValue = "0")
        val lastMessageRead: Int = 0, // Прочитано последнее сообщение (0/1)
        @ColumnInfo(name = "last_message_attachments", defaultValue = "[]")
        val lastMessageAttachments: String =
                "[]" // 📎 JSON attachments последнего сообщения (кэш из messages)
)

/** Entity для групповых диалогов */
@Entity(
        tableName = "groups",
        indices =
                [
                        Index(value = ["account", "group_id"], unique = true)]
)
data class GroupEntity(
        @PrimaryKey(autoGenerate = true) val id: Long = 0,
        @ColumnInfo(name = "account") val account: String,
        @ColumnInfo(name = "group_id") val groupId: String,
        @ColumnInfo(name = "title") val title: String,
        @ColumnInfo(name = "description") val description: String = "",
        @ColumnInfo(name = "key") val key: String
)

@Dao
interface GroupDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertGroup(group: GroupEntity): Long

    @Query(
            "SELECT * FROM groups WHERE account = :account AND group_id = :groupId LIMIT 1"
    )
    suspend fun getGroup(account: String, groupId: String): GroupEntity?

    @Query("DELETE FROM groups WHERE account = :account AND group_id = :groupId")
    suspend fun deleteGroup(account: String, groupId: String): Int

    @Query("DELETE FROM groups WHERE account = :account")
    suspend fun deleteAllByAccount(account: String): Int
}

/** Строка истории звонков (messages + данные собеседника из dialogs) */
data class CallHistoryRow(
        @Embedded val message: MessageEntity,
        @ColumnInfo(name = "peer_key") val peerKey: String,
        @ColumnInfo(name = "peer_title") val peerTitle: String?,
        @ColumnInfo(name = "peer_username") val peerUsername: String?,
        @ColumnInfo(name = "peer_verified") val peerVerified: Int?,
        @ColumnInfo(name = "peer_online") val peerOnline: Int?
)

/** DAO для работы с сообщениями */
@Dao
interface MessageDao {

    /** Вставка нового сообщения (IGNORE если уже существует) */
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun insertMessage(message: MessageEntity): Long

    /** Вставка нескольких сообщений (IGNORE если уже существуют) */
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun insertMessages(messages: List<MessageEntity>)

    /** Получить сообщения диалога (постранично) */
    @Query(
            """
        SELECT * FROM messages 
        WHERE account = :account AND dialog_key = :dialogKey
        ORDER BY timestamp DESC, id DESC
        LIMIT :limit OFFSET :offset
    """
    )
    suspend fun getMessages(
            account: String,
            dialogKey: String,
            limit: Int,
            offset: Int
    ): List<MessageEntity>

    /**
     * 📁 Получить сообщения для Saved Messages (постранично) Специальный метод для случая когда
     * from_public_key = to_public_key = account Использует упрощенный запрос без дублирования OR
     * условий
     */
    @Query(
            """
        SELECT * FROM messages 
        WHERE account = :account 
          AND from_public_key = :account 
          AND to_public_key = :account
        ORDER BY timestamp DESC, id DESC
        LIMIT :limit OFFSET :offset
    """
    )
    suspend fun getMessagesForSavedDialog(
            account: String,
            limit: Int,
            offset: Int
    ): List<MessageEntity>

    /** 📁 Получить количество сообщений в Saved Messages */
    @Query(
            """
        SELECT COUNT(*) FROM messages 
        WHERE account = :account 
          AND from_public_key = :account 
          AND to_public_key = :account
    """
    )
    suspend fun getMessageCountForSavedDialog(account: String): Int

    /** Получить сообщения диалога как Flow */
    @Query(
            """
        SELECT * FROM messages 
        WHERE account = :account AND dialog_key = :dialogKey
        ORDER BY timestamp ASC, id ASC
    """
    )
    fun getMessagesFlow(account: String, dialogKey: String): Flow<List<MessageEntity>>

    /** Получить количество сообщений в диалоге */
    @Query(
            """
        SELECT COUNT(*) FROM messages 
        WHERE account = :account AND dialog_key = :dialogKey
    """
    )
    suspend fun getMessageCount(account: String, dialogKey: String): Int

    /**
     * 📸 Получить количество сообщений между двумя пользователями (для проверки первого сообщения
     * при отправке аватара)
     */
    @Query(
            """
        SELECT COUNT(*) FROM messages 
        WHERE account = :account 
          AND ((from_public_key = :sender AND to_public_key = :recipient)
               OR (from_public_key = :recipient AND to_public_key = :sender))
    """
    )
    suspend fun getMessageCount(account: String, sender: String, recipient: String): Int

    /** Получить последние N сообщений диалога */
    @Query(
            """
        SELECT * FROM messages 
        WHERE account = :account AND dialog_key = :dialogKey
        ORDER BY timestamp DESC, id DESC
        LIMIT :limit
    """
    )
    suspend fun getRecentMessages(
            account: String,
            dialogKey: String,
            limit: Int
    ): List<MessageEntity>

    /** Найти сообщение по ID */
    @Query("SELECT * FROM messages WHERE account = :account AND message_id = :messageId LIMIT 1")
    suspend fun getMessageById(account: String, messageId: String): MessageEntity?

    /** Обновить статус доставки */
    @Query(
            "UPDATE messages SET delivered = :status WHERE account = :account AND message_id = :messageId"
    )
    suspend fun updateDeliveryStatus(account: String, messageId: String, status: Int)

    /** 🔄 Обновить статус доставки и attachments (для очистки localUri после отправки) */
    @Query(
            "UPDATE messages SET delivered = :status, attachments = :attachments WHERE account = :account AND message_id = :messageId"
    )
    suspend fun updateDeliveryStatusAndAttachments(
            account: String,
            messageId: String,
            status: Int,
            attachments: String
    )

    /** Обновить статус прочтения */
    @Query("UPDATE messages SET read = 1 WHERE account = :account AND message_id = :messageId")
    suspend fun markAsRead(account: String, messageId: String)

    /** Отметить все сообщения диалога как прочитанные */
    @Query(
            """
        UPDATE messages SET read = 1 
        WHERE account = :account AND dialog_key = :dialogKey AND from_me = 0
    """
    )
    suspend fun markDialogAsRead(account: String, dialogKey: String)

    /** Удалить сообщение */
    @Query("DELETE FROM messages WHERE account = :account AND message_id = :messageId")
    suspend fun deleteMessage(account: String, messageId: String)

    /**
     * Найти сообщение по публичному ключу отправителя и timestamp (для reply) Ищет с допуском по
     * времени для учета возможных рассинхронизаций
     */
    @Query(
            """
        SELECT * FROM messages 
        WHERE account = :account 
          AND dialog_key = :dialogKey
          AND from_public_key = :fromPublicKey
          AND timestamp BETWEEN :timestampFrom AND :timestampTo
        ORDER BY timestamp ASC, id ASC
        LIMIT 1
    """
    )
    suspend fun findMessageByContent(
            account: String,
            dialogKey: String,
            fromPublicKey: String,
            timestampFrom: Long,
            timestampTo: Long
    ): MessageEntity?

    /**
     * Получить количество непрочитанных сообщений для диалога Считает только входящие сообщения
     * (from_me = 0) которые не прочитаны (read = 0)
     */
    @Query(
            """
        SELECT COUNT(*) FROM messages 
        WHERE account = :account 
          AND from_public_key = :opponentKey 
          AND from_me = 0 
          AND read = 0
    """
    )
    suspend fun getUnreadCountForDialog(account: String, opponentKey: String): Int

    /** Удалить все сообщения диалога (возвращает количество удалённых) */
    @Query("DELETE FROM messages WHERE account = :account AND dialog_key = :dialogKey")
    suspend fun deleteDialog(account: String, dialogKey: String): Int

    /** Удалить все сообщения между двумя пользователями (возвращает количество удалённых) */
    @Query(
            """
        DELETE FROM messages 
        WHERE account = :account AND (
            (from_public_key = :user1 AND to_public_key = :user2) OR 
            (from_public_key = :user2 AND to_public_key = :user1)
        )
    """
    )
    suspend fun deleteMessagesBetweenUsers(account: String, user1: String, user2: String): Int

    /** Удалить все сообщения аккаунта */
    @Query("DELETE FROM messages WHERE account = :account")
    suspend fun deleteAllByAccount(account: String): Int

    /** Количество непрочитанных сообщений в диалоге */
    @Query(
            """
        SELECT COUNT(*) FROM messages 
        WHERE account = :account AND dialog_key = :dialogKey AND from_me = 0 AND read = 0
    """
    )
    suspend fun getUnreadCount(account: String, dialogKey: String): Int

    /** Проверить существование сообщения */
    @Query(
            "SELECT EXISTS(SELECT 1 FROM messages WHERE account = :account AND message_id = :messageId)"
    )
    suspend fun messageExists(account: String, messageId: String): Boolean

    /** Найти сообщение по ID */
    @Query("SELECT * FROM messages WHERE account = :account AND message_id = :messageId LIMIT 1")
    suspend fun findMessageById(account: String, messageId: String): MessageEntity?

    /**
     * Отметить все исходящие сообщения к собеседнику как прочитанные Используется когда приходит
     * PacketRead от собеседника.
     */
    @Query(
            """
        UPDATE messages SET read = 1 
        WHERE account = :account
          AND to_public_key = :opponent
          AND from_me = 1
          AND read != 1
    """
    )
    suspend fun markAllAsRead(account: String, opponent: String): Int

    /** 🔥 DEBUG: Получить последнее сообщение в диалоге для отладки */
    @Query(
            """
        SELECT * FROM messages 
        WHERE account = :account 
          AND ((from_public_key = :opponent AND to_public_key = :account) 
            OR (from_public_key = :account AND to_public_key = :opponent))
        ORDER BY timestamp DESC, id DESC LIMIT 1
    """
    )
    suspend fun getLastMessageDebug(account: String, opponent: String): MessageEntity?

    /**
     * 🔥 Получить статус последнего сообщения (fromMe, delivered, read) для диалога Возвращает null
     * если сообщений нет
     */
    @Query(
            """
        SELECT from_me, delivered, read FROM messages 
        WHERE account = :account 
          AND ((from_public_key = :opponent AND to_public_key = :account) 
            OR (from_public_key = :account AND to_public_key = :opponent))
        ORDER BY timestamp DESC, id DESC LIMIT 1
    """
    )
    suspend fun getLastMessageStatus(account: String, opponent: String): LastMessageStatus?

    /**
     * 📎 Получить attachments последнего сообщения для диалога Возвращает null если сообщений нет
     * или нет attachments
     */
    @Query(
            """
        SELECT attachments FROM messages 
        WHERE account = :account 
          AND ((from_public_key = :opponent AND to_public_key = :account) 
            OR (from_public_key = :account AND to_public_key = :opponent))
        ORDER BY timestamp DESC, id DESC LIMIT 1
    """
    )
    suspend fun getLastMessageAttachments(account: String, opponent: String): String?

    /**
     * Get all outgoing messages stuck in WAITING status (delivered = 0).
     * Used to retry sending on reconnect (desktop parity: _packetQueue flush).
     * Only returns messages younger than minTimestamp to avoid retrying stale messages.
     */
    @Query(
            """
        SELECT * FROM messages 
        WHERE account = :account 
          AND from_me = 1 
          AND delivered = 0 
          AND (attachments IS NULL OR TRIM(attachments) = '' OR TRIM(attachments) = '[]')
          AND timestamp >= :minTimestamp
        ORDER BY timestamp ASC
    """
    )
    suspend fun getWaitingMessages(account: String, minTimestamp: Long): List<MessageEntity>

    /**
     * Mark old WAITING messages as ERROR (delivery timeout expired).
     * Desktop parity: MESSAGE_MAX_TIME_TO_DELEVERED_S = 80s.
     */
    @Query(
            """
        UPDATE messages SET delivered = 2 
        WHERE account = :account 
          AND from_me = 1 
          AND delivered = 0 
          AND (attachments IS NULL OR TRIM(attachments) = '' OR TRIM(attachments) = '[]')
          AND timestamp < :maxTimestamp
    """
    )
    suspend fun markExpiredWaitingAsError(account: String, maxTimestamp: Long): Int

    /**
     * Desktop parity recovery:
     * own direct messages synced from another device are stored with chacha_key "sync:*"
     * and should always be DELIVERED (never WAITING/ERROR).
     */
    @Query(
            """
        UPDATE messages 
        SET delivered = 1
        WHERE account = :account
          AND from_me = 1
          AND delivered != 1
          AND chacha_key LIKE 'sync:%'
    """
    )
    suspend fun markSyncedOwnMessagesAsDelivered(account: String): Int

    @Query(
            """
        SELECT DISTINCT to_public_key
        FROM messages
        WHERE account = :account
          AND from_me = 1
          AND delivered != 1
          AND chacha_key LIKE 'sync:%'
    """
    )
    suspend fun getSyncedOwnMessageOpponentsWithNonDeliveredStatus(account: String): List<String>

    /**
     * Update delivery status AND timestamp on delivery confirmation.
     * Desktop parity: useDialogFiber.ts sets timestamp = Date.now() on PacketDelivery.
     */
    @Query(
            """
        UPDATE messages SET delivered = :status, timestamp = :timestamp 
        WHERE account = :account AND message_id = :messageId
    """
    )
    suspend fun updateDeliveryStatusAndTimestamp(account: String, messageId: String, status: Int, timestamp: Long)

    // ═══════════════════════════════════════════════════════════
    // 🔍 SEARCH: Media, Files
    // ═══════════════════════════════════════════════════════════

    /**
     * Получить сообщения с IMAGE вложениями (type: 0)
     * Для вкладки "Media" в поиске
     */
    @Query(
            """
        SELECT * FROM messages
        WHERE account = :account
          AND primary_attachment_type = 0
        ORDER BY timestamp DESC
        LIMIT :limit OFFSET :offset
    """
    )
    suspend fun getMessagesWithMedia(account: String, limit: Int, offset: Int): List<MessageEntity>

    /**
     * Получить сообщения с FILE вложениями (type: 2)
     * Для вкладки "Files" в поиске
     */
    @Query(
            """
        SELECT * FROM messages
        WHERE account = :account
          AND primary_attachment_type = 2
        ORDER BY timestamp DESC
        LIMIT :limit OFFSET :offset
    """
    )
    suspend fun getMessagesWithFiles(account: String, limit: Int, offset: Int): List<MessageEntity>

    /**
     * 📞 История звонков на основе CALL attachments (type: 4)
     * LEFT JOIN на dialogs нужен для имени/username/verified без дополнительных запросов.
     */
    @Query(
            """
        SELECT 
            m.*,
            CASE 
                WHEN m.from_me = 1 THEN m.to_public_key
                ELSE m.from_public_key
            END AS peer_key,
            d.opponent_title AS peer_title,
            d.opponent_username AS peer_username,
            d.verified AS peer_verified,
            d.is_online AS peer_online
        FROM messages m
        LEFT JOIN dialogs d
            ON d.account = m.account
           AND d.opponent_key = CASE
                WHEN m.from_me = 1 THEN m.to_public_key
                ELSE m.from_public_key
           END
        WHERE m.account = :account
          AND m.primary_attachment_type = 4
        ORDER BY m.timestamp DESC, m.id DESC
        LIMIT :limit
    """
    )
    fun getCallHistoryFlow(account: String, limit: Int = 300): Flow<List<CallHistoryRow>>

    /** Пиры, у которых есть call attachments (нужно для пересчета dialogs после удаления). */
    @Query(
            """
        SELECT DISTINCT
            CASE 
                WHEN from_me = 1 THEN to_public_key
                ELSE from_public_key
            END AS peer_key
        FROM messages
        WHERE account = :account
          AND primary_attachment_type = 4
    """
    )
    suspend fun getCallHistoryPeers(account: String): List<String>

    /** Удалить все call events из messages для аккаунта. */
    @Query(
            """
        DELETE FROM messages
        WHERE account = :account
          AND primary_attachment_type = 4
    """
    )
    suspend fun deleteAllCallMessages(account: String): Int

    /** Все сообщения аккаунта (для поиска по тексту), отсортированные по дате */
        @Query(
            """
        SELECT * FROM messages
        WHERE account = :account
          AND plain_message != ''
        ORDER BY timestamp DESC
        LIMIT :limit OFFSET :offset
    """
        )
    suspend fun getAllMessagesPaged(account: String, limit: Int, offset: Int): List<MessageEntity>
}

@Dao
interface MessageSearchIndexDao {

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun upsert(items: List<MessageSearchIndexEntity>)

    @Query(
            """
        SELECT * FROM message_search_index
        WHERE account = :account
          AND plain_text_normalized LIKE '%' || :queryNormalized || '%'
        ORDER BY timestamp DESC
        LIMIT :limit OFFSET :offset
    """
    )
    suspend fun search(
            account: String,
            queryNormalized: String,
            limit: Int,
            offset: Int = 0
    ): List<MessageSearchIndexEntity>

    @Query(
            """
        SELECT * FROM message_search_index
        WHERE account = :account
          AND dialog_key = :dialogKey
          AND plain_text_normalized LIKE '%' || :queryNormalized || '%'
        ORDER BY timestamp DESC
        LIMIT :limit OFFSET :offset
    """
    )
    suspend fun searchInDialog(
            account: String,
            dialogKey: String,
            queryNormalized: String,
            limit: Int,
            offset: Int = 0
    ): List<MessageSearchIndexEntity>

    @Query(
            """
        SELECT m.* FROM messages m
        LEFT JOIN message_search_index s
            ON s.account = m.account
           AND s.message_id = m.message_id
        WHERE m.account = :account
          AND m.plain_message != ''
          AND s.message_id IS NULL
        ORDER BY m.timestamp DESC
        LIMIT :limit
    """
    )
    suspend fun getUnindexedMessages(account: String, limit: Int): List<MessageEntity>

    @Query("DELETE FROM message_search_index WHERE account = :account")
    suspend fun deleteByAccount(account: String): Int
}

/** DAO для работы с диалогами */
@Dao
interface DialogDao {

    /** Вставка/обновление диалога */
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertDialog(dialog: DialogEntity): Long

    /**
     * Получить все диалоги отсортированные по последнему сообщению Исключает requests (диалоги без
     * исходящих сообщений от нас) Исключает пустые диалоги (без сообщений)
     */
    @Query(
            """
        SELECT * FROM dialogs
        WHERE account = :account
          AND (
              i_have_sent = 1
              OR opponent_key = '0x000000000000000000000000000000000000000001'
              OR opponent_key = '0x000000000000000000000000000000000000000002'
          )
          AND has_content = 1
        ORDER BY last_message_timestamp DESC
        LIMIT 30
    """
    )
    fun getDialogsFlow(account: String): Flow<List<DialogEntity>>

    /** Получить все диалоги с пагинацией */
    @Query(
            """
        SELECT * FROM dialogs
        WHERE account = :account
          AND (
              i_have_sent = 1
              OR opponent_key = '0x000000000000000000000000000000000000000001'
              OR opponent_key = '0x000000000000000000000000000000000000000002'
          )
          AND has_content = 1
        ORDER BY last_message_timestamp DESC
        LIMIT :limit OFFSET :offset
    """
    )
    suspend fun getDialogsPaged(account: String, limit: Int, offset: Int): List<DialogEntity>

    /**
     * Получить requests - диалоги где нам писали, но мы не отвечали Исключает пустые диалоги (без
     * сообщений)
     */
    @Query(
            """
        SELECT * FROM dialogs
        WHERE account = :account
          AND i_have_sent = 0
          AND opponent_key != '0x000000000000000000000000000000000000000001'
          AND opponent_key != '0x000000000000000000000000000000000000000002'
          AND has_content = 1
        ORDER BY last_message_timestamp DESC
        LIMIT 30
    """
    )
    fun getRequestsFlow(account: String): Flow<List<DialogEntity>>

    /** Получить количество requests Исключает пустые диалоги (без сообщений) */
    @Query(
            """
        SELECT COUNT(*) FROM dialogs
        WHERE account = :account
          AND i_have_sent = 0
          AND opponent_key != '0x000000000000000000000000000000000000000001'
          AND opponent_key != '0x000000000000000000000000000000000000000002'
          AND has_content = 1
    """
    )
    fun getRequestsCountFlow(account: String): Flow<Int>

    /**
     * Desktop parity: get all dialogs where opponent_title is empty or equals the raw
     * public key (or its prefix). Used by requestMissingUserInfo() to batch-resolve names
     * after sync, like Desktop's useUserInformation per-component hook.
     */
    @Query("""
        SELECT * FROM dialogs
        WHERE account = :account
          AND has_content = 1
          AND opponent_key NOT LIKE '#group:%'
          AND (
              opponent_title = ''
              OR opponent_title = opponent_key
              OR LENGTH(opponent_title) <= 8
          )
    """)
    suspend fun getDialogsWithEmptyTitle(account: String): List<DialogEntity>

    /** Получить диалог */
    @Query("SELECT * FROM dialogs WHERE account = :account AND opponent_key = :opponentKey LIMIT 1")
    suspend fun getDialog(account: String, opponentKey: String): DialogEntity?

    /** Найти direct-диалог по username собеседника (без учета регистра и '@'). */
    @Query(
            """
        SELECT * FROM dialogs
        WHERE account = :account
          AND opponent_key NOT LIKE '#group:%'
          AND LOWER(REPLACE(TRIM(opponent_username), '@', '')) = LOWER(REPLACE(TRIM(:username), '@', ''))
        LIMIT 1
    """
    )
    suspend fun getDialogByUsername(account: String, username: String): DialogEntity?

    /** Обновить последнее сообщение */
    @Query(
            """
        UPDATE dialogs SET 
            last_message = :lastMessage,
            last_message_timestamp = :timestamp,
            has_content = CASE WHEN TRIM(:lastMessage) != '' THEN 1 ELSE has_content END
        WHERE account = :account AND opponent_key = :opponentKey
    """
    )
    suspend fun updateLastMessage(
            account: String,
            opponentKey: String,
            lastMessage: String,
            timestamp: Long
    )

    /** Обновить количество непрочитанных */
    @Query(
            "UPDATE dialogs SET unread_count = :count WHERE account = :account AND opponent_key = :opponentKey"
    )
    suspend fun updateUnreadCount(account: String, opponentKey: String, count: Int)

    /** Инкрементировать непрочитанные */
    @Query(
            "UPDATE dialogs SET unread_count = unread_count + 1 WHERE account = :account AND opponent_key = :opponentKey"
    )
    suspend fun incrementUnreadCount(account: String, opponentKey: String)

    /** Сбросить непрочитанные */
    @Query(
            "UPDATE dialogs SET unread_count = 0 WHERE account = :account AND opponent_key = :opponentKey"
    )
    suspend fun clearUnreadCount(account: String, opponentKey: String)

    /** Отметить что я отправлял сообщения в этот диалог Возвращает количество обновлённых строк */
    @Query(
            "UPDATE dialogs SET i_have_sent = 1 WHERE account = :account AND opponent_key = :opponentKey"
    )
    suspend fun markIHaveSent(account: String, opponentKey: String): Int

    /** Обновить онлайн статус */
    @Query(
            """
        UPDATE dialogs SET 
            is_online = :isOnline,
            last_seen = :lastSeen
        WHERE account = :account AND opponent_key = :opponentKey
    """
    )
    suspend fun updateOnlineStatus(
            account: String,
            opponentKey: String,
            isOnline: Int,
            lastSeen: Long
    )

    /** Сбросить online-флаг у всех диалогов аккаунта (защита от устаревшего кэша при старте) */
    @Query("UPDATE dialogs SET is_online = 0 WHERE account = :account AND is_online != 0")
    suspend fun clearOnlineStatuses(account: String)

    /** Получить онлайн статус пользователя */
    @Query(
            """
        SELECT is_online, last_seen 
        FROM dialogs 
        WHERE account = :account AND opponent_key = :opponentKey
        LIMIT 1
    """
    )
    fun observeOnlineStatus(account: String, opponentKey: String): Flow<OnlineStatusInfo?>

    data class OnlineStatusInfo(
            @ColumnInfo(name = "is_online") val isOnline: Int,
            @ColumnInfo(name = "last_seen") val lastSeen: Long
    )

    /** Удалить диалог */
    @Query("DELETE FROM dialogs WHERE account = :account AND opponent_key = :opponentKey")
    suspend fun deleteDialog(account: String, opponentKey: String)

    /** Удалить все диалоги аккаунта */
    @Query("DELETE FROM dialogs WHERE account = :account")
    suspend fun deleteAllByAccount(account: String)

    /** Обновить информацию о собеседнике */
    @Query(
            """
        UPDATE dialogs SET 
            opponent_title = :title,
            opponent_username = :username,
            verified = :verified
        WHERE account = :account AND opponent_key = :opponentKey
    """
    )
    suspend fun updateOpponentInfo(
            account: String,
            opponentKey: String,
            title: String,
            username: String,
            verified: Int
    )

    /** Обновить только имя/username собеседника, не трогая verified */
    @Query(
            """
        UPDATE dialogs SET 
            opponent_title = :title,
            opponent_username = :username
        WHERE account = :account AND opponent_key = :opponentKey
    """
    )
    suspend fun updateOpponentDisplayName(
            account: String,
            opponentKey: String,
            title: String,
            username: String
    )

    /**
     * Получить общее количество непрочитанных сообщений, исключая указанный диалог Используется для
     * отображения badge на кнопке "назад" в экране чата
     */
    @Query(
            """
        SELECT COALESCE(SUM(unread_count), 0) FROM dialogs 
        WHERE account = :account AND opponent_key != :excludeOpponentKey
    """
    )
    fun getTotalUnreadCountExcludingFlow(account: String, excludeOpponentKey: String): Flow<Int>

    /** 🚀 Получить последнее сообщение диалога по dialog_key (использует индекс) */
    @Query(
            """
        SELECT * FROM messages 
        WHERE account = :account AND dialog_key = :dialogKey
        ORDER BY timestamp DESC, id DESC LIMIT 1
    """
    )
    suspend fun getLastMessageByDialogKey(account: String, dialogKey: String): MessageEntity?

    /** 🚀 Количество непрочитанных входящих сообщений от оппонента */
    @Query(
            """
        SELECT COUNT(*) FROM messages 
        WHERE account = :account 
          AND from_public_key = :opponentKey 
          AND to_public_key = :account
          AND from_me = 0 
          AND read = 0
    """
    )
    suspend fun countUnreadFromOpponent(account: String, opponentKey: String): Int

    /** 🚀 Проверить есть ли исходящие сообщения к оппоненту */
    @Query(
            """
        SELECT EXISTS(
            SELECT 1 FROM messages 
            WHERE account = :account 
              AND from_public_key = :account 
              AND to_public_key = :opponentKey
              AND from_me = 1
            LIMIT 1
        )
    """
    )
    suspend fun hasSentToOpponent(account: String, opponentKey: String): Boolean

    /** 🚀 Универсальный подсчет непрочитанных входящих по dialog_key (direct + group) */
    @Query(
            """
        SELECT COUNT(*) FROM messages
        WHERE account = :account
          AND dialog_key = :dialogKey
          AND from_me = 0
          AND read = 0
    """
    )
    suspend fun countUnreadByDialogKey(account: String, dialogKey: String): Int

    /** 🚀 Универсальная проверка исходящих сообщений по dialog_key (direct + group) */
    @Query(
            """
        SELECT EXISTS(
            SELECT 1 FROM messages
            WHERE account = :account
              AND dialog_key = :dialogKey
              AND from_me = 1
            LIMIT 1
        )
    """
    )
    suspend fun hasSentByDialogKey(account: String, dialogKey: String): Boolean

    /**
     * 🚀 ОПТИМИЗИРОВАННЫЙ updateDialogFromMessages Заменяет монолитный SQL с 9 коррелированными
     * подзапросами на:
     * - 3 индексированных запроса (getLastMessageByDialogKey, countUnreadFromOpponent,
     * hasSentToOpponent)
     * - 1 SELECT существующего диалога для сохранения метаданных
     * - 1 INSERT OR REPLACE Всё обёрнуто в транзакцию для консистентности. Использует dialog_key с
     * индексом (account, dialog_key, timestamp) вместо OR-условий.
     */
    @Transaction
    suspend fun updateDialogFromMessages(account: String, opponentKey: String) {
        val normalizedOpponentKey = opponentKey.trim()
        val normalizedOpponentKeyLower = normalizedOpponentKey.lowercase()
        val isGroupDialog =
                normalizedOpponentKeyLower.startsWith("#group:") ||
                        normalizedOpponentKeyLower.startsWith("group:")
        val isSystemDialog =
                normalizedOpponentKey == "0x000000000000000000000000000000000000000001" ||
                        normalizedOpponentKey == "0x000000000000000000000000000000000000000002"

        // 📁 Для saved messages dialogKey = account (не "$account:$account")
        val dialogKey =
                if (account == normalizedOpponentKey) account
                else if (isGroupDialog) normalizedOpponentKey
                else if (account < normalizedOpponentKey) "$account:$normalizedOpponentKey"
                else "$normalizedOpponentKey:$account"

        // 1. Получаем последнее сообщение — O(1) по индексу (account, dialog_key, timestamp)
        val lastMsg = getLastMessageByDialogKey(account, dialogKey) ?: return

        // 2. Получаем существующий диалог для сохранения метаданных (online, verified, title...)
        val existing = getDialog(account, normalizedOpponentKey)

        // 3. Считаем непрочитанные входящие по dialog_key.
        val unread = countUnreadByDialogKey(account, dialogKey)

        // 4. Проверяем были ли исходящие — O(1)
        val hasSent = hasSentByDialogKey(account, dialogKey)

        // 5. Один INSERT OR REPLACE с вычисленными данными
        val hasContent =
                if (
                        lastMsg.plainMessage.isNotBlank() ||
                                (lastMsg.attachments.isNotBlank() &&
                                        lastMsg.attachments.trim() != "[]")
                ) {
                    1
                } else {
                    0
                }
        insertDialog(
                DialogEntity(
                        id = existing?.id ?: 0,
                        account = account,
                        opponentKey = normalizedOpponentKey,
                        opponentTitle = existing?.opponentTitle ?: "",
                        opponentUsername = existing?.opponentUsername ?: "",
                        lastMessage = lastMsg.plainMessage,
                        lastMessageTimestamp = lastMsg.timestamp,
                        unreadCount = unread,
                        isOnline = existing?.isOnline ?: 0,
                        lastSeen = existing?.lastSeen ?: 0,
                        verified = existing?.verified ?: 0,
                        // Desktop parity: request flag is always derived from message history.
                        iHaveSent = if (hasSent || isSystemDialog) 1 else 0,
                        hasContent = hasContent,
                        lastMessageAttachmentType = lastMsg.primaryAttachmentType,
                        lastSenderKey = lastMsg.fromPublicKey,
                        lastMessageFromMe = lastMsg.fromMe,
                        lastMessageDelivered = if (lastMsg.fromMe == 1) lastMsg.delivered else 0,
                        lastMessageRead = if (lastMsg.fromMe == 1) lastMsg.read else 0,
                        lastMessageAttachments = lastMsg.attachments
                )
        )
    }

    /**
     * 📁 ОПТИМИЗИРОВАННЫЙ updateSavedMessagesDialogFromMessages Для saved messages (opponentKey ==
     * account)
     */
    @Transaction
    suspend fun updateSavedMessagesDialogFromMessages(account: String) {
        // 📁 dialogKey для saved messages = account (не "$account:$account")
        val dialogKey = account

        val lastMsg = getLastMessageByDialogKey(account, dialogKey) ?: return
        val existing = getDialog(account, account)
        val hasContent =
                if (
                        lastMsg.plainMessage.isNotBlank() ||
                                (lastMsg.attachments.isNotBlank() &&
                                        lastMsg.attachments.trim() != "[]")
                ) {
                    1
                } else {
                    0
                }

        insertDialog(
                DialogEntity(
                        id = existing?.id ?: 0,
                        account = account,
                        opponentKey = account,
                        opponentTitle = existing?.opponentTitle ?: "",
                        opponentUsername = existing?.opponentUsername ?: "",
                        lastMessage = lastMsg.plainMessage,
                        lastMessageTimestamp = lastMsg.timestamp,
                        unreadCount = 0,
                        isOnline = existing?.isOnline ?: 0,
                        lastSeen = existing?.lastSeen ?: 0,
                        verified = existing?.verified ?: 0,
                        iHaveSent = 1,
                        hasContent = hasContent,
                        lastMessageAttachmentType = lastMsg.primaryAttachmentType,
                        lastSenderKey = lastMsg.fromPublicKey,
                        lastMessageFromMe = 1,
                        lastMessageDelivered = 1,
                        lastMessageRead = 1,
                        lastMessageAttachments = lastMsg.attachments
                )
        )
    }
}
